OJS 2.4.6 DB Upgrade fail-MySQL

Software Support
1

Attempting to upgrade from ojs 2.3.3 to 2.4.6 and keep getting the error:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘300 AND UNIX_TIMESTAMP(pa.date_published) - UNIX_TIMESTAMP(a.date_submitted) = 0’ at line 1

Right now we’re running PHP version 5.3.3. In the db it looks like the column date_published is set to datetime, so I’m not sure how to go about fixing the problem.

2

Hi @oneill38,

The part of the upgrade that’s giving you trouble is in dbscripts/xml/upgrade/2.3.5_update.xml:

<!--
 - 2. Null out pa.date_published from entries where the date published
 -    is less than 5 minutes after the date submitted. (Likely to
 -    indicate a batch process such as expedited submission or quick
 -    submit.)
 -->
<sql>
    <query driver="mysql">UPDATE published_articles pa, articles a SET pa.date_published = NULL WHERE a.article_id = pa.article_id AND UNIX_TIMESTAMP(pa.date_published) - UNIX_TIMESTAMP(a.date_submitted) &lt; 300 AND UNIX_TIMESTAMP(pa.date_published) - UNIX_TIMESTAMP(a.date_submitted) &gt;= 0</query>
    <query driver="postgres7">UPDATE published_articles SET date_published = NULL WHERE article_id IN (SELECT a.article_id FROM published_articles pa, articles a WHERE a.article_id = pa.article_id AND EXTRACT(EPOCH FROM pa.date_published) - EXTRACT(EPOCH FROM a.date_submitted) &lt; 300 AND EXTRACT(EPOCH FROM pa.date_published) - EXTRACT(EPOCH FROM a.date_submitted) &gt;= 0)</query>
</sql>

I’m not sure why this query isn’t working – what version of MySQL are you using? Can you try the query manually in MySQL, making sure to first convert entities to characters (e.g. &lt; to >)?

Note that if you encounter a SQL problem during upgrade, you’ll need to restore your DB from backup to the state it was in before you tried the upgrade. An error like this will leave your database somewhere between the two versions and it’s likely that trying to run the upgrade script again will leave the upgrade process confused.

Regards,
Alec Smecher
Public Knowledge Project Team

3

That seemed to get it working, and allowed the upgrade to complete. Unfortunately the homepage is now a white screen printing out MySQL queries, but that could be due to some customizations that were done to the platform, that predate me.

4

Hi @oneill38,

Is “debug” turned on in your config.inc.php configuration file? That’ll cause SQL queries to get dumped to the reader.

Regards,
Alec Smecher
Public Knowledge Project Team

5

Ahh yup, you were right! Thank you!