Initially I have indexed a few products using search API. But later, I removed the indexed products and recreate new products. Now when I try to re-setup search API, I was trying to execute pending tasks, but I could not get past an error page.

The error page reads
An AJAX HTTP error occurred.
HTTP Result Code: 500
Debugging information follows.
Path: /batch?id=51&op=do_nojs&op=do
StatusText: 500 Service unavailable (with message)
ResponseText: The website encountered an unexpected error. Please try again later.

The error message reads
"Drupal\search_api\SearchApiException: SQLSTATE[42000]: Syntax error or access violation: 1171 All parts of a PRIMARY KEY must be NOT NULL; if you need NULL in a key, use UNIQUE instead: ALTER TABLE {search_api_db_default_index_sku} CHANGE `value` `value` BIGINT DEFAULT NULL COMMENT 'The field\'s value for this item'; Array ( ) in Drupal\search_api_db\Plugin\search_api\backend\Database->fieldsUpdated() (line 1044 of /var/www/drupal/modules/search_api/modules/search_api_db/src/Plugin/search_api/backend/Database.php)."

Please help me. I have tried to google on this issue and browse through the issue list over here but could not get any relevant answer.

CommentFileSizeAuthor
#5 2925464-5--db_backend_value_column_not_null.patch2.87 KBdrunken monkey
#5 2925464-5--db_backend_value_column_not_null--tests_only.patch1.04 KBdrunken monkey
#3 2925464-3--db_backend_value_column_not_null.patch1.83 KBdrunken monkey

Comments

shadeworm created an issue. See original summary.

shadeworm’s picture

shadeworm commented
Issue summary: View changes
drunken monkey’s picture

drunken monkey
he/him
Primary language German
Location Vienna, Austria
commented
Status: Active » Needs review
Issue tags: +Needs tests
StatusFileSize
new 2925464-3--db_backend_value_column_not_null.patch1.83 KB

Thanks a lot for reporting this problem!
Looking at this, the mistake is obvious: when altering existing table definitions, we neglect to set "not null" to TRUE for the value columns, which we normally do. This didn't cause any visible problems (probably just a bit of lost performance) until #2884451: search_api_db tables should be created with a primary key landed and added primary keys to those tables. Now, this of course blows up, as you see.

The attached patch should fix this, please test!
Also, we should add a test to make sure this keeps working.

borisson_’s picture

borisson_
Primary language Dutch
Location Mechelen, 🇧🇪
commented

I'm not sure if we need to test this actually, it's a database definition. However, the code looks great!

drunken monkey’s picture

drunken monkey
he/him
Primary language German
Location Vienna, Austria
commented
Issue tags: -Needs tests
StatusFileSize
new 2925464-5--db_backend_value_column_not_null--tests_only.patch1.04 KB
new 2925464-5--db_backend_value_column_not_null.patch2.87 KB

I'm not sure if we need to test this actually, it's a database definition. However, the code looks great!

Well, it did cause an error, so I do think a regression test is in order. Normally you're the one who's all "We need tests!" all the time – stop making me be the responsible one!

However, I'm failing anyways to reproduce the problem in the first place. For me, all three DBMSs (even Postgres!) seem to auto-correct the ALTER TABLE statement to make all necessary columns NOT NULL.

@ shadeworm: What database and version are you using? Any special config options maybe? In case you're familiar with SQL, it would be great if you could provide basic SQL steps to reproduce. I did the following, but didn't encounter any errors:

CREATE TABLE test ( id INT(10) UNSIGNED NOT NULL, val INT(10) NOT NULL, PRIMARY> ;
ALTER TABLE test CHANGE val val INT(9) NULL ;
SHOW CREATE TABLE ; -- Shows "val" as INT(9) NOT NULL.
.jch’s picture

.jch commented

Apache/2.4.25 (x64) PHP/7.2.2 ~ Core 8.5.0-rc1 ~ MySQL 5.7.20-log

search_api (dev-1.x build 21f5a31) appears to have resolved the null primary key issue for MySQL 5.7 with the above config.
Will continue testing.

Outstanding work!

borisson_’s picture

borisson_
Primary language Dutch
Location Mechelen, 🇧🇪
commented
Status: Needs review » Reviewed & tested by the community

I like this, and we had a manual verification of the patch. Let's get this in.

  • drunken monkey committed 6589813 on 8.x-1.x 
    Issue #2925464 by drunken monkey, borisson_, .jch: Fixed problem with...
drunken monkey’s picture

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

Good to hear, thanks for reviewing!
Committed.
Thanks again, everyone!

Status: Fixed » Closed (fixed)

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