Here is a test I made with current 2.6.
* album A1 is private, permission to user U1
* album A2 is private, permission to users {U1, U2, U3} and group G1
* album A2/A3 is private, permission to users {U1, U3} and group G1
If I move A2 into A1, then I get:
* album A1 is private, permission to user U1 (unchanged)
* album A1/A2 is private, permission to user U1 (lost permission to U2, U3 and G1)
* album A1/A2/A3 is private, permission to user U1 (lost permission to U2 and G1)
I'm not sure it's the smartest way of changing permissions (changed by [Bugtracker] ticket 2977) but permissions are coherent. Should I rework the algorithm so that A1 gets permission to U2, U3 and G1?
I don't think Piwigo 2.6 generates incoherent permissions with album hierarchy when moving albums. They must have been generated before Piwigo 2.5.3 (2013-10-26).
I will work on 2 tasks:
1) Piwigo 2.6: avoid crash when encountering incoherent permissions
2) Piwigo 2.7: upgrade script to repair permissions
Offline
When moving a public album A1 into a private album A2, A1 becomes private. So in my previous example the removal of extra permissions in sub-albums is coherent with that.
Currently, moving an album make it less visible.
Offline
$ svn diff include/functions_category.inc.php Index: include/functions_category.inc.php =================================================================== --- include/functions_category.inc.php (revision 27838) +++ include/functions_category.inc.php (working copy) @@ -538,6 +538,15 @@ if ( !isset( $cat['id_uppercat'] ) ) continue; + // Piwigo before 2.5.3 may have generated inconsistent permissions, ie + // private album A1/A2 permitted to user U1 but private album A1 not + // permitted to U1. + // + // TODO 2.7: add an upgrade script to repair permissions and remove this + // test + if ( !isset($cats[ $cat['id_uppercat'] ])) + continue; + $parent = & $cats[ $cat['id_uppercat'] ]; $parent['nb_categories']++;
fixes the warnings and SQL crash.
Offline
Still bugged.
Suppose:
A1 private
A2 public
A2/A3 public with existing old permission U1 (it was private, than we put it in public)
You will get errors if
1. You move A2 into A1 because U1 is not cleaned on A3 (move categories will try to clean from all children of A2 the difference between A2 permissions and A1 permissions ... permissions of A3 are not taken into account)
See solution here: http://piwigo.org/forum/viewtopic.php?p … 73#p150373
2. You set A2 to private from Album edit -> A3 is not cleaned because we try to clean differences between current A2 and previous A2 which is empty)
Reported here: http://piwigo.org/forum/viewtopic.php?p … 75#p150375 similar solution to 1.
Last edited by rvelices (2014-03-19 13:35:01)
Offline
Also there is another bug (same issue : a public album with some old permissions in the db):
3. In admin / Albums / Properties -> Public / Private tab when A2 is marked as private, permissions on A3 are not cleaned ...
Offline
You're right, I reproduce the problem (I have tried case 1)
2 solutions :
1) apply the changes you propose on [Forum, post 150373 by rvelices in topic 23482] Error message on my piwigo gallery (I haven't tried it)
2) when setting an album to public, remove all permissions
I think solution 2 is better because I don't see the good reason to keep hidden permissions. What do you think?
Offline
Solution 2 does not solve another case which I didn't list previously:
A1 public
A1/A2 private with U1
When making A1 private from albums double select screen, U1 is not cleared.
So I think the right solution would be to move the checks from function move_categories to set_cat_status by applying my changes and taking into account that you can set several categories to private
And yes we would need a clean upgrade task for next version
Offline
I have been working on the problem. It is a bit complicated and I want to keep the code understandable for the future (maintenance...)
One problem is that you can set several albums as private at the same time and they can be nested. For example :
A1
A1/A2
A1/A2/A3
A1/A2/A4
A1/A5
A6
A6/A7
If you select them all but A1 (here we suppose A1 is public), then you have to:
1) only keep A2, A5 and A6 (remove A3, A4, A7 from the list of permissions to check)
2) on each one of the 3 albums find the "reference album for permissions", ie the parent if parent is private or the album itself if the parent is public
3) for each of the 3 albums find the permissions (maybe hidden) of its reference
4) for each of the 3, "delete from user_access where cat_id in $subcats where user_id not in (<users permitted to the reference>)" (same for groups)
Do you agree with that?
Steps 2, 3 and 4 don't seem complicated. I'm working on step 1: I think I have to loop on all $cat_ids and keep them only if their uppercats doesn't contain another cat_id already listed... happy headache.
Offline
This is working quite well:
function set_cat_status($categories, $value) { echo __FUNCTION__.' = start<br>'; if (!in_array($value, array('public', 'private'))) { trigger_error("set_cat_status invalid param $value", E_USER_WARNING); return false; } // make public a category => all its parent categories become public if ($value == 'public') { $uppercats = get_uppercat_ids($categories); $query = ' UPDATE '.CATEGORIES_TABLE.' SET status = \'public\' WHERE id IN ('.implode(',', $uppercats).') ;'; pwg_query($query); } // make a category private => all its child categories become private if ($value == 'private') { $subcats = get_subcat_ids($categories); $query = ' UPDATE '.CATEGORIES_TABLE.' SET status = \'private\' WHERE id IN ('.implode(',', $subcats).')'; pwg_query($query); $all_categories = array(); $top_categories = array(); $parent_ids = array(); $query = ' SELECT id, name, id_uppercat, uppercats, global_rank FROM '.CATEGORIES_TABLE.' WHERE id IN ('.implode(',', $categories).') ;'; $result = pwg_query($query); while ($row = pwg_db_fetch_assoc($result)) { $all_categories[] = $row; } usort($all_categories, 'global_rank_compare'); echo '<pre>'; print_r($all_categories); echo '</pre>'; foreach ($all_categories as $cat) { $is_top = true; if (!empty($cat['id_uppercat'])) { foreach (explode(',', $cat['uppercats']) as $id_uppercat) { if (isset($top_categories[$id_uppercat])) { $is_top = false; break; } } } if ($is_top) { $top_categories[$cat['id']] = $cat; if (!empty($cat['id_uppercat'])) { $parent_ids[] = $cat['id_uppercat']; } } } echo __FUNCTION__.', top_ids = '.implode(',', array_keys($top_categories)).'<br>'; echo __FUNCTION__.', parent_ids = '.implode(',', $parent_ids).'<br>'; // to find the reference of each top album, we will need the parent albums $parent_cats = array(); if (count($parent_ids) > 0) { $query = ' SELECT id, status FROM '.CATEGORIES_TABLE.' WHERE id IN ('.implode(',', $parent_ids).') ;'; $result = pwg_query($query); while ($row = pwg_db_fetch_assoc($result)) { $parent_cats[$row['id']] = $row; } } echo '<pre>$parent_cats : '."\n"; print_r($parent_cats); echo '</pre>'; $tables = array( USER_ACCESS_TABLE => 'user_id', GROUP_ACCESS_TABLE => 'group_id' ); foreach ($top_categories as $top_category) { // what is the "reference" for list of permissions? The parent album // if it is private, else the album itself $ref_cat_id = $top_category['id']; echo '<pre>'; print_r($top_category); echo '</pre>'; if (!empty($top_category['id_uppercat']) and isset($parent_cats[ $top_category['id_uppercat'] ]) and 'private' == $parent_cats[ $top_category['id_uppercat'] ]['status']) { echo __FUNCTION__.', je prends le parent<br>'; $ref_cat_id = $top_category['id_uppercat']; } echo __FUNCTION__.', $ref_cat_id = '.$ref_cat_id.'<br>'; $subcats = get_subcat_ids(array($top_category['id'])); foreach ($tables as $table => $field) { $query = ' SELECT '.$field.' FROM '.$table.' WHERE cat_id = '.$ref_cat_id.' ;'; $ref_access = array_from_query($query, $field); if (count($ref_access) == 0) { $ref_access[] = -1; } $query = ' DELETE FROM '.$table.' WHERE '.$field.' NOT IN ('.implode(',', $ref_access).') AND cat_id IN ('.implode(',', $subcats).') ;'; echo $query.'<br>'; pwg_query($query); } } } }
Offline
rvelices, can you apply your use cases on [Subversion] r27925 please?
Offline
flop25 wrote:
Hello
could you make a Repair on piwigo tables in the database? Could you also upload by ftp by overwriting existing files, acording to the version you wil found in include/constants.php
Thanks this was a problem I also had. Useful thread for me.
Offline
Hello,
My website disappeared a few days ago and I got exactly the same warning.
Petter wrote:
Hi, i got this error message om my gallery, not quite sure when it show'd up the first time.
How can i fix this, without loosing all my pictures?
Warning: [mysql error 1064] You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ') AND ic.image_id NOT IN (608,609,610,611,612,613,614,615,616,3276,3277,3278,327' at line 5
SELECT tag_id, COUNT(DISTINCT(it.image_id)) AS counter
FROM piwigo_image_category ic
INNER JOIN piwigo_image_tag it
ON ic.image_id=it.image_id
WHERE (category_id NOT IN (35,126,134,139,141,147,168,169,175,176,178,184,190,195,215,7,31,41,56,112,200,236,240,241,242,) AND ic.image_id NOT IN (608,609,610,611,612,613,614,615,616,3276,3277,3278,3279,3280,3281,3282,3283,3284,3285,3286,3287,3288,3289,3290,3291,3292,3293,3294,3295,3296,3297,3298,3299,3300,3301,3302,3303,3304,3305,3306,3307,3308,3309,3310,3311,3312,3313,3314,3315,3316))
GROUP BY tag_id
; in /customers/1/e/4/bortne-mathisen.com/httpd.www/galleri/include/dblayer/functions_mysqli.inc.php on line 798
Fatal error: Call to a member function fetch_assoc() on a non-object in /customers/1/e/4/bortne-mathisen.com/httpd.www/galleri/include/dblayer/functions_mysqli.inc.php on line 181
The funny thing was, I could see everything when I was logged in, but not when I was logged out. I found this thread, but to be honest, I don't understand much of the discussion. But I understood it was something with the permissions, and found an easy way to have it fixed. All I did was to delete the albums (moved the photos to a new album before of course) and it all worked again.
Maybe it is useful info for others who have similar problems.
Thanks guys for the great job!
Hi :-)
Have you active plugin ?
Offline