MySQL查询information_schema.columns报错

1、DTS数据同步报错

2、源端用户user1拥有所有database的权限，包括select权限

3、使用user1用户登录源端MySQL，当指定database为database1，select被拒绝

4、从MySQL的物理表文件看，表的.frm和.ibd文件是正常的

5、将报错的表table1备份为table2，删除table1，select information_schema.columns、information_schema.tables可执行且不报错

6、将table2重命名为table1，select information_schema.columns、information_schema.tables再次报一样的错误

7、将table1重命名为table2，select information_schema.columns、information_schema.tables可执行且不报错

8、原因判断

参考： https://bugs.mysql.com/bug.php?id=63527

其他用户也遇到了与MySQL对象相关的information_schema.columns、information_schema.tables的select报错，但是涉及的MySQL对象为view，而我们这里为table。

view可以指定definer等，而table1的创建语法中没有找到这样的字眼。但是推测以某一种方式与definer相关联。

9、辅证

如有以下sql：

T1.limit_user 为account表中查询出来的值

‘9,8,4’

查询sql正确查询结果应为:

不可推荐 ,推销客户,推销客户

实际结果为:

不可推荐

原因：

在mysql中in里面如果是字符串的话，会自动转化成int类型的，内部使用了如下方法： CAST('4,3' AS INT)

导致’4,3‘ 变成了4，所以上述查询sql结果只有第一个。

解决方案

FIND_IN_SET('查询的值（如:1）', '使用逗号隔开的字符串集合，如:（'1,2,3'）')

正确的sql

在使用mysql是在navicat中查询一条简单语句使用了 select * from table 虽然查询成功了,但是报了

[Err] 1055 - Expression #1 of ORDER BY clause is not in GROUP BY clause and contains nonaggregated column 'information_schema.PROFILING.SEQ' which is not functionally dependent on columns in GROUP BY clausethis is incompatible with sql_mode=only_full_group_by 错误.

在MySQL5.7之后，sql_mode中默认存在ONLY_FULL_GROUP_BY，SQL语句未通过ONLY_FULL_GROUP_BY语义检查所以报错。

ONLY_FULL_GROUP_BY模式要求select语句中查询出来的列必须是明确的（其他语句也是一样）。

以SQL语句 select columes from table group by list 为例：columns必须是聚集函数或者在group by后的表达式list中，并且list中必须包含主键，否则也会报错。

insert、update、delete语句都会报错（但不影响SQL语句的执行），因为这三种语句执行之前也会执行查询 *** 作。

以主键为id的表为例：

执行sql语句使sql_mode的模式去掉ONLY_FULL_GROUP_BY,但是重启服务会失效

注意: 在ubuntu 18中的mysql配置文件需要修改 mysqld.cnf , 其他系统的其他mysql版本需要根据特定情况查找需要修改的配置文件,如:windows中一般是 my.ini ,linux中可能是 my.cnf

在[mysqld]下面增加如下内容

验证: 再次在navicat中 select * from table 查询,已没有错误.

---