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.

Improve this question
11
  • 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, 2015 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, 2015 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, 2015 at 17:09
  • 1
    How is "shutdown" done?
    – Rick James
    Dec 20, 2015 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, 2015 at 9:56

2 Answers 2

Reset to default
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.

Improve this answer
2
  • 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, 2015 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, 2015 at 9:34
1

I have been working with Xampp 5.6 for Windows in localhost, for many years, and I noticed that the MariaDB which comes with this package invariably corrupts my WordPress InnoDB tables.

Consequently, I decided to replace MariaDB with MySQL 5.6.44 and the result was astonishing: never more my InnoDB tables became corrupted! I guess it's a bug of this specific version of MariaDB, that comes with this Xampp version.

I had to copy to the MySQL folder the my.ini file and the data and backup folders, that are inside MariaDB folder. I also deleted the lib folder that comes inside the MySQL folder, as suggested here, to save space in my installation.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.