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
Projects
v8.0.0

Comments

@seuaCoder
Copy link

seuaCoder commented Nov 30, 2019 •
edited

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 

#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 

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

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.

@rijkvanzanten rijkvanzanten added this to Done in v8.0.0 Dec 3, 2019
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
No open projects
v8.0.0
  
Done
Milestone
No milestone
Development

No branches or pull requests
2 participants
@philleepflorence @seuaCoder