April 17, 2014

More on MySQL Error Messages

I wrote about MySQL Error Messages before and as you might guess I’m not very happy with quality for error messages it produces. Now I’m revisiting this subject with couple of more annoying examples I ran into during last couple of days.

First question you should ask is what is error 17 ? Do to this you can run external utility perror:

It would be already a lot handy if one would walk through all error messages where error code is used and add the text description right into the code. ERROR 1010 (HY000): Error dropping database (can’t rmdir ‘./test/’, errno: 17 “File exists”) would already be a lot more helpful.

If you’re wondering why this could happen, the answer is you may get some extra files, which do not belong to MySQL in database directory. MySQL will only remove files it knows about such as “.frm” or “.MYI” when it is dropping the database, if you get something else, such as .sql file containing the dump of this database you will not be able to drop it until you remove this file manually.

Another enjoyable error message: “Can’t find messagefile ‘/usr/share/mysql/english/errmsg.sys”

Everything is good and clear right ? Nope !

The file does exist so “can’t find messagefile” error message is misleading to say the least. The error can’t open file would be much more appropriate here and error code (better with description) would be great.

What is the problem in this case ? It is SELinux denying permission to this file as can be seen from “dmesg”

The bad error reporting has an interesting property – you do not care as you do not see those error frequently, but when you do, especially if you’re under the time pressure, bad error reporting really hurts.
I wish Oracle would invest some time fixing those errors. No complicated code required and I bet this could be a great project for some Intern to complete over summer. Why do I point finger to Oracle you would ask, why do not we just fix it in Percona Server ? We could but because there are a lot of simple changes needed, plus many of message files would need to be corrected to include the place
to provide error description in addition to error code, this would make our merge process a lot more complicated, so it is a lot better if it is fixed in upstream.

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. Michael says:

    I agree, error reporting in MySQL sucks. You should never have to run an external command to figure out what the system is talking about.

  2. David says:

    Agreed!

    Even though I now know exactly what error 17 (among others) means on our system, it took a little investigation the first time around…

  3. Baron Schwartz says:

    There is another angle on this that I care about a lot. That is the ability to monitor how many errors have happened, for detecting errors and alerting people of a problem earlier. I have filed http://bugs.mysql.com/61030 for this request.

  4. Amit Kumar Patel says:

    Hi,
    I have problem in Mysql replication on Slave Server.
    Probelm:: Last_SQL_Error: Could not execute Update_rows event on table cbs.airtel_high_priority_sms_que; Can’t find record in ‘airtel_high_priority_sms_que’, Error_code: 1032;
    handler error HA_ERR_KEY_NOT_FOUND; the event’s master log replication.010901, end_log_pos 80129959

    respective log in Master show ::
    # at 80127091
    # at 80127185
    # at 80128145
    # at 80129105
    # at 80129959
    #110429 13:22:24 server id 1 end_log_pos 80127185 Table_map: cbs.airtel_high_priority_sms_que mapped to number 471362
    #110429 13:22:24 server id 1 end_log_pos 80128145 Update_rows: table id 471362
    #110429 13:22:24 server id 1 end_log_pos 80129105 Update_rows: table id 471362
    #110429 13:22:24 server id 1 end_log_pos 80129959 Update_rows: table id 471362
    #110429 13:22:24 server id 1 end_log_pos 80130711 Update_rows: table id 471362 flags: STMT_END_F

    BINLOG ‘
    OG66TRMBAAAAXgAAANGkxgQAAEIxBwAAAAAAA2NicwAcYWlydGVsX2hpZ2hfcHJpb3JpdHlfc21z
    X3F1ZQAPCAgPD/wDAw8MDAgPDwgPDRQADwACFAAUAAoAyAAgAA==
    OG66TRgBAAAAwAMAAJGoxgQAAEIxBwAAAAAAD/////8AgCNVEQAAAAAAAAAAAAAAAAAMOTE5Njc4
    ODgwNzQ3AJMAVGhhbmsgeW91IGZvciByZWdpc3RlcmluZy5Zb3UgaGF2ZSBiZWVuIGNoYXJnZWQg
    UnMxNSBmb3IgUmFuYSBEYWdndWJhdGkgZXZlbnQuQ2FsbCA1MDUwNTMgYXQgMnAvU2VjIG9uIDFz
    dCBNYXkgOTowMFBNLkZvciBUJkMgQ2FsbCA1MDUwNTkgVG9sbEZyZWUuAAAAAAMAAAAFNTA1MDXp
    FPxSShIAAAAAAAAAAAAAAAAAAAAAAAAAATdoEAAAAAAAAAEwAIAjVREAAAAAAAAAAAAAAAAADDkx
    OTY3ODg4MDc0NwCTAFRoYW5rIHlvdSBmb3IgcmVnaXN0ZXJpbmcuWW91IGhhdmUgYmVlbiBjaGFy
    Z2VkIFJzMTUgZm9yIFJhbmEgRGFnZ3ViYXRpIGV2ZW50LkNhbGwgNTA1MDUzIGF0IDJwL1NlYyBv
    biAxc3QgTWF5IDk6MDBQTS5Gb3IgVCZDIENhbGwgNTA1MDU5IFRvbGxGcmVlLgAAAAADAAAABTUw
    NTA16RT8UkoSAADAFfxSShIAADpSv6SXAAAAAAE3aBAAAAAAAAABMACAJVURAAAAAAAAAAAAAAAA
    AAw5MTk2Nzg4NzAyMzUAkwBUaGFuayB5b3UgZm9yIHJlZ2lzdGVyaW5nLllvdSBoYXZlIGJlZW4g
    Y2hhcmdlZCBSczE1IGZvciBSYW5hIERhZ2d1YmF0aSBldmVudC5DYWxsIDUwNTA1MyBhdCAycC9T
    ZWMgb24gMXN0IE1heSA5OjAwUE0uRm9yIFQmQyBDYWxsIDUwNTA1OSBUb2xsRnJlZS4AAAAAAwAA
    AAU1MDUwNegU/FJKEgAAAAAAAAAAAAAAAAAAAAAAAAABN2gQAAAAAAAAATAAgCVVEQAAAAAAAAAA
    AAAAAAAMOTE5Njc4ODcwMjM1AJMAVGhhbmsgeW91IGZvciByZWdpc3RlcmluZy5Zb3UgaGF2ZSBi
    ZWVuIGNoYXJnZWQgUnMxNSBmb3IgUmFuYSBEYWdndWJhdGkgZXZlbnQuQ2FsbCA1MDUwNTMgYXQg
    MnAvU2VjIG9uIDFzdCBNYXkgOTowMFBNLkZvciBUJkMgQ2FsbCA1MDUwNTkgVG9sbEZyZWUuAAAA
    AAMAAAAFNTA1MDXoFPxSShIAAMAV/FJKEgAAOlK/pJcAAAAAATdoEAAAAAAAAAEw
    OG66TRgBAAAAwAMAAFGsxgQAAEIxBwAAAAAAD/////8AgCdVEQAAAAAAAAAAAAAAAAAMOTE5OTU0
    MjQwNjY5AJMAVGhhbmsgeW91IGZvciByZWdpc3RlcmluZy5Zb3UgaGF2ZSBiZWVuIGNoYXJnZWQg

    This problem show only with updates query.

  5. David says:

    Agreed!

    Even though I now know exactly what error 17 (among others) means on our system, it took a little investigation the first time around…

  6. Michael says:

    I agree, error reporting in MySQL sucks. You should never have to run an external command to figure out what the system is talking about.

  7. Chris says:

    I’m getting this error and I’m having trouble resolving it – any help would be appreciated.

    A database ‘test’ exists and is empty. When I execute the command:

    DROP DATABASE test;

    I get the error in question in this article:

    ERROR 1010 (HY000): Error dropping database (can’t rmdir ‘./test/’, errno: 17)

    There is no directory ‘./test’. When I create an empty ‘/.test’ directory the command still fails.

    Any thoughts on where MySQL is trying to go to delete this ‘./test’ directory?

    Thanks,
    Chris

  8. Chris says:

    Well, I figured it out.

    I went to the MySQL basedir and in the data directory there was a ‘test’ directory with a hidden file ‘.empty’ of size 0.

    Removing that file allowed the DROP DATABASE test; command to execute successfully.

    Chris

  9. huarong says:

    I think ‘.empty’ should be a bug….
    There is also a .empty file in data/mysql/

Speak Your Mind

*