Summary: | Update scripts fail with Mysql 5.7 | ||
---|---|---|---|
Product: | Koha | Reporter: | Rodrigo Santellan <rsantellan> |
Component: | Database | Assignee: | Galen Charlton <gmcharlt> |
Status: | NEW --- | QA Contact: | Testopia <testopia> |
Severity: | enhancement | ||
Priority: | P5 - low | CC: | mtj, ztajoli |
Version: | 16.05 | ||
Hardware: | All | ||
OS: | All | ||
GIT URL: | Change sponsored?: | --- | |
Patch complexity: | --- | Documentation contact: | |
Documentation submission: | Text to go in the release notes: | ||
Version(s) released in: | Circulation function: | ||
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
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 ? An other request: are you trying to use last LTS of Ubuntu? You speak about Mysql 5.7 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'},
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} } ); } } } 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. 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 ? 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. 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 Created attachment 55836 [details]
With change of columns for select insert.
Change of columns for select insert and added not null for PK
|