http://forge.typo3.org/http://forge.typo3.org/themes/typo3_forge/favicon/favicon.png?17058661692013-07-09T16:29:31ZTYPO3 ForgeTYPO3 Core - Bug #36754: SQL warning about collation in the install tool, Upgrade wizardhttp://forge.typo3.org/issues/36754?journal_id=1726672013-07-09T16:29:31Zunkonsorten no-lastname-givenmail-an@undkonsorten.com
<ul></ul><p>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.</p> TYPO3 Core - Bug #36754: SQL warning about collation in the install tool, Upgrade wizardhttp://forge.typo3.org/issues/36754?journal_id=1912742013-11-22T11:16:45ZAlexander Schnitzler
<ul><li><strong>Is Regression</strong> set to <i>No</i></li></ul><p>I am also having this bug.</p>
<p>My environment looks like this:</p>
<p>show variables like "collation_database";<br /><pre>
+--------------------+-----------------+
| Variable_name | Value |
+--------------------+-----------------+
| collation_database | utf8_unicode_ci |
+--------------------+-----------------+
</pre></p>
<p>show table status;<br /><pre>
+--------------------+-----------------+
| Name | Collation |
+--------------------+-----------------+
| tt_content | utf8_unicode_ci |
+--------------------+-----------------+
</pre></p>
<p>Create Syntax:</p>
<pre>
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;
</pre>
<p>As you can see there is absolutely no utf8_general_ci in use but my global mysql settings are utf8_general_ci.<br />So I guess the global settings are used for string comparison. Therefore the used Query has to set the collaction explicitely as such:</p>
<pre>
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 != '')
</pre> TYPO3 Core - Bug #36754: SQL warning about collation in the install tool, Upgrade wizardhttp://forge.typo3.org/issues/36754?journal_id=1923642013-12-01T13:57:27ZKamen Stanevhambai@gmail.com
<ul></ul><p>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.</p>
<p>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.</p> TYPO3 Core - Bug #36754: SQL warning about collation in the install tool, Upgrade wizardhttp://forge.typo3.org/issues/36754?journal_id=1992492014-01-15T09:36:34ZAlexander Opitzopitz.alexander@googlemail.com
<ul><li><strong>Target version</strong> set to <i>6.2.0</i></li><li><strong>Is Regression</strong> changed from <i>No</i> to <i>Yes</i></li></ul><p>Mark this as regression as it hinders some users from upgrade to new TYPO3 versions.</p> TYPO3 Core - Bug #36754: SQL warning about collation in the install tool, Upgrade wizardhttp://forge.typo3.org/issues/36754?journal_id=1992562014-01-15T10:08:31ZThomas Skierloskierlo@planet.nl
<ul></ul><p>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.</p>
<p>It is extremely annoying since no sane person would go one step further with a product if having collation errors in the very beginning.</p> TYPO3 Core - Bug #36754: SQL warning about collation in the install tool, Upgrade wizardhttp://forge.typo3.org/issues/36754?journal_id=2075622014-03-11T16:09:23ZGerrit Code Review
<ul><li><strong>Status</strong> changed from <i>New</i> to <i>Under Review</i></li></ul><p>Patch set 1 for branch <strong>master</strong> of project <strong>Packages/TYPO3.CMS</strong> has been pushed to the review server.<br />It is available at <a class="external" href="https://review.typo3.org/28259">https://review.typo3.org/28259</a></p> TYPO3 Core - Bug #36754: SQL warning about collation in the install tool, Upgrade wizardhttp://forge.typo3.org/issues/36754?journal_id=2077002014-03-12T00:08:18ZJigal van Hemertjigal.van.hemert@typo3.org
<ul></ul><p>The problem is in the collation of the database/table/column combined with the connection settings.</p>
<p>The MySQL manual mentions:<br />If you use <abbr title="">CAST</abbr> without specifying CHARACTER SET, the resulting character set and collation are defined by the character_set_connection and collation_connection system variables. <a class="external" href="http://dev.mysql.com/doc/refman/5.5/en/charset-convert.html">http://dev.mysql.com/doc/refman/5.5/en/charset-convert.html</a></p>
<p>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.<br />The connection has the setting UTF-8 and the default collation for the utf8 character set is utf8_general_ci. <a class="external" href="http://dev.mysql.com/doc/refman/5.5/en/charset-charsets.html">http://dev.mysql.com/doc/refman/5.5/en/charset-charsets.html</a></p>
<p>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.</p> TYPO3 Core - Bug #36754: SQL warning about collation in the install tool, Upgrade wizardhttp://forge.typo3.org/issues/36754?journal_id=2077352014-03-12T09:39:31ZJigal van Hemertjigal.van.hemert@typo3.org
<ul></ul><p>Further investigation has given me a bit more insight in UTF-8 handling.</p>
<p>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).</p>
<p>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.</p>
<p>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.</p>
<p>By default TYPO3 uses utf8_general_ci. Any other collation on the server can be handled by using ['SYS']['setDBinit']. For example:<br />['SYS']['setDBinit'] = 'SET NAMES utf8 COLLATE utf8_unicode_ci;'</p>
<p>We'll have to see how we can add some form of support for this in the Install Tool.</p> TYPO3 Core - Bug #36754: SQL warning about collation in the install tool, Upgrade wizardhttp://forge.typo3.org/issues/36754?journal_id=2077552014-03-12T10:48:31ZMarkus Kleinmarkus.klein@typo3.org
<ul></ul><p>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)</p>
<p>For the collation we'd need an additional field in the Step-Installer in step 1.</p> TYPO3 Core - Bug #36754: SQL warning about collation in the install tool, Upgrade wizardhttp://forge.typo3.org/issues/36754?journal_id=2270382014-08-13T00:28:40ZGerrit Code Review
<ul></ul><p>Patch set 1 for branch <strong>master</strong> of project <strong>Packages/TYPO3.CMS</strong> has been pushed to the review server.<br />It is available at <a class="external" href="http://review.typo3.org/32117">http://review.typo3.org/32117</a></p> TYPO3 Core - Bug #36754: SQL warning about collation in the install tool, Upgrade wizardhttp://forge.typo3.org/issues/36754?journal_id=2270392014-08-13T00:33:41ZGerrit Code Review
<ul></ul><p>Patch set 2 for branch <strong>master</strong> of project <strong>Packages/TYPO3.CMS</strong> has been pushed to the review server.<br />It is available at <a class="external" href="http://review.typo3.org/32117">http://review.typo3.org/32117</a></p> TYPO3 Core - Bug #36754: SQL warning about collation in the install tool, Upgrade wizardhttp://forge.typo3.org/issues/36754?journal_id=2270402014-08-13T00:35:57ZGerrit Code Review
<ul></ul><p>Patch set 3 for branch <strong>master</strong> of project <strong>Packages/TYPO3.CMS</strong> has been pushed to the review server.<br />It is available at <a class="external" href="http://review.typo3.org/32117">http://review.typo3.org/32117</a></p> TYPO3 Core - Bug #36754: SQL warning about collation in the install tool, Upgrade wizardhttp://forge.typo3.org/issues/36754?journal_id=2270512014-08-13T11:05:27ZGerrit Code Review
<ul></ul><p>Patch set 4 for branch <strong>master</strong> of project <strong>Packages/TYPO3.CMS</strong> has been pushed to the review server.<br />It is available at <a class="external" href="http://review.typo3.org/32117">http://review.typo3.org/32117</a></p> TYPO3 Core - Bug #36754: SQL warning about collation in the install tool, Upgrade wizardhttp://forge.typo3.org/issues/36754?journal_id=2272832014-08-15T00:17:27ZGerrit Code Review
<ul></ul><p>Patch set 5 for branch <strong>master</strong> of project <strong>Packages/TYPO3.CMS</strong> has been pushed to the review server.<br />It is available at <a class="external" href="http://review.typo3.org/32117">http://review.typo3.org/32117</a></p> TYPO3 Core - Bug #36754: SQL warning about collation in the install tool, Upgrade wizardhttp://forge.typo3.org/issues/36754?journal_id=2273202014-08-15T20:51:06ZGerrit Code Review
<ul></ul><p>Patch set 1 for branch <strong>TYPO3_6-2</strong> of project <strong>Packages/TYPO3.CMS</strong> has been pushed to the review server.<br />It is available at <a class="external" href="http://review.typo3.org/32198">http://review.typo3.org/32198</a></p> TYPO3 Core - Bug #36754: SQL warning about collation in the install tool, Upgrade wizardhttp://forge.typo3.org/issues/36754?journal_id=2273212014-08-15T21:00:13ZAnonymous
<ul><li><strong>Status</strong> changed from <i>Under Review</i> to <i>Resolved</i></li><li><strong>% Done</strong> changed from <i>0</i> to <i>100</i></li></ul><p>Applied in changeset <a class="changeset" title="[BUGFIX] "Illegal mix of collations" in Upgrade Wizard When directly comparing CAST(... AS CHAR)..." href="http://forge.typo3.org/projects/typo3cms-core/repository/1749/revisions/587f8763f34ca6abfd4db0d994018a91e31533b1">587f8763f34ca6abfd4db0d994018a91e31533b1</a>.</p> TYPO3 Core - Bug #36754: SQL warning about collation in the install tool, Upgrade wizardhttp://forge.typo3.org/issues/36754?journal_id=3475172017-10-23T21:48:29ZRiccardo De Contardierredeco@gmail.com
<ul><li><strong>Status</strong> changed from <i>Resolved</i> to <i>Closed</i></li></ul>