How to increase the timeout against a MySQL database
-
I've been tasked with writing a SSRS report against a MySQL database. This is the first time I've ever worked with MySQL, so new experiences for me. With the help and recommendation from one of my colleagues, I've written a SSRS report against the largest table in the database, as a proof-of-concept type of thing. (We weren't even sure that SSRS could run against MySQL.) I used an ODBC connection the SSRS report used, in order to run against the MySQL database.
When I first ran the report, I was doing a SELECT *. Now, before you all panic, this particular table isn't all that big. It has about 56K records in it. However, it timed out before it returned. So next I restricted the number of columns returned to I think about 6 columns (there's a boat load of columns - this database was built with the idea of taking an Excel spreadsheet and just put it into a table in the MySQL database). However, even that timed out, before returning just the 6 columns I selected from all of the records. So, finally I restricted the number of records returned to only those people whose last name began with "S". That finally returned the records and I then proved that SSRS could work against a MySQL database.
Nevertheless, the users are going to want to report against all 56K records, not just some subset. I got into the ODBC Manager to see if I could find a parameter there to increase the timeout period, but it wasn't there. So, how do I increase the timeout when running against a MySQL database?
Kindest Regards, Rod Connect with me on LinkedIn.
-
i think you can just modify the connection string in your shared data source.
by default, it looks something like this, just a servername and a databasename, but you can modify it to add a connect timeout in seconds:
DataSource=DESKTOP-2MS3DR5;Initial Catalog=Lab;
DataSource=DESKTOP-2MS3DR5;Initial Catalog=Lab;Connect Timeout=180
[but you can modify the connection string with additional commands:
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
connectionstrings.com suggested a slightly different syntax, but both are valid:
https://www.connectionstrings.com/mysql/
Server=myServerAddress;Database=myDataBase;Uid=myUsername;Pwd=myPassword;
Connection Timeout=5;
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
There are several different timeout settings so some of what you are hitting depends upon what timeout you hit. I wouldn't expect it to be the connection as that would be independent of whatever query you run. More likely session or execution. Did you note how much time before it timed out before? That could help in tracking down which timeout settings based on the defaults. Were you able to monitor MySQL and see if the query was executing when you tested that out?
Sue
- Sue_H wrote:
There are several different timeout settings so some of what you are hitting depends upon what timeout you hit. I wouldn't expect it to be the connection as that would be independent of whatever query you run. More likely session or execution. Did you note how much time before it timed out before? That could help in tracking down which timeout settings based on the defaults. Were you able to monitor MySQL and see if the query was executing when you tested that out? Sue
No, I did not. But I don't know how to monitor it from MySQL, either. I haven't worked with MySQL, until last week when I installed it on my dev box for the first time.
Kindest Regards, Rod Connect with me on LinkedIn.
-
Is it always around 30 mins or 10 mins or less? Any idea at all? A lot of the defaults are at 30 mins but not all. You might be able to get an idea by checking the Execution log view in the ReportServer database.
Sue
-
I don't know, Sue. I hope to get to it today. Yesterday was eaten by meetings; today we've got to get a report out by noon. Maybe after that...
Kindest Regards, Rod Connect with me on LinkedIn.
[Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply