Bug #16809


FIX FOR Typo3 basic tables definition not compliant with MySQL 5.0 strict mode enabled by default

Added by Alban Cousinie over 17 years ago. Updated over 15 years ago.

Must have
Install Tool
Target version:
Start date:
Due date:
% Done:


Estimated time:
TYPO3 Version:
PHP Version:
Is Regression:
Sprint Focus:


This relates to issues 0004691, 0002634 and 0002059

Issue occurs with MySQL 5.0 databases.

The reason for this issue occurring is :
Some fields in the table definitions are marked as NOT NULL while not providing any default values. When a Typo3 MySQL query does not specify a value for one of those fields, the query fails.
Also since MySQL 5.0, BLOB and TEXT columns cannot be assigned a default value ( but are set as NOT NULL in Typo3 basic table definition).

I have edited the tables.sql file used by typo3 to create a basic database during a fresh install and fixed these issues. It resolves issues 0004691, 0002634 and 0002059.

THIS FIX SHOULD BE APPLIED TO TYPO3 4.0/4.1 MAIN DEVELOPMENT TRUNK QUICKLY since more and more users are using MySQL 5.0 and they should be facing this problem.

Overwrite t3lib/stddb/tables.sql with the tables.sql file supplied in this issue report.

This update has been tested and can not break Typo3 since all it does is remove a few NOT NULL statements in fields definitions where they do not comply with MySQL Strict mode.

An alternate fix to these issues is to edit the MySQL configuration file (my.ini on windows, my.cnf on linux) and change the line :
to :
Then restart MySQL.

But since the bad SQL mode for Typo3 is specified by default into MySQL 5.0, Typo3 should adapt in order to comply and avoid having all newbies blocked in the install tool while facing this bug. Thus this patch has to be included into the main development trunk.
(issue imported from #M4693)


tables.sql (10.1 KB) tables.sql Administrator Admin, 2006-12-22 15:26
4693.diff (19.7 KB) 4693.diff Administrator Admin, 2008-05-12 14:55

Related issues 6 (0 open6 closed)

Related to TYPO3 Core - Bug #15675: Backend Login ProblemClosedKarsten Dambekalns2006-02-19

Related to TYPO3 Core - Bug #15295: Unable to login into BE when using MySQL 5+ClosedKarsten Dambekalns2005-12-19

Related to TYPO3 Core - Bug #19923: Table definition in cache_treelist not compliant with MySQL 5.0 strict modeClosedBenni Mack2009-01-27

Related to TYPO3 Core - Bug #52206: Default-values for text-fieldsClosed2013-09-24

Has duplicate TYPO3 Core - Bug #16807: create admin user fails because of bad be_users table definitionClosedMichael Stucki2006-12-22

Has duplicate TYPO3 Core - Bug #16765: Warn about MySQL 5 while installing.ClosedMichael Stucki2006-12-07

Actions #1

Updated by Alban Cousinie over 17 years ago

Note : this FIX has been edited based on original file tables.sql from typo3 4.0.4 distribution.

Actions #2

Updated by Alban Cousinie over 17 years ago

Obviously it didn't fix every error : when I create my first page into my new typo3 installation, I get an error 'field url from table pages has no default value'.

This field url is not present into definition of table pages in the basic tables.sql file and thus it must be added at some point later from another file. Of course field 'url' is a field of type 'text' which can't be assigned a default value.

Thus the file I have provided is not the THE fix, but a part of the fix. If anyone know from where these additional fields to table pages are being added please post it below, and I'll attempt to fix it.

My fear is the problem can occur with many extensions creating their own tables not compliant with strict mode as well, so a more secure and permanent solution remains the MySQL configuration file sql-mode parameter tweaking.

Actions #3

Updated by Michael Stucki over 17 years ago

I will take care of this

Actions #4

Updated by Edgar Geisler over 17 years ago

I'm a newbee using Typo3 4.04 with MySQL 5.0.27, Apache 2.2.3, PHP/5.2.0, Typo3 4.04 on a Windows XP machine.

Since there are a lot of open 'Backend Login Problems' reported over the web and this issue (04693) resolves ONLY a problem corresponding to data retrieval of a be_user in MySQL 5.x, I want to add, that Typo3 still remains BLOCKING newbee's (coming from the MS Windows corner) during login (more escpecially me).

Until today (wow, what a wounderful new year's start)!.

Digging into the problem I stumbled over a german Typo3 forum's contribution. It suggested to check the 'session'-parameters of the installation (sorry - source lost). For me, it was the substantial clue beside your patch and leads into the Typo3 backend.

In a lot of forum's contributions it was reported, that using LAMP won't create login problems to the backend. So - I haven't used LAMP so far (at this moment, while I'm writing this, I don't know, if LAMP is generally available for MS Windows ?).

I've installed the packages mentioned above from the root sites making escpecially some changes regarding Apache's VirtualHost and doc_root options.
Then, after unzipping the Typo3 CMS, the dummy package, the merging of tem, the execution of the installation script and the application of the patch, I ran into the mentioned backend login problem.

Well - I've fixed this by adding a
"session.auto_start = 1"
in the 'Session' section of the 'php.ini' file.

Actions #5

Updated by Steffen Müller over 17 years ago

Keep in mind that this strict-mode incompatibilitiy also affects the following sysexts:
dbal, cms, impexp, indexedsearch, rtehtmlarea, sys_action, sys_note, tsconfig_help
I am sorry that I do not have the time to provide patches for all these extensions.

Actions #6

Updated by Steffen Müller over 17 years ago

The Extension Kickstarter also produces text columns with NOT NULL attribute.

Beside that, maybe it's a good idea to mention proper CREATE TABLE statements in the coding guideline documentation, too.

Actions #7

Updated by Arnold Gordijn over 16 years ago

I'm using WIN2003 PHP5 and MYSQL5 and TYPO3 Ver. 4.1.3

I tryed the new tables.sql
and yes than I could create a admin user

So could login but other things like improt from .t3d still fail due to NOT NULL and no default set.
I I would surgest the workaround sql-mode="MYSQL40"

Actions #8

Updated by Steffen Heinrich over 16 years ago

You are sure giving a hard time to any newbie ;-)

I've had Apache 1.3, PHP5.2.3 and MySQL5.0.27 properly installed and running on Windows XP, then I decided to get TYPO3 4.1.5.

To make things short, there is at least one other issue with the installer having the same cause.
All imports into table static_tsconfig_help fail despite the installer's insistence that 117 rows were inserted.

CREATE TABLE static_tsconfig_help (
uid int(11) NOT NULL auto_increment,
guide int(11) DEFAULT '0' NOT NULL,
INSERT INTO static_tsconfig_help VALUES ('1', '', ...

Inserting an empty string into an int type field is definitely not the correct thing to do and that it is working with mysql 4 must be considered a severe bug in that version.
Only the subsequent error with creating the admin user had lead me on the right track.
sql-mode="MYSQL40" works but is a clutch. How can this problem be known for so long without a thorough fix?
Do the developers all work with old mysql?
For them there is no way but migrating to mysql 5 to get a hint where to start cleaning up that messy sql. Else trouble will keep haunting you.

just my 2p

(To save other user's from sharing the frustration, how bout at least adding a big red note of warning to the download page?)

Actions #9

Updated by Michael Stucki over 16 years ago

Dmitry, will you have a look at this?

Actions #10

Updated by Dmitry Dulepov about 16 years ago

Will do during t3dd08

Actions #11

Updated by Dmitry Dulepov about 16 years ago

Attached patch fixes NOT NULL issue for core.

Actions #12

Updated by Dmitry Dulepov about 16 years ago

rev 3647


Also available in: Atom PDF