MySQL视图查询报错：Prepared statement needs to be re-prepared

今天公司的项目视图查询报错，报错如：ERROR 1615 (HY000): Prepared statement needs to be re-prepared

网上找了一圈，都说调整以下值就好了：

mysql> set global table_open_cache=16384;
mysql> set global table_definition_cache=16384;

1 2	mysql> set global table_open_cache=16384; mysql> set global table_definition_cache=16384;

其实真正只调整table_definition_cache问题就解决了。这两个参数是MySQL中与表对象缓存相关的参数。

table_open_cache

System Variable	Name	table_open_cache
	Scope	Global
	Dynamic	Yes
Permitted Values	Type	integer
	Default	2000
	Minimum	1
	Maximum	524288

打开表的缓存数量。也不是定义内存大小的，而是定义可以缓存多少打开的表的文件句柄信息。如果定义的太小，那么MySQL在需要打开新表的时候就要不断的关闭已经打开的表和打开此次需要打开的表。性能会受到影响。

table_definition_cache

System Variable	Name	table_definition_cache
	Scope	Global
	Dynamic	Yes
Permitted Values	Type	integer
	Default	-1 (autosized)
	Minimum	400
	Maximum	524288

表定义信息缓存是从MySQL 5.1.3版本才开始引入的一个新的缓存区，用来存放表定义信息。当我们的MySQL中使用了较多的表的时候，此缓存无疑会提高对表定义信息的访问效率。MySQL提供了table_definition_cache参数给我们设置可以缓存的表的数量。在MySQL 5.1.25之前的版本中，默认值为128，从MySQL 5.1.25版本开始，则将默认值调整为256了，最大设置值为524288。注意，这里设置的是可以缓存的表定义信息的数目，而不是内存空间的大小。

然后就是这个两个参数跟视图访问报错有什么关系呢？有点懵逼了，就问了一个朋友@八怪，看了一下这个报错的错误码。

{ “ER_NEED_REPREPARE”, 1615, “Prepared statement needs to be re-prepared” }

分析后他应该是在函数check_and_update_table_version中抛出来的。

static bool
check_and_update_table_version(THD *thd,
                               TABLE_LIST *tables, TABLE_SHARE *table_share)
{ if (! tables->is_table_ref_id_equal(table_share))
  {
    Reprepare_observer *reprepare_observer= thd->get_reprepare_observer(); if (reprepare_observer &&
        reprepare_observer->report_error(thd)) //这里如果前面的指针为NULL则触发这个报错逻辑 { /*
        Version of the table share is different from the
        previous execution of the prepared statement, and it is
        unacceptable for this SQLCOM. Error has been reported.
      */ DBUG_ASSERT(thd->is_error()); return TRUE;
    } /* Always maintain the latest version and type */ tables->set_table_ref_id(table_share);
  }

  DBUG_EXECUTE_IF("reprepare_each_statement", return inject_reprepare(thd);); return FALSE;
}

static bool

check_and_update_table_version(THD *thd,

TABLE_LIST *tables, TABLE_SHARE *table_share)

{ if (! tables->is_table_ref_id_equal(table_share))

{

Reprepare_observer *reprepare_observer= thd->get_reprepare_observer(); if (reprepare_observer &&

reprepare_observer->report_error(thd)) //这里如果前面的指针为NULL则触发这个报错逻辑 { /*

Version of the table share is different from the

previous execution of the prepared statement, and it is

unacceptable for this SQLCOM. Error has been reported.

*/ DBUG_ASSERT(thd->is_error()); return TRUE;

} /* Always maintain the latest version and type */ tables->set_table_ref_id(table_share);

}

DBUG_EXECUTE_IF("reprepare_each_statement", return inject_reprepare(thd);); return FALSE;

}

看来他们确实有联系，但是怎么联系的说不上来，这个观察者搞毛线的我也不知道。以后再说。断点设置

breakpoint     keep y 0x0000000000ebd5f3 in main(int, char**) at /root/mysql5.7.14/percona-server-5.7.14-7/sql/main.cc:25 breakpoint already hit 1 time 4 breakpoint     keep y 0x00000000016a04bd in open_table_from_share(THD*, TABLE_SHARE*, char const*, uint, uint, uint, TABLE*, bool)
                                               at /root/mysql5.7.14/percona-server-5.7.14-7/sql/table.cc:3038 breakpoint already hit 4 times 5 breakpoint     keep y 0x0000000001519a10 in check_and_update_table_version(THD*, TABLE_LIST*, TABLE_SHARE*)
                                               at /root/mysql5.7.14/percona-server-5.7.14-7/sql/sql_base.cc:4219 breakpoint already hit 4 times 6 breakpoint     keep y 0x00000000015285bb in Table_cache::add_used_table(THD*, TABLE*) at /root/mysql5.7.14/percona-server-5.7.14-7/sql/table_cache.h:353 breakpoint already hit 2 times 7 breakpoint     keep y 0x0000000001527427 in TABLE_LIST::set_table_ref_id(enum_table_ref_type, ulonglong)
                                               at /root/mysql5.7.14/percona-server-5.7.14-7/sql/table.h:2100 8 breakpoint     keep y 0x00000000015273e3 in TABLE_LIST::set_table_ref_id(TABLE_SHARE*) at /root/mysql5.7.14/percona-server-5.7.14-7/sql/table.h:2095 breakpoint already hit 1 time

breakpoint keep y 0x0000000000ebd5f3 in main(int, char**) at /root/mysql5.7.14/percona-server-5.7.14-7/sql/main.cc:25 breakpoint already hit 1 time 4 breakpoint keep y 0x00000000016a04bd in open_table_from_share(THD*, TABLE_SHARE*, char const*, uint, uint, uint, TABLE*, bool)

at /root/mysql5.7.14/percona-server-5.7.14-7/sql/table.cc:3038 breakpoint already hit 4 times 5 breakpoint keep y 0x0000000001519a10 in check_and_update_table_version(THD*, TABLE_LIST*, TABLE_SHARE*)

at /root/mysql5.7.14/percona-server-5.7.14-7/sql/sql_base.cc:4219 breakpoint already hit 4 times 6 breakpoint keep y 0x00000000015285bb in Table_cache::add_used_table(THD*, TABLE*) at /root/mysql5.7.14/percona-server-5.7.14-7/sql/table_cache.h:353 breakpoint already hit 2 times 7 breakpoint keep y 0x0000000001527427 in TABLE_LIST::set_table_ref_id(enum_table_ref_type, ulonglong)

at /root/mysql5.7.14/percona-server-5.7.14-7/sql/table.h:2100 8 breakpoint keep y 0x00000000015273e3 in TABLE_LIST::set_table_ref_id(TABLE_SHARE*) at /root/mysql5.7.14/percona-server-5.7.14-7/sql/table.h:2095 breakpoint already hit 1 time

table_open_cache与table_definition_cache对MySQL(内存)的影响

如果您觉得本站对你有帮助，那么可以支付宝扫码捐助以帮助本站更好地发展，在此谢过。