Users running a non-download version of Vanilla (pulled from github), on branch release/2019.016 or master from the last 2 weeks should upgrade to release/2019.017 or latest master for security reasons. Downloaded official open sources releases are not affected.

Porter from VB4 to Vanilla.

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?

Comments

  • MrCaspanMrCaspan New
    edited March 23

    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!

  • 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_JR_J Cheerleader & Troubleshooter Munich Moderator

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


  • BleistivtBleistivt MVP
    edited March 24

    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';

  • 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...

  • MrCaspanMrCaspan New
    edited March 25

    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';
    


  • MrCaspanMrCaspan New
    edited March 25

    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



  • 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!!!

  • I have submitting this issue to GitHub

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

    R_JKaspar
Sign In or Register to comment.