Error while upgrading OJS from 2.4.8 to 3.1 + postgres only

Software Support
,
1

Hi,

Error while upgrading database:

[data: dbscripts/xml/upgrade/3.0.0_update.xml]
ERROR: Upgrade failed: DB: ERROR: missing FROM-clause entry for table “sf”
LINHA 1: …ubmission_id = submission_files.submission_id AND sf.file_id…

Missing sf on dbscripts/xml/upgrade/3.0.0_update.xml on line 102:

98
99
100 UPDATE submission_files sf, article_galleys_migration ag m SET sf.assoc_type = 515, sf.assoc_id = agm.file_id WHERE sf.file_id = agm.style_file_id</que ry>
101 UPDATE submission_files sf, article_galleys_migration a gm SET sf.assoc_type = 515, sf.assoc_id = agm.file_id WHERE sf.file_id = agm.style_file_id</qu ery>
102 UPDATE submission_files sf SET assoc_type = 515, assoc_ id = agm.file_id FROM article_galleys_migration agm WHERE sf.file_id = agm.style_file_id</quer y>
103

I thought it best to warn you.
As soon as I test I’ll notice.

Regards,
Tarcisio Pereira

2

Hi @Tarcisio_Pereira,

Thanks – please post how the testing goes and I’ll make sure the change gets committed to the git repository.

Regards,
Alec Smecher
Public Knowledge Project Team

1 Like
3

Hi @asmecher

That line works like a charm.
But I found a new one:

96 UPDATE submission_files sf SET genre_id = g.genre_id FROM genres g, submissions s, article_galleys_migration agm WHERE g.entry_key = ‘STYLE’ AND g.context_id = s.context_id AND s.submission_id = sf.submission_id AND sf.file_id = agm.style_file_id

This line have 2 changes. After the table name and inside the where clausule.

As soon as I test this new one I’ll notice.

Regards,
Tarcisio Pereira

4

Hi @asmecher,

Same error from here:

I will apply the ‘order by’ and test again.
Please, Is possible to include this corrections to github? And this one too http://wayback.archive-it.org/7100/20160821102046/https://pkp.sfu.ca/support/forum/viewtopic.php?t=11896#p45922

I need to apply those patchs every time.

As soon as I test this ‘new’ correction I’ll notice.

Regards,
Tarcisio Pereira.

5

Hi @Tarcisio_Pereira,

The order by change is something @bozana was looking into – Bozana, did this end up leading to a commit?

As for the ADODB patch, we’re trying to avoid maintaining forks of third-party code. I suspect we’ll start by upgrading the ADODB version, which may resolve this issue, and continue looking at replacing ADODB entirely, in favour of a newer migrations-based approach to versioning (and hopefully a big simplification of our upgrade toolset).

Regards,
Alec Smecher
Public Knowledge Project Team

1 Like
6

Hi, @asmecher

After the changes listed above, everything working like a charm.

Best Regards,
Tarcisio Pereira

7

Ah, sorry @Tarcisio_Pereira and @asmecher, it’s my fault – apparently I haven’t added that ‘order by’ fix :frowning: Will do… Sorry :-\

1 Like
8

Hi @Tarcisio_Pereira,

Thanks for the confirmation – I’ve filed an issue for those two missing sf aliases and will commit the changes you suggest at Correct missing table alias for PostgreSQL upgrade · Issue #2989 · pkp/pkp-lib · GitHub.

Regards,
Alec Smecher
Public Knowledge Project Team

1 Like
9

I had an error upgrading from version 2.4.8.2 to 3.1.0. I used this patch, but I think this line is wrong, right?

  •   <query driver="postgres7">UPDATE submission_files sf SET genre_id = g.genre_id FROM genres g, submissions s, article_galleys_migration agm WHERE g.entry_key = 'STYLE' AND g.context_id = s.context_id AND s.submission_id = sf.submission_files.submission_id AND sf.file_id = agm.style_file_id</query>

sf.submission_files.submission_id appears to be redundant.

I applied the pacth and got the following error: Upgrade failed: DB: ERROR: schema sf does not exist.

I removed sf and left only submission_files and it worked.

I left it like this in the 3.0.0_update.xml:
line 96:
UPDATE submission_files SET genre_id = g.genre_id FROM genres g, submissions s, article_galleys_migration agm WHERE g.entry_key = ‘STYLE’ AND g.context_id = s.context_id AND s.submission_id = submission_files.submission_id AND submission_files.file_id = agm.style_file_id

line 102:
UPDATE submission_files SET assoc_type = 515, assoc_id = agm.file_id FROM article_galleys_migration agm WHERE submission_files.file_id = agm.style_file_id

10

Hi @Adriano_Jose

Thanks a lot! I will merge that change…
It is correct that PostgreSQL needs the full main table name that is being updated to be used and not the alias…

Best,
Bozana

11

@Tarcisio_Pereira and @Adriano_Jose, could you tell me your PostgreSQL version? – it is strange that @Tarcisio_Pereira could execute that statement with the table alias and @Adriano_Jose did not… :open_mouth:
Thanks a lot!

12

Hi @bozana

Mine:
PostgreSQL 9.4.12 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-11), 64-bit
Probably @Adriano_Jose version is < 9.

Regards,
Tarcisio Pereira.

13

Oi @bozana , my version is:
PostgreSQL 9.2.23 on x86_64-redhat-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-16), 64-bit

I think the error occurred because it is sf.submisison_files.submission_id instead of just sf.submission_id or submisison_files.submisison_id.

15

@bozana and @Adriano_Jose

My query:

submission_files sf SET genre_id = g.genre_id FROM genres g, submissions s, article_galleys_migration agm WHERE g.entry_key = ‘STYLE’ AND g.context_id = s.context_id AND s.submission_id = sf.submission_id AND sf.file_id = agm.style_file_id

Adriano’s query:

UPDATE submission_files sf SET genre_id = g.genre_id FROM genres g, submissions s, article_galleys_migration agm WHERE g.entry_key = ‘STYLE’ AND g.context_id = s.context_id AND s.submission_id = sf.submission_files.submission_id AND sf.file_id = agm.style_file_id

It’s not a version issue. It’s wrong syntax.

Regards,
Tarcisio Pereira.

16

Exactly. I saw that the syntax was wrong and I’m suggesting the fix in the .diff file that is available here:

https://github.com/pkp/ojs/commit/4d3455f1f56b4a2d21d86456b6d3e84b00f6b87c.diff

Regards,
Adriano Moreno

1 Like
17

Yes, OK.
And that typo is fixed here: pkp/pkp-lib#2989 fix a typo, using alias and table name · pkp/ojs@71dfecd · GitHub

1 Like