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

分手后的思念是犯贱 2023-06-25 06:15 9阅读 0赞

From: [https://www.ywnds.com/?p=12609][https_www.ywnds.com_p_12609]

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

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

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

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

table\_open\_cache

<table> 
 <tbody> 
  <tr> 
   <td><strong>System Variable</strong></td> 
   <td><strong>Name</strong></td> 
   <td colspan="2">table_open_cache</td> 
  </tr> 
  <tr> 
   <td><strong>Scope</strong></td> 
   <td colspan="2">Global</td> 
  </tr> 
  <tr> 
   <td><strong>Dynamic</strong></td> 
   <td colspan="2">Yes</td> 
  </tr> 
  <tr> 
   <td><strong>Permitted Values</strong></td> 
   <td><strong>Type</strong></td> 
   <td colspan="2">integer</td> 
  </tr> 
  <tr> 
   <td><strong>Default</strong></td> 
   <td colspan="2">2000</td> 
  </tr> 
  <tr> 
   <td><strong>Minimum</strong></td> 
   <td colspan="2">1</td> 
  </tr> 
  <tr> 
   <td><strong>Maximum</strong></td> 
   <td colspan="2">524288</td> 
  </tr> 
 </tbody> 
</table>

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

table\_definition\_cache

<table> 
 <tbody> 
  <tr> 
   <td><strong>System Variable</strong></td> 
   <td><strong>Name</strong></td> 
   <td colspan="2">table_definition_cache</td> 
  </tr> 
  <tr> 
   <td><strong>Scope</strong></td> 
   <td colspan="2">Global</td> 
  </tr> 
  <tr> 
   <td><strong>Dynamic</strong></td> 
   <td colspan="2">Yes</td> 
  </tr> 
  <tr> 
   <td><strong>Permitted Values</strong></td> 
   <td><strong>Type</strong></td> 
   <td colspan="2">integer</td> 
  </tr> 
  <tr> 
   <td><strong>Default</strong></td> 
   <td colspan="2">-1 (autosized)</td> 
  </tr> 
  <tr> 
   <td><strong>Minimum</strong></td> 
   <td colspan="2">400</td> 
  </tr> 
  <tr> 
   <td><strong>Maximum</strong></td> 
   <td colspan="2">524288</td> 
  </tr> 
 </tbody> 
</table>

表定义信息缓存是从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;
    }

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

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(内存)的影响][table_open_cache_table_definition_cache_MySQL]

[https_www.ywnds.com_p_12609]: https://www.ywnds.com/?p=12609
[table_open_cache_table_definition_cache_MySQL]: http://seanlook.com/2017/10/13/mysql-table_open_cache_file_limits/