Percona MySQL 5.5唯一密钥是重复的

2023-08-21 19:13:58

我完全没有想法,所以也许其他人可以回答我的问题.
我们有Percona的MySQL 5.5服务器,流量很大.该应用程序使用PHP并始终写入master.我们同时有4个奴隶,我们只读过这些奴隶.基本上它是标准的主从配置.
上周发生了所有从站上的复制被破坏,所以我检查了数据库有什么问题.
我发现的基本上是我的问题,这怎么可能发生：
其中一个表的唯一键列(不是主键)在2行中具有相同的值.我试图找出这种情况是否发生过一次,但不是.它只发生过一次,但我会理解为什么或如何发生这种情况.
为了更好地理解,我们的数据库中有一些真实数据：

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)

我甚至无法重现,所以如果有人对这种情况有一个解释,那真的很酷.

先感谢您,
塔马斯

UPDATE
根据要求,这是整理的结果：

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)

解决方法:

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

对于这个bug,没有修复,也没有可重现的测试用例.它仅在生产环境中被观察到.

描述的模式是：

>将值插入具有唯一约束的列.
>删除该行.
>两个并发会话INSERT新行,其值与已删除行中的值相同.
>两个会话都提交,并且它们的两个INSERT都成功.

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

如果您尝试插入与删除标记相同的值,则只删除其删除标记,并将该值与您插入的新行相关联.

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

对不起,还没有解决这个bug的问题.我鼓励您登录启动板并注册此错误会影响您.如果您可以发布有关在您的环境中如何发生错误的其他信息,那么这也会有所帮助.最重要的是,如果您可以帮助创建可重现的测试用例！

此外,这可能与针对MySQL股票的错误有关：Bug #69979 columns with unique key gets intermittent duplicate values!虽然有些细节不同.由于开发人员显然认为在InnoDB的MVCC架构中,某些冲突发生并根据竞争条件产生无效结果,因此MySQL错误被关闭为“不是错误”.恕我直言,这应该让他们获得响亮的“WTF？！”

码农公寓

相关文章