OMP 3.1.1-1 : upgrade: replicated monographs

Alfredo_Cosco · June 17, 2018, 2:38pm

Hello,
the path to upgrade is full of pitfalls.
Now we found some series where a monograph appears more than one time:

let’s see:

http://www.fedoabooks.unina.it/index.php/fedoapress/catalog/series/tria

How can I patch this?

Thanks,
Alfredo

bozana · June 21, 2018, 1:23pm

Hmmm… that is strange…
I tested it locally with the current omp-stable-3_1_1 branch, but could not produce it.
Could you execute this SQL in your OMP database:

select s., ps.,
COALESCE(stl.setting_value, stpl.setting_value) AS series_title
from submissions as s
left join published_submissions as ps on ps.submission_id = s.submission_id
left join series_settings as stpl on s.series_id = stpl.series_id and stpl.setting_name = ‘title’ and stpl.locale = ‘en_US’
left join series_settings as stl on s.series_id = stl.series_id and stl.setting_name = ‘title’ and stl.locale = ‘it_IT’ left join features as psf on s.submission_id = psf.submission_id and psf.assoc_type = 530 and psf.assoc_id = 1 where s.context_id = X and ps.pub_id is not null and s.status in (3) and s.series_id in (‘Y’)
group by s.submission_id, ps.date_published, COALESCE(stl.setting_value, stpl.setting_value)
order by case when psf.seq is null then 1 else 0 end, psf.seq ASC, ps.date_published desc

Where you should change X with your context ID, and Y with that series ID.
Your primary locale in en_US and then you also have it_IT, correct?

EDIT: Do you always have/see only one submission in the backend? I.e. is the problem only in the frontend on the series page?

Best,
Bozana

Alfredo_Cosco · June 23, 2018, 6:34pm

Hello @bozana,
the query gives me an error:
#1064 - 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 ' ps., COALESCE(stl.setting_value, stpl.setting_value) AS series_title from submi' at line 1

Your primary locale in en_US and then you also have it_IT, correct?

No, primary locale is it_IT

Do you always have/see only one submission in the backend? I.e. is the problem only in the frontend on the series page?

Yes, the back-end works well.

Tnx,
Alfredo

bozana · June 25, 2018, 12:32pm

Hi @Alfredo_Cosco,

Ah, “*” character has to apparently to be escaped in quotations in this editor…

could you try this:

select s.*, ps.*,
COALESCE(stl.setting_value, stpl.setting_value) AS series_title
from submissions as s
left join published_submissions as ps on ps.submission_id = s.submission_id
left join series_settings as stpl on s.series_id = stpl.series_id and stpl.setting_name = 'title' and stpl.locale = 'it_IT'
left join series_settings as stl on s.series_id = stl.series_id and stl.setting_name = 'title' and stl.locale = 'en_US' 
left join features as psf on s.submission_id = psf.submission_id and psf.assoc_type = 530 and psf.assoc_id = 1 
where s.context_id = 1 and ps.pub_id is not null and s.status in (3) and s.series_id in ('1')
group by s.submission_id, ps.date_published, COALESCE(stl.setting_value, stpl.setting_value)
order by case when psf.seq is null then 1 else 0 end, psf.seq ASC, ps.date_published desc

I changed the order of it_IT and en_US.
Do not forget to adapt the where line, i.e. s.context_id = 1 and s.series_id in (‘1’)

Best,
Bozana

Alfredo_Cosco · June 25, 2018, 1:24pm

Hello,
thank you very much.
The query worked now, and the result is only one record:
that’s it in json:

    [
   {
      "submission_id": 75,
      "locale": "it_IT",
      "context_id": 1,
      "series_id": 14,
      "series_position": "1",
      "edited_volume": 2,
      "language": "",
      "date_submitted": "2018-03-14 13:34:22",
      "last_modified": "2018-03-14 13:30:34",
      "date_status_modified": "2018-06-17 10:44:43",
      "status": 3,
      "submission_progress": 0,
      "pages": null,
      "fast_tracked": 0,
      "hide_author": 0,
      "stage_id": 5,
      "citations": "",
      "pub_id": 58,
      "submission_id": 75,
      "date_published": "2018-03-14 16:58:59",
      "audience": "",
      "audience_range_qualifier": "",
      "audience_range_from": "",
      "audience_range_to": "",
      "audience_range_exact": "",
      "cover_image": "a:8:{s:4:\"name\";s:9:\"cover.png\";s:5:\"width\";i:2015;s:6:\"height\";i:2845;s:13:\"thumbnailName\";s:13:\"thumbnail.png\";s:14:\"thumbnailWidth\";d:212;s:15:\"thumbnailHeight\";d:300;s:10:\"uploadName\";s:51:\"Pagine da 75-Monographic book-398-1-11-20180314.png\";s:12:\"dateUploaded\";s:19:\"2018-03-14 16:31:59\";}",
      "series_title": "TRIA Urban Studies"
   }
]

bozana · June 25, 2018, 5:56pm

Hi @Alfredo_Cosco

Hmmmm… strange…
What theme do you use i.e. did you maybe make any code changes?
Would it be possible for you to print out the variable $publishedMonographs here omp/CatalogHandler.inc.php at omp-3_1_1-1 · pkp/omp · GitHub? – you could either use

error_log(print_r($publishedMonographs, true));

(That would print the array into your server PHP error log file).

Or create a file “debut.txt” in your OMP folder and make it writable for the web user/group and then print it there, something like:

$file = 'debug.txt';
$current = file_get_contents($file);
$current .= print_r($publishedMonographs, true);
file_put_contents($file, $current);

Hmmm…

Best,
Bozana

Alfredo_Cosco · June 26, 2018, 9:37am

Hello,
the theme in a standard theme with some changes, but just in css or js ( to make a test I deactivated JS and the problem still exist).
Printing $publishedMonographs, an array with 3 identical objects.
You can find the debug file here: http://www.fedoabooks.unina.it/debug.txt
(I removed the abstracts to make it more readable).

Moreover: if I print in CatalogHandles.inc.php:
var_dump($total)
the result is “3”.

Here everything works fine: Genere, scienza e società | SHARE Libri
Saperi storici e nuove tecnologie | SHARE Libri

Here not:
http://www.fedoabooks.unina.it/index.php/fedoapress/catalog/series/osservatorio

Best,
Alfredo

bozana · June 26, 2018, 11:52am

Phew… So strange…
Just for a case: could you try the fix from this issue: Resolve missing GROUP BY in submission list fetch · Issue #3826 · pkp/pkp-lib · GitHub ?
Hmm… I’ll search further…

Alfredo_Cosco · June 26, 2018, 2:46pm

Fixed, no changes :
Thanks,
Alfredo

bozana · June 27, 2018, 8:53am

Hi @Alfredo_Cosco, how did you fix it?

Alfredo_Cosco · June 27, 2018, 9:07am

Hello @bozana,
I made the fix from github but there were no changes:
http://www.fedoabooks.unina.it/index.php/fedoapress/catalog/series/tria

3 books instead of 1

Best,
Alfredo

Alfredo_Cosco · June 28, 2018, 9:16am

Hello @bozana,
I’ve got the problem:
if in the EDIT Series window
grid/settings/series/series-grid/edit-series?seriesId=ID

I order the series by Title
the monograph is replicated 3 times

else if

I set on other kinds of order (eg. by Series Position)
the monograph appears one time.

Now the problem is: why It happens?

Best,
Alfredo

bozana · June 29, 2018, 2:52pm

Wow, great @Alfredo_Cosco!
I’ll take a look…
Thanks a lot!
Bozana

bozana · July 5, 2018, 9:45am

Hi @Alfredo_Cosco

I created a GitHub Issue and provided a patch there: book double listed in a series, when ordered by title · Issue #3864 · pkp/pkp-lib · GitHub. Track that issue for when the patch is merged – it has to be reviewed first…

Best,
Bozana