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

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

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

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.
 
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 ??
 
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.
 
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:
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.
That'd be a much much larger change and something that won't happen now.

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 :)
 
Top Bottom