Swapping values in MySQL without violating constraints

I’ve learned something new! I am synchronizing lots of data coming in XML containers into a MySQL database. Some child nodes of that XML miss identifiers which could be used to update the table and hence I was just truncating the table and re-inserting those values (many to many relation, no id). But since this is a live-used database this lead to the problem that stuff disappeared while using it.

Till (thanks) had the idea to use an „imported“ flag which is swapped after synchronizing. That means we add a column called „imported“ and add a primary key on id,imported. In our live system we use the data with imported = 0 while we synchronize data with imported = 1. When the synchronization process finished, we swap imported from 0 to 1 (and 1 to 0) and delete the old (now imported = 1) data.

That would look like that:

$this->db->query("UPDATE " . $this->tableName . " SET imported = abs(imported - 1)");
$this->db->query("DELETE FROM " . $this->tableName . " WHERE imported = 1");

But that won’t work as I found out. It is moaning about the primary key would exist already. Sven (thanks) assumed that this might be caused because the UPDATE is not running atomic and if that would be the case, using transactions would help. I’ve created a test-case and noticed that the same happens with transactions. I removed the primary key and noticed it works. We’ve found an entry on stackoverflow which describes this problem in MySQL. In one of the comments one suggest to remove the key within that transaction, do the swapping and re-add the key.

Without testing, I assume that adding a key to a big table will take longer than a simple DELETE would; Since you have to use Transactions anyway, the proper way (in my case, since I don’t need both but just the newly imported stuff) would be:

$db->beginTransaction();
$db->exec("DELETE FROM test WHERE imported = 0");
$db->exec("UPDATE test SET imported = abs(imported -1)");
$db->commit();

But then, I can drop imported completly, and I don’t need to issue an UPDATE. INSERT would do fine.

No Comments

Post a Comment