#1 2023-03-23 11:54:29

tw102609
Member
Registered: 2019-03-15

Groovy script data source and temp tables

Hello,
I have a question regarding the dynamic data source (groovy script) and the application/database behavior using these reports. We are using a dynamic data source for a report that we would like to schedule every 5 minutes. It seems that the report that uses the dynamic data source (groovy script) creates a set of "temp tables" in the database however these are actual tables in the schema. rs_tmptbl_1_1_x (for example rs_tmptbl_1_1_285, rs_tmptbl_1_1_286, etc)

We have a tool that takes periodic backups of our databases and when we scheduled the report every 5 min that uses the groovy script data source the backup process began failing.
They may be called temporary tables, but to the database they're not, and the backup tool can't backup what's not there because the app drops a table halfway through a backup cycle. For example, it expects to backup rs_tmptbl_1_1_285 but the table is not there by the time the backup process gets to that table.

I was wondering if there is more information you could provide on this behavior and how we could come up with a solution that would allow us to schedule a report that uses the groovy every 5 min and avoid this backup problem. Is there any setting where we can control how often the temp tables are dropped? That could allow us to increase the time between when they are created and dropped and allow the backup to take place. Thanks in advance!

Offline

#2 2023-04-12 09:31:41

eduardo
Administrator
Registered: 2016-11-01
Website

Re: Groovy script data source and temp tables

Hi tw102609,

you can use https://mysqldump.guru/mysqldump-ignore-table.html to ignore tables during your backup process.
You can also create a script for this based on regular expressions (for the table names): https://stackoverflow.com/questions/253 … -a-pattern

Regards,
Eduardo

Offline

Board footer

Powered by FluxBB