Bug #18821

Epic #68397: Make TYPO3 work with MySQL strict mode

Installer will not create any be_users if unsupported sql_mode is used

Added by Phil about 7 years ago. Updated 14 days ago.

Status:Closed Start date:2008-05-20
Priority:Should have Due date:
Assigned To:Alexander Opitz % Done:

100%
Category:Install Tool Spent time: -
Target version:-
TYPO3 Version:4.2 Is Regression:No
PHP Version:5.2 Sprint Focus:
Complexity:

Description

Insert statement fails due to 'NOT NULL' DEFUALT NULL column descriptions.

Field 'TSconfig' doesn't have a default value

i presume its the same for column uc. turning on SQL debug i got the SQL statement and ran in in mysql command line as follows:

mysql> INSERT INTO be_users
-> (
-> username,
-> password,
-> admin,
-> uc,
-> fileoper_perms,
-> tstamp,
-> crdate
-> ) VALUES (
-> 'admin',
-> '5f4dcc3b5aa765d61d8327deb882cf99',
-> '1',
-> '',
-> '7',
-> '1211301857',
-> '1211301857'
-> );
ERROR 1364 (HY000): Field 'TSconfig' doesn't have a default value

windows vista
mysql: 5.1.24-rc-community
php 5.2.4
Apache/2.2.4
(issue imported from #M8474)

Related issues

related to Core - Bug #15295: Unable to login into BE when using MySQL 5+ Closed 2005-12-19
related to Core - Task #54883: Document that TYPO3 CMS is not compatible with MySQL stri... Resolved 2014-01-09

Associated revisions

Revision bad3f24c
Added by Sascha Egerer almost 2 years ago

[BUGFIX] Check for incompatible SQL modes

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/19671
Reviewed-by: Sascha Egerer
Tested-by: Sascha Egerer
Reviewed-by: Markus Klein
Reviewed-by: Alexander Opitz
Tested-by: Alexander Opitz
Reviewed-by: Stefan Neufeind
Tested-by: Stefan Neufeind

Revision e727d5ff
Added by Sascha Egerer almost 2 years ago

[BUGFIX] Check for incompatible SQL modes

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

History

#1 Updated by Norbert Bernhardt almost 7 years ago

This happens on a regular basis when MySQL's sql_mode is set to STRICT_ALL_TABLES.

The MySQL manual states for this mode:
Enable strict mode for all storage engines. Invalid data values are rejected. Additional detail follows. (Added in MySQL 5.0.2)

There are no default values for text and blob fields in Typo3 as far as I overlooked it. I had the problem with be_sessions and fe_sessions having no default value for ses_data. In this case Typo3 tries to insert values for ses_id, ses_name, ses_iplock, ses_hashlock, ses_userid, ses_tstamp and MySQL throws error
#14916 - Field 'ses_data' doesn't have a default value

The solution would be to define the value
NULL
to each text and blob field in the Typo3 database to make it strictly SQL compliant.

#2 Updated by Norbert Bernhardt almost 7 years ago

Seems the problem of non-strict-conformity exists also in Typo3 code. Trying to insert a new content entry in tt_content fails with:

2: SQL error: 'Incorrect integer value: '' for column 'sys_language_uid' at row 1' (tt_content:NEW4903a79adce61)

tt_content.sys_language_uid is a integer number, trying to insert '' as an empty string doesn't seem very SQL-compliant.

@ Typo3 Core Team: Any plans to change that? Help appreciated?

#4 Updated by Gerrit Code Review over 2 years ago

  • Status changed from New to Under Review

Patch set 1 for branch master has been pushed to the review server.
It is available at https://review.typo3.org/19671

#5 Updated by Gerrit Code Review over 2 years ago

Patch set 2 for branch master has been pushed to the review server.
It is available at https://review.typo3.org/19671

#6 Updated by Gerrit Code Review over 2 years ago

Patch set 3 for branch master has been pushed to the review server.
It is available at https://review.typo3.org/19671

#7 Updated by Alexander Opitz about 2 years ago

  • Assigned To set to Alexander Opitz
  • Target version deleted (0)

#8 Updated by Gerrit Code Review about 2 years ago

Patch set 4 for branch master has been pushed to the review server.
It is available at https://review.typo3.org/19671

#9 Updated by Gerrit Code Review about 2 years ago

Patch set 5 for branch master has been pushed to the review server.
It is available at https://review.typo3.org/19671

#10 Updated by Gerrit Code Review almost 2 years ago

Patch set 6 for branch master has been pushed to the review server.
It is available at https://review.typo3.org/19671

#11 Updated by Gerrit Code Review almost 2 years ago

Patch set 7 for branch master has been pushed to the review server.
It is available at https://review.typo3.org/19671

#12 Updated by Gerrit Code Review almost 2 years ago

Patch set 8 for branch master has been pushed to the review server.
It is available at https://review.typo3.org/19671

#13 Updated by Gerrit Code Review almost 2 years ago

Patch set 9 for branch master has been pushed to the review server.
It is available at https://review.typo3.org/19671

#14 Updated by Gerrit Code Review almost 2 years ago

Patch set 10 for branch master has been pushed to the review server.
It is available at https://review.typo3.org/19671

#15 Updated by Gerrit Code Review almost 2 years ago

Patch set 11 for branch master has been pushed to the review server.
It is available at https://review.typo3.org/19671

#16 Updated by Gerrit Code Review almost 2 years ago

Patch set 12 for branch master has been pushed to the review server.
It is available at https://review.typo3.org/19671

#17 Updated by Anonymous almost 2 years ago

  • Status changed from Under Review to Resolved
  • % Done changed from 0 to 100

#18 Updated by Gerrit Code Review almost 2 years ago

  • Status changed from Resolved to Under Review

Patch set 1 for branch TYPO3_6-1 has been pushed to the review server.
It is available at https://review.typo3.org/23237

#19 Updated by Stefan Neufeind almost 2 years ago

Patch was reverted again since, after some discussion, this was not the right place to add the additional check. Will need a follow-up. The general idea itself however was okay.

#20 Updated by Anonymous almost 2 years ago

  • Status changed from Under Review to Resolved

#21 Updated by Alexander Opitz almost 2 years ago

  • Status changed from Resolved to New

#22 Updated by Sascha Egerer 9 months ago

  • Subject changed from installer will not create any be_users to Installer will not create any be_users if unsupported sql_mode is used
  • Status changed from New to Accepted
  • Is Regression set to No

Currently there is not check at all anymore which ends up in a very frustrating experience if somebody doesn't know about the "sql mode" requirements.

#23 Updated by Morton Jonuschat 14 days ago

  • Parent task set to #68397

#24 Updated by Morton Jonuschat 14 days ago

  • Status changed from Accepted to Closed

Closing this since the field configuration for TSconfig has been fixed a long time ago in TYPO3 4.3 - https://github.com/TYPO3/TYPO3.CMS/commit/a0bbc6fdf9d6541df871ec32ebe95f392712c7c2

The InstallTool check will be tracked in a dedicated issue: #68407

Also available in: Atom PDF