i can not open the geographic data from the map

[goon818]

If i want open the site geograpic data from the map i get this error message.
I have to clear the sql table wt_placelocation. Then i work for one day. On the next day i get the same error message.

Illuminate\Database\QueryException: SQLSTATE[42000]: Syntax error or access violation: 1104 The SELECT would examine more than MAX_JOIN_SIZE rows; check your WHERE and use SET SQL_BIG_SELECTS=1 or SET MAX_JOIN_SIZE=# if the SELECT is okay (SQL: select COUNT(*) AS child_count, SUM(wt_p0.pl_place IS NOT NULL AND wt_p0.pl_lati IS NULL OR wt_p1.pl_place IS NOT NULL AND wt_p1.pl_lati IS NULL OR wt_p2.pl_place IS NOT NULL AND wt_p2.pl_lati IS NULL OR wt_p3.pl_place IS NOT NULL AND wt_p3.pl_lati IS NULL OR wt_p4.pl_place IS NOT NULL AND wt_p4.pl_lati IS NULL OR wt_p5.pl_place IS NOT NULL AND wt_p5.pl_lati IS NULL OR wt_p6.pl_place IS NOT NULL AND wt_p6.pl_lati IS NULL OR wt_p7.pl_place IS NOT NULL AND wt_p7.pl_lati IS NULL OR wt_p8.pl_place IS NOT NULL AND wt_p8.pl_lati IS NULL OR wt_p9.pl_place IS NOT NULL AND wt_p9.pl_lati IS NULL) AS no_coord from wt_placelocation as wt_p0 left join wt_placelocation as wt_p1 on wt_p1.pl_parent_id = wt_p0.pl_id left join wt_placelocation as wt_p2 on wt_p2.pl_parent_id = wt_p1.pl_id left join wt_placelocation as wt_p3 on wt_p3.pl_parent_id = wt_p2.pl_id left join wt_placelocation as wt_p4 on wt_p4.pl_parent_id = wt_p3.pl_id left join wt_placelocation as wt_p5 on wt_p5.pl_parent_id = wt_p4.pl_id left join wt_placelocation as wt_p6 on wt_p6.pl_parent_id = wt_p5.pl_id left join wt_placelocation as wt_p7 on wt_p7.pl_parent_id = wt_p6.pl_id left join wt_placelocation as wt_p8 on wt_p8.pl_parent_id = wt_p7.pl_id left join wt_placelocation as wt_p9 on wt_p9.pl_parent_id = wt_p8.pl_id where wt_p0.pl_parent_id = 5 limit 1) in file /vendor/illuminate/database/Connection.php on line 664
Stack trace:

1. Illuminate\Database\QueryException->() /vendor/illuminate/database/Connection.php:664
2. Fisharebest\Webtrees\Http\Controllers\Admin\LocationController->mapData() [internal]:0
3. Doctrine\DBAL\Driver\PDOException->() /vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/PDOStatement.php:119
4. Fisharebest\Webtrees\Http\Controllers\Admin\LocationController->mapData() [internal]:0
5. PDOException->() /vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/PDOStatement.php:117
6. PDOStatement->execute() /vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/PDOStatement.php:117
7. Doctrine\DBAL\Driver\PDOStatement->execute() /vendor/illuminate/database/Connection.php:330
8. Illuminate\Database\Connection->Illuminate\Database{closure}() /vendor/illuminate/database/Connection.php:657
9. Illuminate\Database\Connection->runQueryCallback() /vendor/illuminate/database/Connection.php:624
10. Illuminate\Database\Connection->run() /vendor/illuminate/database/Connection.php:333
11. Illuminate\Database\Connection->select() /vendor/illuminate/database/Query/Builder.php:2096
12. Illuminate\Database\Query\Builder->runSelect() /vendor/illuminate/database/Query/Builder.php:2084
13. Illuminate\Database\Query\Builder->Illuminate\Database\Query{closure}() /vendor/illuminate/database/Query/Builder.php:2570
14. Illuminate\Database\Query\Builder->onceWithColumns() /vendor/illuminate/database/Query/Builder.php:2085
15. Illuminate\Database\Query\Builder->get() /vendor/illuminate/database/Concerns/BuildsQueries.php:77
16. Illuminate\Database\Query\Builder->first() /app/Http/Controllers/Admin/LocationController.php:783
17. Fisharebest\Webtrees\Http\Controllers\Admin\LocationController->childLocationStatus() /app/Http/Controllers/Admin/LocationController.php:698
18. Fisharebest\Webtrees\Http\Controllers\Admin\LocationController->getPlaceListLocation() /app/Http/Controllers/Admin/LocationController.php:79
19. Fisharebest\Webtrees\Http\Controllers\Admin\LocationController->mapData() [internal]:0
20. ReflectionMethod->invoke() /app/Application.php:45
21. Fisharebest\Webtrees\Application->dispatch() /index.php:155
22. {closure}() /app/Module/HitCountFooterModule.php:186
23. Fisharebest\Webtrees\Module\HitCountFooterModule->handle() /index.php:141
24. {closure}() /app/Http/Middleware/Housekeeping.php:75
25. Fisharebest\Webtrees\Http\Middleware\Housekeeping->handle() /index.php:141
26. {closure}() /app/Http/Middleware/DebugBarData.php:41
27. Fisharebest\Webtrees\Http\Middleware\DebugBarData->handle() /index.php:141
28. {closure}() /app/Http/Middleware/BootModules.php:59
29. Fisharebest\Webtrees\Http\Middleware\BootModules->handle() /index.php:141
30. {closure}() /app/Http/Middleware/UseTheme.php:71
31. Fisharebest\Webtrees\Http\Middleware\UseTheme->handle() /index.php:141
32. {closure}() /app/Http/Middleware/UseLocale.php:52
33. Fisharebest\Webtrees\Http\Middleware\UseLocale->handle() /index.php:141
34. {closure}() /app/Http/Middleware/UseTree.php:58
35. Fisharebest\Webtrees\Http\Middleware\UseTree->handle() /index.php:141
36. {closure}() /app/Http/Middleware/UseSession.php:59
37. Fisharebest\Webtrees\Http\Middleware\UseSession->handle() /index.php:141
38. {closure}() /app/Http/Middleware/UseFilesystem.php:50
39. Fisharebest\Webtrees\Http\Middleware\UseFilesystem->handle() /index.php:141
40. {closure}() /app/Http/Middleware/SetPhpLimits.php:54
41. Fisharebest\Webtrees\Http\Middleware\SetPhpLimits->handle() /index.php:141
42. {closure}() /app/Http/Middleware/CheckForMaintenanceMode.php:48
43. Fisharebest\Webtrees\Http\Middleware\CheckForMaintenanceMode->handle() /index.php:141
44. {closure}() /index.php:158

[magicsunday]

How many records does your database contain?

[fisharebest]

@goon818 has activated the MySQL setting MAX_JOIN_SIZE, which means that MySQL will not attempt to run queries that might produce a large number of rows.

[fisharebest]

In a way, this is similar to the PHP settings for memory_limit and execution time.

webtrees can override these settings. But if they are set by the server's administrator, then should we?

[magicsunday]

Maybe provide some of the common db configuration options, which could be overwritten, in an additional admin config section especially for databases?

[goon818]

In the database are 325 places by 1807 persons. The point with the administrator could be. The can be a reason that the one day it's running and on the next day not. I have the problem on paid webhost.

[DaPoHou]

Change to a web hosting provider. There are too many alternative service providers.

[fisharebest]

We can change this setting in webtrees - just like we can change php.ini settings.

I just want to find the right place to do this. Give me a few days....

[fisharebest]

@goon818 - can you try something for me?

There is now an example module which modifies PHP and MySQL settings:

https://github.com/fisharebest/webtrees/tree/master/modules_v4/example-server-configuration.disable

Copy this to a new name (without .disable).
Edit the file module.php.
Uncomment the line containing sql_big_selects.

Does this fix your problem?

[goon818]

Thank You "fisharebest"
After i add the hostfile and i edit the module.php can i open site geographic data.
I tested it tomorrow, then i can open the site too. It's perfect.

[goon818]

It's working today, too. Your fix is perfect.
Thank you.