suppress warning messages in mysql

How to Suppress Warning Messages in MySQL

Sometimes when we try running certain MySQL commands or SQL queries in console or other MySQL tools, we get a warning message from MySQL server. Of course, this does not stop the execution but it can be annoying if you see warning messages frequently. This cane be a big problem if you are programmatically running MySQL queries and using query result to proceed with code execution. In such cases, it might be advisable to temporarily disable MySQL warning till your queries are run. In this article, we will learn how to suppress warning messages in MySQL.


How to Suppress Warning Messages in MySQL

By default, MySQL is configured to show warning messages. You can easily disable it by changing the value of sql_notes server parameter.

To do this, log into MySQL console. Now let us try to drop a table ‘sample’ that does not exist.

mysql> drop table if exists sample;

You will see the following output which indicates that there is 1 warning message as a result of above query.

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

We will view the warning with following command.

mysql> show warnings;

It will show the following result.

+-------+------+-----------------------------------+
| Level | Code | Message                           |
+-------+------+-----------------------------------+
| Note  | 1051 | Unknown table 'sample'     |
+-------+------+-----------------------------------+
1 row in set (0.00 sec)

Run the following command to disable warning messages in MySQL.

mysql> SET sql_notes = 0;

Now let us try to drop the above mentioned table once again.

mysql> drop table if exists sample;
Query OK, 0 rows affected (0.07 sec)

As you can see above, it does not indicate there is any warning. Still let us try to view warning.

mysql> show warnings;
Empty set (0.00 sec)

You will see that there are no warnings this time.

If you want to re-enable warning in MySQL, run the following command.

mysql> SET sql_notes = 1;

But please note, if you set sql_notes to 0, the value of sql_notes will remain 0 only during current session. If you start a new session, it will be reset to 1.

If you want to permanently suppress warnings in MySQL, you need to set sql_notes=0 in my.cnf (Linux)/my.ini (Windows) file which contains all the important server configurations.

$ vi /etc/mysql/my.cnf

Add the following line in it.

sql_notes = 0;

Thereafter you need to restart MySQL server to apply changes.

$ service mysqld restart

In this article, we have learnt how to suppress warning messages. This is useful if you are programmatically running MySQL queries and do not want unnecessary warnings to disrupt the query result.

Also read:

How to Show JavaScript Date in AM/PM format
How to Get HTML Element’s Actual Width & Height
How to Call Parent Window Function from IFrame
How to Get Cookie By Name in JavaScript
How to Find Max Value of Attribute in Array of JS Objects

Leave a Reply

Your email address will not be published.