painful upgrade from 2.25 to 3.0.1: incomplete tables
hello all, i'm following the document: http://wiki.koha.org/doku.php?id=22_to_30 and it's failing when executing the script koha-3.00.01-stable/misc/migration_tools/22_to_30/rebuild_unimarc_100.pl Creating/updating field 100 if needed DBD::mysql::st execute failed: Unknown column 'done' in 'where clause' at /usr/local/koha/lib/C4/Biblio.pm line 2299. DBD::mysql::st fetchrow_array failed: fetch() without execute() at /usr/local/koha/lib/C4/Biblio.pm line 2300. Use of uninitialized value in numeric eq (==) at /usr/local/koha/lib/C4/Biblio.pm line 2302. ... the reason is that the zebraqueue table isn't as expected: CREATE TABLE `zebraqueue` ( `id` int(11) NOT NULL auto_increment, `biblio_auth_number` int(11) NOT NULL default '0', `operation` char(20) NOT NULL default '', `server` char(20) NOT NULL default '', `done` int(11) NOT NULL default '0', `time` timestamp NOT NULL default CURRENT_TIMESTAMP, PRIMARY KEY (`id`), KEY `zebraqueue_lookup` (`server`, `biblio_auth_number`, `operation`, `done`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; but my actual table looks like: CREATE TABLE `zebraqueue` ( `id` int(11) NOT NULL AUTO_INCREMENT, `biblio_auth_number` int(11) NOT NULL DEFAULT '0', `operation` char(20) NOT NULL DEFAULT '', `server` char(20) NOT NULL DEFAULT '', PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=9555 DEFAULT CHARSET=utf8; as you can see, done and time are missing, so my guess is that something was wrong during the script execution, update22to30.pl ? The only errors i had are: 090319 9:23:24 [Warning] option 'thread_stack': unsigned value 65536 adjusted to 131072 DBD::mysql::db do failed: Cannot add or update a child row: a foreign key constraint fails (`BDkoha4_test`.`#sql-455b_55bd`, CONSTRAINT `#sql-455b_55bd_ibfk_3` FOREIGN KEY (`holdingbranch`) REFERENCES `branches` (`branchcode`) ON UPDATE CASCADE) at /home/henry/koha-3.00.01-stable/installer/data/mysql/update22to30.pl line 3442. ==================== An error occured during : alter table items ADD FOREIGN KEY holdingbranch (holdingbranch) REFERENCES branches (branchcode) on update CASCADE on delete RESTRICT It probably means there is something wrong in your DB : a row (items.holdingbranch) refers to a value in branches.branchcode that does not exist. solve the problem and run updater again (or just the previous SQL statement). You can find those values with select items.* from items where holdingbranch not in (select branchcode from branches) ==================== the last error is easily corrected, but for the first one, i have no idea if it's related to the uncomplete table? and how can i verify if others tables where affected during the update process? thanks in advance for reply, gerard
the reason is that the zebraqueue table isn't as expected: CREATE TABLE `zebraqueue` ( `id` int(11) NOT NULL auto_increment, `biblio_auth_number` int(11) NOT NULL default '0', `operation` char(20) NOT NULL default '', `server` char(20) NOT NULL default '', `done` int(11) NOT NULL default '0', `time` timestamp NOT NULL default CURRENT_TIMESTAMP, PRIMARY KEY (`id`), KEY `zebraqueue_lookup` (`server`, `biblio_auth_number`, `operation`, `done`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
but my actual table looks like: CREATE TABLE `zebraqueue` ( `id` int(11) NOT NULL AUTO_INCREMENT, `biblio_auth_number` int(11) NOT NULL DEFAULT '0', `operation` char(20) NOT NULL DEFAULT '', `server` char(20) NOT NULL DEFAULT '', PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=9555 DEFAULT CHARSET=utf8;
In kohastructure.sql, you get: CREATE TABLE `zebraqueue` ( `id` int(11) NOT NULL auto_increment, `biblio_auth_number` int(11) NOT NULL default '0', `operation` char(20) NOT NULL default '', `server` char(20) NOT NULL default '', `done` int(11) NOT NULL default '0', `time` timestamp NOT NULL default CURRENT_TIMESTAMP, PRIMARY KEY (`id`), KEY `zebraqueue_lookup` (`server`, `biblio_auth_number`, `operation`, `done`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; In update22to30.pl, you get: zebraqueue => "( `id` int NOT NULL auto_increment, `biblio_auth_number` int(11) NOT NULL default '0', `operation` char(20) NOT NULL default '', `server` char(20) NOT NULL default '', PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci AUTO_INCREMENT=1", ); IMHO, update22to30.pl should be updated itself. But updatedatabase.pl does also tables upgrades depending on kohaversion.pl Koha version. So after an upgrade from 2 to 3 version. updatedatabase.pl will update required tables. For example zebraqueue: ALTER TABLE `zebraqueue` ADD `done` INT NOT NULL DEFAULT '0', ADD `time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ; -- Frédéric
Frederic Demians wrote:
the reason is that the zebraqueue table isn't as expected: CREATE TABLE `zebraqueue` ( `id` int(11) NOT NULL auto_increment, `biblio_auth_number` int(11) NOT NULL default '0', `operation` char(20) NOT NULL default '', `server` char(20) NOT NULL default '', `done` int(11) NOT NULL default '0', `time` timestamp NOT NULL default CURRENT_TIMESTAMP, PRIMARY KEY (`id`), KEY `zebraqueue_lookup` (`server`, `biblio_auth_number`, `operation`, `done`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
but my actual table looks like: CREATE TABLE `zebraqueue` ( `id` int(11) NOT NULL AUTO_INCREMENT, `biblio_auth_number` int(11) NOT NULL DEFAULT '0', `operation` char(20) NOT NULL DEFAULT '', `server` char(20) NOT NULL DEFAULT '', PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=9555 DEFAULT CHARSET=utf8;
In kohastructure.sql, you get:
CREATE TABLE `zebraqueue` ( `id` int(11) NOT NULL auto_increment, `biblio_auth_number` int(11) NOT NULL default '0', `operation` char(20) NOT NULL default '', `server` char(20) NOT NULL default '', `done` int(11) NOT NULL default '0', `time` timestamp NOT NULL default CURRENT_TIMESTAMP, PRIMARY KEY (`id`), KEY `zebraqueue_lookup` (`server`, `biblio_auth_number`, `operation`, `done`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
In update22to30.pl, you get:
zebraqueue => "( `id` int NOT NULL auto_increment, `biblio_auth_number` int(11) NOT NULL default '0', `operation` char(20) NOT NULL default '', `server` char(20) NOT NULL default '', PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci AUTO_INCREMENT=1", ); IMHO, update22to30.pl should be updated itself.
but done and time are still missing here!?
But updatedatabase.pl does also tables upgrades depending on kohaversion.pl Koha version. So after an upgrade from 2 to 3 version. updatedatabase.pl will update required tables. For example zebraqueue:
ALTER TABLE `zebraqueue` ADD `done` INT NOT NULL DEFAULT '0', ADD `time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ;
do you suggest that i have to execute this script (updatedatabase.pl) after running update22to30.pl ? or instead of? what's the correct way? thanks for your reply, gerard
do you suggest that i have to execute this script (updatedatabase.pl) after running update22to30.pl ? or instead of? what's the correct way?
After. I would also take a look at: * kohaversion.pl * Version system preference (Admin > System Preferences) Compare both values. It will tell you what updatedatabase.pl will do. -- Frédéric
Frederic Demians wrote:
do you suggest that i have to execute this script (updatedatabase.pl) after running update22to30.pl ? or instead of? what's the correct way?
After. I would also take a look at:
* kohaversion.pl
our $VERSION = '3.00.01.005';
* Version system preference (Admin > System Preferences)
but at this point, my installation is totally broken, isn't it? until i finished to execute all the necessary scripts if (C4::Context->preference("Version") < TransformToNum($DBversion)) { in what file C4::Context gets Version?
Compare both values. It will tell you what updatedatabase.pl will do.
but at this point, my installation is totally broken, isn't it?
I can't say. But yes, you may have to restore a backup and redo the whole process.
if (C4::Context->preference("Version") < TransformToNum($DBversion)) { in what file C4::Context gets Version?
DB table: systempreferences select value from systempreferences where variable='Version' -- Frédéric
Frederic Demians wrote:
but at this point, my installation is totally broken, isn't it?
I can't say. But yes, you may have to restore a backup and redo the whole process.
if (C4::Context->preference("Version") < TransformToNum($DBversion)) { in what file C4::Context gets Version?
DB table: systempreferences select value from systempreferences where variable='Version'
ok, it works, because i forgot to launch a script, as stated in the wiki. But some scripts are failing, i'll open a new thread thanks for your help, gerard
participants (2)
-
Frederic Demians -
ghenry