Bug #14354

MySQL gt 4.1 no default value for auto inc

Added by Stefan Kreisberg over 15 years ago. Updated over 13 years ago.

Status:
Closed
Priority:
Should have
Category:
Install Tool
Target version:
-
Start date:
2004-10-12
Due date:
% Done:

0%

TYPO3 Version:
3.8.0beta2
PHP Version:
Tags:
Complexity:
Is Regression:
Sprint Focus:

Description

In MySQL > 4.0 default values for auto_inc columns are ignored, in MySQL > 4.1 they produce an error. Many extensions (perhaps created with the extrepwiz?) create sql schemas with auto_inc wich have default values ...

example:
replace the line uid int(11) NOT NULL default '0' auto_increment with uid int(11) unsigned NOT NULL auto_increment - I'd like to fix all extensions but do not know howto :D
(issue imported from #M416)

0000416-bug_416.diff View (584 Bytes) Administrator Admin, 2004-11-15 22:27

0000416-class.t3lib_install.php.diff View (600 Bytes) Administrator Admin, 2005-02-04 19:26

0000416-class.t3lib_install.php.part2.diff View (747 Bytes) Administrator Admin, 2005-02-04 20:01

class.t3lib_install_4.diff View (2.2 KB) Administrator Admin, 2005-04-07 18:06

class.t3lib_install_5.diff View (3.03 KB) Administrator Admin, 2005-04-07 21:51

class.t3lib_install__beta2.diff View (1007 Bytes) Administrator Admin, 2005-04-18 11:49


Related issues

Related to TYPO3 Core - Bug #14139: Install tool is incapable of comparing modern database dumps Closed 2004-05-03
Duplicated by TYPO3 Core - Bug #14378: MySQL 4.1.7 does not tolerate DEFAULT '0' with AUTO_INCREMENT Closed 2004-11-08
Duplicated by TYPO3 Core - Bug #14422: bug with MySQL 4.1.7 Closed 2004-11-30
Duplicated by TYPO3 Core - Bug #14528: Mysql 4.1 bug 416 still buggy Closed 2005-02-03
Duplicated by TYPO3 Core - Bug #14529: Cannot create static tables containing data with MySQL 4.x Closed 2005-02-03
Duplicated by TYPO3 Core - Bug #14645: Database fields don't get updated Closed 2005-04-07
Duplicated by TYPO3 Core - Bug #14876: Warning: mysql_fetch_assoc() error Closed 2005-07-22
Duplicated by TYPO3 Core - Bug #15295: Unable to login into BE when using MySQL 5+ Closed 2005-12-19

History

#1 Updated by miikaawaadizi over 15 years ago

This isn't trivial, this is more worth critical.

Every extention created with the kickstarter is automatically writing DEFAULT '0' into the uid auto_increment column, and MySQL 4.1.* refuses to accept it. That means either having to go manually through every extension you want to install, strip out the SQL, remove the DEFAULT '0' from every uid line and insert it manually (which is time consuming), or don't install extensions.

What would be ideal is if the extension manager parsed the CREATE statement of every extension when installing, rewrote the CREATE statement and removed the DEFAULT '0' before feeding it to the database. There's no valid reason for an auto_increment field to be set to DEFAULT '0' anyways, AFAIK.

Guys, this one needs bumping up to somewhere at the top of the list.

Kathi

#2 Updated by Stefan Kreisberg over 15 years ago

Ok, sorry for the wrong status. THis is one of the first bugs I report. Dunno even how to change it from trivial to critical. Anyways it shouldnt cause a great headache to replace the default '0' when installing, i.e. str_replace() should do the trick.

Another thing is: what about the kickstarter? I know it writes SQL but perhaps it needs modifications better sooner than later...

#3 Updated by Ingmar Schlecht over 15 years ago

I agree this bug is critical. A fix is likely to be shipped with 3.7.1.

#4 Updated by Ingmar Schlecht over 15 years ago

I have attached a fix that deletes DEFAULT '0' within the CREATE statement of fields named "uid" having "auto_increment".

I suppose this fix will catch most of the wrong SQL statements, but possibly not all.
For example a lower-case default '0' would not be caught. A field named "id" having auto_increment would also not be caught, only "uid" will.

#5 Updated by Michael Stucki over 15 years ago

Have a look at bug #495 that fixes the problem on the kickstarter side.

#6 Updated by Ingmar Schlecht over 15 years ago

OK, I'll commit that to kickstarter CVS soon, but I still think it's necessary to fix t3lib_install as well so it doesn't execute those bugus SQL statements any more.

Remember that still many extensions do have this DEFAULT '0' in the definition of the uid field.

#7 Updated by Michael Stucki over 15 years ago

Yes, of course. Both make sense.

#8 Updated by Ingmar Schlecht over 15 years ago

I just fixed this in HEAD. The fix will probably be ported to the 3.7 branch as well.

#9 Updated by Michael Stucki about 15 years ago

Ingmar writes:
--- cut ---
I have attached a fix that deletes DEFAULT '0' within the CREATE statement of fields named "uid" having "auto_increment".

I suppose this fix will catch most of the wrong SQL statements, but possibly not all. For example a lower-case default '0' would not be caught. A field named "id" having auto_increment would also not be caught, only "uid" will.
--- cut ---

User "pruss" was wondering why this only happens in "uid" fields and not for all. I think he's right, the rewrite should be done on every field that contains "auto_increment" and "default 0".

However there's a little risk that a text field could contain this value in its context, so it should also be checked if the field is an integer field (there are different types for that, though).
UPDATE: Stupid me, this doesn't count of course. Simply make sure that only "create table" and "alter table" commands are being watched (which I think is the case already).

edited on: 04.02.05 17:54

#10 Updated by Peter Russ about 15 years ago

Your right with fields, where the default is like
default "autoincrement default '0'". This default values would be resetted. But I see no alternative without some rewrite of the method or sofisticated regexp. Perhaps that the better solution.

But with the fix provided at 747 the user could at least set up such important extensions as sys_messages or sys_todos without any DB problems. So what's more important?

edited on: 04.02.05 18:14

#11 Updated by Michael Stucki about 15 years ago

The first priority is that no content gets changed by mistake.

Imagine a SQL line like this:

INSERT INTO .... VALUES .... "This is my text about auto_increment and default '0' blablabla...";

So could you please run a debug to see which SQL statements get into $linecontent? If there are only "ALTER TABLE" and "INSERT TABLE" commands then I think we can safely remove the "uid" check condition.

(While working on this, please also consider to make all checks case-insensitive which is not the case yet I think...)

#12 Updated by Peter Russ about 15 years ago

the change is only in method getUpdateSuggestions (line 454). This method only uses 'alter table', 'drop table' and 'create table'.

Thanks for the hint of case sensitivity. Add.

#13 Updated by Michael Stucki about 15 years ago

Added a new patch version against HEAD. Basically it is pretty much the same like yours, Peter. It's just a little bit beautified... :-)

#14 Updated by old_webbo about 15 years ago

Uploaded class.t3lib_install.php.part2.diff which is copied accross from #14529 to resolve the same issue but in the getCreateTables method.

See #14529 for a bigger explanation.

#15 Updated by old_toph about 15 years ago

If you do your replacement not just for the 'uid' fields, but simply for all field definitions containing 'auto_increment', this fix will also take care of extensions like realurl, where the uid has a different field name.

#16 Updated by Peter Russ about 15 years ago

toph,

that's already fixed s. class.t3lib_install.php.diff and class.t3lib_install.php.part2.diff

Enjoy.

#17 Updated by Michael Stucki about 15 years ago

Citing "pruss" since he posted this in bug #0000748 right when I was closing it...

--- cut ---
if (TYPO3_OS=='WIN')    {$table=strtolower($table);}    // tablenames are always
lowercase on windows!

THIS is not right, because it depends on your setup of MySql. So you can
keep  to have tablenames consistent to other OS.
--- cut ---

Since this line was existing even before these patches I think it doesn't really hurt...(?)

#18 Updated by old_strangefruit almost 15 years ago

It would be useful if there was some concise information on what to do with these files. Where do they go precisely? I am new to typo3 and finding that even a db (doing my quickstart install, MySQL issued 18 warnings!) setup is already a problem is not very encouraging at all. Why can't the sql setup file be fixed and offered for separate download?

edited on: 29.03.05 12:15

#19 Updated by Stefan Kaufmann almost 15 years ago

After some troubleshooting and reading all posts I uploaded the class.t3lib_install.php from CVS and it worked. Did not test everything but I was able to add tables at least. Using MySQL 4.1.10a, Typo 3.7. Hope this helps anyone else struggling with the issue. I know this should maybe posted on the list, but during my troubleshooting I ended up here, thats why I add it here. Thanks for all helping to sort this one out.
Half of the patches did not work properly using the GNU 'patch' tool. At least om y Suse 9.2 machine...

#20 Updated by Rupert Germann almost 15 years ago

I would suggest to close this bug or bug 495. The patches from this bug (416) are only fixing a part of the mysql 4.1 problem. The the last patch from bug 495 is fixing the complete problem which concerns also the file class.tx_kickstarter_compilefiles.php

or even better: it would be great, if the fixes from bug 495 could be integrated in TYPO3 3.8 beta2 so both bugs could be closed.

#21 Updated by Michael Stucki almost 15 years ago

Hi Rupi et all,
I will look at this problem tomorrow. Will test if everything works right and resolve both at the end of the day (hopefully) :-))

#22 Updated by Rupert Germann almost 15 years ago

THANX !!

#23 Updated by Rupert Germann almost 15 years ago

I made a new patch against latest cvs version: class.t3lib_install_4.diff
this patch is a bit shorter as the last one but it works for me (MySQL 4.1.10) .

that's what I tested:
- installing/updating extensions with wrong default values in their uid-lines > ok
dumping sql files from ext-manager Backup/Delete > ok
table comparison in the install tool. with or without '$changedUnsignedFields' set -> ok

the replace at line 519 seems not to be needed since the wrong default values in field definitions have been already replaced in function getFieldDefinitions_sqlContent(). Or they have not been added in function assembleFieldDefinition().

so that could it be finally ?

#24 Updated by Rupert Germann almost 15 years ago

and again ;-)

One thing I did not test: import of static tables.
this fails with my last patch because I removed one eregi_replace to much.

#25 Updated by Rupert Germann almost 15 years ago

patch nr 5 fixes the problem

#26 Updated by Michael Stucki almost 15 years ago

The whole problem is finally (hopefully!) solved.
Many thanks to Ingmar Schlecht, Rupert Germann and whoever I forgot for their valuable help.

Please test TYPO3 3.8.0beta2 and post a note if there are still problems with DEFAULT parameters in auto_incremented SQL lines.

#27 Updated by Rupert Germann almost 15 years ago

import of static data is still not working.
If I click in the install tool on the "Import the whole file 'CURRENT_STATIC' directly:" or on one of the single "import static data" checkboxes, all tables with static data will be deleted.

I have time to make a patch this afternoon.

#28 Updated by Rupert Germann almost 15 years ago

The latest patch fixes the problem for beta2.

(patch is made against beta2 and NOT against CVS)

#29 Updated by Rupert Germann almost 15 years ago

beta2.1 works like a charm ;-)
tested:
- fresh install
- updating a 3.7.0 installation
- installing extension with wrong default values in autoinc lines.

mysql 4.1.11

#30 Updated by Rob Kamp almost 15 years ago

Could somebody tell me please if there is a fix for 3.7.0 and where to find it?

Rob Kamp

#31 Updated by Peter Niederlag almost 15 years ago

Hi imho a tiny problem still remains in current CVS HEAD

INSTALL-Tool/Compare still complains about "changing fields". :-<

[update: removed some stuff not belonging here]

I think this needs a Fix for 3.8 and 3.7 as well

#32 Updated by Rupert Germann almost 15 years ago

finally fixed in CVS

#33 Updated by Peter Niederlag almost 15 years ago

I don't really like to spoil the party but I feel it still is not working quite as intended. .-<

At first looks good, now please try the following:
  • add some extension which adds some DB-Stuff
  • remove extension again(first let EM create tables/fields of course)

Supposed to COMPARE would show you additional fields which are not in $TCA an let you easily remove/rename them. Now it's blank. .-<

Compare $TCA will still find unneeded stuff but not the "regular" COMAPRE which will compile those update statements, that I find useful. ;)

tried with EXT:tt_adress, latest CVS HEAD from now (rev 1.30 of t3lib_install with rupis latest changes)
Peter

#34 Updated by Rupert Germann almost 15 years ago

Hi Peter
I did the same what you described but it works for me. I installed and uninstalled tt_address, opened "database compare" in the install tool and clicked on the upper COMPARE link. As expected the installtool suggests to rename the tt_address table:

ALTER TABLE tt_address RENAME zzz_deleted_tt_address;

with the changes from today the class.t3lib_install is now at the same state as the one from beta2.1. Can reproduce the error in beta2.1 too ?

#35 Updated by Peter Niederlag almost 15 years ago

sorry I forgot to put this bug on my watchlist, so missed your note :-<

OK, works here fine now with HEAD as of today (may-8-2005). plz don't ask me why it didn't on 4-28-2005.

I will try to get this fix into 3.7 now.

Thx,
Peter

#36 Updated by Peter Niederlag almost 15 years ago

sorry I forgot to put this bug on my watchlist, so missed your note :-<

OK, works here fine now with HEAD as of today (may-8-2005). plz don't ask me why it didn't on 4-28-2005.

I will try to get this fix into 3.7 now.

Thx,
Peter

Also available in: Atom PDF