环境:tomcat6、hibernate3、mysql5把数据库迁移到另一台服务器,出现如下错误:执行下面sql语句得到结果集data:
select count(id), date_format(starttime,'%Y-%m-%d') as date from tb_record where pid in (select id from tb_project where valid=true and name like 'xxx' and starttime between '2009-06-29' and '2009-07-06') group by date;执行如下语句:
if(!((String)(data.get(0)[1])).equals(START_TIME){
....
};抛出异常:
java.lang.ClassCastException: [B cannot be cast to java.lang.String将数据库迁移到其它数据库问题就消失了,
我就不知道这个[B是个什么类型??
toString()得到:“[B@73bc22”
getClass().getName()得到:“[B”请问这个问题的原因可能是什么引起的?怎样修复?这个“[B”是个啥?
select count(id), date_format(starttime,'%Y-%m-%d') as date from tb_record where pid in (select id from tb_project where valid=true and name like 'xxx' and starttime between '2009-06-29' and '2009-07-06') group by date;执行如下语句:
if(!((String)(data.get(0)[1])).equals(START_TIME){
....
};抛出异常:
java.lang.ClassCastException: [B cannot be cast to java.lang.String将数据库迁移到其它数据库问题就消失了,
我就不知道这个[B是个什么类型??
toString()得到:“[B@73bc22”
getClass().getName()得到:“[B”请问这个问题的原因可能是什么引起的?怎样修复?这个“[B”是个啥?
select count(id), date_format(starttime,'%Y-%m-%d') as date from tb_record where pid in (select id from tb_project where valid=true and name like 'xxx' and starttime between '2009-06-29' and '2009-07-06') group by date
我将所有表结构和数据移到另一个服务器,就没有问题。
现在看唯一的区别就是:没有问题的数据库是我自己安装的,出问题的数据库是那台服务器原有的,我直接建库导入的。
show variables like 'char%';
SHOW CREATE TABLE tb_record
SHOW CREATE TABLE tb_project
select count(id), date_format(starttime,'%Y-%m-%d') as date
from tb_record
where pid in (
select id from tb_project w
here valid=true
and name like 'xxx'
and starttime between '2009-06-29' and '2009-07-06') group by date;
在连接正常的数据库时返回的这个字段为String: "2009-06-09"
在连接异常数据库时返回的是byte[10]我也是比较怀疑是字符集问题
+-----------+------------+
| count(id) | date |
+-----------+------------+
| 103 | 2009-06-29 |
| 82 | 2009-06-30 |
| 55 | 2009-07-01 |
| 46 | 2009-07-02 |
| 44 | 2009-07-03 |
| 15 | 2009-07-04 |
| 1 | 2009-07-05 |
+-----------+------------+
7 rows in set (1.79 sec)
试一下,是不是你的java的问题mysql> select 1,curdate();
+---+------------+
| 1 | curdate() |
+---+------------+
| 1 | 2009-07-06 |
+---+------------+
1 row in set (0.05 sec)mysql>
select count(id), date_format(starttime,'%Y-%m-%d') as `date`
from tb_record
where pid in (
select id from tb_project w
here valid=true
and name like 'xxx'
and starttime between '2009-06-29' and '2009-07-06') group by `date`;
java的问题是啥意思?这个程序已经正常跑了半年了,就是换数据库会出错。回wwwwb,把date改成datee了没效果~现在最大可能(虽然可能也不是很大)就是两个版本的mysql的函数date_format()返回值类型不用,于是分别被hibernate映射为String和byte[]类型
SELECT date_format(starttime,'%Y-%m-%d') from tt
看看结果如何
试了,返回的结果依然被hibernate转成了byte[10]
+----------------------+
| version() |
+----------------------+
| 5.1.33-community-log |
+----------------------+
1 row in set (0.00 sec)mysql> show variables like 'char%';
+--------------------------+----------------------------------
| Variable_name | Value
+--------------------------+----------------------------------
| character_set_client | latin1
| character_set_connection | latin1
| character_set_database | latin1
| character_set_filesystem | binary
| character_set_results | latin1
| character_set_server | latin1
| character_set_system | utf8
| character_sets_dir | C:\Program Files\MySQL\MySQL Serv
+--------------------------+----------------------------------
8 rows in set (0.06 sec)mysql> show variables like 'datetime_format';
+-----------------+-------------------+
| Variable_name | Value |
+-----------------+-------------------+
| datetime_format | %Y-%m-%d %H:%i:%s |
+-----------------+-------------------+
1 row in set (0.00 sec)mysql>
是的,我之前那个sql的执行结果也是这种格式
+------------------+
| version() |
+------------------+
| 5.0.27-community |
+------------------+
1 row in set (0.00 sec)mysql> show variables like 'char%';
+--------------------------+----------------------------------------------------+
| Variable_name | Value |
+--------------------------+----------------------------------------------------+
| character_set_client | utf8 |
| character_set_connection | utf8 |
| character_set_database | latin1 |
| character_set_filesystem | binary |
| character_set_results | utf8 |
| character_set_server | latin1 |
| character_set_system | utf8 |
| character_sets_dir | \\localhost\e$\InstanstRails\mysql\share\charsets\ |
+--------------------------+----------------------------------------------------+
8 rows in set (0.00 sec)正常服务器:
mysql> select version();
+------------------+
| version() |
+------------------+
| 5.1.30-community |
+------------------+
1 row in set (0.01 sec)mysql> show variables like 'char%';
+--------------------------+---------------------------+
| Variable_name | Value |
+--------------------------+---------------------------+
| character_set_client | utf8 |
| character_set_connection | utf8 |
| character_set_database | utf8 |
| character_set_filesystem | binary |
| character_set_results | utf8 |
| character_set_server | utf8 |
| character_set_system | utf8 |
| character_sets_dir | E:\MySQL5\share\charsets\ |
+--------------------------+---------------------------+
8 rows in set (0.00 sec)
不是那条记录的问题,从mysql直接执行开看,两个服务器返回的都是"0000-00-00"这种格式,
但hibernate解释后的类型一个是String,一个是byte[10]
我怀疑是不是问题版本的mysql的date_format返回的不是varchar而是其它什么类型,所以被hibernate映射成了byte数组
[mysql]default-character-set=utf8
or
set names utf8
connector是一样的呀...没有换过,5.x的,不知道是不是最新,反正是相当新了
2. 如果上述不对,则 select '2009-07-06';看你的java返回是什么 bye[] or string?另外注意一下你的 字符集并不完全一致,建议都改成 utf8mysql> show variables like 'char%';
+--------------------------+----------------------------------------------------+
| Variable_name | Value |
+--------------------------+----------------------------------------------------+
| character_set_client | utf8 |
| character_set_connection | utf8 |
| character_set_database | latin1 |
| character_set_filesystem | binary |
| character_set_results | utf8 |
| character_set_server | latin1 |
| character_set_system | utf8 |
| character_sets_dir | \\localhost\e$\InstanstRails\mysql\share\charsets\ |
+--------------------------+----------------------------------------------------+
初步认为是mysql配置差异或版本差异造成的,如果是版本差异我解决不了,
如果是配置差异,由于5.0那个mysql上还有其它重要业务不敢动,目前只能土土的跑两个mysql了~谢谢两位热心帮助,我后续再看看问题原因到底在哪里:)
要在新字符集下重新插入记录