Skip to content
This repository has been archived by the owner on Jan 6, 2023. It is now read-only.

Querying filtered data with meta=filter_count is returning timeout error on large table #1505

Closed
seuaCoder opened this issue Nov 30, 2019 · 8 comments
Closed

Querying filtered data with meta=filter_count is returning timeout error on large table #1505

seuaCoder opened this issue Nov 30, 2019 · 8 comments

Comments

@seuaCoder
Copy link

seuaCoder commented Nov 30, 2019 •
edited
Loading

No description provided.

@philleepflorence
Copy link
Contributor

Thanks for the information... I was able to import your collection and tested and got the following:
URL: {{url}}/app/items/urls?access_token={{access_token}}&meta=*&active=1
RESPONSE:

{
    "meta": {
        "collection": "urls",
        "type": "collection",
        "result_count": 200,
        "total_count": 36046,
        "filter_count": 36046,
        "limit": 200,
        "offset": 0,
        "page": 1,
        "page_count": 181,
        "links": {
            "self": "http://api.directus.local/app/items/urls",
            "current": "http://api.directus.local/app/items/urls?access_token=api@directus@token&meta=*&active=1&page=1",
            "next": "http://api.directus.local/app/items/urls?access_token=api@directus@token&meta=*&active=1&offset=200&page=2",
            "previous": null,
            "first": "http://api.directus.local/app/items/urls?access_token=api@directus@token&meta=*&active=1&offset=0&page=1",
            "last": "http://api.directus.local/app/items/urls?access_token=api@directus@token&meta=*&active=1&offset=36000&page=181"
        }
    },
    "data": [
        {
            "id": 1,
            "status": "draft",
            "created_by": 1,
            "created_on": "2019-11-27 08:52:02",
            "url": "https://github.com",
            "dataset_id": 307,
            "active": 1,
            "b_dataset_id": 8
        },
        {
            "id": 2,
            "status": "draft",
            "created_by": 1,
            "created_on": "2019-11-27 08:52:02",
            "url": "https://www.mymovies.it/film/2018/likemeback/",
            "dataset_id": 307,
            "active": 1,
            "b_dataset_id": 8
        },
        {...}
    ]
}

The response time was 365ms @ 42.9KB.
This is using V8-RC2 on a MacBook Pro.

Are you getting any errors before the timeout?

@seuaCoder
Copy link
Author

after checking the logs, i have the following error :

[2019-11-30 10:17:02] api[_].ERROR: Directus\Database\Exception\InvalidFieldException: Invalid field "active" in "urls" in /home/directus/src/core/Directus/Database/TableGateway/RelationalTableGateway.php:1473 Stack trace:

@philleepflorence
Copy link
Contributor

Can you please add the stack trace?

@seuaCoder
Copy link
Author

seuaCoder commented Nov 30, 2019 •
edited
Loading 

#0 /home/directus/src/core/Directus/Database/TableGateway/RelationalTableGateway.php(1800): Directus\Database\TableGateway\RelationalTableGateway->doFilter
#1 [internal function]: Directus\Database\TableGateway\RelationalTableGateway->processFilter
#2 /home/directus/src/core/Directus/Database/TableGateway/RelationalTableGateway.php(829): call_user_func_array
#3 /home/directus/src/core/Directus/Database/TableGateway/RelationalTableGateway.php(1130): Directus\Database\TableGateway\RelationalTableGateway->applyParamsToTableEntriesSelect
#4 /home/directus/src/core/Directus/Database/TableGateway/RelationalTableGateway.php(1258): Directus\Database\TableGateway\RelationalTableGateway->fetchItems
#5 /home/directus/src/core/Directus/Database/TableGateway/RelationalTableGateway.php(854): Directus\Database\TableGateway\RelationalTableGateway->fetchData
#6 [internal function]: Directus\Database\TableGateway\RelationalTableGateway->getItems
#7 /home/directus/src/core/Directus/Services/AbstractService.php(339): call_user_func_array
#8 /home/directus/src/core/Directus/Services/AbstractService.php(283): Directus\Services\AbstractService->getDataAndSetResponseCacheTags
#9 /home/directus/src/core/Directus/Services/ItemsService.php(82): Directus\Services\AbstractService->getItemsAndSetResponseCacheTags
#10 /home/directus/src/endpoints/Items.php(43): Directus\Services\ItemsService->findAll
#11 [internal function]: Directus\Api\Routes\Items->all
#12 /home/directus/vendor/slim/slim/Slim/Handlers/Strategies/RequestResponse.php(40): call_user_func
#13 /home/directus/vendor/slim/slim/Slim/Route.php(281): Slim\Handlers\Strategies\RequestResponse->__invoke
#14 /home/directus/src/core/Directus/Application/Http/Middleware/AbstractRateLimitMiddleware.php(34): Slim\Route->__invoke
#15 [internal function]: Directus\Application\Http\Middleware\AbstractRateLimitMiddleware->__invoke
#16 /home/directus/vendor/slim/slim/Slim/DeferredCallable.php(57): call_user_func_array
#17 [internal function]: Slim\DeferredCallable->__invoke
#18 /home/directus/vendor/slim/slim/Slim/MiddlewareAwareTrait.php(70): call_user_func
#19 /home/directus/src/core/Directus/Application/Http/Middleware/AuthenticationMiddleware.php(120): Slim\Route->Slim\{closure}
#20 [internal function]: Directus\Application\Http\Middleware\AuthenticationMiddleware->__invoke
#21 /home/directus/vendor/slim/slim/Slim/DeferredCallable.php(57): call_user_func_array
#22 [internal function]: Slim\DeferredCallable->__invoke
#23 /home/directus/vendor/slim/slim/Slim/MiddlewareAwareTrait.php(70): call_user_func
#24 /home/directus/src/core/Directus/Application/Http/Middleware/TableGatewayMiddleware.php(25): Slim\Route->Slim\{closure}
#25 [internal function]: Directus\Application\Http\Middleware\TableGatewayMiddleware->__invoke
#26 /home/directus/vendor/slim/slim/Slim/DeferredCallable.php(57): call_user_func_array
#27 [internal function]: Slim\DeferredCallable->__invoke
#28 /home/directus/vendor/slim/slim/Slim/MiddlewareAwareTrait.php(70): call_user_func
#29 /home/directus/vendor/slim/slim/Slim/MiddlewareAwareTrait.php(117): Slim\Route->Slim\{closure}
#30 /home/directus/vendor/slim/slim/Slim/Route.php(268): Slim\Route->callMiddlewareStack
#31 /home/directus/vendor/slim/slim/Slim/App.php(503): Slim\Route->run
#32 /home/directus/src/core/Directus/Application/Http/Middleware/AbstractRateLimitMiddleware.php(34): Slim\App->__invoke
#32 /home/directus/src/core/Directus/Application/Http/Middleware/AbstractRateLimitMiddleware.php(34): Slim\App->__invoke
#33 [internal function]: Directus\Application\Http\Middleware\AbstractRateLimitMiddleware->__invoke
#34 /home/directus/vendor/slim/slim/Slim/DeferredCallable.php(57): call_user_func_array
#35 [internal function]: Slim\DeferredCallable->__invoke
#36 /home/directus/vendor/slim/slim/Slim/MiddlewareAwareTrait.php(70): call_user_func
#37 /home/directus/vendor/directus/proxy-detection/src/ProxyDetectionMiddleware.php(30): Slim\App->Slim\{closure}
#38 /home/directus/src/core/Directus/Application/Http/Middleware/ProxyMiddleware.php(18): RKA\Middleware\ProxyDetectionMiddleware->__invoke
#39 [internal function]: Directus\Application\Http\Middleware\ProxyMiddleware->__invoke
#40 /home/directus/vendor/slim/slim/Slim/DeferredCallable.php(57): call_user_func_array
#41 [internal function]: Slim\DeferredCallable->__invoke
#42 /home/directus/vendor/slim/slim/Slim/MiddlewareAwareTrait.php(70): call_user_func
#43 /home/directus/vendor/akrabat/ip-address-middleware/src/IpAddress.php(113): Slim\App->Slim\{closure}
#44 [internal function]: RKA\Middleware\IpAddress->__invoke
#45 /home/directus/vendor/slim/slim/Slim/DeferredCallable.php(57): call_user_func_array
#46 [internal function]: Slim\DeferredCallable->__invoke
#47 /home/directus/vendor/slim/slim/Slim/MiddlewareAwareTrait.php(70): call_user_func
#48 /home/directus/src/core/Directus/Application/Http/Middleware/CorsMiddleware.php(66): Slim\App->Slim\{closure}
#49 [internal function]: Directus\Application\Http\Middleware\CorsMiddleware->__invoke
#50 /home/directus/vendor/slim/slim/Slim/DeferredCallable.php(57): call_user_func_array
#51 [internal function]: Slim\DeferredCallable->__invoke
#52 /home/directus/vendor/slim/slim/Slim/MiddlewareAwareTrait.php(70): call_user_func
#53 /home/directus/src/core/Directus/Application/Http/Middleware/ResponseCacheMiddleware.php(54): Slim\App->Slim\{closure}
#54 [internal function]: Directus\Application\Http\Middleware\ResponseCacheMiddleware->__invoke
#55 /home/directus/vendor/slim/slim/Slim/DeferredCallable.php(57): call_user_func_array
#56 [internal function]: Slim\DeferredCallable->__invoke
#57 /home/directus/vendor/slim/slim/Slim/MiddlewareAwareTrait.php(70): call_user_func
#58 /home/directus/vendor/slim/slim/Slim/MiddlewareAwareTrait.php(117): Slim\App->Slim\{closure}
#59 /home/directus/vendor/slim/slim/Slim/App.php(392): Slim\App->callMiddlewareStack
#60 /home/directus/vendor/slim/slim/Slim/App.php(297): Slim\App->process
#61 /home/directus/src/core/Directus/Application/Application.php(161): Slim\App->run
#62 /home/directus/public/index.php(5): Directus\Application\Application->run [] []

@philleepflorence
Copy link
Contributor

If you go to direcus_fields, please send the rows for the collection urls.

@philleepflorence
Copy link
Contributor

Can you see if you get the same issues using the master @ https://github.com/directus/directus

@seuaCoder
Copy link
Author

seuaCoder commented Nov 30, 2019 •
edited
Loading 

INSERT INTO `directus_fields` (`id`, `collection`, `field`, `type`, `interface`, `options`, `locked`, `validation`, `required`, `readonly`, `hidden_detail`, `hidden_browse`, `sort`, `width`, `group`, `note`, `translation`) VALUES
(195, 'urls', 'id', 'integer', 'primary-key', '[]', 0, NULL, 1, 0, 1, 1, 1, 'full', NULL, '', NULL),
(196, 'urls', 'status', 'status', 'status', '{\"status_mapping\":{\"published\":{\"name\":\"Published\",\"value\":\"published\",\"text_color\":\"white\",\"background_color\":\"accent\",\"browse_subdued\":false,\"browse_badge\":true,\"soft_delete\":false,\"published\":true},\"draft\":{\"name\":\"Draft\",\"value\":\"draft\",\"text_color\":\"white\",\"background_color\":\"blue-grey-100\",\"browse_subdued\":true,\"browse_badge\":true,\"soft_delete\":false,\"published\":false},\"deleted\":{\"name\":\"Deleted\",\"value\":\"deleted\",\"text_color\":\"white\",\"background_color\":\"red\",\"browse_subdued\":true,\"browse_badge\":true,\"soft_delete\":true,\"published\":false}}}', 0, NULL, 0, 0, 0, 0, 6, 'full', NULL, NULL, NULL),
(197, 'urls', 'created_by', 'user_created', 'user-created', '{\"template\":\"{{first_name}} {{last_name}}\",\"display\":\"both\"}', 0, NULL, 0, 1, 1, 1, 7, 'full', NULL, NULL, NULL),
(198, 'urls', 'created_on', 'datetime_created', 'datetime-created', NULL, 0, NULL, 0, 1, 1, 1, 8, 'full', NULL, NULL, NULL),
(200, 'urls', 'url', 'string', 'text-input', '{\"trim\":true,\"showCharacterCount\":true,\"formatValue\":false,\"width\":\"auto\"}', 0, NULL, 0, 0, 0, 0, 4, 'full', NULL, NULL, NULL),
(212, 'urls', 'dataset_id', 'm2o', 'many-to-one', '{\"template\":\"{{ keywords }}\",\"placeholder\":\"Select one\",\"threshold\":20}', 0, NULL, 0, 0, 0, 0, 3, 'full', NULL, NULL, NULL),
(286, 'urls', 'active', 'boolean', 'toggle', '{\"checkbox\":false}', 0, NULL, 0, 0, 0, 0, 5, 'fill', NULL, '', NULL),
(287, 'urls', 'b_dataset_id', 'integer', 'numeric', '{\"localized\":true}', 0, NULL, 0, 0, 0, 0, 2, 'half', NULL, NULL, NULL);

@seuaCoder
Copy link
Author

seuaCoder commented Nov 30, 2019 •
edited
Loading

There was a something wrong with the "active" field which was boolean but the field type was "tinyint 3". After updating the type to "tinyint 1" the problem was resolve.

Sign up for free to subscribe to this conversation on GitHub. Already have an account? Sign in.
Assignees
No one assigned
Labels
None yet
Projects
None yet
Milestone
No milestone
Development

No branches or pull requests
2 participants
@philleepflorence @seuaCoder