meta data check should ignore AUTO_INCREMENT difference

[Lordess]

• dble version：
  2.18.10.1 release

• preconditions ：

1. Physical tables have auto-increment column:

CREATE TABLE sharding_table (
id BIGINT PRIMARY KEY AUTO_INCREMENT,
clientNum CHAR(20) NOT NULL )

2. Records are distributed inequally on dataNodes( physical tables ). Thus, AUTO_INCREMENT is different between physical tables in the result of SHOW CREATE TABLE.

-- Execute belows on dble 8066 AFTER dble and dble are ready
INSERT INTO testdb.sharding_table
VALUES
('00000000000000000001'),
('00000000000000000002'),
('00000000000000000003');

• configs：

schema.xml

<schema name="testdb">
    <table name="sharding_table" rule="mod-by-clientNum" dataNodes="dn1,dn2" />
</schema>

rule.xml

<tableRule name="mod-by-clientNum" >
    <rule>
        <columns>clientNum</columns>
        <algorithms>mod-by-two</algorithm>
    </rule>
</tableRule>

<function name="mod-by-two" class="Hash">
    <property name="partitionCount">2</property>
    <property name="partitionLength">1</property>
</function>

server.xml
ommitted

• steps：
  step1. Issue a RELOAD command to dble on 9066.
• expect result：
  1. Sucess.
• real result：
  1. Got failure. And you will got errors like belows.

yyyy-mm-dd hh:MM:SS.sss  WARN [complexQueryExecutorXXX] (com.actiontech.dble.meta.table.MultiTablesMetaHandler.consistentWarning(MultiTablesMetaHandler.java:192)) - Table[sharding_table] structure are not consistent in different data node!
yyyy-mm-dd hh:MM:SS.sss  INFO [complexQueryExecutorXXX] (com.actiontech.dble.meta.table.MultiTablesMetaHandler.consistentWarning(MultiTablesMetaHandler.java:195)) - Currently detected:
yyyy-mm-dd hh:MM:SS.sss  INFO [complexQueryExecutorXXX] (com.actiontech.dble.meta.table.MultiTablesMetaHandler.consistentWarning(MultiTablesMetaHandler.java:202)) - DataNode:[dn1]:CREATE TABLE `sharding_table` (
  `id` BIGINT PRIMARY KEY AUTO_INCREMENT,
  `clientNum` CHAR(20) NOT NULL )
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin=[dn1]
yyyy-mm-dd hh:MM:SS.sss  INFO [complexQueryExecutorXXX] (com.actiontech.dble.meta.table.MultiTablesMetaHandler.consistentWarning(MultiTablesMetaHandler.java:202)) - DataNode:[dn2]:CREATE TABLE `sharding_table` (
  `id` BIGINT PRIMARY KEY AUTO_INCREMENT,
  `clientNum` CHAR(20) NOT NULL )
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin=[dn2]

• supplements：
  Why I use auto-increment in a sharding table:
  1. InnoDB has an low level optimization while user asking for inserting records with sorted sequential primary key. I need it.
  2. Values in a business column would change and causing B+ tree rotation and index update. Business column is not ideal primary key.
  3. Business logic has no interest in maintaining such an internal use column. Auto increment is a good idea for self maintaining.
  4. Only do I use "excessive sharding" solution so that I have no need to merge physical tables. Primary key conflict won't happen.

/label ~BUG

[yanhuqing666]

the result of check @@meatadata may not correct.

[yanhuqing666]

to @Lordess :
In your case, column id must NOT config as primaryKey in table's attribute of schema.xml, else it will cache a single datanode as routing result

[FlyingMao]

verified version： 5.6.29-dble-9.9.9.9-53ddf3e-20181214052815