Unable to Create/Modify Reports - Access denied to database 'information_schema'

(8 posts) (2 voices)
  1. cdavison, Member

    I am no longer able to create/modify any reports within MyDBR after updating. I had the "No valid license found for..." issue occuring so I updated my server. After updating, I am unable to create or modify any of my reports. I can view the current code from the MyDBR web client, but I cannot modify.

    The error is below when I try to save a new procedure from within the web gui. It seems like it's trying to access the information_schema table instead of the mydbr schema.


  2. myDBR Team, Key Master

    How did you do the update? With myDBR's automatic updater or manually?

    The error is bit a obscure as every user should have read rights to information_schema.

    Could you log into the server from command line using myDBR's credentials and try to create a procedure. Then log in as root and check under what definer the objects in the database are created:

    select definer, routine_schema, if (substr(routine_name, 1, 6)='sp_DBR', 'report', 'myDBR object'), count(*)
    from information_schema.routines
    where routine_name like 'sp_MyDBR%' or routine_name like 'sp_DBR%'
    group by definer, routine_schema, if (substr(routine_name, 1, 6)='sp_DBR', 'report', 'myDBR object')

    myDBR Team

  3. cdavison, Member

    I updated using the updater, but had to add the '&action=update' to the end of the update wizard since the update page didn't load properly. The forum that I did that solution is here.

    I do not have root access to the server, but my MySQL host within my corporation claims that they have not changed anything on their end.

    I am able to query the information_schema from the sql editor from within MyDBR. Is something within MyDBR trying to write to the information_schema table?

    The output of your table is below.

    information_schema.routines query output

  4. myDBR Team, Key Master


    I updated using the updater, but had to add the '&action=update' to the end of the update wizard since the update page didn't load properly. The forum that I did that solution is here.

    This just means that your server either does not allow HTTPS access to http://www.mydbr.com or the PHP is not configured to recognize the certificates. We will be moving towards full HTTPS in the update process, just that we could not do it yet without user's servers configured correctly.

    As for the error itself, first things first: in top of the editor window, there is a popup selection of the databases available. What is the database that is selected and do you see the myDBR database in the list? if not, run the automatic updater again.

    If that does not solve the issue, then:

    What is the output of command:

    show databases

    Is the definer in the query the same that you are using in mydbr_config.php?

    myDBR Team

  5. cdavison, Member

    The default database at the top drop down of a report is information_schema. When I change it to mydbr, like it should be, the report saves. I've never had to change this before in order for a report to save properly. Is there any way to change the report so that the default schema is mydbr instead of information_schema?

    According to the mydbr_config.php, the schema is myDbr as I would expect it to be.

    The output of

    show databases
    is four different databases in this order:
    [other schema]
    [other schema]

  6. myDBR Team, Key Master

    The default selection should be the mydbr-database.

    Is the spelling in mydbr_config.php (caps) the same as the one in the output of show databases-command?

    myDBR Team

  7. cdavison, Member

    That did the trick! My mydbr_config.php file had it as 'myDbr' when it should have been all lower case. Interesting enough, this case sensitivity was just now was effected, because I haven't touched that config file for years.

  8. myDBR Team, Key Master

    The database selection is a new feature in 5.8. Databaae names can be case sensitive/case insensitive depending on configuration and platform. The SQL Editor does not try to force the insensitivity due to this. Now that your database was named 'myDbr' in the config and in real life it was 'mydbr', the default selection was not done.

    myDBR Team


You must log in to post.