Porter from VB4 to Vanilla.

MrCaspan

So I have exported the VB forum perfectly fine but when trying to import it I am getting errors all over the place. It's not using proper encoding for the text so that when you try to import it it throws errors.

I have to manually search the Dicussion.txt file for these special CHRs and replace them with their HTML equivalent.

I have had to manually manipulate the User.txt file because birthdates were throwing errors like 1979-00-00 is not a valid date, which is right but how did it get into the database wrong or when it was exported why did it not fix it?

Not its telling me that my permissions are \N for everything and they can only be NULL 0 or 1? should \N be null? I'm not sure why this is breaking so bad...

Is Google Cloud SQL just being too strict and causing these issues? I have turned off innodb strict mode off and SQL mode is traditional and I have also tried no mode set at all either

Anyone else work with Google Cloud MySQL and ever dealt with these issues?

MrCaspan

There are some large bugs in the importer one of them is the resume position. For some reason my New database would not take special chrs and they were not exported properly so when importing the database throws an error that it doe snot like this kind of text. SO you fix the issue and when you resume the file byte count has change so there for so has the position where to resume from. You should be using line numbers to resume from not byte count to resume at or else all the data is shifted and wrong!

What a PITA i spend like 20 hours on this import only to find out this error!

MrCaspan

So can anyone help me make sure the export is happening using UTF8mb4 ? so that when I am importing it does not run into these restriction

R_J

I would try to convert the existing database to UTF8mb4 first and then run the export tool. Sounds more doable to me...

Bleistivt

Editing the exported files by hand is probably not recommended.

Also, Porter should recognize the charset by itself. Still, you could try harcoding it on line 14224 (Porter 2.5):

$characterSet = 'utf8mb4';

MrCaspan

I just found out that this MySQL Server I am exporting form is 4.9.... So Latin was the default data set

Sigh... I need to convert this database to utf8mdb if its even possible on this version of MySQL! I might have to export it form 4.9 to 5.7 then convert it to utf8mb4 then export it...

MrCaspan

Also it seems the it exports NULLs as \N in the export file... Problem is when you import it its converting \N to '' which is '{Blank}'

So SQL is throwing a hissy fit because i am trying to import a '' into an tinyint field It shoudl be converting \N to NULLs not '' can anyone tell where this is happening in the import script class.importmodel.php ? I only see one line for NULL which was line 28 but this constant is never referenced again in the file

class.importmodel.php:28 below

  /** Null character in the import file. */
  const NULL = '\N';

MrCaspan

So i did some MySQL CLI testing and ran the following command with the Permission.txt file

load data LOCAL infile '/home/mistercaspan/Permission.txt' into table GDN_zPermission character set utf8mb4 columns terminated by ',' optionally enclosed by '"' escaped by '\\' lines terminated by '\n' ignore 1 lines;

This command loaded the Permission.txt file perfectly fine and \N was replaced with NULLs when I looed at the data. No SQL errors nothing. But when I try the same file with class.importmodel.php it gives me the error

Incorrect integer value: '' for column 'Garden.Profiles.View' at row 1

So why is it I can run the exact same SQL statement on CLI except for the LOCAL part as you need that for CLI and when the import script runs it the \N turns to '{Blank}'

Any ideas anyone?

PS here is a copy of my Permission.txt fie and a copy of the error I get

Permission.txt

MrCaspan

Found the bug in porter

In the function fGetCSV2 in class.importmodel.php you will see that it replaces \N with a proper null. If you do a var_dump() of the $results before it is returned the array with have proper NULL values in it. Once it is returned to the loadTableInsert as $row in the while loop this command is preformed on it

$row = array_map('trim', $row);

If you look at the trim command on PHP.net the VERY FIRST COMMENT from 13 years ago SAYS

It may be useful to know that trim() returns an empty string when the argument is an unset/null variable.

so basically it turned all NULL values in the array to '' and as we all know '' is not a NULL!!!!!!

So there are a few ways to fix this

Option 1

Go to the fGetCSV2 function in applications/dashboard/models/class.importmodel.php and replace the line

$token = null;

with

$token = 'null';

You have now made the null into a string and not a null value. Problem is SQL will see this as a literal string with the word null in it. So jsut before the SQL input we have to search and replace 'null' with null so SQL sees them as a NULL

So in the loadTableInsert function in applications/dashboard/models/class.importmodel.php search for

$inserts .= '('.implode(',', $row).')';

and add so it looks like so

$inserts .= '('.implode(',', $row).')';
$inserts = str_replace("'null'", "null", $inserts);

This will then fix the issue!!!

HOLLY HELL this bug took me like 5 days to find!!!

MrCaspan

I have submitting this issue to GitHub

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

Linc

It's important to put issues like this on the correct repo: https://github.com/vanilla/porter

If you're able to suggest a solution via PR, that's even better. I skimmed this discussion but it sounded like you found quite a deep one. 😊

MrCaspan

this one is on the import side of it not the export so i reported it to the Vanilla github.. I even submitted a fix for it :) look at me using github for the first time!!