Once executed db_check.pl on a server with Abills 0.78.16:
cd /usr/abills/misc/db_check ./db_check.pl
And got an error:
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
I tried to disable the “FOREIGN KEY” check and execute sql queries again, but other errors occurred:
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)
The structure of the tables was as follows:
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'
Solved the problem by removing both “FOREIGN KEY” from the users_contacts table:
ALTER TABLE users_contacts DROP FOREIGN KEY users_contacts_ibfk_1; ALTER TABLE users_contacts DROP FOREIGN KEY users_contacts_ibfk_2;
After that, I executed the SQL queries of db_check.pl and then created both “FOREIGN KEY” as before:
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;
See also my article:
Installing and configuring the billing system ABillS