1

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

Neil Aitken
  • 93
  • 1
  • 8

1 Answers1

3

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.

Swoogan
  • 2,007
  • 1
  • 13
  • 21
  • 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 09:55