We have been reviewing different softwares to load-balancing our spring boot application without change our application, looks like this is the best solution, no single line of code change needed, just connect to the proxysql container instead of mysql container, and it should work OOB. However, we are not able to get any query rule working.
We have a couple spring boot applications, here is my setup: (ProxySQL is at latest version, Spring boot is also latest version)
- 1 mysql 8.0.22 master and 2 slaves, all run in different containers
- 3 proxysql containers
- 3 mysql business users
In my proxysql conf file, I set the default group for all users is the write group (10), which is the only master in the replication group. The slaves have read_only=1.
My spring boot applications have initSql command:
SET NAMES 'utf8mb4' COLLATE 'utf8mb4_unicode_ci';
So if I have the above initSql statement in my application, I have this error:
ProxySQL Error: connection is locked to hostgroup 10 but trying to reach hostgroup 20
If I remove the initSql statement, everything works (because all sql statements are routed to the master).
If I add a rule, the sql is Select ? and route that rule to read group, the application won't start.
[WARN ] 2021-01-04 11:08:08.913 [main] SqlExceptionHelper - SQL Error: 0, SQLState: null
[ERROR] 2021-01-04 11:08:08.913 [main] SqlExceptionHelper - Failed to validate a newly established connection.
and further down the trace I have this:
nested exception is org.springframework.orm.jpa.JpaSystemException: Unable to acquire JDBC Connection; nested exception is org.hibernate.exception.GenericJDBCException: Unable to acquire JDBC Connection
If I disable that rule, and enable another rule:
select organizati0_.org_id as org_id1_14_, organizati0_.org_desc as org_desc2_14_, organizati0_.org_name as org_name3_14_, organizati0_.org_type as org_type4_14_ from ems_org organizati0_ where organizati0_.org_name=? and organizati0_.org_type=?
Then I will encounter the same error as the SET NAMES 'utf8mb4' COLLATE 'utf8mb4_unicode_ci';:
ProxySQL Error: connection is locked to hostgroup 10 but trying to reach hostgroup 20
(the above has nothing to do with any SET commands, still failed)
Also tried this
set mysql-set_query_lock_on_hostgroup=0;
load mysql variables to runtime;
Still same errors.
So I am not able to get this working at all. I am new to proxysql, so has anyone successfully connected proxysql to spring boot applications?
Thanks
