CODEWITHSUNDEEP
mysqlproxysql
VictorP
VictorP

Reputation: 34

Issue with Proxysql 2.6 and caching_sha2_password plugin with Percona 8 MySQL cluster

I'm struggiling since 4 days with a strange issue with my ProxySQL+MySQL cluster. I've attached all information that seems relevant to me. I read documentation about how to import 'caching_sha2_password' from MySQL to ProxySQL (https://proxysql.com/documentation/Password-management/#import-caching_sha2_passwords) but even that worked, when I try to connect using a PHP script, it failed. After connect to ProxySQL using MySQL cliente (mysql) and I execute PHP script again, it works!

Hope anyone can help. Below you have my secuencial path....

=============================================================================================== OS: # Red Hat Enterprise Linux release 9.4 (Plow) - Oracle Linux 9

MySQL cluster: 3 nodes (1 master, 2 slaves) replicating MySQL version: Server version: 8.0.36-28 Percona Server (GPL), Release 28, Revision 47601f19

ProxySQL version: ProxySQL version 2.6.3-percona-1.1, codename Truls

global variables:

    # mysql -uadmin -p -h 0.0.0.0 -P6032 --prompt='Admin> ' -s -e 'show global variables' | grep -e mysql-default_authentication -e mysql-have_ssl
    Enter password:
    mysql-default_authentication_plugin caching_sha2_password
    mysql-have_ssl  true

===============================================================================================

    *mysql> select hex(authentication_string) from user where user='ftptest';*
    +----------------------------------------------------------------------------------------------------------------------------------------------+
    | hex(authentication_string)                                                                                                                           |
    +----------------------------------------------------------------------------------------------------------------------------------------------+
    |     244124303035243E257A64062E3C10397A582F1345503C6D2A66174865582E75797163574B4F4C58322E5A665A645A7A564A5476507A612F304648726E646943544334324B32 |
    +----------------------------------------------------------------------------------------------------------------------------------------------+
    1 row in set (0.00 sec)

===============================================================================================

    *Admin> insert into mysql_users (username, password, active, use_ssl) values ('ftptest', UNHEX('244124303035243E257A64062E3C10397A582F1345503C6D2A66174865582E75797163574B4F4C58322E5A665A645A7A564A5476507A612F304648726E646943544334324B32'), 1, 0);
    **Admin> load mysql users to runtime;
    Admin> save mysql users to disk;*

===============================================================================================

    # cat test.php
    #!/usr/bin/php
    <?php
        print("Connect with ftptest\n");
        if( ! $c = new PDO( "mysql:host=10.200.72.135;dbname=mysql;charset=utf8;", "ftptest", "mynewpassword" ) ) throw new Exception("Can't open MySQL");
        print(">>>>> ftptest connected\n");

===============================================================================================

    # *php test.php*
    Connect with unix-prov-mysql
    >>>>> unix-prov-mysql connected
    Connect with ftptest
    PHP Fatal error:  Uncaught PDOException: SQLSTATE[HY000] [2006] MySQL server has gone away in /root/test.php:7
    Stack trace:
    #0 /root/test.php(7): PDO->__construct()
    #1 {main}
      thrown in /root/test.php on line 7

===============================================================================================

    # *mysql -u ftptest -p -h 10.200.72.135 -s*
    Enter password: mynewpassword
    mysql>

===============================================================================================

    # *php test.php*
    Connect with ftptest
    >>>>> ftptest connected

===============================================================================================

-- If I update the user's password, I need to connect again using the credentials and then test.php executes successfully.

    *mysql> alter user 'ftptest' identified by 'mypassword';*
    Query OK, 0 rows affected (0.01 sec)
    *mysql> select hex(authentication_string) from user where user='ftptest';
    *+----------------------------------------------------------------------------------------------------------------------------------------------+
    | hex(authentication_string)                                                                                                                   |
    +----------------------------------------------------------------------------------------------------------------------------------------------+
    | 24412430303524564D58543350161E30197553035E39140230384E624B4F527567667A6B314249333443355754362E6B567555357350376951576F566467374E625637686233 |
    +----------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

===============================================================================================

    **Admin> update mysql_users set password=UNHEX('24412430303524564D58543350161E30197553035E39140230384E624B4F527567667A6B314249333443355754362E6B567555357350376951576F566467374E625637686233') where username = 'ftptest';
    *Admin> load mysql users to runtime;
    Admin> save mysql users to disk;*

===============================================================================================

    # *php test.php*
    Connect with ftptest
    PHP Fatal error:  Uncaught PDOException: SQLSTATE[HY000] [2006] MySQL server has gone away in /root/test.php:7
    Stack trace:
    #0 /root/test.php(7): PDO->__construct()
    #1 {main}
      thrown in /root/test.php on line 7

    # *mysql -u ftptest -p -h 10.200.72.135 -s*
    Enter password: mypassword
    mysql>

===============================================================================================

    # *php test.php*
    Connect with ftptest
    >>>>> ftptest connected

===============================================================================================

Do you have any clue about how to fix this issue?

Thank you very much!

Upvotes: 0

Views: 67

Answers (0)

Related Questions