← All changes
|
vendor/wp-php-toolkit/reprint-server/src/class-mysql-dump-producer.php
+1206
-0
16.2-beta
→
16.3-beta
View file →
| @@ -1,0 +1,1206 @@ | ||
| 1 | +<?php | |
| 2 | + | |
| 3 | +namespace WordPress\Reprint\Server; | |
| 4 | + | |
| 5 | +require_once __DIR__ . '/utils.php'; | |
| 6 | +require_once __DIR__ . "/class-database-rows-reader.php"; | |
| 7 | + | |
| 8 | +/** | |
| 9 | + * Generates a MySQL dump as a sequence of SQL fragments, one per call to next_sql_fragment(). | |
| 10 | + * | |
| 11 | + * This class exists because shared hosting environments kill long-running PHP processes. | |
| 12 | + * A traditional mysqldump would time out on large databases. Instead, this producer | |
| 13 | + * yields one SQL fragment at a time — a CREATE TABLE, a batched INSERT, or an UPDATE — | |
| 14 | + * and exposes a JSON cursor after each emitted fragment. The caller can serialize that | |
| 15 | + * cursor, end the HTTP request, and resume at the same SQL-fragment boundary in a later | |
| 16 | + * request. The cursor records emitted SQL progress instead of fetched-but-unemitted rows. | |
| 17 | + * | |
| 18 | + * The producer is a finite state machine that walks through tables sequentially: | |
| 19 | + * | |
| 20 | + * INIT → EMIT_HEADER → NEXT_TABLE → CREATE_TABLE → TABLE_HEADER → | |
| 21 | + * START_INSERT ⇄ EMIT_ROW → (EMIT_OVERSIZED_UPDATE) → … → EMIT_FOOTER → FINISHED | |
| 22 | + * | |
| 23 | + * All values are base64-encoded in the SQL output (via FROM_BASE64('...')). This avoids | |
| 24 | + * charset-related corruption: MySQL interprets string literals according to the | |
| 25 | + * connection charset, but base64 is pure ASCII and the decoded bytes are assigned | |
| 26 | + * directly to the column's declared charset. JSON columns are a special case — MySQL | |
| 27 | + * rejects binary charset input for JSON, so those get an extra CONVERT(... USING utf8mb4). | |
| 28 | + * | |
| 29 | + * For keyed rows, eligible large columns can be inserted as empty strings and then | |
| 30 | + * filled via UPDATE ... SET col = CONCAT(col, chunk) statements. | |
| 31 | + * | |
| 32 | + * Known limitations: | |
| 33 | + * | |
| 34 | + * - Rows too large to be SELECTed. If a row is larger than max_allowed_packet or the | |
| 35 | + * PHP memory_limit, it won't be exported. The underlying assumption is that WordPress | |
| 36 | + * wouldn't be able to use that data anyway. If that turns out to be wrong, and there | |
| 37 | + * are plugins that use huge blobs with byte offset queries, we'll need to add measures | |
| 38 | + * to detect those situations and export that data in chunks. | |
| 39 | + * - Tables without a primary key can't use the oversized row handling as there's no | |
| 40 | + * stable row identifier for the UPDATE ... SET col = CONCAT(col, chunk) WHERE ... query. | |
| 41 | + */ | |
| 42 | +class MySQLDumpProducer | |
| 43 | +{ | |
| 44 | + /** | |
| 45 | + * Maximum decoded SQL body bytes for one multipart part. | |
| 46 | + * | |
| 47 | + * The producer closes a multi-row INSERT at this limit, or splits or | |
| 48 | + * rejects one oversized row fragment. The exporter uses the same limit to | |
| 49 | + * stop grouping fragments before the complete multipart part becomes | |
| 50 | + * oversized, then checks its final byte length before writing it. | |
| 51 | + */ | |
| 52 | + public const MAX_SQL_PART_BODY_BYTES = 16 * 1024 * 1024; | |
| 53 | + | |
| 54 | + const STATE_INIT = "init"; | |
| 55 | + const STATE_EMIT_HEADER = "emit_header"; | |
| 56 | + const STATE_NEXT_TABLE = "next_table"; | |
| 57 | + const STATE_CREATE_TABLE = "create_table"; | |
| 58 | + const STATE_TABLE_HEADER = "table_header"; | |
| 59 | + const STATE_START_INSERT = "start_insert"; | |
| 60 | + const STATE_EMIT_ROW = "emit_row"; | |
| 61 | + const STATE_EMIT_OVERSIZED_UPDATE = "emit_oversized_update"; | |
| 62 | + const STATE_EMIT_FOOTER = "emit_footer"; | |
| 63 | + const STATE_FINISHED = "finished"; | |
| 64 | + | |
| 65 | + /** @var mixed PDO or a PDO-compatible adapter. */ | |
| 66 | + private $db; | |
| 67 | + | |
| 68 | + /** @var DatabaseRowsReader */ | |
| 69 | + private $row_reader; | |
| 70 | + | |
| 71 | + /** @var string|null */ | |
| 72 | + private $current_sql_fragment = null; | |
| 73 | + | |
| 74 | + /** @var bool */ | |
| 75 | + private $current_fragment_must_be_its_own_part = false; | |
| 76 | + | |
| 77 | + /** @var string */ | |
| 78 | + private $state = self::STATE_INIT; | |
| 79 | + | |
| 80 | + /** @var int */ | |
| 81 | + private $rows_in_batch = 0; | |
| 82 | + | |
| 83 | + /** @var bool */ | |
| 84 | + private $emit_create_table; | |
| 85 | + | |
| 86 | + /** | |
| 87 | + * Derived from MySQL's max_allowed_packet (at 80% to leave headroom for | |
| 88 | + * protocol framing). Rows whose formatted SQL exceeds this limit are split | |
| 89 | + * into an INSERT with empty placeholders followed by UPDATE ... CONCAT() | |
| 90 | + * statements that append the real data in chunks. | |
| 91 | + * | |
| 92 | + * @var int | |
| 93 | + */ | |
| 94 | + private $max_statement_size; | |
| 95 | + | |
| 96 | + /** | |
| 97 | + * When a row is too large for a single INSERT, its big columns are split | |
| 98 | + * into chunks and queued here. Each entry tracks the column name, its | |
| 99 | + * data type, the current byte offset into the value, and the total byte | |
| 100 | + * length. Character columns also track a character offset because MySQL's | |
| 101 | + * SUBSTRING() counts characters for those types. The actual data is | |
| 102 | + * re-fetched from the database on demand, keeping cursors small (a few | |
| 103 | + * hundred bytes rather than megabytes of raw data). | |
| 104 | + * | |
| 105 | + * @var array Array of {column: string, data_type: string, byte_offset: int, total_length: int, character_offset?: int} | |
| 106 | + */ | |
| 107 | + private $oversized_queue = []; | |
| 108 | + | |
| 109 | + /** @var array|null */ | |
| 110 | + private $oversized_pk_values = null; | |
| 111 | + | |
| 112 | + /** @var int */ | |
| 113 | + private $current_statement_size = 0; | |
| 114 | + | |
| 115 | + /** | |
| 116 | + * Reader cursor from before a fetched row which must begin the next INSERT. | |
| 117 | + * | |
| 118 | + * The live producer retains that row in memory. A serialized producer | |
| 119 | + * cursor uses this earlier reader position so a new process fetches the | |
| 120 | + * row again instead of skipping it. | |
| 121 | + * | |
| 122 | + * @var array|null | |
| 123 | + */ | |
| 124 | + private $reader_cursor_before_retained_record = null; | |
| 125 | + | |
| 126 | + /** | |
| 127 | + * @param object $db Database connection — either a real PDO (MySQL) or a | |
| 128 | + * PDO-compatible adapter (SQLite sites). No type hint because the | |
| 129 | + * adapter isn't a PDO subclass and PHP 7.4 lacks union types. | |
| 130 | + */ | |
| 131 | + public function __construct($db, $options = []) | |
| 132 | + { | |
| 133 | + $this->db = $db; | |
| 134 | + $this->row_reader = new DatabaseRowsReader($db, $options); | |
| 135 | + $this->emit_create_table = (bool)($options["create_table_query"] ?? true); | |
| 136 | + | |
| 137 | + if (isset($options["max_statement_size"])) { | |
| 138 | + $this->max_statement_size = (int)$options["max_statement_size"]; | |
| 139 | + } else { | |
| 140 | + $this->max_statement_size = $this->detect_max_statement_size(); | |
| 141 | + } | |
| 142 | + | |
| 143 | + if (isset($options["cursor"])) { | |
| 144 | + $this->initialize_from_cursor($options["cursor"]); | |
| 145 | + } | |
| 146 | + } | |
| 147 | + | |
| 148 | + public function get_sql_fragment(): ?string | |
| 149 | + { | |
| 150 | + return $this->current_sql_fragment; | |
| 151 | + } | |
| 152 | + | |
| 153 | + public function is_finished(): bool | |
| 154 | + { | |
| 155 | + return self::STATE_FINISHED === $this->state; | |
| 156 | + } | |
| 157 | + | |
| 158 | + /** Returns whether this fragment must be sent in its own multipart part. */ | |
| 159 | + public function current_fragment_must_be_its_own_part(): bool | |
| 160 | + { | |
| 161 | + return $this->current_fragment_must_be_its_own_part; | |
| 162 | + } | |
| 163 | + | |
| 164 | + /** | |
| 165 | + * Advances the state machine and populates the next SQL fragment. | |
| 166 | + * | |
| 167 | + * Call get_sql_fragment() after this returns true to retrieve the SQL. | |
| 168 | + * Returns false only when the dump is complete (state = FINISHED). | |
| 169 | + */ | |
| 170 | + public function next_sql_fragment() | |
| 171 | + { | |
| 172 | + if ($this->is_finished()) { | |
| 173 | + return false; | |
| 174 | + } | |
| 175 | + | |
| 176 | + $this->current_fragment_must_be_its_own_part = false; | |
| 177 | + | |
| 178 | + if (self::STATE_INIT === $this->state) { | |
| 179 | + if (!$this->row_reader->has_initialized_tables()) { | |
| 180 | + $this->row_reader->initialize_tables_to_process(); | |
| 181 | + } | |
| 182 | + $this->state = self::STATE_EMIT_HEADER; | |
| 183 | + } | |
| 184 | + | |
| 185 | + while (true) { | |
| 186 | + switch ($this->state) { | |
| 187 | + case self::STATE_EMIT_HEADER: | |
| 188 | + $this->emit_sql_header(); | |
| 189 | + $this->state = self::STATE_NEXT_TABLE; | |
| 190 | + $this->current_fragment_must_be_its_own_part = true; | |
| 191 | + return true; | |
| 192 | + | |
| 193 | + case self::STATE_NEXT_TABLE: | |
| 194 | + if ($this->move_to_next_table()) { | |
| 195 | + $this->state = $this->emit_create_table | |
| 196 | + ? self::STATE_CREATE_TABLE | |
| 197 | + : self::STATE_TABLE_HEADER; | |
| 198 | + } else { | |
| 199 | + $this->state = self::STATE_EMIT_FOOTER; | |
| 200 | + } | |
| 201 | + break; | |
| 202 | + | |
| 203 | + case self::STATE_EMIT_FOOTER: | |
| 204 | + $this->emit_sql_footer(); | |
| 205 | + $this->state = self::STATE_FINISHED; | |
| 206 | + $this->current_fragment_must_be_its_own_part = true; | |
| 207 | + return true; | |
| 208 | + | |
| 209 | + case self::STATE_CREATE_TABLE: | |
| 210 | + $this->emit_create_table_statement(); | |
| 211 | + $this->state = self::STATE_TABLE_HEADER; | |
| 212 | + $this->current_fragment_must_be_its_own_part = true; | |
| 213 | + return true; | |
| 214 | + | |
| 215 | + case self::STATE_TABLE_HEADER: | |
| 216 | + $this->emit_table_header_comment(); | |
| 217 | + $this->state = self::STATE_START_INSERT; | |
| 218 | + return true; | |
| 219 | + | |
| 220 | + case self::STATE_START_INSERT: | |
| 221 | + if ($this->emit_insert_header()) { | |
| 222 | + return true; | |
| 223 | + } | |
| 224 | + // Empty table — emit_insert_header set state to NEXT_TABLE | |
| 225 | + break; | |
| 226 | + | |
| 227 | + case self::STATE_EMIT_ROW: | |
| 228 | + return $this->emit_row(); | |
| 229 | + | |
| 230 | + case self::STATE_EMIT_OVERSIZED_UPDATE: | |
| 231 | + if ($this->emit_oversized_update()) { | |
| 232 | + $this->current_fragment_must_be_its_own_part = true; | |
| 233 | + return true; | |
| 234 | + } | |
| 235 | + break; | |
| 236 | + | |
| 237 | + case self::STATE_FINISHED: | |
| 238 | + return false; | |
| 239 | + } | |
| 240 | + } | |
| 241 | + | |
| 242 | + return false; | |
| 243 | + } | |
| 244 | + /** | |
| 245 | + * Emits "INSERT INTO ... VALUES (first_row)" as a single fragment. | |
| 246 | + * | |
| 247 | + * The first row is always bundled with the INSERT header to prevent | |
| 248 | + * emitting a dangling "INSERT INTO ... VALUES" with no rows — which | |
| 249 | + * would happen if the caller saves the cursor right after the header | |
| 250 | + * and the data changes before the next request. | |
| 251 | + */ | |
| 252 | + private function emit_insert_header() | |
| 253 | + { | |
| 254 | + $this->rows_in_batch = 0; | |
| 255 | + if ($this->row_reader->get_current_record() === null) { | |
| 256 | + if (!$this->row_reader->next_record()) { | |
| 257 | + $this->state = self::STATE_NEXT_TABLE; | |
| 258 | + return false; | |
| 259 | + } | |
| 260 | + } | |
| 261 | + | |
| 262 | + $column_list = implode( | |
| 263 | + ",", | |
| 264 | + array_map(function ($col) { | |
| 265 | + return $this->row_reader->quote_identifier($col); | |
| 266 | + }, $this->row_reader->get_current_column_names()) | |
| 267 | + ); | |
| 268 | + | |
| 269 | + $header = "INSERT INTO " . $this->row_reader->quote_identifier($this->row_reader->get_current_table()) . " ({$column_list}) VALUES\n"; | |
| 270 | + $this->current_statement_size = strlen($header) + strlen($this->on_duplicate_key()) + 1; | |
| 271 | + | |
| 272 | + $current_record_ends_query_batch = $this->row_reader->is_current_record_at_query_batch_boundary(); | |
| 273 | + $first_row_sql = $this->format_row_for_insert( | |
| 274 | + $this->row_reader->get_current_record(), | |
| 275 | + $this->current_statement_size | |
| 276 | + ); | |
| 277 | + $this->current_statement_size += strlen($first_row_sql); | |
| 278 | + | |
| 279 | + $this->row_reader->clear_current_record(); | |
| 280 | + $this->reader_cursor_before_retained_record = null; | |
| 281 | + $this->rows_in_batch = 1; | |
| 282 | + | |
| 283 | + // Oversized updates require closing this INSERT with a semicolon so the | |
| 284 | + // subsequent UPDATE statements are syntactically separate. | |
| 285 | + $has_oversized = $this->has_pending_oversized_updates(); | |
| 286 | + | |
| 287 | + if ( | |
| 288 | + $current_record_ends_query_batch || | |
| 289 | + $this->rows_in_batch >= $this->row_reader->get_batch_size() | |
| 290 | + ) { | |
| 291 | + $this->finish_insert_batch($header . $first_row_sql, $has_oversized); | |
| 292 | + return true; | |
| 293 | + } | |
| 294 | + | |
| 295 | + if ($has_oversized) { | |
| 296 | + $sql = $header . $first_row_sql . $this->on_duplicate_key() . ';'; | |
| 297 | + $this->current_sql_fragment = $sql; | |
| 298 | + $this->current_statement_size = 0; | |
| 299 | + $this->state = self::STATE_EMIT_OVERSIZED_UPDATE; | |
| 300 | + } else { | |
| 301 | + $sql = $header . $first_row_sql; | |
| 302 | + $this->current_sql_fragment = $sql; | |
| 303 | + $this->state = self::STATE_EMIT_ROW; | |
| 304 | + } | |
| 305 | + | |
| 306 | + return true; | |
| 307 | + } | |
| 308 | + | |
| 309 | + /** Emits one row with a leading comma, or closes the open INSERT when no row remains. */ | |
| 310 | + private function emit_row() | |
| 311 | + { | |
| 312 | + $reader_cursor_before_current_record = $this->row_reader->get_cursor_state(); | |
| 313 | + if (!$this->row_reader->next_record()) { | |
| 314 | + $this->current_sql_fragment = $this->on_duplicate_key() . ';'; | |
| 315 | + $this->current_statement_size = 0; | |
| 316 | + $this->state = self::STATE_NEXT_TABLE; | |
| 317 | + return true; | |
| 318 | + } | |
| 319 | + | |
| 320 | + $row_tuple_bytes = $this->estimate_formatted_row_tuple_bytes( | |
| 321 | + $this->row_reader->get_current_record() | |
| 322 | + ); | |
| 323 | + $maximum_insert_statement_bytes = min( | |
| 324 | + $this->max_statement_size, | |
| 325 | + self::MAX_SQL_PART_BODY_BYTES | |
| 326 | + ); | |
| 327 | + if ( | |
| 328 | + $this->current_statement_size + 1 + $row_tuple_bytes > | |
| 329 | + $maximum_insert_statement_bytes | |
| 330 | + ) { | |
| 331 | + // This row fits as the first row of another INSERT, but not in the | |
| 332 | + // current one. Keep it in memory for the live producer. A resumed | |
| 333 | + // producer uses the earlier cursor and fetches it again. | |
| 334 | + $this->reader_cursor_before_retained_record = $reader_cursor_before_current_record; | |
| 335 | + $this->current_sql_fragment = $this->on_duplicate_key() . ';'; | |
| 336 | + $this->current_statement_size = 0; | |
| 337 | + $this->rows_in_batch = 0; | |
| 338 | + $this->state = self::STATE_START_INSERT; | |
| 339 | + return true; | |
| 340 | + } | |
| 341 | + | |
| 342 | + $current_record_ends_query_batch = $this->row_reader->is_current_record_at_query_batch_boundary(); | |
| 343 | + $row_sql = $this->format_row_for_insert( | |
| 344 | + $this->row_reader->get_current_record(), | |
| 345 | + strlen($this->on_duplicate_key()) + 1 | |
| 346 | + ); | |
| 347 | + $this->current_statement_size += strlen($row_sql) + 1; | |
| 348 | + $this->row_reader->clear_current_record(); | |
| 349 | + $this->rows_in_batch++; | |
| 350 | + | |
| 351 | + $has_oversized = $this->has_pending_oversized_updates(); | |
| 352 | + | |
| 353 | + if ( | |
| 354 | + $current_record_ends_query_batch || | |
| 355 | + $this->rows_in_batch >= $this->row_reader->get_batch_size() | |
| 356 | + ) { | |
| 357 | + $this->finish_insert_batch("," . $row_sql, $has_oversized); | |
| 358 | + return true; | |
| 359 | + } | |
| 360 | + | |
| 361 | + if ($has_oversized) { | |
| 362 | + $this->current_sql_fragment = "," . $row_sql . $this->on_duplicate_key() . ';'; | |
| 363 | + $this->current_statement_size = 0; | |
| 364 | + $this->state = self::STATE_EMIT_OVERSIZED_UPDATE; | |
| 365 | + } else { | |
| 366 | + $this->current_sql_fragment = "," . $row_sql; | |
| 367 | + } | |
| 368 | + | |
| 369 | + return true; | |
| 370 | + } | |
| 371 | + | |
| 372 | + /** Finishes an INSERT batch at its bounded row limit. */ | |
| 373 | + private function finish_insert_batch($sql, $has_oversized) | |
| 374 | + { | |
| 375 | + $this->current_sql_fragment = $sql . $this->on_duplicate_key() . ';'; | |
| 376 | + $this->current_statement_size = 0; | |
| 377 | + if ($has_oversized) { | |
| 378 | + $this->state = self::STATE_EMIT_OVERSIZED_UPDATE; | |
| 379 | + } else { | |
| 380 | + $this->state = self::STATE_START_INSERT; | |
| 381 | + } | |
| 382 | + } | |
| 383 | + | |
| 384 | + /** | |
| 385 | + * Returns a no-op update for rows already written by a stopped INSERT. | |
| 386 | + * | |
| 387 | + * MyISAM can keep a complete prefix of a multi-row INSERT when the query | |
| 388 | + * stops. Repeating that INSERT should skip the existing rows and write the | |
| 389 | + * missing rows. Assigning any inserted column to itself handles a simple | |
| 390 | + * or composite key without hiding invalid values behind INSERT IGNORE. | |
| 391 | + * MySQL can also detect an enforced UNIQUE key whose columns are NOT NULL. | |
| 392 | + * A nullable UNIQUE key cannot identify an existing row because MySQL | |
| 393 | + * permits more than one row whose unique-key value contains NULL. | |
| 394 | + */ | |
| 395 | + private function on_duplicate_key() | |
| 396 | + { | |
| 397 | + $first_column = $this->row_reader->get_current_column_names()[0]; | |
| 398 | + $quoted_column = $this->row_reader->quote_identifier($first_column); | |
| 399 | + return "\nON DUPLICATE KEY UPDATE {$quoted_column} = {$quoted_column}"; | |
| 400 | + } | |
| 401 | + | |
| 402 | + /** | |
| 403 | + * Emits DROP TABLE IF EXISTS followed by the CREATE TABLE from SHOW CREATE TABLE. | |
| 404 | + * Also handles views (SHOW CREATE TABLE returns 'Create View' for those). | |
| 405 | + */ | |
| 406 | + private function emit_create_table_statement() | |
| 407 | + { | |
| 408 | + $quoted_table = $this->row_reader->quote_identifier($this->row_reader->get_current_table()); | |
| 409 | + try { | |
| 410 | + $query = "SHOW CREATE TABLE {$quoted_table}"; | |
| 411 | + $result = $this->db->query($query); | |
| 412 | + $row = $result->fetch(PdoConstants::fetch_assoc()); | |
| 413 | + } catch (\Exception $e) { | |
| 414 | + throw new \RuntimeException( | |
| 415 | + "Failed to get CREATE TABLE for {$quoted_table}: " . $e->getMessage() . " Query: {$query}" | |
| 416 | + ); | |
| 417 | + } | |
| 418 | + | |
| 419 | + $sql = null; | |
| 420 | + if ($row) { | |
| 421 | + if (isset($row["Create Table"])) { | |
| 422 | + $sql = $row["Create Table"]; | |
| 423 | + } elseif (isset($row["Create View"])) { | |
| 424 | + $sql = $row["Create View"]; | |
| 425 | + } | |
| 426 | + } | |
| 427 | + | |
| 428 | + if ($sql) { | |
| 429 | + // Prevent breaking the line by identifiers with a newline byte in them. | |
| 430 | + $header = "--\n-- Table structure for table ".str_replace("\n",'\n',$quoted_table)."\n--\n\n"; | |
| 431 | + $drop = "DROP TABLE IF EXISTS {$quoted_table};\n"; | |
| 432 | + $this->current_sql_fragment = $header . $drop . $sql . ";"; | |
| 433 | + } else { | |
| 434 | + $keys = $row ? implode(", ", array_keys($row)) : "(no row returned)"; | |
| 435 | + throw new \RuntimeException( | |
| 436 | + "SHOW CREATE TABLE {$quoted_table} returned no usable SQL. " . | |
| 437 | + "Available keys: {$keys}" | |
| 438 | + ); | |
| 439 | + } | |
| 440 | + } | |
| 441 | + | |
| 442 | + /** | |
| 443 | + * Emits SET statements that configure constraint checks and set a strict SQL mode. | |
| 444 | + * These are restored in emit_sql_footer(). Without disabling FK checks, tables | |
| 445 | + * that reference each other would need to be imported in dependency order. | |
| 446 | + * Unique checks stay enabled because replayed INSERT statements use unique | |
| 447 | + * keys to recognize rows which are already present. | |
| 448 | + * | |
| 449 | + * The SQL_MODE explicitly omits NO_ZERO_DATE, NO_ZERO_IN_DATE, and NO_ENGINE_SUBSTITUTION. | |
| 450 | + * | |
| 451 | + * For dates, many WordPress databases contain zero dates like '0000-00-00' | |
| 452 | + * or '0000-00-00 00:00:00' (e.g. in wp_posts.post_date for drafts). The | |
| 453 | + * source server may have been running without those restrictions, and the | |
| 454 | + * dump must be importable regardless of the target server's default sql_mode. | |
| 455 | + * | |
| 456 | + * From the MySQL 8.0 Reference Manual (§5.1.11 "Server SQL Modes"): | |
| 457 | + * | |
| 458 | + * NO_ZERO_DATE — [...] The server requires dates to have nonzero month | |
| 459 | + * and day values. If NO_ZERO_DATE is enabled and strict mode is enabled, | |
| 460 | + * '0000-00-00' is not permitted and inserts produce an error. [...] | |
| 461 | + * If NO_ZERO_DATE is disabled, '0000-00-00' is permitted and inserts | |
| 462 | + * produce no warning. | |
| 463 | + * | |
| 464 | + * NO_ZERO_IN_DATE — [...] Affects whether the server permits dates in | |
| 465 | + * which the year part is nonzero but the month or day part is 0. | |
| 466 | + * [...] If this mode is disabled, dates with zero parts are permitted | |
| 467 | + * and inserts produce no warning. | |
| 468 | + * | |
| 469 | + * By omitting both flags while keeping STRICT_TRANS_TABLES, the dump | |
| 470 | + * preserves MySQL's permissive behavior toward zero dates during import. | |
| 471 | + * | |
| 472 | + * By omitting NO_ENGINE_SUBSTITUTION, we allow imports to succeed under stricter requirements. | |
| 473 | + * Example: A MyISAM source table carries ENGINE=MyISAM into the dump, and the target | |
| 474 | + * database uses enforce_storage_engine to InnoDB. With NO_ENGINE_SUBSTITUTION, the | |
| 475 | + * import would fail because the target engine is not MyISAM and cannot be substituted. | |
| 476 | + * | |
| 477 | + * @see https://dev.mysql.com/doc/refman/8.0/en/sql-mode.html#sqlmode_no_zero_date | |
| 478 | + * @see https://dev.mysql.com/doc/refman/8.0/en/sql-mode.html#sqlmode_no_zero_in_date | |
| 479 | + * @see https://mariadb.com/docs/reference/mdb/system-variables/enforce_storage_engine/ | |
| 480 | + */ | |
| 481 | + private function emit_sql_header() | |
| 482 | + { | |
| 483 | + $this->current_sql_fragment = self::get_session_setup_sql(); | |
| 484 | + } | |
| 485 | + | |
| 486 | + /** Returns the connection settings required before executing dump SQL. */ | |
| 487 | + public static function get_session_setup_sql() | |
| 488 | + { | |
| 489 | + return "SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=1;\n" . | |
| 490 | + "SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;\n" . | |
| 491 | + // @TODO: Restore STRICT_TRANS_TABLES | |
| 492 | + "SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='ONLY_FULL_GROUP_BY,ERROR_FOR_DIVISION_BY_ZERO';\n" . | |
| 493 | + "SET AUTOCOMMIT=0;\n"; | |
| 494 | + } | |
| 495 | + | |
| 496 | + /** Emits COMMIT and restores the session variables saved in the header. */ | |
| 497 | + private function emit_sql_footer() | |
| 498 | + { | |
| 499 | + $footer = | |
| 500 | + "\nCOMMIT;\n" . | |
| 501 | + "SET SQL_MODE=@OLD_SQL_MODE;\n" . | |
| 502 | + "SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;\n" . | |
| 503 | + "SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;\n"; | |
| 504 | + $this->current_sql_fragment = $footer; | |
| 505 | + } | |
| 506 | + | |
| 507 | + /** Emits a SQL comment marking the start of data for the current table. */ | |
| 508 | + private function emit_table_header_comment() | |
| 509 | + { | |
| 510 | + $comment = "\n--\n-- Dumping data for table " . str_replace("\n",'\n',$this->row_reader->quote_identifier($this->row_reader->get_current_table())) . "\n--\n"; | |
| 511 | + $this->current_sql_fragment = $comment; | |
| 512 | + } | |
| 513 | + | |
| 514 | + /** Advances to the next table and resets all per-table state. */ | |
| 515 | + private function move_to_next_table() | |
| 516 | + { | |
| 517 | + $has_table = $this->row_reader->move_to_next_table(); | |
| 518 | + if ($has_table) { | |
| 519 | + $this->rows_in_batch = 0; | |
| 520 | + $this->oversized_queue = []; | |
| 521 | + $this->oversized_pk_values = null; | |
| 522 | + $this->current_statement_size = 0; | |
| 523 | + $this->reader_cursor_before_retained_record = null; | |
| 524 | + } | |
| 525 | + return $has_table; | |
| 526 | + } | |
| 527 | + | |
| 528 | + /** | |
| 529 | + * Returns the producer cursor as a JSON string. | |
| 530 | + * | |
| 531 | + * The caller can pass this string back as the "cursor" option to a new | |
| 532 | + * MySQLDumpProducer to resume at the current SQL-fragment boundary. The | |
| 533 | + * JSON is NOT base64-encoded — that's the HTTP layer's concern (export.php). | |
| 534 | + * | |
| 535 | + * String values in primary key checkpoints are wrapped in | |
| 536 | + * {"__binary__": "<base64>"} markers because raw database bytes can't | |
| 537 | + * survive JSON encoding. Complete database rows are omitted. During an | |
| 538 | + * open INSERT, a fixed-size hash represents the ordered column names, which | |
| 539 | + * are reloaded from table metadata on resume. | |
| 540 | + */ | |
| 541 | + public function get_reentrancy_cursor() | |
| 542 | + { | |
| 543 | + $cursor_data = $this->reader_cursor_before_retained_record ?? | |
| 544 | + $this->row_reader->get_cursor_state(); | |
| 545 | + unset( | |
| 546 | + $cursor_data["current_row"], | |
| 547 | + $cursor_data["current_row_ends_query_batch"], | |
| 548 | + $cursor_data["current_column_names"] | |
| 549 | + ); | |
| 550 | + $current_column_names_hash = $this->get_current_column_names_hash(); | |
| 551 | + if ($current_column_names_hash !== null) { | |
| 552 | + $cursor_data["current_column_names_hash"] = $current_column_names_hash; | |
| 553 | + } | |
| 554 | + $cursor_data["state"] = $this->state; | |
| 555 | + $cursor_data["rows_in_batch"] = $this->rows_in_batch; | |
| 556 | + /** | |
| 557 | + * Tracking for rows that are larger than max_allowed_packet or | |
| 558 | + * max_statement_size. | |
| 559 | + */ | |
| 560 | + $cursor_data["oversized_queue"] = $this->encode_oversized_queue_for_cursor($this->oversized_queue); | |
| 561 | + $cursor_data["current_statement_size"] = $this->current_statement_size; | |
| 562 | + | |
| 563 | + $json = json_encode($cursor_data); | |
| 564 | + if ($json === false) { | |
| 565 | + throw new \RuntimeException( | |
| 566 | + "Failed to encode reentrancy cursor: " . json_last_error_msg() | |
| 567 | + ); | |
| 568 | + } | |
| 569 | + return $json; | |
| 570 | + } | |
| 571 | + | |
| 572 | + /** Base64-encodes all chunk payloads in the oversized queue for JSON safety. */ | |
| 573 | + /** | |
| 574 | + * The oversized queue entries are already cursor-safe (just column names, | |
| 575 | + * data types, and integer offsets), so encoding is a no-op. | |
| 576 | + */ | |
| 577 | + private function encode_oversized_queue_for_cursor($queue) | |
| 578 | + { | |
| 579 | + return $queue; | |
| 580 | + } | |
| 581 | + | |
| 582 | + /** Reverses encode_oversized_queue_for_cursor(). */ | |
| 583 | + private function decode_oversized_queue_from_cursor($queue) | |
| 584 | + { | |
| 585 | + if (!is_array($queue)) { | |
| 586 | + return []; | |
| 587 | + } | |
| 588 | + $decoded = []; | |
| 589 | + foreach ($queue as $item) { | |
| 590 | + if ( | |
| 591 | + !is_array($item) || | |
| 592 | + !isset($item['column'], $item['data_type'], $item['byte_offset'], $item['total_length']) | |
| 593 | + ) { | |
| 594 | + throw new \InvalidArgumentException( | |
| 595 | + "Invalid cursor: oversized_queue item must contain " . | |
| 596 | + "'column', 'data_type', 'byte_offset', and 'total_length' keys" | |
| 597 | + ); | |
| 598 | + } | |
| 599 | + $decoded_item = [ | |
| 600 | + 'column' => $item['column'], | |
| 601 | + 'data_type' => $item['data_type'], | |
| 602 | + 'byte_offset' => (int) $item['byte_offset'], | |
| 603 | + 'total_length' => (int) $item['total_length'], | |
| 604 | + ]; | |
| 605 | + if ($this->row_reader->is_character_string_type($item['data_type'])) { | |
| 606 | + if (!array_key_exists('character_offset', $item)) { | |
| 607 | + if ((int) $item['byte_offset'] !== 0) { | |
| 608 | + throw new \InvalidArgumentException( | |
| 609 | + "The saved database pull cursor uses an earlier oversized text format. " . | |
| 610 | + "Run db-pull --abort and start again." | |
| 611 | + ); | |
| 612 | + } | |
| 613 | + $decoded_item['character_offset'] = 0; | |
| 614 | + } else { | |
| 615 | + $decoded_item['character_offset'] = (int) $item['character_offset']; | |
| 616 | + } | |
| 617 | + } | |
| 618 | + $decoded[] = $decoded_item; | |
| 619 | + } | |
| 620 | + return $decoded; | |
| 621 | + } | |
| 622 | + | |
| 623 | + /** | |
| 624 | + * Restores internal state from a previously-serialized cursor. | |
| 625 | + * | |
| 626 | + * The row reader reloads column types and ordered names from table metadata. | |
| 627 | + * A missing current table resets the producer to STATE_INIT. An active-table | |
| 628 | + * cursor must contain the ordered-column hash saved at its fragment boundary. | |
| 629 | + */ | |
| 630 | + private function initialize_from_cursor($cursor) | |
| 631 | + { | |
| 632 | + $cursor_data = json_decode($cursor, true); | |
| 633 | + if ($cursor_data === null && json_last_error() !== JSON_ERROR_NONE) { | |
| 634 | + throw new \InvalidArgumentException( | |
| 635 | + 'Invalid cursor format: cursor must be valid JSON. ' . | |
| 636 | + 'JSON error: ' . json_last_error_msg() . '. ' . | |
| 637 | + 'Received: ' . substr($cursor, 0, 100) | |
| 638 | + ); | |
| 639 | + } | |
| 640 | + if (is_array($cursor_data)) { | |
| 641 | + if (array_key_exists("current_row", $cursor_data)) { | |
| 642 | + throw new \InvalidArgumentException( | |
| 643 | + "The saved database pull cursor uses an earlier format. " . | |
| 644 | + "Run db-pull --abort and start again." | |
| 645 | + ); | |
| 646 | + } | |
| 647 | + | |
| 648 | + $this->state = $cursor_data["state"] ?? self::STATE_INIT; | |
| 649 | + $this->rows_in_batch = $cursor_data["rows_in_batch"] ?? 0; | |
| 650 | + if (!is_int($this->rows_in_batch) && !is_float($this->rows_in_batch)) { | |
| 651 | + throw new \InvalidArgumentException( | |
| 652 | + "Invalid cursor: rows_in_batch must be numeric, got " . gettype($this->rows_in_batch) | |
| 653 | + ); | |
| 654 | + } | |
| 655 | + $this->rows_in_batch = (int) $this->rows_in_batch; | |
| 656 | + | |
| 657 | + $encoded_queue = $cursor_data["oversized_queue"] ?? []; | |
| 658 | + $this->oversized_queue = $this->decode_oversized_queue_from_cursor($encoded_queue); | |
| 659 | + $this->oversized_pk_values = null; | |
| 660 | + if ($this->state === self::STATE_EMIT_OVERSIZED_UPDATE) { | |
| 661 | + // The last emitted primary key identifies the row whose | |
| 662 | + // oversized columns are still being appended. | |
| 663 | + $this->oversized_pk_values = $this->row_reader->decode_database_values_from_cursor( | |
| 664 | + $cursor_data["last_pk_values"] ?? null | |
| 665 | + ); | |
| 666 | + } | |
| 667 | + $this->current_statement_size = $cursor_data["current_statement_size"] ?? 0; | |
| 668 | + | |
| 669 | + if (!$this->row_reader->restore_cursor_state($cursor_data)) { | |
| 670 | + $this->state = self::STATE_INIT; | |
| 671 | + } else { | |
| 672 | + $expected_column_names_hash = $cursor_data["current_column_names_hash"] ?? null; | |
| 673 | + $actual_column_names_hash = $this->get_current_column_names_hash(); | |
| 674 | + if ($actual_column_names_hash !== null) { | |
| 675 | + if ($expected_column_names_hash === null) { | |
| 676 | + throw new \InvalidArgumentException( | |
| 677 | + "Invalid cursor: an active table cursor must contain current_column_names_hash. " . | |
| 678 | + "Run db-pull --abort and start again." | |
| 679 | + ); | |
| 680 | + } | |
| 681 | + if ( | |
| 682 | + !is_string($expected_column_names_hash) || | |
| 683 | + !preg_match('/^[0-9a-f]{64}$/D', $expected_column_names_hash) | |
| 684 | + ) { | |
| 685 | + throw new \InvalidArgumentException( | |
| 686 | + "Invalid cursor: current_column_names_hash must be a lowercase SHA-256 string" | |
| 687 | + ); | |
| 688 | + } | |
| 689 | + if (!hash_equals($expected_column_names_hash, $actual_column_names_hash)) { | |
| 690 | + // phpcs:disable WordPress.Security.EscapeOutput.ExceptionNotEscaped -- Cursor errors are returned as plain API messages. | |
| 691 | + throw new \RuntimeException( | |
| 692 | + "Cannot restore the database row cursor because the ordered columns for table " . | |
| 693 | + $this->row_reader->quote_identifier($this->row_reader->get_current_table()) . | |
| 694 | + " changed. Run db-pull --abort and start again." | |
| 695 | + ); | |
| 696 | + // phpcs:enable WordPress.Security.EscapeOutput.ExceptionNotEscaped | |
| 697 | + } | |
| 698 | + } | |
| 699 | + } | |
| 700 | + } | |
| 701 | + } | |
| 702 | + | |
| 703 | + /** Returns a fixed-size SHA-256 hash of the current table's ordered column names. */ | |
| 704 | + private function get_current_column_names_hash() | |
| 705 | + { | |
| 706 | + if ( | |
| 707 | + $this->state === self::STATE_NEXT_TABLE || | |
| 708 | + $this->row_reader->get_current_table() === null | |
| 709 | + ) { | |
| 710 | + return null; | |
| 711 | + } | |
| 712 | + $column_names = $this->row_reader->get_current_column_names(); | |
| 713 | + if ($column_names === null) { | |
| 714 | + return null; | |
| 715 | + } | |
| 716 | + return hash("sha256", serialize(array_values($column_names))); | |
| 717 | + } | |
| 718 | + | |
| 719 | + /** | |
| 720 | + * Formats a single column value as a SQL literal. | |
| 721 | + * | |
| 722 | + * Numeric types are emitted as bare literals. Everything else — strings, | |
| 723 | + * binary, dates, enums — goes through FROM_BASE64(). JSON is special: | |
| 724 | + * MySQL rejects binary-charset input for JSON columns, so we wrap with | |
| 725 | + * CONVERT(... USING utf8mb4) to decode the base64 into a utf8mb4 string. | |
| 726 | + * JSON can only be encoded as UTF-8 or UTF-16, and it's typically UTF-8. | |
| 727 | + * As of this version, we do not support UTF-16-encoded JSON data strings. | |
| 728 | + * | |
| 729 | + * @TODO: Support UTF-16-encoded JSON data strings. | |
| 730 | + */ | |
| 731 | + private function format_value($value, $data_type) | |
| 732 | + { | |
| 733 | + if ($value === null) { | |
| 734 | + return "NULL"; | |
| 735 | + } | |
| 736 | + | |
| 737 | + if ($this->row_reader->is_numeric_type($data_type)) { | |
| 738 | + return (string) $value; | |
| 739 | + } | |
| 740 | + | |
| 741 | + if (strtoupper($data_type) === "JSON") { | |
| 742 | + if ($value === "") { | |
| 743 | + return "''"; | |
| 744 | + } | |
| 745 | + $base64 = base64_encode($value); | |
| 746 | + return "CONVERT(FROM_BASE64('" . $base64 . "') USING utf8mb4)"; | |
| 747 | + } | |
| 748 | + | |
| 749 | + // Treat all other data types as strings and encode them as base64. This | |
| 750 | + // allows us to express all possible text encodings and arbitrary binary values. | |
| 751 | + if ($value === "") { | |
| 752 | + return "''"; | |
| 753 | + } | |
| 754 | + return "FROM_BASE64('" . base64_encode($value) . "')"; | |
| 755 | + } | |
| 756 | + | |
| 757 | + /** | |
| 758 | + * Estimates the byte length of format_value()'s output without actually | |
| 759 | + * encoding. Used by format_row_for_insert() to decide whether a row | |
| 760 | + * would exceed max_statement_size before doing the expensive encoding. | |
| 761 | + */ | |
| 762 | + private function estimate_formatted_size($value, $data_type) | |
| 763 | + { | |
| 764 | + if ($value === null) { | |
| 765 | + return 4; // NULL | |
| 766 | + } | |
| 767 | + | |
| 768 | + if ($this->row_reader->is_numeric_type($data_type)) { | |
| 769 | + return strlen((string) $value); | |
| 770 | + } | |
| 771 | + | |
| 772 | + $len = strlen((string) $value); | |
| 773 | + if ($len === 0) { | |
| 774 | + return 2; // '' | |
| 775 | + } | |
| 776 | + | |
| 777 | + /** Base64 output is always ceil(n/3)*4 bytes. */ | |
| 778 | + $estimated_base64_length = 4 * integer_divide($len + 2, 3); | |
| 779 | + // FROM_BASE64('<data>') adds 15 bytes. JSON adds the surrounding | |
| 780 | + // CONVERT(... USING utf8mb4), for 38 wrapper bytes in total. | |
| 781 | + $wrapper_bytes = strtoupper($data_type) === "JSON" ? 38 : 15; | |
| 782 | + return $wrapper_bytes + $estimated_base64_length; | |
| 783 | + } | |
| 784 | + | |
| 785 | + /** Auto-detects max_allowed_packet and uses 80% of it. Falls back to 1MB. */ | |
| 786 | + private function detect_max_statement_size() | |
| 787 | + { | |
| 788 | + try { | |
| 789 | + $result = $this->db->query("SELECT @@max_allowed_packet as max_allowed_packet"); | |
| 790 | + $row = $result->fetch(PdoConstants::fetch_assoc()); | |
| 791 | + if ($row && isset($row['max_allowed_packet'])) { | |
| 792 | + return (int)($row['max_allowed_packet'] * 0.8); | |
| 793 | + } | |
| 794 | + } catch (\Exception $e) { | |
| 795 | + } | |
| 796 | + | |
| 797 | + return 1024 * 1024; | |
| 798 | + } | |
| 799 | + | |
| 800 | + /** | |
| 801 | + * Formats a row as a VALUES tuple, splitting oversized columns if needed. | |
| 802 | + * | |
| 803 | + * The approach is estimate-first: compute the approximate encoded size of | |
| 804 | + * each column before doing the actual (expensive) base64 encoding. If the | |
| 805 | + * row fits the statement and part-body limits, encode everything. If it | |
| 806 | + * doesn't, replace eligible large non-PK columns with '' and queue their | |
| 807 | + * real values as UPDATE ... CONCAT() chunks in $this->oversized_queue. | |
| 808 | + * | |
| 809 | + * Tables without a primary key can't use the UPDATE fallback because | |
| 810 | + * there is no stable row identifier for the WHERE clause. Reject those | |
| 811 | + * rows before building an over-limit SQL fragment. | |
| 812 | + */ | |
| 813 | + private function format_row_for_insert($row, $sql_fragment_fixed_bytes) | |
| 814 | + { | |
| 815 | + $estimated_sizes = []; | |
| 816 | + $raw_values = []; | |
| 817 | + | |
| 818 | + foreach ($this->row_reader->get_current_column_names() as $col) { | |
| 819 | + $value = $row[$col] ?? null; | |
| 820 | + $raw_values[$col] = $value; | |
| 821 | + $data_type = $this->row_reader->get_data_type($col); | |
| 822 | + $estimated_sizes[$col] = $this->estimate_formatted_size($value, $data_type); | |
| 823 | + } | |
| 824 | + | |
| 825 | + $row_tuple_bytes = $this->estimate_formatted_row_tuple_bytes($row); | |
| 826 | + $row_separator_bytes = $this->rows_in_batch > 0 ? 1 : 0; | |
| 827 | + $maximum_insert_statement_bytes = min( | |
| 828 | + $this->max_statement_size, | |
| 829 | + self::MAX_SQL_PART_BODY_BYTES | |
| 830 | + ); | |
| 831 | + $projected_statement_size = | |
| 832 | + $this->current_statement_size + $row_separator_bytes + $row_tuple_bytes; | |
| 833 | + $projected_fragment_size = | |
| 834 | + $sql_fragment_fixed_bytes + $row_separator_bytes + $row_tuple_bytes; | |
| 835 | + | |
| 836 | + if ( | |
| 837 | + $projected_statement_size <= $maximum_insert_statement_bytes && | |
| 838 | + $projected_fragment_size <= self::MAX_SQL_PART_BODY_BYTES | |
| 839 | + ) { | |
| 840 | + $formatted_values = []; | |
| 841 | + foreach ($this->row_reader->get_current_column_names() as $col) { | |
| 842 | + $data_type = $this->row_reader->get_data_type($col); | |
| 843 | + $formatted_values[$col] = $this->format_value($raw_values[$col], $data_type); | |
| 844 | + } | |
| 845 | + return "(" . implode(",", array_values($formatted_values)) . ")"; | |
| 846 | + } | |
| 847 | + | |
| 848 | + // The rest of this method deals with rows that are too large to fit into a single INSERT on | |
| 849 | + // the receiving end. | |
| 850 | + | |
| 851 | + if (!$this->row_reader->get_current_primary_key_columns() || count($this->row_reader->get_current_primary_key_columns()) === 0) { | |
| 852 | + // phpcs:disable WordPress.Security.EscapeOutput.ExceptionNotEscaped -- Protocol error returned as authenticated API data, never HTML. | |
| 853 | + throw new \RuntimeException( | |
| 854 | + "Row in table " . $this->row_reader->quote_identifier($this->row_reader->get_current_table()) . | |
| 855 | + " has an estimated current INSERT size of {$projected_statement_size} bytes and SQL fragment size of" . | |
| 856 | + " {$projected_fragment_size} bytes. The limits are max_statement_size" . | |
| 857 | + " ({$this->max_statement_size} bytes) and the SQL part body limit" . | |
| 858 | + " (" . self::MAX_SQL_PART_BODY_BYTES . " bytes)," . | |
| 859 | + " but the table has no primary key, so the oversized row" . | |
| 860 | + " cannot be split into UPDATE ... CONCAT() chunks." | |
| 861 | + ); | |
| 862 | + // phpcs:enable WordPress.Security.EscapeOutput.ExceptionNotEscaped | |
| 863 | + } | |
| 864 | + | |
| 865 | + $this->oversized_pk_values = []; | |
| 866 | + foreach ($this->row_reader->get_current_primary_key_columns() as $pk_col) { | |
| 867 | + if (!array_key_exists($pk_col, $row)) { | |
| 868 | + throw new \RuntimeException( | |
| 869 | + "Primary key column '{$pk_col}' missing from row for table " . | |
| 870 | + $this->row_reader->quote_identifier($this->row_reader->get_current_table()) | |
| 871 | + ); | |
| 872 | + } | |
| 873 | + $this->oversized_pk_values[$pk_col] = $row[$pk_col]; | |
| 874 | + } | |
| 875 | + | |
| 876 | + // Split the largest columns first to bring the row under the limit | |
| 877 | + $sorted_sizes = $estimated_sizes; | |
| 878 | + arsort($sorted_sizes); | |
| 879 | + | |
| 880 | + $this->oversized_queue = []; | |
| 881 | + $chunked_columns = []; | |
| 882 | + | |
| 883 | + $excess = max( | |
| 884 | + $projected_statement_size - $maximum_insert_statement_bytes, | |
| 885 | + $projected_fragment_size - self::MAX_SQL_PART_BODY_BYTES | |
| 886 | + ); | |
| 887 | + $saved_bytes = 0; | |
| 888 | + $unchunkable_data_types = []; | |
| 889 | + | |
| 890 | + foreach ($sorted_sizes as $col => $size) { | |
| 891 | + if (in_array($col, $this->row_reader->get_current_primary_key_columns())) { | |
| 892 | + continue; | |
| 893 | + } | |
| 894 | + | |
| 895 | + if ($size < 1000) { | |
| 896 | + continue; | |
| 897 | + } | |
| 898 | + | |
| 899 | + if ($excess <= 0) { | |
| 900 | + break; | |
| 901 | + } | |
| 902 | + | |
| 903 | + $raw_value = $raw_values[$col]; | |
| 904 | + if ($raw_value === null || $raw_value === '') { | |
| 905 | + continue; | |
| 906 | + } | |
| 907 | + | |
| 908 | + $data_type = $this->row_reader->get_data_type($col); | |
| 909 | + $normalized_data_type = strtoupper($data_type); | |
| 910 | + if ( | |
| 911 | + !$this->row_reader->is_binary_type($normalized_data_type) && | |
| 912 | + !$this->row_reader->is_character_string_type($normalized_data_type) | |
| 913 | + ) { | |
| 914 | + $unchunkable_data_types[$normalized_data_type] = true; | |
| 915 | + continue; | |
| 916 | + } | |
| 917 | + $value_length = strlen($raw_value); | |
| 918 | + $chunk_size = $this->compute_chunk_size($col); | |
| 919 | + | |
| 920 | + if ($value_length > $chunk_size) { | |
| 921 | + $chunked_columns[$col] = true; | |
| 922 | + $saved_bytes += $size - 2; // Saved bytes (size minus the '' replacement) | |
| 923 | + $excess -= $size - 2; | |
| 924 | + | |
| 925 | + $queue_item = [ | |
| 926 | + 'column' => $col, | |
| 927 | + 'data_type' => $data_type, | |
| 928 | + 'byte_offset' => 0, | |
| 929 | + 'total_length' => $value_length, | |
| 930 | + ]; | |
| 931 | + if ($this->row_reader->is_character_string_type($data_type)) { | |
| 932 | + $queue_item['character_offset'] = 0; | |
| 933 | + } | |
| 934 | + $this->oversized_queue[] = $queue_item; | |
| 935 | + } | |
| 936 | + } | |
| 937 | + | |
| 938 | + if ($excess > 0 && !empty($unchunkable_data_types)) { | |
| 939 | + $unchunkable_data_type = key($unchunkable_data_types); | |
| 940 | + // phpcs:disable WordPress.Security.EscapeOutput.ExceptionNotEscaped -- Protocol error returned as authenticated API data, never HTML. | |
| 941 | + throw new \RuntimeException( | |
| 942 | + "Row in table " . $this->row_reader->quote_identifier($this->row_reader->get_current_table()) . | |
| 943 | + " cannot use UPDATE ... CONCAT() chunks for data type {$unchunkable_data_type}." | |
| 944 | + ); | |
| 945 | + // phpcs:enable WordPress.Security.EscapeOutput.ExceptionNotEscaped | |
| 946 | + } | |
| 947 | + | |
| 948 | + if ( | |
| 949 | + $projected_statement_size - $saved_bytes > | |
| 950 | + $maximum_insert_statement_bytes || | |
| 951 | + $projected_fragment_size - $saved_bytes > | |
| 952 | + self::MAX_SQL_PART_BODY_BYTES | |
| 953 | + ) { | |
| 954 | + // phpcs:disable WordPress.Security.EscapeOutput.ExceptionNotEscaped -- Protocol error returned as authenticated API data, never HTML. | |
| 955 | + throw new \RuntimeException( | |
| 956 | + "Row in table " . $this->row_reader->quote_identifier($this->row_reader->get_current_table()) . | |
| 957 | + " cannot fit the SQL size limits with the available UPDATE chunking." . | |
| 958 | + " max_statement_size is {$this->max_statement_size}" . | |
| 959 | + " bytes and the SQL part body limit is " . self::MAX_SQL_PART_BODY_BYTES . " bytes." | |
| 960 | + ); | |
| 961 | + // phpcs:enable WordPress.Security.EscapeOutput.ExceptionNotEscaped | |
| 962 | + } | |
| 963 | + | |
| 964 | + if (empty($chunked_columns)) { | |
| 965 | + $this->oversized_pk_values = null; | |
| 966 | + } | |
| 967 | + | |
| 968 | + $formatted_values = []; | |
| 969 | + foreach ($this->row_reader->get_current_column_names() as $col) { | |
| 970 | + if (isset($chunked_columns[$col])) { | |
| 971 | + $formatted_values[$col] = "''"; | |
| 972 | + continue; | |
| 973 | + } | |
| 974 | + $data_type = $this->row_reader->get_data_type($col); | |
| 975 | + $formatted_values[$col] = $this->format_value($raw_values[$col], $data_type); | |
| 976 | + } | |
| 977 | + | |
| 978 | + return "(" . implode(",", array_values($formatted_values)) . ")"; | |
| 979 | + } | |
| 980 | + | |
| 981 | + /** Returns the exact SQL bytes used by one formatted VALUES tuple. */ | |
| 982 | + private function estimate_formatted_row_tuple_bytes($row) | |
| 983 | + { | |
| 984 | + $tuple_bytes = 2; | |
| 985 | + $column_index = 0; | |
| 986 | + foreach ($this->row_reader->get_current_column_names() as $column) { | |
| 987 | + if ($column_index > 0) { | |
| 988 | + ++$tuple_bytes; | |
| 989 | + } | |
| 990 | + $tuple_bytes += $this->estimate_formatted_size( | |
| 991 | + $row[$column] ?? null, | |
| 992 | + $this->row_reader->get_data_type($column) | |
| 993 | + ); | |
| 994 | + ++$column_index; | |
| 995 | + } | |
| 996 | + return $tuple_bytes; | |
| 997 | + } | |
| 998 | + | |
| 999 | + /** | |
| 1000 | + * Computes the maximum raw byte size of each chunk for the given column, | |
| 1001 | + * such that an UPDATE ... SET col = CONCAT(col, FROM_BASE64('...')) | |
| 1002 | + * statement stays within both SQL size limits. | |
| 1003 | + */ | |
| 1004 | + private function compute_chunk_size($column) | |
| 1005 | + { | |
| 1006 | + $quoted_table = $this->row_reader->quote_identifier($this->row_reader->get_current_table()); | |
| 1007 | + $quoted_column = $this->row_reader->quote_identifier($column); | |
| 1008 | + $update_overhead = strlen("UPDATE {$quoted_table} SET {$quoted_column} = CONCAT({$quoted_column}, ) WHERE ;"); | |
| 1009 | + $where_clause_size = $this->estimate_pk_where_size(); | |
| 1010 | + $total_overhead = $update_overhead + $where_clause_size + 100; // Extra margin | |
| 1011 | + | |
| 1012 | + $maximum_update_statement_size = min( | |
| 1013 | + $this->max_statement_size, | |
| 1014 | + self::MAX_SQL_PART_BODY_BYTES | |
| 1015 | + ); | |
| 1016 | + $max_chunk_raw_size = ($maximum_update_statement_size - $total_overhead); | |
| 1017 | + | |
| 1018 | + // Base64 inflates by ~1.33x, plus FROM_BASE64('') wrapper overhead | |
| 1019 | + $max_chunk_raw_size = (int)(($max_chunk_raw_size - 20) / 1.34); | |
| 1020 | + return max($max_chunk_raw_size, 1000); | |
| 1021 | + } | |
| 1022 | + | |
| 1023 | + /** Rough strlen() estimate for the WHERE pk1 = v1 AND pk2 = v2 clause. */ | |
| 1024 | + private function estimate_pk_where_size() | |
| 1025 | + { | |
| 1026 | + if (!$this->oversized_pk_values) { | |
| 1027 | + /** | |
| 1028 | + * A wild guess. 1KB is probably more than necessary, but we're trying to stay | |
| 1029 | + * on the safe side. | |
| 1030 | + */ | |
| 1031 | + return 1024; | |
| 1032 | + } | |
| 1033 | + | |
| 1034 | + $size = 0; | |
| 1035 | + foreach ($this->oversized_pk_values as $col => $value) { | |
| 1036 | + $size += strlen($this->row_reader->build_comparison($col, $value, "=")); | |
| 1037 | + $size += 5; // AND | |
| 1038 | + } | |
| 1039 | + | |
| 1040 | + return (int)$size; | |
| 1041 | + } | |
| 1042 | + | |
| 1043 | + /** | |
| 1044 | + * Emits one UPDATE ... SET col = CONCAT(col, chunk) statement. | |
| 1045 | + * | |
| 1046 | + * Instead of storing the entire column value in memory, this method | |
| 1047 | + * re-reads just the needed chunk from the database using SUBSTRING(). | |
| 1048 | + * This keeps the cursor tiny (byte offsets only) while still producing | |
| 1049 | + * the correct UPDATE statements. | |
| 1050 | + * | |
| 1051 | + * Returns false when the queue is drained so the next INSERT can begin. | |
| 1052 | + */ | |
| 1053 | + private function emit_oversized_update() | |
| 1054 | + { | |
| 1055 | + if (empty($this->oversized_queue)) { | |
| 1056 | + $this->state = self::STATE_START_INSERT; | |
| 1057 | + $this->oversized_pk_values = null; | |
| 1058 | + return false; | |
| 1059 | + } | |
| 1060 | + | |
| 1061 | + $current = $this->oversized_queue[0]; | |
| 1062 | + $column = $current['column']; | |
| 1063 | + $data_type = $current['data_type']; | |
| 1064 | + $byte_offset = $current['byte_offset']; | |
| 1065 | + $total_length = $current['total_length']; | |
| 1066 | + | |
| 1067 | + $chunk_size = $this->compute_chunk_size($column); | |
| 1068 | + | |
| 1069 | + // MySQL SUBSTRING() counts characters for character strings, while | |
| 1070 | + // $chunk_size is a byte budget. Every requested character may use the | |
| 1071 | + // column character set's maximum byte length, so a fixed amount of | |
| 1072 | + // spare space would not bound the result. Divide the byte budget by | |
| 1073 | + // that per-character maximum to keep the raw chunk within its limit | |
| 1074 | + // without splitting a character. Binary strings continue in bytes. | |
| 1075 | + $character_string = $this->row_reader->is_character_string_type($data_type); | |
| 1076 | + if ($character_string) { | |
| 1077 | + $value_offset = $current['character_offset']; | |
| 1078 | + $value_length = max( | |
| 1079 | + 1, | |
| 1080 | + integer_divide( | |
| 1081 | + $chunk_size, | |
| 1082 | + $this->row_reader->get_maximum_character_bytes($column) | |
| 1083 | + ) | |
| 1084 | + ); | |
| 1085 | + } else { | |
| 1086 | + $value_offset = $byte_offset; | |
| 1087 | + $value_length = min($chunk_size, $total_length - $byte_offset); | |
| 1088 | + } | |
| 1089 | + | |
| 1090 | + $chunk_result = $this->fetch_value_substring_from_the_current_oversized_row( | |
| 1091 | + $column, | |
| 1092 | + $value_offset + 1, | |
| 1093 | + $value_length, | |
| 1094 | + $character_string | |
| 1095 | + ); | |
| 1096 | + $chunk = $chunk_result['value']; | |
| 1097 | + $chunk_bytes = strlen($chunk); | |
| 1098 | + | |
| 1099 | + if ($chunk_bytes === 0 && $byte_offset < $total_length) { | |
| 1100 | + // phpcs:disable WordPress.Security.EscapeOutput.ExceptionNotEscaped -- Protocol error returned as authenticated API data, never HTML. | |
| 1101 | + throw new \RuntimeException( | |
| 1102 | + "Oversized column " . | |
| 1103 | + $this->row_reader->quote_identifier($this->row_reader->get_current_table()) . "." . | |
| 1104 | + $this->row_reader->quote_identifier($column) . | |
| 1105 | + " returned an empty chunk at byte offset {$byte_offset} before its saved" . | |
| 1106 | + " {$total_length}-byte length. The source value changed during export;" . | |
| 1107 | + " run db-pull --abort and start again." | |
| 1108 | + ); | |
| 1109 | + // phpcs:enable WordPress.Security.EscapeOutput.ExceptionNotEscaped | |
| 1110 | + } | |
| 1111 | + if ($chunk_bytes > $total_length - $byte_offset) { | |
| 1112 | + // phpcs:disable WordPress.Security.EscapeOutput.ExceptionNotEscaped -- Protocol error returned as authenticated API data, never HTML. | |
| 1113 | + throw new \RuntimeException( | |
| 1114 | + "Oversized column " . | |
| 1115 | + $this->row_reader->quote_identifier($this->row_reader->get_current_table()) . "." . | |
| 1116 | + $this->row_reader->quote_identifier($column) . | |
| 1117 | + " returned {$chunk_bytes} bytes at byte offset {$byte_offset}, beyond its" . | |
| 1118 | + " saved {$total_length}-byte length. The source value changed during export;" . | |
| 1119 | + " run db-pull --abort and start again." | |
| 1120 | + ); | |
| 1121 | + // phpcs:enable WordPress.Security.EscapeOutput.ExceptionNotEscaped | |
| 1122 | + } | |
| 1123 | + | |
| 1124 | + $formatted_chunk = $this->format_value($chunk, $data_type); | |
| 1125 | + | |
| 1126 | + $where_parts = []; | |
| 1127 | + foreach ($this->oversized_pk_values as $pk_col => $pk_value) { | |
| 1128 | + $where_parts[] = $this->row_reader->build_comparison($pk_col, $pk_value, "="); | |
| 1129 | + } | |
| 1130 | + $where_clause = implode(" AND ", $where_parts); | |
| 1131 | + | |
| 1132 | + $quoted_table = $this->row_reader->quote_identifier($this->row_reader->get_current_table()); | |
| 1133 | + $quoted_column = $this->row_reader->quote_identifier($column); | |
| 1134 | + $sql = "UPDATE {$quoted_table} SET {$quoted_column} = CONCAT({$quoted_column}, {$formatted_chunk}) WHERE {$where_clause};"; | |
| 1135 | + | |
| 1136 | + $this->current_sql_fragment = $sql; | |
| 1137 | + | |
| 1138 | + $this->oversized_queue[0]['byte_offset'] += $chunk_bytes; | |
| 1139 | + if ($character_string) { | |
| 1140 | + $this->oversized_queue[0]['character_offset'] += $chunk_result['value_length']; | |
| 1141 | + } | |
| 1142 | + if ($this->oversized_queue[0]['byte_offset'] >= $total_length) { | |
| 1143 | + array_shift($this->oversized_queue); | |
| 1144 | + } | |
| 1145 | + | |
| 1146 | + return true; | |
| 1147 | + } | |
| 1148 | + | |
| 1149 | + /** | |
| 1150 | + * Fetches a substring of a column value from the current table using | |
| 1151 | + * the oversized row's primary key values. | |
| 1152 | + * | |
| 1153 | + * Character strings use character ranges so a chunk never cuts a | |
| 1154 | + * multibyte character. Binary strings cast before SUBSTRING so their | |
| 1155 | + * ranges count bytes. Both return raw bytes for base64 encoding. | |
| 1156 | + * | |
| 1157 | + * @return array { | |
| 1158 | + * Fetched substring details. | |
| 1159 | + * | |
| 1160 | + * @type string $value Raw substring bytes. | |
| 1161 | + * @type int $value_length Length in characters or bytes, matching the requested range. | |
| 1162 | + * } | |
| 1163 | + */ | |
| 1164 | + private function fetch_value_substring_from_the_current_oversized_row( | |
| 1165 | + string $column, | |
| 1166 | + int $start, | |
| 1167 | + int $length, | |
| 1168 | + bool $character_string | |
| 1169 | + ): array { | |
| 1170 | + $quoted_table = $this->row_reader->quote_identifier($this->row_reader->get_current_table()); | |
| 1171 | + $quoted_column = $this->row_reader->quote_identifier($column); | |
| 1172 | + | |
| 1173 | + $where_parts = []; | |
| 1174 | + foreach ($this->oversized_pk_values as $pk_col => $pk_value) { | |
| 1175 | + $where_parts[] = $this->row_reader->build_comparison($pk_col, $pk_value, "="); | |
| 1176 | + } | |
| 1177 | + $where_clause = implode(" AND ", $where_parts); | |
| 1178 | + | |
| 1179 | + $value_expression = $character_string | |
| 1180 | + ? "SUBSTRING({$quoted_column}, {$start}, {$length})" | |
| 1181 | + : "SUBSTRING(CAST({$quoted_column} AS BINARY), {$start}, {$length})"; | |
| 1182 | + $sql = "SELECT CAST({$value_expression} AS BINARY) AS value_chunk," | |
| 1183 | + . " CHAR_LENGTH({$value_expression}) AS value_length" | |
| 1184 | + . " FROM {$quoted_table} WHERE {$where_clause}"; | |
| 1185 | + $stmt = $this->db->prepare($sql); | |
| 1186 | + $stmt->execute(); | |
| 1187 | + $result = $stmt->fetch(PdoConstants::fetch_assoc()); | |
| 1188 | + | |
| 1189 | + if ($result === false) { | |
| 1190 | + throw new \RuntimeException( | |
| 1191 | + "Failed to fetch column substring for oversized row: {$column}" | |
| 1192 | + ); | |
| 1193 | + } | |
| 1194 | + | |
| 1195 | + return [ | |
| 1196 | + 'value' => $result['value_chunk'], | |
| 1197 | + 'value_length' => (int) $result['value_length'], | |
| 1198 | + ]; | |
| 1199 | + } | |
| 1200 | + | |
| 1201 | + /** @return bool */ | |
| 1202 | + private function has_pending_oversized_updates() | |
| 1203 | + { | |
| 1204 | + return !empty($this->oversized_queue); | |
| 1205 | + } | |
| 1206 | +} | |