Bug #36754
closed
SQL warning about collation in the install tool, Upgrade wizard
Added by Oliver Klee about 12 years ago. Updated over 6 years ago.
100%
Description
My TYPO3 installation is running today's master. All my DB tables are using the utf8_unicode_ci collation.
When I log in to the Install Tool and click on "Upgrade Wizard", I get the following SQL warning:
exec_SELECTquery
caller t3lib_DB::exec_SELECTquery
ERROR Illegal mix of collations (utf8_general_ci,IMPLICIT) and (utf8_unicode_ci,IMPLICIT) for operation '<>'
lastBuiltQuery SELECT COUNT FROM tt_content WHERE media <> '' AND CAST AS CHAR) <> media OR (CType = 'uploads' AND select_key != '')
debug_backtrace require(init.php),install/index.php#147 // tx_install->init#346 // tx_install->updateWizard#495 // tx_install->updateWizard_parts#6401 // Tx_Install_Updates_Base->shouldRenderWizard#6442 // Tx_Install_Updates_File_TtContentUploadsUpdateWizard->checkForUpdate#157 // t3lib_DB->exec_SELECTcountRows#111 // t3lib_DB->exec_SELECTquery#337 // t3lib_DB->debug#191
Updated by unkonsorten no-lastname-given almost 11 years ago
Had the same problem. Some of my columns (not tables) in tt_content and pages for some reason utf8_unicode_ci. After changing everything to utf8_general_ci the error message disappeared.
Updated by Alexander Schnitzler over 10 years ago
- Is Regression set to No
I am also having this bug.
My environment looks like this:
show variables like "collation_database";
+--------------------+-----------------+ | Variable_name | Value | +--------------------+-----------------+ | collation_database | utf8_unicode_ci | +--------------------+-----------------+
show table status;
+--------------------+-----------------+ | Name | Collation | +--------------------+-----------------+ | tt_content | utf8_unicode_ci | +--------------------+-----------------+
Create Syntax:
CREATE TABLE `tt_content` ( `uid` int(11) NOT NULL AUTO_INCREMENT, `pid` int(11) NOT NULL DEFAULT '0', `t3ver_oid` int(11) NOT NULL DEFAULT '0', `t3ver_id` int(11) NOT NULL DEFAULT '0', `t3ver_wsid` int(11) NOT NULL DEFAULT '0', `t3ver_label` varchar(255) COLLATE utf8_unicode_ci NOT NULL DEFAULT '', `t3ver_state` tinyint(4) NOT NULL DEFAULT '0', `t3ver_stage` int(11) NOT NULL DEFAULT '0', `t3ver_count` int(11) NOT NULL DEFAULT '0', `t3ver_tstamp` int(11) NOT NULL DEFAULT '0', `t3ver_move_id` int(11) NOT NULL DEFAULT '0', `t3_origuid` int(11) NOT NULL DEFAULT '0', `tstamp` int(11) unsigned NOT NULL DEFAULT '0', `crdate` int(11) unsigned NOT NULL DEFAULT '0', `cruser_id` int(11) unsigned NOT NULL DEFAULT '0', `hidden` tinyint(4) unsigned NOT NULL DEFAULT '0', `sorting` int(11) unsigned NOT NULL DEFAULT '0', `CType` varchar(255) COLLATE utf8_unicode_ci NOT NULL DEFAULT '', `header` varchar(255) COLLATE utf8_unicode_ci NOT NULL DEFAULT '', `header_position` varchar(6) COLLATE utf8_unicode_ci NOT NULL DEFAULT '', `bodytext` mediumtext COLLATE utf8_unicode_ci, `image` text COLLATE utf8_unicode_ci, `imagewidth` mediumint(11) unsigned NOT NULL DEFAULT '0', `imageorient` tinyint(4) unsigned NOT NULL DEFAULT '0', `imagecaption` text COLLATE utf8_unicode_ci, `imagecols` tinyint(4) unsigned NOT NULL DEFAULT '0', `imageborder` tinyint(4) unsigned NOT NULL DEFAULT '0', `media` text COLLATE utf8_unicode_ci, `layout` int(11) unsigned NOT NULL DEFAULT '0', `deleted` tinyint(4) unsigned NOT NULL DEFAULT '0', `cols` int(11) unsigned NOT NULL DEFAULT '0', `records` text COLLATE utf8_unicode_ci, `pages` tinytext COLLATE utf8_unicode_ci, `starttime` int(11) unsigned NOT NULL DEFAULT '0', `endtime` int(11) unsigned NOT NULL DEFAULT '0', `colPos` int(11) unsigned NOT NULL DEFAULT '0', `subheader` varchar(255) COLLATE utf8_unicode_ci NOT NULL DEFAULT '', `spaceBefore` smallint(5) unsigned NOT NULL DEFAULT '0', `spaceAfter` smallint(5) unsigned NOT NULL DEFAULT '0', `fe_group` varchar(100) COLLATE utf8_unicode_ci NOT NULL DEFAULT '0', `header_link` varchar(255) COLLATE utf8_unicode_ci NOT NULL DEFAULT '', `imagecaption_position` varchar(6) COLLATE utf8_unicode_ci NOT NULL DEFAULT '', `image_link` text COLLATE utf8_unicode_ci, `image_zoom` tinyint(3) unsigned NOT NULL DEFAULT '0', `image_noRows` tinyint(3) unsigned NOT NULL DEFAULT '0', `image_effects` tinyint(3) unsigned NOT NULL DEFAULT '0', `image_compression` tinyint(3) unsigned NOT NULL DEFAULT '0', `altText` text COLLATE utf8_unicode_ci, `titleText` text COLLATE utf8_unicode_ci, `longdescURL` text COLLATE utf8_unicode_ci, `header_layout` varchar(30) COLLATE utf8_unicode_ci NOT NULL DEFAULT '0', `text_align` varchar(6) COLLATE utf8_unicode_ci NOT NULL DEFAULT '', `text_face` tinyint(3) unsigned NOT NULL DEFAULT '0', `text_size` tinyint(3) unsigned NOT NULL DEFAULT '0', `text_color` tinyint(3) unsigned NOT NULL DEFAULT '0', `text_properties` tinyint(3) unsigned NOT NULL DEFAULT '0', `menu_type` varchar(30) COLLATE utf8_unicode_ci NOT NULL DEFAULT '0', `list_type` varchar(255) COLLATE utf8_unicode_ci NOT NULL DEFAULT '0', `table_border` tinyint(3) unsigned NOT NULL DEFAULT '0', `table_cellspacing` tinyint(3) unsigned NOT NULL DEFAULT '0', `table_cellpadding` tinyint(3) unsigned NOT NULL DEFAULT '0', `table_bgColor` int(11) unsigned NOT NULL DEFAULT '0', `select_key` varchar(80) COLLATE utf8_unicode_ci NOT NULL DEFAULT '', `sectionIndex` tinyint(3) unsigned NOT NULL DEFAULT '0', `linkToTop` tinyint(3) unsigned NOT NULL DEFAULT '0', `file_collections` text COLLATE utf8_unicode_ci, `filelink_size` tinyint(3) unsigned NOT NULL DEFAULT '0', `filelink_sorting` tinytext COLLATE utf8_unicode_ci NOT NULL, `target` varchar(30) COLLATE utf8_unicode_ci NOT NULL DEFAULT '', `section_frame` int(11) unsigned NOT NULL DEFAULT '0', `date` int(10) unsigned NOT NULL DEFAULT '0', `multimedia` tinytext COLLATE utf8_unicode_ci, `image_frames` int(11) unsigned NOT NULL DEFAULT '0', `recursive` tinyint(3) unsigned NOT NULL DEFAULT '0', `imageheight` mediumint(8) unsigned NOT NULL DEFAULT '0', `rte_enabled` tinyint(4) NOT NULL DEFAULT '0', `sys_language_uid` int(11) NOT NULL DEFAULT '0', `tx_impexp_origuid` int(11) NOT NULL DEFAULT '0', `pi_flexform` mediumtext COLLATE utf8_unicode_ci, `accessibility_title` varchar(30) COLLATE utf8_unicode_ci NOT NULL DEFAULT '', `accessibility_bypass` tinyint(3) unsigned NOT NULL DEFAULT '0', `accessibility_bypass_text` varchar(30) COLLATE utf8_unicode_ci NOT NULL DEFAULT '', `l18n_parent` int(11) NOT NULL DEFAULT '0', `l18n_diffsource` mediumblob, `selected_categories` text COLLATE utf8_unicode_ci, `category_field` varchar(64) COLLATE utf8_unicode_ci NOT NULL DEFAULT '', `tx_phpunit_is_dummy_record` tinyint(1) unsigned NOT NULL DEFAULT '0', `categories` int(11) NOT NULL DEFAULT '0', PRIMARY KEY (`uid`), KEY `t3ver_oid` (`t3ver_oid`,`t3ver_wsid`), KEY `parent` (`pid`,`sorting`), KEY `language` (`l18n_parent`,`sys_language_uid`), KEY `phpunit_dummy` (`tx_phpunit_is_dummy_record`) ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
As you can see there is absolutely no utf8_general_ci in use but my global mysql settings are utf8_general_ci.
So I guess the global settings are used for string comparison. Therefore the used Query has to set the collaction explicitely as such:
SELECT COUNT(uid) FROM tt_content WHERE media <> '' COLLATE utf8_unicode_ci AND CAST(CAST(media AS DECIMAL) AS CHAR) COLLATE utf8_unicode_ci <> media OR (CType = 'uploads' AND select_key != '')
Updated by Kamen Stanev over 10 years ago
Actually I would suggest that the check if the field is only numeric is made outside of the SQL statement, since that way it will be database portable. The collation and collate statement is supported by MySQL but not by PostgreSQL for example. I understand that the other databases are getting little to no attention, but they will never get any if there are such specific statements in the core.
Also I needed like 2 days to investigate why the upload/media content is not migrated, and there was no clear error message in the upgrade wizard what is happening. For a Typo3 newbie it was very frustrating and I am surprised to see that almost nobody had similar problem. Also, this bug is year and a half old, and I think that it should be fixed properly if you want more people to upgrade to the latest version.
Updated by Alexander Opitz over 10 years ago
- Target version set to 6.2.0
- Is Regression changed from No to Yes
Mark this as regression as it hinders some users from upgrade to new TYPO3 versions.
Updated by Thomas Skierlo over 10 years ago
This error even is triggered if all collations are set to utf8_unicode_ci (global, connection, db, table, fields) AND it is triggered even on an empty tt_content table of a fresh install.
It is extremely annoying since no sane person would go one step further with a product if having collation errors in the very beginning.
Updated by Gerrit Code Review about 10 years ago
- Status changed from New to Under Review
Patch set 1 for branch master of project Packages/TYPO3.CMS has been pushed to the review server.
It is available at https://review.typo3.org/28259
Updated by Jigal van Hemert about 10 years ago
The problem is in the collation of the database/table/column combined with the connection settings.
The MySQL manual mentions:
If you use CAST without specifying CHARACTER SET, the resulting character set and collation are defined by the character_set_connection and collation_connection system variables. http://dev.mysql.com/doc/refman/5.5/en/charset-convert.html
In the query media is first cast to DECIMAL and then back to CHAR. Because the decimal value has no character set and collation it will use the settings from the connection.
The connection has the setting UTF-8 and the default collation for the utf8 character set is utf8_general_ci. http://dev.mysql.com/doc/refman/5.5/en/charset-charsets.html
If we can set a collation while setting the character set of the connection in mysqli initialization, the problem can be solved in a nice way.
Updated by Jigal van Hemert about 10 years ago
Further investigation has given me a bit more insight in UTF-8 handling.
utf8_general_ci is a bit faster (ca. 10%) than utf8_unicode_ci. utf8_unicode_ci is more accurate in sorting and comparing (for non-western language, but also handling 'ß' and 'ss', 'Œ' and 'OE', etcetera).
The mysqli functions only allow to set the character set and not the collation, because the C API doesn't have functionality for this and it has nothing to do with the actions the client has to perform.
The utf8 character set in MySQL doesn't support all characters. Since MySQL 5.5.3 there is a utf8mb4 character set which supports a larger part of the unicode set.
By default TYPO3 uses utf8_general_ci. Any other collation on the server can be handled by using ['SYS']['setDBinit']. For example:
['SYS']['setDBinit'] = 'SET NAMES utf8 COLLATE utf8_unicode_ci;'
We'll have to see how we can add some form of support for this in the Install Tool.
Updated by Markus Klein about 10 years ago
So if I see that correctly, it is not unlikely we are forced to introduce the handing of different character sets again for utf8mb4. (at least towards the DB)
For the collation we'd need an additional field in the Step-Installer in step 1.
Updated by Gerrit Code Review almost 10 years ago
Patch set 1 for branch master of project Packages/TYPO3.CMS has been pushed to the review server.
It is available at http://review.typo3.org/32117
Updated by Gerrit Code Review almost 10 years ago
Patch set 2 for branch master of project Packages/TYPO3.CMS has been pushed to the review server.
It is available at http://review.typo3.org/32117
Updated by Gerrit Code Review almost 10 years ago
Patch set 3 for branch master of project Packages/TYPO3.CMS has been pushed to the review server.
It is available at http://review.typo3.org/32117
Updated by Gerrit Code Review almost 10 years ago
Patch set 4 for branch master of project Packages/TYPO3.CMS has been pushed to the review server.
It is available at http://review.typo3.org/32117
Updated by Gerrit Code Review almost 10 years ago
Patch set 5 for branch master of project Packages/TYPO3.CMS has been pushed to the review server.
It is available at http://review.typo3.org/32117
Updated by Gerrit Code Review over 9 years ago
Patch set 1 for branch TYPO3_6-2 of project Packages/TYPO3.CMS has been pushed to the review server.
It is available at http://review.typo3.org/32198
Updated by Anonymous over 9 years ago
- Status changed from Under Review to Resolved
- % Done changed from 0 to 100
Applied in changeset 587f8763f34ca6abfd4db0d994018a91e31533b1.
Updated by Riccardo De Contardi over 6 years ago
- Status changed from Resolved to Closed