Difficulty: 6/10DDL Locks, Zero-Downtime Migrations, Schema RefactoringMySQL provides two different ways to rename an MySQL table. We can use either the RENAME TABLE or ALTER TABLE statement.Query:CopyRenaming multiple tablesCopyScenario Questions0-2 years experience1We have a table called 'usr' and we want to rename it to 'users' to match our style guide. How would you write the SQL to do this safely, and what happens to any active SELECT queries running on 'usr' at the exact moment you execute the rename?2Imagine you need to rename a table in production. You run the rename command, but you realize there's a background cron job running every minute that inserts data into the old table name. What error will that cron job throw, and how could we have structured the deployment to prevent that failure?2-5 years experience1We need to rename our 'orders' table to 'customer_orders' as part of a refactor. However, this table is referenced by several foreign keys in other tables, and there are a couple of database views pointing to it. If you just run a standard 'RENAME TABLE', what breaks, and how do you coordinate this change to avoid application downtime?Follow-up QuestionsHow does MySQL handle metadata locks (MDL) during a rename if there is a long-running SELECT query active?What is the difference in locking behavior and atomicity between 'ALTER TABLE RENAME' and 'RENAME TABLE'?How do triggers, foreign keys, and stored procedures behave when their underlying table is renamed?Suggest Improvement
