Bug #16195 SHOW VARIABLES doesn't report correctly sql_warnings and sql_notes values
Submitted: 4 Jan 2006 16:32 Modified: 2 May 2006 1:35
Reporter: Thomas CORBIERE Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:5.0.18/5.0.19 BK OS:Microsoft Windows (Windows XP/linux)
Assigned to: Jim Winstead

[4 Jan 2006 16:32] Thomas CORBIERE
Description:
When you set sql_warnings and sql_notes to OFF, the server behave as if they are OFF but SHOW VARIABLES still display them as ON.

it affects both 5.0.18 and 4.1.16

How to repeat:
mysql> SET sql_notes = 0, sql_warnings = 0 ; SELECT @@sql_notes, @@sql_warnings
\G SHOW VARIABLES LIKE 'sql\_%s' \G SET sql_notes = 1, sql_warnings = 1 ; SELECT
 @@sql_notes, @@sql_warnings \G SHOW VARIABLES LIKE 'sql\_%s' \G
Query OK, 0 rows affected (0.00 sec)

*************************** 1. row ***************************
   @@sql_notes: 0
@@sql_warnings: 0
1 row in set (0.01 sec)

*************************** 1. row ***************************
Variable_name: sql_notes
        Value: ON
*************************** 2. row ***************************
Variable_name: sql_warnings
        Value: ON
2 rows in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

*************************** 1. row ***************************
   @@sql_notes: 1
@@sql_warnings: 1
1 row in set (0.00 sec)

*************************** 1. row ***************************
Variable_name: sql_notes
        Value: ON
*************************** 2. row ***************************
Variable_name: sql_warnings
        Value: ON
2 rows in set (0.00 sec)
[8 Jan 2006 3:05] Miguel Solorzano
Thank you for the bug report.

miguel@hegel:~/dbs/5.0> bin/mysql -uroot
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 5.0.19-debug

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

mysql> SET sql_notes = 0, sql_warnings = 0 ; SELECT @@sql_notes, @@sql_warnings
Query OK, 0 rows affected (0.00 sec)

    -> \G SHOW VARIABLES LIKE 'sql\_%s' \G
*************************** 1. row ***************************
   @@sql_notes: 0
@@sql_warnings: 0
1 row in set (0.00 sec)

*************************** 1. row ***************************
Variable_name: sql_notes
        Value: ON
*************************** 2. row ***************************
Variable_name: sql_warnings
        Value: ON
2 rows in set (0.01 sec)

mysql> SET sql_notes = 1, sql_warnings = 1 ;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT
    ->  @@sql_notes, @@sql_warnings \G SHOW VARIABLES LIKE 'sql\_%s' \G
*************************** 1. row ***************************
   @@sql_notes: 1
@@sql_warnings: 1
1 row in set (0.00 sec)

*************************** 1. row ***************************
Variable_name: sql_notes
        Value: ON
*************************** 2. row ***************************
Variable_name: sql_warnings
        Value: ON
2 rows in set (0.00 sec)

mysql>
[21 Apr 2006 2:55] Jim Winstead
i actually noticed this problem when adding sql_big_tables to SHOW VARIABLES, glad to see there's a bug for it already.
[21 Apr 2006 3:40] Bugs System
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/5243
[1 May 2006 16:02] Jim Winstead
Fixed in 5.0.22 and 5.1.10.
[2 May 2006 1:35] Paul Dubois
Noted in 5.0.22, 5.1.10.

The <literal>sql_notes</literal> and
<literal>sql_warnings</literal> system variables were not
always displayed correctly by <literal>SHOW
VARIABLES</literal> (for example, they were displayed as
<literal>ON</literal> after being set to
<literal>OFF</literal>). (Bug #16195)