Pt-online-schema-change id ID

MySQL & MariaDB Percona Server for MySQL 5.7
1

Hello.
I used the pt-online-schema-change tool and did not understand one logic.
There is a test database mybases, there is a test table sbtest1, the table has an id column (special attention that it is in lower case) and a primary key in it.
I make the following request:
pt-online-schema-change --alter "MODIFY COLUMN ID BIGINT AUTO_INCREMENT" D=mybases,t=sbtest1 --print --execute --critical-load Threads_running=10 --max-lag=200s.
When the command is applied, the column name changes from id to ID (upper case). Is this normal behavior?

2

Your command says to change id to ID. You need to modify your --alter to make the column “id” and not “ID”

Also, please understand that switching to bigint is rarely needed. You should be currently using int unsigned which allows for values over 4 billion. Are you near this cap for row count on this table?

1 Like
3

Hi @ArtemGob .

As Matthew mentioned, it’s expected and it’s not related to how pt-osc works.

I replicated your scenario with direct ALTERs:

mysql> SHOW CREATE TABLE test.tbl\G
*************************** 1. row ***************************
       Table: tbl
Create Table: CREATE TABLE `tbl` (
  `id` tinyint NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.05 sec)

mysql> ALTER TABLE test.tbl MODIFY COLUMN ID BIGINT AUTO_INCREMENT;
Query OK, 0 rows affected (0.36 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> SHOW CREATE TABLE test.tbl\G
*************************** 1. row ***************************
       Table: tbl
Create Table: CREATE TABLE `tbl` (
  `ID` bigint NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

As you can see, the results are the same when performing the command directly; I hope this helps you enlighten the expected behaviour.

Best,
Mauricio.

1 Like