vb.org Archive - View Single Post - Script/SQL comman(s) for querying for orphaned images/attachments?

LOCO.MTB · #1 02-22-2013, 08:13 AM

I just found a 2.7MB+ pdf file stired in 'filedata' table that was orphaned.... IAW had NO reference anywhere else... not in 'attachment' table, et al. As far as I can tell, unless the file is listed in 'attachment', you can't find it in CP or anywhere else except by accident using phpMyAdmin, et al. database browser.

So... if there is one orphan attachment, there is likely more... and I want to clean 'em out. I'm not a DBA or a SQL guy, so can anyone help out?

Looks like to me what needs to happen is that I need to run an outside loop choosing each filedataid one at a time, and then see if there is a corresponding record in attachment with the same filedataid... and for any record in filedata that doesn't have a corresponding record in attachment, the record is added to a list of records to display as the query result.

Anyone want to take a stab at helping me construct such a query/tool?

Thanks!

X vBulletin 3.8.12 by vBS Debug Information
Page Generation 0.01238 seconds Memory Usage 1,760KB Queries Executed 11 (?)
More Information
Template Usage: (1)SHOWTHREAD_SHOWPOST (1)ad_footer_end (1)ad_footer_start (1)ad_header_end (1)ad_header_logo (1)ad_navbar_below (1)footer (1)gobutton (1)header (1)headinclude (6)option (1)post_thanks_box (1)post_thanks_button (1)post_thanks_javascript (1)post_thanks_navbar_search (1)post_thanks_postbit_info (1)postbit (1)postbit_onlinestatus (1)postbit_wrapper (1)spacer_close (1)spacer_open Phrase Groups Available: global postbit reputationlevel showthread	Included Files: ./showpost.php ./global.php ./includes/init.php ./includes/class_core.php ./includes/config.php ./includes/functions.php ./includes/class_hook.php ./includes/modsystem_functions.php ./includes/functions_bigthree.php ./includes/class_postbit.php ./includes/class_bbcode.php ./includes/functions_reputation.php ./includes/functions_post_thanks.php Hooks Called: init_startup init_startup_session_setup_start init_startup_session_setup_complete cache_permissions fetch_postinfo_query fetch_postinfo fetch_threadinfo_query fetch_threadinfo fetch_foruminfo style_fetch cache_templates global_start parse_templates global_setup_complete showpost_start bbcode_fetch_tags bbcode_create postbit_factory showpost_post postbit_display_start post_thanks_function_post_thanks_off_start post_thanks_function_post_thanks_off_end post_thanks_function_fetch_thanks_start post_thanks_function_fetch_thanks_end post_thanks_function_thanked_already_start post_thanks_function_thanked_already_end fetch_musername postbit_imicons bbcode_parse_start bbcode_parse_complete_precache bbcode_parse_complete postbit_display_complete post_thanks_function_can_thank_this_post_start showpost_complete
Messages: