Not the answer you need?
Register and ask your own question!

Getting error while trying to execute basic queries like show databases / select version, etc

Errors observed in monitoring logs:
-lookup symclass-mysql-cluster-proxysql-unready on 172.27.0.10:53: no such host
-ProxySQL Admin Error: UNIQUE constraint failed: proxysql_servers.hostname, proxysql_servers.port
-ERROR 2003 (HY000): Can't connect to MySQL server on '127.0.0.1' (111)
-ERROR 2005 (HY000): Unknown MySQL server host

Does anyone has any idea regarding the same?
Tagged:

Answers

  • matthewbmatthewb Senior [email protected] Percona Staff Role
    It looks like your K8s DNS is not functioning properly (ie: 172.27.0.10:53: no such host) Port 53 is the DNS lookup port and things are unable to reach it. This may be why ProxySQL is failing in it's setup.
  • lokesh123lokesh123 Current User Role Patron

    mysql -u sbuser -p -h 127.0.0.1 -P 6033

    Enter password:

    Welcome to the MySQL monitor. Commands end with ; or \g.

    Your MySQL connection id is 3

    Server version: proxysql (ProxySQL)


    Copyright (c) 2009-2020 Percona LLC and/or its affiliates

    Copyright (c) 2000, 2020, 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> show databases;

    ERROR 2013 (HY000): Lost connection to MySQL server during query

    mysql>

    mysql> show databases;

    ERROR 2006 (HY000): MySQL server has gone away

    No connection. Trying to reconnect...

    Connection id:  4

    Current database: *** NONE ***


    ERROR 9001 (HY000): Max connect timeout reached while reaching hostgroup 0 after 10012ms

    mysql>


    any idea @matthewb

  • matthewbmatthewb Senior [email protected] Percona Staff Role

    Looks like something is not right between ProxySQL and the backend MySQL. Here are some tasks for you to do:

    • Can you connect to the ProxySQL Admin and do 'SELECT * FROM runtime_mysql_servers'?
    • Are all 4 pods online? (kubectl get pods)
    • Can you connect directly to each MySQL backend?
  • lokesh123lokesh123 Current User Role Patron

    @matthewb


    mysql> SELECT * FROM runtime_mysql_servers;

    +--------------+--------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+

    | hostgroup_id | hostname   | port | gtid_port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |

    +--------------+--------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+

    | 0      | 192.168.0.1 | 3306 | 0     | ONLINE | 1   | 0      | 1000      | 0          | 0    | 0       |     |

    | 0      | 192.168.0.2 | 3306 | 0     | ONLINE | 1   | 0      | 1000      | 0          | 0    | 0       |     |

    | 0      | 192.168.0.3 | 3306 | 0     | ONLINE | 1   | 0      | 1000      | 0          | 0    | 0       |     |

    +--------------+--------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------


    I am trying to setup in Racspace ...percona cluster is working fine.

    but some issues with proxysql.

  • matthewbmatthewb Senior [email protected] Percona Staff Role

    @lokesh123 I listed 3 tasks in the previous message. Can you please do all three ?

  • lokesh123lokesh123 Current User Role Patron

    @matthewb

    yes I am able to connect mysql backend.

    and these are stand alone servers not on kubernet containers.

  • matthewbmatthewb Senior [email protected] Percona Staff Role

    @lokesh123 You posted this question inside the 'Percona Kubernetes Operator for Percona XtraDB Cluster' forum, so naturally, one would assume that Kubernetes is involved here.

    In ProxySQL admin, please run each of the following. Please also use the forum formatting tools when pasting console output:

    SELECT * FROM runtime_mysql_galera_hostgroups;
    SELECT * FROM runtime_mysql_users;
    SELECT hostname, connect_error FROM mysql_server_connect_log ORDER BY time_start_us DESC LIMIT 10;
    

    Please run all 3.

  • lokesh123lokesh123 Current User Role Patron


    @matthewb


    mysql> SELECT * FROM runtime_mysql_galera_hostgroups;


    Empty set (0.00 sec)




    mysql> SELECT * FROM runtime_mysql_users;


    +----------+-------------------------------------------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+---------+


    | username | password         | active | use_ssl | default_hostgroup | default_schema | schema_locked | transaction_persistent | fast_forward | backend | frontend | max_connections | comment |


    +----------+-------------------------------------------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+---------+


    | sbuser | *A963A39DD5801D80DEE9205ECEF04B4E843FC4CD | 1  | 0  | 0     |    | 0    | 1      | 0   | 0  | 1  | 10000   |   |


    | sqlproxy | *A963A39DD5801D80DEE9205ECEF04B4E843FC4CD | 1  | 0  | 0     |    | 0    | 1      | 0   | 0  | 1  | 10000   |   |


    | sbuser | *A963A39DD5801D80DEE9205ECEF04B4E843FC4CD | 1  | 0  | 0     |    | 0    | 1      | 0   | 1  | 0  | 10000   |   |


    | sqlproxy | *A963A39DD5801D80DEE9205ECEF04B4E843FC4CD | 1  | 0  | 0     |    | 0    | 1      | 0   | 1  | 0  | 10000   |   |


    +----------+-------------------------------------------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+---------+


    4 rows in set (0.00 sec)




    mysql> SELECT hostname, connect_error FROM mysql_server_connect_log ORDER BY time_start_us DESC LIMIT 10;


    +--------------+---------------+


    | hostname  | connect_error |


    +--------------+---------------+


    | 192.168.0.1 | NULL   |


    | 192.168.0.2 | NULL   |


    | 192.168.0.3 | NULL   |


    | 192.168.0.1 | NULL   |


    | 192.168.0.2 | NULL   |


    | 192.168.0.3 | NULL   |


    | 192.168.0.1 | NULL   |


    | 192.168.0.2 | NULL   |


    | 192.168.0.3 | NULL   |


    | 192.168.0.1 | NULL   |


    +--------------+---------------+


    10 rows in set (0.00 sec)

  • matthewbmatthewb Senior [email protected] Percona Staff Role

    @lokesh123 Please use the forum formatting to paste output. Very difficult to read.

    There appears to be no issues with ProxySQL connecting to the backend servers.

    mysql_galera_hostgroups is empty. You need to configure this table so that proxysql is aware of PXC node states.

    Please run this:

    mysql -h 127.0.0.1 -P 6033 -u sbuser -p -e "SELECT @@hostname"
    


  • lokesh123lokesh123 Current User Role Patron

    sorry not able to get How to use formatting tools?


    @matthewb


    mysql> select * from mysql_galera_hostgroups ;

    +------------------+-------------------------+------------------+-------------------+--------+-------------+-----------------------+-------------------------+---------+

    | writer_hostgroup | backup_writer_hostgroup | reader_hostgroup | offline_hostgroup | active | max_writers | writer_is_also_reader | max_transactions_behind | comment |

    +------------------+-------------------------+------------------+-------------------+--------+-------------+-----------------------+-------------------------+---------+

    | 10        | 12           | 11        | 13        | 1   | 1      | 2           | 100           | NULL  |

    +------------------+-------------------------+------------------+-------------------+--------+-------------+-----------------------+-------------------------+---------+

    1 row in set (0.00 sec)



    [email protected]:/var/lib/proxysql# mysql -h 127.0.0.1 -P 6033 -u sbuser -p -e "SELECT @@hostname"

    Enter password:

    ERROR 9001 (HY000) at line 1: Max connect timeout reached while reaching hostgroup 11 after 10011ms

    still getting same issue.

  • matthewbmatthewb Senior [email protected] Percona Staff Role

    @lokesh123 Click the icon to the left of the comment box to format using "code" style.

    I see your galera hostgroups are 10 (writer), and 11(reader). But when you sent mysql_servers, all 3 had hostgroup 0. You need to fix that. Update mysql_servers and set one server to 10 and the other 2 to 11. Save/Load mysql servers to runtime.

Sign In or Register to comment.

MySQL, InnoDB, MariaDB and MongoDB are trademarks of their respective owners.
Copyright ©2005 - 2020 Percona LLC. All rights reserved.