mysql 双引号报错_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.

问题排查

从监控脚本中获取到监控查询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");

排查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

确认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 三个参数导致问题的发生

确认为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