I recently had a client who was using a table structure that utilized a self-referential structure using an ID field and Parent_ID fields. After upgrading to Innodb, his DBA was trying to utilize a foreign key constraint to perform cascading UPDATE and DELETE statements. After much heart-ache, they called me in to find out why it was not working. Unfortunately, I had to share with them the fact that MySQL does not support this type of use of constraints on self-referential tables.
This deviation from SQL standards results affects an ON UPDATE CASCADE or ON UPDATE SET NULL that recurses to update the same table it has previously updated during the cascade. Instead of cascading, it acts like RESTRICT. This means that you cannot use self-referential ON UPDATE CASCADE or ON UPDATE SET NULL operations in MySQL. According to the Innodb developers, this is to prevent infinite loops resulting from cascaded updates, although I would think this could be addressed in future versions. A self-referential ON DELETE SET NULL, on the other hand, is still possible, as is a self-referential ON DELETE CASCADE.
Filed in MySQL
|
Also tagged CASCADE, constraint, data, DBA, DELETE, Foreign, foreign key, Hierarchical, infinite, Innodb, MySQL, referential, RESTRICT, sql, table, table structure, type, UPDATE
|
If you have seen this error, don’t worry, you are not alone. This error is often accompanied by a message that a table or file could not be created, and usually happens when creating a foreign key. In my experience, 99% of the time this is due to an incompatibility between the two fields in the foreign key. Usually it is something simple like unsigned integer to signed integer. The trickiest I have seen is when trying to create a foreign key between two CHAR fields and they do not share the same CHARSET and COLLATE. ALTER the table so that the CHARSET and COLLATE are the same, and try to add the foreign key again.
Let me know if you have other examples of how you have worked around errno: 150 when adding foreign keys.
Filed in Errors, MySQL
|
Also tagged ALTER, CHARSET, COLLATE, Foreign, foreign key, incompatibility, Innodb, integer, MySQL, table, UNSIGNED, unsigned integer
|
Using ALTER IGNORE TABLE [TABLENAME] ADD UNIQUE INDEX `UNIQUE_INDEX` ([FIELDNAME]) to remove duplicate rows in a table is a fast an efficient process, however on large tables where the physical size is larger than server memory, the ALTER statement can take a long time to run in a production environment.
If you need to remove duplicates on a very large table (we recently used this on a table of 77 million rows), try this method :
delete t1 from table t1, table t2
where t1.duplicate_field= t2.duplicate_field (add more if need ie. and t1.duplicate_field2=t2.duplicate_field2)
and t1.unique_field > t2.unique_field
and breakup into ranges to run faster
If you use an auto-incrementing ID field as the primary key, use this as your unique field, and in the Where clause to run on a range of records to break into smaller operations.
I recently was working with a 3rd party DB that was designed well and used lots of foreign keys to enforce referential integrity. Unfortunately, the application that used the DB structure was very poorly written and caused all sorts of problems when the foreign keys were applied. I had to quickly remove all the foreign keys from about 160 tables. I can never understand why developers will create foreign keys, however then not write their code properly to prevent errors, or at least attempt to catch those errors.
The code below will quickly create a script that removes all Foreign Keys from a database. Many folks will simply change the engine type to MyIsam and then back to InnoDB. This may work, but it is very slow if you already have a lot of data in your tables.
select concat(‘alter table ‘,table_schema,’.',table_name,’ DROP FOREIGN KEY ‘,constraint_name,’;') from information_schema.table_constraints
where constraint_type=’FOREIGN KEY’;
You can limit by schema by adding the line : AND table_schema rlike ‘Schema Name’
Remember, Foreign keys are only valid when using Innodb, MyISAM does not support foreign keys.
Filed in MySQL
|
Also tagged constraint, constraint name, data, DROP, Foreign, foreign key, Innodb, integrity, MyISAM, MySQL, night mare, referential, schema name, table, table schema, type
|