topic utf8mb4 configuration tmysql in Talend Studio

utf8mb4 configuration tmysql

Anonymous — Mon, 19 Feb 2018 13:26:54 GMT

Hi,

I have been trying for days to insert data such smileys, data from all languages into mysql through talend.

The source is sql server and target mysql.

If I use the 'Set Names utf8mb4' in mysql and run my insert statements, the data is successfully inserted. (Ex - ハウス, is inserted correctly)

However, if I set the 'Set Names utf8mb4' in additional parameters, it just does not work (ハウス is inserted as ???)

As of now, its tmssqlinput - tmysqlbulkoutputexec

Any idea on how to solve this?

Re: utf8mb4 configuration tmysql

lojdr — Tue, 20 Feb 2018 14:53:18 GMT

Hi.

I am not sure if I am correct, but By default in Talend is used UTF-8 not UTF-8MB4. I think. you need to convert the text to UTF8MB4.

See e.g. this thread:

https://community.talend.com/t5/Design-and-Development/Encoding-UTF-8-and-ISO-8859/td-p/23158

Hope it helps.

Regards

Lojdr

Re: utf8mb4 configuration tmysql

Anonymous — Tue, 20 Feb 2018 14:56:55 GMT

Yes Lojdr. That's my question precisely. Where do I put the statement 'set names utf8mb4'

Re: utf8mb4 configuration tmysql

lojdr — Tue, 20 Feb 2018 15:32:07 GMT

"set names utf8mb4" is related to the connection so in Additional JDBC Parameters as character_set_connection.

Re: utf8mb4 configuration tmysql

Anonymous — Wed, 21 Feb 2018 08:52:04 GMT

So, in the additional setting I must include character_set_connection = utf8mb4. What about the other character settings for MySQL which must be set to utf8mb4?

Re: utf8mb4 configuration tmysql

lojdr — Wed, 21 Feb 2018 13:46:08 GMT

Character set for table definition or at least for the specific column.

Re: utf8mb4 configuration tmysql

Anonymous — Wed, 21 Feb 2018 14:01:57 GMT

Hi, this does not work for me until I set the character connection ,server connection to utf8mb4.

The tables and columns are already in utf8mb4

So , I am using the set names utf8mb4, which I am unable to use in taken

Re: utf8mb4 configuration tmysql

lojdr — Wed, 21 Feb 2018 14:09:10 GMT

Have you converted the string you are sending to the database to the URF8MB4 encoding?

Re: utf8mb4 configuration tmysql

Anonymous — Thu, 22 Feb 2018 07:16:30 GMT

I'm using tmssqlinput. How do I convert the string here to utf8mb4. In the additional parameters of tmssqlinput I have included character encoding = utf8mb4

And, also in tmysqlputput I have included in the additional parameters set names utf8mb4

But, these don't work when the incoming string has a emoji

Re: utf8mb4 configuration tmysql

lojdr — Thu, 22 Feb 2018 07:33:20 GMT

I gave you a link to an example how to convert strings from one encoding to another in Talend in my first post. Sorry, I can't help you more.

Re: utf8mb4 configuration tmysql

Anonymous — Thu, 22 Feb 2018 07:50:43 GMT

I have gone through the link you have sent me in your previous posts lojdr. But, I couldn't find any solution there

Anyways, thank you for posting your thoughts

Re: utf8mb4 configuration tmysql

lojdr — Thu, 22 Feb 2018 11:33:45 GMT

There is a post from @rhall where he showed how to transform text from one encoding to another with tJava. Use that.

Re: utf8mb4 configuration tmysql

Anonymous — Tue, 22 May 2018 11:46:52 GMT

The problem was with MySQL version. The tmysqlbulkoutputexec works for mysql 5.6 but not the same with 5.7

I guess the handling of utf8 in tmysqlbulkoutputexec is different for mysql 5.6 and 5.7