Drag and drop sorting of galleries will break when a gallery is in multiple collections (terms)

One fix for this is the following:
The table holding the sorted galleries is media_gallery_weight, easy to check.

In the file
media_gallery.pages.inc line 184 to 208 approx we find the following function for reordering galleries

/**
 * Reorder a gallery collection.
 */
function media_gallery_reorder_collection($collection, $order) {
  // Get a complete ordered list of the galleries in this collection.
  $galleries = db_query('SELECT ti.nid FROM {taxonomy_index} ti LEFT JOIN {media_gallery_weight} mgw on ti.nid = mgw.nid  WHERE ti.tid = :tid ORDER BY mgw.weight ASC, ti.nid ASC', array(':tid' => $collection->tid))->fetchCol();
  // Sort the list.
  $galleries = _media_gallery_reorder($galleries, $order);
  // Resave gallery weights for the entire collection.
  db_delete('media_gallery_weight')
    ->condition('tid', $collection->tid)
    ->execute();
  $query = db_insert('media_gallery_weight')
    ->fields(array('tid', 'nid', 'weight'));
  foreach ($galleries as $weight => $nid) {
    $query->values(array(
      'tid' => $collection->tid,
      'nid' => $nid,
      'weight' => $weight,
    ));
  }
  $query->execute();
  return TRUE;
}

Line 189 :

$galleries = db_query('SELECT ti.nid FROM {taxonomy_index} ti LEFT JOIN {media_gallery_weight} mgw on ti.nid = mgw.nid  WHERE ti.tid = :tid ORDER BY mgw.weight ASC, ti.nid ASC', array(':tid' => $collection->tid))->fetchCol();

returns a result that will violate the key constraints of media_gallery_weight and when this happens, first your sort order for the term you are sorting will be deleted and then when inserting the new one it does not work and you get an SQL exception (in the ajax calls), thereby leaving you with a deleted sort order but not updated. This happens if there are galleries with more than one gallery collection taxonomy term and as soon as the primary key constraint is violated. It does not happen immediately, don't be fooled by it looking smooth.

One way to fix the SQL above is to use SELECT DISTINCT, another is to use the full primary key in media_gallery_weight, nid and tid, in the LEFT JOIN condition, rewriting the left join:

...LEFT JOIN {media_gallery_weight} mgw ON (ti.nid = mgw.nid AND ti.tid = mgw.tid) WHERE ... 

This solution has also been posted under the issue Gallery Containers (http://drupal.org/node/1037002), which issue describes how to unhide the taxonomy.

@Moloc
Yes, SELECT DISTINCT would be wrong. Thanks for pointing out (and writing the patch)