LinuxQuestions.org
Support LQ: Use code LQ3 and save $3 on Domain Registration
Go Back   LinuxQuestions.org > Forums > Linux Forums > Linux - Newbie
User Name
Password
Linux - Newbie This Linux forum is for members that are new to Linux.
Just starting out and have a question? If it is not in the man pages or the how-to's this is the place!

Notices


Reply
  Search this Thread
Old 09-17-2003, 09:03 AM   #1
jlinden
LQ Newbie
 
Registered: Sep 2003
Posts: 7

Rep: Reputation: 0
Cron/MySQL problem


I am fairly new to both linux and mysql… I have a cron job that runs every minute to move data from one mysql table to another (both tables are in the same database), see below:

Cron job is cronupdsysklog2:

mysql -u *** --password=*** syslog < /var/scripts/updsysklog2

Script is:

INSERT INTO sysklog2 (machine,facility,priority,logdate,logtime,message)
SELECT machine, facility, priority, cast(left(date1,8) as date) as logdate, cast(right(date1,6) as time) as logtime, message FROM sysklog;

DELETE FROM sysklog;

The problem I am having is every time this runs it adds ~250k to memory and doesn’t release it then server locks up every 5 or 6 days.

Any ideas on how to fix this?

FYI... I made the script executable by using:
chmod 700 /var/scripts/cronupdsysklog2

Thanks!
 
Old 09-17-2003, 09:35 AM   #2
Andy@DP
Member
 
Registered: Aug 2003
Location: Aberdeen, UK.
Distribution: Debian, Ubuntu
Posts: 208
Blog Entries: 2

Rep: Reputation: 30
One solution I could think of would be to use a PHP script instead. There are tutorials out there that explain about running them using cron.
That way you can use the PHP mysql_connect and mysql_close methods. I think this would help free up resources...oh and using mysql_free_result after you use the mysql_query method.

Hope that helps
 
Old 09-18-2003, 12:55 PM   #3
jlinden
LQ Newbie
 
Registered: Sep 2003
Posts: 7

Original Poster
Rep: Reputation: 0
Unhappy

I changed it to a PHP cron job and I am still having the same problem, every minute the job runs ram usage increase by 200-500k. Here is the php script i wrote:

include("dbinfo.inc.php");

$connect = MYSQL_CONNECT($hostname, $username, $password) OR DIE("Unable to connect to the database");
@mysql_select_db( "$dbName") or die( "Unable to select database");
$insert = "INSERT INTO sysklog2 (machine,facility,priority,logdate,logtime,message) SELECT machine, facility, priority, cast(left(date1,8) as date) as logdate, cast(right(date1,6) as time) as logtime, message FROM sysklog;";
$query = mysql_query($insert);
$delete = "DELETE from sysklog;";
$query = mysql_query($delete);
mysql_close($connect);


Any suggestions/ideas?
 
Old 09-19-2003, 09:55 AM   #4
Andy@DP
Member
 
Registered: Aug 2003
Location: Aberdeen, UK.
Distribution: Debian, Ubuntu
Posts: 208
Blog Entries: 2

Rep: Reputation: 30
after last $query call mysql_free_result($query) to release system resources. Failing that use mysql_pconnect to use the same persistant connection, that may help

Hope that works.

Last edited by Andy@DP; 09-19-2003 at 09:56 AM.
 
  


Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off



Similar Threads
Thread Thread Starter Forum Replies Last Post
Cron Jobs with MySQL SharkBait Mandriva 1 06-07-2005 02:23 AM
Mysql dumps via Cron job. Thrifty Linux - Software 2 07-28-2004 03:13 PM
how to run the mysql load statement in cron gschrade Linux - Software 5 07-27-2004 03:27 PM
starting mysql with cron shimmer2101 Linux - Software 9 07-02-2004 09:37 AM
Backup MySQL w/ CRON tracer Linux - General 0 03-28-2003 12:40 PM


All times are GMT -5. The time now is 04:16 AM.

Main Menu
Advertisement
My LQ
Write for LQ
LinuxQuestions.org is looking for people interested in writing Editorials, Articles, Reviews, and more. If you'd like to contribute content, let us know.
Main Menu
Syndicate
RSS1  Latest Threads
RSS1  LQ News
Twitter: @linuxquestions
Facebook: linuxquestions Google+: linuxquestions
Open Source Consulting | Domain Registration