In case anyone has seen spurious problems using MySQL version 5.0.32 (and presumably the identical 5.0.33 community version) you might want to take a look at MySQL Bugs #25653 and #25596. They are about a little (but serious) InnoDB bug.
If you do not yet use this version, you might consider waiting for a fixed release to become available.
Last week I needed to write an update SQL script that would insert some records into a database. However as the script was to be incorporated into a software update that was going to be deployed to all our customers, it needed to check some condition first and see, whether the insert was required at all.
Even though MySQL provides some non-standard SQL enhancement there is no INSERT IF EXISTS kind of statement.
I managed to do it using some temp-tables and a combination of INSERT IGNORE ... SELECT FROM and UPDATE ... WHERE statements.
The following example demonstrates how to insert a new row of data in the table named real_table. The data must only be inserted into this table, if another record in a table named condition_table exists. No change of real_table must occur, if there is no record matching the condition.
Moreover (because I needed this) the example takes take of modifying a possibly present record in real_table that already has the same primary key value as the one going to be inserted (PKvalue).
Now, how are we going to do this. First thing we need is a temporary table that resembles the structure of the real_table. It is going to keep the new row(s) for a moment. MySQL makes it simple to create a new temp table with the CREATE TABLE ... LIKE syntax:
-- create temp table with same structure as the real table
CREATE TEMPORARY TABLE temp_tbl LIKE real_table;
-- insert the new record into the temp table only
INSERT INTO temp_tbl (columnA, columnB, columnC , columnD , columnE, columnPK)
VALUES ( 1, 0, 'sampleValueA', 'sampleValueB', 9 , 'PKvalue');
So why is this necessary? Well, as I said before, the INSERT command does not allow any sort of condition. However the SELECT command - and along with that the INSERT ... SELECT - does. Now we can use it to read data from the temp table and along with it do a query on the condition_table. Because we do not know in advance whether the key is already present, the IGNORE option on INSERTwill be used. It will not abort with an error if there is a duplicate key.
As you can see, the WHERE condition does not have anything to do with the data we are going to insert. Instead it just checks if there is a "trigger record" in some other table. If such a record does not exist, the WHERE EXISTS clause will return false and the INSERT will not be done.
-- now try to insert the data into the real table.
-- take into account the external condition that must be met for this
-- insert to happen in the first place.
-- use "insert ignore" if there may be a chance of the PK being already present
-- and this is not an error
INSERT IGNORE INTO real_table (columnA, columnB, columnC, columnD, columnE, columnPK)
SELECT columnA, columnB, columnC, columnD, columnE, columnPK FROM temp_tbl
WHERE EXISTS (SELECT 1 FROM condition_table c
WHERE c.column = 'mustbepresentforinsert');
Now we are at a point where we can know for sure that the primary key value PKvalue exists in real_table. However because it may have been there before and the INSERT IGNORE might just have suppressed the error, we need to make sure, the rest of the record's fields are correct. To do so, we will do an UPDATE statement that is quite similar to the INSERT one:
-- if you used ignore above, the insert might not have done anything
-- because the PK was already present.
-- in this this update is needed to make sure you end up with the
-- new values you wanted to insert.
UPDATE real_table
SET columnA=1,
columnB=0,
columnC='sampleValueA',
columnD='sampleValueB',
columnE=9
WHERE columnPK='PKvalue'
AND EXISTS (SELECT 1 FROM condition_table c
WHERE c.column = 'mustbepresentforinsert');
Again there is the same EXISTS clause to make sure we also check the trigger record. However this time it is accompanied by a WHERE columnPK='PKvalue' to make sure we only update the record we (maybe) just inserted.
This is it. Now the data in real_table has been either inserted or updated to the desired values, but even if the condition was met. Finally we should drop the temporary table again:
DROP TABLE temp_tbl;
Just to make sure: you should surround the INSERT and UPDATE statements with a new transaction if you use a storage engine that supports it. The CREATE|DROP TABLEs are DDL statements and are not covered by the transaction mechanism anyway, however they might influence it (e. g. through implicit commits or something like that), so they should be left out.
After some time here is another post concerning MySQL. It is not as if I had not had anything to do with MySQL in the meantime, but most of it was mailing back and forth with their customer support (which is really quite good) to get some issues resolved we stumbled over in our MySQL 4.1 to 5.0 migration.
Before those were fixed we could not use MySQL 5 with our application, because there were some incompatible changes we could not work around.
One of them was the unsatisfactory precision when querying DECIMALs I wrote about earlier. This is fixed in 5.0.32-enterprise and the just released 5.0.33 community edition (see Bug #23260). In fact I was just about to write about the new release policy which I find somewhat strange (enterprise and community editions with the enterprise releases being faster than the community ones) when I noticed that a new community release had been made public the day before yesterday. If you like to know more about this whole "edition" thing, see the MySQL Performance Blog's post about where to get a recent MySQL version and also pay attention to the comments.
Another problem we noticed and reported to MySQL at the end of October was a changed behavior of InnoDB regarding rollbacks after lock-wait timeouts. MySQL 5 would only rollback last statement of a transaction if it ran into a lock-wait timeout. MySQL 4.1 instead rolled back the whole transaction. As we use a persistence framework which may automatically re-order a transaction's individual statements, this something that really bites us.
Fortunately MySQL agreed on providing a compatibility mode through a configuration option. Strangely this is not noted in the release notes of either 5.0.33 nor 5.0.32, however the relevant Bug #24200 contains the switch's name (innodb_rollback_on_timeout) which restores the old behavior.
So it seems we will be able to change to MySQL 5 finally.
This is the first post to this blog that I created using Writely - sorry - Google Docs and Spreadsheets.
It serves no particular purpose except to try the integration.
Even though it look nice at first glance I do not believe this is something I will use anymore in the future, as all formatting is done directly with spans and individual attributes.
There is no such thing as CSS integration which I have come to like very much. There are some options likeQuotations that are generated as block-quotesor
As announced I let the machine run till today - with no more crashes. The distributed GC interval was set to 4 hours. I believe we will be able to use this workaround in our production environments. Later this year we may upgrade to Java 6 which did not show the erratic behavior. Moreover, as we do not really know which combination of individual factors cause the bug to surface at all, we cannot even know for sure if there is a hardware configuration out in the field which would fail in the first place.
Nevertheless, thanks for all the input and suggestions. As a roundup I will just let the machine run memtest86 for some time to make sure this is not a memory problem. However I do not believe it will show any problems for reasons stated earlier.