MySQL 双引号导致ERROR 1054 (42S22) at line 1: Unknown column

• 问题现象
• 问题排查

问题现象

发现监控进程获取MySQL数据的时候报错导致监控进程报错：

mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1054 (42S22) at line 1: Unknown column 'sync_binlog' in 'where clause'
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1054 (42S22) at line 1: Unknown column 'innodb_flush_log_at_trx_commit' in 'where clause'
Can't close! at /app/teledb/teledb/152/orzdba_8801/oradba line 670.

问题排查

1. 从监控脚本中获取到监控查询sql：

show variables where Variable_name in ("sync_binlog","max_connections","max_user_connections","max_connect_errors","table_open_cache","table_definition_cache","thread_cache_size","binlog_format","open_files_limit","max_binlog_size","max_binlog_cache_size");

show variables where Variable_name in ("innodb_flush_log_at_trx_commit","innodb_flush_method","innodb_buffer_pool_size","innodb_max_dirty_pages_pct","innodb_log_buffer_size","innodb_log_file_size","innodb_log_files_in_group","innodb_thread_concurrency","innodb_file_per_table","innodb_adaptive_hash_index","innodb_open_files","innodb_io_capacity","innodb_read_io_threads","innodb_write_io_threads","innodb_adaptive_flushing","innodb_lock_wait_timeout","innodb_log_files_in_group");

2. 排查sql问题，在不同集群中查询验证
   相同的sql在同一个集群中，插叙正常；但是在问题实例中报错：

## 正常实例
mysql> show variables where variable_name in ("sync_binlog","max_connections","max_user_connections","max_connect_errors","table_open_cache","table_definition_cache","thread_cache_size","binlog_format","open_files_limit","max_binlog_size","max_binlog_cache_size");
+------------------------+------------+
| Variable_name          | Value      |
+------------------------+------------+
| binlog_format          | ROW        |
| max_binlog_cache_size  | 4294967296 |
| max_binlog_size        | 1073741824 |
| max_connect_errors     | 1000000    |
| max_connections        | 3000       |
| max_user_connections   | 0          |
| open_files_limit       | 131072     |
| sync_binlog            | 1          |
| table_definition_cache | 4096       |
| table_open_cache       | 4096       |
| thread_cache_size      | 64         |
+------------------------+------------+
11 rows in set (0.00 sec)

mysql> select * from information_schema.global_variables where variable_name in ("sync_binlog","max_connections","max_user_connections","max_connect_errors","table_open_cache","table_definition_cache","thread_cache_size","binlog_format","open_files_limit","max_binlog_size","max_binlog_cache_size");
+------------------------+----------------+
| VARIABLE_NAME          | VARIABLE_VALUE |
+------------------------+----------------+
| BINLOG_FORMAT          | ROW            |
| MAX_BINLOG_CACHE_SIZE  | 4294967296     |
| OPEN_FILES_LIMIT       | 131072         |
| MAX_BINLOG_SIZE        | 1073741824     |
| MAX_USER_CONNECTIONS   | 0              |
| SYNC_BINLOG            | 1              |
| MAX_CONNECTIONS        | 3000           |
| TABLE_DEFINITION_CACHE | 4096           |
| MAX_CONNECT_ERRORS     | 1000000        |
| TABLE_OPEN_CACHE       | 4096           |
| THREAD_CACHE_SIZE      | 64             |
+------------------------+----------------+
11 rows in set, 1 warning (0.00 sec)

## 问题实例
mysql> explain select * from information_schema.global_variables where VARIABLE_NAME in ("sync_binlog","max_connections","max_user_connections","max_connect_errors","table_open_cache","table_definition_cache","thread_cache_size","binlog_format","open_files_limit","max_binlog_size","max_binlog_cache_size");
ERROR 1054 (42S22): Unknown column 'sync_binlog' in 'where clause'
mysql> show variables where Variable_name in ("innodb_flush_log_at_trx_commit","innodb_flush_method","innodb_buffer_pool_size","innodb_max_dirty_pages_pct","innodb_log_buffer_size","innodb_log_file_size","innodb_log_files_in_group","innodb_thread_concurrency","innodb_file_per_table","innodb_adaptive_hash_index","innodb_open_files","innodb_io_capacity","innodb_read_io_threads","innodb_write_io_threads","innodb_adaptive_flushing","innodb_lock_wait_timeout","innodb_log_files_in_group");
ERROR 1054 (42S22): Unknown column 'innodb_flush_log_at_trx_commit' in 'where clause

3. 确认sql本身导致该问题，但问题原因猜测是数据库参数导致
   获取两个实例中的参数，比对差异；存在差异的参数为：

## 正常实例参数
SQL_MODE        STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

## 问题实例参数
SQL_MODE        PIPES_AS_CONCAT,ANSI_QUOTES,NO_AUTO_VALUE_ON_ZERO,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

经确认：sql_mode在晚上做割接的时候做个修改，添加了PIPES_AS_CONCAT,ANSI_QUOTES,NO_AUTO_VALUE_ON_ZERO 三个参数导致问题的发生

4. 确认为sql_mode 中ANSI_QUOTES导致

官网对sql_mode中该参数说明：

ANSI_QUOTES
Treat " as an identifier quote character (like the quote character) and not as a string quote character. You can still use to quote identifiers with this mode enabled. With ANSI_QUOTES enabled, you cannot use double quotation marks to quote literal strings because they are interpreted as identifiers.

即在开启ANSI_QUOTES参数时，双引号不在作为字符串的引号符而是作为标识符引号字符（同`符号相同）；导致问题sql的条件在解析的时候报错。

sql_mode官方说明：sql_mode