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

MySQL & MariaDB Percona Operator for MySQL
1

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?

2

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.

3

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

4
  • 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?
    • 5

    @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.

    6

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

    7

    @matthewb

    yes I am able to connect mysql backend.

    and these are stand alone servers not on kubernet containers.

    8

    @lokesh123You 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.

    9

    @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)

    10

    @lokesh123Please 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"
    11

    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)

    root@prod-db-01:/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.

    12

    @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.

    1 Like