[Koha-bugs] [Bug 13669] Web installer fails to load sample data on MySQL 5.6+

[bugzilla-daemon at bugs.koha-community.org]

https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=13669

--- Comment #49 from Bernardo Gonzalez Kriegel <bgkriegel at gmail.com> ---
Errors are not in the procedeure, I tested extensively but on msyql < 5.6, so
this is specific to ver 5.6+ treatment of SQL, or 5.7 more specifically

1) table: auth_subfield_structure

line:  72   `defaultvalue` TEXT DEFAULT '',
error: (BLOB, TEXT, GEOMETRY or JSON column 'defaultvalue' can't have a default
value): CREATE TABLE `auth_subfield_structure`

Seems that we can't have default values here.

Ref: http://dev.mysql.com/doc/refman/5.7/en/blob.html
->" BLOB and TEXT columns cannot have DEFAULT values."

2) table: virtualshelves
line:  2212   `created_on` TIMESTAMP NOT NULL, -- creation time
error: (Invalid default value for 'created_on'): CREATE TABLE `virtualshelves`

Seems that first TIMESTAMP column in a table not explicitly nullable
automatically gets DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

Could it be DATETIME instead of TIMESTAMP??

Reference
http://shankargopal.blogspot.com.ar/2013/03/mysql-566-timestamp-columns-and-default.html


3) and 4)
error: (Cannot add foreign key constraint): CREATE TABLE `virtualshelfcontents`
error: (Cannot add foreign key constraint): CREATE TABLE `virtualshelfshares`

This two because virtualshelves table not created

MORE
----
Fixing somehow previous kohastructure errors, we have more Errors in sample
data

auth_subfield_structure, (Incorrect integer value: '' for column 'linkid' at
row 1)
Here we have an error, '' instead of 0, MySQL < 5.6 tolerates this, 5.7 no

sample_patrons.sql, (Column 'address' cannot be null)

sample_quotes.sql, (Incorrect datetime value: '0000-00-00 00:00:00' for column
'timestamp' at row 1)

MySQL 5.7 does not like things like that.
Perhaps we can change the sql_mode for Koha globally, 
or fix all the sample data (not difficult) in all languages, 
and test/sign/QA everything... before release?

Ref:
https://www.digitalocean.com/community/tutorials/how-to-prepare-for-your-mysql-5-7-upgrade

-- 
You are receiving this mail because:
You are watching all bug changes.