Hi, we are having an issue with a chunky custom query used to get data for a MariaDB dataset. The query takes about 7-8 minutes to run normally. It returns a lot of rows (around 2 million I believe).
When used in Quicksight for a dataset, it basically hangs and never finishes.
The same query can be used in a MySQL dataset connecting to the same database (but we don’t want to use the MySQL dataset type because it fails to connect with SSL enabled. I opened a separate question for that one because if we could get that to work, that would be good enough for us).
I can also watch the output of SHOW PROCESSLIST and see that the query does run and complete in the normal time, it’s just that Quicksight shows the refresh as being in “Running” state and it goes forever.
I don’t think it’s the number of rows either, because I’ve tried limiting it to a small number of rows and it still doesn’t work. My guess is it’s something to do with how long it takes, but the runtime of long queries is not a problem for MySQL datasets, so I’m pretty stumped.
Any help or suggestions on how to debug or fix this would be appreciated! Thanks in advance.
*Update - I’ve been able to determine that the cutoff for the the refresh hanging is somewhere around 5 minutes and 50 seconds. Did some testing using a query like “select case when now() > ‘’ then sleep(355) else now() end as x;” to get around the bit where QuickSight figures out the columns.
Hi @perry_the_platypus - Thanks for the details. I assumed the data set is direct query based, please confirm the same. Can you please compare the execution time between maria db and my sql db from a client tool. If I remembered , timeout for a direct query is 2 mins, but let’s hear from other experts. We need to tune the sql in maria db and check why the sql is taking more time.
Tagging @David_Wong @ErikG @sagmukhe @Biswajit_1993 for their expert advise.
Regards - Sanjeeb
1 Like
Hi @Sanjeeb2022 thanks for helping out. From every client tool I’ve tested, the query takes about 7-8 minutes. Also note – I want to make sure we’re absolutely clear – we have only one database server, which is MariaDB. When I talk about using the MySQL or MariaDB, I’m only talking about the data type we choose to connect with, as shown in this screenshot of the page that shows when you click New Dataset.
That is the only thing we’re changing – the server is the same. You can usually connect to a MariaDB database with a MySQL client because MariaDB and MySQL were forked from the same code base originally, and they pretty much speak the same protocol between client and server.
As for your question, we’re not doing a direct query, we’re using the SPICE import option for all this. If there was a timeout for SPICE imports we could work around it, but it doesn’t seem intentional that these different datasource client types (MySQL and MariaDB) would have different timeouts.
1 Like
Hi @perry_the_platypus - This is bit odd. Can you please submit a ticket to AWS customer support team so that they can analyze and investigate the backend logs what is happening when you are selecting the maria db as database. Since the dataset is perfectly working when you are selecting mysql, there may be some problem with maria db side.
To raise a ticket, please follow the below link -
Regards - Sanjeeb
1 Like
Hi @Sanjeeb2022, sorry for the delay. Thanks – yes, I’ll open a support ticket, and update here if we can get it figured out.
1 Like
