Thursday, August 19, 2010
In MYSQL, as well as most database engines, restoring a backup, or importing from an existing dump file can take a long time depending on the number of indexes and primary keys you have on each table. You can speed this process up dramatically by modifying your original dump file by surrounding it with the following:
SET AUTOCOMMIT = 0;
SET FOREIGN_KEY_CHECKS=0;
.. your dump file ..
SET FOREIGN_KEY_CHECKS = 1;
COMMIT;
SET AUTOCOMMIT = 1;
This will force MySQL to not commit until all rows have been loaded, as well as skip all foreign keys checks. Only skip these checks if you are 100% sure that no constraint is violated. This will usually be the case when dumping from one table and inserting into another.
Filed in MySQL, Restore Backup
|
Also tagged backup, COMMIT, database, database engines, Dump, Foreign, long time, MySQL, performance, Restoring, table
|
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, data, DBA, DELETE, Foreign, foreign key, Hierarchical, infinite, Innodb, key, MySQL, referential, RESTRICT, sql, table, table structure, type, UPDATE
|
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 name, data, DROP, Foreign, foreign key, Innodb, integrity, key, MyISAM, MySQL, night mare, referential, schema name, table, table schema, type
|