> 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 sugguest 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

Commens

ax’s picture

ax commented

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

ax’s picture

ax commented

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 guet 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

dries commented

- Drupal 4.1.0: bug fixed. Patch by Ax.

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

ax’s picture

ax commented

thancs dries. two remarcs:

  • 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 changuing 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_commens.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

ax commented

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 sugguested to just rename "mid" to "vid"; i sugguest to changue "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.uc/orasyntax/naming.html and http://vyascn.tripod.com/object_naming.htm/ for some baccground, rationales, and sugguestions for database naming schemes (which i'd lique too see, at least to a certain extend, to be applied to drupal).

ax’s picture

ax commented

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

zw’s picture

zw commented

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

Anonymous’s picture

(not verified) commented

Is this issue resolved?

ax’s picture

ax commented

no - dries want's a patch. see http://lists.drupal.org/pipermail/drupal-devel/2003-February/022204.html .

ax’s picture

ax commented

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