UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
3 Pages V  1 2 3 >  (Go to first unread post)
   Reply to this topicStart new topic
> Mysql Connection Timeouts    
 
   
niesz
post Feb 28 2010, 11:07 AM
Post#1


Utter A-fishin'-ado
Posts: 17,958
Joined: 1-August 05
From: Cincinnati, Ohio, USA . . . ><((((°>


I've recently switch web hosting providers (Now using GoDaddy) in a Windows Shared Hosting environment.
've got a MySQL 5.0 DB that runs a small eCommerce website. The website runs fine.
The data for the site is managed by a locally running Access 2000 DB.
Ever since I moved to the new host, I now receive "MySQL Server has gone away" errors after about 1 minute of inactivity.
I've tried using the 3.51 connector and the 5.1 connector with the same results.
Is there any other settings I should check or other suggestions to get rid of this behavior?
Go to the top of the page
 
BananaRepublic
post Feb 28 2010, 11:16 AM
Post#2


Dungeon Cleaner
Posts: 1,504
Joined: 16-June 07
From: Banana Republic


Check the wait_timeout setting. The default is 28800 seconds (8 hours), but the host may have decreased it.
!--c1-->
CODE
SHOW VARIABLES LIKE 'wait_timeout';

http://dev.mysql.com/doc/refman/5.0/en/ser...ar_wait_timeout
HTH.
Go to the top of the page
 
niesz
post Feb 28 2010, 11:20 AM
Post#3


Utter A-fishin'-ado
Posts: 17,958
Joined: 1-August 05
From: Cincinnati, Ohio, USA . . . ><((((°>


It seems they have it set to 60.
Is there a way to increase this myself? or is this a setting that GoDaddy would have to make? Can I control this via the connection string to the Linked Table in Access?
Go to the top of the page
 
BananaRepublic
post Feb 28 2010, 12:04 PM
Post#4


Dungeon Cleaner
Posts: 1,504
Joined: 16-June 07
From: Banana Republic


If you have the privileges, try this:
!--c1-->
CODE
SET wait_timeout=XX;

This works in interactive session, at least. For the connection string, you could either use the initstmt argument, though I've never used that before:
CODE
ODBC;DRIVER={MySQL 5.1 ODBC Driver};SERVER=xxx.xxx.xxx.xxx;DATABASE=xxxx;UID=xxxx;PWD=xxx;INITSTMT=SET wait_timeout=xxxx;

Note: I'm not sure if the statement needs to be delimited or quoted somehow.
Alternatively, if you have the shell access, you would want to look for the my.ini or my.cnf and configure the timeout from there
http://dev.mysql.com/doc/refman/5.1/en/option-files.html
Within the file, you would specify this in the client section:
CODE
wait_timeout=xxx

Let me know if this get you closer...
Go to the top of the page
 
niesz
post Feb 28 2010, 12:15 PM
Post#5


Utter A-fishin'-ado
Posts: 17,958
Joined: 1-August 05
From: Cincinnati, Ohio, USA . . . ><((((°>


I ran the SET WAIT_TIMEOUT line and it seemed to 'take'.
'll have my wife try and update some items later today and let you know how it goes.
Thanks again!
hwn
Go to the top of the page
 
BananaRepublic
post Feb 28 2010, 12:22 PM
Post#6


Dungeon Cleaner
Posts: 1,504
Joined: 16-June 07
From: Banana Republic


Be aware that this is a connection-specific setting. Other connections will not share this, unless you prefix 'GLOBAL';
!--c1-->
CODE
SET GLOBAL wait_timeout=xx;

but to permanently change the default without re-specifying it every time, you'd have to edit the my.cnf or my.ini (usually on the windows, it's my.ini, but in *nix world, it's my.cnf. Go figure.)
Go to the top of the page
 
niesz
post Feb 28 2010, 08:41 PM
Post#7


Utter A-fishin'-ado
Posts: 17,958
Joined: 1-August 05
From: Cincinnati, Ohio, USA . . . ><((((°>


Well, as you mentioned, it was connection specific. I tried using the GLOBAL option but I don't have SuperUser rights on the server.
o I created a cludge that seems to work OK. I created a small bound form bound to a one record table. And I used the Timer to Requery the form every 30 seconds.
With this running in the background all the time, it holds the connection from Access open. I let the DB sit idle for an hour and a half and it was still good when I came back.
Ohate not having full control over stuff! I guess I'm spoiled at work.
Go to the top of the page
 
BananaRepublic
post Feb 28 2010, 11:06 PM
Post#8


Dungeon Cleaner
Posts: 1,504
Joined: 16-June 07
From: Banana Republic


Why not just execute a simple PT to set the timeout at the start up of Access application? If you also specify "Allow Multiple Statement" option, then this should effectively mean that Access application will use same connection for all queries. MyODBC already supports Active statements, so enabling Multiple statements should effectively make all kind of queries run over a single connection, with the exception being that if you open another connection in code, of course. (If you're familiar with MARS in SQL Server, then this is basically the same thing... except that MySQL always has the active statement part "on" while Multiple statements is optional.)
know how you feel! When I used MySQL, I looked at the hosted solution but decided that running a old server with small Linux footprint made more sense than depending on third-party to do it right!
Do let me know if this works out for you. <
Go to the top of the page
 
Carlapet
post Mar 1 2010, 10:48 AM
Post#9



Posts: 51
Joined: 2-April 08



Just set your ODBC refresh interval to less than 60 seconds.
It is on the properties advanced page I believe.
Go to the top of the page
 
niesz
post Mar 1 2010, 12:02 PM
Post#10


Utter A-fishin'-ado
Posts: 17,958
Joined: 1-August 05
From: Cincinnati, Ohio, USA . . . ><((((°>


BR,
o I would have a PT that has:
Allow Multiple Statement;
SET wait_timeout=28800;
??
Ofound this on MySQL Dev:
I'll also try setting the ODBC refresh interval to <60 and see if it works or not.
Go to the top of the page
 
BananaRepublic
post Mar 1 2010, 12:25 PM
Post#11


Dungeon Cleaner
Posts: 1,504
Joined: 16-June 07
From: Banana Republic


Carlajet has a great idea. Didn't think of that, though would be interesting to see how that works... Does that require having a form open, though?
Walt- No. The option for multiple statement is specified in the connection string, not as a SQL statements. Example:
CODE
ODBC;DRIVER={MySQL 5.1 ODBC Driver};SERVER=xxx.xxx.xxx.xxx;DATABASE=xxxx;OPTION=yyyy;UID=xxxx;PWD=zzzz;

FWIW, I usually put down several options... You're welcome to my code if you want.
CODE
Opt = odFieldLength + odFoundRows + odDynamicCursor + odFullColumnNames + _
            odUseMyCnf + odMultiStatements + odColumnSizeS32 + _
            odNoTransactions + odAutoReconnect + odNoBigInt
strConnection = "ODBC;DRIVER={MySQL ODBC 5.1 Driver};" & _
                 "Server=xxx.xxx.xxx.xxx;" & _
                 "Port=3306;" & _
                 "Option=" & Opt & ";" & _
                 "Stmt=;" & _
                 "Database=xxxx;" & _
                 "Uid=" & UserName & ";" & _
                 "Pwd=" & Password

I wrote up a little enum, copying from the option page on MySQL documentation. That was long ago, but that was still for the same version.
CODE
Public Enum MyODBCOption
        odFieldLength = 1              'FLAG_FIELD_LENGTH       'Don't Optimize Column Width
        odFoundRows = 2                'FLAG_FOUND_ROWS         'Return Matching Rows
        odDebug = 4                    'FLAG_DEBUG              'Trace Driver Calls To myodbc.log
        odBigPacket = 8                'FLAG_BIG_PACKETS        'Allow Big Results
        odNoPrompt = 16                'FLAG_NO_PROMPT          'Don't Prompt Upon Connect
        odDynamicCursor = 32           'FLAG_DYNAMIC_CURSOR     'Enable Dynamic Cursor
        odNoSchema = 64                'FLAG_NO_SCHEMA          'Ignore # in Table Name
        odNoDefaultCursor = 128        'FLAG_NO_DEFAULT_CURSOR  'User Manager Cursors
        odNoLocale = 256               'FLAG_NO_LOCALE          'Don't Use Set Locale
        odPadSpace = 512               'FLAG_PAD_SPACE          'Pad Char To Full Length
        odFullColumnNames = 1024       'FLAG_FULL_COLUMN_NAMES  'Return Table Names for SQLDescribeCol
        odCompressedProto = 2048       'FLAG_COMPRESSED_PROTO   'Use Compressed Protocol
        odIgnoreSpace = 4096           'FLAG_IGNORE_SPACE       'Ignore Space After Function Names
        odNamedPipe = 8192             'FLAG_NAMED_PIPE         'Force Use of Named Pipes
        odNoBigInt = 16384             'FLAG_NO_BIGINT          'Change BIGINT Columns to Int
        odNoCatalog = 32768            'FLAG_NO_CATALOG         'No Catalog
        odUseMyCnf = 65536             'FLAG_USE_MYCNF          'Read Options From my.cnf
        odSafe = 131072                'FLAG_SAFE               'Safe
        odNoTransactions = 262144      'FLAG_NO_TRANSACTIONS    'Disable transactions
        odLogQuery = 524288            'FLAG_LOG_QUERY          'Save queries to myodbc.sql
        odNoCache = 1048576            'FLAG_NO_CACHE           'Don't Cache Result (forward only cursors)
        odForwardCursor = 2097152      'FLAG_FORWARD_CURSOR     'Force Use Of Forward Only Cursors
        odAutoReconnect = 4194304      'FLAG_AUTO_RECONNECT     'Enable auto-reconnect.
        odAutoIsNull = 8388608         'FLAG_AUTO_IS_NULL       'Flag Auto Is Null
        odZeroDateToMin = 16777216     'FLAG_ZERO_DATE_TO_MIN   'Flag Zero Date to Min
        odMinDateToZero = 33554432     'FLAG_MIN_DATE_TO_ZERO   'Flag Min Date to Zero
        odMultiStatements = 67108864   'FLAG_MULTI_STATEMENTS   'Allow multiple statements
        odColumnSizeS32 = 134217728    'FLAG_COLUMN_SIZE_S32    'Limit column size to 32-bit value
End Enum

At minimum, Access front-ends should always specify option to return matching rows (per MySQL documentation recommendations)
HTH.
EDIT:
Oops, forgot to add the link:
The documentation detailing all options
Also, if you scroll to very bottom of the page, it lists two recommended configurations for Access, either 3 or 35. But as you see, I usually enable other options as well.
Go to the top of the page
 
Carlapet
post Mar 1 2010, 12:33 PM
Post#12



Posts: 51
Joined: 2-April 08



The ODBC refresh interval works whether a form is open or not. Basically Access sends another handshake on the current connection letting MySQL know it is still there. Uses the least amount of resources of the methods I have seen for keeping the connection alive.
Go to the top of the page
 
BananaRepublic
post Mar 1 2010, 12:37 PM
Post#13


Dungeon Cleaner
Posts: 1,504
Joined: 16-June 07
From: Banana Republic


Awesome. I had a vague impression it depended on having a open recordset somewhere in the application, but if what you claim is true, then that would be definitely much easier than the kludge I was suggesting to Walt.
If you don't mind me asking, how did you determine this? Did a test on it or?
Go to the top of the page
 
Carlapet
post Mar 1 2010, 12:44 PM
Post#14



Posts: 51
Joined: 2-April 08



We run a FE/BE between Access and Mysql and were constantly running into IDLE timeouts with our linked tables.
Otried the have a timer running in the background but found that it only kept alive connections to the SAME mysql server.
I just used a simple logging(Wireshark) tool in an isolated network to test the efficiency. The ODBC refresh interval is a single response, no reply needed from the server that tells MySQL to keep the connection alive. The timer option always uses more because it has to run a query and so on.
We have tested this on around 20+ machines and they all work running Office 2007, however some issues still exist in 2003. It seems to be hit or miss if the setting does anything in 2003, we set it programatically so it just might be a problem with the code not working in 2003, have not looked into it very much so far.
Go to the top of the page
 
BananaRepublic
post Mar 1 2010, 12:54 PM
Post#15


Dungeon Cleaner
Posts: 1,504
Joined: 16-June 07
From: Banana Republic


Brilliant! That's very valuable information. Thanks for sharing! I should have had thought to use Wireshark long ago.
The issue with 2003 is curious, though. I vaguely remember fiddling with the setting and being unsatisfied with the results on 2003 but that was too long ago but that should be interesting to look at.
Again, I'm indebted to you for sharing. <
Go to the top of the page
 
Carlapet
post Mar 1 2010, 12:59 PM
Post#16



Posts: 51
Joined: 2-April 08



Of course, that is what we are all here for:)
Go to the top of the page
 
niesz
post Mar 1 2010, 02:14 PM
Post#17


Utter A-fishin'-ado
Posts: 17,958
Joined: 1-August 05
From: Cincinnati, Ohio, USA . . . ><((((°>


OK, I'm Jones'ing to try the Refresh intreval thing when I get home!
But until then, let me ask another ?.
Where/How can you set the connection string for a linked table?
I've always just created a DSN and used that, but I don't see any way to specify those extra settings using ODBC manager in Win7 ... ?
This would just be used for a linked table, I'm not specifying any connection strings in code .. (maybe I'll have to in order to get them correct)
Go to the top of the page
 
niesz
post Mar 1 2010, 05:23 PM
Post#18


Utter A-fishin'-ado
Posts: 17,958
Joined: 1-August 05
From: Cincinnati, Ohio, USA . . . ><((((°>


Well, Tools> Access Options >ODBC Refresh interval set to 45 (seconds). After I leave the DB sit for a few minutes (no forms open at all), I then try to open a linked table.
That's when I get:
ODCB--call failed
MySQL server has gone away
Doesn't look like that solution works for me.
Win7
Access 2007
Go to the top of the page
 
BananaRepublic
post Mar 1 2010, 05:30 PM
Post#19


Dungeon Cleaner
Posts: 1,504
Joined: 16-June 07
From: Banana Republic


You manage the connection options in the MySQL ODBC driver, not in ODBC administrator... When you create a new DSN, you should choose the MySQL driver, then when you press next, it should then open the MySQL ODBC dialog. The options are represented on the bottom on a tab control (you need to press the "Detail" button to see this):

Too bad the ODBC refresh didn't work... A silly question, but try it really lower... 5 seconds for example. That is bad for production, but I just want to verify this isn't a case of timing being off or something like that.
Go to the top of the page
 
niesz
post Mar 1 2010, 06:30 PM
Post#20


Utter A-fishin'-ado
Posts: 17,958
Joined: 1-August 05
From: Cincinnati, Ohio, USA . . . ><((((°>


Changed it to 10 seconds with no effect.
appreciate the screen shots. That's where I was at to see the settings, but I don't see any check boxes for
odFieldLength = 1 'FLAG_FIELD_LENGTH 'Don't Optimize Column Width
odFoundRows = 2 'FLAG_FOUND_ROWS 'Return Matching Rows
odDynamicCursor = 32 'FLAG_DYNAMIC_CURSOR 'Enable Dynamic Cursor
...which, I assume, are the ones needed for a value of 3 or 35.
O*do* see Allow Multiple Statements, though.
(Actually I see the ones for 2 and 32, but not 1)
Go to the top of the page
 
3 Pages V  1 2 3 >


Custom Search
RSSSearch   Top   Lo-Fi    10th December 2017 - 09:27 PM