Search code examples
mysqlauto-increment

Inserted rows vanish

We're having a problem on a wiki site (using mysql 5.5.9). There is a text table, and a revision table where revision.rev_text_id is a foreign key to text.old_id (there isn't a new one, ask wikimedia).

There is an INSERT INTO text..., then INSERT INTO revision... which uses the new old_id/rev_text_id received from the previous query.

The second query works, the first - I'm not sure. The thing is after the whole thing is gone through (with a few dozen more queries) the revision row is there, with a fresh new value in it's rev_text_id column.

However, the text row isn't there. Funny thing - the text table auto increment advances, as the next action skips a value for it's old_id, the missing value matching what we have in revision table.

Is there a chance the first query makes the auto increment index advance though the row isn't actually inserted? What causes this?

ADDTITION

When I copied the INSERT INTO text query from the logs and ran it on the server - it executed fine (row was added to table)

EDIT

Full queries are:

INSERT INTO text (old_id,old_text,old_flags) VALUES (NULL,'{text input by user}','utf8');
INSERT INTO `revision` (rev_id,rev_page,rev_text_id,rev_comment,rev_minor_edit,rev_user,rev_user_text,rev_timestamp,rev_deleted,rev_len,rev_parent_id,rev_sha1) VALUES (NULL,'{pageId}','{textId}','{comment}','{isMinor}','{userId}','{userName}','{TS}','{isDeleted}','{length}','{parentRevision}','{HASH}')"

Also, if relevant, text table is running InnoDB and revision is MyISAM.

EDIT

More information from logs:

Bad request

BEGIN
INSERT  INTO `text`
INSERT  INTO `revision`
UPDATE  `page` SET page_latest
INSERT  INTO `recentchanges`
INSERT  INTO `cu_changes`
SELECT  wl_user  FROM `watchlist` 
SELECT  user_id  FROM `user` 
SELECT  user_id,user_name,user_real_name,user_password,user_newpassword,user_newpass_time,user_email,user_touched,use
SELECT  ug_group  FROM `user_groups` 
SELECT  up_property,up_value  FROM `user_properties` 
SELECT  user_id,user_name,user_real_name,user_password,user_newpassword,user_newpass_time,user_email,user_touched,use
SELECT  ug_group  FROM `user_groups` 
SELECT  up_property,up_value  FROM `user_properties` 
SELECT  lc_value  FROM `l10n_cache` 
SELECT  lc_value  FROM `l10n_cache`
...

A few more SELECTs. Pause of activity for 2 sec, then the log has commands from a new user, no more queries from this user (on same thread).

An edit by same user to a different page, which went well:

BEGIN
INSERT INTO `text`
INSERT INTO `revision`
UPDATE `page` SET page_latest
INSERT INTO `recentchanges`
INSERT INTO `cu_changes`
SELECT wl_user  FROM `watchlist` 
COMMIT
BEGIN
UPDATE `watchlist` SET wl_notificationtimestamp
COMMIT
BEGIN
SELECT user_id  FROM `user`
SELECT user_id,user_name,user_real_name,user_password,user_newpasswo
SELECT ug_group  FROM `user_groups` 
SELECT up_property,up_value  FROM `user_properties` 
SELECT *  FROM `user` 
SELECT up_property,up_value  FROM `user_properties` 
INSERT INTO `logging`
UPDATE `user` SET user_editcount=user_editcount+1
SELECT 1  FROM `user` 
UPDATE `user` SET user_touched = '20121227211743'
COMMIT

etc.

Solution

  • Is there a chance the first query makes the auto increment index advance though the row isn't actually inserted? What causes this?

    Yes, when you try to insert a row in a table with an auto_increment column, the insertion will fail, but the auto_increment value will be incremented by 1.

    If you decide to convert the tables to InnoDB, I suggest you to use transactions. This way if one the queries fails none of them will be inserted.