I checked logfile to see what is going on.
While the connection is set as user root and
SELECT CURRENT_USER() ;
|
CURRENT_USER()
|
---------------------
|
root@%
|
I have found the following records in logfile:
200328 17:29:10 63 Connect radim@xxx as anonymous on |
...
|
200328 17:33:03 63 Query create table xpivot1 |
engine=CONNECT table_type=PIVOT option_list='PivotCol=FirstLetter,FncCol=cnt' |
Srcdef='select table_type, left(table_name,1) as FirstLetter , count(table_type) as cnt from tables group by 1, 2' |
65 Connect root@localhost as anonymous on pohoda |
65 Connect Access denied for user 'root'@'localhost' (using password: NO) |
Since I found a topic discussing "Connect ...@localhost as anonymous" at
https://dba.stackexchange.com/questions/81092/what-does-as-anonymous-on-means-in-mysqls-general-log,
I am more confused than at the beginning 
I'm running into the same error, trying to create a pivot table on MariaDB 10.5.3 on Debian Stretch.
I was able to get around the error by specifying a username and password in the option_list, but then I ran into another error about not being able to find matching columns. I got past that error by using a query in Srcdef, but then I had issues getting the pivot to group the way I wanted it to. I finally had to resort to not using the CONNECT Pivot table at all, and doing the pivot inside of a stored procedure instead.
But I found it interesting that I could not get past this Access denied error. I had a password enabled for root@localhost, but after reading more about this problem switched to using unix socket. I can login from the shell prompt without using a password, and can access the table I need fine, but I still couldn't get the CONNECT engine to work without specifying a user with a password.
About "Access denied":
This is not a bug and is precisely described in the documentation saying:
Additional access options
There are four cases where pivot must call the server containing the source table or on which the SrcDef statement must be executed:
1. The source table is not a CONNECT table.
2. The SrcDef option is specified.
3. The source table is on another server.
4. The columns are not specified.By default, pivot tries to call the currently used server using host=localhost, user=root not using password, and port=3306. However, this may not be what is needed, in particular if the local root user has a password in which case you can get an “access denied” error message when creating or using the pivot table.
Specify the host, user, password and/or port options in the option_list to override the default connection options used to access the source table, get column specifications, execute the generated group by or SrcDef query.
About your other errors:
To investigate I need you to provide the source table and the description of the desired pivot table so I can try to reproduce and see what error you get.