MySQL Bugs: #25411: trigger code truncated

Bug #25411	trigger code truncated
Submitted:	4 Jan 2007 11:56	Modified:	6 Jul 2007 19:16
Reporter:	Stefan Pasel	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Parser	Severity:	S2 (Serious)
Version:	5.0.34-BK, 5.0.30-max-log	OS:	Linux (Linux, 2.6.18-1.2257.fc5 (x86_64))
Assigned to:	Marc ALFF	CPU Architecture:	Any
Tags:	crash, rt_q1_2007, trigger

Description:
I wanted to create a trigger to upate T2 for each row inserted on T1 (Two identical tables). I was not sure if INSERT INTO .. SELECT would work so i gave it a try:

CREATE DEFINER=<user> TRIGGER `testtrigger` AFTER INSERT ON `T1` FOR EACH ROW INSERT INTO T2 SELECT NEW.*

The syntax is probably not supported (therefore i see the bug non-critical) but the trigger was created anyway. (In the .TRG file the '*' was missing though).

Afterwards any action on the table (SELECT, SHOW STATUS, CHECK, REPAIR - does not matter) resulted in a warning "INCORRECT QUERY SYNTAX". 

Trying to view all triggers with 

"SELECT TRIGGER_NAME, EVENT_MANIPULATION, EVENT_OBJECT_TABLE, ACTION_STATEMENT 
    FROM INFORMATION_SCHEMA.TRIGGERS 
    WHERE TRIGGER_SCHEMA='dbname';" 

failed also.

All messages from the mysql errlog read:

Version: '5.0.30-max-log'  socket: '/var/lib/mysql/mysql.sock'  port: 3306  MySQL Community Edition - Experimental (GPL)
mysqld-max: my_new.cc:51: int __cxa_pure_virtual(): Assertion `"Pure virtual method called." == "Aborted"' failed.
mysqld got signal 6;
This could be because you hit a bug. It is also possible that this binary
or one of the libraries it was linked against is corrupt, improperly built,
or misconfigured. This error can also be caused by malfunctioning hardware.
We will try our best to scrape up some info that will hopefully help diagnose
the problem, but since we have already crashed, something is definitely wrong
and this may fail.

key_buffer_size=134217728
read_buffer_size=2093056
max_used_connections=19
max_connections=400
threads_connected=12
It is possible that mysqld could use up to
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = 4225468 K
bytes of memory
Hope that's ok; if not, decrease some variables in the equation.

thd=0x2aaab9b8b3d0
Attempting backtrace. You can use the following information to find out
where mysqld died. If you see no messages after this, something went
terribly wrong...
Cannot determine thread, fp=0x45160078, backtrace may not be correct.
Bogus stack limit or frame pointer, fp=0x45160078, stack_bottom=0x45160000, thread_stack=262144, aborting backtrace.
Trying to get some variables.
Some pointers may be invalid and cause the dump to abort...
thd->query at 0x2aaab9b91520  is invalid pointer
thd->thread_id=1359
The manual page at http://www.mysql.com/doc/en/Crashing.html contains
information that should help you find out what is causing the crash.

Number of processes running now: 0

How to repeat:

Create a Trigger on whatever table with:

CREATE DEFINER=<user> TRIGGER `testtrigger` AFTER INSERT ON `T1` FOR EACH ROW INSERT INTO T2 SELECT NEW.*

Afterwards do SELECT * FROM T1 or any other action described above.

Suggested fix:
Check Syntax before applying and storing the trigger.

Workaround: I manually deleted the *.TRN and .TRG files representing the trigger to get my system back to normal.

Thank you for a bug report. Verified just as described, with 5.0.34-BK on Linux. I had not got any crashed (as I have -debug version, maybe), but the problem is still obvious:

openxs@suse:~/dbs/5.0> bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.0.34-debug Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> drop table t1;
Query OK, 0 rows affected (0.00 sec)

mysql> drop table t2;
Query OK, 0 rows affected (0.01 sec)

mysql> create table t1 (c1 int);
Query OK, 0 rows affected (0.01 sec)

mysql> create table t2 (c1 int);
Query OK, 0 rows affected (0.01 sec)

mysql> create trigger testtrigger after insert on t1 for each row
    -> insert into t2 select new.*;
Query OK, 0 rows affected (0.01 sec)

mysql> select * from t1;
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 '' at
line 2
mysql> show create table t1;
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 '' at
line 2
mysql> show create table t2;
+-------+-----------------------------------------------------------------------
-----------------+
| Table | Create Table
                 |
+-------+-----------------------------------------------------------------------
-----------------+
| t2    | CREATE TABLE `t2` (
  `c1` int(11) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
+-------+-----------------------------------------------------------------------
-----------------+
1 row in set (0.00 sec)

mysql> insert into t1 values(1);
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 '' at
line 2
mysql> insert into t2 values(1);
Query OK, 1 row affected (0.00 sec)

You can not execute any SQL statement with t1. This is a serious bug.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/17858

ChangeSet@1.2322, 2007-01-10 09:05:04-07:00, malff@weblab.(none) +3 -0
  Bug#25411 (trigger code truncated)
  
  Before this change, when a trigger code ends with the following characters:
  - '*'
  - ';'
  - '/'
  - whitespace
  the trigger code would be truncated by the parser, and later saved in the
  trigger .TRG file.
  In particluar, the following statement, which is syntaxically correct
  (it refers to an unknown table NEW, in a select from dual)
    INSERT INTO T2 SELECT NEW.*;
  whould be saved as :
    INSERT INTO T2 SELECT NEW.
  As a result, reading the .TRG file when reloading the trigger later fails,
  which prevents execution of any statements involving the table.
  
  The reason for truncating trailing '*' '/' and ';' characters is related to
  the use of the following syntax :
  /*!50003 CREATE TRIGGER t1_bi BEFORE INSERT ON t1
    FOR EACH ROW INSERT INTO t2 VALUES(NEW.c * 10) */;
  which is used for dumps and replication.
  Unders some circumpstances, this syntax can be altered to:
  CREATE TRIGGER t1_bi BEFORE INSERT ON t1
    FOR EACH ROW INSERT INTO t2 VALUES(NEW.c * 10) */;
  which create the need to clean up the closing comments, implemented by
  the function skip_rear_comments().
  
  With this fix, the function skip_rear_comments() has been changed to remove:
  - trailing whitespace
  - trailing ';' delimiters
  - one trailing '*' '/' sequence
  so that other combinations, like '*' ';' in this case, are not affected.

See related Bug#26302

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/24620

ChangeSet@1.2437, 2007-04-16 20:14:46-06:00, malff@weblab.(none) +16 -0
  Bug#25411 (trigger code truncated), PART I
  
  The issue found with bug 25411 is due to the function skip_rear_comments()
  which damages the source code while implementing a work around.
  The root cause of the problem is in the lexical analyser, which does not
  process special comments properly.
  For special comments like :
  [1] aaa /*!50000 bbb */ ccc
  since 5.0 is a version older that the current code, the parser is in lining
  the content of the special comment, so that the query to process is
  [2] aaa bbb ccc
  However, the text of the query captured when processing a stored procedure,
  stored function or trigger (or event in 5.1), can be after rebuilding it:
  [3] aaa bbb */ ccc
  which is wrong.
  
  To fix bug 25411 properly, the lexical analyser needs to return [2] when
  in lining special comments.
  In order to implement this, some preliminary cleanup is required in the code,
  which is implemented by this patch.
  
  Before this change, the structure named LEX (or st_lex) contains attributes
  that belong to lexical analysis, as well as attributes that represents the
  abstract syntax tree (AST) of a statement.
  Creating a new LEX structure for each statements (which makes sense for the
  AST part) also re-initialized the lexical analysis phase each time, which
  is conceptually wrong.
  
  With this patch, the previous st_lex structure has been split in two:
  - st_lex represents the Abstract Syntax Tree for a statement. The name "lex"
  has not been changed to avoid a bigger impact in the code base.
  - class lex_input_stream represents the internal state of the lexical
    analyser, which by definition should *not* be reinitialized when parsing
    multiple statements from the same input stream.
  
  This change is a pre-requisite for bug 25411, since the implementation of
  lex_input_stream will later improve to deal properly with special comments,
  and this processing can not be done with the current implementation of
  sp_head::reset_lex and sp_head::restore_lex, which interfere with the lexer.
  
  This change set alone does not fix bug 25411.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/25200

ChangeSet@1.2437, 2007-04-23 15:55:50-06:00, malff@weblab.(none) +16 -0
  Bug#25411 (trigger code truncated), PART I
  
  The issue found with bug 25411 is due to the function skip_rear_comments()
  which damages the source code while implementing a work around.
  The root cause of the problem is in the lexical analyser, which does not
  process special comments properly.
  For special comments like :
  [1] aaa /*!50000 bbb */ ccc
  since 5.0 is a version older that the current code, the parser is in lining
  the content of the special comment, so that the query to process is
  [2] aaa bbb ccc
  However, the text of the query captured when processing a stored procedure,
  stored function or trigger (or event in 5.1), can be after rebuilding it:
  [3] aaa bbb */ ccc
  which is wrong.
  
  To fix bug 25411 properly, the lexical analyser needs to return [2] when
  in lining special comments.
  In order to implement this, some preliminary cleanup is required in the code,
  which is implemented by this patch.
  
  Before this change, the structure named LEX (or st_lex) contains attributes
  that belong to lexical analysis, as well as attributes that represents the
  abstract syntax tree (AST) of a statement.
  Creating a new LEX structure for each statements (which makes sense for the
  AST part) also re-initialized the lexical analysis phase each time, which
  is conceptually wrong.
  
  With this patch, the previous st_lex structure has been split in two:
  - st_lex represents the Abstract Syntax Tree for a statement. The name "lex"
  has not been changed to avoid a bigger impact in the code base.
  - class lex_input_stream represents the internal state of the lexical
    analyser, which by definition should *not* be reinitialized when parsing
    multiple statements from the same input stream.
  
  This change is a pre-requisite for bug 25411, since the implementation of
  lex_input_stream will later improve to deal properly with special comments,
  and this processing can not be done with the current implementation of
  sp_head::reset_lex and sp_head::restore_lex, which interfere with the lexer.
  
  This change set alone does not fix bug 25411.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/25303

ChangeSet@1.2437, 2007-04-24 09:24:21-06:00, malff@weblab.(none) +16 -0
  Bug#25411 (trigger code truncated), PART I
  
  The issue found with bug 25411 is due to the function skip_rear_comments()
  which damages the source code while implementing a work around.
  The root cause of the problem is in the lexical analyser, which does not
  process special comments properly.
  For special comments like :
  [1] aaa /*!50000 bbb */ ccc
  since 5.0 is a version older that the current code, the parser is in lining
  the content of the special comment, so that the query to process is
  [2] aaa bbb ccc
  However, the text of the query captured when processing a stored procedure,
  stored function or trigger (or event in 5.1), can be after rebuilding it:
  [3] aaa bbb */ ccc
  which is wrong.
  
  To fix bug 25411 properly, the lexical analyser needs to return [2] when
  in lining special comments.
  In order to implement this, some preliminary cleanup is required in the code,
  which is implemented by this patch.
  
  Before this change, the structure named LEX (or st_lex) contains attributes
  that belong to lexical analysis, as well as attributes that represents the
  abstract syntax tree (AST) of a statement.
  Creating a new LEX structure for each statements (which makes sense for the
  AST part) also re-initialized the lexical analysis phase each time, which
  is conceptually wrong.
  
  With this patch, the previous st_lex structure has been split in two:
  - st_lex represents the Abstract Syntax Tree for a statement. The name "lex"
  has not been changed to avoid a bigger impact in the code base.
  - class lex_input_stream represents the internal state of the lexical
    analyser, which by definition should *not* be reinitialized when parsing
    multiple statements from the same input stream.
  
  This change is a pre-requisite for bug 25411, since the implementation of
  lex_input_stream will later improve to deal properly with special comments,
  and this processing can not be done with the current implementation of
  sp_head::reset_lex and sp_head::restore_lex, which interfere with the lexer.
  
  This change set alone does not fix bug 25411.

Pushed into 5.0.42

Pushed into 5.1.19-beta

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/27292

ChangeSet@1.2441, 2007-05-24 12:21:16-06:00, malff@weblab.(none) +15 -0
  Bug#25411 (trigger code truncated), PART II
  Bug 28127 (Some valid identifiers names are not parsed correctly)
  Bug 26302 (MySQL server cuts off trailing "*/" from comments in SP/func)
  
  This patch is the second part of a major cleanup, required to fix
  Bug 25411 (trigger code truncated).
  
  The root cause of the issue stems from the function skip_rear_comments,
  which was a work around to remove "extra" "*/" characters from the query
  text, when parsing a query and reusing the text fragments to represent a
  view, trigger, function or stored procedure.
  The reason for this work around is that "special comments",
  like /*!50002 XXX */, were not parsed properly, so that a query like:
    AAA /*!50002 BBB */ CCC
  would be seen by the parser as "AAA BBB */ CCC" when the current version
  is greater or equal to 5.0.2
  
  The root cause of this stems from how special comments are parsed.
  Special comments are really out-of-bound text that appear inside a query,
  that affects how the parser behave.
  In nature, /*!50002 XXX */ in MySQL is similar to the C concept
  of preprocessing :
    #if VERSION >= 50002
    XXX
    #endif
  
  Depending on the current VERSION of the server, either the special comment
  should be expanded or it should be ignored, but in all cases the "text" of
  the query should be re-written to strip the "/*!50002" and "*/" markers,
  which does not belong to the SQL language itself.
  
  Prior to this fix, these markers would leak into :
  - the storage format for VIEW,
  - the storage format for FUNCTION,
  - the storage format for FUNCTION parameters, in mysql.proc (param_list),
  - the storage format for PROCEDURE,
  - the storage format for PROCEDURE parameters, in mysql.proc (param_list),
  - the storage format for TRIGGER,
  - the binary log used for replication.
  
  In all cases, not only this cause format corruption, but also provide a vector
  for dormant security issues, by allowing to tunnel code that will be activated
  after an upgrade.
  
  The proper solution is to deal with special comments strictly during parsing,
  when accepting a query from the outside world.
  Once a query is parsed and an object is created with a persistant
  representation, this object should not arbitrarily mutate after an upgrade.
  In short, special comments are a useful but limited feature for MYSQLdump,
  when used at an *interface* level to facilitate import/export,
  but bloating the server *internal* storage format is *not* the proper way
  to deal with configuration management of the user logic.
  
  With this fix:
  - the Lex_input_stream class now acts as a comment pre-processor,
  and either expands or ignore special comments on the fly.
  - MYSQLlex and sql_yacc.yy have been cleaned up to strictly use the
  public interface of Lex_input_stream. In particular, how the input stream
  accepts or rejects a character is private to Lex_input_stream, and the
  internal buffer pointers of that class are strictly private, and should not
  be tempered with during parsing.
  
  This caused many changes mostly in sql_lex.cc.
  
  During the code cleanup in case MY_LEX_NUMBER_IDENT,
  Bug 28127 (Some valid identifiers names are not parsed correctly)
  was found and fixed.
  
  By parsing special comments properly, and removing the function
  'skip_rear_comments' [sic],
  Bug 26302 (MySQL server cuts off trailing "*/" from comments in SP/func)
  has been fixed as well.

Requested an additional change related to memory allocation.
The patch will have to go into 5.1 only.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/28612

ChangeSet@1.2549, 2007-06-12 15:23:58-06:00, malff@weblab.(none) +16 -0
  Bug#25411 (trigger code truncated), PART II
  Bug 28127 (Some valid identifiers names are not parsed correctly)
  Bug 26302 (MySQL server cuts off trailing "*/" from comments in SP/func)
  
  This patch is the second part of a major cleanup, required to fix
  Bug 25411 (trigger code truncated).
  
  The root cause of the issue stems from the function skip_rear_comments,
  which was a work around to remove "extra" "*/" characters from the query
  text, when parsing a query and reusing the text fragments to represent a
  view, trigger, function or stored procedure.
  The reason for this work around is that "special comments",
  like /*!50002 XXX */, were not parsed properly, so that a query like:
    AAA /*!50002 BBB */ CCC
  would be seen by the parser as "AAA BBB */ CCC" when the current version
  is greater or equal to 5.0.2
  
  The root cause of this stems from how special comments are parsed.
  Special comments are really out-of-bound text that appear inside a query,
  that affects how the parser behave.
  In nature, /*!50002 XXX */ in MySQL is similar to the C concept
  of preprocessing :
    #if VERSION >= 50002
    XXX
    #endif
  
  Depending on the current VERSION of the server, either the special comment
  should be expanded or it should be ignored, but in all cases the "text" of
  the query should be re-written to strip the "/*!50002" and "*/" markers,
  which does not belong to the SQL language itself.
  
  Prior to this fix, these markers would leak into :
  - the storage format for VIEW,
  - the storage format for FUNCTION,
  - the storage format for FUNCTION parameters, in mysql.proc (param_list),
  - the storage format for PROCEDURE,
  - the storage format for PROCEDURE parameters, in mysql.proc (param_list),
  - the storage format for TRIGGER,
  - the binary log used for replication.
  
  In all cases, not only this cause format corruption, but also provide a vector
  for dormant security issues, by allowing to tunnel code that will be activated
  after an upgrade.
  
  The proper solution is to deal with special comments strictly during parsing,
  when accepting a query from the outside world.
  Once a query is parsed and an object is created with a persistant
  representation, this object should not arbitrarily mutate after an upgrade.
  In short, special comments are a useful but limited feature for MYSQLdump,
  when used at an *interface* level to facilitate import/export,
  but bloating the server *internal* storage format is *not* the proper way
  to deal with configuration management of the user logic.
  
  With this fix:
  - the Lex_input_stream class now acts as a comment pre-processor,
  and either expands or ignore special comments on the fly.
  - MYSQLlex and sql_yacc.yy have been cleaned up to strictly use the
  public interface of Lex_input_stream. In particular, how the input stream
  accepts or rejects a character is private to Lex_input_stream, and the
  internal buffer pointers of that class are strictly private, and should not
  be tempered with during parsing.
  
  This caused many changes mostly in sql_lex.cc.
  
  During the code cleanup in case MY_LEX_NUMBER_IDENT,
  Bug 28127 (Some valid identifiers names are not parsed correctly)
  was found and fixed.
  
  By parsing special comments properly, and removing the function
  'skip_rear_comments' [sic],
  Bug 26302 (MySQL server cuts off trailing "*/" from comments in SP/func)
  has been fixed as well.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/29041

ChangeSet@1.2523, 2007-06-19 01:54:35+04:00, anozdrin@ibm. +5 -0
  Fix typo in the patch for BUG#25411 on 24-Apr-2007.

Pushed into 5.1.20-beta

Pushed into 5.0.46

Pushed into 5.1.21-beta

Noted in 5.1.20 changelog.

Embedded /* ... */ comments were handled incorrectly within the
definitions of stored programs and views, resulting in malformed
definitions. This also affected binary log contents.

See related Bug#25930, which was fixed as a side effect.