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

XF 1.2 Unknown column 'user.is_staff' in 'where clause'

Discussion in 'Troubleshooting and Problems' started by Sheldon, Jun 20, 2013.

  1. Sheldon

    Sheldon Well-Known Member

    Upgraded a very slow site, more of a test/play area.

    Go to AdminCP, attempt to check box on a user to "Show as Staff", get this error:

    Code:
    Zend_Db_Statement_Mysqli_Exception: Mysqli prepare error: Unknown column 'is_staff' in 'field list' - library/Zend/Db/Statement/Mysqli.php:77
    Generated By: Sheldon, 1 minute ago
    Stack Trace
    #0 /home2/familygu/public_html/library/Zend/Db/Statement.php(115): Zend_Db_Statement_Mysqli->_prepare('UPDATE `xf_user...')
    #1 /home2/familygu/public_html/library/Zend/Db/Adapter/Mysqli.php(381): Zend_Db_Statement->__construct(Object(Zend_Db_Adapter_Mysqli), 'UPDATE `xf_user...')
    #2 /home2/familygu/public_html/library/Zend/Db/Adapter/Abstract.php(478): Zend_Db_Adapter_Mysqli->prepare('UPDATE `xf_user...')
    #3 /home2/familygu/public_html/library/Zend/Db/Adapter/Abstract.php(632): Zend_Db_Adapter_Abstract->query('UPDATE `xf_user...', Array)
    #4 /home2/familygu/public_html/library/XenForo/DataWriter.php(1623): Zend_Db_Adapter_Abstract->update('xf_user', Array, 'user_id = 1')
    #5 /home2/familygu/public_html/library/XenForo/DataWriter.php(1592): XenForo_DataWriter->_update()
    #6 /home2/familygu/public_html/library/XenForo/DataWriter.php(1388): XenForo_DataWriter->_save()
    #7 /home2/familygu/public_html/library/XenForo/ControllerAdmin/User.php(510): XenForo_DataWriter->save()
    #8 /home2/familygu/public_html/library/XenForo/FrontController.php(335): XenForo_ControllerAdmin_User->actionSave()
    #9 /home2/familygu/public_html/library/XenForo/FrontController.php(132): XenForo_FrontController->dispatch(Object(XenForo_RouteMatch))
    #10 /home2/familygu/public_html/admin.php(13): XenForo_FrontController->run()
    #11 {main}
    
    Banners won't appear for Staff at all.

    When checking Member List, receive this error:
    Code:
    Zend_Db_Statement_Mysqli_Exception: Mysqli prepare error: Unknown column 'user.is_staff' in 'where clause' - library/Zend/Db/Statement/Mysqli.php:77
    Generated By: Sheldon, 3 minutes ago
    Stack Trace
    #0 /home2/familygu/public_html/library/Zend/Db/Statement.php(115): Zend_Db_Statement_Mysqli->_prepare('?????SELECT use...')
    #1 /home2/familygu/public_html/library/Zend/Db/Adapter/Mysqli.php(381): Zend_Db_Statement->__construct(Object(Zend_Db_Adapter_Mysqli), '?????SELECT use...')
    #2 /home2/familygu/public_html/library/Zend/Db/Adapter/Abstract.php(478): Zend_Db_Adapter_Mysqli->prepare('?????SELECT use...')
    #3 /home2/familygu/public_html/library/XenForo/Model.php(219): Zend_Db_Adapter_Abstract->query('?????SELECT use...', Array, 2)
    #4 /home2/familygu/public_html/library/XenForo/Model/User.php(297): XenForo_Model->fetchAllKeyed('?????SELECT use...', 'user_id')
    #5 /home2/familygu/public_html/library/XenForo/ControllerPublic/Member.php(46): XenForo_Model_User->getUsers(Array, Array)
    #6 /home2/familygu/public_html/library/XenForo/FrontController.php(335): XenForo_ControllerPublic_Member->actionIndex()
    #7 /home2/familygu/public_html/library/XenForo/FrontController.php(132): XenForo_FrontController->dispatch(Object(XenForo_RouteMatch))
    #8 /home2/familygu/public_html/index.php(13): XenForo_FrontController->run()
    #9 {main}
    
     
  2. Mike

    Mike XenForo Developer Staff Member

    Code:
                ALTER TABLE  `xf_user`
                    ADD `is_staff` TINYINT UNSIGNED NOT NULL DEFAULT  '0',
                    ADD INDEX message_count (  `message_count` ),
                    ADD INDEX trophy_points (  `trophy_points` ),
                    ADD INDEX like_count (  `like_count` ),
                    ADD INDEX register_date (  `register_date` ),
                    ADD INDEX `staff_username` (  `is_staff` ,  `username` )
    That query failed, probably because one of those indexes already existed. Check your xf_user table to see.
     
    SneakyDave and Jake Bunce like this.
  3. Sheldon

    Sheldon Well-Known Member

    Ran this, corrected issue:
    Code:
                ALTER TABLE  `xf_user`
                    ADD `is_staff` TINYINT UNSIGNED NOT NULL DEFAULT  '0',
                    ADD INDEX message_count (  `message_count` ),
                    ADD INDEX trophy_points (  `trophy_points` ),
                    ADD INDEX like_count (  `like_count` ),
                    ADD INDEX `staff_username` (  `is_staff` ,  `username` )
    Just curious @Mike any reason the query would fail? Something on my end? An add-on?
     
  4. Brogan

    Brogan XenForo Moderator Staff Member

    Do you have any add-ons installed which may have modified the xf_user table?
     
  5. Mike

    Mike XenForo Developer Staff Member

    You had an unexpected (non standard) schema (though just an index in this case).
     
  6. Sheldon

    Sheldon Well-Known Member

    I don't think I have any that alter it.

    add-ons.png
     
  7. Chris D

    Chris D XenForo Developer Staff Member

    Xen Media Gallery adds a column to xf_user.

    But that's not an uncommon thing for an add-on to do.
     
  8. Mike

    Mike XenForo Developer Staff Member

    Based on the differences in the query, something added an index to xf_user.register_date.

    It's a balance. We could run a separate query for each of these updates and it'd be more resilient. However, it would literally take 6 times as long (and it can be a fairly big table...).
     
  9. Jake Bunce

    Jake Bunce XenForo Moderator Staff Member

    What about using SHOW INDEX and SHOW COLUMNS to find what exists, then use the results to build a single SQL query to add the remaining alterations.
     
  10. Mike

    Mike XenForo Developer Staff Member

    That'd be a much much larger change and something that won't happen now.
     
  11. Mouth

    Mouth Well-Known Member

  12. Chris D

    Chris D XenForo Developer Staff Member

  13. Bob

    Bob Well-Known Member

    Nope :)
     
    SneakyDave likes this.
  14. Mouth

    Mouth Well-Known Member

    Here's my /library/ with a grep search on register_date (partial results, due to 1,000 character limit when posting) ...

    Code:
    root@ubuntu-server:/var/www/dev/library# grep -i -r register_date
    ExtraStats/Model/User.php:        $registeredToday = $this->countUsers(array('register_date' => array('>', $dateCut)));
    ExtraStats/Model/User.php:        if (!empty($conditions['register_date']) && is_array($conditions['register_date'])) {
    ExtraStats/Model/User.php:            list($operator, $cutOff) = $conditions['register_date'];
    ExtraStats/Model/User.php:            $sqlConditions[] = "user.register_date $operator " . $db->quote($cutOff);
    ExtraStats/Installer.php:            $existed = $db->fetchOne("SHOW INDEX FROM `xf_user` WHERE COLUMN_NAME = 'register_date'");
    ExtraStats/Installer.php:                $db->query("ALTER TABLE `xf_user` ADD INDEX `register_date` (`register_date`)");
    ExtraStats/ControllerPublic/Misc.php:        return $this->_actionExtraStats('registeredToday', 'register_date');
    WidgetFramework/addon-widget_framework.xml:    <xen:option value="register_date">{xen:phrase wf_register_date}</xen:option>
    WidgetFramework/addon-widget_framework.xml:    <phrase title="wf_register_date" version_id="1" version_string="0.9"><![CDATA[Register Date]]></phrase>
    WidgetFramework/WidgetRenderer/Users.php:            if ($widget['options']['limit'] == 8 && $widget['options']['order'] == 'register_date') {
    XfAddOns/Sitemap/Helper/Member.php:            $this->addUrl($url, $data['register_date']);
    XfAddOns/Sitemap/Sitemap/Member.php:            $this->addUrl($url, $data['register_date']);
    [...]
    LikeButtonEverywhere/ControllerPublic/RecentActivity.php:                    'register_date' => 0,
    EWRutiles/Model/SpamLogs.php:            SELECT NULL AS spamlog_id, register_date AS spamlog_date, NULL AS spamlog_engine,
    EWRutiles/Model/Unconfirmed.php:            $user['days_since'] = floor(($today - $user['register_date']) / (60 * 60 * 24));
    EWRutiles/Model/Sitemap.php:                'order' => 'register_date',
    EWRutiles/Model/Sitemap.php:                    $this->addUrl($document, $sub_node, 'url', XenForo_Link::buildPublicLink('canonical:members', $member), $member['register_date']);
    EWRutiles/Model/SpamFinder.php:            SELECT xf_user.user_id, xf_user.username, xf_user.message_count, xf_user.like_count, xf_user.register_date,
    EWRutiles/Model/SpamFinder.php:            ".($criteria['register_date'] ? 'xf_user.register_date > '.(XenForo_Application::$time - $criteria['register_date'] * 86400).' AND' : '')."
    EWRutiles/Model/SpamFinder.php:            ".($criteria['register_date'] ? 'xf_user.register_date > '.(XenForo_Application::$time - $criteria['register_date'] * 86400).' AND' : '')."
    EWRutiles/Model/SpamFinder.php:            SELECT xf_user.user_id, xf_user.username, xf_user.message_count, xf_user.like_count, xf_user.register_date,
    EWRutiles/Model/SpamFinder.php:            ".($criteria['register_date'] ? 'xf_user.register_date > '.(XenForo_Application::$time - $criteria['register_date'] * 86400).' AND' : '')."
    EWRutiles/Model/SpamFinder.php:            SELECT xf_user.user_id, xf_user.username, xf_user.message_count, xf_user.like_count, xf_user.register_date,
    EWRutiles/Model/SpamFinder.php:            ".($criteria['register_date'] ? 'xf_user.register_date > '.(XenForo_Application::$time - $criteria['register_date'] * 86400).' AND' : '')."
    EWRutiles/Model/SpamFinder.php:            ".($criteria['register_date'] ? 'xf_user.register_date > '.(XenForo_Application::$time - $criteria['register_date'] * 86400).' AND' : '')."
    EWRutiles/Model/SpamFinder.php:            ".($criteria['register_date'] ? 'xf_user.register_date > '.(XenForo_Application::$time - $criteria['register_date'] * 86400).' AND' : '')."
    EWRutiles/addon-EWRutiles.xml:                    <dfn class="spamDate"><xen:datetime time="{$entry.register_date}" /></dfn>
    Waindigo/EditUserJoinDate/ViewAdmin/User/Edit.php:        if (isset($this->_params['user']['register_date'])) {
    Waindigo/EditUserJoinDate/ViewAdmin/User/Edit.php:            $this->_params['user']['register_date_month'] = date("n", $this->_params['user']['register_date']);
    Waindigo/EditUserJoinDate/ViewAdmin/User/Edit.php:            $this->_params['user']['register_date_day'] = date("j", $this->_params['user']['register_date']);
    Waindigo/EditUserJoinDate/ViewAdmin/User/Edit.php:            $this->_params['user']['register_date_year'] = date("Y", $this->_params['user']['register_date']);
    Waindigo/EditUserJoinDate/ControllerAdmin/User.php:                'register_date_month' => XenForo_Input::INT,
    Waindigo/EditUserJoinDate/ControllerAdmin/User.php:                'register_date_day' => XenForo_Input::INT,
    Waindigo/EditUserJoinDate/ControllerAdmin/User.php:                'register_date_year' => XenForo_Input::INT,
    Waindigo/EditUserJoinDate/ControllerAdmin/User.php:            $registerDate = mktime(0, 0, 0, $userInput['register_date_month'], $userInput['register_date_day'], $userInput['register_date_year']);
    Waindigo/EditUserJoinDate/ControllerAdmin/User.php:            $writer->set('register_date', $registerDate);
    Waindigo/EditUserJoinDate/ControllerAdmin/User.php:        if ($field['name'] == "register_date_day" or $field['name'] == "register_date_year")
    Waindigo/EditUserJoinDate/Extend/XenForo/ViewAdmin/User/Edit.php:        if (isset($this->_params['user']['register_dat'])) {
    Waindigo/EditUserJoinDate/Extend/XenForo/ViewAdmin/User/Edit.php:            $this->_params['user']['register_date_month'] = date("n", $this->_params['user']['register_date']);
    Waindigo/EditUserJoinDate/Extend/XenForo/ViewAdmin/User/Edit.php:            $this->_params['user']['register_date_day'] = date("j", $this->_params['user']['register_date']);
    Waindigo/EditUserJoinDate/Extend/XenForo/ViewAdmin/User/Edit.php:            $this->_params['user']['register_date_year'] = date("Y", $this->_params['user']['register_date']);
    Waindigo/EditUserJoinDate/Extend/XenForo/ControllerAdmin/User.php:        if ($field['name'] == 'register_date_day' or $field['name'] == 'register_date_year') {
    Waindigo/EditUserJoinDate/Extend/XenForo/DataWriter/User.php:                'register_date_month' => XenForo_Input::UINT,
    Waindigo/EditUserJoinDate/Extend/XenForo/DataWriter/User.php:                'register_date_day' => XenForo_Input::UINT,
    Waindigo/EditUserJoinDate/Extend/XenForo/DataWriter/User.php:                'register_date_year' => XenForo_Input::UINT,
    Waindigo/EditUserJoinDate/Extend/XenForo/DataWriter/User.php:            $registerDate = mktime(0, 0, 0, $userInput['register_date_month'], $userInput['register_date_day'], $userInput['register_date_year']);
    Waindigo/EditUserJoinDate/Extend/XenForo/DataWriter/User.php:            $this->set('register_date', $registerDate);
    Waindigo/EditUserJoinDate/Listener/TemplateHook.php:            $viewParams['user']['register_date_month'] = date('n', XenForo_Application::$time);
    Waindigo/EditUserJoinDate/Listener/TemplateHook.php:            $viewParams['user']['register_date_day'] = date('j', XenForo_Application::$time);
    Waindigo/EditUserJoinDate/Listener/TemplateHook.php:            $viewParams['user']['register_date_year'] = date('Y', XenForo_Application::$time);
    Tac/StopHumanSpam/Model/StopHumanSpam.php:        $register_date = $visitor->get('register_date');
    Tac/StopHumanSpam/Model/StopHumanSpam.php:        $days_registered = floor((time() - $register_date)/(60*60*24));
    Tac/StopHumanSpam/Model/StopHumanSpam.php:        $register_date = $visitor->get('register_date');
    Tac/StopHumanSpam/Model/StopHumanSpam.php:        $days_registered = floor((time() - $register_date)/(60*60*24));
    
    Line 6 ... ExtraStats appears to be the responsible add-on ??
     
  15. xfrocks

    xfrocks Well-Known Member

    To be honest, I don't remember but I did a similar search to @Mouth and come up with nothing from [bd] Widget Framework.
     
  16. Chris D

    Chris D XenForo Developer Staff Member

    It's this add-on, it seems:

    Code:
     
    ExtraStats/Installer.php:            $existed = $db->fetchOne("SHOW INDEX FROM `xf_user` WHERE COLUMN_NAME = 'register_date'");
    ExtraStats/Installer.php:                $db->query("ALTER TABLE `xf_user` ADD INDEX `register_date` (`register_date`)");
    http://xenforo.com/community/resources/extra-statistics.1722/

    But that doesn't explain why @Sheldon's failed because he didn't have that add-on installed... Unless of course that add-on HAD been installed at some point in the past.

    All add-on developers should be prefixing their columns, tables, indexes and anything else with their add-on ID.
     
    SneakyDave, Daniel Hood and Lawrence like this.
  17. Sheldon

    Sheldon Well-Known Member

    No, I have never had that add-on installed.
     
  18. SneakyDave

    SneakyDave Well-Known Member

    Was the index part of an older xF version, then removed in a more recent 1.1 version, but an upgrade to 1.2 re-created it?

    Can't imagine a use for an add-on to create that index, unless it is extending User::getLatestUser
     
    Last edited: Oct 11, 2013
  19. Mouth

    Mouth Well-Known Member

    Is it still on the to-do list for a fix?
    I would expect any upgrade making table changes, would first test for the changes already existing before making the change and causing an SQL stop and impacting the remainder of its SQL. But I can see how this can be considered an add-ons cause for not prefixing it's DB changes, but hoping it doesn't result in finger pointing and no action :)
     
  20. Mouth

    Mouth Well-Known Member

Share This Page