B4J Question Error with charset and collation on mysql.

IslandMedic

Member
Licensed User
Longtime User
here is the connection string:
JdbcUrl=jdbc:mysql://localhost:3306/b4j_server?characterEncoding=utf8&autoReconnect=true&useSSL=false&useUnicode=true

here is the error I get:
com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: COLLATION 'utf8_unicode_ci' is not valid for CHARACTER SET 'utf8mb4'

On the client browser I get "SyntaxError: Unexpected end of JSON input"

Mysql v 8.0
connector v: mysql-connector-java-5.1.48-bin

I have checked the table and db and they are set to utf8 / utf8_bin

I am trying to get the example websockets ServerExample to run. It is showing this error on line 26 of the DB module.
 

IslandMedic

Member
Licensed User
Longtime User
Line 26: Dim count As Int = sq.ExecQuerySingleResult2("SELECT count(name) FROM b4j_users WHERE name = ? COLLATE utf8mb4_bin", _
Array As String(name))

I had to change the collate to the utf8mb4_bin.... that is the only way it would move past the error.... still don't understand the tables and db are set to just utf8 and utf8_bin...and yet this is what I had to do. hemm.
 
Upvote 0

IslandMedic

Member
Licensed User
Longtime User
Yeah lying in bed last night I thought of the same thing. I tried this morning and no luck still behaving the same. what is really weird is I don't know where utf8mb4 is coming from? Is it the way the data is coming in over the http connection that it is formatted in such a way that that bj4 thinks it is that collation? I am at a bit of a loss.

brad
 
Upvote 0

Jorge M A

Well-Known Member
Licensed User
There are some things you can go checking out.

One, check your "my.ini" file in the MySQL installation folder.
Check the entries:

B4X:
[mysql]
default-character-set=utf8

[client]
default-character-set=utf8

if you change them, don't forget to backup first, and restart the service later.

Another attempt is that you remove the characterEncoding and useUnicode parameters from your connection string, so that MySQL uses the default ones, and you have a better clue where the error comes from.
 
Upvote 0

IslandMedic

Member
Licensed User
Longtime User
ok changing the ini file for force the utf8 seems to have worked. There was also and updated connector string than what is in the settings file. In the version 8 of mysql you need to use this now. DriverClass=com.mysql.cj.jdbc.Driver

Now on to the next error, when i go and hit register.. to register a login.. \i get and error of not found. it looks like it has to do with the RecaptchaPrivateKey= so i am working my way through that.
 
Upvote 0
Top