Why do innodb tables get corrupted so easily? 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.

I've tried 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.
Innodb is rather advanced and complex engine than myisam. It is a default storage engine for reasons...

you said the system is upgraded from 5.1 to mariadb 10 (eqv to 5.6). So the upgrade path was mysql 5.1 -> maria 5.5 -> maria 10?


Can you share the query?
Does the issue repeats if you: Kill query and then restart?
Is there anything apart from query running while you're shutting down?
I'd try to find out if there is related bug report.


Solutions to consider even before we identify exact error here:
- separate instance for reporting.
- optimize the query.