{"action":"created","issue":{"url":"https://api.github.com/repos/sysown/proxysql/issues/1388","repository_url":"https://api.github.com/repos/sysown/proxysql","labels_url":"https://api.github.com/repos/sysown/proxysql/issues/1388/labels{/name}","comments_url":"https://api.github.com/repos/sysown/proxysql/issues/1388/comments","events_url":"https://api.github.com/repos/sysown/proxysql/issues/1388/events","html_url":"https://github.com/sysown/proxysql/issues/1388","id":298917644,"number":1388,"title":"Multiplexing causes application failure (probably expected, but checking anyway)","user":{"login":"drpauldixon","id":8829666,"avatar_url":"https://avatars0.githubusercontent.com/u/8829666?v=4","gravatar_id":"","url":"https://api.github.com/users/drpauldixon","html_url":"https://github.com/drpauldixon","followers_url":"https://api.github.com/users/drpauldixon/followers","following_url":"https://api.github.com/users/drpauldixon/following{/other_user}","gists_url":"https://api.github.com/users/drpauldixon/gists{/gist_id}","starred_url":"https://api.github.com/users/drpauldixon/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/drpauldixon/subscriptions","organizations_url":"https://api.github.com/users/drpauldixon/orgs","repos_url":"https://api.github.com/users/drpauldixon/repos","events_url":"https://api.github.com/users/drpauldixon/events{/privacy}","received_events_url":"https://api.github.com/users/drpauldixon/received_events","type":"User","site_admin":false},"labels":[],"state":"open","locked":false,"assignee":null,"assignees":[],"milestone":null,"comments":3,"created_at":"2018-02-21T10:36:25Z","updated_at":"2018-02-26T13:08:51Z","closed_at":null,"author_association":"NONE","body":"We have a customised drupal application that takes a pre-configured account number and contract number, which then runs some SQL across 2x schemas running on a single database server (dev environment). All queries are run via proxysql. There are no rules in place for read/write splitting etc. It's just straight proxying for now.\r\n\r\nWhen multiplexing is enabled, the registration fails. It appears that some SQL queries are run out of order - there are some transactions in the general log which I've captured, which I believe would temporarily disable multiplexing. However, I think there are other sql queries (outside of the transaction) being run on other threads, resulting in a SELECT running before an INSERT, resulting in empty data being returned.\r\n\r\nI disabled multiplexing and tried again. This time, the registration was successful.\r\n\r\nI tried 3x registrations with multiplexing enabled (all failed).\r\nI tried 3x registrations with multiplexing disable (all succeeded).\r\n\r\nBellow are some greps from the general log file for the 2x SQL users used to complete the registration (unfortunately, other things were happing at the same time - it's a shared dev system). However, there are differences in the log:\r\n\r\nWith multiplexing disabled, I see change user.\r\nWith multiplexing enabled, I see connect.\r\n\r\nWhich is expected, since in the latter case I'd expect proxysql to multiplex the requests over many connections.\r\n\r\nI imagine that due to the nature of the underlying drupal code, the only option is to globally disable multiplexing.\r\n\r\nRegards,\r\nPaul.\r\n\r\n\r\n## Registration success (multiplexing disabled)\r\n\r\n```\r\negrep \"(xduser|xfuser)\" registration_working_multiplexing_disabled.log\r\n    16938022 Change user\txfuser@10.100.1.61 on xfw\r\n    16938023 Change user\txduser@10.100.1.61 on xds\r\n    16938023 Change user\txduser@10.100.1.61 on xds\r\n    16939034 Change user\txduser@10.100.1.61 on xds\r\n    16939034 Change user\txduser@10.100.1.61 on xds\r\n    16939041 Change user\txduser@10.100.1.61 on xds\r\n    16938023 Change user\txduser@10.100.1.61 on xds\r\n    16939041 Change user\txduser@10.100.1.61 on xds\r\n    16939035 Change user\txfuser@10.100.1.61 on xfw\r\n    16938022 Change user\txfuser@10.100.1.61 on xfw\r\n    16911625 Change user\txfuser@10.100.1.61 on xfw\r\n    16939034 Change user\txduser@10.100.1.61 on xds\r\n    16939034 Change user\txduser@10.100.1.61 on xds\r\n    16939046 Change user\txduser@10.100.1.61 on xds\r\n    16847810 Change user\txduser@10.100.1.61 on xds\r\n    16852851 Change user\txfuser@10.100.1.61 on xfw\r\n    16847810 Change user\txduser@10.100.1.61 on xds\r\n    16939046 Change user\txduser@10.100.1.61 on xds\r\n    16939043 Change user\txduser@10.100.1.61 on xds\r\n    16852851 Change user\txfuser@10.100.1.61 on xfw\r\n    16939043 Change user\txduser@10.100.1.61 on xds\r\n    16939035 Change user\txfuser@10.100.1.61 on xfw\r\n    16852851 Change user\txfuser@10.100.1.61 on xfw\r\n    16911625 Change user\txfuser@10.100.1.61 on xfw\r\n    16852851 Change user\txfuser@10.100.1.61 on xfw\r\n```\r\n\r\n## Registration failure (multiplexing enabled)\r\n\r\n```\r\negrep \"(xduser|xfuser)\" registration_failing_multiplexing_enabled.log\r\n    13838895 Connect\txfuser@10.100.1.61 on xfw\r\n    13838916 Connect\txduser@10.100.1.61 on xds\r\n    13838933 Connect\txduser@10.100.1.61 on xds\r\n    13838935 Connect\txduser@10.100.1.61 on xds\r\n    13838942 Connect\txfuser@10.100.1.61 on xfw\r\n    13838943 Connect\txfuser@10.100.1.61 on xfw\r\n    13838947 Connect\txduser@10.100.1.61 on xds\r\n    13838948 Connect\txduser@10.100.1.61 on xds\r\n    13838950 Connect\txduser@10.100.1.61 on xds\r\n    13838954 Connect\txduser@10.100.1.61 on xds\r\n```\r\n"},"comment":{"url":"https://api.github.com/repos/sysown/proxysql/issues/comments/368498901","html_url":"https://github.com/sysown/proxysql/issues/1388#issuecomment-368498901","issue_url":"https://api.github.com/repos/sysown/proxysql/issues/1388","id":368498901,"user":{"login":"tapuhi","id":26842593,"avatar_url":"https://avatars2.githubusercontent.com/u/26842593?v=4","gravatar_id":"","url":"https://api.github.com/users/tapuhi","html_url":"https://github.com/tapuhi","followers_url":"https://api.github.com/users/tapuhi/followers","following_url":"https://api.github.com/users/tapuhi/following{/other_user}","gists_url":"https://api.github.com/users/tapuhi/gists{/gist_id}","starred_url":"https://api.github.com/users/tapuhi/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/tapuhi/subscriptions","organizations_url":"https://api.github.com/users/tapuhi/orgs","repos_url":"https://api.github.com/users/tapuhi/repos","events_url":"https://api.github.com/users/tapuhi/events{/privacy}","received_events_url":"https://api.github.com/users/tapuhi/received_events","type":"User","site_admin":false},"created_at":"2018-02-26T13:08:51Z","updated_at":"2018-02-26T13:08:51Z","author_association":"NONE","body":"We have the same issue . Following . Anyway by using read-committed it solved our issue in part of the dbs . Some others we cannot move to read-committed due to application restrictions. "}}
{"actor":{"display_login":"tapuhi"}}