Hi Guntis,

So to confirm, are you trying to run "epadmin upgrade" against a database running on MySQL 4.1.22 or is the database now running on a more up to date version of MySQL or MariaDB?  If not I would export and reimport to a more recent version of MySQL before trying the upgrade, as I suspect that version of MySQL may not support the upgrade operations that EPrints 3.3.16 needs to perform.

The "Upgrading rindex and grep tables for subject" is still down to a foreign key constraint which I am surprised to see once you have set the flag as I suggested.  One thing you could do to get a better idea of what is going wrong in to temporarily turn on MySQL debug that will log all the queries being run.  This can be done by editing line 112 of perl_lib/EPrints/Database.pm to:

my $DEBUG_SQL = 1;

Where previous the value would have been set to 0.  This will create a lot of noise when you run "epadmin upgrade" but it should allow you to spot the specific query that has an issue (i.e. the last one before the script fails).  It may be worth trying to run the failed last SQL query again from the MySQL command to get out a specific error message, as it looks like this gets masked by the epadmin script.

If you are planning on upgrading to EPrints 3.4.2.  I would recommend getting a completely default install of this working first, then add your old archive into the archives directory and follow the instructions for upgrading to 3.4.x at:

https://wiki.eprints.org/w/Upgrading_3.3.12%2B_to_3.4

and possible also upgrade instructions for 3.1 to 3.2 and 3.2 to 3.3 at:

https://wiki.eprints.org/w/Upgrading_from_3.1
https://wiki.eprints.org/w/Upgrading_from_3.2

I would advise getting your archive's configuration working so you can create a new empty EPrints database rather than immediately trying to get it working with your old database and upgrading it.  By creating a new database and checking the your repository works OK in a web browsers you can be failry confident this part is correct.  Then you can import your old database update the database.pl configuration to point at it an drun "epadmin upgrade" knowing that if there are issues it is due to the database rather than your EPrints configuration.

Regards

David Newman

On 17/01/2021 15:38, Guntis Zelvis via Eprints-tech wrote:

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="">

Virus-free. www.avg.com

*** Options: http://mailman.ecs.soton.ac.uk/mailman/listinfo/eprints-tech
*** Archive: http://www.eprints.org/tech.php/
*** EPrints community wiki: http://wiki.eprints.org/

*** Options: http://mailman.ecs.soton.ac.uk/mailman/listinfo/eprints-tech
*** Archive: http://www.eprints.org/tech.php/
*** EPrints community wiki: http://wiki.eprints.org/