mariadb 10.1.34, views and prepared statements: ERROR 1615 (HY000): Prepared statement needs to be re-prepared

There are various reports over the internet of this. Seems most reports are just indicating that it's a load issue. I can however reproduce this with two connections so it can't be load. One thread to perform database backups, of the form:

export MYSQL_PWD=somethingnotthis

for db in "$\{databaselist[@]\}"; do

    mysqldump -ubackup -R --single-transaction "$\{db\}" > "/backuppath/$\{db\}.sql"

done

(My actual script does a bit more than that but for testing it can be reduced to that, used as a secondary backup mechanism.)

I restarted mariadb, then stopped the slave for the sake of eliminating that as cause. show processlist shows only two connections before I start, the slave slaving off of this host, and the interactive session I'm using to connect. I then start the backup loop (causing a third connection), and then this in the interactive session:

MariaDB [test]> create table foo(a int, primary key(a));

Query OK, 0 rows affected (0.81 sec)

MariaDB [test]> create view foo2 as select * from foo;

Query OK, 0 rows affected (0.40 sec)

MariaDB [test]> insert into foo values(1),(2),(3),(4);

Query OK, 4 rows affected (0.21 sec)

Records: 4  Duplicates: 0  Warnings: 0

MariaDB [test]> select * from foo2;

| a |

| 1 |

| 2 |

| 3 |

| 4 |

4 rows in set (0.00 sec)

MariaDB [test]> prepare test from 'select * from foo2';

Query OK, 0 rows affected (0.02 sec)

Statement prepared

MariaDB [test]> execute test;

ERROR 1615 (HY000): Prepared statement needs to be re-prepared

At this point I still only see three connections in processlist: mysqldump, my interactive and the slave reading replication logs.

My system has nearly 25000 tables and views, will likely go up to to nearly 26000 soon, so I should increase these two variables anyway:

| Variable_name          | Value |

| table_definition_cache | 512   |

| table_open_cache       | 512   |

But since each database only has around 105 tables, I still don't get why this is happening.

If mysqldump is holding 105 tables open, and I'm using 2 (foo table and foo2 view), then "forced open" should still be <110, which is well below the cache limit.

Each of the 248 databases will soon be expanded to utilize the same views and prepared statements, so I'm afraid the problem is going to get worse. I'll be upping those values to 10240 anyway, so hopefully it'll avoid the issue, but I still don't see how this can be happening. Normally we don't see >500 concurrent connections, most of them idle at any given point in time.

MariaDB [test]> show status like 'open_table%';

| Variable_name          | Value |

| Open_table_definitions | 513   |

| Open_tables            | 512   |

So it's definitely hitting those limits. Increasing those variables to 5120 allows the status values to start growing immediately. During this time I can once more issue "execute test". However, once it reaches 5120 again, same problem.

I have three problems here:

1. The prepared statement (according to what I understand) should be valid until there are DDL changes to the underlying tables (and views?) - this doesn't seem to be the case.
2. If the views/tables that is in use by prepared statement, requires it to be recreated, surely the tables should just be re-opened and one that's no longer required to be open should be expunged from the cache?
3. Once the backups have finished I need to restart the affected server, simply deallocating and re-preparing, or even reconnecting the clients is not good enough.

As per previous reports, I can reproduce this even if the test DB isn't even in the backup list (nor was it during my testing above).

If I'm not mistaken I'm going to need to set my open_{table,definition}_cache values to some value larger than my tables in order to have an operational system. This doesn't seem right.