MySQL database size limit crossed

[anita.kcx]

Hi,

I have a database called my_db1 which contains 3 tables. Out of the 3 tables one table is being populated 24*7. My hosting provider (i.e godaddy) provides 25 Mysql databases 1 GB each.
What should I do if the database exceeds the 1 GB size limit? Is there a way that my PHP script can detect the size of the database and automatically starts populating data to a different database with the same table structure?

Thanks
Anita

[StephNicolaou]

Unfortunately, GoDaddy doesn't allow you to increase your database size limit.

However you can have more than one database, if you upgrade you account plan.

If you have the Economy plan, you can have 10 databases, Deluxe gives you 25 database and you can also have an Unlimited plan.

The PHP sqcript to get your database size is as follows:

<?php
$db = mysql_connect("hostname", "username","password"); // MySql database connection
mysql_select_db("dbname",$db); //Select the database
?>

<?php
{
$sql = "SHOW TABLE STATUS"; //Command to get the database status
$result = mysql_query($sql); 
while($row = mysql_fetch_array($result))
{
$dbsize = $row['Data_length']+$row['Index_length']; // Columns 'Index_length' and 'Data_length' of each row calculated
}
echo($dbsize); // Print the database size
}
?>

Once you have the size (with $dbsize) you can check that thedbsize < 1GB and if true, run the following PHP script to create a table and it's tables:

<?php
$con = mysql_connect("hostname", "username","password"); 
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }

// Create database
if (mysql_query("CREATE DATABASE my_db",$con))
  {
  echo "Database created";
  }
else
  {
  echo "Error creating database: " . mysql_error();
  }

// Create table
mysql_select_db("my_db", $con);
$sql = "CREATE TABLE Employees
(
FirstName varchar(15),
LastName varchar(15),
Age int
)";

// Execute query
mysql_query($sql,$con);

mysql_close($con);
?>

Hope that helps :)

[anita.kcx]

Hey Thanks, this is what I was looking for.

Thanks,
Anita

[StephNicolaou]

No problem. Could you please mark this thread as solved if done so?

Thanks :)

[effectiveMySQL]

You can also retrieve the information from the INFORMATION_SCHEMA.TABLES table.