MySQLSyntaxErrorException: COLLATION 'utf8_bin' is not valid for CHARACTER SET 'latin1'

Question

Navigation

#1 by (1 votes)

1

I am developing a login system with connection to a database of a client, but at the time of confirming the user and password send this error:

com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: COLLATION 'utf8_bin' is not valid for CHARACTER SET 'latin1'

In the confirmation code I put collate utf8_bin ... this had previously worked for me in local database queries.

Here is the code of the query:

String Consulta = "select * from usuarios where Usuario ='" + user + "' collate utf8_bin and Contrasena ='" + pass + "' collate utf8_bin";

mysql java

asked by Marco Torres 17.06.2016 в 21:17

source

1 answer

score 1 · Answer 1

The problem is in what the COLLATE that you have in the Database is latin1 for that field.

You have 2 options; the first is to perform the query without the collate :

String Consulta = "select * from usuarios where Usuario ='" + user + "' and Contrasena ='" + pass + "'";

The second option is to modify the COLLATE of those fields:

ALTER TABLE usuarios ALTER COLUMN Usuario [TYPE] COLLATE utf8_bin
ALTER TABLE usuarios ALTER COLUMN Contrasena [TYPE] COLLATE utf8_bin

Or the entire database:

ALTER DATABASE MyDataBase COLLATE utf8_bin