主页 / dba / 问题 / 71522
Accepted
Tamas Szasz
Asked: 2014-07-15 23:33:00 +0800 CST

Percona MySQL 5.5 唯一键重复

  • 772

我完全没有想法，所以也许其他人可以回答我的问题。我们有一台来自 Percona 的 MySQL 5.5 服务器，流量很大。该应用程序是在 PHP 中编写的，始终掌握。我们同时有 4 个奴隶，我们只从中读取。基本上就是标准的主从配置。上周发生了所有从机上的复制都被破坏了，所以我检查了数据库有什么问题。我发现的基本上是我的问题，这是怎么发生的：其中一个表中的唯一键列（不是主键）在两行中具有相同的值。我试图找出这种情况是否不止一次发生，但没有。它只发生过一次，但我会理解为什么或怎么会发生这种情况。为了更好地理解，这里有一些来自我们数据库的真实数据：

show create table registeredUsers;
| registeredUsers | CREATE TABLE `registeredUsers` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `userId` varchar(32) NOT NULL,
  `client` varchar(200) NOT NULL,
  `osVersion` varchar(10) NOT NULL,
  `deviceGroup` varchar(50) NOT NULL,
  `registrationDate` datetime NOT NULL,
  `lastAction` datetime NOT NULL,
  `cultureLanguage` varchar(2) NOT NULL,
  `cultureRegion` varchar(2) NOT NULL,
  `cultureCode` varchar(5) NOT NULL DEFAULT 'de-de',
  `lastPush` datetime DEFAULT NULL,
  `pushToken` mediumtext,
  `permaToken` varchar(74) DEFAULT NULL,
  `accessCount` int(11) NOT NULL DEFAULT '0',
  `access` varchar(1) NOT NULL DEFAULT '1',
  `provider` varchar(255) NOT NULL,
  `providerTld` varchar(5) NOT NULL,
  `environment` tinyint(1) DEFAULT '0',
  `udidMd5` varchar(32) NOT NULL,
  `development` tinyint(1) DEFAULT '0',
  PRIMARY KEY (`id`),
  UNIQUE KEY `userId_2` (`userId`),
  UNIQUE KEY `permaAccessToken_2` (`permaToken`),
  KEY `client` (`client`),
  KEY `lastAction` (`lastAction`),
  KEY `deviceGroup` (`deviceGroup`),
  KEY `osVersion` (`osVersion`),
  KEY `cultureCode` (`cultureCode`),
  KEY `udidMd5` (`udidMd5`)
) ENGINE=InnoDB AUTO_INCREMENT=38466378 DEFAULT CHARSET=utf8 |

有问题的列是 userId，它是唯一的。这是一个查询，显示我们有 2 行具有相同的值：

mysql> select userId, count(userId) as ct from registeredUsers group by userId having ct;
+----------------------------------+----+
| userId                           | ct |
+----------------------------------+----+
| 748ec561dbc733452bfd697076787ef9 |  2 |
+----------------------------------+----+
1 row in set (3.53 sec)

我什至无法重现，所以如果有人对这种情况有解释，那就太好了。

提前谢谢你，塔马斯

更新 按照这里的要求是整理的结果：

mysql> SHOW FULL COLUMNS FROM registeredUsers;
+------------------+---------------------+-----------------+------+-----+---------+----------------+---------------------------------+---------+
| Field            | Type                | Collation       | Null | Key | Default | Extra          | Privileges                      | Comment |
+------------------+---------------------+-----------------+------+-----+---------+----------------+---------------------------------+---------+
| id               | bigint(20) unsigned | NULL            | NO   | PRI | NULL    | auto_increment | select,insert,update,references |         |
| userId           | varchar(32)         | utf8_general_ci | NO   | UNI | NULL    |                | select,insert,update,references |         |
| client           | varchar(200)        | utf8_general_ci | NO   | MUL | NULL    |                | select,insert,update,references |         |
| osVersion        | varchar(10)         | utf8_general_ci | NO   | MUL | NULL    |                | select,insert,update,references |         |
| deviceGroup      | varchar(50)         | utf8_general_ci | NO   | MUL | NULL    |                | select,insert,update,references |         |
| registrationDate | datetime            | NULL            | NO   |     | NULL    |                | select,insert,update,references |         |
| lastAction       | datetime            | NULL            | NO   | MUL | NULL    |                | select,insert,update,references |         |
| cultureLanguage  | varchar(2)          | utf8_general_ci | NO   |     | NULL    |                | select,insert,update,references |         |
| cultureRegion    | varchar(2)          | utf8_general_ci | NO   |     | NULL    |                | select,insert,update,references |         |
| cultureCode      | varchar(5)          | utf8_general_ci | NO   | MUL | de-de   |                | select,insert,update,references |         |
| lastPush         | datetime            | NULL            | YES  |     | NULL    |                | select,insert,update,references |         |
| pushToken        | mediumtext          | utf8_general_ci | YES  |     | NULL    |                | select,insert,update,references |         |
| permaToken       | varchar(74)         | utf8_general_ci | YES  | UNI | NULL    |                | select,insert,update,references |         |
| accessCount      | int(11)             | NULL            | NO   |     | 0       |                | select,insert,update,references |         |
| access           | varchar(1)          | utf8_general_ci | NO   |     | 1       |                | select,insert,update,references |         |
| provider         | varchar(255)        | utf8_general_ci | NO   |     | NULL    |                | select,insert,update,references |         |
| providerTld      | varchar(5)          | utf8_general_ci | NO   |     | NULL    |                | select,insert,update,references |         |
| environment      | tinyint(1)          | NULL            | YES  |     | 0       |                | select,insert,update,references |         |
| udidMd5          | varchar(32)         | utf8_general_ci | NO   | MUL | NULL    |                | select,insert,update,references |         |
| development      | tinyint(1)          | NULL            | YES  |     | 0       |                | select,insert,update,references |         |
+------------------+---------------------+-----------------+------+-----+---------+----------------+---------------------------------+---------+
20 rows in set (0.00 sec)

更新 2

mysql> SELECT id FROM registeredUsers WHERE userid = '748ec561dbc733452bfd697076787ef9' ORDER BY id DESC ;
+----------+
| id       |
+----------+
| 38245456 |
+----------+
1 row in set (0.00 sec)

mysql> select userId from registeredUsers where id in (38245456, 38245457);
+----------------------------------+
| userId                           |
+----------------------------------+
| 748ec561dbc733452bfd697076787ef9 |
| 748ec561dbc733452bfd697076787ef9 |
+----------------------------------+
2 rows in set (0.00 sec)

mysql> select id, userId from registeredUsers where id in (38245456, 38245457);
+----------+----------------------------------+
| id       | userId                           |
+----------+----------------------------------+
| 38245456 | 748ec561dbc733452bfd697076787ef9 |
| 38245457 | 748ec561dbc733452bfd697076787ef9 |
+----------+----------------------------------+
2 rows in set (0.00 sec)

mysql> select id, userId from registeredUsers where userId = '748ec561dbc733452bfd697076787ef9';
+----------+----------------------------------+
| id       | userId                           |
+----------+----------------------------------+
| 38245456 | 748ec561dbc733452bfd697076787ef9 |
+----------+----------------------------------+
1 row in set (0.00 sec)

更新 3 可以肯定的是，这两个字符串是相同的，这里是一个返回所有 2 行的查询。（感谢它的建议）

SELECT id FROM registeredUsers WHERE userid >= '748ec561dbc733452bfd697076787ef9' LIMIT 2;
+----------+
| id       |
+----------+
| 38245456 |
| 38245457 |
+----------+
2 rows in set (0.00 sec)
mysql php

1 个回答

  1. Best Answer

    看起来您可能遇到了针对 Percona Server 5.5 记录的错误：
    Concurrent duplicate inserts can violate a unique key constraint in InnoDB tables。

    目前还没有针对此错误的修复程序，也没有可重现的测试用例。它仅在生产环境中观察到。

    描述的模式是：

    1. 将值插入到具有唯一约束的列中。
    2. 删除该行。
    3. 两个并发会话 INSERT 具有与已删除行中相同值的新行。
    4. 两个会话都提交，并且它们的两个 INSERT 都成功。

    根本原因可能与已删除行的未完成清除有关。在 InnoDB 中，从索引中删除条目是一个多步骤过程。首先，条目被“删除标记”，将条目留在索引中以推迟从索引中的物理删除。然后，清除线程执行最终删除，其中可能包括 B 树的一些重新平衡。

    如果您尝试插入与带有删除标记的值相同的值，它只会删除其删除标记，并将该值与您插入的新行相关联。

    根据错误报告，似乎虽然删除的条目只是删除标记，但尚未清除，但两个并发会话可以插入相同的值。这可能一直发生在非唯一索引上，这没问题。但是，如果索引是唯一索引，这当然是一个问题。

    抱歉，目前还没有解决此错误的方法。我鼓励您登录启动板并注册此错误会影响您。如果您可以发布有关错误如何在您的环境中发生的其他信息，那也会有所帮助。最重要的是，如果您可以帮助创建可重现的测试用例！

    此外，这可能与针对普通 MySQL 的错误有关：Bug #69979 columns with unique key gets intermittent duplicate values！虽然有些细节不同。那个 MySQL 错误被关闭为“不是错误”，因为开发人员显然得出结论，在 InnoDB 的 MVCC 架构中，发生一些冲突并根据竞争条件产生无效结果是可以接受的。恕我直言，这应该为他们赢得响亮的“WTF？！”

    • 7

相关问题