MySQL Bugs: #14238: fill_help_tables.sql Error 1264

Bug #14238	fill_help_tables.sql Error 1264
Submitted:	23 Oct 2005 13:57	Modified:	21 Nov 2005 19:20
Reporter:	Michael Rilling	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	5.0.13-rc-nt-max, 5.0.13-rc	OS:	Windows (Windows, Linux)
Assigned to:	Paul DuBois	CPU Architecture:	Any

Description:
The Table mysql.help_category has the field parent_category_id set with unsigned as shown in the mysql_create_system_tables script.  When populating the Help tables with the fill_help_tables.sql script, you recieve an error on line 23.  This is due to the "Contents" insert trying to set parent_category_id to "-1".

How to repeat:
create/recreate the help_category table as in mysql_create_system_tables.  Run the fill_help_tables.sql script.

Suggested fix:
Remove "unsigned" from the parent_category_id field.

Thank you for a problem report. Indeed, column is defined as unsigned:

mysql> select version();
+--------------+
| version()    |
+--------------+
| 5.0.13-rc-nt |
+--------------+
1 row in set (0.03 sec)

mysql> use mysql
Database changed
mysql> desc help_category;
+--------------------+----------------------+------+-----+---------+-------+
| Field              | Type                 | Null | Key | Default | Extra |
+--------------------+----------------------+------+-----+---------+-------+
| help_category_id   | smallint(5) unsigned | NO   | PRI |         |       |
| name               | char(64)             | NO   | UNI |         |       |
| parent_category_id | smallint(5) unsigned | YES  |     | NULL    |       |
| url                | char(128)            | NO   |     |         |       |
+--------------------+----------------------+------+-----+---------+-------+
4 rows in set (0.32 sec)

But line 23 in the fill_help_tables.sql (both on Windows and on Linux) is the following:

insert into help_category (help_category_id,name,parent_category_id) values (15,"Contents",-1),(16,"Point properties",24),(17,"Encryption Functions",22),(18,"LineString properties",24),(19,"Geometry properties",24),(20,"Logical operators",22),(21,"Miscellaneous Functions",22),(22,"Functions",15),(23,"String Functions", 22),(24,"Geographic features",15),(25,"Information Functions",22),(26,"Comparison operators",22),(27,"Bit unctions",22),(28,"Data Definition",15);

So, either table definition or the script should be changed.

Interesting, in 4.1.14-nt the script works by mysql silently changing the "-1" to a "0".  In 5.0.13-rc-nt-max when running the script you get the error 1264 stating "Out of range value adjusted ...", but that sql segment on line 23 still fails to insert.

fill_help_tables_sql for future releases of MySQL will
not contain negative category values.