View Issue Details

IDProjectCategoryView StatusLast Update
0009001phpList 3 applicationGeneralpublic18-02-08 14:06
Reporterskoledin Assigned To 
Status resolvedResolutionfixed 
Product Version2.10.4 
Target Version2.10.7Fixed in Version2.10.5 
Summary0009001: "Database error 1054" when viewing list members
DescriptionI've just upgraded from 2.8.12 to 2.10.4 and have migrated the database and everything seems to be working fine except for one issue.

I have a number of lists and approximately 85,000 users. When viewing the lists (/lists/admin/?page=members&id=xx), some of the lists always throw a number of the following error:

Database error 1054 while doing query Unknown column 'Array' in 'where clause'

The number of errors varies for each list from 0-10 or so, but remains consistent and the same for each particular list.

The errors are all displayed directly after the "Tag all users in this page" option and right before the headers for the list of members.

I took a look at both the HTML source and members.php but nothing jumped out at me.

Any ideas about what may be causing this, whether it's serious, and where to look for the issue? Of course, a fix is certainly welcome, but I don't mind dealing with it myself, just looking for a few clues about where to dig in.

TagsNo tags attached.


related to 0003721 closed phplist 2.10.x 
related to 0015527 resolvedmichiel Upgrading database from 2.11.5 to 2.11.5 



22-01-07 22:43

reporter   ~0022780

further investigation shows the following query as one of the culprits:

          from user_attribute,listattr_howdidyouh
          where user_attribute.userid = 191 and
 in (Array) and
          user_attribute.attributeid = 9

Seems strange that the system is querying lots of attribute information when we're just trying to get the set of users in a list. For this display, it looks like only id, email, confirmed status, and number of messages are needed, why all the extra queries?

I managed to clear/stop the error for this particular user by setting and option in one of my multi-checkbox attributes.



22-01-07 22:55

administrator   ~0022782

what attribute is the one using that table? I'll have a look if I can replicate it.


11-03-07 17:01

manager   ~0024611

Not sure whether this is related, but Emily reports this possible bug in :

I found what appears to be a bug in admin/commonlib/pages/importcsv.php

I just installed v. 2.10.4 and the error "Database error 1054 Unknown column 'id' in 'field list'" was coming up on the import with different attributes page.

I changed line 682 from:
   if (Sql_Table_Exists("groups") {

   if (Sql_Table_Exists($tables["groups"])) {

and now it appears to be working fine.


11-03-07 17:31

administrator   ~0024612

phplist doesn't have a table called "groups". It's webbler related and it's a bit sloppy coding to avoid the problem on systems that are not a Webbler as well. So your fix, however avoiding the problem, doesn't sort it.

I'll add some extra checks to ensure that the code is only used in phplist/webbler systems.


27-04-07 21:55

administrator   ~0026364

Hernan, can you try to replicate this problem?


06-05-07 05:34

reporter   ~0026626

I'm having this error and have fixed it as follows:

In the file /admin/commonlib/lib/userlib.php

line 174:

Change this:
$table_prefix"."listattr_".$att["tablename"].".id in ($val_ids[0]) and

to this:
$table_prefix"."listattr_".$att["tablename"].".id in ('$val_ids[0]') and

notice i simply quoted the val_ids[0]. I was able to trace it down to this query failing.

I have to say.. phplist code is so sloppy. Have you ever considered taking some time to refractor it and clean it up?


06-05-07 13:03

administrator   ~0026627

the fix in 0009001:0026626 may avoid the DB error, but it doesn't actually correct the problem. Instead it will look for values being "Array", and simply not return anything.

Yes, certain parts of phplist are getting a bit old (5+ years). Are you offering to help, cleaning up?


07-05-07 03:42

reporter   ~0026629

In my case it wasn't looking for "Array" but other values such as "Online" etc.. meaning the query was looking like this ... IN (Online) .... i checked and all the values that ended up in the statement was an attribute value. So it made no sense that it wouldn't have quoted them. It won't matter what the value is, quoting it can't be incorrect.

In the case of the other users problem, i'm willing to bet if they check, one of the arribute values is "Array". It's not actually an array here but the text "Array"..

of course i didn't really track this down further to see exactly what it's trying to do here..

But like i said i can't see how quoting it could cause any problems. It doesn't matter what the value in ($val_ids[0]) is.. if it's numeric quoting it won't hurt. Based on the name of the variable it's epecting an ids so it's probably numberic most of the time. Again quoting it won't hurt.

It solves the database errors, but like the last note said, it's never going to find anything as it's looking for ids here. But again it never CAN find anything when it's putting string values in there.

So, to conclude:

This error is a sympom of some other problem that put these string values in here in the first place where they should be. Putting the quotes in there solves the symtom but not the source problem.


07-05-07 04:16

reporter   ~0026630

Yes i would volunteer my time for a refractoring effort. But I'd only do it given the right conditions:

1. A complete freeze on any new features
2. Some meeting of the minds for a code design (like figuring out what functions we'd need, and break it all up into classes etc..)

I don't know if the team would be willing to do #1, but i really think it would help. It seems phplist, as great as it is, is prone to odd errors that are really hard to track out in the big mess of code.


07-05-07 13:57

administrator   ~0026631

that's fantastic. I think it would be good to discuss this on the developers mailinglist.

1. code freeze is fine, in fact it's constantly frozen, as I only sporadically work on the code full stop.
2. I may want to code a little bit before refactoring, I'm currently in need to add database replication support in the Sql.
3. on the mailinglist we can discuss a little on what coding standards we want to follow, and how to work it out, partly with PHP6 in mind (ie defaulting to no magic quotes, no safe mode, no register globals etc). The current code is all over the place in that respect, which is probably partly the cause of errors at times.
4. if we get a few more people to help out, with say 4 or 5 people it should be quite possible to get it done in a fairly good time span.
5. the "team" is currently just me, so that's easy to get decided :-)


07-05-07 18:16

reporter   ~0026642

can you contact me directly via email? I think my account here has my email in, don't want the whole world to email me.


15-07-07 02:37

reporter   ~0029349

Glad to see the development team is showing signs of growth again. Best whishes and good coding!

Returning to the original report posted above, it might be possible the upgrade from 2.8 to 2.10.4 misses one or two steps.

These are the findings of *Karma* in this forum post:

"It seems there is a much simpler solution to this problem.


- My server was upgraded to MySQL 5.
- I updated my 2.8.12 installation of phplist to 2.10.4. It told me the update was successful.
- Trying to send a message was giving me the error:
Database error 1054 while doing query Unknown column 'repeatinterval' in 'field list'
- I ran the function 'dbcheck' (database check) from the main admin page. It showed me that, in the messages table, the field 'repeatinterval' did not exist. (red X). Everything else was fine.
- I decided to examine the database file with phpMyAdmin. In the messages table, there is a field named 'repeat', occupying the location that the code seems to expect for 'repeatinterval'.
- Acting on a hunch (after reading some other posts talking about how 'repeat' may have been changed to avoid a conflict with some keyword in MySQL), I decided to simply use phpMyAdmin to edit the name of the field in the messages tabel from 'repeat' -> 'repeatinterval'.
- checking with the 'dbcheck' function again now shows everything is fine.

VOILA! - problem gone, everything back to normal, test messages are working, no errors.

Apparently the database updating procedure is supposed to rename this field, but in my case, it did not.

Apparently the code mentioned above or in the other links here will do a similar thing, by letting the table field stay named 'repeat', and substituting a renamed variable in the code, but it seems that just renaming the field to 'repeatinterval' is the correct solution."



15-07-07 02:50

administrator   ~0029350

ok, great, so the solution is:

any upgrade should, just to be sure, check for the "repeat" column and rename it "repeatinterval" (regardless of previous version). This column was indeed renamed to avoid the use of reserved words in Mysql 5.


16-07-07 19:08

administrator   ~0029405

Ok, the upgrade code will double check on the "repeat" column and rename it. However, if the database has already been upgraded to Mysql 5 before this is called, it will throw an error because "repeat" will be a reserved word. I've added a reference to this issue in the code, when this happens so that people can fix this manually.