0

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.

2
  • What does the error log say?
    – quanta
    Oct 19, 2011 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, 2011 at 13:44

1 Answer 1

0
  • 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

2

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .