Bug #15772

"NOT NULL" removed from SQL gives error with MySQL 3.23.52

Added by Kasper Skaarhoj almost 14 years ago. Updated over 13 years ago.

Status:
Closed
Priority:
Must have
Category:
Install Tool
Target version:
-
Start date:
2006-03-06
Due date:
% Done:

0%

TYPO3 Version:
4.0
PHP Version:
4
Tags:
Complexity:
Is Regression:
Sprint Focus:

Description

Lines 324ff in class.t3lib_install.php

// Remove NOT NULL statements (they are not needed)
$parts[1] = str_replace(' NOT NULL', '', $parts[1]);

results in an error with MySQL 3.23.52 because two new tables (sys_refindex + sys_preview) in TYPO3 4.0 uses a varchar(32) field as primary key and primary keys in that MySQL version seems to require the "NOT NULL" attribute.
This will surely be a problem when people begin to upgrade sites.

(issue imported from #M2777)

class.t3lib_install.php.patch View (2.48 KB) Administrator Admin, 2006-03-26 00:23


Related issues

Duplicated by TYPO3 Core - Bug #15888: Database analyzer fails creating some tables with MySQL 3.23 Closed 2006-03-23
Duplicated by TYPO3 Core - Bug #15587: SQL-Error while updating to 4beta2 (create table sys_refindex) Closed 2006-02-07

History

#1 Updated by Robert Lemke almost 14 years ago

As I don't have a server with MySQL 3.x running, I can't check it myself. But in theory, even MySQL 3.x should accept a varchar definition without a DEFAULT definition.

See http://dev.mysql.com/doc/refman/4.1/en/data-type-defaults.html for more information.

#2 Updated by Karsten Dambekalns almost 14 years ago

Yes, but this isn't about varchar columns but about varchar columns used as primary keys. I cannot check this, either, though...

#3 Updated by Ingmar Schlecht almost 14 years ago

Attached patch.
It's not tested at all but should be a hint at what I think is the only way to work around this problem.

#4 Updated by Ingmar Schlecht almost 14 years ago

A new version of the patch is attached FYI, it is already in CVS.

I have well tested it and I'm pretty sure the way this patch does the trick it is the only feasible way.

Now the NOT NULL statements are not removed anymore, but ignored during comparision.
That means the NOT NULL attribute is only taken into account when fields are initially created but it will be ignored during .sql-file/database comparision later on.

This makes it work for all MySQL versions.

#5 Updated by Karsten Dambekalns almost 14 years ago

FYI: DBAL still drops all those NOT NULL statements, and even without they only make sense in this case (if needed for a key), as MySQL will never act as expected when NOT NULL is supplied (otherwise TYPO3 would not work the way it does!).

So the only "real" way to fix this is to drop the declaration except for fields used in primary keys...

#6 Updated by Ingmar Schlecht almost 14 years ago

OK, but as long as you don't use DBAL it should work fine for all MySQL versions now, right?

IMHO the NOT NULL statements of fields that are not used as primary keys should be removed from the .sql file table defititions.

cheers,
Ingmar

Also available in: Atom PDF