仅当我们尝试导出到 csv 时，mysql 查询才会失败

Question

背景信息:

我正在尝试将各种表中的数据转储到一个 csv 文件中。当我自己运行实际查询时(没有写入 csv 文件的命令)，我得到的结果集没有问题。

问题:

当我修改查询以写入 csv 文件时，出现错误:

ERROR 1356 (HY000): View 'racktables.RackObject' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them

代码

这是执行选择的代码:

SELECT RackObject.id, RackObject.name, RackObject.label, RackObject.asset_no, RackObject.objtype_id, 
   (SELECT min(rack_id) FROM RackSpace WHERE object_id = RackObject.id) as rack_id, 
   (SELECT parent_entity_id AS rack_id FROM EntityLink WHERE child_entity_type='object' AND child_entity_id = RackObject.id AND parent_entity_type = 'rack' ORDER BY rack_id ASC LIMIT 1) as rack_id_2, 
   (SELECT parent_entity_id FROM EntityLink WHERE child_entity_type='object' AND child_entity_id = RackObject.id AND parent_entity_type = 'object' ORDER BY parent_entity_id ASC LIMIT 1) as container_id, 
   (SELECT name FROM RackObject WHERE id = container_id) as container_name, 
   (SELECT objtype_id FROM RackObject WHERE id = container_id) as container_objtype_id, RackObject.has_problems, RackObject.comment, 
   (SELECT COUNT(*) FROM Port WHERE object_id = RackObject.id) as nports,    
   (SELECT domain_id FROM VLANSwitch WHERE object_id = id LIMIT 1) as 8021q_domain_id, 
   (SELECT template_id FROM VLANSwitch WHERE object_id = id LIMIT 1) as 8021q_template_id 
 FROM RackObject 
 ORDER BY RackObject.name;

下面是尝试导出到 csv 的查询:

  mysql> SELECT RackObject.id, RackObject.name, RackObject.label, RackObject.asset_no, RackObject.objtype_id, 
 (SELECT min(rack_id) FROM RackSpace WHERE object_id = RackObject.id) as rack_id, 
 (SELECT parent_entity_id AS rack_id FROM EntityLink WHERE child_entity_type='object' AND child_entity_id = RackObject.id AND parent_entity_type = 'rack' ORDER BY rack_id ASC LIMIT 1) as rack_id_2, 
 (SELECT parent_entity_id FROM EntityLink WHERE child_entity_type='object' AND child_entity_id = RackObject.id AND parent_entity_type = 'object' ORDER BY parent_entity_id ASC LIMIT 1) as container_id, 
 (SELECT name FROM RackObject WHERE id = container_id) as container_name,  
 (SELECT objtype_id FROM RackObject WHERE id = container_id) as container_objtype_id, RackObject.has_problems, RackObject.comment, 
 (SELECT COUNT(*) FROM Port WHERE object_id = RackObject.id) as nports, 
 (SELECT domain_id FROM VLANSwitch WHERE object_id = id LIMIT 1) as 8021q_domain_id, 
 (SELECT template_id FROM VLANSwitch WHERE object_id = id LIMIT 1) as 8021q_template_id 
 INTO OUTFILE '/var/log/objects.csv' 
 FIELDS ENCLOSED BY '"' 
 TERMINATED BY ';' 
 ESCAPED BY '"' 
 LINES TERMINATED BY '\r\n' 
 FROM RackObject 
 ORDER BY RackObject.name;
ERROR 1356 (HY000): View 'racktables.RackObject' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
mysql> 

到目前为止我尝试了什么:

我用谷歌搜索了错误消息，但到目前为止，我还没有找到与我的问题相关的任何内容。

我目前也在研究 csv 转储选项...一个一个地删除以查看是否是我正在使用的特定指令导致了问题。

最后，我打算一个一个地删除，各种子选择以查看它是否是表级别的东西......但这对我来说似乎很奇怪，因为我可以运行查询来筛选没有问题。

我也试过这个查询......效果很好:

mysql> SELECT * INTO OUTFILE '/var/log/objects.csv' FIELDS ENCLOSED BY '"' TERMINATED BY ';' ESCAPED BY '"' LINES TERMINATED BY '\r\n' FROM Object;
Query OK, 20079 rows affected (0.08 sec)

mysql> 

如有任何建议，我们将不胜感激。谢谢。

编辑 1

这是 racktables.RackObject 的样子:

mysql> desc racktables.RackObject;
+--------------+------------------+------+-----+---------+-------+
| Field        | Type             | Null | Key | Default | Extra |
+--------------+------------------+------+-----+---------+-------+
| id           | int(10) unsigned | NO   |     | 0       |       |
| name         | char(255)        | YES  |     | NULL    |       |
| label        | char(255)        | YES  |     | NULL    |       |
| objtype_id   | int(10) unsigned | NO   |     | 1       |       |
| asset_no     | char(64)         | YES  |     | NULL    |       |
| has_problems | enum('yes','no') | NO   |     | no      |       |
| comment      | text             | YES  |     | NULL    |       |
+--------------+------------------+------+-----+---------+-------+
7 rows in set (0.00 sec)

mysql> 

Answer 1

运行简化场景后:

SELECT *
INTO OUTFILE '/var/log/objects.csv'
FIELDS ENCLOSED BY '"'
TERMINATED BY ';'
ESCAPED BY '"'
LINES TERMINATED BY '\r\n'
FROM RackObject
ORDER BY RackObject.name; 

事实证明，在 MySQL 中无法将数据从 View 导出到输出文件。查询必须重写为没有 View 的版本。