Mysql – Importing a thesqldump from MySQL 5.0.95 to MariaDB 10.0.36: Some tables incomplete

mariadbMySQLmysqldump

I have an ancient MySQL setup (5.0.95) that I'm trying to shut down. We've mysqldumped the databases we need, and we're trying to import them into MariaDB (10.0.36). We're running into some odd quirks, and I suspect it's because some of the tables are InnoDB. Post-import, several .ibd files are showing up on the new server in /var/lib/mysql/dbname that aren't present on the old server. For each table that has an .ibd file, we've noticed that the data import is incomplete to varying degrees. For example, one table should have more than 4,000 rows in it, but after import, it only has 307. Also, of the three views present in the database, two import correctly, but one appears as an empty table.

Here's some output from the latest import attempt:

mysqldump -u -p live < live_dump_from_old_setup.sql

User time 8.74, System time 0.22
Maximum resident set size 14896, Integral resident set size 0
Non-physical pagefaults 5767, Physical pagefaults 0, Swaps 0
Blocks in 0 out 0, Messages in 0 out 0, Signals 0
Voluntary context switches 2780, Involuntary context switches 33

My application connects to the database successfully after the import, so no problem there, but a lot of the data is missing.

I'm fairly new to MariaDB, even though it's supposed to be a drop-in replacement for MySQL, so I'm sure I'm missing something. How can I ensure that my entire mysqldump is imported correctly?

Best Answer

5.0 -> 5.1 -> 5.5 -> 5.6/10.0 -- That's 3 major upgrades. Various tables in the mysql database have been added/changed. Lots of changes to information_schema / performance_schema.

You would be quite lucky to leap from 5.0 to 10.0 without some major hiccups. You did run mysql_upgarde? MariaDB tries to make that handle multiple hops, but this may be more than it can handle.

if enforce_storage_engine is turned on, this could explain the sudden appearance of unexpected .ibd files. There can be hiccups in blindly changing the Engine.

How are you measuring number of rows? When using SHOW TABLE STATUS, MyISAM has exact numbers; InnoDB has approximate numbers. Even 307 versus 4000 is not unheard of. Use SELECT COUNT(*) .. to be sure.

As for the empty table -- manually run the load for it; see if you get any extra clues.

Related Question