'Rename MySQL table name safely in Flyway script

I'm going to rename a MySQL 8.0 table via Flyway SQL script:

RENAME TABLE aaa TO bbb;

and it's supposed to be run on env many times - but such the script is not idempotent, trying to run it second time I catch an error:

SQL State  : 42S01
Error Code : 1050
Message    : Table 'bbb' already exists
Location   : db/migration/V7__blabla.sql (/usr/local/...)
Line       : 1
Statement  : RENAME TABLE aaa TO bbb

Could someone suggest please how to handle it?

Many thanks in advance!

mysqlsqlflyway


Solution 1:[1]

IF EXISTS

Use the IF EXISTS clause. The command ceases, with no effect, if no such table is found.

ALTER TABLE IF EXISTS name
RENAME TO new_name 
;

This works in Postgres. For MySQL, see this Question.

Solution 2:[2]

you should think about multiple cases

  1. Renaming a table referenced by a view
  2. Renaming a table referenced by a stored procedure
  3. Renaming a table that has foreign keys referenced to

this link will help users safely rename your table. https://www.mysqltutorial.org/mysql-rename-table/

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1
Solution 2 Mohsen Bahaloo

Related Questions