Professional Web Applications Themes

Sudden unavailability - MySQL

Hi! I need some help here as I have a serious problem. I ahve MySQL 4.0.21 here on a XP Pro machine to server my Apache webserver and it has worked flawless for ages. Now ussdenly after a restart I got a message from one of websites that there was a problem with my SQL server. Checked in WinMySQLAdmin and all the fields for the server is empty (as if not running) and thens ervices and it shows the task as running. Restarting, rebooting or anything like it makes no difference. I ahven't changed anything in config so I am ...

  1. #1

    Default Sudden unavailability

    Hi!

    I need some help here as I have a serious problem. I ahve MySQL 4.0.21 here
    on a XP Pro machine to server my Apache webserver and it has worked flawless
    for ages. Now ussdenly after a restart I got a message from one of websites
    that there was a problem with my SQL server. Checked in WinMySQLAdmin and
    all the fields for the server is empty (as if not running) and thens ervices
    and it shows the task as running. Restarting, rebooting or anything like it
    makes no difference. I ahven't changed anything in config so I am clueless
    why this suddenly happends..

    Any advice or suggestions to get things working again would be greatly
    apreciated!

    Best Wishes
    Thomas


    Thomas Andersson Guest

  2. #2

    Default Re: Sudden unavailability

    "Thomas Andersson" <thomastifozi.net> wrote in message
    news:468inqF9bl8mU1individual.net...
    > Checked in WinMySQLAdmin and
    > all the fields for the server is empty (as if not running) and thens
    > ervices
    > and it shows the task as running.
    Is there any helpful information in the MySQL error log?
    You can find this file under the MySQL datadir, the filename is
    <hostname>.err.

    Regards,
    Bill K.


    Bill Karwin Guest

  3. #3

    Default Re: Sudden unavailability

    Bill Karwin wrote:
    >> Checked in WinMySQLAdmin and
    >> all the fields for the server is empty (as if not running) and then
    >> services and it shows the task as running.
    >
    > Is there any helpful information in the MySQL error log?
    > You can find this file under the MySQL datadir, the filename is
    > <hostname>.err.
    Nope, at least not to my eye:

    060225 6:03:38 InnoDB: Started
    MySql: ready for connections.
    Version: '4.0.21-nt' socket: '' port: 3306 Source distribution

    Nothing else (except for similar shutdown messages where I have shut it down
    to restart).
    Services reports it as running and it seems to be, just not talking.
    What happened betwee the boots was that Norton Systemworks/Antivirus
    expired.. wonder if that might have ed up something in the system
    preventing comunication? (It's all uninstalled now).

    Best Wishes
    Thomas


    Thomas Andersson Guest

  4. #4

    Default Re: Sudden unavailability

    Bill Karwin wrote:
    > Is there any helpful information in the MySQL error log?
    > You can find this file under the MySQL datadir, the filename is
    > <hostname>.err.
    Lite update from my experimentation.
    I downloaded 4.0.26 and copied the files straight over my install (after
    backing it up) and then WinMySQLAdmin showed the server again, BUT.. there
    was no databases anymore, none of them showed up.
    Delete and copy back original install, then redo copying over of new files
    except the data dir.. and again Admin shows no data, as if no server
    running...

    Does this give a clue to what might be the problem?

    Best Wishes
    Thomas


    Thomas Andersson Guest

  5. #5

    Default Re: Sudden unavailability

    "Thomas Andersson" <thomastifozi.net> wrote in message
    news:46a8isFa1gugU1individual.net...
    > Delete and copy back original install, then redo copying over of new files
    > except the data dir.. and again Admin shows no data, as if no server
    > running...
    >
    > Does this give a clue to what might be the problem?
    No, I'm sorry but this does not ring any bells for me.

    My strategy would be to start combing through MySQL forums, newsgroup
    archives, and anything else I could find through Google to see if similar
    symptoms had been found by other folks in the past. This is a very tricky
    type of failure. It's easier when there's a specific error message to use
    in a search.

    Good luck... I'll leave your message marked as unread in my newsreader, and
    if I think of anything else I'll post.

    Have you considered purchasing support from MySQL AB?

    Regards,
    Bill K.


    Bill Karwin Guest

  6. #6

    Default Re: Sudden unavailability

    Thomas Andersson wrote:
    > Bill Karwin wrote:
    >
    >
    >>>Checked in WinMySQLAdmin and
    >>>all the fields for the server is empty (as if not running) and then
    >>>services and it shows the task as running.
    >>
    >>Is there any helpful information in the MySQL error log?
    >>You can find this file under the MySQL datadir, the filename is
    >><hostname>.err.
    >
    >
    > Nope, at least not to my eye:
    >
    > 060225 6:03:38 InnoDB: Started
    > MySql: ready for connections.
    > Version: '4.0.21-nt' socket: '' port: 3306 Source distribution
    >
    > Nothing else (except for similar shutdown messages where I have shut it down
    > to restart).
    > Services reports it as running and it seems to be, just not talking.
    > What happened betwee the boots was that Norton Systemworks/Antivirus
    > expired.. wonder if that might have ed up something in the system
    > preventing comunication? (It's all uninstalled now).
    >
    > Best Wishes
    > Thomas
    >
    >
    Don't just copy the files. Do a MySql backup and restore the databases.

    Copying may or may not work, depending on a lot of things. But using
    the database's backup/restore facilities should always work.

    And ensure your my.cnf file contains the correct data directory.

    --
    ==================
    Remove the "x" from my email address
    Jerry Stuckle
    JDS Computer Training Corp.
    [email]jstucklexattglobal.net[/email]
    ==================
    Jerry Stuckle Guest

  7. #7

    Default Re: Sudden unavailability

    Bill Karwin wrote:
    > My strategy would be to start combing through MySQL forums, newsgroup
    > archives, and anything else I could find through Google to see if
    > similar symptoms had been found by other folks in the past. This is
    > a very tricky type of failure. It's easier when there's a specific
    > error message to use in a search.
    I'll try that too.
    > Good luck... I'll leave your message marked as unread in my
    > newsreader, and if I think of anything else I'll post.
    Thanks.
    > Have you considered purchasing support from MySQL AB?
    Couldn't afford it and no point for a hobby project mostly run for free for
    others.

    Latest I tried was a Clean 4.0.26 install and copying over data folders one
    at a time. with nothing but the data/mysql folder copied it goes blank again
    and refuses to talk so clearly the error is in there somewhere. If I copy
    any other datafolder it just ignores it.
    What's the proper upgrade procedure??

    Best Wishes
    Thomas


    Thomas Andersson Guest

  8. #8

    Default Re: Sudden unavailability

    "Thomas Andersson" <thomastifozi.net> wrote in
    news:46b0ahFa7gafU1individual.net:

    > Latest I tried was a Clean 4.0.26 install and copying over data
    > folders one at a time. with nothing but the data/mysql folder copied
    > it goes blank again and refuses to talk so clearly the error is in
    > there somewhere. If I copy any other datafolder it just ignores it.
    > What's the proper upgrade procedure??
    first off, i don't have the answer.

    secondly, as has been suggested in this thread and ALL doentation, ** do
    not simply copy over data folders **. that is NOT guaranteed to work,
    especially with newer versions of MySQL, and you are simply asking for
    trouble.

    one would HOPE you have a backup sql dump, anywhere, of any age, so at the
    very least you can recover table structure, and ideally, some recent data.

    the proper upgrade procedure is noted here:
    [url]http://dev.mysql.com/doc/refman/5.0/en/windows-upgrading.html[/url]

    i hope you didn't have to learn the 'backup data often' rule of computing,
    and that you didn't lose anything too vital.

    cheers.
    Good Man Guest

  9. #9

    Default Re: Sudden unavailability

    Here's a thread that may be helpful.
    [url]http://www.faqts.com/knowledge_base/view.phtml/aid/20068/fid/83[/url]

    Can you verify that the MySQL service is pointing to the right location for
    the datadir?
    Try this in mysql CLI or MySQL Query Browser:

    mysql> show variables like "datadir";

    Does the path it reports look right?
    > What's the proper upgrade procedure??
    The upgrade procedure I use is to back up all my databases, install new
    software, and restore all databases from their backup. I don't trust that
    the software will be perfectly compatible from release to release.

    I agree with the other folks who have responded; it's not safe to do
    filesystem copying of the files under the datadir. You could risk
    corrupting data, or at least putting the files in the wrong location. Use
    the tools that come with MySQL to back up and restore.

    Regards,
    Bill K.


    Bill Karwin Guest

  10. #10

    Default Re: Sudden unavailability

    Jerry Stuckle wrote:
    > Don't just copy the files. Do a MySql backup and restore the
    > databases.
    >
    > Copying may or may not work, depending on a lot of things. But using
    > the database's backup/restore facilities should always work.
    Well, that's not an option right now as the server doesn't comunicate at
    all.
    > And ensure your my.cnf file contains the correct data directory.
    It does.
    Any other dieas?

    Best Wishes
    Thomas


    Thomas Andersson Guest

  11. #11

    Default Re: Sudden unavailability

    Bill Karwin wrote:
    > The upgrade procedure I use is to back up all my databases, install
    > new software, and restore all databases from their backup. I don't
    > trust that the software will be perfectly compatible from release to
    > release.
    >
    > I agree with the other folks who have responded; it's not safe to do
    > filesystem copying of the files under the datadir. You could risk
    > corrupting data, or at least putting the files in the wrong location.
    > Use the tools that come with MySQL to back up and restore.
    Yes, I'm aware of that, I just tried that as an emergency measure to get it
    running again. Most likely something in the "mysql" database is corrup and
    prevent the server to work properly,


    Thomas Andersson Guest

  12. #12

    Default Re: Sudden unavailability

    Thomas Andersson wrote:
    > Jerry Stuckle wrote:
    >
    >
    >>Don't just copy the files. Do a MySql backup and restore the
    >>databases.
    >>
    >>Copying may or may not work, depending on a lot of things. But using
    >>the database's backup/restore facilities should always work.
    >
    >
    > Well, that's not an option right now as the server doesn't comunicate at
    > all.
    >
    >
    >>And ensure your my.cnf file contains the correct data directory.
    >
    >
    > It does.
    > Any other dieas?
    >
    > Best Wishes
    > Thomas
    >
    >
    Bring up your older version of MySQL, backup the database, reinstall the
    newer version and restore.

    "Short cuts" in programming rarely are.

    --
    ==================
    Remove the "x" from my email address
    Jerry Stuckle
    JDS Computer Training Corp.
    [email]jstucklexattglobal.net[/email]
    ==================
    Jerry Stuckle Guest

  13. #13

    Default Re: Sudden unavailability

    Jerry Stuckle wrote:
    > Bring up your older version of MySQL, backup the database, reinstall
    > the newer version and restore.
    >
    > "Short cuts" in programming rarely are.
    Ah, you probably never read my OP, the only reason I tried upgrading and
    copying data was that fro some reason my server suddenly ceased to work. The
    server is running (well the service runs anyway), but it's not talking and
    in the admin panel all sever data is blank, the databse pane is blank too...
    I.e. I have nothing working to go back to or back up as I need to have a
    workings erver to do that.


    Thomas Andersson Guest

  14. #14

    Default Re: Sudden unavailability

    Thomas Andersson wrote:
    > Jerry Stuckle wrote:
    >
    >
    >>Bring up your older version of MySQL, backup the database, reinstall
    >>the newer version and restore.
    >>
    >>"Short cuts" in programming rarely are.
    >
    >
    > Ah, you probably never read my OP, the only reason I tried upgrading and
    > copying data was that fro some reason my server suddenly ceased to work. The
    > server is running (well the service runs anyway), but it's not talking and
    > in the admin panel all sever data is blank, the databse pane is blank too...
    > I.e. I have nothing working to go back to or back up as I need to have a
    > workings erver to do that.
    >
    >
    Oh, I read it OK.

    But it looks like rather than determining the cause of the problem you
    tried a shortcut and just installed a new version.

    Better to determine why the server isn't working. For instance - it
    could be because your data files were corrupted.

    --
    ==================
    Remove the "x" from my email address
    Jerry Stuckle
    JDS Computer Training Corp.
    [email]jstucklexattglobal.net[/email]
    ==================
    Jerry Stuckle Guest

  15. #15

    Default Re: Sudden unavailability

    "Thomas Andersson" <thomastifozi.net> wrote in message
    news:46cmtnF9vmtsU1individual.net...
    > Most likely something in the "mysql" database is corrup and
    > prevent the server to work properly,
    Okay, then as an experiment, I'd try a fresh install of MySQL as you have
    done, but do not restore the 'mysql' system database from your copy of your
    previous datadir. Only restore the other databases. If that is successful,
    then the only data you've lost is about user accounts, password, and
    privileges.

    Another suggestion would be to try to repair the 'mysql' database, using
    tools provided with MySQL. See this section:
    [url]http://dev.mysql.com/doc/refman/5.0/en/table-maintenance.html[/url]

    Regards,
    Bill K.


    Bill Karwin Guest

  16. #16

    Default Re: Sudden unavailability

    Jerry Stuckle wrote:
    > Oh, I read it OK.
    >
    > But it looks like rather than determining the cause of the problem you
    > tried a shortcut and just installed a new version.
    I don't know sql very well so I wouldn't know how to find the exact error or
    fix it, that's why I turned here for help and advice.
    > Better to determine why the server isn't working. For instance - it
    > could be because your data files were corrupted.
    I'm quite sure that's the case, but where to go from there? How to find
    exactlky what the error is and where, and if possible how to solve it?
    Something is amiss in the mysql/data/mysql folder as exchanging that for a
    fresh one from the upgrade (or same version for that matter) kicks the
    server into gear aagin, just won't see or load any of the databases present.
    I assume the reason that they don't load is that mysql is the master
    database containing accounts and lists of databases to run?? Could starting
    fresh and recreating all acccounts and then creating new empty databases
    with teh same names and then copying back the folders work? Are there any
    tools to yze and maybe repair the folder?

    Best Wishes
    Thomas


    Thomas Andersson Guest

  17. #17

    Default Re: Sudden unavailability

    Bill Karwin wrote:
    > Okay, then as an experiment, I'd try a fresh install of MySQL as you
    > have done, but do not restore the 'mysql' system database from your
    > copy of your previous datadir. Only restore the other databases. If
    > that is successful, then the only data you've lost is about user
    > accounts, password, and privileges.
    Thanks, that's almost what I just did. I keept the original install with all
    folders, but replaced mysql in data with a fresh one from the install
    archive, that starts the server OK but I can't access it with my pass
    (account gone) and it doesn't load any of the databases in the data dir.
    > Another suggestion would be to try to repair the 'mysql' database,
    > using tools provided with MySQL. See this section:
    > [url]http://dev.mysql.com/doc/refman/5.0/en/table-maintenance.html[/url]
    Thanks again, I'll look into that.

    Best Wishes
    Thomas


    Thomas Andersson Guest

  18. #18

    Default Re: Sudden unavailability

    Thomas Andersson wrote:
    > Jerry Stuckle wrote:
    >
    >
    >>Oh, I read it OK.
    >>
    >>But it looks like rather than determining the cause of the problem you
    >>tried a shortcut and just installed a new version.
    >
    >
    > I don't know sql very well so I wouldn't know how to find the exact error or
    > fix it, that's why I turned here for help and advice.
    >
    >
    >>Better to determine why the server isn't working. For instance - it
    >>could be because your data files were corrupted.
    >
    >
    > I'm quite sure that's the case, but where to go from there? How to find
    > exactlky what the error is and where, and if possible how to solve it?
    > Something is amiss in the mysql/data/mysql folder as exchanging that for a
    > fresh one from the upgrade (or same version for that matter) kicks the
    > server into gear aagin, just won't see or load any of the databases present.
    > I assume the reason that they don't load is that mysql is the master
    > database containing accounts and lists of databases to run?? Could starting
    > fresh and recreating all acccounts and then creating new empty databases
    > with teh same names and then copying back the folders work? Are there any
    > tools to yze and maybe repair the folder?
    >
    > Best Wishes
    > Thomas
    >
    >
    That's one possibility. There are lots of them.

    I think the first thing to do is backup the current databases and
    restore all from a previous backup. If it works, then you know there's
    a database problem and you can restore one at a time until it fails again.

    Another way is enabling all errors and statement logging, then looking
    at the mysql log after performing an operation should give some
    indication as to where the error is.

    IOW, standard debugging techniques to determine the cause of the error
    goes far.


    --
    ==================
    Remove the "x" from my email address
    Jerry Stuckle
    JDS Computer Training Corp.
    [email]jstucklexattglobal.net[/email]
    ==================
    Jerry Stuckle Guest

  19. #19

    Default Re: Sudden unavailability

    "Thomas Andersson" <thomastifozi.net> wrote in message
    news:46gh2hFaujt6U1individual.net...
    > ...starts the server OK but I can't access it with my pass
    > (account gone) and it doesn't load any of the databases in the data dir.
    Recreating an account in MySQL is as simple as granting privileges to that
    account:

    mysql> grant all on mydatabase.* to 'login''%' identified by 'password';

    I'm not sure what you mean that it doesn't load any of the databases.
    Can you access the databases using the default 'root' login provided when
    you installed?

    Regards,
    Bill K.


    Bill Karwin Guest

Similar Threads

  1. Sudden Crash.
    By Nightfall_Blue in forum Coldfusion Server Administration
    Replies: 1
    Last Post: July 12th, 11:31 AM
  2. IE crashes all of a sudden in Win 98
    By Qwann in forum Macromedia Flash Player
    Replies: 1
    Last Post: February 12th, 01:10 PM
  3. Command Not Available all of a sudden
    By david_kohn@adobeforums.com in forum Adobe Photoshop Mac CS, CS2 & CS3
    Replies: 1
    Last Post: April 5th, 10:50 PM
  4. Sudden reboot
    By Methusela Oreiley in forum Sun Solaris
    Replies: 3
    Last Post: July 3rd, 05:53 AM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139