Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

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

Closed
hhyo opened this issue Oct 29, 2018 · 6 comments
Closed

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

hhyo opened this issue Oct 29, 2018 · 6 comments
Labels
bug Something isn't working

Comments

@hhyo
Copy link

hhyo commented Oct 29, 2018

  • 获取优化报告时，数据采样提示语法错误，错误信息：
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
@LPX-E5BD8 LPX-E5BD8 added the bug Something isn't working label Oct 29, 2018
@hhyo
Copy link
Author

hhyo commented Oct 29, 2018

@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
Copy link
Contributor

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

@hhyo
Copy link
Author

hhyo commented Oct 29, 2018

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

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

@martianzhang
Copy link
Contributor 

wantRowsCount := 300 * common.Config.SamplingStatisticTarget
soar -h | grep -i sampling
  -sampling-statistic-target int
        SamplingStatisticTarget, 数据采样因子，对应postgres的default_statistics_target (default 100)

@lenovore
Copy link

lenovore commented Dec 5, 2018

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

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

@hhyo
Copy link
Author

hhyo commented Jan 2, 2019

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

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
bug Something isn't working
Projects
None yet
Milestone
No milestone
Development

No branches or pull requests
4 participants
@martianzhang @hhyo @lenovore @LPX-E5BD8