Hints for exploring gallery2 database inconsitencies

Cry Regarder

Joined: 2010-12-07
Posts: 9

Posted: Fri, 2012-11-30 19:22

Below are some disorganized thoughts that may help you if you experience problems with your gallery2 database.

I recently experienced some significant corruption of my 40,000 image gallery2 database. Unfortunately the corruption wasn't detected before backups were rotated.

Attached is a very simple python script (rename to orphans.py) you can tailor to your system that checks most of the gallery2 tables to see if any of their records no longer have matching records in the g2_Entity database. In addition it does some checks to make sure that parents and children connect up properly. If all is well, the script should report empty results for every query.

Another query that can be helpful (not implemented by the script) is to check if there are gallery entities that aren't linked to a parent.

select g2_Entity.* from 
       g2_Entity left join g2_ChildEntity on 
       g2_ChildEntity.g_id=g2_Entity.g_id where 
       g2_ChildEntity.g_id is null;

Many entities aren't supposed to have parents (GalleryGroup, GalleryUser). Others may not, for example the core gallery with g_id = 1.

For me, I filtered on GalleryDerivativeImage and found that I had a bunch of Derivatives that weren't linked to anything. After deleting them, I could re-run my script to see what other items in other tables no longer had matching entities and delete those items.

The gallery2 database structure has some redundancies for performance, for example the number of children for an item is stored but it could just be derived from the database. The script doesn't check for those types of issues.

Another such example is the rating system. g2_RatingCacheMap stores a running average of the ratings. If for whatever reason you decide to delete all the 1 star ratings :-P from your g2_RatingMap, don't forget to update g2_RatingCacheMap as well.

Cry

Attachment	Size
orphans.txt	5.38 KB

suprsidr

Joined: 2005-04-17
Posts: 8339

Posted: Fri, 2012-11-30 19:51

I have a similar script which removes orphans using G2's API

-s
________________________________
All New jQuery Minislideshow for G2/G3

Cry Regarder

Joined: 2010-12-07
Posts: 9

Posted: Fri, 2012-11-30 20:07

Pretty Cool! I was too paranoid to do automated deletes. I did manual sql deletes for thousands of records. Why was I so paranoid? Because for several of my photos, everything was there but either the g2_Entity record or the g2_Item record was missing. For these rather than deleting everything, I was able to manually reconstruct their g2_Entity or g2_Item records.

Cry

Austin Kinsella

Joined: 2007-08-05
Posts: 39

Posted: Tue, 2012-12-11 12:44

Our club has a project where members take a photo to a specific theme each week for the year. There is an album in our gallery (http://www.carlowphoto.ie/wordpress/?page_id=655&g2_itemId=19009) with sub-albums for each week. Attempts to display some of the sub-albums gives an error:

Error (ERROR_MISSING_OBJECT) : Missing object for 18597
in modules/core/classes/GalleryStorage/GalleryStorageExtras.class at line 2054 (GalleryCoreApi::error)
in modules/core/classes/GalleryStorage/GalleryStorageExtras.class at line 98 (GalleryStorageExtras::_identifyEntities)
in modules/core/classes/GalleryStorage.class at line 314 (GalleryStorageExtras::loadEntities)
in modules/core/classes/helpers/GalleryEntityHelper_simple.class at line 71 (GalleryStorage::loadEntities)
in modules/core/classes/GalleryCoreApi.class at line 2361 (GalleryEntityHelper_simple::loadEntitiesById)
in modules/core/classes/GalleryTheme.class at line 1291 (GalleryCoreApi::loadEntitiesById)
in themes/carbon/theme.inc at line 177 (GalleryTheme::loadCommonTemplateData)
in modules/core/classes/GalleryTheme.class at line 932 (CarbonTheme::showAlbumPage)
in modules/core/classes/GalleryView.class at line 301 (GalleryTheme::loadTemplate)
in main.php at line 465 (GalleryView::doLoadTemplate)
in main.php at line 104
in modules/core/classes/GalleryEmbed.class at line 189
in /var/www/cps/wordpress/wp-content/plugins/wpg2/wpg2template.inc at line 76 (GalleryEmbed::handleRequest)
in ??? at line 0
in /var/www/cps/wordpress/wp-includes/plugin.php at line 403
in /var/www/cps/wordpress/wp-includes/template-loader.php at line 7
in /var/www/cps/wordpress/wp-blog-header.php at line 16
in /var/www/cps/wordpress/index.php at line 17

The missing object id is the same for each of the affected sub-albums. I've run missingObjectFix, and it reports no problems. I've run the query in the first post, and it lists 71 items, none of them with id 18597. There is no row in g2_Entity nor g2_Item with g_id 18597.

Any suggestions on what I should be looking for?

AK

suprsidr

Joined: 2005-04-17
Posts: 8339

Posted: Tue, 2012-12-11 12:52

it could be a user that was deleted.
missingObjectFix does not check for missing users.

What entityType are these 71 items?

-s
________________________________
All New jQuery Minislideshow for G2/G3

Austin Kinsella

Joined: 2007-08-05
Posts: 39

Posted: Tue, 2012-12-11 13:09

Mostly users. It could well be a user, as we removed a few after the start of the project. Items owned by deleted users were re-assigned to admin, so why should it cause a problem?

AK

suprsidr

Joined: 2005-04-17
Posts: 8339

Posted: Tue, 2012-12-11 13:18

Are you referring to the albums that are requiring login?

Quote:

Mostly users. It could well be a user, as we removed a few after the start of the project. Items owned by deleted users were re-assigned to admin, so why should it cause a problem?

Were they removed from the WP interface or gallery->admin->user interface?

I recently helped another WPG2 user w/ similar issues.

-s
________________________________
All New jQuery Minislideshow for G2/G3

Austin Kinsella

Joined: 2007-08-05
Posts: 39

Posted: Tue, 2012-12-11 13:35

Could have been either! The user lists are matched up.

I've just tried accessing one of those albums as an ordinary user, and get a different error "The action you attempted is not permitted. "

AK

suprsidr

Joined: 2005-04-17
Posts: 8339

Posted: Tue, 2012-12-11 13:56

do you have a list of those 71 ids?

-s
________________________________
All New jQuery Minislideshow for G2/G3

Austin Kinsella

Joined: 2007-08-05
Posts: 39

Posted: Tue, 2012-12-11 14:06

+-------+---------------------+ | g_id | g_creationTimestamp +-------+---------------------+ | 1 | 1236968738 | | 2 | 1236968738 | | 3 | 1236968738 | | 4 | 1236968738 | | 5 | 1236968738 | | 6 | 1236968738 | | 11 | 1236968782 | | 13 | 1236968783 | | 14 | 1236976075 | | 15 | 1236976075 | | 16 | 1236976075 | | 17 | 1236976075 | | 18 | 1236976075 | | 19 | 1236976075 | | 20 | 1236976075 | | 22 | 1236976075 | | 23 | 1236976075 | | 30 | 1236976075 | | 34 | 1236976075 | | 36 | 1236976075 | | 4112 | 1260117863 | | 4401 | 1261009535 | | 4421 | 1261049257 | | 4424 | 1261049349 | | 4437 | 1261052580 | | 4440 | 1261052966 | | 4673 | 1264710705 | | 5908 | 1286119603 | | 6311 | 1286879313 | | 6335 | 1286879751 | | 6353 | 1286879970 | | 6365 | 1286880120 | | 6377 | 1286880297 | | 6395 | 1286880597 | | 6407 | 1286880774 | | 6413 | 1286880833 | | 6425 | 1286880980 | | 6461 | 1286881519 | | 6485 | 1286882037 | | 6761 | 1286922994 | | 8278 | 1288221318 | | 8409 | 1288396051 | | 8939 | 1288556606 | | 13417 | 1297623400 | | 13770 | 1297900002 | | 13776 | 1297901510 | | 14943 | 1300490988 | | 14949 | 1300552418 | | 15529 | 1302349459 | | 17933 | 1318868734 | | 19012 | 1325760544 | | 19037 | 1325800102 | | 19142 | 1326140748 | | 19328 | 1326496992 | | 19412 | 1326742367 | | 20837 | 1328314245 | | 26106 | 1349889416 | | 26112 | 1349957161 | | 26211 | 1350145365 | | 26403 | 1350933307 | | 26902 | 1352372507 | | 26914 | 1352373448 | | 26928 | 1352394853 | | 27273 | 1353302050 | | 27279 | 1353318723 | | 27324 | 1353438355 | | 27380 | 1353532778 | | 27400 | 1353540692 | | 27440 | 1353586148 | | 27553 | 1353627025 | | 28167 | 1354145811 | AK --------------+----------+-------------------------+----------------+----------------+------------------+
| g_isLinkable | g_linkId | g_modificationTimestamp | g_serialNumber | g_entityType | g_onLoadHandlers |
--------------+----------+-------------------------+----------------+----------------+------------------+
0 | NULL | 1236968738 | 1 | GalleryEntity | NULL |
0 | NULL | 1236968738 | 1 | GalleryGroup | NULL |
0 | NULL | 1236968738 | 1 | GalleryGroup | NULL |
0 | NULL | 1236968738 | 1 | GalleryGroup | NULL |
0 | NULL | 1236968738 | 1 | GalleryUser | NULL |
0 | NULL | 1236968738 | 1 | GalleryUser | NULL |
0 | NULL | 1236968782 | 1 | GalleryEntity | NULL |
0 | NULL | 1236968783 | 1 | GalleryEntity | NULL |
0 | NULL | 1260051326 | 2 | GalleryUser | NULL |
0 | NULL | 1330185396 | 7 | GalleryUser | NULL |
0 | NULL | 1236976075 | 1 | GalleryUser | NULL |
0 | NULL | 1278443152 | 2 | GalleryUser | NULL |
0 | NULL | 1290037200 | 2 | GalleryUser | NULL |
0 | NULL | 1268399242 | 2 | GalleryUser | NULL |
0 | NULL | 1236976075 | 1 | GalleryUser | NULL |
0 | NULL | 1287604367 | 3 | GalleryUser | NULL |
0 | NULL | 1290852889 | 4 | GalleryUser | NULL |
0 | NULL | 1291576445 | 4 | GalleryUser | NULL |
0 | NULL | 1291565035 | 2 | GalleryUser | NULL |
0 | NULL | 1236976075 | 1 | GalleryUser | NULL |
0 | NULL | 1260117863 | 1 | GalleryGroup | NULL |
0 | NULL | 1325292149 | 4 | GalleryUser | NULL |
0 | NULL | 1261049310 | 2 | WatermarkImage | NULL |
0 | NULL | 1261049369 | 2 | WatermarkImage | NULL |
0 | NULL | 1261052600 | 2 | WatermarkImage | NULL |
0 | NULL | 1261052966 | 1 | GalleryUser | NULL |
0 | NULL | 1291322037 | 4 | GalleryUser | NULL |
0 | NULL | 1286119603 | 1 | GalleryGroup | NULL |
0 | NULL | 1286879313 | 1 | GalleryUser | NULL |
0 | NULL | 1327947310 | 2 | GalleryUser | NULL |
0 | NULL | 1295478315 | 2 | GalleryUser | NULL |
0 | NULL | 1289593999 | 3 | GalleryUser | NULL |
0 | NULL | 1286880297 | 1 | GalleryUser | NULL |
0 | NULL | 1289424477 | 3 | GalleryUser | NULL |
0 | NULL | 1297966448 | 2 | GalleryUser | NULL |
0 | NULL | 1286880833 | 1 | GalleryUser | NULL |
0 | NULL | 1289426317 | 2 | GalleryUser | NULL |
0 | NULL | 1286881519 | 1 | GalleryUser | NULL |
0 | NULL | 1286882037 | 1 | GalleryGroup | NULL |
0 | NULL | 1288446345 | 3 | GalleryUser | NULL |
0 | NULL | 1288856631 | 2 | GalleryUser | NULL |
0 | NULL | 1288396051 | 1 | GalleryEntity | NULL |
0 | NULL | 1288556606 | 1 | GalleryGroup | NULL |
0 | NULL | 1355189848 | 2 | GalleryUser | NULL |
0 | NULL | 1355184305 | 2 | GalleryUser | NULL |
0 | NULL | 1297901510 | 1 | GalleryUser | NULL |
0 | NULL | 1355184214 | 2 | GalleryUser | NULL |
0 | NULL | 1304629649 | 2 | GalleryUser | NULL |
0 | NULL | 1302349459 | 1 | WatermarkImage | NULL |
0 | NULL | 1318868734 | 1 | GalleryUser | NULL |
0 | NULL | 1350046241 | 2 | GalleryUser | NULL |
0 | NULL | 1325800102 | 1 | GalleryUser | NULL |
0 | NULL | 1355184112 | 3 | GalleryUser | NULL |
0 | NULL | 1335558564 | 2 | GalleryUser | NULL |
0 | NULL | 1355184158 | 2 | GalleryUser | NULL |
0 | NULL | 1355184192 | 3 | GalleryUser | NULL |
0 | NULL | 1349889416 | 1 | GalleryUser | NULL |
0 | NULL | 1349957161 | 1 | GalleryUser | NULL |
0 | NULL | 1350257613 | 2 | GalleryUser | NULL |
0 | NULL | 1350933307 | 1 | GalleryUser | NULL |
0 | NULL | 1352372507 | 1 | GalleryUser | NULL |
0 | NULL | 1352373448 | 1 | GalleryUser | NULL |
0 | NULL | 1352394853 | 1 | GalleryUser | NULL |
0 | NULL | 1353302050 | 1 | GalleryUser | NULL |
0 | NULL | 1353318723 | 1 | GalleryUser | NULL |
0 | NULL | 1353438355 | 1 | GalleryUser | NULL |
0 | NULL | 1353532778 | 1 | GalleryUser | NULL |
0 | NULL | 1353540692 | 1 | GalleryUser | NULL |
0 | NULL | 1353586148 | 1 | GalleryUser | NULL |
0 | NULL | 1353627025 | 1 | GalleryUser | NULL |
0 | NULL | 1354145811 | 1 | GalleryUser | NULL

Austin Kinsella

Joined: 2007-08-05
Posts: 39

Posted: Tue, 2012-12-11 14:56

Most, if not all, the problems with this set of albums seem to relate to one owner
If I select g_id,g_description from g2_Item where g_ownerid = 18597; the returned list corresponds to the problem albums

+-------+-----------------------------------------------------------+
| g_id | g_description |
+-------+-----------------------------------------------------------+
| 19223 | missed the first week...my steel and glass is here. |
| 19604 | A stolen Lifebuoy is a stolen Life. Easy Way to RIP 3:52) |
| 20121 | Diversity 3/52 |
| 20941 | Abandoned : Rejected |
| 21349 | Joseph 7/52 |
| 21786 | Joseph 8/52 |
| 22517 | Time (Tea Time!) |
+-------+-----------------------------------------------------------+
7 rows in set (0.00 sec)

Any suggestion for a fix? What if I just change the g_ownerid in g2_Item? The corresponding entities exist.

AK

suprsidr

Joined: 2005-04-17
Posts: 8339

Posted: Tue, 2012-12-11 15:06

Did you run the python script Cry provides in the first post. Report output.

-s
________________________________
All New jQuery Minislideshow for G2/G3

suprsidr

Joined: 2005-04-17
Posts: 8339

Posted: Tue, 2012-12-11 15:47

I'll write a script to change owner to admin - please confirm the admin id is 6 for me.

You cannot just write a query to switch owners, you have to transfer view/edit/delete permissions as well.

-s
________________________________
All New jQuery Minislideshow for G2/G3

Cry Regarder

Joined: 2010-12-07
Posts: 9

Posted: Tue, 2012-12-11 15:54

This looks like the output from the part of my script that checks for things without parents. GalleryUsers don't have parents and shouldn't. I would focus on the four WatermarkImage entries.

| 4421 | 1261049257 | 0 | NULL | 1261049310 | 2 | WatermarkImage | NULL |
| 4424 | 1261049349 | 0 | NULL | 1261049369 | 2 | WatermarkImage | NULL |
| 4437 | 1261052580 | 0 | NULL | 1261052600 | 2 | WatermarkImage | NULL |
| 15529 | 1302349459 | 0 | NULL | 1302349459 | 1 | WatermarkImage | NULL |

What is the deal with them?

Is their any other output from the rest of my script?

Cry

Cry Regarder

Joined: 2010-12-07
Posts: 9

Posted: Tue, 2012-12-11 16:00

WatermarkImages might not have parents either. Are these the images that users uploaded to provide watermarks or are they images that have been marked with watermarks? I'm thinking the former in which case they also aren't a problem.

suprsidr

Joined: 2005-04-17
Posts: 8339

Posted: Tue, 2012-12-11 16:20

upload the attached to yoursite.com/path to gallery2/setNewOwner.php
backup your G2 DB
visit the script via browser just once.
Hopefully no errors

-s
________________________________
All New jQuery Minislideshow for G2/G3

Austin Kinsella

Joined: 2007-08-05
Posts: 39

Posted: Tue, 2012-12-11 16:56

suprsidr, Cry, thank you very much for your help. setNewOwner has fixed it.
When that user was deleted (he has left the country) I must have chosen not to re-assign his images - bad choice.

I didn't run your orphans script, Cry, as it relies on mysql.connector, and I don't know where that is. I don't know python ...

AK

Hints for exploring gallery2 database inconsitencies

XUser login