what is this error?

[Pedram_IR]

mysql error:


Error

SQL-query:

CREATE TABLE ttrss_feed_categories(
id integer NOT NULL PRIMARY KEY AUTO_INCREMENT ,
owner_uid integer NOT NULL ,
title varchar( 200 ) NOT NULL ,
collapsed bool NOT NULL defaultfalse,
INDEX ( owner_uid ) ,
FOREIGN KEY ( owner_uid ) REFERENCES ttrss_users( id ) ON DELETE CASCADE
) TYPE = InnoDB


MySQL said: Documentation
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'false,
index(owner_uid),
foreign key (owner_uid) references




i use EasyPHP 1.8:

PHP Version : 4.3.10
Apache Version: Apache/1.3.33 (Win32) PHP/4.3.10
MYSQL Version: 4.1.9-max
phpMyAdmin 2.6.1

[chuck]

Coincidence, I'm having the same exact error trying to install. On the same day!

This is when trying to install the schema thusly:
mysql (command-line options for database name, host, user, & password) < ttrss_schema_mysql.sql > output

The exact error message I get (pretty much the same as above) is:

ERROR 1064 (00000) at line 42: You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'false,
index(owner_uid),
foreign key (owner_uid) references t

I also got this error by trying to copy-paste the contents of ttrss_schema_mysql.sql into phpMyAdmin and run it that way instead.

Could it have to do with our version of MySQL? `mysql --version` reports:
mysql Ver 14.12 Distrib 5.0.16, for pc-linux-gnu (i686) using readline 5.0

[chuck]

I got rid of this error by putting single-quotes aroung the word false on lines 45, 63, and 64. (so they read default 'false' rather than default false ) Several more tables get successfully created, but then I get a different error:

ERROR 1005 (00000) at line 263: Can't create table './db171285112/ttrss_scheduled_updates.frm' (errno: 150)

It seems to me that someone might want to check over the SQL in the schema file. The error we came in here with seems to be a pretty basic syntax error, if merely putting quotes around false fixes it -- but I don't have any idea about this one, and the error message is typically unhelpful (typically for MySQL that is)

BTW, I got the idea of putting the single-quotes around false by using phpMyAdmin to create a test table with a column of the same type and options, and looking at the SQL it generated and displayed for the task.

[fox]

Guys, your MySQL is probably too old or somehow broken. I was under impression that any 4.1 version should be compatible, but maybe I was wrong.

My test instance is 4.1.15 (from Debian etch).

[fox]

Just to mention that schema actually works:

Code: Select all

fox@naboo[tt-rss]% mysql -u fox -p fox < schema/ttrss_schema_mysql.sql
Enter password:
fox@naboo[tt-rss]%


[chuck]

fox -- my MySQL version doesn'ty appear to be OLD... read my first message again. Maybe it's too NEW.

A little googlin' finds me that "errno 150" refers to a foreign key problem. the table being created when this new error hits is:

create table ttrss_scheduled_updates (id integer not null primary key auto_increment,
owner_uid integer not null,
feed_id integer default null,
entered datetime not null,
foreign key (owner_uid) references ttrss_users(id) ON DELETE CASCADE,
foreign key (feed_id) references ttrss_feeds(id) ON DELETE CASCADE) TYPE=InnoDB;

I found out that this kind of problem can come from an inconsistent type in a foreign key. I'm no MySQL expert but I wonder if the problem may be with the definition of the feed_id column. It says here "feed_id integer default null," and that it is a foreign key which references ttrss_feeds(id), but at line 49 where the ttrss_feeds table is created, it gives the id column as "id integer not null..." So maybe it's an inconsistency that it's null by default in ttrss_scheduled_updates but not allowed to be null in ttrss_feeds? Maybe 4.x versions of MySQL allowed this but 5.0 doesn't? In which case, maybe the quote-mark thing is enough to fix Pedram_IR's issue.

Just a thought...

[chuck]

another possibility, found in the comments for the online MySQL 5.0 documentation:

Here's another gotcha to avoid the dread 150 error. If you declare an int field without a length (it defaults to 11) it cannot be used as a foreign key constraint. You should explicitly declare them as int(11), which is a sound policy anyway, but sometimes when you're testing things you just want it to work. I had this gotcha in 4.1.14, so I don't know if it applies to 5 or not, but if you get a 150, it's worth checking to see if that's an issue.

I'm not sure whether he means declaring with a length in the referenced table or the referencing one, but I notice neither is declared with a length.

[chuck]

Guys, your MySQL is probably too old or somehow broken. I was under impression that any 4.1 version should be compatible, but maybe I was wrong.

My test instance is 4.1.15 (from Debian etch).

i use EasyPHP 1.8:

PHP Version : 4.3.10

Could it have to do with our version of MySQL? `mysql --version` reports:
mysql Ver 14.12 Distrib 5.0.16, for pc-linux-gnu (i686) using readline 5.0

Yeeeeaaaah.... too OLD.....

[fox]

Ah. 5.0 is unsupported, sorry. It's a bit of a mystery - I have gotten some emails that schema imports and works just fine and, on the other hand, for other people it doesn't work at all.

Here's another gotcha to avoid the dread 150 error. If you declare an int field without a length (it defaults to 11) it cannot be used as a foreign key constraint.

You see, this sort of thing is exactly why MySQL support will be in this state until MySQL developers pull their heads from their asses. I don't want to create a hackery of my software just becase those idiots can't put any effort to make their brain dead SQL parser at least somewhat consistent between releases.

[chuck]

I saw the same problem, but I overcame it by using

SET FOREIGN_KEY_CHECKS = 0;

before using DROP TABLE. Afterwards I used:

SET FOREIGN_KEY_CHECKS = 1;

thinking about trying this around the create table ttrss_scheduled_updates, and see how far that gets me. Who knows...

Anyway I still think the cause may be more likelt the null/not-null thing than the int-length thing... the poster of that comment said it was a problem he had with 4.1.14 and didn't know if it applied to 5.0.

[chuck]

You see, this sort of thing is exactly why MySQL support will be in this state until MySQL developers pull their heads from their asses. I don't want to create a hackery of my software just becase those idiots can't put any effort to make their brain dead SQL parser at least somewhat consistent between releases.

That's fine for you I suppose, but those of us who can't very well ask our hosting providers to downgrade their database are just going to have to either not use your software, or create hackeries of it ourselves then.

BTW the SET FOREIGN_KEY_CHECKS trick worked in getting the schema to set u pall the way. Whether it will cause a problem with running the software remains to be seen, but I plan to press on... I'll let you know how it goes.

[fox]

That's fine for you I suppose, but those of us who can't very well ask our hosting providers to downgrade their database are just going to have to either not use your software, or create hackeries of it ourselves then.

Well, it's a free world. Anyway, it's not that I won't accept patches or start erasing wiki and forum entries with workarounds, you know. I just don't have time to dig MySQL intricacies and, frankly, it's not that interesting.

BTW the SET FOREIGN_KEY_CHECKS trick worked in getting the schema to set u pall the way. Whether it will cause a problem with running the software remains to be seen, but I plan to press on... I'll let you know how it goes.

Good. If you manage it to work, it would be great to document all patches/workarounds somewhere in one place. I'll post in on the site and maybe even update trunk to be MySQL 5.0 compatible.