0

I was playing with a test client both on C# and JAVA and wanted to execute a simple query: *select * from data;*

mysql 5.7, connector/j 5.1.39, connector/.net 6.9.9. The data table has ID and VAL columns - int and text respectively. The db schema, table and text column are using utf8 collation but strangely enough I could not make both clients to receive the query result in sync. Changing collation results in failure either in c# or java - tried utf8mb4, latin1 and more random choices.

if C# DataReader has records the JAVA ResultSet is empty or vice versa. When C# fails it throws: Fatal error encountered attempting to read the resultset and inner exception: Expected end of data packet. If java fails it just has an empty ResultSet

I tried to change different collation and charset/characterEncoding settings and trial and error leaded me to here:

If i use utf8 collation for the database and the following C# connection string:

server=x.x.x.x;user=mydbuser;database=mydb;port=3306;password=mypassword;Character Set=utf8

and this Java connection string

jdbc:mysql://x.x.x.x/mydb?user=mydbuser&password=mypassword&characterEncoding=ascii

both apps are showing result - even when the VAL column contains utf (Cyrillic) characters the java client shows them correctly regarding the ascii parameter. if characterEncoding is set to utf8 the ResultSet is again empty.

I could not wrap my head around why this works.

Tyronne
  • 135
  • 7
  • See http://stackoverflow.com/a/38363567/1766831 . In particular, please provide the `SELECT ... HEX` that it suggests. That will help decide whether the data is already corrupted in the table. – Rick James Aug 14 '16 at 12:42
  • here is the hex: value1 76616C756531 value2 76616C756532 текст на бг D182D0B5D0BAD181D18220D0BDD0B020D0B1D0B3 – Tyronne Aug 14 '16 at 13:53
  • OK, that is correctly stored utf8 (or utf8mb4). – Rick James Aug 14 '16 at 16:43
  • Hmmm... Why do you have the hex of `value1` and ` value1`? Is that output from 3 rows? – Rick James Aug 14 '16 at 16:45
  • "ResultSet is again empty" -- Are you doing any conversions after pulling the values out? – Rick James Aug 14 '16 at 16:46
  • Yes 3 rows - couldn't figure out how to format it in the comment. No conversion at all - vanilla select and then print. – Tyronne Aug 14 '16 at 18:32
  • For C#, check the spelling in `Character Set=utf8`. For jdbc, search for JDBC and Java in [_this_](http://mysql.rjweb.org/doc.php/charcoll) for hints. – Rick James Aug 14 '16 at 18:47
  • Rick, thanks for all your effort on this issue. Yesterday I had to import some data (for another unrelated task) from another db and I decided to do it in my test db - it had the following line in the sql dump - _SET character_set_client = utf8_. After I finished what I had to do, I executed my test and all was ok - both c# and java were fetching the results as expected. I even removed the characterEncoding/Character Set from the connection string and it worked as expected. I assume that I messed up the db during db/table/column creation and altering the settings at some point. – Tyronne Aug 15 '16 at 19:33
  • Alas, there are so many knobs to tweak that when you get it right, you don't necessarily know what you did. I have been chasing issues like this for years. (I don't yet have the details for C# in my list.) – Rick James Aug 15 '16 at 23:17

0 Answers0