Fixed Error with importing IPS 4.1x to XF2

[Jeremy W.]

• XF\Db\Exception: MySQL query error [1366]: Incorrect integer value: '_default0' for column db_xf.xf_user.user_id at row 1
• src/XF/Db/AbstractStatement.php:217
• Generated by: admin
• Mar 18, 2019 at 7:07 PM

Stack trace
INSERT INTO xf_user (user_id, username, email, style_id, language_id, timezone, visible, activity_visible, user_group_id, secondary_group_ids, display_style_group_id, permission_combination_id, message_count, alerts_unread, conversations_unread, register_date, last_activity, trophy_points, avatar_date, avatar_width, avatar_height, avatar_highdpi, gravatar, user_state, is_moderator, is_admin, is_staff, is_banned, reaction_score, custom_title, warning_points, secret_key, privacy_policy_accepted, terms_accepted) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
------------

#0 src/XF/Db/Mysqli/Statement.php(196): XF\Db\AbstractStatement->getException('MySQL query err...', 1366, '22007')
#1 src/XF/Db/Mysqli/Statement.php(77): XF\Db\Mysqli\Statement->getException('MySQL query err...', 1366, '22007')
#2 src/XF/Db/AbstractAdapter.php(94): XF\Db\Mysqli\Statement->execute()
#3 src/XF/Db/AbstractAdapter.php(218): XF\Db\AbstractAdapter->query('INSERT INTO `x...', Array)
#4 src/XF/Import/Data/EntityEmulator.php(326): XF\Db\AbstractAdapter->insert('xf_user', Array)
#5 src/XF/Import/Data/User.php(201): XF\Import\Data\EntityEmulator->insert('_default0', Object(XF\Db\Mysqli\Adapter))
#6 src/XF/Import/Data/AbstractData.php(126): XF\Import\Data\User->write('_default0')
#7 src/XF/Import/Helper.php(107): XF\Import\Data\AbstractData->save('_default0')
#8 src/XF/Import/Importer/AbstractCoreImporter.php(107): XF\Import\Helper->importUser('_default0', Object(XF\Import\Data\User), Array)
#9 src/addons/XFI/Import/Importer/IpsForums.php(710): XF\Import\Importer\AbstractCoreImporter->importUser('_default0', Object(XF\Import\Data\User), Array)
#10 src/XF/Import/Runner.php(231): XFI\Import\Importer\IpsForums->stepUsers(Object(XF\Import\StepState), Array, 8)
#11 src/XF/Import/Runner.php(59): XF\Import\Runner->runStep('users', Object(XF\Import\StepState), 8)
#12 src/XF/Import/Runner.php(151): XF\Import\Runner->run()
#13 src/XF/Import/Runner.php(128): XF\Import\Runner->runUntilCompleteInternal(Object(XF\Import\Manager), Object(Closure))
#14 src/XF/Cli/Command/Import.php(144): XF\Import\Runner->runUntilComplete(Object(XF\Import\Manager), Object(Closure))
#15 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))
#16 src/vendor/symfony/console/Application.php(953): Symfony\Component\Console\Command\Command->run(Object(Symfony\Component\Console\Input\ArgvInput), Object(Symfony\Component\Console\Output\ConsoleOutput))
#17 src/vendor/symfony/console/Application.php(248): Symfony\Component\Console\Application->doRunCommand(Object(XF\Cli\Command\Import), Object(Symfony\Component\Console\Input\ArgvInput), Object(Symfony\Component\Console\Output\ConsoleOutput))
#18 src/vendor/symfony/console/Application.php(148): Symfony\Component\Console\Application->doRun(Object(Symfony\Component\Console\Input\ArgvInput), Object(Symfony\Component\Console\Output\ConsoleOutput))
#19 src/XF/Cli/Runner.php(63): Symfony\Component\Console\Application->run(Object(Symfony\Component\Console\Input\ArgvInput), Object(Symfony\Component\Console\Output\ConsoleOutput))
#20 cmd.php(15): XF\Cli\Runner->run()
#21 {main}
Request state
array(1) {
["cli"] => string(17) "cmd.php xf:import"
}

 

[Paul B]

Are there any records in the IPS database which have a non numeric value for the user ID?

 

[Jeremy W.]

Are there any records in the IPS database which have a non numeric value for the user ID?

Thanks for the reply. No, the first member_id in core_members for IPS is 1, however I see they merged pp_(i believe pp_members_profile) to core_members table. Guessing error is coming from there?

 

[Chris D]

I think this is due to a dodgy order in the MySQL query so the member_id is being overwritten with a NULL value.

Ideally to fix this for now you'd need to edit the file src/addons/XFI/Import/Importer/IpsForums.php.

Line 680 onwards looks like:

SELECT
   members.*,
   moderator.*,
   pfields_content.*,
   uapr.row_perm_cache AS user_row_perm_cache,
   gapr.row_perm_cache AS group_row_perm_cache

Change it to:

SELECT
   moderator.*,
   pfields_content.*,
   uapr.row_perm_cache AS user_row_perm_cache,
   gapr.row_perm_cache AS group_row_perm_cache,
   members.*

That should then work.

 

[XF Bug Bot]

Thank you for reporting this issue. It has now been resolved and we are aiming to include it in a future XFI release (1.2.1).

Change log:

Prevent query error when importing using the IpsForums importer.

Any changes made as a result of this issue being resolved may not be rolled out here until later.