View Full Version : Error: loading FoorMartTables.sql into mysql

06-29-2003, 12:47 AM
I'm trying to set to the mondrian demo using mysql 3.23.52 but unfortunately I don't seem to get very far:
mysql -D mondrian -u xxx -p < FoodMartTables.sql
Enter password:
ERROR 1064 at line 110: You have an error in your SQL syntax near 'position(
position_id INTEGER NOT NULL,
position_title VARCHAR(30) NOT NUL' at line 1
The statement in question seem to be:
CREATE TABLE position(
position_id INTEGER NOT NULL ...
Some experiementing seemed to show that mysql objects to the tablename "position" (which is interesting since position is not an officially reserved word for mysql). If I change the "CREATE TABLE position" to "CREATE TABLE positionx" the table can be created and the script runs without any problems.
1) Can someone confirm this?
2) Is there some sort of workaround available?
--> R

06-29-2003, 07:52 AM
Yes, "POSITION" is a reserved word.
My MySQL environment is hosed, so I can't try these suggestions but (a) try using MySQL 4, (b) try starting in --ansi mode. Please let me know if either of these works.

06-29-2003, 11:27 PM
Well, I've just tried starting mysql in --ansi mode and that made no real difference (ie: the error still occours).
As for your other suggestion to try MySql4.0, I'll have to look into how I can do that without messing up my MySql3 install. I'll get back to you on that.

07-07-2003, 04:44 AM
With MySQL 3.23.52 , I solved this problem by inserting a space after table name "position".
CREATE TABLE position ( position_id INTEGER NOT NULL, position_title VARCHAR(30) NOT NULL, pay_type VARCHAR(30) NOT NULL, min_scale DECIMAL(10,2) NOT NULL, max_scale DECIMAL(10,2) NOT NULL, management_role VARCHAR(30) NOT NULL);
As mentioned in other posts, you must also create the missing index :
CREATE UNIQUE INDEX i_time_by_day_id ON time_by_day ( time_id );

07-07-2003, 05:43 AM
OK, I can now confirm that the loading of MySql data also fails (with the same error) using MySql 4 ...
Yes, the fix itself is trivial, however, I would expect there to be dependencies on the POSITION table which would come back to haunt you later.
--> R

07-07-2003, 07:19 PM
Thanks for pointing this out. I've uploaded a new mondrian-data-mysql.zip, and I've obsoleted some files in perforce which were causing confusion.

02-05-2004, 11:07 AM
Hello, I've the same problem with mondrian 1.0.1 and the last mondrian-data.zip. Is really important this table?. I put the space after the name of the table: create table position (position_id ... and the creation is ok, but there are errors in the foodmartdata.sql inserts for this table. Thanks in advance.

02-05-2004, 11:39 AM
Ok, i've the answer... use foodmart.position in the create table and in the insert statements.