MySQL Bugs: #12930: A Specific MySQL Error Message That Sucks (tm)

Bug #12930	A Specific MySQL Error Message That Sucks (tm)
Submitted:	1 Sep 2005 14:20	Modified:	1 Sep 2005 14:34
Reporter:	Martin Olsson	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	mysql Ver 14.7 Distrib 4.1.12, for Win3	OS:	Windows (Windows 2000)
Assigned to:		CPU Architecture:	Any

Description:
First off, thanks for providing this GREAT PRODUCT; MySQL. I really enjoy using it, I love how simple it is to use and how well it integrated with PHP! Second off, thanks for making it open-source so I can use it for hobby projects etc, I sure will recommend it for professional/commercial projects later if I should ever take part in one.

I was refactoring my PHP code today and as part of that I also went through my SQL and made sure fields where consistantly named etc. Therefore I ran into a problem where I had a FOREIGN KEY/REFERENCES that pointed to a non-existant field. This was of course a problem in my SQL syntax but this bug report is about the poor error message that was generated by MySQL.

How to repeat:

1. Make sure sql.txt is located in the current dir.

2. Make sure this version of MySQL is installed:
D:\MD>mysql --version
mysql  Ver 14.7 Distrib 4.1.12, for Win32 (ia32)

3. Execute the following command (substitute BLAH with correct root password):
mysql -u root --password=BLAH < sql.txt

4. MySQL now says:
ERROR 1005 (HY000) at line 10: Can't create table '.\databasen\qmtbl_questions.frm' (errno: 150)

My complaints about this message is:
1. It clearly says (errno: 150) however the errno I saw in PHP was 1005 which is also show here. Which one is it? Confusing!

2. You have a list of error messages here:
http://dev.mysql.com/doc/mysql/en/error-handling.html
I looked up errno 1005 in this list, but why even bother provide a list when you're not include any information what so ever as to what could potentially (and most commonly) cause this problem. Silly.

3. The errror message should mention the problem, ie the fact that the CREATE line for qmtbl_questions is referencing the "image_index" field inside qmtbl_images which of course does not exist (I just renamed it to image_id instead).

The exact contents of sql.txt is showed below:
CREATE DATABASE IF NOT EXISTS databasen;
USE databasen;
CREATE TABLE IF NOT EXISTS qmtbl_users(user_id INT NOT NULL AUTO_INCREMENT, username TEXT, password TEXT, registration_date DATETIME, last_seen DATETIME, image_mime TEXT, image_data MEDIUMBLOB, realname TEXT, email TEXT, PRIMARY KEY (user_id));
CREATE TABLE IF NOT EXISTS qmtbl_quizes(quiz_id INT NOT NULL AUTO_INCREMENT, owner_user_id INT, public_quiz BOOL, title TEXT, creation_date DATETIME, last_finished DATETIME, times_finished INT, PRIMARY KEY (quiz_id), FOREIGN KEY (owner_user_id) REFERENCES qmtbl_users (user_id));
CREATE TABLE IF NOT EXISTS qmtbl_images(image_id INT NOT NULL AUTO_INCREMENT, image_md5 TEXT, image_mime TEXT, image_data MEDIUMBLOB, PRIMARY KEY (image_id));
CREATE TABLE IF NOT EXISTS qmtbl_questions(question_id INT NOT NULL AUTO_INCREMENT, quiz_id INT, question TEXT, comment TEXT, image_index INT, question_type ENUM('qt_written_answer', 'qt_multiple_choice'), PRIMARY KEY (question_id), FOREIGN KEY (quiz_id) REFERENCES qmtbl_quizes (quiz_id), FOREIGN KEY (image_index) REFERENCES qmtbl_images (image_index));

Suggested fix:
1. This error message should have been something like:
Error in query BLAH, foreign key reference to non-existant field image_index in table qmtbl_images.

2. Don't print stuff like (errno: 150) when it's really (errno: 1005)

3. Update your error message webpage and add POTENTIAL CAUSES.

the SQL with the bad FOREIGN KEY yada REFERENCES yada

Attachment: sql.txt (text/plain), 1.09 KiB.

Please use the perror tool for have the description of error
messages:

C:\mysql4110a\bin>perror 150
MySQL error:  150 = Foreign key constraint is incorrectly formed

Great with a quick reply! Thanks Miguel.

While the perror utility works great on my machine too (I just didn't know about it until now), this tool is no solution -- not even a short term one -- to the problems I described. I'm not saying you said that I just wanted to make my own point of view clear. Several questions remain:

Why is there two, apparently valid, error codes involved here?

And why can't MySQL just print a sane error message from the start? 

It just seems very odd to first print an incomprehesible error message and then forcing the user to launch a separate tool to just to obtain an understandable error message. Maybe you could get away with that in the 50ties while programming some obscure mainframe which required a phd to use, but hey it's 2005 and most people are not tight schedules.

IMHO improving the quality of error messages, this one in particular but also MySQL error messages in general, would be a great way to advance the MySQL product line.

Maybe you could hire some intern to work-though the error messages or something?

Meanwhile, foreign key error weas changed to be more comprehensible