Mysql Duplicate entry error during new column addition on production database



  • I have a MySQL table that has 95929357 records, below is the table schema for the same

    CREATE TABLE `Friends` (
      `id` bigint(20) NOT NULL AUTO_INCREMENT,
      `createdTime` datetime DEFAULT NULL,
      `friendUserId` bigint(20) DEFAULT NULL,
      `friendshipStatus` int(11) DEFAULT NULL,
      `remarks` varchar(255) DEFAULT NULL,
      `updatedTime` datetime DEFAULT NULL,
      `userId` bigint(20) DEFAULT NULL,
      PRIMARY KEY (`id`),
      UNIQUE KEY `userId` (`userId`,`friendUserId`),
      KEY `createdTime` (`createdTime`)
    ) ENGINE=InnoDB AUTO_INCREMENT=1321597934 DEFAULT CHARSET=latin1
    

    I have two questions to ask

    Question 1# If it has only 95929357 records, how does the AUTO_INCREMENT increase to 1321597934(Note- entries are inserted by code with default value 0). I also noticed, there is a gap in id(exp- from id 50001124 to new id 50001393)

    | 50001122 | 2021-09-19 03:33:06 |    116176872 |                0 | NULL    | 2021-09-19 03:33:06 | 103802246 |
    | 50001123 | 2021-09-19 03:33:06 |    114361519 |                0 | NULL    | 2021-09-19 03:33:06 | 116176872 |
    | 50001124 | 2021-09-19 03:33:06 |    116176872 |                0 | NULL    | 2021-09-19 03:33:06 | 114361519 |
    | 50001393 | 2021-09-19 03:33:07 |    109233877 |                0 | NULL    | 2021-09-19 03:33:07 | 125715813 |
    | 50001394 | 2021-09-19 03:33:07 |    125715813 |                0 | NULL    | 2021-09-19 03:33:07 | 109233877 |
    | 50001395 | 2021-09-19 03:33:07 |    105588884 |                0 | NULL    | 2021-09-19 03:33:07 | 125715813 |
    | 50001396 | 2021-09-19 03:33:07 |    125715813 |                0 | NULL    | 2021-09-19 03:33:07 | 105588884 |
    | 50001397 | 2021-09-19 03:33:07 |    106198652 |                0 | NULL    | 2021-09-19 03:33:07 | 125715813 |
    | 50001398 | 2021-09-19 03:33:07 |    125715813 |                0 | NULL    | 2021-09-19 03:33:07 | 106198652 |
    

    Question 2# I was trying to add new column with below command

    ALTER TABLE Friends ADD COLUMN source INT(11) DEFAULT '0';
    

    but getting below error while table alter

    ERROR 1062 (23000): Duplicate entry '126750770-127846472' for key 'userId'
    

    UPDATE -

    Select result of the above key

    mysql> select * from Friends where userId = 126750770 AND friendUserId = 127846472;
    +------------+---------------------+--------------+------------------+---------+---------------------+-----------+--------+
    | id         | createdTime         | friendUserId | friendshipStatus | remarks | updatedTime         | userId    | source |
    +------------+---------------------+--------------+------------------+---------+---------------------+-----------+--------+
    | 1120753287 | 2022-01-27 12:36:12 |    127846472 |                0 | NULL    | 2022-01-27 12:36:12 | 126750770 |      0 |
    +------------+---------------------+--------------+------------------+---------+---------------------+-----------+--------+
    1 row in set (0.00 sec)
    

    mysql> select * from Friends where userId = 127846472 AND friendUserId = 126750770;
    +------------+---------------------+--------------+------------------+---------+---------------------+-----------+--------+
    | id | createdTime | friendUserId | friendshipStatus | remarks | updatedTime | userId | source |
    +------------+---------------------+--------------+------------------+---------+---------------------+-----------+--------+
    | 1120753288 | 2022-01-27 12:36:12 | 126750770 | 0 | NULL | 2022-01-27 12:36:12 | 127846472 | 0 |
    +------------+---------------------+--------------+------------------+---------+---------------------+-----------+--------+
    1 row in set (0.00 sec)



  • There are several causes of gaps in AUTO_INCREMENT: REPLACE, INSERT IGNORE, ROLLBACK, IODKU, and others. Please present some of your insert-like statements; I will provide further details. I assume you don't know what happened about 2021-09-19 03:33:07.

    Duplicate entry '126750770-127846472' should not have happened. This is worrisome as if there was a crash that was not adequately recovered from (unusual). Does "268" ring any bells? (That is how many ids are missing there.

    Did you convert from MyISAM to InnoDB at some point in the past?

    I suggest you do some more investigating, starting with

    SELECT * FROM Friends
        WHERE userId = 126750770
          AND friend = 127846472;
    

    That may be only one of several "dup keys".

    Let us know what you find out.

    Additional Suggestion

    If the combination (userId, friendUserId) is unique, why not get rid of id and promote the UNIQUE to PRIMARY KEY(userId, friendUserId)? That would save space and speed up some queries. There is probably no use for id by any other table.

    ALTER

    A long-running ALTER on the table may have blocked INSERTs long enough for them to timeout. This might lead to them being ROLLBACK'd after the ids had already been assigned.




Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2