| Bug #17704 | Triggers: MAX, Insert select with several rows, strange error | ||
|---|---|---|---|
| Submitted: | 24 Feb 2006 18:27 | Modified: | 17 Apr 2006 23:19 |
| Reporter: | Matthias Leich | ||
| Status: | Duplicate | ||
| Category: | Server | Severity: | S2 (Serious) |
| Version: | 5.0 | OS: | |
| Assigned to: | Paul DuBois | Target Version: | |
[24 Feb 2006 18:28]
Matthias Leich
testscript
Attachment: ml1104.test (application/test, text), 1.10 KiB.
[4 Mar 2006 14:41]
Konstantin Osipov
Stefan, I believe this is a documentation issue: there is an email to docs-requests with Subject: Re: Missing limitation of triggers/stored functions describing the exact problem. Please set to 'Not a bug' if this is fixed already. Matthias: a trigger can not access (not even read data) the table it's defined for.
[17 Apr 2006 23:19]
Dmitri Lenev
Hi, Matthias and Paul! I can't repeat behavior that you have described using the latest tree. With ltaest server error about table 't1' being marked as crashed is not emitted and code that you have provided works as expected. After closer look at your test case I am pretty sure that this bug is yet another manifestation of bug#17764 "trigger crashes myisam table" which is already fixed in 5.0.20. So I am marking this bug as duplicate.
Description: CREATE TABLE t1 (f1 BIGINT, f2 BIGINT, f3 BIGINT); CREATE TABLE t2 (f1 BIGINT); INSERT INTO t1 VALUES(0,0,0); INSERT INTO t2 VALUES(5),(6),(7); # Basically the trigger recalculates values to be inserted CREATE TRIGGER trg BEFORE INSERT ON t1 FOR EACH ROW BEGIN SET @counter = 1; SET @my_max1 = 0, @my_max2 = 0; # The problem disappears, when the next line with MAX is deleted. SELECT MAX(f1), MAX(f2) INTO @my_max1,@my_max2 FROM t1; SET new.f1 = @my_max1 + @counter, new.f2 = @my_max2 + @counter; SET @counter = @counter + 1; END| SELECT * FROM t1; f1 f2 f3 0 0 0 # harmless - INSERT SELECT one record INSERT INTO t1 (f1,f2,f3) SELECT f1,f1,f1 FROM t2 WHERE f1 = 5; SELECT * FROM t1; f1 f2 f3 0 0 0 1 1 5 # bad - INSERT SELECT two records INSERT INTO t1 (f1,f2,f3) SELECT f1,f1,f1 FROM t2 WHERE f1 > 5; ERROR HY000: Table 't1' is marked as crashed and should be repaired SELECT * FROM t1; f1 f2 f3 0 0 0 1 1 5 2 2 6 # harmless (Why ?) - INSERT two records INSERT INTO t1 (f1,f2,f3) VALUES(8,8,8),(9,9,9); SELECT * FROM t1; f1 f2 f3 0 0 0 1 1 5 2 2 6 3 3 8 4 4 9 My environment: - Intel PC with Linux(SuSE 9.3) - MySQL compiled from source Version 5.0 last ChangeSet@1.2079.1.1, 2006-02-24 Version 5.1 last ChangeSet@1.2166, 2006-02-23 How to repeat: Please execute the statements above or use my attached testscript ml1104.test copy it to mysql-test/t echo "Dummy" > r/ml1104.result # Produce a dummy file with # expected results ./mysql-test-run ml1104