TYPO3 Core

Funny guy. Either your setup is wrong or Mysql made again an incompatible change.

If you cannot give any hint why this is broken, please, give at least pointer to thise "forums" and the "internet" where this problem has been discussed.

SHOUTING will get us nowhere, it just leaves a bad taste.

Dear Martin

I found some hints under:
http://typo3.org/documentation/mailing-lists/english-main-list-archive/thread/110185765/?tx_maillisttofaq_pi1%5Bsword%5D=BE%20login&tx_maillisttofaq_pi1%5Banswered_only%5D=0&tx_maillisttofaq_pi1%5Bmode%5D=1
All of the good advices within this tread showed me, that there must be a problem using TYPO3 and the MySQL server 5+ (or already 4.1+?).

It may be, that the problem is placed within the corner of MySQL, that's right.
Excuse my "shouting", but i'm angrily, because i checked the official TYPO3 system requirements under: http://typo3.org/1275.0.html and there wasn't any note, that i should use MySQL XYZ instead of a.e. MySQL 5+, that's all...

Greetings

Dear Martin

In the meantime i installed MySQL 4.0.26 on the same server and tried the TYPO3 environment SUCCESSFULLY WITHIN MINUTES - so it must be a problem between TYPO3 3.8.1 and MySQL ?4.1? or MySQL 5.+.

One thing what i can clearly say/trace back/verify: SQL statements used a.e. within the SQL script file /typo3conf/database.sql which are using the statement:
...
uid int(11) unsigned DEFAULT '0' NOT NULL auto_increment
...
doesn't work under MySQL 5+ (maybe also not under 4.1+) because it's not allowed to define a default value when defining auto_increment.
It should be:
...
uid int(11) unsigned NOT NULL auto_increment
...

So it would be nice if a corresponding statement can be found under the system requirements list - i guess ;-).

Greetings

Meanwhile the auto_increment vs. default issue has been resolved. Is this bug still an issue?

Dear Karsten

In the meantime i'm using TYPO3 with MySQL 4.0.26 as a testsystem. This system is now "live" for our development team, so that i can not easy switch / reinstall the database or TYPO3 environment, unfortunately.

As i recognized on an other, own PHP/MySQL project, there are many subtle changes at MySQL 4+ -> 5+ at the level of the SQL syntax. So maybe it would be best practice if you make your own tests...

In my opinion, if any as the commented problem occurs, it should be a corresponding notice at the official TYPO3 system requirements page.

Greetings

Hi together,

I am trying to install the Beta 4.0 together with the dummy package.
Before I had updated my pht ->5.1.1 and mySQL -> 5.0.17.
I am running typo3 under Win Server 2003 and IIS.

I got the following errors:

1. Update Tables
After updating the tables, I got long list of Alter Tables, which I can't execute successful, because there is nothing to change.
The only differnces are, that in the current values the "NOT NULL" is missing. But if I access the tables via phpmyAdmin, the settings are correct and the NOT NULL is already set.

2. BE Login
I am also not able to log in in the BE.
Not wit user and password and also not with a new created user and password via Database Analyser->create new "admin" user.

So my experiences simular to acn.
In the next step I will switch back to my old mySQL 4.0.x to checkt it.

Maybe this helps,
Hans-Georg.

Hi,

I went back to mySQL 4.1.12 nt.
The problems with BE login and the NOT NULL by update tables are gone.
I only still have a problem with changing a table varchar to char. Also via phpmyadmin it doesn't work.
But I also did not find any table with set to CHAR. All set to VARCHAR.

Maybe a problem of the Beta 4.0 Dummy package.

So finally it seems to be a problem with mySQL 5.x !!!!

Greetings,
Hans-Georg.

Hans, do you have a proper DB connectoin? Mysql changed the way it stored the password for the Mysql users. You cannot simply upgrade from 4.0 or 3.23 without either upgrading the Mysql user accounts or starting Mysql with the compatibilty switch (see Mysql docs).

"DEFAULT '0' NOT NULL auto_increment" does not work starting from MySQL 4.1. See http://bugs.mysql.com/bug.php?id=392

Hi Dmitry,

thanks for your input.

But if I get this problem, does it meen, that this correction is not implemented in Beta 4 and I should report it as Beta 4 Bug?

Regards,
Hans-Georg.

Hi Martin,

due to the fact that I want start from scratch, I did not a Upgrade but instead I rebuild a new mySQL.

Therefore I schould not have any upgrade problems.
Do you agree?

Maybe the incompatibily issues from mySQL 5.015 created some problems.

Currently I want to use my time to get more familiar with typo3 and therefore I will currently stay with mySQL 4.1.x.

Regards,
Hans-Georg.

Dear H-G

As Dmitry Dulepov reported (and that's what i can confirm), there is a problem using TYPO3 with a MySQL 4.1+ database within the background at the level of the used SQL syntax which is executed by TYPO3 (auto_increment).

My opinion:
Be aware / don't use a MySQL DB higher then 4.0.x as the TYPO3 backend database, until a corresponding batch/fix at the level of the by TYPO3 used SQL syntax is available.

Again:
Unfortunately a corresponding remark can not be found at the TYPO3 system requirements list.

Greetings

Again: I am using MySQL 4.1.15 here with CVS/beta1 and have no problems with auto_increment and DEFAULT, as a fix for this has been added quite a while ago.

@Hans-Georg: a char vs. varchar problem might indeed exist, because MySQL does silent column specification changes (see MySQL manual). But: this is a cosmetic problem, not a bug, and should be solved by changing the field definition in the corresponding ext_tables.sql file.

As for MySQL 5: I have not yet tried this, but will do so ASAP.

Karsten, the report was about 3.8.1.

Are those changes in 3.8.1 or are they only in CVS?

Martin, thanks for pointing that out - I am so focused on 4.0, that I didn't even assume someone would post a bug on 3.8.1 in December. :)

See bug #14354 to see when this was fixed - the ChangeLog says:
---- snip ----
2004-11-16 Ingmar Schlecht <ingmar@typo3.org> * Fixed bug #1435416: Removing spurious DEFAULT '0' in SQL create table statements of uid columns right before they're executed in t3lib_install.
---- snip ----

3.8.0 was released on May 23rd 2005, the last update to that bug was May 8th - so this was (seemed to be?) fixed in 3.8.0 already.

I give some comment to this bug, after done some testing with different version of MySQL 5.0.x. Rest of my configuration is Apache 2.0.54, PHP 5.1beta, Win XP SP2. Using TYPO3 3.8.0, 3.8.1, 4.0beta.

My first comment! I think there are two different issues been put on the table here. One is the problem with BE-login, and the other differences in Compare tables in install tool.

When it comes to problem with BE-login I have no problem login with MySQL 5.0.12beta, but can't login with 5.0.13rc and 5.0.18. With the two later it isn't possible to make new admin users in install tool. So maybe there is a change in MySQL from 5.0.12 to 5.0.13 who makes the problem.

I was doing an ugrade from MySQL from 5.0.12 to 5.0.13 without logout from BE (Closing the browser) Then I had no problem open the backend. Logged out, and no login possible.

The problem regarding bug 416 isn't relevant here. As Karsten point out it's solved in version 3.8.0. I'm running 3 servers with TYPO3 3.8.1 and/or 4.0beta and MySQL 4.1.14 and 4.1.16. No compare problems here.

The mismatch in compare look like this in all 5.0.x versions of MySQL i have tested. The same for TYPO3 3.8.0, 3.8.1 an 4.0beta. Same problem

ALTER TABLE be_groups CHANGE uid uid(11) unsigned NOT NULL auto_increment;
Current value: int(11) unsigned auto_increment

And as I can see it is the same for every table and field, but if I check the tables with MySQL Administrator NOT NULL is checked.

But I can't tell anything about how to solve it.

I use it with MySQL 5+ and i have no problems, so the problem must be in your configuration.
It works like it worked before with MySQL 4.1 or older, only problem ist the default, but only as warning, not as error.

I can give some more information on the problem.

If I install/configure MySQL with utf8 as default charset i can upgrade MySQL from 4.1.16 to 5.0.12 to 5.0.13 to 5.0.18 without problem.

If I install/configure MySQL with latin1 as default charset I can't login as admin in backend in 5.0.13 and 5.0.18.

I have tested with TYPO3 3.8.1 Quickstart. I can't confirm that different charset are the problem, more testing is needed. I haven't had opportunity to test with more than XP SP2, PHP 5.1rc1 and PHP 5.0.4, so it's impossible to isolate the problem at this time.

It should be checked with user who have testservers with MySQL 5.0.x (works and not works), following.
- PHP version.
- MySQL version (5.0.x). There are differences form 5.0.12 to 5.0.18
- Used default charset in MySQL

ok, then i will report my working configuration (on Windows)

- MySQL Version 5.0.7-beta-nt-log
character_set_client latin1
character_set_connection latin1
character_set_database latin1
character_set_results latin1
character_set_server latin1
character_set_system utf8
collation_connection latin1_swedish_ci
collation_database latin1_swedish_ci
collation_server latin1_swedish_ci

PHP 4.3.10 and 5 (no problems occur depending on PHP Version)

I have the same problems as decribed earlier. My configuration is WinXP SP2 + Apache 2.0.55 + PHP 5.1.1 + MySQL 5.0.18
My SQL Server has been configured for charset latin1 so I reinstalled it and choose utf8 as default charset. After reinstalling Typo3 4.0 the problem persists. The install tool can import the database.sql file without problems and everything looks perfect but when you open the backend all login attempts fail.

The bigest mystery to me is that I can't create a new admin user in the install tool. When I try, the tool gives a sucessfull message but nothing apears in the database. If the insert query has failed normaly an error message should show up but nothing happens at all. Does MySQL have an error log file or something similar for debuging?

A friend of mine is working with MySQL 5.0.18 and typo3 under linux and everything works. Can anybody confirm that it is a windows only issue?

Hallo at all

I think I found a solution for the issue. I was confused about the fact that no new admins are created when requested in the install tool. I activated the query log on the server, copied the insert query for the new be_admin from there and executed it i phpmyadmin. The query fails because no usergroup value is given and a default value is not set for the field.
Looks like mysql 5.0 sets very high standards for correct sql syntax. So i remembered that the mysql setup wizard asked me if i would like to enable strict mode and i had selected yes. It seems like typo3 does not meet the requirements of strict mode at the moment. More information can be found here:
http://dev.mysql.com/doc/refman/5.0/en/server-sql-mode.html

I finaly edited the my.ini of the server and set the sql mode to MYSQL4. After a restart of the service everything works now, except for the phantom diverences reported in the database compare mentioned here earlier.

In the next release the typo3 team should check all sql querys for strict mode compatibility.

great, Manuel!
i updated an application (non typo) of mine to mysql 5 and had to recode some queries, yes..

OK, so what is the SQL mode you are using when it doesn't work? What is the output of "SELECT @@sql_mode;"?

Dear Michael,

the sql_mode string of the server had been as follows: STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

This is the default setting which is recomended by the setup wizard of mysql 5.0.18 for windows. In this mode INSERT querys must include values for all fields or the omited fields must have default values. Otherwise the query is rejected. This is the reason why no admin can be created with the install tool. I guess the BE login fails because the INSERT for the be_sessions table also fails. If MYSQL4 is used as sql_mode string everything works.

1. SELECT @@sql_mode;

OK, so what is the SQL mode you are using when it doesn't work? What is the output of "SELECT @@sql_mode;"? <<

As shown at the starting note, i tried TYPO3 DummyPackage 3.8.1 and QuickStart 3.8.1 with a MySQL DB 5.0.16 at the background (which i use in the meantime also for other, own applications).

Executing the presented SQL query returns:
STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

1. About the 'character-set issue':
   I'm not so shure about if it's a problem by the character-set setting, because within my used my.ini it's defined:

- CLIENT SECTION
[client]
...
[mysql]
default-character-set=utf8

- SERVER SECTION
[mysqld]
...
default-character-set=utf8

I'm totally confused ... !!

Dear acn@getunik.com,

your confusion can be solved. As I posted before your sql-mode setting is the reason why it does not work. You have to open your my.ini, find the line with the parameter sql-mode=STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION and change it to sql-mode=MYSQL4. Then restart the mysql sevice and typo3 should work. The character set seems not to have an effect on the issue.

Dear Manuel

Thanks a lot for your help!! I want to test-it later (probably next week...).

Unfortunately we use the MySQL DB 5.0.16 as a database server which supports other applications to. Several applications take advantage of extended MySQL 5+ features (stored procedures), so it woud not be possible to switch back to the "sql-mode=MYSQL4", i'm right?

Anyway, thanks a lot for your "real" help!!

Greetings

As a workaround you can execute a SET SESSION sql_mode='MYSQL4' after the connection is made to the Mysql server if you hack it into t3lib_db.

Anyway MYSQL4 mode doesn't turn off stored procedures etc It simply has a more relaxed syntax (as opposed to to ANSI SQL)!

Manuel has posted a link to the Mysql docs which describe the modes. Use it.

I think the workaround mentioned by Masi is the way to go.
Does anything speak against changing this in CVS core?

The solution suggested by Masi would be possible, but should probably only be used when running on MySQL5, to avoid potential problems when 4 or even 3.23.x is used.

But if we decide to drop the NOT NULL alltogether, this would become a non-issue (see thread on core list).

I didn't understand all of the NOT NULL issues, but in Mysql 3.23 there was a reason for them. You save one bit (for the NULL state) and it made a difference for indices. I recall that tze docs said that NOT NULL fields are faster as well. But anyway, I don't think that TYPO3 will gain so much percormance by the NOT NULLs.

As for SET SESSION sql_mode='MYSQL4', if we have a 3.8.2 I think we should use it there and drop the NOT NULLs for 4.0.

@Karsten Hachmeister: As far as I can see, the NOT NULL problem fixes the DB Compare stuff but not the BE login problems.

CONCLUSION ================

There are numerous MySQL issues, some of them are listed here, some are not (charset related stuff, for example).

My suggestion is to implement the following changes:

- Remove NOT NULL (seems every agrees on this)
- Add a new configuration option to TYPO3_CONF_VARS: SYS[setDBinit] which can hold a number of SQL queries which are called in $TYPO3_DB->sql_pconnect() right after the connection was done. This will solve all outstanding problems and gives us good chances to fix additional problems in future versions of MySQL.
- Users can now add the "SET SESSION sql_mode='MYSQL4'" to the above option to fix their problems
- The recommended setting is to set the sql_mode in the config file (saves one query per connection)
- In the long term, we should analyze the problem exactly to see why TYPO3 conflicts with some of these MySQL settings (copied from comment 5122): "STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

Questions:
- Who does what?

There was no feedback after my last comment. I would like to have a solution for this in Beta2, so what are your thoughts about that?

Hm, beware mysql_query can hold only one statement. If we don't want to parse the query, we need an array.

@Michael Stucki

comment #5238
Well, I understood that the login failed because an insert into the db failed. That insert failed because of an unspecified column that was declared NOT NULL (see comment #5068), something which MySQL didn't care about in earlier versions. So it should fix the login issue.

comment #5239
It conflicts with STRICT_TRANS_TABLES, the others only affect things TYPO3 doesn't care about (no GRANT in TYPO3 and it doesn't care about the table type).

Bottom line: remove the NOT NULL, add the config for the intial query, but make it an array (see comment #5378).

There is a new setting in TYPO3_CONF_VARS now; it can be used to set the SQL_MODE to a compatible choice.