Однажды выполнил db_check.pl на сервере с Abills 0.78.16:
cd /usr/abills/misc/db_check ./db_check.pl
И получил ошибку:
Current: SMALLINT(6) AUTO_INCREMENT NOT NULL Change to : SMALLINT(6) UNSIGNED AUTO_INCREMENT NOT NULL ALTER TABLE `users_contact_types` MODIFY COLUMN `id` SMALLINT(6) UNSIGNED AUTO_INCREMENT NOT NULL; DBD::mysql::db do failed: Cannot change column 'id': used in a foreign key constraint 'users_contacts_ibfk_2' of table 'abills.users_contacts' at /usr/abills/misc/db_check/../../Abills/mysql/dbcore.pm line 262, <STDIN> line 3. Current: SMALLINT(6) Change to : SMALLINT(6) UNSIGNED NOT NULL DEFAULT 0 ALTER TABLE `users_contacts` MODIFY COLUMN `type_id` SMALLINT(6) UNSIGNED NOT NULL DEFAULT 0; DBD::mysql::db do failed: Cannot change column 'type_id': used in a foreign key constraint 'users_contacts_ibfk_2' at /usr/abills/misc/db_check/../../Abills/mysql/dbcore.pm line 262, <STDIN> line 4
Попробовал отключить проверку «FOREIGN KEY» и снова выполнить sql запросы, но возникли другие ошибки:
SET FOREIGN_KEY_CHECKS = 0; SET GLOBAL FOREIGN_KEY_CHECKS=0; ALTER TABLE `users_contact_types` MODIFY COLUMN `id` SMALLINT(6) UNSIGNED AUTO_INCREMENT NOT NULL; ERROR 1025 (HY000): Error on rename of './abills/#sql-33e_4eccf' to './abills/users_contact_types' (errno: 150 - Foreign key constraint is incorrectly formed) ALTER TABLE `users_contacts` MODIFY COLUMN `type_id` SMALLINT(6) UNSIGNED NOT NULL DEFAULT 0; ERROR 1025 (HY000): Error on rename of './abills/#sql-33e_4eccf' to './abills/users_contacts' (errno: 150 - Foreign key constraint is incorrectly formed)
Структура таблиц была следующая:
CREATE TABLE `users_contacts` ( `id` int(11) unsigned NOT NULL AUTO_INCREMENT, `uid` int(11) unsigned NOT NULL, `type_id` smallint(6) DEFAULT NULL, `value` varchar(250) NOT NULL DEFAULT '', `priority` smallint(6) unsigned NOT NULL DEFAULT '0', `comments` text, PRIMARY KEY (`id`), KEY `type_id` (`type_id`), KEY `_uid_contact` (`uid`), CONSTRAINT `users_contacts_ibfk_1` FOREIGN KEY (`uid`) REFERENCES `users` (`uid`) ON DELETE CASCADE, CONSTRAINT `users_contacts_ibfk_2` FOREIGN KEY (`type_id`) REFERENCES `users_contact_types` (`id`) ON DELETE CASCADE ) ENGINE=InnoDB AUTO_INCREMENT=12002 DEFAULT CHARSET=utf8 COMMENT='Main user contacts table' CREATE TABLE `users_contact_types` ( `id` smallint(6) NOT NULL AUTO_INCREMENT, `name` varchar(30) NOT NULL, `is_default` tinyint(1) unsigned NOT NULL DEFAULT '0', `hidden` tinyint(1) unsigned NOT NULL DEFAULT '0', PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8 COMMENT='Types of user contacts' CREATE TABLE `users` ( `id` varchar(20) NOT NULL DEFAULT '', `activate` date NOT NULL DEFAULT '0000-00-00', `expire` date NOT NULL DEFAULT '0000-00-00', `credit` double(10,2) NOT NULL DEFAULT '0.00', `reduction` double(6,2) NOT NULL DEFAULT '0.00', `reduction_date` date NOT NULL DEFAULT '0000-00-00', `registration` date DEFAULT '0000-00-00', `password` blob NOT NULL, `uid` int(11) unsigned NOT NULL AUTO_INCREMENT, `gid` smallint(6) unsigned NOT NULL DEFAULT '0', `disable` tinyint(1) unsigned NOT NULL DEFAULT '0', `company_id` int(11) unsigned NOT NULL DEFAULT '0', `bill_id` int(11) unsigned NOT NULL DEFAULT '0', `ext_bill_id` int(10) unsigned NOT NULL DEFAULT '0', `credit_date` date DEFAULT '0000-00-00', `domain_id` smallint(6) unsigned NOT NULL DEFAULT '0', `deleted` tinyint(1) unsigned NOT NULL DEFAULT '0', PRIMARY KEY (`uid`), UNIQUE KEY `id` (`domain_id`,`id`), KEY `bill_id` (`bill_id`), KEY `company_id` (`company_id`) ) ENGINE=InnoDB AUTO_INCREMENT=8013 DEFAULT CHARSET=utf8 COMMENT='Users list'
Решил проблему удалением обеих «FOREIGN KEY» из таблицы users_contacts:
ALTER TABLE users_contacts DROP FOREIGN KEY users_contacts_ibfk_1; ALTER TABLE users_contacts DROP FOREIGN KEY users_contacts_ibfk_2;
После чего выполнил SQL запросы db_check.pl и потом создал оба «FOREIGN KEY» как прежде:
ALTER TABLE users_contacts ADD FOREIGN KEY (uid) REFERENCES users(uid) ON DELETE CASCADE; ALTER TABLE users_contacts ADD FOREIGN KEY (type_id) REFERENCES users_contact_types(id) ON DELETE CASCADE;
Смотрите также мою статью:
Установка и настройка биллинговой системы ABillS