mysql infobright load_mycat对Infobright支持情况,测试load data数据异常,求解决办法或思路....

weixin_39885690

于 2021-02-07 08:43:40 发布

阅读量158

点赞数

文章标签： mysql infobright load

本文链接：https://blog.csdn.net/weixin_39885690/article/details/113907839

版权

本文详细描述了在不同环境（CentOS和Windows）下，尝试将数据从MyISAM迁移到Infobright-BRIGHTHOUSE引擎时遇到的字符集问题，涉及loaddata操作失败。作者分析了错误日志，探讨了可能的原因并寻求解决思路。

摘要由CSDN通过智能技术生成

测试环境1：

OS： CentOS6.7

mycat： 1.6/1.5

Infobright： infobright-4.0.7-0-x86_64-ice.rpm

测试环境2：

OS： windows7_64

mycat： 1.6-release (Eclipse源码调试)

Infobright： infobright-4.0.7-0-win_64-ice.exe

数据表：

CREATE TABLEloga(idint(23) DEFAULT NULL,create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

) ENGINE=BRIGHTHOUSE DEFAULT CHARSET=utf8;

CREATE TABLE logb (

id int(23) DEFAULT NULL,

create_time timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

) ENGINE=MyISAM DEFAULT CHARSET=utf8;

测试数据：

1001;“2016-10-01 12:12:12”

1002;“2016-11-01 12:12:12”

1003;“2016-12-01 12:12:12”

server.xml

utf8

......

root123

mycat

rule.xml

create_time

partbymonth

yyyy-MM-dd HH:mm:ss

2016-10-01 00:00:00

schema.xml

writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">

select user()

writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">

select user()

writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">

select user()

客户端连接：

mysql -h192.168.0.102 -P8066 -uroot -proot123 -C --local-infile=1 --default-character-set=utf8 -Dmycat

查看字符集：

+--------------------------+-----------

| 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 | C:\Program

+--------------------------+-----------

load data：

LOAD DATA LOCAL INFILE 'c:/log.log' IGNORE INTO TABLE logtest CHARACTER SET 'utf8' FIELDS TERMINATED BY ';' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\r\n' (id, create_time);

测试结果：

分别尝试了1.5和1.6版本，window和linux环境，

1.向MyISAM引擎表logb中load数据正常，能正常分片。

2.向BRIGHTHOUSE引擎表loga中load数据失败：Wrong data or column definition. Row: 1, field: 2.

基本排除mycat的load data语法原因，文档看了好多遍了；

简单跟了下代码，没有发现问题，与logb表处理一致。

日志：

2016-10-14 23:03:03,305 [DEBUG][$_NIOREACTOR-2-RW] ServerConnection [id=2, schema=mycat, host=192.168.0.102, user=root,txIsolation=3, autocommit=true, schema=mycat] LOAD DATA LOCAL INFILE 'c:/log.log' IGNORE INTO TABLE logtest CHARACTER SET 'utf8' FIELDS TERMINATED BY ';' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\r\n' (id, create_time) (io.mycat.net.FrontendConnection:FrontendConnection.java:288)

2016-10-14 23:03:03,305 [DEBUG][$_NIOREACTOR-2-RW] ServerConnection [id=2, schema=mycat, host=192.168.0.102, user=root,txIsolation=3, autocommit=true, schema=mycat]LOAD DATA LOCAL INFILE 'c:/log.log' IGNORE INTO TABLE logtest CHARACTER SET 'utf8' FIELDS TERMINATED BY ';' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\r\n' (id, create_time) (io.mycat.server.ServerQueryHandler:ServerQueryHandler.java:57)

2016-10-14 23:03:03,325 [DEBUG][$_NIOREACTOR-2-RW] ServerConnection [id=2, schema=mycat, host=192.168.0.102, user=root,txIsolation=3, autocommit=true, schema=mycat]LOAD DATA LOCAL INFILE 'c:/log.log' IGNORE INTO TABLE logtest CHARACTER SET 'utf8' COLUMNS TERMINATED BY ';' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '

' (id, create_time), route={

1 -> dn2{LOAD DATA LOCAL INFILE 'c:/log.log' IGNORE INTO TABLE logtest CHARACTER SET 'utf8' COLUMNS TERMINATED BY ';' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '

' (id, create_time)}

2 -> dn3{LOAD DATA LOCAL INFILE 'c:/log.log' IGNORE INTO TABLE logtest CHARACTER SET 'utf8' COLUMNS TERMINATED BY ';' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '

' (id, create_time)}

3 -> dn1{LOAD DATA LOCAL INFILE 'c:/log.log' IGNORE INTO TABLE logtest CHARACTER SET 'utf8' COLUMNS TERMINATED BY ';' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '

' (id, create_time)}

} rrs (io.mycat.server.NonBlockingSession:NonBlockingSession.java:119)

2016-10-14 23:03:03,326 [DEBUG][$_NIOREACTOR-2-RW] execute mutinode query LOAD DATA LOCAL INFILE 'c:/log.log' IGNORE INTO TABLE logtest CHARACTER SET 'utf8' COLUMNS TERMINATED BY ';' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '

' (id, create_time) (io.mycat.backend.mysql.nio.handler.MultiNodeQueryHandler:MultiNodeQueryHandler.java:101)

2016-10-14 23:03:03,327 [DEBUG][$_NIOREACTOR-2-RW] rrs.getRunOnSlave()-null (io.mycat.backend.mysql.nio.handler.MultiNodeQueryHandler:MultiNodeQueryHandler.java:170)

2016-10-14 23:03:03,327 [DEBUG][$_NIOREACTOR-2-RW] node.getRunOnSlave()1-null (io.mycat.backend.mysql.nio.handler.MultiNodeQueryHandler:MultiNodeQueryHandler.java:185)

2016-10-14 23:03:03,328 [DEBUG][$_NIOREACTOR-2-RW] node.getRunOnSlave()2-null (io.mycat.backend.mysql.nio.handler.MultiNodeQueryHandler:MultiNodeQueryHandler.java:187)

2016-10-14 23:03:03,328 [DEBUG][$_NIOREACTOR-2-RW] rrs.getRunOnSlave() null (io.mycat.backend.datasource.PhysicalDBNode:PhysicalDBNode.java:96)

2016-10-14 23:03:03,328 [DEBUG][$_NIOREACTOR-2-RW] rrs.getRunOnSlave() null (io.mycat.backend.datasource.PhysicalDBNode:PhysicalDBNode.java:127)

2016-10-14 23:03:03,331 [DEBUG][$_NIOREACTOR-2-RW] node.getRunOnSlave()1-null (io.mycat.backend.mysql.nio.handler.MultiNodeQueryHandler:MultiNodeQueryHandler.java:185)

2016-10-14 23:03:03,331 [DEBUG][$_NIOREACTOR-2-RW] node.getRunOnSlave()2-null (io.mycat.backend.mysql.nio.handler.MultiNodeQueryHandler:MultiNodeQueryHandler.java:187)

2016-10-14 23:03:03,331 [DEBUG][$_NIOREACTOR-2-RW] rrs.getRunOnSlave() null (io.mycat.backend.datasource.PhysicalDBNode:PhysicalDBNode.java:96)

2016-10-14 23:03:03,331 [DEBUG][$_NIOREACTOR-2-RW] rrs.getRunOnSlave() null (io.mycat.backend.datasource.PhysicalDBNode:PhysicalDBNode.java:127)

2016-10-14 23:03:03,332 [DEBUG][$_NIOREACTOR-2-RW] node.getRunOnSlave()1-null (io.mycat.backend.mysql.nio.handler.MultiNodeQueryHandler:MultiNodeQueryHandler.java:185)

2016-10-14 23:03:03,332 [DEBUG][$_NIOREACTOR-2-RW] node.getRunOnSlave()2-null (io.mycat.backend.mysql.nio.handler.MultiNodeQueryHandler:MultiNodeQueryHandler.java:187)

2016-10-14 23:03:03,333 [DEBUG][$_NIOREACTOR-2-RW] rrs.getRunOnSlave() null (io.mycat.backend.datasource.PhysicalDBNode:PhysicalDBNode.java:96)

2016-10-14 23:03:03,333 [DEBUG][$_NIOREACTOR-2-RW] rrs.getRunOnSlave() null (io.mycat.backend.datasource.PhysicalDBNode:PhysicalDBNode.java:127)

2016-10-14 23:03:03,446 [DEBUG][$_NIOREACTOR-0-RW] release connection MySQLConnection [id=16, lastTime=1476457383311, user=root, schema=db2, old shema=db2, borrowed=true, fromSlaveDB=false, threadId=47, charset=utf8, txIsolation=3, autocommit=true, attachment=dn2{LOAD DATA LOCAL INFILE 'c:/log.log' IGNORE INTO TABLE logtest CHARACTER SET 'utf8' COLUMNS TERMINATED BY ';' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '

' (id, create_time)}, respHandler=io.mycat.backend.mysql.nio.handler.MultiNodeQueryHandler@59103246, host=localhost, port=3306, statusSync=null, writeQueue=0, modifiedSQLExecuted=true] (io.mycat.server.NonBlockingSession:NonBlockingSession.java:354)

2016-10-14 23:03:03,446 [WARN ][$_NIOREACTOR-0-RW] error response from MySQLConnection [id=16, lastTime=1476457383444, user=root, schema=db2, old shema=db2, borrowed=false, fromSlaveDB=false, threadId=47, charset=utf8, txIsolation=3, autocommit=true, attachment=null, respHandler=null, host=localhost, port=3306, statusSync=null, writeQueue=0, modifiedSQLExecuted=false] err Wrong data or column definition. Row: 1, field: 2. code:2 (io.mycat.backend.mysql.nio.handler.MultiNodeHandler:MultiNodeHandler.java:135)

2016-10-14 23:03:03,457 [DEBUG][$_NIOREACTOR-2-RW] release connection MySQLConnection [id=22, lastTime=1476457383311, user=root, schema=db1, old shema=db1, borrowed=true, fromSlaveDB=false, threadId=53, charset=utf8, txIsolation=3, autocommit=true, attachment=dn1{LOAD DATA LOCAL INFILE 'c:/log.log' IGNORE INTO TABLE logtest CHARACTER SET 'utf8' COLUMNS TERMINATED BY ';' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '

2016-10-14 23:03:03,458 [DEBUG][$_NIOREACTOR-0-RW] release connection MySQLConnection [id=4, lastTime=1476457383311, user=root, schema=db3, old shema=db3, borrowed=true, fromSlaveDB=false, threadId=38, charset=utf8, txIsolation=3, autocommit=true, attachment=dn3{LOAD DATA LOCAL INFILE 'c:/log.log' IGNORE INTO TABLE logtest CHARACTER SET 'utf8' COLUMNS TERMINATED BY ';' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '

2016-10-14 23:03:03,458 [WARN ][$_NIOREACTOR-0-RW] error response from MySQLConnection [id=4, lastTime=1476457383451, user=root, schema=db3, old shema=db3, borrowed=false, fromSlaveDB=false, threadId=38, charset=utf8, txIsolation=3, autocommit=true, attachment=null, respHandler=null, host=localhost, port=3306, statusSync=null, writeQueue=0, modifiedSQLExecuted=false] err Wrong data or column definition. Row: 1, field: 2. code:2 (io.mycat.backend.mysql.nio.handler.MultiNodeHandler:MultiNodeHandler.java:135)

2016-10-14 23:03:03,458 [WARN ][$_NIOREACTOR-2-RW] error response from MySQLConnection [id=22, lastTime=1476457383451, user=root, schema=db1, old shema=db1, borrowed=false, fromSlaveDB=false, threadId=53, charset=utf8, txIsolation=3, autocommit=true, attachment=null, respHandler=null, host=localhost, port=3306, statusSync=null, writeQueue=0, modifiedSQLExecuted=false] err Wrong data or column definition. Row: 1, field: 2. code:2 (io.mycat.backend.mysql.nio.handler.MultiNodeHandler:MultiNodeHandler.java:135)

2016-10-14 23:03:03,458 [DEBUG][$_NIOREACTOR-0-RW] error all end ,clear session resource (io.mycat.backend.mysql.nio.handler.MultiNodeHandler:MultiNodeHandler.java:202)

2016-10-14 23:03:03,458 [INFO ][$_NIOREACTOR-2-RW] close connection,reason:stream closed ,ServerConnection [id=2, schema=mycat, host=192.168.0.102, user=root,txIsolation=3, autocommit=true, schema=mycat] (io.mycat.net.AbstractConnection:AbstractConnection.java:508)

虽然mycat没有指明支持Infobright，且Infobright不在支持社区版，但是使用量还是有的，而且，看这个错误提示应该不会需要重大调整吧？

有没有解决办法或者思路？求帮助...