[MDEV-29135] Replication SQL error when type text is converted to text compressed Created: 2022-07-19  Updated: 2022-09-25  Resolved: 2022-09-25

Status: Closed
Project: MariaDB Server
Component/s: Replication
Affects Version/s: 10.5.16
Fix Version/s: N/A

Type: Bug Priority: Major
Reporter: VAROQUI Stephane Assignee: Unassigned
Resolution: Incomplete Votes: 0
Labels: None


 Description   

We wanted to rolling upgrade a chat message table, so we took a first replica and change our table definition with the following SQL:

alter table chat_messages MODIFY COLUMN `text` text COMPRESSED COLLATE utf8mb4_unicode_ci DEFAULT NULL;

Once done the replication was broken with the following error message 

Last_SQL_Error: Column 6 of table 'XXX.chat_messages' cannot be converted from type 'blob' to type 'text /!100301 COMPRESSED/'

*Note to reproduce replication settings *

  • binlog_format : ROW
  • Using_Gtid: Current_Pos
  • Parallel_Mode: optimistic

*Note to reproduction table definition *

  • The column origin column type COLUMN `text` text COLLATE utf8mb4_unicode_ci DEFAULT NULL;
  • The table is range partitioned by on timestamp created_at column
  • The table is engine innodb

 Comments   
Comment by VAROQUI Stephane [ 2022-07-19 ]

We tried changing ALL_NON_LOSSY conversion type without success

Comment by VAROQUI Stephane [ 2022-07-20 ]

Because COMPRESSED is loosing one Byte in data length we tried to use an upper data type size but without success as well

Column 6 of table 'XXX.chat_messages' cannot be converted from type 'blob' to type 'longtext /!100301 COMPRESSED/'

Comment by Angelique Sklavounos (Inactive) [ 2022-07-25 ]

Hi stephane@skysql.com, thank you for the report.

We tried changing ALL_NON_LOSSY conversion type without success

Was the replica SQL thread still stopped with error 1677 when set @@global.slave_type_conversions='ALL_NON_LOSSY'; was performed?

Comment by VAROQUI Stephane [ 2022-07-25 ]

Yes but i think in non loosy it is not a bug as when row image max out the
data type size it should break, but here it was failing what ever the blob
content size and in any forced loosy replication setup we workaround the
issue using percona toolkit on the master as we do not yet run 10.10 that
have online DDL using data copy Can t wait for it

Le lun. 25 juil. 2022 à 16:18, Angelique Sklavounos (Jira) <jira@mariadb.org>

Comment by Angelique Sklavounos (Inactive) [ 2022-08-25 ]

Hi stephane@skysql.com

We tried changing ALL_NON_LOSSY conversion type without success

If I follow what I believe are the steps in the Description, I also see the error. However, I am able to set the replica to ALL_NON_LOSSY, then STOP, RESET, START. If I alter the table again on the replica, then I do not get an error.

stephane@skysql.com could you please confirm if the following matches what you did?

Steps to error
Primary

create table a (chat text COLLATE utf8mb4_unicode_ci DEFAULT NULL, timestamp TIMESTAMP NOT NULL) engine=innodb PARTITION BY RANGE (UNIX_TIMESTAMP(timestamp))
 
(
 
	PARTITION p0 VALUES LESS THAN (UNIX_TIMESTAMP('2022-08-25 12:40:00')),
 
	PARTITION p1 VALUES LESS THAN (UNIX_TIMESTAMP('2022-09-01 00:00:00')),
 
);
 
insert into a values("chat1",NOW());

Replica:

alter table a MODIFY COLUMN chat text COMPRESSED COLLATE utf8mb4_unicode_ci DEFAULT NULL;

Error not seen yet.

Primary:

insert into a values("chat2”,NOW());

Error 1677 seen on replica; SQL thread stopped.

Steps to remove error
Replica:

set @@global.slave_type_conversions='ALL_NON_LOSSY’;
 
stop replica; reset replica; start replica;
 
alter table a MODIFY COLUMN chat text COMPRESSED COLLATE utf8mb4_unicode_ci DEFAULT NULL;

Primary:

 insert into a values("chat3”,NOW());

No error on replica. Replica table has new value. Replica table definition (SHOW CREATE TABLE) still mentions compressed

Generated at Thu Feb 08 10:06:10 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.