EPrints Technical Mailing List Archive

Message: #08451


< Previous (by date) | Next (by date) > | < Previous (in thread) | Next (in thread) > | Messages - Most Recent First | Threads - Most Recent First

Re: [EP-tech] EPrints v3.1.2 migration to the latest version


CAUTION: This e-mail originated outside the University of Southampton.
David, thanks for advice. This removed most errors related to foreign key constraints. Just one remained:

Upgrading rindex and grep tables for subject
DBD::mysql::db do failed: Error on rename of './scireldiaprints/#sql-10710_48' to './scireldiaprints/subject__rindex' (errno: 150) at /usr/share/eprints/bin/../perl_lib/EPrints/Database.pm line 3052, <STDIN> line 1.
SQL ERROR (do): ALTER IGNORE TABLE subject__rindex MODIFY field VARCHAR(64) CHARACTER SET UTF8, MODIFY word VARCHAR(128) CHARACTER SET UTF8, DEFAULT CHARACTER SET UTF8, DROP PRIMARY KEY, ADD PRIMARY KEY(field,word,`subjectid`), DROP KEY `subject__rindex_subjectid_2`
SQL ERROR (do): Error on rename of './scireldiaprints/#sql-10710_48' to './scireldiaprints/subject__rindex' (errno: 150) (#1025)

However, the main problem that stops upgrade script remains:

Upgrading dataset metafield

------------------------------------------------------------------
---------------- EPrints System Error ----------------------------
------------------------------------------------------------------
An unexpected error occurred while attempting to convert tables to UTF-8.

------------------------------------------------------------------
EPrints System Error inducing stack dump
 at /usr/share/eprints/bin/../perl_lib/EPrints.pm line 147, <STDIN> line 1.
        EPrints::abort() called at /usr/share/eprints/bin/epadmin line 2753
        main::upgrade_mysql_charset('EPrints::Repository=HASH(0x13dec90)', 'EPrints::Database::mysql=HASH(0x408a3b8)') called at /usr/share/eprints/bin/epadmin line 3008
        main::upgrade_3_1_2_to_3_2_0('EPrints::Repository=HASH(0x13dec90)', 'EPrints::Database::mysql=HASH(0x408a3b8)') called at /usr/share/eprints/bin/epadmin line 2887
        main::upgrade('scireldiaprints') called at /usr/share/eprints/bin/epadmin line 320

And, yes. This is very old version of ePrints (3.1.3) as well as underlaying MySQL version (4.1.22). Now I am researching upgrade path to ePrints 3.4.2 on MariaDB 5.5.68.

Guntis


On Sat, Jan 16, 2021 at 8:37 PM David R Newman <drn@ecs.soton.ac.uk> wrote:

Hi Guntis,

This looks to be down to foreign key constraints.  As you are trying to upgrade from a version of EPrints that is over 12 years ago, I cannot know whether this was a known issue at the time or just some quirk of running an very old EPrints database schema in what may be a rather more recent version of MySQL. 

Assuming that you have suitables backups of the database so you can roll back if things go wrong, I would recommend temporarily adding the following line to perl_lib/EPrints/Database/mysql.pm (for EPrints 3.3.16) after line 243:

		$self->do('SET foreign_key_checks = 0;');
This should allow you to run the upgrade script without getting any errors like you previously reported.  However, once you have hopefully run epadmin upgrade successfully you should immediately remove this line from mysql.pm.  This temporary change should allow the upgrade script to tinker with the database schema without having to worry about foreign keys constraints being temporarily broken.  Hopefully, by the time it finishes the database schema will be fully upgraded but there will not be any foreign key constraint issues still present.

I am not sure whether the issues you had from you first run of "epadmin upgrade" will have left the database in an inconsistent state, so it may be worth restoring the database from a backup be re-running "epadmin upgrade" with the change I have suggested to mysql.pm.

Let me know how it goes.  I may be able to help if you still have problems but I have really much worked with EPrints prior to 3.2.  There may be some veterans on the list that may know more about known issues upgrading from 3.1.x to 3.3.

Regards

David Newman

On 16/01/2021 18:01, Guntis Zelvis via Eprints-tech wrote:
CAUTION: This e-mail originated outside the University of Southampton.

Hello! I am trying to migrate EPrints v3.1.2 to the latest version. As
I understand it is impossible to migrate straight to v3.4.2, so first
I try to install v3.3.16 and upgrade repository to this version, but
there is few errors in output of 'epadmin upgrade REPOID'

Added dataset upload_progress
DBD::mysql::db do failed: Can't create table 'REPOID.#sql-10710_38'
(errno: 150) at /usr/share/eprints/bin/../perl_lib/EPrints/Database.pm
line 3052, <STDIN> line 1.
SQL ERROR (do): ALTER TABLE `subject_name_sortvalue` ADD CONSTRAINT
`subject_name_sortvalue_fk` FOREIGN KEY(`subjectid`) REFERENCES
`subject`(`subjectid`) ON DELETE CASCADE
SQL ERROR (do): Can't create table 'REPOID.#sql-10710_38' (errno: 150) (#1005)

Later there is:

Upgrading dataset file
Upgrading `file` by insertion
DBD::mysql::db do failed: Cannot delete or update a parent row: a
foreign key constraint fails at
/usr/share/eprints/bin/../perl_lib/EPrints/Database.pm line 3052,
<STDIN> line 1.
SQL ERROR (do): DROP TABLE IF EXISTS `old_file`
SQL ERROR (do): Cannot delete or update a parent row: a foreign key
constraint fails (#1217)

Upgrading dataset upload_progress
Upgrading `upload_progress` by insertion
DBD::mysql::db do failed: Cannot delete or update a parent row: a
foreign key constraint fails at
/usr/share/eprints/bin/../perl_lib/EPrints/Database.pm line 3052,
<STDIN> line 1.
SQL ERROR (do): DROP TABLE IF EXISTS `old_upload_progress`
SQL ERROR (do): Cannot delete or update a parent row: a foreign key
constraint fails (#1217)

Upgrading rindex and grep tables for subject
DBD::mysql::st execute failed: Table 'REPOID.subject__rindex' doesn't
exist at /usr/share/eprints/perl_lib/EPrints/Database/mysql.pm line
423, <STDIN> line 1.
DBD::mysql::st fetch failed: fetch() without execute() at
/usr/share/eprints/perl_lib/EPrints/Database/mysql.pm line 426,
<STDIN> line 1.
DBD::mysql::st execute failed: Table 'REPOID.subject__rindex' doesn't
exist at /usr/share/eprints/bin/epadmin line 2769, <STDIN> line 1.
DBD::mysql::st fetch failed: fetch() without execute() at
/usr/share/eprints/bin/epadmin line 2770, <STDIN> line 1.
DBD::mysql::db do failed: Table 'REPOID.subject__rindex' doesn't exist
at /usr/share/eprints/bin/../perl_lib/EPrints/Database.pm line 3052,
<STDIN> line 1.
SQL ERROR (do): ALTER IGNORE TABLE subject__rindex MODIFY field
VARCHAR(64) CHARACTER SET UTF8, MODIFY word VARCHAR(128) CHARACTER SET
UTF8, DEFAULT CHARACTER SET UTF8, ADD PRIMARY
KEY(field,word,`subjectid`)
SQL ERROR (do): Table 'REPOID.subject__rindex' doesn't exist (#1146)

And everything stops here:
Upgrading dataset metafield

------------------------------------------------------------------
---------------- EPrints System Error ----------------------------
------------------------------------------------------------------
An unexpected error occurred while attempting to convert tables to UTF-8.

------------------------------------------------------------------
EPrints System Error inducing stack dump
 at /usr/share/eprints/bin/../perl_lib/EPrints.pm line 147, <STDIN> line 1.
        EPrints::abort() called at /usr/share/eprints/bin/epadmin line 2753
        main::upgrade_mysql_charset('EPrints::Repository=HASH(0xb9fc80)',
'EPrints::Database::mysql=HASH(0x384b458)') called at
/usr/share/eprints/bin/epadmin line 3008
        main::upgrade_3_1_2_to_3_2_0('EPrints::Repository=HASH(0xb9fc80)',
'EPrints::Database::mysql=HASH(0x384b458)') called at
/usr/share/eprints/bin/epadmin line 2887
        main::upgrade('REPOID') called at
/usr/share/eprints/bin/epadmin line 320

How to fix these errors and complete repository upgrade?

*** Options: http://mailman.ecs.soton.ac.uk/mailman/listinfo/eprints-tech
*** Archive: https://eur03.safelinks.protection.outlook.com/?url="">
*** EPrints community wiki: https://eur03.safelinks.protection.outlook.com/?url="">