Publisher does not support the Fluid field type. Please do not contact asking when support will be available.

If you purchased an add-on from expressionengine.com, be sure to visit boldminded.com/claim to add the license to your account here on boldminded.com.

Ticket: SQL Timeout error on settings page

Status Resolved
Add-on / Version DataGrab 5.0.5
Severity
EE Version 7.3.1

Gauthier De Paoli

Aug 18, 2023

I did 2 imports yesterday (everything went fine)

But now when I’m trying to reach the datagrab settings homepage, I have this error:

BoldMinded\DataGrab\Dependency\Illuminate\Database\QueryException Caught
SQLSTATE[HY000] [2002] Connection timed out (SQL: select count(*) as aggregate from `exp_datagrab_jobs` where `queue` = delete_2)
user/addons/datagrab/vendor-build/illuminate/database/Connection.php:588

Stack Trace: Please include when reporting this error
#0 user/addons/datagrab/vendor-build/illuminate/database/Connection.php(649): BoldMinded\DataGrab\Dependency\Illuminate\Database\Connection->runQueryCallback(‘select count(*)...’, Array, Object(Closure))
#1 user/addons/datagrab/vendor-build/illuminate/database/Connection.php(632): BoldMinded\DataGrab\Dependency\Illuminate\Database\Connection->tryAgainIfCausedByLostConnection(Object(BoldMinded\DataGrab\Dependency\Illuminate\Database\QueryException), ‘select count(*)...’, Array, Object(Closure))
#2 user/addons/datagrab/vendor-build/illuminate/database/Connection.php(562): BoldMinded\DataGrab\Dependency\Illuminate\Database\Connection->handleQueryException(Object(BoldMinded\DataGrab\Dependency\Illuminate\Database\QueryException), ‘select count(*)...’, Array, Object(Closure))
#3 user/addons/datagrab/vendor-build/illuminate/database/Connection.php(322): BoldMinded\DataGrab\Dependency\Illuminate\Database\Connection->run(‘select count(*)...’, Array, Object(Closure))
#4 user/addons/datagrab/vendor-build/illuminate/database/Query/Builder.php(1994): BoldMinded\DataGrab\Dependency\Illuminate\Database\Connection->select(‘select count(*)...’, Array, true)
#5 user/addons/datagrab/vendor-build/illuminate/database/Query/Builder.php(1984): BoldMinded\DataGrab\Dependency\Illuminate\Database\Query\Builder->runSelect()
#6 user/addons/datagrab/vendor-build/illuminate/database/Query/Builder.php(2418): BoldMinded\DataGrab\Dependency\Illuminate\Database\Query\Builder->BoldMinded\DataGrab\Dependency\Illuminate\Database\Query\{closure}()
#7 user/addons/datagrab/vendor-build/illuminate/database/Query/Builder.php(1985): BoldMinded\DataGrab\Dependency\Illuminate\Database\Query\Builder->onceWithColumns(Array, Object(Closure))
#8 user/addons/datagrab/vendor-build/illuminate/database/Query/Builder.php(2358): BoldMinded\DataGrab\Dependency\Illuminate\Database\Query\Builder->get(Array)
#9 user/addons/datagrab/vendor-build/illuminate/database/Query/Builder.php(2296): BoldMinded\DataGrab\Dependency\Illuminate\Database\Query\Builder->aggregate(‘count’, Array)
#10 user/addons/datagrab/vendor-build/illuminate/queue/DatabaseQueue.php(65): BoldMinded\DataGrab\Dependency\Illuminate\Database\Query\Builder->count()
#11 user/addons/datagrab/vendor-build/illuminate/queue/QueueManager.php(257): BoldMinded\DataGrab\Dependency\Illuminate\Queue\DatabaseQueue->size(‘delete_2’)
#12 user/addons/datagrab/models/datagrab_model.php(754): BoldMinded\DataGrab\Dependency\Illuminate\Queue\QueueManager->__call(‘size’, Array)
#13 user/addons/datagrab/mcp.datagrab.php(161): Datagrab_model->getDeleteQueueSize(2)
#14 [internal function]: Datagrab_mcp->index()
#15 ee/ExpressionEngine/Controller/Addons/Addons.php(1590): call_user_func_array(Array, Array)
#16 ee/ExpressionEngine/Controller/Addons/Addons.php(866): ExpressionEngine\Controller\Addons\Addons->getModuleSettings(‘datagrab’, ‘index’, Array)
#17 [internal function]: ExpressionEngine\Controller\Addons\Addons->settings(‘datagrab’)
#18 ee/ExpressionEngine/Core/Core.php(268): call_user_func_array(Array, Array)
#19 ee/ExpressionEngine/Core/Core.php(124): ExpressionEngine\Core\Core->runController(Array)
#20 ee/ExpressionEngine/Boot/boot.php(184): ExpressionEngine\Core\Core->run(Object(ExpressionEngine\Core\Request))
#21 www/admin/index.php(139): require_once(’...’)
#21 www/admin/index.php(139): require_once(’...’)

 

#1

Gauthier De Paoli

Aug 18, 2023

Comment has been marked private.

#2

Gauthier De Paoli

Aug 24, 2023

Comment has been marked private.

#3

Gauthier De Paoli

Aug 24, 2023

Comment has been marked private.

#4

BoldMinded (Brian)

Aug 24, 2023

Do you know how many items you’re trying to import? How big is the queue when you start an import?

#5

Gauthier De Paoli

Aug 24, 2023

25 items in total

#6

BoldMinded (Brian)

Aug 24, 2023

yeah there is no reason it should timeout with 25 items. When you start an import, have you watched the exp_datagrab_queue table to see it import items? And does it create any with “delete_2” as the queue name?

#7

BoldMinded (Brian)

Aug 24, 2023

It might help if you share screenshots of you Datagrab import settings.

#8

Gauthier De Paoli

Aug 24, 2023

Problem is that I don’t even have access to the index page because the SQL error occurs during the datagrab index page.

I retrieved URL from my local env and tried it on the website and the same SQL error occured.

To be sure your understood : the SQL error occurs on every datagrab related requests (so far), even the addon homepage. Since I swap databases between local and this staging env, it seems the staging env registered a queue state that don’t exist anymore. The script tries to load it but fails with this SQL timeout.

I’ll had screenshot of the import settings (from local env)

#9

Gauthier De Paoli

Aug 24, 2023

Comment has been marked private.

#10

BoldMinded (Brian)

Aug 24, 2023

The login you added does not work.

#11

Gauthier De Paoli

Aug 24, 2023

Comment has been marked private.

#12

BoldMinded (Brian)

Aug 24, 2023

Looks like you’re using MariaDB 10.5… have you tried an older DB? MySQL 8 maybe?

I don’t have Maria 10.5 in my local environment so it’ll take some time to switch over, but that is the only thing that sticks out to me as a differentiator that could be causing an issue with a seemingly harmless database count query.

#13

BoldMinded (Brian)

Aug 24, 2023

Comment has been marked private.

#14

Gauthier De Paoli

Aug 25, 2023

Comment has been marked private.

#15

BoldMinded (Brian)

Aug 25, 2023

The queue _is_ the exp_datagrab_jobs table, so if there are no rows in that table, then the queue is cleaned/flushed.

No it does not actually use artisan.

Try the clean EE install, ideally in on a separate server if possible.

#16

Gauthier De Paoli

Aug 25, 2023

Hi Brian,

I found the problem when trying to create another new EE Install.

The problem is that Datagrab addon.setup.php does not fill the database port to the database configuration that relies on Laraval Queue.

The Laravel Queue then uses a default port number and for most of user, it works. But, in my case, I use a distant database with a specific port number.

Any Laravel Queue database request was trying to get a connection with a non-existing database port and then… timed out.

'DatabaseConfig' => function () {
            return  [
                'driver' => 'mysql',
                'host' => ee('db')->hostname,
                'port' => ee('db')->port,
                'database' => ee('db')->database,
                'username' => ee('db')->username,
                'password' => ee('db')->password,
                'charset' => ee('db')->char_set,
                'collation' => ee('db')->dbcollat,
                'prefix' => ee('db')->dbprefix . 'datagrab_',
            ];
        }

I added the

port' => ee('db')->port

to the DatabaseConfig and ... it works !

#17

BoldMinded (Brian)

Aug 25, 2023

Well, that’s interesting to know. Glad you figured it out. I’ll update the DataGrab code and include this in the next release. Thanks for sharing.

Login to reply