PolarDB:Create custom temporary tables on read-only nodes

How it works

Temporary tables store intermediate query results within a session, reducing repetitive data scans and keeping data isolated between concurrent sessions. When the session ends, the table and its data are automatically dropped.

Because PolarProxy does not automatically route temporary table creation and subsequent DML operations to the same node, you must include a SQL hint (/*force_node='pi-bpxxxxxxxx'*/) in every statement that touches the temporary table. Without the hint, the statement routes to the primary node, and any subsequent statement on a different node fails with:

Table 'test.new_tbl' doesn't exist

Supported versions

Your cluster must run one of the following versions:

• PolarDB for MySQL 5.7, revision version 5.7.1.0.35 or later

• PolarDB for MySQL 8.0.1, revision version 8.0.1.1.28 or later

• PolarDB for MySQL 8.0.2, revision version 8.0.2.2.5 or later

Limitations

Prerequisites

Before you begin, make sure that you have:

• A read-only node in your cluster. To add one, see Add or remove read-only nodes

• A connection to the cluster. See Connect to a cluster

Create a temporary table on a read-only node

Include the /*force_node='pi-bpxxxxxxxx'*/ hint in every statement that creates or accesses the temporary table. Replace pi-bpxxxxxxxx with your read-only node ID.

-- Create using MEMORY engine
/*force_node='pi-bpxxxxxxxx'*/ CREATE TEMPORARY TABLE new_tbl (c1 int PRIMARY KEY, c2 varchar(100)) ENGINE=MEMORY;

-- Create using MyISAM engine
/*force_node='pi-bpxxxxxxxx'*/ CREATE TEMPORARY TABLE new_tbl (c1 int PRIMARY KEY, c2 varchar(100)) ENGINE=MYISAM;

-- Create from an existing table (copies structure only)
/*force_node='pi-bpxxxxxxxx'*/ CREATE TEMPORARY TABLE new_tbl SELECT * FROM orig_tbl LIMIT 0;

-- Insert data
/*force_node='pi-bpxxxxxxxx'*/ INSERT INTO new_tbl VALUES(1, 'test_string');

-- Query
/*force_node='pi-bpxxxxxxxx'*/ SELECT * FROM new_tbl;

To verify that the temporary table is accessible from the read-only node, connect to the cluster endpoint and run the following queries:

mysql -u <user> -h test-4.xxx.polardb.aliyuncs.com -P3306 -p<pwd>

SELECT * FROM new_tbl;

Expected output:

+----+-------------+
| c1 | c2          |
+----+-------------+
|  1 | test_string |
+----+-------------+
1 row in set (0.07 sec)

SHOW CREATE TABLE new_tbl;

Expected output:

+---------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table   | Create Table                                                                                                                                                                          |
+---------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| new_tbl | CREATE TEMPORARY TABLE `new_tbl` (
  `c1` int(11) NOT NULL,
  `c2` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`c1`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+---------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.08 sec)

Create temporary tables using stored procedures (recommended)

For batch operations, use a stored procedure. This keeps all temporary table logic in one callable unit and reduces the number of hint-annotated statements in your application code.

1. Create the stored procedure using the cluster endpoint or the primary node.

   CREATE TABLE t1 (c1 int PRIMARY KEY, c2 varchar(100));
   INSERT INTO t1 VALUES(1, 'test_string');
   
   DELIMITER //;
   CREATE  PROCEDURE tmp_p0()
   BEGIN
   
   CREATE TEMPORARY TABLE IF NOT EXISTS tmp_table engine = MYISAM AS
   SELECT * FROM t1 limit 10;
   SELECT * FROM tmp_table;
   
   DROP TABLE tmp_table;
   
   END //;
   
   DELIMITER ;

2. Execute the stored procedure on the target read-only node.

   /*force_node='pi-bpxxxxxxxx'*/ call tmp_p0();

Related topics

• HINT syntax — full reference for routing hints in PolarDB for MySQL

• Global read-only node — overview of read-only node architecture