Configure a PolarDB data source

PolarDB is an out-of-the-box, stable, reliable, and elastically scalable online database service provided by Alibaba Cloud (Learn about PolarDB).

Before you purchase PolarDB: what you need to know

• OpenSearch currently supports PolarDB for MySQL 5.6, 5.7, and 8.0.

• The PolarDB cluster must belong to the Alibaba Cloud account that you are currently logged on with. Otherwise, the cluster cannot be accessed or used.

• The PolarDB cluster must reside in the same region as the OpenSearch application.

• After a PolarDB cluster is created, binary logging (binlog) is disabled by default, which causes data source registration to fail. Therefore, you must enable it as follows: loose_polar_log_bin is OFF by default and must be set to ON_WITH_GTID; binlog_row_image is FULL by default and does not need to be changed.

• Cloned instances are supported.

• The PolarDB cluster must be set as a read/write cluster.

Supported features

• Pulling full data from specified database tables (manually or on a schedule).

• Horizontally merging data from one or more data source tables. These source tables must have identical schemas and identical data source plug-in configurations, and their primary key values must not be duplicated (duplicate primary key values are overwritten). The following two scenarios are mainly supported:

  • An application table is configured with one data source that contains multiple source tables.

  • An application table is configured with multiple data sources, and each data source contains one or more source tables.

• Data source field conversion plug-ins.

• Supported data synchronization methods:

  • Automatic synchronization;

  • Self-purchased DTS real-time synchronization;

  • No automatic synchronization.

• Filter conditions (for full data).

• Matching database table names by using the wildcard *.

Limits

• Only PolarDB clusters whose binlogs are in full mode are supported. To enable this mode: loose_polar_log_bin is OFF by default and must be set to ON_WITH_GTID; binlog_row_image is FULL by default and does not need to be changed.

• Only PolarDB for MySQL 5.6, 5.7, and 8.0 are currently supported.

• The PolarDB cluster must belong to the Alibaba Cloud account that you are currently logged on with. Otherwise, the cluster cannot be accessed.

• The PolarDB cluster must reside in the same region as the OpenSearch application.

• After a PolarDB data source is configured for a Standard Edition application, incremental data cannot be pushed by using SDKs or API operations.

• PolarDB data sources of Standard Edition applications do not support data source filter conditions.

• The replace into syntax is not supported.

• The truncate and drop commands are not supported. Use the delete command to delete data.

• The PolarDB access password cannot contain the % symbol. Otherwise, index rebuilding tasks fail.

• Merging field columns across source tables with different schemas in different databases is not supported.

• We recommend that you set both loose_max_statement_time and connect_timeout to 0. After the full synchronization triggered by index rebuilding or an offline change is complete, you can change them back to normal values.

Usage notes

• If you connect a data source (RDS or PolarDB) attached to DRDS to OpenSearch, you must specify the names of the actual database shards under DRDS when you configure the data source. (A database under DRDS is split into one shadow database and eight actual database shards, and written data is randomly written to the actual database shards.)

• PolarDB clusters support switching between internal and public endpoints. OpenSearch does not charge any traffic fees for retrieving data from PolarDB.

• OpenSearch can pull full data only from the primary database. We recommend that you rebuild indexes to import full data during off-peak hours based on your business workload.

• The system automatically converts time-type values such as datetime and timestamp in PolarDB cluster tables to milliseconds. Set the type of the corresponding application table fields to TIMESTAMP.

• Documents (in full data) that do not meet the data source filter conditions are filtered out, and documents with the same primary key values in the corresponding application table are also deleted.

• If the data source has no incremental data for a long period of time (15 days or more), data synchronization may become abnormal. In this case, manually perform index rebuilding or an offline change to resolve the issue.

• If SSL certificate encryption is enabled for PolarDB, make sure that the certificate has not expired. An expired certificate causes connection errors. Renew the SSL certificate in a timely manner.

• The China (Qingdao) region does not support PolarDB data source configuration.

• When you synchronize data from a PolarDB data source by using OpenSearch, you must add the IP address ranges of OpenSearch servers to the security settings of the corresponding RDS or PolarDB instance. The following table lists the IP address whitelists for each region:

  Region	IP address
  China (Hangzhou)	100.104.190.128/26,100.104.241.128/26
  China (Beijing)	100.104.16.192/26,100.104.179.0/26
  China (Shanghai)	100.104.37.0/26,100.104.46.0/26
  China (Shenzhen)	100.104.87.192/26,100.104.132.192/26
  China (Zhangjiakou)	100.104.155.192/26,100.104.238.64/26
  Germany	100.104.127.0/26,100.104.35.192/26
  US	100.104.193.128/26,100.104.119.128/26
  Singapore	100.104.58.192/26,100.104.74.192/26

Account authorization

• When you connect PolarDB, you must authorize access to the cluster and enter an account and password. Choose the account and password carefully when you connect for the first time.

• [Ensure account permissions] Make sure that the account has permissions to view all tables in the database (a limit of the upstream DTS service), so that show create table *. * can be executed correctly. Otherwise, real-time synchronization may encounter problems.

• [Minimize account permission changes] Account changes prevent the current real-time task from consuming data normally and also affect the creation of new versions. If you change the account password, you must delete the instance and reconnect the database.

FAQ

• If index rebuilding gets stuck after you configure a PolarDB data source, create a test table in the database where the data tables reside, and write or update 1 to 2 records per minute to ensure that binlogs are continuously generated during index rebuilding.

• If the PolarDB cluster of an Advanced Edition application had overdue payments during this period but the overdue payments were later settled, you can directly trigger a manual index rebuilding.

• The PolarDB cluster access password cannot contain the % symbol. Otherwise, index rebuilding tasks fail. (Error message: Illegal hex characters in escape (%) pattern.)

• The system requires that primary key values in an application table be unique. If primary key values are duplicated across sharded tables, they are overwritten. You can use the StringCatenateExtractor data source plug-in to concatenate multiple field values. Set the source fields to pk,$table (replace pk with the primary key field of the PolarDB cluster table; $table is a default system variable that indicates the corresponding database table name), and set the concatenation character to - (customizable).

For example, if the PolarDB cluster table is my_table_0 and the primary key field value is 123456, the new primary key value after concatenation is 123456-my_table_0.

• To filter data based on a date or datetime field in a database table, assuming the field name is createtime, the time format in the data source filter condition must be createtime>'2018-03-01 00:00:00'. If you use a format such as createtime>'2018-3-1 00:00:00', an error is reported.

Configure a PolarDB data source

• Configure a PolarDB data source when you quickly build an application.

• For an existing application, you can modify the configuration on the application details page by using "Offline change".

Configuration steps and notes in the console

1. When you create or modify an application, in Step 3: Data Source, add or edit a data source, select PolarDB as the data source, and click Create Database.

2. After you enter the PolarDB data source information, click the Connect button.

Parameter	Description
Cluster ID	The ID of the PolarDB cluster, which you can obtain in the PolarDB console (case-sensitive). Example format of the cluster ID: pc-uf6c056ny9tiaj1l7
Database Name	The name of the database to connect to in the instance (case-insensitive).
Username	The database account used to obtain the table schemas and full data of the database (case-sensitive).
Password	The password of the account.

OpenSearch attempts to connect and returns a result message based on the situation:

Message	Solution
The PolarDB cluster does not exist in the current region of the current user	Check whether the cluster ID is correct and make sure that the PolarDB cluster resides in the same region as the OpenSearch application. If the error persists when these conditions are met, submit a ticket.
Failed to connect to the database service	Check whether the PolarDB connection information is correct, including the cluster ID, database name, username, and password.
The table does not exist in the current PolarDB cluster	Check whether the table name is correct and whether the table actually exists in the PolarDB database.
PolarDB cluster configuration item issue	Go to the Parameters page in the PolarDB console, modify the corresponding configuration items, and then try again.

3. After the PolarDB data source is connected, select data tables. On the page that appears after the data source connection is established, select the corresponding tables and click OK.

In the Data Tables list on the left, select the target tables and click the >> button to add them to the Selected list on the right.

• Select or enter the names of the tables to access in the database (case-sensitive).

• Sharded table rules in the table_* format are supported, such as table_a and table_b.

4. If the connection is successful, configure the fields. OpenSearch automatically obtains the table fields. For more information about data source plug-ins, see this topic.

To add a data source plug-in, click the + button in the Content Conversion column of the field mapping row. After you complete the field mappings, click OK.

5. Configure the PolarDB data source filter conditions (not supported in Standard Edition), configure the data source synchronization method, and then click Finish to complete the application schema configuration.

Data Synchronization Method provides three options: Automatic Synchronization, DTS Data Subscription Instance, and No Automatic Synchronization.

• An OpenSearch application table can also be configured with multiple data sources, but the table schemas and configurations of these data sources must ultimately be identical.

• If a message indicates that automatic synchronization is not supported, use DTS real-time synchronization.

• The filter conditions configured for a PolarDB data source only pull records that meet the conditions. For more information, see Data source filter conditions.