Bug #49777 mysqldump - does not report the same size after restore.
Submitted: 17 Dec 2009 18:41 Modified: 17 Dec 2009 22:05
Reporter: Tomas Dalebjörk Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server: Backup Severity:S3 (Non-critical)
Version:5.0.45-7.el5 OS:Linux (RHEL 5.3 x86_64 (2.6.18-128.el5))
Assigned to: CPU Architecture:Any
Tags: mysqldump

[17 Dec 2009 18:41] Tomas Dalebjörk
Description:
Hi,
A customer of my runned a "mysqldump" on eFEAP database, and restored that to a new database eFEAP_RESTORE.

And compared the different sizes, and noticed a size difference.
Is this really a good comparasion?

mysql> select table_schema, sum(data_length + index_length)/1024/1024 "DB Sizein MB" from information_schema.tables group by table_schema;
+--------------------+------------------+
| table_schema       | DB Sizein MB     |
+--------------------+------------------+
| eFEAP              |   55899.37500000 | 
| eFEAP_RESTORE      |   54515.76562500 | 
+--------------------+------------------+
13 rows in set (2 min 40.18 sec)

Or is this a bug?

Thanks in advance for an excellent support

How to repeat:
I have tried to reproduced this, but with no luck
[17 Dec 2009 19:04] Sveta Smirnova
Thank you for the report.

Yes, this looks incorrect. But version 5.0.45 is old and many bugs were fixed since. Please try with version 5.0.88 and inform us if problem still exists.
[17 Dec 2009 20:18] Tomas Dalebjörk
Thanks for your feedback.
Just a clearification.

Is the statement: 
- select table_schema, sum(data_length + index_length)/1024/1024
"DB Sizein MB" from information_schema.tables group by table_schema;

A good method to check if all data is restored?
[17 Dec 2009 20:20] Tomas Dalebjörk
I mean, couldn't the data before contain fragmented information.
While the restored data only contains defragmented information?
[17 Dec 2009 20:34] Peter Laursen
why not just SELECT COUNT (*) FROM every_table, if you want to check.

Peter
(not a MySQL person)
[17 Dec 2009 20:34] Peter Laursen
COUNT(*) and not COUNT (*) ....
[17 Dec 2009 20:57] Tomas Dalebjörk
I threat that feedback as that the "select" statement mentioned is not a correct method.
Thanks for the support 
you can close this case off now
[17 Dec 2009 21:03] Peter Laursen
well .. this report of mine may be related (or duplicate):
http://bugs.mysql.com/bug.php?id=48899

The storage volume reported from I_S and SHOW cannot be 100% relied on and it is not clear how a defragmented tablespace affects the reporting with various engines.
[17 Dec 2009 22:05] Sveta Smirnova
Thank you for the feedback.

Closing as "Not a Bug".