My environment is Windows 7 and I'm using XAMPP.
I was doing some performance testing on MySQL simulating adding 100 rows at a time. I was doing this using a simple PHP query with for loops.
However, MySQL seems to have locked up as I can't add anything to the table anymore.
I logged into MySQL to look at the process list as below:
mysql> SHOW PROCESSLIST \G;
*************************** 1. row ***************************
Id: 1
User: root
Host: localhost:49349
db: NULL
Command: Sleep
Time: 2
State:
Info: NULL
*************************** 2. row ***************************
Id: 17
User: root
Host: localhost:49759
db: NULL
Command: Query
Time: 0
State: NULL
Info: SHOW PROCESSLIST
2 rows in set (0.00 sec)
I tried two things:
A. KILL QUERY 17 to which I got the following result:
mysql> KILL QUERY 17; ERROR 1317 (70100): Query execution was interrupted
B. Then I used the XAMPP controller to stop mysql and then start it again, but that didn't work as the table just seems to be locked permanently.
I also can't add data to the other INNODB tables in the database. MyISAM tables seems to be ok.
Is there a way I can force INNODB to just restart/reboot or something i.e. all queries and connections?
Thanks in advance.
EDIT
It turns out this was a problem that was caused originally by the way I had PHP PDO for MySQL executing queries. I had set PDO::ATTR_AUTOCOMMIT = false
when I created the MYSQL connection (for some reasons I can't change), so I had to specifically run $db_connection_handle->query('COMMIT;')
to specifically commit the transaction. So it appeared like the tables were locked.
Best Answer
show innodb status
looking for active transactions. Example: