[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

[EP-tech] Re: "Manage deposits" list not sorting



Hi Robin,

I think I have got to the bottom of this issue and it is a fairly simple
fix but was an absolute pain to diagnose.  Hopefully this will fix your
problem as well.

It looks like MariaDB does not sort sub-queries which is needed by
EPrints to reorder an EPrints::List.  This is how the ordering of the
table on the Manage Deposits page is ordered.  More detail can be found
on this issue on MariaDB at:

https://mariadb.com/kb/en/mariadb/why-is-order-by-in-a-from-subquery-ignored/

I am not sure if this is only an issue with MariaDB or whether later
versions of MySQL have the same issue.  Therefore, even if you are
running MySQL it is worth giving my (slightly hacky) fix a try.

Below is a rough diff of what I needed to change in
perl_lib/Database/mysql.pm to make things work:

--- mysql.pm	(revision 9244)
+++ mysql.pm	(working copy)
@@ -397,7 +397,9 @@
 	$sql .= "($Q_pos, $Q_keyname)";
 	$sql .= " SELECT \@i:=\@i+1, $Q_keyname";
-	$sql .= " FROM ($select_sql) ".$self->quote_identifier( "S" );
+ # MariaDB does not order sub-queries unless limited. Using limit of
2^31-1 in case any system is using a signed 32-bit integer.
+	my $limit = " LIMIT 2147483647";
+        $limit = "" if $select_sql =~ /LIMIT/;
+        $sql .= " FROM ($select_sql$limit)
".$self->quote_identifier( "S" );
 
 	$self->do( $sql );
 }

Hope this helps,

David Newman





On Tue, 2015-09-15 at 19:44 +0100, Adam Field wrote:

> Oooohhhh, complicated
> 
> 
> 
> OK, you'll need to check the user record to see which columns they
> have turned on, and then order by the first one, as I believe that is
> the one default ordering applies to.
> 
> 
> A better idea, though, would be to figure out what's going on.
> 
> 
> Is anyone else on the list experiencing this problem?
> 
> 
> --
> Adam Field
> Business Relationship Manager and Community Lead
> EPrints Services
> 
> 
> 
> > On 8 Sep 2015, at 14:30, Robin Sylvestre
> > <robin.sylvestre at polymtl.ca> wrote:
> > 
> > 
> > 
> > Thank you Adam!
> > 
> > I can confirm it works better now, although there is still one thing
> > left. 
> > When we get to the "manage deposits" list, it always appears as if
> > sorted by 
> > descending eprint ids, regardless of what should be the active
> > column. The 
> > list gets sorted correctly only after we click on the column's
> > title.
> > 
> > For instance, see the attached screenshots: When the page is loaded 
> > (screenshot001) and what it should be (screenshot002).
> > 
> > Any ideas?
> > 
> > Robin Sylvestre
> > 
> > 
> > 
> > -----Message d'origine-----
> > De : Field A.N. [mailto:af05v at ecs.soton.ac.uk]
> > Envoy? : 7 septembre 2015 06:45
> > ? : eprints-tech at ecs.soton.ac.uk
> > Objet : [EP-tech] Re: "Manage deposits" list not sorting
> > 
> > I've seen this issue before -- in the case I examined, it looked
> > like the 
> > _buffer_order parameter was somehow being lost.  I managed to get it
> > working 
> > with the following code in the Items plugin:
> > 
> > in the 'properties_from' function:
> > 
> >        #af05v 20150512 Workaround for immediate solution to lack of 
> > ordering
> >        my $order = $session->param('_buffer_order');
> >        $self->{processor}->{af05v_order} = $order;
> > 
> > 
> > in the perform_search function:
> > 
> >        my $list =
> > $self->{session}->current_user->owned_eprints_list( 
> > %$search, # custom_order => $search->{order}
> >        #af05v 20150512 Workaround for immediate solution to lack of 
> > ordering
> >                custom_order => $self->{processor}->{af05v_order}
> >        );
> > 
> > However, I didn't have time to figure out *why* this was happening.
> >  Anyone 
> > have any ideas?
> > 
> > --
> > Adam Field
> > Business Relationship Manager and Community Lead EPrints Services
> > +44 (0)23 8059 8814
> > 
> > 
> > 
> > 
> > 
> > On 2 Sep 2015, at 17:48, Robin Sylvestre wrote:
> > 
> > 
> > > Hello fellow EPrinters,
> > > 
> > > Running version 3.3.14, we have noticed a strange behavior on the
> > > ?manage 
> > > deposits? screen. Items do not sort at all, when clicking on the 
> > > appropriate column title (see attached screen shots). Nothing
> > > changes, 
> > > only the up/down arrow appears.
> > > 
> > > The strange thing is that the ?revision? list is not affected by
> > > this bug, 
> > > only the ?deposits? list.
> > > 
> > > I have tried to do a ?epadmin reorder [archive] eprint? but that
> > > does not 
> > > correct the problem. Both of our live and test versions have this
> > > problem.
> > > 
> > > Did anyone notice a similar bug or has a solution to offer?
> > > Thanks!!
> > > 
> > > 
> > > 
> > > Robin Sylvestre
> > > Technicien en documentation
> > > Biblioth?que - Services techniques et syst?mes informatis?s ?cole
> > > Polytechnique de Montr?al
> > > T?l.: 514-340-4711 poste 3743
> > > 
> > > <ScreenShot001.png><ScreenShot003.png>*** Options:
> > > http://mailman.ecs.soton.ac.uk/mailman/listinfo/eprints-tech
> > > *** Archive: http://www.eprints.org/tech.php/
> > > *** EPrints community wiki: http://wiki.eprints.org/
> > > *** EPrints developers Forum: http://forum.eprints.org/
> > 
> > 
> > 
> > 
> > <ScreenShot001.png><ScreenShot002.png>*** Options:
> > http://mailman.ecs.soton.ac.uk/mailman/listinfo/eprints-tech
> > *** Archive: http://www.eprints.org/tech.php/
> > *** EPrints community wiki: http://wiki.eprints.org/
> > *** EPrints developers Forum: http://forum.eprints.org/
> > 
> 
> 
> 
> 
> *** Options: http://mailman.ecs.soton.ac.uk/mailman/listinfo/eprints-tech
> *** Archive: http://www.eprints.org/tech.php/
> *** EPrints community wiki: http://wiki.eprints.org/
> *** EPrints developers Forum: http://forum.eprints.org/


-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://mailman.ecs.soton.ac.uk/pipermail/eprints-tech/attachments/20150922/fa7a9083/attachment.html