I am attempting to use the LOAD DATA feature. At the end it reports that I have several warnings but there does not appear to be a way to determine what the warnings are. I installed V4.1 which has the SHOW WARNINGS feature, but, it returns empty sets after the load command.
I have 30+ tables with several hundred thousand rows. Does anyone have a simpler (and less time consuming) way than writing ETL to export the tables and compare every record field by field (as recommended by the MySQL Doc) to determine what the problems are?
I don't know if this helps, but ...
The first thing you need to compare are the number of rows imported into the table to the number of rows in the text file. If they are the same then you know the warnings are a conversion error and not a field violation (like importing a NULL value into a column that is NOT NULL). If you do have rows missing then I'd recommend putting a sequence number in each row of the ASCII file. I know it's a bit of a pain but then you would know immediately which rows were rejected. (You would need to write a program to determine the break in the sequence). If you have Delphi then you could use an ASCII file driver to read the ASCII file and compare the values to the table.
If you have the same number of rows, then one or more values were imported incorrectly. Have you looked at your ENUM's to make sure none of them are ""? For example, an ENUM(Y","N") expects "Y" or "N" in the text file. If an invalid value is in the text file, like "True" then the ENUM sets it to an empty string (this is not the same as NULL).
Writing a table compare utility that compares two tables or one table to an ASCII file is relatively easy to do. Another alternative is to dump the table that was just imported back to a CSV file, and use a utility that compares 2 text files to see if you can spot the differences. If I were importing data a lot, I'd write a table compare utility that writes my own log file showing the record#, and the two field values that disagree, or the record that was missing. For missing records I'd allow him to fix the record fields and then allow him to re-import just those invalid records. Perhaps someone has already written a utility like this. It's not that hard.
Mike
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
