-1

How to update the command timeout in mysql database. I know how to do it at application level but I want to change the command timeout at db end. So that I have to change it only at one place. At application end I am using 

"default command timeout = 0"

and it is working fine. But can anyone tell me how to do the same at db end?

Shadow
  • 33,525
  • 10
  • 51
  • 64
mayank bansal
  • 61
  • 9
  • I would imagine, that googling "configure mysql database" would return plenty of results. – fancyPants Jul 24 '17 at 08:35
  • have you taken a look at your my.cnf file? – treyBake Jul 24 '17 at 08:37
  • @fancyPants I have been searching the same from 4 hours, but not able to found out anything related to command timeout in configuration. Everywhere there is a reference for connection timeout only. – mayank bansal Jul 24 '17 at 08:39
  • @ThisGuyHasTwoThumbs yes, but in my.cnf also there is no parameter called - "command timeout". – mayank bansal Jul 24 '17 at 08:41
  • 2
    I googled for 10 seconds and I found out, that command timeout relates to the connector to the database, not the database itself. Adapt your google search and you'll find the solution. – fancyPants Jul 24 '17 at 08:42

1 Answers1

1

The answer is: you cannot. Why? Because command timeout is specific to the MySQL .Net Connector:

Sets the default value of the command timeout to be used. This does not supersede the individual command timeout property on an individual command object. If you set the command timeout property, that will be used. This option was added in Connector/Net 5.1.4

As MySQL documentation on MySqlCommand object says:

Prior to MySQL Connector/Net 6.2, MySqlCommand.CommandTimeout included user processing time, that is processing time not related to direct use of the connector. Timeout was implemented through a .NET Timer, that triggered after CommandTimeout seconds. This timer consumed a thread.

MySQL Connector/Net 6.2 introduced timeouts that are aligned with how Microsoft handles SqlCommand.CommandTimeout. This property is the cumulative timeout for all network reads and writes during command execution or processing of the results. A timeout can still occur in the MySqlReader.Read method after the first row is returned, and does not include user processing time, only IO operations. The 6.2 implementation uses the underlying stream timeout facility, so is more efficient in that it does not require the additional timer thread as was the case with the previous implementation.

So, before 6.2 the command timeout included client side processing time - this obviously cannot be taken into account on the server side.

Since 6.2, the behaviour has changed and does not include client side processing time. However, it is now defined as "cumulative timeout for all network reads and writes". In MySQL server you can configure various timeouts, but those are not cumulative, nor apply to both reads and writes. There are separate read and write timeouts only. So, there is no mapping of this connector parameter to the MySQL configuration.

Shadow
  • 33,525
  • 10
  • 51
  • 64
  • I am getting timeout expired exception while executing the query as it is taking more time. So Is there any alternative solution that I can handle at db end? (If there is any parameter in db that can handle the timeout of executing query.) – mayank bansal Jul 24 '17 at 09:44
  • It depends on what timeout error you receive. There are quite a few timeout related settings in MySQL and each has different meaning. Also, if the timeout error is related to the command timeout, then the db configuration ill not help too much. You should ask another question in which you describe the exact error message (perhaps even shot us the relevant code) and ask for help in dealing with that code. – Shadow Jul 24 '17 at 10:26