0

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)
1
  • 2
    (1) The gap in autoincremented column values is a norma. AI guarantees uniqueness and not adjacency. Do not pay attention to this. (2) Shown query cannot result in shown error message directly. Check your data consistency.
    – Akina
    Commented Feb 22, 2022 at 7:17

1 Answer 1

1

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.

6
  • I'm using insert ignore into Friends(userId,createdTime,friendUserId,friendshipStatus,updatedTime) values (?,?,?,?,?) query for insertion. I'm able to select from the above query(In reverse order as well, since I store the values in both orders) mysql> select * from Friends where userId = 126750770 AND friendUserId = 127846472; 1 row in set (0.00 sec) mysql> select * from Friends where userId = 127846472 AND friendUserId = 126750770; 1 row in set (0.00 sec) Note: removed the result to save characters Commented Feb 24, 2022 at 6:39
  • I added to my Answer
    – Rick James
    Commented Feb 24, 2022 at 18:05
  • @ankit.vishen - add the result to your Question.
    – Rick James
    Commented Feb 24, 2022 at 20:11
  • result added, please check the update section and I appreciate your suggestion, will try it out. Commented Feb 25, 2022 at 5:23
  • Is it possible to get the error because of ongoing queries? since I was doing alter on the production database. Commented Feb 25, 2022 at 5:27

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.