Stack Exchange Network
Stack Exchange network consists of 183 Q&A communities including
Stack Overflow
, the largest, most trusted online community for developers to learn, share their knowledge, and build their careers.
Visit Stack Exchange
Server Fault is a question and answer site for system and network administrators. It only takes a minute to sign up.
Sign up to join this community
Teams
Q&A for work
Connect and share knowledge within a single location that is structured and easy to search.
Learn more about Teams
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.
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.
–
Check your logs. You may find that the ibdata file is full and can't expand or isn't set to auto-expand.
Check show innodb status looking for active transactions. Example:
---TRANSACTION 0 16662058, ACTIVE 20 sec, process no 22014, OS thread id 140277380307216
2 lock struct(s), heap size 368, 30 row lock(s), undo log entries 30
MySQL thread id 3662, query id 46913632 localhost user
Trx read view will not see trx with id >= 0 16662059, sees < 0 16662059
–
