Merge pull request #1183 from Alkarex/mysql_utf8mb4

Update MySQL to utf8mb4 (full unicode) 🔥 💕

5 files changed, 91 insertions, 26 deletions

diff --git a/app/Controllers/feedController.php b/app/Controllers/feedController.php
index 6a8aa01cf..ffda1450d 100755
--- a/app/Controllers/feedController.php
+++ b/app/Controllers/feedController.php
@@ -200,7 +200,9 @@ class FreshRSS_feed_Controller extends Minz_ActionController {
 				$entryDAO->addEntry($values);
 			}
 			$feedDAO->updateLastUpdate($feed->id());
-			$feedDAO->commit();
+			if ($feedDAO->inTransaction()) {
+				$feedDAO->commit();
+			}
 
 			// Entries are in DB, we redirect to feed configuration page.
 			$url_redirect['params']['id'] = $feed->id();
@@ -364,7 +366,7 @@ class FreshRSS_feed_Controller extends Minz_ActionController {
 								//', old hash ' . $existingHash . ', new hash ' . $entry->hash());
 							//TODO: Make an updated/is_read policy by feed, in addition to the global one.
 							$entry->_isRead(FreshRSS_Context::$user_conf->mark_updated_article_unread ? false : null);	//Change is_read according to policy.
-							if (!$entryDAO->hasTransaction()) {
+							if (!$entryDAO->inTransaction()) {
 								$entryDAO->beginTransaction();
 							}
 							$entryDAO->updateEntry($entry->toArray());
@@ -396,7 +398,7 @@ class FreshRSS_feed_Controller extends Minz_ActionController {
 							$feed->pubSubHubbubError(true);
 						}
 
-						if (!$entryDAO->hasTransaction()) {
+						if (!$entryDAO->inTransaction()) {
 							$entryDAO->beginTransaction();
 						}
 						$entryDAO->addEntry($entry->toArray());
@@ -408,7 +410,7 @@ class FreshRSS_feed_Controller extends Minz_ActionController {
 			if ($feed_history >= 0 && rand(0, 30) === 1) {
 				// TODO: move this function in web cron when available (see entry::purge)
 				// Remove old entries once in 30.
-				if (!$entryDAO->hasTransaction()) {
+				if (!$entryDAO->inTransaction()) {
 					$entryDAO->beginTransaction();
 				}
 
@@ -421,8 +423,8 @@ class FreshRSS_feed_Controller extends Minz_ActionController {
 				}
 			}
 
-			$feedDAO->updateLastUpdate($feed->id(), 0, $entryDAO->hasTransaction());
-			if ($entryDAO->hasTransaction()) {
+			$feedDAO->updateLastUpdate($feed->id(), 0, $entryDAO->inTransaction());
+			if ($entryDAO->inTransaction()) {
 				$entryDAO->commit();
 			}
 
diff --git a/app/Models/EntryDAO.php b/app/Models/EntryDAO.php
index f74055835..c9e6f9742 100644
--- a/app/Models/EntryDAO.php
+++ b/app/Models/EntryDAO.php
@@ -11,7 +11,7 @@ class FreshRSS_EntryDAO extends Minz_ModelPdo implements FreshRSS_Searchable {
 	}
 
 	protected function addColumn($name) {
-		Minz_Log::debug('FreshRSS_EntryDAO::autoAddColumn: ' . $name);
+		Minz_Log::warning('FreshRSS_EntryDAO::addColumn: ' . $name);
 		$hasTransaction = false;
 		try {
 			$stm = null;
@@ -38,7 +38,7 @@ class FreshRSS_EntryDAO extends Minz_ModelPdo implements FreshRSS_Searchable {
 				return $stm && $stm->execute();
 			}
 		} catch (Exception $e) {
-			Minz_Log::debug('FreshRSS_EntryDAO::autoAddColumn error: ' . $e->getMessage());
+			Minz_Log::error('FreshRSS_EntryDAO::addColumn error: ' . $e->getMessage());
 			if ($hasTransaction) {
 				$this->bd->rollBack();
 			}
@@ -46,9 +46,44 @@ class FreshRSS_EntryDAO extends Minz_ModelPdo implements FreshRSS_Searchable {
 		return false;
 	}
 
-	protected function autoAddColumn($errorInfo) {
+	private $triedUpdateToUtf8mb4 = false;
+
+	protected function updateToUtf8mb4() {
+		if ($this->triedUpdateToUtf8mb4) {
+			return false;
+		}
+		$this->triedUpdateToUtf8mb4 = true;
+		$db = FreshRSS_Context::$system_conf->db;
+		if ($db['type'] === 'mysql') {
+			include_once(APP_PATH . '/SQL/install.sql.mysql.php');
+			if (defined('SQL_UPDATE_UTF8MB4')) {
+				Minz_Log::warning('Updating MySQL to UTF8MB4...');
+				$hadTransaction = $this->bd->inTransaction();
+				if ($hadTransaction) {
+					$this->bd->commit();
+				}
+				$ok = false;
+				try {
+					$sql = sprintf(SQL_UPDATE_UTF8MB4, $this->prefix, $db['base']);
+					$stm = $this->bd->prepare($sql);
+					$ok = $stm->execute();
+				} catch (Exception $e) {
+					Minz_Log::error('FreshRSS_EntryDAO::updateToUtf8mb4 error: ' . $e->getMessage());
+				}
+				if ($hadTransaction) {
+					$this->bd->beginTransaction();
+					//NB: Transaction not starting. Why? (tested on PHP 7.0.8-0ubuntu and MySQL 5.7.13-0ubuntu)
+				}
+				return $ok;
+			}
+		}
+		return false;
+	}
+
+	protected function autoUpdateDb($errorInfo) {
 		if (isset($errorInfo[0])) {
-			if ($errorInfo[0] == '42S22') {	//ER_BAD_FIELD_ERROR
+			if ($errorInfo[0] === '42S22') {	//ER_BAD_FIELD_ERROR
+				//autoAddColumn
 				foreach (array('lastSeen', 'hash') as $column) {
 					if (stripos($errorInfo[2], $column) !== false) {
 						return $this->addColumn($column);
@@ -56,6 +91,11 @@ class FreshRSS_EntryDAO extends Minz_ModelPdo implements FreshRSS_Searchable {
 				}
 			}
 		}
+		if (isset($errorInfo[1])) {
+			if ($errorInfo[1] == '1366') {	//ER_TRUNCATED_WRONG_VALUE_FOR_FIELD
+				return $this->updateToUtf8mb4();
+			}
+		}
 		return false;
 	}
 
@@ -94,7 +134,7 @@ class FreshRSS_EntryDAO extends Minz_ModelPdo implements FreshRSS_Searchable {
 			return $this->bd->lastInsertId();
 		} else {
 			$info = $this->addEntryPrepared == null ? array(0 => '', 1 => '', 2 => 'syntax error') : $this->addEntryPrepared->errorInfo();
-			if ($this->autoAddColumn($info)) {
+			if ($this->autoUpdateDb($info)) {
 				return $this->addEntry($valuesTmp);
 			} elseif ((int)($info[0] / 1000) !== 23) {	//Filter out "SQLSTATE Class code 23: Constraint Violation" because of expected duplicate entries
 				Minz_Log::error('SQL error addEntry: ' . $info[0] . ': ' . $info[1] . ' ' . $info[2]
@@ -145,7 +185,7 @@ class FreshRSS_EntryDAO extends Minz_ModelPdo implements FreshRSS_Searchable {
 			return $this->bd->lastInsertId();
 		} else {
 			$info = $this->updateEntryPrepared == null ? array(0 => '', 1 => '', 2 => 'syntax error') : $this->updateEntryPrepared->errorInfo();
-			if ($this->autoAddColumn($info)) {
+			if ($this->autoUpdateDb($info)) {
 				return $this->updateEntry($valuesTmp);
 			}
 			Minz_Log::error('SQL error updateEntry: ' . $info[0] . ': ' . $info[1] . ' ' . $info[2]
@@ -615,7 +655,7 @@ class FreshRSS_EntryDAO extends Minz_ModelPdo implements FreshRSS_Searchable {
 			return $result;
 		} else {
 			$info = $stm == null ? array(0 => '', 1 => '', 2 => 'syntax error') : $stm->errorInfo();
-			if ($this->autoAddColumn($info)) {
+			if ($this->autoUpdateDb($info)) {
 				return $this->listHashForFeedGuids($id_feed, $guids);
 			}
 			Minz_Log::error('SQL error listHashForFeedGuids: ' . $info[0] . ': ' . $info[1] . ' ' . $info[2]
@@ -636,7 +676,7 @@ class FreshRSS_EntryDAO extends Minz_ModelPdo implements FreshRSS_Searchable {
 			return $stm->rowCount();
 		} else {
 			$info = $stm == null ? array(0 => '', 1 => '', 2 => 'syntax error') : $stm->errorInfo();
-			if ($this->autoAddColumn($info)) {
+			if ($this->autoUpdateDb($info)) {
 				return $this->updateLastSeen($id_feed, $guids);
 			}
 			Minz_Log::error('SQL error updateLastSeen: ' . $info[0] . ': ' . $info[1] . ' ' . $info[2]
@@ -692,7 +732,9 @@ class FreshRSS_EntryDAO extends Minz_ModelPdo implements FreshRSS_Searchable {
 	public function optimizeTable() {
 		$sql = 'OPTIMIZE TABLE `' . $this->prefix . 'entry`';	//MySQL
 		$stm = $this->bd->prepare($sql);
-		$stm->execute();
+		if ($stm) {
+			return $stm->execute();
+		}
 	}
 
 	public function size($all = false) {
diff --git a/app/SQL/install.sql.mysql.php b/app/SQL/install.sql.mysql.php
index 0f4e04620..c78839ef7 100644
--- a/app/SQL/install.sql.mysql.php
+++ b/app/SQL/install.sql.mysql.php
@@ -2,17 +2,17 @@
 define('SQL_CREATE_TABLES', '
 CREATE TABLE IF NOT EXISTS `%1$scategory` (
 	`id` SMALLINT NOT NULL AUTO_INCREMENT,	-- v0.7
-	`name` varchar(255) NOT NULL,
+	`name` varchar(191) NOT NULL,
 	PRIMARY KEY (`id`),
 	UNIQUE KEY (`name`)	-- v0.7
-) DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci
+) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci
 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,
 	`category` SMALLINT DEFAULT 0,	-- v0.7
-	`name` varchar(255) NOT NULL,
+	`name` varchar(191) NOT NULL,
 	`website` varchar(255) CHARACTER SET latin1,
 	`description` text,
 	`lastUpdate` int(11) DEFAULT 0,	-- Until year 2038
@@ -30,7 +30,7 @@ CREATE TABLE IF NOT EXISTS `%1$sfeed` (
 	INDEX (`name`),	-- v0.7
 	INDEX (`priority`),	-- v0.7
 	INDEX (`keep_history`)	-- v0.7
-) DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci
+) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci
 ENGINE = INNODB;
 
 CREATE TABLE IF NOT EXISTS `%1$sentry` (
@@ -53,7 +53,7 @@ CREATE TABLE IF NOT EXISTS `%1$sentry` (
 	INDEX (`is_favorite`),	-- v0.7
 	INDEX (`is_read`),	-- v0.7
 	INDEX `entry_lastSeen_index` (`lastSeen`)	-- v1.1.1
-) DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci
+) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci
 ENGINE = INNODB;
 
 INSERT IGNORE INTO `%1$scategory` (id, name) VALUES(1, "%2$s");
@@ -62,3 +62,24 @@ INSERT IGNORE INTO `%1$sfeed` (url, category, name, website, description, ttl) V
 ');
 
 define('SQL_DROP_TABLES', 'DROP TABLES %1$sentry, %1$sfeed, %1$scategory');
+
+define('SQL_UPDATE_UTF8MB4', '
+ALTER DATABASE `%2$s` CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
+
+ALTER TABLE `%1$scategory` CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
+UPDATE `%1$scategory` SET name=SUBSTRING(name,1,190) WHERE LENGTH(name) > 191;
+ALTER TABLE `%1$scategory` MODIFY `name` VARCHAR(191) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL;
+OPTIMIZE TABLE `%1$scategory`;
+
+ALTER TABLE `%1$sfeed` CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
+UPDATE `%1$sfeed` SET name=SUBSTRING(name,1,190) WHERE LENGTH(name) > 191;
+ALTER TABLE `%1$sfeed` MODIFY `name` VARCHAR(191) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL;
+ALTER TABLE `%1$sfeed` MODIFY `description` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
+OPTIMIZE TABLE `%1$sfeed`;
+
+ALTER TABLE `%1$sentry` CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
+ALTER TABLE `%1$sentry` MODIFY `title` VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL;
+ALTER TABLE `%1$sentry` MODIFY `author` VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
+ALTER TABLE `%1$sentry` MODIFY `tags` VARCHAR(1023) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
+OPTIMIZE TABLE `%1$sentry`;
+');
diff --git a/app/install.php b/app/install.php
index 062f66814..df6e1aeec 100644
--- a/app/install.php
+++ b/app/install.php
@@ -19,7 +19,7 @@ if (isset($_GET['step'])) {
 	define('STEP', 0);
 }
 
-define('SQL_CREATE_DB', 'CREATE DATABASE IF NOT EXISTS %1$s DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci;');
+define('SQL_CREATE_DB', 'CREATE DATABASE IF NOT EXISTS %1$s DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;');
 
 if (STEP === 3 && isset($_POST['type'])) {
 	$_SESSION['bd_type'] = $_POST['type'];
@@ -253,7 +253,7 @@ function newPdo() {
 	case 'mysql':
 		$str = 'mysql:host=' . $_SESSION['bd_host'] . ';dbname=' . $_SESSION['bd_base'];
 		$driver_options = array(
-			PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES utf8',
+			PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES utf8mb4',
 		);
 		break;
 	case 'sqlite':
@@ -309,7 +309,7 @@ function checkStep0() {
 }
 
 function checkStep1() {
-	$php = version_compare(PHP_VERSION, '5.3.0') >= 0;
+	$php = version_compare(PHP_VERSION, '5.3.3') >= 0;
 	$minz = file_exists(join_path(LIB_PATH, 'Minz'));
 	$curl = extension_loaded('curl');
 	$pdo_mysql = extension_loaded('pdo_mysql');
@@ -437,7 +437,7 @@ function checkBD() {
 		switch ($_SESSION['bd_type']) {
 		case 'mysql':
 			$driver_options = array(
-				PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES utf8'
+				PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES utf8mb4'
 			);
 
 			try {	// on ouvre une connexion juste pour créer la base si elle n'existe pas
@@ -536,7 +536,7 @@ function printStep1() {
 	<?php if ($res['php'] == 'ok') { ?>
 	<p class="alert alert-success"><span class="alert-head"><?php echo _t('gen.short.ok'); ?></span> <?php echo _t('install.check.php.ok', PHP_VERSION); ?></p>
 	<?php } else { ?>
-	<p class="alert alert-error"><span class="alert-head"><?php echo _t('gen.short.damn'); ?></span> <?php echo _t('install.check.php.nok', PHP_VERSION, '5.3.0'); ?></p>
+	<p class="alert alert-error"><span class="alert-head"><?php echo _t('gen.short.damn'); ?></span> <?php echo _t('install.check.php.nok', PHP_VERSION, '5.3.3'); ?></p>
 	<?php } ?>
 
 	<?php if ($res['minz'] == 'ok') { ?>
diff --git a/app/views/update/checkInstall.phtml b/app/views/update/checkInstall.phtml
index ed3858b56..543ab43de 100644
--- a/app/views/update/checkInstall.phtml
+++ b/app/views/update/checkInstall.phtml
@@ -9,7 +9,7 @@
 	<p class="alert <?php echo $status ? 'alert-success' : 'alert-error'; ?>">
 		<?php
 			if ($key === 'php') {
-				echo _t('admin.check_install.' . $key . '.' . ($status ? 'ok' : 'nok'), PHP_VERSION, '5.3.0');
+				echo _t('admin.check_install.' . $key . '.' . ($status ? 'ok' : 'nok'), PHP_VERSION, '5.3.3');
 			} else {
 				echo _t('admin.check_install.' . $key . '.' . ($status ? 'ok' : 'nok'));
 			}