mrfaiz
commented
5 years ago Hello, Did you find any solution? I am facing same problem. ProxSQL version is 2.0.2
Open arawak opened 6 years ago
mrfaiz
commented
5 years ago Hello, Did you find any solution? I am facing same problem. ProxSQL version is 2.0.2
arawak
commented
5 years ago Sorry, no. Didn't really have time to work around it so I decide not to go with ProxySQL for my use case.
If you are in a position to use other stacks than Java, I'm guessing that the Python drivers will work, since there's an Ansible module.
renecannao
commented
5 years ago ProxySQL's Admin is not a MySQL backend.
ProxySQL's Admin uses the MySQL protocol so it allows clients and drivers to use this protocol to communicate with it.
Although, if the client/driver makes the strong assumption that the backend is a MySQL server and doesn't handle errors, the client/driver will fail.
In the case of JDBC, it asks a bunch of variables using @@ (SELECT @@session.auto_increment_increment AS auto_increment_increment, @@character_set_client AS character_set_client, ... ) that do not exist in SQLite.
ProxySQL's Admin returns an error, and the JDBC driver gives up.
mrfaiz
commented
5 years ago @arawak thanks .. I will try Python drivers. @renecannao Is there any way for Java ? (like mariadb-connector-j or other)
tjlee
commented
5 years ago @mrfaiz I found a hack workaround. You need to change mysql-server_version to any version grater than 8.0.3. On ProxySQL Admin node execute as admin:
update global_variables set variable_value="8.0.4 (ProxySQL)"
where variable_name='mysql-server_version';
load mysql variables to run;save mysql variables to disk;+1 Possible solution for ProxySQL is to set ProxySQL version according to MySQL version.
mdefanti
commented
5 years ago I'm having the same problem with MySQL Connector version 5.1.40 and ProxySQL version 2.0.7. I tried changing the mysql-server_version to different values but none have worked.
Is there a workaround for this ?
petitajt
commented
4 years ago @mrfaiz I found a hack workaround. You need to change
mysql-server_versionto any version grater than 8.0.3. On ProxySQL Admin node execute as admin:update global_variables set variable_value="8.0.4 (ProxySQL)" where variable_name='mysql-server_version'; load mysql variables to run;save mysql variables to disk;
I also tried your workaround with several versions of MySQL Connector/J. I also tried various version of MariaDB connector. All tests were made with a ProxySQL v2.0.8 and a spring boot application (tests done with both Hikari and DBCP2).
Could you confirm that you got this to work and share the versions of the library you used?
Thanks
tjlee
commented
4 years ago @petitajt
I was working with MySQL 8.0.17 & ProxySQL 2.0.6
petitajt
commented
4 years ago @tjlee Ok thanks. I finally opted for a trimmed version of MariaDB connector. Mainly removed transaction handling and some options. Not really suitable for heavy prod use but sufficient for my needs.
hylitjob
commented
4 years ago @tjlee Ok thanks. I finally opted for a trimmed version of MariaDB connector. Mainly removed transaction handling and some options. Not really suitable for heavy prod use but sufficient for my needs.
java.sql.SQLException: unrecognized token: "@"
How do you solve the question above with mysql jdbc driver
Thanks!
hylitjob
commented
4 years ago @tjlee Ok thanks. I finally opted for a trimmed version of MariaDB connector. Mainly removed transaction handling and some options. Not really suitable for heavy prod use but sufficient for my needs.
I tried another way to solve this issue Switch the jdbc connector to sqlite3 driver, as follow:
`spring.datasource.url=jdbc:sqlite:/var/lib/proxysql/proxysql.db
spring.datasource.username=
spring.datasource.password=
spring.datasource.driver-class-name=org.sqlite.JDBC`
Update the proxysql config using java mybatis as follow:
`
<select id="findMysqlUserCount" parameterType="java.lang.String"
resultType="java.lang.Integer">
select count(0) from mysql_users where comment = #{id}
</select>
<select id="findMysqlServerCount"
parameterType="java.lang.String" resultType="java.lang.Integer">
select count(0) from
mysql_servers where comment = #{id}
</select>
<select id="findMysqlQueryRuleCount"
parameterType="java.lang.String" resultType="java.lang.Integer">
select count(0) from
mysql_query_rules where comment = #{id}
</select>
<insert id="addMysqlUser" parameterType="MysqlUsers">
insert into mysql_users(
username,
password,
default_hostgroup,
default_schema,
active,
transaction_persistent,
frontend,backend,
max_connections,
schema_locked,
fast_forward,
comment)
values(
#{username},
#{password},
#{defaultHostgroup},
#{defaultSchema},
#{active},
#{transactionPersistent},
#{frontend},
#{backend},
#{maxConnections},
#{schemaLocked},
#{fastForward},
#{id}
)
</insert>
<update id="updatMysqlUser" parameterType="MysqlUsers">
update mysql_users set
username= #{username},
password= #{password},
default_hostgroup=#{defaultHostgroup},
default_schema=#{defaultSchema},
active= #{active},
transaction_persistent=#{transactionPersistent},
frontend= #{frontend},
backend= #{backend},
max_connections= #{maxConnections},
schema_locked= #{schemaLocked},
fast_forward= #{fastForward}
where
comment=#{id}
</update>`
Then , load disk to mem and runtime as follow, and it works: `public class ProxysqlCommandExecutor {
private static final Logger LOGGER = LoggerFactory.getLogger(ProxysqlCommandExecutor.class);
private static final String[] RESTART_COMMAND_ARRAY = { "systemctl restart proxysql" };
private static final String COMMAND_PREFIX = " mysql -u admin -padmin -h 127.0.0.1 -P6032 --prompt='Admin> ' -e ";
private static final String[] FLUSH_COMMAND_ARRAY = {
COMMAND_PREFIX + "'LOAD MYSQL VARIABLES TO MEM;'",
COMMAND_PREFIX + "'LOAD MYSQL SERVERS TO MEM;'",
COMMAND_PREFIX + "'LOAD MYSQL USERS TO MEM;'",
COMMAND_PREFIX + "'LOAD MYSQL QUERY RULES TO MEM;'",
COMMAND_PREFIX + "'LOAD MYSQL VARIABLES TO RUNTIME;'",
COMMAND_PREFIX + "'LOAD MYSQL SERVERS TO RUNTIME;'",
COMMAND_PREFIX + "'LOAD MYSQL USERS TO RUNTIME;'",
COMMAND_PREFIX + "'LOAD MYSQL QUERY RULES TO RUNTIME;'" };
public int configFlush() {
int flag = 0;
try {
for (String command : FLUSH_COMMAND_ARRAY) {
if (this.runCommand(command) > 0) {
flag = 1;
}
}
} catch (Exception e) {
e.printStackTrace();
LOGGER.error("ProxysqlCommandExecutor-configFlush-error[" + e);
}
return flag;
}
public int configRefresh(List<String> refreshSqlList) {
configFlush();
return 0;
}
public int configRefreshAll() {
configFlush();
return 0;
}`
I'm trying to use JDBC (Spring Boot) to access the admin port on ProxySQL 1.4.10, but I get a failure when I issue the query "select * from mysql_users":
The source code is here: https://github.com/ArawakCC/proxysql-k8s-agent
Basically I'm running ProxySQL cluster with three instances inside a namespace in Kubernetes (minikube). My project is intended to provide a way to change ProxySQL configuration via an HTTP API.
When I hit my /users endpoint, it issues "select * from mysql_users" which invokes the connection pool, creates a connection, and sends the preamble SELECT, which ProxySQL doesn't recognise and rejects.
TCPdump:
No problem using the CLI.
I also tried using the MySQL driver directly, outside of the connection pool, with the same results. Tried also the Mariadb JDBC connector, which fails differently ("could not load system variables. socket connected: true")
There are similar issues already entered, but the most relevant are closed, and one suggests that a hacked JDBC connector is needed (I did try Drizzle, with no luck).