Interesting mySQL backup problem . . .

[moondog]

I am backing up several tables via a PHP script. The script lists the tables, you click on one, then click "backup table" and the table is backed up (the new table name is the old table name appended with the date of the backup, so table 'orders' would become 'orders_043007').

The problem is the size of the backed up table. They always end up being 6x larger than the original table.

I backed up one table last night that had a size of 31.8 MB and the backup was 197.4 MB, according to the data in phpMyAdmin.

The SQL I am using to do the backup is:

$dateString = date('mdy');
$newTable = $_POST[buTable] . "_$dateString";
$sql = "CREATE TABLE $newTable AS SELECT * FROM $_POST[buTable]";

The other thing I am doing after the copy / creation is I am setting the primary keys:

		# add primary keys
		$pk = array (
			'AUTO_ORDER' => 'ID',
			'CB_ITEMS' => 'id',
			'CB_ITEMS_CLOSED' => 'id',
			'CB_ORDERS' => 'number',
			'CB_ORDERS_CLOSED' => 'number',
			'CREDITS' => 'id',
			'CUSTOMER' => 'custID',
			'product' => 'id',
			'RESUBMITS' => 'id',
			);
			
		$pk_type = array (
			'AUTO_ORDER' => 'int',
			'CB_ITEMS' => 'bigint',
			'CB_ITEMS_CLOSED' => 'bigint',
			'CB_ORDERS' => 'bigint',
			'CB_ORDERS_CLOSED' => 'bigint',
			'CREDITS' => 'bigint',
			'CUSTOMER' => 'bigint',
			'product' => 'int',
			'RESUBMITS' => 'bigint',
		);
		
		$sql = "ALTER TABLE $newTable ADD PRIMARY KEY({$pk[$_POST[buTable]]})";
		$result = mysql_db_query($db_name,$sql,$db_connect);
		if(mysql_error($db_connect)) {die("Error:<br>SQL: $sql<br>Error: " . mysql_error($db_connect) . "<br>");}
		
		
		$sql = "ALTER TABLE $newTable CHANGE {$pk[$_POST[buTable]]} {$pk[$_POST[buTable]]} {$pk_type[$_POST[buTable]]} NOT NULL AUTO_INCREMENT";
		$result = mysql_db_query($db_name,$sql,$db_connect);
		if(mysql_error($db_connect)) {die("Error:<br>SQL: $sql<br>Error: " . mysql_error($db_connect) . "<br>");}
		main("Backed up Table: $_POST[buTable]");

Anybody have any ideas or thoughts as to why this large increase in size might be happening?

 

[mikesherov]

A few questions that may get you thinking about the solution:

1. Was the original data created using a different version of MySQL than you are using now? The way certain things are stored changed over the past few MySQL updates. For example, namepros member DanR can tell you that DECIMAL changed the way it works as of MySQL 5.0.3! http://www.namepros.com/programming/320554-urgent-mysql-issue-will-pay-25-a.html

2. Why are you altering the new table's primary key type? If you look at http://dev.mysql.com/doc/refman/5.0/en/numeric-types.html , you'll see that BIGINT requires 8 bytes, where INT only requires 4. MEDIUMINT requires 3, SMALLINT requires 2, and TINYINT only 1!

3. Have you tried using ANALYZE TABLE? This is a dead giveaway if PHPMyAdmin is saying that the cardinality of your indexes is "none". See if that helps.

Other than that, it may be helpful if you most the structures of your old and new tables here. Use PHPMyAdmin to dump the structure of both tables and post it here...

 

[moondog]

The backup is on the same server, the same mysql version. Basically, once per week, I just go in and backup the existing table to the same database, with the date appended. Same server, same version, same everything.

The reason I alter the table's primary keys is because I noticed that when I did the SQL:

$sql = "CREATE TABLE $newTable AS SELECT * FROM $_POST[buTable]";

the primary key information did not follow through. When I had to restore the table to where it was previously, I would rename the table back to its original form, but then I would have to manually set up the primary keys again. So, I just did it via code.

Lastly, you suggest that I run an "analyze" on the table. I did that and the message I receive back was that the status was "OK". And, the cardinality is set (i.e. it does not say "none").

Ugh . . .

-Bob

A few questions that may get you thinking about the solution:

1. Was the original data created using a different version of MySQL than you are using now? The way certain things are stored changed over the past few MySQL updates. For example, namepros member DanR can tell you that DECIMAL changed the way it works as of MySQL 5.0.3! http://www.namepros.com/programming/320554-urgent-mysql-issue-will-pay-25-a.html

2. Why are you altering the new table's primary key type? If you look at http://dev.mysql.com/doc/refman/5.0/en/numeric-types.html , you'll see that BIGINT requires 8 bytes, where INT only requires 4. MEDIUMINT requires 3, SMALLINT requires 2, and TINYINT only 1!

3. Have you tried using ANALYZE TABLE? This is a dead giveaway if PHPMyAdmin is saying that the cardinality of your indexes is "none". See if that helps.

Other than that, it may be helpful if you most the structures of your old and new tables here. Use PHPMyAdmin to dump the structure of both tables and post it here...

 

[mikesherov]

If you have data that was created before MySQL 5.0.3 but was then upgraded, the previous data behaves like normal until it's reused or inserted into a new table.

The data can be on the same server and exist in two states!