Search This Blog

ERROR 1033 (HY000) on InnoDB configuration error

One of the key features MySQL often
uses to advertise for their database is the modular architecture that
allows them to have different storage engines below the same SQL
layer. In practice the application and/or database designer can
choose from a variety of low level data storage implementations that
each offer different characteristics and may be chosen on a per table
basis. (Even though I personally believe most designs will use one
type of table for all tables of a particular schema).

The idea behind this is that for
example people who do not need transactions should not have to worry
about them at all – maybe there is a performance impact involved
which they cannot afford to take. Moreover some specialized types of
index or column might not be available on all engines. Basically the
concept is very interesting and can be really useful for developers.

However there is a weakness that in my
opinion needs some severe work to be done: The interface between the
common SQL layer and the storage engines seems to be somewhat limited
with respect to what storage engines can do to inform the level above
about status and error conditions.

For example there is no (elegant) way
to find out about the details of a constraint violation problem when
using the InnoDB storage engine. While you will get an error message
that some statement failed due to a violation of referential
integrity constraints, you have to use the generic “show engine
innodb status” command to get some details. However this will not
only tell you about the error you care about at that particular
moment, but will also give you lots of information on lots of other
stuff inside InnoDB. This is however necessary, because you do not
have any other means of find out out about those – e. g. when you
are investigating a performance problem.

From what I learned from a consultant
some time ago this is due to the limit interface specification
through with MySQL itself (the upper layer) and the storage engines
talk to each other. Because this protocol has to be somewhat generic
messages from the bottom to the upper levels have to be somehow
wrapped into some form of special result set which you then have to
parse and understand on your own. Moreover if memory serves me right,
there is a limitation on how much data can be transferred at a time
(could be a limitation of the client as well). Because of this you
will not even always get a full InnoDB status output, because it will
be truncated if it gets bigger than 64k.

While this is not particularly nice it
is a limitation I believe is acceptable, especially in the case of
InnoDB, because the innodb_monitor feature allows you to get the full
output into a log file.

What I consider much worse however, is
that error messages from the underlying storage engine are often
mapped to more generic MySQL messages in an unpredictable way.

Time and again I have run into problems
that present you with an error message that has nothing to do with
the actual problem. For example in a replication scenario you might
get an error message 1236, claiming that there is something wrong
with replication position counters, but it turns out that this
message can also mean a full disk on the master. If you know enough
about the implementation details you might see the way this message
comes to pass, but if you are troubleshooting a production system
this is not what you want to do. Moreover I tend to forget these
peculiarities if they are just seldom enough. Just recently I found a
machine spitting out strange errors on each and every query I issued
(InnoDB):

Now, just having this message, what
would you expect is wrong here? File corruption? Broken SCSI
controller? Faulty memory?

When you use Google to search for
“ERROR 1033 (HY000)” you will get all sorts of results, most of
them suggesting to try myisam_check (not very useful for InnoDB) or
the REPAIR statements. Often you will find someone who claims that
restoring from the latest backup might be the only option.

While all of this is certainly true to
solve the problems that this error message what originally intended
to report, in my case they were all just leading in the wrong
direction.

Turns out that something was wrong with
the my.cnf configuration file. This was on a machine set up using the “Streaming Slave Deployment” mechanism I described in an
earlier article. For some reason the script that usually adapts the
config file automatically after downloading the data files had not
been started, so a default my.cnf was still in place.

Unfortunately the InnoDB data file
sizes did not match those downloaded from the master server. This is
what my.cnf contained:

It may not be obvious, but 555745280
bytes for ibdata1 is not 512MB, but 530MB. Nevertheless the MySQL
server started even with this wrong configuration. However every
statement would fail with the message above.

While I really like MySQL and find it
generally rather easy to configure and get very high performance,
this is definitely a major weakness I would like to see improved in
future versions. For the time being I will try to make a post about
anything that strikes me odd enough for someone to be interested in,
too :)

Worked great for me too...Moved 72GB db to new server with much larger settings in the new my.cnf... put in the old one and the error went away.I then mysqldump'd the data, put the new startup db and my.cnf in place and reloaded teh mysqldump'd data with the new settings.Thanks!

You could mark this file as autoextending. However getting an existing file to precisely 500MB won't be possible. You can add a second data file (keeping the first one at 82MB) and specify that to be 418MB if you like.

MySQL v5.0.26 complains "Incorrect information in file", when InnoDB is not loaded. Use "SHOW VARIABLES" to check if MySQL is having INNODB. If not, uncomment the innodb_data_file_path as it throws a "syntax error"-Error on that.

For other comments above I would like to note, that same/similiar error happends when you changeinnodb_log_file_sizesolution seems to be removing ib_logfile* after successful and safely shutted down mysql.

I had this problem as well. My issue was the tmp dir mysql was set to use had permissions that were too strict. Once fixing the perms some things worked better but I needed to also bounce the entire mysql server. Then things worked right

Popular posts from this blog

Today I had to look at a piece of code a colleague had written, using my XPathAccessor class. She used it in a servlet which gets XML formatted requests. As those are generated by an external 3rd party tool we agreed on some XML schema definitions. Everything they send us needs to conform to its corresponding schema, each reply we send gets validated against a different set.In order to allow independent testing on either side, we provided a little test kit that allows testing our system without having to set up a servlet engine. Basically it just takes a file, reads it into a String and hands that to the handler.First it gets parsed without validation. This is necessary to find out which type of request we were send (the address is the same for all of them). After the root element is known, it will be read again, this time using the right schema to verify the request.Once that is done, some reply is put together and sent back to the client. So far, so good.When I looked at the code I …

(Also see the follow-up post about some progress)Today I was (again) facing a log file from a machine that had for some reason not been able to start a temporary MySQL daemon during the night to prepare for a streaming MySQL slave installation. The necessary 2nd daemon had created its new ibdata files, however just after that aborted the startup process with the following message:Can't start server: Bind on TCP/IP port: No such file or directory
071001 23:09:55 [ERROR] Do you already have another mysqld server running on port: 3310 ?
071001 23:09:55 [ERROR] Aborting
071001 23:09:55 [Note] mysql\bin\mysqld.exe: Shutdown completeAs you can see, the port is a different one from the default MySQL port, so I can be sure there was no conflict with the primary instance. Even more curiously the same process has been working on that and other machines flawlessly for some time. However I remember having seen this message once before, but back then I did not have the time to look into it any…

Some words in advance...Recently I wrote about multi-threading problems with java.util.Calendar and java.text.DateFormat. The last sentence was So maybe it is time to search your code for all static usages of the Calendar and various ...Format classes, before you start getting strange errors.Searching code is not very practical, especially if you do it manually. Everyone knows you can look at code for hours, without seeing an problem - and as soon as it has reached production systems it starts breaking up in various ways :-)Fortunately smart and reknown people have devised ways of making the computer look for bugs automatically. Amongst others, FindBugs is a very nice - and free - tool that analyzes your Java application's compiled bytecode and looks for numerous so called bug patterns. Those patterns are divided into categories, such as "Bad practice", "Correctness", "Multithreaded correctness", "Performance" and some more. Each of them lo…