Alter table - 1205 lock wait timeout exceeded

Alter table - 1205 lock wait timeout exceeded

How can i alter my timeout setting
Hey there,
i have a rather long script that makes several connections to a MySQL database. Sometimes , if there is a problem with the script, or a bug in my code, or something else unexpected, the connections dont get closed. Is there a way that i can set MySQL to percieve when a thread has been idle for like 5 minutes and kill it for me? Like a timeout function.
i have googled and searched around here, but i am having trouble finding out just where to do this.
i have MySQL-Adminstrator and phpmyadmin that i can use if its any easier.
thanks




It does. The wait_timeout setting is set to 28,800 seconds and MySQL will kill inactive threads after this. You can set it while MySQL is running with "SET wait_timeout = $value;", or set it directly in your my.ini file.

--Simon

SET wait_timeout = 900;
ALTER TABLE `stock_db`.`stock_coredata_usmqtr` ADD INDEX `MGStockNumberIndustryID_InterimEndingDate` USING BTREE(`MGStockNumberIndustryID`, `InterimEndingDate`);

http://forums.devshed.com/mysql-help-4/how-can-i-alter-my-timeout-setting-313101.html

=====

Try bulk loading instead it is better
optimized for cluster
LOAD DATA INFILE ;

BR
-- Martin

======

got to the problem...


the problem was in the my.ini file (for windows only) that was to be
edited and not the config.ini. in this file we have to set the timeout
by setting innodb_lock_wait_timeout.

======
The InnoDB error codes page lists the following information:

1205 (ER_LOCK_WAIT_TIMEOUT)

Lock wait timeout expired. Transaction was rolled back.



So how can we prevent that from happening?

This happens because the value of innodb_lock_wait_timeout was not enough for InnoDB to acquire the lock. On my servers it was set to default: 50.

The manual has following information:

The timeout in seconds an InnoDB transaction may wait for a lock before being rolled back. InnoDB automatically detects transaction deadlocks in its own lock table and rolls back the transaction. Beginning with MySQL 4.0.20 and 4.1.2, InnoDB notices locks set using the LOCK TABLES statement. Before that, if you use the LOCK TABLES statement, or other transaction-safe storage engines than InnoDB in the same transaction, a deadlock may arise that InnoDB cannot notice. In cases like this, the timeout is useful to resolve the situation. The default is 50 seconds.