[MDEV-20152] Generated column's index not updated from RBR events (both Galera and async replication) Created: 2019-07-24  Updated: 2023-03-02

Status: Open
Project: MariaDB Server
Component/s: Galera, Virtual Columns
Affects Version/s: 10.2.25
Fix Version/s: 10.2

Type: Bug Priority: Minor
Reporter: Michaël de groot Assignee: Nikita Malyavin
Resolution: Unresolved Votes: 1
Labels: None

Issue Links:
Relates
relates to MDEV-17890 Server crash on DELETE with YEAR fiel... Closed

 Description   

Hi,

In my particular case I tested this from a Galera cluster to an asynchronous slave, but I don't think the Galera part has anything to do with it.

On the cluster, I have a schema. On the slave I have the same schema but with a denormalization of the week number:

CREATE TABLE `example` (
 
  `id` char(36) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '',
 
  `user_id` char(36) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '',
 
  `event_code` int(11) NOT NULL,
 
  `local_date_time` datetime NOT NULL,
 
  `utc_date_time` datetime NOT NULL,
 
  `collection_id` char(36) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
 
PRIMARY KEY(id)
 
);

On the slave I add a virtual column:

ALTER TABLE example 
	ADD `local_year_weeknum` int(11) GENERATED ALWAYS AS (year(`local_date_time`) * 100 + week(`local_date_time`, 0)) VIRTUAL,
 
	ADD KEY `michael_user_yearweeknum_collection_eventcode` (`user_id`, `local_year_weeknum`, `collection_id`, `event_code`);

I can find data that is already present in the example table, but when data comes in through replication it does not appear in the index.
I can, however, select the same data using the clustered index.

Thanks in advance!
Michaël

 Comments   
Comment by Michaël de groot [ 2019-08-05 ]

Hi,

For clarification, the process to reproduce:
1. Create the test systems (In my case a 3 node galera cluster with a slave running from node 2)
2. Insert some data in the table - insert into example values (1,2,3,now(),now(),4));
3. Run the alter table on the slave
4. Insert some more data in the cluster (in my case we are writing to node 1) - insert into example values (2,3,4,now(),now(),5));
5. Observe the data does not exist in the secondary index, compare:
select * from example ;
and
select user_id, local_year_weeknum from example force index (michael_user_yearweeknum_collection_eventcode) ;

Comment by Michaël de groot [ 2019-08-07 ]

Some more information: We have tested this and it seams to occur with any node receiving data through Row Based Asynchronous Replication. It occurs in the newest 10.3.13 and 10.4.7 versions.

It does not occur with SBR, so this could be a work-around. Also It does NOT occur with STORED virtual columns, which makes a decent work-around also for Galera Cluster.

Comment by Michaël de groot [ 2019-09-25 ]

I think these 2 issues might be related, or this one might be duplicate to the other one, as we got similar entries in the log:

140238793991936 [ERROR] InnoDB: Unable to find a record to delete-mark
 
Sep 24 11:36:26 node1 mysqld[5256]: InnoDB: tuple DATA TUPLE: 5 fields;
 
Sep 24 11:36:26 node1 mysqld[5256]: 0: len 36; hex 35366538366634342d666635612d313165362d623035342d303035303536393266376365; asc 56e86f44-ff5a-11e6-b054-00505692f7ce;;
 
syslog.7.gz:Sep 17 13:06:44 node1 mysqld[7990]: 2019-09-17 13:06:44 139871097554688 [ERROR] InnoDB: Record in index `michael_userId_yearweeknum_collection_eventcode` of table `database`.`example` was not found on update: TUPLE (info_bits=0, 5 fields): {[36]8d2743de-2fd2-11e7-b054-00505692f7ce(0x386 .....

Comment by Michaël de groot [ 2023-03-02 ]

hey @Sergei Golubchik and @Elena Stepenova any news about this issue? Is it possible that it was fixed in the efforts of another issue?

I cannot imagine nobody else ran into this issue in 3 years (unless virtual columns aren't really used).

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