{"action":"created","issue":{"url":"https://api.github.com/repos/sysown/proxysql/issues/1632","repository_url":"https://api.github.com/repos/sysown/proxysql","labels_url":"https://api.github.com/repos/sysown/proxysql/issues/1632/labels{/name}","comments_url":"https://api.github.com/repos/sysown/proxysql/issues/1632/comments","events_url":"https://api.github.com/repos/sysown/proxysql/issues/1632/events","html_url":"https://github.com/sysown/proxysql/issues/1632","id":346876838,"node_id":"MDU6SXNzdWUzNDY4NzY4Mzg=","number":1632,"title":"ProxySQL dropping connection during backend server master failover","user":{"login":"chaithanyaganta","id":7595101,"node_id":"MDQ6VXNlcjc1OTUxMDE=","avatar_url":"https://avatars3.githubusercontent.com/u/7595101?v=4","gravatar_id":"","url":"https://api.github.com/users/chaithanyaganta","html_url":"https://github.com/chaithanyaganta","followers_url":"https://api.github.com/users/chaithanyaganta/followers","following_url":"https://api.github.com/users/chaithanyaganta/following{/other_user}","gists_url":"https://api.github.com/users/chaithanyaganta/gists{/gist_id}","starred_url":"https://api.github.com/users/chaithanyaganta/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/chaithanyaganta/subscriptions","organizations_url":"https://api.github.com/users/chaithanyaganta/orgs","repos_url":"https://api.github.com/users/chaithanyaganta/repos","events_url":"https://api.github.com/users/chaithanyaganta/events{/privacy}","received_events_url":"https://api.github.com/users/chaithanyaganta/received_events","type":"User","site_admin":false},"labels":[],"state":"open","locked":false,"assignee":null,"assignees":[],"milestone":null,"comments":16,"created_at":"2018-08-02T06:30:46Z","updated_at":"2018-08-20T10:00:32Z","closed_at":null,"author_association":"NONE","body":"During backend server master failover (ie., in the target host group all the write nodes are either offline soft or offline hard), write queries are failing due to time-out from backend server (_**2013, 'Lost connection to MySQL server during query (timed out)**_). But, front-end proxysql connection is getting dropped immediately. We need to reconnect to query again.\r\nAlthough `mysql-connect_retries_on_failure` is set to 10, it is returning after a single failure.\r\n\r\nTransition of nodes status during this failover:\r\n\r\nInitial:\r\nMaster - ONLINE\r\nStandBy - OFFLINE_SOFT\r\n\r\nDuring failover (this is where we are getting query timeouts):\r\nMaster - OFFLINE_HARD\r\nStandBy - OFFLINE_SOFT \r\n\r\nAfter failover:\r\nMaster - OFFLINE_HARD\r\nStandBy(new Master) - ONLINE\r\n\r\nProxySQL version: **v1.4.9-1.1.el7**"},"comment":{"url":"https://api.github.com/repos/sysown/proxysql/issues/comments/414263643","html_url":"https://github.com/sysown/proxysql/issues/1632#issuecomment-414263643","issue_url":"https://api.github.com/repos/sysown/proxysql/issues/1632","id":414263643,"node_id":"MDEyOklzc3VlQ29tbWVudDQxNDI2MzY0Mw==","user":{"login":"renecannao","id":3645227,"node_id":"MDQ6VXNlcjM2NDUyMjc=","avatar_url":"https://avatars3.githubusercontent.com/u/3645227?v=4","gravatar_id":"","url":"https://api.github.com/users/renecannao","html_url":"https://github.com/renecannao","followers_url":"https://api.github.com/users/renecannao/followers","following_url":"https://api.github.com/users/renecannao/following{/other_user}","gists_url":"https://api.github.com/users/renecannao/gists{/gist_id}","starred_url":"https://api.github.com/users/renecannao/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/renecannao/subscriptions","organizations_url":"https://api.github.com/users/renecannao/orgs","repos_url":"https://api.github.com/users/renecannao/repos","events_url":"https://api.github.com/users/renecannao/events{/privacy}","received_events_url":"https://api.github.com/users/renecannao/received_events","type":"User","site_admin":false},"created_at":"2018-08-20T10:00:32Z","updated_at":"2018-08-20T10:00:32Z","author_association":"CONTRIBUTOR","body":"@chaithanyaganta , I slighting modified your code, here is the `diff`:\r\n```\r\n$ diff -u JdbcTest.java.orig JdbcTest.java\r\n--- JdbcTest.java.orig\t2018-08-20 11:48:21.268316327 +0200\r\n+++ JdbcTest.java\t2018-08-20 11:51:44.027986404 +0200\r\n@@ -1,9 +1,8 @@\r\n-package com.jcg.examples;\r\n-\r\n import java.sql.DriverManager;\r\n import java.sql.Connection;\r\n import java.sql.PreparedStatement;\r\n import java.sql.SQLException;\r\n+import java.util.concurrent.TimeUnit;\r\n \r\n /**\r\n  * @author ganta\r\n@@ -27,7 +26,7 @@\r\n \r\n         try {\r\n             connection = DriverManager\r\n-                    .getConnection(\"jdbc:mysql://127.0.0.1:3306/test\",\"admin\", \"admin\");\r\n+                    .getConnection(\"jdbc:mysql://127.0.0.1:6033/sbtest\",\"sbtest\", \"sbtest\");\r\n             connection.setAutoCommit(false);\r\n             int i=0;\r\n             while (i<500000) {\r\n@@ -42,6 +41,11 @@\r\n                     System.out.println(\"Query status: \" + pstmt.executeUpdate());\r\n \r\n                     connection.commit();\r\n+\t\t\t\t\tSystem.out.println(java.time.LocalTime.now()); \r\n+\t\t\t\t\ttry {\r\n+\t\t\t\t\t\tTimeUnit.SECONDS.sleep(1);\r\n+\t\t\t\t\t} catch (InterruptedException ignore) {\r\n+\t\t\t\t\t}\r\n                 } catch (SQLException e) {\r\n                     e.printStackTrace();\r\n                     //connection.rollback();\r\n```\r\nI didn't change anything in the way the code behaves, I only changed credentials, and added a timer and a sleep.\r\n\r\nHere is the output:\r\n```\r\njava -cp .:./mysql-connector-java-5.1.47.jar JdbcTestest.java \r\n-------- MySQL JDBC Connection Testing ------------\r\nMySQL JDBC Driver Registered!\r\nQuery status: 1\r\nQuery status: 1\r\n11:47:00.942\r\nQuery status: 1\r\nQuery status: 1\r\n11:47:01.971\r\nQuery status: 1\r\nQuery status: 1\r\n11:47:02.994\r\nQuery status: 1\r\nQuery status: 1\r\n11:47:09.012\r\nQuery status: 1\r\nQuery status: 1\r\n11:47:10.031\r\nQuery status: 1\r\nQuery status: 1\r\n11:47:11.055\r\nQuery status: 1\r\nQuery status: 1\r\n11:47:12.083\r\nQuery status: 1\r\nQuery status: 1\r\n11:47:13.124\r\n```\r\n\r\nYou can easily see that graceful switchover happened from 11:47:02.994 to 11:47:09.012 (6 seconds).\r\nNo errors, at all!\r\n\r\nWhat are you doing wrong?\r\nI don't know, but looking at your code my first concern is: are you really connecting to ProxySQL?\r\nThe doubt comes from 2 reasons:\r\n1. You seem to be using `admin:admin`, although the [wiki](https://github.com/sysown/proxysql/wiki/Global-variables#admin-admin_credentials) specifies:\r\n\r\n> users in admin-admin_credentials cannot be used also in mysql_users table.\r\n\r\n2. In your gist, from table `stats_mysql_query_digest` it seems user `mdsperf` is being used."}}
{"actor":{"display_login":"renecannao"}}