MySQL – How to Stop a ROLLBACK in Progress

MySQLrollback

How do I stop a rollback in progress. I killed a very long running INSERT INTO table1 SELECT * FROM table2 statement. I want to commit what's already there but I can't since it's rolling back.

This is a mySQL database.

Best Answer

Unfortunately, the correct answer is YOU CANNOT STOP A ROLLBACK. You have to let InnoDB clean up after itself.

If table1 and table2 are in the same database mydb and you want to load table1 from table2 in segments, use mysqldump:

MYSQL_USER=root
MYSQL_PASS=rootpassword
DB=mydb
MYSQL_CONN="-u${MYSQL_USER} -p${MYSQL_PASS}"
mysqldump ${MYSQL_CONN} -t ${DB} table2 | sed 's/table2/table1/g' > TableData.sql
mysql ${MYSQL_CONN} -D${DB} < TableData.sql
rm -f TableData.sql

or

MYSQL_USER=root
MYSQL_PASS=rootpassword
DB=mydb
MYSQL_CONN="-u${MYSQL_USER} -p${MYSQL_PASS}"
mysqldump ${MYSQL_CONN} -t ${DB} table2|sed 's/table2/table1/g' |mysql ${MYSQL_CONN} -D${DB}

That way, when you interrupt the mysqldump, everything prior to that interruption is committed.

Give it a Try !!!

Related Question