Sampling Error，You have an error in your SQL syntax;

[hhyo]

• 获取优化报告时，数据采样提示语法错误，错误信息：

2018/10/29 15:24:59.806 [D] [mysql.go:76] Execute SQL with DSN(mysql:3306/optimizer_ix4i4k7wMDiYHo1G) : CREATE TABLE `sql_workflow` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `workflow_name` varchar(50) NOT NULL ,
  `engineer` varchar(50) NOT NULL,
  `review_man` varchar(50) NOT NULL,
  `create_time` datetime(6) NOT NULL,
  `finish_time` datetime(6) DEFAULT NULL,
  `status` varchar(50) NOT NULL,
  `is_backup` varchar(20) NOT NULL,
  `review_content` longtext NOT NULL,
  `cluster_name` varchar(50) NOT NULL,
  `reviewok_time` datetime(6) DEFAULT NULL,
  `sql_content` longtext NOT NULL,
  `execute_result` longtext NOT NULL,
  `is_manual` int(11) NOT NULL,
  `audit_remark` varchar(200) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=72 DEFAULT CHARSET=utf8
2018/10/29 15:24:59.829 [D] [env.go:397] createTable, Start Sampling data from archer.sql_workflow to optimizer_ix4i4k7wMDiYHo1G.sql_workflow ...
2018/10/29 15:24:59.830 [D] [mysql.go:76] Execute SQL with DSN(mysql:3306/archer) : show table status where name = 'sql_workflow'
2018/10/29 15:24:59.832 [D] [mysql.go:76] Execute SQL with DSN(mysql:3306/archer) : show table status where name = 'sql_workflow'
2018/10/29 15:24:59.833 [D] [sampling.go:99] SamplingData, tableRows: 66, wantRowsCount: 30000, factor: 454.545455
2018/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
7wMDiYHo1G' and TABLE_NAME = 'sql_workflow'
2018/10/29 15:24:59.839 [E] [sampling.go:227] doSampling Error from optimizer_ix4i4k7wMDiYHo1G.sql_workflow: Received #1064 error from MySQL server: "You 
have 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", "
"], [2, "CHECKED", 0, "Audit completed", "None", "create ' at line 1"
2018/10/29 15:24:59.839 [D] [sampling.go:215] 70 rows sampling out
2018/10/29 15:24:59.840 [D] [index.go:84] Enter: NewAdvisor(), Caller: main.main

• 语句和数据信息(表结构上面的错误日志有)

  select * from sql_workflow;

  INSERT 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','');

• 配置信息和版本信息

  
  ./soar -version
  Version: 2018-10-23 16:27:51 +0800 @7519019
  Branch: master
  Compile: 2018-10-23 18:26:17 +0800 by go version go1.10.4 linux/amd64
  GitDirty: 105

./soar -print-config allow-online-as-test: false drop-test-temporary: true only-syntax-check: false sampling-statistic-target: 100 sampling: true profiling: false trace: false explain: true conn-time-out: 3 query-time-out: 30 delimiter: ; log-level: 7 log-output: /opt/archery/downloads/log/soar.log report-type: html report-css: "" report-javascript: "" report-title: SQL优化分析报告 markdown-extensions: 94 markdown-html-flags: 0 ignore-rules:

• "" rewrite-rules:
• delimiter
• orderbynull
• groupbyconst
• dmlorderby
• having
• star2columns
• insertcolumns
• distinctstar blacklist: /opt/soar.blacklist max-join-table-count: 5 max-group-by-cols-count: 5 max-distinct-count: 5 max-index-cols-count: 5 max-total-rows: 9999999 max-query-cost: 9999 spaghetti-query-length: 2048 allow-drop-index: false max-in-count: 10 max-index-bytes-percolumn: 767 max-index-bytes: 3072 table-allow-charsets:
• utf8
• utf8mb4 table-allow-engines:
• innodb max-index-count: 10 max-column-count: 40 index-prefix: idx unique-key-prefix: uk max-subquery-depth: 5 max-varchar-length: 1024 explain-sql-report-type: pretty explain-type: extended explain-format: traditional explain-warn-select-type:
• "" explain-warn-access-type:
• ALL explain-max-keys: 3 explain-min-keys: 0 explain-max-rows: 10000 explain-warn-extra:
• "" explain-max-filtered: 100 explain-warn-scalability:
• O(n) show-warnings: false show-last-query-cost: false query: "" list-heuristic-rules: false list-rewrite-rules: false list-test-sqls: false list-report-types: false verbose: true dry-run: true max-pretty-sql-length: 1024

[hhyo]

@liipx 另外一个Sampling data的错误

• 准备表结构和数据

  create table soar_test(
  id bigint unsigned primary key auto_increment,
  v varchar(10) not null default ''
  );
  insert into soar_test (v)
  values ('');

• 输入语句 select * from soar_test;

• 错误日志

  2018/10/29 16:30:20.994 [D] [mysql.go:76] Execute SQL with DSN(127.0.0.1:3306/optimizer_osGC5MGrm3tl97k4) : CREATE TABLE `soar_test` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `v` varchar(10) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`)
  ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8
  2018/10/29 16:30:21.020 [D] [env.go:397] createTable, Start Sampling data from archery.soar_test to optimizer_osGC5MGrm3tl97k4.soar_test ...
  2018/10/29 16:30:21.025 [D] [mysql.go:76] Execute SQL with DSN(mysql:3306/archery) : show table status where name = 'soar_test'
  2018/10/29 16:30:21.032 [D] [mysql.go:76] Execute SQL with DSN(mysql:3306/archery) : show table status where name = 'soar_test'
  2018/10/29 16:30:21.039 [D] [sampling.go:99] SamplingData, tableRows: 1, wantRowsCount: 30000, factor: 30000.000000
  2018/10/29 16:30:21.039 [D] [sampling.go:118] Sampling data execute: select DATA_TYPE from information_schema.COLUMNS where TABLE_SCHEMA='optimizer_osGC5MGrm3tl97k4' and TABLE_NAME = 'soar_test'
  2018/10/29 16:30:21.046 [E] [sampling.go:227] doSampling Error from optimizer_osGC5MGrm3tl97k4.soar_test: Received #1048 error from MySQL server: "Column 'v' cannot be null"
  2018/10/29 16:30:21.046 [D] [sampling.go:215] 1 rows sampling out

[LPX-E5BD8]

贴case的时候时候小心泄露隐私数据。

[hhyo]

贴case的时候时候小心泄露隐私数据。

谢谢提醒，是特意准备的测试数据，库名也是开源的项目测试库。不过问一句wantRowsCount: 30000是可配置还是固定的。

[martianzhang]

wantRowsCount := 300 * common.Config.SamplingStatisticTarget

soar -h | grep -i sampling
  -sampling-statistic-target int
        SamplingStatisticTarget, 数据采样因子，对应postgres的default_statistics_target (default 100)

[lenovore]

Received #1048 error from MySQL server: "Column 'v' cannot be null"

Sampling data会把空字符串转换成null？

[hhyo]

两个case测试都正常，谢谢~