Your syslog8.txt file has two problems:
1. The first field, log_date, is in m/d/yyyy format. It will not be
converted by MySQL automatically. It needs to be in yyyy-mm-dd format.
For this reason, all records will have the default value of '0000-00-00'
for that field.
2. The primary key on your table is log_id. In the sample you sent,
all of the log_id's are the same. The first row is inserted, and all
others with that ID are skipped.
kgt
Elizabeth Bonifacio wrote:
Dear Guys,
I have been stucked in loading file using "load data local infile" command.
At first it is loading a null value on my first record followed with
one record from my input file. The rest are ignored and i've been
receiving lots of warnings.
I tried converting the text file into a csv and was successful in
loading all the files but with a lot of null values and data going
another field. I used the "fields terminated by ',' lines terminated
by ','" instead of '\r\n' because the loading problem.
I tried reformatting my txt and csv file, check its spacing(tabs) one
by one but still the data seem to go haywire.
I'm actually building an application that will accept continuous data
(syslog) using c++ that I need to store in Mysql. But just want to
handle one thing at a time so I'm just testing how it will accept
loading file from the mysql prompt.
I'm running on windows XP, mysql server 4.1.11. Kindly refer to the
table structure and the attached file. I know you guys are experience
and must have encounter this matter before.
Will appreciate all your help to a new developer like me,
Thanks,
Elizabeth
mysql> show create table log8;
| log8 |CREATE TABLE `log8` (
`log_date` date default '0000-00-00',
`log_time` time default '00:00:00',
`device_id` varchar(17) default NULL,
`log_id` varchar(11) NOT NULL default '',
`type` varchar(7) default NULL,
`subtype` varchar(7) default NULL,
`pri` varchar(6) default NULL,
`vd` varchar(4) default NULL,
`serial_number` int(11) default NULL,
`duration` int(5) default NULL,
`policyid` tinyint(3) default NULL,
`proto` tinyint(3) default NULL,
`service` varchar(10) default NULL,
`status` varchar(6) default NULL,
`src` varchar(15) default NULL,
`srcname` varchar(15) default NULL,
`dst` varchar(15) default NULL,
`dstname` varchar(15) default NULL,
`src_int` varchar(8) default NULL,
`dst_int` varchar(8) default NULL,
`sent` int(11) default NULL,
`rcvd` int(11) default NULL,
`sent_pkt` tinyint(4) default NULL,
`rcvd_pkt` tinyint(4) default NULL,
`src_port` int(11) default NULL,
`dst_port` int(11) default NULL,
`vpn` varchar(5) default NULL,
`tran_ip` varchar(15) default NULL,
`tran_port` int(5) default NULL,
`dir_disp` varchar(5) default NULL,
`tran_disp` varchar(5) default NULL,
PRIMARY KEY (`log_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
1 row in set (0.08 sec)
mysql>
On 6/27/05, Gleb Paharenko <[EMAIL PROTECTED]> wrote:
Hello.
Send an example of your file and your table's definition
(use SHOW CREATE TABLE for this purpose). Please, next time
send your replies to the list.
Hope you're doing great today. Back to my question last week, I've
been encountering this problem of loading my file using the "load data
local infile" command for a particular text file.
I cannot determine the error in my sql codes since i find no problem
loading other text file into the database.
mysql> load data local infile 'syslog5.txt' into table log4 fields
terminated by ' \t' lines terminated by '\n';
Query OK, 2 rows affected, 513 warnings (0.03 sec)
Records: 20 Deleted: 0 Skipped: 18 Warnings: 505
I'm not familiar with this "warnings" myself. I can only load the
first line in the "syslog5.txt" and the rest are ignored. From the two
rows only the first line is stored and the rest are ignored.
The result of my select statement from the log4 table includes all
null value on the first row and the value of the second line for the
second row.
Would appreciate if you can teach me how to debug the warning or maybe
enlighten me on the cause of the problem.
Thanks for all your help.
Elizabeth
On 6/24/05, Gleb Paharenko <[EMAIL PROTECTED]> wrote:
Hello.
What 'SHOW WARNINGS' reports?
Elizabeth Bonifacio <[EMAIL PROTECTED]> wrote:
--
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.NET http://www.ensita.net/
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Gleb Paharenko
/ /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED]
/_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET
<___/ www.mysql.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
------------------------------------------------------------------------
6/23/2005 15:45:50 FGT2002A04200757 22010001 traffic
allowed notice root 3656393 180 1 17 53020/udp accept
192.168.2.6 192.168.2.6 83.217.132.7 83.217.132.7 internal
external 183 69 1 1 32193 53020 n/a
202.189.48.98 61114 org noop
6/23/2005 15:45:50 FGT2002A04200757 22010001 traffic
allowed notice root 3655880 584 1 17 36480/udp accept
192.168.2.6 192.168.2.6 70.67.164.40 70.67.164.40 internal
external 1601 595 8 8 32193 36480 n/a
202.189.48.98 60652 org noop
6/23/2005 15:45:50 FGT2002A04200757 22010001 traffic
allowed notice root 3656371 202 1 17 3417/udp accept
192.168.2.222 192.168.2.222 68.67.220.64 68.67.220.64 internal
external 153 920 2 2 12000 3417 n/a
202.189.48.98 61092 org noop
6/23/2005 15:45:50 FGT2002A04200757 22010001 traffic
allowed notice root 3656394 180 1 17 38818/udp accept
192.168.2.6 192.168.2.6 193.49.4.200 193.49.4.200 internal
external 172 70 1 1 32193 38818 n/a
202.189.48.98 61115 org noop
6/23/2005 15:45:50 FGT2002A04200757 22010001 traffic
allowed notice root 3655172 1012 1 17 16249/udp accept
192.168.2.222 192.168.2.222 203.136.241.66 203.136.241.66 internal
external 1804 427 11 7 12000 16249 n/a
202.189.48.98 60059 org noop
6/23/2005 15:45:50 FGT2002A04200757 22010001 traffic
allowed notice root 3656397 180 1 17 56167/udp accept
192.168.2.222 192.168.2.222 203.64.121.219 203.64.121.219 internal
external 164 70 1 1 12000 56167 n/a
202.189.48.98 61118 org noop
6/23/2005 15:45:50 FGT2002A04200757 22010001 traffic
allowed notice root 3656366 209 1 17 3246/udp accept
192.168.2.50 192.168.2.50 219.5.220.9 219.5.220.9 internal
external 296 98 2 2 53538 3246 n/a
202.189.48.98 61087 org noop
6/23/2005 15:45:50 FGT2002A04200757 22010001 traffic
allowed notice root 3656367 209 1 17 57154/udp accept
192.168.2.50 192.168.2.50 212.179.96.181 212.179.96.181 internal
external 296 98 2 2 53538 57154 n/a
202.189.48.98 61088 org noop
6/23/2005 15:45:51 FGT2002A04200757 22010001 traffic
allowed notice root 3656395 181 1 17 1398/udp accept
192.168.2.6 192.168.2.6 84.252.47.195 84.252.47.195 internal
external 179 91 1 1 32193 1398 n/a
202.189.48.98 61116 org noop
6/23/2005 15:45:51 FGT2002A04200757 22010001 traffic
allowed notice root 3656398 180 1 17 36480/udp accept
192.168.2.222 192.168.2.222 70.67.164.40 70.67.164.40 internal
external 178 79 1 1 12000 36480 n/a
202.189.48.98 61119 org noop
------------------------------------------------------------------------
