1

I want to execute a alter command on a table, create table and alter table commands are as below.

CREATE TABLE `xyz` (
      `entity_id` int(11) NOT NULL AUTO_INCREMENT,
      `masterform_id` int(11) NOT NULL DEFAULT '1',
      `app_status` varchar(500) DEFAULT NULL,
      `NegativeMarks` decimal(15,5) DEFAULT NULL,
      `ActualScore` decimal(15,5) DEFAULT NULL,
      `RawScore` decimal(15,5) DEFAULT NULL,
      `PANProratedMarks` decimal(15,5) DEFAULT NULL,
      `PANNormalizedMarks` decimal(15,5) DEFAULT NULL,
      `RRBZoneNormalizedMarks` decimal(15,5) DEFAULT NULL,
      `RRBZoneProratedMarks` decimal(15,5) DEFAULT NULL,  
      `RRBZoneAllocationTempStorage` varchar(200) DEFAULT NULL,
      `GraduatePercentage` decimal(15,5) DEFAULT NULL,
        `PANAllocationTempStorage` varchar(1500) DEFAULT NULL,
      PRIMARY KEY (`entity_id`),
      UNIQUE KEY `app_seq_no` (`app_seq_no`),
      UNIQUE KEY `ParticipantID` (`ParticipantID`),
      UNIQUE KEY `RegistrationNo` (`RegistrationNo`),  
      KEY `idx_PANNormalizedMarks` (`PANNormalizedMarks`),  
      KEY `idx_RRBZoneNormalizedMarks` (`RRBZoneNormalizedMarks`)
    ) ENGINE=InnoDB AUTO_INCREMENT=273252 DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC


Alter table xyz
    modify column ActualScore decimal(15,5),
    modify column app_status varchar(500),
    add index CalculatedCategory(CalculatedCategory),
    modify column GraduatePercentage decimal(15,5),
    modify column NegativeMarks decimal(15,5),
    modify column PANAllocationTempStorage varchar(1500),
    modify column PANNormalizedMarks decimal(15,5),
    modify column PANProratedMarks decimal(15,5),
    drop index idx_ParticipantID,
    add unique ParticipantID(ParticipantID),
    modify column RawScore decimal(15,5),
    drop index idx_RegistrationNo,
    add unique RegistrationNo(RegistrationNo),
    modify column RRBZoneNormalizedMarks decimal(15,5),
    modify column RRBZoneProratedMarks decimal(15,5);

I am getting this error:

SQLError:Can't DROP 'idx_ParticipantID'; check that column/key exists

But I am getting this same 104 times. Could you please let me know why am I getting this error 104 times in log? If the index doesnt exist it should just give the error once, please correct me if I am wrong.

Karan mehta
  • 89
  • 2
  • 10
  • If i try your create Statement i get error message `Key column 'app_seq_no' doesn't exist in table` – Jens Mar 14 '17 at 07:26
  • @Jens you can ignore the keys, I have edited it in my script. Please let me know why the error message occurs so many times. – Karan mehta Mar 14 '17 at 07:36
  • Because there is no index with this name. Also you should provide an [mcve] – Jens Mar 14 '17 at 07:38

1 Answers1

0

After creating table xyz, you are supposed to create the wanted indexes, one of them is idx_ParticipantID (an index on the column ParticipantID). But you didn't create that one, that's why you can't drop it.

But you can ignore those errors, they have no effect on your database. I advise you to read here some about indexes.

Community
  • 1
  • 1
Houssam Badri
  • 2,441
  • 3
  • 29
  • 60
  • Agreed with you, but why does the error message occur multiple times. Ideally, it should occur only once, right? – Karan mehta Mar 14 '17 at 07:38
  • try removing ROW_FORMAT=DYNAMIC because only for my_isam and clean the log file and retry – Houssam Badri Mar 14 '17 at 07:50
  • removing ROW_FORMAT=DYNAMIC didnt work..Also I tried it again and got this error only once. Does large number of data in the table have any impact on this? – Karan mehta Mar 14 '17 at 14:12
  • No, data has no effect on that. But I think this may be due to the concatenation on the log file. So don't worry, keep moving:) – Houssam Badri Mar 14 '17 at 17:50