Skip to content

R4 — MySQL .sql dump streaming parser: pitfalls + architecture

Verified against MySQL manual, mysqldump C source, phpMyAdmin, UpdraftPlus, and mysqldump-php sources. C1 (sql_dump.py) implements the architecture in §B; every item in §A becomes a unit-test fixture.

A. Pitfall checklist (each = required test fixture)

  1. Multi-row extended INSERT on one line: INSERT INTO \wp_posts` VALUES (1,'a'),(2,'b');`
  2. Extended inserts spanning physical lines (phpMyAdmin: tuple-per-line, ,\n separator, 50 KB cap; UpdraftPlus: ,\n separator, 512 KB cap; mysqldump 9.x multiline mode).
  3. UpdraftPlus statement boundary is ; + " \n" (space before newline) — exact ;\n detection misses it.
  4. Chunk caps: mysqldump 16384, phpMyAdmin 50000, UpdraftPlus 524288, mysqldump-php 1000000; single rows up to 24 MiB (max-allowed-packet) — never readline() whole.
  5. Backslash escapes from mysqldump: 0 \' \" \n \r \Z \\ \ (note backtick IS escaped).
  6. Quote doubling (NO_BACKSLASH_ESCAPES / some PHP exporters): 'Bob''s'.
  7. Backslash parity: 'D:\\path\\' closes at final quote; 'a\'' does NOT close — count consecutive backslashes, quote closes iff count is even.
  8. MySQL unescape table: \0 \' \" \b \n \r \t \Z \\; \% and \_ stay literal two-char; any other \x -> x.
  9. Adjacent string concatenation 'a' 'b' is legal SQL.
  10. '' emitted for empty numeric columns (UpdraftPlus).
  11. Unquoted NULL, bare numerics, quoted DECIMAL '1.50'.
  12. Unquoted hex blobs 0x616263 (odd digit count possible), X'4D7953514C', b'0101'.
  13. _binary 'raw\xff bytes' introducer WITH space; file is NOT valid UTF-8 — byte-oriented parsing only, lazy decode of extracted values with errors='replace'.
  14. Executable conditional comments /*!40101 SET ... */; may contain strings and ;; ;; double-semicolon after routine bodies; DELIMITER ;; blocks must be opaque.
  15. -- comments require following whitespace (--x is not a comment); # to EOL (UpdraftPlus uses # exclusively); values may contain --/# bytes — only quote-state-aware scanning distinguishes.
  16. CRLF line endings everywhere possible.
  17. Table detection: CREATE TABLE [IF NOT EXISTS], backtick-doubled names (\weird``name``), unquoted names;DROP TABLE IF EXISTS+50001` view sections must not double-count.
  18. LOCK TABLES/UNLOCK TABLES present in mysqldump but ABSENT in phpMyAdmin/UpdraftPlus — INSERT table attribution must come from the INSERT prefix itself.
  19. INSERT IGNORE INTO and mysqldump-php's INSERT IGNORE INTO (double space); REPLACE INTO — prefix matching tolerant of arbitrary whitespace.
  20. Complete-insert column lists: INSERT INTO \wp_options` (`option_id`,...) VALUES` — column mapping must prefer the explicit list over CREATE TABLE order.
  21. php-serialized option values full of ; : { } " defeat naive ; splitting.
  22. Statement at EOF without trailing newline must flush.
  23. SET NAMES/SET @@session... lines ignored; harvest charset as decode hint.

B. Architecture (binding)

  1. Binary chunk reader ('rb', 256 KB–1 MB reads, carry-over lookahead); bounded statement-head buffer (64 KB) for classification; never line-based.
  2. One byte-level state machine: CODE, LINE_COMMENT, BLOCK_COMMENT, SQ, DQ + backslash parity flag + paren depth + current DELIMITER.
  3. Statement ends at ; in CODE at depth 0 (or current delimiter) followed by optional whitespace + newline/EOF.
  4. Classify on head: CREATE TABLE / INSERT-REPLACE INTO / other(skip).
  5. CREATE TABLE handler: bounded capture; extract name + column order + ENGINE.
  6. INSERT row splitter: explicit column list if present else CREATE order; top-level ( after VALUES starts row; ),( ),\n( ),\n ( (any whitespace) starts next.
  7. Target-row extraction (wp_options: siteurl/home/active_plugins/template/ blog_public/stylesheet + autoload byte sums; wp_posts: type+status counts) — capture only matching value spans; SQL-unescape (doubling + MySQL escape table + hex/binary literal decoding); decode lazily errors='replace'.
  8. Per-statement byte cap (64 MB); structural anomalies -> log via wpt.log, continue.
  9. PHP-unserialize restricted to s/a/i/b/d with length-echo validation (s:N:"..." must be exactly N bytes) — hostile payloads fail loudly, never loop.
  10. Rows counted by tuple parsing (statement count != row count — extended inserts).

C. Naive-parser failure fixtures (10, each a test proving our parser survives)

  1. split(';') on serialized PHP value
  2. regex ),( split on content containing that literal + real multiline separators
  3. line-start comment stripping breaking on --/# inside values
  4. INSERT INTO \(\w+)`` missing IGNORE/double-space/REPLACE/unquoted variants
  5. '([^']*)' failing on doubling/escapes/introducers/hex
  6. comma-split columns breaking on commas in values
  7. readline() unbounded on 10 MB single-line INSERT
  8. ;\n-exact missing ; \n / ;\r\n / */ ;; / EOF
  9. whole-file UTF-8 decode crashing on _binary blobs
  10. row counting via INSERT-statement count

Citations: dev.mysql.com string-literals/comments/hexadecimal-literals/mysqldump; mysql-server client/mysqldump.cc; phpMyAdmin ExportSql.php; UpdraftPlus backup.php; ifsnop/mysqldump-php Mysqldump.php (line refs in research session log).