| Bug #8773 | Triggers: Error msg should read Trigger instead Func, when CREATE TRIGGER fails | ||
|---|---|---|---|
| Submitted: | 24 Feb 2005 10:32 | Modified: | 10 Jul 2005 6:22 |
| Reporter: | Disha | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server | Severity: | S3 (Non-critical) |
| Version: | 5.0.2 | OS: | Any (Any) |
| Assigned to: | Dmitry Lenev | CPU Architecture: | Any |
[24 Feb 2005 10:45]
MySQL Verification Team
I can reproduce it neither on Windows nor on my Linux boxes. I always get syntax error: ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '9999 where f1=1111' at line 1
[24 Feb 2005 11:16]
Disha
Hi Victoria, A change to the step 4 in the 'steps to repeat' section. Please use the following instead: 4) CREATE TRIGGER trg1 BEFORE INSERT ON T1 FOR EACH ROW UPDATE t1 set f2=9999 where f1=1111; This will repro the issue. thanks.
[24 Feb 2005 12:59]
MySQL Verification Team
I got the same error with 5.0.2, but I tested this report also with latest 5.0 BK tree and didn't get any error. But if I try to insert data, MySQL server crashes.
mysql> CREATE TABLE t1 (f1 INT, f2 INT);
Query OK, 0 rows affected (0.06 sec)
mysql> CREATE TRIGGER trg1 BEFORE INSERT ON t1 FOR EACH ROW UPDATE t1 set f2=9999 where f1=1111;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into t1 values(1111,5);
Query OK, 0 rows affected (0.03 sec)
Rows matched: 0 Changed: 0 Warnings: 0
mysql>
Number of processes running now: 0
050224 15:52:55 mysqld restarted
Program received signal SIGSEGV, Segmentation fault.
[Switching to Thread 180236 (LWP 2561)]
0x08213c58 in ha_myisam::write_row (this=0x8c2c0b8, buf=0x8c2c198 "ùW\004") at ha_myisam.cc:252
252 statistic_increment(table->in_use->status_var.ha_write_count,&LOCK_status);
(gdb) Quit
(gdb) bt
#0 0x08213c58 in ha_myisam::write_row (this=0x8c2c0b8, buf=0x8c2c198 "ùW\004")
at ha_myisam.cc:252
#1 0x081cf877 in write_record (thd=0x8c2f298, table=0x8c2c750, info=0xbe3ff27c)
at sql_insert.cc:856
#2 0x081cecb3 in mysql_insert (thd=0x8c2f298, table_list=0x8c4fef0, fields=@0x8c2f708,
values_list=@0x8c2f738, update_fields=@0x8c2f728, update_values=@0x8c2f718, duplic=DUP_ERROR,
ignore=false) at sql_insert.cc:386
#3 0x0818c6b8 in mysql_execute_command (thd=0x8c2f298) at sql_parse.cc:3215
#4 0x081903a7 in mysql_parse (thd=0x8c2f298, inBuf=0x8c4fe80 "insert into t1 values(1111, 5)",
length=30) at sql_parse.cc:5173
#5 0x08189207 in dispatch_command (command=COM_QUERY, thd=0x8c2f298, packet=0x8c47e51 "",
packet_length=31) at sql_parse.cc:1644
#6 0x08192ed3 in do_command (thd=0x8c2f298) at sql_parse.cc:1451
#7 0x081881b0 in handle_one_connection (arg=0x8c2f298) at sql_parse.cc:1107
#8 0xb7e4614b in pthread_start_thread () from /lib/libpthread.so.0
#9 0xb7e461df in pthread_start_thread_event () from /lib/libpthread.so.0
#10 0xb7d7950a in clone () from /lib/libc.so.6
[10 Jul 2005 6:22]
Dmitry Lenev
Hi! Starting from version 5.0.3 server won't crash in this situation. Instead trigger will emit following warning: "Error 1100: Table 'some_table' was not locked with LOCK TABLES" (this problem is reported as error and will cause failure of statement invoking trigger starting from 5.0.7). And starting from 5.0.10 thanks to fix for bug #8406 it is perfectly legal to access tables from triggers. mysql> CREATE TABLE t1 (f1 INT, f2 INT); Query OK, 0 rows affected (0.01 sec) mysql> CREATE TRIGGER trg1 BEFORE INSERT ON t1 FOR EACH ROW UPDATE t1 set -> f2=9999 where f1=1111; Query OK, 0 rows affected (0.01 sec) mysql> insert into t1 values(1111,5); Query OK, 1 row affected (0.02 sec) mysql> select * from t1; +------+------+ | f1 | f2 | +------+------+ | 1111 | 5 | +------+------+ 1 row in set (0.01 sec) mysql> insert into t1 values(1111,6); Query OK, 1 row affected (0.01 sec) mysql> select * from t1; +------+------+ | f1 | f2 | +------+------+ | 1111 | 9999 | | 1111 | 6 | +------+------+ So I'm closing this bug-report as fixed in 5.0.10.
Description: Error message should read Trigger instead Function, when CREATE TRIGGER fails. An error displayed when TRIGGER creation fails because of UPDATE statement in Trigger action. This is as expected. However, displayed error reads '...not allowed in a FUNCTION' instead of '...not allowed in a TRIGGER'. How to repeat: 1. Create a database test1; 2. Use test1; 3. Create table 't1' with fields 'f1' and 'f2' as: CREATE TABLE t1 (f1 INT, f2 INT); 4. Try to Create an UPDATE trigger say 'trg1' on 't1' as subject: CREATE TRIGGER t1.trg1 BEFORE INSERT ON T1 FOR EACH ROW UPDATE f2=9999 where f1=1111; 5. Observe the error displayed for trigger creation failure. ACTUAL RESULT: The error displayed reads: "Statements like SELECT, INSERT, UPDATE (and others) are not allowed in a FUNCTION". Suggested fix: The displayed error should read: "Statements like SELECT, INSERT, UPDATE (and others) are not allowed in a TRIGGER"