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

Tagged: 

Viewing 0 reply threads
  • 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 ) ) ;

Viewing 0 reply threads
  • You must be logged in to reply to this topic.