XF 2.1 XF\Db\InvalidQueryException: MySQL statement prepare error [1064]

[rmedia]

Hi,

I'm trying to Import to newest Xenforo from vBulletin 4.2.3. In th middle I have got such Error. Do you have any ideas how to solve the issue?

Server version: 10.3.18-MariaDB-0+deb10u1 Debian 10
PHP 7.2.27-5+0~20200202.35+debian10

XF\Db\InvalidQueryException: MySQL statement prepare error [1064]: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'deletionlog.userid AS deleteuserid, deletionlog.username AS deleteusername, ' at line 3 src/XF/Db/AbstractStatement.php:228

SELECT thread.*,
IF(user.username IS NULL, thread.postusername, user.username) AS postusername
deletionlog.userid AS deleteuserid,
deletionlog.username AS deleteusername,
deletionlog.reason AS deletereason,
deletionlog.dateline AS deletedate
FROM vb_thread AS
thread
LEFT JOIN vb_user AS
user ON (user.userid = thread.postuserid)
LEFT JOIN vb_deletionlog AS
deletionlog ON (deletionlog.primaryid = thread.threadid AND deletionlog.type = 'thread')
INNER JOIN vb_forum AS
forum ON (forum.forumid = thread.forumid AND forum.link = '' AND forum.options & 4)
WHERE thread.threadid > ? AND thread.threadid <= ?
AND thread.open <> 10
ORDER BY thread.threadid
LIMIT 1000

------------

#0 src/XF/Db/Mysqli/Statement.php(196): XF\Db\AbstractStatement->getException('MySQL statement...', 1064, '42000')
#1 src/XF/Db/Mysqli/Statement.php(39): XF\Db\Mysqli\Statement->getException('MySQL statement...', 1064, '42000')
#2 src/XF/Db/Mysqli/Statement.php(54): XF\Db\Mysqli\Statement->prepare()
#3 src/XF/Db/AbstractAdapter.php(94): XF\Db\Mysqli\Statement->execute()
#4 src/XF/Db/AbstractAdapter.php(165): XF\Db\AbstractAdapter->query('
SELECT thre...', Array)
#5 src/addons/XFI/Import/Importer/vBulletin.php(3063): XF\Db\AbstractAdapter->fetchAllKeyed('
SELECT thre...', 'threadid', Array)
#6 src/addons/XFI/Import/Importer/vBulletin.php(2962): XFI\Import\Importer\vBulletin->getThreads(0, 10767, 1000)
#7 src/XF/Import/Runner.php(231): XFI\Import\Importer\vBulletin->stepThreads(Object(XF\Import\StepState), Array, 8)
#8 src/XF/Import/Runner.php(59): XF\Import\Runner->runStep('threads', Object(XF\Import\StepState), 8)
#9 src/XF/Import/Runner.php(151): XF\Import\Runner->run()
#10 src/XF/Import/Runner.php(128): XF\Import\Runner->runUntilCompleteInternal(Object(XF\Import\Manager), Object(Closure))
#11 src/XF/Cli/Command/Import.php(144): XF\Import\Runner->runUntilComplete(Object(XF\Import\Manager), Object(Closure))
#12 src/vendor/symfony/console/Command/Command.php(255): XF\Cli\Command\Import->execute(Object(Symfony\Component\Console\Input\ArgvInput), Object(Symfony\Component\Console\Output\ConsoleOutput))
#13 src/vendor/symfony/console/Application.php(982): Symfony\Component\Console\Command\Command->run(Object(Symfony\Component\Console\Input\ArgvInput), Object(Symfony\Component\Console\Output\ConsoleOutput))
#14 src/vendor/symfony/console/Application.php(255): Symfony\Component\Console\Application->doRunCommand(Object(XF\Cli\Command\Import), Object(Symfony\Component\Console\Input\ArgvInput), Object(Symfony\Component\Console\Output\ConsoleOutput))
#15 src/vendor/symfony/console/Application.php(148): Symfony\Component\Console\Application->doRun(Object(Symfony\Component\Console\Input\ArgvInput), Object(Symfony\Component\Console\Output\ConsoleOutput))
#16 src/XF/Cli/Runner.php(63): Symfony\Component\Console\Application->run(Object(Symfony\Component\Console\Input\ArgvInput), Object(Symfony\Component\Console\Output\ConsoleOutput))
#17 cmd.php(15): XF\Cli\Runner->run()
#18 {main}

 

[Chris D]

You created a bug report for this then deleted it, but I think it is actually a bug.

Can you try editing a file for us?

Edit src/addons/XFI/Import/Importer/vBulletin.php.

Find:

IF(user.username IS NULL, thread.postusername, user.username) AS postusername

Replace with:

IF(user.username IS NULL, thread.postusername, user.username) AS postusername,

(Basically add a comma at the end of that line).

Does that allow you to continue? Do you face any other issues?

 

[markw365]

Not sure if it's a bug report or not, but your varchar for delete_reason in xf_deletion_log is only 100 characters and our system blew it up. I resized it to 200 and it resumed. Still importing. I also found a bug with the missing comma in the importer.