XF 2.2 Column does not exist but it does

KSA · Jan 10, 2024

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}

mattrogowski · Jan 11, 2024

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.

KSA · Jan 11, 2024

mattrogowski said:
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.

avalanch · Jan 11, 2024

Here you go, several things are missing, this chat will tell you how to fix them.

ChatGPT

A conversational AI system that listens, learns, and challenges

chat.openai.com

KSA · Jan 11, 2024

what the hell is table 'xf_user_follow_Following_3'

mattrogowski · Jan 11, 2024

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.

KSA · Jan 11, 2024

mattrogowski said:
you'll need to refer to the original queries to add the indexes

any idea on where are those queries stored in the files?

XF 2.2 Column does not exist but it does

KSA

Well-known member

mattrogowski

Well-known member

KSA

Well-known member

avalanch

Active member

ChatGPT

KSA

Well-known member

mattrogowski

Well-known member

KSA

Well-known member

Similar threads

We value your privacy