问题描述
我认为有人试图模拟第二个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
错误:Thread stack overrun: 9024 bytes used of a 131072 byte stack, and 128000 bytes needed. Use 'mysqld --thread_stack=#' to specify a bigger stack.' on query. Default database: 'mydb'. Query: 'insert ignore into job_title (title) values ('Morning Show Personality')
The error: Thread stack overrun: 9024 bytes used of a 131072 byte stack, and 128000 bytes needed. Use 'mysqld --thread_stack=#' to specify a bigger stack.' on query. Default database: 'mydb'. Query: 'insert ignore into job_title (title) values ('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)
您所得到的错误是有意义的:9024 + 128000>131072.该错误使用了131072字节堆栈的9024字节,需要128000字节.
The error you got, 9024 bytes used of a 131072 byte stack, and 128000 bytes needed, makes sense: 9024 + 128000 > 131072.
这篇关于为什么此MySQL触发器会导致堆栈溢出?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!