multiple-column primary key

Discussion on a SQL Parser for EasyMOD
Locked
MindFreakz
Registered User
Posts: 2
Joined: Mon Oct 17, 2005 6:37 pm

multiple-column primary key

Post by MindFreakz » Mon Oct 17, 2005 6:49 pm

If you give a sql-statement, which contains a primairy key, that contains more than one column, EM gives a error.
The sql runs fine using phpMyAdmin.

Code: Select all

CREATE TABLE phpbb_chatbox_auth (
	`chat_id` int(11) NOT NULL,
	`group_id` int(11) NOT NULL,
  	`speak` tinyint(3) default '0',
	PRIMARY KEY ( `chat_id`, `group_id` )
);
The error is:

Code: Select all

FATAL ERROR&#58 Unable to parse SQL statement; [KEY ( `user_id` ]


CREATE TABLE phpbb_chatbox_session ( `user_id` int(11) NOT NULL, `chat_id` int(11) NOT NULL, `lastactive` int(10) DEFAULT '0' NOT NULL, PRIMARY KEY ( `user_id`, `chat_id` ) )
Last, a reference from MySQL explaining primary key:
http://dev.mysql.com/doc/refman/5.0/en/create-table.html wrote: A PRIMARY KEY can be a multiple-column index. However, you cannot create a multiple-column index using the PRIMARY KEY key attribute in a column specification. Doing so only marks that single column as primary. You must use a separate PRIMARY KEY(index_col_name, ...) clause.

User avatar
markus_petrux
Registered User
Posts: 376
Joined: Fri Jun 18, 2004 10:58 pm
Location: Girona, Catalunya (Spain)
Contact:

Re: multiple-column primary key

Post by markus_petrux » Tue Oct 18, 2005 2:23 pm

Thanks for the report. This is actually fixed with the new SQL Parser. You can check this using the online conversion tool. That last step of this tool generates a db_update.php script that you could use to apply the SQL changes of that MOD. However, please make backups of your database. The SQL Parser is not finished and so, it may leave your board unstable.

MindFreakz
Registered User
Posts: 2
Joined: Mon Oct 17, 2005 6:37 pm

Re: multiple-column primary key

Post by MindFreakz » Mon Nov 07, 2005 5:41 pm

I found another bug, this time, it's in the on-line conversion tool (don't know about the current easymod version).
If you proces a string (with offcourse single-quotes), it doesn't addslashes to it, when it's creating the php-page:

the query:

Code: Select all

INSERT INTO phpbb_chatbox (chat_name) VALUES ('Public chat');
the result:

Code: Select all

$sql = array();
$sql[] = 'INSERT INTO ' . $table_prefix . 'chatbox(chat_name) VALUES('Public chat')';
$sql_count = count($sql);

User avatar
markus_petrux
Registered User
Posts: 376
Joined: Fri Jun 18, 2004 10:58 pm
Location: Girona, Catalunya (Spain)
Contact:

Re: multiple-column primary key

Post by markus_petrux » Mon Nov 07, 2005 6:04 pm

Yep! That's a bug in the online conversion tool. It should be fixed now.

Thanks :)

Locked