Slave mysql error when running alter statement on master

MySQL & MariaDB
1

Hi everyone,
I have master slave model on mysql, I run alter statement on master. The alter statement works on master but slave gives error that it cannot convert data from enum to long. I don’t know why there is that error. Below is the schema of the table and my alter statement.

schema table:

CREATE TABLE `async_add_partition` (
  `impala_tbl_name` varchar(250) COLLATE utf8mb4_unicode_ci NOT NULL,
  `partition_value` varchar(250) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'data_date_key = 20230206, action_hour = 19',
  `partition_key` varchar(250) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'data_date_key, action_hour',
  `status` enum('NEW','DONE','REJECT','TABLE_NOT_FOUND') COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'NEW',
  `error_log` longtext COLLATE utf8mb4_unicode_ci COMMENT 'error log query',
  `created` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `modified` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`impala_tbl_name`,`partition_value`),
  KEY `status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

alter statement

ALTER TABLE bigdata.async_add_partition ADD trino_status ENUM('NEW','DONE','REJECT') CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT 'NEW' NOT NULL COMMENT 'Trang thai cache cua trino' AFTER `status`;

show slave status

Column 4 of table 'bigdata.async_add_partition' cannot be converted from type 'enum' to type 'longtext'
2

@Tai_Nguyen_Huu

Did you checked if there is not any mismatch in the table schema of both source and replica ? What exact database version you having for both ?

3

Run SHOW CREATE TABLE async_add_partition on both source, and replica. Verify schema matches before making any table alter.

Also, string-based PRIMARY KEYs are non-performant. You would do better to have an AUTO_INCREMENT INT primary key, and a UNIQUE(impala_tbl_name, partition_value) index on those columns.

4

this is schema of table on slave

CREATE TABLE bigdata.async_add_partition (
    impala_tbl_name   VARCHAR(250) COLLATE utf8mb4_unicode_ci NOT NULL,
    partition_value   VARCHAR(250) COLLATE utf8mb4_unicode_ci NOT NULL 
                     COMMENT 'data_date_key = 20230206, action_hour = 19',
    partition_key     VARCHAR(250) COLLATE utf8mb4_unicode_ci DEFAULT NULL 
                     COMMENT 'data_date_key, action_hour',
    status            ENUM('NEW','DONE','REJECT','TABLE_NOT_FOUND') 
                     COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'NEW',
    error_log         LONGTEXT COLLATE utf8mb4_unicode_ci 
                     COMMENT 'error log query',
    created           DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    modified          DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP 
                     ON UPDATE CURRENT_TIMESTAMP,
    
    PRIMARY KEY (impala_tbl_name, partition_value),
    KEY status (status)
) 
ENGINE=InnoDB 
DEFAULT CHARSET=utf8mb4 
COLLATE=utf8mb4_unicode_ci;
5

Can you add the column to the end of the table? (eg: remove the “AFTER”) Does this replicate?