mysql>explain select * from travelrecord order by id limit 100000,100 ;
+-----------+--------------------------------------------------------+
|DATA_NODE | SQL |
+-----------+--------------------------------------------------------+
|dn1 | SELECT * FROM travelrecordORDER BY id LIMIT 0, 100100 |
|dn2 | SELECT * FROM travelrecordORDER BY id LIMIT 0, 100100 |
|dn3 | SELECT * FROM travelrecordORDER BY id LIMIT 0, 100100 |
+-----------+--------------------------------------------------------+
3rows in set (0.01 sec)
mysql>
1.2看後台mycat.log日志：
02/1417:52:30.103 DEBUG [$_NIOREACTOR-0-RW](ServerQueryHandler.java:56) -ServerConnection [id=3, schema=TESTDB,host=127.0.0.1, user=test,txIsolation=3, autocommit=true, schema=TESTDB]explainselect * from travelrecord order by id limit 1000000,100
02/1417:52:30.103 DEBUG [$_NIOREACTOR-0-RW](EnchachePool.java:76) -SQLRouteCache miss cache ,key:TESTDB select * from travelrecord order by id limit1000000,100
02/1417:52:30.105 DEBUG [$_NIOREACTOR-0-RW] (EnchachePool.java:59)-SQLRouteCache add cache ,key:TESTDB select * from travelrecord order by idlimit 1000000,100 value:select * from travelrecord order by id limit1000000,100, route={
1 -> dn1{SELECT *
FROMtravelrecord
ORDERBY id
LIMIT0, 1000100}
2 -> dn2{SELECT *
FROMtravelrecord
ORDERBY id
LIMIT0, 1000100}
3 -> dn3{SELECT *
FROMtravelrecord
ORDERBY id
LIMIT0, 1000100}
}
02/1417:52:33.880 DEBUG [$_NIOREACTOR-0-RW](ServerQueryHandler.java:56) -ServerConnection [id=3, schema=TESTDB,host=127.0.0.1, user=test,txIsolation=3, autocommit=true, schema=TESTDB]select* from travelrecord order by id limit 1000000,100
02/1417:52:33.881 DEBUG [$_NIOREACTOR-0-RW](EnchachePool.java:76) -SQLRouteCache miss cache ,key:TESTDBselect * from travelrecord order by id limit1000000,100
02/1417:52:33.882 DEBUG [$_NIOREACTOR-0-RW](EnchachePool.java:59) -SQLRouteCache add cache ,key:TESTDBselect * fromtravelrecord order by id limit 1000000,100 value:select * from travelrecordorder by id limit 1000000,100, route={
1 -> dn1{SELECT *
FROMtravelrecord
ORDERBY id
LIMIT0, 1000100}
2 -> dn2{SELECT *
FROMtravelrecord
ORDERBY id
LIMIT0, 1000100}
3 -> dn3{SELECT *
FROMtravelrecord
ORDERBY id
LIMIT0, 1000100}
}
02/1417:52:33.882 DEBUG [$_NIOREACTOR-0-RW](NonBlockingSession.java:113) -ServerConnection [id=3, schema=TESTDB,host=127.0.0.1, user=test,txIsolation=3, autocommit=true, schema=TESTDB]select* from travelrecord order by id limit 1000000,100, route={
1 -> dn1{SELECT *
FROMtravelrecord
ORDERBY id
02/1417:52:33.882 DEBUG [$_NIOREACTOR-0-RW](NonBlockingSession.java:113) -ServerConnection [id=3, schema=TESTDB,host=127.0.0.1, user=test,txIsolation=3, autocommit=true, schema=TESTDB]select* from travelrecord order by id limit 1000000,100, route={
1 -> dn1{SELECT *
FROMtravelrecord
ORDERBY id
LIMIT0, 1000100}
2 -> dn2{SELECT *
FROMtravelrecord
ORDERBY id
LIMIT0, 1000100}
3 -> dn3{SELECT *
FROMtravelrecord
ORDERBY id
LIMIT0, 1000100}
}rrs
02/1417:52:33.882 DEBUG [$_NIOREACTOR-0-RW](MultiNodeQueryHandler.java:82) -execute mutinode query select * from travelrecordorder by id limit 1000000,100
02/1417:52:33.882 DEBUG [$_NIOREACTOR-0-RW](MultiNodeQueryHandler.java:97) -has data merge logic
02/1417:52:33.882 DEBUG [$_NIOREACTOR-0-RW](PhysicalDBPool.java:452) -select read source hostM1 for dataHost:wgq_idc_mon_1_12
02/1417:52:33.883 DEBUG [$_NIOREACTOR-0-RW](PhysicalDBPool.java:452) -select read source hostM1 fordataHost:wgq_idc_mon_1_12
02/1417:52:33.883 DEBUG [$_NIOREACTOR-0-RW](PhysicalDBPool.java:452) -select read source hostM1 for dataHost:wgq_idc_mon_1_12
02/1417:52:33.883 DEBUG [$_NIOREACTOR-3-RW](MultiNodeQueryHandler.java:171) -received ok response ,executeResponse:falsefrom MySQLConnection [id=19, lastTime=1455443553868, user=root, schema=db1, oldshema=db1, borrowed=true, fromSlaveDB=false, threadId=220, charset=latin1,txIsolation=3, autocommit=true, attachment=dn1{SELECT *
FROMtravelrecord
ORDERBY id
LIMIT0, 1000100},respHandler=org.opencloudb.mysql.nio.handler.MultiNodeQueryHandle[email protected],host=110.254.11.111, port=3317, [email protected]7af37,writeQueue=0, modifiedSQLExecuted=false]
02/1417:52:33.884 DEBUG [$_NIOREACTOR-1-RW](MultiNodeQueryHandler.java:241) -on row end reseponse MySQLConnection [id=25,lastTime=1455443553868, user=root, schema=db3, old shema=db3, borrowed=true,fromSlaveDB=false, threadId=227, charset=latin1, txIsolation=3,autocommit=true, attachment=dn3{SELECT *
FROMtravelrecord
02/1417:52:33.883 DEBUG [$_NIOREACTOR-3-RW](MultiNodeQueryHandler.java:171) -received ok response ,executeResponse:falsefrom MySQLConnection [id=19, lastTime=1455443553868, user=root, schema=db1, oldshema=db1, borrowed=true, fromSlaveDB=false, threadId=220, charset=latin1,txIsolation=3, autocommit=true, attachment=dn1{SELECT *
FROMtravelrecord
ORDERBY id
LIMIT0, 1000100},respHandler=org.opencloudb.mysql.nio.handler.MultiNodeQueryHandle[email protected],host=110.254.11.111, port=3317,[email protected]7af37,writeQueue=0, modifiedSQLExecuted=false]
02/1417:52:33.884 DEBUG [$_NIOREACTOR-1-RW](MultiNodeQueryHandler.java:241) -on row end reseponse MySQLConnection [id=25,lastTime=1455443553868, user=root, schema=db3, old shema=db3, borrowed=true,fromSlaveDB=false, threadId=227, charset=latin1, txIsolation=3,autocommit=true, attachment=dn3{SELECT *
FROMtravelrecord
ORDERBY id
LIMIT0, 1000100},respHandler=org.opencloudb.mysql.nio.handler.MultiNodeQueryHandle[email protected],host=110.254.11.111, port=3317, statusSync=null, writeQueue=0,modifiedSQLExecuted=false]
02/1417:52:33.884 DEBUG [$_NIOREACTOR-3-RW](DataMergeService.java:94) -field metadata inf:[ID=ColMeta [colIndex=0,colType=8], DAYS=ColMeta [colIndex=4, colType=3], TRAVELDATE=ColMeta[colIndex=2, colType=10], USER_ID=ColMeta [colIndex=1, colType=253],FEE=ColMeta [colIndex=3, colType=246]]
02/1417:52:33.884 DEBUG [$_NIOREACTOR-1-RW](NonBlockingSession.java:229) -release connection MySQLConnection [id=25,lastTime=1455443553868, user=root, schema=db3, old shema=db3, borrowed=true,fromSlaveDB=false, threadId=227, charset=latin1, txIsolation=3,autocommit=true, attachment=dn3{SELECT *
FROMtravelrecord
ORDERBY id
LIMIT0, 1000100},respHandler=org.opencloudb.mysql.nio.handler.MultiNodeQueryHandle[email protected],host=110.254.11.111, port=3317, statusSync=null, writeQueue=0,modifiedSQLExecuted=false]
02/1417:52:33.885 DEBUG [$_NIOREACTOR-1-RW](PhysicalDatasource.java:403) -release channel MySQLConnection [id=25,lastTime=1455443553868, user=root, schema=db3, old shema=db3, borrowed=true,fromSlaveDB=false, threadId=227, charset=latin1, txIsolation=3,autocommit=true, attachment=null, respHandler=null, host=110.254.11.111,port=3317, statusSync=null, writeQueue=0, modifiedSQLExecuted=false]
02/1417:52:33.886 DEBUG [$_NIOREACTOR-3-RW](NonBlockingSession.java:229) -release connection MySQLConnection [id=19,lastTime=1455443553868, user=root, schema=db1, old shema=db1, borrowed=true,fromSlaveDB=false, threadId=220, charset=latin1, txIsolation=3,autocommit=true, attachment=dn1{SELECT *
FROMtravelrecord
ORDERBY id
LIMIT0, 1000100}, respHandler=org.opencloudb.mysql.nio.handler.MultiNodeQueryHandle[email protected],host=110.254.11.111, port=3317,[email protected]7af37,writeQueue=0, modifiedSQLExecuted=false]
02/1417:52:33.886 DEBUG [$_NIOREACTOR-3-RW](PhysicalDatasource.java:403) -release channel MySQLConnection [id=19,lastTime=1455443553868, user=root, schema=db1, old shema=db1, borrowed=true,fromSlaveDB=false, threadId=220, charset=latin1, txIsolation=3,autocommit=true, attachment=null, respHandler=null, host=110.254.11.111, port=3317,statusSync=null, writeQueue=0, modifiedSQLExecuted=false]
02/1417:52:33.886 DEBUG [$_NIOREACTOR-3-RW](MultiNodeQueryHandler.java:241) -on row end reseponse MySQLConnection [id=42,lastTime=1455443553868, user=root, schema=db2, old shema=db2, borrowed=true,fromSlaveDB=false, threadId=234, charset=latin1, txIsolation=3,autocommit=true, attachment=dn2{SELECT *
FROMtravelrecord
ORDERBY id
LIMIT0, 1000100},respHandler=org.opencloudb.mysql.nio.handler.MultiNodeQueryHandle[email protected],host=110.254.11.111, port=3317, statusSync=null, writeQueue=0,modifiedSQLExecuted=false]
02/1417:52:33.886 DEBUG [$_NIOREACTOR-3-RW](NonBlockingSession.java:229) -release connection MySQLConnection [id=42,lastTime=1455443553868, user=root, schema=db2, old shema=db2, borrowed=true,fromSlaveDB=false, threadId=234, charset=latin1, txIsolation=3,autocommit=true, attachment=dn2{SELECT *
FROMtravelrecord
ORDERBY id
LIMIT0, 1000100},respHandler=org.opencloudb.mysql.nio.handler.MultiNodeQueryHandle[email protected], host=110.254.11.111,port=3317, statusSync=null, writeQueue=0, modifiedSQLExecuted=false]
02/1417:52:33.886 DEBUG [$_NIOREACTOR-3-RW](PhysicalDatasource.java:403) -release channel MySQLConnection [id=42,lastTime=1455443553868, user=root, schema=db2, old shema=db2, borrowed=true,fromSlaveDB=false, threadId=234, charset=latin1, txIsolation=3,autocommit=true, attachment=null, respHandler=null, host=110.254.11.111,port=3317, statusSync=null, writeQueue=0, modifiedSQLExecuted=false]
02/1417:52:33.887 DEBUG [BusinessExecutor5](DataMergeService.java:296) -prepare mpp merge result for select * fromtravelrecord order by id limit 1000000,100
02/1417:52:33.887 DEBUG [BusinessExecutor5](MultiNodeQueryHandler.java:324) -last packet id:8
路由裡面是先查limit 0,1000000條，然後再查出limit 0,1000100條記錄，在這個基礎上查出100條（prepare mpp mergeresult for select * from travelrecord order by id limit 1000000,100），所以如果這個M=1000000越來越大，一次性拉的數據就越大，這樣就越來越慢了，那麼需要查詢的數據就比較多
２結合Mycat日志，分析跨分片事務的執行過程，以一個正常的跨分片SQL的事務過程，以及,一個錯誤SQL為例（比如某個分片上主鍵沖突或其他ＳＱＬ錯誤，導致無法執行成功）分別加, 以說明2.1 正常的跨分片事務分析執行情況：
mysql>set autocommit=0
mysql>update order040302 set SN=concat(SN,'-mycat');
QueryOK, 2 rows affected (0.01 sec)
Rowsmatched: 1 Changed: 1 Warnings: 0
mysql>commit;
mycat.log日志顯示：
02/1800:01:24.351 DEBUG [$_NIOREACTOR-1-RW](ServerQueryHandler.java:56) -ServerConnection [id=4, schema=TESTDB,host=127.0.0.1, user=test,txIsolation=3, autocommit=true, schema=TESTDB]updateorder040302 set SN=concat(SN,'-mycat')
02/1800:01:24.356 DEBUG [$_NIOREACTOR-1-RW](NonBlockingSession.java:113) -ServerConnection [id=4, schema=TESTDB,host=127.0.0.1, user=test,txIsolation=3, autocommit=true, schema=TESTDB]updateorder040302 set SN=concat(SN,'-mycat'), route={
1 -> dn21{update order040302 setSN=concat(SN,'-mycat')}
2 -> dn22{update order040302 setSN=concat(SN,'-mycat')}
}rrs
02/1800:01:24.356 DEBUG [$_NIOREACTOR-1-RW](MultiNodeQueryHandler.java:82) -execute mutinode query update order040302 setSN=concat(SN,'-mycat')
02/1800:01:24.357 DEBUG [$_NIOREACTOR-3-RW](MultiNodeQueryHandler.java:171) -received ok response ,executeResponse:falsefrom MySQLConnection [id=7, lastTime=1455724884356, user=root, schema=db3, oldshema=db3, borrowed=true, fromSlaveDB=false, threadId=218, charset=latin1,txIsolation=3, autocommit=true, attachment=dn21{update order040302 setSN=concat(SN,'-mycat')},respHandler=org.opencloudb.mysql.nio.handler.MultiNodeQueryHandle[email protected],host=110.254.11.111, port=3317, [email protected]a27302,writeQueue=0, modifiedSQLExecuted=true]
02/1800:01:24.358 DEBUG [$_NIOREACTOR-2-RW](MultiNodeQueryHandler.java:171) -received ok response ,executeResponse:truefrom MySQLConnection [id=30, lastTime=1455724884356, user=root, schema=db3, oldshema=db3, borrowed=true, fromSlaveDB=false, threadId=200, charset=latin1,txIsolation=3, autocommit=true, attachment=dn22{update order040302 setSN=concat(SN,'-mycat')},respHandler=org.opencloudb.mysql.nio.handler.MultiNodeQueryHandle[email protected],host=110.254.11.111, port=3327, statusSync=null, writeQueue=0,modifiedSQLExecuted=true]
02/1800:01:24.359 DEBUG [$_NIOREACTOR-2-RW](NonBlockingSession.java:229) -release connection MySQLConnection [id=30,lastTime=1455724884356, user=root, schema=db3, old shema=db3, borrowed=true,fromSlaveDB=false, threadId=200, charset=latin1, txIsolation=3,autocommit=true, attachment=dn22{update order040302 setSN=concat(SN,'-mycat')},respHandler=org.opencloudb.mysql.nio.handler.MultiNodeQueryHandle[email protected],host=110.254.11.111, port=3327, statusSync=null, writeQueue=0,modifiedSQLExecuted=true]
02/1800:01:24.359 DEBUG [$_NIOREACTOR-3-RW](MultiNodeQueryHandler.java:171) -received ok response ,executeResponse:truefrom MySQLConnection [id=7, lastTime=1455724884356, user=root, schema=db3, oldshema=db3, borrowed=true, fromSlaveDB=false, threadId=218, charset=latin1,txIsolation=3, autocommit=true, attachment=dn21{update order040302 setSN=concat(SN,'-mycat')}, respHandler=org.opencloudb.mysql.nio.handler.MultiNodeQueryHandle[email protected],host=110.254.11.111, port=3317, statusSync=null, writeQueue=0,modifiedSQLExecuted=true]
02/1800:01:24.359 DEBUG [$_NIOREACTOR-3-RW](NonBlockingSession.java:229) -release connection MySQLConnection [id=7,lastTime=1455724884356, user=root, schema=db3, old shema=db3, borrowed=true,fromSlaveDB=false, threadId=218, charset=latin1, txIsolation=3,autocommit=true, attachment=dn21{update order040302 setSN=concat(SN,'-mycat')}, respHandler=org.opencloudb.mysql.nio.handler.MultiNodeQueryHandle[email protected],host=110.254.11.111, port=3317, statusSync=null, writeQueue=0,modifiedSQLExecuted=true]
02/1800:01:24.359 DEBUG [$_NIOREACTOR-3-RW](PhysicalDatasource.java:403) -release channel MySQLConnection [id=7,lastTime=1455724884356, user=root, schema=db3, old shema=db3, borrowed=true,fromSlaveDB=false, threadId=218, charset=latin1, txIsolation=3,autocommit=true, attachment=null, respHandler=null, host=110.254.11.111,port=3317, statusSync=null, writeQueue=0, modifiedSQLExecuted=false]
分析結果：update語句是修改所有的節點上的數據，02/18 00:01:24.356處NonBlockingSession啟動了分布式事務，下面就分別在02/18 00:01:24.359有2處 NonBlockingSession.java:229在dn21、dn22上面執行了2遍，大家看到在2個節點dn21和dn22都執行完畢後，才有(PhysicalDatasource.java:403)-release channel MySQLConnection開始關閉釋放連接，也就是說等所有節點都執行完畢後才結束整個事務。
2.2 非正常的sql事務執行，有一處報錯的情況下（失敗經過）
准備數據，需要一個節點成功，一個節點失敗，所以准備拿SN字段的長度來做實驗，一個超過報錯，一個沒有超過不報錯
2.2.1 查看表結構：
mysql>show create table order040302;
+-------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|Table | Create Table |
+-------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|order040302 | CREATE TABLE `order040302` (
`ID` int(11) NOT NULL AUTO_INCREMENT,
`CODE` varchar(64) DEFAULT NULL,
`SN` varchar(64) DEFAULT NULL,
`CREATE_TIME` datetime DEFAULT NULL,
PRIMARY KEY (`ID`)
)ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 |
+-------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1row in set (0.00 sec)
mysql>
修改數據，設置成如此，一會就可以update一個節點失敗一個節點成功了，在ID=1的節點上因為長度超過64報錯，而在ID=2的節點上面則長度小於64而正常執行：
mysql> update order040302 set SN =concat('',SN,'___',SN) where ID=1;
QueryOK, 1 row affected (0.01 sec)
Rowsmatched: 1 Changed: 1 Warnings: 0
mysql>select length(t1.SN),t1.* from order040302 t1;
+---------------+----+------------+-------------------------------------------------------+---------------------+
|length(t1.SN) | ID | CODE | SN | CREATE_TIME |
+---------------+----+------------+-------------------------------------------------------+---------------------+
| 53 | 1 | 0-10000020 |beijing_03-10000020-mycat___beijing_03-10000020-mycat | 2016-02-13 00:40:27 |
| 26 | 2 | 1-10000020 |shanghai_10-10000020-mycat | 2016-02-1300:40:18 |
+---------------+----+------------+-------------------------------------------------------+---------------------+
2rows in set (0.00 sec)
mysql> update order040302 set SN =concat('fenpianyigechenggong_yigeshibai_',SN) ;
2.2.2 mycat.log日志如下：
02/2205:44:39.666 DEBUG [$_NIOREACTOR-0-RW](ServerQueryHandler.java:56) -ServerConnection [id=1, schema=TESTDB,host=127.0.0.1, user=test,txIsolation=3, autocommit=true, schema=TESTDB]updateorder040302 set SN = concat('fenpianyigechenggong_yigeshibai_',SN)
02/2205:44:39.821 DEBUG [$_NIOREACTOR-0-RW](NonBlockingSession.java:113) -ServerConnection [id=1, schema=TESTDB,host=127.0.0.1, user=test,txIsolation=3, autocommit=true, schema=TESTDB]updateorder040302 set SN = concat('fenpianyigechenggong_yigeshibai_',SN), route={
1 -> dn21{update order040302 set SN =concat('fenpianyigechenggong_yigeshibai_',SN)}
2 -> dn22{update order040302 set SN =concat('fenpianyigechenggong_yigeshibai_',SN)}
}rrs
02/2205:44:39.827 DEBUG [$_NIOREACTOR-0-RW](MultiNodeQueryHandler.java:82) -execute mutinode query update order040302 setSN = concat('fenpianyigechenggong_yigeshibai_',SN)
02/2205:44:39.830 DEBUG [$_NIOREACTOR-3-RW](MultiNodeQueryHandler.java:171) -received ok response ,executeResponse:falsefrom MySQLConnection [id=7, lastTime=1456091079827, user=root, schema=db3, oldshema=db3, borrowed=true, fromSlaveDB=false, threadId=245, charset=latin1,txIsolation=3, autocommit=true, attachment=dn21{update order040302 set SN =concat('fenpianyigechenggong_yigeshibai_',SN)},respHandler=org.opencloudb.mysql.nio.handler.MultiNodeQueryHandle[email protected],host=110.254.11.111, port=3317, [email protected]b49aaa,writeQueue=0, modifiedSQLExecuted=true]
02/2205:44:39.831 DEBUG [$_NIOREACTOR-0-RW](MultiNodeQueryHandler.java:171) -received ok response ,executeResponse:falsefrom MySQLConnection [id=12, lastTime=1456091079827, user=root, schema=db3, oldshema=db3, borrowed=true, fromSlaveDB=false, threadId=222, charset=latin1,txIsolation=3, autocommit=true, attachment=dn22{update order040302 set SN =concat('fenpianyigechenggong_yigeshibai_',SN)}, respHandler=org.opencloudb.mysql.nio.handler.MultiNodeQueryHandle[email protected],host=110.254.11.111, port=3327,[email protected]6b3db1,writeQueue=0, modifiedSQLExecuted=true]
02/2205:44:39.831 DEBUG [$_NIOREACTOR-3-RW](NonBlockingSession.java:229) -release connection MySQLConnection [id=7,lastTime=1456091079827, user=root, schema=db3, old shema=db3, borrowed=true,fromSlaveDB=false, threadId=245, charset=latin1, txIsolation=3,autocommit=true, attachment=dn21{update order040302 set SN =concat('fenpianyigechenggong_yigeshibai_',SN)},respHandler=org.opencloudb.mysql.nio.handler.MultiNodeQueryHandle[email protected],host=110.254.11.111, port=3317,[email protected]b49aaa,writeQueue=0, modifiedSQLExecuted=true]
02/2205:44:39.832 DEBUG [$_NIOREACTOR-3-RW](PhysicalDatasource.java:403) -release channel MySQLConnection [id=7,lastTime=1456091079827, user=root, schema=db3, old shema=db3, borrowed=true,fromSlaveDB=false, threadId=245, charset=latin1, txIsolation=3,autocommit=true, attachment=null, respHandler=null, host=110.254.11.111,port=3317, statusSync=null, writeQueue=0, modifiedSQLExecuted=false]
02/2205:44:39.832 DEBUG [$_NIOREACTOR-0-RW](MultiNodeQueryHandler.java:171) -received ok response ,executeResponse:truefrom MySQLConnection [id=12, lastTime=1456091079827, user=root, schema=db3, oldshema=db3, borrowed=true, fromSlaveDB=false, threadId=222, charset=latin1,txIsolation=3, autocommit=true, attachment=dn22{update order040302 set SN =concat('fenpianyigechenggong_yigeshibai_',SN)},respHandler=org.opencloudb.mysql.nio.handler.MultiNodeQueryHandle[email protected],host=110.254.11.111, port=3327, statusSync=null, writeQueue=0,modifiedSQLExecuted=true]
02/2205:44:39.832 WARN [$_NIOREACTOR-3-RW](MultiNodeHandler.java:127) -error response from MySQLConnection [id=7,lastTime=1456091079827, user=root, schema=db3, old shema=db3, borrowed=false,fromSlaveDB=false, threadId=245, charset=latin1, txIsolation=3,autocommit=true, attachment=null, respHandler=null, host=110.254.11.111,port=3317, statusSync=null, writeQueue=0, modifiedSQLExecuted=false] err Datatoo long for column 'SN' at row 1 code:1406
02/2205:44:39.833 DEBUG [$_NIOREACTOR-0-RW](NonBlockingSession.java:229) -release connection MySQLConnection [id=12,lastTime=1456091079827, user=root, schema=db3, old shema=db3, borrowed=true,fromSlaveDB=false, threadId=222, charset=latin1, txIsolation=3,autocommit=true, attachment=dn22{update order040302 set SN =concat('fenpianyigechenggong_yigeshibai_',SN)},respHandler=org.opencloudb.mysql.nio.handler.MultiNodeQueryHandle[email protected],host=110.254.11.111, port=3327, statusSync=null, writeQueue=0,modifiedSQLExecuted=true]
02/2205:44:39.834 DEBUG [$_NIOREACTOR-0-RW](PhysicalDatasource.java:403) -release channel MySQLConnection [id=12,lastTime=1456091079827, user=root, schema=db3, old shema=db3, borrowed=true,fromSlaveDB=false, threadId=222, charset=latin1, txIsolation=3,autocommit=true, attachment=null, respHandler=null, host=110.254.11.111, port=3327,statusSync=null, writeQueue=0, modifiedSQLExecuted=false]
02/2205:44:39.835 DEBUG [$_NIOREACTOR-0-RW](MultiNodeHandler.java:194) -error all end ,clear session resource
從日志記錄裡面可以看出：[
mysql>select length(t1.SN),t1.* from order040302 t1;
+---------------+----+------------+------------------------------------------------------------+---------------------+
|length(t1.SN) | ID | CODE | SN | CREATE_TIME |
+---------------+----+------------+------------------------------------------------------------+---------------------+
| 53 | 1 | 0-10000020 |beijing_03-10000020-mycat___beijing_03-10000020-mycat | 2016-02-13 00:40:27 |
| 58 | 2 | 1-10000020 |fenpianyigechenggong_yigeshibai_shanghai_10-10000020-mycat | 2016-02-1300:40:18 |
+---------------+----+------------+------------------------------------------------------------+---------------------+
2rows in set (0.06 sec)
mysql>
可見，我這個不是事務，事務會所有一起回退的。mycat窗口中如何開啟事務？
2.3 如何mysql窗口那樣，在mycat窗口開啟事務2.3.1 開啟事務，手動提交
mysql>set autocommit=0;
QueryOK, 0 rows affected (0.00 sec)
mysql>update order040302 set SN = concat('fenpianyigechenggong_yigeshibai_',SN) ;
ERROR1105 (HY000): Data too long for column 'SN' at row 1
mysql>commit;
ERROR1003 (HY000): Transaction error, need to rollback.
mysql>
mysql>select length(t1.SN),t1.* from order040302 t1;
ERROR1003 (HY000): Transaction error, need to rollback.Data too long for column 'SN'at row 1
mysql>
mysql>rollback;
QueryOK, 0 rows affected (0.01 sec)
mysql>
mysql>select length(t1.SN),t1.* from order040302 t1;
+---------------+----+------------+-------------------------------------------------------+---------------------+
|length(t1.SN) | ID | CODE | SN | CREATE_TIME |
+---------------+----+------------+-------------------------------------------------------+---------------------+
| 53 | 1 | 0-10000020 |beijing_03-10000020-mycat___beijing_03-10000020-mycat | 2016-02-13 00:40:27 |
| 26 | 2 | 1-10000020 |shanghai_10-10000020-mycat | 2016-02-1300:40:18 |
+---------------+----+------------+-------------------------------------------------------+---------------------+
2rows in set (0.09 sec)
mysql>
2.3.2 後台mycat.log日志如下，run失敗信息如下：
02/2217:54:25.035 DEBUG [$_NIOREACTOR-1-RW](ServerQueryHandler.java:56) -ServerConnection [id=2, schema=TESTDB,host=127.0.0.1, user=test,txIsolation=3, autocommit=false, schema=TESTDB]updateorder040302 set SN = concat('fenpianyigechenggong_yigeshibai_',SN)
02/2217:54:25.187 DEBUG [$_NIOREACTOR-1-RW](NonBlockingSession.java:113) -ServerConnection [id=2, schema=TESTDB, host=127.0.0.1,user=test,txIsolation=3, autocommit=false, schema=TESTDB]update order040302 setSN = concat('fenpianyigechenggong_yigeshibai_',SN), route={
1 -> dn21{update order040302 set SN =concat('fenpianyigechenggong_yigeshibai_',SN)}
2 -> dn22{update order040302 set SN =concat('fenpianyigechenggong_yigeshibai_',SN)}
}rrs
02/2217:54:25.193 DEBUG [$_NIOREACTOR-1-RW](MultiNodeQueryHandler.java:82) -execute mutinode query update order040302 setSN = concat('fenpianyigechenggong_yigeshibai_',SN)
02/2217:54:25.195 DEBUG [$_NIOREACTOR-3-RW](MultiNodeQueryHandler.java:171) -received ok response ,executeResponse:falsefrom MySQLConnection [id=7, lastTime=1456134865193, user=root, schema=db3, oldshema=db3, borrowed=true, fromSlaveDB=false, threadId=278, charset=latin1,txIsolation=0, autocommit=true, attachment=dn21{update order040302 set SN =concat('fenpianyigechenggong_yigeshibai_',SN)},respHandler=org.opencloudb.mysql.nio.handler.MultiNodeQueryHandle[email protected],host=110.254.11.111, port=3317, [email protected]f5e91e,writeQueue=0, modifiedSQLExecuted=true]
02/2217:54:25.196 DEBUG [$_NIOREACTOR-1-RW](MultiNodeQueryHandler.java:171) -received ok response ,executeResponse:falsefrom MySQLConnection [id=17, lastTime=1456134865193, user=root, schema=db3, oldshema=db3, borrowed=true, fromSlaveDB=false, threadId=259, charset=latin1,txIsolation=0, autocommit=true, attachment=dn22{update order040302 set SN =concat('fenpianyigechenggong_yigeshibai_',SN)},respHandler=org.opencloudb.mysql.nio.handler.MultiNodeQueryHandle[email protected],host=110.254.11.111, port=3327,[email protected]e9d255,writeQueue=0, modifiedSQLExecuted=true]
02/2217:54:25.196 DEBUG [$_NIOREACTOR-3-RW](MultiNodeQueryHandler.java:171) -received ok response ,executeResponse:falsefrom MySQLConnection [id=7, lastTime=1456134865193, user=root, schema=db3, oldshema=db3, borrowed=true, fromSlaveDB=false, threadId=278, charset=latin1,txIsolation=3, autocommit=false, attachment=dn21{update order040302 set SN =concat('fenpianyigechenggong_yigeshibai_',SN)},respHandler=org.opencloudb.mysql.nio.handler.MultiNodeQueryHandle[email protected],host=110.254.11.111, port=3317, [email protected]f5e91e,writeQueue=0, modifiedSQLExecuted=true]
02/2217:54:25.196 DEBUG [$_NIOREACTOR-1-RW](MultiNodeQueryHandler.java:171) -received ok response ,executeResponse:falsefrom MySQLConnection [id=17, lastTime=1456134865193, user=root, schema=db3, oldshema=db3, borrowed=true, fromSlaveDB=false, threadId=259, charset=latin1,txIsolation=3, autocommit=false, attachment=dn22{update order040302 set SN =concat('fenpianyigechenggong_yigeshibai_',SN)}, respHandler=org.opencloudb.mysql.nio.handler.MultiNodeQueryHandle[email protected],host=110.254.11.111, port=3327,[email protected]e9d255,writeQueue=0, modifiedSQLExecuted=true]
02/2217:54:25.197 WARN [$_NIOREACTOR-3-RW](MultiNodeHandler.java:127) -error response from MySQLConnection [id=7,lastTime=1456134865193, user=root, schema=db3, old shema=db3, borrowed=true,fromSlaveDB=false, threadId=278, charset=latin1, txIsolation=3,autocommit=false, attachment=dn21{update order040302 set SN = concat('fenpianyigechenggong_yigeshibai_',SN)},respHandler=org.opencloudb.mysql.nio.handler.MultiNodeQueryHandle[email protected],host=110.254.11.111, port=3317,[email protected]f5e91e,writeQueue=0, modifiedSQLExecuted=true] err Data too long for column 'SN' atrow 1 code:1406
02/2217:54:25.197 DEBUG [$_NIOREACTOR-1-RW](MultiNodeQueryHandler.java:171) -received ok response ,executeResponse:truefrom MySQLConnection [id=17, lastTime=1456134865193, user=root, schema=db3, oldshema=db3, borrowed=true, fromSlaveDB=false, threadId=259, charset=latin1,txIsolation=3, autocommit=false, attachment=dn22{update order040302 set SN =concat('fenpianyigechenggong_yigeshibai_',SN)},respHandler=org.opencloudb.mysql.nio.handler.MultiNodeQueryHandle[email protected],host=110.254.11.111, port=3327, statusSync=null, writeQueue=0,modifiedSQLExecuted=true]
02/2217:54:25.199 DEBUG [$_NIOREACTOR-1-RW](MultiNodeHandler.java:194) -error all end ,clear session resource
2.3.3後台mycat.log日志，rollback操作日志如下：
02/2217:57:17.432 DEBUG [$_NIOREACTOR-1-RW](ServerQueryHandler.java:56) -ServerConnection [id=2, schema=TESTDB,host=127.0.0.1, user=test,txIsolation=3, autocommit=false,schema=TESTDB]rollback
02/2217:57:17.434 DEBUG [$_NIOREACTOR-1-RW] (RollbackNodeHandler.java:71)-rollback job run for MySQLConnection [id=7, lastTime=1456134865193, user=root,schema=db3, old shema=db3, borrowed=true, fromSlaveDB=false, threadId=278,charset=latin1, txIsolation=3, autocommit=false, attachment=dn21{updateorder040302 set SN = concat('fenpianyigechenggong_yigeshibai_',SN)},respHandler=org.opencloudb.mysql.nio.handler.MultiNodeQueryHandle[email protected],host=110.254.11.111, port=3317,[email protected]f5e91e,writeQueue=0, modifiedSQLExecuted=true]
02/2217:57:17.435 DEBUG [$_NIOREACTOR-1-RW](RollbackNodeHandler.java:71) -rollback job run for MySQLConnection [id=17,lastTime=1456134865193, user=root, schema=db3, old shema=db3, borrowed=true,fromSlaveDB=false, threadId=259, charset=latin1, txIsolation=3,autocommit=false, attachment=dn22{update order040302 set SN =concat('fenpianyigechenggong_yigeshibai_',SN)},respHandler=org.opencloudb.mysql.nio.handler.MultiNodeQueryHandle[email protected],host=110.254.11.111, port=3327, statusSync=null, writeQueue=0,modifiedSQLExecuted=true]
02/2217:57:17.436 DEBUG [$_NIOREACTOR-1-RW](NonBlockingSession.java:361) -clear session [email protected]
02/2217:57:17.436 DEBUG [$_NIOREACTOR-1-RW](NonBlockingSession.java:229) -release connection MySQLConnection [id=7,lastTime=1456134865193, user=root, schema=db3, old shema=db3, borrowed=true,fromSlaveDB=false, threadId=278, charset=latin1, txIsolation=3,autocommit=false, attachment=dn21{update order040302 set SN =concat('fenpianyigechenggong_yigeshibai_',SN)},[email protected]2c96cb44,host=110.254.11.111, port=3317, [email protected]f5e91e,writeQueue=0, modifiedSQLExecuted=true]
02/2217:57:17.438 DEBUG [$_NIOREACTOR-1-RW](NonBlockingSession.java:229) -release connection MySQLConnection [id=17,lastTime=1456134865193, user=root, schema=db3, old shema=db3, borrowed=true,fromSlaveDB=false, threadId=259, charset=latin1, txIsolation=3,autocommit=false, attachment=dn22{update order040302 set SN =concat('fenpianyigechenggong_yigeshibai_',SN)},[email protected]2c96cb44,host=110.254.11.111, port=3327, statusSync=null, writeQueue=0,modifiedSQLExecuted=true]
02/2217:57:17.438 DEBUG [$_NIOREACTOR-3-RW](RollbackReleaseHandler.java:58) -autocomit is false,but no commit or rollback,so mycat rollbacked backend conn MySQLConnection [id=7,lastTime=1456134865193, user=root, schema=db3, old shema=db3, borrowed=true,fromSlaveDB=false, threadId=278, charset=latin1, txIsolation=3,autocommit=false, attachment=null,respHandler=org.opencloudb.mysql.nio.handler.RollbackReleaseHandl[email protected],host=110.254.11.111, port=3317,[email protected]f5e91e,writeQueue=0, modifiedSQLExecuted=true]
02/2217:57:17.439 DEBUG [$_NIOREACTOR-3-RW](PhysicalDatasource.java:403) -release channel MySQLConnection [id=7,lastTime=1456134865193, user=root, schema=db3, old shema=db3, borrowed=true,fromSlaveDB=false, threadId=278, charset=latin1, txIsolation=3,autocommit=false, attachment=null, respHandler=null, host=110.254.11.111,port=3317, statusSync=null, writeQueue=0, modifiedSQLExecuted=false]
02/2217:57:17.439 DEBUG [$_NIOREACTOR-1-RW](RollbackReleaseHandler.java:58) -autocomit is false,but no commit or rollback,so mycat rollbacked backend conn MySQLConnection [id=17,lastTime=1456134865193, user=root, schema=db3, old shema=db3, borrowed=true,fromSlaveDB=false, threadId=259, charset=latin1, txIsolation=3,autocommit=false, attachment=null,respHandler=org.opencloudb.mysql.nio.handler.RollbackReleaseHandl[email protected],host=110.254.11.111, port=3327, statusSync=null, writeQueue=0,modifiedSQLExecuted=true]
02/2217:57:17.440 DEBUG [$_NIOREACTOR-1-RW](PhysicalDatasource.java:403) -release channel MySQLConnection [id=17,lastTime=1456134865193, user=root, schema=db3, old shema=db3, borrowed=true,fromSlaveDB=false, threadId=259, charset=latin1, txIsolation=3,autocommit=false, attachment=null, respHandler=null, host=110.254.11.111,port=3327, statusSync=null, writeQueue=0, modifiedSQLExecuted=false]
看到在執行update的時候，有報錯信息：在dn21節點上面err Data too long forcolumn ‘SN’ at row 1 code:1406，所以按照理論上任何節點發生錯誤了，就面臨rollback的錯誤信息，任何任何都會提示報錯信息，只能執行rollback來恢復。PS：這裡目前的mycat版本沒有辦法做到在update失敗後自動rollback，需要手動rollback操作。
３創建一個在多個分片上存在的存儲過程，存儲過程可以是統計某個表的記錄個數並返回，然後用Mycat注解方式完成調用3.1准備好的簡單的存儲過程
DELIMITER$$
CREATE
PROCEDUREdb3.pro_select()
BEGIN
SELECTCOUNT(1) FROM order040302;
END$$
DELIMITER;
3.2在各mysql節點上執行創建存儲過程dn21節點上的mysql窗口上：
mysql>DELIMITER $$
mysql>CREATE
-> PROCEDURE `db3`.`pro_select`()
-> BEGIN
->SELECT COUNT(1) FROM order040302;
-> END$$
QueryOK, 0 rows affected (0.01 sec)
mysql>DELIMITER ;
mysql>
dn22節點上的mysql窗口上：
mysql>DELIMITER $$
mysql>CREATE
-> PROCEDURE `db3`.`pro_select`()
-> BEGIN
->SELECT COUNT(1) FROM order040302;
-> END$$
QueryOK, 0 rows affected (0.01 sec)
mysql>DELIMITER ;
mysql>
檢查存儲過程：
mysql>select * from information_schema.ROUTINES\G
***************************1. row ***************************
SPECIFIC_NAME: pro_select
ROUTINE_CATALOG: def
ROUTINE_SCHEMA: db3
ROUTINE_NAME:pro_select
ROUTINE_TYPE:PROCEDURE
DATA_TYPE:
CHARACTER_MAXIMUM_LENGTH:NULL
CHARACTER_OCTET_LENGTH: NULL
NUMERIC_PRECISION: NULL
NUMERIC_SCALE: NULL
DATETIME_PRECISION: NULL
CHARACTER_SET_NAME: NULL
COLLATION_NAME: NULL
DTD_IDENTIFIER: NULL
ROUTINE_BODY:SQL
ROUTINE_DEFINITION: BEGIN
SELECTCOUNT(1) FROM order040302;
END
EXTERNAL_NAME: NULL
EXTERNAL_LANGUAGE: NULL
PARAMETER_STYLE: SQL
IS_DETERMINISTIC: NO
SQL_DATA_ACCESS: CONTAINS SQL
SQL_PATH:NULL
SECURITY_TYPE: DEFINER
CREATED: 2016-02-22 19:17:12
LAST_ALTERED:2016-02-22 19:17:12
SQL_MODE:STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION
ROUTINE_COMMENT:
DEFINER: [email protected]
CHARACTER_SET_CLIENT:latin1
COLLATION_CONNECTION:latin1_swedish_ci
DATABASE_COLLATION: utf8mb4_general_ci
1row in set (0.00 sec)
mysql>
然後在mycat中執行：
mysql>/*!mycat:sql=select * from order040302 where ID=2*/ call pro_select();
+----------+
|COUNT(1) |
+----------+
| 1 |
| 1 |
+----------+
2rows in set (0.01 sec)
QueryOK, 0 rows affected (0.01 sec)
mysql>
問題，為啥是2行記錄，不知道可以不可以並成一行記錄呢？
普通sql到了最後會有如下：
02/23 00:07:21.478 DEBUG [BusinessExecutor0] (DataMergeService.java:296) -prepare mpp merge result for select count(*) from order040302
02/23 00:07:21.479 DEBUG [BusinessExecutor0] (MultiNodeQueryHandler.java:324) -last packet id:5
的聚合操作，有了這個merge result操作後，就會把2條記錄變成一條記錄，但是mycat注解後的執行sql顯示注解後，沒有這一個merge result的操作，所以是2條記錄。哇塞，我好偉大，發現了其中的區別，但是那麼新問題來了，怎麼完善注解後生成的2條記錄變成為1條記錄額？
3.3看後台mycat.log日志：看到日志中42.293記錄，走的route路由進入dn21和dn22節點上面，最終執行的是注解的-execute mutinode query select * from order040302，如下所示：
02/22 20:19:42.292 DEBUG [$_NIOREACTOR-1-RW](ServerQueryHandler.java:56) -ServerConnection [id=10, schema=TESTDB,host=127.0.0.1, user=test,txIsolation=3, autocommit=true,schema=TESTDB]/*!mycat:sql=select * from order040302*/ call pro_select()
02/22 20:19:42.293 DEBUG [$_NIOREACTOR-1-RW](NonBlockingSession.java:113) -ServerConnection [id=10, schema=TESTDB,host=127.0.0.1, user=test,txIsolation=3, autocommit=true, schema=TESTDB]select* from order040302, route={
1 -> dn21{call pro_select()}
2 -> dn22{call pro_select()}
} rrs
02/22 20:19:42.293 DEBUG [$_NIOREACTOR-1-RW](MultiNodeQueryHandler.java:82) -execute mutinode query select * fromorder040302
02/2220:19:42.295 DEBUG [$_NIOREACTOR-0-RW](MultiNodeQueryHandler.java:171) -received ok response ,executeResponse:falsefrom MySQLConnection [id=4, lastTime=1456143582284, user=root, schema=db3, oldshema=db3, borrowed=true, fromSlaveDB=false, threadId=280, charset=latin1,txIsolation=3, autocommit=true, attachment=dn21{call pro_select()},respHandler=org.opencloudb.mysql.nio.handler.MultiNodeQueryHandle[email protected],host=110.254.11.111, port=3317,[email protected]9bc30,writeQueue=0, modifiedSQLExecuted=true]
02/2220:19:42.295 DEBUG [$_NIOREACTOR-3-RW](MultiNodeQueryHandler.java:241) -on row end reseponse MySQLConnection [id=11,lastTime=1456143582284, user=root, schema=db3, old shema=db3, borrowed=true,fromSlaveDB=false, threadId=254, charset=latin1, txIsolation=3, autocommit=true,attachment=dn22{call pro_select()},respHandler=org.opencloudb.mysql.nio.handler.MultiNodeQueryHandle[email protected],host=110.254.11.111, port=3327, statusSync=null, writeQueue=0,modifiedSQLExecuted=true]
02/2220:19:42.295 DEBUG [$_NIOREACTOR-0-RW](MultiNodeQueryHandler.java:241) -on row end reseponse MySQLConnection [id=4,lastTime=1456143582284, user=root, schema=db3, old shema=db3, borrowed=true,fromSlaveDB=false, threadId=280, charset=latin1, txIsolation=3,autocommit=true, attachment=dn21{call pro_select()},respHandler=org.opencloudb.mysql.nio.handler.MultiNodeQueryHandle[email protected],host=110.254.11.111, port=3317,[email protected]9bc30,writeQueue=0, modifiedSQLExecuted=true]
02/2220:19:42.296 DEBUG [$_NIOREACTOR-0-RW](MultiNodeQueryHandler.java:280) -last packet id:6
02/2220:19:42.295 DEBUG [$_NIOREACTOR-3-RW](MultiNodeQueryHandler.java:171) -received ok response ,executeResponse:truefrom MySQLConnection [id=11, lastTime=1456143582284, user=root, schema=db3, oldshema=db3, borrowed=true, fromSlaveDB=false, threadId=254, charset=latin1,txIsolation=3, autocommit=true, attachment=dn22{call pro_select()},respHandler=org.opencloudb.mysql.nio.handler.MultiNodeQueryHandle[email protected],host=110.254.11.111, port=3327, statusSync=null, writeQueue=0,modifiedSQLExecuted=true]
02/2220:19:42.296 DEBUG [$_NIOREACTOR-0-RW](MultiNodeQueryHandler.java:171) -received ok response ,executeResponse:truefrom MySQLConnection [id=4, lastTime=1456143582284, user=root, schema=db3, oldshema=db3, borrowed=true, fromSlaveDB=false, threadId=280, charset=latin1,txIsolation=3, autocommit=true, attachment=dn21{call pro_select()},respHandler=org.opencloudb.mysql.nio.handler.MultiNodeQueryHandle[email protected],host=110.254.11.111, port=3317, statusSync=null, writeQueue=0,modifiedSQLExecuted=true]
02/2220:19:42.296 DEBUG [$_NIOREACTOR-3-RW](NonBlockingSession.java:229) -release connection MySQLConnection [id=11,lastTime=1456143582284, user=root, schema=db3, old shema=db3, borrowed=true,fromSlaveDB=false, threadId=254, charset=latin1, txIsolation=3,autocommit=true, attachment=dn22{call pro_select()},respHandler=org.opencloudb.mysql.nio.handler.MultiNodeQueryHandle[email protected],host=110.254.11.111, port=3327, statusSync=null, writeQueue=0,modifiedSQLExecuted=true]
02/2220:19:42.296 DEBUG [$_NIOREACTOR-0-RW](NonBlockingSession.java:229) -release connection MySQLConnection [id=4,lastTime=1456143582284, user=root, schema=db3, old shema=db3, borrowed=true,fromSlaveDB=false, threadId=280, charset=latin1, txIsolation=3,autocommit=true, attachment=dn21{call pro_select()},respHandler=org.opencloudb.mysql.nio.handler.MultiNodeQueryHandle[email protected],host=110.254.11.111, port=3317, statusSync=null, writeQueue=0,modifiedSQLExecuted=true]
02/2220:19:42.296 DEBUG [$_NIOREACTOR-3-RW](PhysicalDatasource.java:403) -release channel MySQLConnection [id=11,lastTime=1456143582284, user=root, schema=db3, old shema=db3, borrowed=true,fromSlaveDB=false, threadId=254, charset=latin1, txIsolation=3,autocommit=true, attachment=null, respHandler=null, host=110.254.11.111, port=3327,statusSync=null, writeQueue=0, modifiedSQLExecuted=false]
02/2220:19:42.297 DEBUG [$_NIOREACTOR-0-RW](PhysicalDatasource.java:403) -release channel MySQLConnection [id=4,lastTime=1456143582284, user=root, schema=db3, old shema=db3, borrowed=true,fromSlaveD**重點內容**B=false, threadId=280, charset=latin1, txIsolation=3,autocommit=true, attachment=null, respHandler=null, host=110.254.11.111,port=3317, statusSync=null, writeQueue=0, modifiedSQLExecuted=false]
