statments issued ad server startup

[Jesper]

Hi,

I would like to issue three statements to the database (mysql v4.1)
each time the server starts up. Is there any built in way to do this?
Like a init file to put them in?

At the moment I have a perlscript that starts the server and issues
these statements but I have to supply password each time. It feels
like there could be a better way of solving this..

//Jesper

[Bill Karwin]

Jesper wrote:

I would like to issue three statements to the database (mysql v4.1)
each time the server starts up. Is there any built in way to do this?
Like a init file to put them in?

You don't mention what operating system you're on. On UNIX/Linux, the
way to do this would be to start mysql in an init script (probably in
/etc/init.d or /etc/rc.d/init.d depending on your platform).

I would recommend scripting your other startup tasks in a separate
script, but link it in the rc.d directories as a `SnnMyScript' script,
where nn is a number greater than the number of your mysql startup
script link. For example, if mysql is S71mysqld, make your own script
at least S72MyScript.

Refer to docs on init if you don't understand what I'm talking about.

Regards,
Bill K.

[Jesper]

I think my question was a little unclear. Sorry ... It is not the
problem of running a scipt or starting a service when the computer
starts up. But the problem of issuing statements to the MySQL
-database everytime it is started.
These are the statements:

select initprobedata(id,filename) from AFFYMETRIX.PROBEDATAFILES;
select initqnormdata(id,filename) from AFFYMETRIX.QNORM;

Right now I have a "wrapper" script written in perl that starts mysql
(with /etc/init.d/mysql start) connects to the database and issues my
statments. The problem here is that I don't want passwords to the
database my script and therefore have to supply it every time... Can I
somehow avoid this by putting the statements in a initiation file that
are automatically issued to the mysql database at startup?

(I am running a mysql 4.1 on Fedora 2 system)

//Jesper
Bill Karwin <bi**@karwin.com> wrote in message news:<d1*********@enews2.newsguy.com>...

Jesper wrote:

I would like to issue three statements to the database (mysql v4.1)
each time the server starts up. Is there any built in way to do this?
Like a init file to put them in?

You don't mention what operating system you're on. On UNIX/Linux, the
way to do this would be to start mysql in an init script (probably in
/etc/init.d or /etc/rc.d/init.d depending on your platform).

I would recommend scripting your other startup tasks in a separate
script, but link it in the rc.d directories as a `SnnMyScript' script,
where nn is a number greater than the number of your mysql startup
script link. For example, if mysql is S71mysqld, make your own script
at least S72MyScript.

Refer to docs on init if you don't understand what I'm talking about.

Regards,
Bill K.

[Bill Karwin]

Jesper wrote:

I think my question was a little unclear. Sorry ... It is not the
problem of running a scipt or starting a service when the computer
starts up.
Well, I was suggesting that you could write an init script which doesn't
start any service, it just executes SQL statements. There's no rule
that init scripts must be used only to start services.

But I'm not sure if you mean that you want your SQL statements to run
each time the computer starts, or on some other schedule. If the
latter, you should use cron.
But the problem of issuing statements to the MySQL
-database everytime it is started.
These are the statements:

select initprobedata(id,filename) from AFFYMETRIX.PROBEDATAFILES;
select initqnormdata(id,filename) from AFFYMETRIX.QNORM;

Right now I have a "wrapper" script written in perl that starts mysql
(with /etc/init.d/mysql start) connects to the database and issues my
statments.
I'm a little confused... from your description, it sounds like you think
/etc/init.d/mysql needs to be run when you want to connect to a
database. That's not usually the case. /etc/init.d/mysql is typically
run at boot-time, and it starts a mysqld process which persists while
the machine is running. Then anytime you want to access a database, you
can use the client tools like mysql or mysqldump, or you can write your
own script using Perl/DBI or the like. But mysqld continues to run in
the meantime.

Anyway, that's the traditional way to use it. Of course, it's possible
to set things up so that mysqld _isn't_ started at boot-time, and then
you'd have to run /etc/init.d/mysql start (or mysqld_safe, which is run
by that script) the first time you want to use a database. But then you
wouldn't have to start up mysqld again unless you deliberately shut it down.

If you have an environment in which you have the mysqld server process
coming up and down irregularly, you could consider editing the
mysqld_safe script. Add a call to your Perl script (minus its
invocation of /etc/init.d/mysql, or else you'll have an infinite loop),
somewhere in mysqld_safe after that script has succeeded in starting the
mysqld process. I have never tried this, however, so I can't vouch for
this method.
The problem here is that I don't want passwords to the
database [in] my script and therefore have to supply it every time... Can I
somehow avoid this by putting the statements in a initiation file that
are automatically issued to the mysql database at startup?

There's a .my.cnf file, and there's a MYSQL_PWD environment variable,
which can each be used to store a password so you don't have to type it.
I recommend using the .my.cnf file, but make sure you chmod it to
prevent unauthorized people from reading it.

Read these pages:
http://dev.mysql.com/doc/mysql/en/pa...-security.html
http://dev.mysql.com/doc/mysql/en/option-files.html

Regards,
Bill K.

[Jorey Bump]

je****@sbc.su.se (Jesper) wrote in
news:3c**************************@posting.google.c om:

I think my question was a little unclear. Sorry ... It is not the
problem of running a scipt or starting a service when the computer
starts up. But the problem of issuing statements to the MySQL
-database everytime it is started.
These are the statements:

select initprobedata(id,filename) from AFFYMETRIX.PROBEDATAFILES;
select initqnormdata(id,filename) from AFFYMETRIX.QNORM;

Right now I have a "wrapper" script written in perl that starts mysql
(with /etc/init.d/mysql start) connects to the database and issues my
statments. The problem here is that I don't want passwords to the
database my script and therefore have to supply it every time... Can I
somehow avoid this by putting the statements in a initiation file that
are automatically issued to the mysql database at startup?

(I am running a mysql 4.1 on Fedora 2 system)

One solution is to create a mysql user with restricted SELECT privileges
that doesn't require a password from localhost. This is an acceptable
approach if the data isn't sensitive in your local environment;

GRANT SELECT ON AFFYMETRIX.PROBEDATAFILES TO 'foo'@'localhost';
GRANT SELECT ON AFFYMETRIX.QNORM TO 'foo'@'localhost';
FLUSH PRIVILEGES;

That limits the user to the specified tables, but you restrict the
columns, too:

GRANT SELECT (col1, col2) ON AFFYMETRIX.QNORM TO 'foo'@'localhost';