4

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.

  • 1
    Shutting down or restarting mysql service should not result in corrupted tables. This sounds like a bug or failing hardware (RAM, disk). – ypercubeᵀᴹ Dec 19 '15 at 14:22
  • Also, does this happen in MySQL 5.1 version? Or in some other version in the migration route (MySQL 5.5, Maria 5.1, Maria 5.5, Maria 10)? – ypercubeᵀᴹ Dec 19 '15 at 14:24
  • I wonder if mysqladmin shutdown would be a better shutdown approach than whatever ridiculous thing Windows might be doing... But that doesn't change the fact that you shouldn't need to be shutting down a production server, and when you do, InnoDB should be able to recover, if recovery were needed in the first place, which it shouldn't be... – Michael - sqlbot Dec 19 '15 at 17:09
  • 1
    How is "shutdown" done? – Rick James Dec 20 '15 at 23:16
  • 1
    No, then no. You did not upgrade the server, only dump and restore the database in a different version. I would not expect anything wrong, like crashes from that. So, you have 2 servers running with xamp, one with 5.1 and another with Maria 10. Not sure, my guess would be that there might be some kind of incompatibility between xamp and Maria. You should probably raise an bug request or post in Maria DB discussion list (including the error log and the exact details of your installation). – ypercubeᵀᴹ Dec 22 '15 at 9:56
1

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.

  • I'd for sure not deny possibility of failing hardware but it is less likely as the issue occurs on manual intervention everytime and not automatically. You should share query details and also consider alternatives to reporting and restarts. Also – mysql_user Dec 20 '15 at 7:20
  • it's failing on a brand new server with all new hardware, but granted that doesn't rule out hardware failure... although it's the only issue we've run into so far – David R. Dec 22 '15 at 9:34

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.