Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-22067

Pivot throws 'Access denied for user'

Details

    Description

      I am not able to run:

      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';

      The query returns an error:

      SQL error (1105): (1045) Access denied for user 'root'@'localhost' (using password: NO) 
      

      , while root is still connected.

      If I run the query from the command line, the same error reveals.

      Attachments

        Activity

          radimmachu Radim Machů added a comment -

          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

          radimmachu Radim Machů added a comment - 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
          RHOPKINS13 RHOPKINS13 added a comment -

          I'm running into the same error, trying to create a pivot table on MariaDB 10.5.3 on Debian Stretch.

          RHOPKINS13 RHOPKINS13 added a comment - I'm running into the same error, trying to create a pivot table on MariaDB 10.5.3 on Debian Stretch.
          RHOPKINS13 RHOPKINS13 added a comment -

          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.

          RHOPKINS13 RHOPKINS13 added a comment - 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.

          bertrandop Olivier Bertrand added a comment - 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.

          People

            bertrandop Olivier Bertrand
            radimmachu Radim Machů
            Votes:
            1 Vote for this issue
            Watchers:
            3 Start watching this issue

            Dates

              Created:
              Updated:

              Git Integration

                Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.