MySQL always trying to create table

[gwalrath]

I have configured MySQL in the zuul-data-config.properties file, but when zuul starts, it constantly tries to create the SETTINGS table and fails because it's already there:

[11-16 22:39:06] ERROR ContextLoader [localhost-startStop-1]: Context initialization failed org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'org.springframework.dao.annotation.PersistenceExceptionTranslationPostProcessor#0': Initialization of bean failed; nested exception is org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'liquibase' defined in class path resource [zuul-data-context.xml]: Invocation of init method failed; nested exception is liquibase.exception.MigrationFailedException: Migration failed for change set /data/changelog/db.changelog-1.5.xml::zuul-1.5-create-settings-table::mcantrell: Reason: liquibase.exception.DatabaseException: Error executing SQL CREATE TABLE SETTINGS (ID INT AUTO_INCREMENT NOT NULL, NAME VARCHAR(255) NOT NULL, FOLDER VARCHAR(32) NULL, CONSTRAINT PK_SETTINGS PRIMARY KEY (ID)): Table 'SETTINGS' already exists: Caused By: Error executing SQL CREATE TABLE SETTINGS (ID INT AUTO_INCREMENT NOT NULL, NAME VARCHAR(255) NOT NULL, FOLDER VARCHAR(32) NULL, CONSTRAINT PK_SETTINGS PRIMARY KEY (ID)): Table 'SETTINGS' already exists: Caused By: Table 'SETTINGS' already exists at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.doCreateBean(AbstractAutowireCapableBeanFactory.java:527)

Properties looks like this:

jdbc.zuul.url=jdbc:mysql://10.253.70.39/zuul_prod jdbc.zuul.generate.ddl=none jdbc.zuul.username=zuuladmin jdbc.zuul.password=**** jdbc.zuul.driver=com.mysql.jdbc.Driver jdbc.zuul.dialect=org.hibernate.dialect.MySQLDialect jdbc.zuul.validationQuery=select 1 from DUAL

Is there a step I'm missing somewhere?

[psmith]

I'll try and take a look today. What version of mysql are you running? What version of java, and what app server are you deploying to?

[gwalrath]

MySQL is 5.6.10. Tomcat 7/Java 7, all running on Ubuntu:

Using CATALINA_BASE: /usr/share/tomcat7 Using CATALINA_HOME: /usr/share/tomcat7 Using CATALINA_TMPDIR: /usr/share/tomcat7/temp Using JRE_HOME: /usr Using CLASSPATH: /usr/share/tomcat7/bin/bootstrap.jar:/usr/share/tomcat7/bin/tomcat-juli.jar Server version: Apache Tomcat/7.0.52 (Ubuntu) Server built: Jun 19 2015 08:54:46 Server number: 7.0.52.0 OS Name: Linux OS Version: 3.13.0-48-generic Architecture: amd64 JVM Version: 1.7.0_85-b01 JVM Vendor: Oracle Corporation

[gwalrath]

One other note - the "MySQL" server I'm connecting to is the MySQL service at Amazon Web Services. From what I understand, it appears to be a MySQL server to any connecting application, but it's something else under the covers.

https://aws.amazon.com/rds/mysql/

[psmith]

I think it's case sensitivity for tables names in mysql when running on a *nix os that is screwing this up. When I get the same error as you on all subsequent runs, aside from the first time I run zuul (on mysql installed on linux). On the first time, it creates all the tables, then starts throwing errors when it tries to select the data.

liquibase scripts are using all uppercase names for tables. The generated sql, is using lowercase names, example: SQL INSERT INTO settings (NAME) SELECT DISTINCT NAME FROM settings_group: Table 'zuul_prod.settings' doesn't exist

So, next time zuul runs, it looks for the settings table, doesn't think it exists (because it doesn't, but a SETTINGS table does exist). So it then try to create a table, but it creates SETTINGS and you get the error.

To see if that is happening to you:

What happens if you log in and show tables?

You should see something like this: mysql> use zuul_prod;

mysql> show tables; +-----------------------+ | Tables_in_zuul_prod | +-----------------------+ | DATABASECHANGELOG | | DATABASECHANGELOGLOCK | | ENCRYPTION_KEY | | ENVIRONMENT | | SECURITY_ROLE | | SECURITY_USER | | SECURITY_USER_ROLE | | SETTINGS | | SETTINGS_AUDIT | | SETTINGS_ENTRY | | SETTINGS_GROUP | +-----------------------+

Now, here is where it gets really fun. If I run...

mysql> select * from settings

I get:

ERROR 1146 (42S02): Table 'zuul_prod.settings' doesn't exist

If i run:

mysql> select * from SETTINGS;

I get:

Empty set (0.00 sec)

Work around.... setting

lower_case_table_names = 1 in the [mysqld] section of the appropriate my.cnf file

I'll look into a real fix, but since this supports multiple db's, os's, It will have to be regression tested against a bunch of different variations.

(I'm working on dockerizing all this to make integration regression testing, and heck, demo's easier)

[gwalrath]

I don't kwow if I want to do the lower_case_table_names = 1, since there are other apps using this same MySQL instance, and will be even more in the future. Plus, it looks to be difficult to set options on this 'host', as I'm not sure i have a login interface (like SSH). Seems to be a black box.

What's the workaround for now - just running mysql on a Windows box? Or running Zuul on a Windows box?

[psmith]

Renaming all the table identifiers in the liquibase scripts to either all upper, or all lower case. See this pull request https://github.com/Confluex/Zuul/pull/75 for the files, etc that had to change. This hasn't been regression tested with other databases, etc, so it hasn't been accepted. It's not the most trivial thing to test since one must test upgrading existing databases, or identify it as a breaking change and provide a data migration plan.

MySql on a windows box would remove the case issues, so would using a different db for zuul on the linux box.

[gwalrath]

We ended up creating a whole new RDS instance at AWS for this, with lower_case_table_names = 1, and that resolved the issue. Thanks.