I am busy sanitizing a 250,000 record tab delimited database file. Each record has more than 50 fields.
After importing the file with the mysql util, the following is shown;
Code:
Query OK, 231369 rows affected, 65535 warnings (6.12 sec)
Records: 231369 Deleted: 0 Skipped: 0 Warnings: 5392
After some digging in the online documentation I found the
show warnings statement:
Code:
show warnings ;
[snip]
| Warning | 1366 | Incorrect integer value: '' for column 'xxxx_yyy' at row 1 |
| Warning | 1265 | Data truncated for column 'first_name' at row 1 |
| Warning | 1366 | Incorrect integer value: '' for column 'zzz' at row 1 |
[snip]
| Warning | 1366 | Incorrect integer value: '' for column 'id2' at row 13 |
+---------+------+----------------------------------------------------------------+
64 rows in set (0.01 sec)
The
Incorrect integer value: can be ignored, because those fields is empty. But I really would like to know which fields have been truncated. From those 5392 warnings only 64 are shown.
But as stated at
http://dev.mysql.com/doc/refman/5.1/...-warnings.html
Quote:
The max_error_count system variable controls the maximum number of error, warning, and note messages for which the server stores information, and thus the number of messages that SHOW WARNINGS displays. By default, max_error_count is 64. To change the number of messages the server can store, change the value of max_error_count.
The value of warning_count is not limited by max_error_count if the number of messages generated exceeds max_error_count.
|
Code:
mysql> show variables like 'max_error_count' ;
+-----------------+-------+
| Variable_name | Value |
+-----------------+-------+
| max_error_count | 64 |
+-----------------+-------+
mysql> SET max_error_count=6000;
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT @@max_error_count;
+-------------------+
| @@max_error_count |
+-------------------+
| 6000 |
+-------------------+
1 row in set (0.00 sec)
So now I should be able to check all those 5392 warnings ......