Description of issue or problem I’m having:
HTTP 500 error occasionally when logging in to OJS after upgrading from 3.1.1.2 to 3.3.0.7
Steps I took leading up to the issue:
Upgrading to 3.3.0.7, I did have some issues with not only character encoding (database was latin1), but differing database engines across tables. I had to manually set some tables to MyISAM to get the upgrade to complete without foreign key errors. Everything except for this so far seems good post-all these issues though.
What I tried to resolve the issue:
Nothing yet, if you refresh the page it logs in fine.
Application Version - e.g., OJS 3.1.2:
OJS 3.3.0.7
Additional information, such as screenshots and error log messages if applicable:
From the Apache error log when the 500 error happens:
[php7:error] [pid 23983:tid 139654652159744] [client 128.226.57.168:54311] PHP Fatal error: Uncaught PDOException: SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry ‘1’ for key ‘user_settings_user_id’ in /var/www/html/lib/pkp/lib/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/PDOStatement.php:115
It looks the key user_settings_user_id in the user_settings table may have gotten set up as a unique key, which shouldn’t be the case in the settings tables. Could you run SHOW CREATE TABLE user_settings in your database and share the output for that?
Thanks for sharing the output. It doesn’t look like this is what’s causing the issue.
Another possibility is that the users table contains a user_id that appears more than once. From your error message above it looks like the user_id was 1, but it’s probably worthwhile to see if there are any other duplicate user_ids in the users table.
@ewhanson Doesn’t look like there’s any duplicates, if my queries are correct:
mysql> select * from users group by user_id having count(*) > 1;
Empty set (0.00 sec)
mysql> select user_id from users; 983 rows in set (0.00 sec)
mysql> select distinct user_id from users; 983 rows in set (0.01 sec)
Just scanning the Apache log, I would make an educated guess that everyone gets this the first time they try to log in for the day. Do you think there’s any chance it has anything to do with differing database engines? I did struggle with that during the upgrade. I kept having to reload the database to pre-upgrade, and re-upgrade it until I hit all the tables that had an issue being different (MyISAM vs InnoDB).
Thanks for sharing that. It is strange that it seems to be happening to others but only once in a while. This kind of reaches my limit on database knowledge, but others on here will probably know more.
I checked with a colleague who offered the following suggestions for further narrowing down the issue:
I don’t know if this is feasible at all, but they could try duplicating that installation on a private server, turning on the debug setting in the config file and then getting OJS queries dumped at them. They’d get the last query right before it all failed, perhaps.
MySQL also has a global general_log variable that you can set to On, which will log everything. It’d be incredibly verbose but it may turn something up too.
Another out there place to look in case the rest doesn’t turn up anything:
If it’s only happening once to everyone, I wonder if it’s a database update on the users table to update the “last login” field or maybe the sessions table, which also has a user_id column. I know it was complaining about the user_settings table, which sounds like the queries are failing when trying to build the user object itself.
So I have the OJS instance cloned… but so far am not able to recreate the issue on the test server. That’s telling in itself as it was easy to recreate on the main server. Both are running the same Linux distro and version. The MySQL version differs slightly but is close and both are within requirements, PHP is the same version. Any other ideas to compare in the server environment that might cause this?
Main server
PHP 7.4.24 (cli) (built: Sep 21 2021 11:23:11) ( NTS )
mysql Ver 14.14 Distrib 5.5.0-m2
Test server
PHP 7.4.24 (cli) (built: Sep 21 2021 11:23:11) ( NTS )
mysql Ver 14.14 Distrib 5.6.30
I am curious about how the database was cloned on the test server. This is just a guess, but if it was dumped and then re-imported, I wonder if any issues with keys was sorted during the import.
Can you perhaps try to set up a test installation on the same server as your live installation, using the same method? I’d be interested in knowing if the problem goes away when the database is imported again, with the mysql version remaining the same,
To clone it to the test server on production I ran
mysqldump ojs > ojs.sql
and then on test, created an empty MySQL database with create database ojs, gave my user full access to it and then at command line ran
mysql -p ojs < ojs.sql
I copied the folder structure from production to test, and only had to change the database name and password in config.inc.php. It all seemed to go pretty smoothly. I can try another instance of it on the same server and see what happens.
I 100% had issues during the upgrade on production that started this, mostly all database related, so I’d bet this is fallout from that. However, those did not seem like uncommon problems, and as far as I can tell I handled in the same way most did in the research from this forum. It was related to character encoding and also the database engines being different across tables, which apparently caused a known issue with foreign keys in other tables.
Thanks for playing ball here, give me some time to clone it locally.
I will mention one other thing, based on this command, and that’s that this command leaves a few decisions up to the MySQL server, like the character set of the dump file. It’s possible that if the database was using latin1 or utf8 tables, and the server character set was something else, the mysqldump file could convert content without you realizing it. We actually get this a lot at PKPPS when new clients migrate to our servers and they provide us with a database dump. We usually ask them to be explicit and use:
based on what they are using. We then do the conversion to UTF8 ourselves.
Just mentioning this because key errors can often crop up (or be fixed!) after a database has been dumped and transcoded. Strings that used to match in one character set might not any more.
Might be worth looking at the two server environments and verify the default character sets and the database encodings are all the same everywhere.
Well the clone on the local server resolved the issue, but left me with some unknowns unfortunately. I have a feeling @jnugent is on to it with not being precise in the encoding/collation, either that or the differing database engines. But simply creating a new blank database, dumping and then importing the file in to it seemed to clear up the issue.
So I don’t get the warm fuzzies but at least the end-users are all set.
Thank you both for the suggestions and assisting in troubleshooting the issue.