| Bug #120104 | Modify VARCHAR column leads to unexpected rebuild of table | ||
|---|---|---|---|
| Submitted: | 19 Mar 7:36 | Modified: | 30 Mar 7:32 |
| Reporter: | George Ma (OCA) | Email Updates: | |
| Status: | Open | Impact on me: | |
| Category: | MySQL Server: DDL | Severity: | S3 (Non-critical) |
| Version: | 8.4.7 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
[19 Mar 7:36]
George Ma
[19 Mar 7:38]
George Ma
If make a little change on the table structure, SQL2 will not rebuild. create table test_001( id int auto_increment key, c1 varchar(20), c2 varchar(20), c3 int, key idx_1(c1(10)), key idx_2(c2(10)) ) engine = innodb; SQL2: alter table test_001 modify c1 varchar(30) after id, modify c2 varchar(30) after c1; // NO rebuild
[30 Mar 7:32]
George Ma
# Analysis
## Test Cases
### Case 1: Normal Index, No AFTER Clause (No Rebuild)
```sql
CREATE TABLE test_001(
id int auto_increment key,
c1 varchar(20),
c2 varchar(20),
c3 int,
key idx_1(c1),
key idx_2(c2)
) engine = innodb;
ALTER TABLE test_001 MODIFY c1 varchar(30), MODIFY c2 varchar(30);
```
**Handler Flags:** `8796093562880` (0x80000084000)
**Bits Set:**
- Bit 14: `ALTER_COLUMN_EQUAL_PACK_LENGTH`
- Bit 19: `ALTER_COLUMN_DEFAULT`
- Bit 43: `ALTER_COLUMN_INDEX_LENGTH`
**Result:** No rebuild required
---
### Case 2: Normal Index + AFTER Clause (Requires Rebuild)
```sql
CREATE TABLE test_001(
id int auto_increment key,
c1 varchar(20),
c2 varchar(20),
c3 int,
key idx_1(c1),
key idx_2(c2)
) engine = innodb;
ALTER TABLE test_001 MODIFY c1 varchar(30) AFTER id, MODIFY c2 varchar(30) AFTER c1;
```
**Handler Flags:** `540675` (0x84003)
**Bits Set:**
- Bit 0: `ADD_INDEX`
- Bit 1: `DROP_INDEX`
- Bit 14: `ALTER_COLUMN_EQUAL_PACK_LENGTH`
- Bit 19: `ALTER_COLUMN_DEFAULT`
**Result:** Requires rebuild
---
### Case 3: Prefix Index + AFTER Clause (No Rebuild)
```sql
CREATE TABLE test_001(
id int auto_increment key,
c1 varchar(20),
c2 varchar(20),
c3 int,
key idx_1(c1(10)),
key idx_2(c2(10))
) engine = innodb;
ALTER TABLE test_001 MODIFY c1 varchar(30) AFTER id, MODIFY c2 varchar(30) AFTER c1;
```
**Handler Flags:** `540672` (0x84000)
**Bits Set:**
- Bit 14: `ALTER_COLUMN_EQUAL_PACK_LENGTH`
- Bit 19: `ALTER_COLUMN_DEFAULT`
**Result:** No rebuild required
---
## Root Cause Analysis
### Key Code Path
The critical logic is in `sql/sql_table.cc` lines 12101-12118:
```cpp
/*
If there is a change in index length due to column expansion
like varchar(X) changed to varchar(X + N) and has a compatible
packed data representation, we mark it for fast/INPLACE change
in index definition. Some engines like InnoDB supports INPLACE
alter for such cases.
In other cases, key definition has changed if we are using a
different field or if the used key part length is different, or
key part direction has changed.
*/
if (key_part->length != new_part->length &&
ha_alter_info->alter_info->flags == Alter_info::ALTER_CHANGE_COLUMN &&
(key_part->field->is_equal(new_field) == IS_EQUAL_PACK_LENGTH)) {
ha_alter_info->handler_flags |=
Alter_inplace_info::ALTER_COLUMN_INDEX_LENGTH;
} else if (key_part->length != new_part->length)
return true;
```
### Why Case 1 Doesn't Rebuild
**Conditions:**
- Normal index: `idx_1(c1)` - key part length = 20 (full column)
- Column change: `varchar(20)` → `varchar(30)`
- No AFTER clause
**Execution Flow:**
1. `key_part->length` (20) != `new_part->length` (30) → **true**
2. `ALTER_CHANGE_COLUMN` flag is set → **true**
3. `is_equal()` returns `IS_EQUAL_PACK_LENGTH` → **true**
4. Sets `ALTER_COLUMN_INDEX_LENGTH` (bit 43)
5. Index can be adjusted in-place without rebuild
### Why Case 2 Rebuilds
**Conditions:**
- Normal index: `idx_1(c1)` - key part length = 20
- Column change: `varchar(20)` → `varchar(30)`
- **With AFTER clause**
**Execution Flow:**
1. The AFTER clause triggers field remove/re-insert in `prepare_fields_and_keys()`
2. This affects `new_field->field` pointer setup
3. Causes `has_index_def_changed()` to return `true`
4. Triggers `ADD_INDEX` + `DROP_INDEX` flags
5. **Requires table rebuild**
**Note:** Even though the column position doesn't actually change (c1 is already after id), the AFTER clause triggers a different code path.
### Why Case 3 Doesn't Rebuild
**Conditions:**
- **Prefix index:** `idx_1(c1(10))` - key part length = 10 (not full column)
- Column change: `varchar(20)` → `varchar(30)`
- With AFTER clause
**Execution Flow:**
1. `key_part->length` (10) == `new_part->length` (10) → **false**
- Prefix length stays the same regardless of column expansion
2. Skips the first condition
3. Second condition `key_part->length != new_part->length` is also **false**
4. Does NOT return `true`
5. No `ADD_INDEX` triggered
6. Only metadata flags set: `ALTER_COLUMN_EQUAL_PACK_LENGTH` + `ALTER_COLUMN_DEFAULT`
7. **No rebuild required**
---
## Summary Table
| Case | Index Type | AFTER Clause | Key Part Length Change | Flags | Rebuild? |
|------|-----------|--------------|------------------------|-------|----------|
| 1 | Normal `idx(c1)` | No | 20→30 | `ALTER_COLUMN_INDEX_LENGTH` | **No** |
| 2 | Normal `idx(c1)` | Yes | 20→30 | `ADD_INDEX` + `DROP_INDEX` | **Yes** |
| 3 | Prefix `idx(c1(10))` | Yes | 10→10 (no change) | Metadata only | **No** |
---
## Key Insights
1. **Prefix indexes are special:** When using prefix indexes like `c1(10)`, the key part length doesn't change even when the column expands, avoiding the rebuild trigger.
2. **AFTER clause side effects:** Even when column position doesn't actually change, the AFTER clause triggers field re-processing that can cause index rebuild.
3. **ALTER_COLUMN_INDEX_LENGTH vs ADD_INDEX:**
- `ALTER_COLUMN_INDEX_LENGTH` (bit 43) allows in-place index adjustment
- `ADD_INDEX` + `DROP_INDEX` requires full table rebuild
---
## Flag Definitions (from handler.h)
| Bit | Flag Name | Description |
|-----|-----------|-------------|
| 0 | `ADD_INDEX` | Add non-unique, non-primary index |
| 1 | `DROP_INDEX` | Drop non-unique, non-primary index |
| 14 | `ALTER_COLUMN_EQUAL_PACK_LENGTH` | Column type change with compatible pack length |
| 19 | `ALTER_COLUMN_DEFAULT` | Default value changed |
| 43 | `ALTER_COLUMN_INDEX_LENGTH` | Index length change (in-place) |
