Hi PKP community! So I’ve been slowly upgrading our 3.1.2 instances to 3.3.0.7 and have run into a weird problem that I’m hoping someone can shed some light on.
We have a development and production server that are identical with the exception of the MariaDB versions. Dev is running 5.5.X and production is running 10.3.X. When I run an upgrade in development using the most recent production data it always goes smoothly, but if I run the same upgrade on production I run into SO MANY “1265 Data truncated for…” errors, which is super annoying because it’s the production server and results in a lot more downtime than anticipated.
After I track down and delete the offending rows, which always have some sort of forbidden NULL value the upgrade finally manages to complete successfully. The weird bit is that the same offending NULL values on development are somehow handled and replaced with zeroes or whatever data is necessary for the upgrade to complete.
For instance, here’s one error from a broken production upgrade:
Upgrade failed: DB: SQLSTATE[22001]: String data, right truncated: 1406 Data too long for column ‘url_path’ at row 2 (SQL:
UPDATE issue_galleys as ig
SET url_path = (
SELECT igs.setting_value
FROM issue_galley_settings as igs
WHERE igs.galley_id = ig.galley_id
AND igs.setting_name = ‘pub-id::publisher-id’
)
)
But in development the setting_value was successfully truncated to the 64 characters required by the url_path column and no other errors were reported.
I don’t have much control over the database configurations, but is there some sort of setting that I’m missing? I see a lot of the these errors showing up in the forum so maybe this might be helpful for someone.
I did some more digging and I think I know what’s happening, but don’t know why yet. So here’s an example of an upgrade error I got on my most recent upgrade:
ERROR: Upgrade failed: DB: SQLSTATE[23000]: Integrity constraint violation: 1048 Column ‘assoc_type’ cannot be null (SQL: update user_settings set assoc_type = ? where assoc_type = 0)
In this case I assume the upgrade was trying to set a null value, but in the table settings ‘assoc_type’ was set to not allow NULL. My attempts at altering the table column resulted in neither an error or change and it wasn’t until I deleted the index related to assoc_type that the change to allow NULL would hold. After fixing that the instance was able to upgrade successfully.
Any idea why this might be happening? I’m assuming any ALTER TABLE commands in the upgrade scripts are ignored if the db engine is InnoDB. From what I’ve read it sounds like indexes need to be dropped first before tables are altered. This seems to be confirmed by the fact that development upgrades were successful considering that nothing was indexed.
I’m having a hard time making sense of how the db updates are done in OJS so I can’t confirm if indexes are being dropped prior to any table modifications.
The Data too long for column message arises either when there is data that is too long (e.g. 70 characters in a 64-character column), or when attempting to store a NULL value in a NOT NULL column.
url_path column in the in issue_galleys table
The message relating to url_path in issue_galleys is not due to NULL values – the url_path column permits NULLs. So I suspect you have an entry in issue_galley_settings in your 3.1.2 instance that’s too long. Try listing the 5 longest entries in your pre-upgrade database:
SELECT galley_id, setting_value FROM issue_galley_settings WHERE setting_name='pub-id::publisher-id' ORDER BY LENGTH(setting_value) DESC LIMIT 5;
The limit in OJS 3.3.0-x is 64 characters.
assoc_type cannot be null in user_settings
For the error message:
ERROR: Upgrade failed: DB: SQLSTATE[23000]: Integrity constraint violation: 1048 Column ‘assoc_type’ cannot be null (SQL: update user_settings set assoc_type = ? where assoc_type = 0)
What step in the upgrade are you executing when this occurs? (The command-line based upgrade tool should list steps as they execute.)
Regards,
Alec Smecher
Public Knowledge Project Team
Thanks for your help @asmecher! It’s very much appreciated.
I have been trimming my data prior to upgrading to get past that error. As for the second error, I don’t have my console output anymore from when the error occurred, but will post if I run into it on the next upgrade.