From 307e6995fec51d368beeada9e1b69c40c3e7d065 Mon Sep 17 00:00:00 2001 From: Alexandre Alapetite Date: Thu, 25 Oct 2018 22:43:13 +0200 Subject: MySQL GUID case sensitive (#2078) * MySQL GUID case sensitive latin1_bin https://github.com/FreshRSS/FreshRSS/issues/2077 * Prepare update for existing bases * Perform DB update during actualize * Reduce frequency slightly * No optimize at the same time * Take advantage of the SQL modifications in 1.12 * Move higher up * Move to purge, which all users can manually call --- app/SQL/install.sql.mysql.php | 17 +++++++++++------ 1 file changed, 11 insertions(+), 6 deletions(-) (limited to 'app/SQL/install.sql.mysql.php') diff --git a/app/SQL/install.sql.mysql.php b/app/SQL/install.sql.mysql.php index 1fc7e44d3..b3353ac95 100644 --- a/app/SQL/install.sql.mysql.php +++ b/app/SQL/install.sql.mysql.php @@ -12,10 +12,10 @@ ENGINE = INNODB; CREATE TABLE IF NOT EXISTS `%1$sfeed` ( `id` SMALLINT NOT NULL AUTO_INCREMENT, -- v0.7 - `url` VARCHAR(511) CHARACTER SET latin1 NOT NULL, + `url` VARCHAR(511) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL, `category` SMALLINT DEFAULT 0, -- v0.7 `name` VARCHAR(' . FreshRSS_DatabaseDAO::LENGTH_INDEX_UNICODE . ') NOT NULL, - `website` VARCHAR(255) CHARACTER SET latin1, + `website` VARCHAR(255) CHARACTER SET latin1 COLLATE latin1_bin, `description` TEXT, `lastUpdate` INT(11) DEFAULT 0, -- Until year 2038 `priority` TINYINT(2) NOT NULL DEFAULT 10, @@ -38,11 +38,11 @@ ENGINE = INNODB; CREATE TABLE IF NOT EXISTS `%1$sentry` ( `id` BIGINT NOT NULL, -- v0.7 - `guid` VARCHAR(760) CHARACTER SET latin1 NOT NULL, -- Maximum for UNIQUE is 767B + `guid` VARCHAR(760) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL, -- Maximum for UNIQUE is 767B `title` VARCHAR(255) NOT NULL, `author` VARCHAR(255), `content_bin` BLOB, -- v0.7 - `link` VARCHAR(1023) CHARACTER SET latin1 NOT NULL, + `link` VARCHAR(1023) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL, `date` INT(11), -- Until year 2038 `lastSeen` INT(11) DEFAULT 0, -- v1.1.1, Until year 2038 `hash` BINARY(16), -- v1.1.1 @@ -66,11 +66,11 @@ INSERT IGNORE INTO `%1$scategory` (id, name) VALUES(1, "%2$s"); define('SQL_CREATE_TABLE_ENTRYTMP', ' CREATE TABLE IF NOT EXISTS `%1$sentrytmp` ( -- v1.7 `id` BIGINT NOT NULL, - `guid` VARCHAR(760) CHARACTER SET latin1 NOT NULL, + `guid` VARCHAR(760) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL, `title` VARCHAR(255) NOT NULL, `author` VARCHAR(255), `content_bin` BLOB, - `link` VARCHAR(1023) CHARACTER SET latin1 NOT NULL, + `link` VARCHAR(1023) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL, `date` INT(11), `lastSeen` INT(11) DEFAULT 0, `hash` BINARY(16), @@ -136,3 +136,8 @@ ALTER TABLE `%1$sentry` MODIFY `author` VARCHAR(255) CHARACTER SET utf8mb4 COLLA ALTER TABLE `%1$sentry` MODIFY `tags` VARCHAR(1023) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; OPTIMIZE TABLE `%1$sentry`; '); + +define('SQL_UPDATE_GUID_LATIN1_BIN', ' -- v1.12 +ALTER TABLE `%1$sentrytmp` MODIFY `guid` VARCHAR(760) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL; +ALTER TABLE `%1$sentry` MODIFY `guid` VARCHAR(760) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL; +'); -- cgit v1.2.3