replication_fix

<?php
set_time_limit(0);

// set input args
$master_host = $argv[1];
$master_user = $argv[2];
$master_pass = $argv[3];

$slave_host = $argv[4];
$slave_user = $argv[5];
$slave_pass = $argv[6];

$database = $argv[7];
$tablename = $argv[8];
$primary_key = $argv[9];
$secondary_key = $argv[10];

// check for required data
if (empty($master_host) || empty($master_user) || empty($master_pass))
    die ("Incorrect Input: Master server login data missing!rn");

if (empty($slave_host) || empty($slave_user) || empty($slave_pass))
    die ("Incorrect Input: Slave server login data missing!rn");

if (empty($database))
    die ("You didn't specify a database!rn");

if (empty($tablename))
    die ("You didn't specify a table!rn");

if (empty($primary_key))
    die ("You need to specify the primary key/ID field!rn");

// first, connect to the master
$master_link = mysql_connect($master_host, $master_user, $master_pass);

// check for link failure
if (!$master_link)
    die ("unable to connect to master!rn");

// select database on the master
@mysql_select_db($database, $master_link);

// connect to slave
$slave_link = mysql_connect($slave_host, $slave_user, $slave_pass);

// check for failure
if (!$slave_link)
    die ("Unable to connect to slave!rn");

// select database on the slave
@mysql_select_db($database, $slave_link);

$processed = 0;
$inserted = 0;
$updated = 0;

// get a result set from the table on master
$master_query = "select * from $tablename";
$master_result = @safe_query($master_query, $master_link);
while ($master_row = @mysql_fetch_array($master_result, MYSQL_ASSOC))
{
    $processed++;

    // we are going to check the primary key field and see if its in the same db/table as the slave
    $master_record_id = $master_row[$primary_key];

    // find out if this record exists on the slave
    $add_record = LookupSlaveRecord($slave_link, $master_record_id, $tablename, $primary_key);

    // check to see if we should add this record to slave, or continue
    if (empty($add_record))
    {
        // now find out if we have the secondary key to check
        if (!empty($secondary_key))
        {
            $secondary_data = $master_row[$secondary_key];

            // check to see if this key already has an entry
            $update_record = LookupSlaveRecord($slave_link, $secondary_data, $tablename, $secondary_key);

            // ok if the secondary key doesnt exist already, insert
            if (empty($update_record))
            {
                InsertSlaveRecord($slave_link, $master_row, $tablename);
                $inserted++;
            }
            else
            {
                // the secondary key exists on this table, update
                UpdateSlaveRecord($slave_link, $master_record_id, $secondary_data, $tablename, $primary_key, $secondary_key);
                $updated++;
            }

        }
        // we don't, just try to add
        else
        {
            // add this record to slave
            InsertSlaveRecord($slave_link, $master_row, $tablename);
            $inserted++;
        }
    }
    else
        // we already have this record, lets move on
        continue;
}

echo "processed: ".$processed."rn";
echo "inserted: ".$inserted."rn";
echo "updated: ".$updated."rn";

function UpdateSlaveRecord($slave_link, $master_record_id, $secondary_data, $tablename, $primary_key, $secondary_key)
{
    $q = "update $tablename set $primary_key='$master_record_id' where $secondary_key='".addslashes($secondary_data)."'";
    $r = safe_query($q);

    if ($r)
        return 1;
    else
        return 0;
}

// insert a record into the slave based on the current row item from the master
// make sure that the result set being passed is mysql_fetch_array with a type of
// MYSQL_ASSOC. otherwise foreach doesnt work correctly.
function InsertSlaveRecord($slave_link, $master_row, $tablename)
{
    // build the query
    $q = "insert into $tablename (";
    $values = " values(";
    foreach ($master_row as $name => $data)
    {
        // update the query
        $q .= " ".$name.",";

        // update values string
        $values .= " '".addslashes($data)."',";
    }

    // remove last comma in the query and values strings and close parenthesis
    $pos = strrpos($q, ",");
    $q = substr($q, 0, $pos);
    $q .= ")";

    $pos = strrpos($values, ",");
    $values = substr($values, 0, $pos);
    $values .= ")";

    // append values
    $q = $q . $values;

  // execute
    $r = safe_query($q, $slave_link);
    if ($r)
        return 1;
    else
        return 0;
}

// find out if a record on the slave exists
function LookupSlaveRecord($slave_link, $master_record_id, $tablename, $primary_key)
{
    // look up the record
    $q = "select $primary_key from $tablename where $primary_key='".addslashes($master_record_id)."'";
    $r = safe_query($q, $slave_link);
    if (empty($r))
    {
        return false;
    }
    // record exists
    if ($row = mysql_fetch_array($r))
        return 1;

    // record doesnt exist
    else
        return 0;
}

function safe_query($q, $slave_link = null)
{
    $res = @mysql_query($q, $slave_link);
    if (empty($res))
    {
        echo mysql_error(), PHP_EOL, $q, PHP_EOL;
    }
    return $res;
}