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

MariaDB: Can't create database 'null' error if no database was specified in URL #2206

Closed
htto opened this issue Nov 13, 2018 · 6 comments
Closed

Comments

@htto
Copy link

@htto htto commented Nov 13, 2018

Which version and edition of Flyway are you using?

Flyway Community Edition 5.2.1 by Boxfuse (also happens with 5.1.4)

Which client are you using? (Command-line, Java API, Maven plugin, Gradle plugin)

CLI

Which database are you using (type & version)?

mysql Ver 15.1 Distrib 10.1.34-MariaDB, for debian-linux-gnu (x86_64) using readline 5.2

Which operating system are you using?

ubuntu 18.10 (also happens on arch linux)

What did you do?

Use flyways multi-schema support

What did you expect to see?

Having flyway create the schema table to hold flyway_schema_history

What did you see instead?

Working call that created the schema for flyway_schema_history

time flyway -schemas=history,other migrate
Flyway Community Edition 5.2.1 by Boxfuse
Database: jdbc:mariadb://127.0.0.1:3306 (MySQL 10.1)
Creating schema `history` ...
Creating schema `other` ...
Creating Schema History table: `history`.`flyway_schema_history`
Current version of schema `history`: null
Schema `history` is up to date. No migration necessary.

real	0m0,494s
user	0m0,599s
sys	0m0,180s

Failed call that doesn't create the schema and directly fails at the flyway_schema_history table

$> time flyway -schemas=hist,other migrate
Flyway Community Edition 5.2.1 by Boxfuse
Database: jdbc:mariadb://127.0.0.1:3306 (MySQL 10.1)
Creating Schema History table: `hist`.`flyway_schema_history`
ERROR: 
Migration createMetaDataTable.sql failed
----------------------------------------
SQL State  : 42000
Error Code : 1049
Message    : (conn=10221) Unknown database 'hist'
Location   : org/flywaydb/core/internal/database/mysql/createMetaDataTable.sql (/webdev/home/user/t/file:/webdev/tools/flyway/flyway-5.2.1/lib/community/flyway-core-5.2.1.jar!/org/flywaydb/core/internal/database/mysql/createMetaDataTable.sql)
Line       : 17
Statement  : CREATE TABLE `hist`.`flyway_schema_history` (
    `installed_rank` INT NOT NULL,
    `version` VARCHAR(50),
    `description` VARCHAR(200) NOT NULL,
    `type` VARCHAR(20) NOT NULL,
    `script` VARCHAR(1000) NOT NULL,
    `checksum` INT,
    `installed_by` VARCHAR(100) NOT NULL,
    `installed_on` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `execution_time` INT NOT NULL,
    `success` BOOL NOT NULL,
    -- Add the primary key as part of the CREATE TABLE statement in case `innodb_force_primary_key` is enabled
    CONSTRAINT `flyway_schema_history_pk`PRIMARY KEY (`installed_rank`)
) ENGINE=InnoDB


real	0m9,650s
user	0m1,086s
sys	0m0,275s

Please note the timing difference and the missing Creating schema [..] ...

@htto htto changed the title Flyway sometimes fails to create schema for metadata table Flyway sometimes fails to create schema for metadata table with multiple schemas Nov 13, 2018
@axelfontaine
Copy link
Contributor

@axelfontaine axelfontaine commented Nov 13, 2018

What does the debug output (-X) look like?

@htto
Copy link
Author

@htto htto commented Nov 13, 2018

EDIT Just re-tested this with 5.2.1, same issue there /EDIT

Ok

  >> flyway -X -url=jdbc:mariadb://localhost -user=root -password='' -schemas=history,other migrate
Flyway Community Edition 5.1.4 by Boxfuse

DEBUG: Java 1.8.0_192 (Oracle Corporation)
DEBUG: Linux 4.18.16-arch1-1-ARCH amd64

DEBUG: Loading config file: /opt/flyway/flyway-5.1.4/conf/flyway.conf
DEBUG: Unable to load config file: /home/user/flyway.conf
DEBUG: Unable to load config file: /tmp/flyway.conf
DEBUG: Using configuration:
DEBUG: flyway.jarDirs -> /opt/flyway/flyway-5.1.4/jars
DEBUG: flyway.password -> 
DEBUG: flyway.url -> jdbc:mariadb://localhost
DEBUG: flyway.schemas -> history,other
DEBUG: flyway.user -> root
DEBUG: flyway.locations -> filesystem:/opt/flyway/flyway-5.1.4/sql
DEBUG: Adding location to classpath: /opt/flyway/flyway-5.1.4/drivers/jtds-1.3.1.jar
DEBUG: Adding location to classpath: /opt/flyway/flyway-5.1.4/drivers/mssql-jdbc-6.4.0.jre7.jar
DEBUG: Adding location to classpath: /opt/flyway/flyway-5.1.4/drivers/derby-10.14.2.0.jar
DEBUG: Adding location to classpath: /opt/flyway/flyway-5.1.4/drivers/mariadb-java-client-2.2.4.jar
DEBUG: Adding location to classpath: /opt/flyway/flyway-5.1.4/drivers/sqlite-jdbc-3.20.1.jar
DEBUG: Adding location to classpath: /opt/flyway/flyway-5.1.4/drivers/hsqldb-2.4.0.jar
DEBUG: Adding location to classpath: /opt/flyway/flyway-5.1.4/drivers/postgresql-42.2.2.jre6.jar
DEBUG: Adding location to classpath: /opt/flyway/flyway-5.1.4/drivers/h2-1.4.197.jar
DEBUG: Adding location to classpath: /opt/flyway/flyway-5.1.4/drivers/derbyclient-10.14.2.0.jar
Database: jdbc:mariadb://localhost (MySQL 10.1)
DEBUG: Driver  : MariaDB connector/J 2.2.4
DEBUG: DDL Transactions Supported: false
DEBUG: Schemas: history,other
DEBUG: Spring Jdbc available: false
DEBUG: Scanning for SQL callbacks ...
DEBUG: Scanning for filesystem resources at '/opt/flyway/flyway-5.1.4/sql' (Prefix: '', Suffixes: '.sql')
DEBUG: Scanning for resources in path: /opt/flyway/flyway-5.1.4/sql (/opt/flyway/flyway-5.1.4/sql)
DEBUG: Filtering out resource: /opt/flyway/flyway-5.1.4/sql/put-your-sql-migrations-here.txt (filename: put-your-sql-migrations-here.txt)
DEBUG: Scanning for filesystem resources at '/opt/flyway/flyway-5.1.4/sql' (Prefix: 'V', Suffixes: '.sql')
DEBUG: Scanning for resources in path: /opt/flyway/flyway-5.1.4/sql (/opt/flyway/flyway-5.1.4/sql)
DEBUG: Filtering out resource: /opt/flyway/flyway-5.1.4/sql/put-your-sql-migrations-here.txt (filename: put-your-sql-migrations-here.txt)
DEBUG: Scanning for filesystem resources at '/opt/flyway/flyway-5.1.4/sql' (Prefix: 'R', Suffixes: '.sql')
DEBUG: Scanning for resources in path: /opt/flyway/flyway-5.1.4/sql (/opt/flyway/flyway-5.1.4/sql)
DEBUG: Filtering out resource: /opt/flyway/flyway-5.1.4/sql/put-your-sql-migrations-here.txt (filename: put-your-sql-migrations-here.txt)
Creating schema `history` ...
Creating schema `other` ...
Creating Schema History table: `history`.`flyway_schema_history`
DEBUG: Parsing createMetaDataTable.sql ...
DEBUG: Found statement at line 17: CREATE TABLE `history`.`flyway_schema_history` (
    `installed_rank` INT NOT NULL,
    `version` VARCHAR(50),
    `description` VARCHAR(200) NOT NULL,
    `type` VARCHAR(20) NOT NULL,
    `script` VARCHAR(1000) NOT NULL,
    `checksum` INT,
    `installed_by` VARCHAR(100) NOT NULL,
    `installed_on` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `execution_time` INT NOT NULL,
    `success` BOOL NOT NULL,
    -- Add the primary key as part of the CREATE TABLE statement in case `innodb_force_primary_key` is enabled
    CONSTRAINT `flyway_schema_history_pk`PRIMARY KEY (`installed_rank`)
) ENGINE=InnoDB
DEBUG: Found statement at line 32: CREATE INDEX `flyway_schema_history_s_idx` ON `history`.`flyway_schema_history` (`success`)
DEBUG: Executing SQL: CREATE TABLE `history`.`flyway_schema_history` (
    `installed_rank` INT NOT NULL,
    `version` VARCHAR(50),
    `description` VARCHAR(200) NOT NULL,
    `type` VARCHAR(20) NOT NULL,
    `script` VARCHAR(1000) NOT NULL,
    `checksum` INT,
    `installed_by` VARCHAR(100) NOT NULL,
    `installed_on` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `execution_time` INT NOT NULL,
    `success` BOOL NOT NULL,
    -- Add the primary key as part of the CREATE TABLE statement in case `innodb_force_primary_key` is enabled
    CONSTRAINT `flyway_schema_history_pk`PRIMARY KEY (`installed_rank`)
) ENGINE=InnoDB
DEBUG: Update Count: 0
DEBUG: Executing SQL: CREATE INDEX `flyway_schema_history_s_idx` ON `history`.`flyway_schema_history` (`success`)
DEBUG: Update Count: 0
DEBUG: Created Schema History table: `history`.`flyway_schema_history`
DEBUG: Locking table `history`.`flyway_schema_history`...
DEBUG: Lock acquired for table `history`.`flyway_schema_history`
DEBUG: Schema History table `history`.`flyway_schema_history` successfully updated to reflect changes
Current version of schema `history`: null
Schema `history` is up to date. No migration necessary.

Fail


  >> flyway -X -url=jdbc:mariadb://localhost -user=root -password='' -schemas=hist,other migrate
Flyway Community Edition 5.1.4 by Boxfuse

DEBUG: Java 1.8.0_192 (Oracle Corporation)
DEBUG: Linux 4.18.16-arch1-1-ARCH amd64

DEBUG: Loading config file: /opt/flyway/flyway-5.1.4/conf/flyway.conf
DEBUG: Unable to load config file: /home/user/flyway.conf
DEBUG: Unable to load config file: /tmp/flyway.conf
DEBUG: Using configuration:
DEBUG: flyway.jarDirs -> /opt/flyway/flyway-5.1.4/jars
DEBUG: flyway.password -> 
DEBUG: flyway.url -> jdbc:mariadb://localhost
DEBUG: flyway.schemas -> hist,other
DEBUG: flyway.user -> root
DEBUG: flyway.locations -> filesystem:/opt/flyway/flyway-5.1.4/sql
DEBUG: Adding location to classpath: /opt/flyway/flyway-5.1.4/drivers/jtds-1.3.1.jar
DEBUG: Adding location to classpath: /opt/flyway/flyway-5.1.4/drivers/mssql-jdbc-6.4.0.jre7.jar
DEBUG: Adding location to classpath: /opt/flyway/flyway-5.1.4/drivers/derby-10.14.2.0.jar
DEBUG: Adding location to classpath: /opt/flyway/flyway-5.1.4/drivers/mariadb-java-client-2.2.4.jar
DEBUG: Adding location to classpath: /opt/flyway/flyway-5.1.4/drivers/sqlite-jdbc-3.20.1.jar
DEBUG: Adding location to classpath: /opt/flyway/flyway-5.1.4/drivers/hsqldb-2.4.0.jar
DEBUG: Adding location to classpath: /opt/flyway/flyway-5.1.4/drivers/postgresql-42.2.2.jre6.jar
DEBUG: Adding location to classpath: /opt/flyway/flyway-5.1.4/drivers/h2-1.4.197.jar
DEBUG: Adding location to classpath: /opt/flyway/flyway-5.1.4/drivers/derbyclient-10.14.2.0.jar
Database: jdbc:mariadb://localhost (MySQL 10.1)
DEBUG: Driver  : MariaDB connector/J 2.2.4
DEBUG: DDL Transactions Supported: false
DEBUG: Schemas: hist,other
DEBUG: Spring Jdbc available: false
DEBUG: Scanning for SQL callbacks ...
DEBUG: Scanning for filesystem resources at '/opt/flyway/flyway-5.1.4/sql' (Prefix: '', Suffixes: '.sql')
DEBUG: Scanning for resources in path: /opt/flyway/flyway-5.1.4/sql (/opt/flyway/flyway-5.1.4/sql)
DEBUG: Filtering out resource: /opt/flyway/flyway-5.1.4/sql/put-your-sql-migrations-here.txt (filename: put-your-sql-migrations-here.txt)
DEBUG: Scanning for filesystem resources at '/opt/flyway/flyway-5.1.4/sql' (Prefix: 'V', Suffixes: '.sql')
DEBUG: Scanning for resources in path: /opt/flyway/flyway-5.1.4/sql (/opt/flyway/flyway-5.1.4/sql)
DEBUG: Filtering out resource: /opt/flyway/flyway-5.1.4/sql/put-your-sql-migrations-here.txt (filename: put-your-sql-migrations-here.txt)
DEBUG: Scanning for filesystem resources at '/opt/flyway/flyway-5.1.4/sql' (Prefix: 'R', Suffixes: '.sql')
DEBUG: Scanning for resources in path: /opt/flyway/flyway-5.1.4/sql (/opt/flyway/flyway-5.1.4/sql)
DEBUG: Filtering out resource: /opt/flyway/flyway-5.1.4/sql/put-your-sql-migrations-here.txt (filename: put-your-sql-migrations-here.txt)
DEBUG: Schema `other` already exists. Skipping schema creation.
Creating Schema History table: `hist`.`flyway_schema_history`
DEBUG: Parsing createMetaDataTable.sql ...
DEBUG: Found statement at line 17: CREATE TABLE `hist`.`flyway_schema_history` (
    `installed_rank` INT NOT NULL,
    `version` VARCHAR(50),
    `description` VARCHAR(200) NOT NULL,
    `type` VARCHAR(20) NOT NULL,
    `script` VARCHAR(1000) NOT NULL,
    `checksum` INT,
    `installed_by` VARCHAR(100) NOT NULL,
    `installed_on` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `execution_time` INT NOT NULL,
    `success` BOOL NOT NULL,
    -- Add the primary key as part of the CREATE TABLE statement in case `innodb_force_primary_key` is enabled
    CONSTRAINT `flyway_schema_history_pk`PRIMARY KEY (`installed_rank`)
) ENGINE=InnoDB
DEBUG: Found statement at line 32: CREATE INDEX `flyway_schema_history_s_idx` ON `hist`.`flyway_schema_history` (`success`)
DEBUG: Executing SQL: CREATE TABLE `hist`.`flyway_schema_history` (
    `installed_rank` INT NOT NULL,
    `version` VARCHAR(50),
    `description` VARCHAR(200) NOT NULL,
    `type` VARCHAR(20) NOT NULL,
    `script` VARCHAR(1000) NOT NULL,
    `checksum` INT,
    `installed_by` VARCHAR(100) NOT NULL,
    `installed_on` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `execution_time` INT NOT NULL,
    `success` BOOL NOT NULL,
    -- Add the primary key as part of the CREATE TABLE statement in case `innodb_force_primary_key` is enabled
    CONSTRAINT `flyway_schema_history_pk`PRIMARY KEY (`installed_rank`)
) ENGINE=InnoDB
DEBUG: Rolling back transaction...
DEBUG: Transaction rolled back
DEBUG: Schema History table creation failed. Retrying in 1 sec ...
DEBUG: Parsing createMetaDataTable.sql ...
DEBUG: Found statement at line 17: CREATE TABLE `hist`.`flyway_schema_history` (
    `installed_rank` INT NOT NULL,
    `version` VARCHAR(50),
    `description` VARCHAR(200) NOT NULL,
    `type` VARCHAR(20) NOT NULL,
    `script` VARCHAR(1000) NOT NULL,
    `checksum` INT,
    `installed_by` VARCHAR(100) NOT NULL,
    `installed_on` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `execution_time` INT NOT NULL,
    `success` BOOL NOT NULL,
    -- Add the primary key as part of the CREATE TABLE statement in case `innodb_force_primary_key` is enabled
    CONSTRAINT `flyway_schema_history_pk`PRIMARY KEY (`installed_rank`)
) ENGINE=InnoDB
DEBUG: Found statement at line 32: CREATE INDEX `flyway_schema_history_s_idx` ON `hist`.`flyway_schema_history` (`success`)
DEBUG: Executing SQL: CREATE TABLE `hist`.`flyway_schema_history` (
    `installed_rank` INT NOT NULL,
    `version` VARCHAR(50),
    `description` VARCHAR(200) NOT NULL,
    `type` VARCHAR(20) NOT NULL,
    `script` VARCHAR(1000) NOT NULL,
    `checksum` INT,
    `installed_by` VARCHAR(100) NOT NULL,
    `installed_on` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `execution_time` INT NOT NULL,
    `success` BOOL NOT NULL,
    -- Add the primary key as part of the CREATE TABLE statement in case `innodb_force_primary_key` is enabled
    CONSTRAINT `flyway_schema_history_pk`PRIMARY KEY (`installed_rank`)
) ENGINE=InnoDB
DEBUG: Rolling back transaction...
DEBUG: Transaction rolled back
DEBUG: Schema History table creation failed. Retrying in 1 sec ...
DEBUG: Parsing createMetaDataTable.sql ...
DEBUG: Found statement at line 17: CREATE TABLE `hist`.`flyway_schema_history` (
    `installed_rank` INT NOT NULL,
    `version` VARCHAR(50),
    `description` VARCHAR(200) NOT NULL,
    `type` VARCHAR(20) NOT NULL,
    `script` VARCHAR(1000) NOT NULL,
    `checksum` INT,
    `installed_by` VARCHAR(100) NOT NULL,
    `installed_on` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `execution_time` INT NOT NULL,
    `success` BOOL NOT NULL,
    -- Add the primary key as part of the CREATE TABLE statement in case `innodb_force_primary_key` is enabled
    CONSTRAINT `flyway_schema_history_pk`PRIMARY KEY (`installed_rank`)
) ENGINE=InnoDB
DEBUG: Found statement at line 32: CREATE INDEX `flyway_schema_history_s_idx` ON `hist`.`flyway_schema_history` (`success`)
DEBUG: Executing SQL: CREATE TABLE `hist`.`flyway_schema_history` (
    `installed_rank` INT NOT NULL,
    `version` VARCHAR(50),
    `description` VARCHAR(200) NOT NULL,
    `type` VARCHAR(20) NOT NULL,
    `script` VARCHAR(1000) NOT NULL,
    `checksum` INT,
    `installed_by` VARCHAR(100) NOT NULL,
    `installed_on` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `execution_time` INT NOT NULL,
    `success` BOOL NOT NULL,
    -- Add the primary key as part of the CREATE TABLE statement in case `innodb_force_primary_key` is enabled
    CONSTRAINT `flyway_schema_history_pk`PRIMARY KEY (`installed_rank`)
) ENGINE=InnoDB
DEBUG: Rolling back transaction...
DEBUG: Transaction rolled back
DEBUG: Schema History table creation failed. Retrying in 1 sec ...
DEBUG: Parsing createMetaDataTable.sql ...
DEBUG: Found statement at line 17: CREATE TABLE `hist`.`flyway_schema_history` (
    `installed_rank` INT NOT NULL,
    `version` VARCHAR(50),
    `description` VARCHAR(200) NOT NULL,
    `type` VARCHAR(20) NOT NULL,
    `script` VARCHAR(1000) NOT NULL,
    `checksum` INT,
    `installed_by` VARCHAR(100) NOT NULL,
    `installed_on` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `execution_time` INT NOT NULL,
    `success` BOOL NOT NULL,
    -- Add the primary key as part of the CREATE TABLE statement in case `innodb_force_primary_key` is enabled
    CONSTRAINT `flyway_schema_history_pk`PRIMARY KEY (`installed_rank`)
) ENGINE=InnoDB
DEBUG: Found statement at line 32: CREATE INDEX `flyway_schema_history_s_idx` ON `hist`.`flyway_schema_history` (`success`)
DEBUG: Executing SQL: CREATE TABLE `hist`.`flyway_schema_history` (
    `installed_rank` INT NOT NULL,
    `version` VARCHAR(50),
    `description` VARCHAR(200) NOT NULL,
    `type` VARCHAR(20) NOT NULL,
    `script` VARCHAR(1000) NOT NULL,
    `checksum` INT,
    `installed_by` VARCHAR(100) NOT NULL,
    `installed_on` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `execution_time` INT NOT NULL,
    `success` BOOL NOT NULL,
    -- Add the primary key as part of the CREATE TABLE statement in case `innodb_force_primary_key` is enabled
    CONSTRAINT `flyway_schema_history_pk`PRIMARY KEY (`installed_rank`)
) ENGINE=InnoDB
DEBUG: Rolling back transaction...
DEBUG: Transaction rolled back
DEBUG: Schema History table creation failed. Retrying in 1 sec ...
DEBUG: Parsing createMetaDataTable.sql ...
DEBUG: Found statement at line 17: CREATE TABLE `hist`.`flyway_schema_history` (
    `installed_rank` INT NOT NULL,
    `version` VARCHAR(50),
    `description` VARCHAR(200) NOT NULL,
    `type` VARCHAR(20) NOT NULL,
    `script` VARCHAR(1000) NOT NULL,
    `checksum` INT,
    `installed_by` VARCHAR(100) NOT NULL,
    `installed_on` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `execution_time` INT NOT NULL,
    `success` BOOL NOT NULL,
    -- Add the primary key as part of the CREATE TABLE statement in case `innodb_force_primary_key` is enabled
    CONSTRAINT `flyway_schema_history_pk`PRIMARY KEY (`installed_rank`)
) ENGINE=InnoDB
DEBUG: Found statement at line 32: CREATE INDEX `flyway_schema_history_s_idx` ON `hist`.`flyway_schema_history` (`success`)
DEBUG: Executing SQL: CREATE TABLE `hist`.`flyway_schema_history` (
    `installed_rank` INT NOT NULL,
    `version` VARCHAR(50),
    `description` VARCHAR(200) NOT NULL,
    `type` VARCHAR(20) NOT NULL,
    `script` VARCHAR(1000) NOT NULL,
    `checksum` INT,
    `installed_by` VARCHAR(100) NOT NULL,
    `installed_on` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `execution_time` INT NOT NULL,
    `success` BOOL NOT NULL,
    -- Add the primary key as part of the CREATE TABLE statement in case `innodb_force_primary_key` is enabled
    CONSTRAINT `flyway_schema_history_pk`PRIMARY KEY (`installed_rank`)
) ENGINE=InnoDB
DEBUG: Rolling back transaction...
DEBUG: Transaction rolled back
DEBUG: Schema History table creation failed. Retrying in 1 sec ...
DEBUG: Parsing createMetaDataTable.sql ...
DEBUG: Found statement at line 17: CREATE TABLE `hist`.`flyway_schema_history` (
    `installed_rank` INT NOT NULL,
    `version` VARCHAR(50),
    `description` VARCHAR(200) NOT NULL,
    `type` VARCHAR(20) NOT NULL,
    `script` VARCHAR(1000) NOT NULL,
    `checksum` INT,
    `installed_by` VARCHAR(100) NOT NULL,
    `installed_on` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `execution_time` INT NOT NULL,
    `success` BOOL NOT NULL,
    -- Add the primary key as part of the CREATE TABLE statement in case `innodb_force_primary_key` is enabled
    CONSTRAINT `flyway_schema_history_pk`PRIMARY KEY (`installed_rank`)
) ENGINE=InnoDB
DEBUG: Found statement at line 32: CREATE INDEX `flyway_schema_history_s_idx` ON `hist`.`flyway_schema_history` (`success`)
DEBUG: Executing SQL: CREATE TABLE `hist`.`flyway_schema_history` (
    `installed_rank` INT NOT NULL,
    `version` VARCHAR(50),
    `description` VARCHAR(200) NOT NULL,
    `type` VARCHAR(20) NOT NULL,
    `script` VARCHAR(1000) NOT NULL,
    `checksum` INT,
    `installed_by` VARCHAR(100) NOT NULL,
    `installed_on` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `execution_time` INT NOT NULL,
    `success` BOOL NOT NULL,
    -- Add the primary key as part of the CREATE TABLE statement in case `innodb_force_primary_key` is enabled
    CONSTRAINT `flyway_schema_history_pk`PRIMARY KEY (`installed_rank`)
) ENGINE=InnoDB
DEBUG: Rolling back transaction...
DEBUG: Transaction rolled back
DEBUG: Schema History table creation failed. Retrying in 1 sec ...
DEBUG: Parsing createMetaDataTable.sql ...
DEBUG: Found statement at line 17: CREATE TABLE `hist`.`flyway_schema_history` (
    `installed_rank` INT NOT NULL,
    `version` VARCHAR(50),
    `description` VARCHAR(200) NOT NULL,
    `type` VARCHAR(20) NOT NULL,
    `script` VARCHAR(1000) NOT NULL,
    `checksum` INT,
    `installed_by` VARCHAR(100) NOT NULL,
    `installed_on` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `execution_time` INT NOT NULL,
    `success` BOOL NOT NULL,
    -- Add the primary key as part of the CREATE TABLE statement in case `innodb_force_primary_key` is enabled
    CONSTRAINT `flyway_schema_history_pk`PRIMARY KEY (`installed_rank`)
) ENGINE=InnoDB
DEBUG: Found statement at line 32: CREATE INDEX `flyway_schema_history_s_idx` ON `hist`.`flyway_schema_history` (`success`)
DEBUG: Executing SQL: CREATE TABLE `hist`.`flyway_schema_history` (
    `installed_rank` INT NOT NULL,
    `version` VARCHAR(50),
    `description` VARCHAR(200) NOT NULL,
    `type` VARCHAR(20) NOT NULL,
    `script` VARCHAR(1000) NOT NULL,
    `checksum` INT,
    `installed_by` VARCHAR(100) NOT NULL,
    `installed_on` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `execution_time` INT NOT NULL,
    `success` BOOL NOT NULL,
    -- Add the primary key as part of the CREATE TABLE statement in case `innodb_force_primary_key` is enabled
    CONSTRAINT `flyway_schema_history_pk`PRIMARY KEY (`installed_rank`)
) ENGINE=InnoDB
DEBUG: Rolling back transaction...
DEBUG: Transaction rolled back
DEBUG: Schema History table creation failed. Retrying in 1 sec ...
DEBUG: Parsing createMetaDataTable.sql ...
DEBUG: Found statement at line 17: CREATE TABLE `hist`.`flyway_schema_history` (
    `installed_rank` INT NOT NULL,
    `version` VARCHAR(50),
    `description` VARCHAR(200) NOT NULL,
    `type` VARCHAR(20) NOT NULL,
    `script` VARCHAR(1000) NOT NULL,
    `checksum` INT,
    `installed_by` VARCHAR(100) NOT NULL,
    `installed_on` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `execution_time` INT NOT NULL,
    `success` BOOL NOT NULL,
    -- Add the primary key as part of the CREATE TABLE statement in case `innodb_force_primary_key` is enabled
    CONSTRAINT `flyway_schema_history_pk`PRIMARY KEY (`installed_rank`)
) ENGINE=InnoDB
DEBUG: Found statement at line 32: CREATE INDEX `flyway_schema_history_s_idx` ON `hist`.`flyway_schema_history` (`success`)
DEBUG: Executing SQL: CREATE TABLE `hist`.`flyway_schema_history` (
    `installed_rank` INT NOT NULL,
    `version` VARCHAR(50),
    `description` VARCHAR(200) NOT NULL,
    `type` VARCHAR(20) NOT NULL,
    `script` VARCHAR(1000) NOT NULL,
    `checksum` INT,
    `installed_by` VARCHAR(100) NOT NULL,
    `installed_on` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `execution_time` INT NOT NULL,
    `success` BOOL NOT NULL,
    -- Add the primary key as part of the CREATE TABLE statement in case `innodb_force_primary_key` is enabled
    CONSTRAINT `flyway_schema_history_pk`PRIMARY KEY (`installed_rank`)
) ENGINE=InnoDB
DEBUG: Rolling back transaction...
DEBUG: Transaction rolled back
DEBUG: Schema History table creation failed. Retrying in 1 sec ...
DEBUG: Parsing createMetaDataTable.sql ...
DEBUG: Found statement at line 17: CREATE TABLE `hist`.`flyway_schema_history` (
    `installed_rank` INT NOT NULL,
    `version` VARCHAR(50),
    `description` VARCHAR(200) NOT NULL,
    `type` VARCHAR(20) NOT NULL,
    `script` VARCHAR(1000) NOT NULL,
    `checksum` INT,
    `installed_by` VARCHAR(100) NOT NULL,
    `installed_on` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `execution_time` INT NOT NULL,
    `success` BOOL NOT NULL,
    -- Add the primary key as part of the CREATE TABLE statement in case `innodb_force_primary_key` is enabled
    CONSTRAINT `flyway_schema_history_pk`PRIMARY KEY (`installed_rank`)
) ENGINE=InnoDB
DEBUG: Found statement at line 32: CREATE INDEX `flyway_schema_history_s_idx` ON `hist`.`flyway_schema_history` (`success`)
DEBUG: Executing SQL: CREATE TABLE `hist`.`flyway_schema_history` (
    `installed_rank` INT NOT NULL,
    `version` VARCHAR(50),
    `description` VARCHAR(200) NOT NULL,
    `type` VARCHAR(20) NOT NULL,
    `script` VARCHAR(1000) NOT NULL,
    `checksum` INT,
    `installed_by` VARCHAR(100) NOT NULL,
    `installed_on` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `execution_time` INT NOT NULL,
    `success` BOOL NOT NULL,
    -- Add the primary key as part of the CREATE TABLE statement in case `innodb_force_primary_key` is enabled
    CONSTRAINT `flyway_schema_history_pk`PRIMARY KEY (`installed_rank`)
) ENGINE=InnoDB
DEBUG: Rolling back transaction...
DEBUG: Transaction rolled back
DEBUG: Schema History table creation failed. Retrying in 1 sec ...
DEBUG: Parsing createMetaDataTable.sql ...
DEBUG: Found statement at line 17: CREATE TABLE `hist`.`flyway_schema_history` (
    `installed_rank` INT NOT NULL,
    `version` VARCHAR(50),
    `description` VARCHAR(200) NOT NULL,
    `type` VARCHAR(20) NOT NULL,
    `script` VARCHAR(1000) NOT NULL,
    `checksum` INT,
    `installed_by` VARCHAR(100) NOT NULL,
    `installed_on` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `execution_time` INT NOT NULL,
    `success` BOOL NOT NULL,
    -- Add the primary key as part of the CREATE TABLE statement in case `innodb_force_primary_key` is enabled
    CONSTRAINT `flyway_schema_history_pk`PRIMARY KEY (`installed_rank`)
) ENGINE=InnoDB
DEBUG: Found statement at line 32: CREATE INDEX `flyway_schema_history_s_idx` ON `hist`.`flyway_schema_history` (`success`)
DEBUG: Executing SQL: CREATE TABLE `hist`.`flyway_schema_history` (
    `installed_rank` INT NOT NULL,
    `version` VARCHAR(50),
    `description` VARCHAR(200) NOT NULL,
    `type` VARCHAR(20) NOT NULL,
    `script` VARCHAR(1000) NOT NULL,
    `checksum` INT,
    `installed_by` VARCHAR(100) NOT NULL,
    `installed_on` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `execution_time` INT NOT NULL,
    `success` BOOL NOT NULL,
    -- Add the primary key as part of the CREATE TABLE statement in case `innodb_force_primary_key` is enabled
    CONSTRAINT `flyway_schema_history_pk`PRIMARY KEY (`installed_rank`)
) ENGINE=InnoDB
DEBUG: Rolling back transaction...
DEBUG: Transaction rolled back
ERROR: Unexpected error
org.flywaydb.core.internal.sqlscript.FlywaySqlScriptException: 
Migration createMetaDataTable.sql failed
----------------------------------------
SQL State  : 42000
Error Code : 1049
Message    : (conn=6) Unknown database 'hist'
Location   : org/flywaydb/core/internal/database/mysql/createMetaDataTable.sql (/tmp/file:/opt/flyway/flyway-5.1.4/lib/flyway-core-5.1.4.jar!/org/flywaydb/core/internal/database/mysql/createMetaDataTable.sql)
Line       : 17
Statement  : CREATE TABLE `hist`.`flyway_schema_history` (
    `installed_rank` INT NOT NULL,
    `version` VARCHAR(50),
    `description` VARCHAR(200) NOT NULL,
    `type` VARCHAR(20) NOT NULL,
    `script` VARCHAR(1000) NOT NULL,
    `checksum` INT,
    `installed_by` VARCHAR(100) NOT NULL,
    `installed_on` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `execution_time` INT NOT NULL,
    `success` BOOL NOT NULL,
    -- Add the primary key as part of the CREATE TABLE statement in case `innodb_force_primary_key` is enabled
    CONSTRAINT `flyway_schema_history_pk`PRIMARY KEY (`installed_rank`)
) ENGINE=InnoDB

	at org.flywaydb.core.internal.database.ExecutableSqlScript.handleException(ExecutableSqlScript.java:331)
	at org.flywaydb.core.internal.database.ExecutableSqlScript.executeStatement(ExecutableSqlScript.java:301)
	at org.flywaydb.core.internal.database.ExecutableSqlScript.execute(ExecutableSqlScript.java:243)
	at org.flywaydb.core.internal.schemahistory.JdbcTableSchemaHistory$1.call(JdbcTableSchemaHistory.java:127)
	at org.flywaydb.core.internal.util.jdbc.TransactionTemplate.execute(TransactionTemplate.java:74)
	at org.flywaydb.core.internal.schemahistory.JdbcTableSchemaHistory.create(JdbcTableSchemaHistory.java:124)
	at org.flywaydb.core.internal.command.DbMigrate.migrate(DbMigrate.java:126)
	at org.flywaydb.core.Flyway$1.execute(Flyway.java:947)
	at org.flywaydb.core.Flyway$1.execute(Flyway.java:910)
	at org.flywaydb.core.Flyway.execute(Flyway.java:1238)
	at org.flywaydb.core.Flyway.migrate(Flyway.java:910)
	at org.flywaydb.commandline.Main.executeOperation(Main.java:161)
	at org.flywaydb.commandline.Main.main(Main.java:108)
Caused by: java.sql.SQLSyntaxErrorException: (conn=6) Unknown database 'hist'
	at org.mariadb.jdbc.internal.util.exceptions.ExceptionMapper.get(ExceptionMapper.java:177)
	at org.mariadb.jdbc.internal.util.exceptions.ExceptionMapper.getException(ExceptionMapper.java:110)
	at org.mariadb.jdbc.MariaDbStatement.executeExceptionEpilogue(MariaDbStatement.java:228)
	at org.mariadb.jdbc.MariaDbStatement.executeInternal(MariaDbStatement.java:334)
	at org.mariadb.jdbc.MariaDbStatement.execute(MariaDbStatement.java:386)
	at org.flywaydb.core.internal.util.jdbc.JdbcTemplate.executeStatement(JdbcTemplate.java:244)
	at org.flywaydb.core.internal.database.StandardSqlStatement.execute(StandardSqlStatement.java:44)
	at org.flywaydb.core.internal.database.ExecutableSqlScript.executeStatement(ExecutableSqlScript.java:280)
	... 11 more
Caused by: java.sql.SQLException: Unknown database 'hist'
Query is: CREATE TABLE `hist`.`flyway_schema_history` (
    `installed_rank` INT NOT NULL,
    `version` VARCHAR(50),
    `description` VARCHAR(200) NOT NULL,
    `type` VARCHAR(20) NOT NULL,
    `script` VARCHAR(1000) NOT NULL,
    `checksum` INT,
    `installed_by` VARCHAR(100) NOT NULL,
    `installed_on` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `execution_time` INT NOT NULL,
    `success` BOOL NOT NULL,
    -- Add the primary key as part of the CREATE TABLE statement in case `innodb_force_primary_key` is enabled
    CONSTRAINT `flyway_schema_history_pk`PRIMARY KEY (`installed_rank`)
) ENGINE=InnoDB
	at org.mariadb.jdbc.internal.util.LogQueryTool.exceptionWithQuery(LogQueryTool.java:119)
	at org.mariadb.jdbc.internal.protocol.AbstractQueryProtocol.executeQuery(AbstractQueryProtocol.java:199)
	at org.mariadb.jdbc.MariaDbStatement.executeInternal(MariaDbStatement.java:328)
	... 15 more

@htto
Copy link
Author

@htto htto commented Nov 13, 2018

I guess I figured the problem: Flyway does not create the first schema for the metadata table, if one of the following schemas already exists, even if they are empty.

Btw. this also breaks the baseline command....

Reproduction:

mysql -u root -h localhost -P 3306 --password='' -e 'DROP SCHEMA IF EXISTS existing; DROP SCHEMA IF EXISTS new; CREATE SCHEMA existing' && flyway -X -url=jdbc:mariadb://localhost:3306 -user=root -password= baseline -schemas=new,existing

@htto
Copy link
Author

@htto htto commented Nov 13, 2018

EDIT Just re-tested this with 5.2.1, same issue there /EDIT

Btw. if you don't specify schemas you get this funny thing:

  >> flyway -X -url=jdbc:mariadb://localhost -user=root -password=''  baseline
Flyway Community Edition 5.1.4 by Boxfuse

DEBUG: Java 1.8.0_192 (Oracle Corporation)
DEBUG: Linux 4.18.16-arch1-1-ARCH amd64

DEBUG: Loading config file: /opt/flyway/flyway-5.1.4/conf/flyway.conf
DEBUG: Unable to load config file: /home/user/flyway.conf
DEBUG: Unable to load config file: /tmp/flyway.conf
DEBUG: Using configuration:
DEBUG: flyway.jarDirs -> /opt/flyway/flyway-5.1.4/jars
DEBUG: flyway.password -> 
DEBUG: flyway.url -> jdbc:mariadb://localhost
DEBUG: flyway.user -> root
DEBUG: flyway.locations -> filesystem:/opt/flyway/flyway-5.1.4/sql
DEBUG: Adding location to classpath: /opt/flyway/flyway-5.1.4/drivers/jtds-1.3.1.jar
DEBUG: Adding location to classpath: /opt/flyway/flyway-5.1.4/drivers/mssql-jdbc-6.4.0.jre7.jar
DEBUG: Adding location to classpath: /opt/flyway/flyway-5.1.4/drivers/derby-10.14.2.0.jar
DEBUG: Adding location to classpath: /opt/flyway/flyway-5.1.4/drivers/mariadb-java-client-2.2.4.jar
DEBUG: Adding location to classpath: /opt/flyway/flyway-5.1.4/drivers/sqlite-jdbc-3.20.1.jar
DEBUG: Adding location to classpath: /opt/flyway/flyway-5.1.4/drivers/hsqldb-2.4.0.jar
DEBUG: Adding location to classpath: /opt/flyway/flyway-5.1.4/drivers/postgresql-42.2.2.jre6.jar
DEBUG: Adding location to classpath: /opt/flyway/flyway-5.1.4/drivers/h2-1.4.197.jar
DEBUG: Adding location to classpath: /opt/flyway/flyway-5.1.4/drivers/derbyclient-10.14.2.0.jar
Database: jdbc:mariadb://localhost (MySQL 10.1)
DEBUG: Driver  : MariaDB connector/J 2.2.4
DEBUG: DDL Transactions Supported: false
DEBUG: Schema: null
DEBUG: Spring Jdbc available: false
DEBUG: Scanning for SQL callbacks ...
DEBUG: Scanning for filesystem resources at '/opt/flyway/flyway-5.1.4/sql' (Prefix: '', Suffixes: '.sql')
DEBUG: Scanning for resources in path: /opt/flyway/flyway-5.1.4/sql (/opt/flyway/flyway-5.1.4/sql)
DEBUG: Filtering out resource: /opt/flyway/flyway-5.1.4/sql/put-your-sql-migrations-here.txt (filename: put-your-sql-migrations-here.txt)
Creating schema `null` ...
DEBUG: Locking table `null`.`flyway_schema_history`...
DEBUG: Rolling back transaction...
DEBUG: Transaction rolled back
DEBUG: Schema creation failed. Retrying in 1 sec ...
Creating schema `null` ...
DEBUG: Rolling back transaction...
DEBUG: Transaction rolled back
DEBUG: Schema creation failed. Retrying in 1 sec ...
Creating schema `null` ...
DEBUG: Rolling back transaction...
DEBUG: Transaction rolled back
DEBUG: Schema creation failed. Retrying in 1 sec ...
Creating schema `null` ...
DEBUG: Rolling back transaction...
DEBUG: Transaction rolled back
DEBUG: Schema creation failed. Retrying in 1 sec ...
Creating schema `null` ...
DEBUG: Rolling back transaction...
DEBUG: Transaction rolled back
DEBUG: Schema creation failed. Retrying in 1 sec ...
Creating schema `null` ...
DEBUG: Rolling back transaction...
DEBUG: Transaction rolled back
DEBUG: Schema creation failed. Retrying in 1 sec ...
Creating schema `null` ...
DEBUG: Rolling back transaction...
DEBUG: Transaction rolled back
DEBUG: Schema creation failed. Retrying in 1 sec ...
Creating schema `null` ...
DEBUG: Rolling back transaction...
DEBUG: Transaction rolled back
DEBUG: Schema creation failed. Retrying in 1 sec ...
Creating schema `null` ...
DEBUG: Rolling back transaction...
DEBUG: Transaction rolled back
DEBUG: Schema creation failed. Retrying in 1 sec ...
Creating schema `null` ...
DEBUG: Rolling back transaction...
DEBUG: Transaction rolled back
ERROR: Unexpected error
org.flywaydb.core.internal.exception.FlywaySqlException: 
Unable to create schema `null`
------------------------------
SQL State  : HY000
Error Code : 1007
Message    : (conn=114) Can't create database 'null'; database exists

	at org.flywaydb.core.internal.database.Schema.create(Schema.java:115)
	at org.flywaydb.core.internal.command.DbSchemas$1.call(DbSchemas.java:81)
	at org.flywaydb.core.internal.command.DbSchemas$1.call(DbSchemas.java:69)
	at org.flywaydb.core.internal.util.jdbc.TransactionTemplate.execute(TransactionTemplate.java:74)
	at org.flywaydb.core.internal.command.DbSchemas.create(DbSchemas.java:69)
	at org.flywaydb.core.Flyway$5.execute(Flyway.java:1103)
	at org.flywaydb.core.Flyway$5.execute(Flyway.java:1096)
	at org.flywaydb.core.Flyway.execute(Flyway.java:1238)
	at org.flywaydb.core.Flyway.baseline(Flyway.java:1096)
	at org.flywaydb.commandline.Main.executeOperation(Main.java:159)
	at org.flywaydb.commandline.Main.main(Main.java:108)
Caused by: java.sql.SQLException: (conn=114) Can't create database 'null'; database exists
	at org.mariadb.jdbc.internal.util.exceptions.ExceptionMapper.get(ExceptionMapper.java:198)
	at org.mariadb.jdbc.internal.util.exceptions.ExceptionMapper.getException(ExceptionMapper.java:110)
	at org.mariadb.jdbc.MariaDbStatement.executeExceptionEpilogue(MariaDbStatement.java:228)
	at org.mariadb.jdbc.MariaDbPreparedStatementClient.executeInternal(MariaDbPreparedStatementClient.java:216)
	at org.mariadb.jdbc.MariaDbPreparedStatementClient.execute(MariaDbPreparedStatementClient.java:150)
	at org.flywaydb.core.internal.util.jdbc.JdbcTemplate.execute(JdbcTemplate.java:225)
	at org.flywaydb.core.internal.database.mysql.MySQLSchema.doCreate(MySQLSchema.java:63)
	at org.flywaydb.core.internal.database.Schema.create(Schema.java:113)
	... 10 more
Caused by: java.sql.SQLException: Can't create database 'null'; database exists
Query is: CREATE SCHEMA `null`
	at org.mariadb.jdbc.internal.util.LogQueryTool.exceptionWithQuery(LogQueryTool.java:153)
	at org.mariadb.jdbc.internal.protocol.AbstractQueryProtocol.executeQuery(AbstractQueryProtocol.java:254)
	at org.mariadb.jdbc.MariaDbPreparedStatementClient.executeInternal(MariaDbPreparedStatementClient.java:209)
	... 14 more

Looks like the schema handling needs some love.

@axelfontaine
Copy link
Contributor

@axelfontaine axelfontaine commented Nov 19, 2018

Your initial issue is a duplicate of #1328

Repurposing this one for the null schema one.

@axelfontaine axelfontaine changed the title Flyway sometimes fails to create schema for metadata table with multiple schemas MariaDB: Can't create database 'null' error if no database was specified in URL Nov 19, 2018
@axelfontaine axelfontaine added this to the Flyway 5.2.2 milestone Nov 19, 2018
axelfontaine added a commit that referenced this issue Nov 19, 2018
…se was specified in URL
axelfontaine added a commit to flyway/flywaydb.org that referenced this issue Nov 19, 2018
@axelfontaine
Copy link
Contributor

@axelfontaine axelfontaine commented Nov 19, 2018

This has now been fixed. When no schema has been specified (not in the URL and not using the schemas property), Flyway will now fail fast with a clear error message instead of falling into the behavior you documented above.

dohrayme pushed a commit to dohrayme/flyway that referenced this issue Feb 3, 2020
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Linked pull requests

Successfully merging a pull request may close this issue.

None yet
2 participants