Forums Forums Community Discussion Error in MYSQL syntax – add on extra field, sorting and etc

Tagged: 

This topic contains 2 replies, has 2 voices, and was last updated by  mpky 1 year, 8 months ago.

  • Author
    Posts
  • #2227

    mpky
    Participant

    Hi Tamil,
    The system become unstable after migration. We have done all the necessary steps as per your advice but still facing problems as followings:

    1) customize extra field error
    PDOException Object ( [message:protected] => SQLSTATE[42000]: Syntax error or access violation: 1166 Incorrect column name ‘Date Opening ‘ [string:Exception:private] => [code:protected] => 42000 [file:protected] => /var/www/html/cats/lib/ClsNaanalPDO.php [line:protected] => 124 [trace:Exception:private] => Array ( [0] => Array ( [file] => /var/www/html/cats/lib/ClsNaanalPDO.php [line] => 124 [function] => execute [class] => PDOStatement [type] => -> [args] => Array ( ) ) [1] => Array ( [file] => /var/www/html/cats/lib/ClsNaanalPDO.php [line] => 555 [function] => query [class] => ClsNaanalPDO [type] => -> [args] => Array ( [0] => ALTER IGNORE TABLE joborder ADD COLUMN Date Opening VARCHAR(255) default NULL ) ) [2] => Array ( [file] => /var/www/html/cats/lib/DatabaseConnectionPDO.php [line] => 149 [function] => setQuery [class] => ClsNaanalPDO [type] => -> [args] => Array ( [0] => ALTER IGNORE TABLE joborder ADD COLUMN Date Opening VARCHAR(255) default NULL ) ) [3] => Array ( [file] => /var/www/html/cats/lib/ExtraFields.php [line] => 159 [function] => query [class] => DatabaseConnection [type] => -> [args] => Array ( [0] => ALTER IGNORE TABLE joborder ADD COLUMN Date Opening VARCHAR(255) default NULL ) ) [4] => Array ( [file] => /var/www/html/cats/modules/settings/SettingsUI.php [line] => 2134 [function] => define [class] => ExtraFields [type] => -> [args] => Array ( [0] => Date Opening [1] => 6 ) ) [5] => Array ( [file] => /var/www/html/cats/lib/ModuleUtility.php [line] => 196 [function] => onCustomizeExtraFields [class] => SettingsUI [type] => -> [args] => Array ( ) ) [6] => Array ( [file] => /var/www/html/cats/index.php [line] => 350 [function] => loadModule [class] => ModuleUtility [type] => :: [args] => Array ( [0] => settings ) ) ) [previous:Exception:private] => [errorInfo] => Array ( [0] => 42000 [1] => 1166 [2] => Incorrect column name ‘Date Opening ‘ ) ) ;

    2) Sorting field error
    PDOException Object ( [message:protected] => SQLSTATE[42000]: Syntax error or access violation: 1064 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 ‘Opening ASC LIMIT 0, 50’ at line 59 [string:Exception:private] => [code:protected] => 42000 [file:protected] => /var/www/html/cats/lib/ClsNaanalPDO.php [line:protected] => 124 [trace:Exception:private] => Array ( [0] => Array ( [file] => /var/www/html/cats/lib/ClsNaanalPDO.php [line] => 124 [function] => execute [class] => PDOStatement [type] => -> [args] => Array ( ) ) [1] => Array ( [file] => /var/www/html/cats/lib/ClsNaanalPDO.php [line] => 555 [function] => query [class] => ClsNaanalPDO [type] => -> [args] => Array ( [0] => SELECT SQL_CALC_FOUND_ROWS joborder.joborder_id AS jobOrderID, joborder.joborder_id AS exportID, joborder.date_modified AS dateModifiedSort, joborder.date_created AS dateCreatedSort, joborder.is_hot AS isHot, IF(attachment_id, 1, 0) AS attachmentPresent, joborder.title AS title, company.name AS companyName, company.company_id AS companyID, joborder.status AS status, DATE_FORMAT(joborder.date_created, ‘%d-%m-%y’) AS dateCreated, ( SELECT COUNT(*) FROM candidate_joborder_status_history WHERE joborder_id = joborder.joborder_id AND status_to = 400 AND site_id = 1 ) AS submitted, ( SELECT COUNT(*) FROM candidate_joborder WHERE joborder_id = joborder.joborder_id AND site_id = 1 ) AS pipeline, recruiter_user.first_name AS recruiterFirstName,recruiter_user.last_name AS recruiterLastName,CONCAT(recruiter_user.last_name, recruiter_user.first_name) AS recruiterSort, Gender AS Gender, Job Opening AS Job Opening FROM joborder LEFT JOIN company ON joborder.company_id = company.company_id LEFT JOIN contact ON joborder.contact_id = contact.contact_id LEFT JOIN attachment ON joborder.joborder_id = attachment.data_item_id AND attachment.data_item_type = 400 LEFT JOIN user AS recruiter_user ON joborder.recruiter = recruiter_user.user_id LEFT JOIN saved_list_entry ON saved_list_entry.data_item_type = 400 AND saved_list_entry.data_item_id = joborder.joborder_id AND saved_list_entry.site_id = 1 WHERE joborder.site_id = 1 AND joborder.is_admin_hidden = 0 AND (joborder.status = ‘Active’ OR joborder.status = ‘OnHold’ OR joborder.status = ‘Full’ ) AND ((joborder.owner = 1255 and joborder.ownertype=0) OR (joborder.owner = 1 and joborder.ownertype=1) OR (joborder.owner = 1 and joborder.ownertype=1)) GROUP BY joborder.joborder_id ORDER BY Job Opening ASC LIMIT 0, 50 ) ) [2] => Array ( [file] => /var/www/html/cats/lib/DatabaseConnectionPDO.php [line] => 149 [function] => setQuery [class] => ClsNaanalPDO [type] => -> [args] => Array ( [0] => SELECT SQL_CALC_FOUND_ROWS joborder.joborder_id AS jobOrderID, joborder.joborder_id AS exportID, joborder.date_modified AS dateModifiedSort, joborder.date_created AS dateCreatedSort, joborder.is_hot AS isHot, IF(attachment_id, 1, 0) AS attachmentPresent, joborder.title AS title, company.name AS companyName, company.company_id AS companyID, joborder.status AS status, DATE_FORMAT(joborder.date_created, ‘%d-%m-%y’) AS dateCreated, ( SELECT COUNT(*) FROM candidate_joborder_status_history WHERE joborder_id = joborder.joborder_id AND status_to = 400 AND site_id = 1 ) AS submitted, ( SELECT COUNT(*) FROM candidate_joborder WHERE joborder_id = joborder.joborder_id AND site_id = 1 ) AS pipeline, recruiter_user.first_name AS recruiterFirstName,recruiter_user.last_name AS recruiterLastName,CONCAT(recruiter_user.last_name, recruiter_user.first_name) AS recruiterSort, Gender AS Gender, Job Opening AS Job Opening FROM joborder LEFT JOIN company ON joborder.company_id = company.company_id LEFT JOIN contact ON joborder.contact_id = contact.contact_id LEFT JOIN attachment ON joborder.joborder_id = attachment.data_item_id AND attachment.data_item_type = 400 LEFT JOIN user AS recruiter_user ON joborder.recruiter = recruiter_user.user_id LEFT JOIN saved_list_entry ON saved_list_entry.data_item_type = 400 AND saved_list_entry.data_item_id = joborder.joborder_id AND saved_list_entry.site_id = 1 WHERE joborder.site_id = 1 AND joborder.is_admin_hidden = 0 AND (joborder.status = ‘Active’ OR joborder.status = ‘OnHold’ OR joborder.status = ‘Full’ ) AND ((joborder.owner = 1255 and joborder.ownertype=0) OR (joborder.owner = 1 and joborder.ownertype=1) OR (joborder.owner = 1 and joborder.ownertype=1)) GROUP BY joborder.joborder_id ORDER BY Job Opening ASC LIMIT 0, 50 ) ) [3] => Array ( [file] => /var/www/html/cats/lib/DatabaseConnectionPDO.php [line] => 327 [function] => query [class] => DatabaseConnection [type] => -> [args] => Array ( [0] => SELECT SQL_CALC_FOUND_ROWS joborder.joborder_id AS jobOrderID, joborder.joborder_id AS exportID, joborder.date_modified AS dateModifiedSort, joborder.date_created AS dateCreatedSort, joborder.is_hot AS isHot, IF(attachment_id, 1, 0) AS attachmentPresent, joborder.title AS title, company.name AS companyName, company.company_id AS companyID, joborder.status AS status, DATE_FORMAT(joborder.date_created, ‘%m-%d-%y’) AS dateCreated, ( SELECT COUNT(*) FROM candidate_joborder_status_history WHERE joborder_id = joborder.joborder_id AND status_to = 400 AND site_id = 1 ) AS submitted, ( SELECT COUNT(*) FROM candidate_joborder WHERE joborder_id = joborder.joborder_id AND site_id = 1 ) AS pipeline, recruiter_user.first_name AS recruiterFirstName,recruiter_user.last_name AS recruiterLastName,CONCAT(recruiter_user.last_name, recruiter_user.first_name) AS recruiterSort, Gender AS Gender, Job Opening AS Job Opening FROM joborder LEFT JOIN company ON joborder.company_id = company.company_id LEFT JOIN contact ON joborder.contact_id = contact.contact_id LEFT JOIN attachment ON joborder.joborder_id = attachment.data_item_id AND attachment.data_item_type = 400 LEFT JOIN user AS recruiter_user ON joborder.recruiter = recruiter_user.user_id LEFT JOIN saved_list_entry ON saved_list_entry.data_item_type = 400 AND saved_list_entry.data_item_id = joborder.joborder_id AND saved_list_entry.site_id = 1 WHERE joborder.site_id = 1 AND joborder.is_admin_hidden = 0 AND (joborder.status = ‘Active’ OR joborder.status = ‘OnHold’ OR joborder.status = ‘Full’ ) AND ((joborder.owner = 1255 and joborder.ownertype=0) OR (joborder.owner = 1 and joborder.ownertype=1) OR (joborder.owner = 1 and joborder.ownertype=1)) GROUP BY joborder.joborder_id ORDER BY Job Opening ASC LIMIT 0, 50 ) ) [4] => Array ( [file] => /var/www/html/cats/lib/DataGrid.php [line] => 1381 [function] => getAllAssoc [class] => DatabaseConnection [type] => -> [args] => Array ( [0] => SELECT SQL_CALC_FOUND_ROWS joborder.joborder_id AS jobOrderID, joborder.joborder_id AS exportID, joborder.date_modified AS dateModifiedSort, joborder.date_created AS dateCreatedSort, joborder.is_hot AS isHot, IF(attachment_id, 1, 0) AS attachmentPresent, joborder.title AS title, company.name AS companyName, company.company_id AS companyID, joborder.status AS status, DATE_FORMAT(joborder.date_created, ‘%m-%d-%y’) AS dateCreated, ( SELECT COUNT(*) FROM candidate_joborder_status_history WHERE joborder_id = joborder.joborder_id AND status_to = 400 AND site_id = 1 ) AS submitted, ( SELECT COUNT(*) FROM candidate_joborder WHERE joborder_id = joborder.joborder_id AND site_id = 1 ) AS pipeline, recruiter_user.first_name AS recruiterFirstName,recruiter_user.last_name AS recruiterLastName,CONCAT(recruiter_user.last_name, recruiter_user.first_name) AS recruiterSort, Gender AS Gender, Job Opening AS Job Opening FROM joborder LEFT JOIN company ON joborder.company_id = company.company_id LEFT JOIN contact ON joborder.contact_id = contact.contact_id LEFT JOIN attachment ON joborder.joborder_id = attachment.data_item_id AND attachment.data_item_type = 400 LEFT JOIN user AS recruiter_user ON joborder.recruiter = recruiter_user.user_id LEFT JOIN saved_list_entry ON saved_list_entry.data_item_type = 400 AND saved_list_entry.data_item_id = joborder.joborder_id AND saved_list_entry.site_id = 1 WHERE joborder.site_id = 1 AND joborder.is_admin_hidden = 0 AND (joborder.status = ‘Active’ OR joborder.status = ‘OnHold’ OR joborder.status = ‘Full’ ) AND ((joborder.owner = 1255 and joborder.ownertype=0) OR (joborder.owner = 1 and joborder.ownertype=1) OR (joborder.owner = 1 and joborder.ownertype=1)) GROUP BY joborder.joborder_id ORDER BY Job Opening ASC LIMIT 0, 50 ) ) [5] => Array ( [file] => /var/www/html/cats/lib/DataGrid.php [line] => 523 [function] => _getData [class] => DataGrid [type] => -> [args] => Array ( ) ) [6] => Array ( [file] => /var/www/html/cats/lib/JobOrders.php [line] => 1573 [function] => __construct [class] => DataGrid [type] => -> [args] => Array ( [0] => joborders:JobOrdersListByViewDataGrid [1] => Array ( [rangeStart] => 0 [maxResults] => 50 [filter] => Status==Active / OnHold / Full [filterVisible] => [sortBy] => dateCreatedSort [sortDirection] => DESC ) [2] => 0 ) ) [7] => Array ( [file] => /var/www/html/cats/modules/joborders/dataGrids.php [line] => 72 [function] => __construct [class] => JobOrdersDataGrid [type] => -> [args] => Array ( [0] => joborders:JobOrdersListByViewDataGrid [1] => 1 [2] => Array ( [rangeStart] => 0 [maxResults] => 50 [filter] => Status==Active / OnHold / Full [filterVisible] => [sortBy] => dateCreatedSort [sortDirection] => DESC ) [3] => 0 ) ) [8] => Array ( [file] => /var/www/html/cats/lib/DataGrid.php [line] => 280 [function] => __construct [class] => JobOrdersListByViewDataGrid [type] => -> [args] => Array ( [0] => 1 [1] => Array ( [rangeStart] => 0 [maxResults] => 50 [filter] => Status==Active / OnHold / Full [filterVisible] => [sortBy] => dateCreatedSort [sortDirection] => DESC ) [2] => 0 ) ) [9] => Array ( [file] => /var/www/html/cats/modules/joborders/JobOrdersUI.php [line] => 270 [function] => get [class] => DataGrid [type] => :: [args] => Array ( [0] => joborders:JobOrdersListByViewDataGrid [1] => Array ( [rangeStart] => 0 [maxResults] => 50 [filter] => Status==Active / OnHold / Full [filterVisible] => [sortBy] => dateCreatedSort [sortDirection] => DESC ) ) ) [10] => Array ( [file] => /var/www/html/cats/modules/joborders/JobOrdersUI.php [line] => 247 [function] => listByView [class] => JobOrdersUI [type] => -> [args] => Array ( ) ) [11] => Array ( [file] => /var/www/html/cats/lib/ModuleUtility.php [line] => 209 [function] => render [class] => JobOrdersUI [type] => -> [args] => Array ( ) ) [12] => Array ( [file] => /var/www/html/cats/index.php [line] => 350 [function] => loadModule [class] => ModuleUtility [type] => :: [args] => Array ( [0] => joborders ) ) ) [previous:Exception:private] => [errorInfo] => Array ( [0] => 42000 [1] => 1064 [2] => 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 ‘Opening ASC LIMIT 0, 50’ at line 59 ) ) ;

    • This topic was modified 1 year, 8 months ago by  mpky.
  • #2229

    tamilamuthan15
    Keymaster

    mpky,

    can you explain the steps to reproduce? All the steps from your previous version to current version. And including your extra field detail.

    with regards,
    R. Tamil Amuthan
    Software Consultant
    Auieo Software Pvt. Ltd.

  • #2230

    mpky
    Participant

    Hi Tamil,
    Thanks for your reply.
    Previously we encountered the 4 tabs missing after migration from 1.3.2 to Ver. 2.0. We have followed your advice to uninstall CandidATS in new VPS and remove auieoLog.log if exist and migrate again. This procedure has overcome the tabs issue.

    After that, we tried add on customize extra field – Field name: Job opening, Field Type: Date at job orders. Error No. 1 (the detail in previous post) shown. But somehow the extra field created despite the error no. 1 when we checked the job orders. When sorting (ascending/descending) the customize extra field – Date opening, the Error No. 2 (the detail in previous post) shown.

    what possibilities that trigger this, please enlighten.

    Many thanks.

    p/s: in old version, the extra customize field -date opening was there, but somehow we had accidentally deleted and problem faced when recreated the field

You must be logged in to reply to this topic.