Bug 10234 - MySQL error when adding entry to z39.50servers
Summary: MySQL error when adding entry to z39.50servers
Status: CLOSED FIXED
Alias: None
Product: Koha
Classification: Unclassified
Component: Database (show other bugs)
Version: Main
Hardware: All All
: P5 - low normal (vote)
Assignee: Galen Charlton
QA Contact:
URL:
Keywords:
Depends on:
Blocks:
 
Reported: 2013-05-13 00:59 UTC by David Cook
Modified: 2015-12-03 22:00 UTC (History)
1 user (show)

See Also:
Change sponsored?: ---
Patch complexity: ---
Documentation contact:
Documentation submission:
Text to go in the release notes:
Version(s) released in:


Attachments

Note You need to log in before you can comment on or make changes to this bug.
Description David Cook 2013-05-13 00:59:55 UTC
Recently, someone reported getting an error when trying to add an entry into z39.50servers (http://irc.koha-community.org/koha/2013-05-09#i_1250075).

Here is a snippet of their log:

log:  "z3950servers.pl: DBD::mysql::st execute failed: Field 'description' doesn't have a default value at /usr/share/koha/intranet/cgā€‹i-bin/admin/z3950servers.pl line 133"

I believe the problem is the result of using a strict SQL mode:

Strict mode controls how MySQL handles input values that are invalid or missing. A value can be invalid for several reasons. For example, it might have the wrong data type for the column, or it might be out of range. A value is missing when a new row to be inserted does not contain a value for a non-NULL column that has no explicit DEFAULT clause in its definition. (For a NULL column, NULL is inserted if the value is missing.) (http://dev.mysql.com/doc/refman/5.5/en/server-sql-mode.html [You can check different versions, they all seem to have the same behaviour from what I can tell.])

*
In this case, the field 'description' has no default value but is also NOT NULL field. If your SQL mode isn't strict, you'll get a warning but the insert will succeed (using a blank value rather than a null value). However, it if it strict, you'll get an error and a rolled back/failed insert. 
*

If we observe other fields in kohastructure.sql that are NOT NULL and don't have a "real" default value, we typically use a default of ''. This will stop the warnings and the errors. 

Admittedly, the field "description" is documented as not being used, so perhaps the best thing to do is remove the field or perhaps make it NULL, but...using a default of '', will be the least potentially hazardous as it won't really change anything. It'll just enforce what we know as a default behaviour of using '' when the default value is missing.
Comment 1 Jonathan Druart 2015-04-14 14:19:48 UTC
It should have been solved by bug 6536.
Marked as fixed, please open if I am wrong.