CREATE TABLE…SELECT writes inconsistent PAGE_MAX_TRX_ID

As noted in MDEV-14992, CHECK TABLE…EXTENDED is reporting bogus errors for the output of BACKUP SERVER.

According to the undo log records in the copy of the data directory that is attached to MDEV-14992, the next unassigned transaction identifier is 22. Alas, that identifier is not unassigned: it occurs as PAGE_MAX_TRX_ID in a secondary index page. The very last action that is included in the backup is the completion of a CREATE TABLE…SELECT operation, a mini-transaction that updates the PAGE_MAX_TRX_ID as well as all page data.

In ha_innobase::create(), trx_sys.assign_new_trx_no() had assigned the "end" transaction identifier 21 for this transaction. The consistent PAGE_MAX_TRX_ID to write would have been the start identifier (DB_TRX_ID=20) of the ha_innobase::create() transaction, which had been assigned in trx_start_for_ddl_low().

What happens instead is that when CREATE TABLE…SELECT is about to write the very first row to the table, a new transaction "start" identifier is assigned and eventually associated with the PageBulk::m_mtr, to be written as the PAGE_MAX_TRX_ID:

#5  0x0000652d4e9f865d in trx_start_if_not_started_xa_low (trx=trx@entry=0x7766b18a6780, read_write=read_write@entry=0x1)

    at /data/Server/MDEV-14992B/storage/innobase/trx/trx0trx.cc:2445

#6  0x0000652d4e97d54b in row_insert_for_mysql (mysql_rec=mysql_rec@entry=0x6f6680195e40 "\375\b", prebuilt=0x6f66801a59e8, ins_mode=ROW_INS_NORMAL)

    at /data/Server/MDEV-14992B/storage/innobase/row/row0mysql.cc:1274

#7  0x0000652d4e82b0ff in ha_innobase::write_row (this=0x6f66801955d0, record=0x6f6680195e40 "\375\b") at /data/Server/MDEV-14992B/storage/innobase/handler/ha_innodb.cc:8232

Instead of doing this (which is a valid thing to do on an INSERT…SELECT or in other cases), we should somehow remember the original transaction ID and use it instead. Perhaps we could just overwrite the trx->id with something that we cache in row_prebuilt_t for this special case?

As far as I understand, this problem only affects CREATE…SELECT. Even ALTER TABLE…ALGORITHM=COPY is fine, because in order for the operation to be recovered as completed in the backup, a ha_innobase::rename_table() must have completed (and successfully allocated a subsequent transaction ID).

This problem is likely only reproducible by specifying the parameter innodb_log_recovery_target that was introduced in MDEV-37949. When this parameter is nonzero, InnoDB will not start any transactions, for example for attempting to drop tables whose names start with #sql-, or for updating persistent statistics.