Use `utf8mb4` as default charset for MySQL about core-bundle HOT 35 CLOSED

Comment by @Toflar
Wednesday Nov 12, 2014 at 11:43 GMT

This issue should get the "up for discussion" flag, I can't answer to it in a simple comment.

from core-bundle.

Comment by @tristanlins
Wednesday Nov 12, 2014 at 12:24 GMT

It seems MySQL 5.4 has absolutely no support anymore:
http://www.mysql.de/support/supportedplatforms/database.html

For ubuntu 12.04/14.04 MySQL 5.5 is the oldes supported version.
For debian 6 MySQL 5.1 is still supported.
Apple OSX only MySQL 5.6 is supported (EOL reached on September, 25, 2014, see the EOL notice).

5.1 has reached is EOL, even it's still under "sustaining" support, see the notice from December 4, 2013:

MySQL 5.1 covered under Oracle Lifetime Sustaining Support
Per Oracle's Lifetime Support policy, as of December 31, 2013, MySQL 5.1 is covered under Oracle Sustaining Support.
Users are encouraged to upgrade to MySQL 5.6.

It seems that 1&1 provides MySQL 5.5, see the console output: https://hilfe-center.1und1.de/datenbanken-c82637/mysql-c82650/bedienung-c82751/mysql-ueber-kommandozeilenumgebung-steuern-a794057.html

Same for strato (have a look at the screenshots): http://www.strato-faq.de/article/567/So-nutzen-Sie-unsere-MySQL-Datenbank.html

from core-bundle.

Comment by @leofeyer
Monday Nov 17, 2014 at 18:34 GMT

Then I think we should consider changing this.

from core-bundle.

Comment by @leofeyer
Monday Nov 17, 2014 at 18:34 GMT

Thanks @tristanlins for your investigation.

from core-bundle.

Comment by @leofeyer
Monday Mar 09, 2015 at 16:29 GMT

Changed in 7de04f3.

from core-bundle.

Comment by @aschempp
Monday Mar 09, 2015 at 17:11 GMT

Won't this make existing databases incompatible?

from core-bundle.

Comment by @leofeyer
Monday Mar 09, 2015 at 18:31 GMT

Hm, you could have a point here. The change does not touch the database, but it will set the default charset of the DB connection to utf8mb4. @contao/developers What do you think?

from core-bundle.

Comment by @ausi
Monday Mar 09, 2015 at 18:56 GMT

AFAIK utf8mb4 should be completely backwards compatible with utf8. The MySQL-Documentation says „You need not worry about converting characters or losing data when upgrading utf8 data from older versions of MySQL“. Also, comparison and string functions should work correct with utf8 and utf8mb4 mixed.

One thing I noticed in an other project when upgrading from utf8 to utf8mb4 was longer index keys, 4 bytes per character instead of 3. A UNIQUE INDEX (something(255)) might be too long. But this should only be a problem if you actually change the charset of the table or column.

from core-bundle.

Comment by @leofeyer
Tuesday Mar 10, 2015 at 19:44 GMT

@ausi Does this problem only occur for unique indices?

from core-bundle.

Comment by @ausi
Tuesday Mar 10, 2015 at 22:16 GMT

The maximum length for non-unique indices is the same, which is 1000 bytes resp. 250 characters for MyISAM. But if you change the charset, the index length is automatically set to the maximum (250) without an error. I also tested it with different SQL modes, so this shouldn’t be a problem.

Do we also want to change the storage engine from MyISAM to InnoDB in Contao 4?

from core-bundle.

Comment by @Toflar
Tuesday Mar 10, 2015 at 22:26 GMT

Do we also want to change the storage engine from MyISAM to InnoDB in Contao 4?

Not sure about BC but we should definitely get rid of MyISAM, it's a pain!

from core-bundle.

Comment by @ausi
Tuesday Mar 10, 2015 at 22:34 GMT

From mysql.com: „FULLTEXT index support for InnoDB tables requires MySQL 5.6.4 or higher.“

AFAIR fulltext index is used in tl_search

from core-bundle.

Comment by @ausi
Tuesday Mar 10, 2015 at 22:37 GMT

Not sure about BC but we should definitely get rid of MyISAM, it's a pain!

I’d also vote for InnoDB

from core-bundle.

Comment by @aschempp
Wednesday Mar 11, 2015 at 05:56 GMT

We should definitely check out InnoDB options!

from core-bundle.

Comment by @leofeyer
Wednesday Mar 11, 2015 at 08:01 GMT

Switching to InnoDB (which I'd greatly appreciate) would also imply to modify the DC_Table driver. InnoDB supports foreign keys to maintain referential integrity, so the driver should no longer try to simulate this.

from core-bundle.

Comment by @Toflar
Wednesday Mar 11, 2015 at 09:04 GMT

Exactly, but it does not hurt if it tries to. We can remove that later :)

from core-bundle.

Comment by @leofeyer
Wednesday Mar 11, 2015 at 09:06 GMT

Not sure about that. Might throw an exception if the record gets removed by the DB before the driver has finished its routines.

from core-bundle.

Comment by @Toflar
Wednesday Mar 11, 2015 at 12:47 GMT

Which only happens when constraints are defined. And this is not the case yet :) I mean, obviously we cannot go for InnoDB and use all its features from day one. But we can at least make sure all users use InnoDB from Contao 4 on and then safely use the features of it in the future :)

from core-bundle.

I have created a separate ticket regarding InnoDB: #188

from core-bundle.

Although the decision has been made I'd like to add that many users have CentOS based Servers with Plesk Panel installed. Most of those configuration doesn't offer mysql 5.5. So they won't be able to use Contao 4. I think that will lock out quite a lot of people from using Contao 4.

from core-bundle.

I think CentOS might be a valid argument. I just noticed that uberspace doesn’t support MySQL 5.5 at the time. @leofeyer maybe we should rethink this decision?

from core-bundle.

Especially if CentOS is used with Plesk, upgrading the database is only something a very skilled person can/will do. Today, a lot of hosters and distros like Debian and CentOS still rely on MySQL 5.1 which will be an issue.

from core-bundle.

Ok, I'm going to revert it.

from core-bundle.

Reverted in eb8e26e.

from core-bundle.

How about progressively enhancing the connection if the DB supports it? Based on the comments above, the compat issue is minimal/non-existant, and up-to-date systems can use the enhanced mode.

from core-bundle.

@fbender You can do that for your installation of Contao by setting doctrine.dbal.connections.default.charset to UTF8MB4 in app/config/config.yml and the following in your system/config/localconfig.php

$GLOBALS['TL_CONFIG']['dbCharset']   = 'utf8mb4';
$GLOBALS['TL_CONFIG']['dbCollation'] = 'utf8mb4_general_ci';

from core-bundle.

I know that ;) – this is about the default setting. And for the default, I'm proposing a progressive enhancement (or graceful degradation) when the system supports the new setting.

from core-bundle.

I don’t think that this would be easy to detect and I’m not sure if it is a good approach to automatically change the charset if someone updates the database.

from core-bundle.

Of course this should only be set during (first) installation. Could be as easy as trying to connect to MySQL with the new charset in the install tool, watch it fail and switch to the fallback default value without mb4. So, graceful degradation.

from core-bundle.

I guess we will run into compat issues then. Imagine I setup my installation on localhost, where I (always) have the latest software. Then I wont be able to launch it on the webserver with older MySQL.

from core-bundle.

You still have to install the system on your server, don't you? Im-/exporting database content is not affected by this.

from core-bundle.

Im-/exporting database content is not affected by this.

It is affected in the use case @aschempp described, importing utf8mb4 data to a utf8 table could break.

from core-bundle.

I see. I was assuming the charset encoding only applies to the transport channel and not the stored data – is it not?

from core-bundle.

AFAIK dbCharset is used for the CREATE TABLE statements and therefore for the stored data.

from core-bundle.

Eventually implemented in 26fffa9.

from core-bundle.