mysql --ansi cannot import install database

Posted by ax on December 11, 2002 at 9:36pm

3 followers

Issue Summary

> 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

#1

Posted by ax on January 1, 2003 at 4:08pm

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

#2

Posted by ax on January 4, 2003 at 3:18pm

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

#3

Posted by Dries on January 26, 2003 at 12:09am

Version:

<none>

- Drupal 4.1.0: bug fixed. Patch by Ax.

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

#4

Posted by ax on January 29, 2003 at 3:37pm

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_*"

#5

Posted by ax on February 24, 2003 at 11:47pm

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).

Project:	Drupal core
Version:	x.y.z
Component:	Other
Category:	bug report
Priority:	critical
Assigned:	Unassigned
Status:	closed (fixed)

mysql --ansi cannot import install database

Jump to:

Issue Summary

Comments

#1

#2

#3

#4

#5

#6

#7

#8

#9

#10