1、DTS數(shù)據(jù)同步報(bào)錯(cuò)
提醒： 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_incrementfrom information_schema.tables WHERE table_schema = ? AND table_type <>'VIEW' ,
select * from information_schema.columns where table_schema = ? and table_name = ?
2歧胁、源端用戶(hù)user1擁有所有database的權(quán)限港令，包括select權(quán)限
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用戶(hù)登錄源端MySQL，當(dāng)指定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、將報(bào)錯(cuò)的表table1備份為table2偶垮，刪除table1张咳，select information_schema.columns帝洪、information_schema.tables可執(zhí)行且不報(bào)錯(cuò)
create table table2 as select * from table1;
drop table table1;
6、將table2重命名為table1脚猾，select information_schema.columns葱峡、information_schema.tables再次報(bào)一樣的錯(cuò)誤
alter table table2 rename name1;
7、將table1重命名為table2龙助，select information_schema.columns砰奕、information_schema.tables可執(zhí)行且不報(bào)錯(cuò)
8、原因判斷
參考：https://bugs.mysql.com/bug.php?id=63527
其他用戶(hù)也遇到了與MySQL對(duì)象相關(guān)的information_schema.columns提鸟、information_schema.tables的select報(bào)錯(cuò)军援，但是涉及的MySQL對(duì)象為view，而我們這里為table称勋。
view可以指定definer等胸哥，而table1的創(chuàng)建語(yǔ)法中沒(méi)有找到這樣的字眼。但是推測(cè)以某一種方式與definer相關(guān)聯(lián)赡鲜。
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 |
+---------+------+---------------------------------------------------------------------------------------------------------------------------------------+
