ZStack的SQL相關(guān)內(nèi)容踩坑一覽

1. org.zstack.core.db.SQL存在的問(wèn)題

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類來(lái)執(zhí)行一個(gè)比較復(fù)雜的SQL語(yǔ)句, 該語(yǔ)句包含了函數(shù), 子查詢, 分組, 排序, 左外連接. 實(shí)際執(zhí)行的時(shí)候會(huì)報(bào)錯(cuò). 大抵意思是"我Hibernate就是餓死, 死外邊, 從這里跳下去, 也不認(rèn)你這個(gè)語(yǔ)法!"
平時(shí)寫寫SELECT FROM這樣簡(jiǎn)單的SQL語(yǔ)句的時(shí)候, SQL類君還是比較正常的, 那么這次抽風(fēng)的問(wèn)題是在哪呢?
直接將報(bào)錯(cuò)原因丟去百度, 看到一句"JPQL語(yǔ)句和SQL原生語(yǔ)句有些不同, 復(fù)雜的語(yǔ)法會(huì)導(dǎo)致Hibernate無(wú)法解析", 根本原因get.
下一步查看源代碼, 在SQL類的構(gòu)造函數(shù)中看到這玩意:

    private SQL(String sql) {
        this.sql = sql;
        query = dbf.getEntityManager().createQuery(this.sql);
    }

點(diǎn)擊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)建一個(gè)JPQL語(yǔ)句的查詢實(shí)例, 那么不難想到這周圍肯定會(huì)有創(chuàng)建原生SQL語(yǔ)句的查詢實(shí)例, 果然:

    /**
     * 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ù)此方法對(duì)一開(kāi)始的查詢語(yǔ)句進(jìn)行改造:

List<String> auschwitz = dbf.getEntityManager().createNativeQuery(sql)
                .setParameter("importance", BackupDBSnapshotImportance.Normal.toString())
                .setParameter("backupDBUuid", this.getTargetResourceUuid())
                .getResultList();

Hibernate: "真香".

2. *.sql文件中Timestamp類型隱藏的坑點(diǎn)

由于ZStack所應(yīng)用的數(shù)據(jù)庫(kù)依然是5.5.56版本的MariaDB(當(dāng)前穩(wěn)定版本是10.3.9), 會(huì)有很多潛在的問(wèn)題.

2.1 建表報(bào)錯(cuò)

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;

上表是一個(gè)虛擬的VO, 只留有必要的字段. 這種表創(chuàng)建語(yǔ)句乍看沒(méi)有問(wèn)題, 實(shí)際在進(jìn)行測(cè)試的時(shí)候:

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? 為什么我們只有一個(gè)Timestamp設(shè)定了CURRENT_TIMESTAMP, 卻依然報(bào)錯(cuò)? 這其實(shí)是Mysql 5.7版本前的一個(gè)Bug.
當(dāng)同時(shí)滿足:

  1. 表中有1個(gè)以上的Timestamp字段,
  2. 其中一個(gè)Timestamp字段X設(shè)定了DEFAULT CURRENT_TIMESTAMP或ON UPDATE CURRENT_TIMESTAMP
  3. X字段之前有別的Timestamp

三種條件時(shí), 就會(huì)觸發(fā)該Bug.
由于MariaDB 10 才對(duì)應(yīng)到Mysql 5.6, 因此在該版本中, 這個(gè)Bug顯然是存在的.
介于此, 在不升級(jí)版本的情況下, 解決辦法有兩種:

  1. 給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;
  1. 將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, 兩個(gè)字段都設(shè)置了默認(rèn)值
實(shí)際插入數(shù)據(jù)(所有的時(shí)間戳字段均未主動(dòng)賦值)后表中數(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
  1. 可以看出, oldestTime和latestTime默認(rèn)值本來(lái)應(yīng)該是1970-1-1 08:00:00這樣的時(shí)間, 實(shí)際上沒(méi)有卵用
  2. 設(shè)定為NULL的oldestTime沒(méi)有被賦值
  3. lastOpDate, createDate和設(shè)定為NOT NULL的latestTime一樣都被賦值為當(dāng)前時(shí)間了(雖然這個(gè)賦值行為也在意料之外)

總結(jié):

  1. Timestamp的Default關(guān)鍵字實(shí)際上沒(méi)有卵用
  2. 當(dāng)Timestamp設(shè)定為NULL時(shí), 插入數(shù)據(jù)不賦值的情況下此列值為NULL
  3. 當(dāng)Timestamp設(shè)定為NOT NULL或不設(shè)定的時(shí)候, 插入數(shù)據(jù)不賦值的情況下, 此列會(huì)賦值為當(dāng)前的時(shí)間
最后編輯于 ：
?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請(qǐng)聯(lián)系作者
【社區(qū)內(nèi)容提示】社區(qū)部分內(nèi)容疑似由AI輔助生成，瀏覽時(shí)請(qǐng)結(jié)合常識(shí)與多方信息審慎甄別。
平臺(tái)聲明：文章內(nèi)容（如有圖片或視頻亦包括在內(nèi)）由作者上傳并發(fā)布，文章內(nèi)容僅代表作者本人觀點(diǎn)，簡(jiǎn)書系信息發(fā)布平臺(tái)，僅提供信息存儲(chǔ)服務(wù)。

相關(guān)閱讀更多精彩內(nèi)容

友情鏈接更多精彩內(nèi)容