By bawdo2001 on

Hello All,

At the top of all "create XXX" pages (as in "create book pages" et cetera" I get an error message similar to the following:

user error: You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'types LIKE '%page%' ORDER BY weight, name' at line 1
query: SELECT * FROM vocabulary WHERE types LIKE '%page%' ORDER BY weight, name in /var/www/html/drupal/includes/database.mysql.inc on line 69.

I'm using
Drupal 4.1.0
Apache 2.0.40-11.3
MySQL 4.1.0-alpha
php 4.2.2-8.0.7

I have set all php up as per the drupal installation guide, and have searched through the Drupal forums for a possible solution for the past several hours to no avail.

Any help would be appreciated.

Regards, Bawdo2001

Comments

ax’s picture

ax commented

(i think) the query is failing because "types" is a reserved word in mysql 4. it wasn't in mysql 3 (there, it was "type").

fix: change "types" (and, to be sure, "type" also) to a both mysql 3 and 4 non reserved word such as "voc_type" in both code and database. see also the links mentioned here (search for "database naming schemes") for suggested database naming conventions. if we would use something like that (especially, prefix every db field with a short table name prefix a la "voc_type", "nod_type", etc), we wouldn't have problems like this. (besides, it would be possible to auto-generate nice ER-diagrams ...).

bawdo2001’s picture

bawdo2001 commented

Types is definately a reserved word.

I have renamed the field in the vocabulary table and changed all appropriate references to the former field name.

This has fixed the problem.

bawdo2001’s picture

bawdo2001 commented 
G'day All,

Here are the changes that I made to my install of Drupal-4.1.0 to get around the reserved word problem:

<h2>#######################
Changes to the code
#######################</h2>

<h2>changes to update.php</h2>
CHANGED

types TEXT,  

TO --->   

<strong>voc_types TEXT,</strong>


CHANGED

db_query("INSERT INTO vocabulary SET vid = '$offset', name = '$c->name', types = '". str_replace(" ", "", $c->types) ."'");

TO --->

<strong>db_query("INSERT INTO vocabulary SET vid = '$offset', name = '$c->name', voc_types = '". str_replace(" ", "", $c->voc_types) ."'");</strong>


<h2>changes to /modules/taxonomy.module</h2>
CHANGED

$data = array("name" => $edit["name"], "types" => @implode(",", $edit["types"]), "description" => $edit["description"], "multiple" => $edit["multiple"], "required" => $edit["required"], "hierarchy" => $edit["hierarchy"], "relations" => $edit["relations"], "weight" => $edit["weight"]);

TO --->

<strong>$data = array("name" => $edit["name"], "voc_types" => @implode(",", $edit["types"]), "description" => $edit["description"], "multiple" => $edit["multiple"], "required" => $edit["required"], "hierarchy" => $edit["hierarchy"], "relations" => $edit["relations"], "weight" => $edit["weight"]);</strong>


CHANGED

$result = db_query("SELECT * FROM vocabulary WHERE types LIKE '%%%s%%' ORDER BY weight, name", $type);

TO --->

<strong>$result = db_query("SELECT * FROM vocabulary WHERE voc_types LIKE '%%%s%%' ORDER BY weight, name", $type);</strong>


CHANGED
$c = db_query("SELECT * FROM vocabulary WHERE types LIKE '%%%s%%' ORDER BY weight, name", $type);

TO --->

<strong>$c = db_query("SELECT * FROM vocabulary WHERE voc_types LIKE '%%%s%%' ORDER BY weight, name", $type);</strong>


<H2>#######################
Changes to the MySQL DB
#######################</H2>

CHANGED
CREATE TABLE vocabulary (
  vid int(10) unsigned NOT NULL auto_increment,
  name varchar(255) NOT NULL default '',
  description text,
  relations tinyint(3) unsigned NOT NULL default '0',
  hierarchy tinyint(3) unsigned NOT NULL default '0',
  multiple tinyint(3) unsigned NOT NULL default '0',
  required tinyint(3) unsigned NOT NULL default '0',
  types text,
  weight tinyint(4) NOT NULL default '0',
  PRIMARY KEY  (vid)
) TYPE=MyISAM;

TO --->
<strong>
CREATE TABLE vocabulary (
  vid int(10) unsigned NOT NULL auto_increment,
  name varchar(255) NOT NULL default '',
  description text,
  relations tinyint(3) unsigned NOT NULL default '0',
  hierarchy tinyint(3) unsigned NOT NULL default '0',
  multiple tinyint(3) unsigned NOT NULL default '0',
  required tinyint(3) unsigned NOT NULL default '0',
  voc_types text,
  weight tinyint(4) NOT NULL default '0',
  PRIMARY KEY  (vid)
) TYPE=MyISAM CHARSET=latin1;
</strong>

I hope this helps. Regards, Bawdo2001