| 1 |
<?php |
| 2 |
|
| 3 |
namespace WPSynchro\Database; |
| 4 |
|
| 5 |
use WPSynchro\Database\Exception\SerializedStringException; |
| 6 |
use WPSynchro\Migration\MigrationController; |
| 7 |
use WPSynchro\Transport\Destination; |
| 8 |
use WPSynchro\Transport\RemoteTransport; |
| 9 |
use WPSynchro\Utilities\SyncTimerList; |
| 10 |
|
| 11 |
/** |
| 12 |
* Class for handling database migration |
| 13 |
*/ |
| 14 |
class DatabaseSync |
| 15 |
{ |
| 16 |
// Constants |
| 17 |
const TMP_TABLE_PREFIX = 'wpsyntmp_'; |
| 18 |
// Data objects |
| 19 |
public $job = null; |
| 20 |
public $migration = null; |
| 21 |
// Timers and limits |
| 22 |
public $timer = null; |
| 23 |
public $max_time_per_sync = 0; |
| 24 |
// Throttling |
| 25 |
public $has_backed_off_because_of_memory = false; |
| 26 |
// Dependencies |
| 27 |
public $logger; |
| 28 |
public $serialized_string_handler; |
| 29 |
|
| 30 |
/** |
| 31 |
* Constructor |
| 32 |
*/ |
| 33 |
public function __construct() |
| 34 |
{ |
| 35 |
$this->logger = MigrationController::getInstance()->getLogger(); |
| 36 |
$this->serialized_string_handler = new SerializedStringHandler(); |
| 37 |
} |
| 38 |
|
| 39 |
/** |
| 40 |
* Start a migration chunk - Returns completion percent |
| 41 |
*/ |
| 42 |
public function runDatabaseSync(&$migration, &$job) |
| 43 |
{ |
| 44 |
// Start timer |
| 45 |
$this->timer = SyncTimerList::getInstance(); |
| 46 |
|
| 47 |
$this->migration = &$migration; |
| 48 |
$this->job = &$job; |
| 49 |
|
| 50 |
$this->logger->log('INFO', 'Starting database migration loop with remaining time: ' . $this->timer->getRemainingSyncTime()); |
| 51 |
|
| 52 |
// Prepare sync data |
| 53 |
$this->prepareSyncData(); |
| 54 |
|
| 55 |
// Check preflight errors |
| 56 |
if (count($this->job->errors) > 0) { |
| 57 |
return; |
| 58 |
} |
| 59 |
|
| 60 |
// Now, do some work |
| 61 |
$lastrun_time = 2; |
| 62 |
|
| 63 |
while ($this->timer->shouldContinueWithLastrunTime($lastrun_time)) { |
| 64 |
$nomorework = true; |
| 65 |
foreach ($this->job->from_dbmasterdata as &$table) { |
| 66 |
if ($table->is_completed) { |
| 67 |
$table->rows = $table->completed_rows; |
| 68 |
} else { |
| 69 |
// Pre processing throttling stuff |
| 70 |
$this->handlePreProcessingThrottling($table); |
| 71 |
|
| 72 |
// Call proper service to get/send data depending on pull/push |
| 73 |
$lastrun_timer = $this->timer->startTimer('databasesync', 'while', 'lastrun'); |
| 74 |
|
| 75 |
if ($this->migration->type == 'pull') { |
| 76 |
$result_from_remote_service = $this->retrieveDataFromRemoteService($table); |
| 77 |
} elseif ($this->migration->type == 'push') { |
| 78 |
$result_from_remote_service = $this->sendDataToRemoteService($table); |
| 79 |
} else { |
| 80 |
$result_from_remote_service = 0; |
| 81 |
} |
| 82 |
|
| 83 |
$table->completed_rows += $result_from_remote_service; |
| 84 |
if ($table->completed_rows > $table->rows) { |
| 85 |
$table->rows = $table->completed_rows; |
| 86 |
} |
| 87 |
$nomorework = false; |
| 88 |
|
| 89 |
// Throttling |
| 90 |
$lastrun_time = $this->timer->getElapsedTimeToNow($lastrun_timer); |
| 91 |
$this->handlePostProcessingThrottling($lastrun_time); |
| 92 |
$this->logger->log('DEBUG', 'Lastrun in : ' . $lastrun_time . ' seconds - response size throttle: ' . $this->job->db_throttle_table_response_size . ' and remaining time: ' . $this->timer->getRemainingSyncTime()); |
| 93 |
// Break out to test if we have time for more |
| 94 |
break; |
| 95 |
} |
| 96 |
} |
| 97 |
|
| 98 |
// Recalculate completion and update state in job |
| 99 |
$this->updateCompletionStatusPercent(); |
| 100 |
|
| 101 |
// If no more work, mark as completed |
| 102 |
if ($nomorework) { |
| 103 |
$this->job->database_completed = true; |
| 104 |
break; |
| 105 |
} |
| 106 |
|
| 107 |
// If we found errors, break out |
| 108 |
if (count($this->job->errors) > 0) { |
| 109 |
break; |
| 110 |
} |
| 111 |
|
| 112 |
// Save status to DB |
| 113 |
$this->job->save(); |
| 114 |
} |
| 115 |
|
| 116 |
$this->logger->log('INFO', 'Ending database migration loop with remaining time: ' . $this->timer->getRemainingSyncTime() . ' seconds'); |
| 117 |
} |
| 118 |
|
| 119 |
/** |
| 120 |
* Prepare and fetch data for sync |
| 121 |
*/ |
| 122 |
private function prepareSyncData() |
| 123 |
{ |
| 124 |
// Determine max time per sync |
| 125 |
$this->max_time_per_sync = ceil($this->timer->getSyncMaxExecutionTime() / 5); |
| 126 |
if ($this->max_time_per_sync > 10) { |
| 127 |
$this->max_time_per_sync = 10; |
| 128 |
} |
| 129 |
|
| 130 |
// Check if first run |
| 131 |
if (!$this->job->db_first_run_setup) { |
| 132 |
$this->createTablesOnRemoteDatabase(); |
| 133 |
$this->job->db_first_run_setup = true; |
| 134 |
} |
| 135 |
} |
| 136 |
|
| 137 |
/** |
| 138 |
* Handle pre processing throttling of rows based on time per sync |
| 139 |
*/ |
| 140 |
private function handlePreProcessingThrottling($table) |
| 141 |
{ |
| 142 |
// If table is different than last time this ran |
| 143 |
if ($table->name != $this->job->db_throttle_table) { |
| 144 |
$this->job->db_throttle_table = $table->name; |
| 145 |
$this->job->db_throttle_table_response_size = $this->job->db_response_size_wanted_default; |
| 146 |
|
| 147 |
$this->logger->log('INFO', 'New table is started: ' . sanitize_text_field($table->name) . ' and setting new max response size: ' . $this->job->db_throttle_table_response_size); |
| 148 |
} |
| 149 |
} |
| 150 |
|
| 151 |
/** |
| 152 |
* Handle post processing throttling of rows based on time per sync |
| 153 |
* |
| 154 |
*/ |
| 155 |
private function handlePostProcessingThrottling($lastrun_time) |
| 156 |
{ |
| 157 |
// Check if we are too close to max memory (aka handling too large datasets and risking outofmemory) - One time thing per run |
| 158 |
$current_peak = \memory_get_peak_usage(); |
| 159 |
|
| 160 |
if (!$this->has_backed_off_because_of_memory && $current_peak > $this->job->masterdata_max_memory_limit_bytes) { |
| 161 |
// Back off a bit |
| 162 |
$this->has_backed_off_because_of_memory = true; |
| 163 |
$new_response_limit = floor($this->job->db_throttle_table_response_size * 0.70); |
| 164 |
$this->logger->log('WARNING', 'Hit memory peak - Current peak: ' . $current_peak . ' and memory limit: ' . $this->job->masterdata_max_memory_limit_bytes . ' - Backing off from: ' . $this->job->db_throttle_table_response_size . ' rows to: ' . $new_response_limit . ' rows'); |
| 165 |
$this->job->db_throttle_table_response_size = $new_response_limit; |
| 166 |
return; |
| 167 |
} |
| 168 |
|
| 169 |
// Check that last return response size in bytes does not exceed the max limit |
| 170 |
if ($this->job->db_last_response_length > 0 && $this->job->db_last_response_length > $this->job->db_response_size_wanted_max) { |
| 171 |
// Back off |
| 172 |
$this->job->db_throttle_table_response_size = intval($this->job->db_throttle_table_response_size * 0.80); |
| 173 |
return; |
| 174 |
} |
| 175 |
|
| 176 |
// Throttle rows per sync |
| 177 |
if ($lastrun_time < $this->max_time_per_sync) { |
| 178 |
// Scale up |
| 179 |
$this->job->db_throttle_table_response_size = ceil($this->job->db_throttle_table_response_size * 1.05); |
| 180 |
} else { |
| 181 |
// Back off |
| 182 |
$this->job->db_throttle_table_response_size = ceil($this->job->db_throttle_table_response_size * 0.90); |
| 183 |
} |
| 184 |
|
| 185 |
// Make sure the response size never gets above the max |
| 186 |
if ($this->job->db_throttle_table_response_size > $this->job->db_response_size_wanted_max) { |
| 187 |
$this->job->db_throttle_table_response_size = $this->job->db_response_size_wanted_max; |
| 188 |
} |
| 189 |
} |
| 190 |
|
| 191 |
/** |
| 192 |
* Send data to remote service (used for push) |
| 193 |
*/ |
| 194 |
private function sendDataToRemoteService(&$table) |
| 195 |
{ |
| 196 |
if ($this->migration == null) { |
| 197 |
return 0; |
| 198 |
} |
| 199 |
|
| 200 |
$calculated_inital_rows_per_request = floor($this->job->db_response_size_wanted_default / $table->row_avg_bytes); |
| 201 |
if ($calculated_inital_rows_per_request < 2) { |
| 202 |
$calculated_inital_rows_per_request = 2; |
| 203 |
} |
| 204 |
|
| 205 |
$timer = SyncTimerList::getInstance(); |
| 206 |
|
| 207 |
$database_helper_functions = new DatabaseHelperFunctions(); |
| 208 |
$data_result_from_db = $database_helper_functions->getDataFromDB( |
| 209 |
$table->name, |
| 210 |
$table->getColumnNames(), |
| 211 |
$table->primary_key_column, |
| 212 |
$table->last_primary_key, |
| 213 |
$table->completed_rows, |
| 214 |
$this->job->db_throttle_table_response_size, |
| 215 |
$calculated_inital_rows_per_request, |
| 216 |
$timer->getRemainingSyncTime() / 2 // only allow it some time, so there is time to process it also |
| 217 |
); |
| 218 |
|
| 219 |
$rows_fetched = count($data_result_from_db->data); |
| 220 |
|
| 221 |
// If there is no more rows, mark it as completed |
| 222 |
if (!$data_result_from_db->has_more_rows_in_table) { |
| 223 |
$this->logger->log('INFO', 'Marking table: ' . $table->name . ' as completed'); |
| 224 |
$table->is_completed = true; |
| 225 |
} |
| 226 |
|
| 227 |
// Generate SQL queries from data |
| 228 |
$sql_inserts = []; |
| 229 |
if ($rows_fetched > 0) { |
| 230 |
$sql_inserts = $this->generateSQLInserts($table, $data_result_from_db->data, $this->job->masterdata_max_sql_packet_bytes); |
| 231 |
} else { |
| 232 |
return 0; |
| 233 |
} |
| 234 |
|
| 235 |
// Create POST request to remote |
| 236 |
foreach ($sql_inserts as $sql_insert) { |
| 237 |
$body = new \stdClass(); |
| 238 |
$body->sql_inserts = $sql_insert; |
| 239 |
$body->type = $this->migration->type; |
| 240 |
$this->callRemoteClientDBService($body, 'to'); |
| 241 |
// Check for error |
| 242 |
if (count($this->job->errors) > 0) { |
| 243 |
return; |
| 244 |
} |
| 245 |
} |
| 246 |
|
| 247 |
return $rows_fetched; |
| 248 |
} |
| 249 |
|
| 250 |
/** |
| 251 |
* Call service for executing sql queries |
| 252 |
*/ |
| 253 |
public function callRemoteClientDBService(&$body, $to_or_from = 'to') |
| 254 |
{ |
| 255 |
// Start timer |
| 256 |
$this->timer = SyncTimerList::getInstance(); |
| 257 |
|
| 258 |
// Set destination |
| 259 |
if ($to_or_from == "to") { |
| 260 |
$destination = new Destination(Destination::TARGET); |
| 261 |
} else { |
| 262 |
$destination = new Destination(Destination::SOURCE); |
| 263 |
} |
| 264 |
|
| 265 |
$url = $destination->getFullURL() . '?action=wpsynchro_db_sync'; |
| 266 |
|
| 267 |
// Get remote transfer object |
| 268 |
$remotetransport = new RemoteTransport(); |
| 269 |
$remotetransport->setDestination($destination); |
| 270 |
$remotetransport->init(); |
| 271 |
$remotetransport->setUrl($url); |
| 272 |
$remotetransport->setDataObject($body); |
| 273 |
$database_result = $remotetransport->remotePOST(); |
| 274 |
|
| 275 |
if ($database_result->isSuccess()) { |
| 276 |
$result_body = $database_result->getBody(); |
| 277 |
$this->job->db_last_response_length = $database_result->getBodyLength(); |
| 278 |
$this->logger->log('DEBUG', "Got a proper response from 'clientsyncdatabase' with response length: " . $this->job->db_last_response_length); |
| 279 |
|
| 280 |
// Check for returning data |
| 281 |
if (isset($result_body->data)) { |
| 282 |
return $result_body; |
| 283 |
} |
| 284 |
} else { |
| 285 |
$this->job->errors[] = __('Database migration failed with error, which means we can not continue the migration.', 'wpsynchro'); |
| 286 |
} |
| 287 |
} |
| 288 |
|
| 289 |
/** |
| 290 |
* Retrieve data from remote service (used for pull) |
| 291 |
*/ |
| 292 |
private function retrieveDataFromRemoteService(&$table) |
| 293 |
{ |
| 294 |
global $wpdb; |
| 295 |
|
| 296 |
if ($this->migration == null) { |
| 297 |
return 0; |
| 298 |
} |
| 299 |
|
| 300 |
$calculated_inital_rows_per_request = floor($this->job->db_response_size_wanted_default / $table->row_avg_bytes); |
| 301 |
if ($calculated_inital_rows_per_request < 2) { |
| 302 |
$calculated_inital_rows_per_request = 2; |
| 303 |
} |
| 304 |
|
| 305 |
$timer = SyncTimerList::getInstance(); |
| 306 |
|
| 307 |
$body = new \stdClass(); |
| 308 |
$body->table = $table->name; |
| 309 |
$body->last_primary_key = $table->last_primary_key; |
| 310 |
$body->primary_key_column = $table->primary_key_column; |
| 311 |
$body->completed_rows = $table->completed_rows; |
| 312 |
$body->max_response_size = $this->job->db_throttle_table_response_size; |
| 313 |
$body->type = $this->migration->type; |
| 314 |
$body->default_rows_per_request = $calculated_inital_rows_per_request; |
| 315 |
$body->column_names = $table->getColumnNames(); |
| 316 |
$body->time_limit = $timer->getRemainingSyncTime() / 2; // only allow it some time, so there is time to process it also |
| 317 |
|
| 318 |
// Call remote service |
| 319 |
$this->logger->log('DEBUG', 'Getting data from remote DB with data: ' . json_encode($body)); |
| 320 |
$remote_result = $this->callRemoteClientDBService($body, 'from'); |
| 321 |
|
| 322 |
// Check for errors |
| 323 |
if (count($this->job->errors) > 0) { |
| 324 |
return 0; |
| 325 |
} |
| 326 |
|
| 327 |
if (is_array($remote_result->data)) { |
| 328 |
$rows_fetched = count($remote_result->data); |
| 329 |
} else { |
| 330 |
$rows_fetched = 0; |
| 331 |
} |
| 332 |
$this->logger->log('DEBUG', 'Got rows: ' . $rows_fetched); |
| 333 |
|
| 334 |
if (!$remote_result->has_more_rows_in_table) { |
| 335 |
$this->logger->log('INFO', 'Marking table: ' . $table->name . ' as completed'); |
| 336 |
$table->is_completed = true; |
| 337 |
} |
| 338 |
|
| 339 |
// Insert statements |
| 340 |
if ($rows_fetched > 0) { |
| 341 |
$sql_inserts = $this->generateSQLInserts($table, $remote_result->data, $this->job->masterdata_max_sql_packet_bytes); |
| 342 |
$wpdb->query('SET FOREIGN_KEY_CHECKS=0;'); |
| 343 |
foreach ($sql_inserts as $sql_insert) { |
| 344 |
$wpdb->query($sql_insert); |
| 345 |
if (strlen($wpdb->last_error) > 0) { |
| 346 |
$this->job->errors[] = $wpdb->last_error; |
| 347 |
$wpdb->last_error = ''; |
| 348 |
} |
| 349 |
$wpdb->flush(); |
| 350 |
} |
| 351 |
} |
| 352 |
|
| 353 |
$this->logger->log('DEBUG', 'Inserted ' . $rows_fetched . ' rows into target database'); |
| 354 |
|
| 355 |
return $rows_fetched; |
| 356 |
} |
| 357 |
|
| 358 |
/** |
| 359 |
* Generate sql inserts, queued together inside max_packet_allowed gathered from metadata and setup in preparesyncdata method |
| 360 |
*/ |
| 361 |
public function generateSQLInserts(&$table, &$rows, $max_packet_length) |
| 362 |
{ |
| 363 |
$insert_buffer = ''; |
| 364 |
$insert_buffer_length = 0; |
| 365 |
$insert_count = 0; |
| 366 |
$insert_count_max = 998; // Max 1000 inserts per statement, limit in mysql (minus a few such as foreign key check) |
| 367 |
$last_primary_key = 0; |
| 368 |
$inserts_array = []; |
| 369 |
|
| 370 |
$sql_insert_prefix = function ($temp_tablename, $col_and_val) { |
| 371 |
$cols = array_keys($col_and_val); |
| 372 |
|
| 373 |
$insert_buffer = 'INSERT INTO `' . $temp_tablename . '` (`' . implode('`,`', $cols) . '`) VALUES '; |
| 374 |
return $insert_buffer; |
| 375 |
}; |
| 376 |
|
| 377 |
foreach ($rows as $row) { |
| 378 |
// If beginning of new buffer |
| 379 |
$col_and_val = get_object_vars($row); |
| 380 |
|
| 381 |
// Check if we have a generated column, in that case, remove it |
| 382 |
foreach ($col_and_val as $col => $val) { |
| 383 |
if ($table->column_types->isGenerated($col)) { |
| 384 |
unset($col_and_val[$col]); |
| 385 |
} |
| 386 |
} |
| 387 |
|
| 388 |
if ($insert_buffer == '') { |
| 389 |
$insert_buffer = $sql_insert_prefix($table->temp_name, $col_and_val); |
| 390 |
$insert_buffer_length = strlen($insert_buffer); |
| 391 |
} |
| 392 |
|
| 393 |
$temp_insert_add = '('; |
| 394 |
$error_during_column_handling = false; |
| 395 |
foreach ($col_and_val as $col => $val) { |
| 396 |
if ($col == $table->primary_key_column) { |
| 397 |
$last_primary_key = $val; |
| 398 |
} |
| 399 |
|
| 400 |
// Handle NULL values |
| 401 |
if (is_null($val)) { |
| 402 |
$temp_insert_add .= 'NULL,'; |
| 403 |
} elseif ($table->column_types->isString($col)) { |
| 404 |
// Handle string values |
| 405 |
if ($col != 'guid') { |
| 406 |
$this->handleSearchReplace($val); |
| 407 |
} |
| 408 |
$temp_insert_add .= "'" . $this->escape($val) . "',"; |
| 409 |
} elseif ($table->column_types->isNumeric($col)) { |
| 410 |
// Handle numeric values |
| 411 |
if (strpos($val, 'e') > -1 || strpos($val, 'E') > -1) { |
| 412 |
$temp_insert_add .= "'" . $this->escape($val) . "',"; |
| 413 |
} else { |
| 414 |
$temp_insert_add .= $this->escape($val) . ','; |
| 415 |
} |
| 416 |
} elseif ($table->column_types->isBinary($col)) { |
| 417 |
// Handle binary values |
| 418 |
$available_memory = $this->job->masterdata_max_memory_limit_bytes; |
| 419 |
$val_length = strlen($val); |
| 420 |
$expected_length = $val_length * 2; |
| 421 |
if ($expected_length > $available_memory) { |
| 422 |
$warningsmsg = sprintf(__('Large row with binary column ignored from table: %s - Size of value: %d - Max size %d bytes - Increase memory limit on server', 'wpsynchro'), $table->name, $val_length, $available_memory); |
| 423 |
$this->logger->log('WARNING', $warningsmsg); |
| 424 |
$this->job->warnings[] = $warningsmsg; |
| 425 |
$error_during_column_handling = true; |
| 426 |
break; |
| 427 |
} else { |
| 428 |
if (strlen($val) > 0) { |
| 429 |
$temp_insert_add .= '0x' . bin2hex($val) . ','; |
| 430 |
} else { |
| 431 |
$temp_insert_add .= 'NULL,'; |
| 432 |
} |
| 433 |
} |
| 434 |
} elseif ($table->column_types->isBit($col)) { |
| 435 |
// Handle bit values |
| 436 |
$temp_insert_add .= "b'" . decbin($val) . "',"; |
| 437 |
} |
| 438 |
} |
| 439 |
|
| 440 |
if ($error_during_column_handling) { |
| 441 |
continue; |
| 442 |
} |
| 443 |
|
| 444 |
$temp_insert_add = trim($temp_insert_add, ', ') . '),'; |
| 445 |
$tmp_insert_add_length = strlen($temp_insert_add); |
| 446 |
|
| 447 |
if ($tmp_insert_add_length > $max_packet_length) { |
| 448 |
$warningsmsg = sprintf(__('Large row ignored from table: %s - Size: %d - This happens when a table row is larger than your system limits allows. These limits are a combination of max SQL packet size, memory limits and PHP max_post_size on both ends of the migration.', 'wpsynchro'), $table->name, $tmp_insert_add_length); |
| 449 |
$this->logger->log('WARNING', $warningsmsg); |
| 450 |
$this->job->warnings[] = $warningsmsg; |
| 451 |
continue; |
| 452 |
} |
| 453 |
|
| 454 |
if ((($insert_buffer_length + $tmp_insert_add_length) < $max_packet_length) && $insert_count < $insert_count_max) { |
| 455 |
$insert_buffer .= $temp_insert_add; |
| 456 |
$insert_buffer_length += $tmp_insert_add_length; |
| 457 |
$insert_count++; |
| 458 |
} else { |
| 459 |
// Save sql to array |
| 460 |
$insert_buffer = trim($insert_buffer, ', '); |
| 461 |
$inserts_array[] = $insert_buffer; |
| 462 |
// Start from beginning |
| 463 |
$insert_buffer = $sql_insert_prefix($table->temp_name, $col_and_val); |
| 464 |
$insert_buffer .= $temp_insert_add; |
| 465 |
$insert_buffer_length = strlen($insert_buffer); |
| 466 |
$insert_count = 1; |
| 467 |
} |
| 468 |
} |
| 469 |
if (strlen($insert_buffer) > 0 && $insert_count > 0) { |
| 470 |
$insert_buffer = trim($insert_buffer, ', '); |
| 471 |
$inserts_array[] = $insert_buffer; |
| 472 |
} |
| 473 |
|
| 474 |
$table->last_primary_key = $last_primary_key; |
| 475 |
|
| 476 |
return $inserts_array; |
| 477 |
} |
| 478 |
|
| 479 |
/** |
| 480 |
* Handle SQL escape |
| 481 |
*/ |
| 482 |
private function escape($data) |
| 483 |
{ |
| 484 |
global $wpdb; |
| 485 |
return \mysqli_real_escape_string($wpdb->__get('dbh'), $data); |
| 486 |
} |
| 487 |
|
| 488 |
/** |
| 489 |
* Handle in-data search/replace |
| 490 |
*/ |
| 491 |
public function handleSearchReplace(&$data) |
| 492 |
{ |
| 493 |
// Check data type |
| 494 |
if (is_serialized($data)) { |
| 495 |
try { |
| 496 |
$this->serialized_string_handler->searchReplaceSerialized($data, $this->job->db_search_replaces); |
| 497 |
} catch (SerializedStringException $ex) { |
| 498 |
$this->logger->log('ERROR', $ex->getMessage(), $ex->data); |
| 499 |
} |
| 500 |
} else { |
| 501 |
// Its just plain data, so simple fixy fixy |
| 502 |
foreach ($this->job->db_search_replaces as $replaces) { |
| 503 |
$data = str_replace($replaces->from, $replaces->to, $data); |
| 504 |
} |
| 505 |
} |
| 506 |
} |
| 507 |
|
| 508 |
/** |
| 509 |
* Create tables on remote (and filter out temp tables) |
| 510 |
*/ |
| 511 |
private function createTablesOnRemoteDatabase() |
| 512 |
{ |
| 513 |
global $wpdb; |
| 514 |
|
| 515 |
// the list of queries to setup tables |
| 516 |
$sql_queries = []; |
| 517 |
|
| 518 |
// Disable foreign key checks |
| 519 |
$sql_queries[] = 'SET FOREIGN_KEY_CHECKS = 0;'; |
| 520 |
|
| 521 |
// Create the temp tables (and drop them if already exists) |
| 522 |
foreach ($this->job->from_dbmasterdata as &$table) { |
| 523 |
if (!isset($table->temp_name) || strlen($table->temp_name) == 0) { |
| 524 |
$table->temp_name = self::TMP_TABLE_PREFIX . uniqid(); |
| 525 |
} |
| 526 |
|
| 527 |
$create_table = str_replace('`' . $table->name . '`', '`' . $table->temp_name . '`', $table->create_table); |
| 528 |
|
| 529 |
// Go through every table name, so see if table is referenced in create statement - Could be a innodb constraint or whatever |
| 530 |
foreach ($this->job->from_dbmasterdata as &$inside_table) { |
| 531 |
if ($inside_table->name == $table->name) { |
| 532 |
// Ignore if it is the same table |
| 533 |
continue; |
| 534 |
} |
| 535 |
|
| 536 |
// Check if the create statement contains the name of inside-table |
| 537 |
if (strpos($table->create_table, '`' . $inside_table->name . '`') > -1) { |
| 538 |
// If not yet given a temp name, set that first |
| 539 |
if (!isset($inside_table->temp_name) || strlen($inside_table->temp_name) == 0) { |
| 540 |
$inside_table->temp_name = self::TMP_TABLE_PREFIX . uniqid(); |
| 541 |
} |
| 542 |
// Replace in create statement, so inside tables new temp name is there instead |
| 543 |
$create_table = str_replace('`' . $inside_table->name . '`', '`' . $inside_table->temp_name . '`', $create_table); |
| 544 |
} |
| 545 |
} |
| 546 |
|
| 547 |
// Adapt create statement according to MySQL version, key naming etc |
| 548 |
$sql_queries[] = $this->adaptCreateStatement($create_table, $this->job->to_sql_version); |
| 549 |
} |
| 550 |
|
| 551 |
if ($this->migration->type == 'pull') { |
| 552 |
// Execute the sql queries |
| 553 |
foreach ($sql_queries as $sql_query) { |
| 554 |
$sql_result = $wpdb->query($sql_query); |
| 555 |
if ($sql_result === false) { |
| 556 |
$database_helper_functions = new DatabaseHelperFunctions(); |
| 557 |
$logs = $database_helper_functions->getLastDBQueryErrors(); |
| 558 |
foreach ($logs['log_errors'] as $log_error) { |
| 559 |
$this->logger->log('CRITICAL', $log_error); |
| 560 |
} |
| 561 |
foreach ($logs['user_errors'] as $user_error) { |
| 562 |
$this->job->errors[] = $user_error; |
| 563 |
} |
| 564 |
break; |
| 565 |
} |
| 566 |
} |
| 567 |
} elseif ($this->migration->type == 'push') { |
| 568 |
// if push, then always call remote service for sql create tables |
| 569 |
|
| 570 |
$body = new \stdClass(); |
| 571 |
$body->sql_inserts = $sql_queries; |
| 572 |
$body->type = $this->migration->type; |
| 573 |
|
| 574 |
$this->callRemoteClientDBService($body, 'to'); |
| 575 |
} |
| 576 |
} |
| 577 |
|
| 578 |
/** |
| 579 |
* Change create statements according to MySQL version, key name, constraint name etc |
| 580 |
*/ |
| 581 |
public function adaptCreateStatement($create, $to_db_version) |
| 582 |
{ |
| 583 |
// Change name to random in all constraints, if there, to prevent trouble with existing |
| 584 |
$create = preg_replace_callback("/CONSTRAINT\s`(\S+)`/", function () { |
| 585 |
return 'CONSTRAINT `' . uniqid() . '`'; |
| 586 |
}, $create); |
| 587 |
|
| 588 |
// Change index names to random, just to prevent overlaps |
| 589 |
$create = preg_replace_callback("/KEY\s`(\S+)`/", function () { |
| 590 |
return 'KEY `' . uniqid() . '`'; |
| 591 |
}, $create); |
| 592 |
|
| 593 |
// utf8mb4_0900_ai_ci collation is only from mysql 8 |
| 594 |
if (stripos($create, 'utf8mb4_0900_ai_ci') !== false) { |
| 595 |
// utf8mb4_0900_ai_ci is only for MySQL 8 |
| 596 |
if (version_compare($to_db_version, '8', '<')) { |
| 597 |
$create = str_replace("utf8mb4_0900_ai_ci", "utf8mb4_unicode_520_ci", $create); |
| 598 |
} |
| 599 |
} |
| 600 |
|
| 601 |
// Changes according to MySQL version |
| 602 |
if (version_compare($to_db_version, '5.5', '>=') && version_compare($to_db_version, '5.6', '<')) { // MySQL |
| 603 |
$create = str_replace("utf8mb4_unicode_520_ci", "utf8mb4_unicode_ci", $create); |
| 604 |
} elseif (version_compare($to_db_version, '10.1', '>=') && version_compare($to_db_version, '10.2', '<')) { // MariaDB |
| 605 |
$create = str_replace("utf8mb4_unicode_520_ci", "utf8mb4_unicode_ci", $create); |
| 606 |
} |
| 607 |
|
| 608 |
return $create; |
| 609 |
} |
| 610 |
|
| 611 |
/** |
| 612 |
* Calculate completion percent |
| 613 |
*/ |
| 614 |
private function updateCompletionStatusPercent() |
| 615 |
{ |
| 616 |
if (!isset($this->job->from_dbmasterdata)) { |
| 617 |
return; |
| 618 |
} |
| 619 |
|
| 620 |
$totalrows = 0; |
| 621 |
$completedrows = 0; |
| 622 |
$percent_completed = 0; |
| 623 |
// Data sizes |
| 624 |
$total_data_size = 0; |
| 625 |
|
| 626 |
foreach ($this->job->from_dbmasterdata as $table) { |
| 627 |
if (isset($table->rows)) { |
| 628 |
$temp_rows = $table->rows; |
| 629 |
} else { |
| 630 |
$temp_rows = 0; |
| 631 |
} |
| 632 |
if (isset($table->completed_rows)) { |
| 633 |
$temp_completedrows = $table->completed_rows; |
| 634 |
} else { |
| 635 |
$temp_completedrows = 0; |
| 636 |
} |
| 637 |
$totalrows += $temp_rows; |
| 638 |
$completedrows += $temp_completedrows; |
| 639 |
$total_data_size += $table->data_total_bytes; |
| 640 |
} |
| 641 |
|
| 642 |
if ($totalrows > 0) { |
| 643 |
$percent_completed = floor(($completedrows / $totalrows) * 100); |
| 644 |
} else { |
| 645 |
$percent_completed = 100; |
| 646 |
} |
| 647 |
// :) |
| 648 |
if ($percent_completed > 100) { |
| 649 |
$percent_completed = 100; |
| 650 |
} |
| 651 |
|
| 652 |
$this->job->database_progress = $percent_completed; |
| 653 |
|
| 654 |
// Update status description |
| 655 |
$current_number = $total_data_size * ($percent_completed / 100); |
| 656 |
$total_number = $total_data_size; |
| 657 |
$one_mb = 1012 * 1024; |
| 658 |
|
| 659 |
if ($total_number < $one_mb) { |
| 660 |
$total_number = number_format_i18n($total_number / 1024, 0) . 'kB'; |
| 661 |
$current_number = number_format_i18n($current_number / 1024, 0) . 'kB'; |
| 662 |
} else { |
| 663 |
$total_number = number_format_i18n($total_number / $one_mb, 1) . 'MB'; |
| 664 |
$current_number = number_format_i18n($current_number / $one_mb, 1) . 'MB'; |
| 665 |
} |
| 666 |
|
| 667 |
$completed_desc_rows = number_format_i18n($completedrows, 0); |
| 668 |
$total_desc_rows = number_format_i18n($totalrows, 0); |
| 669 |
|
| 670 |
if ($this->job->database_progress < 100) { |
| 671 |
$database_progress_description = sprintf(__('Data: %s / %s - Rows: %s / %s', 'wpsynchro'), $current_number, $total_number, $completed_desc_rows, $total_desc_rows); |
| 672 |
} else { |
| 673 |
$database_progress_description = ''; |
| 674 |
} |
| 675 |
|
| 676 |
$this->logger->log('INFO', 'Database progress update: ' . $database_progress_description); |
| 677 |
$this->job->database_progress_description = $database_progress_description; |
| 678 |
} |
| 679 |
} |
| 680 |
|
