Database(MySQL) Writer node error

KNIME Extensions
1

 

Dear everyone,

I need helps!

I'm trying to write twitter data to MySQL DB using Database Writer node.

But it couldn't write every raws of data with next warning message...

(WARN      DatabaseWriterConnection           Error while adding row #50 (98), reason: Incorrect string value: '\xF0\x9F\x92\xA3 @...' for column 'tweet' at row 1 - more errors...)

I think it is because of some string such as emoticon that is showing like □ □ □.

Therefore, I had check the configuration of MySQL default character variables.

It was uft8mb4 so that it should take those string with no problem.

I can't even figure out what is the clue for this issue..

Would you give me some idea?

How can I wirte all raws of my data to MySQL DB? 

 

 

2

uft8mb4 only means that the database is able to store all kinds of UTF-8 sequences. However it seems the strings you are trying to store is not valid UTF-8. If  you are using Windows the default charset is CP1252 and not every CP1252 string is automatically a valid UTF-8 string. You can try to add

-Dfile.encoding=UTF-8

to your knime.ini (at the bottom). Also if you post your workflow we could check whether we can work around such situations automatically.

3

Hello, thor!

Thanks for your quick response.

I did add the code(-Dfile.encoding=UTF-8) to knime.ini following your advice and retried my KNIME workflow.

But the problem is still remained. 

So I attach my workflow image and workflow folder zip file.

Please let me know the solution again.

Thanks a lot.

 

 

4

I couldn't reproduce your problem. I took your workflow, just changed the connection details in the Database Writer, used a database that has the charset set to utf8mb4 and was able to sucessfully insert all rows.

What does "SHOW FULL COLUMNS FROM apple" say about the collation for the "Tweet" column? In my case it's "utf8mb4_general_ci".

5

 

Hi, thor.

I checked my status of the collation for the "Tweet" column of apple. (an attached image)

In my case, it is "utf8mb4_unicode_ci". The difference with yours is "_unicode_" part.

Do you think it could make the error?

If it might be.., do you know how can I change my status to "_general_"?

Thank you for your continuous helps.

 

6

Sorry, it also works if I set the collation to "utf8mb4_unicode_ci". I have no idea what the problem could be. Can you send me the relevant part of the knime.log? It may contain more information about the actual database error.

7

 

Hello, thor.

Sure, you are my only hope utill now. 

I attached knime.log file below as a zip file. 

Thank you again for your kind helps.

 

 

8

Hello Thor

 

I have the same Problem but I do not want to change the DB to utf8mb4 - is there a possibility to get rid of all non utf8 charaters ?

I tried several nodes

- String Manipulation

- String Replacer

it can be that I configured teh nodes wrong

 

 

9

Hello,

did we ever solve this? I have the same issue.