How to show a MySQL warning that just happened?

Question

I just ran a simple MySQL CREATE TABLE statement that produced the line

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

It didn't actually show me what the warning was, though. How can you see the contents of a warning that just occurred? I'm using MySQL 5.1, if it makes a difference. The only thing I found online was "SHOW WARNINGS;" but that only produced

"Empty set (0.00 sec)."

Answer 1

SHOW WARNINGS is the only method I'm aware of, but you have to run it immediately after a query that had warnings attached to it. If you ran any other queries in between, or dropped the connection, then SHOW WARNINGS won't work.

The MySQL manual page for SHOW WARNINGS doesn't indicate any other methods, so I'm fairly certain that you're stuck with it.

Answer 2

You can use --show-warnings with login to show warnings as shown below. *By default, warnings are hidden:

mysql -u john -p --show-warnings

Or, you can use warnings(WARNINGS) or \W after login to show warnings as shown below:

mysql> warnings
Show warnings enabled.

Or:

mysql> WARNINGS
Show warnings enabled.

Or:

mysql> \W
Show warnings enabled.

Then, a warning is shown as below:

mysql> SELECT 1/0;
+------+
| 1/0  |
+------+
| NULL |
+------+
1 row in set, 1 warning (0.00 sec)

Warning (Code 1365): Division by 0

And, you can use nowarning(NOWARNING) or \w after login to hide warnings as shown below:

mysql> nowarning
Show warnings disabled.

Or:

mysql> NOWARNING
Show warnings disabled.

Or:

mysql> \w
Show warnings disabled.

Then, a warning is hidden as below:

mysql> SELECT 1/0; 
+------+
| 1/0  |
+------+
| NULL |
+------+
1 row in set, 1 warning (0.00 sec)

Or on Windows, you can set show-warnings under [mysql] in my.ini as shown below. *My answer explains [mysql] and my answer explains where my.ini is located on Windows:

# "my.ini"

[mysql]
...
show-warnings

Then, you can log in by setting my.ini's location to --defaults-file= or --defaults-extra-file= to show warnings as shown below. *--defaults-file= or --defaults-extra-file= must be the 1st option otherwise there is the error:

mysql --defaults-file='C:\ProgramData\MySQL\MySQL Server 8.0\my.ini' -u john -p

Or:

mysql --defaults-extra-file='C:\ProgramData\MySQL\MySQL Server 8.0\my.ini' -u john -p

Answer 3

When MySQL database imports data, warnings often appear.

These warnings are easy to be ignored.

I suddenly want to see what the warnings are when I get to the data today.

MySQL view warning command

show warnings;

The command is very concise, and the general warning is like this when I check it.

1.

Warning | 1265 | Data truncated for column ‘title’ at row 1265

This warning is that the field length is not enough,

the imported data is automatically cropped by the system.

2.

| Warning | 1366 | Incorrect string value: ‘\xB5\xDA\xB6\xFE\xBD\xEC...‘ for column ‘Journal title’ at row 1444 |

This is the wrong character set of the data.

For the second one, you need to modify the character set of the database or the txt encoding format of the imported data.

3.

| Warning | 1366 | Incorrect integer value: ‘‘ for column ‘work number’ at row 13 |

This is to insert empty data, if the field allows null values, you can ignore these warnings. 4.

| Warning | 1262 | Row 11 was truncated; it contained more data than there were input columns |

This is a redundant column of imported data.

The MySQL warning is easy to understand, and it is easy to modify the table after the warning message.

Answer 4

@HorusKol, do you have documentation for that? I couldn't find any. But I did find out that the command line option --show-warnings will do the job, according to the MySQL manual.

Answer 5

You can also set the command line to always display warnings after a query using \W

You can switch them off again with \w

Answer 6

All the suggestions above didn't work for me. I used SHOW WARNINGS\G and it worked.

Answer 7

You can use \W to enable and \w to disable the visibility of the warning. After enabling, the warning will be visible whenever it occurs.