Import from vBulletin issue with quotes in salt

lfolco

I am trying to export a vBulletin 4 database into a Vanailla 2.1.5 fresh installation using Vanilla Porter. I'm running into an issue where there are quotes in the salt field, which is causing the fields to not be delineated correctly. Getting this error message:

Row 1 doesn't contain data for all columns|Gdn_Database|Query|load data infile '[path to file]User.txt' into table GDN_zUser character set utf8 columns terminated by ',' optionally enclosed by '"' escaped by '\' lines terminated by '\n' ignore 1 lines

Has anyone else run into this issue?

Linc

Ding. Get rid of STRICT_TRANS_TABLES.

x00

You can file an issue here

https://github.com/vanilla/porter/issues

Linc

@lfolco The porter should escape quotes. I've never seen this issue before. Are you using the latest version of the porter?

lfolco

yes, i'm using the latest version. when i look at the files that vanilla is using to import, i'm seeing that the quotes are escaped with backslashes, but still getting the error.

peregrine

I'm running into an issue where there are quotes in the salt field, which is causing the fields to not be delineated correctly

can you post an example of a line from user.txt with improper columns.

you can change sensitive info - but note which column you changed,

what version number is the latest version. your idea of latest might not coincide with lincoln's concept of latest.

always good to post version numbers or programs (i.e. porter) so you know your in the same frame of reference.

lfolco

I'm wondering if it's actually quotes now: i pared down the user file and tried to run it directly in mysql and i'm getting the same error:

UserID:int,Name:varchar(20),Email:varchar(200),Title:varchar(100),RankID:int,HourOffset:int,LastIPAddress:varchar(15),InviteUserID:int,InsertIPAddress:varchar(15),Password:varbinary(100),DateOfBirth:datetime,DateFirstVisit:datetime,DateLastActive:datetime,DateInserted:datetime,DateUpdated:datetime,Photo:varchar(255),Banned:tinyint,HashMethod:varchar(10) "1","Admin","test@example.com","Administrator",1,"0","","0","","cdce1bf79f807370a1ade1749d64|~z/zY~eUIklQ}0^s!27O]tZ=8qfGe","0000-00-00","2014-07-15 08:18:05","2014-10-24 08:10:52","2014-07-15 08:18:05","2014-10-27 04:57:36",\N,"0","vbulletin"

peregrine

out of curiosity change

the \N

to ""

and try it.

its the only one without quotes.

lfolco

I tried that! Didn't work

peregrine

maybe lincoln would let you send him your database, to see if it is a problem with porter.

lincoln and x00 are the porter experts.

peregrine

@lfolco said:
I'm wondering if it's actually quotes now: i pared down the user file and tried to run it directly in mysql and i'm getting the same error:

UserID:int,Name:varchar(20),Email:varchar(200),Title:varchar(100),RankID:int,HourOffset:int,LastIPAddress:varchar(15),InviteUserID:int,InsertIPAddress:varchar(15),Password:varbinary(100),DateOfBirth:datetime,DateFirstVisit:datetime,DateLastActive:datetime,DateInserted:datetime,DateUpdated:datetime,Photo:varchar(255),Banned:tinyint,HashMethod:varchar(10) "1","Admin","test@example.com","Administrator",1,"0","","0","","cdce1bf79f807370a1ade1749d64|~z/zY~eUIklQ}0^s!27O]tZ=8qfGe","0000-00-00","2014-07-15 08:18:05","2014-10-24 08:10:52","2014-07-15 08:18:05","2014-10-27 04:57:36",\N,"0","vbulletin"

you would get a different error, since line 1 can't be used depending on how you import manually and the create table database structure has to exist.

maybe lincoln would let you send him your database, to see if it is a problem with porter.

lincoln and x00 are the porter experts.

Linc

@lfolco What is the exact, entire query you attempted to run manually?

lfolco

LOAD DATA INFILE '[path]/vanilla/uploads/import/User.txt' INTO TABLE GDN_zUser CHARACTER SET utf8 COLUMNS
TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' ESCAPED BY '\' LINES TERMINATED BY '\n' IGNORE 1 LINES;

Linc

What is the structure of your GDN_zUser? Can you give me the CREATE TABLE syntax?

lfolco

CREATE TABLE GDN_zUser ( UserID int(11) DEFAULT NULL, Name varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL, Email varchar(200) COLLATE utf8_unicode_ci DEFAULT NULL, Title varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL, RankID int(11) DEFAULT NULL, HourOffset int(11) DEFAULT NULL, LastIPAddress varchar(39) COLLATE utf8_unicode_ci DEFAULT NULL, InviteUserID int(11) DEFAULT NULL, InsertIPAddress varchar(39) COLLATE utf8_unicode_ci DEFAULT NULL, Password varbinary(100) DEFAULT NULL, DateOfBirth datetime DEFAULT NULL, DateFirstVisit datetime DEFAULT NULL, DateLastActive datetime DEFAULT NULL, DateInserted datetime DEFAULT NULL, DateUpdated datetime DEFAULT NULL, Photo varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL, Banned tinyint(4) DEFAULT NULL, HashMethod varchar(10) COLLATE utf8_unicode_ci DEFAULT NULL, _NewID int(11) DEFAULT NULL, _Action enum('Insert','Update') COLLATE utf8_unicode_ci DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

peregrine

you are missing these in your columns info aren't you?

 _NewID int(11) DEFAULT NULL,
_Action enum('Insert','Update') COLLATE utf8_unicode_ci DEFAULT NULL

but then again I know nothing about porter, so I may be running interference.

lfolco

yep! i suspected that would be it, but i'm not familiar enough with vanilla yet to know for sure. the table gets created whenever i start the import. when i cancel/restart the import, the table gets deleted (along with a few other GDN_z* tables).

i tried doing the sql import, but it complained that i was missing the GDN_zUser table.

Linc

The additional columns should be irrelevant because they have a valid default set.

This is the correct SQL query:

LOAD DATA INFILE '/Users/Linc/Desktop/Users.txt' INTO TABLE GDN_zUser CHARACTER SET utf8 COLUMNS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' ESCAPED BY '\\' LINES TERMINATED BY '\n' IGNORE 1 LINES;

Notice the double \. Otherwise you are escaping the terminating single quote.

The raw code is:

$Sql = "load data infile $Path into table $Tablename
         character set utf8
         columns terminated by ','
         optionally enclosed by '\"'
         escaped by '\\\\'
         lines terminated by '\\n'
         ignore 1 lines";
      $this->Query($Sql);

Notice the \\ which should cycle down to just \ for SQL. How you end up with only a single backslash is beyond my depth for guessing off the top of my head.

lfolco

When i run the correct sql query, though, it doesn't work; i get the same error message.

Linc

Are you importing on a Windows box?

lfolco

no, a mac.

Linc

Try these things, 1 at a time:

• Check your txt file for extra line breaks.
• Change the \N (for the Photo field) to "".
• Change lines terminated by '\n' to lines terminated by '\r\n'.