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類來執(zhí)行一個比較復(fù)雜的SQL語句, 該語句包含了函數(shù), 子查詢, 分組, 排序, 左外連接. 實際執(zhí)行的時候會報錯. 大抵意思是"我Hibernate就是餓死, 死外邊, 從這里跳下去, 也不認(rèn)你這個語法!"
平時寫寫SELECT FROM這樣簡單的SQL語句的時候, SQL類君還是比較正常的, 那么這次抽風(fēng)的問題是在哪呢?
直接將報錯原因丟去百度, 看到一句"JPQL語句和SQL原生語句有些不同, 復(fù)雜的語法會導(dǎo)致Hibernate無法解析", 根本原因get.
下一步查看源代碼, 在SQL類的構(gòu)造函數(shù)中看到這玩意:
private SQL(String sql) {
this.sql = sql;
query = dbf.getEntityManager().createQuery(this.sql);
}
點擊createQuery進(jìn)去一探究竟.
/**
* 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);
根據(jù)介紹可以得知該方法是創(chuàng)建一個JPQL語句的查詢實例, 那么不難想到這周圍肯定會有創(chuàng)建原生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);
由此, 根據(jù)此方法對一開始的查詢語句進(jìn)行改造:
List<String> auschwitz = dbf.getEntityManager().createNativeQuery(sql)
.setParameter("importance", BackupDBSnapshotImportance.Normal.toString())
.setParameter("backupDBUuid", this.getTargetResourceUuid())
.getResultList();
Hibernate: "真香".
2. *.sql文件中Timestamp類型隱藏的坑點
由于ZStack所應(yīng)用的數(shù)據(jù)庫依然是5.5.56版本的MariaDB(當(dāng)前穩(wěn)定版本是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, 只留有必要的字段. 這種表創(chuàng)建語句乍看沒有問題, 實際在進(jìn)行測試的時候:
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設(shè)定了CURRENT_TIMESTAMP, 卻依然報錯? 這其實是Mysql 5.7版本前的一個Bug.
當(dāng)同時滿足:
- 表中有1個以上的Timestamp字段,
- 其中一個Timestamp字段X設(shè)定了DEFAULT CURRENT_TIMESTAMP或ON UPDATE CURRENT_TIMESTAMP
- X字段之前有別的Timestamp
三種條件時, 就會觸發(fā)該Bug.
由于MariaDB 10 才對應(yīng)到Mysql 5.6, 因此在該版本中, 這個Bug顯然是存在的.
介于此, 在不升級版本的情況下, 解決辦法有兩種:
- 給X以外的所有Timestamp設(shè)定默認(rèn)值, 例如:
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關(guān)鍵字的特殊行為
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設(shè)置為NULL, latestTime設(shè)置為NOT NULL, 兩個字段都設(shè)置了默認(rèn)值
實際插入數(shù)據(jù)(所有的時間戳字段均未主動賦值)后表中數(shù)據(jù)如下:
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默認(rèn)值本來應(yīng)該是1970-1-1 08:00:00這樣的時間, 實際上沒有卵用
- 設(shè)定為NULL的oldestTime沒有被賦值
- lastOpDate, createDate和設(shè)定為NOT NULL的latestTime一樣都被賦值為當(dāng)前時間了(雖然這個賦值行為也在意料之外)
總結(jié):
- Timestamp的Default關(guān)鍵字實際上沒有卵用
- 當(dāng)Timestamp設(shè)定為NULL時, 插入數(shù)據(jù)不賦值的情況下此列值為NULL
- 當(dāng)Timestamp設(shè)定為NOT NULL或不設(shè)定的時候, 插入數(shù)據(jù)不賦值的情況下, 此列會賦值為當(dāng)前的時間
