Skip navigation

Tag Archives: mysql

*’Cause I have a feeling there’s more to come

I just spent an hour or so figuring out why a long SQL query is returning 0 rows. Long as in,

SELECT books.isbn, title, lastname, firstname, publishername
FROM books
INNER JOIN authored ON books.isbn = authored.isbn
INNER JOIN bookpersons ON authored.personid = bookpersons.personid
INNER JOIN published ON published.isbn = books.isbn
INNER JOIN publishers ON published.publisherid = publishers.publisherid
WHERE title = "Brave New World";

I was already looking into derived relations and subqueries until I realized that tables published and publishers are empty.

I’m calling it a day. So much for populating published and publishers and testing if my query works. Haaaayyyy… OTL

UPDATE: Having problems making your DDLs utilizing foreign keys work with InnoDB? This may answer some questions.

In retrospect, I made the same mistake when I asked that at SO as when I made this post. The errant statement when I made this post is

CREATE TABLE IF NOT EXISTS timelogs(
timestarted DATETIME NOT NULL,
timeended DATETIME NOT NULL,
taskid INTEGER NOT NULL,
PRIMARY KEY (timestarted),
FOREIGN KEY (taskid) REFERENCES tasks);

No column reference.


Note: This post is a summary of this Ubuntu Forums Programming Talk thread. With special thanks to nklatt for asking the questions which led me to my resolution.

I’ve been working on a JDBC-backed application. Since I am familiar with MySQL, the MySQL Connector/J comes as a natural choice for a database driver. Since I’m using Java, I expected my code to run on both Linux and Windows environments.

Testing it with Vista, it runs well. However, when I tested it in Ubuntu, I was greeted with the following error:

java.sql.SQLException: Can't create table 'dbname.timelogs' (errno: 150)

Googling around, I found this which says that this error happens since MyISAM, the default storage mechanism used by MySQL, doesn’t support foreign key constraints. And yes, I am using foreign keys in my tables. Also, a quote from the official MySQL docs says,

Foreign key constraints are supported for the InnoDB storage engine only. For other storage engines, the foreign key syntax is correctly parsed but not implemented.1

I checked the tables created at Vista and found out that they use the MyISAM storage engine as I expected since I didn’t specify any storage engine in my CREATE statement and so should fall back to the default. This made me wonder all the more why didn’t it throw the error in Vista when MyISAM isn’t supposed to support foreign key constraints.

At this point, I must note that Vista runs XAMPP 1.7.3 while Ubuntu runs version 1.7.4 . I didn’t think that my XAMPP version will matter—after all, Vista is only a version behind. Spoiler: I thought wrong.

I changed my CREATE statements such that they explicitly specify the InnoDB storage engine. I ran my modified code in Vista and, surprisingly, Vista threw the very error Ubuntu does.

Confused, I switched back to Ubuntu and, for the first time in this bug-hunt, checked the table created2 at phpMyAdmin (I have two tables in my database, only one of which specifies a foreign key. The one without the foreign key gets created, of course). I discovered that MySQL 5.5.8 (the MySQL server that comes with XAMPP 1.7.4) actually defaults to InnoDB and not to MyISAM3.

So it turns out that, weirdly, the InnoDB storage engine was the one causing all my problems despite the documentation declaring that it supports (in fact, it is the only storage engine that supports) foreign key constraints. Resolution: stick with MyISAM since that is where my code runs, despite the fact that it doesn’t really support foreign key constraints.

(In any case, the user will interact with the DB through a GUI. So, it’d be more or less the GUI programmer’s fault, i.e. me, if some record doesn’t make sense. Sigh. So much for being a one-man team.)

  1. http://dev.mysql.com/doc/workbench/en/wb-foreign-key-relationships.html , retrieved 6/22/11, 11:09PM GMT+8. []
  2. This was still the one created by the code not specifying any storage engine causing MySQL to default. []
  3. If you read the thread I linked above, nklatt pointed this out beforehand for MySQL 5.5 . []