mysql unlock transaction running for many days

Question

in "show engine innodb status" i get the following row:

MySQL thread id 1, OS thread handle 0x2b0a8fef1700, query id 860436 localhost 127.0.0.1 rdsadmin cleaning up
---TRANSACTION 334275772, ACTIVE 1403158714 sec recovered trx
ROLLING BACK 2 lock struct(s), heap size 376, 1 row lock(s), undo log entries 399300

it locks a row i can't modify or delete and don't know what to do. since i'm using aws rds i can't even restart the server

what can be done?

Answer 1

Probably a bit late; but here was my solution. Had to identify the actual table affected; this was done by having mysql operational, and making calls from our application that uses MySQL, then watching 'show full processlist' to see where queries were queuing up - our ncs.keywords table.

Solution was to drop that offending table, recreate the table structure, then, repopulate the table.

OS: Centos 7 .. MySQL version: 5.7

Symptom : CPU running at 100% for mysqld upon MySQL start; nothing in processlist. Stop MySQL

 #] systemctl stop mysqld

Wouldn't 'stop' so find the process and kill manually

 #] kill -9 12345

Edit the MySQL config /etc/my.cnf, add line innodb_force_recovery = 3 as per documentation Start MySQL [starts ok], CPU now at 0%. No rollback occurring.

 #] systemctl start mysqld

Drop affected table ncs.keywords [we did not have to try to save our data - we repopulate it via our code]

 #] mysql -u root -p
   > show create table ncs.keywords  [keep this syntax]
   > drop table ncs.keywords
   > exit

Check mysql ; restart again

  #] systemctl restart mysqld
  #] mysql -u root -p
     > [paste the 'show create' syntax to create the fresh table again]
  #] exit

Remove innodb_force_recovery = 3 from /etc/my.cnf, and restart MySQL again. CPU now at 0%, whilst not in recovery mode.

We were OK because a recovery of the data wasn't required. But you may need to dump data out of the table first.

Hope that helps [next time!].