Error 1067 when starting as service, no problem in standalone

Please note, I already posted this on the MySQL official forum, but received
no response. I thought I'd try again in another location. My apologies to
anyone reading this twice...

Despite everything I've tried, I cannot get MySQL to start as a Windows
service. However, I AM able to get it started via the mysqld-nt --console
command.

Here is my (funky) setup:

- MySQL 5.0 on Windows XP SP2 development server
- datadir in my.ini file set to network path Z:/path/to/mysql/ (note the
forward slashes)
- This network drive is actually an external hard drive (via firewire) on my
workstation PC

Permissions are set accordingly:
- User "MyUserAccount" exists on BOTH server and workstation (with identical
passwords)
- MySQL service is set to run as "MyUserAccount"
- "MyUserAccount" has FULL permissions to access the local MySQL basedir and
the network datadir path

In short, I can execute:

mysqld-nt --console (with or without --standalone)

.... to get the server started. I enter the mysql command line and can see
all my databases from my network share. I then can verify through Task
Manager that the mysqld-nt service is running under the "MyUserAccount" user
(this is the default Windows user for my server).

I shutdown with:

mysqladmin -u root -p shutdown

Now I try to start the service using:

- net start mysql or...
- Windows Services console

.... and receive the following error:

"Could not start the MySQL service on Local Computer.
Error 1067: The process terminated unexpectedly."

Checking Windows Event Viewer gives me foggy error messages such as:

"Aborting" and...
"The MySQL service terminated unexpectedly. It has done this x time(s)."


I will gladly provide more information if needed, but I am completely
stumped at this point. Many thanks to any who can help me out with this.

- Kevin

Re: Error 1067 when starting as service, no problem in standalone

"Kevin D." wrote in message
news:4PJFf.55710$V.32341@fed1read04...
> "Could not start the MySQL service on Local Computer.
> Error 1067: The process terminated unexpectedly."
>
> Checking Windows Event Viewer gives me foggy error messages such as:
>
> "Aborting" and...
> "The MySQL service terminated unexpectedly. It has done this x time(s)."

I find little useful information for MySQL recorded in the Windows Event
log. You should look in the MySQL server error log for better information.
This error log can be found under the MySQL data directory, as a filename
"hostname.err" where hostname is the name of your host on which MySQL runs.

Regards,
Bill K.

Re: Error 1067 when starting as service, no problem in standalone

thanks for the reply!

that's part of my problem, no hostname.err log file is created since the
datadir is located on the remote drive which, for some odd reason, mysql
won't connect to when running as a windows service (yet has no problem
connecting to it when run in --console --standalone mode)




"Bill Karwin" wrote in message
news:ds82vh0109f@enews4.newsguy.com...
> "Kevin D." wrote in message
> news:4PJFf.55710$V.32341@fed1read04...
>> "Could not start the MySQL service on Local Computer.
>> Error 1067: The process terminated unexpectedly."
>>
>> Checking Windows Event Viewer gives me foggy error messages such as:
>>
>> "Aborting" and...
>> "The MySQL service terminated unexpectedly. It has done this x time(s)."
>
> I find little useful information for MySQL recorded in the Windows Event
> log. You should look in the MySQL server error log for better
> information. This error log can be found under the MySQL data directory,
> as a filename "hostname.err" where hostname is the name of your host on
> which MySQL runs.
>
> Regards,
> Bill K.
>

Re: Error 1067 when starting as service, no problem in standalone

"Kevin D." wrote in message
news:XZNFf.55742$V.31108@fed1read04...
> thanks for the reply!
>
> that's part of my problem, no hostname.err log file is created since the
> datadir is located on the remote drive which, for some odd reason, mysql
> won't connect to when running as a windows service (yet has no problem
> connecting to it when run in --console --standalone mode)

That probably explains why MySQL won't start as a service, if its data dir
is on a remote drive and it doesn't like it. This behavior might be by
design!

It's a very bad idea to locate the data dir on a remote drive. If the
network between your server host and your remote drive host has any hiccup,
you'll get data corruption. Plus the traffic is transmitted unencrypted.

Relocate the data dir to a local drive and I think your service startup
issue will be resolved.
Use the remote drive only to store database backups, not the live database.

Regards,
Bill K.

Re: Error 1067 when starting as service, no problem in standalone

Kevin D. wrote:
> Please note, I already posted this on the MySQL official forum, but received
> no response. I thought I'd try again in another location. My apologies to
> anyone reading this twice...
>
> Despite everything I've tried, I cannot get MySQL to start as a Windows
> service. However, I AM able to get it started via the mysqld-nt --console
> command.
>
> Here is my (funky) setup:
>
> - MySQL 5.0 on Windows XP SP2 development server
> - datadir in my.ini file set to network path Z:/path/to/mysql/ (note the
> forward slashes)
> - This network drive is actually an external hard drive (via firewire) on my
> workstation PC
>
> Permissions are set accordingly:
> - User "MyUserAccount" exists on BOTH server and workstation (with identical
> passwords)
> - MySQL service is set to run as "MyUserAccount"
> - "MyUserAccount" has FULL permissions to access the local MySQL basedir and
> the network datadir path
>
> In short, I can execute:
>
> mysqld-nt --console (with or without --standalone)
>
> ... to get the server started. I enter the mysql command line and can see
> all my databases from my network share. I then can verify through Task
> Manager that the mysqld-nt service is running under the "MyUserAccount" user
> (this is the default Windows user for my server).
>
> I shutdown with:
>
> mysqladmin -u root -p shutdown
>
> Now I try to start the service using:
>
> - net start mysql or...
> - Windows Services console
>
> ... and receive the following error:
>
> "Could not start the MySQL service on Local Computer.
> Error 1067: The process terminated unexpectedly."
>
> Checking Windows Event Viewer gives me foggy error messages such as:
>
> "Aborting" and...
> "The MySQL service terminated unexpectedly. It has done this x time(s)."
>
>
> I will gladly provide more information if needed, but I am completely
> stumped at this point. Many thanks to any who can help me out with this.
>
> - Kevin

I had a few services I needed to configure to retrieve data from the
net... however I would not recommend doing this with MySQL table data.
Perhaps you can explain on why you have chosen this setup as there might
be a different, more appropriate way.

Now to your question. As windows starts it also starts the services,
this is done before a user is signed in. Drive mappings are user
dependent and are made when a user logs in, so the drive mappings are
not available at the time the MySQL service is started. Therefor it
works when you are logged in and manually start the service, but not
when you automatically start it before you log in.

It might work if you don't use the mapped drive but use the full UNC
path to the datadir, those paths are usually resolved by the DNS and are
available to windows at the time it starts the services. If you don't
have DNS or name resolving you might want to use the IP number, so either:

\\server\path\to\mysql\datadir

or

\\ip-number\path\to\mysql\datadir

Good luck, but I am really curious if you really need this setup.

Jonathan

Re: Error 1067 when starting as service, no problem in standalone

Kevin D. wrote:
> thanks for the reply!
>
> that's part of my problem, no hostname.err log file is created since the
> datadir is located on the remote drive which, for some odd reason, mysql
> won't connect to when running as a windows service (yet has no problem
> connecting to it when run in --console --standalone mode)
>
>
>
>
> "Bill Karwin" wrote in message
> news:ds82vh0109f@enews4.newsguy.com...
>> "Kevin D." wrote in message
>> news:4PJFf.55710$V.32341@fed1read04...
>>> "Could not start the MySQL service on Local Computer.
>>> Error 1067: The process terminated unexpectedly."
>>>
>>> Checking Windows Event Viewer gives me foggy error messages such as:
>>>
>>> "Aborting" and...
>>> "The MySQL service terminated unexpectedly. It has done this x time(s)."
>> I find little useful information for MySQL recorded in the Windows Event
>> log. You should look in the MySQL server error log for better
>> information. This error log can be found under the MySQL data directory,
>> as a filename "hostname.err" where hostname is the name of your host on
>> which MySQL runs.
>>
>> Regards,
>> Bill K.
>>

Please don't top post the next time, as it messes up the order of the
conversation, normal order is to post your answers at the bottom or in
between to keep the logic of a conversation.

Jonathan

Re: Error 1067 when starting as service, no problem in standalone

"Jonathan" wrote in message
news:43e7bb8f$0$2023$ba620dc5@text.nova.planet.nl...
> Kevin D. wrote:
>> Please note, I already posted this on the MySQL official forum, but
>> received no response. I thought I'd try again in another location. My
>> apologies to anyone reading this twice...
>>
>> Despite everything I've tried, I cannot get MySQL to start as a Windows
>> service. However, I AM able to get it started via the mysqld-nt --console
>> command.
>>
>> Here is my (funky) setup:
>>
>> - MySQL 5.0 on Windows XP SP2 development server
>> - datadir in my.ini file set to network path Z:/path/to/mysql/ (note the
>> forward slashes)
>> - This network drive is actually an external hard drive (via firewire) on
>> my workstation PC
>>
>> Permissions are set accordingly:
>> - User "MyUserAccount" exists on BOTH server and workstation (with
>> identical passwords)
>> - MySQL service is set to run as "MyUserAccount"
>> - "MyUserAccount" has FULL permissions to access the local MySQL basedir
>> and the network datadir path
>>
>> In short, I can execute:
>>
>> mysqld-nt --console (with or without --standalone)
>>
>> ... to get the server started. I enter the mysql command line and can see
>> all my databases from my network share. I then can verify through Task
>> Manager that the mysqld-nt service is running under the "MyUserAccount"
>> user (this is the default Windows user for my server).
>>
>> I shutdown with:
>>
>> mysqladmin -u root -p shutdown
>>
>> Now I try to start the service using:
>>
>> - net start mysql or...
>> - Windows Services console
>>
>> ... and receive the following error:
>>
>> "Could not start the MySQL service on Local Computer.
>> Error 1067: The process terminated unexpectedly."
>>
>> Checking Windows Event Viewer gives me foggy error messages such as:
>>
>> "Aborting" and...
>> "The MySQL service terminated unexpectedly. It has done this x time(s)."
>>
>>
>> I will gladly provide more information if needed, but I am completely
>> stumped at this point. Many thanks to any who can help me out with this.
>>
>> - Kevin
>
> I had a few services I needed to configure to retrieve data from the
> net... however I would not recommend doing this with MySQL table data.
> Perhaps you can explain on why you have chosen this setup as there might
> be a different, more appropriate way.
>
> Now to your question. As windows starts it also starts the services, this
> is done before a user is signed in. Drive mappings are user dependent and
> are made when a user logs in, so the drive mappings are not available at
> the time the MySQL service is started. Therefor it works when you are
> logged in and manually start the service, but not when you automatically
> start it before you log in.
>
> It might work if you don't use the mapped drive but use the full UNC path
> to the datadir, those paths are usually resolved by the DNS and are
> available to windows at the time it starts the services. If you don't have
> DNS or name resolving you might want to use the IP number, so either:
>
> \\server\path\to\mysql\datadir
>
> or
>
> \\ip-number\path\to\mysql\datadir
>
> Good luck, but I am really curious if you really need this setup.
>
> Jonathan


that's a good idea to try the full UNC path instead although i don't think
that's the problem since i am not performing my testing during windows
startup but only after login and all drives are connected (remember too that
this network share CAN be used as the mysql datadir when i start mysql via
'mysqld-nt --console')

to explain why i want to set it up this way (and i've been quickly changing
my mind over the last week in order to finally relieve my headaches)...

my environment is for testing/development only, i'm a web developer working
from home and i just purchased an old compaq to use as my server (got tired
of running all the required services on my main workstation, plus i want to
expose this server to the internet so i can "stage" the websites to my
clients during development)

in short, i don't trust the compaq HD for keeping my data since it's quite
old, etc.

i currently keep all my development files (everything from photoshop designs
to html/php files and all mysql tables) on an external hard drive connected
to my workstation (i then do backups from my external drive down to my
workstation drive, etc.)

i like to keep all my development files in a single location for portability
(in case i need to work remotely i just bring the drive) and ease of backup,
etc.

at this point i am willing to keep trying this configuration but it seems to
make more sense to just move the datadir to the same physical drive as my
mysql basedir (this has been tested and does work, btw) and perform nightly
dumps to my external drive via the network share

now i'm wondering, is it also a bad idea to run apache on my server and have
its document root set to this external drive too?

sorry for being long-winded (and for top-posting earlier)... hope this gives
everyone some more insight

- kevin

Re: Error 1067 when starting as service, no problem in standalone

"Kevin D." wrote in message
news:%vPFf.55749$V.12616@fed1read04...
> my environment is for testing/development only, i'm a web developer
> working from home and i just purchased an old compaq to use as my server
. . .
> in short, i don't trust the compaq HD for keeping my data since it's quite
> old, etc.

If you have doubts about the stability of the hard drive in the Compaq, it's
a pretty inexpensive task to replace the drive. Or replace the whole
machine!

> now i'm wondering, is it also a bad idea to run apache on my server and
> have its document root set to this external drive too?

The risk of data corruption is less, since typically files used by Apache
are read-only. But I would still not do this. For instance, you said you
plan to make your server open to external clients. What happens when
someone tries to preview some of your staged websites, when your workstation
is off?

Regards,
Bill K.

Re: Error 1067 when starting as service, no problem in standalone

"Bill Karwin" wrote in message
news:ds8ihc01s5p@enews4.newsguy.com...
> "Kevin D." wrote in message
> news:%vPFf.55749$V.12616@fed1read04...
>> my environment is for testing/development only, i'm a web developer
>> working from home and i just purchased an old compaq to use as my server
> . . .
>> in short, i don't trust the compaq HD for keeping my data since it's
>> quite old, etc.
>
> If you have doubts about the stability of the hard drive in the Compaq,
> it's a pretty inexpensive task to replace the drive. Or replace the whole
> machine!
>
>> now i'm wondering, is it also a bad idea to run apache on my server and
>> have its document root set to this external drive too?
>
> The risk of data corruption is less, since typically files used by Apache
> are read-only. But I would still not do this. For instance, you said you
> plan to make your server open to external clients. What happens when
> someone tries to preview some of your staged websites, when your
> workstation is off?
>
> Regards,
> Bill K.
>

points noted, but my workstation stays on all the time... i'm just trying to
off-load some cpu power from it and dedicate the "server" tasks to, well,
the server :)

i may have to consider moving all my web and db files to the server, and
just performing nightly backups onto the external drive