CODEWITHSUNDEEP
mysqldatabasejdbcencodingplayframework
mpartan
mpartan

Reputation: 1316

Mysterious MySQL encoding issue with Java Driver and Play Framework

Problem is that with inserting Nordic characters into a database I get the result I want in the command line, but when I fetch them in the application, the text and the encoding is somehow messed up

I am using MySQL 5.7.15 from the Docker -repository in the server where this issue occurs. I have a table where the names can have Nordic characters, i.e. å, ä and ö.

id    | name
1     | Kalle Anka

If I do an inspection (SHOW FULL COLUMNS FROM users) to the table, it says name's collation is utf8_general_ci. Now I have a file updates.sql (UTF-8) that I am running against the database, with statements like this

update users set name = 'Gåsfrid Lukas' where id = 1;
# quite a few lines more

The script runs fine in a local development environment and in a server environment. I get a similar response in the command line in both environments.

select * from site where id = 38593;
id       |  name
1        |  Gåsfrid Lukas

I have a GET -route, which only does a simple query to the database and returns the user in JSON. It is using Play Framework 2.4.3, com.mysql.jdbc.Driver as the Driver (Squeryl as the ORM). I can get more into detail with the implementation if needed, but I don't think it's the problem here. The connection URL is configured like this.

db.default.url = "jdbc:mysql://localhost/duckbase?useUnicode=true&characterEncoding=utf-8&mysqlEncoding=utf8"

In development everything works nice and correct, and it returns following JSON

{"name":"Gåsfrid Lukas","id":1}

However, in the server environment everything isn't so nice anymore, and the encoding is messed up like this

{"name":"GÃ¥sfrid Lukas","id":1}

To make things even more weird, if in a local development machine I take a database dump from the database, and put it in the server environment, and then update the original user with a statement

UPDATE users u1 INNER JOIN backup.users u2 ON u2.id = u1.id SET u1.name = u2.name WHERE u1.id = 1 AND u2.id = 1; #this copies Gåsfrid Lukas -name  from the backup to the actual DB
SELECT * from users WHERE id = 1;
# returns
id    | name
1     | G▒frid Lukas

So the name seems to be corrupted in the database? Nope, the earlier REST-route returns following JSON with the corrupted entry.

{"name":"Gåsfrid Lukas","id":1}

So bottom line, when everything looks to be OK in the database (CLI), the application doesn't work properly. When everything doesn't look to be OK in the database, the application works properly.

I am a little dumbfound by this. Is the MySQL Driver causing something there that is causing this behavior? If the MySQL Driver is somehow fixing a faulty encoding so that the G▒sfrid Lukas -entry works, how come the original UTF-8 -name is showing incorrectly? What MySQL configuration parameters should I look into that could shed light into this?

Upvotes: 0

Views: 123

Answers (1)

Rick James
Rick James

Reputation: 142208

Ã¥ is Mojibake for å. See http://stackoverflow.com/questions/38363566/trouble-with-utf8-characters-what-i-see-is-not-what-i-stored for discussion of causes and prevention of such.

If that does not suffice, follow the debugging tips and show us the HEX, etc.

Upvotes: 1

Related Questions