BUG: 7.2.1

SuiteCRM Forum - English Language SuiteCRM General Discussion
#1

Using MSSQL I’m getting SQL errors off of the database particularly with the meetings module, I was not seeing these errors before. Below is an excerpt from my sugarcrm.log file:

                                (SELECT users.id ,  N'                                                                                                                                                                                                                                                              ' c_accept_status_fields ,  N'                                    '  call_id ,  N'                                                                                                                                                                                                                                                              ' m_accept_status_fields ,  N'                                    '  meeting_id ,  users.first_name ,  users.last_name ,  LTRIM(RTRIM(ISNULL(users.first_name,'')+N' '+ISNULL(users.last_name,''))) as name,  users.user_name ,  users.phone_work ,  users.created_by  ,  N'users' panel_name  , ROW_NUMBER()
                                OVER (ORDER BY N'                                                                                                                                                                                                                                                              ' m_accept_status_fields ,  N'                                    '  meeting_id ,  users.first_name ,  users.last_name ,  ltrim(rtrim(isnull(users.first_name,'')+N' '+isnull(users.last_name,''))) asc) AS row_number
                                FROM users  LEFT JOIN users_cstm ON users.id = users_cstm.id_c  INNER JOIN  meetings_users ON users.id=meetings_users.user_id AND meetings_users.meeting_id=N'82bf6344-d218-6cef-37b0-54dbab4ae2ce' AND meetings_users.deleted=0

where users.deleted=0)
) AS a
WHERE row_number > 0::: [Microsoft][SQL Server Native Client 11.0][SQL Server]Incorrect syntax near ‘m_accept_status_fields’.
03/13/15 10:50:59 [5028][78e270bd-9d38-bcb5-40a7-53d7ff970e8c][FATAL] am_projecttemplates_project_1 for am_projecttemplates_project_1 failed to load

03/13/15 10:51:09 [5028][5f8fe7fa-752c-fa73-76fe-53d2ea154d33][FATAL] Error retrieving Meeting list: Query Failed:SELECT TOP 10 * FROM
(
(SELECT users.id , N’ ’ c_accept_status_fields , N’ ’ call_id , N’ ’ m_accept_status_fields , N’ ’ meeting_id , users.first_name , users.last_name , LTRIM(RTRIM(ISNULL(users.first_name,’’)+N’ ‘+ISNULL(users.last_name,’’))) as name, users.user_name , users.phone_work , users.created_by , N’users’ panel_name , ROW_NUMBER()
OVER (ORDER BY N’ ’ m_accept_status_fields , N’ ’ meeting_id , users.first_name , users.last_name , ltrim(rtrim(isnull(users.first_name,’’)+N’ ‘+isnull(users.last_name,’’))) asc) AS row_number
FROM users LEFT JOIN users_cstm ON users.id = users_cstm.id_c INNER JOIN meetings_users ON users.id=meetings_users.user_id AND meetings_users.meeting_id=N’82bf6344-d218-6cef-37b0-54dbab4ae2ce’ AND meetings_users.deleted=0

where users.deleted=0)
) AS a
WHERE row_number > 0::: [Microsoft][SQL Server Native Client 11.0][SQL Server]Incorrect syntax near ‘m_accept_status_fields’.
03/13/15 10:51:09 [13204][4d867fca-51fa-d8c6-4519-53d7fe10c831][FATAL] am_projecttemplates_project_1 for am_projecttemplates_project_1 failed to load

03/13/15 10:51:32 [5028][a32b71cb-d69c-a28d-34b7-53d7ff9fd846][FATAL] am_projecttemplates_project_1 for am_projecttemplates_project_1 failed to load

03/13/15 10:51:34 [13204][6a0a72ba-0fc1-3def-5081-53d7ffa48f96][FATAL] am_projecttemplates_project_1 for am_projecttemplates_project_1 failed to load

03/13/15 10:51:39 [5104][4b185251-2bc9-4fb8-aeca-53d7c057723a][FATAL] am_projecttemplates_project_1 for am_projecttemplates_project_1 failed to load

#2

More details required

Upgrade or fresh install ?

Operating System and version ?

Browser and version ?

PHP version ?

Sqlserver Version ?

#3

This is an update installation using the packaged SuiteCRM-Upgrade-7.1.x-to-7.2.1.zip

  • operating system is windows server 2008 R2 running IIS w PHP 5.5.11

-this error seems to be browser independent

-MSSQL 2008 R2 Enterprise

#4

Same trouble here on a fresh install Windows Server + SQL Server.
I guess we should read something like that (for Meetings):


SELECT TOP 10 a.id, a.c_accept_status_fields, a.call_id, a.m_accept_status_fields, a.first_name, a.last_name, a.name, a.user_name,
	a.phone_work, a.created_by, a.panel_name, a.row_number FROM
(
SELECT users.id, N'' c_accept_status_fields, N'' call_id, accept_status m_accept_status_fields, meeting_id, users.first_name, users.last_name, LTRIM(RTRIM(ISNULL(users.first_name,'')+N' '+ISNULL(users.last_name,''))) as name, users.user_name, users.phone_work, users.created_by, N'users' panel_name, ROW_NUMBER()
OVER (PARTITION BY accept_status, meeting_id, users.first_name, users.last_name ORDER BY LTRIM(RTRIM(ISNULL(users.first_name,'')+N' '+ISNULL(users.last_name,''))) asc) AS row_number
 FROM users INNER JOIN meetings_users ON users.id=meetings_users.user_id AND meetings_users.meeting_id=N'7387025b-d6ac-cf3f-6b87-556f40a65227' AND meetings_users.deleted=0
 where users.deleted=0)  a
WHERE row_number > 0

Is there a nice person to tell me what file to modify?
Thank you so much,

Didier
SuiteCRM really rocks!

#5

Two options:

1) Simply comment line 63 on subpaneldefs.php (in \modules\Meetings\metadata) so that the Meeting Details page shows without the error message (but without the list of users concerned by the Meeting):


// See https://suitecrm.com/forum/suitecrm-7-0-discussion/3994-bug-7-2-1  'subpanel_name' => 'ForMeetings',

2) My understanding of the problem is that SQL SERVER doesn’t accept AS alias in an OVER clause so the problem is general and not limited to Meetings… and may arise in other parts of SuiteCRM. Looking at the code, my opinion is that MssqlManager::limitQuery should remove an alias when it’s value is N’ … '?).

So I’ve added one more “fishy regexp” and modified line 457 like this:


// *** ADEMSI - See https://suitecrm.com/forum/suitecrm-7-0-discussion/3994-bug-7-2-1 - Start ***
//$newSQL = "SELECT TOP $count * FROM
//    (
//        " . $matches[1] . $selectPart[1] . ", ROW_NUMBER()
//        OVER (ORDER BY " . $this->returnOrderBy($sql, $orderByMatch[3]) . ") AS row_number
//        " . $selectPart[2] . $orderByMatch[1]. "
//    ) AS a
//    WHERE row_number > $start";
	$ademsi_3994_bug = preg_replace('/\'\s*\'[^,]+?,/i','', $this->returnOrderBy($sql, $orderByMatch[3]));
        $newSQL = "SELECT TOP $count * FROM
                  (
                          " . $matches[1] . $selectPart[1] . ", ROW_NUMBER()
                           OVER (ORDER BY " . $ademsi_3994_bug . ") AS row_number
                            " . $selectPart[2] . $orderByMatch[1]. "
                    ) AS a
                    WHERE row_number > $start";
// *** ADEMSI - See https://suitecrm.com/forum/suitecrm-7-0-discussion/3994-bug-7-2-1 - End ***

And I’m getting the Meeting Detail page with no error message and showing the list of the users: