Bug #20052

Epic #68397: Make TYPO3 work with MySQL strict mode

SQL error: 'Incorrect integer value: '' for column 'storage_pid' at row 1' (pages:NEW499d21cdec168)

Added by Steen Rabl about 11 years ago. Updated over 4 years ago.

Status:
Rejected
Priority:
Should have
Assignee:
-
Category:
-
Target version:
-
Start date:
2009-02-19
Due date:
% Done:

100%

TYPO3 Version:
4.2
PHP Version:
5.2
Tags:
Complexity:
Is Regression:
No
Sprint Focus:

Description

On a clean installation:

I get SQL error: 'Incorrect integer value: '' for column 'storage_pid' at row 1' (pages:NEW499d21cdec168) when I try to create a new page.

Clean typo3 installation into: c/www/typo3.local/public
Dummy 4.2.6 coppied into the same directory, ran the installation, DB created ok, trried to create a new page.

From administration log:
Attempt to insert a record on page 'Incorrect integer value: '' for column 'storage_pid' at row 1' (pages:NEW499d21cdec168) from table '' without permissions. Or non-existing page. (msg#1.1.12)

  1. DEBUG SYSTEM INFORMATION - START ###
    HTTP_HOST : typo3.local
    TYPO3_HOST_ONLY : typo3.local
    TYPO3_PORT :
    PATH_INFO :
    QUERY_STRING : TYPO3_INSTALL[type]=phpinfo
    REQUEST_URI : /typo3/install/index.php?TYPO3_INSTALL[type]=phpinfo
    HTTP_REFERER : http://typo3.local/typo3/install/index.php?
    TYPO3_REQUEST_HOST : http://typo3.local
    TYPO3_REQUEST_URL : http://typo3.local/typo3/install/index.php?TYPO3_INSTALL[type]=phpinfo
    TYPO3_REQUEST_SCRIPT: http://typo3.local/typo3/install/index.php
    TYPO3_REQUEST_DIR : http://typo3.local/typo3/install/
    TYPO3_SITE_URL : http://typo3.local/
    TYPO3_SITE_SCRIPT : typo3/install/index.php?TYPO3_INSTALL[type]=phpinfo
    TYPO3_SSL :
    TYPO3_REV_PROXY :
    SCRIPT_NAME : /typo3/install/index.php
    TYPO3_DOCUMENT_ROOT : C:/www/typo3.local/public
    SCRIPT_FILENAME : C:/www/typo3.local/public/typo3/install/index.php
    REMOTE_ADDR : 127.0.0.1
    REMOTE_HOST :
    HTTP_USER_AGENT : Mozilla/4.0 (compatible; MSIE 7.0; Windows NT 5.1; .NET CLR 1.1.4322; .NET CLR 2.0.50727; InfoPath.2; .NET CLR 3.0.04506.648; .NET CLR 3.5.21022; OfficeLiveConnector.1.3; OfficeLivePatch.0.0)
    HTTP_ACCEPT_LANGUAGE: da
    CONST: PHP_OS : WINNT
    CONST: TYPO3_OS : WIN
    CONST: PATH_thisScri: C:/www/typo3.local/public/typo3/install/index.php
    CONST: php_sapi_name: apache2handler
    OTHER: TYPO3_VERSION: 4.2.6
    OTHER: PHP_VERSION : 5.2.8
    imagecreatefromgif(): 1
    imagecreatefrompng(): 1
    imagecreatefromjpeg(: 1
    imagegif() : 1
    imagepng() : 1
    imagejpeg() : 1
    imagettftext() : 1
    OTHER: IMAGE_TYPES : 15
    OTHER: memory_limit : 128M
    SERVER: SERVER_PORT : 80
    SERVER: SERVER_SOFTW: Apache/2.2.11 (Win32) PHP/5.2.8
    SERVER: GATEWAY_INTE: CGI/1.1
    SERVER: SCRIPT_NAME : /typo3/install/index.php
    SERVER: PATH_TRANSLA:
    T3CV_GFX: image_proc: 1
    T3CV_GFX: gdlib : 1
    T3CV_GFX: gdlib_png : 0
    T3CV_GFX: gdlib_2 : 0
    T3CV_GFX: im : 1
    T3CV_GFX: im_path : /usr/X11R6/bin/
    T3CV_GFX: im_path_lz: /usr/bin/
    T3CV_GFX: im_version:
    T3CV_GFX: im_negate_: 0
    T3CV_GFX: im_imvMask: 0
    T3CV_GFX: im_combine: combine
  2. DEBUG SYSTEM INFORMATION - END ###

Server information
MySQL version: 5.0.67-community-nt via TCP/IP

client information
MySQl Client version 5.1.11
(issue imported from #M10487)

bug_10487.patch View (1.47 KB) Administrator Admin, 2009-10-09 15:16

10487_nostrict.patch View (630 Bytes) Administrator Admin, 2009-10-09 23:19


Related issues

Related to TYPO3 Core - Task #54883: Document that TYPO3 CMS is not compatible with MySQL strict modes Closed 2014-01-09
Duplicated by TYPO3 Core - Bug #20433: The import export menu will not activate Closed 2009-05-16
Duplicated by TYPO3 Core - Bug #20415: Error Message that keeps comming up Closed 2009-05-12
Duplicated by TYPO3 Core - Bug #18866: Cannot create page with Mysl in strict mode (STRICT_TRANS_TABLES) Rejected 2008-05-28
Duplicated by TYPO3 Core - Bug #21212: Unable to create a page Closed 2009-10-09
Duplicated by TYPO3 Core - Bug #20486: MySQL errors and unable to create a page Closed 2009-05-21
Duplicated by TYPO3 Core - Bug #21974: SQL error if i want to create a page by using "Page (inside)" Closed 2010-01-16
Duplicated by TYPO3 Core - Bug #55020: Incorrect integer value: '' for column 'sys_language_uid' at row 1 Closed 2014-01-15

Associated revisions

Revision bad3f24c (diff)
Added by Sascha Egerer over 6 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 (diff)
Added by Sascha Egerer over 6 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 Marcus Krause about 11 years ago

Are you using MySQL as DB? If so, is it running in SQL strict mode?

Issue is probably related to #0006299, #0008474 and #0008542.

#2 Updated by Steen Rabl about 11 years ago

You are absolutly correct it's MySQL running in SQL strict mode.

I have used the default installation so I would never have thourght that it would be a problem as the requirements for Typo3 don't state any MySQL restrictions.

What would be the correct sql mode ?

#3 Updated by Steen Rabl about 11 years ago

Found it sql-mode="MYSQL40"

#4 Updated by Marcus Krause about 11 years ago

Unfortunately, it's currently not possible to run MySQL in strict mode. This would result in numerous error like that one you are reporting.

However, this is not intended. If you feel able to contribute and provide patches, this would be highly appreciated.

#5 Updated by Peter Beernink over 10 years ago

Added a patch which solves the issue.
Except from the BE, the same error was given when a page was cached.

The patch was created against r6127

#6 Updated by Niels Fröhling over 10 years ago

The patch I added will turn off strict_mode for the sql-session. No other fixes to the sql-dialect necessary. Checked it on XAMP-installation, was the only way to make it work (I had no access to the servers settings).

#7 Updated by Niels Fröhling over 10 years ago

This then even is a duplicate of 0008542

#8 Updated by Andrew Murphy over 10 years ago

Feedback:

I downloaded and installed TYPO3 4.2.9 [typo3_src+dummy-4.2.9.zip] onto a local workstation already having Apache HTTPD 2.2.11, MySQL 5.0.51a, and PHP 5.2.8.

I logged in to the TYPO3 back end at [http://localhost/typo3/index.php] as an administrator. Once in, I clicked on the Page section of the Web module, then clicked on the globe appearing in the tree structure of the navigation area (the second column), then on "new" in the pop-up menu, then on the "Page (inside)" in the details view. In the box that appeared in the details view, I unchecked "hide page", entered "Home Page" as the Page Title, entered "Home" as the Subtitle, then clicked on the "save and close document" disk icon button. I received the [SQL error: 'Incorrect integer value: '' for column 'storage_pid' at row 1'] error. I logged out of the TYPO3 back end, then stopped the Apache HTTPD service.

After researching the problem, I used the [10487_nostrict.patch] offered by Niels Fr?hling (0030995) to add a MySQL 'SET SESSION sql_mode=\'\'' instruction to the [t3lib/class.t3lib_db.php] file. I started the Apache HTTPD service, then repeated the procedure above to create a new page. This time, I did not receive the SQL error. Instead, the procedure was successful, and the new page appeared in the tree structure.

#9 Updated by Renga over 10 years ago

I moved my sites from 3.8 on Windows to 4.2.9 on ubuntu hardy heron 8.04

http://www.rengucha.com/phpInfo.php will show all config details.

Issues
1.0 All sites are showing up but for one. It is just showing blank white screen with "Done" at the bottom. www.nittalum.us

2.0 I am not able to change content in BE. When I change and save, the last section where I edit becomes white. When I again click on Page and page it has the old data. Nothing is saved. Suspecting the above issue I tried the patch as below

2.1 > sudo patch  -b -f  -p 1 -i  /home/suresh/typo3/10487_nostrict.patch -o ./class.t3lib_db.php

(Stripping trailing CRs from patch.)
patching file class.t3lib_db.php
Hunk #1 FAILED at 963.

Please help

class.t3lib_db.php.rej has the following


  • 963,968 **
    if (!$this->link) {
    t3lib_div::sysLog('Could not connect to MySQL server '.$TYPO3_db_host.' with user '.$TYPO3_db_username.': '.$error_msg,'Core',4);
    } else {
    $setDBinit = t3lib_div::trimExplode(chr(10), $GLOBALS['TYPO3_CONF_VARS']['SYS']['setDBinit'],TRUE);
    foreach ($setDBinit as $v) {
    if (mysql_query($v, $this->link) === FALSE) {
    --- 963,969 ----
    if (!$this->link) {
    t3lib_div::sysLog('Could not connect to MySQL server '.$TYPO3_db_host.' with user '.$TYPO3_db_username.': '.$error_msg,'Core',4);
    } else {
    + mysql_query('SET SESSION sql_mode=\'\'', $this->link);
    $setDBinit = t3lib_div::trimExplode(chr(10), $GLOBALS['TYPO3_CONF_VARS']['SYS']['setDBinit'],TRUE);
    foreach ($setDBinit as $v) {
    if (mysql_query($v, $this->link) === FALSE) {

#10 Updated by Renga over 10 years ago

I just went into class.t3lib_db.php and added the following line
mysql_query('SET SESSION sql_mode=\'\'', $this->link);

restarted Apache and mySQL. Still not working.

Then I saw the following in the Apache log

PHP Fatal error: Cannot re-assign $this in /var/www/web1/quickstart/typo3conf/ext/static_info_tables/class.tx_staticinfotables_syslanguage.php on line 41

#11 Updated by Simon Schick about 10 years ago

This problem is also resistent in TYPO3 v4.3.1

But one thing:
Why don't you add the SQL-command into the var $GLOBALS['TYPO3_CONF_VARS']['SYS']['setDBinit'] which you can set in the install-tool ??

If you use this option you don't have to modify the TYPO3-core ...

#12 Updated by Simon Schick about 10 years ago

For all coreFreaks ...
I've debugged this function and searched for a useful solution ... (Using TYPO3 v4.3.1)

MySQL-Default-Value is "0" ... This is only set in the MySQL!! I can't find it anywhere in the PHP-code (f.e. the $TCA[]).

The field we're talking about is "storage_pid" and if you look into the $TCA[] this field is defined as: { type:"group", internal_type: "db", allowed: "pages", size: 1, maxitems: 1, minitems: 0, show_thumbs: 1, witards: { suggest: { type: "suggest" } } }

Because the type of this fild is "group" we have to look at the function $t3lib_TCEmain->checkValue_group_select() (If you want to know why - look at $t3lib_TCEmain->checkValue_SW() )
In this function he checks the internal_type which is set to "db". Than he calles $t3lib_TCEmain->checkValue_group_select_processDBdata() to check which data is selected and he will return an empty array. So far all steps are correct - but after he has confirmed this call - he should check if the returned value is empty and check if this field has an default-value (this is also missing in the $TCA[]). I think if we'd use $unsetResult we could get troubles if we edit a page :)

My solution:
  • add a check after checkValue_group_select_processDBdata()
  • add the default-value to the $TCA-array as it's set in the database (or use $unsetResult after the check if this don't cause troubles - I haven't tested ...)

A more complex way would be to read out the MySQL-default-value for this column an set this value if an empty array is given by the function checkValue_group_select_processDBdata()

#13 Updated by Andrew Murphy over 9 years ago

Feedback

This problem persists in TYPO3 v4.4.4. Here is a short procedure checklist for TYPO3 administrators already logged in to the back end when this error occurs. (This procedure is based on Simon Schick's suggestion on 17.01.10 11:07.)
1. Go to "User tools>User settings". Click on the "Admin functions" tab. Click "Create Install Tool Enable File".
2. Go to "Admin tools>Install". Enter your password and click "login". Click "All Configuration".
3. Search the page for "setDBinit" (or scroll to [SYS][setDBinit]). You may see a line in the textarea saying, "SET NAMES utf8;". After that line, enter "SET SESSION sql_mode='';" without the first and last double quotes ("). Go to the bottom of the page, and click "Write to localconf.php". Continue, and confirm that your line has been entered by looking at the "setDBinit" section again.
4. Click "Logout from Install Tool". You are done. Return to the back end, and continue your task.

#14 Updated by Gerrit Code Review about 7 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

#15 Updated by Gerrit Code Review about 7 years ago

Patch set 2 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 about 7 years ago

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

#17 Updated by Alexander Opitz almost 7 years ago

  • Assignee set to Alexander Opitz
  • Target version deleted (0)

#18 Updated by Gerrit Code Review almost 7 years ago

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

#19 Updated by Gerrit Code Review almost 7 years ago

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

#20 Updated by Gerrit Code Review over 6 years ago

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

#21 Updated by Gerrit Code Review over 6 years ago

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

#22 Updated by Gerrit Code Review over 6 years ago

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

#23 Updated by Gerrit Code Review over 6 years ago

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

#24 Updated by Gerrit Code Review over 6 years ago

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

#25 Updated by Gerrit Code Review over 6 years ago

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

#26 Updated by Gerrit Code Review over 6 years ago

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

#27 Updated by Anonymous over 6 years ago

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

#28 Updated by Gerrit Code Review over 6 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

#29 Updated by Stefan Neufeind over 6 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.

#30 Updated by Anonymous over 6 years ago

  • Status changed from Under Review to Resolved

#31 Updated by Chris topher over 6 years ago

  • Status changed from Resolved to New

#32 Updated by Patrik Nasfors almost 6 years ago

  • Assignee deleted (Alexander Opitz)

What should the Status of this issue be? New and Alexander Opitz (the first name) as Assignee, seems like a mistake.

#33 Updated by Mathias Schreiber about 5 years ago

  • Status changed from New to Rejected
  • Is Regression set to No

Making TYPO3 work with script mode is at least a year of work.
We have this on our agenda, but since this touches a wider aspect of the code I'm closing these tickets down.

#34 Updated by Morton Jonuschat over 4 years ago

  • Parent task set to #68397

Also available in: Atom PDF