免费注册 查看新帖 |

Chinaunix

  平台 论坛 博客 文库
最近访问板块 发新帖
查看: 1557 | 回复: 0

Mysql的临时表truncate操作失败(转) [复制链接]

论坛徽章:
0
发表于 2011-12-19 13:55 |显示全部楼层
今天意外发现Mysql的临时表在进行truncate 操作的时候提示没有drop table的权限而失败,重现步骤:

【步骤一】创建测试用户,授予select,insert,update,delete,create temporary tables 权限
mysql> grant select, insert, update, delete, create temporary tables on test.* to 'hx'@'localhost' identified by 'hx';
Query OK, 0 rows affected (0.00 sec)

【步骤二】使用测试表连接上数据库,创建测试临时表,并插入测试数据
mysql> create temporary table if not exists tempprc (assets_sn varchar(60), ct int, max_de_date varchar(10), new_col int) engine=memory;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into tempprc values (1, 1, 1);
Query OK, 1 row affected (0.00 sec)

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from tempprc;
+-----------+------+-------------+
| assets_sn | ct   | max_de_date |
+-----------+------+-------------+
| 1         |    1 | 1           |
+-----------+------+-------------+
1 row in set (0.00 sec)

【步骤三】截断临时表 tempprc
mysql> truncate table tempprc;
ERROR 1142 (42000): DROP command denied to user 'hx'@'localhost' for table 'tempprc'

mysql> truncate temporary table tempprc;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'table tempprc' at line 1

【步骤四】删除临时表 tempprc
mysql> drop temporary table tempprc;                                                                                                Query OK, 0 rows affected (0.00 sec)


根据以上实验和Mysql的文档关于 truncate 操作的解释:

【参考】http://dev.mysql.com/doc/refman/5.1/en/truncate-table.html

Beginning with MySQL 5.1.32, TRUNCATE TABLE is treated for purposes of binary logging and replication as DROP TABLE followed by CREATE TABLE — that is, as DDL rather than DML. This is due to the fact that, when using InnoDB and other transactional storage engines where the transaction isolation level does not allow for statement-based logging (READ COMMITTED or READ UNCOMMITTED), the statement was not logged and replicated when using STATEMENT or MIXED logging mode. (Bug#36763) However, it is still applied on replication slaves using InnoDB in the manner described previously. 

确认结论:Mysql 对于非INNODB的引擎,truncate的操作都是等价于先 drop table 再create table 来实现的,所以使用在临时表上(engine = memory)使用 truncate 操作的时候会提示没有 drop table 的权限。

其次,对于当前session可以drop temporary table 也能够获得合理的解释:

【参考】http://dev.mysql.com/doc/refman/5.1/en/drop-table.html

The TEMPORARY keyword has the following effects:

  • The statement drops only TEMPORARY tables.

  • The statement does not end an ongoing transaction.

  • No access rights are checked. (A TEMPORARY table is visible only to the session that created it, so no check is necessary.)

Using TEMPORARY is a good way to ensure that you do not accidentally drop a non-TEMPORARY table.

Mysql 在删除临时表的时候,不进行任何权限校验,因为临时表是且仅仅是当前session可见,所以校验是不必要的。

【参考资料】

关于Mysql truncate 临时表的讨论: http://bugs.mysql.com/bug.php?id=47576

您需要登录后才可以回帖 登录 | 注册

本版积分规则 发表回复

  

北京盛拓优讯信息技术有限公司. 版权所有 京ICP备16024965号-6 北京市公安局海淀分局网监中心备案编号:11010802020122 niuxiaotong@pcpop.com 17352615567
未成年举报专区
中国互联网协会会员  联系我们:huangweiwei@itpub.net
感谢所有关心和支持过ChinaUnix的朋友们 转载本站内容请注明原作者名及出处

清除 Cookies - ChinaUnix - Archiver - WAP - TOP