Upgrade from 2.3.7.0 to 2.4.8.5 failed with error Incorrect datetime value: ‘0000-00-00 00:00:00’ for column ‘date_published’ **at row 549**

Software Support
1

Hello team.

I am trying to upgrade from 2.3.7.0 to 2.4.8.5 and although i have tried either to apply pkp/pkp-lib#1788 Fix MySQL persnickityness in repairing bad data · pkp/ojs@b3f26db · GitHub or to completely delete it but i am getting

root@new-ojs-2019:/var/www/newdani/ojs-2.4.8-5# php5.6 tools/upgrade.php upgrade
[pre-install]
[load: upgrade.xml]
[version: 2.4.8.5]
[schema: lib/pkp/xml/schema/signoff.xml]
[data: dbscripts/xml/upgrade/2.4.3_preupdate_usageStatistics.xml]
[data: dbscripts/xml/upgrade/2.4.3_preupdate_usageStatistics2.xml (skipped)]
[data: dbscripts/xml/upgrade/2.4.0_preupdate_article_files.xml]
[data: dbscripts/xml/upgrade/2.4.0_preupdate_emaillog.xml]
[data: dbscripts/xml/upgrade/2.4.0_preupdate_reviewrounds.xml]
[data: dbscripts/xml/upgrade/2.4.0_idupgrade_issues.xml]
[data: dbscripts/xml/upgrade/2.4.0_idupgrade_articles.xml]
[data: dbscripts/xml/upgrade/2.4.0_idupgrade_published_articles.xml]
[data: dbscripts/xml/upgrade/2.4.0_idupgrade_issue_galleys.xml (skipped)]
[data: dbscripts/xml/upgrade/2.4.0_idupgrade_article_galleys.xml]
[data: dbscripts/xml/upgrade/2.4.0_idupgrade_supp_files.xml]
[data: dbscripts/xml/upgrade/2.4.0_notifications.xml]
[schema: lib/pkp/xml/schema/common.xml]
[schema: lib/pkp/xml/schema/groups.xml]
[schema: lib/pkp/xml/schema/log.xml]
[schema: lib/pkp/xml/schema/announcements.xml]
[schema: lib/pkp/xml/schema/scheduledTasks.xml]
[schema: lib/pkp/xml/schema/temporaryFiles.xml]
[schema: lib/pkp/xml/schema/metadata.xml]
[schema: lib/pkp/xml/schema/reviews.xml]
[schema: lib/pkp/xml/schema/reviewForms.xml]
[schema: lib/pkp/xml/schema/controlledVocab.xml]
[schema: lib/pkp/xml/schema/submissions.xml]
[schema: lib/pkp/xml/schema/comments.xml]
[schema: lib/pkp/xml/schema/notes.xml]
[schema: lib/pkp/xml/schema/gifts.xml]
[schema: lib/pkp/xml/schema/mutex.xml]
[schema: lib/pkp/xml/schema/tombstone.xml]
[schema: lib/pkp/xml/schema/metrics.xml]
[schema: dbscripts/xml/ojs_schema.xml]
ERROR: Upgrade failed: DB: Incorrect datetime value: ‘0000-00-00 00:00:00’ for column ‘date_published’ at row 549

My last attempt with the above error was with the following xml.

root@new-ojs-2019:/var/www/newdani/ojs-2.4.8-5# vi dbscripts/xml/upgrade/2.4.2_update.xml

<?xml version="1.0" encoding="UTF-8"?> UPDATE review_assignments SET stage_id=1 WHERE stage_id=0 UPDATE published_articles SET date_published = NULL WHERE CONCAT(date_published) = '0000-00-00 00:00:00' ~
2

I think that again NULL values on date_published causes the problem as follows.

mysql> SELECT COUNT() FROM published_articles WHERE date_published IS NULL;
±---------+
| COUNT() |
±---------+
| 12673 |
±---------+
1 row in set (0.01 sec)

Because of patch pkp/pkp-lib#1788 Fix MySQL persnickityness in repairing bad data · pkp/ojs@b3f26db · GitHub i would expect to see some values.

Patch does
UPDATE published_articles SET date_published = NULL WHERE CONCAT(date_published) = ‘0000-00-00 00:00:00’

but it is zero as follows
mysql> SELECT COUNT() FROM published_articles WHERE date_published=‘0000-00-00 00:00:00’;
±---------+
| COUNT() |
±---------+
| 0 |
±---------+
1 row in set, 1 warning (0.02 sec)

mysql>

Is worth manually setting 0000-00-00 00:00:00 on NULL records?

UPDATE published_articles SET date_published=‘0000-00-00 00:00:00’ WHERE date_published IS NULL;

Regards
Kostas

3

I really do not understand while during upgrading i am getting the following error

ERROR: Upgrade failed: DB: Incorrect datetime value: ‘0000-00-00 00:00:00’ for column ‘date_published’ at row 549

even though there is no record in the database.
Even if i try to put the mysql including on the aforementioned patch there is no record

mysql> UPDATE published_articles SET date_published = NULL WHERE CONCAT(date_published) = ‘0000-00-00 00:00:00’;
Query OK, 0 rows affected (0.11 sec)
Rows matched: 0 Changed: 0 Warnings: 0

4

Issue solved by setting the following sql mode and restarting sql server (before upgrading).

egrep sql_mode /etc/mysql/mysql.conf.d/mysqld.cnf
sql_mode = ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION,ONLY_FULL_GROUP_BY

Previously sql mode was
sql_mode = ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION

5

This topic was automatically closed after 6 days. New replies are no longer allowed.