{UNKNOWN}: DBI Exception: DBD::mysql::db do failed: Duplicate entry '0' for key 'PRIMARY'
Hi We have loaded our Koha 19.11 database into a new Koha 24.05 instance. When running "koha-upgrade-schema" the following error appears: ... Upgrade to 19.12.00.017 [12:00:41]: Bug 21674 - Add unique key (parent_id, branchcode) to library_group {UNKNOWN}: DBI Exception: DBD::mysql::db do failed: Duplicate entry '0' for key 'PRIMARY' at /usr/share/perl5/DBIx/Class/Schema.pm line 1118. DBIx::Class::Schema::throw_exception(Koha::Schema=HASH(0x55a4b4a0dba8), "DBI Exception: DBD::mysql::db do failed: Duplicate entry '0' "...) called at /usr/share/perl5/DBIx/Class/Storage.pm line 113 DBIx::Class::Storage::throw_exception(DBIx::Class::Storage::DBI::mysql=HASH(0x55a4b9ff1b70), "DBI Exception: DBD::mysql::db do failed: Duplicate entry '0' "...) called at /usr/share/perl5/DBIx/Class/Storage/DBI.pm line 1623 DBIx::Class::Storage::DBI::__ANON__("DBD::mysql::db do failed: Duplicate entry '0' for key 'PRIMARY'", DBI::db=HASH(0x55a4ba7d2b38), undef) called at /usr/share/koha/intranet/cgi-bin/installer/data/mysql/updatedatabase.pl line 20736 Does anyone know how to solve this problem? At the moment I'm not even able to find out in which table the duplicate entry can be found... Best wishes: Michael -- Geschäftsführer · Diplombibliothekar BBS, Informatiker eidg. Fachausweis Admin Kuhn GmbH · Pappelstrasse 20 · 4123 Allschwil · Schweiz T 0041 (0)61 261 55 61 · E mik@adminkuhn.ch · W www.adminkuhn.ch
updatedatabase.pl line 20736 It's the conversion from issuingrules to circulation_rules. What's the state of your DB right now? Has it started to convert? How many circ rules already created? I suspect it's 0, but it's worth asking. Considering the id is an autoincrement, and it not moved from one table to the other, I fail to see how you could have two "0"... (even one, actually). Logo inLibro <https://inLibro.com> Philippe Blouin Directeur de la technologie T 833-INLIBRO (465-4276) <tel:833-465-4276>, poste 230 C philippe.blouin@inLibro.com www.inLibro.com <https://inLibro.com> On 2024-07-10 06:46, Michael Kuhn wrote:
Hi
We have loaded our Koha 19.11 database into a new Koha 24.05 instance. When running "koha-upgrade-schema" the following error appears:
... Upgrade to 19.12.00.017 [12:00:41]: Bug 21674 - Add unique key (parent_id, branchcode) to library_group {UNKNOWN}: DBI Exception: DBD::mysql::db do failed: Duplicate entry '0' for key 'PRIMARY' at /usr/share/perl5/DBIx/Class/Schema.pm line 1118. DBIx::Class::Schema::throw_exception(Koha::Schema=HASH(0x55a4b4a0dba8), "DBI Exception: DBD::mysql::db do failed: Duplicate entry '0' "...) called at /usr/share/perl5/DBIx/Class/Storage.pm line 113 DBIx::Class::Storage::throw_exception(DBIx::Class::Storage::DBI::mysql=HASH(0x55a4b9ff1b70), "DBI Exception: DBD::mysql::db do failed: Duplicate entry '0' "...) called at /usr/share/perl5/DBIx/Class/Storage/DBI.pm line 1623 DBIx::Class::Storage::DBI::__ANON__("DBD::mysql::db do failed: Duplicate entry '0' for key 'PRIMARY'", DBI::db=HASH(0x55a4ba7d2b38), undef) called at /usr/share/koha/intranet/cgi-bin/installer/data/mysql/updatedatabase.pl line 20736
Does anyone know how to solve this problem? At the moment I'm not even able to find out in which table the duplicate entry can be found...
Best wishes: Michael
Hi Philippe You wrote:
updatedatabase.pl line 20736
It's the conversion from issuingrules to circulation_rules. What's the state of your DB right now? Has it started to convert?
The script stopped just after giving the shown message.
How many circ rules already created? I suspect it's 0, but it's worth asking.
In the database I find 13 rules: MariaDB [koha_bib]> select * from circulation_rules; +----+------------+--------------+----------+-------------------+------------+ | id | branchcode | categorycode | itemtype | rule_name | rule_value | +----+------------+--------------+----------+-------------------+------------+ | 2 | NULL | NULL | NULL | refund | 1 | | 16 | NULL | DIENST | NULL | maxissueqty | 9999 | | 17 | NULL | DIENST | NULL | maxonsiteissueqty | 9999 | | 18 | NULL | ARCHIV | NULL | maxissueqty | 200 | | 19 | NULL | ARCHIV | NULL | maxonsiteissueqty | 200 | | 20 | NULL | ARCHBE | NULL | maxissueqty | 50 | | 21 | NULL | ARCHBE | NULL | maxonsiteissueqty | 50 | | 22 | NULL | ARCHPR | NULL | maxissueqty | 200 | | 23 | NULL | ARCHPR | NULL | maxonsiteissueqty | 200 | | 26 | NULL | INSTIT | NULL | maxonsiteissueqty | 0 | | 27 | NULL | INSTIT | NULL | maxissueqty | 0 | | 28 | NULL | INSTMI | NULL | maxonsiteissueqty | 200 | | 29 | NULL | INSTMI | NULL | maxissueqty | 200 | +----+------------+--------------+----------+-------------------+------------+ 13 rows in set (0,000 sec)
Considering the id is an autoincrement, and it not moved from one table to the other, I fail to see how you could have two "0"... (even one, actually).
The Auto_increment value for table "issuingrules" is NULL: MariaDB [koha_bib]> SELECT `AUTO_INCREMENT` FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'koha_bib' AND TABLE_NAME = 'issuingrules'; +----------------+ | AUTO_INCREMENT | +----------------+ | NULL | +----------------+ I'm also sending you the output of "select * from issuingrules" in the attached file. Maybe it shows something to you that I don't see? I don't know how to continue with this update? Best wishes: Michael -- Geschäftsführer · Diplombibliothekar BBS, Informatiker eidg. Fachausweis Admin Kuhn GmbH · Pappelstrasse 20 · 4123 Allschwil · Schweiz T 0041 (0)61 261 55 61 · E mik@adminkuhn.ch · W www.adminkuhn.ch
On 2024-07-10 06:46, Michael Kuhn wrote:
Hi
We have loaded our Koha 19.11 database into a new Koha 24.05 instance. When running "koha-upgrade-schema" the following error appears:
... Upgrade to 19.12.00.017 [12:00:41]: Bug 21674 - Add unique key (parent_id, branchcode) to library_group {UNKNOWN}: DBI Exception: DBD::mysql::db do failed: Duplicate entry '0' for key 'PRIMARY' at /usr/share/perl5/DBIx/Class/Schema.pm line 1118. DBIx::Class::Schema::throw_exception(Koha::Schema=HASH(0x55a4b4a0dba8), "DBI Exception: DBD::mysql::db do failed: Duplicate entry '0' "...) called at /usr/share/perl5/DBIx/Class/Storage.pm line 113 DBIx::Class::Storage::throw_exception(DBIx::Class::Storage::DBI::mysql=HASH(0x55a4b9ff1b70), "DBI Exception: DBD::mysql::db do failed: Duplicate entry '0' "...) called at /usr/share/perl5/DBIx/Class/Storage/DBI.pm line 1623 DBIx::Class::Storage::DBI::__ANON__("DBD::mysql::db do failed: Duplicate entry '0' for key 'PRIMARY'", DBI::db=HASH(0x55a4ba7d2b38), undef) called at /usr/share/koha/intranet/cgi-bin/installer/data/mysql/updatedatabase.pl line 20736
Does anyone know how to solve this problem? At the moment I'm not even able to find out in which table the duplicate entry can be found...
Best wishes: Michael
Ok, let's cheat. You have very few rules, and most of the info has been moved already. So just comment the lines 20734 to 20746 and run updatedatabase.pl again. OR modify the version to move ahead. update systempreferences set value = '19.1200018' where variable = 'version'; Then you can drop the issuingrules table later, when you're satisfied with everything. Note that you'll need to update the rules manually in the staff interface to add back the issue length and renewal count/period... Logo inLibro <https://inLibro.com> Philippe Blouin Directeur de la technologie T 833-INLIBRO (465-4276) <tel:833-465-4276>, poste 230 C philippe.blouin@inLibro.com www.inLibro.com <https://inLibro.com> On 2024-07-10 08:10, Michael Kuhn wrote:
Hi Philippe
You wrote:
updatedatabase.pl line 20736
It's the conversion from issuingrules to circulation_rules. What's the state of your DB right now? Has it started to convert?
The script stopped just after giving the shown message.
How many circ rules already created? I suspect it's 0, but it's worth asking.
In the database I find 13 rules:
MariaDB [koha_bib]> select * from circulation_rules; +----+------------+--------------+----------+-------------------+------------+
| id | branchcode | categorycode | itemtype | rule_name | rule_value | +----+------------+--------------+----------+-------------------+------------+
| 2 | NULL | NULL | NULL | refund | 1 | | 16 | NULL | DIENST | NULL | maxissueqty | 9999 | | 17 | NULL | DIENST | NULL | maxonsiteissueqty | 9999 | | 18 | NULL | ARCHIV | NULL | maxissueqty | 200 | | 19 | NULL | ARCHIV | NULL | maxonsiteissueqty | 200 | | 20 | NULL | ARCHBE | NULL | maxissueqty | 50 | | 21 | NULL | ARCHBE | NULL | maxonsiteissueqty | 50 | | 22 | NULL | ARCHPR | NULL | maxissueqty | 200 | | 23 | NULL | ARCHPR | NULL | maxonsiteissueqty | 200 | | 26 | NULL | INSTIT | NULL | maxonsiteissueqty | 0 | | 27 | NULL | INSTIT | NULL | maxissueqty | 0 | | 28 | NULL | INSTMI | NULL | maxonsiteissueqty | 200 | | 29 | NULL | INSTMI | NULL | maxissueqty | 200 | +----+------------+--------------+----------+-------------------+------------+
13 rows in set (0,000 sec)
Considering the id is an autoincrement, and it not moved from one table to the other, I fail to see how you could have two "0"... (even one, actually).
The Auto_increment value for table "issuingrules" is NULL:
MariaDB [koha_bib]> SELECT `AUTO_INCREMENT` FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'koha_bib' AND TABLE_NAME = 'issuingrules'; +----------------+ | AUTO_INCREMENT | +----------------+ | NULL | +----------------+
I'm also sending you the output of "select * from issuingrules" in the attached file. Maybe it shows something to you that I don't see?
I don't know how to continue with this update?
Best wishes: Michael
Hi Philippe Many thanks! You wrote:
Ok, let's cheat. You have very few rules, and most of the info has been moved already. So just comment the lines 20734 to 20746 and run updatedatabase.pl again.
OR modify the version to move ahead.
update systempreferences set value = '19.1200018' where variable = 'version';
Then you can drop the issuingrules table later, when you're satisfied with everything. Note that you'll need to update the rules manually in the staff interface to add back the issue length and renewal count/period...
I have modified the version and then moved ahead... unfortunately there is another duplicate entry in another table and the script aborted again: Upgrade to 19.12.00.029 [15:13:32]: Bug 17702 - Add column account_credit_types.archived {UNKNOWN}: DBI Exception: DBD::mysql::db do failed: Duplicate entry '0' for key 'PRIMARY' at /usr/share/perl5/DBIx/Class/Schema.pm line 1118. DBIx::Class::Schema::throw_exception(Koha::Schema=HASH(0x5598ef055ba8), "DBI Exception: DBD::mysql::db do failed: Duplicate entry '0' "...) called at /usr/share/perl5/DBIx/Class/Storage.pm line 113 DBIx::Class::Storage::throw_exception(DBIx::Class::Storage::DBI::mysql=HASH(0x5598f4639198), "DBI Exception: DBD::mysql::db do failed: Duplicate entry '0' "...) called at /usr/share/perl5/DBIx/Class/Storage/DBI.pm line 1623 DBIx::Class::Storage::DBI::__ANON__("DBD::mysql::db do failed: Duplicate entry '0' for key 'PRIMARY'", DBI::db=HASH(0x5598f4e19490), undef) called at /usr/share/koha/intranet/cgi-bin/installer/data/mysql/updatedatabase.pl line 20966 How should I proceed? Is there a way how I can check the original database for duplicate entries so the update process won't abort? Best wishes: Michael -- Geschäftsführer · Diplombibliothekar BBS, Informatiker eidg. Fachausweis Admin Kuhn GmbH · Pappelstrasse 20 · 4123 Allschwil · Schweiz T 0041 (0)61 261 55 61 · E mik@adminkuhn.ch · W www.adminkuhn.ch
On 2024-07-10 08:10, Michael Kuhn wrote:
Hi Philippe
You wrote:
updatedatabase.pl line 20736
It's the conversion from issuingrules to circulation_rules. What's the state of your DB right now? Has it started to convert?
The script stopped just after giving the shown message.
How many circ rules already created? I suspect it's 0, but it's worth asking.
In the database I find 13 rules:
MariaDB [koha_bib]> select * from circulation_rules; +----+------------+--------------+----------+-------------------+------------+ | id | branchcode | categorycode | itemtype | rule_name | rule_value | +----+------------+--------------+----------+-------------------+------------+ | 2 | NULL | NULL | NULL | refund | 1 | | 16 | NULL | DIENST | NULL | maxissueqty | 9999 | | 17 | NULL | DIENST | NULL | maxonsiteissueqty | 9999 | | 18 | NULL | ARCHIV | NULL | maxissueqty | 200 | | 19 | NULL | ARCHIV | NULL | maxonsiteissueqty | 200 | | 20 | NULL | ARCHBE | NULL | maxissueqty | 50 | | 21 | NULL | ARCHBE | NULL | maxonsiteissueqty | 50 | | 22 | NULL | ARCHPR | NULL | maxissueqty | 200 | | 23 | NULL | ARCHPR | NULL | maxonsiteissueqty | 200 | | 26 | NULL | INSTIT | NULL | maxonsiteissueqty | 0 | | 27 | NULL | INSTIT | NULL | maxissueqty | 0 | | 28 | NULL | INSTMI | NULL | maxonsiteissueqty | 200 | | 29 | NULL | INSTMI | NULL | maxissueqty | 200 | +----+------------+--------------+----------+-------------------+------------+ 13 rows in set (0,000 sec)
Considering the id is an autoincrement, and it not moved from one table to the other, I fail to see how you could have two "0"... (even one, actually).
The Auto_increment value for table "issuingrules" is NULL:
MariaDB [koha_bib]> SELECT `AUTO_INCREMENT` FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'koha_bib' AND TABLE_NAME = 'issuingrules'; +----------------+ | AUTO_INCREMENT | +----------------+ | NULL | +----------------+
I'm also sending you the output of "select * from issuingrules" in the attached file. Maybe it shows something to you that I don't see?
I don't know how to continue with this update?
Best wishes: Michael
Hi At my own risk I have modified the version to 19.12.00.029 and then moved ahead... (of course table "opac_news" will have to be checked after the update) Unfortunately then another error popped up: Upgrade to 19.12.00.075 [15:28:32]: Bug 25086 - Set changed_fields column of borrower_modifications as nullable {UNKNOWN}: DBI Exception: DBD::mysql::db do failed: Can't create table `koha_bib`.`serial` (errno: 150 "Foreign key constraint is incorrectly formed") at /usr/share/perl5/DBIx/Class/Schema.pm line 1118. DBIx::Class::Schema::throw_exception(Koha::Schema=HASH(0x5654e142fba8), "DBI Exception: DBD::mysql::db do failed: Can't create table `"...) called at /usr/share/perl5/DBIx/Class/Storage.pm line 113 DBIx::Class::Storage::throw_exception(DBIx::Class::Storage::DBI::mysql=HASH(0x5654e693cdd8), "DBI Exception: DBD::mysql::db do failed: Can't create table `"...) called at /usr/share/perl5/DBIx/Class/Storage/DBI.pm line 1623 DBIx::Class::Storage::DBI::__ANON__("DBD::mysql::db do failed: Can't create table `koha_bib`.`seri"..., DBI::db=HASH(0x5654e71fe510), undef) called at /usr/share/koha/intranet/cgi-bin/installer/data/mysql/updatedatabase.pl line 21746 I guess in this case I cannot just modify the version and move ahead. I assume there will be more errors like this since the process hasn't even reached Koha 20.05. To me it seems like this database is corrupt and I cannot proceed to Koha 24.05. Now my question ist - how can I check the integrity of the Koha 19.11 database so the update process to reach Koha 24.05 will work as expected? Best wisshes: Michael -- Geschäftsführer · Diplombibliothekar BBS, Informatiker eidg. Fachausweis Admin Kuhn GmbH · Pappelstrasse 20 · 4123 Allschwil · Schweiz T 0041 (0)61 261 55 61 · E mik@adminkuhn.ch · W www.adminkuhn.ch Am 10.07.24 um 15:20 schrieb Michael Kuhn via Koha-devel:
Hi Philippe
Many thanks! You wrote:
Ok, let's cheat. You have very few rules, and most of the info has been moved already. So just comment the lines 20734 to 20746 and run updatedatabase.pl again.
OR modify the version to move ahead.
update systempreferences set value = '19.1200018' where variable = 'version';
Then you can drop the issuingrules table later, when you're satisfied with everything. Note that you'll need to update the rules manually in the staff interface to add back the issue length and renewal count/period...
I have modified the version and then moved ahead... unfortunately there is another duplicate entry in another table and the script aborted again:
Upgrade to 19.12.00.029 [15:13:32]: Bug 17702 - Add column account_credit_types.archived {UNKNOWN}: DBI Exception: DBD::mysql::db do failed: Duplicate entry '0' for key 'PRIMARY' at /usr/share/perl5/DBIx/Class/Schema.pm line 1118. DBIx::Class::Schema::throw_exception(Koha::Schema=HASH(0x5598ef055ba8), "DBI Exception: DBD::mysql::db do failed: Duplicate entry '0' "...) called at /usr/share/perl5/DBIx/Class/Storage.pm line 113 DBIx::Class::Storage::throw_exception(DBIx::Class::Storage::DBI::mysql=HASH(0x5598f4639198), "DBI Exception: DBD::mysql::db do failed: Duplicate entry '0' "...) called at /usr/share/perl5/DBIx/Class/Storage/DBI.pm line 1623 DBIx::Class::Storage::DBI::__ANON__("DBD::mysql::db do failed: Duplicate entry '0' for key 'PRIMARY'", DBI::db=HASH(0x5598f4e19490), undef) called at /usr/share/koha/intranet/cgi-bin/installer/data/mysql/updatedatabase.pl line 20966
How should I proceed?
Is there a way how I can check the original database for duplicate entries so the update process won't abort?
Best wishes: Michael
If you're a bit adventurous (and have some time, which you're probably thinking you've burnt enough already): try to update to 20.05, for example, using 20.05 code. So you reset your installation (you have saved your DB, right?), then instead of installing a 24.05, you install an older version, like 20.05 and do the update. The specific lines in updatedatabase.pl probably are the same, but a lot of underlying plumbing could be different and work badly with your system. I'd start by ruling that out. Logo inLibro <https://inLibro.com> Philippe Blouin Directeur de la technologie T 833-INLIBRO (465-4276) <tel:833-465-4276>, poste 230 C philippe.blouin@inLibro.com www.inLibro.com <https://inLibro.com> On 2024-07-10 09:44, Michael Kuhn via Koha-devel wrote:
Hi
At my own risk I have modified the version to 19.12.00.029 and then moved ahead... (of course table "opac_news" will have to be checked after the update)
Unfortunately then another error popped up:
Upgrade to 19.12.00.075 [15:28:32]: Bug 25086 - Set changed_fields column of borrower_modifications as nullable {UNKNOWN}: DBI Exception: DBD::mysql::db do failed: Can't create table `koha_bib`.`serial` (errno: 150 "Foreign key constraint is incorrectly formed") at /usr/share/perl5/DBIx/Class/Schema.pm line 1118. DBIx::Class::Schema::throw_exception(Koha::Schema=HASH(0x5654e142fba8), "DBI Exception: DBD::mysql::db do failed: Can't create table `"...) called at /usr/share/perl5/DBIx/Class/Storage.pm line 113 DBIx::Class::Storage::throw_exception(DBIx::Class::Storage::DBI::mysql=HASH(0x5654e693cdd8), "DBI Exception: DBD::mysql::db do failed: Can't create table `"...) called at /usr/share/perl5/DBIx/Class/Storage/DBI.pm line 1623 DBIx::Class::Storage::DBI::__ANON__("DBD::mysql::db do failed: Can't create table `koha_bib`.`seri"..., DBI::db=HASH(0x5654e71fe510), undef) called at /usr/share/koha/intranet/cgi-bin/installer/data/mysql/updatedatabase.pl line 21746
I guess in this case I cannot just modify the version and move ahead. I assume there will be more errors like this since the process hasn't even reached Koha 20.05. To me it seems like this database is corrupt and I cannot proceed to Koha 24.05.
Now my question ist - how can I check the integrity of the Koha 19.11 database so the update process to reach Koha 24.05 will work as expected?
Best wisshes: Michael
Hi Philippe You wrote:
If you're a bit adventurous (and have some time, which you're probably thinking you've burnt enough already): try to update to 20.05, for example, using 20.05 code.
So you reset your installation (you have saved your DB, right?), then instead of installing a 24.05, you install an older version, like 20.05 and do the update.
The specific lines in updatedatabase.pl probably are the same, but a lot of underlying plumbing could be different and work badly with your system. I'd start by ruling that out.
1. On an different host with Koha 23.11 I loaded the database which then showed the following error: ERROR 1452 (23000) at line 8292833: Cannot add or update a child row: a foreign key constraint fails (`koha_test`.`#sql-alter-368-2a`, CONSTRAINT `borrower_message_transport_preferences_ibfk_1` FOREIGN KEY (`borrower_message_preference_id`) REFERENCES `borrower_message_preferences` (`borrower_) 2. I didn't care for it and then tried to update the database. This time the update process went far far further but it still aborted here: Upgrade to 23.06.00.013 [17:56:51]: Bug 28966 - Holds queue view too slow to load for large numbers of holds ERROR - {UNKNOWN}: DBI Exception: DBD::mysql::db do failed: Can't DROP CONSTRAINT `tmp_holdsqueue_ibfk_1`; check that it exists at /usr/share/koha/lib/C4/Installer.pm line 741 This seems to be a different problem and I'm hoping the solution could lead to the road of success... Do you happen to know how to solve this? 3. Furthermore I saw the database dump file I received for the update ends like this: -- -- Constraints der Tabelle `virtualshelves` -- ALTER TABLE `virtualshelves` ADD CONSTRAINT `virtualshelves_ibfk_1` FOREIGN KEY (`owner`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE SET NULL ON UPDATE SET NULL; COMMIT; /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */; /*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */; /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */; while the database dump files I usually create myself do not contain constraints at all and end like this: -- Dump completed on 2024-07-10 6:25:22 Does this make sense to you? Best wishes: Michael -- Geschäftsführer · Diplombibliothekar BBS, Informatiker eidg. Fachausweis Admin Kuhn GmbH · Pappelstrasse 20 · 4123 Allschwil · Schweiz T 0041 (0)61 261 55 61 · E mik@adminkuhn.ch · W www.adminkuhn.ch
On 2024-07-10 09:44, Michael Kuhn via Koha-devel wrote:
Hi
At my own risk I have modified the version to 19.12.00.029 and then moved ahead... (of course table "opac_news" will have to be checked after the update)
Unfortunately then another error popped up:
Upgrade to 19.12.00.075 [15:28:32]: Bug 25086 - Set changed_fields column of borrower_modifications as nullable {UNKNOWN}: DBI Exception: DBD::mysql::db do failed: Can't create table `koha_bib`.`serial` (errno: 150 "Foreign key constraint is incorrectly formed") at /usr/share/perl5/DBIx/Class/Schema.pm line 1118. DBIx::Class::Schema::throw_exception(Koha::Schema=HASH(0x5654e142fba8), "DBI Exception: DBD::mysql::db do failed: Can't create table `"...) called at /usr/share/perl5/DBIx/Class/Storage.pm line 113 DBIx::Class::Storage::throw_exception(DBIx::Class::Storage::DBI::mysql=HASH(0x5654e693cdd8), "DBI Exception: DBD::mysql::db do failed: Can't create table `"...) called at /usr/share/perl5/DBIx/Class/Storage/DBI.pm line 1623 DBIx::Class::Storage::DBI::__ANON__("DBD::mysql::db do failed: Can't create table `koha_bib`.`seri"..., DBI::db=HASH(0x5654e71fe510), undef) called at /usr/share/koha/intranet/cgi-bin/installer/data/mysql/updatedatabase.pl line 21746
I guess in this case I cannot just modify the version and move ahead. I assume there will be more errors like this since the process hasn't even reached Koha 20.05. To me it seems like this database is corrupt and I cannot proceed to Koha 24.05.
Now my question ist - how can I check the integrity of the Koha 19.11 database so the update process to reach Koha 24.05 will work as expected?
Best wisshes: Michael
If you went with an existing 23.11 and tried to overwrite the database with you 19.05 one, it's possible it got conflicts since it drop-and-add in the order in the file and dependencies might break. Always easier to just drop the old database and load the "new" one in an empty space. Then you went from 19 to 23.06.00.013, which is much further, but then failed on something unrelated to any previous error. (beside being a constraint, like the primary key earlier). Cannot dropping something that doesn't exist is NOT a deal breaker, so try comment installer/data/mysql/db_revs/230600014.pl line 11, and try again. Logo inLibro <https://inLibro.com> Philippe Blouin Directeur de la technologie T 833-INLIBRO (465-4276) <tel:833-465-4276>, poste 230 C philippe.blouin@inLibro.com www.inLibro.com <https://inLibro.com> On 2024-07-10 12:30, Michael Kuhn wrote:
Hi Philippe
You wrote:
If you're a bit adventurous (and have some time, which you're probably thinking you've burnt enough already): try to update to 20.05, for example, using 20.05 code.
So you reset your installation (you have saved your DB, right?), then instead of installing a 24.05, you install an older version, like 20.05 and do the update.
The specific lines in updatedatabase.pl probably are the same, but a lot of underlying plumbing could be different and work badly with your system. I'd start by ruling that out.
1. On an different host with Koha 23.11 I loaded the database which then showed the following error:
ERROR 1452 (23000) at line 8292833: Cannot add or update a child row: a foreign key constraint fails (`koha_test`.`#sql-alter-368-2a`, CONSTRAINT `borrower_message_transport_preferences_ibfk_1` FOREIGN KEY (`borrower_message_preference_id`) REFERENCES `borrower_message_preferences` (`borrower_)
2. I didn't care for it and then tried to update the database. This time the update process went far far further but it still aborted here:
Upgrade to 23.06.00.013 [17:56:51]: Bug 28966 - Holds queue view too slow to load for large numbers of holds ERROR - {UNKNOWN}: DBI Exception: DBD::mysql::db do failed: Can't DROP CONSTRAINT `tmp_holdsqueue_ibfk_1`; check that it exists at /usr/share/koha/lib/C4/Installer.pm line 741
This seems to be a different problem and I'm hoping the solution could lead to the road of success... Do you happen to know how to solve this?
3. Furthermore I saw the database dump file I received for the update ends like this:
-- -- Constraints der Tabelle `virtualshelves` -- ALTER TABLE `virtualshelves` ADD CONSTRAINT `virtualshelves_ibfk_1` FOREIGN KEY (`owner`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE SET NULL ON UPDATE SET NULL; COMMIT;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */; /*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */; /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
while the database dump files I usually create myself do not contain constraints at all and end like this:
-- Dump completed on 2024-07-10 6:25:22
Does this make sense to you?
Best wishes: Michael
Hi Philippe You wrote:
If you went with an existing 23.11 and tried to overwrite the database with you 19.05 one, it's possible it got conflicts since it drop-and- add in the order in the file and dependencies might break. Always easier to just drop the old database and load the "new" one in an empty space.
Thanks for this hint!
Then you went from 19 to 23.06.00.013, which is much further, but then failed on something unrelated to any previous error. (beside being a constraint, like the primary key earlier). Cannot dropping something that doesn't exist is NOT a deal breaker, so try
comment installer/data/mysql/db_revs/230600014.pl line 11, and try again.
After all we found out the database dump I was provided with was corrupt or unusual in at least two ways: 1) it didn't contain the final line "Dump completed" and so really was incomplete, but 2) it did include lines like the following (and the corresponding SQL commands) that are usually not included in the database dump: -- Constraints der exportierten Tabellen -- Constraints der Tabelle `accountlines` -- Constraints der Tabelle `account_credit_types_branches` etc. etc. Provided with an incorrupt dump I was able to load it into the MariaDB server and the update from Koha 19.11 to 24.05 worked like a charm. No errors at all. Best wishes and many thanks for your help: Michael -- Geschäftsführer · Diplombibliothekar BBS, Informatiker eidg. Fachausweis Admin Kuhn GmbH · Pappelstrasse 20 · 4123 Allschwil · Schweiz T 0041 (0)61 261 55 61 · E mik@adminkuhn.ch · W www.adminkuhn.ch
On 2024-07-10 12:30, Michael Kuhn wrote:
Hi Philippe
You wrote:
If you're a bit adventurous (and have some time, which you're probably thinking you've burnt enough already): try to update to 20.05, for example, using 20.05 code.
So you reset your installation (you have saved your DB, right?), then instead of installing a 24.05, you install an older version, like 20.05 and do the update.
The specific lines in updatedatabase.pl probably are the same, but a lot of underlying plumbing could be different and work badly with your system. I'd start by ruling that out.
1. On an different host with Koha 23.11 I loaded the database which then showed the following error:
ERROR 1452 (23000) at line 8292833: Cannot add or update a child row: a foreign key constraint fails (`koha_test`.`#sql-alter-368-2a`, CONSTRAINT `borrower_message_transport_preferences_ibfk_1` FOREIGN KEY (`borrower_message_preference_id`) REFERENCES `borrower_message_preferences` (`borrower_)
2. I didn't care for it and then tried to update the database. This time the update process went far far further but it still aborted here:
Upgrade to 23.06.00.013 [17:56:51]: Bug 28966 - Holds queue view too slow to load for large numbers of holds ERROR - {UNKNOWN}: DBI Exception: DBD::mysql::db do failed: Can't DROP CONSTRAINT `tmp_holdsqueue_ibfk_1`; check that it exists at /usr/share/koha/lib/C4/Installer.pm line 741
This seems to be a different problem and I'm hoping the solution could lead to the road of success... Do you happen to know how to solve this?
3. Furthermore I saw the database dump file I received for the update ends like this:
-- -- Constraints der Tabelle `virtualshelves` -- ALTER TABLE `virtualshelves` ADD CONSTRAINT `virtualshelves_ibfk_1` FOREIGN KEY (`owner`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE SET NULL ON UPDATE SET NULL; COMMIT;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */; /*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */; /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
while the database dump files I usually create myself do not contain constraints at all and end like this:
-- Dump completed on 2024-07-10 6:25:22
Does this make sense to you?
Best wishes: Michael
The problem is deeper, then. issuingrule didn't have unique id, so the problem is not with the starting db. It seems to be when creating the new entries, autoincrement doesn't seem to do its job ? I don't think there's any integrity problem with your old database, more with the environment, the actual database server configuration. But I'm just spitballing at this point... Logo inLibro <https://inLibro.com> Philippe Blouin Directeur de la technologie T 833-INLIBRO (465-4276) <tel:833-465-4276>, poste 230 C philippe.blouin@inLibro.com www.inLibro.com <https://inLibro.com> On 2024-07-10 09:20, Michael Kuhn wrote:
Hi Philippe
Many thanks! You wrote:
Ok, let's cheat. You have very few rules, and most of the info has been moved already. So just comment the lines 20734 to 20746 and run updatedatabase.pl again.
OR modify the version to move ahead.
update systempreferences set value = '19.1200018' where variable = 'version';
Then you can drop the issuingrules table later, when you're satisfied with everything. Note that you'll need to update the rules manually in the staff interface to add back the issue length and renewal count/period...
I have modified the version and then moved ahead... unfortunately there is another duplicate entry in another table and the script aborted again:
Upgrade to 19.12.00.029 [15:13:32]: Bug 17702 - Add column account_credit_types.archived {UNKNOWN}: DBI Exception: DBD::mysql::db do failed: Duplicate entry '0' for key 'PRIMARY' at /usr/share/perl5/DBIx/Class/Schema.pm line 1118. DBIx::Class::Schema::throw_exception(Koha::Schema=HASH(0x5598ef055ba8), "DBI Exception: DBD::mysql::db do failed: Duplicate entry '0' "...) called at /usr/share/perl5/DBIx/Class/Storage.pm line 113 DBIx::Class::Storage::throw_exception(DBIx::Class::Storage::DBI::mysql=HASH(0x5598f4639198), "DBI Exception: DBD::mysql::db do failed: Duplicate entry '0' "...) called at /usr/share/perl5/DBIx/Class/Storage/DBI.pm line 1623 DBIx::Class::Storage::DBI::__ANON__("DBD::mysql::db do failed: Duplicate entry '0' for key 'PRIMARY'", DBI::db=HASH(0x5598f4e19490), undef) called at /usr/share/koha/intranet/cgi-bin/installer/data/mysql/updatedatabase.pl line 20966
How should I proceed?
Is there a way how I can check the original database for duplicate entries so the update process won't abort?
Best wishes: Michael
participants (2)
-
Michael Kuhn -
Philippe Blouin