MySQL query information_schema.columns error

DTS data synchronization error¶

prompt:

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 = ?

permissions¶

The source user user1 has all database permissions, including select permissions

mysql> 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'@'%';

Use user1 to log in to the source MySQL¶

When the database is specified as database1, the select is rejected

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 'py_330106_033_white_list_ext'

1. From the physical table file of MySQL, the .frm and .ibd files of the table are normal

2. Back up the error table table1 to table2, delete table1, and the information_schema.columns and information_schema.tables reported above are executable and no error is reported.

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

3. Rename table2 to table1, and report the same error again for the selection of information_schema.columns and information_schema.tables

mysql > alter table table2 rename name1;

4. Rename table1 to table2, the information_schema.columns and information_schema.tables reported above are executable and no error is reported

possible reason¶

Other users have also encountered the select error of information_schema.columns and information_schema.tables related to MySQL objects, but the MySQL object involved is view, and here is table.
View can specify definer, etc., but such words are not found in the creation grammar of table1. But it is speculated to be related to the definer in a certain way.

Reference: https://bugs.mysql.com/bug.php?id=63527

Auxiliary certificate¶

warnings of information_schema.columns, information_schema.tables

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 |
+---------+------+---------------------------------------------------------------------------------------------------------------------------------------+