Smart Search - Optimize displays 1054 Unknown column 'id' in 'field list'

This forum is for general questions about extensions for Joomla! 4.x.

Moderators: pe7er, General Support Moderators

Forum rules
Forum Rules
Absolute Beginner's Guide to Joomla! <-- please read before posting, this means YOU.
Forum Post Assistant - If you are serious about wanting help, you will use this tool to help you post.
Windows Defender SmartScreen Issues <-- please read this if using Windows 10.
Post Reply
jamesborders
Joomla! Apprentice
Joomla! Apprentice
Posts: 17
Joined: Sat Dec 22, 2012 1:44 am

Smart Search - Optimize displays 1054 Unknown column 'id' in 'field list'

Post by jamesborders » Fri Sep 30, 2022 11:35 pm

I was running the "Optimize" command in the Smart Search component of Joomla 4.2.3 when I received this error. How do I find out which table this is from and how do I fix it?
Last edited by toivo on Fri Sep 30, 2022 11:42 pm, edited 1 time in total.
Reason: mod note: moved from 4.x SEO, retitled

User avatar
sozzled
Joomla! Master
Joomla! Master
Posts: 13061
Joined: Sun Jul 05, 2009 3:30 am
Location: Canberra, Australia

Re: Smart Search - Optimize displays 1054 Unknown column 'id' in 'field list'

Post by sozzled » Fri Sep 30, 2022 11:51 pm

Good question. It's a not a fault with J! 4.2.3 as far as I can tell; it's a problem with your database.

This is what I would do. Go to Components » Smart Search » Index » Maintenance (pull-down) and select Clear Index. Confirm that you want to clear the index. When that process finishes, start the indexer again.

After the indexer finishes (it could take a while) try using the Optimise facility again. Let us know how that works for you.
“If you think I’m wrong then say, ‘I think you’re wrong.’ If you say ‘You’re wrong!’, how do you know?”
Walking the talk: https://j4xdemo.enduring.com.au
:)

User avatar
ceford
Joomla! Ace
Joomla! Ace
Posts: 1346
Joined: Mon Feb 24, 2014 10:38 pm
Location: Edinburgh, Scotland
Contact:

Re: Smart Search - Optimize displays 1054 Unknown column 'id' in 'field list'

Post by ceford » Sat Oct 01, 2022 1:59 am

In Global Configuration set Debug System to Yes and Error Reporting to Maximum. Then run Optimise again. The problem should give a stack trace that you can post here (screenshot). That should tell us which line of code triggers the error. You can also look at the list of queries in the debug bar. You may be able to find the offending query there.

jamesborders
Joomla! Apprentice
Joomla! Apprentice
Posts: 17
Joined: Sat Dec 22, 2012 1:44 am

Re: Smart Search - Optimize displays 1054 Unknown column 'id' in 'field list'

Post by jamesborders » Mon Oct 03, 2022 4:22 pm

Attached is the Debug Trace.
Smart Search Debug Trace.png
You do not have the required permissions to view the files attached to this post.

User avatar
sozzled
Joomla! Master
Joomla! Master
Posts: 13061
Joined: Sun Jul 05, 2009 3:30 am
Location: Canberra, Australia

Re: Smart Search - Optimize displays 1054 Unknown column 'id' in 'field list'

Post by sozzled » Mon Oct 03, 2022 7:56 pm

@jamesborder: did you try my suggestion above?
“If you think I’m wrong then say, ‘I think you’re wrong.’ If you say ‘You’re wrong!’, how do you know?”
Walking the talk: https://j4xdemo.enduring.com.au
:)

jamesborders
Joomla! Apprentice
Joomla! Apprentice
Posts: 17
Joined: Sat Dec 22, 2012 1:44 am

Re: Smart Search - Optimize displays 1054 Unknown column 'id' in 'field list'

Post by jamesborders » Mon Oct 03, 2022 8:20 pm

@sozzled : Yes I did and it didn't solve the problem. Same error message.

User avatar
sozzled
Joomla! Master
Joomla! Master
Posts: 13061
Joined: Sun Jul 05, 2009 3:30 am
Location: Canberra, Australia

Re: Smart Search - Optimize displays 1054 Unknown column 'id' in 'field list'

Post by sozzled » Mon Oct 03, 2022 9:11 pm

Thanks for letting us know. Evidently, from what you've written, the smart search indexer cannot build the _finder* tables because there's this "unknown column 'id' in 'field list" caused by something, somewhere in your software soup that's incompatible with J! 4.2.3.

A very quick check of Google using your error message doesn't flag much that relates to the J! core CMS but there are many results pointing to one or other third-party extensions. I think we need to rule those out as possibilities.

Suggest using the Forum Post Assistant (see pink area at the top of this page) and post the BBcode report that it generates here.
“If you think I’m wrong then say, ‘I think you’re wrong.’ If you say ‘You’re wrong!’, how do you know?”
Walking the talk: https://j4xdemo.enduring.com.au
:)

User avatar
ceford
Joomla! Ace
Joomla! Ace
Posts: 1346
Joined: Mon Feb 24, 2014 10:38 pm
Location: Edinburgh, Scotland
Contact:

Re: Smart Search - Optimize displays 1054 Unknown column 'id' in 'field list'

Post by ceford » Mon Oct 03, 2022 10:12 pm

In the bottom right of your screeshot is the actual query but a good bit of it is off the screen. It mentions com_docman. Can you copy all of the query and post it here. These are the queries I see with a new install of 4.2.3 with sample data:

Code: Select all

SELECT `l`.`link_id`
FROM `#__finder_links` AS `l`
WHERE `l`.`type_id` = 1 AND `l`.`url` LIKE 'index.php?option=com_categories&view=category&id=%' AND `l`.`url` NOT IN (
SELECT CONCAT('index.php?option=com_categories&view=category&id=', id)
FROM `#__categories`)
SELECT `l`.`link_id`
FROM `#__finder_links` AS `l`
WHERE `l`.`type_id` = 2 AND `l`.`url` LIKE 'index.php?option=com_contact&view=contact&id=%' AND `l`.`url` NOT IN (
SELECT CONCAT('index.php?option=com_contact&view=contact&id=', id)
FROM `#__contact_details`)
SELECT `l`.`link_id`
FROM `#__finder_links` AS `l`
WHERE `l`.`type_id` = 3 AND `l`.`url` LIKE 'index.php?option=com_content&view=article&id=%' AND `l`.`url` NOT IN (
SELECT CONCAT('index.php?option=com_content&view=article&id=', id)
FROM `#__content`)
SELECT `l`.`link_id`
FROM `#__finder_links` AS `l`
WHERE `l`.`type_id` = 4 AND `l`.`url` LIKE 'index.php?option=com_newsfeeds&view=newsfeed&id=%' AND `l`.`url` NOT IN (
SELECT CONCAT('index.php?option=com_newsfeeds&view=newsfeed&id=', id)
FROM `#__newsfeeds`)
SELECT `l`.`link_id`
FROM `#__finder_links` AS `l`
WHERE `l`.`type_id` = 5 AND `l`.`url` LIKE 'index.php?option=com_tags&view=tag&id=%' AND `l`.`url` NOT IN (
SELECT CONCAT('index.php?option=com_tags&view=tag&id=', id)
FROM `#__tags`)
Plant on line 282 of administrator/components/com_finder/src/Indexer/Adapter.php:
file_put_contents(JPATH_ADMINISTRATOR . '/logs/test.php', $query->__tostring(), FILE_APPEND);

User avatar
sozzled
Joomla! Master
Joomla! Master
Posts: 13061
Joined: Sun Jul 05, 2009 3:30 am
Location: Canberra, Australia

Re: Smart Search - Optimize displays 1054 Unknown column 'id' in 'field list'

Post by sozzled » Mon Oct 03, 2022 10:20 pm

Ahhh ... Docman ... I suspected that. Docman—a third-party extension for J! that hasn't been updated in goodness-knows-how-long—that has been de-listed from the Joomla Extensions Directory; say no more. :laugh:

There's probably no need to post the FPA report now that we know the cause of the problem—thank you @ceford for spotting it.

Get rid of Docman (uninstall it) and then try to rebuild the index and optimise the tables. ;)
“If you think I’m wrong then say, ‘I think you’re wrong.’ If you say ‘You’re wrong!’, how do you know?”
Walking the talk: https://j4xdemo.enduring.com.au
:)

User avatar
toivo
Joomla! Master
Joomla! Master
Posts: 15228
Joined: Thu Feb 15, 2007 5:48 am
Location: Sydney, Australia

Re: Smart Search - Optimize displays 1054 Unknown column 'id' in 'field list'

Post by toivo » Mon Oct 03, 2022 11:45 pm

@sozzled, please check the developer website first before posting about updates! An extension does not have to be in JED for it to be updated regularly. As shown by DOCman's Changelog, version 4.0.11 was released on 28 September.

@jamesborders, please post the FPA results by following the instructions at https://forumpostassistant.github.io/docs/ so that the configuration can be reviewed. And do not uninstall DOCman. If it is not the latest version, update it.
Toivo Talikka, Global Moderator

User avatar
sozzled
Joomla! Master
Joomla! Master
Posts: 13061
Joined: Sun Jul 05, 2009 3:30 am
Location: Canberra, Australia

Re: Smart Search - Optimize displays 1054 Unknown column 'id' in 'field list'

Post by sozzled » Tue Oct 04, 2022 12:06 am

@jamesborders: as @toivo recommends, if you do not have the latest version of Docman (released seven days ago) you will need to pay $69 to update it, according to the developer's website, and hope that it works with your J! 4.2.3 website. If you are unsure about Docman working with J! 4.2.3 (which was released three days ago) then ask the Docman developers before you pay for the latest version. If you do not use or need Docman then uninistall it. The choice is yours. :)
“If you think I’m wrong then say, ‘I think you’re wrong.’ If you say ‘You’re wrong!’, how do you know?”
Walking the talk: https://j4xdemo.enduring.com.au
:)

jamesborders
Joomla! Apprentice
Joomla! Apprentice
Posts: 17
Joined: Sat Dec 22, 2012 1:44 am

Re: Smart Search - Optimize displays 1054 Unknown column 'id' in 'field list'

Post by jamesborders » Sat Oct 08, 2022 5:46 pm

@toivo : I've tried several times to reply with the results from FPA but they never show up in the reply chain. I must be doing something wrong but I don't know what. I click REPLY with Full Editor, paste the results with CNTRL+V, turn off "Smiles", then Submit. I know the Moderator has to approve my reply but the FPA never shows up here.

User avatar
toivo
Joomla! Master
Joomla! Master
Posts: 15228
Joined: Thu Feb 15, 2007 5:48 am
Location: Sydney, Australia

Re: Smart Search - Optimize displays 1054 Unknown column 'id' in 'field list'

Post by toivo » Sat Oct 08, 2022 8:15 pm

jamesborders wrote:
Sat Oct 08, 2022 5:46 pm
I know the Moderator has to approve my reply but the FPA never shows up here.
There were no posts in the moderation queue and nothing in the logs indicates that your reply was deleted. Check the size of the FPA results and if the post is too big, post it in two parts.
Toivo Talikka, Global Moderator


Post Reply

Return to “Extensions for Joomla! 4.x”