How to suppress warnings during drop procedure in MySQL

To suppress the warnings of NOTE level at session level do the following.

Please note that I have shown mysql> prompt only for demonstration purpose. In real you need to execute the required SQL command only like DELIMITER;.

mysql> DELIMITER ;

mysql>

mysql> SET @OLD_SQL_NOTES = @@session.SQL_NOTES;

Query OK, 0 rows affected (0.00 sec)

mysql> SET SESSION SQL_NOTES = 1;

Query OK, 0 rows affected (0.00 sec)

Now, let us drop a procedure which does not exist. As you can see a warning message is now visible.

mysql> DROP PROCEDURE IF EXISTS Alter_Table_Remove_Column;

Query OK, 0 rows affected, 1 warning (0.00 sec)

To look at the above warning message, you need to issue the SHOW WARNINGS command.

mysql> show warnings;

This will produce the following output displaying the warning message.

+-------+------+---------------------------------------------+

| Level | Code | Message                                     |

+-------+------+---------------------------------------------+

| Note  | 1305 | PROCEDURE sqlprac.Alter_Table_Remove_Column does not exist |

+-------+------+---------------------------------------------+

1 row in set (0.00 sec)

Now, since we need to suppress warnings, use SQL_NOTES and set it to OFF.

mysql> SET SESSION SQL_NOTES = 0;

Query OK, 0 rows affected (0.00 sec)

Let us drop the above procedure once again.

mysql> DROP PROCEDURE IF EXISTS Alter_Table_Remove_Column;

Query OK, 0 rows affected (0.00 sec)

The above process is called suppressing warning in MySQL. Now, when you will again try to fetch the warnings, it will display "Empty set" as shown below.

mysql> show warnings;

Empty set (0.00 sec)

If you want to reset the SQL_NOTES value back again to the original session value, do the following.

mysql> SET SESSION SQL_NOTES = @OLD_SQL_NOTES;

Query OK, 0 rows affected (0.00 sec)