java Grails dbmupdate失败：MySQLSyntaxErrorException:您的SQL语法有错误
我使用Grails数据库迁移。在最新的数据库更新中，我创建了下表：
import java.security.MessageDigest
class City {
String id
Date dateCreated
Date lastUpdated
String name
static constraints = {
id maxSize: 16, unique: true
name nullable: false, blank: false
}
static mapping = {
id generator:'assigned'
}
def setIdIfMissing() {
if (!id) {
String uuid = UUID.randomUUID().toString()
MessageDigest sha1 = MessageDigest.getInstance("SHA1")
byte[] digest = sha1.digest(uuid.getBytes())
def tmpId = new BigInteger(1, digest).toString(16)
id = tmpId[0..15] // size of the id
}
}
/**
* Constructor
*/
City() {
setIdIfMissing()
}
}
我用grails dbm-gorm-diff Update4.groovy -add创建了变更集。这就是
databaseChangeLog = {
changeSet(author: "mg (generated)", id: "1437215773652-1") {
createTable(tableName: "city") {
column(name: "id", type: "varchar(16)") {
constraints(nullable: "false", primaryKey: "true", primaryKeyName: "cityPK")
}
column(name: "version", type: "bigint") {
constraints(nullable: "false")
}
column(name: "date_created", type: "datetime") {
constraints(nullable: "false")
}
column(name: "last_updated", type: "datetime") {
constraints(nullable: "false")
}
column(name: "name", type: "varchar(255)") {
constraints(nullable: "false")
}
}
}
changeSet(author: "mg (generated)", id: "1437215773652-2") {
createIndex(indexName: "id_uniq_1437215773559", tableName: "city", unique: "true") {
column(name: "id")
}
}
}
下面是数据库配置。我还使用hibernate空间方言：
dataSource {
pooled = true
jmxExport = true
driverClassName = "com.mysql.jdbc.Driver"
username = "sa"
password = ""
}
hibernate {
cache.use_second_level_cache = true
cache.use_query_cache = false
cache.region.factory_class = 'org.hibernate.cache.SingletonEhCacheRegionFactory' // Hibernate 3
singleSession = true // configure OSIV singleSession mode
flush.mode = 'manual' // OSIV session flush mode outside of transactional context
}
// environment specific settings
environments {
development {
dataSource {
url = "jdbc:mysql://localhost/test?useUnicode=yes&characterEncoding=UTF-8&zeroDateTimeBehavior=convertToNull"
username = "root"
password = ""
dialect = org.hibernatespatial.mysql.MySQLSpatialInnoDBDialect
pooled = true
properties {
maxActive = -1
minEvictableIdleTimeMillis=1800000
timeBetweenEvictionRunsMillis=1800000
numTestsPerEvictionRun=3
testOnBorrow=true
testWhileIdle=true
testOnReturn=true
validationQuery = "select 1"
}
}
}
test {
dataSource {
dbCreate = "update"
url = "jdbc:h2:mem:testDb;MVCC=TRUE;LOCK_TIMEOUT=10000;DB_CLOSE_ON_EXIT=FALSE"
}
}
}
然后我运行``grails dbm update``，这会导致以下错误：
|Starting dbm-update for database root @ jdbc:mysql://localhost/test?useUnicode=yes&characterEncoding=UTF-8&zeroDateTimeBehavior=convertToNull
Error |
2015-07-18 12:41:10,184 [main] ERROR liquibase - Change Set Update4.groovy::1437215773652-1::mg (generated) failed. Error: Error executing SQL CREATE TABLE `city` (`id` VARCHAR(16) NOT NULL, `version` BIGINT NOT NULL, `date_created` DATETIME NOT NULL, `last_updated` DATETIME NOT NULL, `name` VARCHAR(255) NOT NULL, CONSTRAINT `cityPK` PRIMARY KEY (`id`)) type=InnoDB: 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 'type=InnoDB' at line 1
Message: Error executing SQL CREATE TABLE `city` (`id` VARCHAR(16) NOT NULL, `version` BIGINT NOT NULL, `date_created` DATETIME NOT NULL, `last_updated` DATETIME NOT NULL, `name` VARCHAR(255) NOT NULL, CONSTRAINT `cityPK` PRIMARY KEY (`id`)) type=InnoDB: 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 'type=InnoDB' at line 1
Line | Method
->> 62 | execute in liquibase.executor.jvm.JdbcExecutor
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
| 104 | execute in ''
| 1091 | execute . . . . . in liquibase.database.AbstractDatabase
| 1075 | executeStatements in ''
| 317 | execute . . . . . in liquibase.changelog.ChangeSet
| 27 | visit in liquibase.changelog.visitor.UpdateVisitor
| 58 | run . . . . . . . in liquibase.changelog.ChangeLogIterator
| 114 | update in liquibase.Liquibase
| 26 | doCall . . . . . in DbmUpdate$_run_closure1_closure2
| 59 | doCall in _DatabaseMigrationCommon_groovy$_run_closure2_closure11
| 133 | executeInSession in grails.plugin.databasemigration.MigrationUtils
| 51 | doCall in _DatabaseMigrationCommon_groovy$_run_closure2
^ 25 | doCall . . . . . in DbmUpdate$_run_closure1
Caused by MySQLSyntaxErrorException: 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 'type=InnoDB' at line 1
->> 400 | handleNewInstance in com.mysql.jdbc.Util
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
| 383 | getInstance in ''
| 980 | createSQLException in com.mysql.jdbc.SQLError
| 3847 | checkErrorPacket in com.mysql.jdbc.MysqlIO
| 3783 | checkErrorPacket in ''
| 2447 | sendCommand in ''
| 2594 | sqlQueryDirect . in ''
| 2541 | execSQL in com.mysql.jdbc.ConnectionImpl
| 2499 | execSQL . . . . . in ''
| 844 | execute in com.mysql.jdbc.StatementImpl
| 748 | execute . . . . . in ''
| 92 | doInStatement in liquibase.executor.jvm.JdbcExecutor$1ExecuteStatementCallback
| 55 | execute . . . . . in liquibase.executor.jvm.JdbcExecutor
| 104 | execute in ''
| 1091 | execute . . . . . in liquibase.database.AbstractDatabase
| 1075 | executeStatements in ''
| 317 | execute . . . . . in liquibase.changelog.ChangeSet
| 27 | visit in liquibase.changelog.visitor.UpdateVisitor
| 58 | run . . . . . . . in liquibase.changelog.ChangeLogIterator
| 114 | update in liquibase.Liquibase
| 26 | doCall . . . . . in DbmUpdate$_run_closure1_closure2
| 59 | doCall in _DatabaseMigrationCommon_groovy$_run_closure2_closure11
| 133 | executeInSession in grails.plugin.databasemigration.MigrationUtils
| 51 | doCall in _DatabaseMigrationCommon_groovy$_run_closure2
^ 25 | doCall . . . . . in DbmUpdate$_run_closure1
liquibase.exception.MigrationFailedException: Migration failed for change set Update4.groovy::1437215773652-1::mg (generated):
Reason: liquibase.exception.DatabaseException: Error executing SQL CREATE TABLE `city` (`id` VARCHAR(16) NOT NULL, `version` BIGINT NOT NULL, `date_created` DATETIME NOT NULL, `last_updated` DATETIME NOT NULL, `name` VARCHAR(255) NOT NULL, CONSTRAINT `cityPK` PRIMARY KEY (`id`)) type=InnoDB: 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 'type=InnoDB' at line 1:
Caused By: Error executing SQL CREATE TABLE `city` (`id` VARCHAR(16) NOT NULL, `version` BIGINT NOT NULL, `date_created` DATETIME NOT NULL, `last_updated` DATETIME NOT NULL, `name` VARCHAR(255) NOT NULL, CONSTRAINT `cityPK` PRIMARY KEY (`id`)) type=InnoDB: 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 'type=InnoDB' 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 'type=InnoDB' at line 1
at liquibase.changelog.ChangeSet.execute(ChangeSet.java:347)
at liquibase.changelog.visitor.UpdateVisitor.visit(UpdateVisitor.java:27)
at liquibase.changelog.ChangeLogIterator.run(ChangeLogIterator.java:58)
at liquibase.Liquibase.update(Liquibase.java:114)
at DbmUpdate$_run_closure1_closure2.doCall(DbmUpdate:26)
at _DatabaseMigrationCommon_groovy$_run_closure2_closure11.doCall(_DatabaseMigrationCommon_groovy:59)
at grails.plugin.databasemigration.MigrationUtils.executeInSession(MigrationUtils.groovy:133)
at _DatabaseMigrationCommon_groovy$_run_closure2.doCall(_DatabaseMigrationCommon_groovy:51)
at DbmUpdate$_run_closure1.doCall(DbmUpdate:25)
Caused by: liquibase.exception.DatabaseException: Error executing SQL CREATE TABLE `city` (`id` VARCHAR(16) NOT NULL, `version` BIGINT NOT NULL, `date_created` DATETIME NOT NULL, `last_updated` DATETIME NOT NULL, `name` VARCHAR(255) NOT NULL, CONSTRAINT `cityPK` PRIMARY KEY (`id`)) type=InnoDB: 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 'type=InnoDB' at line 1
at liquibase.executor.jvm.JdbcExecutor.execute(JdbcExecutor.java:62)
at liquibase.executor.jvm.JdbcExecutor.execute(JdbcExecutor.java:104)
at liquibase.database.AbstractDatabase.execute(AbstractDatabase.java:1091)
at liquibase.database.AbstractDatabase.executeStatements(AbstractDatabase.java:1075)
at liquibase.changelog.ChangeSet.execute(ChangeSet.java:317)
... 8 more
Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: 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 'type=InnoDB' at line 1
at com.mysql.jdbc.Util.handleNewInstance(Util.java:400)
at com.mysql.jdbc.Util.getInstance(Util.java:383)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:980)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3847)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3783)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2447)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2594)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2541)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2499)
at com.mysql.jdbc.StatementImpl.execute(StatementImpl.java:844)
at com.mysql.jdbc.StatementImpl.execute(StatementImpl.java:748)
at liquibase.executor.jvm.JdbcExecutor$1ExecuteStatementCallback.doInStatement(JdbcExecutor.java:92)
at liquibase.executor.jvm.JdbcExecutor.execute(JdbcExecutor.java:55)
... 12 more
是什么导致了这个错误？我如何防止它
# 1 楼答案
为了让输出更加清晰，我不得不给出以下答案：
第一次尝试使用SQL命令，请参见错误消息中的上述内容：
现在让我们将type=InnoDB更改为engine=InnoDB并再次运行：
它成功了
出于某种原因，SQL生成的create语句应该是engine=InnoDB，而不是type=InnoDB