mysql - Delete all rows in all tables which aren't used in any FK relation anymore -

mysql - Delete all rows in all tables which aren't used in any FK relation anymore -

in order trim production database loading in test system, we've deleted rows in many tables. left cruft in couple of tables, namely rows aren't used in fk relation anymore. want accomplish garbage collection in java.

or set way: if have m tables in database. n of them (i.e. not all) have foreign key relations. i've deleted couple of high level rows (i.e. have outgoing fk relations) via sql. leaves rows in related tables alone.

does have sql stored procedure or java programme finds n tables , follows fk relations delete rows no longer needed.

if finding n tables complex, provide script list of tables scan or, preferably, negative list of tables ignore.

also note:

we have tables used in many (>50) fk relations, i.e. a, b, c, ... utilize rows in z. all fk relations utilize technical pk column single column.

even simple stored procedures little ugly, , interesting exercise in pushing stored procedures beyond point it's easy take them.

to utilize code below, launch mysql shell, use target database, paste big block of stored procedures below, , execute

call delete_orphans_from_all_tables();

to delete orphaned rows tables in database.

to provide zoomed-out overview:

delete_orphans_from_all_tables entry point. other sprocs prefixed dofat create clear relate delete_orphans_from_all_tables , create less noisy have them kicking around. delete_orphans_from_all_tables works calling dofat_delete_orphans_from_all_tables_iter repeatedly until there no more rows delete. dofat_delete_orphans_from_all_tables_iter works looping on tables targets of foreign key constraints, , each table deleting rows aren't referenced anywhere.

here's code:

delimiter // create procedure dofat_store_tables_targeted_by_foreign_keys () begin -- procedure creates temporary table called targettablenames -- containing names of tables target of foreign -- key relation. set @db_name = database(); drop temporary table if exists targettablenames; create temporary table targettablenames ( table_name varchar(255) not null ); prepare stmt 'insert targettablenames(table_name) select distinct referenced_table_name information_schema.key_column_usage referenced_table_schema = ?'; execute stmt using @db_name; end// create procedure dofat_deletion_clause_for_table( in table_name varchar(255), out result text ) deterministic begin -- given table foo, foo.col1 referenced bar.col1, , -- foo.col2 referenced qwe.col3, homecoming string like: -- -- not (foo.col1 in (select col1 bar) <=> 1) , -- not (foo.col2 in (select col3 qwe) <=> 1) -- -- used dofat_delete_orphans_from_table target orphaned -- rows. -- -- odd-looking `not (x in y <=> 1)` build used in favour of -- more obvious (x not in y) build handle nulls properly; note -- (x not in y) evaluate null if either x null or if x not in -- y , *any* value in y null. set @db_name = database(); set @table_name = table_name; prepare stmt 'select group_concat( concat( \'not (\', @table_name, \'.\', referenced_column_name, \' in (\', \'select \', column_name, \' \', table_name, \')\', \' <=> 1)\' ) separator \' , \' ) @result information_schema.key_column_usage referenced_table_schema = ? , referenced_table_name = ?'; execute stmt using @db_name, @table_name; set result = @result; end// create procedure dofat_delete_orphans_from_table (table_name varchar(255)) begin -- takes argument name of table target of @ to the lowest degree -- 1 foreign key. -- deletes table rows not referenced -- foreign key. phone call dofat_deletion_clause_for_table(table_name, @deletion_clause); set @stmt = concat( 'delete ', @table_name, ' ', @deletion_clause ); prepare stmt @stmt; execute stmt; end// create procedure dofat_delete_orphans_from_all_tables_iter( out rows_deleted int ) begin -- dofat_store_tables_targeted_by_foreign_keys must called before -- work. -- -- loops 1 time on tables referenced foreign -- key. each table, deletes rows not referenced. -- note not guaranteed leave tables without orphans, -- since deletion of rows table late in sequence may leave -- rows table in sequence orphaned. declare loop_done bool; -- variable name needs differ column name utilize populate -- because of bug http://bugs.mysql.com/bug.php?id=28227 declare table_name_ varchar(255); declare curs cursor select table_name targettablenames; declare go on handler not found set loop_done = true; set rows_deleted = 0; set loop_done = false; open curs; repeat fetch curs table_name_; phone call dofat_delete_orphans_from_table(table_name_); set rows_deleted = rows_deleted + row_count(); until loop_done end repeat; close curs; end// create procedure delete_orphans_from_all_tables () begin phone call dofat_store_tables_targeted_by_foreign_keys(); repeat phone call dofat_delete_orphans_from_all_tables_iter(@rows_deleted); until @rows_deleted = 0 end repeat; end// delimiter ;

as aside, exercise has taught me few things create writing code of level of complexity using mysql sprocs frustrating business. mention these because may help you, or curious future reader, understand crazy stylistic choices in code above.

grossly verbose syntax , boilerplate simple things. e.g. needing declare , assign on different lines needing set delimiters around procedure definitions needing utilize prepare/execute combo utilize dynamic sql). utter lack of referential transparency: prepare stmt concat( ... ); syntax error, while @foo = concat( ... ); prepare stmt @foo; not. execute stmt using @foo fine, execute stmt using foo foo procedure variable syntax error. a select statement , procedure lastly statement select statement both homecoming result set, pretty much you'd ever result set (like looping on or checking if in it) can targeted @ select statement, not call statement. you can pass session variable out parameter sproc, can't pass sproc variable out parameter sproc. totally arbitrary restrictions , bizarre behaviours blindside you: no dynamic sql allowed in functions, in procedures using cursor fetch column procedure variable of same name sets variable null throws no warning or error

lack of ability cleanly pass result sets between procedures

result sets basic type in sql; they're selects homecoming , think them objects when using sql application layer. within mysql sproc, can't assign them variables or pass them 1 sproc another. if need functionality, have have 1 sproc write result set temporary table sproc can read it.

eccentric , unfamiliar constructs , idioms: three equivalent ways of assigning variable - set foo = bar, select foo = bar , select bar foo. you'd expect should utilize procedure variables state , avoid session variables same reasons avoid globals in normal programming language. in fact need utilize session variables everywhere because many language constructs (like out params , execute) won't take other kind of variable. the syntax using cursor loop on result set looks alien.

despite these obstacles, can still piece little programs sprocs if determined.

mysql stored-procedures garbage-collection