> mysql drupal-4_1_0 < databasedatabase.mysql
ERROR 1064 at line 236: You have an error in your SQL syntax near 'mid (mid)
) TYPE=MyISAM' at line 6

line 236 is this:

CREATE TABLE moderation_roles (
  rid int(10) unsigned NOT NULL default '0',
  mid int(10) unsigned NOT NULL default '0',
  value tinyint(4) NOT NULL default '0',
  KEY rid (rid),
  KEY mid (mid)
) TYPE=MyISAM;

reason: well - MID() is a mysql-function ...

fix:

CREATE TABLE moderation_roles (
  rid int(10) unsigned NOT NULL default '0',
  mid int(10) unsigned NOT NULL default '0',
  value tinyint(4) NOT NULL default '0',
  INDEX idx_rid (rid),
  INDEX idx_mid (mid)
) TYPE=MyISAM;

notes:
- i suggest to always name indexes idx_&lt;col_name&gt; to avoid such errors in the future
- i renamed KEY to INDEX because this is more standard

Comments

ax’s picture

this still doesn't work in latest cvs - please fix (it's just some chars to add). thanks

ax’s picture

just a clarification: "mysql --ansi" means mysql running in ansi mode

another issue i just noticed with the latest rc: when importing database.mysql with phpMyAdmin, i get the following error:

You have an error in your SQL syntax near '------------------------[...]
--
--

CREATE TABLE ac' at line 2

this is reasonable as the manual states: Note that the -- (double-dash) comment style requires you to have at least one space after the second dash - which isn't the case in the latest database.mysql

Dries’s picture

- Drupal 4.1.0: bug fixed. Patch by Ax.

- Drupal CVS: bug open. We should rename "mid" to "vid" (vote ID).

ax’s picture

thanks dries. two remarks:

  • the 4.1.0 fix doesn't fix the problem that phpMyAdmin cannot import the install database because of the bad "---------..." (christ - why doesn't the site display the hyphens!) comment in line 4. you may consider changing it to "--<space>--------..." or use something different than mysqldump (which generates the "---------...") to generate the database file. see http://www.mysql.com/doc/en/ANSI_diff_comments.html
  • of course we can 'rename "mid" to "vid" (vote ID)'. this doesn't solve the problem of (accidentally) generating invalid index names, though. there are many functions that may have the same name as the table column we want to generate an index for (see http://www.mysql.com/doc/en/Function_Index.html), and we would have the same problem as mentioned before. always using "idx_<col_name>" for the index name completely solves the problem - there is no function "idx_*"
ax’s picture

a phpMyAdmin fix went into cvs today - this issue is almost solved now. now we only need to decide what to do with "KEY mid (mid)" in cvs database.mysql which is invalid because "mid()" is a function. dries suggested to just rename "mid" to "vid"; i suggest to change "KEY mid (mid)" to "INDEX idx_mid (mid)" (or "idx_vid (vid)" - i don't care) /and to apply this naming scheme to all indexes/ - see above / below for why.

see also http://www.ss64.demon.co.uk/orasyntax/naming.html and http://vyaskn.tripod.com/object_naming.htm/ for some background, rationales, and suggestions for database naming schemes (which i'd like too see, at least to a certain extend, to be applied to drupal).

ax’s picture

note that the PostgreSQL database.pgsql scheme already uses a similar convention: "CREATE INDEX book_nid_idx ON book(nid);"

zw’s picture

For the table or colum names the manual suggests to enclose them in ` (backtick) - ie. SELECT `CREATE` FROM `SELECT`. phpMyAdmin has an option to add these to all names when exporting (including indexes).

Anonymous’s picture

Is this issue resolved?

ax’s picture

ax’s picture

fixed (completely) on 2003/03/16 (KEY mid (mid) -> KEY idx_mid (mid)). closing.