557686 - PostgreSQL crashes when deleting a custom field of type Date/Time

Status: RESOLVED FIXED

(Bugzilla :: Administration, task)

Description

[Frédéric Buclin]

DBD::Pg::db selectrow_array failed: ERREUR:  syntaxe en entrée invalide pour le type timestamp : «  » [for Statement "SELECT COUNT(*) FROM bugs WHERE cf_field5 IS NOT NULL AND cf_field5 != '' AND cf_field5 != '00-00-00 00:00:00'"] at Bugzilla/Field.pm line 738
	Bugzilla::Field::remove_from_db('Bugzilla::Field=HASH(0xad97978)') called at /var/www/html/bugzilla_qa36/editfields.cgi line 159
 at Bugzilla/Field.pm line 738
	Bugzilla::Field::remove_from_db('Bugzilla::Field=HASH(0xad97978)') called at /var/www/html/bugzilla_qa36/editfields.cgi line 159


Excluding the empty string as in bug 557495 doesn't help as it now throws:

DBD::Pg::db selectrow_array failed: ERREUR:  valeur du champ date/time en dehors des limites : « 00-00-00 00:00:00 »
HINT:  Peut-être avez-vous besoin d'un paramétrage « datestyle » différent. [for Statement "SELECT COUNT(*) FROM bugs WHERE cf_field5 IS NOT NULL AND cf_field5 != '00-00-00 00:00:00'"] at Bugzilla/Field.pm line 738
	Bugzilla::Field::remove_from_db('Bugzilla::Field=HASH(0x95dd948)') called at /var/www/html/bugzilla_qa36/editfields.cgi line 159
 at Bugzilla/Field.pm line 738
	Bugzilla::Field::remove_from_db('Bugzilla::Field=HASH(0x95dd948)') called at /var/www/html/bugzilla_qa36/editfields.cgi line 159

Here, it complains that '00-00-00 00:00:00' is not a valid date. Assuming this date can never be introduced, neither in MySQL nor in PostgreSQL, maybe should we simply skip it.

Comment 1

[Frédéric Buclin]

Attachment: patch, v1

'00-00-00 00:00:00' is not a valid date.

Comment 2

[Max Kanat-Alexander]

  That date can be introduced in MySQL by manual DB editors and other things. In fact, it probably could even be put into the database by a user in the interface.

  I don't think this should block 3.4.7 or 3.6--this only affects PostgreSQL 8.3, and there's probably even an option to disable the strictness.

Comment 3

[Frédéric Buclin]

(In reply to comment #2)
>   I don't think this should block 3.4.7 or 3.6--this only affects PostgreSQL
> 8.3, and there's probably even an option to disable the strictness.

I don't see how "this only affects Pg 8.3" doesn't make it a blocker. It's a crash, and you cannot delete the custom field at all. Also, the 3.4 branch will soon be locked to security fixes only.

I don't care that one can manually add '00-00-00 00:00:00' into the DB. This is not a valid date, and if someone managed to insert this date anyway, I'm fine with removing this field.

Comment 4

[Frédéric Buclin]

(In reply to comment #3)
> I don't care that one can manually add '00-00-00 00:00:00' into the DB. This is
> not a valid date, and if someone managed to insert this date anyway, I'm fine
> with removing this field.

Err... I should have said the opposite. If someone inserted this date, then the field will stay, which is not a big problem.

Comment 5

[Max Kanat-Alexander]

Comment on attachment 437464 [details] [diff] [review]
patch, v1

  Okay, that works for me. :-) I see you're the reviewer of the original patch, anyhow, so I'm OK with your decision on this.

Comment 6

[Max Kanat-Alexander]

  It's sort of possible that a MySQL install before version 5.0 would contain these dates, by the way, since I think that's where those incorrect defaults of 0000-00-00 come from in MySQL DBs (from MySQL 4.1).

Comment 7

[Frédéric Buclin]

OK, we are safe. When entering a date in a Date/Time custom field, the checker _check_datetime_field() in Bug.pm makes sure that the date is valid by calling str2time(), which returns undef (false) if it's not. So you cannot insert 0000-00-00 at all.

Comment 8

[Frédéric Buclin]

Committing to: bzr+ssh://[e-mail]/bugzilla/trunk/
modified Bugzilla/Field.pm
Committed revision 7116.

Committing to: bzr+ssh://[e-mail]/bugzilla/3.6/
modified Bugzilla/Field.pm
Committed revision 7074.

Committing to: bzr+ssh://[e-mail]/bugzilla/3.4/
modified Bugzilla/Field.pm
Committed revision 6758.