Xtrabackup and JSON_TABLE(...)

MySQL & MariaDB Percona XtraBackup
1

Hello all,

It looks like some of the locks that xtrabackup takes are preventing a stored proc from using json table functions in MySQL. I searched around a bit and couldn’t find anything really relating to this problem, but this is the most suspicious thing that has changed lately.

It is late so I will dig in to test next Tuesday, but in the meantime, I was wondering if anyone else has experienced this – or knew if it is expected behavior?

2

Hi @philip_plachta

Xtrabackup uses LTFB (On Percona Server) or LIFB (On Oracle MySQL) to prevent unsafe statements during backup.

Can you please share your SP and table DDL so we can have a further look into this issue? Also, is your source server Percona Server or Oracle MySQL?

1 Like
3

I did some testing this morning and the issue was not the json_tables. We had a ‘DROP TABLE’ command instead of ‘DROP TEMPORARY TABLE’ command in the SP that we did not catch until looking over the script again.

So, xtrabackup definitely works with json table functions in oracle mysql, but hangs up on a ‘DROP TABLE COMMAND’, even if the table doesn’t exist, which is probably expected behavior.