{"action":"created","issue":{"url":"https://api.github.com/repos/lerencao/notes/issues/85","repository_url":"https://api.github.com/repos/lerencao/notes","labels_url":"https://api.github.com/repos/lerencao/notes/issues/85/labels{/name}","comments_url":"https://api.github.com/repos/lerencao/notes/issues/85/comments","events_url":"https://api.github.com/repos/lerencao/notes/issues/85/events","html_url":"https://github.com/lerencao/notes/issues/85","id":347258414,"node_id":"MDU6SXNzdWUzNDcyNTg0MTQ=","number":85,"title":"JDBC loadbalance 模式下的一个坑","user":{"login":"lerencao","id":1456096,"node_id":"MDQ6VXNlcjE0NTYwOTY=","avatar_url":"https://avatars1.githubusercontent.com/u/1456096?v=4","gravatar_id":"","url":"https://api.github.com/users/lerencao","html_url":"https://github.com/lerencao","followers_url":"https://api.github.com/users/lerencao/followers","following_url":"https://api.github.com/users/lerencao/following{/other_user}","gists_url":"https://api.github.com/users/lerencao/gists{/gist_id}","starred_url":"https://api.github.com/users/lerencao/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/lerencao/subscriptions","organizations_url":"https://api.github.com/users/lerencao/orgs","repos_url":"https://api.github.com/users/lerencao/repos","events_url":"https://api.github.com/users/lerencao/events{/privacy}","received_events_url":"https://api.github.com/users/lerencao/received_events","type":"User","site_admin":false},"labels":[],"state":"open","locked":false,"assignee":null,"assignees":[],"milestone":null,"comments":0,"created_at":"2018-08-03T04:51:09Z","updated_at":"2018-08-03T05:41:25Z","closed_at":null,"author_association":"OWNER","body":"昨天在写代码插入数据到 tidb 的时候，时不时报错说 connection closed。一直没找到原因。\r\n今天梳理代码，发现了这个坑。\r\n\r\n\r\n用 jdbc 连接  tidb 的时候，\r\n\r\n- 配置了 `jdbc:mysql:loadbalance` 模式（后面有多台 tidb，暂时还没有 load balance，只能先通过这种方式来搞）。\r\n- 然后 jdbc 连接中没有显式的配置用哪个库。\r\n\r\n这个时候，mysql client 底层在做 loadblance 的时候，会把之前 connection 的状态 sync 到新的 connection 上。这里面包括了：\r\n\r\n- auto commit\r\n- catalog\r\n- isolation level\r\n- max rows\r\n\r\n``` java\r\n  void syncSessionState(Connection source, Connection target, boolean readOnly) throws SQLException {\r\n        if (target != null) {\r\n            target.setReadOnly(readOnly);\r\n        }\r\n\r\n        if (source == null || target == null) {\r\n            return;\r\n        }\r\n\r\n        boolean prevUseLocalSessionState = source.getUseLocalSessionState();\r\n        source.setUseLocalSessionState(true);\r\n\r\n        target.setAutoCommit(source.getAutoCommit());\r\n        target.setCatalog(source.getCatalog());\r\n        target.setTransactionIsolation(source.getTransactionIsolation());\r\n        target.setSessionMaxRows(source.getSessionMaxRows());\r\n\r\n        source.setUseLocalSessionState(prevUseLocalSessionState);\r\n    }\r\n```\r\n\r\n需要注意了，连接配置中没有设置 catalog，所以会是空字符串。\r\n\r\n`setCatalog` 拿到这个空字符串，会发送  __USE ``__  命令到 Server 端。\r\n\r\n``` java\r\n            StringBuilder query = new StringBuilder(\"USE \");\r\n            query.append(StringUtils.quoteIdentifier(catalog, quotedId, getPedantic()));\r\n\r\n            execSQL(null, query.toString(), -1, null, DEFAULT_RESULT_SET_TYPE, DEFAULT_RESULT_SET_CONCURRENCY, false, this.database, null, false);\r\n\r\n            this.database = catalog;\r\n```\r\n\r\nTiDB（2.0.2） 对于这样的命令，会直接报错说找不到 database。\r\n\r\n```\r\nServer version: 5.7.10-TiDB-v2.0.2 MySQL Community Server (Apache License 2.0)\r\n\r\nCopyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.\r\n\r\nOracle is a registered trademark of Oracle Corporation and/or its\r\naffiliates. Other names may be trademarks of their respective\r\nowners.\r\n\r\nType 'help;' or '\\h' for help. Type '\\c' to clear the current input statement.\r\n\r\nmysql> use ``;\r\nERROR 1049 (42000): Unknown database ''\r\n```\r\n\r\n``` go\r\nfunc (e *SimpleExec) executeUse(s *ast.UseStmt) error {\r\n\tdbname := model.NewCIStr(s.DBName)\r\n\tdbinfo, exists := e.is.SchemaByName(dbname)\r\n\tif !exists {\r\n\t\treturn infoschema.ErrDatabaseNotExists.GenByArgs(dbname)\r\n\t}\r\n\te.ctx.GetSessionVars().CurrentDB = dbname.O\r\n\t// character_set_database is the character set used by the default database.\r\n\t// The server sets this variable whenever the default database changes.\r\n\t// See http://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_character_set_database\r\n\tsessionVars := e.ctx.GetSessionVars()\r\n\tterror.Log(errors.Trace(sessionVars.SetSystemVar(variable.CharsetDatabase, dbinfo.Charset)))\r\n\tterror.Log(errors.Trace(sessionVars.SetSystemVar(variable.CollationDatabase, dbinfo.Collate)))\r\n\treturn nil\r\n}\r\n```\r\n\r\nMySQL 会报错说 No database selected 。\r\n\r\n```\r\nServer version: 5.6.24-72.2-log Percona Server (GPL), Release 72.2, Revision 8d0f85b\r\n\r\nCopyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.\r\n\r\nOracle is a registered trademark of Oracle Corporation and/or its\r\naffiliates. Other names may be trademarks of their respective\r\nowners.\r\n\r\nType 'help;' or '\\h' for help. Type '\\c' to clear the current input statement.\r\n\r\nmysql> use ``;\r\nERROR 1046 (3D000): No database selected\r\n```\r\n"},"comment":{"url":"https://api.github.com/repos/lerencao/notes/issues/comments/410150270","html_url":"https://github.com/lerencao/notes/issues/85#issuecomment-410150270","issue_url":"https://api.github.com/repos/lerencao/notes/issues/85","id":410150270,"node_id":"MDEyOklzc3VlQ29tbWVudDQxMDE1MDI3MA==","user":{"login":"lerencao","id":1456096,"node_id":"MDQ6VXNlcjE0NTYwOTY=","avatar_url":"https://avatars1.githubusercontent.com/u/1456096?v=4","gravatar_id":"","url":"https://api.github.com/users/lerencao","html_url":"https://github.com/lerencao","followers_url":"https://api.github.com/users/lerencao/followers","following_url":"https://api.github.com/users/lerencao/following{/other_user}","gists_url":"https://api.github.com/users/lerencao/gists{/gist_id}","starred_url":"https://api.github.com/users/lerencao/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/lerencao/subscriptions","organizations_url":"https://api.github.com/users/lerencao/orgs","repos_url":"https://api.github.com/users/lerencao/repos","events_url":"https://api.github.com/users/lerencao/events{/privacy}","received_events_url":"https://api.github.com/users/lerencao/received_events","type":"User","site_admin":false},"created_at":"2018-08-03T05:41:25Z","updated_at":"2018-08-03T05:41:25Z","author_association":"OWNER","body":"在tidb 和 mysql 都报错的情况下， load balance  就会 close 这个connection。上层业务就会报错。\r\n\r\n\r\n"}}
{"actor":{"display_login":"lerencao"}}