Skip to content

v0.9.2

v0.9.2: Paginate text primary keys in indexed collation order (#401)
@adamziel adamziel tagged this 27 Jul 19:14 
Text-primary-key exports now resume in the source primary key's indexed
collation order, so every batch can seek into the primary B-tree instead
of scanning and sorting the table again.

## Background

#398 made both sides of pagination byte-ordered with `CAST(primary_key
AS BINARY)`. That kept `WHERE` and `ORDER BY` consistent, but applying a
function to the indexed column prevents MySQL from using the primary key
for either the range or the order.

For example:

```sql
CREATE TABLE translated_keys (
    category VARCHAR(16) CHARACTER SET latin1 COLLATE latin1_german2_ci NOT NULL,
    sequence_number INT NOT NULL,
    PRIMARY KEY (category, sequence_number)
);

INSERT INTO translated_keys VALUES
    (CONVERT(X'E4' USING latin1), 1),
    ('ae', 2),
    (CONVERT(X'E4' USING latin1), 3),
    ('b', 1);
```

With `batch_size=1`, resuming after each row previously compared and
ordered by `CAST(category AS BINARY)`. MySQL reported a full scan and
`Using filesort` for every batch. On this table, `ä` and `ae` are equal
under the indexed collation, so `sequence_number` must order those
composite keys.

## This change

The SELECT list still casts text to binary, so the dump and cursor
retain the original database bytes. Pagination compares the bare text
column with `FROM_BASE64(...)` and orders by that same bare column. The
column's declared character set and collation win the comparison;
connection charset and collation do not participate.

Both the native PDO path and the `wpdb` fallback run `SET NAMES utf8mb4
COLLATE utf8mb4_bin` before exporter queries. This gives them the same
client, connection, and result charsets and the same connection
collation without overriding a column's stored collation.

The resulting plan uses a `PRIMARY` range scan without a filesort on
MySQL 5.7, MySQL 8.0, and MariaDB 10.11. `ENUM` and `SET` retain byte
ordering because their indexes order numeric positions while the cursor
contains their displayed strings.

## Testing

The one-row resume tests cover raw latin1 bytes, numeric-looking text
keys, a non-binary collation, and a composite `(latin1 VARCHAR, INT)`
key with collation-equal text values. The new index-order and composite
tests fail against #398 and pass here. The connection tests start PDO
with a latin1 session and simulate the PDO-less `wpdb` path; both
confirm that exporter setup ends at `utf8mb4_bin`.

```sh
cd tests && ../vendor/bin/phpunit
php vendor/bin/phpstan analyze --memory-limit=1G
vendor/bin/phpcs --standard=PHPCompatibility --runtime-set testVersion 7.2- -ps packages/reprint-exporter/src/class-mysql-dump-producer.php packages/reprint-exporter/src/export.php packages/reprint-exporter/src/class-wpdb-driver-pdo.php
git diff --check
```
Assets 2
Loading