List:General Discussion« Previous MessageNext Message »
From:Michael Gargiullo Date:November 3 2006 3:00pm
Subject:Loading data using infile
View as plain text  
I have a script that builds a data file. The data looks like this:

"62527427012682984", "191151", "177628526", "3634025281", "1", "58400",
"80", "1899", "", "2006/10/02 23:15:02", "19",
"47","","2006-11-02-231557-cust.txt", "0", "0", "IKE", "ESP: AES-256 +
SHA1", "cx-ccb_vpn", "Cx-CCB", "", ""
"62527427012682983", "191150", "177628526", "3634025281", "2", "4163",
"0", "1899", "", "2006/10/02 23:14:59", "-1",
"50","","2006-11-02-231557-cust.txt", "0", "0", "IKE", "ESP: AES-256 +
SHA1", "cx-ccb_vpn", "Cx-CCB", "", ""

When I run this load data command like so: 

LOAD DATA CONCURRENT LOCAL INFILE '/db/cust/tmp/firewall/cust.txt.ctl'
INTO TABLE LogData FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES
TERMINATED BY '\n'

I don't get any errors, but get strange data inserted:

| 62527427012682983 |  0 |  0 |  0 |  0 |  0 |  0 |  0 | 0 | 0000-00-00
00:00:00 | 0 | 0 | 0000-00-00 00:00:00 | 2006-11-02-231557-cust.txt |
"0" | "0" | "IKE" | "ESP: AES-256 + SHA1" |  "cx-ccb_vpn"  | "Cx-CCB" |
"" | ""  |
| 62527427012682984 |  0 |  0 |  0 |  0 |  0 |  0 |  0 | 0 | 0000-00-00
00:00:00 | 0 | 0 | 0000-00-00 00:00:00 | 2006-11-02-231557-cust.txt |
"0" | "0" | "IKE" | "ESP: AES-256 + SHA1" |  "cx-ccb_vpn"  | "Cx-CCB" |
"" | ""  |

If I try one row it works:

insert into LogData values (62527427012682984, 191151, 177628526,
3634025281, 1, 58400, 80, 1899, 0, '2006/10/02 23:15:02', 19,
47,'','2006-11-02-231557-cust.txt', '0', '0', 'IKE', 'ESP: AES-256 +
SHA1', 'cx-ccb_vpn', 'Cx-CCB', '', '');
Query OK, 1 row affected, 1 warning (0.00 sec)

| 52527427012682984 | 191151 | 177628526 | 3634025281 |  1 |  58400 |
80 | 1899 | 0 | 2006-10-02 23:15:02 | 19 |  47 | 0000-00-00 00:00:00 |
2006-11-02-231557-cust.txt | 0 | 0 | IKE | ESP: AES-256 + SHA1 |
cx-ccb_vpn | Cx-CCB |   |   |


The only difference I can see is the use of single quotes on the command
line.  What's very puzzling is that this same load file line works on my
other machine.  The only difference I can see between them is this table
has a few more fields as integers.


The table is mostly ints:

+-----------------------+----------------------+------+-----+---------+-
------+
| Field                 | Type                 | Null | Key | Default |
Extra |
+-----------------------+----------------------+------+-----+---------+-
------+
| event_id              | bigint(1) unsigned   | NO   | PRI |         |
|
| dev_rec_num           | int(1) unsigned      | YES  |     | NULL    |
|
| src                   | int(1) unsigned      | NO   | MUL |         |
|
| dst                   | int(1) unsigned      | NO   | MUL |         |
|
| protocol_id_i         | tinyint(1) unsigned  | YES  |     | NULL    |
|
| src_port_id_i         | smallint(1) unsigned | YES  |     | NULL    |
|
| dst_port_id_i         | smallint(1) unsigned | YES  |     | NULL    |
|
| origin_id_i           | smallint(1) unsigned | YES  | MUL | NULL    |
|
| collection_point_id_i | smallint(1) unsigned | YES  |     | NULL    |
|
| date_time             | datetime             | NO   | MUL |         |
|
| rule_id_i             | smallint(1)          | YES  | MUL | NULL    |
|
| action_id_i           | smallint(1) unsigned | YES  |     | NULL    |
|
| date_entered          | datetime             | NO   |     |         |
|
| filename              | varchar(35)          | YES  |     | NULL    |
|
| user_def_1            | varchar(50)          | YES  |     | NULL    |
|
| user_def_2            | varchar(255)         | YES  |     | NULL    |
|
| user_def_3            | varchar(50)          | YES  |     | NULL    |
|
| user_def_4            | varchar(50)          | YES  |     | NULL    |
|
| user_def_5            | varchar(50)          | YES  |     | NULL    |
|
| user_def_6            | varchar(50)          | YES  |     | NULL    |
|
| user_def_7            | varchar(50)          | YES  |     | NULL    |
|
| user_def_8            | varchar(50)          | YES  |     | NULL    |
|
+-----------------------+----------------------+------+-----+---------+-
------+


Any Ideas?

-Mike
Thread
Loading data using infileMichael Gargiullo3 Nov
RE: Loading data using infileMichael Gargiullo3 Nov