Omeka S query timing out

coret · January 1, 2024, 4:24pm

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).

coret · January 2, 2024, 6:25pm

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)

jflatnes · January 2, 2024, 7:36pm

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.

coret · January 2, 2024, 8:29pm

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

Daniel_KM · March 19, 2024, 11:41am

In my cases, the result is the inverse: when i remove these indexes, the speed is slow for a big base.