search

sysown / proxysql

High-performance MySQL proxy with a GPL license.
http://www.proxysql.com
GNU General Public License v3.0
6.05k stars 983 forks source link

Cannot connect to ProxySQL from Spring-Boot after upgrading org.mariadb.jdbc from 2.7.5 to 3.0.4 #3852

Open keydon opened 2 years ago

keydon commented 2 years ago

ProxySQL Version: 2.3.1 ProxySQL-OS: Official Docker-Image running on Kubernetes

Connection from Spring-Boot-Client (2.6.7) with org.mariadb.jdbc (3.0.4) to proxySql cannot be established. Though directly to the MariaDB 10.5 Galera Cluster it works. With the older org.mariadb.jdbc (2.7.5) it worked, so I am not able to upgrade this dependency for now.

Any ideas?

Spring-Logs:

[main] INFO  network.we.ServerApplication - Starting ServerApplication using Java 11.0.8 on pc with PID 19560 (C:\git\service\target\classes started by me in C:\git\service)
[main] INFO  network.we.ServerApplication - No active profile set, falling back to 1 default profile: "default"
[main] INFO  org.springframework.data.repository.config.RepositoryConfigurationDelegate - Bootstrapping Spring Data JPA repositories in DEFAULT mode.
[main] INFO  org.springframework.data.repository.config.RepositoryConfigurationDelegate - Finished Spring Data repository scanning in 52 ms. Found 2 JPA repository interfaces.
[main] INFO  org.springframework.cloud.context.scope.GenericScope - BeanFactory id=e5beed89-67b0-3f6f-9e70-00220718d5a8
[main] INFO  org.springframework.boot.web.embedded.tomcat.TomcatWebServer - Tomcat initialized with port(s): 0 (http)
[main] INFO  org.apache.coyote.http11.Http11NioProtocol - Initializing ProtocolHandler ["http-nio-auto-1"]
[main] INFO  org.apache.catalina.core.StandardService - Starting service [Tomcat]
[main] INFO  org.apache.catalina.core.StandardEngine - Starting Servlet engine: [Apache Tomcat/9.0.62]
[main] INFO  org.apache.catalina.core.ContainerBase.[Tomcat].[localhost].[/] - Initializing Spring embedded WebApplicationContext
[main] INFO  com.zaxxer.hikari.HikariDataSource - service - Starting...
[main] ERROR com.zaxxer.hikari.pool.HikariPool - service - Exception during pool initialization.
java.sql.SQLNonTransientConnectionException: (conn=72501) Initialization command fail
    at org.mariadb.jdbc.export.ExceptionFactory.createException(ExceptionFactory.java:281)
    at org.mariadb.jdbc.export.ExceptionFactory.create(ExceptionFactory.java:347)
    at org.mariadb.jdbc.client.impl.StandardClient.postConnectionQueries(StandardClient.java:374)
    at org.mariadb.jdbc.client.impl.StandardClient.<init>(StandardClient.java:205)
    at org.mariadb.jdbc.Driver.connect(Driver.java:64)
    at org.mariadb.jdbc.Driver.connect(Driver.java:83)
    at org.mariadb.jdbc.Driver.connect(Driver.java:27)
    at com.zaxxer.hikari.util.DriverDataSource.getConnection(DriverDataSource.java:138)
    at com.zaxxer.hikari.pool.PoolBase.newConnection(PoolBase.java:364)
    at com.zaxxer.hikari.pool.PoolBase.newPoolEntry(PoolBase.java:206)
    at com.zaxxer.hikari.pool.HikariPool.createPoolEntry(HikariPool.java:476)
    at com.zaxxer.hikari.pool.HikariPool.checkFailFast(HikariPool.java:561)
    at com.zaxxer.hikari.pool.HikariPool.<init>(HikariPool.java:115)
    at com.zaxxer.hikari.HikariDataSource.getConnection(HikariDataSource.java:112)
    at org.springframework.jdbc.datasource.DataSourceUtils.fetchConnection(DataSourceUtils.java:159)
    at org.springframework.jdbc.datasource.DataSourceUtils.doGetConnection(DataSourceUtils.java:117)
    at org.springframework.jdbc.datasource.DataSourceUtils.getConnection(DataSourceUtils.java:80)
    at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:330)
    at org.springframework.boot.jdbc.EmbeddedDatabaseConnection.isEmbedded(EmbeddedDatabaseConnection.java:162)
    at org.springframework.boot.autoconfigure.orm.jpa.HibernateDefaultDdlAutoProvider.getDefaultDdlAuto(HibernateDefaultDdlAutoProvider.java:42)
    at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
    at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.base/java.lang.reflect.Method.invoke(Method.java:566)
    at org.springframework.beans.factory.support.SimpleInstantiationStrategy.instantiate(SimpleInstantiationStrategy.java:154)
    at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:208)
    at org.springframework.context.support.AbstractApplicationContext.getBean(AbstractApplicationContext.java:1154)
    at org.springframework.boot.web.servlet.context.ServletWebServerApplicationContext.refresh(ServletWebServerApplicationContext.java:145)
    at org.springframework.boot.SpringApplication.refresh(SpringApplication.java:740)
    at org.springframework.boot.SpringApplication.refreshContext(SpringApplication.java:415)
    at org.springframework.boot.SpringApplication.run(SpringApplication.java:303)
    at org.springframework.boot.builder.SpringApplicationBuilder.run(SpringApplicationBuilder.java:164)
    at network.we.ServerApplication.main(ServerApplication.java:15)
Caused by: java.sql.BatchUpdateException: java.sql.SQLNonTransientConnectionException: (conn=72501) Socket error
    at org.mariadb.jdbc.export.ExceptionFactory.createBatchUpdate(ExceptionFactory.java:213)
    at org.mariadb.jdbc.client.impl.StandardClient.executePipeline(StandardClient.java:599)
    at org.mariadb.jdbc.client.impl.StandardClient.postConnectionQueries(StandardClient.java:343)
    ... 48 common frames omitted
Caused by: java.sql.SQLNonTransientConnectionException: (conn=72501) Socket error
    at org.mariadb.jdbc.export.ExceptionFactory.createException(ExceptionFactory.java:281)
    at org.mariadb.jdbc.export.ExceptionFactory.create(ExceptionFactory.java:347)
    at org.mariadb.jdbc.client.impl.StandardClient.readPacket(StandardClient.java:837)
    at org.mariadb.jdbc.client.impl.StandardClient.readResults(StandardClient.java:754)
    at org.mariadb.jdbc.client.impl.StandardClient.readResponse(StandardClient.java:673)
    at org.mariadb.jdbc.client.impl.StandardClient.executePipeline(StandardClient.java:550)
    ... 49 common frames omitted
Caused by: java.io.EOFException: unexpected end of stream, read 0 bytes from 4 (socket was closed by server)
    at org.mariadb.jdbc.client.socket.impl.PacketReader.readPacket(PacketReader.java:78)
    at org.mariadb.jdbc.message.ClientMessage.readPacket(ClientMessage.java:114)
    at org.mariadb.jdbc.client.impl.StandardClient.readPacket(StandardClient.java:815)
    ... 52 common frames omitted
[main] INFO  org.hibernate.jpa.internal.util.LogHelper - HHH000204: Processing PersistenceUnitInfo [name: default]
[main] INFO  org.hibernate.Version - HHH000412: Hibernate ORM core version 5.6.8.Final

Proxy-SQL Console logs:

2022-04-25 14:29:38 MySQL_Session.cpp:3364:handler___status_NONE_or_default(): [ERROR] Unexpected packet from client 127.0.0.1 . Session_status: 1 , client_status: 0 Disconnecting it
 2022-04-25 14:29:40 MySQL_Session.cpp:3364:handler___status_NONE_or_default(): [ERROR] Unexpected packet from client 127.0.0.1 . Session_status: 1 , client_status: 0 Disconnecting it
2022-04-25 14:31:45 MySQL_Session.cpp:3364:handler___status_NONE_or_default(): [ERROR] Unexpected packet from client 127.0.0.1 . Session_status: 1 , client_status: 1701191724 Disconnecting it
2022-04-25 14:31:47 MySQL_Session.cpp:3364:handler___status_NONE_or_default(): [ERROR] Unexpected packet from client 127.0.0.1 . Session_status: 1 , client_status: 1701191724 Disconnecting it

Cannot find /var/lib/proxysql/proxysql.log within the container, also not in the data dir o.O Whrere can I find the full log?

renecannao commented 2 years ago

Try to disable usePipelineAuth (see https://mariadb.com/kb/en/about-mariadb-connector-j/). If that doesn't work, please provide a full reproducible test case.

Cannot find /var/lib/proxysql/proxysql.log within the container, also not in the data dir o.O Whrere can I find the full log?

In docker container proxysql is running in foreground and error messages are sent to stderr, thus no error log

keydon commented 2 years ago

Hio, sorry for taking so long. Not sure what you mean with reproducible test case, but I have created a docker composition for you. It contains a mariadb, a proxysql (configured to use that mariadb) and a sample-spring-boot-application, which just connects to proxysql (and fails attempting to do so).

details can be found in the readme. https://github.com/keydon/issue-3852-repro-proxysql

Does that help?

renecannao commented 2 years ago

Hi @keydon . I think it helps.

@JavierJF : can you please follow up on this? Thanks

keydon commented 2 years ago

Any updates on this issue?

keydon commented 2 years ago

Some updates from my side. I tried again with version 3.0.7, and at first nothing was changed. But I decided to dig deeper. And I discovered they intrduced a new undocumented property disablePipeline. If I set that to true it connects fine, yay! Just not sure what disabling this means exactly, I will do some more research on that.

renecannao commented 2 years ago

This sounds very similar to the various issues we had with usePipelineAuth : is set to true (the default) things fails.

I wonder if disablePipeline is also the solution for https://github.com/sysown/proxysql/issues/3880