May 2016 - Koha-bugs - lists.koha-community.org

[Bug 13669] Web installer fails to load sample data on MySQL 5.6+
by bugzilla-daemon＠bugs.koha-community.org 23 May '16

23 May '16

https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=13669 --- Comment #49 from Bernardo Gonzalez Kriegel <bgkriegel(a)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… 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-my… -- You are receiving this mail because: You are watching all bug changes.

1 0

[Bug 13669] Web installer fails to load sample data on MySQL 5.6+
by bugzilla-daemon＠bugs.koha-community.org 23 May '16

23 May '16

https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=13669 --- Comment #48 from Bernardo Gonzalez Kriegel <bgkriegel(a)gmail.com> --- (In reply to Katrin Fischer from comment #47) > I have installed the dependency from CPAN, made sure that the database was > empty, applied the patches and ran the web installer in English. But I still > get errors, any idea what's going wrong there? > ... I can confirm similar problems on Ubuntu 16.05, Myslq 5.7 My test where not done on this kind of system, sorry :( In case the problem where from DBIX::RunSQL (which gives problems), I wrote a revision using Tomas' approach (SQL::SplitStatment), and found the same problems [Sun May 22 18:49:18 2016] install.pl: DBD::mysql::db do failed: BLOB, TEXT, GEOMETRY or JSON column 'defaultvalue' can't have a default value at /home/bgkriegel/kohaclone/C4/Installer.pm line 443. [Sun May 22 18:49:51 2016] install.pl: DBD::mysql::db do failed: Invalid default value for 'created_on' at /home/bgkriegel/kohaclone/C4/Installer.pm line 443. [Sun May 22 18:49:52 2016] install.pl: DBD::mysql::db do failed: Cannot add foreign key constraint at /home/bgkriegel/kohaclone/C4/Installer.pm line 443. [Sun May 22 18:49:52 2016] install.pl: DBD::mysql::db do failed: Cannot add foreign key constraint at /home/bgkriegel/kohaclone/C4/Installer.pm line 443 -- You are receiving this mail because: You are watching all bug changes.

1 0

[Bug 13669] Web installer fails to load sample data on MySQL 5.6+
by bugzilla-daemon＠bugs.koha-community.org 22 May '16

22 May '16

https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=13669 --- Comment #47 from Katrin Fischer <katrin.fischer(a)bsz-bw.de> --- I have installed the dependency from CPAN, made sure that the database was empty, applied the patches and ran the web installer in English. But I still get errors, any idea what's going wrong there? Help? [Sun May 22 19:38:37 2016] install.pl: Use of uninitialized value in numeric lt (<) at /usr/local/share/perl/5.22.1/DBIx/RunSQL.pm line 261. [Sun May 22 19:38:37 2016] install.pl: Use of uninitialized value in numeric lt (<) at /usr/local/share/perl/5.22.1/DBIx/RunSQL.pm line 261. [Sun May 22 19:38:37 2016] install.pl: Use of uninitialized value in numeric lt (<) at /usr/local/share/perl/5.22.1/DBIx/RunSQL.pm line 261. [Sun May 22 19:38:37 2016] install.pl: Use of uninitialized value in numeric lt (<) at /usr/local/share/perl/5.22.1/DBIx/RunSQL.pm line 261. [Sun May 22 19:38:37 2016] install.pl: Use of uninitialized value in numeric lt (<) at /usr/local/share/perl/5.22.1/DBIx/RunSQL.pm line 261. [Sun May 22 19:38:37 2016] install.pl: DBD::mysql::st execute failed: BLOB, TEXT, GEOMETRY or JSON column 'defaultvalue' can't have a default value at /usr/local/share/perl/5.22.1/DBIx/RunSQL.pm line 254. Please contact your system administrator -- You are receiving this mail because: You are watching all bug changes.

1 0

[Bug 14978] issues.itemnumber should be a unique key
by bugzilla-daemon＠bugs.koha-community.org 22 May '16

22 May '16

https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=14978 Jonathan Druart <jonathan.druart(a)bugs.koha-community.org> changed: What |Removed |Added ---------------------------------------------------------------------------- Blocks| |16534 Referenced Bugs: https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=16534 [Bug 16534] Error when checking out already checked out item -- You are receiving this mail because: You are watching all bug changes.

1 0

[Bug 13790] Add unique id issue_id to issues and old_issues tables
by bugzilla-daemon＠bugs.koha-community.org 22 May '16

22 May '16

https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=13790 Jonathan Druart <jonathan.druart(a)bugs.koha-community.org> changed: What |Removed |Added ---------------------------------------------------------------------------- Blocks| |16534 Referenced Bugs: https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=16534 [Bug 16534] Error when checking out already checked out item -- You are receiving this mail because: You are watching all bug changes.

1 0

[Bug 13669] Web installer fails to load sample data on MySQL 5.6+
by bugzilla-daemon＠bugs.koha-community.org 21 May '16

21 May '16

https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=13669 --- Comment #46 from Jonathan Druart <jonathan.druart(a)bugs.koha-community.org> --- Created attachment 51685 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=51685&action=ed… Bug 13669: Catch the errors to have them to the logs This patch redirect STDERR to a variable to retrieve the errors raised by the DBMS when loading a sql file, it could be useful to debug errors. Signed-off-by: Bernardo Gonzalez Kriegel <bgkriegel(a)gmail.com> It's better of course, trying to load a failed fiel it outputs mysl errors DBD::mysql::st execute failed: You have an error in your SQL syntax... No errors Signed-off-by: Jonathan Druart <jonathan.druart(a)bugs.koha-community.org> -- You are receiving this mail because: You are watching all bug changes.

1 0

[Bug 13669] Web installer fails to load sample data on MySQL 5.6+
by bugzilla-daemon＠bugs.koha-community.org 21 May '16

21 May '16

https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=13669 --- Comment #45 from Jonathan Druart <jonathan.druart(a)bugs.koha-community.org> --- Created attachment 51684 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=51684&action=ed… Bug 13669: Re-adds error handling to load_sql This patch prevents crashing in case an error is detected when loading a file To test: 1) Apply patch 2) Mangle kohastructure.sql or any sample file adding and invalid SQL line 3) Run webinstaller and check that the error is handled gracefully Signed-off-by: Jonathan Druart <jonathan.druart(a)bugs.koha-community.org> -- You are receiving this mail because: You are watching all bug changes.

1 0

[Bug 13669] Web installer fails to load sample data on MySQL 5.6+
by bugzilla-daemon＠bugs.koha-community.org 21 May '16

21 May '16

https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=13669 --- Comment #44 from Jonathan Druart <jonathan.druart(a)bugs.koha-community.org> --- Created attachment 51683 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=51683&action=ed… Bug 13669: (alternative) use DBIx::RunSQL Alternative POC solution, on top of previous patches, feel free to obsolte. This patch use DBIx::RunSQL->run_sql_file to procees each sql file. To test: 1) Apply all patches 2) Same test plan of patch 1 Timing test running web installer, marc21, all sample data, time in seconds a) without patch structure data 59.7 66.5 58.6 66.0 b) SQL::SplitStatement (patch 1+2) 59.4 101 59.7 102 c) DBIx::RunSQL (patch 3) 60.7 66.8 59.4 66.2 Tomas' version is a bit slower loading sample data, all give similar results processing kohastructure. New dependency to package: DBIx::RunSQL Signed-off-by: Chris Cormack <chrisc(a)catalyst.net.nz> Signed-off-by: Jonathan Druart <jonathan.druart(a)bugs.koha-community.org> -- You are receiving this mail because: You are watching all bug changes.

1 0

[Bug 13669] Web installer fails to load sample data on MySQL 5.6+
by bugzilla-daemon＠bugs.koha-community.org 21 May '16

21 May '16

https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=13669 --- Comment #43 from Jonathan Druart <jonathan.druart(a)bugs.koha-community.org> --- Created attachment 51682 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=51682&action=ed… Bug 13669: (followup) Fix kohastructure.sql so FK dependencies are sorted The current table creation order is left to mysql's strategy, which is not suitable for parsing the SQL files and passing one statement at a time in the current order. This patch just moves table creation statements around so FK constraints are defined for previously created tables. Signed-off-by: Chris Cormack <chrisc(a)catalyst.net.nz> Signed-off-by: Jonathan Druart <jonathan.druart(a)bugs.koha-community.org> -- You are receiving this mail because: You are watching all bug changes.

1 0

[Bug 13669] Web installer fails to load sample data on MySQL 5.6+
by bugzilla-daemon＠bugs.koha-community.org 21 May '16

21 May '16

https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=13669 Jonathan Druart <jonathan.druart(a)bugs.koha-community.org> changed: What |Removed |Added ---------------------------------------------------------------------------- Attachment #51600|0 |1 is obsolete| | Attachment #51601|0 |1 is obsolete| | Attachment #51608|0 |1 is obsolete| | Attachment #51671|0 |1 is obsolete| | Attachment #51680|0 |1 is obsolete| | --- Comment #42 from Jonathan Druart <jonathan.druart(a)bugs.koha-community.org> --- Created attachment 51681 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=51681&action=ed… Bug 13669: Use perl to load SQL statements from sample data This patch changes C4::Installer::load_sql so it uses File::Slurp [1] to read the SQL files, and SQL::SplitStatement to extract the statements from the full SQL file so they can be passed to $dbh->do. To test: - On Mysql 5.5, run the webinstaller => SUCCESS: Everything works as expected - Apply the patch - Run $ mysql -uroot > DROP DATABASE koha_kohadev ; CREATE DATABASE koha_kohadev; - Run the webinstaller => SUCCESS: Everything works as expected - On Mysql 5.6+ (5.7 is implied) - Run $ mysql -uroot > DROP DATABASE koha_kohadev ; CREATE DATABASE koha_kohadev; - Run the webinstaller => FAIL: It cannot load the sql files due to a password-in-command-line error - Apply the patch - Make sure everything is clean (it should): $ mysql -uroot > DROP DATABASE koha_kohadev ; CREATE DATABASE koha_kohadev; - Run the webinstaller => SUCCESS: EVerything works as expected - Sign off :-D [1] Note: This is a POC patch, in the sense that it does the job, fixes a nasty problem but using File::Slurp to load the SQL files in memory comes with a big runtime penalty. You will notice the install procedure is now much slower, for instance. Signed-off-by: Chris Cormack <chrisc(a)catalyst.net.nz> Signed-off-by: Jonathan Druart <jonathan.druart(a)bugs.koha-community.org> -- You are receiving this mail because: You are watching all bug changes.

1 0