| Bug #12069 | UNIQUE Index fails on addition of 12th column | ||
|---|---|---|---|
| Submitted: | 20 Jul 2005 19:11 | Modified: | 27 Jul 2005 23:01 |
| Reporter: | Terry Trewitt | Email Updates: | |
| Status: | Can't repeat | Impact on me: | |
| Category: | MySQL Server | Severity: | S3 (Non-critical) |
| Version: | 3.23.56 | OS: | Linux (Linux, Windows) |
| Assigned to: | CPU Architecture: | Any | |
[27 Jul 2005 23:01]
MySQL Verification Team
I was unable to repeat with server from the current BK source: ERROR 1062: Duplicate entry '2-Trip Deposit-2-2-22.50-23.50-1234-John-10-2005-12-12-2005-12-12-2005-12-12' for key 2 mysql> show index from xoops_c_payments; +------------------+------------+--------------+--------------+----------------+-----------+-------------+----------+--------+---------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Comment | +------------------+------------+--------------+--------------+----------------+-----------+-------------+----------+--------+---------+ | xoops_c_payments | 0 | PRIMARY | 1 | pid | A | 1 | NULL | NULL | | | xoops_c_payments | 0 | Uniqueness | 1 | uid | A | NULL | NULL | NULL | | | xoops_c_payments | 0 | Uniqueness | 2 | payment_type | A | NULL | NULL | NULL | | | xoops_c_payments | 0 | Uniqueness | 3 | trip_to | A | NULL | NULL | NULL | | | xoops_c_payments | 0 | Uniqueness | 4 | trip_from | A | NULL | NULL | NULL | | | xoops_c_payments | 0 | Uniqueness | 5 | payment_amount | A | NULL | NULL | NULL | | | xoops_c_payments | 0 | Uniqueness | 6 | check_amount | A | NULL | NULL | NULL | | | xoops_c_payments | 0 | Uniqueness | 7 | check_number | A | NULL | NULL | NULL | | | xoops_c_payments | 0 | Uniqueness | 8 | check_name | A | NULL | NULL | NULL | | | xoops_c_payments | 0 | Uniqueness | 9 | days | A | NULL | NULL | NULL | | | xoops_c_payments | 0 | Uniqueness | 10 | post_date | A | NULL | NULL | NULL | | | xoops_c_payments | 0 | Uniqueness | 11 | receive_date | A | NULL | NULL | NULL | | | xoops_c_payments | 0 | Uniqueness | 12 | issue_date | A | NULL | NULL | NULL | | | xoops_c_payments | 1 | trip_to | 1 | trip_to | A | NULL | NULL | NULL | | | xoops_c_payments | 1 | payment_type | 1 | payment_type | A | NULL | NULL | NULL | | | xoops_c_payments | 1 | deposit_date | 1 | deposit_date | A | NULL | NULL | NULL | | | xoops_c_payments | 1 | deposit_ID | 1 | depositor | A | NULL | NULL | NULL | | +------------------+------------+--------------+--------------+----------------+-----------+-------------+----------+--------+---------+ 17 rows in set (0.00 sec) mysql> select version(); +-------------------+ | version() | +-------------------+ | 3.23.59-debug-log | +-------------------+ 1 row in set (0.00 sec)
Description: Adding a 12th column to a UNIQUE index causes it to cease preventing insertion of duplicate rows. Repeatable in version 3.23.56 on Linux 7.2, and version 3.23.49 on Windows 2000 (desktop) Client is phpMyAdmin 2.6.2 PHP Version 4.3.11 How to repeat: (1) Create this table: CREATE TABLE `xoops_c_payments` ( `pid` int(5) unsigned NOT NULL auto_increment, `uid` int(8) NOT NULL default '0', `payment_type` enum('Trip Deposit','Trip Half','Trip Final','Activity Fee','Transfer','Refund','Cancellation Fee','Lifts','Rentals','Lessons','Picnic','Shuttle','Side Trip','Single Room','Expense','Check Request','Other','Merchandise','Membership','Badge','Advertising','Refund from Vendor','Interest','Bookkeeping') default NULL, `trip_to` int(11) default '0', `trip_from` int(11) default '0', `post_date` date default NULL, `receive_date` date default NULL, `deposit_date` datetime default NULL, `issue_date` date default NULL, `depositor` varchar(25) default NULL, `payment_amount` decimal(8,2) default NULL, `penalty_amount` decimal(5,2) default NULL, `check_amount` decimal(8,2) default NULL, `check_number` varchar(12) default NULL, `check_name` varchar(60) default NULL, `season` varchar(9) NOT NULL default '', `days` varchar(10) NOT NULL default '0', `side_trip` varchar(20) default NULL, `comments` text, `history` text, `date_tempvar` datetime default NULL, `refund_requested` enum('No','Yes') default 'No', `refund_need_date` date default NULL, `expense_type` enum('Activity Fees','Airline','Award','Badges','Bank Fees','Board Training','Catering','Directory Postage','Directory Printing','Entertainment','Food & Beverage','Ground Transportation','Lessons','Lift Tickets','Lodging','Luggage Assistance','Luggage Tags','Newsletter Postage','Newsletter Printing','Newsletter Supplies','Marketing Postage','Marketing Printing','Office Supplies','Penalty','PO Box','Postage','Printing','Refund','Rentals','Room Rental','Side Trips','Software','Storage','Taxes','TD Reimbursement','TD Training','Tour Operator','Trip Cash','Trip Packages','TSC Activity','TSC Dues','TSC Participation','TSC Travel','Web Domain Registration','Web Site','Whistles','Other Expense','Voided Check') default NULL, `requester_id` int(8) default NULL, `request_type` enum('Refund','Expense') default NULL, `html` text, `account` enum('LSS','Supplier','Budget','Checking Balance','Money Market Balance','Transfer MM to Checking') NOT NULL default 'LSS', `status` enum('Pending Receipt','Received','Deposited','Cancelled','Transferred','Pending Payment','Paid') default NULL, `check_disposition` enum('Mail to Payee','Mail to Requester','Call Requester') default NULL, `check_cleared_date` date default NULL, PRIMARY KEY (`pid`), UNIQUE KEY `Uniqueness` (`uid`,`payment_type`,`trip_to`,`trip_from`,`payment_amount`,`check_amount`,`check_number`,`check_name`,`days`,`post_date`,`receive_date`,`issue_date`), KEY `trip_to` (`trip_to`), KEY `payment_type` (`payment_type`), KEY `deposit_date` (`deposit_date`), KEY `deposit_ID` (`depositor`) ) TYPE=MyISAM (2) Insert duplicate rows (3) Remove the 12th column from the index: ALTER TABLE `xoops_c_payments` DROP INDEX `Uniqueness` , ADD UNIQUE `Uniqueness` ( `uid` , `payment_type` , `trip_to` , `trip_from` , `payment_amount` , `check_amount` , `check_number` , `check_name` , `days` , `post_date` , `receive_date` ) (4) above update fails until duplicate rows are removed (5) Duplicate rows can no longer be inserted Suggested fix: (1) Document limitations on useage of UNIQUE indices -- or -- (2) Remove the limitation