WordPress база данных потеряла auto increment

У меня была похожая проблема, я её решил, и так как этот вопрос часто появляется в Google по моему запросу, возможно, это поможет другим.

Я перенёс несколько баз данных Wordpress с AWS RDS MySQL на MySQL, работающую на EC2 инстансе, используя сервис миграции баз данных. Чего я не знал, так это то, что он не копирует индексы, ключи, автоинкремент и вообще ничего, кроме базовых данных. Конечно, лучшим подходом было бы сделать дамп базы данных с помощью mysqldump и импортировать его вручную, но в одной из установок Wordpress были значительные изменения, и я не хотел их переделывать. Вместо этого я вручную восстановил значения автоинкремента и индексы.

Я задокументировал, как исправил автоинкремент в Wordpress на своём сайте, вот копия того, что сработало у меня. Возможно, я внесу дополнительные изменения, я обновлю информацию на сайте, но могу забыть обновить этот вопрос.

ALTER TABLE wp_termmeta MODIFY COLUMN meta_id bigint(20) unsigned NOT NULL auto_increment;
ALTER TABLE wp_terms MODIFY COLUMN term_id bigint(20) unsigned NOT NULL auto_increment;
ALTER TABLE wp_term_taxonomy MODIFY COLUMN term_taxonomy_id bigint(20) unsigned NOT NULL auto_increment;
ALTER TABLE wp_commentmeta MODIFY COLUMN meta_id bigint(20) unsigned NOT NULL auto_increment;
ALTER TABLE wp_comments MODIFY COLUMN comment_ID bigint(20) unsigned NOT NULL auto_increment;
ALTER TABLE wp_links MODIFY COLUMN link_id bigint(20) unsigned NOT NULL auto_increment;
ALTER TABLE wp_options MODIFY COLUMN option_id bigint(20) unsigned NOT NULL auto_increment;
ALTER TABLE wp_postmeta MODIFY COLUMN meta_id bigint(20) unsigned NOT NULL auto_increment;
ALTER TABLE wp_users MODIFY COLUMN ID bigint(20) unsigned NOT NULL auto_increment;
ALTER TABLE wp_posts MODIFY COLUMN ID bigint(20) unsigned NOT NULL auto_increment;
ALTER TABLE wp_usermeta MODIFY COLUMN umeta_id bigint(20) unsigned NOT NULL auto_increment;

CREATE INDEX term_id on wp_termmeta (term_id);
CREATE INDEX meta_key on wp_termmeta (meta_key(191));
CREATE INDEX slug on wp_terms (slug(191));
CREATE INDEX name on wp_terms (name(191));
CREATE UNIQUE INDEX term_id_taxonomy on wp_term_taxonomy (term_id, taxonomy);
CREATE INDEX taxonomy on wp_term_taxonomy (taxonomy );
CREATE INDEX comment_id on wp_commentmeta (comment_id);
CREATE INDEX meta_key on wp_commentmeta (meta_key(191));
CREATE INDEX comment_post_ID on wp_comments (comment_post_ID);
CREATE INDEX comment_approved_date_gmt on wp_comments (comment_approved,comment_date_gmt);
CREATE INDEX comment_date_gmt on wp_comments (comment_date_gmt);
CREATE INDEX comment_parent on wp_comments (comment_parent);
CREATE INDEX comment_author_email on wp_comments (comment_author_email(10));
CREATE INDEX link_visible on wp_links (link_visible);
CREATE UNIQUE INDEX option_name on wp_options (option_name);
CREATE INDEX post_id on wp_postmeta (post_id);
CREATE INDEX meta_key on wp_postmeta (meta_key);
CREATE INDEX post_name on wp_posts (post_name(191));
CREATE INDEX type_status_date on wp_posts (post_type,post_status,post_date,ID);
CREATE INDEX post_parent on wp_posts (post_parent);
CREATE INDEX post_author on wp_posts (post_author);
CREATE INDEX user_login_key on wp_users (user_login);
CREATE INDEX user_nicename on wp_users (user_nicename);
CREATE INDEX user_email on wp_users (user_email);
CREATE INDEX user_id on wp_usermeta (user_id);
CREATE INDEX meta_key on wp_usermeta (meta_key(191));

ALTER TABLE wp_terms AUTO_INCREMENT = 10000;
ALTER TABLE wp_term_taxonomy AUTO_INCREMENT = 10000;
ALTER TABLE wp_commentmeta AUTO_INCREMENT = 10000;
ALTER TABLE wp_comments AUTO_INCREMENT = 10000;
ALTER TABLE wp_links AUTO_INCREMENT = 10000;
ALTER TABLE wp_options AUTO_INCREMENT = 10000;
ALTER TABLE wp_postmeta AUTO_INCREMENT = 10000;
ALTER TABLE wp_users AUTO_INCREMENT = 10000;
ALTER TABLE wp_posts AUTO_INCREMENT = 10000;
ALTER TABLE wp_usermeta AUTO_INCREMENT = 10000;

Примечания

• Вам следует проверить свои таблицы и убедиться, что значение auto_increment установлено на разумное для данной таблицы значение.
• Если вы получаете ошибку "alter table causes auto_increment resequencing resulting in duplicate entry 1" (или 0, или что-то ещё). Обычно это исправляется удалением записи с ID 0 или 1 в таблице. Учтите, что нужно быть осторожным при этом, так как можно удалить важную строку.

Почему это произошло? Вот что пошло не так в моем случае:

Если вы экспортировали базу данных с помощью phpMyAdmin и получили ошибку при повторном импорте, код, который добавляет первичный ключ, не выполняется, потому что он находится в конце SQL-файла, а не при его создании.

Прежде чем я это понял, я обновился до бета-версии phpMyAdmin 5, и она импортировала файлы с ключом, хотя у меня все еще была ошибка.

Первый урок: не позволяйте вашему импорту завершиться с ошибкой, даже если ваши таблицы уже существуют. У меня ошибка произошла на таблице, начинающейся с wp_w, которая идет после пользователей, и это сломало мои автоинкременты.

Если посмотреть в конец вашего SQL-экспорта, вы найдете оператор ALTER TABLE для добавления первичного ключа и автоинкремента.

Вам не нужно указывать автоинкремент — система автоматически знает, каким должен быть следующий инкремент, вот так:

ALTER TABLE wp_posts CHANGE ID ID  BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT;

Если у вас была активность в админке после этого события, в вашем ключевом поле будут нули, что не позволит установить первичный ключ, а без него нельзя использовать автоинкремент. Поэтому вам нужно выполнить скрипт удаления для каждой таблицы, например:

DELETE FROM wp_posts  WHERE ID=0;

Вот полный набор обновлений. Если в вашей таблице есть такие записи, это вызовет ошибку.

DELETE FROM wp_termmeta  WHERE meta_id=0;
DELETE FROM wp_terms  WHERE term_id=0;
DELETE FROM wp_term_taxonomy  WHERE term_taxonomy_id=0;
DELETE FROM wp_commentmeta  WHERE meta_id=0;
DELETE FROM wp_comments  WHERE comment_ID=0;
DELETE FROM wp_links  WHERE link_id=0;
DELETE FROM wp_options  WHERE option_id=0;
DELETE FROM wp_postmeta  WHERE meta_id=0;
DELETE FROM wp_users  WHERE ID=0;
DELETE FROM wp_posts  WHERE ID=0;
DELETE FROM wp_usermeta  WHERE umeta_id=0;

ALTER TABLE  wp_termmeta ADD PRIMARY KEY(meta_id);
ALTER TABLE  wp_terms ADD PRIMARY KEY(term_id);
ALTER TABLE  wp_term_taxonomy ADD PRIMARY KEY(term_taxonomy_id);
ALTER TABLE  wp_commentmeta ADD PRIMARY KEY(meta_id);
ALTER TABLE  wp_comments ADD PRIMARY KEY(comment_ID);
ALTER TABLE  wp_links ADD PRIMARY KEY(link_id);
ALTER TABLE  wp_options ADD PRIMARY KEY(option_id);
ALTER TABLE  wp_postmeta ADD PRIMARY KEY(meta_id);
ALTER TABLE  wp_users ADD PRIMARY KEY(ID);
ALTER TABLE  wp_posts ADD PRIMARY KEY(ID);
ALTER TABLE  wp_usermeta ADD PRIMARY KEY(umeta_id);

ALTER TABLE wp_termmeta CHANGE meta_id meta_id  BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT;
ALTER TABLE wp_terms CHANGE term_id term_id  BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT;
ALTER TABLE wp_term_taxonomy CHANGE term_taxonomy_id term_taxonomy_id  BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT;
ALTER TABLE wp_commentmeta CHANGE meta_id meta_id  BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT;
ALTER TABLE wp_comments CHANGE comment_ID comment_ID  BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT;
ALTER TABLE wp_links CHANGE link_id link_id  BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT;
ALTER TABLE wp_options CHANGE option_id option_id  BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT;
ALTER TABLE wp_postmeta CHANGE meta_id meta_id  BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT;
ALTER TABLE wp_users CHANGE ID ID  BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT;
ALTER TABLE wp_posts CHANGE ID ID  BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT;
ALTER TABLE wp_usermeta CHANGE umeta_id umeta_id  BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT;

Почему это произошло? Трудно сказать наверняка, так как нужно учитывать множество факторов: ошибки при экспорте или импорте, версию MySQL и т.д..

Это довольно специфичный вопрос, связанный с базой данных MySQL, и он не имеет прямого отношения к WordPress. Чтобы получить точный, а не предположительный ответ на вопрос почему, я рекомендую задать его на Stack Overflow или DBA, предоставив максимально подробную информацию о процессе резервного копирования.

Решение: ALTER TABLE table_name AUTO_INCREMENT = increment_number

• Эта команда вручную устанавливает значение AUTO_INCREMENT на выбранное число
• Значение increment_number должно быть как минимум на единицу больше текущего максимального значения первичного ключа в таблице, который использует автоинкремент
• Также не забудьте заменить table_name на имя вашей таблицы

Пример: ALTER TABLE wp_posts AUTO_INCREMENT = 2043 <- наибольшее значение в столбце ID + 1

Дополнительные примечания:

• Вам нужно будет повторить эту операцию для каждой таблицы, где сбился автоинкремент
• Возможно, существует способ изменить все таблицы сразу, но я не эксперт по SQL (поправьте, если такой способ есть)
• Для больших таблиц это может занять некоторое время, так как команда ALTER TABLE вызывает полную перестройку таблицы

Дополнительная информация: здесь и здесь

Я написал обновление для этого.

Использую встроенную схему WP Core, чтобы гарантировать наличие всех таблиц ядра WordPress (даже в будущем, когда выйдут версии 5.1.1 или выше). Скрипт удаляет повреждённые строки и заново добавляет ключи и первичные ключи. Бесплатную версию скрипта (и более подробное объяснение) можно посмотреть здесь: https://wpindexfixer.tools.managedwphosting.nl/

Нет необходимости угадывать значение автоинкремента.

Я забыл импортировать индексы из последнего файла MySQL, поэтому столкнулся с той же проблемой, и вручную выполнять запросы на автоинкремент по одному было сложно. Поэтому я создал скрипт, который принимает динамическое имя таблицы и проверяет наличие первичного ключа. Если скрипт находит первичный ключ, то автоматически применяет к нему автоинкремент.

Возьмите переменные подключения к БД из вашего wp-config.php, сохраните файл в корне WordPress и запустите по URL.

// Конфигурация базы данных
$host = 'localhost';
$dbuser   = 'dbuser';
$dbpassword   = 'dbpassword';
$dbname         = 'database';

// Подключение к БД
$conn = new mysqli($host, $dbuser, $dbpassword);
try {
    $connection = new PDO("mysql:host=$host;dbname=$dbname", $dbuser, $dbpassword, array(PDO::MYSQL_ATTR_INIT_COMMAND => 'SET sql_mode="NO_ZERO_DATE"'));
    $connection->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    echo "Успешное подключение";
} catch (PDOException $e) {
    exit("Ошибка подключения: " . $e->getMessage());
}

// Получение всех таблиц из БД
$stmt = $connection->prepare('SHOW TABLES');
$stmt->execute();
$table_names = array();
foreach ($stmt->fetchAll() as $row) {
    $table_names[] = $row[0];
}

// Для всех таблиц
foreach ($table_names as $table_name) {

    // Получение имени первичного ключа
    $stmt = $connection->prepare("show keys from $table_name where Key_name = 'PRIMARY'");
    $stmt->execute();
    $key_name = $stmt->fetch()['Column_name'];

    // Получение типа первичного ключа
    $stmt = $connection->prepare("show fields from $table_name where Field = '$key_name'");
    $stmt->execute();
    $key_type = $stmt->fetch()['Type'];

    // Если первичный ключ существует, добавляем автоинкремент
    if ($key_name) {

        try {
            // Если автоинкремент отсутствовал, может существовать строка с key=0. Вычисляем следующий доступный ID
            $sql = "select (ifnull( max($key_name), 0)+1) as next_id from $table_name";
            $stmt = $connection->prepare($sql);
            $stmt->execute();
            $next_id = $stmt->fetch()['next_id'];

            // Присваиваем корректный первичный ключ строке с key = 0, если она существует
            $sql = "update $table_name set $key_name = $next_id where $key_name = 0";
            $stmt = $connection->prepare($sql);
            $stmt->execute();

            // Устанавливаем автоинкремент для первичного ключа
            $sql = "alter table $table_name modify column $key_name $key_type auto_increment";
            $stmt = $connection->prepare($sql);
            $stmt->execute();

        } catch (PDOException $e) {
            echo $e->getMessage() . '\n';
        }
    } else {
        echo "Первичный ключ не найден в таблице $table_name.\n";
    }
}
$connection = null;

У меня недавно была похожая проблема с WordPress 6.5.4 — таблица пользователей потеряла автоинкремент и пыталась вставлять каждого нового пользователя, созданного через админку, с ID равным 0.

Я исправил это, экспортировав таблицу пользователей, удалив её и затем создав заново с правильной структурой, вот так:

CREATE TABLE `tn_users` (`ID` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,`user_login` varchar(60) NOT NULL DEFAULT '',`user_pass` varchar(255) NOT NULL DEFAULT '',`user_nicename` varchar(50) NOT NULL DEFAULT '',`user_email` varchar(100) NOT NULL DEFAULT '',`user_url` varchar(100) NOT NULL DEFAULT '',`user_registered` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',`user_activation_key` varchar(255) NOT NULL DEFAULT '',`user_status` int(11) NOT NULL DEFAULT 0,`display_name` varchar(250) NOT NULL DEFAULT '',`spam` tinyint(2) NOT NULL DEFAULT 0,`deleted` tinyint(2) NOT NULL DEFAULT 0,PRIMARY KEY (`ID`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci;

После этого загрузите SQL-экспорт в текстовый редактор и просто скопируйте и вставьте INSERT-запрос(ы) в PHPMyAdmin, чтобы вставить старые данные теперь с правильными значениями ID.