How to set up MySQL on Windows to accept UTF-8 data via Groovy JDBC connections

Question

I'm writing a script in Groovy that needs to store (and later retrieve) data which contains czech characters (such as č, š, í, ě, ...).

I use a standard JDBC connection like

def sql = Sql.newInstance(
    'jdbc:mysql://localhost/db', 
    'root',
    '', 
    'com.mysql.jdbc.Driver'
);

sql.executeInsert(
    'INSERT INTO ... VALUES ...', [ ... ]
);

I have downloaded the latest MySQL ZIP archive for 64bit Windows machines and extracted it on my local hard drive. I now run it (for testing purposes) via mysqld launched manually from the command line.

When I store and retrieve the data some of the czech symbols are corrupted (?s are displayed instead of them). I believe that the database works with a wrong encoding. I would prefer the script to work with UTF-8 encoded data.

I have found a lot of (mutually different) information on the internet about how to set MySQL to work with UTF-8 data. Non of them worked for me, though.

Could you please provide instructions for my specific use case?

Have you set [MySQL to use utf8](http://stackoverflow.com/questions/202205/how-to-make-mysql-handle-utf-8-properly), or set the [table to use 'utf8' as it's charset](http://stackoverflow.com/questions/2136126/creating-mysql-table-with-explicit-default-character-set-what-if-i-dont) when created? Also, check the thing you are printing the output in has support for UTF characters, sometimes you are being returned correct data, but the thing you're using to view it messes it up... — tim_yates, Aug 06 '13 at 10:40
You can [use this to check the charset for a given db/table/column](http://stackoverflow.com/questions/1049728/how-do-i-see-what-character-set-a-database-table-column-is-in-mysql) — tim_yates, Aug 06 '13 at 10:46
One trick you could try for viewing the output is to write it to a text file, then try opening the file in Firefox for example. The Windows terminal is notoriously hard to get encoding working right :-( — tim_yates, Aug 06 '13 at 10:50
I have checked charsets of the db, table, and columns using the proposed commands and they all seem to be utf-8, i.e. ok. Even when writing the output to a text file using the File.withWriter method, the result is still corrupted. — Dušan Rychnovský, Aug 06 '13 at 11:31
Can you try changing your connection url to: `'jdbc:mysql://localhost/db?useUnicode=true&characterEncoding=UTF-8'` — tim_yates, Aug 06 '13 at 11:35
This worked. Thank you :) Please write the advice as an answer so that I can accept it. — Dušan Rychnovský, Aug 06 '13 at 11:35

score 2 · Accepted Answer · answered Aug 06 '13 at 11:39

2

As long as your db/table/column is set to use UTF-8, you can try changing your connection params to:

def sql = Sql.newInstance(
    'jdbc:mysql://localhost/db?useUnicode=true&characterEncoding=UTF-8', 
    'root',
    '', 
    'com.mysql.jdbc.Driver'
)

answered Aug 06 '13 at 11:39

tim_yates

167,322
27
342
338

How to set up MySQL on Windows to accept UTF-8 data via Groovy JDBC connections

1 Answers1