1

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?

  • The extra ibd file isn't an issue as innodb_file_per_table defaults to 1 in mariadb. Maybe try pt-table-sync to copy the database. Was there errors on the import? – danblack Sep 25 '18 at 22:05
  • what happens when you execute the failed CREATE VIEW.. directly (syntax from SHOW CREATE VIEW {viewname} on the origin? – danblack Sep 25 '18 at 22:07
  • @danblack: Several page faults. Not sure about their significance in the grand scheme, but other than what I reported above, everything seemed to import fine. When I create the missing view manually, it works on whatever data imported successfully. Odd. I'm going to try pt-table-sync as you suggested and see where that gets me. I'll report back. – Chris Sep 25 '18 at 22:24
  • page faults, not sure of its meaning in a data import context. The view creation might of been the mysqldump added the view definition before a table was created that is used. Glad its resolved. Hope you have success with pt-table-sync. – danblack Sep 25 '18 at 23:07
  • 1
    So, oddly, the import worked without a hitch when I did it from within the MariaDB command line. source dbname.sql fixed it right up. – Chris Sep 25 '18 at 23:38
0

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.

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.