Bug #41596
closed
Content of $TYPO3_CONF_VARS['SYS']['setDBinit'] is completely overruled / ignored
100%
Description
The Install tool of 4.7 still offers an option to enter a value for ['SYS']['setDBinit'] and the value entered here is written into localconf.php without any restriction or modification.
But that value is then completely ignored by the core and always internally overwritten with 'SET NAMES utf8;'.
While the intention behind this is clear (TYPO3 forces UTF-8 since 4.7) there is a severe flaw in this logic:
setDBinit is by far not only needed to set the character set of the DB communication.
setDBinit is used for ANY initial configuration of the MySQL DB and is very much needed beyond any "SET NAMES" clauses!
Example¶
One of the most important examples of usages of setDBinit would be to switch MySQL 5.1 from strict mode to compatibility mode using this setting:
$TYPO3_CONF_VARS['SYS']['setDBinit'] = "SET SESSION sql_mode='MYSQL40';";
Strict mode is recommended by MySQL 5.1 documentation so this should be the standard for any current MySQL installation. TYPO3 should not expect the whole webserver to be configured in a deprecated MySQL 4.0 compatibility mode. Using setDBinit is the ONLY way to configure MySQL in compatibility mode for TYPO3 while keeping strict mode as the standard for all other installations on the same server.
Without this possibility there is no way to get TYPO3 to run on a modern MySQL setup as the backend login will always fail in strict mode.
Background¶
In /t3lib/config_default.php the following block checks the ['SYS']['setDBinit'] config var:
if (isset($TYPO3_CONF_VARS['SYS']['setDBinit']) &&
$TYPO3_CONF_VARS['SYS']['setDBinit'] !== '-1' &&
preg_match('/SET NAMES utf8/', $TYPO3_CONF_VARS['SYS']['setDBinit']) === FALSE &&
TYPO3_enterInstallScript !== '1'
) {
// Only accept "SET NAMES utf8" for this setting, otherwise die with a nice error
die('This TYPO3 installation is using the $TYPO3_CONF_VARS[\'SYS\'][\'setDBinit\'] property with the following value:' . chr(10) .
$TYPO3_CONF_VARS['SYS']['setDBinit'] . chr(10) . chr(10) .
'It looks like UTF-8 is not used for this connection.' . chr(10) . chr(10) .
'Everything other than UTF-8 is unsupported since TYPO3 4.7.' . chr(10) .
'The DB, its connection and TYPO3 should be migrated to UTF-8 therefore. Please check your setup.');
} else {
$TYPO3_CONF_VARS['SYS']['setDBinit'] = 'SET NAMES utf8;';
}
As you can see, there are only two possible outcomes:
1. An error is thrown
2. The value of ['SYS']['setDBinit'] is overwritten with 'SET NAMES utf8;'
Solution¶
The code block above must be modified to check for "SET NAMES" clauses. Any such clauses can be rejected altogether (read: throw an error) as 'SET NAMES utf8;' is set by default and is the only option allowed.
After this test has passed successfully, the standard 'SET NAMES utf8;' clause must be added (NOT replace!!!) to any existing setDBinit content. This will allow to use commands like the above "SET SESSION sql_mode" while still forcing the UTF-8 setting of modern TYPO3 versions.
I will post a corresponding patch asap.
Files
Updated by Jörg Wagner over 11 years ago
Patch attached.
Updated by Markus Klein over 11 years ago
Just one question: Why do you need to run TYPO3 4.7 in compat mode??
Updated by Jörg Wagner over 11 years ago
I don't need to run TYPO3 in compat mode. I need to run MySQL in compat mode.
MySQL starting with v5.0 defaults to a so called "strict_mode" which conflicts with TYPO3. Typical symptom is that you cannot log into TYPO3 BE anymore. You may search Google for "typo3 sql_mode" to find many discussions of this problem.
There is only one way to run TYPO3 on MySQL 5.x:
You must change the MySQL mode from strict to "MYSQL40" - which is a MySQL 4.0 compatibility mode. This is not recommended according to MySQL docs. (See http://dev.mysql.com/doc/refman/5.1/en/server-sql-mode.html for details on MySQL server modes.)
Changing the MySQL mode can be done either system wide through MySQL's my.cfg/my.ini, or on a per-project basis by sending "SET SESSION sql_mode=MYSQL40" to the database after opening it. Doing it per-project is the way to go. This allows other projects on the same server to still use the modern and recommended strict_mode.
But basically I have only posted this as one of many examples why it is imperative to keep TYPO3's setDBinit conf var available beyond its deprecated "SET NAMES" usage. setDBinit is needed for all initial DB set-up tasks that may be necessary due to the underlying server configuration.
Updated by Markus Klein over 11 years ago
Hi!
I fully agree that the behaviour of the code has to be changed. Would you mind to push your change request to Gerrit?
http://wiki.typo3.org/Contribution_Walkthrough_with_CommandLine
I had a closer look now, and funnily we're running some websites on a MySQL 5.1.49 server compiled from source having the default value for sql_mode, namely empty string. We didn't encounter any problem with TYPO3 4.5 up to 6.0.
As the manual states, the MYSQL40 mode only changes the parsing behaviour of the NOT operator and the output format of the SHOW CREATE TABLE command.
I'm really wondering what the problem here is.
Maybe your distribution ships MySQL with some other sql_mode server setting, which causes the problems.
It would be worth digging deeper into this stuff, finding the actual sql_mode setting making TYPO3 fail.
Regards
Markus
Updated by Jörg Wagner over 11 years ago
Hmmmm, interesting.
I am running a WAMP configuration for development.
When installing MySQL 5.5 (my current version) on Windows, a "MySQL Server Instance Configuration Wizard" is started automatically. It helps to configure MySQL using a graphical interface. This wizard explicitly recommends to activate strict mode:
If you keep this option checked, you end up with the following setting in my.ini:
sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
This setup leads to the BE login problem in TYPO3 as described above (and even without this show stopper TYPO3 would be largely incompatible with this strict mode setup). I did some more testing and the culprit is definitely STRICT_TRANS_TABLES. (This is actually also the setting that makes strict_mode interesting and important for clean DB development - but that would be another discussion.)
Now about "MYSQL40":
Setting sql-mode to "MYSQL40" does not only set the server mode options "NO_FIELD_OPTIONS" and "HIGH_NOT_PRECEDENCE", but it also removes all prior sql-mode settings. This is the reason, why MYSQL40 makes TYPO3 run: It simply trashes all other sql-mode settings (incl. "STRICT_TRANS_TABLES") and replaces them!
After your posting I have tried to reduce my localconf.php entry to an empty sql_mode...
$TYPO3_CONF_VARS['SYS']['setDBinit'] = "SET SESSION sql_mode='';";...and it works perfectly!
Isn't it funny, that the "MYSQL40" solution is propagating all over the web, although a simple purging of the sql_mode would be all that is needed?!?
BTW: You may have noticed that there is a strange inconsistency of sql-mode vs. sql_mode (dash vs. underscore). In my.ini it is sql-mode, in SET SESSION it is sql_mode.
Updated by Jörg Wagner over 11 years ago
Oh yes, and I will try to push to Gerrit if I manage to master that beast. I have not used Gerrit so far.
Updated by Markus Klein over 11 years ago
I was compiling mysql from source on command line only. No wizards and so on. Therefore, sql_mode is empty. ;-)
Addition to your notes: NO_ENGINE_SUBSTITUTION will also fail the installer, since InnoDB is used for the cache tables and has to be substituted transparently, if InnoDB is not available on the server.
It not so much about handling Gerrit, it's more about juggling GIT. Once you've set up your GIT repo, you just push your changes to Gerrit.
If you need help, just ask.
Thanks.
Updated by Gerrit Code Review over 11 years ago
- Status changed from New to Under Review
Patch set 1 for branch TYPO3_4-7 has been pushed to the review server.
It is available at http://review.typo3.org/15338
Updated by Gerrit Code Review over 11 years ago
Patch set 1 for branch master has been pushed to the review server.
It is available at http://review.typo3.org/15369
Updated by Gerrit Code Review over 11 years ago
Patch set 1 for branch master has been pushed to the review server.
It is available at http://review.typo3.org/15457
Updated by Alexander Stehlik about 11 years ago
I can not see why the setDBinit value needs to me modified at all when SET NAMES utf8 is present.
In my case my statement needs to look liks SET NAMES utf8 COLLATE utf8_unicode_ci. Let me explain why:
My database server is running with the utf8_unicode_ci collation and all database are running in this collation, too.
After executing SET NAMES utf8; without the COLLATE option the collation of the connection defaults to utf8_general_ci (no idea if this is a MySQL Bug):
mysql> show session variables like 'collation_connection'; +----------------------+-----------------+ | Variable_name | Value | +----------------------+-----------------+ | collation_connection | utf8_unicode_ci | +----------------------+-----------------+ mysql> set names utf8; mysql> show session variables like 'collation_connection'; +----------------------+-----------------+ | Variable_name | Value | +----------------------+-----------------+ | collation_connection | utf8_general_ci | +----------------------+-----------------+
So can't we just get rid of the else part when checking setDBinit?
Updated by Gerrit Code Review about 11 years ago
Patch set 2 for branch master has been pushed to the review server.
It is available at https://review.typo3.org/15369
Updated by Ernesto Baschny about 11 years ago
@Alexander, the defaults in mysql come from the internal table INFORMATION_SCHEMA.CHARACTER_SETS and INFORMATION_SCHEMA.COLLATE. I am not sure if these can be changed with regular UPDATE statements, you can try. See also this unanswered question: http://forums.mysql.com/read.php?103,296418,296418
If you check out the proposed patch for our core in http://review.typo3.org/15369 you will notice that the "else" part is not there anymore. So it should work in your situation. If this is the case and you are confortable with PHP code, you could review the patch and comment or even vote on it. This could be added in 4.7, 6.0 and 6.1 if it fixes that particular bug.
Thanks for your help!
Updated by Gerrit Code Review about 11 years ago
Patch set 3 for branch master has been pushed to the review server.
It is available at https://review.typo3.org/15369
Updated by Gerrit Code Review about 11 years ago
Patch set 4 for branch master has been pushed to the review server.
It is available at https://review.typo3.org/15369
Updated by Gerrit Code Review about 11 years ago
Patch set 5 for branch master has been pushed to the review server.
It is available at https://review.typo3.org/15369
Updated by Ernesto Baschny about 11 years ago
Thanks for your new patch, Alexander.
Thinking about it, for a more general approach (maybe for 6.2) we could skip the check of "setDBinit" altogether and do the check after connecting to the DB, by checking "show variables where Variable_name='character_set_database'" once the connection is done. This way it would be more generic, as the default character set could also be set directly in the mysql settings, and this would also work.
Updated by Alexander Stehlik about 11 years ago
Thanks for your feedback Ernesto. I already had a discussion about this with Markus Klein in the review system (https://review.typo3.org/15369).
It would be great to find a cleaner solution. At the moment it seems a bit dirty, also because of the strange behavior of the install tool.
Updated by Gerrit Code Review about 11 years ago
Patch set 6 for branch master has been pushed to the review server.
It is available at https://review.typo3.org/15369
Updated by Gerrit Code Review about 11 years ago
Patch set 7 for branch master has been pushed to the review server.
It is available at https://review.typo3.org/15369
Updated by Gerrit Code Review about 11 years ago
Patch set 8 for branch master has been pushed to the review server.
It is available at https://review.typo3.org/15369
Updated by Gerrit Code Review about 11 years ago
Patch set 9 for branch master has been pushed to the review server.
It is available at https://review.typo3.org/15369
Updated by Gerrit Code Review almost 11 years ago
Patch set 10 for branch master has been pushed to the review server.
It is available at https://review.typo3.org/15369
Updated by Gerrit Code Review almost 11 years ago
Patch set 11 for branch master has been pushed to the review server.
It is available at https://review.typo3.org/15369
Updated by Gerrit Code Review over 10 years ago
Patch set 12 for branch master has been pushed to the review server.
It is available at https://review.typo3.org/15369
Updated by Gerrit Code Review over 10 years ago
Patch set 13 for branch master has been pushed to the review server.
It is available at https://review.typo3.org/15369
Updated by Gerrit Code Review over 10 years ago
Patch set 14 for branch master has been pushed to the review server.
It is available at https://review.typo3.org/15369
Updated by Gerrit Code Review over 10 years ago
Patch set 15 for branch master of project Packages/TYPO3.CMS has been pushed to the review server.
It is available at https://review.typo3.org/15369
Updated by Gerrit Code Review over 10 years ago
Patch set 16 for branch master of project Packages/TYPO3.CMS has been pushed to the review server.
It is available at https://review.typo3.org/15369
Updated by Gerrit Code Review over 10 years ago
Patch set 17 for branch master of project Packages/TYPO3.CMS has been pushed to the review server.
It is available at https://review.typo3.org/15369
Updated by Gerrit Code Review over 10 years ago
Patch set 18 for branch master of project Packages/TYPO3.CMS has been pushed to the review server.
It is available at https://review.typo3.org/15369
Updated by Gerrit Code Review over 10 years ago
Patch set 19 for branch master of project Packages/TYPO3.CMS has been pushed to the review server.
It is available at https://review.typo3.org/15369
Updated by Gerrit Code Review over 10 years ago
Patch set 20 for branch master of project Packages/TYPO3.CMS has been pushed to the review server.
It is available at https://review.typo3.org/15369
Updated by Alexander Stehlik over 10 years ago
- Status changed from Under Review to Resolved
- % Done changed from 0 to 100
Applied in changeset 9c905d15d15e240035e0942ebbffd3f295d2eef5.
Updated by