How to resolve lookup table doesn't exist issue with MySQL Datastore DB?

Published:

When connecting to data sources, you may encounter “Table ‘bold_services.LOOKUPTABLEMETADATAINFO’ doesn’t exist” issue. Some common causes of this issue include the primary key being enabled in the database, which leads to failures during the creation of the lookup table.

Steps to resolve the issue

To resolve this issue, you need to disable the primary key in the MySQL database used for the datastore and create the lookup table using the following syntax:

Disable Primary Key: Ensure that the primary key requirement is disabled in your MySQL database settings.

SET SESSION sql_require_primary_key = 0;

Create the Table: Execute the provided SQL command to create the LOOKUPTABLEMETADATAINFO table.

CREATE TABLE `LOOKUPTABLEMETADATAINFO` (
  `ID` float DEFAULT NULL,
  `CLIENTID` text COLLATE utf8mb4_general_ci,
  `TABLESCHEMA` text COLLATE utf8mb4_general_ci,
  `TABLENAME` text COLLATE utf8mb4_general_ci,
  `ROWS` bigint DEFAULT NULL,
  `PUBLISHID` text COLLATE utf8mb4_general_ci,
  `SCHEDULEID` text COLLATE utf8mb4_general_ci,
  `TYPE` text COLLATE utf8mb4_general_ci,
  `PROVIDERTYPE` text COLLATE utf8mb4_general_ci,
  `DISPLAYNAME` text COLLATE utf8mb4_general_ci,
  `ICONNAME` text COLLATE utf8mb4_general_ci
);