Need help debugging a Vanilla 1 to Flarum migration

[red42]

Hi there, I'm trying to port my Vanilla 1 board to Flarum, but I'm running into some strange behaviors. Some fields seem to be NULL when going from the temporary (PORT_) tables to the Flarum table. For example (added some line breaks for readability):

Illuminate\Database\QueryException SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '0' for key 'FLA_discussions.PRIMARY' (SQL: insert into `FLA_discussions` 
(`created_at`, `first_post_id`, `id`, `is_locked`, `is_sticky`, `last_post_id`, `last_posted_at`, `last_posted_user_id`, `post_number_index`, `slug`, `title`, `user_id`) 
values 
(2009-09-12 18:38:29, 1, ?, ?, 0, ?, 2024-01-05 09:47:47, 25, ?, ?, ?, 1), 
(2009-09-12 18:45:21, 2, ?, ?, 0, ?, 2023-06-30 08:58:26, 33, ?, ?, ?, 1), 
(2009-09-13 08:33:05, 3, ?, ?, 0, ?, 2009-09-23 15:06:44, 65, ?, ?, ?, 2)...

Further down, the stack trace shows the actual data being inserted:

  0) Illuminate\Database\Connection->runQueryCallback('insert into `FLA_discussions` (`created_at`, `first_post_id`, `id`, `is_locked`, `is_sticky`, `last_post_id`, `last_posted_at`, `last_posted_user_id`, `post_number_index`, `slug`, `title`, `user_id`) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?), (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?), (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)',
['2009-09-12 18:38:29', '1', NULL, NULL, 0, NULL, '2024-01-05 09:47:47', '25', NULL, NULL, NULL, '1', '2009-09-12 18:45:21', '2', NULL, NULL, 0, NULL, '2023-06-30 08:58:26', '33', NULL, NULL, NULL, '1', '2009-09-13 08:33:05', '3', NULL, NULL, 0, NULL, '2009-09-23 15:06:44', '65', NULL, NULL, NULL, '2', 

As you can see, some fields are fine, like the dates, but others, like the discussion id, are NULL, which seems to cause the constraint violation.

I was able to print the query that retrieves the data from the Vanilla1 board, and running it on the database does return the expected values:

                SELECT d.*,
                    d.LastUserID as LastCommentUserID,
                    d.DateCreated as DateCreated2, d.AuthUserID as AuthUserID2,
                    c.Body,
                    c.FormatType as Format
                FROM VANILLA_Discussion d
                LEFT JOIN VANILLA_Comment c
                    ON d.FirstCommentID = c.CommentID
                WHERE coalesce(d.WhisperUserID, 0) = 0 and d.Active = 1;

So I'm not sure whether the data is not being inserted correctly into PORT_ or if something else is happening when going from PORT_ to Flarum. Can I dump the PORT_ data somehow, or something else I could try?

Thank you!

[red42]

Printing this query on Target/Flarum.php returns NULLs, so it seems to be some issue writing into PORT_Discussion.

        $query = $ex->dbImport()->table('PORT_Discussion')
            ->select(
                '*',
                $ex->dbImport()->raw('CountComments as post_number_index'),
                $ex->dbImport()->raw('DiscussionID as slug'),
                $ex->dbImport()->raw('0 as votes'),
                $ex->dbImport()->raw('0 as hotness')
            );

        print_r($query->get());

Sample row from above:

            [183] => stdClass Object
                (
                    [DiscussionID] => 
                    [Type] => 
                    [ForeignID] => 
                    [CategoryID] => 
                    [InsertUserID] => 35
                    [UpdateUserID] => 35
                    [FirstCommentID] => 201058
                    [LastCommentID] => 
                    [Name] => 
                    [Body] => Some text.
                    [Format] => Text
                    [Tags] => 
                    [CountComments] => 
                    [CountBookmarks] => 
                    [CountViews] => 
                    [Closed] => 
                    [Announce] => 0
                    [Sink] => 
                    [DateInserted] => 2021-04-18 11:26:16
                    [DateUpdated] => 2021-04-18 11:26:16
                    [InsertIPAddress] => 
                    [UpdateIPAddress] => 
                    [DateLastComment] => 2023-12-15 09:02:20
                    [LastCommentUserID] => 137
                    [Score] => 
                    [Attributes] => 
                    [RegardingID] => 
                    [GroupID] => 
                    [QnA] => 
                    [post_number_index] => 
                    [slug] => 
                    [votes] => 0
                    [hotness] => 0
                )

[red42]

The same row dumped from ExportModel::export():

        // Do the export.
        $query = $this->processQuery($query);
        $data = $this->executeQuery($query); // @todo Use new db layer.

        if($tableName == 'Discussion') {
            print_r($this->get($query));
        }

Produces:

    [183] => Array
        (
            [DiscussionID] => 184
            [AuthUserID] => 35
            [WhisperUserID] => 0
            [FirstCommentID] => 201058
            [LastUserID] => 137
            [Active] => 1
            [Closed] => 0
            [Sticky] => 0
            [Sink] => 0
            [Name] => Some discussion title
            [DateCreated] => 2021-04-18 11:26:16
            [DateLastActive] => 2023-12-15 09:02:20
            [CountComments] => 165
            [CategoryID] => 1
            [WhisperToLastUserID] => 
            [WhisperFromLastUserID] => 
            [DateLastWhisper] => 
            [TotalWhisperCount] => 0
            [LastCommentUserID] => 137
            [DateCreated2] => 2021-04-18 11:26:16
            [AuthUserID2] => 35
            [Body] => Some text.
            [Format] => Text
        )

[red42]

I found the issue!
The problem is in src/Storage.php:

    public function mapData(array $row, array $map): array
    {
        // @todo One of those moments I wish I had a collections library in here.
        foreach ($map as $src => $dest) {
            foreach ($row as $columnName => $value) {
                if ($columnName === $src) {
                    $row[$dest] = $value; // Add column with new name.
                    unset($row[$columnName]); // Remove old column.
                }
            }
        }

        return $row;
    }

When $dest and $columnName are the same, it's unsetting a column it should keep. So both Vanilla1 and PORT_ use UserID for the user's id column, so when the mapping occurs, we're setting $row['UserID'] as we should, but it's then unset right afterwards.

[red42]

I've opened a PR with a fix: #39