{"action":"created","issue":{"url":"https://api.github.com/repos/XiaoMi/soar/issues/58","repository_url":"https://api.github.com/repos/XiaoMi/soar","labels_url":"https://api.github.com/repos/XiaoMi/soar/issues/58/labels{/name}","comments_url":"https://api.github.com/repos/XiaoMi/soar/issues/58/comments","events_url":"https://api.github.com/repos/XiaoMi/soar/issues/58/events","html_url":"https://github.com/XiaoMi/soar/issues/58","id":374894195,"node_id":"MDU6SXNzdWUzNzQ4OTQxOTU=","number":58,"title":"Sampling Error，You  have an error in your SQL syntax;","user":{"login":"hhyo","id":8842982,"node_id":"MDQ6VXNlcjg4NDI5ODI=","avatar_url":"https://avatars1.githubusercontent.com/u/8842982?v=4","gravatar_id":"","url":"https://api.github.com/users/hhyo","html_url":"https://github.com/hhyo","followers_url":"https://api.github.com/users/hhyo/followers","following_url":"https://api.github.com/users/hhyo/following{/other_user}","gists_url":"https://api.github.com/users/hhyo/gists{/gist_id}","starred_url":"https://api.github.com/users/hhyo/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/hhyo/subscriptions","organizations_url":"https://api.github.com/users/hhyo/orgs","repos_url":"https://api.github.com/users/hhyo/repos","events_url":"https://api.github.com/users/hhyo/events{/privacy}","received_events_url":"https://api.github.com/users/hhyo/received_events","type":"User","site_admin":false},"labels":[{"id":1083197916,"node_id":"MDU6TGFiZWwxMDgzMTk3OTE2","url":"https://api.github.com/repos/XiaoMi/soar/labels/bug","name":"bug","color":"d73a4a","default":true}],"state":"closed","locked":false,"assignee":null,"assignees":[],"milestone":null,"comments":5,"created_at":"2018-10-29T07:46:59Z","updated_at":"2019-01-02T06:37:37Z","closed_at":"2018-12-26T06:22:59Z","author_association":"NONE","body":"- 获取优化报告时，数据采样提示语法错误，错误信息：\r\n\r\n```\r\n2018/10/29 15:24:59.806 [D] [mysql.go:76] Execute SQL with DSN(mysql:3306/optimizer_ix4i4k7wMDiYHo1G) : CREATE TABLE `sql_workflow` (\r\n  `id` int(11) NOT NULL AUTO_INCREMENT,\r\n  `workflow_name` varchar(50) NOT NULL ,\r\n  `engineer` varchar(50) NOT NULL,\r\n  `review_man` varchar(50) NOT NULL,\r\n  `create_time` datetime(6) NOT NULL,\r\n  `finish_time` datetime(6) DEFAULT NULL,\r\n  `status` varchar(50) NOT NULL,\r\n  `is_backup` varchar(20) NOT NULL,\r\n  `review_content` longtext NOT NULL,\r\n  `cluster_name` varchar(50) NOT NULL,\r\n  `reviewok_time` datetime(6) DEFAULT NULL,\r\n  `sql_content` longtext NOT NULL,\r\n  `execute_result` longtext NOT NULL,\r\n  `is_manual` int(11) NOT NULL,\r\n  `audit_remark` varchar(200) NOT NULL,\r\n  PRIMARY KEY (`id`)\r\n) ENGINE=InnoDB AUTO_INCREMENT=72 DEFAULT CHARSET=utf8\r\n2018/10/29 15:24:59.829 [D] [env.go:397] createTable, Start Sampling data from archer.sql_workflow to optimizer_ix4i4k7wMDiYHo1G.sql_workflow ...\r\n2018/10/29 15:24:59.830 [D] [mysql.go:76] Execute SQL with DSN(mysql:3306/archer) : show table status where name = 'sql_workflow'\r\n2018/10/29 15:24:59.832 [D] [mysql.go:76] Execute SQL with DSN(mysql:3306/archer) : show table status where name = 'sql_workflow'\r\n2018/10/29 15:24:59.833 [D] [sampling.go:99] SamplingData, tableRows: 66, wantRowsCount: 30000, factor: 454.545455\r\n2018/10/29 15:24:59.833 [D] [sampling.go:118] Sampling data execute: select DATA_TYPE from information_schema.COLUMNS where TABLE_SCHEMA='optimizer_ix4i4k\r\n7wMDiYHo1G' and TABLE_NAME = 'sql_workflow'\r\n2018/10/29 15:24:59.839 [E] [sampling.go:227] doSampling Error from optimizer_ix4i4k7wMDiYHo1G.sql_workflow: Received #1064 error from MySQL server: \"You \r\nhave an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '0_0_0'\", \"None\", \"0\", \"\r\n\"], [2, \"CHECKED\", 0, \"Audit completed\", \"None\", \"create ' at line 1\"\r\n2018/10/29 15:24:59.839 [D] [sampling.go:215] 70 rows sampling out\r\n2018/10/29 15:24:59.840 [D] [index.go:84] Enter: NewAdvisor(), Caller: main.main\r\n```\r\n\r\n- 语句和数据信息(表结构上面的错误日志有)\r\n```\r\nselect * from sql_workflow;\r\n```\r\n```\r\nINSERT INTO `sql_workflow` (`id`,`workflow_name`,`engineer`,`review_man`,`create_time`,`finish_time`,`status`,`is_backup`,`review_content`,`cluster_name`,`reviewok_time`,`sql_content`,`execute_result`,`is_manual`,`audit_remark`) VALUES ('1','创建用户信息表','archer','[\"auditor\", \"\"]','2018-06-07 16:34:41','2018-06-07 16:35:28','已正常结束','是','[[1, \"CHECKED\", 0, \"Audit completed\", \"None\", \"use archer_test\", 0, \"'0_0_0'\", \"None\", \"0\", \"\"], [2, \"CHECKED\", 0, \"Audit completed\", \"None\", \"create table users(\\r\\n  id bigint unsigned auto_increment comment 'id',\\r\\n  username varchar(20) not null default '' comment '\\u7528\\u6237\\u540d',\\r\\n  nickname varchar(20) not null default '' comment '\\u59d3\\u540d',\\r\\n  phone varchar(20) not null default '' comment '\\u624b\\u673a\\u53f7',\\r\\n  email varchar(50) not null default '' comment '\\u90ae\\u7bb1',\\r\\n  id_number varchar(18) not null default '' comment '\\u8eab\\u4efd\\u8bc1\\u53f7\\u7801',\\r\\n  primary key(id)\\r\\n) engine=innodb,charset utf8mb4,comment '\\u7528\\u6237\\u4fe1\\u606f\\u8868'\", 0, \"'0_0_1'\", \"mysql_3306_archer_test\", \"0\", \"\"]]','archer','2018-06-07 16:35:28','use archer_test; create table users( id bigint unsigned auto_increment comment 'id', username varchar(20) not null default '' comment '用户名', nickname varchar(20) not null default '' comment '姓名', phone varchar(20) not null default '' comment '手机号', email varchar(50) not null default '' comment '邮箱', id_number varchar(18) not null default '' comment '身份证号码', primary key(id) ) engine=innodb,charset utf8mb4,comment '用户信息表';','[[1, \"RERUN\", 0, \"Execute Successfully\", \"None\", \"use archer_test\", 0, \"'1528360528_136_0'\", \"None\", \"0.000\", \"\"], [2, \"EXECUTED\", 0, \"Execute Successfully\", \"None\", \"create table users(\\r\\n  id bigint unsigned auto_increment comment 'id',\\r\\n  username varchar(20) not null default '' comment '\\u7528\\u6237\\u540d',\\r\\n  nickname varchar(20) not null default '' comment '\\u59d3\\u540d',\\r\\n  phone varchar(20) not null default '' comment '\\u624b\\u673a\\u53f7',\\r\\n  email varchar(50) not null default '' comment '\\u90ae\\u7bb1',\\r\\n  id_number varchar(18) not null default '' comment '\\u8eab\\u4efd\\u8bc1\\u53f7\\u7801',\\r\\n  primary key(id)\\r\\n) engine=innodb,charset utf8mb4,comment '\\u7528\\u6237\\u4fe1\\u606f\\u8868'\", 0, \"'1528360528_136_1'\", \"mysql_3306_archer_test\", \"0.010\", \"\"]]','0','');\r\n```\r\n\r\n- 配置信息和版本信息\r\n```\r\n ./soar -version\r\nVersion: 2018-10-23 16:27:51 +0800 @7519019\r\nBranch: master\r\nCompile: 2018-10-23 18:26:17 +0800 by go version go1.10.4 linux/amd64\r\nGitDirty: 105\r\n\r\n./soar -print-config\r\nallow-online-as-test: false\r\ndrop-test-temporary: true\r\nonly-syntax-check: false\r\nsampling-statistic-target: 100\r\nsampling: true\r\nprofiling: false\r\ntrace: false\r\nexplain: true\r\nconn-time-out: 3\r\nquery-time-out: 30\r\ndelimiter: ;\r\nlog-level: 7\r\nlog-output: /opt/archery/downloads/log/soar.log\r\nreport-type: html\r\nreport-css: \"\"\r\nreport-javascript: \"\"\r\nreport-title: SQL优化分析报告\r\nmarkdown-extensions: 94\r\nmarkdown-html-flags: 0\r\nignore-rules:\r\n- \"\"\r\nrewrite-rules:\r\n- delimiter\r\n- orderbynull\r\n- groupbyconst\r\n- dmlorderby\r\n- having\r\n- star2columns\r\n- insertcolumns\r\n- distinctstar\r\nblacklist: /opt/soar.blacklist\r\nmax-join-table-count: 5\r\nmax-group-by-cols-count: 5\r\nmax-distinct-count: 5\r\nmax-index-cols-count: 5\r\nmax-total-rows: 9999999\r\nmax-query-cost: 9999\r\nspaghetti-query-length: 2048\r\nallow-drop-index: false\r\nmax-in-count: 10\r\nmax-index-bytes-percolumn: 767\r\nmax-index-bytes: 3072\r\ntable-allow-charsets:\r\n- utf8\r\n- utf8mb4\r\ntable-allow-engines:\r\n- innodb\r\nmax-index-count: 10\r\nmax-column-count: 40\r\nindex-prefix: idx_\r\nunique-key-prefix: uk_\r\nmax-subquery-depth: 5\r\nmax-varchar-length: 1024\r\nexplain-sql-report-type: pretty\r\nexplain-type: extended\r\nexplain-format: traditional\r\nexplain-warn-select-type:\r\n- \"\"\r\nexplain-warn-access-type:\r\n- ALL\r\nexplain-max-keys: 3\r\nexplain-min-keys: 0\r\nexplain-max-rows: 10000\r\nexplain-warn-extra:\r\n- \"\"\r\nexplain-max-filtered: 100\r\nexplain-warn-scalability:\r\n- O(n)\r\nshow-warnings: false\r\nshow-last-query-cost: false\r\nquery: \"\"\r\nlist-heuristic-rules: false\r\nlist-rewrite-rules: false\r\nlist-test-sqls: false\r\nlist-report-types: false\r\nverbose: true\r\ndry-run: true\r\nmax-pretty-sql-length: 1024\r\n```\r\n\r\n\r\n"},"comment":{"url":"https://api.github.com/repos/XiaoMi/soar/issues/comments/450794686","html_url":"https://github.com/XiaoMi/soar/issues/58#issuecomment-450794686","issue_url":"https://api.github.com/repos/XiaoMi/soar/issues/58","id":450794686,"node_id":"MDEyOklzc3VlQ29tbWVudDQ1MDc5NDY4Ng==","user":{"login":"hhyo","id":8842982,"node_id":"MDQ6VXNlcjg4NDI5ODI=","avatar_url":"https://avatars1.githubusercontent.com/u/8842982?v=4","gravatar_id":"","url":"https://api.github.com/users/hhyo","html_url":"https://github.com/hhyo","followers_url":"https://api.github.com/users/hhyo/followers","following_url":"https://api.github.com/users/hhyo/following{/other_user}","gists_url":"https://api.github.com/users/hhyo/gists{/gist_id}","starred_url":"https://api.github.com/users/hhyo/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/hhyo/subscriptions","organizations_url":"https://api.github.com/users/hhyo/orgs","repos_url":"https://api.github.com/users/hhyo/repos","events_url":"https://api.github.com/users/hhyo/events{/privacy}","received_events_url":"https://api.github.com/users/hhyo/received_events","type":"User","site_admin":false},"created_at":"2019-01-02T06:37:37Z","updated_at":"2019-01-02T06:37:37Z","author_association":"NONE","body":"两个case测试都正常，谢谢~"}}
{"actor":{"display_login":"hhyo"}}