1

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.

  • Add a WHERE condition,if you sre trying to do it in segments. – Mihai Jul 25 '14 at 13:13
  • Not sure what you mean. It appears MySQL is doing the rollback on it's own once I killed the long running process. – nobilist Jul 25 '14 at 13:16
0

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 !!!

  • This won't overwrite Table1 correct? The problem is table1 over 10GB and growing. Writing to it is super slow with all the indexes. I setup Table2 to insert the new daily data. Table2 has no indexes but primary. This way both tables can be queried during day. At night I dump table2 into table1. Then truncate table1. Maybe there's a better way but that's the best way I know of. Since partitions do not work for inserts last I read. – nobilist Jul 25 '14 at 17:31
  • It will not overwrite table1 because I used the -t option (which means --no-create-info). It prevents the dropping and recreating of the table. You should test this on DEV or STAGING servers to make sure of this action. – RolandoMySQLDBA Jul 25 '14 at 18:33
1

There is no way to do what you are asking as this would fundamentally break the Atomicity part of ACID

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.