Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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.

share|improve this question
    
What does the error log say? –  quanta Oct 19 '11 at 13:27
    
The last entry in the log file: 111019 14:39:41 InnoDB: Error: page 344 log sequence number 12784479 InnoDB: is in the future! Current system log sequence number 6540600. InnoDB: Your database may be corrupt or you may have copied the InnoDB InnoDB: tablespace but not the InnoDB log files. See InnoDB: http://dev.mysql.com/doc/refman/5.1/en/forcing-recovery.html InnoDB: for more information. –  Obi Hill Oct 19 '11 at 13:44

1 Answer 1

  • 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

share|improve this answer
    
Thanks. The log appears to show some corruption. The data seems to be intact. How do I recover database functionality as I can't do INSERTs or UPDATEs, only SELECTs (obviously on data that is not up-to-date). –  Obi Hill Oct 19 '11 at 13:46
    

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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