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.
Another option
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 ''.
Another option
Instead of chaning my MySQL install, I change your database.mysql file. Navigate to the folder
webroot\documents\drupal_download\database
and open the database.mysql file.
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 the text in both pages and types. You can use the one from module as 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.