CODEWITHSUNDEEP
mysqlrollback
N..
N..

Reputation: 129

How to stop ROLLBACK in progress?

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. Appears the rollback happened automatically once I killed the long running process.

This is a mySQL database.

Upvotes: 3

Views: 20265

Answers (3)

JVD
JVD

Reputation: 655

Note, stopping mysql will initiate processing of the rollback log for any uncommitted transactions ; the timeout of the 'mysqladmin shutdown' command may be much less than the time needed to rollback, so you may get into the state where the server cannot be started or stopped by the init script / systemd service or mysqladmin command (like I did) .

DO NOT KILL THE SERVER WITH '-9'/'-KILL' (SIGKILL) UNLESS DATA LOSS IS OK (you are going to drop & recreate the database) - be patient ! :-)

On Linux:

[root@host]# kill -TERM `pidof mysqld`

will initiate a shutdown of the server without needing to connect to its database connection socket.

Here is a script that will then show progress of rollback log processing :

[root@host]# log_len=$(wc -l /var/log/mysql/error.log);
[root@host]# while sleep 10; do 
       tail -n +$log_len /var/log/mysql/error.log | 
       egrep '^ROLLING BACK.*undo log entries'| tail -n 1; 
       log_len=$(wc -l /var/log/mysql/error.log); 
done
ROLLING BACK 875593 lock struct(s), heap size 100792760, 3022415 row lock(s), undo log entries 841038
ROLLING BACK 875575 lock struct(s), heap size 100792760, 3020756 row lock(s), undo log entries 839380 ...

So the rate for my DB+machine is around 100 log entries per second ... very slow , but better than corrupting the DB.

Upvotes: 0

Pirooz Jenabi
Pirooz Jenabi

Reputation: 682

You must first stop mysql (before that Full backup with mysqldump :) )

systemctl stop mysql

if you can't stop, close all MySQL process in htop with filter with f4 and try to stop MySQL

After stop set below line in MySQL config file

innodb_force_recovery = 1

And finally start MySQL

Upvotes: 0

Jaydee
Jaydee

Reputation: 4158

You could try this.

https://dba.stackexchange.com/questions/5654/internal-reason-for-killing-process-taking-up-long-time-in-mysql

You can kill the mysqld process and set innodb_force_recovery to 3 to bring the database up without the rollback, then DROP the table that is causing the runaway rollback.

Upvotes: 6

Related Questions