Bug 11623 - Errors with upgrade from 3.00.00.070 to 3.14.02.000
Summary: Errors with upgrade from 3.00.00.070 to 3.14.02.000
Status: CLOSED WONTFIX
Alias: None
Product: Koha
Classification: Unclassified
Component: Installation and upgrade (web-based installer) (show other bugs)
Version: 3.14
Hardware: All All
: P5 - low normal (vote)
Assignee: Bugs List
QA Contact: Testopia
URL:
Keywords:
Depends on:
Blocks:
 
Reported: 2014-01-28 06:06 UTC by David White
Modified: 2017-06-14 22:10 UTC (History)
3 users (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 White 2014-01-28 06:06:32 UTC
There are a number of database upgrade errors that take place when upgrading from 3.00.00.070 to 3.14.02.000

* A number of upgrades expect the tables to have foreign keys and be in InnoDB format.  In 3.00.00.070, MyISAM (and no foreign keys) is the standard.  With no testing to see if the keys exist, these commands fail with (errno: 152)

Problem examples:
ALTER TABLE issues DROP FOREIGN KEY `issues_ibfk_1`
ALTER TABLE issues DROP FOREIGN KEY `issues_ibfk_2`
ALTER TABLE `message_transports` DROP FOREIGN KEY `message_transports_ibfk_3`
ALTER TABLE patronimage DROP FOREIGN KEY patronimage_fk1


* In updatedatabase.pl, in the section for DBVersion 3.00.00.091, there are references to the @sql_scripts "installer/data/mysql/en/optional/sample_notices_message_attributes.sql" and "installer/data/mysql/en/optional/sample_notices_message_transports.sql" .  These should be "installer/data/mysql/en/mandatory/sample_notices_message_attributes.sql" and "installer/data/mysql/en/mandatory/sample_notices_message_transports.sql"


* Even when that's corrected, the sample_notices_message_transports.sql script fails because it references foreign keys to the "letters" table that have not been created yet.  The reserves/HOLD combo isn't created until the 3.01.00.05 section of the updatedatabase.pl script, and circulation/CHECKIN and circulation/CHECKOUT are not created until the 3.01.00.013 section.
Comment 1 David White 2014-01-28 06:27:19 UTC
I'll just add my own workarounds to these:

* Convert everything to InnoDB prior to running the upgrade script.

* Of these ALTER TABLE DROP FOREIGN KEY errors, the only one that seems to actually generate a red warning message is the patronimage one.  I solved this by pre-creating the "patronimage" table with the patronimage_fk1 foreign key, so the script can happily drop it

* Rewrote the /optional/ pathnames to /mandatory/

* Altered the sample_notices_message_transports.sql to delete these lines:

(4, 'email', 0, 'reserves',    'HOLD')
(4, 'sms',   0, 'reserves',    'HOLD')
(5, 'email', 0, 'circulation', 'CHECKIN')
(5, 'sms',   0, 'circulation', 'CHECKIN')
(6, 'email', 0, 'circulation', 'CHECKOUT')
(6, 'sms',   0, 'circulation', 'CHECKOUT')

These will allow the script to run at the right time, and the 6 lines above will be created later by updatedatabase.pl later in the script anyway (when the appropriate entries in the 'letters' table have also been created).
Comment 2 David White 2014-01-28 06:47:42 UTC
Sorry, one other bug:

* When attempting to run "misc/maintenance/remove_items_from_biblioitems.pl", a number of SQL statements fail because C4/Biblio.pm tries to update columns that don't exist in 3.00.00.070 .  Specifically, any attempt to "UPDATE biblioitems" that refers to the "ean" and "agerestriction" column fails.

* workaround: temporarily remove references to those columns in C4/Biblio.pm, and restore them after "remove_items_from_biblioitems.pl" has completed.
Comment 3 Liz Rea 2014-04-24 02:44:55 UTC
Probably we'll keep this around (and open) for posterity, but I suspect that we won't be fixing these issues. 

Thanks for adding your workarounds, they will probably help someone else someday!
Comment 4 Owen Leonard 2016-08-10 14:30:31 UTC
I think 3.14 is old enough now that this report doesn't need to stay open.