[fixed] Database error 1064 on upgrade from 2.10.9 > 2.10.10

[muelli75]

Hi!


2.10.9 was up and running without errors. Now, after upgrading to 2.10.10 (including "upgrade to new database") i´m getting the follwing errormessage after sending the "SignIn"-form:

Code: Select all

Database error 1064 while doing query You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ') and phplist_user_user_attribute.attributeid = 1' at line 4


This error is displayed up to 4 times on the "Thank you"-Page.

The user who signed in, gets an confirmation-email.


Any ideas?


TIA!

[mdclayton]

I'm getting the same error after installing 2.10.10 (clean install) It appears to be related to a bug fix incorporated into the 2.10.10 release: http://mantis.phplist.com/view.php?id=15182

I reported this about 4 days ago on another thread, but so far there have been no useful answers posted to that thread or other that refer to error 1064 on 2.10.10.

One drastic work-around is to eliminate any attributes that have checkboxes.

[mdclayton]

FIXED.

Version 2.10.10 has a new function called cleanCommaList() in connect.php. This is to remove null values in the attributes array, but it apparently doesn't work. Taking code from an earlier patch (bug 15182), replace the existing cleanCommaList function (line 1131 in connect.php) with this:

Code: Select all

#function cleanCommaList($list) {
#  return join(',',cleanArray(split(',',$list)));
#}

function cleanCommaList($list) {
   foreach ($list as $key=>$value) {
      if(!$value) {
         array_splice($list, $key, 1);  //Remove null value from array
      }
   }
  return $list;
}


Did this and no more errors of any kind. I don't know how the bug reporting system works here, but if this is useful it should be passed along. Don't know how to change the subject to [FIXED] either...

[H2B2]

Thanks for the fix! Thread marked as such.

[escribes]

Hello:
I have the same problem but I don't know where I must put this code. Can someone help me??

Thanks!

[Pseudonym]

Thanks, mdclayton. You're a star! I only wish I'd found your fix several hours ago!


Escribes, open up /lists/admin/connect.php

Search for the following code (around line 1131 in my version):

Code: Select all

function cleanCommaList($list) {
  return join(',',cleanArray(split(',',$list)));
}


Replace that code with this:

Code: Select all

#function cleanCommaList($list) {
#  return join(',',cleanArray(split(',',$list)));
#}

function cleanCommaList($list) {
   foreach ($list as $key=>$value) {
      if(!$value) {
         array_splice($list, $key, 1);  //Remove null value from array
      }
   }
  return $list;
}


[juliesnooley]

Unfortunately when I implement this fix it totally breaks phplist for me... pages no longer display for anything in the directory "lists". Help!

Thanks!

Julie

UPDATE - Dragonrider helped me fix this issue - it was something about how I was downloading the file - the zipped version he sent me worked fine..

[Dragonrider]

If you are having problems adding this fix, to make things easier for you, I'll attach a file with the fix already done.

Just download it, unzip and upload to your lists/admin/ directory.

Thanks to mdclayton and Pseudonym for your work on this issue.

[juliesnooley]

wow- thanks so much. That worked beautifully!

[negi]

Great Md.
Indispensable hack even in new install (not only after upgrade as reported by the bug fix page http://forums.phplist.com/viewtopic.php?f=9&t=24494#p66892)

I searched for hours in site and code, then met your post.

Thanks

[BestfootDesign]

This patch did work fine, thanks for your efforts on it.

Don't these guys do beta's and test? These seem like pretty major breaks to be honest.

In regards to the person who had problems adding the code manually, you need to move php files in ascii format for them to work properly when using ftp.

Also the location of connect.php is in the admin folder vs any sub-folder.

Next one to try to solve the debug not set properly issue .... lots of error messages being generated by that one too.

[eddierock]

I've updated the file connect.php and still getting the errors when importing a list. I'm importing only these fields:
Email Firstname Lastname Date Vendor CC State Product List Membership

It updates everything except the Product. This is a Checkboxgroup Attribute and there is a lot of chatter on this still being broken. Any ideas where to go from here?

Reading emails from file ... ..ok, 3 lines

Code: Select all

Database error 1064 while doing query You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near product one; product two; product three) and phplist_user_user_attribut' at line 4
Fatal Error: Debugging not configured properly
Database error 1064 while doing query You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near product one; product two) and phplist_user_user_attribute.attribute' at line 4
Fatal Error: Debugging not configured properly

All the emails already exist in the database and are member of the lists
User data was updated for 2 users
0 users were matched by foreign key, 2 by email

I'd LOVE to get this fixed.

EddieRock

[RonFred]

I made the fix in function cleanCommaList and one problem did go away. Thank you very much. But after importing new emails into my database, the problem is back even with this fix.

Error message is the following:
Database error 1064 while doing query You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'public speaking skills) and phplist_user_user_attribute.attributeid = ' at line 4

If I ignore the error, it goes away after saving a change to my user within the phplist - details of a user

** Note ** I belive my new problem is related to email import of bad data. I even get a warning of some sort when I imported my csv user list. So my post here may actually be describing no bug at all, just user error. I mention this because I see others struggling with this issue as well. Perhaps they too can notice if their faulty import actually is creating this problem

[GazOutEast]

I used Dragonrider's patch file and don't seem to be getting the 1064 related to users, but I am getting it related to a footer logo when using a test send - I've scoured the code and database and connot find where it's coming from for the life of me.

It refers to two errors - one for line 1 and one for line 2 - and the "near ...." is slightly different in each case - i.e.

near ....logo.gif"';
and
near ....logo.gif")';

But I cannot find any instance of the image name which is followed by a closing parentheses (brackets for them wot ain't sure)

I'm fairly sure it's related to the footer area, even though it refers to line 1 and 2, though maybe it could be coming out of a [SIGNATURE] config somewhere - for this particular logo, them's the only places the logo appears except in the public and private page headers, and that's not returning any errors for sure.

Well stumped.

Gaz

[alienmau]

I'm still getting the Error and i already change this code
Database Error 1064 while doing query You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'text/css">

i'm using the lastest version of phplist 2.10.14, but the bug still happen,
in the connect.php i have this code:

Code: Select all

function cleanCommaList($list) {
  return join(',',cleanArray(split(',',$list)));
}

function cleanCommaList2($sList) {
  if (!strpos($sList,',')) return $sList;
 //if ((strpos($sList,','))=== false) return $sList;
  $aList = explode(',',$sList);
  foreach ($aList as $key=>$value) {
    if(!$value) {
       array_splice($aList, $key, 1);  //Remove null value from array
    }
  }
  return join(',',$aList);
}


and i change it like you suggest but still the problem

so any other idea so fix this??