Search code examples
mysqllogparser

Logparser output to SQL producing empty strings and max value integers

I have a logparser query that produces correct output when run to the screen or CSV file, but when I send it to SQL (MySQL in this case), the data is messed up. the timestamps are right, but the strings show empty (not null, just empty), and the integer columns show as their max value (in this case medium int = 8388607).

This is the table:

CREATE TABLE `penetration` 
(
    `id` INT(10) NOT NULL AUTO_INCREMENT,
    `timestamp` DATETIME NULL DEFAULT NULL,
    `campaign` VARCHAR(10) NULL DEFAULT NULL,
    `build_date` DATE NULL DEFAULT NULL,
    `results` MEDIUMINT(8) NULL DEFAULT NULL,
    `p1` MEDIUMINT(8) NULL DEFAULT NULL,
    `p2` MEDIUMINT(8) NULL DEFAULT NULL,
    `p3` MEDIUMINT(8) NULL DEFAULT NULL,
    `p4` MEDIUMINT(8) NULL DEFAULT NULL,
    `p5` MEDIUMINT(8) NULL DEFAULT NULL,
    `p6` MEDIUMINT(8) NULL DEFAULT NULL,
    `p7` MEDIUMINT(8) NULL DEFAULT NULL,
    `p8` MEDIUMINT(8) NULL DEFAULT NULL,
    `p9` MEDIUMINT(8) NULL DEFAULT NULL,
    `p10` MEDIUMINT(8) NULL DEFAULT NULL,
    `p26` MEDIUMINT(8) NULL DEFAULT NULL,
    `accounts` MEDIUMINT(8) NULL DEFAULT NULL,
    `logon` VARCHAR(10) NULL DEFAULT NULL,
    `client` VARCHAR(2) NULL DEFAULT NULL,
    `facility` VARCHAR(3) NULL DEFAULT NULL,
    `type` VARCHAR(2) NULL DEFAULT NULL,
    INDEX `id` (`id`)
)
COLLATE='utf8_general_ci'
ENGINE=MyISAM
AUTO_INCREMENT=1668
;

The working query from the command line (the nulls are there to distill the problem down to it's essence for troubleshooting):

"SELECT NULL, timestamp, TRIM(campaign) AS campaign, TO_DATE(timestamp) AS build_date, TO_INT(results) AS results, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL FROM penetration.tsv"

The screen output (which is correct) looks like this -- note the campaign and result columns have correct data):

<NULL> 2015-11-16 23:30:14 BLUCCHM1 2015-11-16 219     <NULL> <NULL> <NULL> <NULL> <NULL> <NULL> <NULL> <NULL> <NULL> <NULL> <NULL> <NULL> <NULL> <NULL> <NULL> <NULL>
<NULL> 2015-11-16 23:30:14 BLUCUCH1 2015-11-16 1330    <NULL> <NULL> <NULL> <NULL> <NULL> <NULL> <NULL> <NULL> <NULL> <NULL> <NULL> <NULL> <NULL> <NULL> <NULL> <NULL>

When I execute this, trying to push the data to a database table, I use this query:

logparser -i:TSV -o:SQL -dsn:directorDSN32 -database:[dbname] -username:[username] -password:[password] -driver:"MySQL ODBC 5.1 Driver" -server:[ip address] "SELECT NULL, timestamp, TRIM(campaign) AS campaign, TO_DATE(timestamp) AS build_date, TO_INT(results) AS results, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL FROM penetration.tsv TO penetration"

The query runs successfully, but the table ends up looking like this, notice the timestamps are right, the campaign column is empty, and the results column has the max value for mediumint:

Penetration table image

I'm new so I can't embed an image, but the table output looks like this:

id      timestamp            campaign       build_date        results         ...
1       2015-09-16 17:06:09                 2015-09-16        8,338,607       (NULL)
2       2015-09-16 17:06:09                 2015-09-16        8,338,607       (NULL)

Thanks in advance for any help that can be offered. I imagine the solution is something simple, but I've been going crazy trying to figure it out.

Solution

  • The integer problem was fixed, as Gabriele suggested in the comments, by changing the column type from medium int to integer.

    The string issue turns out to be an issue with the MySQL ODBC 5.1 driver. It's a "non-bug" according to the official report, but several others experience it, and indeed the query started working when I switched to using the 3.51 driver.