View Issue Details
| ID | Project | Category | View Status | Date Submitted | Last Update |
|---|---|---|---|---|---|
| 0017417 | phpList 3 application | Subscriber Export | public | 25-09-14 13:19 | 18-02-19 10:33 |
| Reporter | ivilata | Assigned To | |||
| Priority | low | Severity | minor | Reproducibility | sometimes |
| Status | assigned | Resolution | reopened | ||
| Platform | Debian | OS | Debian Wheezy | OS Version | 7.5 |
| Product Version | 3.0.8 | ||||
| Target Version | 3.5.0 | Fixed in Version | 3.0.9 | ||
| Summary | 0017417: Exporting with "Any date" results in stuck query and timeouts | ||||
| Description | Using 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 Information | When 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. | ||||
| Tags | No tags attached. | ||||
|
|
How many subscribers do you have? Are you leaving all the options below (ID email etc) checked? I can't replicate this so far |
|
|
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. |
|
|
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 |
|
|
The affected admins are not superadmins and they have the "Manage subscribers", "Send campaigns" and "View statistics" privileges, but not "Change settings". |
|
|
mmm, tested this now with the same settings for the administrator - still works in less than a second. Will pass it along someone else |
|
|
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. |
|
|
strange. Can you try running the re-index page, to recreate your DB indexes? You can find it under system. |
|
|
strange. Can you try running the re-index page, to recreate your DB indexes? You can find it under system. |
|
|
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. |
|
|
it's stuck on finding the first and last date of your subscribers. How many subscribers do you have? |
|
|
More than 30000, but the problems already showed with < 10000. |
|
|
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. |
|
|
I'm running an export of 2.5 million (test) subscribers and it's working smoothly. |
|
|
maybe open a new issue to diagnose more if you want. |
|
|
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. |
|
|
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. |
|
|
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 |
|
|
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. |
