Results 1 to 20 of 20

Thread: Checking a mysql db connection the right way.

  1. #1

    Thread Starter
    Addicted Member
    Join Date
    Jan 2013
    Posts
    144

    Checking a mysql db connection the right way.

    Checking for a mysql db connection can be tricky! (and I think this is also true for other types of db servers.) The first thing that comes in mind when trying to write a piece of code to check a mysql db connection is to check the status of the mysql server and falsely assume that the Network Availability on the client PC is always OK and the server PC can always be reached. This is not true most of the time and amusing that can impact the overall performance of the check routine very badly.

    Here is how it should be:

    The first step is to check Network Availability on the client PC. If it's OK then the second step is to check if the server PC can be reached. If that is true, then check the mysql db connection. I tested this and found that it's much much faster than checking the db connection only. The reason is that in most cases the reason why the db connection cannot be established is because of the absence of the Network Availability on the client PC or because the server PC cannot be reached while mysql server is working fine on the server PC. Since checking Network Availability on the client PC and pining the server PC are much faster than doing some mysql db connection checking operations the overall performance will be much much better this way.

    Here's a sample code:

    Code:
    If My.Computer.Network.IsAvailable Then
           If My.Computer.Network.Ping("DB_HOST_NAME") Then
              Try
                 ' Check your mysql db connection here
              Catch ex As MySqlException
                 ' MessageBox.Show("Can't connect! (DB connection can't be established)")
              End Try
           Else
                 ' MessageBox.Show("Can't connect! (DB host can't be reached)")
           End If
        Else
                 ' MessageBox.Show("Can't connect! Network Unavailable")
    End If
    Finally, it's needless to say that this way has no affect when both network availability and server presence are guaranteed to be OK all the time.

    Hope that helps someone.

    EDIT: Sorry admins, I wrote this on the wrong forum, please move it to the right one, thanks

  2. #2
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,274

    Re: Checking a mysql db connection the right way.

    If you want your thread moved then use the Report Post icon under your post to send a message to the mods. I have now done that and asked them to move it to the CodeBank.

  3. #3
    Powered By Medtronic dbasnett's Avatar
    Join Date
    Dec 2007
    Location
    Jefferson City, MO
    Posts
    9,748

    Re: Checking a mysql db connection the right way.

    cPubis - Let me ask you to think about this scenario.

    Time
    1. You send ping to server DB_HOST_NAME
    2. DB_HOST_NAME sends reply
    3. one nanosecond after sending reply DB_HOST_NAME crashes
    4. your method reports all is well, but it isn't


    This is common, trying to predict the future. Do you try to determine if a disk is working on your PC before you use it? I think you attempt to use it knowing that the system will let you know if there is a problem.
    My First Computer -- Documentation Link (RT?M) -- Using the Debugger -- Prime Number Sieve
    Counting Bits -- Subnet Calculator -- UI Guidelines -- >> SerialPort Answer <<

    "Those who use Application.DoEvents have no idea what it does and those who know what it does never use it." John Wein

  4. #4

    Thread Starter
    Addicted Member
    Join Date
    Jan 2013
    Posts
    144

    Re: Checking a mysql db connection the right way.

    Quote Originally Posted by jmcilhinney View Post
    If you want your thread moved then use the Report Post icon under your post to send a message to the mods. I have now done that and asked them to move it to the CodeBank.
    Thank you.

    Quote Originally Posted by dbasnett View Post
    cPubis - Let me ask you to think about this scenario.

    Time
    • 1. You send ping to server DB_HOST_NAME
      2. DB_HOST_NAME sends reply
      3. one nanosecond after sending reply DB_HOST_NAME crashes
      4. your method reports all is well, but it isn't
    No, In this case the method will not report all is well! As demonstrated in the code above, if the db host crashes one nanosecond after receiving the ping reply, the third condition will be triggered and the message "Can't connect! (DB connection can't be established)" will be shown. That's the whole point; to check that the db host itself is fine before doing any db operations for the sake of performance (eliminating unnecessary steps). You'd have been right if you said: "one nanosecond after checking the db connection and found it OK the db host crashes." In this case, a later error handling routine will handle this, like when you try to save a record you put the saving code in a Try-End Try block.

  5. #5
    Powered By Medtronic dbasnett's Avatar
    Join Date
    Dec 2007
    Location
    Jefferson City, MO
    Posts
    9,748

    Re: Checking a mysql db connection the right way.

    Quote Originally Posted by cPubis View Post
    No, In this case the method will not report all is well! As demonstrated in the code above, if the db host crashes one nanosecond after receiving the ping reply, the third condition will be triggered and the message "Can't connect! (DB connection can't be established)" will be shown. That's the whole point; to check that the db host itself is fine before doing any db operations for the sake of performance (eliminating unnecessary steps). You'd have been right if you said: "one nanosecond after checking the db connection and found it OK the db host crashes." In this case, a later error handling routine will handle this, like when you try to save a record you put the saving code in a Try-End Try block.
    If the scenario is

    Time
    1. You send ping to server DB_HOST_NAME
    2. DB_HOST_NAME sends reply
    3. You send connect to server DB_HOST_NAME
    4. one nanosecond after checking the DB_HOST_NAME connection and found it OK the DB_HOST_NAME crashes
    5. your method reports all is well, but it isn't


    That is a lot of work, and it still doesn't accomplish anything. As you pointed out, you still have to put all of the code that interacts with DB_HOST_NAME in Try-Catch blocks.

    Let me ask this again; do you try to determine if a disk is working on your PC before you use it? I think you attempt to use it knowing that the system will let you know if there is a problem. Don't you put code that manipulates files in Try-Catch blocks.

    It is clear you were trying to be helpful, but in the end you have added overhead that isn't needed.

    Before replying to this open a Command Prompt window.
    Ping www.vbforums.com
    When you get a successful reply to that, then reply to this post.
    My First Computer -- Documentation Link (RT?M) -- Using the Debugger -- Prime Number Sieve
    Counting Bits -- Subnet Calculator -- UI Guidelines -- >> SerialPort Answer <<

    "Those who use Application.DoEvents have no idea what it does and those who know what it does never use it." John Wein

  6. #6

    Thread Starter
    Addicted Member
    Join Date
    Jan 2013
    Posts
    144

    Re: Checking a mysql db connection the right way.

    Quote Originally Posted by dbasnett View Post
    That is a lot of work, and it still doesn't accomplish anything.
    No it's not a lot of work, and it does accomplish a very essential thing; Performance. (That's besides the details it gives why exactly the connection cannot be established). I think I explained what it does enough and the code is attached you can test it yourself. As I said earlier, I tested it and found that checking the db connection alone takes about 2000ms to check the connection status, but when applying my method, it does that almost instantly (90ms) in most cases (where the network availability is absence and the host cannot be reached). But when the client network is OK and the db host can be reached, it takes about the same time. So the overall performance is much much better.

    Bottom line: in an imaginary world, where a client network is guaranteed to be available all the time, and the db host can be reached all the time, it's true my code would be taking unnecessary steps and wouldn't accomplish anything. But in the real world? Simply No.

    Quote Originally Posted by dbasnett View Post
    As you pointed out, you still have to put all of the code that interacts with DB_HOST_NAME in Try-Catch blocks.
    That's a different whole story, it's about manipulating the data in later steps. My post is about one single step as the title states very clearly; Check Connection Status.

  7. #7
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,522

    Re: Checking a mysql db connection the right way.

    Bottom line: in an imaginary world, where a client network is guaranteed to be available all the time, and the db host can be reached all the time, it's true my code would be taking unnecessary steps and wouldn't accomplish anything. But in the real world? Simply No.
    If you're in a world where you have to worry about server uptime to the point where this code is necessary... perhaps it's time for a new network or host ... because that's an indication of a serious problem.

    If the connection is going to fail, it's going to fail... no amount of pre-checking will really fix or help that. You still have to code for a failing connection no matter what. Even if the server is online, doesn't mean the database service is... this is one case where pinging just isn't going to help... the server is online and functioning just fine. But if the database service (and yeah, I've had this happen to me), the connection is still going to fail. There's no pre-checking that's going to fix that.

    Something to keep in mind... there's a reason why the ping check appears faster than simply waiting for a failed connection ... in the connection object, there's a connection timeout... it's the amount of time that the client will wait before reporting the connection as a failure ... it's possible that it's this delay that you're seeing in your differences. You're comparing apples to applesauce. That said, there's a reason for that delay ... some instances of SQL Server run on-demand... meaning the service listener is up and waiting for a connection... when the first one is received, THEN it spins up SQL Server and the connection passes through. Granted mySQL may be different... but it's still something to consider.

    Honestly, at the end of the day... all that matters is that the database is reachable or not. How you get there doesn't matter too much I suppose... if it works for you... great... for me, it wouldn't.

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  8. #8
    Powered By Medtronic dbasnett's Avatar
    Join Date
    Dec 2007
    Location
    Jefferson City, MO
    Posts
    9,748

    Re: Checking a mysql db connection the right way.

    Let us try logic.

    If DB_HOST_NAME is up and running then you don't need your code.
    If DB_HOST_NAME is down then the first time you try to use it, in any manner, your method will fail.

    If DB_HOST_NAME is up and running your code adds a ping that isn't needed, extra work.

    If you are a one person shop that extra ping doesn't add much load, but if you have thousands of people using it, then you have thousands of extra packets (one for the ping request, one for the reply). It is also extra work for the server, answering the ping.

    If you are in a situation that what you proposed sounds like a good idea I suggest you fix your server and / or the network it is attached to. In this day and age servers are extremely reliable, and even the internet is an extremely reliable network.

    All of the networks / servers that I am, or have been responsible for have external ICMP requests blocked.

    Did you try pinging VBForums before replying?

    My point in participating in this was that others might get it. You obviously don't.
    My First Computer -- Documentation Link (RT?M) -- Using the Debugger -- Prime Number Sieve
    Counting Bits -- Subnet Calculator -- UI Guidelines -- >> SerialPort Answer <<

    "Those who use Application.DoEvents have no idea what it does and those who know what it does never use it." John Wein

  9. #9

    Thread Starter
    Addicted Member
    Join Date
    Jan 2013
    Posts
    144

    Re: Checking a mysql db connection the right way.

    It's interesting that both of you have pointed out the same thing; my server/network should be fixed/changed! But you didn't ask yourselves why this is not an option for millions of people like me. Now users may try my code in the real world, the world which none of you seems to want to imagine it exists; Third Countries World, where having a reliable server/network is a welfare!

    Thank you

  10. #10
    Powered By Medtronic dbasnett's Avatar
    Join Date
    Dec 2007
    Location
    Jefferson City, MO
    Posts
    9,748

    Re: Checking a mysql db connection the right way.

    Quote Originally Posted by cPubis View Post
    It's interesting that both of you have pointed out the same thing; my server/network should be fixed/changed! But you didn't ask yourselves why this is not an option for millions of people like me. Now users may try my code in the real world, the world which none of you seems to want to imagine it exists; Third Countries World, where having a reliable server/network is a welfare!

    Thank you
    So if you ARE having network / server problems then the answer is to increase the load on both with pings that serve no purpose. Makes sense to me

    Just for the record, I was doing this sort of thing before Cisco was a small startup, and Dell was on the map. I was also doing this when the internet had five nodes, so don't use unreliable for an excuse.

    If this code makes you or others feel better, then great.
    My First Computer -- Documentation Link (RT?M) -- Using the Debugger -- Prime Number Sieve
    Counting Bits -- Subnet Calculator -- UI Guidelines -- >> SerialPort Answer <<

    "Those who use Application.DoEvents have no idea what it does and those who know what it does never use it." John Wein

  11. #11

    Thread Starter
    Addicted Member
    Join Date
    Jan 2013
    Posts
    144

    Re: Checking a mysql db connection the right way.

    Quote Originally Posted by dbasnett View Post
    So if you ARE having network / server problems then the answer is to increase the load on both with pings that serve no purpose. Makes sense to me
    I was expecting you to say:

    So if you, billions of third world people ARE having unreliable network / server and changing/fixing them is not an easy option for you then the answer is to decrease the load on both with pings (which take less than 80ms) instead of trying to connect to mysql service for more than 2000ms and then realize that the server itself is down. Makes sense to me

    Quote Originally Posted by dbasnett View Post
    If this code makes you or others feel better, then great.
    This code serves me and probably billions other in my world, It was the available solution for our situation. I have tested it many times and implemented it and found it very practical, and my 25 users also found it so. If ignoring this simple fact makes you feel better then great
    Last edited by cPubis; Mar 26th, 2016 at 12:40 AM.

  12. #12
    Powered By Medtronic dbasnett's Avatar
    Join Date
    Dec 2007
    Location
    Jefferson City, MO
    Posts
    9,748

    Re: Checking a mysql db connection the right way.

    See post #5. If your network is that unreliable then how many times does the ping fail when the server is up? Guess which traffic is low on the list of priority in most routers, ICMP. When the router has to start dropping traffic low priority traffic is the first to go. I wonder how many times the ping failed when the connection wouldn't?

    Glad that you can save your 25 users 1.92 seconds sometimes, and give them bad information other times.

    I am done here. We just disagree.
    My First Computer -- Documentation Link (RT?M) -- Using the Debugger -- Prime Number Sieve
    Counting Bits -- Subnet Calculator -- UI Guidelines -- >> SerialPort Answer <<

    "Those who use Application.DoEvents have no idea what it does and those who know what it does never use it." John Wein

  13. #13

    Thread Starter
    Addicted Member
    Join Date
    Jan 2013
    Posts
    144

    Re: Checking a mysql db connection the right way.

    Quote Originally Posted by dbasnett View Post
    I am done here. We just disagree.
    Yeah we just disagree. Thank you for your valuable time.


    Well, for people who may find my code useful, here's another benefit of it: Imagine this scenario: A user at work executes a query that returns 1000 records from the master table (30 fields each) and populates a DGV with them (he doesn't need the details table data now to finish the work) then he starts to review them. Oops, Work’s hours end and he didn't finish the work. He puts his laptop on sleep mode and goes home. At home he receives a call from his boss, she needs the work at her desk first thing tomorrow morning. What should he do? What should he do? At home he has no connection with the server! But thank God the software still running and he has all data he needs already populated. Now imagine you didn't implement my code! What will happen? God, that delay! and for many hours working at home, argggggh. Yes, the client software will never know that the user has no connection with the server and will continue trying to connect to mysql server each time the user view a record (to fetch data from details table due to databindings) and that means extra 2000ms for every record. Now implement my code… You know the rest

    Hope that helps someone

  14. #14
    Powered By Medtronic dbasnett's Avatar
    Join Date
    Dec 2007
    Location
    Jefferson City, MO
    Posts
    9,748

    Re: Checking a mysql db connection the right way.

    Quote Originally Posted by cPubis View Post
    ...Imagine this scenario: A user at work executes a query that returns 1000 records from the master table (30 fields each) and populates a DGV with them (he doesn't need the details table data now to finish the work) then he starts to review them. Oops, Work’s hours end and he didn't finish the work. He puts his laptop on sleep mode and goes home. At home he receives a call from his boss, she needs the work at her desk first thing tomorrow morning. What should he do? What should he do? At home he has no connection with the server! But thank God the software still running and he has all data he needs already populated. Now imagine you didn't implement my code! What will happen? God, that delay! and for many hours working at home, argggggh. Yes, the client software will never know that the user has no connection with the server and will continue trying to connect to mysql server each time the user view a record (to fetch data from details table due to databindings) and that means extra 2000ms for every record. Now implement my code…
    This post reminds me of a quote by W. C. Fields, “...dazzle them with brilliance, baffle them with ....”

    What?

    I took what you said and organized it so I could follow along.

    Imagine this scenario:
    • A user at work executes a query that returns 1000 records from the master table (30 fields each) and populates a DGV with them (he doesn't need the details table data now to finish the work)
    • then he starts to review them. Oops, Work’s hours end and he didn't finish the work. He puts his laptop on sleep mode and goes home.
    • At home he receives a call from his boss, she needs the work at her desk first thing tomorrow morning.
    • What should he do? What should he do?
    • At home he has no connection with the server!
    • But thank God the software still running and he has all data he needs already populated.


    Did the worker drive back to work or did the worker remote in using the highly unreliable network you've gone on about? Is the worker using the laptop at home? If so they must be using the data they received before leaving for home because you said they have no connection with the server.

    My First Computer -- Documentation Link (RT?M) -- Using the Debugger -- Prime Number Sieve
    Counting Bits -- Subnet Calculator -- UI Guidelines -- >> SerialPort Answer <<

    "Those who use Application.DoEvents have no idea what it does and those who know what it does never use it." John Wein

  15. #15
    Powered By Medtronic dbasnett's Avatar
    Join Date
    Dec 2007
    Location
    Jefferson City, MO
    Posts
    9,748

    Re: Checking a mysql db connection the right way.

    My First Computer -- Documentation Link (RT?M) -- Using the Debugger -- Prime Number Sieve
    Counting Bits -- Subnet Calculator -- UI Guidelines -- >> SerialPort Answer <<

    "Those who use Application.DoEvents have no idea what it does and those who know what it does never use it." John Wein

  16. #16
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    Connecticut
    Posts
    18,263

    Re: Checking a mysql db connection the right way.

    Quote Originally Posted by cPubis View Post
    Yeah we just disagree. Thank you for your valuable time.


    Well, for people who may find my code useful, here's another benefit of it: Imagine this scenario: A user at work executes a query that returns 1000 records from the master table (30 fields each) and populates a DGV with them (he doesn't need the details table data now to finish the work) then he starts to review them. Oops, Work’s hours end and he didn't finish the work. He puts his laptop on sleep mode and goes home. At home he receives a call from his boss, she needs the work at her desk first thing tomorrow morning. What should he do? What should he do? At home he has no connection with the server! But thank God the software still running and he has all data he needs already populated. Now imagine you didn't implement my code! What will happen? God, that delay! and for many hours working at home, argggggh. Yes, the client software will never know that the user has no connection with the server and will continue trying to connect to mysql server each time the user view a record (to fetch data from details table due to databindings) and that means extra 2000ms for every record. Now implement my code… You know the rest

    Hope that helps someone
    You are fashioning stories out of your imagination and then coding to protect against them.

    Checking for a DATABASE SERVER connection IS SIMPLY NEVER WARRANTED.

    I would suggest that I would want to protect my server from such requests from the outside world.

    There is only EVER one moment you will only ever know if your database connection is going to work - and that is when you submit a database query.

    You MUST always check for a problem on that request - regardless of whether you pinged or poked the server first.

    You are suggesting to do something that is simply NEVER DONE and is actually recommended against, IMO.

    [edit] fwiw I believe I've got clients that kill PING's coming into network addresses - isn't that a common thing in the network admin world? [/edit]

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  17. #17

    Thread Starter
    Addicted Member
    Join Date
    Jan 2013
    Posts
    144

    Re: Checking a mysql db connection the right way.

    Quote Originally Posted by dbasnett View Post
    This post reminds me of a quote by W. C. Fields, “...dazzle them with brilliance, baffle them with ....”
    The workers use their laptops at work and at home. At work, they have access to the network/server, but at home they have not. The luxury of having multiple computers and remote access to Public/Work/Internet networks is not available for most people in here.

    I see you've opened a new thread that is -in fact- only a span of this one to prove something to yourself I can read it like an opened book!

    Quote Originally Posted by szlamany View Post
    You are fashioning stories out of your imagination and then coding to protect against them.
    This statement of yours sir was enough for me to ignore anything you said after it, you are accusing me of lying? and in my face? just to prove to yourself that you're right? God!

    Let me tell you something, both of you, the moment you start to think of someone else (even for a change) and accept the fact that there's another world other than yours, and that this world is full of developers like me who can code and try to compensate the lake of luxury you have in your world with their bare mind power which makes them brilliant just like what you are trying to prove here with all your arguing, you'll stop what you are doing to yourselves, to me, and to all users of this forum who think that you're honest/professional in all your arguments. Believe YOU me.

    Goodbye

  18. #18
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    Connecticut
    Posts
    18,263

    Re: Checking a mysql db connection the right way.

    Quote Originally Posted by cPubis View Post
    Imagine this scenario:
    Dude - you said IMAGINE.

    Right?

    Did I miss something here?

    You said IMAGINE!!!!

    I said "You are fashioning stories out of your imagination"

    Isn't that what you said?

    I never said you were a liar - did I? Where did I say that??

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  19. #19
    Powered By Medtronic dbasnett's Avatar
    Join Date
    Dec 2007
    Location
    Jefferson City, MO
    Posts
    9,748

    Angry Re: Checking a mysql db connection the right way.

    Quote Originally Posted by cPubis View Post
    Let me tell you something, both of you, the moment you start to think of someone else (even for a change) and accept the fact that there's another world other than yours, and that this world is full of developers like me who can code and try to compensate the lake of luxury you have in your world with their bare mind power which makes them brilliant just like what you are trying to prove here with all your arguing, you'll stop what you are doing to yourselves, to me, and to all users of this forum who think that you're honest/professional in all your arguments. Believe YOU me.

    Goodbye
    You get all out of whack because you think szlamany has called you a liar, which he/she didn't. Then you say, "...all users of this forum who think that you're honest/professional in all your arguments." Well I am both honest and professional.

    The world I live in is called "Coding Country, Planet Earth". There are no borders there. The only discrimination that happens there is between those that are trying to get it, and those who won't. In 45+ years of doing computers and networks I have seen plenty like you, in plenty of places. Keep assuming, it suits you.

    The other piece of code that you think I posted to spite you or to spam you, was in fact from another user thread that I replied to before I ever saw your post. How arrogant are you? Believe me, I don't need your code.
    Last edited by dbasnett; Mar 26th, 2016 at 01:59 PM.
    My First Computer -- Documentation Link (RT?M) -- Using the Debugger -- Prime Number Sieve
    Counting Bits -- Subnet Calculator -- UI Guidelines -- >> SerialPort Answer <<

    "Those who use Application.DoEvents have no idea what it does and those who know what it does never use it." John Wein

  20. #20
    Super Moderator Shaggy Hiker's Avatar
    Join Date
    Aug 2002
    Location
    Idaho
    Posts
    38,943

    Re: Checking a mysql db connection the right way.

    I have no idea why you folks are arguing about this. I wouldn't do it this way, but that's all I've got to say about it. Since things are starting to get towards name calling. I'm going to lock the thread and let it stop here.
    My usual boring signature: Nothing

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width