OJS install on Docker - stable_3.3.0

[zenuno]

Hi nice PKP software support,

We have been trying to upgrade an older 2.4.8 OJS installation and it has been hard.

What I managed to do until now is:

• our present production version is 2.4.8.0 with PostgresSQL

• for all our tests and upgrades we always used OJS on Docker.

• for that we build new Dockerfiles that used MariaDB and PostgresSQL. Also we build ARM64 and AMD64 that end up running on a MacBook Pro M1 and Sinology docker (intel).

• we can obviously share our Docker work with you if you feel it adds something.

• as the upgrades inside OJS seem to have failed, so we tried a different approach.

• we also tried the FullJournal export/import plugin and it also failed. We tried it to move from PostgresSQL to MariaDB. It didn’t work unfortunately.

• So on our more recent plan for the migration we did the following:

• we exported the most relevant users and also all the volumes (those that were exporting correctly) we did that with the normal plugins on OJS (all minus one volume exported ok).

• we installed a new version from scratch on MariaDB already - 2.4.8.0

• we imported there the users and volumes of our journal.

• then we upgraded the OJS successfully - 2.4.8.0 >> 2.4.8.5

• and again: 2.4.8-5 >> 3.1.2-4

• we indeed checked the utf8 DB conversion but we seemed we couldn’t upgrade anymore - the error was “ERROR: Upgrade failed: DB: Incorrect string value: ‘\xC4\x9F’ for column ojs.publication_settings.setting_value at row 6”

• As we got to this new apparent dead end we opted for exporting the volumes and users on this version (3.1.2-4)

• so we are now are planning on installing stable-3.3.0 from scratch and try to import the volumes and users we exported from version 3.1.2-4. we hope it’s possible to do that.

• but even before importing we are stuck on trying to install docker OJS stable-3.3.0 without any modification and the install seems to fail on several versions of 3.3.x we already tried…

• the error now is:
  “Errors occurred during installation
  A database error has occurred: SQLSTATE[42S01]: Base table or view already exists: 1050 Table ‘announcement_types’ already exists (SQL: create table announcement_types (type_id bigint not null auto_increment primary key, assoc_type smallint not null, assoc_id bigint not null) default character set utf8 collate ‘utf8_general_ci’)”

I should add that we deleted the all database every time we try the install and we also already tried with various ojs.config.inc.php including our usual one and also a copy from config.TEMPLATE.inc.php (ver. Stable-3.3.0.
The error persists.

We can explain in more detail our configurations if needed obviously but we are trying to summarise our already long path to solve this migration.

I sure hope it’s a ridiculous oversight on my part

I thank you already for giving attention to this matter and look up for any ideas to solve this migration with success.

Thanks
Zé Nuno
(OberCom, Portugal)

extra note on 2022.04.27 18:07h GMT:
I did another experiment and that was a success - I tried the already build docker of stable_3.3.1 on intel and it installed as it should.
My problem is that the intel version is broken when running emulated on the Mac M1 and on GitHub I couldn’t find that specific more recent version. I tried to use an edited version of the stable_3.3.0 but it didn’t work unfortunately.
I will try to use the intel version but I thank you a lot if you could update or publish the stable_3.3.1 version for me to compile to ARM64/Apple Silicon.
Thanks again.

[marc]

Hi @zenuno

Let’s focus on your first error:

ERROR: Upgrade failed: DB: Incorrect string value: ‘\xC4\x9F’ for column `ojs`.`publication_settings`.`setting_value` at row 6”

Usually this error is related with charset conversion issues.

In my case this is usually solved:

1. Make a mysqldump and ensure you export in utf8.
2. Check the file to be sure is utf8.
3. Force your mysql to be utf8mb4_*
4. Load your dumped file.
5. Force your ojs to work with utf8.

About 3, this is how I set my msyql server:

[mysqld]
character-set-server = utf8mb4
collation-server = utf8mb4_general_ci
skip-character-set-client-handshake

About 5, this is how I set my ojs config:

client_charset = utf-8
conection_charset = utf8
database_charset =  utf8mb4_general_ci
charset_normalization = utf-8

(be carefully with the utf8 and utf-8 notation)

BTW, did you know that we are cooking official docker images for OJS?
May be you want to give them a try and send us feedback.

GitHub - pkp/docker-ojs: Open Journal Systems (OJS) is a journal management...

Cheers,
m.

[zenuno]

Hi!!
I am going to check my Mariadb settings but I think I did what you are saying and I will re check and get back to you on that!

And I can confirm that I am indeed using the official docker images. but as I needed the Postgres originally and also to redo them on M1 CPU I am using the GitHub base to build them. Unfortunately it’s not totally updated on GitHub as it is on hub docker. I can’t find the stable-3.3.1 there. Can you help me on that?
And I can also share the differences I made on the docket files to add Postgres’s support and also tar because of the tests with the FullJournal I also did.

I am going to go back and retry the upgrade that failed and will reply when I did it.

Thanks a lot for you attention and specific help.

[zenuno]

Hello again!
After some more debugging I indeed found what was wrong!
we had this line on our docker-compose.yml (MariaDB):
- ./volumes/migration:/docker-entrypoint-initdb.d
and we had used the migration folder to dump one version of the database before being converted to UTF8mbr and as such we were stuck there every time we restarted the Docker…

We are restarting from scratch a 2.4.8-0 that is the same version we have in production with PostgresSQL and we will use now MariaDB and import everything there and we will do the various upgrades to see if we get to 3.3 safely
(2.x > 2.4.8-5 > 3.1.2-4 > 3.2.1-4 > 3.3.x-x)

We will follow up as we do that. Thanks a lot for your guidance. we also corrected a very small detail on the ojs about utf8 too as it wasn’t complete as your sent notes.

Cheers
ZN

[zenuno]

Hello again,

So I changed the configuration (on Docker) to use UTF8 as noted before and I installed from scratch the OJS version 2.4.8-0 and all went well as expected.

• After importing users and issues from our production version in PostgresSQL;
• Successfully upgraded to version 2.4.8.5
• Successfully upgraded to version 3.1.2.4 (had to change de database drive from mysql to mysqli)

After that when we tried to upgrade to either stable-3.2.1 or 3.2.1-4 but it gave out an error:

[data: dbscripts/xml/upgrade/3.2.0_url_path.xml]
ERROR: Upgrade failed: DB: Data too long for column ‘url_path’ at row 4

I have already searched for this error and discovered the issue that uses more than 64 caracters and will be solving it and try to continue.

I should add the reference we used to solve this:

ERROR: Upgrade failed: DB: Data too long for column 'url_path' at row 2

Hi Dung, So, the url_path field in the various tables that have it is a maximum of 64 characters long. You’ll need to look in publication_settings, issue_settings, issue_galley_settings, and publication_galley_settings and find values where setting_name is pub-id::publisher-id and the length of the setting_value field for that record is longer than 64 characters. If you find any, you’ll probably have to adjust those by hand and shorten them. Best, Jason

and

[OMP] Error upgrading from version 3.1.2-4 to 3.2.0

and with these SELECT:

SELECT submission_id FROM submission_settings WHERE setting_name=“pub-id::publisher-id” AND LENGTH(setting_value) > 64;
SELECT galley_id FROM submission_galley_settings WHERE setting_name=“pub-id::publisher-id” AND LENGTH(setting_value) > 64;
SELECT issue_id FROM issue_settings WHERE setting_name=“pub-id::publisher-id” AND LENGTH(setting_value) > 64;
SELECT galley_id FROM issue_galley_settings WHERE setting_name=“pub-id::publisher-id” AND LENGTH(setting_value) > 64;

So all solved!! we managed to upgrade to the last version now!!

• Successfully upgraded to version 3.2.1.4
• Successfully upgraded to version 3.3.0.10

Thanks again.

Cheers
ZN

[zenuno]

Hi again,

I don’t know if it’s a good idea to follow up here or if I should open a new entry on the forum.

I have configured as I reported earlier but now I am having a new problem in that the OJS system is not sending e-mails. We have been using one own domain gmail account as a way of sending e-mail (authenticated SMTP with gmail server) but on this docker config and version (3.3.0.10) it’s not working as expected.

Could you help me in finding out what’s wrong on this config.

The error log with some details omitted:
sendmail: can’t connect to remote host (127.0.0.1): Connection refused
[Fri May 06 18:25:50.672856 2022] [php7:notice] [pid 20] [client 192.168.Y.Y:YYYYYY] Could not instantiate mail function., referer: https://…/obs/management/settings/access

And our E-MAIL CONFIG that we are using with some specific details omitted:

vi config/ojs.config.inc.php

; 2022.05.06 | email activated on our docker config
smtp = On
smtp_server = smtp.gmail.com
;smtp_port = 465
;smtp_auth = ssl
smtp_port = 587
smtp_auth = tls
smtp_username = “YYYY@YYYY.YY”
smtp_password = “yyyyyyyyyyyyyyy”
;show_stacktrace = Off
show_stacktrace = On

We tried with ssl and tls and the result is the same. we also tried configuring through PHP - on config/php.custom.ini.

Thanks already for any help on finding the problem and get it back to working.
(It worked with a very similar configuration on the version 2.4.8-0).

Regards
ZN

[marc]

Sorry for the late answer. Hopefully you fixed it too.

Please open a new thread for each new problem so we can keep the forum as a collective knowlege base.

Cheers,
m.