Not a bug Rebuild User Caches - Mysqli statement execute error - Incorrect integer value

[Mouth]

v1.5.5 | /admin.php?tools/rebuild | Rebuild User Caches | 100 items

Processes approximately 800 records, and then gives this error. Repeatable every time.

Admin Control Panel
Server Error
Mysqli statement execute error : Incorrect integer value: '' for column 'user_group_id' at row 2

1. Zend_Db_Statement_Mysqli->_execute() in Zend/Db/Statement.php at line 297
2. Zend_Db_Statement->execute() in Zend/Db/Adapter/Abstract.php at line 479
3. Zend_Db_Adapter_Abstract->query() in XenForo/DataWriter/User.php at line 1111
4. XenForo_DataWriter_User->rebuildUserGroupRelations() in XenForo/Deferred/User.php at line 38
5. XenForo_Deferred_User->execute() in XenForo/Model/Deferred.php at line 295
6. XenForo_Model_Deferred->runDeferred() in XenForo/Model/Deferred.php at line 429
7. XenForo_Model_Deferred->_runInternal() in XenForo/Model/Deferred.php at line 374
8. XenForo_Model_Deferred->run() in XenForo/ControllerAdmin/Tools.php at line 159
9. XenForo_ControllerAdmin_Tools->actionRunDeferred() in XenForo/FrontController.php at line 351
10. XenForo_FrontController->dispatch() in XenForo/FrontController.php at line 134
11. XenForo_FrontController->run() in /admin.php at line 13

mysql> select * from xf_user where user_group_id is null;
Empty set (0.00 sec)

mysql> select * from xf_user where user_group_id = '';
Empty set (0.07 sec)

mysql> select * from xf_user where user_group_id like ' ';
Empty set (0.07 sec)

mysql> select * from xf_user where user_group_id NOT REGEXP '^-?[0-9]+$';
Empty set (0.08 sec)

 

[Mike]

See here and my response: https://xenforo.com/community/threa...or-incorrect-integer-value.62980/#post-667787 I assume you used a non-official importer at some point?

 

[Mouth]

See here and my response: https://xenforo.com/community/threa...or-incorrect-integer-value.62980/#post-667787 I assume you used a non-official importer at some point?

No, only official importer.

You have bad data in the secondary_group_ids for some reason -- something blank. Have you manipulated data in the database directory or through non-official tools?
The key is to identify the user that's triggering this and try to sort the data out via something like phpMyAdmin. If you set the per page to 1, you may be able to see what the last ID is, and go to admin.php?users/123/edit and add/remove that user from a group (and then do the reverse). You'll probably want to do the user just after the last ID that you see before you get an error as well.

I have over 11,000 records in xf_user where secondary_group_ids = ''. Many users are not within any secondary groups, so I'd expect this to be normal?

Mysqli statement execute error : Incorrect integer value: '' for column 'user_group_id' at row 2

mysql> select user_id,username from xf_user where user_group_id = '';
Empty set (0.01 sec)

mysql> select user_id,username from xf_user where user_group_id is null;
Empty set (0.00 sec)

I now have within ACP "There are manual rebuild processes that have been stopped before completion. Click here to complete them." and am unable to do ACP functions such as rebuilds and add-on upgrades.

 

[Mouth]

I turned on MySQL general_log and this ([...] INSERT INTO xf_user_group_relation (user_id, user_group_id, is_primary) VALUES (16533, 2, 1),(16533, '', 0),(16533, '28', 0) [...]) appears to be the offender?
Not sure how to fix though?

               10053826 Prepare        INSERT INTO xf_user_group_relation

                                (user_id, user_group_id, is_primary)

                        VALUES

                                (16533, 2, 1),(16533, '', 0),(16533, '28', 0)

                        ON DUPLICATE KEY UPDATE

                                is_primary = VALUES(is_primary)

                10053826 Execute        INSERT INTO xf_user_group_relation

                                (user_id, user_group_id, is_primary)

                        VALUES

                                (16533, 2, 1),(16533, '', 0),(16533, '28', 0)

                        ON DUPLICATE KEY UPDATE

                                is_primary = VALUES(is_primary)

                10053826 Query  ROLLBACK

                10053826 Query  set autocommit=1

                10053826 Close stmt

                10053826 Prepare        INSERT INTO xf_deferred

                                        (execute_class, execute_data, unique_key, manual_execute, trigger_date)

                                VALUES

                                        (?, ?, ?, ?, ?)

                                ON DUPLICATE KEY UPDATE

                                        execute_class = VALUES(execute_class),

                                        execute_data = VALUES(execute_data),

                                        manual_execute = VALUES(manual_execute),

                                        trigger_date = VALUES(trigger_date)

                10053826 Execute        INSERT INTO xf_deferred

                                        (execute_class, execute_data, unique_key, manual_execute, trigger_date)

                                VALUES

                                        ('User', 'a:2:{s:8:\"position\";i:16530;s:5:\"batch\";s:3:\"100\";}', 'RebuildUser', '1', '1455102958')

                                ON DUPLICATE KEY UPDATE

                                        execute_class = VALUES(execute_class),

                                        execute_data = VALUES(execute_data),

                                        manual_execute = VALUES(manual_execute),

                                        trigger_date = VALUES(trigger_date)

                10053826 Close stmt

                10053826 Prepare        SELECT option_value FROM xf_option WHERE option_id = 'currentVersionId'

                10053826 Execute        SELECT option_value FROM xf_option WHERE option_id = 'currentVersionId'

                10053826 Close stmt

                10053826 Prepare        INSERT INTO `xf_error_log` (`exception_date`, `user_id`, `ip_address`, `exception_type`, `message`, `filename`, `line`, `trace_string`, `request_state`$

                10053826 Execute        INSERT INTO `xf_error_log` (`exception_date`, `user_id`, `ip_address`, `exception_type`, `message`, `filename`, `line`, `trace_string`, `request_state`$

                10053826 Close stmt

                10053826 Prepare        INSERT INTO `xf_admin_log` (`request_date`, `user_id`, `ip_address`, `request_url`, `request_data`) VALUES (?, ?, ?, ?, ?)

                10053826 Execute        INSERT INTO `xf_admin_log` (`request_date`, `user_id`, `ip_address`, `request_url`, `request_data`) VALUES ('1455265687', '14584', '6\'', 'tools/run-$

                10053826 Close stmt

 

[Mike]

My comment of the field being blank was probably incorrect -- it would be a leading or a trailing comma. Since you have the user ID (16533), you should be able to check that and remove it.

I don't know how that got there though, unless there was some manual manipulation of the value. We haven't seen this tracked back to an XF bug before (and the code would generally prevent it).

 

[Mouth]

My comment of the field being blank was probably incorrect -- it would be a leading or a trailing comma. Since you have the user ID (16533), you should be able to check that and remove it.
I don't know how that got there though, unless there was some manual manipulation of the value. We haven't seen this tracked back to an XF bug before (and the code would generally prevent it).

Thanks. Nothing I'm aware of that would be manually manipulating values, in any table. Add-ons only.
Just cannot seem to find this errant value .....

mysql> select * from xf_user_group_relation where user_group_id like ',%';
Empty set (0.02 sec)

mysql> select * from xf_user_group_relation where user_group_id like '%,';
Empty set (0.01 sec)

mysql> select * from xf_user where user_group_id like '%,';
Empty set (0.05 sec)

mysql> select * from xf_user where user_group_id like ',%';
Empty set (0.07 sec)

mysql> select * from xf_user where user_id = 16533;
+---------+----------+--------------------+--------+--------------+-------------+----------+------------------+---------+---------------+---------------------+------------------------+---------------------------+---------------+----------------------+---------------+---------------+---------------+---------------+-------------+--------------+---------------+----------+------------+--------------+----------+-----------+------------+----------------+-----------------+---------------------+----------+---------------------+----------------+-----------------------+------------------+----------------+-----------------------+
| user_id | username | email              | gender | custom_title | language_id | style_id | timezone         | visible | user_group_id | secondary_group_ids | display_style_group_id | permission_combination_id | message_count | conversations_unread | register_date | last_activity | trophy_points | alerts_unread | avatar_date | avatar_width | avatar_height | gravatar | user_state | is_moderator | is_admin | is_banned | like_count | warning_points | sportsbook_cash | user_last_post_date | is_staff | competition_entries | showcase_count | cta_ft_featured_count | activity_visible | resource_count | weekly_digest_opt_out |
+---------+----------+--------------------+--------+--------------+-------------+----------+------------------+---------+---------------+---------------------+------------------------+---------------------------+---------------+----------------------+---------------+---------------+---------------+---------------+-------------+--------------+---------------+----------+------------+--------------+----------+-----------+------------+----------------+-----------------+---------------------+----------+---------------------+----------------+-----------------------+------------------+----------------+-----------------------+
|   16533 | <redacted>      | <redacted> | male   |              |           3 |       15 | Australia/Sydney |       1 |             2 |                     |                      2 |                         2 |             0 |                    0 |    1124839602 |    1124839648 |             0 |             0 |           0 |            0 |             0 |          | valid      |            0 |        0 |         0 |          0 |              0 |          200.00 |                   0 |        0 |                     |              0 |                     0 |                1 |              0 |                     0 |
+---------+----------+--------------------+--------+--------------+-------------+----------+------------------+---------+---------------+---------------------+------------------------+---------------------------+---------------+----------------------+---------------+---------------+---------------+---------------+-------------+--------------+---------------+----------+------------+--------------+----------+-----------+------------+----------------+-----------------+---------------------+----------+---------------------+----------------+-----------------------+------------------+----------------+-----------------------+
1 row in set (0.00 sec)

mysql> select * from xf_user_group_relation where user_id = 16533;
+---------+---------------+------------+
| user_id | user_group_id | is_primary |
+---------+---------------+------------+
|   16533 |             2 |          1 |
+---------+---------------+------------+
1 row in set (0.00 sec)

 

[Mike]

The secondary_group_ids value doesn't seem to correspond with what's being reported as being inserted (it's inserting a record for group 28). I'm not sure why though. At this point, I'd probably need FTP (and ACP) access to resolve the issue -- though ultimately the issue is likely to be how invalid data got in there initially.

 

[Mouth]

Thanks, the information and thoughts helped me resolve this. xf_user.secondary_groups_ids containing leading comma (g. ",28") values.
No idea how they came to be, as there's been no manual DB manipulation for those.
Please feel free to close the report with prefix 'not a bug'.