On PolarDB for MySQL, you can use OSS foreign tables to directly query CSV-formatted data stored on OSS and reduce storage costs. This topic describes how to use OSS foreign tables to access OSS data.
Prerequisites
Your PolarDB for MySQL cluster meets one of the following requirements:
A cluster of PolarDB for MySQL 8.0.1 whose revision version is 8.0.1.1.25.4 or later.
A cluster of PolarDB for MySQL 8.0.2 whose revision version is 8.0.2.2.1 or later.
For more information about how to check the cluster version, see Query the engine version.
How it works
You can use OSS foreign tables to store cold data (the data that is queried at low frequency) in the CSV format on OSS and then query and analyze cold data. The following figure shows the process.
CSV-formatted data can be of the numeric, date and time, and string types and set to NULL. The following table describes the supported data types.
Geospatial data types are not supported.
You cannot query compressed files in the CSV format.
The NULL value is supported on a cluster of PolarDB for MySQL 8.0.1 whose revision version is 8.0.1.1.28 or later and a cluster of PolarDB for MySQL 8.0.2 whose revision version is 8.0.2.2.5 or later.
Data types
Data type
Size
Range (signed)
Range (unsigned)
Description
TINYINT
1 Byte
-128~127
0~255
A tiny integer value
SMALLINT
2 Bytes
-32768~32767
0~65535
An integer value
MEDIUMINT
3 Bytes
-8388608~8388 607
0~16777215
An integer value
INT or INTEGER
4 Bytes
-2147483648~2147483647
0~4294967295
An integer value
BIGINT
8 Bytes
-9,223,372,036,854,775,808~9223372036854775807
0~18446744073709551615
A big integer value
FLOAT
4 Bytes
-3.402823466 E+38~-1.175494351E-38; 0; 1.175494351E-38~3.402823466351E+38
0; 1.175494351E-38~3.402823466E+38
A single-precision floating-point value
DOUBLE
8 Bytes
-2.2250738585072014E-308~-1.7976931348623157E+308; 0; 1.7976931348623157E+308~2.2250738585072014E-308
0; 1.7976931348623157E+308~2.2250738585072014E-308
A double-precision floating-point value
DECIMAL
For DECIMAL(M,D), it is M+2 if M>D. Otherwise, it is D+2.
Depends on the values of M and D.
Depends on the values of M and D.
A decimal value
Date and time types
Data type
Size
Range
Format
Description
DATE
3 Bytes
1000-01-01~9999-12-31
YYYY-MM-DD
Date values
TIME
3 Bytes
-838:59:59~838:59:59
HH:MM:SS
A time value or duration
YEAR
1 Byte
1901~2155
YYYY
A year value
DATETIME
8 Bytes
1000-01-01 00:00:00~9999-12-31 23:59:59
YYYY-MM-DD HH:MM:SS
A combined date and time value
NoteThe month and date of this type must have two digits. For example, January 1 , 2020 must be written as 2020-01-01, instead of 2020-1-1. The query cannot be executed correctly if 2020-1-1 is pushed down to OSS.
TIMESTAMP
4 Bytes
1970-01-01 00:00:00~2038-01-19 03:14:07
YYYY-MM-DD HH:MM:SS
A timestamp (combined date and time) value
NoteThe month and date of this type must have two digits. For example, January 1 , 2020 must be written as 2020-01-01, instead of 2020-1-1. The query cannot be executed correctly if 2020-1-1 is pushed down to OSS.
String types
Data type
Size
Description
CHAR
0~255 Bytes
A string value of fixed length
VARCHAR
0~65535 Bytes
A string value of variable length
TINYBLOB
0~255 Bytes
A binary string of no more than 255 characters
TINYTEXT
0~255 Bytes
A short string value
BLOB
0~65535 Bytes
A string value in the binary form
TEXT
0~65535 Bytes
A string value
MEDIUMBLOB
0~16777215 Bytes
A medium string value in the binary form
MEDIUMTEXT
0~16777215 Bytes
A medium string value
LONGBLOB
0~4294967295 Bytes
A long string value in the binary form
LONGTEXT
0~4294967295 Bytes
A long string value
NULL value
Insert the NULL value.
Insert the NULL value into an OSS foreign table.
To insert the NULL value into an OSS foreign table, you must set
NULL_MARKER
when you create the table. By default, theNULL_MARKER
value of an OSS foreign table is NULL. You can execute theSHOW CREATE TABLE
statement to view the value:show create table t1;
Sample result:
show create table t1; +-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | t1 | CREATE TABLE `t1` ( `id` int(11) DEFAULT NULL ) ENGINE=CSV DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci /*!99990 800020204 NULL_MARKER='NULL' */ CONNECTION='server_name' | +-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec)
Inserts the NULL value into a CSV file.
If you insert a
NULL_MARKER
in a field in a CSV file without enclosing theNULL_MARKER
in double quotation marks, PolarDB for MySQL recognizes it as NULL.NoteIf you enclose the
NULL_MARKER
in double quotation marks, PolarDB for MySQL recognizes it as a string and the NULL value cannot be discovered by executing theis_null
statement. If the data type of the parameter to which the NULL value is assigned in the CSV file does not match that in the OSS foreign table, an error is reported.The
NULL_MARKER
value cannot contain only digits or be left empty. In addition, it cannot contain the following four characters:"
,\n
,\r
, or,
Example: Execute the following statement to create an OSS foreign table:
CREATE TABLE `t1` ( `id` int(11) DEFAULT NULL, `name` varchar(20) DEFAULT NULL, `time` timestamp NULL DEFAULT NULL ) ENGINE=CSV NULL_MARKER='NULL' CONNECTION='server_name';
If the data file contains the following data:
1,"xiaohong","2022-01-01 00:00:00" NULL,"xiaoming","2022-02-01 00:00:00" 3,NULL,"2022-03-01 00:00:00" 4,"xiaowang",NULL
The following OSS data is obtained if you query the OSS foreign table:
select * from t1; +------+----------+---------------------+ | id | name | time | +------+----------+---------------------+ | 1 | xiaohong | 2022-01-01 00:00:00 | | NULL | xiaoming | 2022-02-01 00:00:00 | | 3 | NULL | 2022-03-01 00:00:00 | | 4 | xiaowang | NULL | +------+----------+---------------------+ 4 rows in set (0.00 sec)
Read the NULL value.
When data is from a CSV file and if the value in the CSV file is NULL and the value in the OSS foreign table can be NULL, the current field is set to NULL.
When data is from a CSV file and if the value in the CSV file is NULL but the value in the OSS foreign table is set to NOT NULL, the data in the CSV file conflicts with the data specified in the OSS foreign table. In this case, different results are returned depending on the sql_mode values that you set for syntax verification.
If you set
sql_mode
toSTRICT_TRANS_TABLES
, an error is reported.If you set
sql_mode
to a value other thanSTRICT_TRANS_TABLES
and the current field has a default value, the value of the current field is set to the default value. If the current field does not have a default value, the current field is assigned to the default value of MySQL based on the field type. For more information, see Data Type Default Values. If a warning message is prompted, you can execute theshow warnings;
statement to view the details of the warning message.
NoteYou can execute the
show variables like "sql_mode";
statement to view the current syntax verification rule. You can also modify thesql_mode
parameter on the Parameters page of the console to modify the current syntax verification rule. For more information, see Modify parameters.Example: Create an OSS foreign table named
t
, and then set theid
field to NOT NULL and no default value.CREATE TABLE `t` ( `id` int(11) NOT NULL ) ENGINE=CSV CONNECTION="server_name";
If the
t.CSV
file contains the following data:NULL 2
If you use an OSS foreign table to read data from a CSV file, one of the following scenarios occurs:
If
sql_mode
is set toSTRICT_TRANS_TABLES
and when you execute the following statement to query the data in the CSV file:select * from t;
The following error message is reported:
ERROR 1364 (HY000): Field 'id' doesn't have a default value
If the
sql_mode
is set to a value other thanSTRICT_TRANS_TABLES
and when you execute the following statement to query the data in the CSV file:select * from t;
A similar result is returned:
+----+ | id | +----+ | 0 | | 2 | +----+ 2 rows in set, 1 warning (0.00 sec)
0 is the default value of MySQL.
Execute the following statement to view the details of the warning message:
show warnings;
A similar result is returned:
+---------+------+-----------------------------------------+ | Level | Code | Message | +---------+------+-----------------------------------------+ | Warning | 1364 | Field 'id' doesn't have a default value | +---------+------+-----------------------------------------+ 1 row in set (0.00 sec)
Limits
You can use OSS foreign tables to query only data in the CSV format.
You can perform three operations on OSS foreign tables: CREATE, SELECT, and DROP.
NoteThe DROP operation delete only table information on PolarDB for MySQL, rather than data files on OSS.
OSS foreign tables do not support indexing, partitioning, or transactions.
Parameters
You can view or modify the parameters listed in the following table on the Parameters page of the console.
Parameter | Level | Description |
loose_csv_oss_buff_size | Session | The memory size occupied by the current OSS thread. Default value: 134217728. Unit: byte. Valid values: 4096 to 134217728 |
loose_csv_max_oss_threads | Global | The number of OSS threads that are allowed to run. Default value: 1. Valid values: 1 to 100. |
The maximum memory for OSS is loose_csv_max_oss_threads * loose_csv_oss_buff_size
.
When the OSS is used, we recommend that the total memory size for OSS is less than 5% of the memory capacity of the current node. Otherwise, an out-of-memory issue may occur.
Procedure
Upload data in the CSV format to OSS.
You can use the ossutil tool to upload local data in the CSV format to a remote OSS bucket.
NoteThe OSS directory for CSV files must be the
DATABASE
oross_prefix
directory on the OSS server.The CSV file name must be
OSS foreign table name.CSV
and the CSV extension must be in uppercase. For example, if the OSS foreign table name ist1
, the CSV file name must bet1.CSV
.The data fields in the CSV file must match the fields of the OSS foreign table. For example, if the OSS foreign table
t1
has only the fieldid
of theINT
type, the CSV file can have only one field of theINT
type.We recommend that you directly upload the local MySQL data file and create the OSS foreign table based on the table definitions.
Add OSS connection information.
You can add OSS connection information by creating an OSS server.
NoteOther methods to connect to OSS has been disabled due to security risks. You can only create an OSS server to add OSS connection information and connect to OSS.
On a cluster of PolarDB for MySQL 8.0.1 whose revision version is 8.0.1.1.28 or later and a cluster of PolarDB for MySQL 8.0.2 whose revision version is 8.0.2.2.5 or later, execute the following statement:
CREATE SERVER <server_name> FOREIGN DATA WRAPPER oss OPTIONS ( EXTRA_SERVER_INFO '{"oss_endpoint": "<my_oss_endpoint>","oss_bucket": "<my_oss_bucket>","oss_access_key_id": "<my_oss_access_key_id>","oss_access_key_secret": "<my_oss_access_key_secret>","oss_prefix":"<my_oss_prefix>","oss_sts_token":"<my_oss_sts_token>"}' );
NoteOn a cluster of PolarDB for MySQL 8.0.1 whose revision version is 8.0.1.1.29 or later and a cluster of PolarDB for MySQL 8.0.2 whose revision version is 8.0.2.2.6 or later, you can specify the
my_oss_sts_token
parameter.The
DATABASE
parameter is supported. If bothDATABASE
andmy_oss_prefix
parameters exist in the OSS server that you create, the final path of the file ismy_oss_prefix/DATABASE
. For more information about how to set theDATABASE
parameter, see the following sections.
The following table describes the parameters in the preceding statement.
Parameter
Type
Required
Description
server_name
String
Yes
The name of the OSS server.
NoteThe name must be globally unique. It is case-insensitive. The name can be up to 64 characters in length. A name that contains more than 64 characters is automatically truncated. You can specify the OSS server name as a quoted string.
my_oss_endpoint
String
Yes
The endpoint of the OSS server.
NoteIf you access your database from an Alibaba Cloud server, use an internal endpoint to avoid incurring Internet traffic. An internal endpoint contains keyword internal.
Example:
oss-cn-xxx-internal.aliyuncs.com
.my_oss_bucket
String
Yes
The bucket where the data file is stored. You must create the bucket on OSS before you import data.
NoteWe recommend that you deploy the bucket in the same zone as the PolarDB for MySQL cluster to reduce network latency.
my_oss_access_key_id
String
Yes
The AccessKey ID of the RAM user or Alibaba Cloud account.
For more information about how to create an AccessKey pair, see Create an AccessKey pair.
my_oss_access_key_secret
String
Yes
The AccessKey secret of the RAM user or Alibaba Cloud account.
For more information about how to create an AccessKey pair, see Create an AccessKey pair.
my_oss_prefix
String
No
The OSS directory for the CSV file.
my_oss_sts_token
String
No
The temporary access credentials provided by STS.
NoteThis parameter is required if you Use temporary credentials provided by STS to access OSS.
The
my_oss_sts_token
parameter has a default expiration time. If themy_oss_sts_token
value expires, you must execute the following statement to reset allEXTRA_SERVER_INFO
values:ALTER SERVER server_name OPTIONS(EXTRA_SERVER_INFO '{"oss_endpoint": "<my_oss_endpoint>", "oss_bucket": "<my_oss_bucket>", "oss_access_key_id": "<my_oss_access_key_id>", "oss_access_key_secret": "<my_oss_access_key_secret>", "oss_prefix":"<my_oss_prefix>", "oss_sts_token": "<my_oss_sts_token>"}');
NoteSERVERS_ADMIN permissions are required when you create an OSS server. You can execute the
SHOW GRANTS FOR the current user
statement to check whether the current user has the SERVERS_ADMIN permissions. A privileged account has the SERVERS_ADMIN permissions by default, and can grant the SERVERS_ADMIN permissions to standard accounts.If you are using a privileged account, you can execute the
SELECT Server_name, Extra_server_info FROM mysql.servers;
statement to view the information of the OSS server that you create. The values of theoss_access_key_id
andoss_access_key_secret
parameters are encrypted for security reasons.
On a cluster of PolarDB for MySQL 8.0.1 whose revision version is between 8.0.1.1.25.4 and 8.0.1.1.28 and a cluster of PolarDB for MySQL 8.0.2 whose revision version is between 8.0.2.2.1 and 8.0.2.2.5, execute the following statement:
CREATE SERVER <server_name> FOREIGN DATA WRAPPER oss OPTIONS (DATABASE '<my_database_name>', EXTRA_SERVER_INFO '{"oss_endpoint": "<my_oss_endpoint>","oss_bucket": "<my_oss_bucket>","oss_access_key_id":"<my_oss_access_key_id>","oss_access_key_secret":"<my_oss_access_key_secret>"}' );
NoteOn such a cluster, the
oss_prefix
parameter is not supported.The following table describes the parameters in the preceding statement.
Parameter
Type
Required
Description
server_name
String
Yes
The name of the OSS server.
NoteThe name must be globally unique. It is case-insensitive. The name can be up to 64 characters in length. A name that contains more than 64 characters is automatically truncated. You can specify the OSS server name as a quoted string.
my_database_name
String
No
The name of the OSS directory for the CSV file.
my_oss_endpoint
String
Yes
The endpoint of the OSS server.
NoteIf you access your database from an Alibaba Cloud server, use an internal endpoint to avoid incurring Internet traffic. An internal endpoint contains keyword internal.
Example:
oss-cn-xxx-internal.aliyuncs.com
my_oss_bucket
String
Yes
The bucket where the data file is stored. You must create the bucket on OSS before you import data.
my_oss_access_key_id
String
Yes
The AccessKey ID of the RAM user or Alibaba Cloud account.
For more information about how to create an AccessKey pair, see Create an AccessKey pair.
my_oss_access_key_secret
String
Yes
The AccessKey secret of the RAM user or Alibaba Cloud account.
For more information about how to create an AccessKey pair, see Create an AccessKey pair.
Create an OSS foreign table.
After you define an OSS server, you can create an OSS foreign table on PolarDB for MySQL to establish a connection with OSS. Example:
create table t1 (id int) engine=csv connection="connection_string";
connection_string
consists of the following items which are combined with forward slashes (/):The OSS server name.
(Optional) The path of the data file in OSS.
NoteFor a cluster of PolarDB for MySQL 8.0.1 whose revision version is 8.0.1.1.28 or later and a cluster of PolarDB for MySQL 8.0.2 whose revision version is 8.0.2.2.5 or later, you can configure the path of the data file in OSS.
(Optional) The data file name.
NoteThe data file name cannot contain the
.CSV
extension.
Example:
create table t1 (id int) engine=csv connection="server_name/a/b/c/d/t1";
In the preceding example, the path of the data file on OSS is
oss_prefix/a/b/c/d/
, and the data file name ist1.CSV
.NoteYou can use only the data file name to specify the data file corresponding to the OSS foreign table. For example, if you execute the
create table t1 (id int) engine=csv connection="server_name/t2"
statement, PolarDB for MySQL searches for thet2.CSV
file in the oss_prefix directory on OSS.If you add the path of the data file in OSS to
connection_string
, you must add the data file name. Otherwise, the last segment of the path is considered as the file name when PolarDB searches for the data file.If you do not specify a data file name, the OSS file corresponding to the current table is
Name of the current table.CSV
. If you specify a data file name, the OSS file corresponding to the current table isSpecified data file name.CSV
.
After an OSS foreign table is created, you can execute the
show create table
statement to view the OSS foreign table. Check whether the CSV engine is used for the created table. If not, an early PolarDB for MySQL version may be used and the OSS engine is not supported.Query data.
The
t1
table is used in the following examples.# Query the number of records in the t1 table. SELECT count(*) FROM t1; # Query records in a specified range. SELECT id FROM t1 WHERE id < 10 AND id > 1; # Query a specified record. SELECT id FROM t1 where id = 3; # Query records by joining multiple tables. SELECT id FROM t1 left join t2 on t1.id = t2.id WHERE t2.name like "%er%";
The following table describes common error messages and causes when you query data.
NoteIf no error message is reported but a warning message is displayed when you query data, you must execute the
SHOW WARNINGS;
statement to view the error message.Error message
Cause
Solution
OSS error: No corresponding data file on the OSS engine.
The data file is not found on OSS.
Check whether the data file exists in the specified path on OSS based on the preceding rules.
If yes, check whether the data file complies with the naming rule: The CSV file name must be
OSS foreign table name.CSV
and the CSV extension must be in uppercase.If not, you must upload the data file to the specified path.
There is not enough memory space for OSS transmission. Currently requested memory %d.
Insufficient memory for OSS queries.
You can use one of the following methods to fix this error:
On the Parameters page of the console, modify
loose_csv_max_oss_threads
parameter values to allow more OSS threads to run.Execute the flush table statement to close threads for some OSS tables.
ERROR 8054 (HY000): OSS error: error message : Couldn't connect to server. Failed connect to aliyun-mysql-oss.oss-cn-hangzhou-internal.aliyuncs.com:80;
The current cluster cannot connect to the OSS server.
Check whether the current cluster is in the same zone as the OSS bucket.
If not, you must move the current cluster and the OSS bucket in the same zone.
If they are in the same zone, you can change the endpoint of the cluster to a public endpoint. If the error persists after the endpoint is modified, contact Alibaba Cloud technical support.
Query optimization
Some query conditions can be push down to the remote OSS bucket for better query efficiency. This optimization process is called engine condition pushdown
. Engine condition pushdown are subject to the following limits:
Only UTF-8 encoded CSV files are supported.
Only the following types of operators are supported in SQL statements:
Comparison operators:
>
,<
,>=
,<=
, and==
Logical operators:
LIKE
,IN
,AND
, andOR
Arithmetic operators:
+
,-
,*
, and/
.
Only a single object can be queried when you use an SQL statement. The following clauses are not supported: JOIN, ORDER BY, GROUP BY, and HAVING.
The WHERE clause cannot contain aggregation conditions. For example,
WHERE max(age) > 100
is not allowed.A maximum of 1,000 columns can be specified for an SQL statement. The column name in an SQL statement can be a maximum of 1,024 bytes in length.
A maximum of five wildcards (
%
) are supported in a LIKE clause.A maximum of 1,024 constants are supported in an IN clause.
The maximum column size and row size for a CSV object are 256 KB.
The maximum size of an SQL statement is 16 KB. A maximum of 20 expressions can be added after WHERE. Each statement supports up to 100 aggregation operations.
This feature is disabled by default. You can enable this feature by executing the SET SESSION optimizer_switch='engine_condition_pushdown=on';
statement.
Queries that meet the preceding conditions are pushed down to the remote OSS bucket. You can use the OSS foreign table execution plan to view which query conditions are pushed down to pushed down to the remote OSS bucket.
View the execution plan of an OSS foreign table by executing the
EXPLAIN
statement. Example:EXPLAIN SELECT count(*) FROM `t1` WHERE `id` > 5 AND `id` < 100 AND `name` LIKE "%1%%%%%" GROUP BY `id` ORDER BY `id` DESC; +----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+----------------------------------------------------------------------------------------------------------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+----------------------------------------------------------------------------------------------------------------------------------+ | 1 | SIMPLE | t1 | NULL | ALL | NULL | NULL | NULL | NULL | 15000 | 1.23 | Using where; With pushed engine condition ((`test`.`t1`.`id` > 5) and (`test`.`t1`.`id` < 100)); Using temporary; Using filesort | +----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+----------------------------------------------------------------------------------------------------------------------------------+ 1 row in set, 1 warning (0.00 sec)
The conditions behind
With pushed engine condition
can be pushed down to the remote OSS bucket. The conditions`name` LIKE "%1%%%%%"
andGROUP BY `id` ORDER BY `id` DESC
cannot be pushed down to the remote OSS bucket. They are only executed on the local OSS server.View the execution plan of an OSS foreign table in the
tree
format. Example:EXPLAIN FORMAT=tree SELECT SELECT count(*) FROM `t1` WHERE `id` > 5 AND `id` < 100 AND `name` LIKE "%1%%%%%" Y `id` ORDER BY `id` DESC; +-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | EXPLAIN | +-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | -> Sort: <temporary>.id DESC -> Table scan on <temporary> -> Aggregate using temporary table -> Filter: (t1.`name` like '%1%%%%%') (cost=1690.00 rows=185) -> Table scan on t1, extra ( engine conditions: ((t1.id > 5) and (t1.id < 100)) ) (cost=1690.00 rows=15000) | +-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec)
The conditions behind
engine conditions:
can be pushed down to the remote OSS bucket. The conditions`name` LIKE "%1%%%%%"
andGROUP BY `id` ORDER BY `id` DESC
cannot be pushed down to the remote OSS bucket. They are only executed on the local OSS server.NoteThe cluster version must be PolarDB for MySQL 8.0.2. You can check the cluster version as stated in Query the engine version.
View the execution plan of an OSS foreign table in the
JSON
format. Example:EXPLAIN FORMAT=json SELECT count(*) FROM `t1` WHERE `id` > 5 AND `id` < 100 AND `name` LIKE "%1%%%%%" GROUP BY `id` ORDER BY `id` DESC; +-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | EXPLAIN | +-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | { "query_block": { "select_id": 1, "cost_info": { "query_cost": "1875.13" }, "ordering_operation": { "using_filesort": false, "grouping_operation": { "using_temporary_table": true, "using_filesort": true, "cost_info": { "sort_cost": "185.13" }, "table": { "table_name": "t1", "access_type": "ALL", "rows_examined_per_scan": 15000, "rows_produced_per_join": 185, "filtered": "1.23", "engine_condition": "((`test`.`t1`.`id` > 5) and (`test`.`t1`.`id` < 100))", "cost_info": { "read_cost": "1671.49", "eval_cost": "18.51", "prefix_cost": "1690.00", "data_read_per_join": "146K" }, "used_columns": [ "id", "name" ], "attached_condition": "(`test`.`t1`.`name` like '%1%%%%%')" } } } } } | +-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set, 1 warning (0.00 sec)
The conditions behind
engine conditions:
can be pushed down to the remote OSS bucket. The conditions`name` LIKE "%1%%%%%"
andGROUP BY `id` ORDER BY `id` DESC
cannot be pushed down to the remote OSS bucket. They are only executed on the local OSS server.
If the following error occurs, some characters in the current OSS data file do not meet the requirements for OSS pushdown.
OSS error: The current query does not support engine condition pushdown. You need to use NO_ECP() hint or set optimizer_switch = 'engine_condition_pushdown=OFF' to turn off the condition push down function.
You can use hints or the optimizer_switch variable to manually disable the engine condition pushdown feature.
Hints
You can use a hint to disable the engine condition pushdown feature for a query. In the following example, the engine condition pushdown feature is disabled for the
t1
table:SELECT /*+ NO_ECP(t1) */ `j` FROM `t1` WHERE `j` LIKE "%c%" LIMIT 10;
optimizer_switch
You can use the optimizer_switch variable to disable the engine condition pushdown feature for all queries on the current session.
SET SESSION optimizer_switch='engine_condition_pushdown=off'; # Set engine_condition_pushdown to off to disable the condition pushdown feature for all queries on the current session.
You can execute the following statement to view the optimizer_switch status and determine whether the engine_condition_pushdown feature is enabled for all queries in the current session:
select @@optimizer_switch;
Synchronize OSS server information between multiple nodes
The primary node and read-only nodes of a PolarDB for MySQL cluster share the same OSS server. This ensures that these nodes can access data in OSS. In addition, OSS server information synchronization between these nodes is lock-free to ensure that operations on these nodes are independent.
After you modify the OSS server information, the modifications are synchronized to read-only nodes in a lock-free manner. If a thread on a read-only node holds the lock for the OSS server, OSS server information synchronization may be delayed. In this case, you can execute the /*force_node='pi-bpxxxxxxxx'*/ flush privileges;
or /*force_node='pi-bpxxxxxxxx'*/flush table oss_foreign_table;
statement to manually update the OSS server information on read-only nodes.