Please describe the issue you're having
If you accept the answer, please mark the topic as [SOLVED] by clicking the tick.

Moderators: RussH, cptr13

Forum rules: Just please remember to play nicely once you walk through the door. You can disagree with us, or any other commenters in this forum, but respect our space and keep your comments directed to the topic at hand.
#3478
Hi Everyone

I am now begin with Ubuntu 16.04 LTS VPS system. Again i having MySQL Error when i try go candidate & Companies Tab.


OS - Ubuntu 16.04LTS
Opencats Version - opencats-0.9.4-full.zip
Web Server - Apache
Database - MySQL


Candidate Tab
Code: Select all
MySQL Query Failed: Expression #6 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'opencats.candidate_joborder_submitted.candidate_joborder_id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

SELECT SQL_CALC_FOUND_ROWS 
                candidate.candidate_id AS candidateID,
                candidate.candidate_id AS exportID,
                candidate.is_hot AS isHot,
                candidate.date_modified AS dateModifiedSort,
                candidate.date_created AS dateCreatedSort,
            IF(candidate_joborder_submitted.candidate_joborder_id, 1, 0) AS submitted,
                                                IF(attachment_id, 1, 0) AS attachmentPresent,
candidate.first_name AS firstName,
candidate.last_name AS lastName,
candidate.city AS city,
candidate.state AS state,
candidate.key_skills AS keySkills,
owner_user.first_name AS ownerFirstName,owner_user.last_name AS ownerLastName,CONCAT(owner_user.last_name, owner_user.first_name) AS ownerSort,
DATE_FORMAT(candidate.date_created, '%d-%m-%y') AS dateCreated,
DATE_FORMAT(candidate.date_modified, '%d-%m-%y') AS dateModified
            FROM
                candidate
            LEFT JOIN attachment
                                                        ON candidate.candidate_id = attachment.data_item_id
														AND attachment.data_item_type = 100
                                                    LEFT JOIN candidate_joborder AS candidate_joborder_submitted
                                                        ON candidate_joborder_submitted.candidate_id = candidate.candidate_id
                                                        AND candidate_joborder_submitted.status >= 400
                                                        AND candidate_joborder_submitted.site_id = 1
                                                        AND candidate_joborder_submitted.status != 650
LEFT JOIN user AS owner_user ON candidate.owner = owner_user.user_id LEFT JOIN saved_list_entry
                                    ON saved_list_entry.data_item_type = 100
                                    AND saved_list_entry.data_item_id = candidate.candidate_id
                                    AND saved_list_entry.site_id = 1
            WHERE
                candidate.site_id = 1
            
            
            
            GROUP BY candidate.candidate_id
            
            ORDER BY dateModifiedSort DESC
            LIMIT 0, 15
Companies Tab
Code: Select all
MySQL Query Failed: Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'opencats.attachment.attachment_id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

SELECT SQL_CALC_FOUND_ROWS 
                IF(attachment_id, 1, 0) AS attachmentPresent,
                company.is_hot AS isHot,
                company.company_id AS companyID,
                company.company_id AS exportID,
                company.is_hot AS isHot,
                company.date_modified AS dateModifiedSort,
                company.date_created AS dateCreatedSort,
            IF(attachment_id, 1, 0) AS attachmentPresent,
company.name AS name,
(
                                                            SELECT
                                                                COUNT(*)
                                                            FROM
                                                                joborder
                                                            WHERE
                                                                company_id = company.company_id
                                                            AND
                                                                site_id = 1
                                                        ) AS jobs,
company.city AS city,
company.state AS state,
company.phone1 AS phone,
owner_user.first_name AS ownerFirstName,owner_user.last_name AS ownerLastName,CONCAT(owner_user.last_name, owner_user.first_name) AS ownerSort,
DATE_FORMAT(company.date_created, '%d-%m-%y') AS dateCreated,
DATE_FORMAT(company.date_modified, '%d-%m-%y') AS dateModified
            FROM
                company
            LEFT JOIN user AS owner_user
                ON company.owner = owner_user.user_id
            LEFT JOIN joborder
                ON company.company_id = joborder.company_id
            LEFT JOIN contact
                ON company.billing_contact = contact.contact_id
            LEFT JOIN attachment
                ON company.company_id = attachment.data_item_id
                AND attachment.data_item_type = 200
             LEFT JOIN saved_list_entry
                                    ON saved_list_entry.data_item_type = 200
                                    AND saved_list_entry.data_item_id = company.company_id
                                    AND saved_list_entry.site_id = 1
            WHERE
                company.site_id = 1
            
            
            GROUP BY company.company_id
            
            ORDER BY dateCreatedSort DESC
            LIMIT 0, 15
#3575
Hi Karthikjoe..

Can you confirm you are still seeing problems, can you ensure you're installing the latest version 0.9.4-2 and that you you're running a supported version of PHP / MySQL (as per the release notes!)

https://github.com/opencats/opencats/releases

Other than that - please reply back if you are encountering difficulties
#3647
bruce7890 wrote:Same problem here - php is 5.7 and mysql reports Ver 14.14 Distrib 5.7.21, for Linux - any ideas?

Hi Bruce, seems fairly straightforward - the error message is "incompatible with sql_mode=only_full_group_by" - some googling on this shows that this was off by default but is now ON.

Resolve this by;
https://stackoverflow.com/questions/239 ... l-group-by

EDIT: looks like this changed very very recently on MySQL 5.7.21 (2018-01-15, General Availability)

It's essential to keep these synchronized to ensur[…]

Export part in Job Order order search

Same problem as mine. Maybe you're right, I'll che[…]

The error message you're encountering indicates th[…]

Errors restoring mysqldump backup

doesnot work for me