Infobright.org Forums |

Cannot Load Data

Posted: 26 July 2010 01:34 AM

[ Ignore ]

Newbie

Total Posts: 6

Joined 2009-06-24

I got a problem to load data into ice. All My script is able to load about 8 files into 8 tables, but only one is giving errors:

Error 02 HY000: Wrong data or column definition.

Below is my sql
LOAD DATA INFILE ‘D:/INFOBRIGHT/partner_service.txt’ INTO TABLE partner_service FIELDS TERMINATED BY ‘|’ ;

The data is:

22615|10|049861676|CNV|2008-01-17 17:52:57|0|0|0.00|\N|\N|\N|PAL|\N|\N|\N|\N|\N|\N|\N|\N|\N|\N|\N|\N|\N|\N|\N|\N|\N
22616|10|049860040|CNV|2008-01-17 17:52:57|78|4989|15.51|\N|\N|\N|PAL|\N|\N|\N|\N|\N|\N|\N|\N|\N|\N|\N|\N|\N|\N|\N|\N|\N
22617|10|049860039|CNV|2008-01-17 17:52:57|1571|145695|434.49|\N|\N|\N|PAL|\N|\N|\N|\N|\N|\N|\N|\N|\N|\N|\N|\N|\N|\N|\N|434.49|\N
22618|10|049860037|CNV|2008-01-17 17:52:57|52|2307|6.86|\N|\N|\N|PAL|\N|\N|\N|\N|\N|\N|\N|\N|\N|\N|\N|\N|\N|\N|\N|\N|\N
22619|10|049860036|CNV|2008-01-17 17:52:57|74|3235|9.44|\N|\N|\N|PAL|\N|\N|\N|\N|\N|\N|\N|\N|\N|\N|\N|\N|\N|\N|\N|\N|\N
22620|10|049855991|CNV|2008-01-17 17:52:57|0|0|0.00|\N|\N|\N|PAL|\N|\N|\N|\N|\N|\N|\N|\N|\N|\N|\N|\N|\N|\N|\N|\N|\N
22621|10|049855942|CNV|2008-01-17 17:52:57|0|0|0.00|\N|\N|\N|PAL|\N|\N|\N|\N|\N|\N|\N|\N|\N|\N|\N|\N|\N|\N|\N|\N|\N
22622|10|049855910|CNV|2008-01-17 17:52:57|22|2737|8.15|\N|\N|\N|PAL|\N|\N|\N|\N|\N|\N|\N|\N|\N|\N|\N|\N|\N|\N|\N|\N|\N
22623|10|049855818|CNV|2008-01-17 17:52:57|0|0|0.00|\N|\N|\N|PAL|\N|\N|\N|\N|\N|\N|\N|\N|\N|\N|\N|\N|\N|\N|\N|\N|\N
22624|10|049855795|CNV|2008-01-17 17:52:57|0|0|0.00|\N|\N|\N|PAL|\N|\N|\N|\N|\N|\N|\N|\N|\N|\N|\N|\N|\N|\N|\N|\N|\N

The data is select statement is:
SELECT * FROM partner_service INTO OUTFILE ‘D:/INFOBRIGHT/partner_service.TXT’ FIELDS TERMINATED BY ‘|’;

The table structure is the same between the source (Mysql Innodb ) and the destination (ICE).
The table structure is:
CREATE TABLE `partner_service` (
`PARTNERSERVICEKEY` int(11) NOT NULL,
`PARTNERKEY` int(11) NOT NULL,
`TELEPHONE_NO` varchar(16) CHARACTER SET utf8 NOT NULL,
`UPDATE_ID` varchar(12) CHARACTER SET utf8 DEFAULT NULL,
`UPDATE_DATE` datetime DEFAULT NULL,
`TOTAL_CALL` int(11) DEFAULT NULL,
`TOTAL_DURATION` int(11) DEFAULT NULL,
`TOTAL_COMMISSION` decimal(12,2) DEFAULT NULL,
`TOTAL_DURATION_SEC` int(11) DEFAULT NULL,
`EFFECTIVE_DATE` date DEFAULT NULL,
`TERMINATION_DATE` datetime DEFAULT NULL,
`PRODUCT_TYPE` varchar(3) CHARACTER SET utf8 DEFAULT NULL,
`EXCHANGE_ID` varchar(6) CHARACTER SET utf8 DEFAULT NULL,
`SEFCODE` varchar(4) CHARACTER SET utf8 DEFAULT NULL,
`CUSTOMER` varchar(40) CHARACTER SET utf8 DEFAULT NULL,
`ADDRESS1` varchar(50) CHARACTER SET utf8 DEFAULT NULL,
`ADDRESS2` varchar(50) CHARACTER SET utf8 DEFAULT NULL,
`CITYNAME` varchar(30) CHARACTER SET utf8 DEFAULT NULL,
`POSTCODE` varchar(5) CHARACTER SET utf8 DEFAULT NULL,
`STATE` varchar(2) CHARACTER SET utf8 DEFAULT NULL,
`SEGMENT` varchar(3) DEFAULT NULL,
`CUSTOMER_NAME` varchar(60) DEFAULT NULL,
`BALANCE` decimal(11,2) DEFAULT NULL,
`CARD_STATUS` varchar(10) DEFAULT NULL,
`ACTIVATE_DATE` date DEFAULT NULL,
`STREAMYX` varchar(3) DEFAULT NULL,
`ACCOUNT` varchar(14) DEFAULT NULL,
`TOTAL_USAGE` decimal(12,2) DEFAULT NULL,
`CARD_NO` varchar(10) DEFAULT NULL
) ENGINE=BRIGHTHOUSE AUTO_INCREMENT=724919 DEFAULT CHARSET=latin1 AVG_ROW_LENGTH=92;

Profile

mfauzim

Posted: 26 July 2010 11:22 AM

[ Ignore ] [ # 1 ]

Newbie

Total Posts: 6

Joined 2009-06-24

Bump!!
Any help is greatly appreciated.

Profile

Geoffrey Falk

Posted: 26 July 2010 11:29 AM

[ Ignore ] [ # 2 ]

Sr. Member

Total Posts: 505

Joined 2008-08-18

Hi,

It loads fine for me. Maybe you have an extra space somewhere?

Geoffrey

Signature

Profile

David Lutz

Posted: 26 July 2010 11:53 AM

[ Ignore ] [ # 3 ]

Sr. Member

Total Posts: 738

Joined 2008-08-18

I copied your DDL into /root/scratch/table.ddl and the data into /tmp/data.txt. I was able to create the table and load the data with no issues. My guess is that there is another line of data in the file that has “bad” data.

I tested on IEE 3.4.1 GA in a 64-bit VM image of CentOS. What version, edition and platform are you using?

[root@localhost ~]# mysql-ib 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.1.40-log build number (revision)=IB_3.4.1_r8746_8921(iee - commercial)

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> use test
Database changed

mysql> source /root/scratch/table.ddl
Query OK, 0 rows affected (0.04 sec)

mysql> desc partner_service;
+--------------------+---------------+------+-----+---------+-------+
| Field              | Type          | Null | Key | Default | Extra |
+--------------------+---------------+------+-----+---------+-------+
| PARTNERSERVICEKEY  | int(11)       | NO   |     | NULL    |       |
| PARTNERKEY         | int(11)       | NO   |     | NULL    |       |
| TELEPHONE_NO       | varchar(16)   | NO   |     | NULL    |       |
| UPDATE_ID          | varchar(12)   | YES  |     | NULL    |       |
| UPDATE_DATE        | datetime      | YES  |     | NULL    |       |
| TOTAL_CALL         | int(11)       | YES  |     | NULL    |       |
| TOTAL_DURATION     | int(11)       | YES  |     | NULL    |       |
| TOTAL_COMMISSION   | decimal(12,2) | YES  |     | NULL    |       |
| TOTAL_DURATION_SEC | int(11)       | YES  |     | NULL    |       |
| EFFECTIVE_DATE     | date          | YES  |     | NULL    |       |
| TERMINATION_DATE   | datetime      | YES  |     | NULL    |       |
| PRODUCT_TYPE       | varchar(3)    | YES  |     | NULL    |       |
| EXCHANGE_ID        | varchar(6)    | YES  |     | NULL    |       |
| SEFCODE            | varchar(4)    | YES  |     | NULL    |       |
| CUSTOMER           | varchar(40)   | YES  |     | NULL    |       |
| ADDRESS1           | varchar(50)   | YES  |     | NULL    |       |
| ADDRESS2           | varchar(50)   | YES  |     | NULL    |       |
| CITYNAME           | varchar(30)   | YES  |     | NULL    |       |
| POSTCODE           | varchar(5)    | YES  |     | NULL    |       |
| STATE              | varchar(2)    | YES  |     | NULL    |       |
| SEGMENT            | varchar(3)    | YES  |     | NULL    |       |
| CUSTOMER_NAME      | varchar(60)   | YES  |     | NULL    |       |
| BALANCE            | decimal(11,2) | YES  |     | NULL    |       |
| CARD_STATUS        | varchar(10)   | YES  |     | NULL    |       |
| ACTIVATE_DATE      | date          | YES  |     | NULL    |       |
| STREAMYX           | varchar(3)    | YES  |     | NULL    |       |
| ACCOUNT            | varchar(14)   | YES  |     | NULL    |       |
| TOTAL_USAGE        | decimal(12,2) | YES  |     | NULL    |       |
| CARD_NO            | varchar(10)   | YES  |     | NULL    |       |
+--------------------+---------------+------+-----+---------+-------+
29 rows in set (0.02 sec)

mysql> set @bh_dataformat='txt_variable';  [ IEE only ]
Query OK, 0 rows affected (0.02 sec)

mysql> LOAD DATA INFILE '/tmp/data.txt' INTO TABLE partner_service FIELDS TERMINATED BY '|';
Query OK, 10 rows affected (0.22 sec)
Records: 10  Deleted: 0  Skipped: 0  Warnings: 0

mysql> select count(*) from partner_service;
+----------+
| count(*) |
+----------+
|       10 |
+----------+
1 row in set (0.00 sec) 

Signature

Profile

David Lutz

Posted: 26 July 2010 01:24 PM

[ Ignore ] [ # 4 ]

Sr. Member

Total Posts: 738

Joined 2008-08-18

mfauzim,

I also tested this on ICE 3.3.2 on 32-bit Windows XP. I was also successful with this test. See session information below:

C:\Program Files\Infobright>"C:\Program Files\Infobright\bin\mysql.exe" --defaults-file="C:\Program Files\Infobright\my-ib.ini" -uroot

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.1.40-log build number (revision)=IB_3.3.2_r7501_7556(ice)

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> use test
Database changed

mysql> source c:\temp\table.ddl
Query OK, 0 rows affected (6.17 sec)

mysql> desc partner_service;
+--------------------+---------------+------+-----+---------+-------+
| Field              | Type          | Null | Key | Default | Extra |
+--------------------+---------------+------+-----+---------+-------+
| PARTNERSERVICEKEY  | int(11)       | NO   |     | NULL    |       |
| PARTNERKEY         | int(11)       | NO   |     | NULL    |       |
| TELEPHONE_NO       | varchar(16)   | NO   |     | NULL    |       |
| UPDATE_ID          | varchar(12)   | YES  |     | NULL    |       |
| UPDATE_DATE        | datetime      | YES  |     | NULL    |       |
| TOTAL_CALL         | int(11)       | YES  |     | NULL    |       |
| TOTAL_DURATION     | int(11)       | YES  |     | NULL    |       |
| TOTAL_COMMISSION   | decimal(12,2) | YES  |     | NULL    |       |
| TOTAL_DURATION_SEC | int(11)       | YES  |     | NULL    |       |
| EFFECTIVE_DATE     | date          | YES  |     | NULL    |       |
| TERMINATION_DATE   | datetime      | YES  |     | NULL    |       |
| PRODUCT_TYPE       | varchar(3)    | YES  |     | NULL    |       |
| EXCHANGE_ID        | varchar(6)    | YES  |     | NULL    |       |
| SEFCODE            | varchar(4)    | YES  |     | NULL    |       |
| CUSTOMER           | varchar(40)   | YES  |     | NULL    |       |
| ADDRESS1           | varchar(50)   | YES  |     | NULL    |       |
| ADDRESS2           | varchar(50)   | YES  |     | NULL    |       |
| CITYNAME           | varchar(30)   | YES  |     | NULL    |       |
| POSTCODE           | varchar(5)    | YES  |     | NULL    |       |
| STATE              | varchar(2)    | YES  |     | NULL    |       |
| SEGMENT            | varchar(3)    | YES  |     | NULL    |       |
| CUSTOMER_NAME      | varchar(60)   | YES  |     | NULL    |       |
| BALANCE            | decimal(11,2) | YES  |     | NULL    |       |
| CARD_STATUS        | varchar(10)   | YES  |     | NULL    |       |
| ACTIVATE_DATE      | date          | YES  |     | NULL    |       |
| STREAMYX           | varchar(3)    | YES  |     | NULL    |       |
| ACCOUNT            | varchar(14)   | YES  |     | NULL    |       |
| TOTAL_USAGE        | decimal(12,2) | YES  |     | NULL    |       |
| CARD_NO            | varchar(10)   | YES  |     | NULL    |       |
+--------------------+---------------+------+-----+---------+-------+
29 rows in set (0.03 sec)

mysql> LOAD DATA INFILE 'c:\\temp\\data.txt' INTO TABLE partner_service FIELDS TERMINATED BY '|';
Query OK, 10 rows affected (6.63 sec)
Records: 10  Deleted: 0  Skipped: 0  Warnings: 0

mysql> select count(*) from partner_service;
+----------+
| count(*) |
+----------+
|       10 |
+----------+
1 row in set (0.17 sec) 

Signature

Profile

mfauzim

Posted: 26 July 2010 06:32 PM

[ Ignore ] [ # 5 ]

Newbie

Total Posts: 6

Joined 2009-06-24

Thanks for all the reply.
I am using ice 3.3.2 on win 2003.
All the data is generated by the SQL select statement.
The data I attached is subset of all the data.
The actual is about 300000 records.

When I test the scripts on my Xp machine it works ok. It loads ok.
This create all the headache.
Is it possible that what is in the data case the loader to misbehave? and how to trouble shoot it.
thanks

Profile

Bob Newell

Posted: 26 July 2010 08:34 PM

[ Ignore ] [ # 6 ]

Member

Total Posts: 160

Joined 2009-04-01

Hi Muhd,

The error suggests that you have an embedded vertical bar “|” in your data somewhere. Maybe you can try generating a tab delimited file then do a search for the vertical bar in the resulting file.

A couple of things about your DDL. I notice that you are using a column option of “CHARACTER SET utf8”. You should probably only use this if you really need the extended character set. I have seen different results with the loader when this character set is used. If you want to clean up the DDL you can also remove the ATUO_INCREMENT and AVG_ROW_SIZE parameters that are not supported by ICE.

When I test the scripts on my Xp machine it works ok.

Does this mean that you can load the exact same file in your InnoDB table?

If so, do you receive any warnings or truncation messages?

... Bob

Profile

mfauzim

Posted: 26 July 2010 09:34 PM

[ Ignore ] [ # 7 ]

Newbie

Total Posts: 6

Joined 2009-06-24

I’ve changes the delimiter to other character and search for character ‘|’, and yes one of the record had the ‘|’ in it. This throw all the load.
After changing the source ‘|’ to space then all works fine.

Thanks for all the help

Profile

Cell Phone

Posted: 31 August 2010 11:52 PM

[ Ignore ] [ # 8 ]

Newbie

Total Posts: 1

Joined 2010-08-20

I got a similar problem while uplaoding some data…...
It took me some days to finally solve it…..
everything was right….
the only problem was a “COMMA” extra somewhere in some line…..
That comma almost took my job…..
thanks that i read this blog and figured out my mistake….

Thanks You,

iPhone Repair Houston

Profile

‹‹ outfile to mount folder can not read... Query Is Soo Slow ››

Username:
Password:
Auto-login on future visits
Forgot your password?