1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

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

Discussion in 'Resolved Bug Reports' started by Mouth, Feb 10, 2016.

  1. Mouth

    Mouth Well-Known Member

    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

    Code:
    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)
    
     
  2. Mike

    Mike XenForo Developer Staff Member

  3. Mouth

    Mouth Well-Known Member

    No, only official importer.

    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?

    Code:
    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.
     
    Last edited: Feb 12, 2016
  4. Mouth

    Mouth Well-Known Member

    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?

    Code:
                   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
    
    
     
  5. Mike

    Mike XenForo Developer Staff Member

    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).
     
  6. Mouth

    Mouth Well-Known Member

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

    Code:
    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)
    
    Code:
    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)
    Code:
    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)
    
     
  7. Mike

    Mike XenForo Developer Staff Member

    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.
     
  8. Mouth

    Mouth Well-Known Member

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

Share This Page