MySQL Bugs: #121177: different innodb_ddl_buffer_size for master and slave masy cause duplicated key error

Bug #121177	different innodb_ddl_buffer_size for master and slave masy cause duplicated key error
Submitted:	27 Aug 7:59	Modified:	1 Sep 12:11
Reporter:	zhang xiaojian (OCA)	Email Updates:
Status:	Open	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S2 (Serious)
Version:	8.0.45, 8.4.8, 9.6.0	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	replication 1062

Description:
Bug #17657223 optimize the sort for inplace DDL, when add an auto increament key wich not need to resort data, may use skip_pk_sort path. The innodb_ddl_buffer_size use to store the new column value and write to new table when buffer is full.

I find when buffer cleared, one auto increate value is wasted. for example:

original table:
A
B
C
D
E
F

alter add new auto increate key and buffer is full after write (3, B)
1. A
2. B
3. C
5. D
6. E
7. F

That not a problem if we have only one primary instance.

If we have a primary-replica, and they have different innodb_ddl_buffer_size value.

The slave may have value after alter table statement applied:
1. A
2. B
4. C
5. D
7. E
8. F

The key of 8 has beed used in replica, if one more insert execute in primary, duplicated key error would break the replication.

How to repeat:
1. init a primary and slave with binlog replication.
2. primary: --innodb_ddl_buffer_size=1048576 slave:--innodb_ddl_buffer_size=65536
3. execute sql in primary 

```
CREATE DATABASE IF NOT EXISTS repro_skip_pk_sort_autoinc;
USE repro_skip_pk_sort_autoinc;

DROP TABLE IF EXISTS t_repro;
DROP TABLE IF EXISTS digits;

-- 缩小 DDL buffer，使 overflow 更容易、结果更明显。
-- SET SESSION innodb_ddl_buffer_size = 165536;
SET SESSION innodb_ddl_threads = 1;

-- 排除非默认自增步长的影响。
SET SESSION auto_increment_increment = 1;
SET SESSION auto_increment_offset = 1;

CREATE TABLE digits (
    n TINYINT UNSIGNED NOT NULL PRIMARY KEY
) ENGINE=InnoDB;

INSERT INTO digits VALUES
    (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);

-- 旧表必须有显式主键。
-- 较宽的 payload 用于频繁填满 DDL key buffer。
CREATE TABLE t_repro (
    a       INT UNSIGNED NOT NULL,
    payload VARCHAR(4000) CHARACTER SET ascii NOT NULL,
    PRIMARY KEY (a)
) ENGINE=InnoDB ROW_FORMAT=DYNAMIC;

-- 生成 500 条、每条约 4KB 的记录。
INSERT INTO t_repro(a, payload)
SELECT seq,
       REPEAT(CHAR(65 + MOD(seq, 26)), 4000)
FROM (
    SELECT d0.n
           + d1.n * 10
           + d2.n * 100
           + 1 AS seq
    FROM digits AS d0
    CROSS JOIN digits AS d1
    CROSS JOIN digits AS d2
) AS numbers
WHERE seq <= 500
ORDER BY seq DESC;

SELECT COUNT(*) AS rows_before_alter
FROM t_repro;

-- 关键操作：
-- 1. 重建聚簇索引；
-- 2. 新增 AUTO_INCREMENT 列；
-- 3. 新主键由新增列组成；
-- 4. 生成的 id 按旧主键扫描顺序递增，因此命中 skip_pk_sort。
ALTER TABLE t_repro
    DROP PRIMARY KEY,
    ADD COLUMN id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    ADD PRIMARY KEY (id),
    AUTO_INCREMENT = 1,
    ALGORITHM = INPLACE,
    LOCK = SHARED;

-- 正常情况下，500 行应只消费 500 个自增值：
--   MIN(id) = 1
--   MAX(id) = 500
--   missing_sequence_values = 0
--
-- 存在该问题时：
--   MAX(id) > 500
--   missing_sequence_values > 0
SELECT
    COUNT(*) AS row_count,
    COUNT(DISTINCT id) AS distinct_id_count,
    MIN(id) AS min_id,
    MAX(id) AS max_id,
    MAX(id) - MIN(id) + 1 - COUNT(*) AS missing_sequence_values
FROM t_repro;

-- 输出每一段丢失的自增值。
WITH ordered_ids AS (
    SELECT
        id,
        LAG(id) OVER (ORDER BY id) AS previous_id
    FROM t_repro
)
SELECT
    previous_id + 1 AS missing_from,
    id - 1 AS missing_to,
    id - previous_id - 1 AS missing_count
FROM ordered_ids
WHERE previous_id IS NOT NULL
  AND id > previous_id + 1
ORDER BY missing_from;

-- 查看部分相邻记录，可以直接观察 id 跳号。
WITH ordered_rows AS (
    SELECT
        id,
        a,
        LAG(id) OVER (ORDER BY id) AS previous_id
    FROM t_repro
)
SELECT previous_id, id, a
FROM ordered_rows
WHERE previous_id IS NOT NULL
  AND id > previous_id + 1
ORDER BY id
LIMIT 20;

-- 验证 ALTER 后保存的 AUTO_INCREMENT 计数器。
SHOW CREATE TABLE t_repro;

-- 再插入一行，观察实际分配的下一个 ID。
INSERT INTO t_repro(a, payload)
VALUES (1000000, 'after alter');

SELECT id AS id_allocated_after_alter
FROM t_repro
WHERE a = 1000000;

-- 如需清理，执行：
-- DROP DATABASE repro_skip_pk_sort_autoinc;
```

rows in primary:
```
mysql> select id from t_repro;
+-----+
| id  |
+-----+
|   1 |
|   2 |
|   3 |
|   4 |
|   5 |
|   6 |
|   7 |
|   8 |
|   9 |
|  10 |
|  11 |
|  12 |
|  13 |
|  14 |
|  15 |
|  16 |
|  17 |
|  18 |
|  19 |
|  20 |
|  21 |
|  22 |
|  23 |
|  24 |
|  25 |
|  26 |
|  27 |
|  28 |
|  29 |
|  30 |
|  31 |
|  32 |
|  33 |
|  34 |
|  35 |
|  36 |
|  37 |
|  38 |
|  39 |
|  40 |
|  41 |
|  42 |
|  43 |
|  44 |
|  45 |
|  46 |
|  47 |
|  48 |
|  49 |
|  50 |
|  51 |
|  52 |
|  53 |
|  54 |
|  55 |
|  56 |
|  57 |
|  58 |
|  59 |
|  60 |
|  61 |
|  62 |
|  63 |
|  64 |
|  65 |
|  66 |
|  67 |
|  68 |
|  69 |
|  70 |
|  71 |
|  72 |
|  73 |
|  74 |
|  75 |
|  76 |
|  77 |
|  78 |
|  79 |
|  80 |
|  81 |
|  82 |
|  83 |
|  84 |
|  85 |
|  86 |
|  87 |
|  88 |
|  89 |
|  90 |
|  91 |
|  92 |
|  93 |
|  94 |
|  95 |
|  96 |
|  97 |
|  98 |
|  99 |
| 100 |
| 101 |
| 102 |
| 103 |
| 104 |
| 105 |
| 106 |
| 107 |
| 108 |
| 109 |
| 110 |
| 111 |
| 112 |
| 113 |
| 114 |
| 115 |
| 116 |
| 117 |
| 118 |
| 119 |
| 120 |
| 121 |
| 122 |
| 123 |
| 124 |
| 125 |
| 126 |
| 127 |
| 128 |
| 129 |
| 131 |
| 132 |
| 133 |
| 134 |
| 135 |
| 136 |
| 137 |
| 138 |
| 139 |
| 140 |
| 141 |
| 142 |
| 143 |
| 144 |
| 145 |
| 146 |
| 147 |
| 148 |
| 149 |
| 150 |
| 151 |
| 152 |
| 153 |
| 154 |
| 155 |
| 156 |
| 157 |
| 158 |
| 159 |
| 160 |
| 161 |
| 162 |
| 163 |
| 164 |
| 165 |
| 166 |
| 167 |
| 168 |
| 169 |
| 170 |
| 171 |
| 172 |
| 173 |
| 174 |
| 175 |
| 176 |
| 177 |
| 178 |
| 179 |
| 180 |
| 181 |
| 182 |
| 183 |
| 184 |
| 185 |
| 186 |
| 187 |
| 188 |
| 189 |
| 190 |
| 191 |
| 192 |
| 193 |
| 194 |
| 195 |
| 196 |
| 197 |
| 198 |
| 199 |
| 200 |
| 201 |
| 202 |
| 203 |
| 204 |
| 205 |
| 206 |
| 207 |
| 208 |
| 209 |
| 210 |
| 211 |
| 212 |
| 213 |
| 214 |
| 215 |
| 216 |
| 217 |
| 218 |
| 219 |
| 220 |
| 221 |
| 222 |
| 223 |
| 224 |
| 225 |
| 226 |
| 227 |
| 228 |
| 229 |
| 230 |
| 231 |
| 232 |
| 233 |
| 234 |
| 235 |
| 236 |
| 237 |
| 238 |
| 239 |
| 240 |
| 241 |
| 242 |
| 243 |
| 244 |
| 245 |
| 246 |
| 247 |
| 248 |
| 249 |
| 250 |
| 251 |
| 252 |
| 253 |
| 254 |
| 255 |
| 256 |
| 257 |
| 258 |
| 259 |
| 261 |
| 262 |
| 263 |
| 264 |
| 265 |
| 266 |
| 267 |
| 268 |
| 269 |
| 270 |
| 271 |
| 272 |
| 273 |
| 274 |
| 275 |
| 276 |
| 277 |
| 278 |
| 279 |
| 280 |
| 281 |
| 282 |
| 283 |
| 284 |
| 285 |
| 286 |
| 287 |
| 288 |
| 289 |
| 290 |
| 291 |
| 292 |
| 293 |
| 294 |
| 295 |
| 296 |
| 297 |
| 298 |
| 299 |
| 300 |
| 301 |
| 302 |
| 303 |
| 304 |
| 305 |
| 306 |
| 307 |
| 308 |
| 309 |
| 310 |
| 311 |
| 312 |
| 313 |
| 314 |
| 315 |
| 316 |
| 317 |
| 318 |
| 319 |
| 320 |
| 321 |
| 322 |
| 323 |
| 324 |
| 325 |
| 326 |
| 327 |
| 328 |
| 329 |
| 330 |
| 331 |
| 332 |
| 333 |
| 334 |
| 335 |
| 336 |
| 337 |
| 338 |
| 339 |
| 340 |
| 341 |
| 342 |
| 343 |
| 344 |
| 345 |
| 346 |
| 347 |
| 348 |
| 349 |
| 350 |
| 351 |
| 352 |
| 353 |
| 354 |
| 355 |
| 356 |
| 357 |
| 358 |
| 359 |
| 360 |
| 361 |
| 362 |
| 363 |
| 364 |
| 365 |
| 366 |
| 367 |
| 368 |
| 369 |
| 370 |
| 371 |
| 372 |
| 373 |
| 374 |
| 375 |
| 376 |
| 377 |
| 378 |
| 379 |
| 380 |
| 381 |
| 382 |
| 383 |
| 384 |
| 385 |
| 386 |
| 387 |
| 388 |
| 389 |
| 391 |
| 392 |
| 393 |
| 394 |
| 395 |
| 396 |
| 397 |
| 398 |
| 399 |
| 400 |
| 401 |
| 402 |
| 403 |
| 404 |
| 405 |
| 406 |
| 407 |
| 408 |
| 409 |
| 410 |
| 411 |
| 412 |
| 413 |
| 414 |
| 415 |
| 416 |
| 417 |
| 418 |
| 419 |
| 420 |
| 421 |
| 422 |
| 423 |
| 424 |
| 425 |
| 426 |
| 427 |
| 428 |
| 429 |
| 430 |
| 431 |
| 432 |
| 433 |
| 434 |
| 435 |
| 436 |
| 437 |
| 438 |
| 439 |
| 440 |
| 441 |
| 442 |
| 443 |
| 444 |
| 445 |
| 446 |
| 447 |
| 448 |
| 449 |
| 450 |
| 451 |
| 452 |
| 453 |
| 454 |
| 455 |
| 456 |
| 457 |
| 458 |
| 459 |
| 460 |
| 461 |
| 462 |
| 463 |
| 464 |
| 465 |
| 466 |
| 467 |
| 468 |
| 469 |
| 470 |
| 471 |
| 472 |
| 473 |
| 474 |
| 475 |
| 476 |
| 477 |
| 478 |
| 479 |
| 480 |
| 481 |
| 482 |
| 483 |
| 484 |
| 485 |
| 486 |
| 487 |
| 488 |
| 489 |
| 490 |
| 491 |
| 492 |
| 493 |
| 494 |
| 495 |
| 496 |
| 497 |
| 498 |
| 499 |
| 500 |
| 501 |
| 502 |
| 503 |
| 504 |
+-----+
501 rows in set (0.00 sec)
```

rows in replica:
```
mysql> select id from t_repro;
+-----+
| id  |
+-----+
|   1 |
|   2 |
|   3 |
|   4 |
|   5 |
|   6 |
|   7 |
|   8 |
|   9 |
|  10 |
|  11 |
|  12 |
|  13 |
|  15 |
|  16 |
|  17 |
|  18 |
|  19 |
|  20 |
|  21 |
|  22 |
|  23 |
|  24 |
|  25 |
|  26 |
|  27 |
|  29 |
|  30 |
|  31 |
|  32 |
|  33 |
|  34 |
|  35 |
|  36 |
|  37 |
|  38 |
|  39 |
|  40 |
|  41 |
|  43 |
|  44 |
|  45 |
|  46 |
|  47 |
|  48 |
|  49 |
|  50 |
|  51 |
|  52 |
|  53 |
|  54 |
|  55 |
|  57 |
|  58 |
|  59 |
|  60 |
|  61 |
|  62 |
|  63 |
|  64 |
|  65 |
|  66 |
|  67 |
|  68 |
|  69 |
|  71 |
|  72 |
|  73 |
|  74 |
|  75 |
|  76 |
|  77 |
|  78 |
|  79 |
|  80 |
|  81 |
|  82 |
|  83 |
|  85 |
|  86 |
|  87 |
|  88 |
|  89 |
|  90 |
|  91 |
|  92 |
|  93 |
|  94 |
|  95 |
|  96 |
|  97 |
|  99 |
| 100 |
| 101 |
| 102 |
| 103 |
| 104 |
| 105 |
| 106 |
| 107 |
| 108 |
| 109 |
| 110 |
| 111 |
| 113 |
| 114 |
| 115 |
| 116 |
| 117 |
| 118 |
| 119 |
| 120 |
| 121 |
| 122 |
| 123 |
| 124 |
| 125 |
| 127 |
| 128 |
| 129 |
| 130 |
| 131 |
| 132 |
| 133 |
| 134 |
| 135 |
| 136 |
| 137 |
| 138 |
| 139 |
| 141 |
| 142 |
| 143 |
| 144 |
| 145 |
| 146 |
| 147 |
| 148 |
| 149 |
| 150 |
| 151 |
| 152 |
| 153 |
| 155 |
| 156 |
| 157 |
| 158 |
| 159 |
| 160 |
| 161 |
| 162 |
| 163 |
| 164 |
| 165 |
| 166 |
| 167 |
| 169 |
| 170 |
| 171 |
| 172 |
| 173 |
| 174 |
| 175 |
| 176 |
| 177 |
| 178 |
| 179 |
| 180 |
| 181 |
| 183 |
| 184 |
| 185 |
| 186 |
| 187 |
| 188 |
| 189 |
| 190 |
| 191 |
| 192 |
| 193 |
| 194 |
| 195 |
| 197 |
| 198 |
| 199 |
| 200 |
| 201 |
| 202 |
| 203 |
| 204 |
| 205 |
| 206 |
| 207 |
| 208 |
| 209 |
| 211 |
| 212 |
| 213 |
| 214 |
| 215 |
| 216 |
| 217 |
| 218 |
| 219 |
| 220 |
| 221 |
| 222 |
| 223 |
| 225 |
| 226 |
| 227 |
| 228 |
| 229 |
| 230 |
| 231 |
| 232 |
| 233 |
| 234 |
| 235 |
| 236 |
| 237 |
| 239 |
| 240 |
| 241 |
| 242 |
| 243 |
| 244 |
| 245 |
| 246 |
| 247 |
| 248 |
| 249 |
| 250 |
| 251 |
| 253 |
| 254 |
| 255 |
| 256 |
| 257 |
| 258 |
| 259 |
| 260 |
| 261 |
| 262 |
| 263 |
| 264 |
| 265 |
| 267 |
| 268 |
| 269 |
| 270 |
| 271 |
| 272 |
| 273 |
| 274 |
| 275 |
| 276 |
| 277 |
| 278 |
| 279 |
| 281 |
| 282 |
| 283 |
| 284 |
| 285 |
| 286 |
| 287 |
| 288 |
| 289 |
| 290 |
| 291 |
| 292 |
| 293 |
| 295 |
| 296 |
| 297 |
| 298 |
| 299 |
| 300 |
| 301 |
| 302 |
| 303 |
| 304 |
| 305 |
| 306 |
| 307 |
| 309 |
| 310 |
| 311 |
| 312 |
| 313 |
| 314 |
| 315 |
| 316 |
| 317 |
| 318 |
| 319 |
| 320 |
| 321 |
| 323 |
| 324 |
| 325 |
| 326 |
| 327 |
| 328 |
| 329 |
| 330 |
| 331 |
| 332 |
| 333 |
| 334 |
| 335 |
| 337 |
| 338 |
| 339 |
| 340 |
| 341 |
| 342 |
| 343 |
| 344 |
| 345 |
| 346 |
| 347 |
| 348 |
| 349 |
| 351 |
| 352 |
| 353 |
| 354 |
| 355 |
| 356 |
| 357 |
| 358 |
| 359 |
| 360 |
| 361 |
| 362 |
| 363 |
| 365 |
| 366 |
| 367 |
| 368 |
| 369 |
| 370 |
| 371 |
| 372 |
| 373 |
| 374 |
| 375 |
| 376 |
| 377 |
| 379 |
| 380 |
| 381 |
| 382 |
| 383 |
| 384 |
| 385 |
| 386 |
| 387 |
| 388 |
| 389 |
| 390 |
| 391 |
| 393 |
| 394 |
| 395 |
| 396 |
| 397 |
| 398 |
| 399 |
| 400 |
| 401 |
| 402 |
| 403 |
| 404 |
| 405 |
| 407 |
| 408 |
| 409 |
| 410 |
| 411 |
| 412 |
| 413 |
| 414 |
| 415 |
| 416 |
| 417 |
| 418 |
| 419 |
| 421 |
| 422 |
| 423 |
| 424 |
| 425 |
| 426 |
| 427 |
| 428 |
| 429 |
| 430 |
| 431 |
| 432 |
| 433 |
| 435 |
| 436 |
| 437 |
| 438 |
| 439 |
| 440 |
| 441 |
| 442 |
| 443 |
| 444 |
| 445 |
| 446 |
| 447 |
| 449 |
| 450 |
| 451 |
| 452 |
| 453 |
| 454 |
| 455 |
| 456 |
| 457 |
| 458 |
| 459 |
| 460 |
| 461 |
| 463 |
| 464 |
| 465 |
| 466 |
| 467 |
| 468 |
| 469 |
| 470 |
| 471 |
| 472 |
| 473 |
| 474 |
| 475 |
| 477 |
| 478 |
| 479 |
| 480 |
| 481 |
| 482 |
| 483 |
| 484 |
| 485 |
| 486 |
| 487 |
| 488 |
| 489 |
| 491 |
| 492 |
| 493 |
| 494 |
| 495 |
| 496 |
| 497 |
| 498 |
| 499 |
| 500 |
| 501 |
| 502 |
| 503 |
| 504 |
| 505 |
| 506 |
| 507 |
| 508 |
| 509 |
| 510 |
| 511 |
| 512 |
| 513 |
| 514 |
| 515 |
| 516 |
| 517 |
| 519 |
| 520 |
| 521 |
| 522 |
| 523 |
| 524 |
| 525 |
| 526 |
| 527 |
| 528 |
| 529 |
| 530 |
| 531 |
| 533 |
| 534 |
| 535 |
| 536 |
| 537 |
| 538 |
+-----+
501 rows in set (0.01 sec)
```

4. Insert a new row in primary and replication error.

mysql> INSERT INTO t_repro (a, payload) VALUES (42, 'hello_tdsql');

Suggested fix:
none

Adding a data row to the key sort buffer can fail if the buffer reaches its size limit, resulting in the row being rebuilt once the buffer is flushed. Each row construction triggers a sequence number increment.

```

ddl::Context::handle_autoinc(ddl::Context * const this, const dtuple_t * dtuple, mem_heap_t * heap) (.../mysql-server/storage/innobase/ddl/ddl0ctx.cc:268)
ddl::Row::build(ddl::Row * const this, ddl::Context & ctx, dict_index_t * index, mem_heap_t * heap, size_t type) (.../mysql-server/storage/innobase/ddl/ddl0ddl.cc:518)
ddl::Parallel_cursor::copy_row(ddl::Parallel_cursor * const this, size_t thread_id, ddl::Row & row) (.../mysql-server/storage/innobase/ddl/ddl0par-scan.cc:412)
ddl::Builder::bulk_add_row(ddl::Cursor&, ddl::Row&, unsigned long, std::function<dberr_t ()>&&)(ddl::Builder * const this, ddl::Cursor & cursor, ddl::Row & row, size_t thread_id, ddl::Latch_release && latch_release) (.../mysql-server/storage/innobase/ddl/ddl0builder.cc:1492)
ddl::Builder::add_row(ddl::Cursor&, ddl::Row&, unsigned long, std::function<dberr_t ()>&&)(ddl::Builder * const this, ddl::Cursor & cursor, ddl::Row & row, size_t thread_id, ddl::Latch_release && latch_release) (.../mysql-server/storage/innobase/ddl/ddl0builder.cc:1613)
...

```

Yes，if master-slave have different buffer size, that would case a replication error.