MySQL查询information_schema.column

1、DTS数据同步报错

提醒： DTS-1020013 Get db tables error,err msg:SELECT command denied to user ''@'%' for column 'C43' in table 'table1', sqls: select table_schema,table_name,engine,row_format,table_collation,table_comment,auto_increment from information_schema.tables WHERE table_schema = ? AND table_type <>'VIEW' ,
select * from information_schema.columns where table_schema = ? and table_name = ?

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

show grants for user1;

GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, INDEX, ALTER, SUPER, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, EVENT, TRIGGER ON . TO 'user1'@'%';

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

select * from information_schema.columns where table_schema='database1';
select * from information_schema.tables where table_schema='database1';

ERROR 1143 (42000): SELECT command denied to user ''@'%' for column 'C43' in table 'table1'

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

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

create table table2 as select * from table1;
drop table table1;

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

alter table table2 rename name1;

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、辅证

select information_schema.columns、information_schema.tables 的warnings
show warnings;
+---------+------+---------------------------------------------------------------------------------------------------------------------------------------+
| Level   | Code | Message                                                                                                                               |
+---------+------+---------------------------------------------------------------------------------------------------------------------------------------+
| Note    | 1449 | The user specified as a definer ('user_xxx'@'%') does not exist                                                                       |
| Warning | 1356 | View 'database1.view1' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them |
+---------+------+---------------------------------------------------------------------------------------------------------------------------------------+