配置全局表场景下DBLE下发解析后的group by 语句给MySQL8.0，返回报错，分片表场景下已修复此问题
TommyZC opened this issue · 2 comments
TommyZC commented
-
dble version：2.19.03/lts
-
preconditions ：create table test2(id int(11), name char(10));
-
configs：
schema.xml
<?xml version="1.0"?>
<!DOCTYPE dble:schema SYSTEM "schema.dtd">
<dble:schema xmlns:dble="http://dble.cloud/" version="2.19.03.14">
<schema name="testdb">
<!-- random sharding using mod sharind rule -->
<table name="test2" type="global" dataNode="dn1,dn2"/>
</schema>
<dataNode name="dn1" dataHost="host1" database="dbletest1"/>
<dataNode name="dn2" dataHost="host1" database="dbletest2"/>
<dataHost name="host1" maxCon="1000" minCon="10" balance="0" switchType="1" slaveThreshold="100">
<heartbeat>select user()</heartbeat>
<!-- can have multi write hosts -->
<writeHost host="host1" url="localhost:3306" user="root" password="xxxxx">
</writeHost>
</dataHost>
</dble:schema>
rule.xml
server.xml
<?xml version="1.0" encoding="UTF-8"?>
<!-- - - Licensed under the Apache License, Version 2.0 (the "License");
- you may not use this file except in compliance with the License. - You
may obtain a copy of the License at - - http://www.apache.org/licenses/LICENSE-2.0
- - Unless required by applicable law or agreed to in writing, software -
distributed under the License is distributed on an "AS IS" BASIS, - WITHOUT
WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. - See the
License for the specific language governing permissions and - limitations
under the License. -->
<!DOCTYPE dble:server SYSTEM "server.dtd">
<dble:server xmlns:dble="http://dble.cloud/" version="9.9.9.9">
<system>
<!-- base config -->
<!--<property name="bindIp">0.0.0.0</property>-->
<!-- property name="serverPort">8066</property> -->
<!--<property name="managerPort">9066</property> -->
<!-- <property name="processors">1</property>-->
<!--<property name="processorExecutor">32</property> -->
<!--<property name="fakeMySQLVersion">5.6.20</property>-->
<property name="sequnceHandlerType">2</property>
<!-- serverBacklog size,default 2048-->
<property name="serverBacklog">2048</property>
<!--<property name="serverNodeId">1</property>-->
<!--<property name="showBinlogStatusTimeout">60000</property>-->
<!--option-->
<!--<property name="useCompression">1</property>-->
<!--<property name="usingAIO">0</property>-->
<!--<property name="useZKSwitch">true</property>-->
<!--connection -->
<!--<property name="charset">utf-8</property>-->
<!--<property name="maxPacketSize">16777216</property>-->
<!--<property name="txIsolation">3</property>-->
<!--consistency-->
<!-- check the consistency of table structure between nodes,default not -->
<property name="checkTableConsistency">0</property>
<!-- check periodt, he default period is 60000 milliseconds -->
<property name="checkTableConsistencyPeriod">60000</property>
<!-- 1 check the consistency of global table, 0 is not -->
<property name="useGlobleTableCheck">0</property>
<property name="glableTableCheckPeriod">86400000</property>
<!-- heartbeat check period -->
<property name="dataNodeIdleCheckPeriod">300000</property>
<property name="dataNodeHeartbeatPeriod">10000</property>
<!-- processor check conn-->
<property name="processorCheckPeriod">1000</property><!-- unit millisecond -->
<property name="sqlExecuteTimeout">300</property><!-- unit second -->
<property name="idleTimeout">1800000</property><!-- unit millisecond -->
<!-- transaction log -->
<!-- 1 enable record the transaction log, 0 disable -->
<property name="recordTxn">0</property>
<!--<property name="transactionLogBaseDir">/txlogs</property>-->
<!--<property name="transactionLogBaseName">server-tx</property>-->
<!--<property name="transactionRatateSize">16</property><!– unit M –>-->
<!-- XA transaction -->
<!-- use XA transaction ,if the mysql service crash,the unfinished XA commit/rollback will retry for several times
it is the check period for ,default is 1000 milliseconds-->
<property name="xaSessionCheckPeriod">1000</property>
<!-- use XA transaction ,the finished XA log will removed. the default period is 1000 milliseconds-->
<property name="xaLogCleanPeriod">1000</property>
<!-- XA Recovery Log path -->
<!--<property name="XARecoveryLogBaseDir">/tmlogs/</property>-->
<!-- XA Recovery Log name -->
<!--<property name="XARecoveryLogBaseName">tmlog</property>-->
<!-- true is use JoinStrategy, default false-->
<property name="useJoinStrategy">true</property>
<property name="nestLoopConnSize">4</property>
<property name="nestLoopRowsSize">2000</property>
<!-- off Heap unit:bytes-->
<property name="bufferPoolChunkSize ">4096</property>
<property name="bufferPoolPageNumber ">512</property>
<property name="bufferPoolPageSize ">2097152</property>
<!-- sql statistics-->
<!-- 1 means use SQL statistics, 0 means not -->
<property name="useSqlStat">0</property>
<!--<property name="bufferUsagePercent">80</property>-->
<!--<property name="clearBigSqLResultSetMapMs">600000</property>-->
<!--<property name="sqlRecordCount">10</property>-->
<!--<property name="maxResultSet">524288</property>-->
<!-- backSocket unit:bytes-->
<!--<property name="backSocketSoRcvbuf ">4194304</property>-->
<!--<property name="backSocketSoSndbuf">1048576</property>-->
<!--<property name="backSocketNoDelay ">1</property>-->
<!-- frontSocket-->
<!--<property name="frontSocketSoRcvbuf ">1048576</property>-->
<!--<property name="frontSocketSoSndbuf">4194304</property>-->
<!--<property name="frontSocketNoDelay ">1</property>-->
</system>
<!-- firewall config -->
<!--
<firewall>
<whitehost>
<host host="127.0.0.1" user="root"/>
<host host="0:0:0:0:0:0:0:1" user="root"/>
</whitehost>
<blacklist check="true">
<property name="selelctAllow">false</property>
</blacklist>
</firewall>
-->
<user name="dble">
<property name="password">dble</property>
<property name="schemas">testdb</property>
<!-- manager user can't set schema-->
</user>
<user name="root">
<property name="password">root</property>
<property name="manager">true</property>
<!-- table's DML privileges INSERT/UPDATE/SELECT/DELETE -->
<!--
<privileges check="false">
<schema name="TESTDB" dml="0110" >
<table name="tb01" dml="0000"></table>
<table name="tb02" dml="1111"></table>
</schema>
</privileges>
-->
</user>
<user name="user">
<property name="password">user</property>
<property name="schemas">TESTDB</property>
<property name="readOnly">true</property>
</user>
</dble:server>
- steps：
step1. excute "select name from test2 group by name limit 1,100;" - expect result：
return select result - real result：
You have an error in your SQL synax; check the manual that corresponds to your MySQL server version for the right synax to use near 'ASC limit 1,100' at line 1 - supplements：
TommyZC commented
目前分析下来DBLE对全局表语句的解析逻辑与分片表是不一样的，如果是全局表，在baseHandlerBuilder的build()方法中会走noShardBuild()方法，分片表会走buildOwn()方法，noShardingBuild()最终调用的是GlobalVistor()的buildGroupBy()方法，buildOwn()最终调用的是PushDownVisitor的buildGroupBy()方法。GlobalVistor()的buildGroupBy()方法未对group by语法做改造，因此全局表下发给MySQL8.0会返回报错。
PanternBao commented
fix by: #1349