Early Bird Registration for DrupalCon Portland 2024 is open! Register by 23:59 PST on 31 March 2024, to get $100 off your ticket.
> 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_<col_name>
to avoid such errors in the future
- i renamed KEY
to INDEX
because this is more standard
Comments
Comment #1
ax CreditAttribution: ax commentedthis still doesn't work in latest cvs - please fix (it's just some chars to add). thanks
Comment #2
ax CreditAttribution: ax commentedjust 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:
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
Comment #3
Dries CreditAttribution: Dries commented- Drupal 4.1.0: bug fixed. Patch by Ax.
- Drupal CVS: bug open. We should rename "mid" to "vid" (vote ID).
Comment #4
ax CreditAttribution: ax commentedthanks dries. two remarks:
Comment #5
ax CreditAttribution: ax commenteda 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).
Comment #6
ax CreditAttribution: ax commentednote that the PostgreSQL database.pgsql scheme already uses a similar convention: "CREATE INDEX book_nid_idx ON book(nid);"
Comment #7
zw CreditAttribution: zw commentedFor 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).
Comment #8
(not verified) CreditAttribution: commentedIs this issue resolved?
Comment #9
ax CreditAttribution: ax commentedno - dries want's a patch. see http://lists.drupal.org/pipermail/drupal-devel/2003-February/022204.html .
Comment #10
ax CreditAttribution: ax commentedfixed (completely) on 2003/03/16 (KEY mid (mid) -> KEY idx_mid (mid)). closing.