1. org.zstack.core.db.SQL存在的问题
String sql = "SELECT snapshot.uuid" +
" FROM BackupDBSnapshotVO AS snapshot" +
" LEFT JOIN" +
" (SELECT volumeSnapshotUuid, COUNT(volumeUuid) AS copyCount" +
" FROM VolumeSnapshotCloneVO" +
" GROUP BY volumeSnapshotUuid) AS clone" +
" ON snapshot.volumeSnapshotUuid = clone.volumeSnapshotUuid" +
" WHERE snapshot.importance = :importance" +
" AND snapshot.backupDBUuid = :backupDBUuid" +
" AND clone.copyCount is null" +
" ORDER BY snapshot.backupDate LIMIT 1";
List<String> auschwitz = SQL.New(sql)
.param("importance", BackupDBSnapshotImportance.Normal.toString())
.param("backupDBUuid", this.getTargetResourceUuid())
.list();
以上是利用SQL类来执行一个比较复杂的SQL语句, 该语句包含了函数, 子查询, 分组, 排序, 左外连接. 实际执行的时候会报错. 大抵意思是"我Hibernate就是饿死, 死外边, 从这里跳下去, 也不认你这个语法!"
平时写写SELECT FROM这样简单的SQL语句的时候, SQL类君还是比较正常的, 那么这次抽风的问题是在哪呢?
直接将报错原因丢去百度, 看到一句"JPQL语句和SQL原生语句有些不同, 复杂的语法会导致Hibernate无法解析", 根本原因get.
下一步查看源代码, 在SQL类的构造函数中看到这玩意:
private SQL(String sql) {
this.sql = sql;
query = dbf.getEntityManager().createQuery(this.sql);
}
点击createQuery进去一探究竟.
/**
* Create an instance of <code>Query</code> for executing a
* Java Persistence query language statement.
* @param qlString a Java Persistence query string
* @return the new query instance
* @throws IllegalArgumentException if the query string is
* found to be invalid
*/
public Query createQuery(String qlString);
根据介绍可以得知该方法是创建一个JPQL语句的查询实例, 那么不难想到这周围肯定会有创建原生SQL语句的查询实例, 果然:
/**
* Create an instance of <code>Query</code> for executing
* a native SQL statement, e.g., for update or delete.
* @param sqlString a native SQL query string
* @return the new query instance
*/
public Query createNativeQuery(String sqlString);
由此, 根据此方法对一开始的查询语句进行改造:
List<String> auschwitz = dbf.getEntityManager().createNativeQuery(sql)
.setParameter("importance", BackupDBSnapshotImportance.Normal.toString())
.setParameter("backupDBUuid", this.getTargetResourceUuid())
.getResultList();
Hibernate: "真香".
2. *.sql文件中Timestamp类型隐藏的坑点
由于ZStack所应用的数据库依然是5.5.56版本的MariaDB(当前稳定版本是10.3.9), 会有很多潜在的问题.
2.1 建表报错
CREATE TABLE `zstack`.`CornHubVO` (
`uuid` varchar(32) NOT NULL UNIQUE COMMENT 'uuid',
`oldestTime` timestamp,
`latestTime` timestamp,
`lastOpDate` timestamp ON UPDATE CURRENT_TIMESTAMP,
`createDate` timestamp,
PRIMARY KEY (`uuid`),
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
上表是一个虚拟的VO, 只留有必要的字段. 这种表创建语句乍看没有问题, 实际在进行测试的时候:
SQL State : HY000
Error Code : 1293
Message : Incorrect table definition; there can be only one TIMESTAMP column with CURRENT_TIMESTAMP in DEFAULT or ON UPDATE clause
Location : /root/zstack/conf/tools/flyway-3.2.1/sql/V2.3.1.1.1__schema.sql (/root/zstack/conf/tools/flyway-3.2.1/sql/V2.3.1.1.1__schema.sql)
Line : 1
Surprise? 为什么我们只有一个Timestamp设定了CURRENT_TIMESTAMP, 却依然报错? 这其实是Mysql 5.7版本前的一个Bug.
当同时满足:
- 表中有1个以上的Timestamp字段,
- 其中一个Timestamp字段X设定了DEFAULT CURRENT_TIMESTAMP或ON UPDATE CURRENT_TIMESTAMP
- X字段之前有别的Timestamp
三种条件时, 就会触发该Bug.
由于MariaDB 10 才对应到Mysql 5.6, 因此在该版本中, 这个Bug显然是存在的.
介于此, 在不升级版本的情况下, 解决办法有两种:
- 给X以外的所有Timestamp设定默认值, 例如:
CREATE TABLE `zstack`.`CornHubVO` (
`uuid` varchar(32) NOT NULL UNIQUE COMMENT 'uuid',
`oldestTime` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
`latestTime` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
`lastOpDate` timestamp ON UPDATE CURRENT_TIMESTAMP,
`createDate` timestamp,
PRIMARY KEY (`uuid`),
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
- 将X以外所有的Timestamp置于X的后面:
`uuid` varchar(32) NOT NULL UNIQUE COMMENT 'uuid',
`lastOpDate` timestamp ON UPDATE CURRENT_TIMESTAMP,
`createDate` timestamp,
`oldestArchiveTime` timestamp,
`latestArchiveTime` timestamp,
PRIMARY KEY (`uuid`),
2.2 Timestamp的DEFAULT及(NOT) NULL关键字的特殊行为
CREATE TABLE `zstack`.`CornHubVO` (
`uuid` varchar(32) NOT NULL UNIQUE COMMENT 'uuid',
`oldestTime` timestamp NULL DEFAULT '0000-00-00 00:00:00',
`latestTime` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
`lastOpDate` timestamp ON UPDATE CURRENT_TIMESTAMP,
`createDate` timestamp,
PRIMARY KEY (`uuid`),
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
oldestTime设置为NULL, latestTime设置为NOT NULL, 两个字段都设置了默认值
实际插入数据(所有的时间戳字段均未主动赋值)后表中数据如下:
uuid: 746a876edb5f45b3baa3bdf615061393
oldestTime: NULL
latestTime: 2018-08-23 21:04:37
lastOpDate: 2018-08-23 21:04:38
createDate: 2018-08-23 21:04:37
- 可以看出, oldestTime和latestTime默认值本来应该是1970-1-1 08:00:00这样的时间, 实际上没有卵用
- 设定为NULL的oldestTime没有被赋值
- lastOpDate, createDate和设定为NOT NULL的latestTime一样都被赋值为当前时间了(虽然这个赋值行为也在意料之外)
总结:
- Timestamp的Default关键字实际上没有卵用
- 当Timestamp设定为NULL时, 插入数据不赋值的情况下此列值为NULL
- 当Timestamp设定为NOT NULL或不设定的时候, 插入数据不赋值的情况下, 此列会赋值为当前的时间
