Sample Header Ad - 728x90

mariadb: why does altering a table column from unsigned to signed int (or the reverse) require the COPY algorithm?

0 votes
0 answers
100 views
I have database table with a tens of millions of records. For some reason the auto increment id column is a signed int and I tried to change it to an unsigned int. Goes without saying but I checked there are no negative values in that column. I tried to use the following statement:
ALTER TABLE table 
MODIFY COLUMN id INT UNSIGNED AUTO_INCREMENT, 
ALGORITHM INSTANT, 
LOCK = NONE;
which was unsuccessful due to it requiring the COPY algorithm
ALGORITHM=INSTANT is not supported. Reason: Cannot change column type. Try 
ALGORITHM=COPY
Since the table is quite large (~15gb data + 15gb indexes) the COPY algorithm would take a long time during which the table would lock. I would assume, that only meta data would be modified, since the storage should not change at all. Both have the same amount of bytes (4) and for positive values the bit-representation should be identical. Everything should be identical. What's the reason why COPY is the only supported algorithm? Is there any way to achieve this without copying the whole table?
Asked by jtopt (1 rep)
Dec 10, 2024, 03:45 PM
Last activity: Dec 10, 2024, 03:45 PM