Database Timeout issue using EoIP

For complex topics that regular users would not be interested in. For power users and database administrators.

Database Timeout issue using EoIP

Postby drawab » Mon Sep 14, 2015 3:40 am

Recently - we have expanded our network

Initial setup - 192.168.1.*
Expanded setup 192.168.2.*
Both networks are connected to each other via Ethernet Over IP - (EoIP) using DD-WRT routers http://www.dd-wrt.com/wiki/index.php/EoIP_Routing and seting up advanced routing we were able to bridging both subnets enabling us to browse from 192.168.2.* network to the 192.168.1.* set and vice versa - not the most ideal setup (insecure) but its a best-case in our situation for the moment until we upgrade

Most things work fine like folder browsing and other aspects (we dont fully see other networked computers in the network neighbourhood) but by using their static IP's we are able to browse to view all their shared folders etc.

But when Open Dental tries to connect to the main servers OD Database - (using the server IP as the server name) it gives a timeout - im assuming this is a MySQL database timeout and the pop-up prompt comes as
Code: Select all
Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding


I initially thought it was MySQL ports being blocked on the server - thats not the case - but even then I recreated the exception rules in windows firewall and re-opened ports 3306 and also placed an exception for the OpenDental program in the Windows firewall.

To test - I have pinged to the server's IP - and I get good results and Ping average 25ms
If I telnet to the server's port 3306 by using "telnet 192.168.1.100 3306" it comes back with a garbled MySQL password prompt - showing that the 3306 port is open and accessible

I have BitDefender as an antivirus which I have disabled / turned it off and I don't see any changes in the results
The Server / network sits behind a DD-WRT router and I have toggled with many aspects but no results

Turning On/Off the SPI Firewall - no change
Adding port fowarding 3306 in DD-WRT to the OD Server 192.168.2.100 - 3306 - no change
Have placed the OD Server in DMZ - no change

What might be the issue here - any thoughts? - im at the moment stumped
drawab
 
Posts: 39
Joined: Tue Apr 01, 2008 4:57 am

Re: Database Timeout issue using EoIP

Postby jsalmon » Mon Sep 14, 2015 1:04 pm

Can other workstations on the 192.168.1.* network still log into Open Dental (if you don't have any, I'd try to get a laptop or something to test)?
One simple setting that was causing an office weird issues like this was that they had enabled IPv6. As soon as IPv6 was turned off they stopped having issues. I highly doubt this is causing you any issues, I just figured it wouldn't hurt to mention because I've seen it help before, regardless of how unexplainable it is.

Side Notes:
I think to get the computer names to work instead of having to always use the ip addresses you need to set up both Preferred DNS server address and an Alternate DNS server address within your Internet Protocol Version 4 (TCP/IPv4) Properties. I'm assuming that one would be pointed to the 192.168.1.* network's DNS and the alternate one would be to the .2
I'm also going to be proceeding under the assumption that you have all computers on the same domain or workgroup and have things of that nature set up correctly. If not, this is a good time to do so 8)
The best thing about a boolean is even if you are wrong, you are only off by a bit.

Jason Salmon
Open Dental Software
http://www.opendental.com
User avatar
jsalmon
 
Posts: 1486
Joined: Tue Nov 30, 2010 1:33 pm

Re: Database Timeout issue using EoIP

Postby drawab » Wed Sep 16, 2015 3:11 pm

Jason

Thank you for the prompt reply, I think I have finally solved the problem, I think probably the port forwarding on the ADSL router probably worked - but for sure your various suggestions got me motivated to finally crack this puzzle - thank you

for future references if someone stumbles across the same problem

- I did have all computers on one Domain / Workgroup - Check
- I tweaked with asigning the Primary DNS server to 192.168.1.1 and the Secondary DNS to 192.168.2.1 but for one whole day it did not work - im still unable to get the computer names to look up - but I think in a day or so Ill work on this project (possible force the computer to go to the 192.168.1.1 and obtain an IP from the DHCP - there will be lag etc but it might be all worth it
- I disabled IPv6 on all remote computers
- When nothing worked i realized I should try and add 3306 port forwarding on my ISP ADSL router, and after which Open Dental mysteriously kicked into action.

Ill keep this post updated if I see / find any more necessary tweaks - next project on this is to set this up enclosed in a proper DD-WRT router to DD-WRT router PPTP tunnel to have full protection

Thank you again
drawab
 
Posts: 39
Joined: Tue Apr 01, 2008 4:57 am

Re: Database Timeout issue using EoIP

Postby dgraffeo » Wed Sep 16, 2015 3:25 pm

Ah, port 3306 is the port that MySQL communicates on.
"To understand what recursion is, you must first understand recursion."

David Graffeo
Open Dental Software
http://www.opendental.com
User avatar
dgraffeo
 
Posts: 147
Joined: Wed Sep 24, 2014 3:19 pm

Re: Database Timeout issue using EoIP

Postby drawab » Fri Sep 18, 2015 3:37 pm

UPDATE:

The timeout setting in MySQL is set to 10 seconds a small tweak of the connect_timeout has solved this problem

NOTES:

Login to the MySQL Command Prompt and enter
Code: Select all
show variables like '%timeout';

then tweak from 10 seconds upwards - I personally found 60 seconds to allow enough of time for any slow connection between the networked sites
Code: Select all
SET GLOBAL connect_timeout=60;
drawab
 
Posts: 39
Joined: Tue Apr 01, 2008 4:57 am


Return to Advanced Topics

Who is online

Users browsing this forum: No registered users and 3 guests