copy table from 1 server to identical table on another server

[tunage]

I have data on a table1 on one server I need copied onto a table1 on another server that is freshly truncated.
I am not getting any error output in the logs or on the screen, but no data ever appears on the second server.

mysql replication is banned and no access to cli for mysqldump (this code will be hit numerous times during the day)




$pdo = new PDO(
'mysql:host=' . DB_HOST_R2D2 . ';dbname=' . DB_DATABASE_DNS,
DB_USER_DNS,
DB_PASSWORD
);
//yoda pdo settings
$pdoyd = new PDO(
'mysql:host=' . DB_HOST_YODA . ';dbname=' . DB_DATABASE_DNS,
DB_USER_DNS,
DB_PASSWORD
);
$pdoyd->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$pdoyd->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
//records table column names
$recordstbl = array('id', 'name', 'type', 'content', 'ttl', 'prio', 'change_date', 'disabled', 'ordername', 'auth');
//domain table column names
$domainstbl = array('id', 'name', 'master', 'last_check', 'type', 'notified_serial', 'account');
//crypto table column names
$cryptotbl = array('id', 'domain_id', 'flags', 'active', 'content');
$tblnames = array('cryptokeys', 'domains', 'records');
//loop through yoda and trunacate all 3 tables
foreach($tblnames as $tbl){
$sql = 'truncate '.$tbl;
$statementyd = $pdoyd->prepare($sql);
$useryd = $statementyd->execute();
var_dump($statementyd);
echo '<br>';
}
//crazy triple loop to get sql query correct
foreach($tblnames as $tbl){
if($tblnames == 'cryptokeys'){
foreach($cryptotbl as $column){
foreach ($column as $pdcolumn){
$pdcolumn = ':'.$pdcolumn;
}
$insert_stmt = $pdoyd->prepare("INSERT INTO ".$tbl." (".$column.") VALUES (".$pdcolumn." ON DUPLICATE KEY IGNORE");
$select_results = $pdo->query("SELECT * FROM ".$tbl);
while ($row = $select_results->fetch(PDO::FETCH_ASSOC)) {
$insert_stmt->execute($row);
}
}
}
if($tblnames == 'domains'){
foreach($domainstbl as $column){
foreach ($column as $pdcolumn){
$pdcolumn = ':'.$pdcolumn;
}
$insert_stmt = $pdoyd->prepare("INSERT INTO ".$tbl." (".$column.") VALUES (".$pdcolumn." ON DUPLICATE KEY IGNORE");
$select_results = $pdo->query("SELECT * FROM ".$tbl);
while ($row = $select_results->fetch(PDO::FETCH_ASSOC)) {
$insert_stmt->execute($row);
}
}
}
if($tblnames == 'records'){
foreach($recordstbl as $column){
foreach ($column as $pdcolumn){
$pdcolumn = ':'.$pdcolumn;
}
$insert_stmt = $pdoyd->prepare("INSERT INTO ".$tbl." (".$column.") VALUES (".$pdcolumn." ON DUPLICATE KEY IGNORE");
$select_results = $pdo->query("SELECT * FROM ".$tbl);
while ($row = $select_results->fetch(PDO::FETCH_ASSOC)) {
$insert_stmt->execute($row);
}
}
}

}



logs (source) db1:

mysql> select * from mysql.general_log;
+---------------------+---------------------------------------+-----------+-----------+--------------+-------------------------------------------------------------------------------------------------------------------------------------------------------+
| event_time | user_host | thread_id | server_id | command_type | argument |
+---------------------+---------------------------------------+-----------+-----------+--------------+-------------------------------------------------------------------------------------------------------------------------------------------------------+
| 2016-01-17 00:34:10 | root[root] @ localhost [127.0.0.1] | 7 | 1 | Query | SELECT content,ttl,prio,type,domain_id,disabled,name,auth FROM records WHERE disabled=0 and type='SOA' and name='nyctelecomm.com' |
| 2016-01-17 00:34:10 | root[root] @ localhost [127.0.0.1] | 7 | 1 | Query | SELECT content,ttl,prio,type,domain_id,disabled,name,auth FROM records WHERE disabled=0 and name='nyctelecomm.com' and domain_id=6 |
| 2016-01-17 00:34:10 | root[root] @ localhost [127.0.0.1] | 7 | 1 | Query | select content from domains, domainmetadata where domainmetadata.domain_id=domains.id and name='nyctelecomm.com' and domainmetadata.kind='PRESIGNED' |
| 2016-01-17 00:34:10 | root[root] @ localhost [127.0.0.1] | 7 | 1 | Query | select cryptokeys.id, flags, active, content from domains, cryptokeys where cryptokeys.domain_id=domains.id and name='nyctelecomm.com' |
| 2016-01-17 00:34:11 | root[root] @ localhost [127.0.0.1] | 6 | 1 | Query | select content from domains, domainmetadata where domainmetadata.domain_id=domains.id and name='nyctelecomm.com' and domainmetadata.kind='NSEC3PARAM' |
| 2016-01-17 00:34:11 | root[root] @ localhost [127.0.0.1] | 6 | 1 | Query | SELECT content,ttl,prio,type,domain_id,disabled,name,auth FROM records WHERE disabled=0 and type='SOA' and name='nyctelecomm.com' |
| 2016-01-17 00:34:11 | root[root] @ localhost [127.0.0.1] | 6 | 1 | Query | select min(ordername) from records where ordername > '' and domain_id=6 and disabled=0 and ordername is not null |
| 2016-01-17 00:34:11 | root[root] @ localhost [127.0.0.1] | 6 | 1 | Query | select ordername, name from records where ordername <= '' and domain_id=6 and disabled=0 and ordername is not null order by 1 desc limit 1 |
| 2016-01-17 00:34:11 | root[root] @ localhost [127.0.0.1] | 6 | 1 | Query | select content from domains, domainmetadata where domainmetadata.domain_id=domains.id and name='nyctelecomm.com' and domainmetadata.kind='SOA-EDIT' |
| 2016-01-17 00:34:11 | [powerdns] @ [108.61.175.20] | 420 | 1 | Connect | powerdns@108.61.175.20 on powerdns |
| 2016-01-17 00:34:12 | powerdns[powerdns] @ [108.61.175.20] | 420 | 1 | Prepare | SELECT domain_id, name, type FROM records |
| 2016-01-17 00:34:12 | powerdns[powerdns] @ [108.61.175.20] | 420 | 1 | Execute | SELECT domain_id, name, type FROM records |
| 2016-01-17 00:34:12 | powerdns[powerdns] @ [108.61.175.20] | 420 | 1 | Close stmt | |
| 2016-01-17 00:34:12 | powerdns[powerdns] @ [108.61.175.20] | 420 | 1 | Quit | |
| 2016-01-17 00:34:13 | [powerdns] @ [108.61.175.20] | 421 | 1 | Connect | powerdns@108.61.175.20 on powerdns |
| 2016-01-17 00:34:13 | powerdns[powerdns] @ [108.61.175.20] | 421 | 1 | Quit | |
| 2016-01-17 00:34:19 | root[root] @ localhost [] | 411 | 1 | Query | select * from mysql.general_log |
+---------------------+---------------------------------------+-----------+-----------+--------------+-------------------------------------------------------------------------------------------------------------------------------------------------------+
17 rows in set (0.00 sec)



logs (target) db2:

mysql> select * from mysql.general_log;
+---------------------+--------------------------------------------+-----------+-----------+--------------+--------------------------------------------------------------------------------------------------------------------------------------+
| event_time | user_host | thread_id | server_id | command_type | argument |
+---------------------+--------------------------------------------+-----------+-----------+--------------+--------------------------------------------------------------------------------------------------------------------------------------+
| 2016-01-17 00:34:15 | powerdns[powerdns] @ localhost [127.0.0.1] | 9 | 2 | Query | SELECT content,ttl,prio,type,domain_id,disabled,name,auth FROM records WHERE disabled=0 and type='SOA' and name='www.zippy-mail.com' |
| 2016-01-17 00:34:15 | powerdns[powerdns] @ localhost [127.0.0.1] | 9 | 2 | Query | SELECT content,ttl,prio,type,domain_id,disabled,name,auth FROM records WHERE disabled=0 and type='SOA' and name='zippy-mail.com' |
| 2016-01-17 00:34:15 | powerdns[powerdns] @ localhost [127.0.0.1] | 9 | 2 | Query | SELECT content,ttl,prio,type,domain_id,disabled,name,auth FROM records WHERE disabled=0 and type='SOA' and name='com' |
| 2016-01-17 00:34:15 | powerdns[powerdns] @ localhost [127.0.0.1] | 9 | 2 | Query | SELECT content,ttl,prio,type,domain_id,disabled,name,auth FROM records WHERE disabled=0 and type='SOA' and name='' |
| 2016-01-17 00:34:23 | powerdns[powerdns] @ localhost [127.0.0.1] | 8 | 2 | Query | SELECT content,ttl,prio,type,domain_id,disabled,name,auth FROM records WHERE disabled=0 and type='SOA' and name='nyctelecomm.com' |
| 2016-01-17 00:34:25 | [powerdns] @ [108.61.175.20] | 246 | 2 | Connect | powerdns@108.61.175.20 on powerdns |
| 2016-01-17 00:34:25 | powerdns[powerdns] @ [108.61.175.20] | 246 | 2 | Prepare | SELECT domain_id, name, type FROM records |
| 2016-01-17 00:34:25 | powerdns[powerdns] @ [108.61.175.20] | 246 | 2 | Execute | SELECT domain_id, name, type FROM records |
| 2016-01-17 00:34:25 | powerdns[powerdns] @ [108.61.175.20] | 246 | 2 | Close stmt | |
| 2016-01-17 00:34:25 | powerdns[powerdns] @ [108.61.175.20] | 246 | 2 | Quit | |
| 2016-01-17 00:34:26 | [powerdns] @ [108.61.175.20] | 247 | 2 | Connect | powerdns@108.61.175.20 on powerdns |
| 2016-01-17 00:34:26 | powerdns[powerdns] @ [108.61.175.20] | 247 | 2 | Prepare | truncate cryptokeys |
| 2016-01-17 00:34:26 | powerdns[powerdns] @ [108.61.175.20] | 247 | 2 | Execute | truncate cryptokeys |
| 2016-01-17 00:34:26 | powerdns[powerdns] @ [108.61.175.20] | 247 | 2 | Prepare | truncate domains |
| 2016-01-17 00:34:26 | powerdns[powerdns] @ [108.61.175.20] | 247 | 2 | Close stmt | |
| 2016-01-17 00:34:26 | powerdns[powerdns] @ [108.61.175.20] | 247 | 2 | Execute | truncate domains |
| 2016-01-17 00:34:26 | powerdns[powerdns] @ [108.61.175.20] | 247 | 2 | Prepare | truncate records |
| 2016-01-17 00:34:26 | powerdns[powerdns] @ [108.61.175.20] | 247 | 2 | Close stmt | |
| 2016-01-17 00:34:26 | powerdns[powerdns] @ [108.61.175.20] | 247 | 2 | Execute | truncate records |
| 2016-01-17 00:34:26 | powerdns[powerdns] @ [108.61.175.20] | 247 | 2 | Close stmt | |
| 2016-01-17 00:34:26 | powerdns[powerdns] @ [108.61.175.20] | 247 | 2 | Quit | |
| 2016-01-17 00:34:41 | root[root] @ localhost [] | 237 | 2 | Query | select id, domain_id, name, type, content from records |
| 2016-01-17 00:34:49 | root[root] @ localhost [] | 237 | 2 | Query | select * from mysql.general_log |
+---------------------+--------------------------------------------+-----------+-----------+--------------+--------------------------------------------------------------------------------------------------------------------------------------+
23 rows in set (0.00 sec)

[mac_gyver]

you need to have php's error_reporting set to E_ALL and display_errors set to ON so that all the php detected errors will be reported and displayed.

 

your sql query statements, at least the first INSERT statement, i stopped looking after that point, are throwing errors, which would result in uncaught exceptions for the posted code. at least the first INSERT statement doesn't contain a correct list of column names or place-holder names and will be throwing sql errors. there are other sql syntax errors in the queries as well.

 

i also recommend that you form your sql query statements in php variables, so that you can echo them to see if the sql syntax is correct.

 

to make the list of column names, you should implode() the array holding the column names, with a comma as the implode separator. you can make the list of place-holder names by using a ',:' as the implode separator.

 

edit: also, when posting code on the forum, please use the forum's

bbcode tags (the edit form's <> button) around code.

Edited January 17, 2016 by mac_gyver

[mac_gyver]

in addition to the above problems, you have a fundamental logic error in your code - if($tblnames == 'cryptokeys'){. $tblnames isn't the variable holding each table name inside the loop.

 

see the following much simplified code that should (untested) work - 

<?php
$pdo = new PDO(
'mysql:host=' . DB_HOST_R2D2 . ';dbname=' . DB_DATABASE_DNS,
DB_USER_DNS,
DB_PASSWORD
);
//yoda pdo settings
$pdoyd = new PDO(
'mysql:host=' . DB_HOST_YODA . ';dbname=' . DB_DATABASE_DNS,
DB_USER_DNS,
DB_PASSWORD
);
$pdoyd->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$pdoyd->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);

$tblnames = array('cryptokeys', 'domains', 'records');

// column names per each table
$cols['records'] = array('id', 'name', 'type', 'content', 'ttl', 'prio', 'change_date', 'disabled', 'ordername', 'auth');
$cols['domains'] = array('id', 'name', 'master', 'last_check', 'type', 'notified_serial', 'account');
$cols['cryptokeys'] = array('id', 'domain_id', 'flags', 'active', 'content');

//loop through yoda and trunacate all 3 tables
foreach($tblnames as $tbl){
    echo $sql = 'truncate '.$tbl;
    echo '<br>';
    $pdoyd->query($sql);
}

foreach($tblnames as $tbl){
    echo $query = "INSERT IGNORE INTO $tbl (`".implode('`,`',$cols[$tbl])."`) VALUE (:".implode(',:',$cols[$tbl]).")";
    echo '<br>';
    $stmt = $pdoyd->prepare($query);
    $select_results = $pdo->query("SELECT * FROM ".$tbl);
    while ($row = $select_results->fetch(PDO::FETCH_ASSOC)) {
        $stmt->execute($row);
    }
}