CODEWITHSUNDEEP
mariadb
g.pickardou
g.pickardou

Reputation: 35913

Why SHOW ERRORS or SHOW WARNINGS lists only the very last error/warning?

I am using the SHOW ERRORS and SHOW WARNIGS statments (on MariaDB 10.10.2) and regardless of the max_error_count is in its default 64 value, the statements above only list the very last error/warning.

Question

How can I list all the last recent errors/warning according the max_error_count variable?

Upvotes: 0

Views: 205

Answers (2)

Georg Richter
Georg Richter

Reputation: 7476

The SQL statement SHOW ERRORS/WARNINGS only returns error or warnings for the last statement which failed or produced a warning. They can be retrieved until another statement produced an error/warning.

If a statement produced more than one error/warnings, then the SHOW ERRORS/WARNINGS return up to max_error warnings.

Example:

delimiter $$
CREATE PROCEDURE p1() BEGIN  DROP TABLE whichdoesnotexist; END $$
CREATE PROCEDURE p2() BEGIN  CALL p1(); END  $$
CREATE PROCEDURE p3() BEGIN  CALL p2(); END  $$
delimiter ;

SET @@max_error_count=2;
CALL p3();
SHOW WARNIINGS;
+-------+------+----------------------------------------+
| Level | Code | Message                                |
+-------+------+----------------------------------------+
| Error | 1051 | Unknown table 'test.whichdoesnotexist' |
| Note  | 4094 | At line 2 in test.p1                   |
+-------+------+----------------------------------------+

SET @@max_error_count=4;
CALL p3();
SHOW WARNINGS;
+-------+------+----------------------------------------+
| Level | Code | Message                                |
+-------+------+----------------------------------------+
| Error | 1051 | Unknown table 'test.whichdoesnotexist' |
| Note  | 4094 | At line 2 in test.p1                   |
| Note  | 4094 | At line 2 in test.p2                   |
| Note  | 4094 | At line 2 in test.p3                   |
+-------+------+----------------------------------------+

# This will not clear warning/errors
SELECT "1","2","3";
+---+---+---+
| 1 | 2 | 3 |
+---+---+---+
| 1 | 2 | 3 |
+---+---+---+

SHOW WARNINGS;
+-------+------+----------------------------------------+
| Level | Code | Message                                |
+-------+------+----------------------------------------+
| Error | 1051 | Unknown table 'test.whichdoesnotexist' |
| Note  | 4094 | At line 2 in test.p1                   |
| Note  | 4094 | At line 2 in test.p2                   |
| Note  | 4094 | At line 2 in test.p3                   |
+-------+------+----------------------------------------+

Upvotes: 2

ysth
ysth

Reputation: 98398

SHOW ERRORS and SHOW WARNINGS only show errors/warnings from the most recent statement in the same session. They will never show anything from previous statements or other sessions. max_error_count will reduce the number shown, but never cause anything else to be shown.

MariaDB [test]> select version();
+-----------------------------------------+
| version()                               |
+-----------------------------------------+
| 10.10.2-MariaDB-1:10.10.2+maria~ubu2204 |
+-----------------------------------------+
1 row in set (0.001 sec)

MariaDB [test]> select date(d) from (select '' d union all select 20220229) d;
+---------+
| date(d) |
+---------+
| NULL    |
| NULL    |
+---------+
2 rows in set, 2 warnings (0.002 sec)

MariaDB [test]> show warnings;
+---------+------+--------------------------------------+
| Level   | Code | Message                              |
+---------+------+--------------------------------------+
| Warning | 1292 | Incorrect datetime value: ''         |
| Warning | 1292 | Incorrect datetime value: '20220229' |
+---------+------+--------------------------------------+
2 rows in set (0.000 sec)

MariaDB [test]> set session max_error_count=1;
Query OK, 0 rows affected (0.001 sec)

MariaDB [test]> select date(d) from (select '' d union all select 20220229) d;
+---------+
| date(d) |
+---------+
| NULL    |
| NULL    |
+---------+
2 rows in set, 2 warnings (0.001 sec)

MariaDB [test]> show warnings;
+---------+------+------------------------------+
| Level   | Code | Message                      |
+---------+------+------------------------------+
| Warning | 1292 | Incorrect datetime value: '' |
+---------+------+------------------------------+
1 row in set (0.000 sec)

Upvotes: 1

Related Questions