Details
-
Bug
-
Status: Open (View Workflow)
-
Minor
-
Resolution: Unresolved
-
10.2.25
-
None
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
Attachments
Issue Links
- relates to
-
-
- Closed
-
Activity
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.
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 .....
|
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).
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) ;