MySQL Forums
Forum List  »  Partitioning

Mysql 8.0.22 - Indexes on virtual columns do not update
Posted by: Vasilis Kioustelidis
Date: December 07, 2020 08:47AM

I have an original replication setup consisting of two OLTP mysql instances (5.7.31 on Centos 7) and two OLAP (5.7.30 on Solaris). The schemas on the OLAP servers differ from OLTP in two significant ways:
1. several tables have computed (i.e. columns) columns that are build by applying:
* a cast to date on a VARCHAR timestamp
* a hashing function on VARCHAR columns
2. the tables that have the above enhancements have been provided with indexes on the computed columns
The original replication setup has been working perfectly for several months withou issue.
I decided to add three new OLAP devices in an effort to remove the solaris nodes AND upgrade to the newer version of mysql.

The three new instances use mysql 8.0.14 (on Centos 7) and get replication from one the OLAP nodes. This new setup was also working as expected and everything was being updated as normal.

The next step was to decouple the three new OLAP nodes from the old OLAP nodes and have them receive replication directly from the OLTP nodes. I first upgraded the new nodes to 8.0.22 without issue and I switched their master configuration to the OLTP servers. The row updates started coming through as expected, the computed columns were complete however the indexes on the computed columns stopped updating.

I can retrieve data using the indexes up to the point when I made the change but since then the indexed receive NO updates. I also tried to create copies of the indexes (different name) and again I noticed that the new indexes were complete up to the creation point-in-time but they received no updates after that.

Vasilis Kioustelidis

Technical Director

EDPS S.A.
+30-210-8993660, Mob: +30-6944709598,Fax:+30-210-8993662

Email:kioustelidis@edps.gr URL:http://www.edps.gr

Options: ReplyQuote


Subject
Views
Written By
Posted
Mysql 8.0.22 - Indexes on virtual columns do not update
215
December 07, 2020 08:47AM


Sorry, only registered users may post in this forum.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.