Deprecated MySQL Spatial Functions after Upgrade

I recently updated my host OS to Ubuntu LTS 20.04, which upgraded the MySQL RDBMS to version 8.

I was prompted to upgrade my database tables, but soon discovered that any plugin requiring Geospatial data (in this case Neatline and Geolocation) no longer functioned.

The Zend function calls are slightly different, but the point of failure is the same:

“Mysqli prepare error: FUNCTION mysite_omeka.AsText does not exist”

More specifically:

“Zend_Db_Statement_Mysqli_Exception: Mysqli prepare error: FUNCTION mysite_omeka.AsText does not exist in /var/www/html/mysite/application/libraries/Zend/Db/Statement/Mysqli.php:77”

A search of the MySQL developer forums reveals that the “AsText” function has been changed in version 8, to “ST_AsText”:

Can anyone point me toward where this function call is initially generated? Is there a specific library or database table I can modify?

The Geolocation plugin doesn’t use the MySQL spatial functions, so it shouldn’t be affected.

Neatline does use them, I seem to recall, and the relevant place where they are generated would be within the Neatline plugin code.

That’s curious – with a few minor differences in the sequence of function calls, both plugins generate an identical error.

(Geolocation fails when I add a map entry to an item in the collection. Neatline just fails to add any collection items to a map.)

Is there any place in the Omeka database calling code that defines or stores the MySQL “AsText” function?

Following the link I posted, it seems internal to MySQL but if the deprecated version is being called I have to assume it’s coming from the application.

Here’s the Neatline error trace:

Mysqli prepare error: FUNCTION adiuva_omeka.AsText does not exist

Zend_Db_Statement_Mysqli_Exception: Mysqli prepare error: FUNCTION adiuva_omeka.AsText does not exist in /var/www/html/adiuva/application/libraries/Zend/Db/Statement/Mysqli.php:77
Stack trace:
#0 /var/www/html/adiuva/application/libraries/Zend/Db/Statement.php(115): Zend_Db_Statement_Mysqli->_prepare()
#1 /var/www/html/adiuva/application/libraries/Zend/Db/Adapter/Mysqli.php(388): Zend_Db_Statement->__construct()
#2 /var/www/html/adiuva/application/libraries/Zend/Db/Adapter/Abstract.php(479): Zend_Db_Adapter_Mysqli->prepare()
#3 /var/www/html/adiuva/application/libraries/Zend/Db/Select.php(711): Zend_Db_Adapter_Abstract->query()
#4 /var/www/html/adiuva/plugins/Neatline/models/NeatlineRecordTable.php(76): Zend_Db_Select->query()
#5 /var/www/html/adiuva/plugins/NeatlineText/NeatlineTextPlugin.php(91): NeatlineRecordTable->queryRecords()
#6 /var/www/html/adiuva/application/libraries/Omeka/Plugin/Broker.php(266): NeatlineTextPlugin->filterNeatlineGlobals()
#7 /var/www/html/adiuva/application/libraries/globals.php(324): Omeka_Plugin_Broker->applyFilters()
#8 /var/www/html/adiuva/plugins/Neatline/helpers/Plugins.php(89): apply_filters()
#9 /var/www/html/adiuva/plugins/Neatline/views/shared/exhibits/partials/exhibit.php(19): nl_getGlobals()
#10 /var/www/html/adiuva/application/libraries/Omeka/View.php(114): include('/var/www/html/a...')
#11 /var/www/html/adiuva/application/libraries/Zend/View/Abstract.php(888): Omeka_View->_run()
#12 /var/www/html/adiuva/application/libraries/Zend/View/Helper/Partial.php(109): Zend_View_Abstract->render()
#13 /var/www/html/adiuva/application/libraries/Zend/View/Abstract.php(349): Zend_View_Helper_Partial->partial()
#14 /var/www/html/adiuva/plugins/Neatline/views/shared/exhibits/partials/editor_core.php(16): Zend_View_Abstract->__call()
#15 /var/www/html/adiuva/application/libraries/Omeka/View.php(114): include('/var/www/html/a...')
#16 /var/www/html/adiuva/application/libraries/Zend/View/Abstract.php(888): Omeka_View->_run()
#17 /var/www/html/adiuva/application/libraries/Zend/View/Helper/Partial.php(109): Zend_View_Abstract->render()
#18 /var/www/html/adiuva/application/libraries/Zend/View/Abstract.php(349): Zend_View_Helper_Partial->partial()
#19 /var/www/html/adiuva/plugins/Neatline/views/shared/exhibits/editor.php(13): Zend_View_Abstract->__call()
#20 /var/www/html/adiuva/application/libraries/Omeka/View.php(114): include('/var/www/html/a...')
#21 /var/www/html/adiuva/application/libraries/Zend/View/Abstract.php(888): Omeka_View->_run()
#22 /var/www/html/adiuva/application/libraries/Zend/Controller/Action/Helper/ViewRenderer.php(912): Zend_View_Abstract->render()
#23 /var/www/html/adiuva/application/libraries/Zend/Controller/Action/Helper/ViewRenderer.php(933): Zend_Controller_Action_Helper_ViewRenderer->renderScript()
#24 /var/www/html/adiuva/application/libraries/Zend/Controller/Action/Helper/ViewRenderer.php(972): Zend_Controller_Action_Helper_ViewRenderer->render()
#25 /var/www/html/adiuva/application/libraries/Zend/Controller/Action/HelperBroker.php(277): Zend_Controller_Action_Helper_ViewRenderer->postDispatch()
#26 /var/www/html/adiuva/application/libraries/Zend/Controller/Action.php(527): Zend_Controller_Action_HelperBroker->notifyPostDispatch()
#27 /var/www/html/adiuva/application/libraries/Zend/Controller/Dispatcher/Standard.php(308): Zend_Controller_Action->dispatch()
#28 /var/www/html/adiuva/application/libraries/Zend/Controller/Front.php(954): Zend_Controller_Dispatcher_Standard->dispatch()
#29 /var/www/html/adiuva/application/libraries/Zend/Application/Bootstrap/Bootstrap.php(105): Zend_Controller_Front->dispatch()
#30 /var/www/html/adiuva/application/libraries/Zend/Application.php(384): Zend_Application_Bootstrap_Bootstrap->run()
#31 /var/www/html/adiuva/application/libraries/Omeka/Application.php(73): Zend_Application->run()
#32 /var/www/html/adiuva/admin/index.php(28): Omeka_Application->run()
#33 {main}

And here’s the Geolocation trace:

Mysqli prepare error: FUNCTION adiuva_omeka.AsText does not exist

Zend_Db_Statement_Mysqli_Exception: Mysqli prepare error: FUNCTION adiuva_omeka.AsText does not exist in /var/www/html/adiuva/application/libraries/Zend/Db/Statement/Mysqli.php:77
Stack trace:
#0 /var/www/html/adiuva/application/libraries/Zend/Db/Statement.php(115): Zend_Db_Statement_Mysqli->_prepare()
#1 /var/www/html/adiuva/application/libraries/Zend/Db/Adapter/Mysqli.php(388): Zend_Db_Statement->__construct()
#2 /var/www/html/adiuva/application/libraries/Zend/Db/Adapter/Abstract.php(479): Zend_Db_Adapter_Mysqli->prepare()
#3 /var/www/html/adiuva/application/libraries/Omeka/Db.php(80): Zend_Db_Adapter_Abstract->query()
#4 /var/www/html/adiuva/application/libraries/Omeka/Db/Table.php(647): Omeka_Db->__call()
#5 /var/www/html/adiuva/application/libraries/Omeka/Db/Table.php(429): Omeka_Db_Table->fetchObjects()
#6 /var/www/html/adiuva/plugins/Neatline/models/NeatlineRecordTable.php(53): Omeka_Db_Table->findBySql()
#7 /var/www/html/adiuva/plugins/Neatline/NeatlinePlugin.php(179): NeatlineRecordTable->syncItem()
#8 /var/www/html/adiuva/application/libraries/Omeka/Plugin/Broker.php(154): NeatlinePlugin->hookAfterSaveItem()
#9 /var/www/html/adiuva/application/libraries/Omeka/Record/AbstractRecord.php(301): Omeka_Plugin_Broker->callHook()
#10 /var/www/html/adiuva/application/libraries/Omeka/Record/AbstractRecord.php(548): Omeka_Record_AbstractRecord->runCallbacks()
#11 /var/www/html/adiuva/application/libraries/Omeka/Controller/AbstractActionController.php(229): Omeka_Record_AbstractRecord->save()
#12 /var/www/html/adiuva/application/controllers/ItemsController.php(88): Omeka_Controller_AbstractActionController->editAction()
#13 /var/www/html/adiuva/application/libraries/Zend/Controller/Action.php(516): ItemsController->editAction()
#14 /var/www/html/adiuva/application/libraries/Zend/Controller/Dispatcher/Standard.php(308): Zend_Controller_Action->dispatch()
#15 /var/www/html/adiuva/application/libraries/Zend/Controller/Front.php(954): Zend_Controller_Dispatcher_Standard->dispatch()
#16 /var/www/html/adiuva/application/libraries/Zend/Application/Bootstrap/Bootstrap.php(105): Zend_Controller_Front->dispatch()
#17 /var/www/html/adiuva/application/libraries/Zend/Application.php(384): Zend_Application_Bootstrap_Bootstrap->run()
#18 /var/www/html/adiuva/application/libraries/Omeka/Application.php(73): Zend_Application->run()
#19 /var/www/html/adiuva/admin/index.php(28): Omeka_Application->run()
#20 {main}

These are both coming from Neatline (you’ll see that NeatlinePlugin.php is listed in both error tracebacks). Neatline also does some things when regular Omeka items are saved, which is why you’re seeing problems in both places.

The issue seems to have been reported to Neatline’s developers.

Fascinating – it spills over into other geolocation plugins somehow!

Thanks for your assistance. I’ll follow up with the Neatline developers.