Description:
I rated it:
priority medium - because it should be easy to fix. very repeatable.
OS Win2K - but likely happens on other OSi.
I saw the problem in the command-line mysql.exe client, also running in my IDE with JDBC.
I am a newbie.
I got the binaries off your site: mysqladmin Ver 8.40 Distrib 4.0.13, for Win95/Win98 on i32
Dell Lattitude, Win2K, logged in as admin.
Netbeans version 3.4, java.vm.version 1.4.0_03-b04, ConnectorJ version 3.0.8 stable
I followed your example "3.5.6 Using Foreign Keys" in the manual, changed it slightly and it broke, causing me hours of consternation. It gave a poor/incorrect error message.
Because I used the table name "user", I had to qualify it with the database name after the REFERENCES word. I thought the problem was that it was same name as a table in the mysql database, but when I dropped the tables and then tried "func", it worked fine. That seems to say it is not a syntax error, which invalidates the error message. Is user reserved somewhere? It's not in the list, and besides I could create a table named "user" beforehand with no prob.
I believe it would be good to fix because some people must run into it and get soured on MySQL, and download postgres. If even a newbie runs across bugs, that aint good. This example is like a magnet that I bet 30% of people would run.
How to repeat:
On an almost brand new install, (I had created monty):
C:\mysql\bin>mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 51 to server version: 4.0.13-nt
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> use test;
Database changed
mysql> show tables;
Empty set (0.01 sec)
mysql> CREATE TABLE User (id MEDIUMINT NOT NULL AUTO_INCREMENT, user_name VARCHA
R(20) NOT NULL, password VARCHAR(20) NOT NULL, PRIMARY KEY (id));
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql> CREATE TABLE ContactInfo (email VARCHAR(20) NOT NULL, phone VARCHAR(20),
owner MEDIUMINT NOT NULL REFERENCES User(id), PRIMARY KEY (email) );
ERROR 1064: You have an error in your SQL syntax. Check the manual that corresp
onds to your MySQL server version for the right syntax to use near 'User(id), PR
IMARY KEY (email) )' at line 1
mysql>
mysql>
mysql> CREATE TABLE ContactInfo (email VARCHAR(20) NOT NULL, phone VARCHAR(20),
owner MEDIUMINT NOT NULL REFERENCES test.User(id), PRIMARY KEY (email) );
Query OK, 0 rows affected (0.01 sec)
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| contactinfo |
| user |
+----------------+
2 rows in set (0.01 sec)
mysql> drop table contactInfo;
Query OK, 0 rows affected (0.00 sec)
mysql> drop table user;
Query OK, 0 rows affected (0.00 sec)
mysql> CREATE TABLE func (id MEDIUMINT NOT NULL AUTO_INCREMENT, user_name VARCHA
R(20) NOT NULL, password VARCHAR(20) NOT NULL, PRIMARY KEY (id));
Query OK, 0 rows affected (0.00 sec)
mysql> CREATE TABLE ContactInfo (email VARCHAR(20) NOT NULL, phone VARCHAR(20),
owner MEDIUMINT NOT NULL REFERENCES func(id), PRIMARY KEY (email) );
Query OK, 0 rows affected (0.01 sec)
Suggested fix:
1. Change the "3.5.6 Using Foreign Keys" example in the manual to include the db name qualifier. That should work in every case and it costs less effort than writing this bug.
This is how I'd like to see it:
..."
Note: you dont always have to use <my_db_name>. prefix like below, but you may run into a bug if you don't!!
CREATE TABLE shirt (
id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
style ENUM('t-shirt', 'polo', 'dress') NOT NULL,
color ENUM('red', 'blue', 'orange', 'white', 'black') NOT NULL,
owner SMALLINT UNSIGNED NOT NULL REFERENCES <my_db_name>.person(id),
PRIMARY KEY (id)
);
"...
If there is a better way to run the example, like quoting or escaping something that I don't know, put it in there!
2. It *is* a bug, right? Don't just fix the docs.. Exterminate!! Thanks.
Description: I rated it: priority medium - because it should be easy to fix. very repeatable. OS Win2K - but likely happens on other OSi. I saw the problem in the command-line mysql.exe client, also running in my IDE with JDBC. I am a newbie. I got the binaries off your site: mysqladmin Ver 8.40 Distrib 4.0.13, for Win95/Win98 on i32 Dell Lattitude, Win2K, logged in as admin. Netbeans version 3.4, java.vm.version 1.4.0_03-b04, ConnectorJ version 3.0.8 stable I followed your example "3.5.6 Using Foreign Keys" in the manual, changed it slightly and it broke, causing me hours of consternation. It gave a poor/incorrect error message. Because I used the table name "user", I had to qualify it with the database name after the REFERENCES word. I thought the problem was that it was same name as a table in the mysql database, but when I dropped the tables and then tried "func", it worked fine. That seems to say it is not a syntax error, which invalidates the error message. Is user reserved somewhere? It's not in the list, and besides I could create a table named "user" beforehand with no prob. I believe it would be good to fix because some people must run into it and get soured on MySQL, and download postgres. If even a newbie runs across bugs, that aint good. This example is like a magnet that I bet 30% of people would run. How to repeat: On an almost brand new install, (I had created monty): C:\mysql\bin>mysql -u root -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 51 to server version: 4.0.13-nt Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> use test; Database changed mysql> show tables; Empty set (0.01 sec) mysql> CREATE TABLE User (id MEDIUMINT NOT NULL AUTO_INCREMENT, user_name VARCHA R(20) NOT NULL, password VARCHAR(20) NOT NULL, PRIMARY KEY (id)); Query OK, 0 rows affected (0.00 sec) mysql> mysql> CREATE TABLE ContactInfo (email VARCHAR(20) NOT NULL, phone VARCHAR(20), owner MEDIUMINT NOT NULL REFERENCES User(id), PRIMARY KEY (email) ); ERROR 1064: You have an error in your SQL syntax. Check the manual that corresp onds to your MySQL server version for the right syntax to use near 'User(id), PR IMARY KEY (email) )' at line 1 mysql> mysql> mysql> CREATE TABLE ContactInfo (email VARCHAR(20) NOT NULL, phone VARCHAR(20), owner MEDIUMINT NOT NULL REFERENCES test.User(id), PRIMARY KEY (email) ); Query OK, 0 rows affected (0.01 sec) mysql> show tables; +----------------+ | Tables_in_test | +----------------+ | contactinfo | | user | +----------------+ 2 rows in set (0.01 sec) mysql> drop table contactInfo; Query OK, 0 rows affected (0.00 sec) mysql> drop table user; Query OK, 0 rows affected (0.00 sec) mysql> CREATE TABLE func (id MEDIUMINT NOT NULL AUTO_INCREMENT, user_name VARCHA R(20) NOT NULL, password VARCHAR(20) NOT NULL, PRIMARY KEY (id)); Query OK, 0 rows affected (0.00 sec) mysql> CREATE TABLE ContactInfo (email VARCHAR(20) NOT NULL, phone VARCHAR(20), owner MEDIUMINT NOT NULL REFERENCES func(id), PRIMARY KEY (email) ); Query OK, 0 rows affected (0.01 sec) Suggested fix: 1. Change the "3.5.6 Using Foreign Keys" example in the manual to include the db name qualifier. That should work in every case and it costs less effort than writing this bug. This is how I'd like to see it: ..." Note: you dont always have to use <my_db_name>. prefix like below, but you may run into a bug if you don't!! CREATE TABLE shirt ( id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT, style ENUM('t-shirt', 'polo', 'dress') NOT NULL, color ENUM('red', 'blue', 'orange', 'white', 'black') NOT NULL, owner SMALLINT UNSIGNED NOT NULL REFERENCES <my_db_name>.person(id), PRIMARY KEY (id) ); "... If there is a better way to run the example, like quoting or escaping something that I don't know, put it in there! 2. It *is* a bug, right? Don't just fix the docs.. Exterminate!! Thanks.