search

MyCATApache / Mycat-Server

GNU General Public License v2.0
9.51k stars 3.85k forks source link

io.mycat.net.FrontendConnection.writeErrMessage(FrontendConnection.java:210) Unknown system variable 'query_cache_size'java.lang.Thread #2559

Open micyo202 opened 4 years ago

micyo202 commented 4 years ago

问题

服务启动正常，通过应用执行任意数据库操作时，mycat报错，如下：

(io.mycat.net.FrontendConnection.writeErrMessage(FrontendConnection.java:210)) - ServerConnection [id=1, schema=TESTDB, host=127.0.0.1, user=root,txIsolation=3, autocommit=true, schema=TESTDB, executeSql=null]Unknown system variable 'query_cache_size'java.lang.Thread .getStackTrace1559

信息

描述

mycat/lib目录中移除mysql-connector-java-5.1.35.jar包，替换为mysql-connector-java-8.0.13.jar包

schema.xml配置：

<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">

    <schema name="TESTDB" checkSQLschema="true" sqlMaxLimit="100">
        <table name="temp_order" dataNode="dn1,dn2" rule="mod-long" />
    </schema>

    <dataNode name="dn1" dataHost="localhost1" database="db1" />
    <dataNode name="dn2" dataHost="localhost1" database="db2" />

    <dataHost name="localhost1" maxCon="1000" minCon="10" balance="0"
              writeType="0" dbType="mysql" dbDriver="jdbc" switchType="1"  slaveThreshold="100">
        <heartbeat>select user()</heartbeat>
        <writeHost host="hostM1" url="jdbc:mysql://192.168.1.200:3306?useUnicode=true&amp;characterEncoding=utf8&amp;serverTimezone=Asia/Shanghai&amp;useSSL=false&amp;allowPublicKeyRetrieval=true" user="数据库用户名"
                   password="数据库密码">
        </writeHost>
    </dataHost>

</mycat:schema>

rule.xml配置：

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mycat:rule SYSTEM "rule.dtd">
<mycat:rule xmlns:mycat="http://io.mycat/">

    <tableRule name="mod-long">
        <rule>
            <columns>id</columns>
            <algorithm>mod-long</algorithm>
        </rule>
    </tableRule>

    <function name="mod-long" class="io.mycat.route.function.PartitionByMod">
        <!-- how many data nodes -->
        <property name="count">2</property>
    </function>

</mycat:rule>

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="defaultSqlParser">druidparser</property>
    <property name="useHandshakeV10">1</property>
    <property name="mutiNodeLimitType">1</property>
    <property name="charset">utf8</property>
    <property name="serverPort">8066</property>
    <property name="managerPort">9066</property>
    </system>

    <user name="root" defaultAccount="true">
        <property name="password">123456</property>
        <property name="schemas">TESTDB</property>
    </user>

</mycat:server>

配置完成后，启动mycat 服务，bin/mycat start 服务启动正常，日志如下：

wrapper.log 

STATUS | wrapper  | 2020/07/22 15:56:08 | --> Wrapper Started as Daemon
STATUS | wrapper  | 2020/07/22 15:56:08 | Launching a JVM...
INFO   | jvm 1    | 2020/07/22 15:56:09 | Wrapper (Version 3.2.3) http://wrapper.tanukisoftware.org
INFO   | jvm 1    | 2020/07/22 15:56:09 |   Copyright 1999-2006 Tanuki Software, Inc.  All Rights Reserved.
INFO   | jvm 1    | 2020/07/22 15:56:09 | 
INFO   | jvm 1    | 2020/07/22 15:56:10 | Loading class `com.mysql.jdbc.Driver'. This is deprecated. The new driver class is `com.mysql.cj.jdbc.Driver'. The driver is automatically registered via the SPI and manual loading of the driver class is generally unnecessary.
INFO   | jvm 1    | 2020/07/22 15:56:11 | MyCAT Server startup successfully. see logs in logs/mycat.log
STATUS | wrapper  | 2020/07/22 16:01:16 | TERM trapped.  Shutting down.
STATUS | wrapper  | 2020/07/22 16:01:17 | <-- Wrapper Stopped
STATUS | wrapper  | 2020/07/22 16:14:09 | --> Wrapper Started as Daemon
STATUS | wrapper  | 2020/07/22 16:14:09 | Launching a JVM...
INFO   | jvm 1    | 2020/07/22 16:14:10 | Wrapper (Version 3.2.3) http://wrapper.tanukisoftware.org
INFO   | jvm 1    | 2020/07/22 16:14:10 |   Copyright 1999-2006 Tanuki Software, Inc.  All Rights Reserved.
INFO   | jvm 1    | 2020/07/22 16:14:10 | 
INFO   | jvm 1    | 2020/07/22 16:14:11 | Loading class `com.mysql.jdbc.Driver'. This is deprecated. The new driver class is `com.mysql.cj.jdbc.Driver'. The driver is automatically registered via the SPI and manual loading of the driver class is generally unnecessary.
INFO   | jvm 1    | 2020/07/22 16:14:12 | MyCAT Server startup successfully. see logs in logs/mycat.log

mycat.log

2020-07-22 16:14:11.425  INFO [WrapperSimpleAppMain] (io.mycat.backend.datasource.PhysicalDBPool.<init>(PhysicalDBPool.java:127)) - total resources of dataHost localhost1 is :1
2020-07-22 16:14:11.431  INFO [WrapperSimpleAppMain] (io.mycat.cache.CacheService.createLayeredPool(CacheService.java:125)) - create layer cache pool TableID2DataNodeCache of type encache ,default cache size 10000 ,default expire seconds18000
2020-07-22 16:14:11.432  INFO [WrapperSimpleAppMain] (io.mycat.cache.DefaultLayedCachePool.createChildCache(DefaultLayedCachePool.java:80)) - create child Cache: TESTDB_ORDERS for layered cache TableID2DataNodeCache, size 50000, expire seconds 18000
2020-07-22 16:14:11.530  INFO [WrapperSimpleAppMain] (io.mycat.config.classloader.DynaClassLoader.<init>(DynaClassLoader.java:34)) - dyna class load from ./catlet,and auto check for class file modified every 60 seconds
2020-07-22 16:14:11.552  INFO [WrapperSimpleAppMain] (io.mycat.MycatServer.startup(MycatServer.java:328)) - MyCat is ready to startup ...
2020-07-22 16:14:11.553  INFO [WrapperSimpleAppMain] (io.mycat.MycatServer.startup(MycatServer.java:340)) - Startup processors ...,total processors:8,aio thread pool size:16    
 each process allocated socket buffer pool  bytes ,a page size:2097152  a page's chunk number(PageSize/ChunkSize) is:512  buffer page's number is:160
2020-07-22 16:14:11.553  INFO [WrapperSimpleAppMain] (io.mycat.MycatServer.startup(MycatServer.java:341)) - sysconfig params:SystemConfig [processorBufferLocalPercent=100, frontSocketSoRcvbuf=1048576, frontSocketSoSndbuf=4194304, backSocketSoRcvbuf=4194304, backSocketSoSndbuf=1048576, frontSocketNoDelay=1, backSocketNoDelay=1, maxStringLiteralLength=65535, frontWriteQueueSize=2048, bindIp=0.0.0.0, serverPort=8066, managerPort=9066, charset=utf8, processors=8, processorExecutor=16, timerExecutor=2, managerExecutor=2, idleTimeout=1800000, catletClassCheckSeconds=60, sqlExecuteTimeout=300, processorCheckPeriod=1000, dataNodeIdleCheckPeriod=300000, dataNodeHeartbeatPeriod=10000, clusterHeartbeatUser=_HEARTBEAT_USER_, clusterHeartbeatPass=_HEARTBEAT_PASS_, clusterHeartbeatPeriod=5000, clusterHeartbeatTimeout=10000, clusterHeartbeatRetry=10, txIsolation=3, parserCommentVersion=50148, sqlRecordCount=10, bufferPoolPageSize=2097152, bufferPoolChunkSize=4096, bufferPoolPageNumber=160, maxResultSet=524288, bigResultSizeSqlCount=10, bufferUsagePercent=80, flowControlRejectStrategy=0, clearBigSqLResultSetMapMs=600000, defaultMaxLimit=100, sequenceHandlerType=0, sqlInterceptor=io.mycat.server.interceptor.impl.DefaultSqlInterceptor, sqlInterceptorType=select, sqlInterceptorFile=/Users/apple/Documents/software/mycat-server-1.6.7.5/logs/sql.txt, mutiNodeLimitType=1, mutiNodePatchSize=100, defaultSqlParser=druidparser, usingAIO=0, packetHeaderSize=4, maxPacketSize=16777216, mycatNodeId=1,ignoreUnknownCommand=0,parallExecute=0,removeGraveAccent=1]
2020-07-22 16:14:11.801  INFO [WrapperSimpleAppMain] (io.mycat.MycatServer.startup(MycatServer.java:450)) - using nio network handler 
2020-07-22 16:14:11.828  INFO [WrapperSimpleAppMain] (io.mycat.MycatServer.startup(MycatServer.java:466)) - $_MyCatManager is started and listening on 9066
2020-07-22 16:14:11.830  INFO [WrapperSimpleAppMain] (io.mycat.MycatServer.startup(MycatServer.java:470)) - $_MyCatServer is started and listening on 8066
2020-07-22 16:14:11.830  INFO [WrapperSimpleAppMain] (io.mycat.MycatServer.startup(MycatServer.java:472)) - ===============================================
2020-07-22 16:14:11.831  INFO [WrapperSimpleAppMain] (io.mycat.MycatServer.startup(MycatServer.java:476)) - Initialize dataHost ...
2020-07-22 16:14:11.831  INFO [WrapperSimpleAppMain] (io.mycat.backend.datasource.PhysicalDBPool.initSource(PhysicalDBPool.java:394)) - init backend mysql source ,create connections total 10 for hostM1 index :0
2020-07-22 16:14:11.832  INFO [WrapperSimpleAppMain] (io.mycat.backend.datasource.PhysicalDatasource.getConnection(PhysicalDatasource.java:564)) - no ilde connection in pool 1355875432 ,create new connection for hostM1 of schema db2 totalConnectionCount: 0 increamentCount: 1
2020-07-22 16:14:11.832  INFO [WrapperSimpleAppMain] (io.mycat.backend.datasource.PhysicalDatasource.getConnection(PhysicalDatasource.java:564)) - no ilde connection in pool 1355875432 ,create new connection for hostM1 of schema db1 totalConnectionCount: 0 increamentCount: 2
2020-07-22 16:14:11.833  INFO [WrapperSimpleAppMain] (io.mycat.backend.datasource.PhysicalDatasource.getConnection(PhysicalDatasource.java:564)) - no ilde connection in pool 1355875432 ,create new connection for hostM1 of schema db2 totalConnectionCount: 0 increamentCount: 3
2020-07-22 16:14:11.833  INFO [WrapperSimpleAppMain] (io.mycat.backend.datasource.PhysicalDatasource.getConnection(PhysicalDatasource.java:564)) - no ilde connection in pool 1355875432 ,create new connection for hostM1 of schema db1 totalConnectionCount: 0 increamentCount: 4
2020-07-22 16:14:11.834  INFO [WrapperSimpleAppMain] (io.mycat.backend.datasource.PhysicalDatasource.getConnection(PhysicalDatasource.java:564)) - no ilde connection in pool 1355875432 ,create new connection for hostM1 of schema db2 totalConnectionCount: 0 increamentCount: 5
2020-07-22 16:14:11.834  INFO [WrapperSimpleAppMain] (io.mycat.backend.datasource.PhysicalDatasource.getConnection(PhysicalDatasource.java:564)) - no ilde connection in pool 1355875432 ,create new connection for hostM1 of schema db1 totalConnectionCount: 0 increamentCount: 6
2020-07-22 16:14:11.834  INFO [WrapperSimpleAppMain] (io.mycat.backend.datasource.PhysicalDatasource.getConnection(PhysicalDatasource.java:564)) - no ilde connection in pool 1355875432 ,create new connection for hostM1 of schema db2 totalConnectionCount: 0 increamentCount: 7
2020-07-22 16:14:11.835  INFO [WrapperSimpleAppMain] (io.mycat.backend.datasource.PhysicalDatasource.getConnection(PhysicalDatasource.java:564)) - no ilde connection in pool 1355875432 ,create new connection for hostM1 of schema db1 totalConnectionCount: 0 increamentCount: 8
2020-07-22 16:14:11.835  INFO [WrapperSimpleAppMain] (io.mycat.backend.datasource.PhysicalDatasource.getConnection(PhysicalDatasource.java:564)) - no ilde connection in pool 1355875432 ,create new connection for hostM1 of schema db2 totalConnectionCount: 0 increamentCount: 9
2020-07-22 16:14:11.835  INFO [WrapperSimpleAppMain] (io.mycat.backend.datasource.PhysicalDatasource.getConnection(PhysicalDatasource.java:564)) - no ilde connection in pool 1355875432 ,create new connection for hostM1 of schema db1 totalConnectionCount: 0 increamentCount: 10
2020-07-22 16:14:12.284  INFO [BusinessExecutor3] (io.mycat.backend.datasource.PhysicalDatasource$1$1.connectionAcquired(PhysicalDatasource.java:514)) - connection id is 9
2020-07-22 16:14:12.284  INFO [BusinessExecutor9] (io.mycat.backend.datasource.PhysicalDatasource$1$1.connectionAcquired(PhysicalDatasource.java:514)) - connection id is 10
2020-07-22 16:14:12.284  INFO [BusinessExecutor4] (io.mycat.backend.datasource.PhysicalDatasource$1$1.connectionAcquired(PhysicalDatasource.java:514)) - connection id is 4
2020-07-22 16:14:12.284  INFO [BusinessExecutor7] (io.mycat.backend.datasource.PhysicalDatasource$1$1.connectionAcquired(PhysicalDatasource.java:514)) - connection id is 6
2020-07-22 16:14:12.284  INFO [BusinessExecutor2] (io.mycat.backend.datasource.PhysicalDatasource$1$1.connectionAcquired(PhysicalDatasource.java:514)) - connection id is 8
2020-07-22 16:14:12.284  INFO [BusinessExecutor5] (io.mycat.backend.datasource.PhysicalDatasource$1$1.connectionAcquired(PhysicalDatasource.java:514)) - connection id is 3
2020-07-22 16:14:12.284  INFO [BusinessExecutor6] (io.mycat.backend.datasource.PhysicalDatasource$1$1.connectionAcquired(PhysicalDatasource.java:514)) - connection id is 5
2020-07-22 16:14:12.284  INFO [BusinessExecutor0] (io.mycat.backend.datasource.PhysicalDatasource$1$1.connectionAcquired(PhysicalDatasource.java:514)) - connection id is 1
2020-07-22 16:14:12.284  INFO [BusinessExecutor8] (io.mycat.backend.datasource.PhysicalDatasource$1$1.connectionAcquired(PhysicalDatasource.java:514)) - connection id is 2
2020-07-22 16:14:12.285  INFO [BusinessExecutor7] (io.mycat.backend.mysql.nio.handler.GetConnectionHandler.connectionAcquired(GetConnectionHandler.java:67)) - connected successfuly JDBCConnection [id=6,autocommit=true,pool=io.mycat.backend.jdbc.JDBCDatasource@50d10468, schema=db1, dbType=MYSQL, oldSchema=null, packetId=0, txIsolation=0, running=false, borrowed=true, host=192.168.1.200, port=3306, con=com.mysql.cj.jdbc.ConnectionImpl@415d4e29, respHandler=null, attachement=null, headerOutputed=false, modifiedSQLExecuted=false, startTime=1595405651838, lastTime=1595405652285, isSpark=false, processor=io.mycat.net.NIOProcessor@6b178826]
2020-07-22 16:14:12.285  INFO [BusinessExecutor6] (io.mycat.backend.mysql.nio.handler.GetConnectionHandler.connectionAcquired(GetConnectionHandler.java:67)) - connected successfuly JDBCConnection [id=5,autocommit=true,pool=io.mycat.backend.jdbc.JDBCDatasource@50d10468, schema=db2, dbType=MYSQL, oldSchema=null, packetId=0, txIsolation=0, running=false, borrowed=true, host=192.168.1.200, port=3306, con=com.mysql.cj.jdbc.ConnectionImpl@691e0c98, respHandler=null, attachement=null, headerOutputed=false, modifiedSQLExecuted=false, startTime=1595405651838, lastTime=1595405652285, isSpark=false, processor=io.mycat.net.NIOProcessor@7a87a42f]
2020-07-22 16:14:12.285  INFO [BusinessExecutor5] (io.mycat.backend.mysql.nio.handler.GetConnectionHandler.connectionAcquired(GetConnectionHandler.java:67)) - connected successfuly JDBCConnection [id=3,autocommit=true,pool=io.mycat.backend.jdbc.JDBCDatasource@50d10468, schema=db1, dbType=MYSQL, oldSchema=null, packetId=0, txIsolation=0, running=false, borrowed=true, host=192.168.1.200, port=3306, con=com.mysql.cj.jdbc.ConnectionImpl@12f9741c, respHandler=null, attachement=null, headerOutputed=false, modifiedSQLExecuted=false, startTime=1595405651838, lastTime=1595405652285, isSpark=false, processor=io.mycat.net.NIOProcessor@55a7fed8]
2020-07-22 16:14:12.285  INFO [BusinessExecutor2] (io.mycat.backend.mysql.nio.handler.GetConnectionHandler.connectionAcquired(GetConnectionHandler.java:67)) - connected successfuly JDBCConnection [id=8,autocommit=true,pool=io.mycat.backend.jdbc.JDBCDatasource@50d10468, schema=db2, dbType=MYSQL, oldSchema=null, packetId=0, txIsolation=0, running=false, borrowed=true, host=192.168.1.200, port=3306, con=com.mysql.cj.jdbc.ConnectionImpl@1cb017ba, respHandler=null, attachement=null, headerOutputed=false, modifiedSQLExecuted=false, startTime=1595405651838, lastTime=1595405652285, isSpark=false, processor=io.mycat.net.NIOProcessor@389613a7]
2020-07-22 16:14:12.285  INFO [BusinessExecutor4] (io.mycat.backend.mysql.nio.handler.GetConnectionHandler.connectionAcquired(GetConnectionHandler.java:67)) - connected successfuly JDBCConnection [id=4,autocommit=true,pool=io.mycat.backend.jdbc.JDBCDatasource@50d10468, schema=db2, dbType=MYSQL, oldSchema=null, packetId=0, txIsolation=0, running=false, borrowed=true, host=192.168.1.200, port=3306, con=com.mysql.cj.jdbc.ConnectionImpl@6f7460d5, respHandler=null, attachement=null, headerOutputed=false, modifiedSQLExecuted=false, startTime=1595405651838, lastTime=1595405652285, isSpark=false, processor=io.mycat.net.NIOProcessor@364695ca]
2020-07-22 16:14:12.285  INFO [BusinessExecutor9] (io.mycat.backend.mysql.nio.handler.GetConnectionHandler.connectionAcquired(GetConnectionHandler.java:67)) - connected successfuly JDBCConnection [id=10,autocommit=true,pool=io.mycat.backend.jdbc.JDBCDatasource@50d10468, schema=db1, dbType=MYSQL, oldSchema=null, packetId=0, txIsolation=0, running=false, borrowed=true, host=192.168.1.200, port=3306, con=com.mysql.cj.jdbc.ConnectionImpl@24153092, respHandler=null, attachement=null, headerOutputed=false, modifiedSQLExecuted=false, startTime=1595405651838, lastTime=1595405652285, isSpark=false, processor=io.mycat.net.NIOProcessor@4da8ad86]
2020-07-22 16:14:12.285  INFO [BusinessExecutor0] (io.mycat.backend.mysql.nio.handler.GetConnectionHandler.connectionAcquired(GetConnectionHandler.java:67)) - connected successfuly JDBCConnection [id=1,autocommit=true,pool=io.mycat.backend.jdbc.JDBCDatasource@50d10468, schema=db2, dbType=MYSQL, oldSchema=null, packetId=0, txIsolation=0, running=false, borrowed=true, host=192.168.1.200, port=3306, con=com.mysql.cj.jdbc.ConnectionImpl@2370075b, respHandler=null, attachement=null, headerOutputed=false, modifiedSQLExecuted=false, startTime=1595405651838, lastTime=1595405652285, isSpark=false, processor=io.mycat.net.NIOProcessor@5ae3c4df]
2020-07-22 16:14:12.285  INFO [BusinessExecutor3] (io.mycat.backend.mysql.nio.handler.GetConnectionHandler.connectionAcquired(GetConnectionHandler.java:67)) - connected successfuly JDBCConnection [id=9,autocommit=true,pool=io.mycat.backend.jdbc.JDBCDatasource@50d10468, schema=db1, dbType=MYSQL, oldSchema=null, packetId=0, txIsolation=0, running=false, borrowed=true, host=192.168.1.200, port=3306, con=com.mysql.cj.jdbc.ConnectionImpl@4a1e4747, respHandler=null, attachement=null, headerOutputed=false, modifiedSQLExecuted=false, startTime=1595405651838, lastTime=1595405652285, isSpark=false, processor=io.mycat.net.NIOProcessor@5ae3c4df]
2020-07-22 16:14:12.285  INFO [BusinessExecutor8] (io.mycat.backend.mysql.nio.handler.GetConnectionHandler.connectionAcquired(GetConnectionHandler.java:67)) - connected successfuly JDBCConnection [id=2,autocommit=true,pool=io.mycat.backend.jdbc.JDBCDatasource@50d10468, schema=db2, dbType=MYSQL, oldSchema=null, packetId=0, txIsolation=0, running=false, borrowed=true, host=192.168.1.200, port=3306, con=com.mysql.cj.jdbc.ConnectionImpl@2b406fde, respHandler=null, attachement=null, headerOutputed=false, modifiedSQLExecuted=false, startTime=1595405651838, lastTime=1595405652285, isSpark=false, processor=io.mycat.net.NIOProcessor@4da8ad86]
2020-07-22 16:14:12.292  INFO [BusinessExecutor1] (io.mycat.backend.datasource.PhysicalDatasource$1$1.connectionAcquired(PhysicalDatasource.java:514)) - connection id is 7
2020-07-22 16:14:12.292  INFO [BusinessExecutor1] (io.mycat.backend.mysql.nio.handler.GetConnectionHandler.connectionAcquired(GetConnectionHandler.java:67)) - connected successfuly JDBCConnection [id=7,autocommit=true,pool=io.mycat.backend.jdbc.JDBCDatasource@50d10468, schema=db1, dbType=MYSQL, oldSchema=null, packetId=0, txIsolation=0, running=false, borrowed=true, host=192.168.1.200, port=3306, con=com.mysql.cj.jdbc.ConnectionImpl@57fe8d10, respHandler=null, attachement=null, headerOutputed=false, modifiedSQLExecuted=false, startTime=1595405651838, lastTime=1595405652292, isSpark=false, processor=io.mycat.net.NIOProcessor@1ff30675]
2020-07-22 16:14:12.373  INFO [WrapperSimpleAppMain] (io.mycat.backend.datasource.PhysicalDBPool.initSource(PhysicalDBPool.java:419)) - init result :finished 10 success 10 target count:10
2020-07-22 16:14:12.374  INFO [WrapperSimpleAppMain] (io.mycat.backend.datasource.PhysicalDBPool.init(PhysicalDBPool.java:365)) - localhost1 index:0 init success
2020-07-22 16:14:12.382  INFO [WrapperSimpleAppMain] (io.mycat.MycatServer.startup(MycatServer.java:515)) - ===============================================
2020-07-22 16:14:12.382  INFO [WrapperSimpleAppMain] (io.mycat.MycatServer.startup(MycatServer.java:516)) - Perform XA recovery log ...
2020-07-22 16:14:12.383  INFO [WrapperSimpleAppMain] (io.mycat.MycatServer.startup(MycatServer.java:520)) - Perform XA recovery log end...

SpringBoot应用配置

application.yml

spring:
  datasource:
    druid:
      url: jdbc:mysql://localhost:8066/TESTDB
      username: root
      password: 123456
      driver-class-name: com.mysql.cj.jdbc.Driver

应用服务正常启动，但当执行任意数据库操作时,无法应，查看mycat.log日志如下，并一直循环打印该错误日志

2020-07-22 16:21:03.197  INFO [$_NIOREACTOR-1-RW] (io.mycat.net.handler.FrontendAuthenticator.success(FrontendAuthenticator.java:226)) - ServerConnection [id=1, schema=TESTDB, host=127.0.0.1, user=root,txIsolation=3, autocommit=true, schema=TESTDB, executeSql=null]'root' login success
2020-07-22 16:21:03.490  WARN [BusinessExecutor10] (io.mycat.backend.mysql.nio.handler.SingleNodeHandler.backConnectionErr(SingleNodeHandler.java:284)) - execute  sql err : errno:1193 Unknown system variable 'query_cache_size' con:JDBCConnection [id=6,autocommit=true,pool=io.mycat.backend.jdbc.JDBCDatasource@50d10468, schema=db1, dbType=MYSQL, oldSchema=db1, packetId=1, txIsolation=0, running=false, borrowed=true, host=192.168.1.200, port=3306, con=com.mysql.cj.jdbc.ConnectionImpl@415d4e29, respHandler=SingleNodeHandler [node=dn1{/* mysql-connector-java-8.0.20 (Revision: afc0a13cd3c5a0bf57eaa809ee0ee6df1fd5ac9b) */SELECT  @@session.auto_increment_increment AS auto_increment_increment, @@character_set_client AS character_set_client, @@character_set_connection AS character_set_connection, @@character_set_results AS character_set_results, @@character_set_server AS character_set_server, @@collation_server AS collation_server, @@collation_connection AS collation_connection, @@init_connect AS init_connect, @@interactive_timeout AS interactive_timeout, @@license AS license, @@lower_case_table_names AS lower_case_table_names, @@max_allowed_packet AS max_allowed_packet, @@net_write_timeout AS net_write_timeout, @@performance_schema AS performance_schema, @@query_cache_size AS query_cache_size, @@query_cache_type AS query_cache_type, @@sql_mode AS sql_mode, @@system_time_zone AS system_time_zone, @@time_zone AS time_zone, @@tx_isolation AS transaction_isolation, @@wait_timeout AS wait_timeout}, packetId=1], attachement=dn1{/* mysql-connector-java-8.0.20 (Revision: afc0a13cd3c5a0bf57eaa809ee0ee6df1fd5ac9b) */SELECT  @@session.auto_increment_increment AS auto_increment_increment, @@character_set_client AS character_set_client, @@character_set_connection AS character_set_connection, @@character_set_results AS character_set_results, @@character_set_server AS character_set_server, @@collation_server AS collation_server, @@collation_connection AS collation_connection, @@init_connect AS init_connect, @@interactive_timeout AS interactive_timeout, @@license AS license, @@lower_case_table_names AS lower_case_table_names, @@max_allowed_packet AS max_allowed_packet, @@net_write_timeout AS net_write_timeout, @@performance_schema AS performance_schema, @@query_cache_size AS query_cache_size, @@query_cache_type AS query_cache_type, @@sql_mode AS sql_mode, @@system_time_zone AS system_time_zone, @@time_zone AS time_zone, @@tx_isolation AS transaction_isolation, @@wait_timeout AS wait_timeout}, headerOutputed=false, modifiedSQLExecuted=false, startTime=1595405651838, lastTime=1595406063297, isSpark=false, processor=io.mycat.net.NIOProcessor@6b178826] frontend host:127.0.0.1/49311/root
2020-07-22 16:21:03.493 ERROR [BusinessExecutor10] (io.mycat.net.FrontendConnection.writeErrMessage(FrontendConnection.java:210)) - ServerConnection [id=1, schema=TESTDB, host=127.0.0.1, user=root,txIsolation=3, autocommit=true, schema=TESTDB, executeSql=null]Unknown system variable 'query_cache_size'java.lang.Thread .getStackTrace1559
io.mycat.net.FrontendConnection .getStack224
io.mycat.net.FrontendConnection .writeErrMessage210
io.mycat.backend.mysql.nio.handler.SingleNodeHandler .backConnectionErr311
io.mycat.backend.mysql.nio.handler.SingleNodeHandler .errorResponse272
io.mycat.backend.jdbc.JDBCConnection .executeSQL378
io.mycat.backend.jdbc.JDBCConnection .access$00048
io.mycat.backend.jdbc.JDBCConnection$1 .run855
java.util.concurrent.ThreadPoolExecutor .runWorker1149
java.util.concurrent.ThreadPoolExecutor$Worker .run624
java.lang.Thread .run748
 write errorMsg:{} error
2020-07-22 16:21:03.514  INFO [$_NIOREACTOR-1-RW] (io.mycat.net.AbstractConnection.close(AbstractConnection.java:520)) - close connection,reason:stream closed ,ServerConnection [id=1, schema=TESTDB, host=127.0.0.1, user=root,txIsolation=3, autocommit=true, schema=TESTDB, executeSql=null]
junwen12221 commented 4 years ago

估计与客户端jdbc驱动识别mycat中的mysql版本号,是5系列的,发送了5系列的sql,可能就是报错那条,然后后端数据库是8系列的,结果报错

funnyAnt commented 4 years ago

客户端驱动我们测试过，最高5.1.35， 再高就会各种意想不到的异常。

mark420524 commented 2 years ago

一样我也出现这个问题了，不过我是通过jdbc链接的

JinYeGang commented 2 years ago

我也是这个问题，请问楼主后来解决了吗

Direct-D commented 2 years ago

我这里使用 mycat 1.6.7.6 版本的测试，只修改了 schema.xml 和 server.vml ，这里连接mysql使用的是他默认的“native”，mysql 版本 5.7 ，启动登陆都是正常的，然后发现如果使用 mysql -h ip -P 8066 -u root -p密码，登陆之后在执行查表（show tables;）操作后，mycat.log日志就会出现这样一个error信息 “2022-06-29 11:16:14.449 ERROR [$_NIOREACTOR-1-RW] (io.mycat.net.FrontendConnection.writeErrMessage(FrontendConnection.java:231)) - ServerConnection [id=1, schema=demo, host=127.0.0.1, user=root,txIsolation=3, autocommit=true, schema=demo, executeSql=select @@command tb_student]Table 'demo.tb_student' doesn't existjava.lang.Thread .getStackTrace1559 ”，但是如果在登陆时加上 “-A” 参数，就不会出现这个现象。请问楼主后来解决这个问题了吗？谢谢。