Description:
After migration from 4.0.17 to 4.0.18-standard with the same binary database, we have some problems with foreign keys. For example after DROP database, mysql complains of foreign key constraint and do NOT drop all the tables.
Next call of DROP database solves the problem, but we can't for example create table
mysql> drop database advert;
ERROR 1217: Cannot delete or update a parent row: a foreign key constraint fails
mysql> drop database advert;
ERROR 1217: Cannot delete or update a parent row: a foreign key constraint fails
mysql> drop database advert;
Query OK, 0 rows affected (0.01 sec)
The next problem is, that database is unable to create some type of table (see how to repeat). In mysql 4.0.17 everythink is OK.
How to repeat:
Create innodb tablespace in 4.0.17. Than install 4.0.18-standard on the top of this tablespace and create db and grant priviledges to it.
CREATE TABLE zone_stat_region_weekly (
zone_id VARCHAR(80) NOT NULL,
-- week is date of monday (week starts at monday!)
-- (e.g. 50th week of 2003 -> 2003-12-08)
week DATE NOT NULL,
region_id VARCHAR(8) NOT NULL,
collocation VARCHAR(80) NOT NULL DEFAULT '',
section VARCHAR(255) NOT NULL DEFAULT '',
CONSTRAINT zone_stat_weekly_pk_zoneid_week_regionid_collocation_section
PRIMARY KEY (zone_id, week, region_id, collocation, section),
impressions INT UNSIGNED NOT NULL,
clicks INT UNSIGNED NOT NULL,
actions INT UNSIGNED NOT NULL,
KEY (region_id),
CONSTRAINT zone_stat_region_weekly_fk_zoneid
FOREIGN KEY (zone_id)
REFERENCES zone (id),
CONSTRAINT region_stat_region_daily_fk_regionid
FOREIGN KEY (region_id)
REFERENCES region (id)
) TYPE=InnoDB COMMENT='weekly statistics of zone: targeting criteron region';
Description: After migration from 4.0.17 to 4.0.18-standard with the same binary database, we have some problems with foreign keys. For example after DROP database, mysql complains of foreign key constraint and do NOT drop all the tables. Next call of DROP database solves the problem, but we can't for example create table mysql> drop database advert; ERROR 1217: Cannot delete or update a parent row: a foreign key constraint fails mysql> drop database advert; ERROR 1217: Cannot delete or update a parent row: a foreign key constraint fails mysql> drop database advert; Query OK, 0 rows affected (0.01 sec) The next problem is, that database is unable to create some type of table (see how to repeat). In mysql 4.0.17 everythink is OK. How to repeat: Create innodb tablespace in 4.0.17. Than install 4.0.18-standard on the top of this tablespace and create db and grant priviledges to it. CREATE TABLE zone_stat_region_weekly ( zone_id VARCHAR(80) NOT NULL, -- week is date of monday (week starts at monday!) -- (e.g. 50th week of 2003 -> 2003-12-08) week DATE NOT NULL, region_id VARCHAR(8) NOT NULL, collocation VARCHAR(80) NOT NULL DEFAULT '', section VARCHAR(255) NOT NULL DEFAULT '', CONSTRAINT zone_stat_weekly_pk_zoneid_week_regionid_collocation_section PRIMARY KEY (zone_id, week, region_id, collocation, section), impressions INT UNSIGNED NOT NULL, clicks INT UNSIGNED NOT NULL, actions INT UNSIGNED NOT NULL, KEY (region_id), CONSTRAINT zone_stat_region_weekly_fk_zoneid FOREIGN KEY (zone_id) REFERENCES zone (id), CONSTRAINT region_stat_region_daily_fk_regionid FOREIGN KEY (region_id) REFERENCES region (id) ) TYPE=InnoDB COMMENT='weekly statistics of zone: targeting criteron region';