为什么这个MySQL触发器会导致堆栈溢出?

前端之家收集整理的这篇文章主要介绍了为什么这个MySQL触发器会导致堆栈溢出?前端之家小编觉得挺不错的,现在分享给大家,也给大家做个参考。

我认为有人试图模拟第二个auto_increment值.刚升级MySQL 5.5.9

CREATE TABLE `job_title` (
  `job_id` int(11) NOT NULL AUTO_INCREMENT,`position_id` int(11) DEFAULT NULL,`title` varchar(255) COLLATE latin1_general_cs NOT NULL,`selectable` tinyint(4) NOT NULL DEFAULT '0',PRIMARY KEY (`job_id`),UNIQUE KEY `title` (`title`)
) ENGINE=InnoDB;

create trigger job_position_trigger
  before insert on job_title for each row
 begin
   if new.position_id is null then 
     set @position = (select max(position_id)+1 from job_title);
     set new.position_id = @position;
   end if;
 end

错误:线程堆栈溢出:使用9024字节的131072字节堆栈,需要128000字节.使用’MysqLd –thread_stack =#’指定一个
 更大的堆栈.’在查询.默认数据库:’mydb’.查询:’将ignore插入job_title(标题)值(‘Morning Show Personality’)

最佳答案
我今天遇到了同样的问题,每次触发都会导致堆栈溢出.原来我的Zend社区服务器安装附带了一个默认的my.cnf文件,其中thread_stack大小设置为128K,这导致每个线程中可用于堆栈的131072字节:

MysqL> show variables where `Variable_name` = 'thread_stack';
+---------------+--------+
| Variable_name | Value  |
+---------------+--------+
| thread_stack  | 131072 |
+---------------+--------+

所以我在/usr/local/zend/MysqL/data/my.cnf中注释了这一行,重新启动了MysqL守护进程,瞧! default 192K是

MysqL> show variables where `Variable_name` = 'thread_stack';
+---------------+--------+
| Variable_name | Value  |
+---------------+--------+
| thread_stack  | 196608 |
+---------------+--------+

现在你的桌子& tchester的触发器工作完美:)(尽管请注意分隔符)

MysqL> CREATE TABLE `job_title` (
    ->   `job_id` int(11) NOT NULL AUTO_INCREMENT,->   `position_id` int(11) DEFAULT NULL,->   `title` varchar(255) COLLATE latin1_general_cs NOT NULL,->   `selectable` tinyint(4) NOT NULL DEFAULT '0',->   PRIMARY KEY (`job_id`),->   UNIQUE KEY `title` (`title`)
    -> ) ENGINE=InnoDB;
Query OK,0 rows affected (0.14 sec)

MysqL> DELIMITER &&
MysqL> create trigger job_position_trigger   
    ->   before insert on job_title for each row  
    -> begin    
    ->     if new.position_id is null then       
    ->        set @position = (select max(position_id)+1 from job_title);
    ->        if @position is null then set @position = 1; end if;
    ->        set new.position_id = @position;    
    ->     end if;  
    -> end; 
    -> &&
Query OK,0 rows affected (0.29 sec)

MysqL> DELIMITER ;
MysqL> insert into job_title (title,selectable) values ("test",1);
Query OK,1 row affected (0.00 sec)

MysqL> insert into job_title (title,selectable) values ("test2",3);
Query OK,1 row affected (0.00 sec)

MysqL> select * from job_title;
+--------+-------------+-------+------------+
| job_id | position_id | title | selectable |
+--------+-------------+-------+------------+
|      1 |           1 | test  |          1 |
|      2 |           2 | test2 |          3 |
+--------+-------------+-------+------------+
2 rows in set (0.00 sec)

你得到的错误,131072字节堆栈使用的9024字节和所需的128000字节是有意义的:9024 128000> 131072.

原文链接:https://www.f2er.com/mysql/433588.html

猜你在找的MySQL相关文章