483275 - milestone url in products table has NULL default on MySQL

Status: RESOLVED WORKSFORME

(Bugzilla :: Database, defect)

Description

[Eric Olson]

User-Agent:       Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.9.0.7) Gecko/2009021910 Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1)
Build Identifier: 3.2.2

I notice that the milestoneurl field in the products table has NULL default when using mySQL. Under Oracle, it's an empty string, and the schema listed at ravenbrook.com also says it should be an empty string. 

mysql> desc products;
+-------------------+-------------+------+-----+---------+----------------+
| Field             | Type        | Null | Key | Default | Extra          |
+-------------------+-------------+------+-----+---------+----------------+
...
| milestoneurl      | tinytext    | NO   |     | NULL    |                |

Under Oracle, the DDL for the products table looks like:
CREATE TABLE "BUGZILLA"."PRODUCTS" 
   (	...
     "MILESTONEURL" VARCHAR2(255 CHAR) DEFAULT '__BZ_EMPTY_STR__' NOT NULL ENABLE, ...


Reproducible: Always

Comment 1

[Max Kanat-Alexander]

Bugzilla::Install::DB correctly has code to set milestoneurl to an empty-string default for all old DBs. Bugzilla::DB::Schema correctly has code that sets the default for new DBs. I checked my local MySQL Bugzilla installation, and the default is indeed an empty string.

Comment 2

[Frédéric Buclin]

My DB also has NULL instead of "" as default. Confirming.

Comment 3

[Eric Olson]

I just did this again to double-check:

mysql> drop database bugs;
mysql> create database bugs;
mysql> GRANT SELECT, INSERT, ...

[ran checksetup.pl]

mysql> use bugs;
mysql> desc products;
+-------------------+-------------+------+-----+---------+----------------+
| Field             | Type        | Null | Key | Default | Extra          |
+-------------------+-------------+------+-----+---------+----------------+
| id                | smallint(6) | NO   | PRI | NULL    | auto_increment |
| name              | varchar(64) | NO   | UNI | NULL    |                |
| classification_id | smallint(6) | NO   |     | 1       |                |
| description       | mediumtext  | YES  |     | NULL    |                |
| milestoneurl      | tinytext    | NO   |     | NULL    |                |
| disallownew       | tinyint(4)  | NO   |     | 0       |                |
| votesperuser      | smallint(6) | NO   |     | 0       |                |
| maxvotesperbug    | smallint(6) | NO   |     | 10000   |                |
| votestoconfirm    | smallint(6) | NO   |     | 0       |                |
| defaultmilestone  | varchar(20) | NO   |     | ---     |                |
+-------------------+-------------+------+-----+---------+----------------+
10 rows in set (0.00 sec)

This is under MySQL 5.0.67-community-nt and ActivePerl v5.8.9.

Comment 4

[Max Kanat-Alexander]

Hmm. Do you know the upgrade history of this database? That is, what versions you came from to get to 3.2.2?

Comment 5

[Eric Olson]

In comment #3, you can see that this happens if I drop the database entirely and create a new one from scratch.

I'm not sure of the history of the database where I first noticed this, but it couldn't have been anything other than 3.2 or 3.2.2.

Comment 6

[Frédéric Buclin]

Also confirmed on a fresh new installation.

Comment 7

[Frédéric Buclin]

(In reply to comment #3)

> +-------------------+-------------+------+-----+---------+----------------+
> | Field             | Type        | Null | Key | Default | Extra          |
> +-------------------+-------------+------+-----+---------+----------------+
> | milestoneurl      | tinytext    | NO   |     | NULL    |                |
> +-------------------+-------------+------+-----+---------+----------------+

Hum, wait. The Null column is NO, and despite the Default column mentions NULL, the following command inserts an empty string as expected, not NULL:

 INSERT INTO products (name) values ("foo");

You can check this by comparing:
 SELECT id FROM products WHERE milestoneurl IS NULL;
vs
 SELECT id FROM products WHERE milestoneurl 0 "";

Only the last command returns the "foo" product. So maybe NULL here means "" in MySQL 5.x (when combined with Null = NO).

Comment 8

[Eric Olson]

From: http://dev.mysql.com/doc/refman/5.1/en/data-type-defaults.html: 

"For data entry, if an INSERT or REPLACE statement includes no value for the column, or an UPDATE statement sets the column to NULL, MySQL handles the column according to the SQL mode in effect at the time:

If strict SQL mode is not enabled [I'm assuming this is the case here], MySQL sets the column to the implicit default value for the column data type...

For string types other than ENUM, the default value is the empty string."

It turns out MySQL does not allow text columns to have a defined default value.

Comment 9

[Eric Olson]

Well, now wait a minute. Trying to repeat what was done in comment 7, I get:

mysql> insert into products (name) values ("foo");
ERROR 1364 (HY000): Field 'milestoneurl' doesn't have a default value

I have to explicitly set the milestoneurl as an empty string:

mysql> insert into products (name, milestoneurl) values ("foo", "");
Query OK, 1 row affected (0.00 sec)