vicidial.org

I am trying to set up an automatic crontab command for MySQL the command is below, obviously it is

on command line it would be

root
"mysql"
"use asterisk"
"UPDATE vicidial_list SET status='DNC' WHERE status='AFTHRS' and list_id='997';"

This works fine via command line, but I would like to set it up to run automatically every few hours? I tried the below but no joy.

### DNC List
#*/1 * * * * mysql -dasterisk -eUPDATE vicidial_list SET status='DNC' WHERE status='AFTHRS' and list_id='997'

I would do like this:
echo "UPDATE vicidial_list SET status='DNC' WHERE status='AFTHRS' and list_id='997'; " | mysql -D asterisk

So like this

*/1 * * * * echo UPDATE vicidial_list SET status='DNC' WHERE status='AFTHRS' and list_id='997'; | mysql -D asterisk

or

*/1 * * * * echo "UPDATE vicidial_list SET status='DNC' WHERE status='AFTHRS' and list_id='997';" | mysql -D asterisk

The difference in the 2 is the quotes?

Any thoughts anyone, the above did not work or I did wrong?

Thanks in advance

to execute mysql from the command line:

Code: Select all

mysql asterisk -u cron -p1234 -e "UPDATE vicidial_list SET status='DNC' WHERE status='AFTHRS' and list_id='997'"

for a cron, you would add * * * * * in front of it (*/1 is redundant) if in crontab -e or "* * * * * root" if in /etc/crontab.

you sure you want to run this every minute?

The once a minute was just for testing every 4 hours is fine. Thank you.

As always William you have all the answers, thank you that worked perfectly.

we aim to please.

and remember: you can thank me by hanging around and helping a couple newbies. you definitely know enough to answer one question per week. and given the odds it'll renew your knowledge base a bit and help you keep sharp.

Also remember that MySQL/MariaDB has a built in scheduler (I'm using it to reset my ADCT/ADC statuses nightly).

geoff3dmg wrote:Also remember that MySQL/MariaDB has a built in scheduler (I'm using it to reset my ADCT/ADC statuses nightly).

Only a crazy person would attempt such a thing. Let me know how it works out. LOL

It's been working just fine for months. You need to enable the scheduler in the my.conf and restart mariadb. Here's the SQL;

Code: Select all

DROP EVENT `Reset ADCT Leads`;
CREATE DEFINER=`root`@`localhost` EVENT `Reset ADCT Leads` ON SCHEDULE EVERY 1 DAY STARTS '2014-01-01 03:00:00' ON COMPLETION NOT PRESERVE ENABLE DO
UPDATE asterisk.vicidial_list as vl
INNER JOIN asterisk.vicidial_log as vlog ON vl.lead_id=vlog.lead_id AND vl.last_local_call_time=vlog.call_date
INNER JOIN asterisk.vicidial_campaigns as vc ON vc.campaign_id=vlog.campaign_id
SET vl.status = 'ADCT'
WHERE vlog.status = 'ADCT' and vl.status = 'ADC' and vl.called_count <= vc.call_count_limit

So it basically sets any ADC leads to ADCT if they are ADCT in the call log and have not hit the campaign call count limit. I have ADCT as a dialable status in my campaigns.

excellent postback of details.

now the next question: is there ANY advantage to this over just using a script to fire the same sql? i'm not sure the tiny overhead of a php/perl script is enough. but I could see where this may be something that could have a php page built to easily create new timed scripts that don't require a php script to fire every minute to see if there's anything to do right now. interesting.

I don't know how to do this in perl/php.

There are 100 examples of it in /usr/share/astguiclient and /srv/www/htdocs/vicidial or agc.

Or you can put it in crontab -e or /etc/crontab as a mysql from the command line entry:

Code: Select all

mysql -u cron -p1234 asterisk -e "select user,pass,active from vicidial_users where user_level='9'"

Adding crontab -e timing options:

Code: Select all

* * * * *   mysql -u cron -p1234 asterisk -e "select user,pass,active from vicidial_users where user_level='9'"

Plus we could supply you with a SIMPLISTIC template such as this:

Code: Select all

<?PHP
define('DEBUG', false);
define('CLIDISPLAY', false);
if (CLIDISPLAY) {
    define('PRE', '');
    define('PRE_END', '');
} else {
    define('PRE', '<pre>');
    define('PRE_END', '</pre>');
}
require_once("../vicidial/dbconnect.php");
$DBLink = new mysqli($VARDB_server, $VARDB_user, $VARDB_pass, $VARDB_database, $VARDB_port);
if ($DBLink->connect_errno) {
    printf(PRE . "Connect failed: %s\n" . PRE_END, $DBLink->connect_error);
    exit();
}

# Sample execution
$Query = "select * from vicidial_users where user='6666' and active='Y' limit 1";
$Records = GetData($DBLink, $Query);
print_r($Records[0]); // Single record return access via [0] to access a field named "id": $Records[0]['id']
// Multiple record return access via array walking
foreach ($Records as $Record) {
    print_r($Record);
}
$Query = "update vicidial_users set active='Y' where user='6666' limit 1";
UpdateData($DBLink, $Query);

#Functions

function GetData($DBLink, $Query) {
    if (DEBUG) {
        echo PRE . "Query: $Query\n" . PRE_END;
    }
    if ($Result = $DBLink->query($Query)) {
        if (DEBUG) {
            printf(PRE . "Affected rows (Non-Select): %d\n" . PRE_END, $DBLink->affected_rows);
        }
        while ($Record = $Result->fetch_assoc()) {
            $ReturnData[] = $Record;
        }
        return $ReturnData;
    } else {
        if (DEBUG) {
            printf(PRE . "Errormessage: %s\n", $DBLink->error);
            printf("Affected rows (Non-Select): %d\n", $DBLink->affected_rows);
            echo "No Records Returned\n" . PRE_END;
        }
        return false;
    }
}

function UpdateData($DBLink, $Query) {
    if (DEBUG) {
        echo PRE . "Query: $Query\n" . PRE_END;
    }
    if ($Result = $DBLink->query($Query)) {
        if (DEBUG) {
            printf(PRE . "%s\n", $DBLink->info);
            printf("Affected rows (Non-Select): %d\n" . PRE_END, $DBLink->affected_rows);
        }
        return;
    } else {
        if (DEBUG) {
            printf(PRE . "Errormessage: %s\n", $DBLink->error);
            printf("Affected rows (Non-Select): %d\n", $DBLink->affected_rows);
            echo "No Records Returned\n" . PRE_END;
        }
        return;
    }
}


Note that the "require_once("../vicidial/dbconnect.php");" must actually point to the dbconnect file for this to work, or you could hard-code the credentials in if you prefer.

Everything below # Functions is static (no need to change it, just be sure it's in the file).

You don't need all the "sample execution" lines, just whichever one you choose to use. GetData returns records as an array, UpdateData doesn't return records (but either will perform the sql command, regardless).