[MDEV-24694] sql_error_log plugin logs errors caught by procedure handler Created: 2021-01-26  Updated: 2021-06-21  Resolved: 2021-06-05

Status: Closed
Project: MariaDB Server
Component/s: Plugins
Affects Version/s: 10.2, 10.3, 10.4, 10.5
Fix Version/s: N/A

Type: Bug Priority: Critical
Reporter: Hartmut Holzgraefe Assignee: Sergei Golubchik
Resolution: Won't Fix Votes: 0
Labels: None


 Description   

When having the sql-error-log plugin enabled, and running a simple procedure test fetching data from a cursor, the following is written to the sql error plugin log, even though a HANDLER for NOT FOUND exists to catch this error, so that it is never actually returned to the client at all:

Error 1329: No data - zero rows fetched, selected, or processed ....

Looks as if the plugin API event that the sql error log plugin hooks into fires before the procedures HANDLER even has a chance to actually act on the error ...

CREATE TABLE t1(i INT);
 
 
 
DELIMITER //
 
 
 
CREATE PROCEDURE p1()
 
BEGIN
 
  DECLARE done INT DEFAULT FALSE;
 
  DECLARE x INT;
 
  DECLARE cur1 CURSOR FOR SELECT i FROM t1;
 
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
 
 
 
  OPEN cur1;
 
 
 
  read_loop: LOOP
 
    FETCH cur1 INTO x;
 
    IF done THEN
 
      LEAVE read_loop;
 
    END IF;
 
  END LOOP;
 
 
 
  CLOSE cur1;
 
END; //
 
 
 
DELIMITER ;
 
 
 
CALL p1();



 Comments   
Comment by Hartmut Holzgraefe [ 2021-02-01 ]

This actually writes

Error 1329: No data - zero rows fetched, selected, or processed ....

upon every cursor fetch loop when reading beyond the last result low, not only for cursor queries returning no result rows at all. I changed the original description accordingly.

This makes the sql_error_log plugin rather useless when using it in combination with stored routines, as the log will basically be spammed with "No data" error messages in this case ...

Comment by Sergei Golubchik [ 2021-06-05 ]

This is how the audit API works, the audit plugin gets all errors, intercepted or not.

The point is to get a record when someone tried to access the data that one had no right to access. It would be fairly pointless auditing if one could always avoid detection by simply writing a handler to intercept "access denied" errors.

Comment by Hartmut Holzgraefe [ 2021-06-05 ]

This is about the SQL error log though, not the audit log, and the error is not about access privileges either.

This specific "No data ... zero rows fetched" error will happen in every fetch loop in a stored routine at the point where no further result data is left to be fetched.

So this will spam the SQL error log for every fetch loop in a stored routine.

Note that the SQL error log is documented as

"The SQL_ERROR_LOG plugin collects errors sent to clients in a log file"

In this case the error is never sent to the client at all though, so this is not working as documented.

https://mariadb.com/kb/en/sql-error-log-plugin/

Comment by Sergei Golubchik [ 2021-06-05 ]

sql_error_log is an audit plugin, it's not magic, it writes error audit events to the log and ignores the rest.

Documentation was amended to say that even if the error was handled by a condition handler, it'll be logged anyway.

What we can do is to ignore "No data ... zero rows fetched" error completely in the plugin and never log it. It's a slippery slope to start treating errors differently depending on the error number, but perhaps it makes sense to ignore this one.

Generated at Thu Feb 08 09:31:57 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.