The Cry of the Dolphins

I had a problem the last few months that I could not understand why it happened, but because it was almost never raised on the production server, I didn't invest a lot of time to find out the reason for it.

The problem was that on some period in time, an index in a table of MySQL that is declared as Auto Increment lost it's value, and the new indexes started counting as "1" instead of (for example) 32507. Now I know what you are thinking, I might have used a small-sized integer, and there was an integer overflow, and instead of giving negative values, it just started counting with "1". Well, no! I used an unsigned big int type. The data on that table is just keep on changing like a temporary table, and the old data is been stored on different table.

I kept restoring the right index value every time it happens, until I started having deadlocks. The deadlocks were very weird, because only one program opened the connection, and it put itself on a deadlock, so I could not understand why I'm having them. After talking with Oded Arbel, and after he enlighten me regarding MySQL and deadlocks, I started investigating the subject. I found out something really disturbing that you might find as well after googeling or something:

MySQL have several table engines: one of them is InnoDB, another one is MyISAM (that replaced ISAM), and many other engines exists as well, that each of them handles and stores the data differently, and therefor giving different advantages over the others. I believe that this is one of the reasons MySQL became so popular with Open Source projects.

I found out that InnoDB stores indexes only in memory, and it even has a variable inside that I can set to tell MySQL how many bytes to allocate for the task. So now things started to be clear. The above problem happened every time I stopped MySQL from running, because I required to add additional hardware to the server, or because I needed to make maintenance that was needed to make sure that nothing will be written to any table/databases. So MySQL cleared the memory of the indexes, and because most of the time, the table was empty, MySQL assumed that it required to start counting from “1″, but the other table that stored the information, used the value of the “temporary” table index, which collided with unique index values.

I used InnoDB because I’m making a lot of insert update and delete actions on the that table, and InnoDB is supposed to be faster in that area, where MyISAM that stores the index values in an "myi" file, supposed to be faster in reading data, but slower in changing the data itself.

It seems that the biggest advantage of MySQL has become its biggest disadvantage, and now it is not usable for me anymore, but I can’t stop using it on production servers, only for new projects.

11 מחשבות על “The Cry of the Dolphins

  1. ארתיום

    The thing you tell is more then strange. "I found out that InnoDB stores indexes only in memory". Are you sure? That means for huge DB it will have to rebuild the indexes each time you open DB?
    How the tables are defined? How the indexes are defined? Maybe you had really defined "Temporary" indexes because they should not be temporary…

  2. ik_5

    InnoDB takes the last used index + one in the table you are using, or start counting from 1 if non found. You can test it for yourself as follows:

    1. Create an InnoDB with a unique ID that is an Auto Inc.
    2. Populate the data as you like.
    3. Delete all records.
    4. restart MySQL daemon.
    5. Add a new record
    If you will follow the above instructions by the order, you will reproduce it for yourself.

  3. ארתיום

    What is wrong with this behavior? Simple code:
    create table x ( a integer primary key not null, b varchar(10));
    insert into x values(NULL,'me');
    insert into x values(NULL,'myself');
    select * from x;
    delete from x;
    insert into x values(NULL,'another me');
    select * from x;

    gives: under Sqlite3

    1|another me

    As you can see the counting will begin from 1 and I do not understand why do you expect something else? The key I unique withing the table and there is no reason to give something else?

  4. ik_5

    So MySQL does not react properly for my needs. For example, on Firebird SQL, you have a trigger that does things for you, and there for it stores the old value, and you can read it, write new values etc..
    And so does few other database engines such as PostgreSQL that also stores the index value on a sequence. My point is that MySQL is not usable for tasks like this. I worked with several database engines to think that this behavior is not correct imho. and the fact that so many database engines have found a way to do it correctly emphasis the problem.

  5. פינגבק: Firebird או המסד הנתונים הפתוח השלישי שלכם « לראות שונה

  6. פינגבק: להתחיל לעבוד עם firebird « לראות שונה

  7. פינגבק: הבלוג של ארתיום

  8. פינגבק: עוד פעם בעיות עם MySQL « לראות שונה

להשאיר תגובה

הזינו את פרטיכם בטופס, או לחצו על אחד מהאייקונים כדי להשתמש בחשבון קיים:

הלוגו של

אתה מגיב באמצעות חשבון שלך. לצאת מהמערכת / לשנות )

תמונת Twitter

אתה מגיב באמצעות חשבון Twitter שלך. לצאת מהמערכת / לשנות )

תמונת Facebook

אתה מגיב באמצעות חשבון Facebook שלך. לצאת מהמערכת / לשנות )

תמונת גוגל פלוס

אתה מגיב באמצעות חשבון Google+ שלך. לצאת מהמערכת / לשנות )

מתחבר ל-%s