4 ms·

The above issues are MySQL specific and have nothing to do with RDS. ALTER TABLE commands lock tables. There are various strategies to work around this. http://

The above issues are MySQL specific and have nothing to do with RDS. ALTER TABLE commands lock tables. There are various strategies to work around this. http://dev.mysql.com/doc/refman/5.7/en/alter-table.html http://dev.mysql.com/doc/refman/5.7/en/alter-table.html

thwarted 12y ago
Renaming a column in 5.6 as described works with concurrent read and write access: https://dev.mysql.com/doc/refman/5.6/en/innodb-create-index-overview.html https://dev.mysql.com/doc/refman/5.6/en/innodb-create-index-... The only thing about implicit full-table locking in the alter-table docs you linked says: The exception referred to earlier is that ALTER TABLE blocks reads (not just writes) at the point where it is ready to install a new version of the table .frm file, discard the old file, and clear outdated table structures from the table and table definition caches. At this point, it must acquire an exclusive lock. To do so, it waits for current readers to finish, and blocks new reads (and writes). There are no strategies to work around this using just the database. Even if you were to manually create a new table with the new format, migrate all the data manually, and then manually rename, the final rename operation would require a short-duration lock to perform its work. You could, of course, modify your application code to work with two tables during a manual migration.
jlhonora 12y ago
> ALTER TABLE commands lock tables Sure, but the migration was running for 15 minutes after we decided to reboot it. It was most probably trapped in a deadlock. Keep in mind it was just a column rename, no indexes involved. Should be a simple operation: > Alterations that modify only table metadata and not table data are immediate because the server only needs to alter the table .frm file, not touch table contents. The following changes are fast alterations that can be made this way: > - Renaming a column, except for the InnoDB storage engine before MySQL 5.6.6.

thwarted 12y ago

Renaming a column in 5.6 as described works with concurrent read and write access: https://dev.mysql.com/doc/refman/5.6/en/innodb-create-index-overview.html https://dev.mysql.com/doc/refman/5.6/en/innodb-create-index-... The only thing about implicit full-table locking in the alter-table docs you linked says: The exception referred to earlier is that ALTER TABLE blocks reads (not just writes) at the point where it is ready to install a new version of the table .frm file, discard the old file, and clear outdated table structures from the table and table definition caches. At this point, it must acquire an exclusive lock. To do so, it waits for current readers to finish, and blocks new reads (and writes). There are no strategies to work around this using just the database. Even if you were to manually create a new table with the new format, migrate all the data manually, and then manually rename, the final rename operation would require a short-duration lock to perform its work. You could, of course, modify your application code to work with two tables during a manual migration.

jlhonora 12y ago

> ALTER TABLE commands lock tables Sure, but the migration was running for 15 minutes after we decided to reboot it. It was most probably trapped in a deadlock. Keep in mind it was just a column rename, no indexes involved. Should be a simple operation: > Alterations that modify only table metadata and not table data are immediate because the server only needs to alter the table .frm file, not touch table contents. The following changes are fast alterations that can be made this way: > - Renaming a column, except for the InnoDB storage engine before MySQL 5.6.6.