Python中文网

一个关于 编程问题的解答网站.

有 Java 编程相关的问题?

你可以在下面搜索框中键入要查询的问题!

带有MariaDB驱动程序的java MySQL服务器产生日期排序错误

1 周，2 日 Questions & Answers

背景与；问题

我的公司决定从MySQL驱动程序改为MariaDB驱动程序/连接器，并且仍然使用MySQL Server DB作为Spring应用程序的数据库服务器

在这次迁移过程中，我发现了与驱动程序/连接器如何处理日期有关的问题，这导致了以下错误：

Illegal mix of collations for operation '<='

在运行以下查询时会发生这种情况，请注意查询最后一行的日期比较

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();

版本：

  • 爪哇：7
  • MySQL:5.5
  • Maria Driver:1.4.6

理解

在阅读MySQL documentation之后，它提到：

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.

也许，这个错误以前没有发生过，因为MySQL连接器处理字符串和日期之间的转换，而这个错误现在是因为试图比较两种不同的数据类型

使用MariaDB作为服务器时不会发生此错误，可能Maria中的转换处理是在数据库级别进行的，而不是在连接器中

测试

我认为这是一个编码/字符集问题，并将所有表和列更改为utf8_general_ci这并没有解决问题

我运行了一些测试——在应用程序中运行查询时总是使用MariaDB驱动程序——得到了以下结果：

Table Test Description

  • 尽管使用MariaDB时测试通过，但这不是一个选项

  • 如果查询参数被转换为date:... and b.valueDate <= DATE(:today)";，那么测试#3和4可以工作，但这将意味着对代码进行许多更改

  • 测试#2是（唯一一个失败的测试）我希望遵循的选项，因为它意味着更少的更改。然而，我似乎无法让它工作

？问题

  1. 有没有一种方法可以使用MySQL和MariaDB连接器而不会造成这种问题

  2. 有没有比铸造迄今为止所有的参数更好的选择

  3. 另一个解决方案？谢谢

更新：

以下是代码中设置的数据源属性：

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

更新#2:

更多信息：

还执行了以下几组：

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

而且，每个INFORMATION_SCHEMA.COLUMNS和INFORMATION_SCHEMA.TABLES都是CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;

@elenst解决方案步骤：

  • 启用常规日志
  • 确认使用MySQL连接器时NAMES和character_set_results的值相同（latin1和NULL）
  • 切换回MariaDB并设置这些值
  • 使用应用程序/查询运行时，错误仍然存在
  • 我还尝试了?sessionVariables=character_set_client=latin1和?sessionVariables=character_set_client=utf8，结果是一样的

@DiegoDupin无法将Timestamp.valueOf()应用于Joda Time LocalDateTime。你可以把它包装起来：

  • setParameter("today", Timestamp.valueOf(String.valueOf(new LocalDateTime(d.getYear(), d.getMonthOfYear(), d.getDayOfMonth(), 23, 59)))) 但它会导致时间戳格式错误
  • ^然而，{}将起作用

问题仍然存在，由于驾驶员更换，系统的其他部分仍然可以分担此故障

@RickJames:SHOW CREATE TABLE acc_sub_account_booking。比较是在valueDate字段上进行的，类型为date，但在另一个（类似）查询中存在的datetime字段上也会发生同样的情况

| 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 |

最终更新：

这里列出的问题可以通过强制转换到Java Date对象来解决，而不是直接使用JodaTime：

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

尽管如此，还是发现了其他问题，最终，我的公司决定恢复使用MariaDB驱动程序的决定

非常感谢所有愿意花时间帮助你的人


共 (2) 个答案

  1. # 1 楼答案

    重要提示：

    在latin1下面的文本和代码行中的任何地方都只是一个例子，而实际的字符集（以及可能的排序规则）值将需要根据在日志中找到的信息进行选择，如实验步骤中所述

    当MariaDB和MySQL Connector/J在其他相同条件下在字符集和排序规则方面存在差异时，这通常是因为MySQL Connector/J可以在新连接时自动执行这些SET语句：

    SET NAMES <character set>;
SET character_set_results = NULL;

    和/或

    SET character_set_results = <character set>;
SET collation_connection = <collation>;

    对于后两者，应该有相应的连接属性，因此它们更明显。对于前两种情况，算法更加模糊

    MariaDB connector不会这样做，它只会设置连接属性中提供的会话变量

    找出确切区别的简单方法是：

    在服务器上启用常规日志（SET GLOBAL general_log=1）； 使用MySQL连接器/J运行脚本； 查看常规日志，找到连接的开始，它应该是这样的：

       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
   ...

    （在SET中的值可能不同）。 然后，对于一个实验，添加完全相同的语句，在建立连接后直接从代码中执行，例如：

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

    （或者使用任何更好的语法，当然，使用的值与您在常规日志中看到的值相同）

    使用MariaDB Connector/J重新编译并立即运行。检查日志，确保语句已执行。检查结果

    如果MySQL和MariaDB连接器之间的行为差异现在消失了，那么您已经找到了原因

    您可以通过尝试删除可能不重要的character_set_results，并通过设置实际会话变量来替换SET NAMES，从character_set_client开始

    最后，在MariaDB中，可以通过将连接器会话变量添加到连接线来配置它们，如下所示：

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

    等等

  2. # 2 楼答案

    一个可能的解决办法是：

    将b.valueDate <= :today更改为b.valueDate < CURDATE() + INTERVAL 1 DAY