I'm upgrading a legacy system running MySQL 5.1 all the way to MariaDB 10. We're on a brand new Windows server, running XAMPP. I've done plenty of DB migrations in my time but this one has just been a week of MySQL / InnoDB hell.
Tables are a mix of MyISAM and InnoDB. Every time MySQL crashes for any reason -- ANY REASON -- one or more of the InnoDB tables gets corrupted so badly I have to manually drop them and re-import them. By "manually drop them" I mean:
1) mysqld crashes on startup unless I do
innodb_force_recovery=6, and when I do that it puts the DB in read-only mode, so DROP TABLE just gives me a table is read-only error.
2) So instead I have to stop mysqld, delete the .ibd file for the table in question, restart mysqld with
innodb_force_recovery=0 (which will now work), and then finally DROP TABLE works.
3) Then I have to re-import 3-4gb tables from SQL dumps, which takes forever.
Interestingly it's never the MyISAM tables, just the InnoDB tables.
If I check the mysql_error.log, what I see is almost always something like this:
2015-12-19 1:39:41 6580 [Warning] InnoDB: Allocated tablespace 234, old maximum was 0 InnoDB: Error: trying to access page number 2601 in space 234, InnoDB: space name example/foobar, InnoDB: which is outside the tablespace bounds. InnoDB: Byte offset 0, len 16384, i/o type 10. InnoDB: If you get this error at mysqld startup, please check that InnoDB: your my.cnf matches the ibdata files that you have in the InnoDB: MySQL server. 2015-12-19 01:39:41 19b4 InnoDB: Assertion failure in thread 6580 in file fil0fil.cc line 5821 InnoDB: We intentionally generate a memory trap. InnoDB: Submit a detailed bug report to http://bugs.mysql.com. InnoDB: If you get repeated assertion failures or crashes, even InnoDB: immediately after the mysqld startup, there may be InnoDB: corruption in the InnoDB tablespace. Please refer to InnoDB: http://dev.mysql.com/doc/refman/5.6/en/forcing-innodb-recovery.html InnoDB: about forcing recovery. 151219 1:39:41 [ERROR] mysqld got exception 0x80000003 ;
After re-creating and importing the tables everything works great, as long as mysqld doesn't crash.
Sadly I can reproduce the steps to cause this pretty darn easily. All I have to do is run a really slow query -- for example, there is a particular report our system generates and the query behind it easily runs for 30 seconds (that's another story). If I happen to stop the mysqld service before it's finished, the very next time I start mysqld it crashes immediately and I'm back to dealing with corrupted InnoDB tables and repeating all the above steps.
innodb_fast_shutdown = 0, but it didn't help.
You might say, "just don't stop mysqld while that query is running"... and you'd be right, except live users in our system can be running that report at any time, which means any time I need to restart mysqld for any reason I'm taking a chance that it won't restart and I'll have to re-create tables all over again.
What am I doing wrong here?
Update 1 The long running query is just this (changed actual name of table, otherwise this is verbatim):
SELECT * FROM `foobar` ORDER BY `daterun` DESC
The table has approx 130,000 rows, and is about 4GB in size.