Table myschema_staging.tmp_customer to myschema_staging. Table myschema_staging.customer to myschema_live.customer, To rename table myschema_live.customer to myschema_staging.tmp_customer, Rename table myschema_staging.tmp_customer to myschema_staging.customer Rename table myschema_staging.customer to myschema_live.customer Rename table myschema_live.customer to myschema_staging.tmp_customer Not directly related, but we also moved our rename statements to be a single transaction so that all rename statements succeed or all fail as opposed to our original process where some might complete and others were pending. Then put the new name into the text box labelled. Thus, if our rename statements, which require a metadata lock, wait more than 60 seconds then the rename statement will fail. To rename a MySQL table with phpMyAdmin, select the database then table, and click the Operations tab. This variable affects the time any query will wait to acquire a metadata lock on a table, after which it will timeout. We changed the lock_wait_timeout value from its default of 1 year to 60 seconds. We then check for time being greater than 60 seconds and cancel the rename if this condition is true. Select id, user, db, state, info, time from information_schema.processlist where id = (select id from information_schema.processlist where command='Query' order by time desc limit 1) If any long running query was identified, then we error out the renames and force manual recovery. We implemented a query against the information_schema.processlist table to identify long running queries prior to running our rename statements.We ended up taking multiple actions to address our issues. Is it possible to programmatically look for long running queries and not run the rename script if found?.Is there a better way to acquire locks on the tables for this renaming purpose?.Is it possible to have the sql command (executed via a shell script via the mysql command) timeout if it waits more than a certain amount of time?.How can we avoid this scenario from happening?. Then the entire database is then waiting on the long running select to finish. Meanwhile the rest of the rename transaction did acquire locks on the other tables preventing other queries from running. I’m guessing the long running select query prevented one of the rename commands from acquiring a lock on the table to carry out the rename. There are some 30 tables involved here and the sql commands we run attempt to rename all 30 tables in one transaction. However, we recently encountered a fatal flaw in this process whereby a long running select query prevented the rename from happening which then took down our database. Once the load of this data is complete, we make it live by the following process: rename table myschema_live.customer to myschema_staging.tmp_customer We have a daily process by which we load data on a daily basis into our production MySQL database to our ‘staging’ tables (a set of MySQL tables residing on a staging schema).
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |