[MySQL]: Warnings

SQL

09/21/2019

MySQL Warnings

Consider the following queries

SQL

CREATE TABLE people
( name VARCHAR(10),
  age INT
);

INSERT INTO people
VALUES('my name is tooo long', 5)  -- longer than limit

Then you'd encounter

TEXT

mysql> INSERT INTO people VALUES('my name is tooo long', 5);
Query OK, 1 row affected, 1 warning (0.00 sec)

To display the warning, do the following:

SQL

SHOW WARNINGS;

Caveat: In shell, you won't be able to see warnings if you type in other query after the warning sign is displayed (i.e. 1 warning (0.00sec))
Output:

TEXT

mysql> SHOW WARNINGS;
+---------+------+-------------------------------------------+
| Level   | Code | Message                                   |
+---------+------+-------------------------------------------+
| Warning | 1265 | Data truncated for column 'name' at row 1 |
+---------+------+-------------------------------------------+
1 row in set (0.01 sec)

What was inserted:

TEXT

mysql> SELECT * FROM people;
+------------+------+
| name       | age  |
+------------+------+
| my name is |    5 |
+------------+------+
1 row in set (0.00 sec)

The exceeded characters are automatically removed

You might encounter an error instead of warning such as

TEXT

ERROR 1406 (22001): Data too long for column 'name' at row 1

To check the warnings, type in the following

SQL

set sql_mode=''; --mode resets back to show error on reboot

The mode resets back to show the errors on reboot of the shell

WRITTEN BY

Ellis Min

Keeping a record

GitHub