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.

I am attempting to create a new index on an existing table with 44,485 records in it.

I am using this query to create my new index

CREATE INDEX index_name ON table_name (field_name);

Whenever I run this I get this timeout error:

1205 - Lock wait timeout exceeded; try restarting transaction

This is only on my local dev machine so there arent any other processes taking up the DB.

Is there a preferred method of adding new indexes to existing tables?

Thanks

share|improve this question

1 Answer 1

up vote 2 down vote accepted

You can increase the lock timeout with an InnoDB:

Edit /etc/my.cnf or /etc/mysql/my.cnf (assuming your dev machine is Linux/Unix)

Uncomment (or add) the line:

innodb_lock_wait_timeout = 50

And change the setting to a larger number, say 300. It's in seconds.

Restart mysql and rerun your query.

Alternatively, you can dump the data, drop the table, recreate it with the index and bring the data back in. Or you could create a new table with the index and select the old table's data into the new table. In both these cases you can break the data import/copy into smaller pieces (read: transactions) which won't exceed the timeout.

share|improve this answer
    
Perfect, thanks for that. According to the innodb docs you can also change the timeout with the SET SESSION or SET GLOBAL commands, have to try that to save a server restart in future. –  Neil Aitken Dec 16 '09 at 9:55

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.