View Issue Details

IDProjectCategoryView StatusLast Update
0017417phpList 3 applicationSubscriber Exportpublic29-06-18 11:03
Reporterivilata 
PrioritylowSeverityminorReproducibilitysometimes
Status assignedResolutionreopened 
PlatformDebianOSDebian WheezyOS Version7.5
Product Version3.0.8 
Target Version3.4.0Fixed in Version3.0.9 
Summary0017417: Exporting with "Any date" results in stuck query and timeouts
DescriptionUsing a normal admin (non-superuser) to export subscribers using "Any date" results in the phpList session getting stuck until it times out. Subsequent accesses using the same session also do time out until the session itself expires. Parallel accesses using other sessions do still work.

Curiously enough, choosing a different date restriction for exporting (e.g. "When they signed up") with a sufficiently large time span as to cover all subscriptions does work without problems. The issue does not show either when using a superuser administrator.
Steps To Reproduce  1. Log in as a non-superuser administrator.
  2. Go to "Subscribers / Export subscribers".
  3. Leave "Any date".
  4. Click on "Export"

The session gets stuck until it times out.
Additional InformationWhen looking at "show full processlist" in the MySQL server, this line shows up:

    | Id | User | Host | db | Command | Time | State | Info |
    | ... | ... | ... | ... | Query | 156 | Sending data | select min(date(user.modified)),max(date(user.modified)) from phplist_list list ,phplist_user_user user ,phplist_listuser listuser |

Using "kill query QUERY_ID" to terminate the query seemingly terminates the user session as well: trying to use it again in the browser brings you back to the login screen.
TagsNo tags attached.

Activities

gingerling

25-09-14 12:24

manager   ~0055159

How many subscribers do you have? Are you leaving all the options below (ID email etc) checked?

I can't replicate this so far

ivilata

25-09-14 12:28

reporter   ~0055160

We have some thousands of subscribers, however getting the full subscriber list as a superuser works: Firefox shows the grey "connecting" loop for a tiny while, then the red "loading" loop for some seconds and the list is there. For a non-superuser, it always gets stuck in the grey "connecting" loop.

Yes, I'm leaving all the rest of options checked.

gingerling

25-09-14 12:39

manager   ~0055161

For me, with 1000 subscribers it is totally instant, I made a new not-superuser account to double check.

Can you tell me what the admin settings are for that account so I can make mine the same as yours

ivilata

26-09-14 09:56

reporter   ~0055166

The affected admins are not superadmins and they have the "Manage subscribers", "Send campaigns" and "View statistics" privileges, but not "Change settings".

gingerling

26-09-14 10:38

manager   ~0055170

mmm, tested this now with the same settings for the administrator - still works in less than a second. Will pass it along someone else

ivilata

20-10-14 10:18

reporter   ~0055463

Still happening in 3.0.9. With "Any date" neither the progress bar nor the counter do appear, and after a while the counter box shows "Proxy Error". This time when I kill the stuck query in MySQL the user doesn't get logged out, which is an improvement over the previous behaviour. No problems with superadmins.

michiel

20-10-14 11:45

manager   ~0055465

strange. Can you try running the re-index page, to recreate your DB indexes? You can find it under system.

michiel

20-10-14 11:53

manager   ~0055466

strange. Can you try running the re-index page, to recreate your DB indexes? You can find it under system.

ivilata

20-10-14 13:15

reporter   ~0055467

The same things happen, and not much faster in the case of the superadmin. In the case of a normal admin, the following query gets stuck: select min(date(user.modified)),max(date(user.modified)) from phplist_list list ,phplist_user_user user ,phplist_listuser listuser

I don't know if the query is complete, maybe join conditions on tables are present but not shown.

michiel

20-10-14 13:29

manager   ~0055468

it's stuck on finding the first and last date of your subscribers.

How many subscribers do you have?

ivilata

20-10-14 13:43

reporter   ~0055469

More than 30000, but the problems already showed with < 10000.

michiel

20-10-14 15:00

manager   ~0055471

yes, and 30k shouldn't matter either. I'd expect it to take a little longer with maybe a million emails.

It must be something else.

michiel

20-10-14 15:21

manager   ~0055472

I'm running an export of 2.5 million (test) subscribers and it's working smoothly.

michiel

20-10-14 15:21

manager   ~0055473

maybe open a new issue to diagnose more if you want.

duncanc

17-01-15 12:31

developer   ~0055875

A new post in the user forum asks about fixing the sql that appears to be the problem - https://forums.phplist.com/viewtopic.php?f=17&t=41977

The sql

select min(date(user.modified)),max(date(user.modified)) from phplist_list list ,phplist_user_user user ,phplist_listuser listuser;

is problematic because it is doing a cartesian (or cross) join on the three tables thus dealing with a potentially very large number of rows. That might be alright on a dedicated server but is likely to cause problems for someone using shared hosting.

As the poster says, it is not clear why the listuser and list tables are used in the query. I guess that it is because the same set of tables are used in other queries and it is just convenient to use them here.

duncanc

18-01-15 07:22

developer   ~0055876

Looking more closely at this, the sql is not correct for an ordinary admin.
It should be selecting based on membership of lists that are owned by the admin. Currently it is selecting the earliest and latest modified dates of any user.

michiel

18-01-15 10:11

manager   ~0055877

Last edited: 28-05-18 08:47

View 2 revisions

related: https://github.com/michield/phplist/pull/56

[EDIT] The target of this url appears to have been moved to: https://github.com/phpList/phplist3/pull/56

xheni

29-06-18 11:02

administrator   ~0060806

I don't believe this should block the release 3.3.4 since it has been open for 4 years. I will take a look at this bug for the next release.