[SOLVED] Run SQL

[The Little Guy]

Is it possible to have mysql run a sql script every 30 minutes?

 

I wan't one to check if users ar still online...

[AndyB]

php can. cron on unix, scheduler on windoze

[The Little Guy]

I am using a shared server, does it still work?

[AndyB]

I am using a shared server, does it still work?

So am I and I have multiple cron tasks running.  Your host may permit cron tasks. Check your server control panel and/or host FAQs

[The Little Guy]

OK... they do support, but How do I write a Cron Job?

[Wildbug]

What OS?

[The Little Guy]

Debian Linux

[Wildbug]

man crontab

[The Little Guy]

My host has a cron feature built into their panel (They made it themself).

 

here is where I do the info:

Command to run: (max 1000 characters)

Use full paths to files and programs.

[The Little Guy]

I am assuming that I just need to place the file directory in that field like so:

/var/log/date.log

[Wildbug]

Is that your MySQL script?  You should probably run it with the MySQL client command.

 

/usr/bin/mysql dbname --user=username --password=passwd < /var/log/date.log

[The Little Guy]

no... that was just an example, but here is what the page looks like where i enter in my information

 

[attachment deleted by admin]

[Wildbug]

Okay, so you'll put the command in the "Command to run" box using full paths, like the example I put above.

[The Little Guy]

is it possible to run a .sql file with cron instead of a .php file?

 

if so... how do I connect to the database with that file?

[Wildbug]

Yes, like in the example above, use the mysql client prog.

 

http://dev.mysql.com/doc/refman/5.1/en/batch-commands.html

[The Little Guy]

OK... I created one... now I'll see if it works.

 

I used this code:

/home/ryannaddy/cron/logOut.sql

[Wildbug]

You need to write a whole command, just as you would from the command line, not just the name of the batch file.

[The Little Guy]

Before I try this... good or no?

/usr/bin/mysql publicsize --user=cantremember --password=xxxxxx < /home/ryannaddy/cron/logOut.sql

[Wildbug]

Assuming that's the correct path to the mysql client, then YES!  You should try it from the command line/shell prompt if you have access.  If it works there, it should work in cron.

[The Little Guy]

if I run it in PuTTy, I get this:

-bash: !!: event not found

 

What does that mean?

[Wildbug]

No idea.  Did you enter the command?  The "!!" is used in bash to mean "the last command." i.e., if you ran, say, "ls -l" but forgot to add the path, you could type "!! /tmp" and get "ls -l /tmp"... but you didn't do that, did you?  What's in your SQL script?

[The Little Guy]

Think it is because of my password?

/usr/bin/mysql publicsize --user=limited --password=rmn!!2233 < /home/ryannaddy/cron/logOut.sql

 

logOut.sql:

UPDATE `users` SET `onlineStatus` = '0' WHERE `onlineStatus` = '1' AND (UNIX_TIMESTAMP(NOW())-`lastAccess` > 1800);

[Wildbug]

Oh.  Yes, that's why.  Quoting it should work:  --password="rmn!!2233"

[The Little Guy]

OK... that removed the bash error, but now I get this:

 

ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2)

[Wildbug]

Hmm, this would imply that the MySQL server is not running.  Verify by running "ps aux|grep mysqld" from the shell prompt.