[MDEV-6421] SQL_ERROR_LOG doesn't log comments in Events Created: 2014-07-06  Updated: 2021-09-08  Resolved: 2016-01-26

Status: Closed
Project: MariaDB Server
Component/s: Prepared Statements
Affects Version/s: 10.0.12
Fix Version/s: 10.0.24, 10.1.12

Type: Bug Priority: Minor
Reporter: Federico Razzoli Assignee: Alexey Botchkov
Resolution: Fixed Votes: 0
Labels: sql_error_log

Issue Links:
Relates
relates to MDEV-6899 extra semicolon in show create event ... Closed
Sprint: 10.1.11

 Description   

With the SQL error log enabled:

DELIMITER ||
 
CREATE EVENT e1
 
	ON SCHEDULE EVERY 10 SECOND
 
DO BEGIN
 
	START TRANSACTION;
 
	INSERT INTO test.non_exists VALUES (0,0,0) /* e1 */;
 
	COMMIT;
 
END;
 
||
 
DELIMITER ;

The statement is wrong, and is written into SQL_ERROR_LOG. But '/* e1 */' is not logged. The comment is preserved in the event source, though:

MariaDB [test]> SHOW CREATE EVENT e1 \G
 
*************************** 1. row ***************************
 
               Event: e1
 
            sql_mode: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
 
           time_zone: SYSTEM
 
        Create Event: CREATE DEFINER=`root`@`localhost` EVENT `e1` ON SCHEDULE EVERY 10 SECOND STARTS '2014-07-06 10:42:40' ON COMPLETION NOT PRESERVE ENABLE DO BEGIN
 
START TRANSACTION;
 
INSERT INTO test.non_exists VALUES (0,0,0) /* e1 */ ;
 
COMMIT;
 
END
 
character_set_client: utf8
 
collation_connection: utf8_general_ci
 
  Database Collation: utf8_general_ci
 
1 row in set (0.00 sec)

The reason why this is a problem, is that there is not easy way to see events errors. Logging these errors into the SQL_ERROR_LOG would be very useful, if we could filter the entries based on a comment. Without this ability, that log is probably too big.

Alternative request: ability to write the scheduler's SQL errors into a separate log. Both solutions would be equally good from my point of view.

 Comments   
Comment by Elena Stepanova [ 2014-07-06 ]

It looks like comments are not logged at all, not only in events:

MariaDB [test]> select  /* comment */ abc;
 
ERROR 1054 (42S22): Unknown column 'abc' in 'field list'

log:

2014-07-06 20:00:00 root[root] @ localhost [127.0.0.1] ERROR 1054: Unknown column 'abc' in 'field list' : select   abc

Not sure if it was intentional and if there was a good reason for that.

Executable comments are logged, which of course is correct:

MariaDB [test]> select  /*! comment */ abc;
 
ERROR 1054 (42S22): Unknown column 'comment' in 'field list'

log:

2014-07-06 20:03:03 root[root] @ localhost [127.0.0.1] ERROR 1054: Unknown column 'comment' in 'field list' : select  /*! comment */ abc

Comment by Federico Razzoli [ 2014-07-06 ]

Elena, interestingly in my installation comments are logged:

2014-07-06 19:10:21 root[root] @ localhost [] ERROR 1054: Unknown column 'abc' in 'field list' : select  /* comment */ abc

But comments in events are not logged.

Comment by Elena Stepanova [ 2014-07-06 ]

This is indeed interesting, I tried the release build 10.0.12 (from non-GLIBC_2_14 bintar), and I'm still not getting comments in the log.
Which package are you using, and what's your environment?

Comment by Federico Razzoli [ 2014-07-06 ]

I use that tar too. My OS is Debian.

Comment by Federico Razzoli [ 2014-07-06 ]

Elena, please no offence if I'm wrong - did you start mysql with --comments option?

I made this error: I just remembered MDEV-4772. But again, no offence, I'm just trying to help and not trying to appear smart.

Comment by Elena Stepanova [ 2014-07-07 ]

Oh no, you are right of course, thanks. I went through all thinkable server options, but totally forgot about the client ones. Sorry for the confusion, I've now retried with the option, reproducible as described.

On the other hand, if you place the comment somewhere else, not at the end of the statement, it's logged:

create event ev3 ON SCHEDULE EVERY 10 SECOND do BEGIN START TRANSACTION; INSERT INTO test.non_exists /* e3 */ VALUES (0,0,0); COMMIT; END|
 

2014-07-07  1:19:23 root[root] @ localhost [localhost] ERROR 1146: Table 'test.non_exists' doesn't exist : INSERT INTO test.non_exists /* e3 */ VALUES (0,0,0)

So it might be an easy workaround.

Comment by Federico Razzoli [ 2014-07-08 ]

From my tests, the comment is not logged if it is at the beginning or at the end of the statement, but seems to be logged if it is written anywhere else.

Yes, it's a good workaround

Comment by Alexey Botchkov [ 2016-01-25 ]

Fixing patch:
http://lists.askmonty.org/pipermail/commits/2016-January/008862.html

Comment by Alexey Botchkov [ 2016-01-26 ]

Final fix: http://lists.askmonty.org/pipermail/commits/2016-January/008869.html

Generated at Thu Feb 08 07:11:46 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.