Mycat-1.4 配置主键自增长的问题.

kys1230 commented 9 years ago

版本 - Mycat-server-1.4-RELEASE-20150901112004-linux.tar.gz

schema.xml配置文件内容:

    <schema name="test_sns" checkSQLschema="false" sqlMaxLimit="100">
            <table name="sns_user_relation" primaryKey="_id" autoIncrement="true" dataNode="dn$1-8" rule="rule2" />
    </schema>

    <dataNode name="dn$1-4" dataHost="host1" database="db$1-4" />
    <dataNode name="dn$5-8" dataHost="host2" database="db$5-8" />

    <dataHost name="host1" maxCon="1000" minCon="10" balance="0"
            writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
            <heartbeat>select user()</heartbeat>
            <writeHost host="host1M1" url="192.168.6.211:3306" user="root"
                    password="123456">
            </writeHost>
    </dataHost>

    <dataHost name="host2" maxCon="1000" minCon="10" balance="0"
            writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">
            <heartbeat>select user()</heartbeat>
            <writeHost host="host2M1" url="192.168.6.210:3306" user="root"
                    password="123456">
            </writeHost>
    </dataHost>

在运行insert into语句的时候报错: mysql> insert into sns_user_relation (user_id, rel_id, rel_type, is_valid, create_time, update_time, group_id, uid) values (149,145,3,1,NOW(),NOW(),0,7001317); ERROR 1003 (HY000): mycat sequnce err.java.lang.NumberFormatException: null

日志错误: 09/23 23:14:54.712 ERROR Thread-1 -MyCATSequenceProcessor.executeSeq(SesionSQLPair) java.lang.NumberFormatException: null at java.lang.Long.parseLong(Long.java:404) at java.lang.Long.parseLong(Long.java:483) at org.opencloudb.sequence.handler.IncrSequenceHandler.nextId(IncrSequenceHandler.java:62) at org.opencloudb.parser.druid.DruidSequenceHandler.getExecuteSql(DruidSequenceHandler.java:58) at org.opencloudb.route.MyCATSequnceProcessor.executeSeq(MyCATSequnceProcessor.java:84) at org.opencloudb.route.MyCATSequnceProcessor.access$200(MyCATSequnceProcessor.java:18) at org.opencloudb.route.MyCATSequnceProcessor$ExecuteThread.run(MyCATSequnceProcessor.java:104)

59090939 commented 7 years ago

你的折分算法在那儿，你又没有指定

SecretSun commented 7 years ago

server.xml 需要添加 sequnceHandlerType 全局序列类型 0本地文件 1数据库 2时间戳
详细请见文档第九章全局序列号

terrytomas commented 6 years ago

遇到同样的问题，请问server里面做了配置并且也将本地的properties文件修改了，但是还是报同样的错误

MissThee commented 5 years ago

碰到了ID自增不能的问题。。。。版本使用的是mysql5.7.19， mycat-1.6，是我用错版本了吗 server.xml配置，默认配置，仅修改了这个参数

<property name="processorBufferPoolType">0</property>

schema.xml配置

<schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="10000"> 
       <table name="table1" primaryKey="id" autoIncrement="true" dataNode="dn1,dn2" rule="auto-sharding-long" />  
</schema> 

<dataNode name="dn1" dataHost="localhost1" database="test_db1" />
<dataNode name="dn2" dataHost="localhost2" database="test_db2" />

<dataHost name="localhost1" maxCon="1000" minCon="10" balance="0" writeType="0" dbType="mysql" dbDriver="native" >
    <heartbeat>select user()</heartbeat> 
    <writeHost host="hostM1" url="localhost:13306" user="root" password="1234" />
</dataHost>
<dataHost name="localhost2" maxCon="1000" minCon="10" balance="0" writeType="0" dbType="mysql" dbDriver="native" >
    <heartbeat>select user()</heartbeat> 
    <writeHost host="hostM2" url="localhost:23306" user="root" password="1234" />
</dataHost>

squence_conf.properties配置

#default global sequence
GLOBAL.HISIDS=
GLOBAL.MINID=10001
GLOBAL.MAXID=20000
GLOBAL.CURID=10000

使用mycat 1.查询，没问题。 2.插入，语句中指定了id值，没问题 3.插入，使用next value for MYCATSEQ_GLOBAL作为id值，没问题

insert into table1 (id,name) values(next value for MYCATSEQ_GLOBAL, 'a name');

4.插入，语句中不设置id值

insert into table1 (name) values('a name');

报错：

insert into table1 (`name`) values('a name')
> 1003 - mycat sequnce err.java.lang.NumberFormatException: null
> 时间: 0.001s

我还以为是mycat自动填充自增id的使用mycat的本地文件id自增后插入时都要使用next value for MYCATSEQ_GLOBAL自己取id值吗？？？

jllyzzd commented 5 years ago

多测试一下就知道了，语句里面需要加上类似这样。"id"=>'next value for MYCATSEQ_COMPANY'。

MyCATApache / Mycat-doc

Mycat-1.4 配置主键自增长的问题. #32