Wednesday, April 27, 2005

How to show warning messages for LOAD DATA INFILE

Ever wondered what the warning messages were when you did a load data infile in MySQL? Well in MySQL 4.1.0 and greater you can by issuing a "SHOW WARNINGS" command at the mysql console- e.g.

mydb1707>load data infile '/tmp/people.txt'
-> into table webapps.merchants
-> fields terminated by '\t'
-> lines terminated by '\n'
-> ignore 1 lines;

Query OK, 103 rows affected, 14 warnings (0.06 sec)
Records: 103 Deleted: 0 Skipped: 0 Warnings: 14

mydb1707>show warnings;
+---------+------+----------------------------------------------+
| Level | Code | Message |
+---------+------+----------------------------------------------+
| Warning | 1265 | Data truncated for column 'state' at row 13 |
| Warning | 1265 | Data truncated for column 'phone' at row 13 |
| Warning | 1265 | Data truncated for column 'state' at row 14 |
| Warning | 1265 | Data truncated for column 'state' at row 52 |
| Warning | 1265 | Data truncated for column 'phone' at row 59 |
| Warning | 1265 | Data truncated for column 'phone' at row 60 |
| Warning | 1265 | Data truncated for column 'phone' at row 61 |
| Warning | 1265 | Data truncated for column 'state' at row 71 |
| Warning | 1265 | Data truncated for column 'phone' at row 72 |
| Warning | 1265 | Data truncated for column 'phone' at row 78 |
| Warning | 1265 | Data truncated for column 'phone' at row 82 |
| Warning | 1265 | Data truncated for column 'phone' at row 86 |
| Warning | 1265 | Data truncated for column 'state' at row 92 |
| Warning | 1265 | Data truncated for column 'phone' at row 100 |
+---------+------+----------------------------------------------+
14 rows in set (0.02 sec)


Check out http://dev.mysql.com/doc/mysql/en/show-warnings.html for the full details.

3 comments:

Anonymous said...

thank you that was very useful

Anonymous said...

first result in google and just what I wanted. thanks.

Anonymous said...

The same for me. Thank you !