ServiceNow -> ProxySQL -> Galera

[Marius Wilberg]

I am trying to set up an on prem Servicenow instance, using a Galera cluster and ProxySQL Read/Write splitting, but are running into some trouble trying to get it working correctly.

Servicenow Application servers - Debian 12 (Bookworm) with ProxySQL 2.6.1

appnode1 / appnode2 / appnode3 / appnode4

Galera Cluster - Debian 12 (Bookworm) with MariaDB 10.11.6

dbnode1 / dbnode2 / dbnode3

The Galera cluster seems to be working just fine, and connecting to the cluster through ProxySQL from one of the appnodes also seem to be working fine

MySQL [(none)]> 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 |
+------------------+-------------------------+------------------+-------------------+--------+-------------+-----------------------+-------------------------+---------+
| 1                | 3                       | 2                | 4                 | 1      | 1           | 1                     | 100                     | NULL    |
+------------------+-------------------------+------------------+-------------------+--------+-------------+-----------------------+-------------------------+---------+

MySQL [(none)]> 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 |
+--------------+--------------+------+-----------+---------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| 1            | 192.168.8.82 | 3306 | 0         | SHUNNED | 1      | 0           | 1000            | 0                   | 0       | 0              |         |
| 1            | 192.168.8.83 | 3306 | 0         | SHUNNED | 1      | 0           | 1000            | 0                   | 0       | 0              |         |
| 1            | 192.168.8.84 | 3306 | 0         | ONLINE  | 1      | 0           | 1000            | 0                   | 0       | 0              |         |
| 2            | 192.168.8.82 | 3306 | 0         | ONLINE  | 1      | 0           | 1000            | 0                   | 0       | 0              |         |
| 2            | 192.168.8.83 | 3306 | 0         | ONLINE  | 1      | 0           | 1000            | 0                   | 0       | 0              |         |
| 2            | 192.168.8.84 | 3306 | 0         | ONLINE  | 1      | 0           | 1000            | 0                   | 0       | 0              |         |
| 3            | 192.168.8.82 | 3306 | 0         | ONLINE  | 1      | 0           | 1000            | 0                   | 0       | 0              |         |
| 3            | 192.168.8.83 | 3306 | 0         | ONLINE  | 1      | 0           | 1000            | 0                   | 0       | 0              |         |
+--------------+--------------+------+-----------+---------+--------+-------------+-----------------+---------------------+---------+----------------+---------+

MySQL [(none)]> select * from proxysql_servers;
+--------------+------+--------+-----------+
| hostname     | port | weight | comment   |
+--------------+------+--------+-----------+
| 192.168.8.66 | 6032 | 100    | appnode1  |
| 192.168.8.67 | 6032 | 100    | appnode2  |
| 192.168.8.68 | 6032 | 100    | appnode3  |
| 192.168.8.69 | 6032 | 100    | appnode4  |
+--------------+------+--------+-----------+


# glide.db.properties

glide.db.name = servicenowtest_16000
glide.db.rdbms = mysql
glide.db.url = jdbc:mysql://localhost:6033/?usePipelineAuth=false
glide.db.user = servicenowtest
glide.db.password = LongRandomPassword!


appnode1: mariadb -u servicenowtest -p -h localhost -P6033 -e "show databases"

Shows the databases accessible to the "servicenowtest" user

[Marius Wilberg]

The problem is when I try to actually run the initial setup of servicenow (which creates the database schemas and tables and whatever) it fails when pointing to ProxySQL, while it works just fine when pointing to either node in the Galera cluster directly.
So I guess there is something missing with my ProxySQL-setup somehow - or something wrong with the glide.db.url