logging verbosity mysql 5.7

Question

The default log error verbosity is 3 for mysql 5.7. I'm trying to set this to a level of 2 in the cnf file but I'm not sure of the syntax.

Answer 1

The GLOBAL variable log_warnings sets the level for verbosity which varies by server version. The following snippet illustrates:

SELECT @@log_warnings; -- make a note of your prior setting
SET GLOBAL log_warnings=2; -- setting above 1 increases output (see server version)

log_warnings as seen above is a dynamic variable.

Configuration file changes in cnf and ini files might look like the following.

[mysqld]
log_error        = /path/to/CurrentError.log
log_warnings     = 2

Please see the MySQL Manual Page entitled [The Error Log][] especially for Flushing and Renaming the Error Log file, and [Error Log Verbosity] with versions related to log_warnings. The GLOBAL variable log_warnings sets the level for verbosity which varies by server version. The following snippet illustrates:

SELECT @@log_warnings; -- make a note of your prior setting
SET GLOBAL log_warnings=2; -- setting above 1 increases output (see server version)

log_warnings as seen above is a dynamic variable.

Configuration file changes in cnf and ini files might look like the following.

[mysqld]
log_error        = /path/to/CurrentError.log
log_warnings     = 2

Please see the MySQL Manual Page entitled The Error Log especially for Flushing and Renaming the Error Log file, and Error Log Verbosity with versions related to log_warnings.

MySQL 5.7.2 expanded the warning level verbosity to 3 and added the GLOBAL log_error_verbosity. Again, it was introduced in 5.7.2. It can be set dynamically and checked as a variable or set via cnf or ini configuration file settings.

As of MySQL 5.7.2:

[mysqld]
log_error           = /path/to/CurrentError.log
log_warnings        = 2
log_error_verbosity = 3 

Change yours to 2 if you want, above.

You can dynamically set, and check, respectively, with:

SET GLOBAL log_error_verbosity=2; -- set it
SELECT @@log_error_verbosity=2; -- sanity check, view it

yet this will be reset to cnf or ini file settings upon server restart.

I will try to improve our Docs page on that.

How to set the Configuration file (cnf or ini, Linux or Windows, respectively).

The my.cnf and my.ini files reside in the basedir. If they do not exist, you can create them, else they will use defaults baked into the server. Often there is a stubbed out file in basedir, not active, named my-default, as a template. Use that to create the real my.cnf or my.ini. Issue a

SELECT @@basedir;

to find the location where my.ini (Windows) or my.cnf (Linux) should reside. Note, you will need to have sufficient rights to modify these files. In Windows, for instance, I need to run Notepad as Administrator to write such a file.

The following are a few commands issued to confirm a change to my.ini (for Windows, my.cnf for Linux) having set log_error_verbosity to 2. Note this is after a server restart which would have used those ini or cnf changes:

I show the use of basedir because that is where your ini or cnf file must exist depending on your operating system.