It was the May Day bank holiday on Monday this week.  I then spent quite a bit of Tuesday and Wednesday completing several mandatory training courses that all University staff must take every few years.  I had six to complete before the end of July so decided to get them all out of the way this week, each taking between 30 and 90 minutes to finish.

I spent most of the remainder of the week continuing to update the BARS review site for Matt Sangster.  I’d made a bit of progress with this last week (upgrading from 2.4.3 to 2.4.8.5) but this week I needed to perform a major upgrade from 2.4.8.5 to 3.2.1 and then a further upgrade to the current version.  Upon starting the process I quickly realised that version 3.2.1 is still not compatible with PHP 8 so I needed to downgrade PHP on my laptop to PHP7 to get things to work.  It was also not documented that the config script’s database connection needed to be changed from ‘mysql’ to ‘mysqli’, and this took a while to figure out.

I managed to get the upgrade to start executing but the process was failing midway through with a very unhelpful ‘Specified key was too long; max key length is 1000 bytes’ error.  This is a MySQL error but it was in no way clear exactly when and why the error was cropping up.  I spent ages looking through numerous source code files to try and understand what was going on and then tried to modify things.  But each time the error appeared I needed to delete and reinstall the database as it was then left in a half-upgraded state, which was a bit of a pain.  I found this posting https://forum.pkp.sfu.ca/t/install-ojs-3-0-2-on-windows-with-php-7-1-8/32901/7 but it suggested upgrading MySQL would fix the issue and I was already running the most recent version and other information I found (such as https://stackoverflow.com/questions/1814532/mysql-error-1071-specified-key-was-too-long-max-key-length-is-767-bytes) didn’t really help much either.

I noticed that the existing tables were mixture of MyISAM and InnoDB storage engine tables so then decided to set them all to InnoDB (each needed to be done separately and there are almost 150 tables) but this still didn’t sort things.  The main difficulty was that I didn’t know exactly which query the upgrade script was sending to the database was causing the error and looking at the code I couldn’t figure out how to add some kind of log to trace things.  Thankfully one of the answers to this question https://stackoverflow.com/questions/4631133/mysql-log-of-invalid-queries noted that you can tell MySQL to log all queries that are passed to it and after doing so I could pinpoint exactly what in the upgrade script was causing the error.

The upgrade script created a number of new tables and one query was ‘ALTER TABLE email_templates_settings ADD  UNIQUE INDEX email_settings_pkey  (email_id, locale, setting_name)’ and it was this that caused the error and made the process fail.  The problem was this appeared to be a perfectly valid query.  Thankfully I then noticed that while existing tables were using the collation ‘utf8mb3_general_ci’ the tables created by the upgrade script were set to ‘utf8mb4_0900_ai_ci’ as this is the default collation now used by MySQL.  Unfortunately this collation uses more bytes than the other one and was pushing the key length over the limit.  After changing the database’s default collation and running things again the upgrade process finally completed successfully.

However, I was still unable to get the HTML and PDF versions of the articles to appear on my local PC.  When I click on the links I just get a blank page, even though I’ve updated the path to files in the config file.  This will require some further investigation.

I was then able to perform the final upgrade from 3.2.1 to 3.4.  I was hoping this would be straightforward but unfortunately I still ran into a few problems.  The upgrade failed with the error ‘Failed to open the referenced table ‘files’ (SQL: alter table submission_files add constraint submission_files_file_id_foreign foreign key (file_id) references files (file_id))’ and there was a single Google hit referring to this (https://forum.pkp.sfu.ca/t/upgrade-ojs-3-2-1-4-to-3-3-0-5-error/67785) but unfortunately the suggested solution (updating the database user privileges) did not work for me as my database user already had full privileges.  Thankfully I spotted that during the previous upgrade some new tables had been created and these were using the ‘Myisam’ storage engine rather than ‘innodb’.  I managed to update MySQL on my PC to ensure innodb was the default engine (see https://stackoverflow.com/questions/4199446/how-to-make-innodb-as-default-engine) and this fixed the issue.  I then encountered some further errors relating to log files that needed to be deleted, but after doing so the upgrade process completed successfully.  Unfortunately the HTML and PDF articles are still not loading, and I was hoping that this was an issue with running the software on my Windows PC.  However, I then uploaded to newly upgraded site to a test server and the issue persisted.  I’ll need to investigate this further next week – I just hope I don’t need to begin the upgrade process all over again to resolve it.

Also this week I made a number of further changes to the Speech Star website, including adding several new video clips and updating video metadata.  There are still a few additional tasks I need to perform for this project and I’ll start to tackle them next week.