Omeka S query timing out

I see several Omeka queries timing out, they’re all like:

SELECT COUNT(*) AS dctrn_count FROM (SELECT 1 AS sclr_0 FROM property p0_ WHERE p0_.id IN (SELECT v1_.property_id AS sclr_1 FROM `value` v1_ INNER JOIN site s2_ INNER JOIN item_site i4_ ON s2_.id = i4_.site_id INNER JOIN (item i3_ LEFT JOIN resource r5_ ON i3_.id = r5_.id AND (r5_.is_public = 1)) ON i3_.id = i4_.item_id AND (r5_.id = v1_.resource_id) WHERE (s2_.id = 2) AND (v1_.is_public = 1)) GROUP BY p0_.id) dctrn_table

a DESCRIBE of this query shows:

id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY <derived2> ALL NULL NULL NULL NULL 188012760
2 DERIVED s2_ const PRIMARY PRIMARY 4 const 1 Using index
2 DERIVED p0_ index PRIMARY PRIMARY 4 NULL 2524 Using index
2 DERIVED i4_ ref PRIMARY,IDX_A1734D1F126F525E,IDX_A1734D1FF6BD1646 IDX_A1734D1FF6BD1646 4 const 74490 Using index
2 DERIVED i3_ eq_ref PRIMARY PRIMARY 4 omeka.i4_.item_id 1 Using index
2 DERIVED r5_ eq_ref PRIMARY,idx_public_type_id_title,is_public PRIMARY 4 omeka.i4_.item_id 1 Using where
2 DERIVED v1_ ref IDX_1D77583489329D25,IDX_1D775834549213EC,idx_public_resource_property,is_public idx_public_resource_property 9 const,omeka.r5_.id,omeka.p0_.id 1 Using index; FirstMatch(p0_)

I’ve grepped the source code of the application (v4.0.4) and modules for dctrn but this didn’t have any results. Does anybody recognize this query to identify the module or core? It must be some code processing in the public part (not the admin).

The application logs shows the following:


Next Doctrine\DBAL\Exception\ConnectionLost: An exception occurred while executing 'SELECT COUNT(*) AS dctrn_count FROM (SELECT 1 AS sclr_0 FROM property p0_ WHERE p0_.id IN (SELECT v1_.property_id AS sclr_1 FROM `value` v1_ INNER JOIN site s2_ INNER JOIN item_site i4_ ON s2_.id = i4_.site_id INNER JOIN (item i3_ LEFT JOIN resource r5_ ON i3_.id = r5_.id AND (r5_.is_public = 1)) ON i3_.id = i4_.item_id AND (r5_.id = v1_.resource_id) WHERE (s2_.id = ?) AND (v1_.is_public = 1)) GROUP BY p0_.id) dctrn_table' with params [2]:

SQLSTATE[HY000]: General error: 2006 MySQL server has gone away in ../omeka-s-4.0.3/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/AbstractMySQLDriver.php:115
Stack trace:
#0 ../omeka-s-4.0.3/vendor/doctrine/dbal/lib/Doctrine/DBAL/DBALException.php(182): Doctrine\DBAL\Driver\AbstractMySQLDriver->convertException()
#1 ../omeka-s-4.0.3/vendor/doctrine/dbal/lib/Doctrine/DBAL/DBALException.php(159): Doctrine\DBAL\DBALException::wrapException()
#2 ../omeka-s-4.0.3/vendor/doctrine/dbal/lib/Doctrine/DBAL/Connection.php(2222): Doctrine\DBAL\DBALException::driverExceptionDuringQuery()
#3 ../omeka-s-4.0.3/vendor/doctrine/dbal/lib/Doctrine/DBAL/Connection.php(1312): Doctrine\DBAL\Connection->handleExceptionDuringQuery()
#4 ../omeka-s-4.0.3/vendor/doctrine/orm/lib/Doctrine/ORM/Query/Exec/SingleSelectExecutor.php(31): Doctrine\DBAL\Connection->executeQuery()
#5 ../omeka-s-4.0.3/vendor/doctrine/orm/lib/Doctrine/ORM/Query.php(325): Doctrine\ORM\Query\Exec\SingleSelectExecutor->execute()
#6 ../omeka-s-4.0.3/vendor/doctrine/orm/lib/Doctrine/ORM/AbstractQuery.php(1181): Doctrine\ORM\Query->_doExecute()
#7 ../omeka-s-4.0.3/vendor/doctrine/orm/lib/Doctrine/ORM/AbstractQuery.php(1135): Doctrine\ORM\AbstractQuery->executeIgnoreQueryCache()
#8 ../omeka-s-4.0.3/vendor/doctrine/orm/lib/Doctrine/ORM/AbstractQuery.php(907): Doctrine\ORM\AbstractQuery->execute()
#9 ../omeka-s-4.0.3/vendor/doctrine/orm/lib/Doctrine/ORM/Tools/Pagination/Paginator.php(115): Doctrine\ORM\AbstractQuery->getScalarResult()
#10 ../omeka-s-4.0.3/application/src/Api/Adapter/AbstractEntityAdapter.php(338): Doctrine\ORM\Tools\Pagination\Paginator->count()
#11 ../omeka-s-4.0.3/application/src/Api/Manager.php(221): Omeka\Api\Adapter\AbstractEntityAdapter->search()
#12 ../omeka-s-4.0.3/application/src/Api/Manager.php(59): Omeka\Api\Manager->execute()
#13 ../omeka-s-modules/AdvancedSearch/src/Api/ManagerDelegator.php(134): Omeka\Api\Manager->search()
#14 ../omeka-s-4.0.3/application/src/Form/Element/AbstractVocabularyMemberSelect.php(64): AdvancedSearch\Api\ManagerDelegator->search()
#15 ../omeka-s-4.0.3/application/src/Form/Element/PropertySelect.php(26): Omeka\Form\Element\AbstractVocabularyMemberSelect->getValueOptions()
#16 ../omeka-s-4.0.3/vendor/laminas/laminas-form/src/View/Helper/FormSelect.php(112): Omeka\Form\Element\PropertySelect->getValueOptions()
#17 ../omeka-s-4.0.3/vendor/laminas/laminas-form/src/View/Helper/FormSelect.php(86): Laminas\Form\View\Helper\FormSelect->render()
#18 ../omeka-s-4.0.3/vendor/laminas/laminas-view/src/Renderer/PhpRenderer.php(407): Laminas\Form\View\Helper\FormSelect->__invoke()
#19 ../omeka-s-4.0.3/application/src/View/Helper/PropertySelect.php(43): Laminas\View\Renderer\PhpRenderer->__call()
#20 ../omeka-s-modules/AdvancedSearch/view/common/advanced-search/properties.phtml(87): Omeka\View\Helper\PropertySelect->__invoke()
#21 ../omeka-s-4.0.3/vendor/laminas/laminas-view/src/Renderer/PhpRenderer.php(519): include('...')
#22 ../omeka-s-4.0.3/vendor/laminas/laminas-view/src/Helper/Partial.php(63): Laminas\View\Renderer\PhpRenderer->render()
#23 ../omeka-s-4.0.3/vendor/laminas/laminas-view/src/Renderer/PhpRenderer.php(407): Laminas\View\Helper\Partial->__invoke()
#24 ../omeka-s-4.0.3/application/view/common/advanced-search.phtml(68): Laminas\View\Renderer\PhpRenderer->__call()
#25 ../omeka-s-4.0.3/vendor/laminas/laminas-view/src/Renderer/PhpRenderer.php(519): include('...')
#26 ../omeka-s-4.0.3/vendor/laminas/laminas-view/src/Helper/Partial.php(63): Laminas\View\Renderer\PhpRenderer->render()
#27 ../omeka-s-4.0.3/vendor/laminas/laminas-view/src/Renderer/PhpRenderer.php(407): Laminas\View\Helper\Partial->__invoke()
#28 ../omeka-s-4.0.3/application/view/omeka/site/item/search.phtml(8): Laminas\View\Renderer\PhpRenderer->__call()
#29 ../omeka-s-4.0.3/vendor/laminas/laminas-view/src/Renderer/PhpRenderer.php(519): include('...')
#30 ../omeka-s-4.0.3/vendor/laminas/laminas-view/src/View.php(194): Laminas\View\Renderer\PhpRenderer->render()
#31 ../omeka-s-4.0.3/vendor/laminas/laminas-view/src/View.php(222): Laminas\View\View->render()
#32 ../omeka-s-4.0.3/vendor/laminas/laminas-view/src/View.php(187): Laminas\View\View->renderChildren()
#33 ../omeka-s-4.0.3/vendor/laminas/laminas-mvc/src/View/Http/DefaultRenderingStrategy.php(98): Laminas\View\View->render()
#34 ../omeka-s-4.0.3/vendor/laminas/laminas-eventmanager/src/EventManager.php(319): Laminas\Mvc\View\Http\DefaultRenderingStrategy->render()
#35 ../omeka-s-4.0.3/vendor/laminas/laminas-eventmanager/src/EventManager.php(171): Laminas\EventManager\EventManager->triggerListeners()
#36 ../omeka-s-4.0.3/vendor/laminas/laminas-mvc/src/Application.php(360): Laminas\EventManager\EventManager->triggerEvent()
#37 ../omeka-s-4.0.3/vendor/laminas/laminas-mvc/src/Application.php(341): Laminas\Mvc\Application->completeRequest()
#38 ../omeka-s-4.0.3/index.php(21): Laminas\Mvc\Application->run()
#39 {main}

Can we conclude that the @Daniel_KM AdvancedSearch module is the culprit?
(deactivating this module is not really an option)

The query at fault looks to be the one used to determine which properties are being “used” by the current site to display the search form. I’m not sure the AdvancedSearch module is really involved, but temporarily disabling it to check might be helpful.

I also note that you’ve got some extra indexes on the value table vs. a standard install, “idx_public_resource_property” and “is_public”… the first of those is actually the index that’s being used. I don’t know that it’s “at fault” here but having them makes it a little harder for us to compare to your results.

1 Like

Sharp John! I removed these (manually created) indexes and the query now returns results under a second. Problem solved, thanks.