MySQL Cookbook Example - load_diag.pl

One of the tools included in the MySQL Cookbook recipes distribution is a Perl script named load_diag.pl that diagnoses where problems occur in data files that you load with the LOAD DATA statement. The following example is adapted from MySQL Cookbook and illustrates how load_diag.pl works. (You can obtain the recipes distribution from the downloads page.)

Assume you have a simple table named diag_test that contains string, date, and number columns:

     CREATE TABLE diag_test
     (
         str     CHAR(10),
         date    DATE,
         num     INT
     );

Assume you also have a data file named diag_sample.dat that you plan to load into the table:

     str1    01-20-2001      97
     str2    02-28-2002
             03-01-2002      64      extra junk

To see if the file will have any problems loading, check it like this:

     % load_diag.pl cookbook diag_test diag_sample.dat
     line 1: 1 warning
       column 2 (date): bad value = (01-20-2001)
     line 2: 2 warnings
       too few columns
       column 2 (date): bad value = (02-28-2002)
       column 3 (num): missing from input line
       column 3 (num): bad value = () (inserting NULL worked better)
     line 3: 1 warning
       excess number of columns

     Number of lines in file: 3
     Warnings found when loading entire file: 4
     Lines containing too few column values: 1
     Lines containing excess column values: 1

     Warnings per column:

     Column    Times      Total     Warnings for     Improved
              missing    warnings   empty columns    with NULL
     str            0           0               0            0
     date           0           2               0            0
     num            1           1               1            1

The output from load_diag.pl indicates on a line-by-line basis where problems occur in the data file, and also contains a general summary of the problems that were found.