Error 1364 upon importing database.mysql with MySQL 5.0+
Symptom
When you use the file database/database.mysql to create a Drupal database, MySQL reports the following error:
error 1364 (hy000) line 803: field 'page' doesn't have a default value.This error is caused by the following lines in database.mysql:
REPLACE blocks SET module = 'user', delta = '0', status = '1';
REPLACE blocks SET module = 'user', delta = '1', status = '1';You may have additional problems when working with Drupal. Sidebars will be absent and posting content results in an error:
user error: Field 'revisions' doesn't have a default value [snip]Cause
MySQL 5.0 and higher have a strict mode that is currenty incompatible with a number of queries in Drupal. The Windows Installer from MySQL.com enables this strict mode by default.
Workaround
There are several workarounds
- Replace in my.ini the current sql-mode line with
sql-mode="MYSQL40" - Start MySQL with the option
--sql-mode="MYSQL40" - Execute the query
SET GLOBAL sql_mode='MYSQL40'
MySQL needs to be restarted before changes in my.ini have an effect. My.ini can be found in the MySQL installation directory or the Windows directory, depending on your configuration.
Note: After switching MySQL mode you have to recreate your Drupal database or your site will not function properly.
Drupal and strict mode
If you don't want (or can't) switch the mode, you may do the following :
Replace the offending lines in the database script :
REPLACE blocks SET module = 'user', delta = '0', status = '1', pages = '', types = '';REPLACE blocks SET module = 'user', delta = '1', status = '1', pages = '', types = '';
Change the table node so that the column revision gets the default value ''.
It worked so far for me, but I'm new to drupal and haven't used much of the functionality yet, so maybe more changes are necessary.
Won't work
There are more incompatible queries:
--
Tips for posting to the forums.
When your problem is solved, please post a follow-up to the thread you started.
A Seemingly Easier Fix
Instead of chaning my MySQL install, I changed my database.mysql file. I got a new version of drupal-4-6 from cvs, navigated to the folder
webroot\documents\drupal_download\databaseand opened the database.mysql file.
You only have to add two new default entries in table block. Originally it is:
CREATE TABLE blocks (module varchar(64) DEFAULT '' NOT NULL,
delta varchar(32) NOT NULL default '0',
status tinyint(2) DEFAULT '0' NOT NULL,
weight tinyint(1) DEFAULT '0' NOT NULL,
region tinyint(1) DEFAULT '0' NOT NULL,
custom tinyint(2) DEFAULT '0' NOT NULL,
throttle tinyint(1) DEFAULT '0' NOT NULL,
visibility tinyint(1) DEFAULT '0' NOT NULL,
pages text NOT NULL,
types text NOT NULL
) TYPE=MyISAM;
Add
DEFAULT ''(those are single quotations, and no space in between) after thetextin bothpagesandtypes. You can use the one frommoduleas a guide.Now you can use either your command line or web based admin to create a new database and import the database.mysql file. I did this and my site ran just fine.