I have a replicated merge tree table on two nodes
I have two columns as client_port and server_port Int16, I want to change it to UInt16. There is currently alot of data in this table (somewhere around 1.2TB with 20billion rows)
On running these queries one by one,
Alter table events_db.events_local_tables ON cluster click_cluster modify COLUMN client_port `UInt16`
Alter table events_db.events_local_tables ON cluster click_cluster modify COLUMN server_port `UInt16`
client_port was not straight forward, it did its changes on 1 node but for the other node we had to restart all zookeeper services manually. Eventually managed to change client_port but the same flow didn't work for server_port fails. It throws the following exception.
2021.09.20 20:24:16.761842 [ 38517 ] {806f2936-b668-41aa-ab7c-16c485393b17} <Debug> executeQuery: (from 10.120.1.122:54962) /*TABIX_QUERY_ID_uUtFccK4*/ Alter table events_db.events_local_tables ON cluster click_cluster modify COLUMN server_port `UInt16`
2021.09.20 20:24:16.761941 [ 38517 ] {806f2936-b668-41aa-ab7c-16c485393b17} <Trace> ContextAccess (default): Access granted: ALTER MODIFY COLUMN(server_port) ON events_db.events_local_tables
2021.09.20 20:24:16.773835 [ 38666 ] {} <Debug> DDLWorker: Processing task query-0000000482 (ALTER TABLE events_db.events_local_tables ON CLUSTER click_cluster MODIFY COLUMN `server_port` UInt16)
2021.09.20 20:24:16.779570 [ 38666 ] {} <Debug> DDLWorker: Executing query: ALTER TABLE events_db.events_local_tables MODIFY COLUMN `server_port` UInt16
2021.09.20 20:24:16.795296 [ 38666 ] {fb879340-1376-4af0-927b-4e8b73575841} <Debug> executeQuery: (from 0.0.0.0:0, user: ) /* ddl_entry=query-0000000482 */ ALTER TABLE events_db.events_local_tables MODIFY COLUMN `server_port` UInt16
2021.09.20 20:24:16.832801 [ 38666 ] {fb879340-1376-4af0-927b-4e8b73575841} <Error> executeQuery: Code: 517. DB::Exception: Metadata on replica is not up to date with common metadata in Zookeeper. Cannot alter. (CANNOT_ASSIGN_ALTER) (version 21.9.2.17 (official build)) (from 0.0.0.0:0) (in query: /* ddl_entry=query-0000000482 */ ALTER TABLE events_db.events_local_tables MODIFY COLUMN `server_port` UInt16), Stack trace (when copying this message, always include the lines below):
2021.09.20 20:24:16.833070 [ 38666 ] {fb879340-1376-4af0-927b-4e8b73575841} <Error> DDLWorker: Query ALTER TABLE events_db.events_local_tables MODIFY COLUMN `server_port` UInt16 wasn't finished successfully: Code: 517. DB::Exception: Metadata on replica is not up to date with common metadata in Zookeeper. Cannot alter. (CANNOT_ASSIGN_ALTER), Stack trace (when copying this message, always include the lines below):
2021.09.20 20:24:17.274010 [ 38666 ] {b001d5b7-d564-413d-82fc-001ec82906b6} <Debug> executeQuery: (from 0.0.0.0:0, user: ) /* ddl_entry=query-0000000482 */ ALTER TABLE events_db.events_local_tables MODIFY COLUMN `server_port` UInt16
2021.09.20 20:24:17.313598 [ 38666 ] {b001d5b7-d564-413d-82fc-001ec82906b6} <Error> executeQuery: Code: 517. DB::Exception: Metadata on replica is not up to date with common metadata in Zookeeper. Cannot alter. (CANNOT_ASSIGN_ALTER) (version 21.9.2.17 (official build)) (from 0.0.0.0:0) (in query: /* ddl_entry=query-0000000482 */ ALTER TABLE events_db.events_local_tables MODIFY COLUMN `server_port` UInt16), Stack trace (when copying this message, always include the lines below):
2021.09.20 20:24:17.313893 [ 38666 ] {b001d5b7-d564-413d-82fc-001ec82906b6} <Error> DDLWorker: Query ALTER TABLE events_db.events_local_tables MODIFY COLUMN `server_port` UInt16 wasn't finished successfully: Code: 517. DB::Exception: Metadata on replica is not up to date with common metadata in Zookeeper. Cannot alter. (CANNOT_ASSIGN_ALTER), Stack trace (when copying this message, always include the lines below):
2021.09.20 20:24:17.429943 [ 38666 ] {2db93657-4c33-4b9d-8817-653f9706a27c} <Debug> executeQuery: (from 0.0.0.0:0, user: ) /* ddl_entry=query-0000000482 */ ALTER TABLE events_db.events_local_tables MODIFY COLUMN `server_port` UInt16
2021.09.20 20:24:17.481732 [ 38666 ] {2db93657-4c33-4b9d-8817-653f9706a27c} <Error> executeQuery: Code: 517. DB::Exception: Metadata on replica is not up to date with common metadata in Zookeeper. Cannot alter. (CANNOT_ASSIGN_ALTER) (version 21.9.2.17 (official build)) (from 0.0.0.0:0) (in query: /* ddl_entry=query-0000000482 */ ALTER TABLE events_db.events_local_tables MODIFY COLUMN `server_port` UInt16), Stack trace (when copying this message, always include the lines below):
2021.09.20 20:24:17.482099 [ 38666 ] {2db93657-4c33-4b9d-8817-653f9706a27c} <Error> DDLWorker: Query ALTER TABLE events_db.events_local_tables MODIFY COLUMN `server_port` UInt16 wasn't finished successfully: Code: 517. DB::Exception: Metadata on replica is not up to date with common metadata in Zookeeper. Cannot alter. (CANNOT_ASSIGN_ALTER), Stack trace (when copying this message, always include the lines below):
2021.09.20 20:24:18.290940 [ 38517 ] {806f2936-b668-41aa-ab7c-16c485393b17} <Error> executeQuery: Code: 341. DB::Exception: There was an error on [10.120.1.117:9000]: Cannot execute replicated DDL query, maximum retries exceeded. (UNFINISHED) (version 21.9.2.17 (official build)) (from 10.120.1.122:54962) (in query: /*TABIX_QUERY_ID_uUtFccK4*/ Alter table events_db.events_local_tables ON cluster click_cluster modify COLUMN server_port `UInt16` ), Stack trace (when copying this message, always include the lines below):
If someone understands these logs let us know whats the straight forward flow for changing column types in Distributed tables
Unfortunately it's NOT documented but it's expected behaivor if you send several alter to different replicas (because you use on cluster )
On running these queries one by one,
Alter table events_db.events_local_tables ON cluster click_cluster modify COLUMN client_port `UInt16`
Alter table events_db.events_local_tables ON cluster click_cluster modify COLUMN server_port `UInt16`
Need to wait until the first alter completed at all replicas!!!! before sending the next.
Or send all alters in a single command
Alter table events_db.events_local_tables ON cluster click_cluster
modify COLUMN client_port `UInt16`,
modify COLUMN server_port `UInt16`;