XF 2.2 Column does not exist but it does

KSA

Well-known member
I am having this strange issue with some errors that claims some column do not exist when in fact they do.

  • Database version: libmysql - mysqlnd 8.1.25
  • PHP version: 8.1.25

    it looks like mysql is not happy about about the below quires

Code:
[LIST]
[*]XF\Db\InvalidQueryException: MySQL statement prepare error [1176]: Key 'post_date' doesn't exist in table 'xf_profile_post'
[*]src/XF/Db/AbstractStatement.php:230
[*]Generated by:
[*]Jan 10, 2024 at 3:36 PM
[/LIST]
[HEADING=2]Stack trace[/HEADING]
            SELECT `xf_profile_post`.*, `xf_user_ProfileUser_1`.*, `xf_user_privacy_Privacy_2`.*
            FROM `xf_profile_post` USE INDEX (`post_date`)
            LEFT JOIN `xf_user` AS `xf_user_ProfileUser_1` ON (`xf_user_ProfileUser_1`.`user_id` = `xf_profile_post`.`profile_user_id`)
LEFT JOIN `xf_user_privacy` AS `xf_user_privacy_Privacy_2` ON (`xf_user_privacy_Privacy_2`.`user_id` = `xf_user_ProfileUser_1`.`user_id`)
            WHERE (`xf_profile_post`.`message_state` <> 'moderated') AND (`xf_profile_post`.`message_state` <> 'deleted')
            ORDER BY `xf_profile_post`.`post_date` DESC
        
LIMIT 200
------------

#0 src/XF/Db/Mysqli/Statement.php(198): XF\Db\AbstractStatement->getException('MySQL statement...', 1176, '42000')
#1 src/XF/Db/Mysqli/Statement.php(41): XF\Db\Mysqli\Statement->getException('MySQL statement...', 1176, '42000')
#2 src/XF/Db/Mysqli/Statement.php(56): XF\Db\Mysqli\Statement->prepare()
#3 src/XF/Db/AbstractAdapter.php(96): XF\Db\Mysqli\Statement->execute()
#4 src/XF/Mvc/Entity/Finder.php(1430): XF\Db\AbstractAdapter->query('
            SELECT `xf_...')
#5 src/XF/FindNew/ProfilePost.php(95): XF\Mvc\Entity\Finder->fetch('200')
#6 src/XF/ControllerPlugin/FindNew.php(65): XF\FindNew\ProfilePost->getResultIds(Array, '200')
#7 src/XF/Pub/Controller/AbstractWhatsNewFindType.php(73): XF\ControllerPlugin\FindNew->runFindNewSearch(Object(XF\FindNew\ProfilePost), Array)
#8 src/XF/Pub/Controller/AbstractWhatsNewFindType.php(29): XF\Pub\Controller\AbstractWhatsNewFindType->triggerNewFindNewAction(Object(XF\FindNew\ProfilePost), Array)
#9 src/XF/Mvc/Dispatcher.php(352): XF\Pub\Controller\AbstractWhatsNewFindType->actionIndex(Object(XF\Mvc\ParameterBag))
#10 src/XF/Mvc/Dispatcher.php(259): XF\Mvc\Dispatcher->dispatchClass('XF:WhatsNewProf...', 'Index', Object(XF\Mvc\RouteMatch), Object(XF\Pub\Controller\WhatsNewProfilePost), NULL)
#11 src/XF/Mvc/Dispatcher.php(115): XF\Mvc\Dispatcher->dispatchFromMatch(Object(XF\Mvc\RouteMatch), Object(XF\Pub\Controller\WhatsNewProfilePost), NULL)
#12 src/XF/Mvc/Dispatcher.php(57): XF\Mvc\Dispatcher->dispatchLoop(Object(XF\Mvc\RouteMatch))
#13 src/XF/App.php(2487): XF\Mvc\Dispatcher->run()
#14 src/XF.php(524): XF\App->run()
#15 index.php(20): XF::runApp('XF\\Pub\\App')
#16 {main}




Code:
[LIST]
[*]XF\Db\InvalidQueryException: Error rendering widget: MySQL statement prepare error [1176]: Key 'post_date' doesn't exist in table 'xf_profile_post'
[*]src/XF/Db/AbstractStatement.php:230
[*]

[*]Jan 10, 2024 at 3:36 PM
[/LIST]
[HEADING=2]Stack trace[/HEADING]
            SELECT `xf_profile_post`.*, `xf_user_ProfileUser_1`.*, `xf_user_privacy_Privacy_2`.*, `xf_user_User_3`.*, `xf_reaction_content_Reactions_4`.*
            FROM `xf_profile_post` USE INDEX (`post_date`)
            LEFT JOIN `xf_user` AS `xf_user_ProfileUser_1` ON (`xf_user_ProfileUser_1`.`user_id` = `xf_profile_post`.`profile_user_id`)
LEFT JOIN `xf_user_privacy` AS `xf_user_privacy_Privacy_2` ON (`xf_user_privacy_Privacy_2`.`user_id` = `xf_user_ProfileUser_1`.`user_id`)
LEFT JOIN `xf_user` AS `xf_user_User_3` ON (`xf_user_User_3`.`user_id` = `xf_profile_post`.`user_id`)
LEFT JOIN `xf_reaction_content` AS `xf_reaction_content_Reactions_4` ON (`xf_reaction_content_Reactions_4`.`content_type` = 'profile_post' AND `xf_reaction_content_Reactions_4`.`content_id` = `xf_profile_post`.`profile_post_id` AND `xf_reaction_content_Reactions_4`.`reaction_user_id` = '1')
            WHERE (`xf_profile_post`.`message_state` = 'visible')
            ORDER BY `xf_profile_post`.`post_date` DESC
        
LIMIT 10
------------

#0 src/XF/Db/Mysqli/Statement.php(198): XF\Db\AbstractStatement->getException('MySQL statement...', 1176, '42000')
#1 src/XF/Db/Mysqli/Statement.php(41): XF\Db\Mysqli\Statement->getException('MySQL statement...', 1176, '42000')
#2 src/XF/Db/Mysqli/Statement.php(56): XF\Db\Mysqli\Statement->prepare()
#3 src/XF/Db/AbstractAdapter.php(96): XF\Db\Mysqli\Statement->execute()
#4 src/XF/Mvc/Entity/Finder.php(1430): XF\Db\AbstractAdapter->query('
            SELECT `xf_...')
#5 src/XF/Widget/NewProfilePosts.php(77): XF\Mvc\Entity\Finder->fetch()
#6 internal_data/code_cache/widgets/_5_whats_new_new_profile_posts.php(5): XF\Widget\NewProfilePosts->render()
#7 src/XF/Template/Templater.php(7722): XF\SubContainer\Widget->{closure}(Object(XF\Template\Templater), Array, Array)
#8 src/XF/SubContainer/Widget.php(168): XF\Template\Templater->renderWidgetClosure(Object(Closure), Array)
#9 src/XF/Template/Templater.php(1939): XF\SubContainer\Widget->getCompiledWidget(Array, Array)
#10 internal_data/code_cache/templates/l1/s1/public/whats_new.php(16): XF\Template\Templater->widgetPosition('whats_new_overv...', Array)
#11 src/XF/Template/Templater.php(1654): XF\Template\Templater->{closure}(Object(XF\Template\Templater), Array, NULL)
#12 src/XF/Template/Template.php(24): XF\Template\Templater->renderTemplate('whats_new', Array)
#13 src/XF/Mvc/Renderer/Html.php(50): XF\Template\Template->render()
#14 src/XF/Mvc/Dispatcher.php(460): XF\Mvc\Renderer\Html->renderView('XF:WhatsNew\\Ove...', 'public:whats_ne...', Array)
#15 src/XF/Mvc/Dispatcher.php(442): XF\Mvc\Dispatcher->renderView(Object(XF\Mvc\Renderer\Html), Object(XF\Mvc\Reply\View))
#16 src/XF/Mvc/Dispatcher.php(402): XF\Mvc\Dispatcher->renderReply(Object(XF\Mvc\Renderer\Html), Object(XF\Mvc\Reply\View))
#17 src/XF/Mvc/Dispatcher.php(60): XF\Mvc\Dispatcher->render(Object(XF\Mvc\Reply\View), 'html')
#18 src/XF/App.php(2487): XF\Mvc\Dispatcher->run()
#19 src/XF.php(524): XF\App->run()
#20 index.php(20): XF::runApp('XF\\Pub\\App')
#21 {main}
Code:
XF\Db\InvalidQueryException: MySQL statement prepare error [1176]: Key 'follow_user_id' doesn't exist in table 'xf_user_follow_Following_3' src/XF/Db/AbstractStatement.php:230
Generated by:  Jan 10, 2024 at 2:39 PM
Stack trace
            SELECT `xf_news_feed`.*, `xf_user_User_1`.*
            FROM `xf_news_feed` FORCE INDEX (`event_date`)
            LEFT JOIN `xf_user` AS `xf_user_User_1` ON (`xf_user_User_1`.`user_id` = `xf_news_feed`.`user_id`)
LEFT JOIN `xf_user_privacy` AS `xf_user_privacy_Privacy_2` ON (`xf_user_privacy_Privacy_2`.`user_id` = `xf_user_User_1`.`user_id`)
LEFT JOIN `xf_user_follow` AS `xf_user_follow_Following_3` FORCE INDEX (`follow_user_id`) ON (`xf_user_follow_Following_3`.`user_id` = `xf_user_User_1`.`user_id` AND `xf_user_follow_Following_3`.`follow_user_id` = '7831')
            WHERE ((`xf_news_feed`.`user_id` = 7831) OR (`xf_news_feed`.`user_id` = 0) OR (`xf_user_privacy_Privacy_2`.`allow_receive_news_feed` IN ('everyone', 'members')) OR (`xf_user_privacy_Privacy_2`.`allow_receive_news_feed` = 'followed' AND `xf_user_follow_Following_3`.`user_id` IS NOT NULL))
            ORDER BY `xf_news_feed`.`event_date` DESC
        
LIMIT 30
------------

#0 src/XF/Db/Mysqli/Statement.php(198): XF\Db\AbstractStatement->getException('MySQL statement...', 1176, '42000')
#1 src/XF/Db/Mysqli/Statement.php(41): XF\Db\Mysqli\Statement->getException('MySQL statement...', 1176, '42000')
#2 src/XF/Db/Mysqli/Statement.php(56): XF\Db\Mysqli\Statement->prepare()
#3 src/XF/Db/AbstractAdapter.php(96): XF\Db\Mysqli\Statement->execute()
#4 src/XF/Mvc/Entity/Finder.php(1430): XF\Db\AbstractAdapter->query('
            SELECT `xf_...')
#5 src/XF/Pub/Controller/WhatsNew.php(38): XF\Mvc\Entity\Finder->fetch(30)
#6 src/XF/Mvc/Dispatcher.php(352): XF\Pub\Controller\WhatsNew->actionLatestActivity(Object(XF\Mvc\ParameterBag))
#7 src/XF/Mvc/Dispatcher.php(259): XF\Mvc\Dispatcher->dispatchClass('XF:WhatsNew', 'LatestActivity', Object(XF\Mvc\RouteMatch), Object(XF\Pub\Controller\WhatsNew), NULL)
#8 src/XF/Mvc/Dispatcher.php(115): XF\Mvc\Dispatcher->dispatchFromMatch(Object(XF\Mvc\RouteMatch), Object(XF\Pub\Controller\WhatsNew), NULL)
#9 src/XF/Mvc/Dispatcher.php(57): XF\Mvc\Dispatcher->dispatchLoop(Object(XF\Mvc\RouteMatch))
#10 src/XF/App.php(2487): XF\Mvc\Dispatcher->run()
#11 src/XF.php(524): XF\App->run()
#12 index.php(20): XF::runApp('XF\\Pub\\App')
#13 {main}
Code:
XF\Db\InvalidQueryException: MySQL statement prepare error [1176]: Key 'thread_id_post_date' doesn't exist in table 'xf_post' src/XF/Db/AbstractStatement.php:230
Generated by:  Jan 9, 2024 at 7:37 AM
Stack trace
            SELECT post_id, post_date, user_id, username
            FROM xf_post USE INDEX (thread_id_post_date)
            WHERE thread_id = ?
                AND message_state = 'visible'
            ORDER BY post_date DESC
            LIMIT 1
        
------------

#0 src/XF/Db/Mysqli/Statement.php(198): XF\Db\AbstractStatement->getException('MySQL statement...', 1176, '42000')
#1 src/XF/Db/Mysqli/Statement.php(41): XF\Db\Mysqli\Statement->getException('MySQL statement...', 1176, '42000')
#2 src/XF/Db/Mysqli/Statement.php(56): XF\Db\Mysqli\Statement->prepare()
#3 src/XF/Db/AbstractAdapter.php(96): XF\Db\Mysqli\Statement->execute()
#4 src/XF/Db/AbstractAdapter.php(147): XF\Db\AbstractAdapter->query('
            SELECT post...', 21278)
#5 src/XF/Entity/Thread.php(983): XF\Db\AbstractAdapter->fetchRow('
            SELECT post...', 21278)
#6 src/XF/Entity/Thread.php(1045): XF\Entity\Thread->rebuildLastPostInfo()
#7 src/XF/Entity/Post.php(815): XF\Entity\Thread->postRemoved(Object(XF\Entity\Post))
#8 src/XF/Mvc/Entity/Entity.php(1659): XF\Entity\Post->_postDelete()
#9 src/XF/Service/Post/Deleter.php(95): XF\Mvc\Entity\Entity->delete()
#10 src/XF/InlineMod/Post/Delete.php(57): XF\Service\Post\Deleter->delete('hard', '')
#11 src/XF/InlineMod/Post/Delete.php(36): XF\InlineMod\Post\Delete->applyToEntity(Object(XF\Entity\Post), Array)
#12 src/XF/InlineMod/AbstractAction.php(80): XF\InlineMod\Post\Delete->applyInternal(Object(XF\Mvc\Entity\ArrayCollection), Array)
#13 src/XF/Pub/Controller/InlineMod.php(129): XF\InlineMod\AbstractAction->apply(Object(XF\Mvc\Entity\ArrayCollection), Array)
#14 src/XF/Mvc/Dispatcher.php(352): XF\Pub\Controller\InlineMod->actionPerform(Object(XF\Mvc\ParameterBag))
#15 src/XF/Mvc/Dispatcher.php(259): XF\Mvc\Dispatcher->dispatchClass('XF:InlineMod', 'Perform', Object(XF\Mvc\RouteMatch), Object(XF\Pub\Controller\InlineMod), Object(XF\Mvc\Reply\Reroute))
#16 src/XF/Mvc/Dispatcher.php(115): XF\Mvc\Dispatcher->dispatchFromMatch(Object(XF\Mvc\RouteMatch), Object(XF\Pub\Controller\InlineMod), Object(XF\Mvc\Reply\Reroute))
#17 src/XF/Mvc/Dispatcher.php(57): XF\Mvc\Dispatcher->dispatchLoop(Object(XF\Mvc\RouteMatch))
#18 src/XF/App.php(2487): XF\Mvc\Dispatcher->run()
#19 src/XF.php(524): XF\App->run()
#20 index.php(20): XF::runApp('XF\\Pub\\App')
#21 {main}
 
Code:
Key 'post_date' doesn't exist in table 'xf_profile_post'

It's the index (key) that doesn't exist, not the column. Should be there but you can add it back:

Code:
ALTER TABLE `xf_profile_post` ADD INDEX (`post_date`);

You're missing several though, have you exported and re-imported this database at some point? You've probably got a bigger problem if all the indexes from all tables are missing.
 
  • Like
Reactions: KSA
Code:
Key 'post_date' doesn't exist in table 'xf_profile_post'

It's the index (key) that doesn't exist, not the column. Should be there but you can add it back:

Code:
ALTER TABLE `xf_profile_post` ADD INDEX (`post_date`);

You're missing several though, have you exported and re-imported this database at some point? You've probably got a bigger problem if all the indexes from all tables are missing.

Thanks a lot this got me confused. Yes the database was exported and re-imported again. In fact the database was corrupted and a lot of work has been done to get it recovered. The forum is running ok but there certain places that might have escaped the previous recovery attempt.
 
ChatGPT doesn't understand the difference between a table alias vs a real table name. Also some of the indexes are comprised of multiple columns. If you need to re-apply them, you'll need to refer to the original queries to add the indexes. If they're missing across the entire database it'll be a bit of a task to add them all back unless someone has a list of queries for all of them.
 
  • Like
Reactions: KSA
Back
Top Bottom