Can't connect to MySQL - You have an error in your SQL syntax
症状
Stash fails to connect to MySQL database either when installing it or migrating databases. The following appears in the atlassian-stash.log:
The schema could not be created.
com.atlassian.stash.internal.db.DefaultDatabaseManager.createSchema(DefaultDatabaseManager.java:317)
com.atlassian.stash.internal.db.DefaultDatabaseManager.prepareDatabase(DefaultDatabaseManager.java:161)
com.atlassian.stash.internal.maintenance.SpringMaintenanceTaskFactory.setupTask(SpringMaintenanceTaskFactory.java:145)
...
Error creating bean with name 'liquibasePrototype' defined in class path resource [stash-context.xml]: Invocation of init method failed; nested exception is liquibase.exception.MigrationFailedException: Migration failed for change set com/atlassian/caviar/db/changelog/r1_0/initial-schema.xml::initial-schema-03::mheemskerk:
Reason: liquibase.exception.DatabaseException: Error executing SQL CREATE TABLE `nb-stash`.`cwd_app_dir_mapping` (`id` BIGINT NOT NULL, `application_id` BIGINT NOT NULL, `directory_id` BIGINT NOT NULL, `allow_all` CHARACTER(255) NOT NULL, `list_index` INT NULL, CONSTRAINT `SYS_PK_10077` PRIMARY KEY (`id`), CONSTRAINT `FK_APP_DIR_DIR` FOREIGN KEY (`directory_id`) REFERENCES nb-stash.cwd_directory(id), CONSTRAINT `FK_APP_DIR_APP` FOREIGN KEY (`application_id`) REFERENCES nb-stash.cwd_application(id)): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '-stash.cwd_directory(id), CONSTRAINT `FK_APP_DIR_APP` FOREIGN KEY (`application_' at line 1:
Caused By: Error executing SQL CREATE TABLE `nb-stash`.`cwd_app_dir_mapping` (`id` BIGINT NOT NULL, `application_id` BIGINT NOT NULL, `directory_id` BIGINT NOT NULL, `allow_all` CHARACTER(255) NOT NULL, `list_index` INT NULL, CONSTRAINT `SYS_PK_10077` PRIMARY KEY (`id`), CONSTRAINT `FK_APP_DIR_DIR` FOREIGN KEY (`directory_id`) REFERENCES nb-stash.cwd_directory(id), CONSTRAINT `FK_APP_DIR_APP` FOREIGN KEY (`application_id`) REFERENCES nb-stash.cwd_application(id)): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '-stash.cwd_directory(id), CONSTRAINT `FK_APP_DIR_APP` FOREIGN KEY (`application_' at line 1:
Caused By: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '-stash.cwd_directory(id), CONSTRAINT `FK_APP_DIR_APP` FOREIGN KEY (`application_' at line 1
org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.initializeBean(AbstractAutowireCapableBeanFactory.java:1553)
org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.doCreateBean(AbstractAutowireCapableBeanFactory.java:539)
org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBean(AbstractAutowireCapableBeanFactory.java:475)
...
Migration failed for change set com/atlassian/caviar/db/changelog/r1_0/initial-schema.xml::initial-schema-03::mheemskerk:
Reason: liquibase.exception.DatabaseException: Error executing SQL CREATE TABLE `nb-stash`.`cwd_app_dir_mapping` (`id` BIGINT NOT NULL, `application_id` BIGINT NOT NULL, `directory_id` BIGINT NOT NULL, `allow_all` CHARACTER(255) NOT NULL, `list_index` INT NULL, CONSTRAINT `SYS_PK_10077` PRIMARY KEY (`id`), CONSTRAINT `FK_APP_DIR_DIR` FOREIGN KEY (`directory_id`) REFERENCES nb-stash.cwd_directory(id), CONSTRAINT `FK_APP_DIR_APP` FOREIGN KEY (`application_id`) REFERENCES nb-stash.cwd_application(id)): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '-stash.cwd_directory(id), CONSTRAINT `FK_APP_DIR_APP` FOREIGN KEY (`application_' at line 1:
Caused By: Error executing SQL CREATE TABLE `nb-stash`.`cwd_app_dir_mapping` (`id` BIGINT NOT NULL, `application_id` BIGINT NOT NULL, `directory_id` BIGINT NOT NULL, `allow_all` CHARACTER(255) NOT NULL, `list_index` INT NULL, CONSTRAINT `SYS_PK_10077` PRIMARY KEY (`id`), CONSTRAINT `FK_APP_DIR_DIR` FOREIGN KEY (`directory_id`) REFERENCES nb-stash.cwd_directory(id), CONSTRAINT `FK_APP_DIR_APP` FOREIGN KEY (`application_id`) REFERENCES nb-stash.cwd_application(id)): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '-stash.cwd_directory(id), CONSTRAINT `FK_APP_DIR_APP` FOREIGN KEY (`application_' at line 1:
Caused By: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '-stash.cwd_directory(id), CONSTRAINT `FK_APP_DIR_APP` FOREIGN KEY (`application_' at line 1
liquibase.changelog.ChangeSet.execute(ChangeSet.java:347)
liquibase.changelog.visitor.UpdateVisitor.visit(UpdateVisitor.java:27)
liquibase.changelog.ChangeLogIterator.run(ChangeLogIterator.java:58)
...
Error executing SQL CREATE TABLE `nb-stash`.`cwd_app_dir_mapping` (`id` BIGINT NOT NULL, `application_id` BIGINT NOT NULL, `directory_id` BIGINT NOT NULL, `allow_all` CHARACTER(255) NOT NULL, `list_index` INT NULL, CONSTRAINT `SYS_PK_10077` PRIMARY KEY (`id`), CONSTRAINT `FK_APP_DIR_DIR` FOREIGN KEY (`directory_id`) REFERENCES nb-stash.cwd_directory(id), CONSTRAINT `FK_APP_DIR_APP` FOREIGN KEY (`application_id`) REFERENCES nb-stash.cwd_application(id)): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '-stash.cwd_directory(id), CONSTRAINT `FK_APP_DIR_APP` FOREIGN KEY (`application_' at line 1
liquibase.executor.jvm.JdbcExecutor.execute(JdbcExecutor.java:62)
liquibase.executor.jvm.JdbcExecutor.execute(JdbcExecutor.java:104)
liquibase.database.AbstractDatabase.execute(AbstractDatabase.java:1091)
...
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '-stash.cwd_directory(id), CONSTRAINT `FK_APP_DIR_APP` FOREIGN KEY (`application_' at line 1
sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:57)
sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
...原因
The issue here is that the MySQL documentation indicates that unquoted identifiers can only contain letters, numbers, $ and _. -'s (hyphens) are not supported in unquoted literals. The schema management tool Stash uses (Liquibase) generally does a good job of escaping names for us, but sometimes it misses it. Stash internally uses underscores in all of our names for this exact reason.
ソリューション
A name for the database like nbstash or nb_stash should work.
Just change the database name and reconnect.
Last modified on Mar 30, 2016
Powered by Confluence and Scroll Viewport.