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

2022-01-01 00:00:00 sql mysql triggers

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

I think someone was trying to simulate having a second auto_increment value. Just upgraded to 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

错误:线程堆栈溢出:131072 字节堆栈中使用了 9024 字节，需要 128000 字节.使用 'mysqld --thread_stack=#' 指定一个更大的堆栈.在查询.默认数据库:'mydb'.查询:'insert ignore into job_title (title) 值 ('Morning Show Personality')

推荐答案

我今天遇到了同样的问题，每个触发器都会导致堆栈溢出.结果我的 Zend Community Server 安装带有一个默认的 my.cnf 文件，其中 thread_stack 大小设置为 128K，这导致每个线程中的堆栈有 131072 字节可用:

I ran into the same problem today, every trigger causing a stack overrun. Turned out my Zend Community Server installation comes with a default my.cnf file in which the thread_stack size was set to 128K, which resulted in 131072 bytes available for the stack in each thread:

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

所以我注释掉了/usr/local/zend/mysql/data/my.cnf 中的那一行，重新启动了 mysql 守护进程，瞧！默认 192K 是

So I commented out the line in /usr/local/zend/mysql/data/my.cnf, restarted the mysql daemon, and voila! The default 192K is 

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

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

Now your table & tchester's trigger work perfectly :) (do note the delimiter though)

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.

The error you got, 9024 bytes used of a 131072 byte stack, and 128000 bytes needed, makes sense: 9024 + 128000 > 131072.

相关文章