[BUGFIX] Check for incompatible SQL modes 71/19671/13
authorSascha Egerer <sascha.egerer@dkd.de>
Sat, 6 Apr 2013 13:05:14 +0000 (15:05 +0200)
committerStefan Neufeind <typo3.neufeind@speedpartner.de>
Thu, 22 Aug 2013 09:24:22 +0000 (11:24 +0200)
If the SQL mode "STRICT_ALL_TABLES" is enabled you
will not be able to save most records. (Enable it in
your my.cnf and try to save a tt_content record)

This patch adds a check in the install tool for the
incompatible SQL modes "STRICT_ALL_TABLES" and
"NO_BACKSLASH_ESCAPES".

The automatic fix for "NO_BACKSLASH_ESCAPES" has been
removed because we don't want to change the mysql
environment automatically.

Resolves: #20052
Resolves: #18866
Resolves: #18821
Releases: 6.2, 6.1, 6.0
Change-Id: Ifd2d7901935f06534a273374bf48266916e23698
Reviewed-on: https://review.typo3.org/19671
Reviewed-by: Sascha Egerer
Tested-by: Sascha Egerer
Reviewed-by: Markus Klein
Reviewed-by: Alexander Opitz
Tested-by: Alexander Opitz
Reviewed-by: Stefan Neufeind
Tested-by: Stefan Neufeind
typo3/sysext/core/Classes/Database/DatabaseConnection.php
typo3/sysext/install/Classes/SystemEnvironment/Check.php

index 5ee7fb9..976b939 100644 (file)
@@ -1203,7 +1203,6 @@ class DatabaseConnection {
                                        );
                                }
                        }
-                       $this->setSqlMode();
                } else {
                        // @TODO: This should raise an exception. Would be useful especially to work during installation.
                        $error_msg = $this->link->connect_error;
@@ -1218,28 +1217,6 @@ class DatabaseConnection {
        }
 
        /**
-        * Fixes the SQL mode by unsetting NO_BACKSLASH_ESCAPES if found.
-        *
-        * @return void
-        */
-       protected function setSqlMode() {
-               $resource = $this->sql_query('SELECT @@SESSION.sql_mode;');
-               if ($resource) {
-                       $result = $this->sql_fetch_row($resource);
-                       if (isset($result[0]) && $result[0] && strpos($result[0], 'NO_BACKSLASH_ESCAPES') !== FALSE) {
-                               $modes = array_diff(\TYPO3\CMS\Core\Utility\GeneralUtility::trimExplode(',', $result[0]), array('NO_BACKSLASH_ESCAPES'));
-                               $query = 'SET sql_mode=\'' . $this->link->real_escape_string(implode(',', $modes)) . '\';';
-                               $this->sql_query($query);
-                               \TYPO3\CMS\Core\Utility\GeneralUtility::sysLog(
-                                       'NO_BACKSLASH_ESCAPES could not be removed from SQL mode: ' . $this->sql_error(),
-                                       'Core',
-                                       \TYPO3\CMS\Core\Utility\GeneralUtility::SYSLOG_SEVERITY_ERROR
-                               );
-                       }
-               }
-       }
-
-       /**
         * Select a SQL database
         *
         * @param string $TYPO3_db Deprecated since 6.1, will be removed in two versions. Database to connect to.
index 9079c02..952ef06 100644 (file)
@@ -80,6 +80,19 @@ class Check {
        );
 
        /**
+        * NO_BACKSLASH_ESCAPES  This mode disables the use of the backslash character as an escape character,
+        *                       but we depend on this for escaping.
+        * STRICT_ALL_TABLES     If a value has not the datatype that is expected by the column,
+        *                       mysql will throw an error in this mode.
+        *
+        * @var array<string>
+        */
+       protected $incompatibleSqlModes = array(
+               'NO_BACKSLASH_ESCAPES',
+               'STRICT_ALL_TABLES'
+       );
+
+       /**
         * Get all status information as array with status objects
         *
         * @return array<\TYPO3\CMS\Install\Status\StatusInterface>
@@ -106,6 +119,7 @@ class Check {
                $statusArray[] = $this->checkSuhosinExecutorIncludeWhitelistContainsPhar();
                $statusArray[] = $this->checkSuhosinExecutorIncludeWhitelistContainsVfs();
                $statusArray[] = $this->checkSomePhpOpcodeCacheIsLoaded();
+               $statusArray[] = $this->checkInvalidSqlModes();
                $statusArray[] = $this->checkReflectionDocComment();
                $statusArray[] = $this->checkWindowsApacheThreadStackSize();
                foreach ($this->requiredPhpExtensions as $extension) {
@@ -200,6 +214,65 @@ class Check {
        }
 
        /**
+        * Check if any SQL mode is set which is not compatible with TYPO3
+        *
+        * @return Status\StatusInterface
+        */
+       public function checkInvalidSqlModes() {
+               try {
+                       $this->loadExtLocalconfDatabase();
+
+                       $sqlModes = $this->getSqlModes();
+               } catch (\RuntimeException $exception) {
+                       $status = new Status\WarningStatus();
+                       $status->setTitle('Can\'t check SQL modes!');
+                       $status->setMessage(
+                               'There is a problem with your database connection.
+                               Error message: ' .
+                               $exception->getMessage()
+                       );
+
+                       return $status;
+               }
+
+               $detectedIncompatibleSqlModes = array_intersect($this->incompatibleSqlModes, $sqlModes);
+
+               if (count($detectedIncompatibleSqlModes)) {
+                       $status = new Status\ErrorStatus();
+                       $status->setTitle('Incompatible SQL modes found!');
+                       $status->setMessage(
+                               'There have been incompatible SQL modes detected.' .
+                               ' The mode "' . implode('" and "', $detectedIncompatibleSqlModes) . '"' .
+                               ' is not compatible with TYPO3 CMS.' .
+                               ' You have to change that setting in your MySQL environment' .
+                               ' or in $TYPO3_CONF_VARS[\'SYS\'][\'setDBinit\']'
+                       );
+               } else {
+                       $status = new Status\OkStatus();
+                       $status->setTitle('No incompatible SQL modes found.');
+               }
+
+               return $status;
+       }
+
+       /**
+        * Returns an array with the current sql mode settings
+        *
+        * @return array Contains all configured SQL modes for the current database connection
+        */
+       public function getSqlModes() {
+               $sqlModes = array();
+               $resource = $GLOBALS['TYPO3_DB']->sql_query('SELECT @@SESSION.sql_mode;');
+               if ($resource !== FALSE) {
+                       $result = $GLOBALS['TYPO3_DB']->sql_fetch_row($resource);
+                       if (isset($result[0])) {
+                               $sqlModes = explode(',', $result[0]);
+                       }
+               }
+               return $sqlModes;
+       }
+
+       /**
         * Check maximum post upload size correlates with maximum file upload
         *
         * @return Status\StatusInterface
@@ -1132,5 +1205,21 @@ class Check {
                }
                return $bytes;
        }
+
+       /**
+        * Some actions like the database analyzer and the upgrade wizards need additional
+        * bootstrap actions performed.
+        *
+        * Those actions can potentially fatal if some old extension is loaded that triggers
+        * a fatal in ext_localconf or ext_tables code! Use only if really needed.
+        *
+        * @return void
+        */
+       protected function loadExtLocalconfDatabase() {
+               \TYPO3\CMS\Core\Core\Bootstrap::getInstance()
+                       ->loadTypo3LoadedExtAndExtLocalconf(FALSE)
+                       ->applyAdditionalConfigurationSettings()
+                       ->initializeTypo3DbGlobal();
+       }
 }
 ?>