OJS OCS OMP OHS

You are viewing the PKP Support Forum | PKP Home Wiki



OJS 2.2.4 -> 2.3.3 upgrade causes dataloss

Are you responsible for making OJS work -- installing, upgrading, migrating or troubleshooting? Do you think you've found a bug? Post in this forum.

Moderators: jmacgreg, btbell, michael, bdgregg, barbarah, asmecher

Forum rules
What to do if you have a technical problem with OJS:

1. Search the forum. You can do this from the Advanced Search Page or from our Google Custom Search, which will search the entire PKP site. If you are encountering an error, we especially recommend searching the forum for said error.

2. Check the FAQ to see if your question or error has already been resolved.

3. Post a question, but please, only after trying the above two solutions. If it's a workflow or usability question you should probably post to the OJS Editorial Support and Discussion subforum; if you have a development question, try the OJS Development subforum.

OJS 2.2.4 -> 2.3.3 upgrade causes dataloss

Postby tgc99 » Tue Oct 26, 2010 3:07 am

I've just run into a very nasty bug that causes dataloss and db schema inconsistency atleast when the DB is postgres and the counter plugin is used.

The problem is the counter plugin and the counter_monthly_log table, after the upgrade it has lost its content and is no longer consistent with the schema definition from the plugin source.

In my OJS 2.2.4 DB I see this:
Code: Select all
ojs=# \d counter_monthly_log
      Table "public.counter_monthly_log"
     Column      |  Type  |     Modifiers     
-----------------+--------+--------------------
 year            | bigint | not null
 journal_id      | bigint | not null
 count_jan       | bigint | not null default 0
 count_feb       | bigint | not null default 0
 count_mar       | bigint | not null default 0
 count_apr       | bigint | not null default 0
 count_may       | bigint | not null default 0
 count_jun       | bigint | not null default 0
 count_jul       | bigint | not null default 0
 count_aug       | bigint | not null default 0
 count_sep       | bigint | not null default 0
 count_oct       | bigint | not null default 0
 count_nov       | bigint | not null default 0
 count_dec       | bigint | not null default 0
 count_ytd_total | bigint | not null default 0
 count_ytd_html  | bigint | not null default 0
 count_ytd_pdf   | bigint | not null default 0
Indexes:
    "counter_monthly_log_pkey" UNIQUE, btree ("year", journal_id)
ojs=# select * from counter_monthly_log;
 year | journal_id | count_jan | count_feb | count_mar | count_apr | count_may | count_jun | count_jul | count_aug | count_sep | count_oct | count_nov | count_dec | count_ytd_total | count_ytd_html | count_ytd_pdf
------+------------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+-----------------+----------------+---------------
 2010 |         31 |         0 |         9 |        14 |         0 |         0 |         0 |         0 |         0 |         0 |         0 |         0 |         0 |              23 |              0 |            23
 2010 |         32 |         0 |         0 |         1 |         0 |         0 |         0 |         0 |         0 |         0 |         0 |         0 |         0 |               1 |              0 |             1
 2010 |         19 |         0 |        48 |        61 |         0 |         0 |         0 |         0 |         0 |         0 |         0 |         0 |         0 |             109 |              0 |           109
...
 2010 |         52 |         0 |         0 |         0 |         0 |         0 |       352 |       178 |       164 |       184 |       415 |         0 |         0 |            1293 |              0 |          1293
 2010 |         13 |         0 |     10320 |     11260 |     13873 |     11476 |      7232 |      7228 |      6750 |      6036 |      4341 |         0 |         0 |           78516 |              0 |         74327
 2010 |         15 |         0 |       135 |       184 |       195 |       140 |       125 |       120 |        85 |        42 |        58 |         0 |         0 |            1084 |             46 |           988


After the upgrade to 2.3.3-2 the table now looks like this!:
Code: Select all
ojsdev=# \d counter_monthly_log
Table "public.counter_monthly_log"
   Column   |  Type  | Modifiers
------------+--------+-----------
 year       | bigint | not null
 journal_id | bigint | not null
 month      | bigint |

ojsdev=# select * from counter_monthly_log;
 year | journal_id | month
------+------------+-------
 2010 |         31 |     
 2010 |         32 |     
 2010 |         19 |     
...
 2010 |         49 |     
 2010 |         12 |     
 2010 |         43 |     


When I discovered this I was puzzled how that could happen, so I redid the upgrade with debugging turned on and noticed this:
Code: Select all
(postgres7): DROP INDEX counter_monthly_log_pkey
-----
-----
(postgres7): ALTER TABLE counter_monthly_log DROP COLUMN "count_jan"
-----
-----
(postgres7): ALTER TABLE counter_monthly_log DROP COLUMN "count_feb"
-----
-----
(postgres7): ALTER TABLE counter_monthly_log DROP COLUMN "count_mar"
-----
-----
(postgres7): ALTER TABLE counter_monthly_log DROP COLUMN "count_apr"
-----
-----
(postgres7): ALTER TABLE counter_monthly_log DROP COLUMN "count_may"
-----
-----
(postgres7): ALTER TABLE counter_monthly_log DROP COLUMN "count_jun"
-----
-----
(postgres7): ALTER TABLE counter_monthly_log DROP COLUMN "count_jul"
-----
-----
(postgres7): ALTER TABLE counter_monthly_log DROP COLUMN "count_aug"
-----
-----
(postgres7): ALTER TABLE counter_monthly_log DROP COLUMN "count_sep"
-----
-----
(postgres7): ALTER TABLE counter_monthly_log DROP COLUMN "count_oct"
-----
-----
(postgres7): ALTER TABLE counter_monthly_log DROP COLUMN "count_nov"
-----
-----
(postgres7): ALTER TABLE counter_monthly_log DROP COLUMN "count_dec"
-----
-----
(postgres7): ALTER TABLE counter_monthly_log DROP COLUMN "count_ytd_total"
-----
-----
(postgres7): ALTER TABLE counter_monthly_log DROP COLUMN "count_ytd_html"
-----
-----
(postgres7): ALTER TABLE counter_monthly_log DROP COLUMN "count_ytd_pdf"
-----
-----
(postgres7): BEGIN
-----
-----
(postgres7): SELECT * INTO TEMPORARY TABLE counter_monthly_log_tmp FROM counter_monthly_log
-----
-----
(postgres7): DROP TABLE counter_monthly_log CASCADE
-----
-----
(postgres7): CREATE TABLE counter_monthly_log (
year                     INT8 NOT NULL,
journal_id               INT8 NOT NULL
)
-----
-----
(postgres7): INSERT INTO counter_monthly_log (year, journal_id) SELECT year, journal_id FROM counter_monthly_log_tmp
-----
-----
(postgres7): DROP TABLE counter_monthly_log_tmp
-----
-----
(postgres7): COMMIT
-----
-----
(postgres7): ALTER TABLE counter_monthly_log ADD COLUMN month INT8
-----
-----
(postgres7): ALTER TABLE counter_monthly_log ALTER COLUMN month SET NOT NULL
-----
ojs2 has produced an error
  Message: WARNING: pg_query(): Query failed: ERROR:  column "month" contains null values
  In file: /home/ojs/ojsdev/lib/pkp/lib/adodb/drivers/adodb-postgres7.inc.php
  At line: 122
  Stacktrace:
  Server info:
   OS: Linux
   PHP Version: 5.1.6
   Apache Version: N/A
   DB Driver: postgres
   DB server version: PostgreSQL 8.1.21 on i686-redhat-linux-gnu, compiled by GCC gcc (GCC) 4.1.2 20080704 (Red Hat 4.1.2-48)
-1: ERROR:  column "month" contains null values
                                                                        ADOConnection._Execute(ALTER TABLE counter_monthly_log ALTER COLUMN month SET NOT NULL, false)% line  864, file: /home/ojs/ojsdev/lib/pkp/lib/adodb/adodb.inc.php
                                                                ADOConnection.Execute(ALTER TABLE counter_monthly_log ALTER COLUMN month SET NOT NULL)% line  452, file: /home/ojs/ojsdev/lib/pkp/classes/install/Installer.inc.php
                                                        Installer.executeSQL(ALTER TABLE counter_monthly_log ALTER COLUMN month SET NOT NULL)% line  443, file: /home/ojs/ojsdev/lib/pkp/classes/install/Installer.inc.php
                                                Installer.executeSQL(Array[41])% line  613, file: /home/ojs/ojsdev/lib/pkp/classes/plugins/PKPPlugin.inc.php
                                        PKPPlugin.updateSchema(Installer::postInstall, Array[2])% line    0, file:


It seems that the upgrade process is crudely trying to mold the table into the new schema defintion but fails utterly. Worse is the fact that this did not stop the upgrade from continuing and without debugging turned on there is no sign that anything went wrong during the upgrade!

Looking in plugins/generic/counter I find upgrade.xml and counter_monthly_log_1_1.xml which contains some SQL to transform the table by summarising the existing data into new columns, however it is MySQL specific.
I made a quick hack to change the SQL to postgres compatible and reran the upgrade but it did not seem to even run this SQL at all :(

-tgc
tgc99
 
Posts: 56
Joined: Thu Oct 18, 2007 3:50 am
Location: Aarhus, Denmark

Re: OJS 2.2.4 -> 2.3.3 upgrade causes dataloss

Postby princec » Tue Oct 26, 2010 3:03 pm

Hi tgc,

The upgrade code was indeed written for MySQL.

You'll need to restore the counter_monthly_log table from your backup.

I'll attempt a translation into sql for postgres. Actually, the only change I would make would be to the CREATE TABLE statement. The ALTER TABLE and INSERT INTO statement are identical to MySQL.

Here are the three statements below, should work fine in postgres 8:

Code: Select all
ALTER TABLE counter_monthly_log
RENAME TO counter_monthly_log_old;


Code: Select all
CREATE TABLE counter_monthly_log
(
   "year" integer,
   "month" integer,
   journal_id integer,
   count_html integer,
   count_pdf integer,
   count_other integer,
   CONSTRAINT counter_monthly_log_01 PRIMARY KEY ("year", "month", journal_id)
)
WITHOUT OIDS;


Code: Select all
INSERT INTO counter_monthly_log
(year,month,journal_id,count_html,count_pdf,count_other)
SELECT year, 1 as month, journal_id, 0, count_jan as count, 0 FROM counter_monthly_log_old
 UNION
SELECT year, 2 as month, journal_id, 0, count_feb as count, 0 FROM counter_monthly_log_old
 UNION
SELECT year, 3 as month, journal_id, 0, count_mar as count, 0 FROM counter_monthly_log_old
 UNION
SELECT year, 4 as month, journal_id, 0, count_apr as count, 0 FROM counter_monthly_log_old
 UNION
SELECT year, 5 as month, journal_id, 0, count_may as count, 0 FROM counter_monthly_log_old
 UNION
SELECT year, 6 as month, journal_id, 0, count_jun as count, 0 FROM counter_monthly_log_old
 UNION
SELECT year, 7 as month, journal_id, 0, count_jul as count, 0 FROM counter_monthly_log_old
 UNION
SELECT year, 8 as month, journal_id, 0, count_aug as count, 0 FROM counter_monthly_log_old
 UNION
SELECT year, 9 as month, journal_id, 0, count_sep as count, 0 FROM counter_monthly_log_old
 UNION
SELECT year, 10 as month, journal_id, 0, count_oct as count, 0 FROM counter_monthly_log_old
 UNION
SELECT year, 11 as month, journal_id, 0, count_nov as count, 0 FROM counter_monthly_log_old
 UNION
SELECT year, 12 as month, journal_id, 0, count_dec as count, 0 FROM counter_monthly_log_old
ORDER BY journal_id, year, month;


Verify each step along the way and when you're confident with the result you can DROP the counter_monthly_log_old table.

Thanks,
Colin Prince
princec
 
Posts: 7
Joined: Fri Oct 31, 2008 8:31 am

Re: OJS 2.2.4 -> 2.3.3 upgrade causes dataloss

Postby tgc99 » Wed Oct 27, 2010 12:03 am

princec wrote:Hi tgc,

The upgrade code was indeed written for MySQL.

You'll need to restore the counter_monthly_log table from your backup.

Fortunately this is not a production system.
From previous experience I know that postgres support in OJS is not that well tested so I'm using a throwaway instance to test the upgrade.

I'll attempt a translation into sql for postgres. Actually, the only change I would make would be to the CREATE TABLE statement. The ALTER TABLE and INSERT INTO statement are identical to MySQL.

Yes I had come to the same conclusion.
However it did not seem like this file was activated at all during the upgrade so even though I had already translated the SQL statements it made no difference.
For reference I've attached the changes I did.

Here are the three statements below, should work fine in postgres 8:

Code: Select all
CREATE TABLE counter_monthly_log
(
   "year" integer,
   "month" integer,
   journal_id integer,
   count_html integer,
   count_pdf integer,
   count_other integer,
   CONSTRAINT counter_monthly_log_01 PRIMARY KEY ("year", "month", journal_id)
)
WITHOUT OIDS;



This does not look to be consistent with the contents of plugins/generic/counter/schema.xml.
It uses INT8 which atleast in my postgres 8.1.21 translates to bigint.
I know very little of MySQL and I was somewhat daunted by the reference documentation however I took 'UNIQUE KEY' to mean a 'UNIQUE INDEX' in postgres.

Verify each step along the way and when you're confident with the result you can DROP the counter_monthly_log_old table.

I can do the manual fix no problem, but this is something that needs to be adressed in the automated upgrade process ASAP.
It would also be good to understand why this error did not cause the upgrade process to fail as that is what I usually see when something like this happens (ie. with bz6023 and similar).

-tgc
Attachments
ojs-2.3.3-counter-upgrade-pg.patch
(3.09 KiB) Downloaded 46 times
tgc99
 
Posts: 56
Joined: Thu Oct 18, 2007 3:50 am
Location: Aarhus, Denmark

Re: OJS 2.2.4 -> 2.3.3 upgrade causes dataloss

Postby princec » Wed Oct 27, 2010 11:34 am

Hi tgc,

Yes, agreed, bigint is a better choice. I'll try installing 2.2.4 using postgres as a DB and then try your patch when upgrading.

Colin
princec
 
Posts: 7
Joined: Fri Oct 31, 2008 8:31 am

Re: OJS 2.2.4 -> 2.3.3 upgrade causes dataloss

Postby princec » Tue Nov 02, 2010 1:08 pm

Hi tgc,

The problem was that OJS was not running the upgrade code for the plugin at the right time. I've made a change for that and also incorporated your code for Postgres, attached to this bug:

http://pkp.sfu.ca/bugzilla/show_bug.cgi?id=6097

Cheers,
Colin
princec
 
Posts: 7
Joined: Fri Oct 31, 2008 8:31 am

Re: OJS 2.2.4 -> 2.3.3 upgrade causes dataloss

Postby marchitelli » Thu Nov 25, 2010 11:53 am

Hi,
we would use the patch to correct the upgrade of COUNTER plugin.

Do you think is it possible or is better to wait for the fix of idempotency issue?

Thanks,
andrea
marchitelli
 
Posts: 121
Joined: Tue May 22, 2007 12:44 am


Return to OJS Technical Support

Who is online

Users browsing this forum: Bing [Bot], Google [Bot] and 2 guests

cron