error "Geographical dispersion"

[hartenthaler]

I just installed a fresh version of your webtrees fork. Then I selected the diagram "Geographical dispersion" and got an error:

SQLSTATE[HY000]: General error: 2036 Using unsupported buffer type: 245  (parameter: 9) (SQL: select `gj_maj_geodisp_views`.* from `gj_maj_geodisp_views` where `majgv_gedcom_id` = 1) …/vendor/illuminate/database/Connection.php:712
#0 …/vendor/illuminate/database/Connection.php(672): Illuminate\Database\Connection->runQueryCallback()
#1 …/vendor/illuminate/database/Connection.php(376): Illuminate\Database\Connection->run()
#2 …/vendor/illuminate/database/Query/Builder.php(2414): Illuminate\Database\Connection->select()
#3 …/vendor/illuminate/database/Query/Builder.php(2402): Illuminate\Database\Query\Builder->runSelect()
#4 …/vendor/illuminate/database/Query/Builder.php(2936): Illuminate\Database\Query\Builder->Illuminate\Database\Query\{closure}()
#5 …/vendor/illuminate/database/Query/Builder.php(2403): Illuminate\Database\Query\Builder->onceWithColumns()
#6 …/vendor/jon48/webtrees-lib/app/Module/GeoDispersion/Services/GeoAnalysisViewDataService.php(65): Illuminate\Database\Query\Builder->get()
#7 …/app/Cache.php(60): MyArtJaub\Webtrees\Module\GeoDispersion\Services\GeoAnalysisViewDataService->MyArtJaub\Webtrees\Module\GeoDispersion\Services\{closure}()

[jon48]

Could you please let me know which database type you are using, and which version? I have only tested on my instances, which are all using MySQL 8+, and this module uses a column of type JSON (which seems to appear in MySQL 5.7.8), so I suspect this is the cause of the issue you are seeing

[hartenthaler]

My provider panel says MySQL 5.7. Do you know how I can find the minor number?

But it is not so important. I was interested in which modules you have developed. It is only a playground for me.

[jon48]

Depending on what you have access to:

• PhpMyAdmin should give you the server version on its homepage
• You can run the query SELECT VERSION() in the database
• Somewhere within webtrees, you can try the PHP code echo \Illuminate\Database\Capsule\Manager::connection()->getPdo()->getAttribute(\PDO::ATTR_SERVER_VERSION)

[hartenthaler]

Thank you! SELECT VERSION() says: 5.7.38.

[jon48]

OK, I would have expected JSON to be available then.

Could you please perform some additional checks:

• In order to check if the issue is with MySQL itself or the connector:

  • Could you please run the following in your database, and check if it produces an error or not?

    PREPARE test_stmt FROM 'select `gj_maj_geodisp_views`.* from `gj_maj_geodisp_views` where `majgv_gedcom_id` = ?';
    SET @a = 1;
    EXECUTE test_stmt USING @a;

• In order to check the connector version

  • Could you please check in the phpinfo() ("Server information" administration page in webtrees) what is shown for the pdo_mysql section?

  

• In order to check if potentially PHP and MySQL are not using the same architecture 32-bit/64-bit (I don't know if that would have an effect, but in case the two checks above do not give me a clue)

  • Could you run the following in your database and return the result? (if your host provider allows it)

    SHOW VARIABLES LIKE 'version_compile_machine';

  • could you create a simple PHP script with only and give me the result (either 4 or 8):

    echo PHP_INT_SIZE;

[hartenthaler]

The MySQL statement results in three times "ok" and "empty result".

PDO MySQL is: mysqlnd 8.1.7

version_compile_machine | x86_64

echo PHP_INT_SIZE: 8

[jon48]

Thanks. Unfortunately, it does not give me ideas... It shows that MySQL correctly handles the JSON type, otherwise you would see an error when running the first statement, so it points to an issue with the PHP connector. However, the connector is the native one, and quite recent, so the JSON datatype should be correctly compiled. As for the 32/64 bitness, both PHP and MySQL seems to be using 64-bits, so again, less likely to be an issue.

I have just installed a setup with both PHP 8.1.7 x64 and MySQL 5.7.38 x64 to replicate your system, but I cannot reproduce the issue (on Windows though, I assume your hosting is on Linux). I reckon I am a bit short of ideas now... Maybe your host provider does not use the standard connector, and compile it itself, but this is not something I know how to detect (maybe the top part of the Server information has some clues).

[jon48]

Thanks for the access.

I do not know if you have performed your previous tests on the same environment as the one you gave me access, but I do not see the same PDO MySQL client version:

The client 5.6.45 would explain the error you reported, as the JSON data type would be unknow yet. Assuming your MySQL server is indeed 5.7.38, the fact that the client library is older means that it cannot use any of the new features introduced in MySQL 5.7+ (technically, it even means that it does not meet the minimum requirements for Laravel 8 / webtrees 2, even though advanced features are not used).

This is a point to raise with your host, but I would not recommend having a MySQL client version older than the server version (at least at a "major" version level 5.6 vs 5.7). That would be at least the limiting factor for the GeoDispersion module which expect 5.7.8 at a minimum for both client & server.

[hartenthaler]

Oh, grrr. Sorry! I have two test environments and I was sure that they are identical. But there was one difference: your system does not contain a .htaccess file in the root directory. But there is one in the second test environment that contains "AddType application/x-httpd-php81 .PHP". So the webtrees-geneajaubart system was running PHP 7.4 with an older PDO MySQL client version and the second system is running PHP 8.1 with the newer version. My fault that I didn't notice this major difference. Now I copied the .htaccess from the second test system to the webtrees-geneajaubart root: and now I can open the menu topic "Geographical dispersion". Thank you for your support! Sorry for the unnecessary work.

[jon48]

Glad we managed to get to the bottom of it.

As I am conscious there is no documentation for this module, I have taken the liberty to create a geographical dispersion map on the website (it is limited to France, as I have created the data only for it). I actually noticed an issue with the summary table at the top, where numbers are completely wrong (I can see the same issue on my website, so that is certainly a bug).

[hartenthaler]

Thank you for creating the geographical dispersion map! I thought that I do not have enough events in France, so that this is the reason why the map was not generated.