Mycat学习笔记 番外篇一.客户端使用latin1字符集，后端MySQL为UTF8字符集，MyCat日志分析。

其实这个番外篇比较无聊——即客户端为lantin字符集，后面MySQL为U8字符集，MyCat在中间到底会起什么作用。

再说下本次验证的环境：

Mac OS 10.11.2   

MySQL 5.6

MyCat 1.5 

OK，开始我们的验证工作。

 

1) 由于数据库与操作系统已被默认设置为U8编码，计划通过修改Mysql  “character_set_client” 参数调整客户端字符集配置进行验证。

关于MYSQL字符集较全面的介绍，请参考 《mysql_query("set names gb2312")设置客户端字符集》 传送门

-- 查看当前系统的字符集

SHOW VARIABLES LIKE 'character%';

-- 查看当前客户端字符集

show variables like 'character_set_client';

OK，确认本地客户端字符集为U8后，使用以下命令调为 latin1 （iso8859-1）

set names latin1

2)来看下mycat 日志在 select 的情况，根据下面红色标出的文字内容，推测Mycat每次从Connection Pool获取后端连接时会去自动同步数据库连接字符集，即将本地Client连到Mycat服务的字符集同步至MyCat与MySQL的连接中。

另外MyCat 默认情况使用U8字符集与后端MySQL通信，可以在Release connection步骤中体现。

即默认情况下都使用U8与MySQL交互，客户端可以指定其他的字符。

正常情况下大家都会使用U8，当然也可通过MYCAT server.xml 配置进行调整。

 

<system> <property name="charset">utf8</property> </system>

 

01/21 22:25:53.174  DEBUG [$_NIOREACTOR-2-RW] (ServerQueryHandler.java:56) -ServerConnection [id=2, schema=TESTDB, host=127.0.0.1, user=test,txIsolation=3, autocommit=true, schema=TESTDB]select * from t_demo_travel_record

LIMIT 0, 1000

 

01/21 22:25:53.174  DEBUG [$_NIOREACTOR-2-RW] (EnchachePool.java:70) -SQLRouteCache hit cache ,key:TESTDBselect * from t_demo_travel_record

LIMIT 0, 1000

 

01/21 22:25:53.175  DEBUG [$_NIOREACTOR-2-RW] (NonBlockingSession.java:113) -ServerConnection [id=2, schema=TESTDB, host=127.0.0.1, user=test,txIsolation=3, autocommit=true, schema=TESTDB]select * from t_demo_travel_record

LIMIT 0, 1000, route={

   1 -> dn1{SELECT *

FROM t_demo_travel_record

LIMIT 0, 1000}

   2 -> dn2{SELECT *

FROM t_demo_travel_record

LIMIT 0, 1000}

} rrs 

01/21 22:25:53.175  DEBUG [$_NIOREACTOR-2-RW] (MultiNodeQueryHandler.java:82) -execute mutinode query select * from t_demo_travel_record

LIMIT 0, 1000

01/21 22:25:53.176  DEBUG [$_NIOREACTOR-2-RW] (MultiNodeQueryHandler.java:97) -has data merge logic 

01/21 22:25:53.176  DEBUG [$_NIOREACTOR-2-RW] (PhysicalDBPool.java:452) -select read source hostM1 for dataHost:localhost1

01/21 22:25:53.176  DEBUG [$_NIOREACTOR-2-RW] (MySQLConnection.java:445) -con need syn ,total syn cmd 1 commands SET names latin1;schema change:false con:MySQLConnection [id=11, lastTime=1453386353176, user=mycat1, schema=mycat_db1, old shema=mycat_db1, borrowed=true, fromSlaveDB=false, threadId=13, charset=latin1, txIsolation=3, autocommit=true, attachment=dn1{SELECT *

FROM t_demo_travel_record

LIMIT 0, 1000}, respHandler=org.opencloudb.mysql.nio.handler.MultiNodeQueryHandler@5f81c977, host=localhost, port=3306, statusSync=null, writeQueue=0, modifiedSQLExecuted=false]

01/21 22:25:53.177  DEBUG [$_NIOREACTOR-2-RW] (PhysicalDBPool.java:452) -select read source hostM1 for dataHost:localhost2

01/21 22:25:53.177  DEBUG [$_NIOREACTOR-3-RW] (MultiNodeQueryHandler.java:171) -received ok response ,executeResponse:false from MySQLConnection [id=11, lastTime=1453386353165, user=mycat1, schema=mycat_db1, old shema=mycat_db1, borrowed=true, fromSlaveDB=false, threadId=13, charset=latin1, txIsolation=3, autocommit=true, attachment=dn1{SELECT *

FROM t_demo_travel_record

LIMIT 0, 1000}, respHandler=org.opencloudb.mysql.nio.handler.MultiNodeQueryHandler@5f81c977, host=localhost, port=3306, statusSync=org.opencloudb.mysql.nio.MySQLConnection$StatusSync@41d1d49d, writeQueue=0, modifiedSQLExecuted=false]

01/21 22:25:53.177  DEBUG [$_NIOREACTOR-2-RW] (MySQLConnection.java:445) -con need syn ,total syn cmd 1 commands SET names latin1;schema change:false con:MySQLConnection [id=9, lastTime=1453386353177, user=mycat2, schema=mycat_db2, old shema=mycat_db2, borrowed=true, fromSlaveDB=false, threadId=6, charset=latin1, txIsolation=3, autocommit=true, attachment=dn2{SELECT *

FROM t_demo_travel_record

LIMIT 0, 1000}, respHandler=org.opencloudb.mysql.nio.handler.MultiNodeQueryHandler@5f81c977, host=localhost, port=3306, statusSync=null, writeQueue=0, modifiedSQLExecuted=false]

01/21 22:25:53.178  DEBUG [$_NIOREACTOR-3-RW] (DataMergeService.java:94) -field metadata inf:[ID=ColMeta [colIndex=0, colType=3], CONTEXT=ColMeta [colIndex=1, colType=253], SHARDING_ID=ColMeta [colIndex=2, colType=3]]

01/21 22:25:53.179  DEBUG [$_NIOREACTOR-1-RW] (MultiNodeQueryHandler.java:171) -received ok response ,executeResponse:false from MySQLConnection [id=9, lastTime=1453386353165, user=mycat2, schema=mycat_db2, old shema=mycat_db2, borrowed=true, fromSlaveDB=false, threadId=6, charset=latin1, txIsolation=3, autocommit=true, attachment=dn2{SELECT *

FROM t_demo_travel_record

LIMIT 0, 1000}, respHandler=org.opencloudb.mysql.nio.handler.MultiNodeQueryHandler@5f81c977, host=localhost, port=3306, statusSync=org.opencloudb.mysql.nio.MySQLConnection$StatusSync@53ad876a, writeQueue=0, modifiedSQLExecuted=false]

01/21 22:25:53.179  DEBUG [$_NIOREACTOR-3-RW] (MultiNodeQueryHandler.java:241) -on row end reseponse MySQLConnection [id=11, lastTime=1453386353165, user=mycat1, schema=mycat_db1, old shema=mycat_db1, borrowed=true, fromSlaveDB=false, threadId=13, charset=latin1, txIsolation=3, autocommit=true, attachment=dn1{SELECT *

FROM t_demo_travel_record

LIMIT 0, 1000}, respHandler=org.opencloudb.mysql.nio.handler.MultiNodeQueryHandler@5f81c977, host=localhost, port=3306, statusSync=org.opencloudb.mysql.nio.MySQLConnection$StatusSync@41d1d49d, writeQueue=0, modifiedSQLExecuted=false]

....

 

01/21 22:28:52.877 DEBUG [$_NIOREACTOR-2-RW] (PhysicalDatasource.java:403) -release channel MySQLConnection [id=2, lastTime=1453386532857, user=mycat2, schema=mycat_db2, old shema=mycat_db2, borrowed=true, fromSlaveDB=false, threadId=2, charset=utf8, txIsolation=3, autocommit=true, attachment=null, respHandler=null, host=localhost, port=3306, statusSync=null, writeQueue=0, modifiedSQLExecuted=false]
01/21 22:28:59.237 DEBUG [Timer1] (SQLJob.java:85) -con query sql:select user() to con:MySQLConnection [id=7, lastTime=1453386539237, user=mycat2, schema=mycat_db2, old shema=mycat_db2, borrowed=true, fromSlaveDB=false, threadId=4, charset=utf8, txIsolation=3, autocommit=true, attachment=null, respHandler=null, host=localhost, port=3306, statusSync=null, writeQueue=0, modifiedSQLExecuted=false]
01/21 22:28:59.239 DEBUG [Timer1] (SQLJob.java:85) -con query sql:select user() to con:MySQLConnection [id=13, lastTime=1453386539239, user=mycat1, schema=mycat_db1, old shema=mycat_db1, borrowed=true, fromSlaveDB=false, threadId=12, charset=utf8, txIsolation=3, autocommit=true, attachment=null, respHandler=null, host=localhost, port=3306, statusSync=null, writeQueue=0, modifiedSQLExecuted=false]
01/21 22:28:59.239 DEBUG [$_NIOREACTOR-3-RW] (PhysicalDatasource.java:403) -release channel MySQLConnection [id=7, lastTime=1453386539219, user=mycat2, schema=mycat_db2, old shema=mycat_db2, borrowed=true, fromSlaveDB=false, threadId=4, charset=utf8, txIsolation=3, autocommit=true, attachment=null, respHandler=null, host=localhost, port=3306, statusSync=null, writeQueue=0, modifiedSQLExecuted=false]
01/21 22:28:59.240 DEBUG [$_NIOREACTOR-1-RW] (PhysicalDatasource.java:403) -release channel MySQLConnection [id=13, lastTime=1453386539239, user=mycat1, schema=mycat_db1, old shema=mycat_db1, borrowed=true, fromSlaveDB=false, threadId=12, charset=utf8, txIsolation=3, autocommit=true, attachment=null, respHandler=null, host=localhost, port=3306, statusSync=null, writeQueue=0, modifiedSQLExecuted=false]