1.6.6.1 connect with MySQL8，ERROR 1184 (HY000): Invalid DataSource:0

beamofsoul commented 5 years ago

ENVIRONMENT:

MYSQL_VERSION=8.0.17-1debian9 MYCAT_VERSION=5.6.29-mycat-1.6.6.1-release-20181031195535 MyCat Server (OpenCloudDB)

CONFIGURATION:

schema.xml:

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

        <schema name="test" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1"></schema>

        <dataNode name="dn1" dataHost="host1" database="test" />

        <dataHost name="host1" maxCon="1000" minCon="10" balance="1" writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">

                <heartbeat>select user()</heartbeat>
                <writeHost host="hostMW" url="127.0.0.1:33061" user="root" password="root">
                        <readHost host="hostMR" url="127.0.0.1:33061" user="root" password="root" />
                </writeHost>

                <writeHost host="hostSW" url="127.0.0.1:33062" user="root" password="root" >
                        <readHost host="hostSR" url="127.0.0.1:33062" user="root" password="root" />
                </writeHost>

        </dataHost>

</mycat:schema>

server.xml:

<system>
    <property name="nonePasswordLogin">0</property>
    <property name="useHandshakeV10">1</property>
    <property name="useSqlStat">0</property>
    <property name="useGlobleTableCheck">0</property>
    <property name="sequnceHandlerType">2</property>
    <property name="subqueryRelationshipCheck">false</property>
    <property name="serverPort">8066</property>
    <property name="managerPort">9066</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>
</system>

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

<user name="repl">
        <property name="password">repl</property>
        <property name="schemas">test</property>
        <property name="readOnly">true</property>
</user>

ERROR:

[root@TEST-APP ~]# mysql -u root -p -P 8066 -h 47.*.*.*
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.29-mycat-1.6.6.1-release-20181031195535 MyCat Server (OpenCloudDB)

Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> use test;
Database changed
mysql> select 1;
ERROR 1184 (HY000): Invalid DataSource:0
mysql>

DESCRIPTION:

I've connected both mysql servers using navicat 12, all going well. When tried to use mycat, the problem came up.

junwen12221 commented 5 years ago

mycat连不上mysql

junwen12221 commented 5 years ago

尝试改一下mysql8的认证方式

beamofsoul commented 5 years ago

@junwen12221 尝试使用mysql_native_password方式进行认证

在mysqld.conf中增加了最后一行来指定默认认证插件

[mysqld]
server-id=1
default-authentication-plugin=mysql_native_password

重启后，再次访问mycat，错误信息换成了ERROR 1184 (HY000): Invalid DataSource:1

[root@TEST-APP ~]# mysql -u root -p -P 8066 -h 47.*.*.*
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 21
Server version: 5.6.29-mycat-1.6.6.1-release-20181031195535 MyCat Server (OpenCloudDB)

Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> use test;
Database changed
mysql> select 1;
ERROR 1184 (HY000): Invalid DataSource:1
mysql>

guanhua-dba commented 4 years ago

我这里也遇到了同样的报错。我的环境是mysql8.0.13+mycat1.6.7.1。本次我从搭建成功mycat后就一直在报错，show tables返回

怀疑过是8.0密码认证改变引起的，但是之前的测试中没有遇到这样的问题。下面是我的配置文件

[root@MySQL05 mycat]# cat conf/schema.xml <?xml version="1.0"?> <!DOCTYPE mycat:schema SYSTEM "schema.dtd">

select user() [root@MySQL05 mycat]# cat conf/server.xml ..................................................................................................................................... ....................................................................................................................................... ........................................................................................................................................ 123456 TESTDB user TESTDB true

guanhua-dba commented 4 years ago

奇怪的问题频出，例如，stop mycat时提示没有运行，但是我查看mycat是运行的。

guanhua-dba commented 4 years ago

你在连接mycat时应该使用这样的方式： mysql -h 127.0.0.1 -u root -P 8066 --default-auth=mysql_native_password -p

关于您提到的问题，我解决了，但是我不知道您的问题是否和我的一样，所以您通过这个命令看一下mycat的运行日志 cat mycat-03-16-1.log|grep error。将报错提供一下，我看看是否和我的一样。

aslzl commented 4 years ago

Invalid DataSource:0 这个问题最后都是如何解决的

guanhua-dba commented 4 years ago

您好，您将github上的链接发我一下。

在 2020年5月28日，14:12，aslzl notifications@github.com<mailto:notifications@github.com> 写道：

Invalid DataSource:0 这个问题最后都是如何解决的

— You are receiving this because you commented. Reply to this email directly, view it on GitHubhttps://github.com/MyCATApache/Mycat-Server/issues/2303#issuecomment-635128007, or unsubscribehttps://github.com/notifications/unsubscribe-auth/AODOS52QP5YH5IKTNCOQHK3RTX6FNANCNFSM4IT4CQPQ.

aslzl commented 4 years ago

非常感谢，这个问题困扰我将近两天了，这是我的邮箱 aslzl19871017@aliyun.com @guanhua-dba

BryantJames commented 4 years ago

请问解决了嘛我的也是mysql8.0遇到了这个问题

699640 commented 4 years ago

@guanhua-dba 同求。这个错误如何解决的？？谢谢！ allen99999@qq.com

guanhua-dba commented 4 years ago

请告知一下mycat和mysql版本号

guanhua-dba commented 4 years ago

请告知一下mycat和mysql版本号

guanhua-dba commented 4 years ago

请告知一下mycat和mysql版本号

guanhua-dba commented 4 years ago

请告知一下mycat和mysql版本号

guanhua-dba commented 4 years ago

请告知一下mycat和mysql版本号

mainlove commented 3 years ago

非常感谢，这个问题困扰我将近两天了，这是我的邮箱 aslzl19871017@aliyun.com @guanhua-dba

最后是怎么解决的。 wmainlove@gmail.com

guanhua-dba commented 3 years ago

您好，麻烦告诉一下MyCAT、MySQL使用的版本，以及MyCAT机器上使用的MySQL的环境，最后是连接的命令提供一下。

699640 commented 3 years ago

你好，我这个问题后来解决了。我上次就回复你了。

------------------ 原始邮件 ------------------ 发件人: @.>; 发送时间: 2021年4月12日(星期一) 下午2:47 收件人: @.>; 抄送: @.>; @.>; 主题: Re: [MyCATApache/Mycat-Server] 1.6.6.1 connect with MySQL8，ERROR 1184 (HY000): Invalid DataSource:0 (#2303)

您好，麻烦告诉一下MyCAT、MySQL使用的版本，以及MyCAT机器上使用的MySQL的环境，最后是连接的命令提供一下。

— You are receiving this because you commented. Reply to this email directly, view it on GitHub, or unsubscribe.

guanhua-dba commented 3 years ago

抱歉，刚刚用的是全部回复，打扰您了。

699640 @.***> 于2021年4月12日周一下午2:50写道：

你好，我这个问题后来解决了。我上次就回复你了。

------------------ 原始邮件 ------------------ 发件人: @.>; 发送时间: 2021年4月12日(星期一) 下午2:47 收件人: @.>; 抄送: @.>; @.>; 主题: Re: [MyCATApache/Mycat-Server] 1.6.6.1 connect with MySQL8，ERROR 1184 (HY000): Invalid DataSource:0 (#2303)

您好，麻烦告诉一下MyCAT、MySQL使用的版本，以及MyCAT机器上使用的MySQL的环境，最后是连接的命令提供一下。

— You are receiving this because you commented. Reply to this email directly, view it on GitHub, or unsubscribe.

— You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub https://github.com/MyCATApache/Mycat-Server/issues/2303#issuecomment-817535575, or unsubscribe https://github.com/notifications/unsubscribe-auth/AODOS5YE65TVHJC6HN2SH3DTIKJ3LANCNFSM4IT4CQPQ .

ovenfrank commented 2 years ago

您好，请问怎么解决的，我也是这个问题

ovenfrank commented 2 years ago

您好，请问怎么解决的，我也是这个问题，邮箱：1132814637@qq.com

zwyqz commented 2 years ago

报这个错是数据源链接补上，issue里面找下修改mysql8的密码格式；新人建议直接使用mycat2

ovenfrank commented 2 years ago

用这种方式启动也不行：--default-auth=mysql_native_password my.cnf也改了密码加密方式

ovenfrank commented 2 years ago

这么多人问，就没个解决方案吗

yunzhongxiaxi commented 1 year ago

我同样的问题，我查看了mycat的logs发现是因为mysql时区的问题，再百度怎么改正mysql的时区，按照方法做就解决了

realLiuzh commented 1 year ago

服了解决的哥们能说一下吗

realLiuzh commented 1 year ago

WARN [$_NIOREACTOR-0-RW] (io.mycat.backend.mysql.nio.MySQLConnectionAuthenticator.handle(MySQLConnectionAuthenticator.java:91)) - can't connect to mysql server ,errmsg:Host '192.168.56.30' is blocked because of many connection errors; unblock with 'mysqladmin flush-hosts' MySQLConnection [id=177, lastTime=1682947069141, user=root, schema=test_db_2, old shema=test_db_2, borrowed=false, fromSlaveDB=true, threadId=0, charset=utf8, txIsolation=3, autocommit=true, attachment=null, respHandler=null, host=192.168.56.20, port=3306, statusSync=null, writeQueue=0, modifiedSQLExecuted=false]

yunzhongxiaxi commented 1 year ago

我们的问题好像不一样，我打开了mycat的logs，报错原因是： Caused by: com.mysql.cj.exceptions.InvalidConnectionAttributeException: The server time zone value '???ú±ê×??±??' is unrecognized or represents more than one time zone. You must configure either the server or JDBC driver (via the serverTimezone configuration property) to use a more specifc time zone value if you want to utilize time zone support. 我就猜想是mysql的时区问题，上网搜了一下，配置mysql的my.ini，加了一句default-time-zone='+08:00'再重启mysql就好了

realLiuzh commented 1 year ago

请问您是看的logs/mycat.log文件吗？

------------------ 原始邮件 ------------------ 发件人: "MyCATApache/Mycat-Server" @.>; 发送时间: 2023年5月1日(星期一) 晚上9:32 @.>; @.**@.>; 主题: Re: [MyCATApache/Mycat-Server] 1.6.6.1 connect with MySQL8，ERROR 1184 (HY000): Invalid DataSource:0 (#2303)

我们的问题好像不一样，我打开了mycat的logs，报错原因是： Caused by: com.mysql.cj.exceptions.InvalidConnectionAttributeException: The server time zone value '???ú±ê×??±??' is unrecognized or represents more than one time zone. You must configure either the server or JDBC driver (via the serverTimezone configuration property) to use a more specifc time zone value if you want to utilize time zone support. 我就猜想是mysql的时区问题，上网搜了一下，配置mysql的my.ini，加了一句default-time-zone='+08:00'再重启mysql就好了

— Reply to this email directly, view it on GitHub, or unsubscribe. You are receiving this because you commented.Message ID: @.***>

zwyqz commented 1 year ago

请问您是看的logs/mycat.log文件吗？ … ------------------ 原始邮件 ------------------ 发件人: "MyCATApache/Mycat-Server" @.>; 发送时间: 2023年5月1日(星期一) 晚上9:32 @.>; @.**@.>; 主题: Re: [MyCATApache/Mycat-Server] 1.6.6.1 connect with MySQL8，ERROR 1184 (HY000): Invalid DataSource:0 (#2303) 我们的问题好像不一样，我打开了mycat的logs，报错原因是： Caused by: com.mysql.cj.exceptions.InvalidConnectionAttributeException: The server time zone value '???ú±ê×??±??' is unrecognized or represents more than one time zone. You must configure either the server or JDBC driver (via the serverTimezone configuration property) to use a more specifc time zone value if you want to utilize time zone support. 我就猜想是mysql的时区问题，上网搜了一下，配置mysql的my.ini，加了一句default-time-zone='+08:00'再重启mysql就好了 — Reply to this email directly, view it on GitHub, or unsubscribe. You are receiving this because you commented.Message ID: @.***>

是的，也可以看看wrappe.log,或者用mycat console启动看错误。

ZHANGBISONG commented 10 months ago

@guanhua-dba 同求。这个错误如何解决的？？谢谢！ allen99999@qq.com

问题解决了吗我用的mysql版本是8.0.28 mycat版本是1.6.7.4

MyCATApache / Mycat-Server

1.6.6.1 connect with MySQL8，ERROR 1184 (HY000): Invalid DataSource:0 #2303