How to resolve table doesn't exist issue with MySQL DB as data store?

Published:

When using Bold BI with MySQL as the backend database, you may encounter issues when adding data sources. This article will guide you through the process of resolving these issues.

Issue

When adding a data source, you may receive the following errors:

There was an error executing the query SELECT `TABLESCHEMA`, `TABLENAME`,`CLIENTID`,SUM(`ROWS`) AS `ROWS`,`PUBLISHID`,`SCHEDULEID`,`TYPE` FROM `LOOKUPTABLEMETADATAINFO` GROUP BY `TABLESCHEMA`,`TABLENAME`,`CLIENTID`,`PUBLISHID`,`SCHEDULEID`,`TYPE` HAVING `TABLESCHEMA` = 'Datasource_a1e9d902-3e2f-4e5d-845d-7ecd6a408b09'. Reason: Table 'boldbi.LOOKUPTABLEMETADATAINFO' doesn't exist

This issue occurs when the necessary tables are not present in the MySQL data store.

An error occurred while creating the table or inserting the values for the data source.Access denied; you need (at least one of) the SUPER or SYSTEM_VARIABLES_ADMIN privilege(s) for this operation; at MySqlConnector.MySqlDataReader.ActivateResultSet() in /_/src/MySqlConnector/MySqlDataReader.cs:line 124 at MySqlConnector.MySqlDataReader.CreateAsync

This issue occurs because the necessary permissions are not available for inserting the data.

Solution

To resolve this issue, you need to provide super access to the user for the particular database.

Granting Super Access

To grant super access to the specified database, execute the following query in MySQL CLI:

Syntax: GRANT ALL PRIVILEGES ON * .* TO ‘<:UserName>’@‘<:Hostname>’ WITH GRANT OPTION;

GRANT ALL PRIVILEGES ON <:database_name>.* TO 'username'@'localhost';
FLUSH PRIVILEGES;

Using MySQL Workbench

Launch MySQL Workbench and connect to the MySQL server as a user with administrative access.
Once connected, select the “Server” menu from the top menu bar.

Choose the “Users and Privileges” option.
In the Users and Privileges window, select the user who needs the SUPER privilege from the list of users.
Click the “Administrative Roles” tab.
Check the box for “SUPER” privilege.

Click the “Apply” button to save the changes.
Close the Users and Privileges window.
To ensure the changes take effect, it is important to restart the MySQL server.

Once the server restarts, the user should have the necessary privileges to insert values into the data source without encountering the “Access denied” error.

For more information on granting privileges, refer to the MySQL documentation.

What can be done if granting SUPER privilege is not possible.

In AWS-managed instances and in some specific instances, the user may not be able to provide SUPER privileges; in such cases, we recommend providing the below privileges.

GRANT SESSION_VARIABLES_ADMIN ON *.* TO 'username'@'%'

GRANT SYSTEM_VARIABLES_ADMIN ON *.* TO 'username'@'%'