Mysql – theISAM data loss at DELETE / INSERT

deleteinsertmyisamMySQL

I have a linked table id_group | id_user. Every time I add a one or more users to a group I DELETE all the links and INSERT the users again including the new one(s). This is how the framework of the application works.

From time to time some groups lose all the users they had, except the new ones, which are inserted.

The primary index of the table is the pair id_group - id_user.

I cannot reproduce this through a query, but the client has complained multiple times and there's an issue no doubt.

I could explain this if the table was innoDB with the DELETE locking the index and the INSERT not being able to add that pair again, however this is a myISAM table and myISAM locks the entire table during DELETE statements.

Best Answer

Is it possible you've got two processes attempting to do this at almost the same time? If this is a web application, could you have a site user who is double-click happy?

Process #1 "examine existing users"
Process #1 "remove all users"
Process #2 "examine existing users" (no users would found)
Process #1 "insert new users and put existing users back"
Process #2 "remove all users"
Process #2 "insert new users and put existing users back (but no existing users were found)"

Related Solutions

MySQL – How Concurrent Insert is Decided for MyISAM Table

While you can do what Rolando suggests and set concurrent_insert=2 to always enable concurrent inserts, to answer your question about filling holes:

we have a MyISAM table with a gap. When we insert new rows and fill those gaps, does the table "immediately" get ready to accept "concurrent inserts" for future insert queries?

Yes (emphasis mine):

If there are holes, concurrent inserts are disabled but are enabled again automatically when all holes have been filled with new data. [src]

Disclaimer: I haven't actually tested it. It seems unless you inserted the exact same data-length in the holes, you will still have holes somewhere.

You can see if there are holes from a query such as this (data_free=0 would mean no holes):

SELECT table_name, data_free FROM information_schema.tables WHERE table_schema='FOO' AND engine='myisam'

Innodb – Fastest way to copy data from MyISAM to InnoDB

There are a few solutions. First, however, I'm not sure about the process in the first place. Is this a one time copy operation? A recurring copy operation? Do you want to migrate from MyISAM to InnoDB?

What is the main reason for your desire for a quick operation?

If you're looking for migration, then why don't you use an online table alter tool, such as oak-online-alter-table (disclaimer: I'm author of this tool) or pt-online-schema-change? Both will allow you to change your schema live and online with very little disturbance.

If you're looking to a copy+paste of your data, then I would suggest using chunking: copying the data in small packets. This way you don't get that huge lock and no funny timeouts. You can use either oak-chunk-update or pt-archiver for this. This may actually make the total runtime shorter because of reduces locking, but may also take longer. Also consider that it is not an atomic operation, and changes to original table while copying is made, may not get caught, so you may get an inconsistent copy.

Otherwise (or in addition) you can use all the usual tweaks, such as

SET GLOBAL innodb_flush_log_at_trx_commit := 2;

or set

[mysqld]
innodb_doublewrite = 0

or perhaps, depending on OS and disks,

[mysqld]
innodb_flush_method = O_DIRECT

Each of the above may reduce disk I/O access. First two will also make your server less crash safe. But if for limited time, this may be OK for you.

Best Answer

Related Solutions

MySQL – How Concurrent Insert is Decided for MyISAM Table

Innodb – Fastest way to copy data from MyISAM to InnoDB

Related Question