南大通用GBase 8a在执行动态SQL Prepare EXECUTE时报错 Prepared statement needs to be re-prepared

GBase 在执行存储过程里，可以执行动态命令，当Prepare时，必须要缓冲涉及的所有的表的frm结构，如果超过了设定的参数table_definition_cache，则会报这个错误。解决方案是提高参数值。

报错样例

其中executeSQL是一个自己写的通用的执行字符串的存储过程，详情请参考

GBase 8a集群存储过程样例，动态存储过程

样例中参数值为256， 而表数量是300个。

gbase> show status like '%table_def%';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| Open_table_definitions   | 256   |
| Opened_table_definitions | 1     |
+--------------------------+-------+
2 rows in set (Elapsed: 00:00:00.01)


gbase> source /home/gbase/ttt.sql
ERROR 1615 (HY000): Prepared statement needs to be re-prepared
{STATEMENT: call executeSQL('select count(*) from ttt1 union all
select count(*) from ttt2 union all
select count(*) from ttt3 union all
select count(*) from ttt4 union all
select count(*) from ttt5 union all
select count(*) from ttt6 union all
select count(*) from ttt7 union all
select count(*) from ttt8 union all
select count(*) from ttt9 union all
select count(*) from ttt10 union all
select count(*) from ttt11 union all
select count(*) from ttt12 union all

解决方案

调高参数

gbase> set global table_definition_cache=402;
Query OK, 0 rows affected (Elapsed: 00:00:00.00)

gbase> source /home/gbase/ttt.sql
+----------+
| count(*) |
+----------+
|        0 |
|        0 |
|        0 |
|        0 |
|        0 |
|        0 |

参考

GBase 8a表打开缓冲参数table_open_cache和表定义缓冲参数table_definition_cache