Bug 17306

Summary: Update scripts fail with Mysql 5.7
Product: Koha Reporter: Rodrigo Santellan <rsantellan>
Component: DatabaseAssignee: Galen Charlton <gmcharlt>
Status: NEW --- QA Contact: Testopia <testopia>
Severity: enhancement    
Priority: P5 - low CC: katrin.fischer, mtj, ztajoli
Version: 16.05   
Hardware: All   
OS: All   
Change sponsored?: --- Patch complexity: ---
Documentation contact: Documentation submission:
Text to go in the release notes:
Version(s) released in:
Bug Depends on:    
Bug Blocks: 17258    
Attachments: Changes of update22to30
Changes of updatadatabase
With change of columns for select insert.

Description Rodrigo Santellan 2016-09-16 12:59:55 UTC
Doing a complete migration from koha 2 to koha 3 with the update22to30.pl and updatedatabase.pl found a few problems with the consistencies of the SQL with the new MySQL 5.7. I have changed the script to be able to run, the only one that is not working properly is 3.09.00.051 that have a bad group by condition but I never have use that module and don't know how to fix it.
Comment 1 Zeno Tajoli 2016-09-16 13:11:20 UTC
Hi Rogrigo,

could write down the changes you have done on updatedatabase.pl ?
Also a .txt file is ok.
No problem with update22to30.pl ?

Do you still have an installation of Koha 2.x ?
Comment 2 Zeno Tajoli 2016-09-16 13:15:35 UTC
An other request: are you trying to use last LTS of Ubuntu?
You speak about Mysql 5.7
Comment 3 Rodrigo Santellan 2016-09-16 13:41:28 UTC
Created attachment 55618 [details]
Changes of update22to30

diff --git a/installer/data/mysql/update22to30.pl b/installer/data/mysql/update22to30.pl
index f10ace9..f2010e1 100755
--- a/installer/data/mysql/update22to30.pl
+++ b/installer/data/mysql/update22to30.pl
@@ -53,10 +53,10 @@ my $DBversion = "3.00.00.000";
         action_logs     => "(
                         `timestamp` TIMESTAMP NOT NULL ,
                         `user` INT( 11 ) NOT NULL default '0' ,
-                        `module` TEXT default '',
-                        `action` TEXT default '' ,
+                        `module` TEXT default NULL,
+                        `action` TEXT default NULL ,
                         `object` INT(11) NULL ,
-                        `info` TEXT default '' ,
+                        `info` TEXT default NULL ,
                         PRIMARY KEY ( `timestamp` , `user` )
                     )",
         letter        => "(
@@ -132,7 +132,7 @@ my $DBversion = "3.00.00.000";
                     labelid int(11) NOT NULL auto_increment,
                                 batch_id varchar(10) NOT NULL default '1',
                                 itemnumber varchar(100) NOT NULL default '',
-                                timestamp timestamp(14) NOT NULL,
+                                timestamp timestamp NOT NULL,
                                 PRIMARY KEY  (labelid)
                                 )",
     
@@ -214,7 +214,7 @@ my $DBversion = "3.00.00.000";
     );
     
     my %requirefields = (
-        subscription => { 'letter' => 'varchar(20) NULL', 'distributedto' => 'text NULL', 'firstacquidate'=>'date default NULL','irregularity'=>'TEXT NULL default \'\'','numberpattern'=>'TINYINT(3) NULL default 0', 'callnumber'=>'text NU
+        subscription => { 'letter' => 'varchar(20) NULL', 'distributedto' => 'text NULL', 'firstacquidate'=>'date default NULL','irregularity'=>'TEXT NULL default NULL','numberpattern'=>'TINYINT(3) NULL default 0', 'callnumber'=>'text NU
         itemtypes => { 'imageurl' => 'varchar(200) NULL'},
         aqbookfund => { 'branchcode' => 'varchar(4) NULL'},
         aqbudget => { 'branchcode' => 'varchar(4) NULL'},
Comment 4 Rodrigo Santellan 2016-09-16 13:42:30 UTC
Created attachment 55619 [details]
Changes of updatadatabase

@@ -76,7 +76,6 @@ local $dbh->{RaiseError} = 0;
 # Record the version we are coming from
 
 my $original_version = C4::Context->preference("Version");
-
 # Deal with virtualshelves
 my $DBversion = "3.00.00.001";
 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
@@ -557,9 +556,11 @@ if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
 
 $DBversion = "3.00.00.019";
 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
+    $dbh->do("SET FOREIGN_KEY_CHECKS=0");
     $dbh->do("ALTER TABLE biblio MODIFY biblionumber INT(11) NOT NULL AUTO_INCREMENT");
     $dbh->do("ALTER TABLE biblioitems MODIFY biblioitemnumber INT(11) NOT NULL AUTO_INCREMENT");
     $dbh->do("ALTER TABLE items MODIFY itemnumber INT(11) NOT NULL AUTO_INCREMENT");
+    $dbh->do("SET FOREIGN_KEY_CHECKS=1");
     print "Upgrade to $DBversion done (made bib/item PKs auto_increment)\n";
     SetVersion ($DBversion);
 }
@@ -1036,8 +1037,7 @@ if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
             `creep_vert` float default NULL,
             `unit` char(20) NOT NULL default 'POINT',
             PRIMARY KEY  (`prof_id`),
-            UNIQUE KEY `printername` (`printername`,`tmpl_id`,`paper_bin`),
-            CONSTRAINT `printers_profile_pnfk_1` FOREIGN KEY (`tmpl_id`) REFERENCES `labels_templates` (`tmpl_id`) ON DELETE CASCADE ON UPDATE CASCADE
+            UNIQUE KEY `printername` (`printername`,`tmpl_id`,`paper_bin`)
             ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ");
     $dbh->do("CREATE TABLE `labels_profile` (
             `tmpl_id` int(4) NOT NULL,
@@ -1149,7 +1149,7 @@ if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
 }
 
 $DBversion = "3.00.00.061";
-if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
+if (C4::Context->preference("Version") < TransformToNum($DBversion) || 1 == 2) {
     $dbh->do("ALTER TABLE labels_templates ADD COLUMN font char(10) NOT NULL DEFAULT 'TR';");
        print "Upgrade to $DBversion done ( Added font column to labels_templates )\n";
     SetVersion ($DBversion);
@@ -1205,7 +1205,7 @@ if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
                 ) ENGINE=InnoDB DEFAULT CHARSET=utf8");
 
     # move closed transactions to old_* tables
-    $dbh->do("INSERT INTO old_issues SELECT * FROM issues WHERE returndate IS NOT NULL");
+    $dbh->do("INSERT INTO old_issues SELECT i.borrowernumber, i.itemnumber, i.date_due, i.branchcode, i.issuingbranch, i.returndate, i.lastreneweddate, i.return, i.renewals, i.timestamp, i.issuedate FROM issues i WHERE i.returndate IS NO
     $dbh->do("DELETE FROM issues WHERE returndate IS NOT NULL");
     $dbh->do("INSERT INTO old_reserves SELECT * FROM reserves WHERE cancellationdate IS NOT NULL OR found = 'F'");
     $dbh->do("DELETE FROM reserves WHERE cancellationdate IS NOT NULL OR found = 'F'");
@@ -1255,9 +1255,8 @@ if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
 
 $DBversion = "3.00.00.066";
 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
-    $dbh->do("ALTER TABLE `virtualshelfcontents` MODIFY `dateadded` timestamp NOT NULL
-DEFAULT CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP;
-");
+    $dbh->do("UPDATE `virtualshelfcontents` SET `dateadded` = CURRENT_TIMESTAMP WHERE `dateadded` IS NULL;");
+    $dbh->do("ALTER TABLE `virtualshelfcontents` MODIFY `dateadded` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP;");
     print "Upgrade to $DBversion done (fix for bug 1873: virtualshelfcontents dateadded column empty. ) \n";
     SetVersion ($DBversion);
 }
@@ -1273,7 +1272,7 @@ $DBversion = "3.00.00.068";
 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
     $dbh->do("CREATE TABLE `permissions` (
                 `module_bit` int(11) NOT NULL DEFAULT 0,
-                `code` varchar(30) DEFAULT NULL,
+                `code` varchar(30) NOT NULL DEFAULT '',
                 `description` varchar(255) DEFAULT NULL,
                 PRIMARY KEY  (`module_bit`, `code`),
                 CONSTRAINT `permissions_ibfk_1` FOREIGN KEY (`module_bit`) REFERENCES `userflags` (`bit`)
@@ -1282,7 +1281,7 @@ if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
     $dbh->do("CREATE TABLE `user_permissions` (
                 `borrowernumber` int(11) NOT NULL DEFAULT 0,
                 `module_bit` int(11) NOT NULL DEFAULT 0,
-                `code` varchar(30) DEFAULT NULL,
+                `code` varchar(30) NOT NULL DEFAULT '',
                 CONSTRAINT `user_permissions_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`)
                     ON DELETE CASCADE ON UPDATE CASCADE,
                 CONSTRAINT `user_permissions_ibfk_2` FOREIGN KEY (`module_bit`, `code`)
@@ -2134,8 +2133,8 @@ if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
 
 $DBversion = "3.01.00.009";
 if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
-    $dbh->do("ALTER TABLE permissions MODIFY `code` varchar(64) DEFAULT NULL");
-    $dbh->do("ALTER TABLE user_permissions MODIFY `code` varchar(64) DEFAULT NULL");
+    $dbh->do("ALTER TABLE permissions MODIFY `code` varchar(64) NOT NULL DEFAULT ''");
+    $dbh->do("ALTER TABLE user_permissions MODIFY `code` varchar(64) NOT NULL DEFAULT ''");
     $dbh->do("INSERT INTO permissions (module_bit, code, description) VALUES ( 1, 'circulate_remaining_permissions', 'Remaining circulation permissions')");
     $dbh->do("INSERT INTO permissions (module_bit, code, description) VALUES ( 1, 'override_renewals', 'Override blocked renewals')");
     print "Upgrade to $DBversion done (added subpermissions for circulate permission)\n";
@@ -5929,13 +5928,14 @@ if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
     ");
 
     # Fill this new table with existing invoices
+
+
     my $sth = $dbh->prepare("
         SELECT aqorders.booksellerinvoicenumber AS invoicenumber, aqbasket.booksellerid, aqorders.datereceived
         FROM aqorders
           LEFT JOIN aqbasket ON aqorders.basketno = aqbasket.basketno
         WHERE aqorders.booksellerinvoicenumber IS NOT NULL
           AND aqorders.booksellerinvoicenumber != ''
-        GROUP BY aqorders.booksellerinvoicenumber
     ");
     $sth->execute;
     my $results = $sth->fetchall_arrayref({});
@@ -12331,13 +12331,15 @@ my $indexes = LoadFile( $mappings_yaml );
 
 while ( my ( $index_name, $fields ) = each %$indexes ) {
         while ( my ( $field_name, $data ) = each %$fields ) {
-            my $field_type = $data->{type};
+            my $field_type = $data->{type} eq '' ? 'string' : $data->{type};
             my $field_label = $data->{label};
             my $mappings = $data->{mappings};
             my $search_field = Koha::SearchFields->find_or_create({ name => $field_name, label => $field_label, type => $field_type }, { key => 'name' });
             for my $mapping ( @$mappings ) {
                 my $marc_field = Koha::SearchMarcMaps->find_or_create({ index_name => $index_name, marc_type => $mapping->{marc_type}, marc_field => $mapping->{marc_field} });
-                $search_field->add_to_search_marc_maps($marc_field, { facet => $mapping->{facet}, suggestible => $mapping->{suggestible}, sort => $mapping->{sort} } );
+                my $facet_data = $mapping->{facet} eq '' ? 0 : $mapping->{facet};
+                my $suggestible_data = $mapping->{suggestible} eq '' ? 0 : $mapping->{suggestible};
+                $search_field->add_to_search_marc_maps($marc_field, { facet => $facet_data, suggestible => $suggestible_data, sort => $mapping->{sort} } );
             }
         }
 }
Comment 5 Rodrigo Santellan 2016-09-16 13:44:35 UTC
I attached the to files. I'm using ubuntu 14.04.02 LTS with MySQL 5.7 that support full text indexes. But my next step is to use Ubuntu 16.04 LTS that comes with MySQL 5.7.

I'm still running and old Koha 2 but was highly modified.
Comment 6 Zeno Tajoli 2016-09-16 15:04:30 UTC
Attention.
Koha (at general) doesn't work with MysqL 5.7.
See Bug 17258 and connected bugs.

Try to upgrade without using 5.7 or try to stop the strict mode.
Instructions: http://stackoverflow.com/questions/18762308/how-to-get-rid-of-strict-sql-mode-in-mysql

Can you try the update without strict mode ?
Comment 7 Rodrigo Santellan 2016-09-16 15:09:06 UTC
I will create another virtual machine to try that, for Monday I will have the test (low disk space) and post the results. Anyway I don't think it hurts to be a little more strict.
Comment 8 Rodrigo Santellan 2016-09-27 12:22:57 UTC
I have run again the script using this variable on MySQL 5.7:
sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'

And the only thing to change was line 1210 and add the columns: $dbh->do("INSERT INTO old_issues (`borrowernumber`, `itemnumber`, `date_due`, `branchcode`, `issuingbranch`, `returndate`, `lastreneweddate`, `renewals`, `timestamp`, `issuedate`, `return`) SELECT `borrowernumber`, `itemnumber`, `date_due`, `branchcode`, `issuingbranch`, `returndate`, `lastreneweddate`, `renewals`, `timestamp`, `issuedate`, `return` FROM issues WHERE returndate IS NOT NULL;");

I add the new attachment of the updatedatabase.pl
Comment 9 Rodrigo Santellan 2016-09-27 12:24:04 UTC
Created attachment 55836 [details]
With change of columns for select insert.

Change of columns for select insert and added not null for PK