16 July 2008

Error 153 when creating savepoints

One of our developers ran into the strangest error a few days ago, which neither the MySQL manual nor Google searching could find any information on. When he called certain stored procedures in a certain order, he got "Error 153 returned from storage engine." It's been reduced to a very simple test case, and a bug report has been filed. Here's the test case:
DROP TABLE IF EXISTS foo;
CREATE TABLE `foo` (
`a` int(11) NOT NULL auto_increment,
PRIMARY KEY (`a`)
) ENGINE=InnoDB;

begin;
insert into foo values();
savepoint a1;
insert into foo values();
savepoint a2;
insert into foo values();
savepoint a1;
insert into foo values();
savepoint a2;
show warnings;
rollback;
The very last "savepoint" generates the warning "Got error 153 from storage engine". Any other MySQL'ers use savepoints and affected by this?

2 comments:

AT-HE said...

i readed somewhere that by skipping the error causing slave subprocess stop, can resume replication activity

show slave status\G
-- if any "slave running:no" slave thread has stopped
-- below shows query that has stopped slave

-- tell mysql to skip 1 (last, bugged) query
stop slave;
SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1;
start slave;

-- check slave status again

Devananda vdv said...

AT-HE,

Blindly skipping replication errors is a sure way to get the replication slave out of sync with the master. While this may get replication running again, the slave's data will probably become different from the master's data because the skipped statement did not run on the slave. Furthermore, depending on the particular event being skipped, you might not solve anything at all -- the very next statement may fail because it depended on the statement you skipped!

Moral of the story: don't use SQL_SLAVE_SKIP_COUNTER unless you really, really know what you're doing!