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

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}
 
Sort by date Sort by votes
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.
 
Upvote 0 Downvote
Mike said:
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.
Click to expand...

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?
 
Upvote 0 Downvote
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...).
 
Upvote 0 Downvote
Mike said:
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...).
Click to expand...

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.
 
Upvote 0 Downvote
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 ??
 
Upvote 0 Downvote
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.
 
Upvote 0 Downvote
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:
Upvote 0 Downvote
Jake Bunce said:
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.
Click to expand...
Mike said:
That'd be a much much larger change and something that won't happen now.
Click to expand...

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 :)
 
Upvote 0 Downvote
You must log in or register to reply here.

Similar threads

mcatze
Duplicate [XF\Db\Exception] MySQL statement prepare error [1054]: Unknown column 'xf_mg_media_item.album_state' in 'where clause' in src/XF/Db/AbstractStatement
Replies
1
Views
211
Chris D
Chris D
Mouth
  • Solved
1.2.5 -> 2.2.0 Beta 2/3 Upgrade - Unknown column 'like_users' in 'where clause'
Replies
14
Views
1K
Mouth
Mouth
KSA
  • Question Question
MG 2.1 MySQL statement prepare error [1054]: Unknown column 'like_users' in 'where clause' src/XF/Db/AbstractStatement.php:217
Replies
6
Views
1K
Chris D
Chris D
J
  • Question Question
XF 1.5 Mysqli prepare error: Unknown column 'user_id' in 'where clause'
Replies
1
Views
915
Mike
Mike
John71
Fixed XF\Db\Exception: MySQL statement prepare error [1054]: Unknown column 'watermark_id' in 'where clause' src/XF/Db/AbstractStatement.php:212
Replies
9
Views
4K
Chris D
Chris D
Back
Top Bottom