Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Multiple IF EXISTS in one statement. EG: modifying multiple columns. #2828

Closed
davidjeddy opened this issue May 22, 2020 · 1 comment
Closed
Assignees
Milestone

Comments

@davidjeddy
Copy link

@davidjeddy davidjeddy commented May 22, 2020

Which version and edition of Flyway are you using?
$ flyway -v
Flyway Community Edition 6.4.2 by Redgate
If this is not the latest version, can you reproduce the issue with the latest one as well?

(Many bugs are fixed in newer releases and upgrading will often resolve the issue)

Which client are you using? (Command-line, Java API, Maven plugin, Gradle plugin): CLI
Which database are you using (type & version)?
$ mysql --version
mysql  Ver 15.1 Distrib 10.3.23-MariaDB, for Linux (x86_64) using readline 5.1
Which operating system are you using?
CentOS 7
What did you do?

(Please include the content causing the issue, any relevant configuration settings, the SQL statement that failed (if relevant) and the command you ran.)

migration 1: created schema and select it...

CREATE SCHEMA IF NOT EXISTS `example`;

USE `example`;

Apply a migration wherein multiple columns on a table are modified or created in one command leveraging IF [NOT] EXISTS to ensure a clean application of the migration.

SET NAMES 'utf8';

CREATE TABLE IF NOT EXISTS example.example_table_5 (
  `id` INT(11) DEFAULT NULL AUTO_INCREMENT PRIMARY KEY
);

-- This throws a warning: `WARNING: DB: Unknown column 'col_1' in 'example_table_5' (SQL State: 42S22 - Error Code: 1054)` but does exit 0.
ALTER TABLE example.example_table_5 
  MODIFY COLUMN IF EXISTS `col_1` CHAR(1) DEFAULT NULL,
  ADD COLUMN `col_2` TINYINT DEFAULT NULL,
  ADD COLUMN `col_3` TINYINT DEFAULT NULL;

-- This does not work
-- `ERROR: Unable to parse statement in ../sql/example_release_version/example_schema_name/V202005221115__example_migratino_5.sql at line 13 col 1. See https://flywaydb.org/documentation/knownparserlimitations for more information: Incomplete statement at line 13 col 1: ALTER TABLE IF EXISTS example.example_table_5`
-- ALTER TABLE IF EXISTS example.example_table_5 
--   MODIFY COLUMN IF EXISTS `col_1` CHAR(1) DEFAULT NULL,
--   ADD COLUMN `col_2` TINYINT DEFAULT NULL,
--   ADD COLUMN `col_3` TINYINT DEFAULT NULL;

-- This does not work
-- `ERROR: Unable to parse statement in ../sql/example_release_version/example_schema_name/V202005221115__example_migratino_5.sql at line 13 col 1. See https://flywaydb.org/documentation/knownparserlimitations for more information: Incomplete statement at line 13 col 1: ALTER TABLE IF EXISTS example.example_table_5`
-- ALTER TABLE example.example_table_5 
--   MODIFY COLUMN IF EXISTS `col_1` CHAR(1) DEFAULT NULL,
--   ADD COLUMN IF NOT EXISTS `col_2` TINYINT DEFAULT NULL,
--   ADD COLUMN `col_3` TINYINT DEFAULT NULL;

-- This does not work
-- ERROR: Unable to parse statement in ../sql/example_release_version/example_schema_name/V202005221115__example_migratino_5.sql at line 27 col 1. See https://flywaydb.org/documentation/knownparserlimitations for more information: Incomplete statement at line 27...
-- ALTER TABLE example.example_table_5 
--   MODIFY COLUMN `col_1` CHAR(1) DEFAULT NULL,
--   ADD COLUMN IF NOT EXISTS `col_2` TINYINT DEFAULT NULL,
--   ADD COLUMN IF NOT EXISTS`col_3` TINYINT DEFAULT NULL;

-- This does not work
-- Error `ERROR: Unable to parse statement in ../sql/example_release_version/example_schema_name/V202005221115__example_migratino_5.sql at line 13 col 1. See https://flywaydb.org/documentation/knownparserlimitations for more information: Incomplete statement at line 13 col 1: ALTER TABLE IF EXISTS example.example_table_5`
-- ALTER TABLE IF EXISTS example.example_table_5 
--   MODIFY COLUMN `col_1` CHAR(1) DEFAULT NULL,
--   ADD COLUMN `col_2` TINYINT DEFAULT NULL,
--   ADD COLUMN `col_3` TINYINT DEFAULT NULL;
What did you expect to see?

Successful execution of the SQL logic. MariaDB support [IF [NOT] EXISTS] on most of the commands. Yet Flyway fails if more than one is encountered in a statement.

What did you see instead?

Failed migrations. :(

@Lyeeedar Lyeeedar self-assigned this May 26, 2020
@Lyeeedar Lyeeedar added this to the Flyway 6.4.3 milestone May 26, 2020
Lyeeedar added a commit that referenced this issue May 26, 2020
Fix #2828 MySQL handle multiple if exists in one statement.
@davidjeddy
Copy link
Author

@davidjeddy davidjeddy commented May 29, 2020

Did I mention the Flyway team is awesome? @MikielAgutu @Lyeeedar Thank you very much.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Development

No branches or pull requests

3 participants