6097 2010-11-01 13:01:00 -0700 Unexpected counter log data loss on upgrade 2012-06-06 15:50:00 -0700 1 1 1 Unclassified OJS Installer 2.4.x All All REOPENED P5 normal --- 1 colin.prince pkp-support a.marchitelli alec jerico.dev marini 21842 colin.prince 2010-11-01 13:01:05 -0700 During upgrade (when using Postgres) from 2.2.4 to 2.3.3 the upgrade executes unexpected SQL: DROP INDEX counter_monthly_log_pkey ALTER TABLE counter_monthly_log DROP COLUMN "count_jan" ALTER TABLE counter_monthly_log DROP COLUMN "count_feb" ALTER TABLE counter_monthly_log DROP COLUMN "count_mar" ALTER TABLE counter_monthly_log DROP COLUMN "count_apr" ALTER TABLE counter_monthly_log DROP COLUMN "count_may" ALTER TABLE counter_monthly_log DROP COLUMN "count_jun" ALTER TABLE counter_monthly_log DROP COLUMN "count_jul" ALTER TABLE counter_monthly_log DROP COLUMN "count_aug" ALTER TABLE counter_monthly_log DROP COLUMN "count_sep" ALTER TABLE counter_monthly_log DROP COLUMN "count_oct" ALTER TABLE counter_monthly_log DROP COLUMN "count_nov" ALTER TABLE counter_monthly_log DROP COLUMN "count_dec" ALTER TABLE counter_monthly_log DROP COLUMN "count_ytd_total" ALTER TABLE counter_monthly_log DROP COLUMN "count_ytd_html" ALTER TABLE counter_monthly_log DROP COLUMN "count_ytd_pdf" BEGIN SELECT * INTO TEMPORARY TABLE counter_monthly_log_tmp FROM counter_monthly_log DROP TABLE counter_monthly_log CASCADE CREATE TABLE counter_monthly_log ( year INT8 NOT NULL, journal_id INT8 NOT NULL ) INSERT INTO counter_monthly_log (year, journal_id) SELECT year, journal_id FROM counter_monthly_log_tmp DROP TABLE counter_monthly_log_tmp COMMIT ALTER TABLE counter_monthly_log ADD COLUMN month INT8 ALTER TABLE counter_monthly_log ALTER COLUMN month SET NOT NULL ALTER TABLE counter_monthly_log ADD COLUMN count_html INT8 UPDATE counter_monthly_log SET count_html=0 ALTER TABLE counter_monthly_log ALTER COLUMN count_html SET DEFAULT 0 UPDATE counter_monthly_log SET count_html = '0' WHERE count_html IS NULL ALTER TABLE counter_monthly_log ALTER COLUMN count_html SET NOT NULL ALTER TABLE counter_monthly_log ADD COLUMN count_pdf INT8 UPDATE counter_monthly_log SET count_pdf=0 ALTER TABLE counter_monthly_log ALTER COLUMN count_pdf SET DEFAULT 0 UPDATE counter_monthly_log SET count_pdf = '0' WHERE count_pdf IS NULL ALTER TABLE counter_monthly_log ALTER COLUMN count_pdf SET NOT NULL ALTER TABLE counter_monthly_log ADD COLUMN count_other INT8 UPDATE counter_monthly_log SET count_other=0 ALTER TABLE counter_monthly_log ALTER COLUMN count_other SET DEFAULT 0 UPDATE counter_monthly_log SET count_other = '0' WHERE count_other IS NULL ALTER TABLE counter_monthly_log ALTER COLUMN count_other SET NOT NULL CREATE UNIQUE INDEX counter_monthly_log_key ON counter_monthly_log (year, month, journal_id) reported in the forum: http://pkp.sfu.ca/support/forum/viewtopic.php?f=8&t=6756 21843 colin.prince 2010-11-01 13:08:27 -0700 For reference: http://github.com/pkp/ojs/blob/ojs-2_3_3-3/plugins/generic/counter/counter_monthly_log_1_1.xml There's no postgres specific code yet, only mysql. 21866 alec 2010-11-01 18:49:00 -0700 Colin, those ALTER TABLE statements are executed when the database descriptor is synced against the database (see dbscripts/xml/upgrade.xml); if you need to change the way the counter logs are stored, that step in the upgrade will need to be executed before the descriptor is synced or else the columns will be dropped and the data lost. 21875 3319 colin.prince 2010-11-02 11:12:31 -0700 Created attachment 3319 Patch against OJS 2.3.3 Adding the counter_monthly_log_1_1.xml to dbscripts/xml/upgrade.xml and adding support for Postgres 21931 colin.prince 2010-11-03 18:21:35 -0700 Committed to official: https://github.com/pkp/ojs/commit/a68cf42c204479bc0ee13e2609d2d5e88ffcc0e5 21956 jerico.dev 2010-11-05 09:27:29 -0700 Colin, I don't think that your solution is idempotent (=can be executed several times without error always leading to the correct end result). Idempotency is important in case something goes wrong at in the update process and users have to repeat it. Users should be able to re-execute the update process without error even if it has been partially executed before. Why don't you move your preparatory changes to a preupdate file instead so that you can use the normal schema synchronization mechanism? There are several preupdate files in OJS that you can use as samples. I'll forward an email about idempotency (if I find it) with more information. 22035 jerico.dev 2010-11-10 06:52:38 -0800 Non-idempotency has been confirmed. Reopened until implemented. 22456 alec 2010-12-09 12:33:37 -0800 Colin, I spotted some space-based indents in your fix here -- make sure your editor is using tabs. Also, I've encountered OJS 2.3.0 installs that don't have a counter_monthly_log table. I'd suggest making this upgrade conditional on that table existing. (See the "condition" attribute in dbscripts/xml/upgrade.xml for that purpose.) Also, the CREATE TABLE statement shouldn't be hard-coded in XML -- use a schema.xml file instead. 22601 a.marchitelli 2011-01-06 13:43:00 -0800 Hi, our user are asking for COUNTER plugin, that we can not enable since there are some issues opened about it. Do you have any news about the idempotency problem solution? We need absolutely to enable COUNTER statistics in a few days. Thank you for helping us. Andrea 22724 a.marchitelli 2011-01-31 09:20:28 -0800 Hi, we succesfully used this method with Postgres (thank to Colin): 1. Copy from the old DB the table counter_monthly_log into the new as counter_monthly_log_backup 2. drop the counter_monthly_log table and recreate it: CREATE TABLE counter_monthly_log ( "year" bigint, "month" bigint, journal_id bigint, count_html bigint, count_pdf bigint, count_other bigint, CONSTRAINT counter_monthly_log_01 PRIMARY KEY ("year", "month", journal_id) ) WITHOUT OIDS; 3. Then we would do a SELECT from the old table and put it in the new table. 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_backup UNION SELECT year, 2 as month, journal_id, 0, count_feb as count, 0 FROM counter_monthly_log_backup UNION SELECT year, 3 as month, journal_id, 0, count_mar as count, 0 FROM counter_monthly_log_backup UNION SELECT year, 4 as month, journal_id, 0, count_apr as count, 0 FROM counter_monthly_log_backup UNION SELECT year, 5 as month, journal_id, 0, count_may as count, 0 FROM counter_monthly_log_backup UNION SELECT year, 6 as month, journal_id, 0, count_jun as count, 0 FROM counter_monthly_log_backup UNION SELECT year, 7 as month, journal_id, 0, count_jul as count, 0 FROM counter_monthly_log_backup UNION SELECT year, 8 as month, journal_id, 0, count_aug as count, 0 FROM counter_monthly_log_backup UNION SELECT year, 9 as month, journal_id, 0, count_sep as count, 0 FROM counter_monthly_log_backup UNION SELECT year, 10 as month, journal_id, 0, count_oct as count, 0 FROM counter_monthly_log_backup UNION SELECT year, 11 as month, journal_id, 0, count_nov as count, 0 FROM counter_monthly_log_backup UNION SELECT year, 12 as month, journal_id, 0, count_dec as count, 0 FROM counter_monthly_log_backup ORDER BY journal_id, year, month 22752 colin.prince 2011-02-03 12:04:10 -0800 As Andrea just pointed out, for this to work properly, the count* fields should be not null and default to 0. So step 2 in the manual process should be: CREATE TABLE counter_monthly_log ( "year" bigint, "month" bigint, journal_id bigint, count_html bigint NOT NULL DEFAULT 0, count_pdf bigint NOT NULL DEFAULT 0, count_other bigint NOT NULL DEFAULT 0, CONSTRAINT counter_monthly_log_01 PRIMARY KEY ("year", "month", journal_id) ) WITHOUT OIDS; This is already specified in the schema so no change required there. 3319 2010-11-02 11:12:00 -0700 2010-11-02 11:12:31 -0700 Patch against OJS 2.3.3 6097a.patch text/plain 4030 colin.prince ZGlmZiAtLWdpdCBhL2Ric2NyaXB0cy94bWwvdXBncmFkZS54bWwgYi9kYnNjcmlwdHMveG1sL3Vw Z3JhZGUueG1sCmluZGV4IGZkZjVkZDkuLmZlMmQxMDYgMTAwNjQ0Ci0tLSBhL2Ric2NyaXB0cy94 bWwvdXBncmFkZS54bWwKKysrIGIvZGJzY3JpcHRzL3htbC91cGdyYWRlLnhtbApAQCAtMjMxLDYg KzIzMSwxMSBAQAogCQk8bm90ZSBmaWxlPSJkb2NzL3JlbGVhc2Utbm90ZXMvUkVBRE1FLTIuMy40 IiAvPgogCTwvdXBncmFkZT4KIAorICAgIDx1cGdyYWRlIG1pbnZlcnNpb249IjIuMi4zLjAiIG1h eHZlcnNpb249IjIuMy4yLjAiPgorICAgICAgICA8IS0tIENPVU5URVIgLSBjb3VudGVyX21vbnRo bHlfbG9nIHVwZGF0ZS4tLT4KKyAgICAgICAgPGRhdGEgZmlsZT0icGx1Z2lucy9nZW5lcmljL2Nv dW50ZXIvY291bnRlcl9tb250aGx5X2xvZ18xXzEueG1sIiAvPgorICAgIDwvdXBncmFkZT4KKwog CTwhLS0gdXBkYXRlIHBsdWdpbiBjb25maWd1cmF0aW9uIC0gc2hvdWxkIGJlIGRvbmUgYXMgdGhl IGZpbmFsIHVwZ3JhZGUgdGFzayAtLT4KIAk8Y29kZSBmdW5jdGlvbj0iYWRkUGx1Z2luVmVyc2lv bnMiIC8+CiA8L2luc3RhbGw+CmRpZmYgLS1naXQgYS9wbHVnaW5zL2dlbmVyaWMvY291bnRlci9j b3VudGVyX21vbnRobHlfbG9nXzFfMS54bWwgYi9wbHVnaW5zL2dlbmVyaWMvY291bnRlci9jb3Vu dGVyX21vbnRobHlfbG9nXzFfMS54bWwKaW5kZXggMzI4MmFkMi4uNzE1Y2M2MSAxMDA2NDQKLS0t IGEvcGx1Z2lucy9nZW5lcmljL2NvdW50ZXIvY291bnRlcl9tb250aGx5X2xvZ18xXzEueG1sCisr KyBiL3BsdWdpbnMvZ2VuZXJpYy9jb3VudGVyL2NvdW50ZXJfbW9udGhseV9sb2dfMV8xLnhtbApA QCAtMiw3ICsyLDcgQEAKIDwhRE9DVFlQRSBkYXRhIFNZU1RFTSAiLi4vLi4vLi4vbGliL3BrcC9k dGQveG1sRGF0YS5kdGQiPgogCiA8IS0tCi0gICogY291bnRlcl9tb250aGx5X2xvZ18xXzcueG1s CisgICogY291bnRlcl9tb250aGx5X2xvZ18xXzEueG1sCiAgICoKICAgKiBDb3B5cmlnaHQgKGMp IDIwMDMtMjAxMCBKb2huIFdpbGxpbnNreQogICAqIERpc3RyaWJ1dGVkIHVuZGVyIHRoZSBHTlUg R1BMIHYyLiBGb3IgZnVsbCB0ZXJtcyBzZWUgdGhlIGZpbGUgZG9jcy9DT1BZSU5HLgpAQCAtMTUs MzQgKzE1LDUwIEBACiAKICAgPCEtLSByZW5hbWUgY3VycmVudCB0YWJsZSB0byBfb2xkIC0tPgog ICA8c3FsPgotICAgIDxxdWVyeSBkcml2ZXI9Im15c3FsIj4KKyAgICA8cXVlcnk+CiAgICAgICBB TFRFUiBUQUJMRSBjb3VudGVyX21vbnRobHlfbG9nIFJFTkFNRSBUTyBjb3VudGVyX21vbnRobHlf bG9nX29sZAogICAgIDwvcXVlcnk+CiAgIDwvc3FsPgogCisKKwogICA8IS0tIGNyZWF0ZSBuZXcg Y291bnRlcl9tb250aGx5X2xvZyB3aXRoIGEgbmV3IHNjaGVtYSAtLT4KICAgPHNxbD4KICAgICA8 cXVlcnkgZHJpdmVyPSJteXNxbCI+CiAgICAgICBDUkVBVEUgVEFCTEUgY291bnRlcl9tb250aGx5 X2xvZyAoIHllYXIgaW50KDExKSBOT1QgTlVMTCwgbW9udGggaW50KDExKSBOT1QgTlVMTCwgam91 cm5hbF9pZCBpbnQoMTEpIE5PVCBOVUxMLCBjb3VudF9odG1sIGludCgyMCkgTk9UIE5VTEwgREVG QVVMVCAwLCBjb3VudF9wZGYgaW50KDIwKSBOT1QgTlVMTCBERUZBVUxUIDAsIGNvdW50X290aGVy IGludCgyMCkgTk9UIE5VTEwgREVGQVVMVCAwLCBVTklRVUUgS0VZIGNvdW50ZXJfbW9udGhseV9s b2dfdWtleSAoeWVhcixtb250aCxqb3VybmFsX2lkKSApCiAgICAgPC9xdWVyeT4KKworICAgIDwh LS0gU2FtZSB0aGluZyBmb3IgcG9zdGdyZXMsIHByb2JhYmx5IG9ubHkgd29ya3Mgb24gUEcgOC54 IC0tPgorICAgIDxxdWVyeSBkcml2ZXI9InBvc3RncmVzNyI+CisgICAgICBDUkVBVEUgVEFCTEUg Y291bnRlcl9tb250aGx5X2xvZyAoIHllYXIgSU5UOCBOT1QgTlVMTCwgam91cm5hbF9pZCBJTlQ4 IE5PVCBOVUxMLCBtb250aCBJTlQ4IE5PVCBOVUxMLCBjb3VudF9odG1sIElOVDggREVGQVVMVCAw IE5PVCBOVUxMLCBjb3VudF9wZGYgSU5UOCBERUZBVUxUIDAgTk9UIE5VTEwsIGNvdW50X290aGVy IElOVDggREVGQVVMVCAwIE5PVCBOVUxMKQorICAgIDwvcXVlcnk+CisgICAgPHF1ZXJ5IGRyaXZl cj0icG9zdGdyZXM3Ij4KKyAgICAgIENSRUFURSBVTklRVUUgSU5ERVggY291bnRlcl9tb250aGx5 X2xvZ19rZXkgT04gY291bnRlcl9tb250aGx5X2xvZyAoeWVhciwgbW9udGgsIGpvdXJuYWxfaWQp CisgICAgPC9xdWVyeT4KICAgPC9zcWw+CiAKKworCiAgIDwhLS0gcHVsbCBkYXRhIG91dCBvZiBf b2xkIHRhYmxlIGFuZCBpbnNlcnQgaW50byBuZXcgdGFibGUsIGFzc3VtZSAwIGZvciBjb3VudF9o dG1sLCBjb3VudF9vdGhlciAtLT4KICAgPHNxbD4KLSAgICA8cXVlcnkgZHJpdmVyPSJteXNxbCI+ CisgICAgPHF1ZXJ5PgogICAgICAgSU5TRVJUIElOVE8gY291bnRlcl9tb250aGx5X2xvZyAoeWVh cixtb250aCxqb3VybmFsX2lkLGNvdW50X2h0bWwsY291bnRfcGRmLGNvdW50X290aGVyKSBTRUxF Q1QgeWVhciwgMSBhcyBtb250aCwgam91cm5hbF9pZCwgMCwgY291bnRfamFuIGFzIGNvdW50LCAw IEZST00gY291bnRlcl9tb250aGx5X2xvZ19vbGQgVU5JT04gU0VMRUNUIHllYXIsIDIgYXMgbW9u dGgsIGpvdXJuYWxfaWQsIDAsIGNvdW50X2ZlYiBhcyBjb3VudCwgMCBGUk9NIGNvdW50ZXJfbW9u dGhseV9sb2dfb2xkIFVOSU9OIFNFTEVDVCB5ZWFyLCAzIGFzIG1vbnRoLCBqb3VybmFsX2lkLCAw LCBjb3VudF9tYXIgYXMgY291bnQsIDAgRlJPTSBjb3VudGVyX21vbnRobHlfbG9nX29sZCBVTklP TiBTRUxFQ1QgeWVhciwgNCBhcyBtb250aCwgam91cm5hbF9pZCwgMCwgY291bnRfYXByIGFzIGNv dW50LCAwIEZST00gY291bnRlcl9tb250aGx5X2xvZ19vbGQgVU5JT04gU0VMRUNUIHllYXIsIDUg YXMgbW9udGgsIGpvdXJuYWxfaWQsIDAsIGNvdW50X21heSBhcyBjb3VudCwgMCBGUk9NIGNvdW50 ZXJfbW9udGhseV9sb2dfb2xkIFVOSU9OIFNFTEVDVCB5ZWFyLCA2IGFzIG1vbnRoLCBqb3VybmFs X2lkLCAwLCBjb3VudF9qdW4gYXMgY291bnQsIDAgRlJPTSBjb3VudGVyX21vbnRobHlfbG9nX29s ZCBVTklPTiBTRUxFQ1QgeWVhciwgNyBhcyBtb250aCwgam91cm5hbF9pZCwgMCwgY291bnRfanVs IGFzIGNvdW50LCAwIEZST00gY291bnRlcl9tb250aGx5X2xvZ19vbGQgVU5JT04gU0VMRUNUIHll YXIsIDggYXMgbW9udGgsIGpvdXJuYWxfaWQsIDAsIGNvdW50X2F1ZyBhcyBjb3VudCwgMCBGUk9N IGNvdW50ZXJfbW9udGhseV9sb2dfb2xkIFVOSU9OIFNFTEVDVCB5ZWFyLCA5IGFzIG1vbnRoLCBq b3VybmFsX2lkLCAwLCBjb3VudF9zZXAgYXMgY291bnQsIDAgRlJPTSBjb3VudGVyX21vbnRobHlf bG9nX29sZCBVTklPTiBTRUxFQ1QgeWVhciwgMTAgYXMgbW9udGgsIGpvdXJuYWxfaWQsIDAsIGNv dW50X29jdCBhcyBjb3VudCwgMCBGUk9NIGNvdW50ZXJfbW9udGhseV9sb2dfb2xkIFVOSU9OIFNF TEVDVCB5ZWFyLCAxMSBhcyBtb250aCwgam91cm5hbF9pZCwgMCwgY291bnRfbm92IGFzIGNvdW50 LCAwIEZST00gY291bnRlcl9tb250aGx5X2xvZ19vbGQgVU5JT04gU0VMRUNUIHllYXIsIDEyIGFz IG1vbnRoLCBqb3VybmFsX2lkLCAwLCBjb3VudF9kZWMgYXMgY291bnQsIDAgRlJPTSBjb3VudGVy X21vbnRobHlfbG9nX29sZCBPUkRFUiBCWSBqb3VybmFsX2lkLCB5ZWFyLCBtb250aAogICAgIDwv cXVlcnk+CiAgIDwvc3FsPgogCiAKKwogICA8IS0tIHJlbW92ZSB0aGUgX29sZCB0YWJsZSAtLT4K ICAgPHNxbD4KICAgICA8cXVlcnkgZHJpdmVyPSJteXNxbCI+CiAgICAgICBEUk9QIFRBQkxFIElG IEVYSVNUUyBjb3VudGVyX21vbnRobHlfbG9nX29sZAogICAgIDwvcXVlcnk+CisKKyAgICA8cXVl cnkgZHJpdmVyPSJwb3N0Z3JlczciPgorICAgICAgRFJPUCBUQUJMRSBjb3VudGVyX21vbnRobHlf bG9nX29sZCBDQVNDQURFCisgICAgPC9xdWVyeT4KICAgPC9zcWw+CiAKLSAgPCEtLSBjcmVhdGUg aW5kZXhlcyBvciBwcmltYXJ5IGtleSAtLT4KIAogCiA8L2RhdGE+Cg==