Bug #52206

Epic #68397: Make TYPO3 work with MySQL strict mode

Default-values for text-fields

Added by Stefan Neufeind about 8 years ago. Updated almost 3 years ago.

Status:
Closed
Priority:
Should have
Assignee:
-
Category:
-
Target version:
-
Start date:
2013-09-24
Due date:
% Done:

100%

Estimated time:
TYPO3 Version:
6.2
PHP Version:
Tags:
Complexity:
Is Regression:
No
Sprint Focus:

Description

In some places text-fields don't have a default-value. phpMyAdmin shows that there is no default-value set.
Unfortunately MySQL (still) does not support default-values for BLOB/TEXT (see bugs.mysql.com/bug.php?id=21532).

Upon insertion of a new content-element (a plugin in this case) I got:
SQL error: 'Field 'filelink_sorting' doesn't have a default value' (tt_content:NEW5240c411f10d4)

This can be prevented by adding a default-value (empty string) in the TCA-definition for that column (in typo3/sysext/frontend/Configuration/TCA/tt_content.php).
I expect this is true for versions down to 4.5 and is just triggered since some newer MySQL/MariaDB-release.

Happens here on Fedora 19 with mariadb-server-5.5.32-8.fc19.


Files


Related issues

Related to TYPO3 Core - Bug #52204: SQL error: Incorrect integer value: '' for column 'backend_layoutRejected2013-09-24

Actions
Related to TYPO3 Core - Bug #18866: Cannot create page with Mysl in strict mode (STRICT_TRANS_TABLES)RejectedAlexander Opitz2008-05-28

Actions
Related to TYPO3 Core - Task #54883: Document that TYPO3 CMS is not compatible with MySQL strict modesClosedErnesto Baschny2014-01-09

Actions
Related to TYPO3 Core - Bug #16809: FIX FOR Typo3 basic tables definition not compliant with MySQL 5.0 strict mode enabled by defaultClosedDmitry Dulepov2006-12-22

Actions
#1

Updated by Stefan Neufeind about 8 years ago

Turns out that from recent system-environment-testing I still had "sql-mode = STRICT_ALL_TABLES" in my.cnf. Removing that makes the problem go away. Still fix it since it's "wrong"?

#2

Updated by Ernesto Baschny over 7 years ago

  • Status changed from New to Accepted

Making TYPO3 compatible with MySQL strict mode is probably a huge challenge that we might want to take some time. But not for 6.2. Which is why I added #54883 suggesting to at least document the fact more clearly and officially.

#3

Updated by Oliver Eglseder about 7 years ago

Just for information
sql_mode=STRICT_TRANS_TABLES reproduces this issue ('Field 'filelink_sorting' doesn't have a default value')

#4

Updated by Stefan Neufeind about 7 years ago

But that's not a default-configuration value, or is it on your platform? Does the install-tool point you towards the problem with a recommendation to change that setting maybe?

#5

Updated by Hallvard Nygård almost 7 years ago

It's default when installing MySQL 5.5 on Windows using Windows Platform installer

C:\Program Files\MySQL\MySQL Server 5.5\my.ini:

sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

Worked after changing it to:

sql-mode="NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
#6

Updated by Mateusz Wojtuła over 6 years ago

Hello,
I have got the same error on Ubuntu 14.04.1 LTS, mysql Ver 14.14 Distrib 5.5.40, for debian-linux-gnu (x86_64) using readline 6.3. Disabling STRICT_ALL_TABLES in mysql configuration solve this bug.

#7

Updated by Simon Schaufelberger over 6 years ago

push. Can this go into 7.x?

#8

Updated by Hsu Yiming about 6 years ago

如果你也遇到同樣的問題,請修改 my.cnf

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_ALL_TABLES,STRICT_TRANS_TABLES

將以下2者拿掉(或註解)

1. STRICT_ALL_TABLES

2. STRICT_TRANS_TABLES

#9

Updated by Morton Jonuschat about 6 years ago

  • Parent task set to #68397
#10

Updated by Gerrit Code Review about 6 years ago

  • Status changed from Accepted to Under Review

Patch set 2 for branch master of project Packages/TYPO3.CMS has been pushed to the review server.
It is available at http://review.typo3.org/41756

#11

Updated by Morton Jonuschat about 6 years ago

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

Updated by Fedir RYKHTIK over 4 years ago

I had such error on Ubuntu/MySQL 5.7 with TYPO3 7.6 LTS. To resolve : added at /etc/mysql/my.cnf in [mysqld] section following configuration :

sql_mode = "ONLY_FULL_GROUP_BY,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREAT‌​E_USER,NO_ENGINE_SUB‌​STITUTION" 

#13

Updated by Benni Mack almost 3 years ago

  • Status changed from Resolved to Closed

Also available in: Atom PDF