[OJS 3.2.1.1] Blank, white page ("Search" function)

Software Support
1

After updating OJS from version 3.1.2.4 to 3.2.1.1, blank white pages appear. I found two such situations, maybe there are more of them?

First sytuation.

Reader interface → “Search” function
After clicking the “Search” button, an empty white page appears. This applies to both simple and advanced search.

szukaj

More information.

In the config.inc.php version 3.1.2.4 I had the following settings:

locale = en_US
client_charset = utf-8
connection_charset = utf8
database_charset = utf8

In version 3.2.1.1, “database_charset” was removed and “collation = utf8_general_ci” was added. So I have these settings:

locale = en_US
client_charset = utf-8
connection_charset = utf8
collation = utf8_general_ci

Clicking the “Search” button displays the following message:

Fatal error: Uncaught Exception: DB Error: Illegal mix of collations (utf8_general_ci,IMPLICIT) and (utf8_polish_ci,IMPLICIT) for operation ‘=’ Query: SELECT o.submission_id, MAX(s.context_id) AS journal_id, MAX(i.date_published) AS i_pub, MAX(p.date_published) AS s_pub, COUNT(*) AS count FROM submissions s JOIN publications p ON (p.publication_id = s.current_publication_id) JOIN publication_settings ps ON (ps.publication_id = p.publication_id AND ps.setting_name=‘issueId’) JOIN issues i ON (CAST(i.issue_id AS CHAR) = ps.setting_value) JOIN submission_search_objects o ON (s.submission_id = o.submission_id) NATURAL JOIN submission_search_object_keywords o0 NATURAL JOIN submission_search_keyword_list k0 WHERE s.status = 3 AND i.published = 1 AND k0.keyword_text = ? AND i.journal_id = ? GROUP BY o.submission_id ORDER BY count DESC LIMIT 500 in /home/www/ojs3/html/lib/pkp/classes/db/DAO.inc.php:703 Stack trace: #0 /home/www/ojs3/html/lib/pkp/classes/db/DAO.inc in /home/www/ojs3/html/lib/pkp/classes/db/DAO.inc.php on line 703

I tried various combinations:

  1. “database_charset = utf8” and “collation = utf8_general_ci”
  2. only “database_charset = utf8”
  3. only “collation = utf8_general_ci”
  4. none of these options

In each of these cases, the same error message is displayed.

Regards
Wojtek

2

Hi @WSMH,

You might need to use:

collation = utf8_polish_ci

It looks like your server is configured with that as a default.

(See also: Illegal mix collations. Error en búsquedas y galeradas HTML. Solucionado - #5 by asmecher)

Regards,
Alec Smecher
Public Knowledge Project Team

3

Thank you for your help.
Unfortunately, after changing the setting to “collation = utf8_polish_ci”, the same error still appears.

The only thing that has changed is the display of the ’ sign in the message.
Previously it was ‘=’ and ‘issueId’
and now it’s ‘=’ and ‘issueId’.

Regards
Wojtek

Edit:

I see that in my post the old and new sign ’ was shown the same.
I will show it in the picture:

apostrofy

5

Hello,

I have a similar issue when searching.

Fatal error : Uncaught Exception: DB Error: Illegal mix of collations (utf8_general_ci,IMPLICIT) and (utf8_unicode_ci,IMPLICIT) for operation ‘=’ Query: SELECT o.submission_id, MAX(s.context_id) AS journal_id, MAX(i.date_published) AS i_pub, MAX(p.date_published) AS s_pub, COUNT(*) AS count FROM submissions s JOIN publications p ON (p.publication_id = s.current_publication_id) JOIN publication_settings ps ON (ps.publication_id = p.publication_id AND ps.setting_name=‘issueId’) JOIN issues i ON (CAST(i.issue_id AS CHAR) = ps.setting_value) JOIN submission_search_objects o ON (s.submission_id = o.submission_id) NATURAL JOIN submission_search_object_keywords o0 NATURAL JOIN submission_search_keyword_list k0 WHERE s.status = 3 AND i.published = 1 AND k0.keyword_text = ? AND i.journal_id = ? GROUP BY o.submission_id ORDER BY count DESC LIMIT 500 in <…>/lib/pkp/classes/db/DAO.inc.php:703 Stack trace: #0 <…>/lib/p in <…>/lib/pkp/classes/db/DAO.inc.php on line 703

The config.inc.php settings:
driver = mysqli
collation = utf8_unicode_ci
client_charset = utf-8
connection_charset = utf8
charset_normalization = On

There are no tables or columns that are defined as utf8_general_ci in the database. This is on MySQL 5.6. Some columns have character_set_name and collation_name as null however, could that be a problem?

Thanks,
Steve

6

Hi all,

I’m having a tough time replicating these conditions in order to dig into it further. My observations:

  • The problem seems to be related to the string literal provided in the query:
    WHERE ... AND ps.setting_name=‘issueId’
  • This query is sent via an ADODB-based connection, which uses the mysqli PHP driver to connect to MySQL. The connection is established in DBConnection.inc.php. The connection character set is set according to the config.inc.php parameter i18n.connection_charset.
  • Other parts of OJS use the Laravel database toolset (via PDO), but this issue doesn’t seem to have shown up in that code.

If anybody is able to shed further light on this, it would be much appreciated!

Thanks,
Alec Smecher
Public Knowledge Project Team

7

Hi Alec,

From my experimentation, this portion of the query caused the issue:
JOIN issues i ON (CAST(i.issue_id AS CHAR) = ps.setting_value)

The CAST specifically. The database collation_connection value is set to utf8_general_ci even though my tables and column are set to utf8_unicode_ci, so it looks like the CAST might default to that collation_connection value?

If it were possible to pass the collation value from config.inc.php to that CAST or change the query so that it uses CONVERT with the collation value from config.inc.php, that might resolve the issue.

For the moment, I reimported the database as utf8_general_ci and I no longer get the error when searching, but I’m forced to use that utf8_general_ci collation.

Thanks,
Steve

8

Hi @Stevel,

Thanks, that’s helpful, and the fact that we only use CAST once or twice in the codebase would explain why this issue crops up so rarely. Unfortunately this is complicated somewhat by our need to support both PostgreSQL and MySQL, which of course don’t agree on a syntax for this, but I’ll see what I can do.

Regards,
Alec Smecher
Public Knowledge Project Team

9

Indeed, I converted everything to utf8_general_ci to resolve the issue. If anyone is wondering how to do this relatively easily, you can generate some sql statements for every table in your db with this:

SELECT CONCAT('ALTER TABLE ',TABLE_SCHEMA,'.',TABLE_NAME,' CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;') FROM information_schema.TABLES WHERE TABLE_SCHEMA = '<DATABASE>';

Replacing <DATABASE> with your database name. You can then take the generated statements and run them.

I also updated the DB’s default collation to match:

ALTER DATABASE <DATABASE> CHARACTER SET utf8 COLLATE utf8_general_ci;
2 Likes
10

Hi @bricas

I tried you solution but it didn’t work. Do you have another suggestion?

After that, I created a test workspace and istalled an empty OJS. I imported my data for this new database installation and copied the public folder,too. Can I do this without problem @asmecher?

Thank you!

11

Hi @riquemg,

Do you mean imported using the OJS XML import/export, or imported e.g. using MySQL import/export tools?

Regards,
Alec Smecher
Public Knowledge Project Team

12

The MySQL tool, mysqldump

13

Hi @riquemg,

I’m assuming the problem you’re trying to solve is the Illegal mix of collations message reported above?

If you use the OJS installation form to create a fresh OJS schema, then use mysqldump/mysql to load data from your old OJS installation into the new one, you’ll run into problems where the new installation already has data in it (e.g. the versions table, users table, and others).

I suspect you could achieve the same thing, without the problems, by editing the output of mysqldump and changing the collation wherever it’s specified in the CREATE TABLE statement. (Or creating a new database with DEFAULT CHARACTER SET utf8, then loading the database dump directly into it without modification. Some experimentation may be required, as I’m not sure of the exact mechanism for this failure.)

Regards,
Alec Smecher
Public Knowledge Project Team

14

Yes, my problem is the mix collations.

This problem ocorrured after I upgraded for the last version 3.2.1-1. I used the command php /tools/upgrade.php upgrade in this process.

I made 2 tests:

  1. I created a new installation and only imported the data from my old OJS. It worked.

  2. I used my old OJS files, but a new OJS database. Then I imported the the data from my old OJS. It didn’t work.

Could some file collation be the problem?

15

Hi @riquemg,

When you imported the data from your old OJS (the 1st test you mention above), did you remove the data from the database for the new installation before running the import?

Regards,
Alec Smecher
Public Knowledge Project Team

16

No, I didn’t. Just recover my backup.

17

Hi @riquemg,

Hmm, I’m not sure what in that process would be changing your collation configuration, but I can’t think of a downside to doing what you’ve done (i.e. lost data). It sounds like it’s working, so I’d suggest carrying on!

Regards,
Alec Smecher
Public Knowledge Project Team

18

@asmecher I made some test.

Test 1) A new OJS, new database, old data. It works.
Test 2) An old OJS, new database, old data. It didn’t work.
Test 3) A new OJS, old database (with old data). It works.

I think, the problem is the files system.

Can Illegal Mix Collation be any relation with files collation?

19

Hi @riquemg,

I’m not sure what you mean by files collation. Can you describe a little further?

Regards,
Alec Smecher
Public Knowledge Project Team

20

I though the problem was the database. But, if I use the two OJS installations (old and new, same version) with the same database, the problem persists only in the old version. In the old version the problem is the same:

Uncaught Exception: DB Error: Illegal mix of collations (utf8_unicode_ci,IMPLICIT) and (utf8_general_ci,IMPLICIT)

I changed the config.inc.php content of the new installation for the old installation. And it worked good. I changed public and plugins old folders for the same folders of the new installation and it didn’t work.

Every file in the file system have an encoding (ISO8859, UTF8, UTF8 with BOM, etc). I’m can’t thinking in another reason of that problem, except the files.

21

Hi @riquemg,

The difference will definitely be database-related, most probably the database’s default character set. I suspect that if you run a mysqldump on both the old database and the new one, and compare them, you’ll see differences in the CREATE TABLE statements related to character sets and collations. This probably stems from different defaults in your old and new database servers.

Regards,
Alec Smecher
Public Knowledge Project Team