[ZBX-13498] mysql db may get duplicated index c_problem_2 for "problem" table Created: 2018 Feb 19  Updated: 2024 Apr 10  Resolved: 2018 Feb 23

Status: Closed
Project: ZABBIX BUGS AND ISSUES
Component/s: Proxy (P), Server (S)
Affects Version/s: None
Fix Version/s: 3.4.8rc1, 4.0.0alpha4, 4.0 (plan)

Type: Problem report Priority: Major
Reporter: Oleksii Zagorskyi Assignee: Vladislavs Sokurenko
Resolution: Fixed Votes: 0
Labels: dbpatches, mysql, upgrade
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Team: Team A
Sprint: Sprint 28
Story Points: 0.5

 Description   

This is followup of ZBX-12758, you can find more details in last comment.
The change in 3.4.6 has added an index create index problem_3 on problem (r_eventid), schema.tmpl was updated too.

Background:
"problem" table, introduced in 3.2, has c_problem_2 constraint, which automatically adds an `c_problem_2` (`r_eventid`) index on MySQL db backend.

What interesting I discovered is:
if zabbix database was existing from a moment, when "problem" table was created together with the constraint, then the existing index c_problem_2 will be magically "renamed" to problem_2, which is attempted to be created.

But, if once after upgrade to 3.2, I had to dump and restore my zabbix database - the attempt to create the new index will be done "as is", and I'll get two identical indexes - c_problem_2 and problem_2.
This is not very good.

It's suggested to improve the patch logic to in a way to rename existing index.
And probably remove the c_problem_2 index for those, who already upgraded to 3.4.6.

 Comments   
Comment by Vladislavs Sokurenko [ 2018 Feb 20 ]

Thanks! I can reproduce the issue when using mysqldump.

Comment by Vladislavs Sokurenko [ 2018 Feb 20 ]

Fixed in development branch:
svn://svn.zabbix.com/branches/dev/ZBX-13498

Comment by Oleksii Zagorskyi [ 2018 Feb 20 ]

So, you have decided to just drop possibly existing duplicated index.

What is not very good, that it's possible that on upgrade (even minor one), the index may be created and then dropped. The table may be not so small in production, so those operations may take some time.
This is not very good for minor/optional upgrades, as they are declared to be very quick, without any changes in schema.

Don't you want to implement existing index renaming?

vso It is done as MySQL manual states

MySQL requires indexes on foreign keys and referenced keys so that foreign key checks can be fast and not require a table scan. In the referencing table, there must be an index where the foreign key columns are listed as the first columns in the same order. Such an index is created on the referencing table automatically if it does not exist. This index might be silently dropped later, if you create another index that can be used to enforce the foreign key constraint. index_name, if given, is used as described previously.

unfortunately patch is already out, not sure if it is wise to change old patch.
Also on top of that all other databases are missing index so it will be slow operation for them anyway, don't see why there must be exception for MySQL

Comment by Andris Mednis [ 2018 Feb 21 ]

Successfully tested.

Comment by Vladislavs Sokurenko [ 2018 Feb 22 ]

Fixed in:

  • pre-3.4.8rc1 r77930
  • pre-4.0.0alpha4(trunk) r77931
Generated at Fri Apr 26 11:06:52 EEST 2024 using Jira 9.12.4#9120004-sha1:625303b708afdb767e17cb2838290c41888e9ff0.