Infobright.org Forums |

LOAD ERROR: Wrong data or column definition

Posted: 01 September 2010 01:17 PM

[ Ignore ]

Newbie

Total Posts: 9

Joined 2010-03-30

From an MySQL InnoDB table I have tried selecting data to an outfile multiple ways. Each time I go to load the data in the corresponding IEE BRIGHTHOUSE table i receive an error. Below are three different methods that I have tried. For the sample data provided, what would be the best choice for defining the values for FIELDS TERMINATED BY, ENCLOSED BY, and LINES TERMINATED BY.

SELECT * INTO OUTFILE '/mnt/tmp/data/log_session.csv' FIELDS TERMINATED BY '\t' ESCAPED by '\\' FROM log_session;

LOAD DATA INFILE '/mnt/tmp/data/log_session.csv' INTO TABLE log_session FIELDS TERMINATED BY '\t' ESCAPED by '\\';

data:
2444268    1927949    "Mozilla/5.0    \N    211.24.140.211    1263149549

log entry: 2010-08-31 22:08:30 Error: Wrong data or column definition. Row: 2444268, field: 3. 

========================================================================

SELECT * INTO OUTFILE '/mnt/tmp/data/log_session.csv' FIELDS TERMINATED BY ';' ENCLOSED BY 'NULL' LINES TERMINATED BY '\n' FROM log_session;

LOAD DATA INFILE '/mnt/tmp/data/log_session.csv' INTO TABLE log_session FIELDS TERMINATED BY ';' ENCLOSED BY 'NULL' LINES TERMINATED BY '\n';

data:
NULL1539NULL;NULL1421NULL;NULLMozilla/5.0 (compatible; Yahoo! Slurp/3.0; http://help.yahoo.com/help/us/ysearch/slurp)NULL;NULLhttp://www.varsity.com/confirmation.aspx?type=pli&ReturnUrl;=/community/addteam.aspxNULL;NULL74.6.22.98NULL;NULL1251237757NULL

log entry: 2010-09-01 14:29:38 Error: Wrong data or column definition. Row: 1539, field: 5. 

========================================================================

SELECT * INTO OUTFILE '/mnt/tmp/data/log_session.csv' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n' FROM log_session;
SELECT * INTO OUTFILE '/mnt/tmp/data/log_session.csv' FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n' FROM log_session;

LOAD DATA INFILE '/mnt/tmp/data/log_session.csv' INTO TABLE log_session FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n';

data:
"2444268","1927949","\"Mozilla/5.0",\N,"211.24.140.211","1263149549"

log entry:
2010-09-01 15:37:52 Error: Wrong data or column definition. Row: 2444268, field: 3. 

========================================================================

Sample Data:
“1538”;“1420”;“Mozilla/4.0 (compatible; MSIE 7.0; Windows NNT 5.1; GTB6; .NNET CLR 1.0.3705; .NNET CLR 1.1.4322; Media Center PC 4.0)”;“http://www.varsity.com/”;“96.35.230.41”;“1251237753”
“1539”;“1421”;“Mozilla/5.0 (compatible; Yahoo! Slurp/3.0; http://help.yahoo.com/help/us/ysearch/slurp)”;“http://www.varsity.com/confirmation.aspx?type=pli&ReturnUrl;=/community/addteam.aspx”;“74.6.22.98”;“1251237757”
“1540”;“1422”;“Mozilla/5.0 (Macintosh; U; Intel Mac OS X 10_5_8; en-us) AppleWebKit/531.9 (KHTML, like Gecko) Version/4.0.3 Safari/531.9”;“http://www.varsity.com/”;“173.26.93.143”;“1251237759”
“8467746”,“6570707”,“Mozilla/5.0 (Windows; U; Windows NT 6.0; \”.XPI\”; rv:1.9.0.18) Gecko/2010020220 Firefox/3.0.18 (.NET CLR 3.5.30729)”,“http://www.power106.fm/”,“59.93.50.188”,“1267431780”
“9612738”,“7477849”,“Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; SV1; .NET CLR 2.0.50727; .NET CLR 3.0.4506.2152; .NET CLR 3.5.30729; WinTSI 13.11.2009)”,“http://assets.1019rxp.com/player/shell.swf?channel_id=314&asset_id=67376&\”=&log_embed_id=205804&token;=$1$IfbOVIS2$dLQAyF0PCTLHId.WQ5mQ1”,“68.109.13.222”,“1268189605”
“10078336”,“7836627”,“Mozilla/4.0 (compatible; MSIE 8.0; Windows NT 6.1; Trident/4.0; SLCC2; .NET CLR 2.0.50727; .NET CLR 3.5.30729; .NET CLR 3.0.30729; Media Center PC 6.0; OfficeLiveConnector.1.3; OfficeLivePatch.0.0)”,“http://www.thisis50.com/profiles/log/list?promoted=1&start=10\\”,“74.65.50.65”,“1268397883”

[ Edited: 01 September 2010 01:26 PM by Scott W]

Profile

Kethees Murugupillai

Posted: 02 September 2010 01:29 PM

[ Ignore ] [ # 1 ]

Administrator

Total Posts: 139

Joined 2008-08-18

I took the first line of sample data and created a table with the assumption of all char fields and was able to load the data.

CREATE TABLE `t1` (
`a1` char(55) COLLATE latin1_bin DEFAULT NULL,
`a2` char(55) COLLATE latin1_bin DEFAULT NULL,
`a3` char(140) COLLATE latin1_bin DEFAULT NULL,
`a4` char(55) COLLATE latin1_bin DEFAULT NULL,
`a5` char(55) COLLATE latin1_bin DEFAULT NULL,
`a6` char(55) COLLATE latin1_bin DEFAULT NULL
) ENGINE=BRIGHTHOUSE DEFAULT CHARSET=latin1 COLLATE=latin1_bin;

mysql> set @bh_dataformat=‘txt_variable’;
Query OK, 0 rows affected (0.00 sec)

mysql> load data infile ‘/home/mysql/data/tmp/a1.txt’ into table t1 fields terminated by ‘;’ enclosed by ‘“’ escaped by ‘\\’;
Query OK, 1 row affected (0.08 sec)
Records: 1 Deleted: 0 Skipped: 0 Warnings: 0

mysql> select * from t1;
+———+———+————————————————————————————————————————————————————————————+————————————-+———————+——————+
| a1 | a2 | a3 | a4 | a5 | a6 |
+———+———+————————————————————————————————————————————————————————————+————————————-+———————+——————+
| 1538 | 1420 | Mozilla/4.0 (compatible; MSIE 7.0; Windows NNT 5.1; GTB6; .NNET CLR 1.0.3705; .NNET CLR 1.1.4322; Media Center PC 4.0) | http://www.varsity.com/ | 96.35.230.41 | 1251237753 |
+———+———+————————————————————————————————————————————————————————————+————————————-+———————+——————+
1 row in set (0.00 sec)

Data in file:
“1538”;“1420”;“Mozilla/4.0 (compatible; MSIE 7.0; Windows NNT 5.1; GTB6; .NNET CLR 1.0.3705; .NNET CLR 1.1.4322; Media Center PC 4.0)”;“http://www.varsity.com/”;“96.35.230.41”;“1251237753”

regards,
Kethees.

Profile

Buddoda!

Posted: 03 September 2010 02:27 AM

[ Ignore ] [ # 2 ]

Newbie

Total Posts: 28

Joined 2010-07-31

My so far experience(may be too little) dares to suggest the below mentioned.
Focus on escaped by clause. This is used, as the third field value consists of quote preceded by \ .

Note: Data should be(when exported to a text file which will be used for loading into brighthouse database) as below mentioned when the values have ” in them:

For example: “Mozilla/5.0 should be \“Mozilla/5.0

"2444268","1927949","\"Mozilla/5.0",\N,"211.24.140.211","1263149549" LOAD DATA INFILE '/mnt/tmp/data/log_session.csv' INTO TABLE log_session FIELDS TERMINATED BY ',' ENCLOSED BY '"' ESCAPED BY '\\"' LINES TERMINATED BY '\n';

[ Edited: 03 September 2010 02:42 AM by Buddoda!]

Profile

Scott W

Posted: 09 September 2010 01:04 PM

[ Ignore ] [ # 3 ]

Newbie

Total Posts: 9

Joined 2010-03-30

Using the escape option resolved my load issue for double quotes(”), but now running into a similar issue with backslashes(\). These backslashes only account for 53 records of about 22+ million records, but we really need to be able to preserve the original data that is being log. Where is it that I am going wrong?

code used to select data out of a table:
SELECT * INTO OUTFILE '/mnt/tmp/data/outfiles/log_embed.csv' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' ESCAPED by '\\' LINES TERMINATED BY '\n' FROM log_embed ORDER BY created ASC, id ASC;
-- Query OK, 22199288 rows affected (10 min 52.02 sec)

code used to load data back into an infobright table:
SET AUTOCOMMIT=0;
SET @bh_dataformat = 'txt_variable';
LOAD DATA INFILE '/mnt/tmp/data/infiles/log_embed.csv' INTO TABLE log_embed FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' ESCAPED by '\\' LINES TERMINATED BY '\n';

sample data resulting with a load error:
186382,661597,157,"http","www","google.com","/\","http://www.google.com/\",1254075692
18101595,43152990,694,"mhtml",,,"file://C:\Documents and Settings\Admin\Desktop\Music Videos concert.mht","mhtml:file://C:\Documents and Settings\Admin\Desktop\Music Videos concert.mht",1279931578 

Profile

Buddoda!

Posted: 15 September 2010 12:51 AM

[ Ignore ] [ # 4 ]

Newbie

Total Posts: 28

Joined 2010-07-31

My so far experience(may be too little) dares to suggest the below mentioned steps.

Step 1 and 2 are applied (when u export data to a file subject to loading).

Step 1: Replace all \ with \\ . Step 2: Replace " with \". Step 3: Load with what I have suggested in my earlier post.

Forgive me, if my suggestion is wrong.

[ Edited: 15 September 2010 01:16 AM by Buddoda!]

Profile

Kethees Murugupillai

Posted: 16 September 2010 10:06 AM

[ Ignore ] [ # 5 ]

Administrator

Total Posts: 139

Joined 2008-08-18

You have to remove the two \ slashes in the first line of input data. It escapes the ” and make the load to fail. See below:

CREATE TABLE `t1` (

`a1` int,
`a2` int,
`a3` int,
`a4` char(55) COLLATE latin1_bin DEFAULT NULL,
`a5` char(55) COLLATE latin1_bin DEFAULT NULL,
`a6` char(140) COLLATE latin1_bin DEFAULT NULL,
`a7` char(100) COLLATE latin1_bin DEFAULT NULL,
`a8` char(100) COLLATE latin1_bin DEFAULT NULL,
`a9` bigint
) ENGINE=BRIGHTHOUSE DEFAULT CHARSET=latin1 COLLATE=latin1_bin;

input data:
186382,661597,157,“http”,“www”,“google.com”,”/”,“http://www.google.com/”,1254075692
18101595,43152990,694,“mhtml”,,,“file://C:\Documents and Settings\Admin\Desktop\Music Videos concert.mht”,“mhtml:file://C:\Documents and Settings\Admin\Desktop\Music Videos concert.mht”,1279931578

mysql> LOAD DATA INFILE ‘/tmp/a.txt’ INTO TABLE t1 FIELDS TERMINATED BY ‘,’ OPTIONALLY ENCLOSED BY ‘“’ ESCAPED by ‘\\’ LINES TERMINATED BY ‘\n’;
Query OK, 2 rows affected (0.02 sec)
Records: 2 Deleted: 0 Skipped: 0 Warnings: 0

mysql> select * from t1;
+—————+—————+———+———-+———+——————+——————————————————————————————————-+—————————————————————————————————————-+——————+
| a1 | a2 | a3 | a4 | a5 | a6 | a7 | a8 | a9 |
+—————+—————+———+———-+———+——————+——————————————————————————————————-+—————————————————————————————————————-+——————+
| 186382 | 661597 | 157 | http | www | google.com | / | http://www.google.com/ | 1254075692 |
| 18101595 | 43152990 | 694 | mhtml | | | file://C:Documents and SettingsAdminDesktopMusic Videos concert.mht | mhtml:file://C:Documents and SettingsAdminDesktopMusic Videos concert.mht | 1279931578 |
+—————+—————+———+———-+———+——————+——————————————————————————————————-+—————————————————————————————————————-+——————+
2 rows in set (0.00 sec)

Profile

‹‹ is there any specification for Infobright server? MySQL Query - Filter Out Duplicates before using Aggregate functions and Group By ››

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