老师您好，我这边在做Mysql的水平拆分设备数据库自增主键的时候插入数据报错
老师您好，我这边在做Mysql的水平拆分设备数据库自增主键的时候mycat.log日志出现了 java.lang.RuntimeException: can’t fetch sequnce in db,sequnce :ORDER_MASTER detail:null
完整的错误如下：
2020-06-26 23:41:08.597 INFO [Timer0] (io.mycat.backend.datasource.PhysicalDatasource.getConnection(PhysicalDatasource.java:529)) - no ilde connection in pool,create new connection for 192.168.189.140 of schema mycat
2020-06-26 23:41:08.604 WARN [$_NIOREACTOR-7-RW] (io.mycat.backend.mysql.nio.MySQLConnectionAuthenticator.handle(MySQLConnectionAuthenticator.java:91)) - can't connect to mysql server ,errmsg:Access denied for user 'im_mycat'@'server01' (using password: YES) MySQLConnection [id=46, lastTime=1593186068595, user=im_mycat, schema=mycat, old shema=mycat, borrowed=false, fromSlaveDB=false, threadId=583, charset=latin1, txIsolation=2, autocommit=true, attachment=null, respHandler=null, host=192.168.189.140, port=3306, statusSync=null, writeQueue=0, modifiedSQLExecuted=false]
2020-06-26 23:41:08.605 INFO [$_NIOREACTOR-7-RW] (io.mycat.sqlengine.SQLJob.connectionError(SQLJob.java:125)) - can't get connection for sql : select user()
2020-06-26 23:41:08.605 ERROR [$_NIOREACTOR-7-RW] (io.mycat.backend.heartbeat.MySQLHeartbeat.nextDector(MySQLHeartbeat.java:212)) - set Error 1 DBHostConfig [hostName=192.168.189.140, url=192.168.189.140:3306]
2020-06-26 23:41:08.607 INFO [$_NIOREACTOR-7-RW] (io.mycat.net.AbstractConnection.close(AbstractConnection.java:514)) - close connection,reason:stream closed ,MySQLConnection [id=46, lastTime=1593186068595, user=im_mycat, schema=mycat, old shema=mycat, borrowed=false, fromSlaveDB=false, threadId=583, charset=latin1, txIsolation=2, autocommit=true, attachment=null, respHandler=null, host=192.168.189.140, port=3306, statusSync=null, writeQueue=0, modifiedSQLExecuted=false]
2020-06-26 23:41:18.597 INFO [Timer1] (io.mycat.backend.datasource.PhysicalDatasource.getConnection(PhysicalDatasource.java:529)) - no ilde connection in pool,create new connection for 192.168.189.140 of schema mycat
2020-06-26 23:41:18.600 WARN [$_NIOREACTOR-0-RW] (io.mycat.backend.mysql.nio.MySQLConnectionAuthenticator.handle(MySQLConnectionAuthenticator.java:91)) - can't connect to mysql server ,errmsg:Access denied for user 'im_mycat'@'server01' (using password: YES) MySQLConnection [id=47, lastTime=1593186078595, user=im_mycat, schema=mycat, old shema=mycat, borrowed=false, fromSlaveDB=false, threadId=584, charset=latin1, txIsolation=2, autocommit=true, attachment=null, respHandler=null, host=192.168.189.140, port=3306, statusSync=null, writeQueue=0, modifiedSQLExecuted=false]
2020-06-26 23:41:18.601 INFO [$_NIOREACTOR-0-RW] (io.mycat.sqlengine.SQLJob.connectionError(SQLJob.java:125)) - can't get connection for sql : select user()
2020-06-26 23:41:18.601 ERROR [$_NIOREACTOR-0-RW] (io.mycat.backend.heartbeat.MySQLHeartbeat.nextDector(MySQLHeartbeat.java:212)) - set Error 2 DBHostConfig [hostName=192.168.189.140, url=192.168.189.140:3306]
2020-06-26 23:41:18.601 INFO [$_NIOREACTOR-0-RW] (io.mycat.net.AbstractConnection.close(AbstractConnection.java:514)) - close connection,reason:stream closed ,MySQLConnection [id=47, lastTime=1593186078595, user=im_mycat, schema=mycat, old shema=mycat, borrowed=false, fromSlaveDB=false, threadId=584, charset=latin1, txIsolation=2, autocommit=true, attachment=null, respHandler=null, host=192.168.189.140, port=3306, statusSync=null, writeQueue=0, modifiedSQLExecuted=false]
2020-06-26 23:41:21.549 ERROR [SequenceExecutor1] (io.mycat.route.MyCATSequnceProcessor.executeSeq(MyCATSequnceProcessor.java:55)) - MyCATSequenceProcessor.executeSeq(SesionSQLPair)
java.lang.RuntimeException: can't fetch sequnce in db,sequnce :ORDER_MASTER detail:null
at io.mycat.route.sequence.handler.IncrSequenceMySQLHandler.getSeqValueFromDB(IncrSequenceMySQLHandler.java:127) ~[Mycat-server-1.6.6.1-test.jar:?]
at io.mycat.route.sequence.handler.IncrSequenceMySQLHandler.nextId(IncrSequenceMySQLHandler.java:94) ~[Mycat-server-1.6.6.1-test.jar:?]
at io.mycat.route.parser.druid.DruidSequenceHandler.getExecuteSql(DruidSequenceHandler.java:83) ~[Mycat-server-1.6.6.1-test.jar:?]
at io.mycat.route.MyCATSequnceProcessor.executeSeq(MyCATSequnceProcessor.java:51) ~[Mycat-server-1.6.6.1-test.jar:?]
at io.mycat.route.util.RouterUtil$1.run(RouterUtil.java:563) ~[Mycat-server-1.6.6.1-test.jar:?]
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145) [?:1.7.0_79]
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615) [?:1.7.0_79]
at java.lang.Thread.run(Thread.java:745) [?:1.7.0_79]
2020-06-26 23:41:28.597 INFO [Timer1] (io.mycat.backend.datasource.PhysicalDatasource.getConnection(PhysicalDatasource.java:529)) - no ilde connection in pool,create new connection for 192.168.189.140 of schema mycat
2020-06-26 23:41:28.603 WARN [$_NIOREACTOR-1-RW] (io.mycat.backend.mysql.nio.MySQLConnectionAuthenticator.handle(MySQLConnectionAuthenticator.java:91)) - can't connect to mysql server ,errmsg:Access denied for user 'im_mycat'@'server01' (using password: YES) MySQLConnection [id=48, lastTime=1593186088595, user=im_mycat, schema=mycat, old shema=mycat, borrowed=false, fromSlaveDB=false, threadId=585, charset=latin1, txIsolation=2, autocommit=true, attachment=null, respHandler=null, host=192.168.189.140, port=3306, statusSync=null, writeQueue=0, modifiedSQLExecuted=false]
2020-06-26 23:41:28.603 INFO [$_NIOREACTOR-1-RW] (io.mycat.sqlengine.SQLJob.connectionError(SQLJob.java:125)) - can't get connection for sql : select user()
2020-06-26 23:41:28.604 INFO [$_NIOREACTOR-1-RW] (io.mycat.net.AbstractConnection.close(AbstractConnection.java:514)) - close connection,reason:stream closed ,MySQLConnection [id=48, lastTime=1593186088595, user=im_mycat, schema=mycat, old shema=mycat, borrowed=false, fromSlaveDB=false, threadId=585, charset=latin1, txIsolation=2, autocommit=true, attachment=null, respHandler=null, host=192.168.189.140, port=3306, statusSync=null, writeQueue=0, modifiedSQLExecuted=false]
2020-06-26 23:41:38.596 INFO [Timer0] (io.mycat.backend.datasource.PhysicalDatasource.getConnection(PhysicalDatasource.java:529)) - no ilde connection in pool,create new connection for 192.168.189.140 of schema mycat
2020-06-26 23:41:38.603 WARN [$_NIOREACTOR-2-RW] (io.mycat.backend.mysql.nio.MySQLConnectionAuthenticator.handle(MySQLConnectionAuthenticator.java:91)) - can't connect to mysql server ,errmsg:Access denied for user 'im_mycat'@'server01' (using password: YES) MySQLConnection [id=49, lastTime=1593186098595, user=im_mycat, schema=mycat, old shema=mycat, borrowed=false, fromSlaveDB=false, threadId=586, charset=latin1, txIsolation=2, autocommit=true, attachment=null, respHandler=null, host=192.168.189.140, port=3306, statusSync=null, writeQueue=0, modifiedSQLExecuted=false]
2020-06-26 23:41:38.603 INFO [$_NIOREACTOR-2-RW] (io.mycat.sqlengine.SQLJob.connectionError(SQLJob.java:125)) - can't get connection for sql : select user()
2020-06-26 23:41:38.603 ERROR [$_NIOREACTOR-2-RW] (io.mycat.backend.heartbeat.MySQLHeartbeat.nextDector(MySQLHeartbeat.java:212)) - set Error 1 DBHostConfig [hostName=192.168.189.140, url=192.168.189.140:3306]
2020-06-26 23:41:38.605 INFO [$_NIOREACTOR-2-RW] (io.mycat.net.AbstractConnection.close(AbstractConnection.java:514)) - close connection,reason:stream closed ,MySQLConnection [id=49, lastTime=1593186098595, user=im_mycat, schema=mycat, old shema=mycat, borrowed=false, fromSlaveDB=false, threadId=586, charset=latin1, txIsolation=2, autocommit=true, attachment=null, respHandler=null, host=192.168.189.140, port=3306, statusSync=null, writeQueue=0, modifiedSQLExecuted=false]
2020-06-26 23:41:48.596 INFO [Timer0] (io.mycat.backend.datasource.PhysicalDatasource.getConnection(PhysicalDatasource.java:529)) - no ilde connection in pool,create new connection for 192.168.189.140 of schema mycat
2020-06-26 23:41:48.600 WARN [$_NIOREACTOR-3-RW] (io.mycat.backend.mysql.nio.MySQLConnectionAuthenticator.handle(MySQLConnectionAuthenticator.java:91)) - can't connect to mysql server ,errmsg:Access denied for user 'im_mycat'@'server01' (using password: YES) MySQLConnection [id=50, lastTime=1593186108595, user=im_mycat, schema=mycat, old shema=mycat, borrowed=false, fromSlaveDB=false, threadId=587, charset=latin1, txIsolation=2, autocommit=true, attachment=null, respHandler=null, host=192.168.189.140, port=3306, statusSync=null, writeQueue=0, modifiedSQLExecuted=false]
2020-06-26 23:41:48.600 INFO [$_NIOREACTOR-3-RW] (io.mycat.sqlengine.SQLJob.connectionError(SQLJob.java:125)) - can't get connection for sql : select user()
2020-06-26 23:41:48.600 ERROR [$_NIOREACTOR-3-RW] (io.mycat.backend.heartbeat.MySQLHeartbeat.nextDector(MySQLHeartbeat.java:212)) - set Error 2 DBHostConfig [hostName=192.168.189.140, url=192.168.189.140:3306]
2020-06-26 23:41:48.601 INFO [$_NIOREACTOR-3-RW] (io.mycat.net.AbstractConnection.close(AbstractConnection.java:514)) - close connection,reason:stream closed ,MySQLConnection [id=50, lastTime=1593186108595, user=im_mycat, schema=mycat, old shema=mycat, borrowed=false, fromSlaveDB=false, threadId=587, charset=latin1, txIsolation=2, autocommit=true, attachment=null, respHandler=null, host=192.168.189.140, port=3306, statusSync=null, writeQueue=0, modifiedSQLExecuted=false]
2020-06-26 23:41:58.597 INFO [Timer0] (io.mycat.backend.datasource.PhysicalDatasource.getConnection(PhysicalDatasource.java:529)) - no ilde connection in pool,create new connection for 192.168.189.140 of schema mycat
2020-06-26 23:41:58.604 WARN [$_NIOREACTOR-4-RW] (io.mycat.backend.mysql.nio.MySQLConnectionAuthenticator.handle(MySQLConnectionAuthenticator.java:91)) - can't connect to mysql server ,errmsg:Access denied for user 'im_mycat'@'server01' (using password: YES) MySQLConnection [id=51, lastTime=1593186118595, user=im_mycat, schema=mycat, old shema=mycat, borrowed=false, fromSlaveDB=false, threadId=588, charset=latin1, txIsolation=2, autocommit=true, attachment=null, respHandler=null, host=192.168.189.140, port=3306, statusSync=null, writeQueue=0, modifiedSQLExecuted=false]
2020-06-26 23:41:58.604 INFO [$_NIOREACTOR-4-RW] (io.mycat.sqlengine.SQLJob.connectionError(SQLJob.java:125)) - can't get connection for sql : select user()
2020-06-26 23:41:58.605 INFO [$_NIOREACTOR-4-RW] (io.mycat.net.AbstractConnection.close(AbstractConnection.java:514)) - close connection,reason:stream closed ,MySQLConnection [id=51, lastTime=1593186118595, user=im_mycat, schema=mycat, old shema=mycat, borrowed=false, fromSlaveDB=false, threadId=588, charset=latin1, txIsolation=2, autocommit=true, attachment=null, respHandler=null, host=192.168.189.140, port=3306, statusSync=null, writeQueue=0, modifiedSQLExecuted=false]
2020-06-26 23:42:08.596 INFO [Timer0] (io.mycat.backend.datasource.PhysicalDatasource.getConnection(PhysicalDatasource.java:529)) - no ilde connection in pool,create new connection for 192.168.189.140 of schema mycat
2020-06-26 23:42:08.602 WARN [$_NIOREACTOR-5-RW] (io.mycat.backend.mysql.nio.MySQLConnectionAuthenticator.handle(MySQLConnectionAuthenticator.java:91)) - can't connect to mysql server ,errmsg:Access denied for user 'im_mycat'@'server01' (using password: YES) MySQLConnection [id=52, lastTime=1593186128595, user=im_mycat, schema=mycat, old shema=mycat, borrowed=false, fromSlaveDB=false, threadId=589, charset=latin1, txIsolation=2, autocommit=true, attachment=null, respHandler=null, host=192.168.189.140, port=3306, statusSync=null, writeQueue=0, modifiedSQLExecuted=false]
2020-06-26 23:42:08.603 INFO [$_NIOREACTOR-5-RW] (io.mycat.sqlengine.SQLJob.connectionError(SQLJob.java:125)) - can't get connection for sql : select user()
2020-06-26 23:42:08.603 ERROR [$_NIOREACTOR-5-RW] (io.mycat.backend.heartbeat.MySQLHeartbeat.nextDector(MySQLHeartbeat.java:212)) - set Error 1 DBHostConfig [hostName=192.168.189.140, url=192.168.189.140:3306]
2020-06-26 23:42:08.604 INFO [$_NIOREACTOR-5-RW] (io.mycat.net.AbstractConnection.close(AbstractConnection.java:514)) - close connection,reason:stream closed ,MySQLConnection [id=52, lastTime=1593186128595, user=im_mycat, schema=mycat, old shema=mycat, borrowed=false, fromSlaveDB=false, threadId=589, charset=latin1, txIsolation=2, autocommit=true, attachment=null, respHandler=null, host=192.168.189.140, port=3306, statusSync=null, writeQueue=0, modifiedSQLExecuted=false]
2020-06-26 23:42:18.597 INFO [Timer1] (io.mycat.backend.datasource.PhysicalDatasource.getConnection(PhysicalDatasource.java:529)) - no ilde connection in pool,create new connection for 192.168.189.140 of schema mycat
2020-06-26 23:42:18.603 WARN [$_NIOREACTOR-6-RW] (io.mycat.backend.mysql.nio.MySQLConnectionAuthenticator.handle(MySQLConnectionAuthenticator.java:91)) - can't connect to mysql server ,errmsg:Access denied for user 'im_mycat'@'server01' (using password: YES) MySQLConnection [id=53, lastTime=1593186138595, user=im_mycat, schema=mycat, old shema=mycat, borrowed=false, fromSlaveDB=false, threadId=590, charset=latin1, txIsolation=2, autocommit=true, attachment=null, respHandler=null, host=192.168.189.140, port=3306, statusSync=null, writeQueue=0, modifiedSQLExecuted=false]
2020-06-26 23:42:18.604 INFO [$_NIOREACTOR-6-RW] (io.mycat.sqlengine.SQLJob.connectionError(SQLJob.java:125)) - can't get connection for sql : select user()
2020-06-26 23:42:18.604 ERROR [$_NIOREACTOR-6-RW] (io.mycat.backend.heartbeat.MySQLHeartbeat.nextDector(MySQLHeartbeat.java:212)) - set Error 2 DBHostConfig [hostName=192.168.189.140, url=192.168.189.140:3306]
2020-06-26 23:42:18.605 INFO [$_NIOREACTOR-6-RW] (io.mycat.net.AbstractConnection.close(AbstractConnection.java:514)) - close connection,reason:stream closed ,MySQLConnection [id=53, lastTime=1593186138595, user=im_mycat, schema=mycat, old shema=mycat, borrowed=false, fromSlaveDB=false, threadId=590, charset=latin1, txIsolation=2, autocommit=true, attachment=null, respHandler=null, host=192.168.189.140, port=3306, statusSync=null, writeQueue=0, modifiedSQLExecuted=false]
mycat 那边的插入数据后的错误显示：
mysql> insert into order_master(order_sn,customer_id,shipping_user,province,city,district,address ,payment_method,order_money,district_money,shipping_money,payment_money,create_time) values('20180324131402',1402,CONCAT('im_',1402),19,262,2192,'',3,0,0,0,0,now());
ERROR 1003 (HY000): mycat sequnce err.java.lang.RuntimeException: can't fetch sequnce in db,sequnce :ORDER_MASTER detail:null
这是我的schema.xml(已设置mycat的datahost)
<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">
<schema name="imooc_db" checkSQLschema="false" sqlMaxLimit="100">
<table name="order_master" primaryKey="order_id" dataNode="orderdb01,orderdb02" rule="order_master" **autoIncrement="true"**/>
<table name="order_detail" primaryKey="order_detail_id" dataNode="ordb" />
<table name="order_cart" primaryKey="cart_id" dataNode="ordb" />
<table name="order_customer_addr" primaryKey="customer_addr_id" dataNode="ordb" />
<table name="region_info" primaryKey="region_id" dataNode="ordb" />
<table name="shipping_info" primaryKey="ship_id" dataNode="ordb" />
<table name="warehouse_info" primaryKey="w_id" dataNode="ordb" />
<table name="warehouse_product" primaryKey="wp_id" dataNode="ordb" />
</schema>
<dataNode name="ordb" dataHost="mysql189142" database="order_db" />
<dataNode name="orderdb01" dataHost="mysql189142" database="orderdb01" />
<dataNode name="orderdb02" dataHost="mysql189142" database="orderdb02" />
<dataNode name="mycat" dataHost="mysql189140" database="mycat" />
<dataHost name="mysql189142" maxCon="1000" minCon="10" balance="3" writeType="0" dbType="mysql" dbDriver="native" switchType="1" >
<heartbeat> select user() </heartbeat>
<writeHost host="192.168.189.142" url="192.168.189.142:3306" user="im_mycat" password="123456" />
</dataHost>
<dataHost name="mysql189140" maxCon="1000" minCon="10" balance="3" writeType="0" dbType="mysql" dbDriver="native" switchType="1" >
<heartbeat> select user() </heartbeat>
<writeHost host="192.168.189.140" url="192.168.189.140:3306" user="im_mycat" password="123456" />
</dataHost>
</mycat:schema>
这是我的server.xml文件（已设置数据库自增）
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mycat:server SYSTEM "server.dtd">
<mycat:server
xmlns:mycat="http://io.mycat/">
<system>
<property name="serverPort">8066</property>
<property name="managerPort">9066</property>
<property name="nonePasswordLogin">0</property>
<property name="useHandshakeV10">1</property>
<property name="useSqlStat">0</property>
<property name="useGlobleTableCheck">0</property>
<property name="sequnceHandlerType">1</property>
<property name="subqueryRelationshipCheck">false</property>
<property name="processorBufferPoolType">0</property>
<property name="handleDistributedTransactions">0</property>
<property name="useOffHeapForMerge">1</property>
<property name="memoryPageSize">64k</property>
<property name="spillsFileBufferSize">1k</property>
<property name="useStreamOutput">0</property>
<property name="systemReserveMemorySize">384m</property>
<property name="useZKSwitch">false</property>
<property name="charset">utf8</property>
<property name="txIsolation">2</property>
<property name="processors">8</property>
<property name="idleTimeout">1800000</property>
<property name="sqlExecuteTimeout">300</property>
<property name="maxPacketSize">104857600</property>
<property name="defaultMaxLimit">100</property>
</system>
<user name="root" defaultAccount="true">
<property name="password">123456</property>
<property name="schemas">imooc_db</property>
</user>
</mycat:server>
这是我的sequence_db_conf.properties文件
#sequence stored in datanode
GLOBAL=mycat
ORDER_MASTER=mycat
我已给mycat用户设置增删改查以及存储过程的权限了，为什么我插入数据的时候还是不成功呢？
mysql> use mycat
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> show tables;
+-----------------+
| Tables_in_mycat |
+-----------------+
| MYCAT_SEQUENCE |
+-----------------+
1 row in set (0.00 sec)
mysql> select * from MYCAT_SEQUENCE;
+--------------+---------------+-----------+
| name | current_value | increment |
+--------------+---------------+-----------+
| GLOBAL | 1 | 1 |
| ORDER_MASTER | 1 | 1 |
+--------------+---------------+-----------+
2 rows in set (0.00 sec)
mysql> grant execute on *.* to im_mycat@'192.168.189.%';
Query OK, 0 rows affected (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)
mysql> show grants for im_mycat@'192.168.189.%';
+------------------------------------------------------------------------------------+
| Grants for im_mycat@192.168.189.% |
+------------------------------------------------------------------------------------+
| GRANT SELECT, INSERT, UPDATE, DELETE, EXECUTE ON *.* TO 'im_mycat'@'192.168.189.%' |
+------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql>
1759
收起
正在回答 回答被采纳积分+3
2回答
BillyWebber
2020-06-27 14:38:16
mycat的使用sql脚本报错日志如下：
0
有任何疑惑可以回复我~
sqlercn
2020-06-27 09:48:19
-
BillyWebber #2
现在数据插入报的 java.lang.RuntimeException: can’t fetch sequnce in db,sequnce :ORDER_MASTER detail:null解决了，发现您的server.xml和垂直分片的server.xml有很大差别 但现在遇到一个新的问题：mycat数据插入的时候显示order_id的长度超出范围[Err] 1264 - Out of range value for column 'order_id' at row 1 但这一个order_id的长度应该在哪里设置呢？我直接使用navicat设置是不成功的（MYCAT_SEUENCES表我也尝试将字段的长度缩短了，但还是不行）
有任何疑惑可以回复我~ 2020-06-27 11:49:57 -
水平分片和垂直分片的server.xml是会有一些不同的。对于表结构的修改，直接修改后端物理数据库表的结构就可以，不过你可以看一下日志拆入的order_id值是多少，理论上不应该超过范围的。
有任何疑惑可以回复我~ 2020-06-27 13:48:47
相似问题
关于自增主键的问题
850
1
5
数据库自增
634
0
4
插入数据报错
885
0
4
selectKey为自增id时，为何要使用主库？
618
1
3
老师你好，数据库索引方面的问题，帮忙解答下，谢谢
811
0
5
登录后可查看更多问答，登录/注册
