Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Error when running jdbc flyway migrations in a Galera Cluster #4768

Open
XobrekX opened this issue Dec 3, 2024 · 0 comments
Open

Error when running jdbc flyway migrations in a Galera Cluster #4768

XobrekX opened this issue Dec 3, 2024 · 0 comments

Comments

@XobrekX
Copy link

XobrekX commented Dec 3, 2024

Hello everyone,

I tried setting up proxysql in between some java applications (e.g. Keycloak Cluster) and a three node galera cluster. My setup is to install proxysql on each application server, the application is connected to the local proxysql application. The local proxysql then is connected to a galera cluster.

So my setup is:

Host-1 Host-2 Host-3
Keycloak Keycloak Keycloak
Proxysql Proxysql Proxysql
Apache Apache Apache
keepalive + haproxy keepalive + haproxy keepalive + haproxy

Talking to Host-4;Host-5 and Host-6 running a galera clusting via a TLS encrypted database connection.

All of my Hosts run Ubuntu Server 20.04 with the latest patches.

My Issue is when updating the version of one of my java apps, flyway makes some db-migrations for the new version but there seems to go something horribly wrong as the keycloak log give me an error: ````Error: 1305-42000: PROCEDURE keycloak_db_1.current_schema does not exist`````. When running the very same update without proxysql in between, the flyway migration work just fine.

The proxysql looks as follows:

datadir="/var/lib/proxysql"
errorlog="/var/log/proxysql/proxysql.log"

admin_variables=
{
	admin_credentials="admin:{{ proxysql_admin_password }}"
	web_enabled=true
	mysql_ifaces="127.0.0.1:6032"
}

mysql_variables=
{
	threads=4
	max_connections=2048
	default_query_delay=0
	default_query_timeout=36000000
	have_compress=true
	poll_timeout=2000
	interfaces="127.0.0.1:6033"
	default_schema="information_schema"
	stacksize=1048576
	server_version="5.5.30"
	connect_timeout_server=10000
	monitor_username="proxysql_monitor"
	monitor_password="{{ galera_proxysql_monitor_password }}"
	monitor_history=60000
	monitor_connect_interval=200000
	monitor_ping_interval=200000
	ping_interval_server_msec=10000
	ping_timeout_server=200
	monitor_galera_healthcheck_interval=1000
	monitor_galera_healthcheck_timeout=600
	monitor_galera_healthcheck_max_timeout_count=3
	commands_stats=true
	sessions_sort=true
	ssl_p2s_ca = "/etc/certificates/root.crt"
}

mysql_galera_hostgroups =
(
    {
        writer_hostgroup=2
        backup_writer_hostgroup=4
        reader_hostgroup=3
        offline_hostgroup=1
        max_writers=1
        writer_is_also_reader=0
        max_transactions_behind=100
        active=1
    }
)

mysql_servers =
(
	{
		address = "{{ hostvars['db-1']['ansible_host'] }}"
		port = 3306
		hostgroup = 2
		use_ssl = 1
		weight = 10
		max_connections = 1000
	},
	{
		address = "{{ hostvars['db-2']['ansible_host'] }}"
		port = 3306
		hostgroup = 2
		use_ssl = 1
		weight = 100
		max_connections = 1000
	},
	{
		address = "{{ hostvars['db-3']['ansible_host'] }}"
		port = 3306
		hostgroup = 3
		use_ssl = 1
		weight = 100
		max_connections = 1000
	}
)

mysql_users:
(
	{
		username = "{{ db.user }}"
		password = "{{ db.password }}"
		default_hostgroup = 2
		active = 1
		transaction_persistent = 0
	}
)

mysql_query_rules =
(
    {
        active=1
        match_digest="^SELECT.*"
        destination_hostgroup=3
        apply=0
    },
    {
        active=1
        match_digest="^SELECT.* FOR UPDATE"
        destination_hostgroup=2
        apply=1
    }
)

The Proxysql Log is not very much helping:

2024-12-03 14:21:59 [INFO] Dumping current MySQL Servers structures for hostgroup 2
HID: 2 , address: db-1 , port: 3306 , gtid_port: 0 , weight: 10 , status: SHUNNED , max_connections: 1000 , max_replication_lag: 0 , use_ssl: 1 , max_latency_ms: 0 , comment: 
HID: 2 , address: db-2 , port: 3306 , gtid_port: 0 , weight: 100 , status: SHUNNED , max_connections: 1000 , max_replication_lag: 0 , use_ssl: 1 , max_latency_ms: 0 , comment: 
HID: 2 , address: db-3 , port: 3306 , gtid_port: 0 , weight: 100 , status: ONLINE , max_connections: 1000 , max_replication_lag: 0 , use_ssl: 1 , max_latency_ms: 0 , comment: 
2024-12-03 14:21:59 [INFO] Dumping mysql_servers: HG 2
+-----+-------------+------+------+--------+--------+-----+-----------+---------+-----+---------+---------+-----------------+
| hid | hostname    | port | gtid | weight | status | cmp | max_conns | max_lag | ssl | max_lat | comment | mem_pointer     |
+-----+-------------+------+------+--------+--------+-----+-----------+---------+-----+---------+---------+-----------------+
| 2   | db-1 | 3306 | 0    | 10     | 1      | 0   | 1000      | 0       | 1   | 0       |         | 140213480073024 |
| 2   | db-2 | 3306 | 0    | 100    | 1      | 0   | 1000      | 0       | 1   | 0       |         | 140213480073216 |
| 2   | db-3 | 3306 | 0    | 100    | 0      | 0   | 1000      | 0       | 1   | 0       |         | 140213410572544 |
+-----+-------------+------+------+--------+--------+-----+-----------+---------+-----+---------+---------+-----------------+
2024-12-03 14:21:59 [INFO] Dumping current MySQL Servers structures for hostgroup 4
HID: 4 , address: db-1 , port: 3306 , gtid_port: 0 , weight: 10 , status: ONLINE , max_connections: 1000 , max_replication_lag: 0 , use_ssl: 1 , max_latency_ms: 0 , comment: 
HID: 4 , address: db-2 , port: 3306 , gtid_port: 0 , weight: 100 , status: ONLINE , max_connections: 1000 , max_replication_lag: 0 , use_ssl: 1 , max_latency_ms: 0 , comment: 
2024-12-03 14:21:59 [INFO] Dumping mysql_servers: HG 4
+-----+-------------+------+------+--------+--------+-----+-----------+---------+-----+---------+---------+-----------------+
| hid | hostname    | port | gtid | weight | status | cmp | max_conns | max_lag | ssl | max_lat | comment | mem_pointer     |
+-----+-------------+------+------+--------+--------+-----+-----------+---------+-----+---------+---------+-----------------+
| 4   | db-1 | 3306 | 0    | 10     | 0      | 0   | 1000      | 0       | 1   | 0       |         | 140213410571392 |
| 4   | db-2 | 3306 | 0    | 100    | 0      | 0   | 1000      | 0       | 1   | 0       |         | 140213410572736 |
+-----+-------------+------+------+--------+--------+-----+-----------+---------+-----+---------+---------+-----------------+
2024-12-03 14:21:59 [INFO] Dumping current MySQL Servers structures for hostgroup 3
2024-12-03 14:21:59 [INFO] Dumping mysql_servers: HG 3
+-----+----------+------+------+--------+--------+-----+-----------+---------+-----+---------+---------+-------------+
| hid | hostname | port | gtid | weight | status | cmp | max_conns | max_lag | ssl | max_lat | comment | mem_pointer |
+-----+----------+------+------+--------+--------+-----+-----------+---------+-----+---------+---------+-------------+
+-----+----------+------+------+--------+--------+-----+-----------+---------+-----+---------+---------+-------------+
2024-12-03 14:21:59 [INFO] Dumping current MySQL Servers structures for hostgroup 1
2024-12-03 14:21:59 [INFO] Dumping mysql_servers: HG 1
+-----+----------+------+------+--------+--------+-----+-----------+---------+-----+---------+---------+-------------+
| hid | hostname | port | gtid | weight | status | cmp | max_conns | max_lag | ssl | max_lat | comment | mem_pointer |
+-----+----------+------+------+--------+--------+-----+-----------+---------+-----+---------+---------+-------------+
+-----+----------+------+------+--------+--------+-----+-----------+---------+-----+---------+---------+-------------+
2024-12-03 14:25:32 MySQL_Session.cpp:4872:handler_minus1_LogErrorDuringQuery(): [WARNING] Error during query on (2,db-3,3306,45752): 1146, Table 'keycloak_db_1.MIGRATION_MODEL' doesn't exist
2024-12-03 14:25:32 MySQL_Session.cpp:4872:handler_minus1_LogErrorDuringQuery(): [WARNING] Error during query on (2,db-3,3306,45752): 1305, PROCEDURE keycloak_db_1.current_schema does not exist
2024-12-03 14:25:32 MySQL_Session.cpp:4872:handler_minus1_LogErrorDuringQuery(): [WARNING] Error during query on (2,db-3,3306,45752): 1305, PROCEDURE keycloak_db_1.current_schema does not exist
2024-12-03 14:25:33 MySQL_Session.cpp:4872:handler_minus1_LogErrorDuringQuery(): [WARNING] Error during query on (2,db-3,3306,45752): 1305, PROCEDURE keycloak_db_1.current_schema does not exist
2024-12-03 14:25:33 MySQL_Session.cpp:4872:handler_minus1_LogErrorDuringQuery(): [WARNING] Error during query on (2,db-3,3306,45752): 1305, PROCEDURE keycloak_db_1.current_schema does not exist
2024-12-03 14:25:33 MySQL_Session.cpp:4872:handler_minus1_LogErrorDuringQuery(): [WARNING] Error during query on (2,db-3,3306,45752): 1146, Table 'keycloak_db_1.DATABASECHANGELOG' doesn't exist
2024-12-03 14:25:33 MySQL_Session.cpp:4872:handler_minus1_LogErrorDuringQuery(): [WARNING] Error during query on (2,db-3,3306,45753): 1305, PROCEDURE keycloak_db_1.current_schema does not exist
2024-12-03 14:25:33 MySQL_Session.cpp:4872:handler_minus1_LogErrorDuringQuery(): [WARNING] Error during query on (2,db-3,3306,45753): 1305, PROCEDURE keycloak_db_1.current_schema does not exist
2024-12-03 14:25:33 MySQL_Session.cpp:4872:handler_minus1_LogErrorDuringQuery(): [WARNING] Error during query on (2,db-3,3306,45753): 1305, PROCEDURE keycloak_db_1.current_schema does not exist
2024-12-03 14:25:33 MySQL_Session.cpp:4872:handler_minus1_LogErrorDuringQuery(): [WARNING] Error during query on (2,db-3,3306,45753): 1146, Table 'keycloak_db_1.DATABASECHANGELOGLOCK' doesn't exist
2024-12-03 14:25:33 MySQL_Session.cpp:4872:handler_minus1_LogErrorDuringQuery(): [WARNING] Error during query on (2,db-3,3306,45753): 1305, PROCEDURE keycloak_db_1.current_schema does not exist

Thanks in advance

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
None yet
Projects
None yet
Development

No branches or pull requests

1 participant