Upgrade from 8.5 to 9.8 error

[Marco Afonso]

Hello again,

This is my second post because first post did not got published on the group.

I'm having a problem when trying to upgrade from version 8.5 to 9.8.

Later on I will upgrade from 9.8 to latest version.

When I run the upgrade script it returns an error saying that a database column does not exists and it stops. Please see message below:

$ sudo php upgrade/upgrade.php
The system has been upgraded. Please wait whilst the necessary upgrade tasks are completed.
Performing upgrade tasks for system version: 5
Running 1 out of 14 (7.14%) 005_migrate_search_filters.php
Upgrade script failed.
Unknown column 'search_filter_id' in 'where clause'

I kindly ask for help, what should I do?

Kind regards,

Marco

[Adrian Giannini]

This is similar to a problem I was having before, which I described and got help for in a thread here: https://groups.google.com/g/resourcespace/c/uAyAzgV7W1U/m/UmQjCiDfAwAJ

I would add $show_detailed_errors = true; to your config.php and then run the upgrade through the web browser, as this will give you a more detailed error message than what you have now. Then you can use phpmyadmin or the mysql command line to add the missing column to the table that it will specify, and hopefully someone else here will chime in with a better command to add the missing column to the table.

Thanks,

Adrian

[Marco Afonso]

Hello all,

Thank you so much for the hints Adrian.

I will try manually and let people know how it goes.

Kind regards,

Marco

[Marco Afonso]

Hello Adrian,

Thank you for your help, I was able to manually create the missing columns for the migration.

I'm trying to have 3 versions working because this is an ongoing migration process:

 - Version 8.5 - its ok migrated from another server with the same version.

 - Version 9.8 - parcially ok, its missing preview images in some of the collections.

 - Version 10.3 - error on dashboard: all collection items saying "no resources found".

All instances are pointing to the same filestore in the filesystem.

Any ideias about what I have to do, so in version 10.3 it founds the filestore files in the filesystem?

In version 10.3 folder I did run in the CLI "php upgrade/upgrade.php" and everything ended fine without errors.

Thank you again!

Marco

[Dan Huby]

Hi Marco,

The error (missing column) is odd because ResourceSpace is designed to automatically add missing columns via the files in /dbstruct.

Are you using Subversion for upgrading (recommended) or are you  manually copying files over the top? If the latter is it possible you've missed some (e.g. the dbstruct files)?

At Montala we upgrade customers running older versions often, when they sign up for our support service. We haven't come across this type of issue but then we always use Subversion.

Dan

[Marco Afonso]

Hello Dan,

Thank you for your reply.

I use git and did checkout the 10.3 branch with:

git svn clone https://svn.resourcespace.com/svn/rs/releases/10.3 media

I saw that the dbstruct folder has the missing columns in version 9:

https://svn.resourcespace.com/svn/rs/releases/9.0/dbstruct/table_usergroup.txt

But running "php upgrade/upgrade.php" didn't add these columns but instead expected them to exist (which they didn't).

Maybe I'm missing something...

Cheers,

Marco

[Dan Huby]

When the error occurs ResourceSpace should catch that and create the missing column, which as you rightly say, is there in dbstruct for 9.0.

It looks like ResourceSpace is possibly unable to catch the DB error. It could be down to your PHP configuration if non-standard, but I'm not sure - I haven't seen this issue before.

[Dan Huby]

By the way you should not need to manually run the upgrade script; it should run via your browser when you access ResourceSpace.

[Marco Afonso]

Thank you Dan, very helpful your comments.

Ok I started all over:

1. cloned branch 9.8 to new directory media9

2. imported database from 8.5 to new database for this version 9

3. set the configuration in the includes/config.php

4. opened in the url: http://server/media9/upgrade/upgrade.php

Got error 500 from the apache web server. Logs:

[Tue Apr 30 14:12:29.131568 2024] [php:error] PHP Fatal error:  Uncaught mysqli_sql_exception: Unknown column 'title' in 'field list' in /var/www/html/media9/include/database_functions.php:482\nStack trace:\n#0 /var/www/html/media9/include/database_functions.php(482): mysqli->prepare('...')\n#1 /var/www/html/media9/include/db.php(249): ps_query('...', Array)\n#2 /var/www/html/media9/upgrade/upgrade.php(13): include_once('...')\n#3 {main}\n  thrown in /var/www/html/media9/include/database_functions.php on line 482

So indeed the missing table column error was reported but not caught by RS, and the missing column was not created.

My php 8.1 has the following config:

display_errors = Off

error_reporting = E_ALL & ~E_DEPRECATED & ~E_STRICT

(And the other parameters described in here https://www.resourcespace.com/knowledge-base/systemadmin/install_php_ini )

What would be the PHP config so RS will continue to create the missing column? Thank you!

Marco

[Dan Huby]

The last step is wrong - but I'm not sure if that's the cause of the issue. You don't need to access upgrade.php - just log in.

On Wednesday 24 April 2024 at 13:49:31 UTC+1 Marco Afonso wrote:

[Dan Huby]

Here are the upgrade instructions:

https://www.resourcespace.com/knowledge-base/systemadmin/upgrading

[Marco Afonso]

Hi Dan,

Either way, accessing the root URL, stops at the same error.

Documentation about the upgrade may help in some cases but not in this case.

It looks like there must be some configuration in PHP to go through the auto-create database columns...

I will play around to see what I can do.

Kind regards,

Marco

[Marco Afonso]

Hello again,

I was able to finally migrate to the new server.

I think it is important to add the following notes to the documention, in the upgrade page:
https://www.resourcespace.com/knowledge-base/systemadmin/upgrading

Before opening the ResourceSpace in the browser:
1. Starting from PHP 8.1 and using MySql, default MySql reporting is not MYSQLI_REPORT_OFF, as this will block the database auto-updates implemented by RS. Manually add into include/config.php: mysqli_report(MYSQLI_REPORT_OFF);
2. PHP error reporting should also be turned off. Manually add into include/config.php: $config_error_reporting=0; Reporting erros may also crash the update script.
3. File store before version 9 (correct me if I'm wrong on this), uses a different folder structure. To access the files created in the previous version, manually set into include/config.php: $filestore_evenspread=false;
4. After upgrading, in new version, if in the home screen some tiles return "Resource not found", it means the collection is set as private. Use an user with permissions to set the collection as public as needed.

I hope this can help other people that will migrate from older versions.