Проверка целостности таблиц БД и восстановление связей

FAQ
Иногда у пользователей возникают ошибки при публикации топиков: белая страница или не догружается сайт до конца (отсутствует сайдбар и/или футер) или не работает прямой эфир и т.п. Наиболее часто это случается при удалении топика, блога или пользователя (через админку) и когда нарушены внешние ключи между таблицами или когда таблицы вовсе не имеют тип InnoDb, который весьма строго рекомендован для таблиц в ЛС.

В данном топике будет рассмотрено решение такой проблемы.

Во-первых нужно найти все несвязанные данные. Для этого нужно выполнить запросы к каждой таблице на предмет наличия в ней «висящих» данных, которые более никуда не относятся. Это могут быть записи в блогах о входящих в него пользователях, топиках в несуществующих блогах и т.п.

Нужно детально обойти каждую таблицу и просмотреть её поля — есть ли записи, которые указывают айдишник другого объекта.

Для примера, привожу часть SQL запросов, которые позволяют найти оставшиеся записи при удалении пользователей из БД.

Сверка id существующих пользователей с id в блогах, проверка комментариев с прямого эфира и контента топиков:

SELECT `user_id` FROM `prefix_blog_user` WHERE `user_id` NOT IN (SELECT `user_id` FROM `prefix_user`)

SELECT * FROM `prefix_topic_content` WHERE `topic_id` NOT IN (SELECT `topic_id` FROM `prefix_topic`)

SELECT * FROM `prefix_comment_online` WHERE `comment_id` NOT IN (SELECT `comment_id` FROM `prefix_comment`)

SELECT * FROM `prefix_talk_user` WHERE `talk_id` NOT IN (SELECT `talk_id` FROM `prefix_talk`)

SELECT * FROM `prefix_topic_read` WHERE `topic_id` NOT IN (SELECT `topic_id` FROM `prefix_topic`)
SELECT * FROM `prefix_topic_read` WHERE `user_id` NOT IN (SELECT `user_id` FROM `prefix_user`)

SELECT * FROM `prefix_topic_tag` WHERE `topic_id` NOT IN (SELECT `topic_id` FROM `prefix_topic`)

SELECT * FROM `prefix_topic_tag` WHERE `user_id` NOT IN (SELECT `user_id` FROM `prefix_user`)

SELECT * FROM `prefix_topic_tag` WHERE `blog_id` NOT IN (SELECT `blog_id` FROM `prefix_blog`)


Конечно же нужно подставить вместо «prefix_» реальный префикс вашей БД, который вы указали при инсталяции сайта (найти также можно в конфиге).

Если какой нибудь из этих запросов возвращает не нулевой результат — это те «зависшие» записи, которые просто следует удалить.

Второй этап — изменение типа таблиц. Нужно изменить тип всех таблиц в БД на InnoDB. Изменить тип таблиц можно, например, в phpMyAdmin в вкладке «Operations», блок «Table options», поле «Storage Engine» для каждой таблицы.

Третий этап — назначить внешние ключи вручную для каждой таблицы. SQL запросы для создания внешних ключей можно найти в файле sql.sql дистрибутива ЛС в папке install по ключевой фразе "Ограничения внешнего ключа сохраненных таблиц". Запросы можно вводить в вкладке «SQL» phpMyAdmin.

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

Если же найти проблему никак не удается, а создать ключи нужно, то перед каждым таким проблемным запросом нужно дописать запрос:

SET FOREIGN_KEY_CHECKS=0;

Это позволит создать ключи без проверки (отключит автоматическую проверку целостности связей при их создании).

Для того чтобы реально проверить создаются ли внешние ключи можно обновлять результат запроса в новой вкладке:

SELECT * FROM `information_schema`.`REFERENTIAL_CONSTRAINTS` WHERE `REFERENTIAL_CONSTRAINTS`.`CONSTRAINT_NAME` NOT LIKE 'prefix_geo_%' AND `REFERENTIAL_CONSTRAINTS`.`CONSTRAINT_SCHEMA` = 'Your_LiveStreet_DB_Name' LIMIT 1000

Вместо «Your_LiveStreet_DB_Name» укажите реальное имя вашей БД, используемой в ЛС. Этот запрос покажет список всех внешних связей для вашей БД.

Напоминание:

Только не забудьте перед добавлением внешних ключей изменить тип таблицы — т.к. даже если тип таблиц будет MyISAM — при выполнении запросов на создание внешних связей MySQL абсолютно спокойным тоном в ответ напишет «Ок» и даже не заикнется что для MyISAM никаких внешних связей и быть не может. Именно поэтому нужно смотреть на список внешних ключей — после каждого корректного запроса в списке связей будет появляться новая запись.
0 комментариев
Только зарегистрированные и авторизованные пользователи могут оставлять комментарии.