Data type text/blob do not take default values [#97510]

after installation the following error occurs

user warning: Table 'drupal.blocks' doesn't exist query: SELECT DISTINCT b.* FROM blocks b LEFT JOIN blocks_roles r ON b.module = r.module AND b.delta = r.delta WHERE b.theme = 'garland' AND b.status = 1 AND (r.rid IN (1) OR r.rid IS NULL) ORDER BY b.region, b.weight, b.module in E:\Discount Club\ApacheDocuments\drupal-5.0-beta1\includes\database.mysql.inc on line 167.

Comments

Comment #1

vkde commented 25 November 2006 at 13:08

Version:

5.0-beta1

This situation happens because the SQL script cannot properly create all tables in MySQL. My installation is based on MySQL 5.0.27-community-nt running on Windows 2003 Server. I created the drupal database manually, used the provided script 'database/database.4.1.mysql', renamed it to .sql and imported it with the 'Restore' tools in the MySQL Administrator (Part of MySQL GUI Tools for Windows, ver. 5.0-r5). The error messages are:

Warning: Error while executing this query:

CREATE TABLE blocks (
  module varchar(64) DEFAULT '' NOT NULL,
  delta varchar(32) NOT NULL default '0',
  theme varchar(255) NOT NULL default '',
  status tinyint(2) DEFAULT '0' NOT NULL,
  weight tinyint(1) DEFAULT '0' NOT NULL,
  region varchar(64) DEFAULT 'left' 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 DEFAULT '' NOT NULL
)
DEFAULT CHARACTER SET utf8 DEFAULT CHARSET=utf8;

The server has returned this error message:
BLOB/TEXT column 'pages' can't have a default value

-----------------

Warning: Error while executing this query:

CREATE TABLE contact (
  cid int(10) unsigned NOT NULL auto_increment,
  category varchar(255) NOT NULL default '',
  recipients longtext NOT NULL default '',
  reply longtext NOT NULL default '',
  weight tinyint(3) NOT NULL default '0',
  selected tinyint(1) NOT NULL default '0',
  PRIMARY KEY (cid),
  UNIQUE KEY category (category)
)
DEFAULT CHARACTER SET utf8 DEFAULT CHARSET=utf8;

The server has returned this error message:
BLOB/TEXT column 'recipients' can't have a default value

-----------------

Warning: Error while executing this query:

CREATE TABLE node_revisions (
  nid int(10) unsigned NOT NULL,
  vid int(10) unsigned NOT NULL,
  uid int(10) NOT NULL default '0',
  title varchar(128) NOT NULL default '',
  body longtext NOT NULL default '',
  teaser longtext NOT NULL default '',
  log longtext NOT NULL default '',
  timestamp int(11) NOT NULL default '0',
  format int(4) NOT NULL default '0',
  PRIMARY KEY  (vid),
  KEY nid (nid),
  KEY uid (uid)
)
DEFAULT CHARACTER SET utf8 DEFAULT CHARSET=utf8;

The server has returned this error message:
BLOB/TEXT column 'body' can't have a default value

-----------------

Warning: Error while executing this query:
INSERT INTO blocks (module, delta, theme, status, pages) VALUES ('user', '0', 'bluemarine', '1', '');
The server has returned this error message:
Table 'drupal.blocks' doesn't exist

-----------------

Warning: Error while executing this query:
INSERT INTO blocks (module, delta, theme, status, pages) VALUES ('user', '1', 'bluemarine', '1', '');
The server has returned this error message:
Table 'drupal.blocks' doesn't exist

-----------------

The same errors are reported if you try to create the tables as SQL commands, using MySQL Query Browser.

If you execute the CREATE commands without the --default ''-- in the corresponding fields, the tables are created, and then you can execute the omitted INSERTs. Drupal does not give error messages anymore and seems to work fine, at least on the surface, developers should say whether taking the defaults off would break any application logic.

This post is to explain the cause of the problem on MySQL 5.0 / Windows, intended more of a hint for the developers, rather than an end-user solution.

-----------------
http://vkde.rothramus.net/