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
