August 23, 2014

MySQL Error control changes

In MySQL 5.0 mainly error control was improved, such as strict mode was added to change famous MySQL behavior of cutting too large strings, too big numbers and allowing you to use dates such as February 31st.

In one case however reverse change was done – in regards to storage engine initialization. Previously if you start MySQL and Innodb storage engine fails to initialize (ie you resized log file but forgot to delete old ones) MySQL Server simply would not start. In recent MySQL 5.0 series however it will continue loading and simply have Innodb storage engine disabled.

This can cause numerous problems especially if you got use to old behavior and do not check MySQL logs but simply check it is started. It especially hurts if you have only some tables in Innodb so you might not notice part of your application does not function. Also monitoring often monitors MySQL is up and running and will not query all tables are accessible not catching such errors. If MySQL is started with Innodb tables disabled accesses to Innodb tables will simply result in errors.

The other way I’ve seen it beating up people is performing Innodb conversion…. By default MySQL substitutes storage engines so if Innodb is disabled MyISAM is used, so I’ve seen people thinking they have converted things to Innodb while they really did not because it was substituted back to MyISAM.

I do not think this change is good one but I’d expect this is something we’ve got to live with – with new “Plugable Storage Engine” concept the importance of storage engines is decreased – if one of these storage engine can’t start who cares ? I do because it has my data in it.

P.S One thing I’m happy tables from storage engines which failed to initialize are not automatically replaced with empty MyISAM tables of the same structure :)

About Peter Zaitsev

Peter managed the High Performance Group within MySQL until 2006, when he founded Percona. Peter has a Master's Degree in Computer Science and is an expert in database kernels, computer hardware, and application scaling.

Comments

  1. BOLK says:

    “o I’ve seen people thinking they have converted things to Innodb while they really did not because it was substituted back to MyISAM.”

    What about full text indexes in InnoDB?

  2. peter says:

    Innodb does not support full text indexes, the work is on a way thought.

    For smaller projects you can use shadow MyISAM table, for larger I would use sphinx (http://www.sphinxsearch.com)

Speak Your Mind

*