Database error messages

[jsullinger]

I am receiving the following DB error message when trying to create an archive from this PubMed OAI: http://www.pubmedcentral.nih.gov/oai/oai.cgi ; for Journal that is called Cytotechnology. I am able to successfully import Cytojournal, but receive this error below when trying to also include Cytotechnology or Journal of Pathology.

DB Error: Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE) for operation '='

Any ideas how to troubleshoot? http://harvester.cytopathnet.org

[jmacgreg]

Hi jsullinger,

This looks to be an old(er) issue: see viewtopic.php?f=2&t=333 for an example of the same thing (albeit in OJS) and some proposed solutions. Also, you may want to consider upgrading to the most recent version of the Harvester, which includes some extensive improvements and enhancements (see http://pkp.sfu.ca/harvester_download, and also search the forums for information on upgrading, as this is a major version change).

Cheers,
James

[JohnGrobler]

Good day,

I am receiving the same error on one of my Harvester sites when browsing to index.php/admin/updateIndex/5:
DB Error: Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE) for operation '='

I've tried to resolve this error by applying the fix as per viewtopic.php?f=2&t=333, but receive a MySQL error for only one of the tables:

SQL query: ALTER TABLE search_keyword_list CONVERT TO CHARACTER SET 'utf8';
MySQL said: #1062 - Duplicate entry 'manner' for key 2

Would it be safe to delete all duplicate keyword_text entries from the table until the SQL query executes successfully? Should I run this query on all new OHS deployments to prevent this problem from occurring?

OHS version 2.3.0.0 used.

Thank you,
John Grobler

[asmecher]

Hi John,

You can delete all contents for that table, perform the database conversion, then use tools/rebuildSearchIndex.php to regenerate the index entirely.

Regards,
Alec Smecher
Public Knowledge Project Team

[JohnGrobler]

Hi Alec,

Thanks for the quick response. I've backed up and deleted the all contents of the search_keyword_list. The conversion executed successfully:
ALTER TABLE search_keyword_list CONVERT TO CHARACTER SET 'utf8'

When trying to rebuild the search_keyword_list table with php tools/rebuildSearchIndex.php, I receive a few of the following errors:
Notice: unserialize(): Error at offset 180 of 1876 bytes in /usr/www/users/username/classes/record/RecordDAO.inc.php on line 159
Notice: unserialize(): Error at offset 332 of 2475 bytes in /usr/www/users/username/classes/record/RecordDAO.inc.php on line 159
Notice: unserialize(): Error at offset 2978 of 3956 bytes in /usr/www/users/username/classes/record/RecordDAO.inc.php on line 159

The table also contains 5000 less entries after the script finished execution when comparing to the original one. Do you have any advice on what else I can try?

Thanks,
John Grobler

[asmecher]

Hi John,

You've probably changed PHP versions in a way that's broken PHP's serialize / unserialize functions, i.e. your new version of PHP is having trouble getting data out of a table because the data was stored using an older version of PHP. Is it plausible to re-harvest your archives?

Regards,
Alec Smecher
Public Knowledge Project Team

[JohnGrobler]

Hi Alec,

Our hosting provider did recently upgrade PHP to a newer version; we will reinstall Harvester and re-harvest the journals. Thank you for the assistance.

Regards,
John Grobler

[asmecher]

Hi John,

No need to reinstall the harvester; simply reharvest the archives and you should be fine.

Regards,
Alec Smecher
Public Knowledge Project Team

[liebesprinz]

Hi all,

My question differs from the topic. However, it is PubMed related.

Due to I am new to the PKP tools - which I like very much - I would like to ask you whether there is any way to search (and/or to harvest) the "PubMed citations" database from within the OHS (or OJS). Backgournd is that the user should search information from within just one interface whether the information is harvested in the local database or on a remote system in case it could not be harvested locally.

If this is possible how would I have to preceed (i.e. which settings, plugin ...)?


By the way, harvesting PubMed Central seems to work fine so far although timeouts are generated from time to time.

Many thanks in advance!

Best regards

Peer