Low Search causing SQL Error when saving channel entries after deleting searchable field
EE 3.5.1 (also tested under 3.5.0)
Low Search 5.0.4 (haven't had a chance to test under 5.0.5 yet, but release notes don't like this has been fixed)
Low Search is causing a SQL error (see below) when saving a channel entry after deleting a searchable channel field (the entry is still saved correctly though).
The problem can be avoided by making the field not searchable and rebuilding the Low Search collection index *before* deleting the field.
Here's the error + stack trace:
Exception Caught
SQLSTATE[42S22]: Column not found: 1054 Unknown column 'd.field_id_30' in 'field list':
SELECT 't'.'entry_id', 't'.'title' AS field_id_0, 'd'.'field_id_29', 'd'.'field_id_30', 'd'.'field_id_122', 'd'.'field_id_124', 'd'.'field_id_436' FROM ('exp_channel_titles' t) JOIN 'exp_channel_data' d ON 't'.'entry_id' = 'd'.'entry_id' WHERE 't'.'channel_id' = 7 AND 't'.'entry_id' IN (12675)
ee/legacy/database/drivers/mysqli/mysqli_connection.php:122
Stack Trace: hide details
#0 ee/legacy/database/drivers/mysqli/mysqli_driver.php(137): CI_DB_mysqli_connection->query('SELECT 't'.'ent...')
#1 ee/legacy/database/DB_driver.php(297): CI_DB_mysqli_driver->_execute('SELECT 't'.'ent...')
#2 ee/legacy/database/DB_driver.php(203): CI_DB_driver->simple_query('SELECT 't'.'ent...')
#3 ee/legacy/database/DB_active_rec.php(1206): CI_DB_driver->query('SELECT 't'.'ent...')
#4 user/addons/low_search/libraries/Low_search_index.php(233): CI_DB_active_record->get()
#5 user/addons/low_search/libraries/Low_search_index.php(83): Low_search_index->build_by_collection(2, Array)
#6 user/addons/low_search/ext.low_search.php(96): Low_search_index->build_by_entry(12675)
#7 [internal function]: Low_search_ext->after_channel_entry_save(Object(EllisLab\ExpressionEngine\Model\Channel\ChannelEntry), Array)
#8 ee/legacy/libraries/Extensions.php(243): call_user_func_array(Array, Array)
#9 ee/legacy/libraries/Extensions.php(138): EE_Extensions->call_class('Low_search_ext', 'after_channel_e...', Array, Array)
#10 [internal function]: EE_Extensions->call('after_channel_e...', Object(EllisLab\ExpressionEngine\Model\Channel\ChannelEntry), Array)
#11 ee/EllisLab/ExpressionEngine/Service/Model/Model.php(642): call_user_func_array(Array, Array)
#12 [internal function]: EllisLab\ExpressionEngine\Service\Model\Model->EllisLab\ExpressionEngine\Service\Model\{closure}('after_channel_e...', Object(EllisLab\ExpressionEngine\Model\Channel\ChannelEntry), Array)
#13 ee/EllisLab/ExpressionEngine/Service/Model/Model.php(615): call_user_func_array(Object(Closure), Array)
#14 [internal function]: EllisLab\ExpressionEngine\Service\Model\Model->EllisLab\ExpressionEngine\Service\Model\{closure}()
#15 ee/EllisLab/ExpressionEngine/Service/Event/Emitter.php(153): call_user_func_array(Object(Closure), Array)
#16 [internal function]: EllisLab\ExpressionEngine\Service\Event\Emitter->emit('afterSave')
#17 ee/EllisLab/ExpressionEngine/Library/Data/Entity.php(631): call_user_func_array(Array, Array)
#18 [internal function]: EllisLab\ExpressionEngine\Library\Data\Entity->emit('afterSave')
#19 ee/EllisLab/ExpressionEngine/Service/Model/Model.php(834): call_user_func_array('parent::emit', Array)
#20 ee/EllisLab/ExpressionEngine/Service/Model/Query/Update.php(60): EllisLab\ExpressionEngine\Service\Model\Model->emit('afterSave')
#21 ee/EllisLab/ExpressionEngine/Service/Model/DataStore.php(294): EllisLab\ExpressionEngine\Service\Model\Query\Update->run()
#22 ee/EllisLab/ExpressionEngine/Service/Model/DataStore.php(260): EllisLab\ExpressionEngine\Service\Model\DataStore->runQuery('Update', Object(EllisLab\ExpressionEngine\Service\Model\Query\Builder))
#23 ee/EllisLab/ExpressionEngine/Service/Model/Query/Builder.php(83): EllisLab\ExpressionEngine\Service\Model\DataStore->updateQuery(Object(EllisLab\ExpressionEngine\Service\Model\Query\Builder))
#24 ee/EllisLab/ExpressionEngine/Service/Model/Model.php(366): EllisLab\ExpressionEngine\Service\Model\Query\Builder->update()
#25 ee/EllisLab/ExpressionEngine/Model/Content/ContentModel.php(198): EllisLab\ExpressionEngine\Service\Model\Model->save()
#26 ee/EllisLab/ExpressionEngine/Controller/Publish/AbstractPublish.php(295): EllisLab\ExpressionEngine\Model\Content\ContentModel->save()
#27 ee/EllisLab/ExpressionEngine/Controller/Publish/Edit.php(482): EllisLab\ExpressionEngine\Controller\Publish\AbstractPublish->saveEntryAndRedirect(Object(EllisLab\ExpressionEngine\Model\Channel\ChannelEntry))
#28 [internal function]: EllisLab\ExpressionEngine\Controller\Publish\Edit->entry('12675')
#29 ee/EllisLab/ExpressionEngine/Core/Core.php(189): call_user_func_array(Array, Array)
#30 ee/EllisLab/ExpressionEngine/Core/Core.php(94): EllisLab\ExpressionEngine\Core\Core->runController(Array)
#31 ee/EllisLab/ExpressionEngine/Boot/boot.php(151): EllisLab\ExpressionEngine\Core\Core->run(Object(EllisLab\ExpressionEngine\Core\Request))
#32 sites/evergreen.ca/www/expr_admin.php(143): require_once('e...')
#32 sites/evergreen.ca/www/expr_admin.php(143): require_once('e...')
Replies
Low 28 Jan 2017 08:58
Ah yes. I once had a bit of script in place, using the custom_field_modify_data hook, that would alter the collection settings when a field was deleted.
However, this hook was fired not only when a field was deleted, but also whenever a field was called or modified. This resulted in some performance drawbacks (even with intended use of the hook), so I decided to remove that code in v4.3.0.
So yeah. The error can occur, but currently there isn't a solid way to avoid it, apart from using the hook again (which sucks for performance) or checking of the fields exist every time the index is updated (which also sucks for performance).
I'm seeing a new before_channel_field_delete in EE3, so that might be the solution for it.
For now, re-saving a search collection when you delete a field is also a possibility.
James Catt 30 Jan 2017 15:07
Sounds promising.
Though if you're not able to find a good solution, it might be worth putting in some way to recover manually after deleting the field. As it was, I couldn't get rid of the error without restoring my database from a backup. Trying to rebuild the collection after deleting the field also runs into an error (although I didn't try recreating the field so that I could make it unsearchable—that might work too).
No biggie for me though—I'll just have to remember to remove the field from the list of searchable fields before deleting for now. :)