Database Error Upgrading from OJS 3.1.01 to 3.2.1-1

Hello,

I’ve run into an issue trying to update my OJS site from 3.1.01 to 3.2.1-1.
After following the Upgrade link from the OJS Installation webtool, the “Upgrade Open Journal Systems” button results in “A database error has occured: Data truncated for column ‘email’ at row 694”, as shown in the image below:

errorcapture

I’ve tried a number of solutions posted in the forums.

I ran ‘php tools/upgrade.php upgrade’ which had similar results:

image

With each failed update I’ve made sure to restore the database immediately after.

For reference, this is my server info:

Apache Version 2.4.46
PHP Version 7.2.33
MySQL Version 10.3.24-MariaDB

Thank you in advance for any help!

Hi @jfaria,

It looks like you have data in your authors table’s email column that’s longer than OJS allows. Did you manually extend this column in the past?

Regards,
Alec Smecher
Public Knowledge Project Team

Thanks for the quick reply!
I’m told the lengths have not been changed, to the best of anyone’s knowledge.

I’ve tried a quick sql query using ‘order by length(email) desc’ to see if there are any large entries and the longest I found is 38 chars.

The column in ‘authors’ takes varchar(90), but I also noticed that the ‘email’ column in the ‘users’ table takes varchar(255). Would that cause any potential issues?

Thanks again!

Hi @jfaria,

Are you looking at the max length before running the upgrade, or after? If you’re looking at the contents of that table after the failure, you won’t see the problem entry. I’m pretty sure it’s the authors table you need to look at, not users.

Regards,
Alec Smecher
Public Knowledge Project Team

I’ve checked the database after restoring it. I just can’t seem figure it out.
I’m currently trying to upgrade our dev site, so I’ve tried restoring from our production database too, in case there was an issue with the dev database, but still got the same results.

Hi @jfaria,

The only other cause I can think of is if an email address contains special characters, and your database encoding is incorrectly configured, so that a single accented character in the user interface could occupy up to 4 bytes in the database. Make sure your configuration file’s character set configuration for your upgrade installation matches that of your production version, and that the database has the same collation setup.

Regards,
Alec Smecher
Public Knowledge Project Team

So, I looked at ‘authors’ and saw that some rows had NULL as the email instead of just an empty string. So I changed all NULL values and it made it past the original problem.
However, the php tools/upgrade.php upgrade now ran into another NULL issue saying “ERROR: Upgrade failed: DB: Column ‘publication_id’ cannot be null”, so I’ll look into that. But at least it seems like there’s some progress.

1 Like