Orphaned data in table AccessMap
|
smilem
Joined: 2009-02-25
Posts: 110 |
Posted: Mon, 2009-10-26 23:34
|
|
Hi i have these Integrity Check errors in my G2.3 I delete all users and images. Still I get: Check data integrity and report any inconsistencies. Last Run Details: Checked 124 entities How do I fix this and prevent in the future? |
|
Posts: 4338
I doubt that 5 orphaned rows in a table will do any harm.
Posts: 110
5 rows because I had 5 test users on my database usernames were
user1
user2
user3
user3
user4
user5
Ia have installed the Integrity Check module to check for problems and when I so DB errors I deleted them thinking the database problem will fix itself, but as you can see if I have live site with hundreds users than that will make hundreds of orphan records?
How can I delete those records? What table to edit and what to delete?
How can I prevent this from happening in the first place?
Thanks
Posts: 4338
Well, on a whim, I decided to run an integrity check on my G2 installation that's been running well, for the last 12 months, with 204,000 items in the entity table. And there are a few orphaned entries in the AccessMap table. What do I intend to do about it? Nothing. It ain't bust, so no point trying to fix it.
Posts: 16503
I too have a few orphaned items. No where near as many items in my Gallery but it's been running fine for 4-5 years like this....
____________________________________________
Like Gallery? Like the support? Donate now!!! See G2 live here
Posts: 110
I created another test user and uploaded 4 photos.
Now I see:
Last Run Details:
Checked 66 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
I deleted the user and got:
Last Run Details:
Checked 44 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
This isn't normal is it? Or the integrity module is absolete and outputs nonsense?
The rows reported are in the DB I checked them using phpMyadmin.
But on fresh gallery2.3 install (no users etc.) I installed integrity module and got this:
Last Run Details:
Checked 8 entities
Orphaned data in table AccessMap with no row in AccessSubscriberMap table; ids: 8, 9, 10
My fresh install tables look like this:
Posts: 16503
What we are saying is, if Gallery is working, don't worry about it.
There's quite possibly something in the code that needs to be cleaned up or wasn't updated when something else was changed. But unless it's a security issue or major bug, I doubt anything will be done about it for G2. All major development are being focused on G3 right now.
____________________________________________
Like Gallery? Like the support? Donate now!!! See G2 live here
Posts: 110
Will Gallery3 still work with the
checkout modules (byemail, paypal, downlaod etc.)
Will there be module like useralbums perhaps with built-in hack that makes each user to see their own albums ?
I'm still testing G2, but if G3 will have all this than perhaps I have to wait for stable build?
I still think somebody should take a look at gallery2 code and fix it ASAP.
Just do a clean install and you will see the problem like have shown you here.
Posts: 4338
No
Posts: 16503
Gallery 3 won't work with any G2 themes or plugins.
They all need to be re-written from scratch to work with G3. G3, just like G2 after G1 is a completely rewrite. They are 3 completely separate products that all do about the same thing only different in each case.
ASAP? Not going to happen. It's been like this for at least a year in 2.3 and probably existed in 2.2 and possibly 2.1, maybe even back in 2.0 and is an artifact left over from Alpha and Beta code. We're talking about 6 years back now. It's fine. Ignore it.
NO development is being done on G2 and this isn't a bug IMO, it's just not clean. G2 is a huge beast and all focus is being given to G3 for future development. If this was a security issue or causing G2 to not work it would be fixed, but that's it.
It's no different than your pages or css not validating 100%. It works and works just fine. There are problems and flaws with your car and house, your body and everything else you use, but they work.
G3 is the future, but it's going to take a while to build up the functionality that G2 has. G3 is easier to code for and we've already seen a faster adoption of people contributing modules and themes to G3. We're not even out of Beta yet and we've had more contributions to G3 than we did for G2 for the first year or two after it's initial release. G3 is going to be way more popular than G2 ever was, but it will still take time for people to write and contribute to it.
Right now, if you're looking for this functionality, I'd stick with G2. We're still supporting it (for probably years as we have done with G1), just not developing it. I wouldn't be surprised to see everything you're looking for available for G3 in 6-12 months after it's initial release.
____________________________________________
Like Gallery? Like the support? Donate now!!! See G2 live here
Posts: 110
Well I found out that as you add files to gallery, the Orphaned data increase in 8 id's every time. Tried to add 30 files, and after I added them I got more Orphaned data.
As I understand the Integrity Check reports in increment of 5 (read it another thread), so there can be new id'd even if you add just 1 photo.
As you say gallery2 works but, I can't undersant why is this not serious problem, won't it slow down gallery2 if I add hudred photos I have at least 20-30 orphaned fields of data. If a user orders same 100 photos another 20-30 fields of data. That is about ~50 orphaned fields (half) of the number photos that were uploaded.
Posts: 4338
Hmmm... well I currently have 67,000 photos (down from a peak of 80,000 last week) and only 5 or so orphaned items reported.
Posts: 16503
And I have 3 much smaller G2 installs:
Install #1
Last Run Details:
Failed to load entity ids: 81, 175
Orphaned data in table AccessMap with no row in AccessSubscriberMap table; ids: 9900
Install #2
Last Run Details:
Checked 1758 entities
Unknown plugin: theme ajaxian
Orphaned data in table PluginParameterMap with no row in PluginMap table; ids: theme/siriux_ijonized
Install #3 (tiny install, but very close to a fresh install with no modifications)
Last Run Details:
Checked 162 entities
All 3 of these installs have seen lots of installing and uninstalling of plugins, themes, etc. Install #1 is about 4-5 years old with many upgrades over the years and 155 photos. Install #2 is about 2-1/2 years old and 616 photos. Install #3 is about 3 months old with about 57 photos.
So reading what Alec posted along with what you posted I can only guess something isn't right with your install or possibly DB.
What setting do you have set for storage type in config.php? mysqlt, mysqli?
Who is your host?
____________________________________________
Like Gallery? Like the support? Donate now!!! See G2 live here
Posts: 110
>So reading what Alec posted along with what you posted I can only guess something
>isn't right with your install or possibly DB.
I agree with you.
>What setting do you have set for storage type in config.php? mysqlt, mysqli?
$storeConfig['type'] = 'mysqli';
>Who is your host?
www.321.lt (shared hosting)
You can see their server config here:
http://srv2.321.lt/info.php
Posts: 110
Some observations:
host 321.lt (same test install as above)
before delete uploaded files from album
Checked 277 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
after delete
Checked 97 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
upload 40 files
Checked 216 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
order prints
Checked 296 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
delete album then delete user
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
Another host test:
clean install
Checked 8 entities
Orphaned data in table AccessMap with no row in AccessSubscriberMap table; ids: 8, 9, 10, 11
after I install "remote" "upload applet" "useralbums" "registration"
Checked 10 entities
Orphaned data in table AccessMap with no row in AccessSubscriberMap table; ids: 8, 9, 10, 11, 20, 21, 22, 23
After I upload 20 files
Checked 52 entities
Orphaned data in table AccessMap with no row in AccessSubscriberMap table; ids: 8, 9, 10, 11, 20, 21, 22, 23
After I upload another 20 files
Checked 52 entities
Orphaned data in table AccessMap with no row in AccessSubscriberMap table; ids: 8, 9, 10, 11, 20, 21, 22, 23
After I delete uploaded fiels:
Last Run Details:
Checked 11 entities
Orphaned data in table AccessMap with no row in AccessSubscriberMap table; ids: 8, 9, 10, 11, 20, 21, 22, 23, 109, 110
After I upload same 40 files again in 1 session:
Checked 130 entities
Orphaned data in table AccessMap with no row in AccessSubscriberMap table; ids: 8, 9, 10, 11, 20, 21, 22, 23, 109, 110
I delete the files:
Checked 11 entities
Orphaned data in table AccessMap with no row in AccessSubscriberMap table; ids: 8, 9, 10, 11, 20, 21, 22, 23, 109, 110
So I don not know what to think.... the problem is in the core module
Posts: 110
I think, perhaps I could use SQL script to delete the orphan data in database? Then gallery2 would not get slower and slower. Anyone knows how to make it?
I could attach my AccessMap, AccessSubscriberMap SQL tables here.
I read this guide http://209.85.135.132/search?q=cache:EBrkmFhdyT0J:www.wellho.net/solutions/mysql-mysql-joins-using-left-join-and-right-join-to-find-orphan-rows.html+select+orphan+records+mysql&cd=1&hl=lt&ct=clnk&gl=lt
But did not fully understand how to make it.
I deleted the orphans by hand and here is my sql for it
Posts: 110
I found out that to view and delete orphan data.
The code deletes 200 rows from database, so imagine instead of 2mb db you would have to deal with 20mb db. This looks like a serious flaw to me., unless your host gives you unlimited mysql transactions (none of the shared hosting do)
You can use this in your phpmyadmin:
SELECT * FROM `g2_AccessMap`
WHERE `g_accessListId` NOT IN(
SELECT DISTINCT `g_accessListId` FROM `g2_AccessSubscriberMap`
);
and to delete the data just replace select with delete.
Who should I contact that this code would be included in integrity check module for g2.3? It should not be hard to do this.
Posts: 22890
Development on G2 and the 3rd party module integrity check has stopped. If you care to continue development with either. Please join the development mailing list and we can show you want needs to be done in creating a fork of G2.3 in SVN and/or the -contrib branch.
Or you can develop the integrity check module and post the code here for others as well as update the codex page for the module.
All the devs are heads down on working on getting G3 working and there is no resources to work on G2.3 but if you care to contribute we are not holing you back.
Dave
_____________________________________________
Blog & G2 || floridave - Gallery Team