Monday, Debtember 17, 2007
“You're kidding, right? That's not seriously the error message, right? Bloody PHP programmers … ”
“Sean,” said Smirk, “there's a trouble ticket in the system I want you to look at.”
“Okay,” I said.
“There's a problem with the port of SugarCRM.” We needed SugarCRM, but needed a version that uses PostgreSQL, not MySQL. It took some effort, but we found a PostgreSQL-based version of SugarCRM.
And there seems to be a problem with it.
I check the ticket.
Appears to be a porting error.
Warning: pg_query() [function.pg-query]: Query failed: ERROR: syntax error at or near "contact_name_owner" LINE 1: ... ' contact_na... ^ in XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXinclude/database/pgsqlManager.php on line 136 Error retrieving Lead list: Query Failed:( SELECT meetings.id , meetings.name, meetings.status, ' ' AS contact_name , ' ' AS contact_id , ' ' contact_name_owner , ' ' contact_name_mod , meetings.date_modified , jt1.user_name AS assigned_user_name , jt1.created_by AS assigned_user_name_owner , 'Users' AS assigned_user_name_mod, ' ' filename , meetings.assigned_user_id , 'meetings' AS panel_name FROM meetings LEFT JOIN users AS jt1 ON jt1.id= meetings.assigned_user_id AND jt1.deleted=0 AND jt1.deleted=0 where ( meetings.parent_id= 'd3e15dc6-9525-d1b8-63fb-474f614635fa' AND meetings.parent_type='Leads' AND meetings.deleted=0 AND (meetings.status='Held' OR meetings.status='Not Held')) AND meetings.deleted=0 ) UNION ALL ( SELECT tasks.id , tasks.name, tasks.status , COALESCE(contacts.first_name,'') || ' ' || COALESCE(contacts.last_name,'') AS contact_name , tasks.contact_id , contacts.assigned_user_id AS contact_name_owner , 'Contacts' AS contact_name_mod, tasks.date_modified , jt1.user_name AS assigned_user_name , jt1.created_by AS assigned_user_name_owner , 'Users' AS assigned_user_name_mod, ' ' filename , tasks.assigned_user_id , 'tasks' AS panel_name FROM tasks LEFT JOIN contacts AS contacts ON contacts.id= tasks.contact_id AND contacts.deleted=0 AND contacts.deleted=0 LEFT JOIN users AS jt1 ON jt1.id= tasks.assigned_user_id AND jt1.deleted=0 AND jt1.deleted=0 where ( tasks.parent_id= 'd3e15dc6-9525-d1b8-63fb-474f614635fa' AND tasks.parent_type='Leads' AND tasks.deleted=0 AND (tasks.status='Completed' OR tasks.status='Deferred')) AND tasks.deleted=0 ) UNION ALL ( SELECT calls.id , calls.name, calls.status, ' ' AS contact_name , ' ' AS contact_id , ' ' contact_name_owner , ' ' contact_name_mod , calls.date_modified , jt1.user_name AS assigned_user_name , jt1.created_by AS assigned_user_name_owner , 'Users' AS assigned_user_name_mod, ' ' filename , calls.assigned_user_id , 'calls' AS panel_name FROM calls LEFT JOIN users AS jt1 ON jt1.id= calls.assigned_user_id AND jt1.deleted=0 AND jt1.deleted=0 where ( calls.parent_id= 'd3e15dc6-9525-d1b8-63fb-474f614635fa' AND calls.parent_type='Leads' AND calls.deleted=0 AND (calls.status='Held' OR calls.status='Not Held')) AND calls.deleted=0 ) UNION ALL ( SELECT notes.id , notes.name, ' ' status , COALESCE(contacts.first_name,'') || ' ' || COALESCE(contacts.last_name,'') AS contact_name , notes.contact_id , contacts.assigned_user_id AS contact_name_owner , 'Contacts' AS contact_name_mod, notes.date_modified, ' ' assigned_user_name , ' ' assigned_user_owner , ' ' assigned_user_mod , notes.filename, notes.created_by , 'notes' AS panel_name FROM notes LEFT JOIN contacts AS contacts ON contacts.id= notes.contact_id AND contacts.deleted=0 AND contacts.deleted=0 where ( notes.parent_id= 'd3e15dc6-9525-d1b8-63fb-474f614635fa' AND notes.parent_type='Leads' AND notes.deleted=0) AND notes.deleted=0 ) UNION ALL ( SELECT emails.id , emails.name, emails.status, ' ' AS contact_name , ' ' AS contact_id , ' ' contact_name_owner , ' ' contact_name_mod , emails.date_modified , jt1.user_name AS assigned_user_name , jt1.created_by AS assigned_user_name_owner , 'Users' AS assigned_user_name_mod, ' ' filename , emails.assigned_user_id , 'emails' AS panel_name FROM emails LEFT JOIN users AS jt1 ON jt1.id= emails.assigned_user_id AND jt1.deleted=0 AND jt1.deleted=0 INNER JOIN emails_leads ON (emails.id=emails_leads.email_id AND emails_leads.lead_id='d3e15dc6-9525-d1b8-63fb-474f614635fa') where ( emails_leads.deleted=0 AND emails.deleted=0) AND emails.deleted=0 ) ORDER BY date_modified desc LIMIT 10 OFFSET 0::PostgreSQL error ERROR: syntax error at or near "contact_name_owner" LINE 1: ... ' contact_na... ^
I could only wish I was making this up.
WTF? And I do mean, seriously, WTF?
I've been looking into The Error From Hell, and seeing how I'm given the exact line number where the error occurs, I thought I'd start there.
So, line 136 from include/database/pgsqlManager.php:
$result = pg_query($sql);
Nice.
So, to see the full query that's failing, I decide to print out the query just before it's made. And that's when I realize that the function this line appears in is called several times throughout the program (not all SQL calls are funneled through this function, just enough to make it painful).
Nice.
Okay, approach the problem from a different angle. What's the function
that this piece of code is in? Oh, that's easy—query().
Okay, try a different approach.
The error complains about a syntax error near
contact_name_owner, so let's try looking for that. Thirty-one
loations where it's used, but none of them appear to construct a 3,544
character long SQL
statement, and the one place where such a statement might be
constructed, isn't called.
Head?
Meet Mr. Desk.
Mr. Desk? Meet my head.
Ouch.
I think I see the problem:
…
' ' AS contact_name , ' ' AS contact_id , ' ' contact_name_owner…
It seems as if there's a few missing ASes in the
SQL Statement From
Hell, but not knowing where it's constructed (or even how), it's
not that easy to fix.
Or rather … there is a way …
Just before I call pg_query(), scan the string, looking for
missing ASes and add them as required, maybe using some form of
regular expression search and replace funct—[at
this point, Sean's head exploded from even entertaining such a notion and
was pronounced “mostly dead” at the scene. Further updates as they
arrive. —Editor]
