1

I'm quite new to the process of importing/exporting databases. I'm using Maria DB, and HeidiSQL to run queries, etc.

I exported an entire database from our dev server, and loaded it into our live server. The script ran without a hitch. No errors, everything appeared to go as planned.

However, when I open two instances of HeidiSQL and compare the details of the tables side by side there are some weird looking discrepancies.

For example, in some cases tables on the newly migrated version say they have more data than the database they were exported from. How is this possible? does HeidiSQL just 'estimate' the amount of rows?

For example, according to HeidiSQL table 1 on the dev server contains 177 rows of data, with a total size of 64.0 KiB.
Where as the same table 1 on the live database contains 219 rows of data, with a total size of 64.0 KiB.

Even more weird, table 2 contains 2,254,576 rows of data, with a total of 232.9 MiB, while the same table on the live database contains 2,334,922 with a total of 224.0 MiB. There are also tables showing less data on the live database version than on the dev database version.

Why is this?

The SQL file creates the copy of the database from scratch on the live database server, it didn't delete or overwrite an older version or anything, and there were no errors on completion.

EDIT:

If I run SELECT COUNT(*) FROM table_2 both databases return 2,326,087 rows. So it appears to be an issue with displaying the correct count in HeidiSQL.

2

From the symptoms, it seems that you are using InnoDB engine for your tables. If so, it is normal not to have accurate number of rows shown by clients (Like HeidiSQL or PHPMyAdmin).

It is also worth it to mention that SHOW TABLE STATUS LIKE 'table_name' does not provide accurate row count for InnoDB tables.

InnoDB estimates the number of rows in the table. If you want the exact count, you should run SELECT COUNT(*), as you just did.

MyISAM, on the other hand, has the information of its number of rows saved and 'always' up to date.

In regards to physical file size, there are many factors that leads to tables that have same date to have different file size on disks. Some are related to the operating system itself, and the file system used; some are related to the configs of MySQL; and some are related to the fragmentation of the data when doing deletes and updates. In short, this difference is very normal.

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.