find then delete orphan data from gallery2 db

Hi, I use Gallery2 v2.3 system on my web server. When I add and delete photos I see orphan records in my db. The project is opensource and developers are focused on new v3 of the product.

However it will be at least a year until it will equal in features so I have to use v2.3 instead.

I attach my 2 tables that develop this problem as SQL dump. The system has integrity check that reports this:

Last Run Details:

Checked 175 entities
Orphaned data in table AccessMap with no row in AccessSubscriberMap table; ids: 387, 388, 390, 391, 392, 727, 728, 729, 730, 731, 732, 733, 734, 735, 789, 790, 791, 792, 793, 794, 795, 796, 797, 850, 851, 852, 853, 854, 855, 856, 857, 858, 1166, 1167, 1168, 1169, 1170, 1171, 1172, 1173, 1174, 1590, 1591, 1592, 1593, 1594, 1595, 1596, 1597, 1598

So I would like to have 2 mySQL queries.

First to view the orphan records.
Second to remove them.

Thanks for your time.

Attached Files

gallery2_g2.sql.txt (12.2 KB, 5 views)

re: find then delete orphan data from gallery2 db

Hey.

You can use NOT IN and a sub-query to find the rows in the parent table that are not associated with any rows in the child table.
Which works both with SELECT and DELETE queries.

P.S.
Seeing as you are using InnoDB, you should really add a foreign key restraint on your child table. No point using an engine who's main advantage is that it enforces relationships, if you don't even use them ;-)

re: find then delete orphan data from gallery2 db

Thanks,

I tried this code is it ok


I read I need to do:

How do I delete them?