MySQL Bugs: #70915: Index creation on the table truncates data 90 %

Bug #70915	Index creation on the table truncates data 90 %
Submitted:	15 Nov 2013 2:12	Modified:	15 Dec 2013 19:36
Reporter:	Oksana Yakusheva	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server	Severity:	S1 (Critical)
Version:	5.5	OS:	Linux
Assigned to:		CPU Architecture:	Any
Tags:	Data Integrity, indexes

Description:
We created a reporting table by selecting as:
*** query:
CREATE TABLE v_report_trk_field (UNIQUE(measure_inst_id,measure_phase))
AS
   SELECT   miv.measure_inst_id, miv.measure_phase,
                  MAX(CASE WHEN miv.tracking_fld_defn_id = '1' THEN miv.double_value ELSE NULL END) mtf1 ,
         MAX(CASE WHEN miv.tracking_fld_defn_id = '2' THEN miv.double_value ELSE NULL END) mtf2 ,
          MAX(CASE WHEN miv.tracking_fld_defn_id = '3' THEN miv.double_value ELSE NULL END) mtf3 ,
          MAX(CASE WHEN miv.tracking_fld_defn_id = '4' THEN miv.double_value ELSE NULL END) mtf4 ,
          MAX(CASE WHEN miv.tracking_fld_defn_id = '5' THEN miv.double_value ELSE NULL END) mtf5 ,
          MAX(CASE WHEN miv.tracking_fld_defn_id = '6' THEN miv.double_value ELSE NULL END) mtf6 ,
          MAX(CASE WHEN miv.tracking_fld_defn_id = '7' THEN miv.double_value ELSE NULL END) mtf7 
 FROM meas_inst_vals miv
WHERE miv.tracking_fld_defn_id IS NOT NULL
   GROUP BY miv.measure_inst_id, miv.measure_phase;

After that we applied indexes:
CREATE UNIQUE INDEX V_REPORT_TRK_FIELD_IDX1 ON V_REPORT_TRK_FIELD (MEASURE_INST_ID, MEASURE_PHASE);

After the execution of the create statement - table had 122 000 rows,
After the indexes were applied , and statement create indexes finished the table had only  6000 rows(90% of data actually gone from the table)
Table didn't have any duplicates , the combination of MEASURE_INST_ID, MEASURE_PHASE was unique.
The same queries/creations, etc were working in 5.1 and 5.0.
Please help!!!

How to repeat:
Just try to simulate data and run queries.

Suggested fix:
Make it work.
I don't think creation of the indexes should lead to data lost. If there is a problem with data, index shouldn't be created and error out.

Sorry create statement is wrong, I added unique for work around and it works.
The statement without it that's what had problems:

CREATE TABLE v_report_trk_field (UNIQUE(measure_inst_id,measure_phase))
AS
   SELECT   miv.measure_inst_id, miv.measure_phase,
                  MAX(CASE WHEN miv.tracking_fld_defn_id = '1' THEN miv.double_value ELSE NULL END) mtf1 ,
         MAX(CASE WHEN miv.tracking_fld_defn_id = '2' THEN miv.double_value ELSE NULL END) mtf2 ,
          MAX(CASE WHEN miv.tracking_fld_defn_id = '3' THEN miv.double_value ELSE NULL END) mtf3 ,
          MAX(CASE WHEN miv.tracking_fld_defn_id = '4' THEN miv.double_value ELSE NULL END) mtf4 ,
          MAX(CASE WHEN miv.tracking_fld_defn_id = '5' THEN miv.double_value ELSE NULL END) mtf5 ,
          MAX(CASE WHEN miv.tracking_fld_defn_id = '6' THEN miv.double_value ELSE NULL END) mtf6 ,
          MAX(CASE WHEN miv.tracking_fld_defn_id = '7' THEN miv.double_value ELSE NULL END) mtf7 ,
          MAX(CASE WHEN miv.tracking_fld_defn_id = '8' THEN miv.double_value ELSE NULL END) mtf8 ,
          MAX(CASE WHEN miv.tracking_fld_defn_id = '9' THEN miv.int_value ELSE NULL END) mtf9 ,
          MAX(CASE WHEN miv.tracking_fld_defn_id = '10' THEN miv.double_value ELSE NULL END) mtf10 ,
          MAX(CASE WHEN miv.tracking_fld_defn_id = '11' THEN miv.double_value ELSE NULL END) mtf11

FROM meas_inst_vals miv
      WHERE miv.tracking_fld_defn_id IS NOT NULL
   GROUP BY miv.measure_inst_id, miv.measure_phase;

CREATE UNIQUE INDEX V_REPORT_TRK_FIELD_IDX1 ON V_REPORT_TRK_FIELD (MEASURE_INST_ID, MEASURE_PHASE);

Thank you for the report.

Which exact minor version of MYSQL server do you use?

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".