Починка ключей у таблиц WordPress при коллизиях

Апрель 2024
Пн	Вт	Ср	Чт	Пт	Сб	Вс
1	2	3	4	5	6	7
8	9	10	11	12	13	14
15	16	17	18	19	20	21
22	23	24	25	26	27	28
29	30

19.07.15
12:14
5273
0

При починке таблиц после потери автоинкремента переопределение полей идентификаторов может привести к коллизиям. Выглядит это так:

mysql> ALTER TABLE wp_comments CHANGE comment_ID 
    -> `comment_ID` bigint(20) unsigned NOT NULL AUTO_INCREMENT PRIMARY KEY;
ERROR 1062 (23000): ALTER TABLE causes auto_increment resequencing, resulting in duplicate
entry '1' for key 'PRIMARY'

mysql> ALTER TABLE wp_comments CHANGE comment_ID

-> `comment_ID` bigint(20) unsigned NOT NULL AUTO_INCREMENT PRIMARY KEY;

ERROR 1062 (23000): ALTER TABLE causes auto_increment resequencing, resulting in duplicate

entry '1' for key 'PRIMARY'

Перенумеровать таблицу в этом случае можно обходным путём:

удаляются ключи, требующие уникальности каких-либо полей:

ALTER TABLE tbl DROP PRIMARY KEY; ALTER TABLE tbl DROP INDEX unique_column;

1
2

ALTER TABLE tbl DROP PRIMARY KEY;
ALTER TABLE tbl DROP INDEX unique_column;
отключается автозамена нулевых значений поля при добавлении к нему автоинкремента — чтобы избежать коллизий:

SET @@SESSION.sql_mode = CONCAT(@@sql_mode, ',NO_AUTO_VALUE_ON_ZERO');

1

SET @@SESSION.sql_mode = CONCAT(@@sql_mode, ',NO_AUTO_VALUE_ON_ZERO');
для поля идентификатора создаётся неуникальный ключ, затем оно переопределяется в автоинкрементное:

ALTER TABLE tbl ADD KEY (id); ALTER TABLE tbl CHANGE id `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT;

1
2

ALTER TABLE tbl ADD KEY (id);
ALTER TABLE tbl CHANGE id `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT;
включается автозамена нулевых значений — чтобы пронумеровать строки:

SET @@SESSION.sql_mode = REPLACE(@@sql_mode, 'NO_AUTO_VALUE_ON_ZERO', '');

1

SET @@SESSION.sql_mode = REPLACE(@@sql_mode, 'NO_AUTO_VALUE_ON_ZERO', '');
содержимое строк с нулевым идентификатором добавляется в эту же таблицу, после чего сами строки удаляются:

INSERT INTO tbl SELECT * FROM tbl WHERE id = 0; DELETE FROM tbl WHERE id = 0;

1
2

INSERT INTO tbl SELECT * FROM tbl WHERE id = 0;
DELETE FROM tbl WHERE id = 0;
восстанавливаются уникальные ключи, создаётся первичный ключ:

ALTER TABLE tbl ADD UNIQUE unique_column (unique_column); ALTER TABLE tbl ADD PRIMARY KEY (id);

1
2

ALTER TABLE tbl ADD UNIQUE unique_column (unique_column);
ALTER TABLE tbl ADD PRIMARY KEY (id);
удаляется неуникальный ключ, созданный прежде для поля идентификаторов:

ALTER TABLE tbl DROP INDEX id;

1

ALTER TABLE tbl DROP INDEX id;

Пример с wp_comments — ключи UNIQUE отсутствуют:

-- автоинкремент требует индексированного поля:
ALTER TABLE wp_comments ADD KEY (comment_ID)
-- отключение автозамены нулей при переопределении поля:
SET @@SESSION.sql_mode = CONCAT(@@sql_mode, ',NO_AUTO_VALUE_ON_ZERO');
ALTER TABLE wp_comments CHANGE comment_ID
	`comment_ID` bigint(20) unsigned NOT NULL AUTO_INCREMENT;
-- включение автозамены нулей - для замены их во вставляемых INSERT'ом строках;
SET @@SESSION.sql_mode = REPLACE(@@sql_mode, 'NO_AUTO_VALUE_ON_ZERO', '');
INSERT INTO wp_comments SELECT * FROM wp_comments WHERE comment_ID = 0;
DELETE FROM wp_comments WHERE comment_ID = 0;
-- восстановление ключей:
ALTER TABLE wp_comments ADD PRIMARY KEY (comment_ID);
ALTER TABLE wp_comments DROP INDEX comment_ID;

-- автоинкремент требует индексированного поля:

ALTER TABLE wp_comments ADD KEY (comment_ID)

-- отключение автозамены нулей при переопределении поля:

SET @@SESSION.sql_mode = CONCAT(@@sql_mode, ',NO_AUTO_VALUE_ON_ZERO');

ALTER TABLE wp_comments CHANGE comment_ID

`comment_ID` bigint(20) unsigned NOT NULL AUTO_INCREMENT;

-- включение автозамены нулей - для замены их во вставляемых INSERT'ом строках;

SET @@SESSION.sql_mode = REPLACE(@@sql_mode, 'NO_AUTO_VALUE_ON_ZERO', '');

INSERT INTO wp_comments SELECT * FROM wp_comments WHERE comment_ID = 0;

DELETE FROM wp_comments WHERE comment_ID = 0;

-- восстановление ключей:

ALTER TABLE wp_comments ADD PRIMARY KEY (comment_ID);

ALTER TABLE wp_comments DROP INDEX comment_ID;

Восстановление первичного ключа и автоинкремента в wp_options, поле option_name является уникальным:

-- удаление уникального индекса - чтобы не мешало вставлять значения:
ALTER TABLE wp_options DROP INDEX option_name;
ALTER TABLE wp_options ADD KEY (option_id); -- ключ для автоинкремента
SET @@SESSION.sql_mode=CONCAT(@@SESSION.sql_mode, ',NO_AUTO_VALUE_ON_ZERO');
ALTER TABLE wp_options CHANGE option_id
	`option_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT;
-- если значения автоинкрементного столбца не копируются, они
-- генерируются автоматически вне зависимости от @@sql_mode:
INSERT INTO wp_options (option_name, option_value, autoload)
    SELECT option_name, option_value, autoload FROM wp_options WHERE option_id = 0;
DELETE FROM wp_options WHERE option_id = 0;
-- восстановление ключей таблицы:
ALTER TABLE wp_options ADD UNIQUE option_name (option_name);
ALTER TABLE wp_options ADD PRIMARY KEY (option_id);
ALTER TABLE wp_options DROP INDEX option_id;

-- удаление уникального индекса - чтобы не мешало вставлять значения:

ALTER TABLE wp_options DROP INDEX option_name;

ALTER TABLE wp_options ADD KEY (option_id); -- ключ для автоинкремента

SET @@SESSION.sql_mode=CONCAT(@@SESSION.sql_mode, ',NO_AUTO_VALUE_ON_ZERO');

ALTER TABLE wp_options CHANGE option_id

`option_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT;

-- если значения автоинкрементного столбца не копируются, они

-- генерируются автоматически вне зависимости от @@sql_mode:

INSERT INTO wp_options (option_name, option_value, autoload)

SELECT option_name, option_value, autoload FROM wp_options WHERE option_id = 0;

DELETE FROM wp_options WHERE option_id = 0;

-- восстановление ключей таблицы:

ALTER TABLE wp_options ADD UNIQUE option_name (option_name);

ALTER TABLE wp_options ADD PRIMARY KEY (option_id);

ALTER TABLE wp_options DROP INDEX option_id;

Добавить комментарий Отменить ответ