Title: MySQL Server with MariaDB driver produces dates collation error
Tags: java;mysql;jdbc;database-connection;mariadb
Question: Context &amp; Problem

My company decided to change from the MySQL driver to the MariaDB driver/connector, and still use the MySQL Server DB as the database server for our Spring App.

During this migration, I found problems related to how the driver/connector handles Dates, which results in the following error:

```Illegal mix of collations for operation '<='
```

This occur when running the following query, notice the date comparison on the query's last line. 

```String sql2 = "select coalesce(sum(b.value), 0) " +
            "  from acc_sub_account_booking b " +
            "    join acc_sub_account sa ON sa.id = b.subAccount_id " +
            "    join km_cash_bond cb ON cb.virtualPayInAccountNumber = sa.iban " +
            "    join km_rented_object ro ON ro.id = cb.rentedObject_id " +
            " where b.bookingType in ('PAY_IN', 'PAY_OUT') " +
            "       and ro.id = :rentedObjectId " +
            "       and b.valueDate <= :today";

Object sum1 = entityManager.createNativeQuery(sql).
            setParameter("rentedObjectId", pRentedObject.getId()).
            setParameter("today", new LocalDateTime(d.getYear(), d.getMonthOfYear(), d.getDayOfMonth(), 23, 59)).
            getSingleResult();
```

Versions:


Java: 7
MySQL: 5.5
Maria Driver: 1.4.6




Understanding

After reading the MySQL documentation, it mentions:

```
  MySQL Connector/J is flexible in the way it handles conversions between MySQL data types and Java data types.
  
  In general, any MySQL data type can be converted to a java.lang.String, and any numeric type can be converted to any of the Java numeric types, although round-off, overflow, or loss of precision may occur.
```

Perhaps, the error didn't occur before because the MySQL connector handles the conversion between String and Date, and the error now results from trying to compare two different data types.

This error does not occur when using MariaDB as the server, maybe the conversion handling in Maria in done at the database level and not in the connector.



Tests

I believed to be a encoding/character set problem, and changed all the tables and columns to ```utf8_general_ci```. This did not resolve the problem.

I ran some tests - always using the MariaDB Driver when running the query through the application - and got the following results:




Although, tests pass when using MariaDB, this is not an option. 
Tests #3 and 4 works, if the query parameters is cast as date: ```... and b.valueDate <= DATE(:today)";``` but this would imply many changes to the code.
Test #2 is (the only one that failed and) the option I would like to follow, as it implies the less amount of changes. However, I can not seem to make it work.


? Question ?


Is there a way to use MySQL and the MariaDB connector without causing this problems? 
Is there a better option than casting ```DATE(:today)``` all the parameters to date?
Another solution? Thank you.




Update:

These are the data source properties set in the code:

```dataSource.setJdbcUrl("jdbc:mysql://"+ hostname + ":3306/"
            + databaseName +
            "?useUnicode=true&amp;amp;" +
            "characterEncoding=utf-8");
```



Update #2:

More information:

The followings sets were also executed:

```ALTER DATABASE km CHARACTER SET utf8 COLLATE utf8_general_ci;
SET collation_connection = 'utf8_general_ci';
SET collation_server = 'utf8_general_ci';
```

Also, every ```INFORMATION_SCHEMA.COLUMNS``` and ```INFORMATION_SCHEMA.TABLES``` was  ```CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;```.



@elenst solution steps:


Enabled general_log;
Confirmed the values for ```NAMES``` and ```character_set_results``` are the same (latin1 and NULL) when using MySQL connector; 
Switched back to MariaDB and set these values
Run with application/query and the error still persists. 
I also tried with```?sessionVariables=character_set_client=latin1```, and ```?sessionVariables=character_set_client=utf8```, and the result was the same.


@DiegoDupin Cannot apply a ```Timestamp.valueOf()``` to a ```Joda Time LocalDateTime```. You can wrap it: 


```setParameter("today", Timestamp.valueOf(String.valueOf(new LocalDateTime(d.getYear(), d.getMonthOfYear(), d.getDayOfMonth(), 23, 59))))``` 
but it results in a Timestamp format error. 
```setParameter("today", new LocalDateTime(d.getYear(), d.getMonthOfYear(), d.getDayOfMonth(), 23, 59).toDate())``` will however work.


The question remains, other parts of the system, due to the driver change, can still share this the fault.



@RickJames: ```SHOW CREATE TABLE acc_sub_account_booking```. The comparison is done on the ```valueDate``` field, type ```date```, although the same happens for a ```datetime``` field present in another (similar) query.

```| acc_sub_account_booking | CREATE TABLE `acc_sub_account_booking` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `creationDate` datetime DEFAULT NULL,
  `lastModifiedDate` datetime DEFAULT NULL,
  `bookingText` varchar(255) DEFAULT NULL,
  `bookingType` varchar(255) DEFAULT NULL,
  `uuid` varchar(255) DEFAULT NULL,
  `value` decimal(19,2) DEFAULT NULL,
  `valueDate` date DEFAULT NULL,
  `zkaGVC` int(4) NOT NULL,
  `subAccount_id` bigint(20) DEFAULT NULL,
  `bankStatementDate` date DEFAULT NULL,
  `counterpartHolder` varchar(255) DEFAULT NULL,
  `counterpartIban` varchar(255) DEFAULT NULL,
  `customerReference` varchar(255) DEFAULT NULL,
  `endToEndReference` varchar(255) DEFAULT NULL,
  `returnReason` varchar(255) DEFAULT NULL,
  `customerSpecificInformations` text,
  `counterpartBic` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uuid` (`uuid`),
  KEY `FK_SAB_SA` (`subAccount_id`),
  CONSTRAINT `FK_SAB_SA` FOREIGN KEY (`subAccount_id`) REFERENCES `acc_sub_account` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3407 DEFAULT CHARSET=utf8 |
```



Final Update:

The problem listed here can be solved by casting to a Java ```Date``` object, instead of directly using ```JodaTime```:

```setParameter("today", new LocalDateTime(d.getYear(), d.getMonthOfYear(), d.getDayOfMonth(), 23, 59).toDate())
```

Nonetheless, other problems were found and in the end, my company decided to revert their decision of using the MariaDB driver.

A very big thank you to all who offer their time to try to help.

Comment: Could you enable the query log and see what is actually being received on the server side? LocalDateTime is not handled in the driver , maybe it is handled by Spring somehow, not familiar with that.  But maybe you are better off using java.sql.Timestamp or something like that.

Comment: Well, it is prepared statement then, server side. You can use useServerPrepStmts=false  in the JDBC URL to ensure client-side prepare.but it will show you serialized binary LocalDateTime probably. because this is how  setObject in this driver works for unknown types.

Comment: @VladislavVaintroub the query log doesn't show the "translated" query:

    15 Prepare select coalesce(sum(u.betrag), 0) from km_rented_object ro   join km_cash_bond cb ON cb.rentedObject_id = ro.id    join konto k ON k.uuid = cb.truster_account_uuid    join umsatz u ON u.konto_id = k.id where ro.id = ? and u.vorfallKennung in (100, 200) and u.buchungszeitpunkt <= ?
    15 Query SELECT 1
    15 Query ROLLBACK
    15 Query set autocommit=1` 

That is what I find strange.

Comment: @RickJames `valueDate`, you were right. I wrote the correct one.

Comment: What is the datatype of `valueDate`?  (Or `SHOW CREATE TABLE`)  Can you display the sql after `:today` has been substituted?

Comment: `buchungszeitpunkt`?  or `b.valueDate`??

Here is the accepted answer: Query.setParameter rely on  PrepareStatement.setObject(...)

MariaDB jdbc driver doesn't handle LocalDateTime object in setObject
I just create this issue for handling that. 

A workaround is to convert LocalDateTime to Timestamp :

```Object sum1 = entityManager.createNativeQuery(sql).
            setParameter("rentedObjectId", pRentedObject.getId()).
            setParameter("today", Timestamp.valueOf(new LocalDateTime(d.getYear(), d.getMonthOfYear(), d.getDayOfMonth(), 23, 59))).
            getSingleResult());
```

Edit : 

If LocalDateTime correspond to java.time.LocalDateTime, then using Timestamp.valueOf((LocalDateTime)x) is a workaround. 

If LocalDateTime correspond to org.joda.time.LocalDateTime, then toDate() is a solution. 

MySQL driver in this particular case works the same way than MariaDB, if Object class is unknown, Object will be serialized and send to server. Since org.joda.time.LocalDateTime as you imagine is not defined in JDBC, you must already face some surprise here. Data send to server is not a temporal value. 

Comment for this answer: Maybe in MySQL it was serialized with toString() ;) I think , for JDBC it is appropriate to either use native JDBC types in setObject, java.sql.Timestamp for example, or native JDK types, like java.util.Date, or (sigh) LocalDateTime, not the Joda, the Java8 one
Comment for this answer: Can't apply a `Timestamp.valueOf()` to a Joda Time `LocalDateTime`. You can wrap it like so: 
`setParameter("today", Timestamp.valueOf(String.valueOf(new LocalDateTime(d.getYear(), d.getMonthOfYear(), d.getDayOfMonth(), 23, 59))))` but it results in a Timestamp format error. 

From my tests, the parameters must either be a `String` or a `java.util.Date`. That would solve this situation, but I don't know how many more can exist.
Comment for this answer: _After Edit:_ I agree with you. It should have been defined as `Date` from the start. Now, `toDate()` would indeed solve this problem, but there isn't a guarantee there aren't others similar cases. I understand MariaDB isn't simply a drop-in, but the question remains of how was this query working with the MySQL driver? And why it isn't with the MariaDB driver?
Comment for this answer: @VladislavVaintroub That - "serialized with toString()" - is also my guess.  You are absolutely right about using the native JDBC types. I wasn't aware of JodaTime been used in the PrepStmts (new to the company). Because it worked, everyone must have thought it's ok... But now it doesn't work, so let the fixing begin :)

Here is another answer: IMPORTANT NOTE:

Everywhere in the text and code lines below ```latin1``` is just an example, while the actual character set (and possibly collation) values will need to be chosen based on the information found in the log as described in the experiment steps.



When there is a difference related to character sets and collations between MariaDB and MySQL Connector/J in otherwise identical conditions, it's often because MySQL Connector/J can automatically execute these ```SET``` statements upon a new connection:

```SET NAMES <character set&gt;;
SET character_set_results = NULL;
```

and/or

```SET character_set_results = <character set&gt;;
SET collation_connection = <collation&gt;;
```

For the latter two, there should be corresponding connection properties, so they are more obvious. For the first two, the algorithm is more obscure. 

MariaDB connector does not do it, it will just set session variables provided in connection properties.

The easy way to discover the exact difference is this:

Enable general log on the server (```SET GLOBAL general_log=1```); 
Run the script using MySQL Connector/J;
Check the general log, find the beginning of a connection, it should look somewhat like this:

```   99 Query     /* mysql-connector-java-5.1.39 ( Revision: 3289a357af6d09ecc1a10fd3c26e95183e5790ad ) */SELECT  @@session.auto_increment_increment AS auto_increment_increment, @@character_set_client AS character_set_client, @@character_set_connection AS character_set_connection, @@character_set_results AS character_set_results, @@character_set_server AS character_set_server, @@init_connect AS init_connect, @@interactive_timeout AS interactive_timeout, @@license AS license, @@lower_case_table_names AS lower_case_table_names, @@max_allowed_packet AS max_allowed_packet, @@net_buffer_length AS net_buffer_length, @@net_write_timeout AS net_write_timeout, @@query_cache_size AS query_cache_size, @@query_cache_type AS query_cache_type, @@sql_mode AS sql_mode, @@system_time_zone AS system_time_zone, @@time_zone AS time_zone, @@tx_isolation AS tx_isolation, @@wait_timeout AS wait_timeout
   99 Query     SET NAMES latin1
   99 Query     SET character_set_results = NULL
   99 Query     SET autocommit=1
   ...
```

(values in ```SET```s  can differ).
Then, for an experiment, add the exact same statements to be executed directly from your code right after establishing connection, e.g.

```con= DriverManager.getConnection(...);
Statement st= con.createStatement();
st.execute("SET NAMES latin1");
st.execute("SET character_set_results = NULL");
```

(or whatever better syntax you would use, and of course with the same values that you've seen in the general log). 

Recompile and run now with MariaDB Connector/J. Check the log, make sure the statements are executed. Check results.

If the difference in behavior between MySQL and MariaDB connectors is now gone, you have found the reason. 

You can further narrow it down by trying to remove ```character_set_results``` which is likely be unimportant, and replacing ```SET NAMES``` by setting actual session variables, starting with ```character_set_client```. 

Finally, in MariaDB connector session variables can be configured by adding them to the connection line as

```"jdbc:mysql://localhost:3306/test?sessionVariables=character_set_client=latin1"
```

etc.

Comment for this answer: jdbc:mysql://localhost:3306/test?sessionVariables=character_set_client=latin1 is not a good advice. This connector assumes everything to be UTF8, mb4 or not
Comment for this answer: @RickJames, is it a bug if you do not have to have "useUnicode=yes&characterEncoding=UTF-8" to use Unicode? But it definitely a bug if one cannot compare *dates* because of "illegal mix of collations".date is not text data
Comment for this answer: driver announces by default UTF8mb3, and does not say it is latin1. 
 
jdbc:mysql://localhost:3306/test?sessionVariables=character_‌​set_client=latin1 would unfortunately announce latin1, connector continues talking UTF8, still
Comment for this answer: Driver sets collation id to 33 or 45 in the client authentication packet, which, according to https://mariadb.com/kb/en/mariadb/supported-character-sets-and-collations/, corresponds to utf8_general_ci or utf8mb4_general_ci . 45(utf8mb4_general_ci) is used only if server sends this collation in its initial authentication packet.
Comment for this answer: There is an easy explanation about confusion. In the context of this specific driver, this example , as written, will easily cause data corruption, whenever any non-ASCII character would be involved.This driver always talks UTF8 to the server, and expects to read UTF8 from the server. This is, perhaps, the biggest difference between MySQL's and MariaDB's drivers.  As to why MySQL driver does a lot SET commands with charsets , I do not know really know, maybe this is historical. They could  just place collation id into authentication packet, like MariaDB driver does it.
Comment for this answer: These are **MySQL** connector options, **MariaDB** connector/J doesn't have them.
Comment for this answer: There has already been [at least one](https://jira.mariadb.org/browse/CONJ-18). Maybe if people report more, the decision will be reconsidered; but the thing is, MariaDB Connector/J was never positioned as a drop-in replacement for MySQL Connector/J, it doesn't provide identical functionality. [It was developed specifically as a **lightweight** JDBC connector](https://mariadb.com/kb/en/mariadb/about-mariadb-connector-j/).
Comment for this answer: I have no idea why the use of `latin1`  in the text has caused so much confusion, as the text said a few times that it's an example (which might work in some cases, but obviously not universally), while actual values need to be find out, and the whole text was about how to do it; but since it *has* caused the confusion, I've just added one more note at the beginning of the text to emphasize this particular point. Hope it will put the "latin1 is bad  for your health" kimanthony@example.net. There were no other changes in the text.
Comment for this answer: Update 2 has more informations and detailed responses to the comments so far. Thank you.
Comment for this answer: @RickJames I'm not sure I fully understand your question - How are the bytes encoded in the client? - but I believe so. We have to deal with german characters so we have to encode them using UTF-8.
Comment for this answer: @RickJames  `SHOW CREATE TABLE` added to the question.
Comment for this answer: If you are using utf8/utf8mb4, then I think the connection line needs two things:  `useUnicode=yes&characterEncoding=UTF-8`
Comment for this answer: Ouch, that's naughty.  File a bug with MariaDB.
Comment for this answer: Things are moving toward `utf8mb4` as being the preferred `CHARACTER SET` (aka "UTF-8" outside MySQL).  I would hope that all connectors, etc, are in that frame of mind.
Comment for this answer: I added my 2-cents to that "closed" bug.
Comment for this answer: How are the bytes encoded in the client?  If they are UTF-8, then do _not_ say that the client is using latin1.
Comment for this answer: "Illegal mix of _collations_" implies a COLLATION problem, not a CHARACTER SET problem.  So, I again ask for the info in the my comment on the Question.
Comment for this answer: The German `ß`, when encoded in latin1 is one byte, hex `DF`.  In utf8 it is 2 bytes, hex `C39F`.  If `C39F` is treated as latin1, it displays as `ÃŸ`.  If you can display the hex for a non-ascii character in your client, that would help in debugging.
Comment for this answer: Update 2 mentions a lot of things, but I still want to see what the result was; please provide `SHOW CREATE TABLE`.

Here is another answer: A likely workaround:

Change ```b.valueDate <= :today``` to ```b.valueDate < CURDATE() + INTERVAL 1 DAY```


{'source': 'StackExchangeDataset', 'subset': 'stackoverflow.com', 'metadata': {'AnswerCount': 3, 'CommentCount': 6, 'ContentLicense': 'CC BY-SA 3.0', 'CreationDate': '2017:00:01/10/17', 'FavoriteCount': None, 'PostTypeId': 1, 'Tags': '<java><mysql><jdbc><database-connection><mariadb>', 'Title': 'MySQL Server with MariaDB driver produces dates collation error', 'ViewCount': 1488, 'id': 41567258, 'user_AboutMe': '', 'user_AccountId': 1523104.0, 'user_DisplayName': 'Rui Rebelo Brito', 'user_Reputation': 163.0, 'user_Views': 61.0, 'user_id': 1422238.0}}