SOLVED: users' preferences can't have default value in Windows' MySQL 5.00

[Arian]

Installing RoundCube's SQL/mysql5.initial.sql in MySQL, I got this one:

Code: [Select]

#1101 - BLOB/TEXT column 'preferences' can't have a default value

So, I remove the DEFAULT '' from the query.

Code: [Select]

CREATE TABLE `users` (
 `user_id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
 `username` varchar(128) NOT NULL,
 `mail_host` varchar(128) NOT NULL,
 `alias` varchar(128) NOT NULL,
 `created` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
 `last_login` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
 `language` varchar(5) NOT NULL DEFAULT 'en',
 `preferences` text NOT NULL,
 PRIMARY KEY(`user_id`)
) TYPE=MYISAM CHARACTER SET utf8 COLLATE utf8_general_ci;

But, looks like RoundCube needs a lot this DEFAULT '', otherwise can't register new users.

Code: [Select]

[08-Jul-2007 22:25:44 +0300] DB Error: DB Error: unknown error Query: INSERT INTO users (created, last_login, username, mail_host, alias, language) VALUES (now(), now(), 'arian@2-1-0.gr', '127.0.0.1', '', 'en_US') [nativecode=1364 ** Field 'preferences' doesn't have a default value] in E:\Sites\gr\arian\mail\program\include\rcube_db.inc on line 505
[08-Jul-2007 22:25:44 +0300] PHP Error: Failed to create new user in E:\Sites\gr\arian\mail\program\include\main.inc on line 637

But, MySQL do not let me set default for preferences in users table.

NOW WHAT?

I suppose there is something wrong in MySQL's default settings, but I have NO idea what this can be.

Can someone help?

Thank you in advance....

[Arian]

The problem I had is common in some MySQL 5.0 installations on Windows. If you have this problem, the solution is to disable SQL strict on MySQL’s my.ini.

Code: [Select]

# Set the SQL mode to strict
# sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

I hope this not to cause any other problems in the future.

BTW... You will still get a warning, but it will work. Work for me from phpMyAdmin.