bad sql syntax on some pages after upgrade

[gil shinar]

Hi All,

After upgrading from 4.5.1 to 5.1, two of the pages had sql error messages while trying to enter them.
I have rebuild the database and could load these pages but I still cannot see the bottom of these pages because of another error message that after investigating it a little bit I got to the conclusion that there's an sql syntax error. The error for one of the pages looks like:
SELECT skillset.id , skillset.name , skillset.id candidate_rate_fields, skillset.assigned_user_id , 'skillset' panel_name FROM skillset AND skillset.deleted=0 where skillset.deleted=0 LIMIT 0,40

and I think it should look like this:
SELECT skillset.id , skillset.name , skillset.id candidate_rate_fields, skillset.assigned_user_id , 'skillset' panel_name FROM skillset where skillset.deleted=0 LIMIT 0,40

The extra AND causes the problem.
The other page has the following error:
SELECT candidate.id , candidate.name , candidate.id candidate_rate_fields, candidate.assigned_user_id , 'candidate' panel_name FROM candidate AND candidate.deleted=0 where candidate.deleted=0 LIMIT 0,40

where I think it should look like:
SELECT candidate.id , candidate.name , candidate.id candidate_rate_fields, candidate.assigned_user_id , 'candidate' panel_name FROM candidate where candidate.deleted=0 LIMIT 0,40

When I try to farther use the repair option from the admin page, it just makes it worse.
I'm trying to look for these sql queries in the php files with no success.

Can anyone help?

Thanks in advance

Gil

[eggsurplus]

Sugar expects there to always be a deleted column (well mostly). My suggestion would be to just add the column and be done with it.

[gil shinar]

Sugar expects there to always be a deleted column (well mostly). My suggestion would be to just add the column and be done with it.

This column is sure there. Notice that after the AND phrase there's a where candidate.delete=0 and that works just fine.

Thanks

[eggsurplus]

Can you post the actual syntax error that is being returned and not just the sql query?

[gil shinar]

Sure

Error retrieving Candidate list: Query Failed: SELECT skillset.id , skillset.name , skillset.id candidate_rate_fields, skillset.assigned_user_id , 'skillset' panel_name FROM skillset AND skillset.deleted=0 where skillset.deleted=0 LIMIT 0,40::MySQL error 1064:

The upper part of the page is displayed correctly. The error message is about the bottom side of it

[eggsurplus]

There's usually more to the error that gets display. If that's not the case can you run this query directly against the database and post the error that gets returned?

[gil shinar]

ERROR 1064 (42000): 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 skillset.deleted=0 where skillset.deleted=0' at line 1

BTW, are there any logs?

[eggsurplus]

Thanks, I see it now. The issue is that Sugar dynamically builds the where query and things have changed between 4.5 and 5.2 for custom modules. Any number of changes could have caused this issue. There's no painless way of going at it. You could recreate the modules using Studio, work backwards through the code to find the cause, or create a completely new module through Studio and compare all of the differences between the the new module and your existing ones to see if anything sticks out.

[gil shinar]

Thanks, I see it now. The issue is that Sugar dynamically builds the where query and things have changed between 4.5 and 5.2 for custom modules. Any number of changes could have caused this issue. There's no painless way of going at it. You could recreate the modules using Studio, work backwards through the code to find the cause, or create a completely new module through Studio and compare all of the differences between the the new module and your existing ones to see if anything sticks out.

I have no idea what do you mean by recreating the modules using Studio. Can you please elaborate?
All day I'm working backwards through the code with no success so far. I'm not even sure where should I look in. Can you please point me to the files that are usually dealing with the mysql queries?

Thanks a lot

[eggsurplus]

It doesn't work that way in Sugar. The queries are built across multiple files. There isn't a file that contains the full query for every page. That's why Sugar is so powerful. The downside are issues like these when upgrading.

Using Studio (actual Module Builder in this case) you can create a module from scratch and deploy it. Then you'd be able to compare the generated code with your Skillset module code to see what the differences are. If this isn't your own module but downloaded from somewhere else perhaps they have an updated version for your version of Sugar.