[BUGFIX] Check for incompatible SQL modes 37/23237/2
authorSascha Egerer <sascha.egerer@dkd.de>
Thu, 22 Aug 2013 09:32:54 +0000 (11:32 +0200)
committerStefan Neufeind <typo3.neufeind@speedpartner.de>
Thu, 22 Aug 2013 09:40:22 +0000 (11:40 +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/23237
Reviewed-by: Stefan Neufeind
Tested-by: Stefan Neufeind
typo3/sysext/core/Classes/Database/DatabaseConnection.php
typo3/sysext/install/Classes/SystemEnvironment/Check.php

index 71032d4..57e7a38 100644 (file)
@@ -1204,7 +1204,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;
@@ -1219,28 +1218,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 60c48ef..6ee439a 100644 (file)
@@ -89,6 +89,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\SystemEnvironment\StatusInterface>
@@ -115,6 +128,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) {
@@ -209,6 +223,65 @@ class Check {
        }
 
        /**
+        * Check if any SQL mode is set which is not compatible with TYPO3
+        *
+        * @return StatusInterface
+        */
+       public function checkInvalidSqlModes() {
+               try {
+                       $this->loadExtLocalconfDatabase();
+
+                       $sqlModes = $this->getSqlModes();
+               } catch (\RuntimeException $exception) {
+                       $status = new 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 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 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 ErrorStatus|OkStatus
@@ -1190,6 +1263,20 @@ 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();
+       }
 }
-?>
+?>
\ No newline at end of file