Database error after upgrade to Mariadb v 10.2

ndaustue · April 30, 2015, 5:40am

Original post is here: http://pkp.sfu.ca/support/forum/viewtopic.php?f=8&t=14435

Content of the original post:
Hi at PKP,

we have the following issue:
We upgraded our hardware and databases. Database was upgraded from MySQL to MariaDB version 10.2.
Having successfully restored the data base and after some minor UTF8 corrections we have the following problem:

Filling a form (reviewers comments, article title, article abstract etc.) with
special characters (especially from the eastern European language codes;
sample text: Jósvafő paleo-polje: morphology and relation to the
landform evolution of Aggtelek) results in an error message:

DB Error: Incorrect string value: ‘\xC5\x91 pal…’ for column ‘comments’ at row 1

Is this a known issue and is there a solution? This problem becomes more
and more dramatic as we have many authors and papers from eastern Europe
whow now fail to upload content to ojs.

		Re: Database error after upgrade to Mariadb v 10.2
		by asmecher » Wed Apr 01, 2015 5:30 pm 

		Hi Andreas,

What are your
connection_charset and database_charset in config.inc.php? Can you check
your database itself to see what the table encodings are set to?

Regards,
Alec Smecher
Public Knowledge Project Team

Reply dated May 27:

Dear Alec,

as I seem to be unable to post a reply to your
answer, please find my comments below. Sorry it took a while, but we had
to check our systems…

I looked at the default encoding of the DATABASE and it turns out to
be UTF-8:

The tables however, are encoded in latin1:

MariaDB [zfgojs]>

My question hence:

Is it okay to convert all tables, wholesale, to utf8, using:

ALTER TABLE <table_name> CONVERT TO CHARACTER SET utf8 COLLATE
utf8_general_ci;

without breaking anything?

Thanks for your help, please advise.

ctgraham · April 30, 2015, 1:14pm

Please carefully read the documentation here:
https://dev.mysql.com/doc/refman/5.1/en/alter-table.html#idm140661332563136
especially the warning:

The CONVERT TO operation converts column values between the character sets. This is not what you want if you have a column in one character set (like latin1) but the stored values actually use some other, incompatible character set (like utf8).

The answer to your question will depend on:

What character set your original tables used (in mysql and in config.inc.php)
What “minor UTF8 corrections” you made in the meantime
What character set your config.inc.php is currently using, and whether new data has been added with that character set against the mysql latin1 configuration.

ndaustue · May 6, 2015, 8:35am

We have left the settings in config.inc.php untouched in the course of the
transfer to Mariadb:

Old system (mysql):
config.inc.php:
client_charset = utf-8
connection_charset = Off
database_charset = Off
charset_normalization = Off

New system (mariadb 10):
config.inc.ph:
client_charset = utf-8
connection_charset = Off
database_charset = Off
charset_normalization = Off

So it seems from what you suggested and the data above that the OJS
tables are still encoded in latin1 and that the database ‘polices’
this as soon as an attempt is made to insert non-latin1 (= utf8
characters unavailable in latin1) with an error.

Planned conversion procedure on the new system:

dump the database in utf8:
mysqldump -u${USER} -p${PWD} -c -e --default-character-set=utf8
–single-transaction --skip-set-charset --add-drop-database -B ${DB} >
dumpfile.sql
fix dumpfile (create database statement) by changing character set
statements to utf8: cat dumpfile.sql | sed ‘s/DEFAULT CHARACTER SET
latin1/DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci/’ | sed
‘s/DEFAULT CHARSET=latin1/DEFAULT CHARSET=utf8/’ > dumpfile-converted.sql
restore database: mysql -u${USER} -p${PWD} < dumpfile-converted.sql
CHANGE THE SETTINGS in config.inc.ph to:
client_charset = utf-8
connection_charset = utf-8
database_charset = utf-8
charset_normalization = On

Can you confirm this or do you have other suggestions ?

Thanks for your support

ctgraham · May 12, 2015, 12:56pm

Sorry for the slow reply. I have been out this past week on holiday.

Your description of the problem sounds correct to me.

Your solution makes sense to me, but I’m just another set of eyes. I have not done this type of character set correction before.

Keep a backup and let us know here if it works!

ndaustue · August 7, 2015, 9:39am

Here is the solution for this problem:

Situation: old latin1 database from mysql 5.xx

Need: everything in utf-8 to avoid problems with utf-8 characters in
reviews etc. (mariadb 10.2.xx):

Suggested procedure…

Dump database using mysqldump, wit utf8 as default character set.
use sed to massage remaining charset settings into utf8
drop database
recreate database with charset=utf8;
SET NAMES to utf8
restore massaged dump (source)

mysqldump -u root -p -c -e --default-character-set=utf8
–single-transaction --skip-set-charset --add-drop-database -B
DATA_BASE_NAME > DATA_BASE_NAME.sql

sed ‘s/DEFAULT CHARACTER SET latin1/DEFAULT CHARACTER SET utf8 COLLATE
utf8_general_ci/’ < DATA_BASE_NAME.sql | sed ‘s/DEFAULT
CHARSET=latin1/DEFAULT CHARSET=utf8/’ > DATA_BASE_NAME_converted.sql

to check what sed did:
diff DATA_BASE_NAME.sql DATA_BASE_NAME_converted.sql

mysql -u root -p

drop database DATA_BASE_NAME;
create database DATA_BASE_NAME charset=utf8;
use DATA_BASE_NAME;
SET NAMES ‘utf8’;
source DATA_BASE_NAME_converted.sql
quit;

Adjust settings of ojs’ config.inc.php accordingly:
client_charset = utf-8
connection_charset = utf-8
database_charset = utf-8
charset_normalization = On

Good luck
Andreas

ctgraham · August 7, 2015, 12:55pm

Great job, @ndaustue! Thanks for the detailed update.

I wonder why there would have been DEFAULT CHARACTER SET commands still specifying latin1 in the dumpfile if the mysqldump --default-character-set option actually performs any character recoding. That seems odd.