Problem/Motivation

When trying to index content, an error is shown and the logs give some more details:

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'field_name' in 'field list': INSERT INTO @search_api_db_default_index_text (item_id, field_name, word, score) VALUES (:db_insert_placeholder_0, :db_insert_placeholder_1, :db_insert_placeholder_2, :db_insert_placeholder_3); Array ( [:db_insert_placeholder_0] => entity:node/1:en [:db_insert_placeholder_1] => entity:node/title [:db_insert_placeholder_2] => test [:db_insert_placeholder_3] => 8000 )

Proposed resolution

Find the faulty SQL query, fix it, submit patch, review and commit patch.

Remaining tasks

All of them.

User interface changes

None

API changes

None

Data model changes

None

CommentFileSizeAuthor
#9 2510564-8--rendered_item_property_type--interdiff.txt1.78 KBdrunken monkey
#9 2510564-8--rendered_item_property_type.patch4.32 KBdrunken monkey
#5 testOnly_5.patch1.64 KBLKS90
#4 interdiff_2-4.txt1.61 KBLKS90
#4 indexing_is_broken-2510564-4.patch2.54 KBLKS90
#2 2510564-2--rendered_item_property_type.patch957 bytesdrunken monkey

Comments

christianadamski’s picture

christianadamski commented

I can confirm this behavior on a blank Drupal 8 using the search_api_db_defaults.
The search_api_db_default_index_text table will just have two columns "item_id" and "value".

However: when uncommenting everything pointing to entity/node:title and rendered_item in search_api.index.default_index.yml and search_api.server.default_server.yml, the module will install fine and work. After that, I could successfully step by step enable the other feature including setting the fields to fulltext and the search_api_db_default_index_text table will be created with the correct 4 columns, including the "field_name" that caused the Exception in the ops post.

drunken monkey’s picture

drunken monkey
he/him
Primary language German
Location Vienna, Austria
commented
Component: Framework » Database backend
Category: Task » Bug report
Status: Active » Needs review
StatusFileSize
new 2510564-2--rendered_item_property_type.patch957 bytes

Does this maybe help?

Status: Needs review » Needs work

The last submitted patch, 2: 2510564-2--rendered_item_property_type.patch, failed testing.

LKS90’s picture

LKS90 commented
Status: Needs work » Needs review
StatusFileSize
new indexing_is_broken-2510564-4.patch2.54 KB
new interdiff_2-4.txt1.61 KB

Yes, it solves the problem described in the issue summary. Here is a test fix for the fix you did, to get the tests green again.

I also wrote a test for the Database defaults, I'll post it in a separate comment (it's complicated :P).

LKS90’s picture

LKS90 commented
StatusFileSize
new testOnly_5.patch1.64 KB

Here is the test I was talking about. The Search API Database defaults submodule depends on configuration from the Standard profile, therefore this test uses it. It takes some time to install when using the standard profile, so I don't think this test should be added to the automated testing.

Another reason why this test shouldn't be committed (yet): There are still issues related to the view_mode when indexing/creating new content, I'll create a separate issue to fix the database defaults submodule.

Edit: The issues I mentioned are already known/a patch might be committed soon: #2473717: Add support for per-bundle view modes in the rendered entity processor.

Status: Needs review » Needs work

The last submitted patch, 5: testOnly_5.patch, failed testing.

The last submitted patch, 5: testOnly_5.patch, failed testing.

drunken monkey’s picture

drunken monkey
he/him
Primary language German
Location Vienna, Austria
commented
Status: Needs work » Needs review

I spotted another issue here, leading to indexing errors for nodes that have more than one tag assigned: the database backend incorrectly handled the case where the table is already saved in the server configuration but has not been created yet (because the config is imported). In this case, the field-specific table had a primary key only containing item_id, not item_id, value – leading to exceptions for any multi-valued fields on items.

Should also be fixed in the attached patch, please test/review!

Also, thanks a lot for fixing the test fails and posting a new test for this! That's exactly what I wanted to suggest, but you already created it, awesome!
The test could probably be optimized a lot by just installing the bits that are needed, not the complete "Standard" profile, but since the complete build still didn't even take 2.5 minutes, I think it would be acceptable to still add it like this, maybe with an @todo comment pointing out the possible performance improvement.
In any case, though, please post this patch in a new issue, I think here we can commit soon and shouldn't have to wait for that test.

drunken monkey’s picture

drunken monkey
he/him
Primary language German
Location Vienna, Austria
commented
StatusFileSize
new 2510564-8--rendered_item_property_type.patch4.32 KB
new 2510564-8--rendered_item_property_type--interdiff.txt1.78 KB

Status: Needs review » Needs work

The last submitted patch, 9: 2510564-8--rendered_item_property_type.patch, failed testing.

LKS90’s picture

LKS90 commented
Status: Needs work » Reviewed & tested by the community 
+++ b/src/Tests/Processor/RenderedItemTest.php
@@ -137,7 +137,7 @@ class RenderedItemTest extends ProcessorTestBase {
+      $this->assertEqual($field->getType(), 'text', 'Node item ' . $nid . ' rendered value is identified as a string.');

Should we update this?
It's a minor nitpick, string is text after all :P.

Couldn't reproduce the test fail which happened on the Jenkins build, I'd say this is good to go.

drunken monkey’s picture

drunken monkey
he/him
Primary language German
Location Vienna, Austria
commented
Status: Reviewed & tested by the community » Fixed

Couldn't reproduce the test fail which happened on the Jenkins build, I'd say this is good to go.

See #2565567: Inconsistent patch test results for old and new testbot for that. I'm just ignoring them for now.

Fixed the test message (thanks for spotting!) and committed it.
Thanks again for all of your work here!

  • drunken monkey committed 8d10a25 on 8.x-1.x 
    Issue #2510564 by drunken monkey, LKS90: Fixed indexing problems with...

Status: Fixed » Closed (fixed)

Automatically closed - issue fixed for 2 weeks with no activity.

PatchRanger’s picture

PatchRanger commented
Version: 8.x-1.x-dev » 7.x-1.x-dev
Status: Closed (fixed) » Active

I had the same symptoms as in original issue at Drupal 7.41:
search_api_db SQLSTATE[42S22]: Column not found: 1054 Unknown column 'field_name' in 'field list'
Let me re-open the issue for 7.x - looks like it should be backported.

drunken monkey’s picture

drunken monkey
he/him
Primary language German
Location Vienna, Austria
commented
Version: 7.x-1.x-dev » 8.x-1.x-dev
Status: Active » Fixed

From what I can see, this has to be a different issue, even if the error message is the same. From the code it doesn't look like this bug could also exist in the D7 version.
Please create a new issue in the search_api_db issue queue.

Status: Fixed » Closed (fixed)

Automatically closed - issue fixed for 2 weeks with no activity.