MySQLtransaction
My MySQL database is hosted on a different machine from my application system, which issues queries to the MySQL database remotely.
If for some reason the network connection between the two machines break, while a query was being processed, what will happen?
I suspect that MySQL will reject any garbage data but I don't want it to save that garbage data in my rows and I don't want MySQL to get stuck.
I might have solved my problem...
I was afraid that 2 different killer threads could: (1) show processlist, (2) choose the same connection to kill, (3) then both would kill it.
The first kill would trigger the execution of whatever I wanted, while the second kill could potentially blow things up.
The easiest, and nearly obvious, solution that I missed is to look at the other side of the problem: instead of making the worker thread unkillable, the killer threads must cooperate: the killer threads would (1) select get_lock('killer_thread'), then only after acquiring the lock they would (2) show processlist, (3) kill the chosen threads and finally (4) select release_lock('killer_thread').
Hello you can sync your local database data with your live mysql server. I had done it making a local sync file that will present all the tables of the local database that I wanted to update to live database and then calling a call back function in footer file that function calls the function to sync local data to live server
Footer.php
jQuery(document).ready(function($){
working = false;
var do_sync = function(){
if ( working ) { return; }
working = true;
jQuery.post(
"<?php echo $this->config->item("sync_url"); ?>",
{},
function(ret){
working = false;
}
);
}
window.setInterval(do_sync, 10000);
});
Config.php
$config['sync_server_url'] = "http://livesite.com/index.php/sync_server";
$config['sync_url'] = "http://localhost/localsite/index.php/sync";
sync.php contents Also you need to on CURL extension in local site wamp so that it can communicate with the server side, If database record that is already synced to server edited then sync field in DB should be set to 0 so that on edit it can overwrite server DB again.
Sync.php
<?php
class Sync extends CI_Controller {
private $tables;
function __construct() {
parent::__construct();
$this->load->database();
$this->tables = array(
"customers" => array("per_id"),
"db_log" => array("id"),
"inventory" => array("tr_id"),
"material" => array("id_mat"),
"mtracking" => array("id_tra"),
"orecipie" => array("id_rec"),
"moutlets" => array("id_st"),
"opeople" => array("per_id"),
"oreceivings" => array("rec_id"),
"osales" => array("sal_id"),
"osales_items" => array("sal_id", "itm_id"),
"osales_payments" => array("sal_id"),
"osales_sus" => array("sal_id", "emp_id"),
"osales_sus_items" => array("sal_id", "itm_id"),
"osales_sus_payments" => array("sal_id"),
"osuppliers" => array("per_id"),
);
$this->id_store = $this->config->item('id_store');
}
function index() {
echo '<br>'.date("H:i:s A");
$newLine = "\r\n";
$output = $update = "";
foreach($this->tables as $table_name => $columns ){
$col_check = "SHOW COLUMNS FROM `{$table_name}` LIKE 'is_sync'";
$col_exists = $this->db->query($col_check);
if ( $col_exists->num_rows > 0 ) { // is_sync column exists for this table
$sql = "SELECT * FROM {$table_name} WHERE is_sync = 0 LIMIT 500" ;
$rows = $this->db->query($sql);
if ( $rows->num_rows > 0 ) { // where is_sync = 0
foreach ( $rows->result_array() as $row ) {
$col_val = $update_col_val = $already_exists = array();
foreach($row as $name => $val) {
if ( is_null( $val ) ) {
continue;
}
$val = mysql_real_escape_string($val);
$update_col_val[] = " `{$name}` = '{$val}' ";
if ( "is_sync" === $name ) {
$val = 1;
}
if ( "id_store" === $name ) {
$val = $this->id_store;
}
$col_val[] = " `{$name}` = '{$val}' ";
if ( $name === $columns[0] ){
$already_exists[] = " `$columns[0]` = '{$val}'";
}
if ( $name === $columns[1] ){
$already_exists[] = " `$columns[1]` = '{$val}'";
}
}
if ( is_array($col_val) && count($col_val) > 0 ) {
//echo $this->table_keys[$table_name];
$output .= "SELECT * FROM `{$table_name}` WHERE `id_store` = {$this->id_store} AND " . implode(" AND ", $already_exists) . " ||";
$output .= "INSERT INTO `{$table_name}` SET " . implode(",", $col_val) . " ||";
$output .= "UPDATE `{$table_name}` SET " . implode(",", $col_val) . " WHERE `id_store` = {$this->id_store} AND " . implode(" AND ", $already_exists);
$output .= " ;; ";
$update .= "UPDATE {$table_name} SET is_sync = 1 WHERE " . implode(" AND ", $update_col_val) . " ;; ";
}
}
} // if is_sync = 0
} // if is_sync column exists
}
if ( !empty($output) && $this->post_data($output) ) {
$update = explode(" ;; ", $update);
foreach($update as $upq){
if (!empty($upq) ) {
$this->db->query($upq);
}
}
} else {
echo $result;
}
echo date("H:i:s A");
}
function post_data($qry){
$qry = htmlspecialchars(urlencode($qry));
$data = "qry=" . $qry;
$ch = curl_init();
curl_setopt( $ch, CURLOPT_URL,$this->config->item("sync_server_url") );
curl_setopt( $ch, CURLOPT_AUTOREFERER, 1);
curl_setopt ( $ch, CURLOPT_HTTPHEADER, array ( 'Content-length: ' . strlen($qry) ) );
curl_setopt( $ch, CURLOPT_RETURNTRANSFER, 1);
curl_setopt( $ch, CURLOPT_POST,1);
curl_setopt( $ch, CURLOPT_POSTFIELDS,$data);
curl_setopt( $ch, CURLOPT_CRLF, 1);
$result = curl_exec($ch);
curl_close($ch);
if ( 1 === intval($result) ) {
return TRUE;
} else {
echo $result;
return $result;
}
}
}
?>
Then after that you need to make a controller on live server I named it sync_server.php
sync_server.php
<?php
class Sync_server extends CI_Controller {
function __construct() {
parent::__construct();
$this->load->database();
}
function index() {
if (isset( $_POST['qry'] ) ) {
$qry = htmlspecialchars_decode( urldecode( $_POST['qry']));
//$this->connect_local_db();
$qry = explode(" ;; ", $qry);
foreach($qry as $q) {
$q = explode( "||", $q );
//print_r($q);
$exists = mysql_query( $q[0] ) or die ("<hr/>" . mysql_error() ."<br/>" . $q[0]);
if ( $exists && mysql_num_rows( $exists ) ) {
mysql_query( $q[2] ) or die ("<hr/>" . mysql_error()."<br />".$q[2]);
} else{
mysql_query( $q[1] ) or die ("<hr/>" . mysql_error()."<br />".$q[1]);
}
//$this->db->query($q);
}
die("1");
} else {
echo "qry not found";
}
}
}
?>
Best Answer
You have to understand that the underlying protocol that you're using to send dta(http://en.wikipedia.org/wiki/Transmission_Control_Protocol). The TC-Protocol is designed to deal with network breaks. Ultimately if you send a transmission to the remote server saying, for instance "insert into table (id, name) values (1, 'test');" then the TC-Protocol will handle not only delivering it, but also making sure it arrives in tact. In fact, it will even retry... but if there's a persistent network break it'll tell your application that the connection to the remote server was lost.
Thus, the answer is simply you do not need to do anything about this. TCP will make sure you don't get garbage data. TCP is the reason that you do not see garbage web-pages when you are on a shoddy connection. It also makes sure that you don't deliver garbage to your DB's.
We run about 500 million transactions a month into the DB and not one are garbage - because of TCP.