MYSQL中的CREATE TEMPORARY TABLE

原创 2011年01月07日 18:07:00

记录一下今天的一个BUG FIXING。早上收到一个BUG,说有一个到模块A的调用B,多执行几次以后就会出错。错误信息显示SQL ERROR。因为CDC SBE就我最近改过模块A的代码,就把BUG塞给我了。

Trouble Shooting的过程:

  • 先检查error log, 没有发现明显问题。于是我把debug log打开后重起模块A,然后手动执行那个调用B,于是我从debug log中拿到了调用B所对应的函数名称。
  • 拿到模块A中的函数名称,我搜索源代码,检查最近有没有人修改过这部分代码。发现没有人改过。查看源代码(当然debug log中也有记录)拿到函数调用B所invoke的store procedure call — CALL SP_arrowpig(30, 1, @retCode); 检查最近有没有人修改了和SP_arrowpig有关的SQL,发现也没有。所以我把这个BUG转嫁给别的替罪羊的想法告吹。(因为如果我发现有谁最近修改了跟出错部分相关的代码的话,我就可以把他拖进来跟我一起查)
  • 现在只能靠自己了。这时候anusheel同学忽然跳进来,说他已经把那个出错的store procedure在command下运行了好几次,头几次是好的,多运行几次就出错了,怀疑是 mysql本身的bug,并且说以前在production环境下也碰到过,他们解决问题的方法是重新启动mysql服务进程。
  • 我也执行了一下,确实像anusheel同学说的那样子,出错信息是这样的:

mysql> CALL SP_arrowpig(30,0,@retCode); select @retCode;
+————–+—————-+———————+
| ErrorPattern   | ErrorName         | ErrorStr                  |
+————–+—————-+———————+
| SQL Error       | SP_arrowpig      | When Doing Task 1   |
+————–+—————————————+
1 row in set (0.01 sec)

Query OK, 0 rows affected, 2 warnings (0.01 sec)

+———-+
| @retCode |
+———-+
| 1            |
+———-+
1 row in set (0.00 sec)

@retCode=1,确实是出错了,我们的编程惯例是retCode=0表示成功的。然后我开始看源代码:

DROP PROCEDURE IF EXISTS SP_arrowpig//
CREATE PROCEDURE SP_arrowpig(IN id INT, IN mode INT, OUT status INT)

BEGIN
    DECLARE current_id INT;
    DECLARE err_str CHAR(255);
    DECLARE EXIT HANDLER FOR SQLEXCEPTION BEGIN
             SET status = 1;
             CALL SP_errmsg("SQL Error","SP_arrowpig",err_str);  –和上面的出错信息匹配上了
                                                                  END;
    SET status=0;
    SET current_id=id;
    arrowpig_label: LOOP
    BEGIN
        IF mode=1 THEN
            SET err_str="When Doing Task 1";  — 出错信息
            CREATE TEMPORARY TABLE IF NOT EXISTS sp_output_tmp ENGINE = MEMORY SELECT …from … where ID=current_id;
        ELSE
            SET err_str="When Doing Task 2";  — 出错信息
            CREATE TEMPORARY TABLE IF NOT EXISTS sp_output_tmp ENGINE = MEMORY SELECT ..from … where ID=current_id;
        END IF;

        SET @parent_id=NULL;
        SELECT `Parent` INTO @parent_id FROM … WHERE `Uid`=current_id; — 找到父亲节点后继续循环
        SET current_id=@parent_id;
        IF ISNULL(current_id) THEN LEAVE arrowpig_label; END IF;
    END;
    END LOOP;  –一个Loop循环
END //

结合源代码和出错信息,我有了以下结论:

  • 程序出错是因为遇到了SQL Exception而产生的。
  • 出错语句是mode=1中的那个create temporary table if not exists…
  • 这里是个Loop循环,为了确定出错的时候current_id的值,我在每次循环进来的地方放了一条select current_id;然后我得到在出错的时候current_id=31。
  • 因为SQL Exception没有给我更多的信息,在我得到了current_id=30以后,我手工运行了下面的SQL:

mysql> create temporary table if not exists sp_output_tmp engine=memory select … from … where ID=31;

ERROR 1114 (HY000): The table ‘sp_output_tmp’ is full

  • 这个时候Alex上线了,我让他登录到同样的server上看看,但是Alex跟我说运行同样的sp,他没有看到任何错误,我晕!
  • 我很奇怪,sp_output_tmp怎么会full的呢,Alex跟我运行同样的东东,怎么就没有错呢?我查了一下mysql的文档,发现create temporary table if not exists … engine=memory select … 有以下特点:
    1. 跟在create table if not exists sp_output_tmp后面的select语句实际上的效果就是往sp_output_tmp里面插入记录。如果没有sp_output_tmp表,就创建一个,如果该表已经存在,就插入。我用desc sp_output_tmp看了一下,没有任何约束。而且在SP_arrowpig中我没有看到任何drop或者truncate的操作,我不放心,grep了整个SQL文件,都没有看到drop/truncate的操作。这就是说,每次调用SP_arrowpig,sp_output_tmp表都会长大,直到full后报错。
    2. temporary表只对当前连接有效,也就是说当我log off的时候,从我log in的那刻起所创建的任何temporary table都会报废。temporary table是connection的私有财产。所以说当Alex log in后,他看到的sp_output_tmp和我看到的不是同一个实例,虽然表名字是一样的。于是我要求Alex再多调用几次SP_arrowpig后,他也看到了同样的错误了。

最后的解决其实很简单:我在SP_arrowpig中进入循环之前,放了一条:

DROP TEMPORARY TABLE IF EXISTS sp_output_tmp;

我们就再也没有看到那条错误了,这里要说明一点,我为什么使用drop而不是 truncate呢?原因是在mode=1和<>1的时候,代码中select的字段是不一样的。truncate的时候不会更改表结构,为了能让参数mode在不同的值得时候都能正常工作,我使用了drop。

 

 

mysql (create temporary table table_name )临时表创建

一般在数据量比较大的查询中,用in()等查询条件,会严重影响查询效率。这时可以用 create temporary table table_name select id,name from tabl...
  • u010265663
  • u010265663
  • 2015年12月07日 20:55
  • 2578

Mysql temporary table

Internal Temporary Table   当我们的SQL语句相对“复杂”,需要MySQL思考更多时,就会出发自身使用internal temp table,比如:语句中有order by...
  • gua___gua
  • gua___gua
  • 2015年07月28日 16:16
  • 1053

Spark SQL之External DataSource外部数据源(一)示例

一、Spark SQL External DataSource简介  随着Spark1.2的发布,Spark SQL开始正式支持外部数据源。Spark SQL开放了一系列接入外部数据源的接口,来让开发...
  • u014388509
  • u014388509
  • 2014年12月22日 01:12
  • 7242

Spark-SparkSQL深入学习系列十一(转自OopsOutOfMemory)

上周Spark1.2刚发布,周末在家没事,把这个特性给了解一下,顺便分析下源码,看一看这个特性是如何设计及实现的。     /** Spark SQL源码分析系列文章*/   (Ps: E...
  • youdianjinjin
  • youdianjinjin
  • 2016年05月11日 19:38
  • 561

MySQL数据库表的基本操作——创建表CREATE TABLE

在操作数据库表之前要先选择数据库,USE
  • u011701678
  • u011701678
  • 2013年08月31日 13:14
  • 28808

db2 常用操作指令

db2 cmd命令操作   1、 打开命令行窗口    #db2cmd  2、 打开控制中心   www.2cto.com     # db2cmd db2cc  3、 打开命令编辑器...
  • qq_33611995
  • qq_33611995
  • 2017年03月31日 09:06
  • 83

慎用create table as select,一定要注意默认值的问题---大一临时表方法

摘要: 1、再做一些数据迁移时候,很多人会使用create table  as select * from table where id=-1的方式来年建立一摸一样的表,但是这样做有个很大的弊端,不...
  • abc15159939239
  • abc15159939239
  • 2014年09月12日 16:45
  • 1675

Esxi中CentOS7 扩展磁盘容量

服务器是搭建在Esxi之上,使用Mysql时忽然报错,数据连接不上,重启服务也出现问题。使用命令journalctl -xe查看一下日志,发现启动服务时报错——: mariadb.service fa...
  • felcon
  • felcon
  • 2016年10月26日 10:25
  • 2619

MySQL Create Table语句的应用与语法

以下的文章主要介绍的是MySQL Create Table语句的实际用法与MySQL Create Table语句的基本语法的介绍,如果你对其具体的操作步骤感兴趣的话,你就可以对以下的文章来一个“大阅...
  • weiyuefei
  • weiyuefei
  • 2016年04月14日 20:26
  • 511

Hive中分组取前N个值

背景 假设有一个学生各门课的成绩的表单,应用hive取出每科成绩前100名的学生成绩。 这个就是典型在分组取Top N的需求。   解决思路 对于取出每科成绩前100名的...
  • sumo2010
  • sumo2010
  • 2016年01月03日 11:44
  • 129
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:MYSQL中的CREATE TEMPORARY TABLE
举报原因:
原因补充:

(最多只允许输入30个字)