Hi- We recently upgraded our servers to mySQL 8.0 and started getting an error with a query that had previously been working. This is the php code causing the error:
PHP Code:
$this->db->select('tblIndicators.*,GROUP_CONCAT(tblVersionInfo.fkDatasetID) as fldDatasetIDs');
$this->db->join('tblIndicatorsDataset', 'tblIndicatorsDataset.fkIndicatorID = tblIndicators.pkIndicatorID');
$this->db->join('tblIndicatorsDatasetVersion', 'tblIndicatorsDatasetVersion.pkIndicatorsDatasetVersionID = tblIndicatorsDataset.fkIndicatorsDatasetVersionID', 'left');
$this->db->join('tblVersionInfo', 'tblIndicatorsDatasetVersion.fkDatasetVersionID = tblVersionInfo.pkVersionID', 'left');
$this->db->order_by('fldCategoryCode');
$this->db->order_by('fldOrder');
$this->db->where(array('fkIndicatorsVersionID'=>$indicatorsVersionID, 'fldDisabled'=>0));
$this->db->group_by('tblIndicators.fldIndicatorCode');
return $this->db->get($tblIndicators)->result();
The error returned is:
Code:
Query error: 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 ')
JOIN `tblIndicatorsDataset` ON `tblIndicatorsDataset`.`fkIndicatorID` = `tblIn' at line 2 - Invalid query: SELECT `tblIndicators`.*, GROUP_CONCAT(tblVersionInfo.fkDatasetID) as fldDatasetIDs
FROM ((SELECT DISTINCT(tblIndicators.pkIndicatorID) as id, tblIndicators.* FROM tblIndicatorsDataset JOIN tblIndicators ON tblIndicatorsDataset.fkIndicatorID = tblIndicators.pkIndicatorID WHERE fkIndicatorsVersionID = 32) as tblIndicators)
JOIN `tblIndicatorsDataset` ON `tblIndicatorsDataset`.`fkIndicatorID` = `tblIndicators`.`pkIndicatorID`
LEFT JOIN `tblIndicatorsDatasetVersion` ON `tblIndicatorsDatasetVersion`.`pkIndicatorsDatasetVersionID` = `tblIndicatorsDataset`.`fkIndicatorsDatasetVersionID`
LEFT JOIN `tblVersionInfo` ON `tblIndicatorsDatasetVersion`.`fkDatasetVersionID` = `tblVersionInfo`.`pkVersionID`
WHERE `fkIndicatorsVersionID` = '32'
AND `fldDisabled` = 0
GROUP BY `tblIndicators`.`fldIndicatorCode`
ORDER BY `fldCategoryCode`, `fldOrder`
If I update the query to remove the paretheses around the from clause, it runs (I also disable ONLY_FULL_GROUP_BY). So the query looks like:
Code:
SET sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));
SELECT `tblIndicators`.*, GROUP_CONCAT(tblVersionInfo.fkDatasetID) as fldDatasetIDs
FROM (SELECT DISTINCT(tblIndicators.pkIndicatorID) as id, tblIndicators.* FROM tblIndicatorsDataset JOIN tblIndicators ON tblIndicatorsDataset.fkIndicatorID = tblIndicators.pkIndicatorID WHERE fkIndicatorsVersionID = 32) as tblIndicators
JOIN `tblIndicatorsDataset` ON `tblIndicatorsDataset`.`fkIndicatorID` = `tblIndicators`.`pkIndicatorID`
LEFT JOIN `tblIndicatorsDatasetVersion` ON `tblIndicatorsDatasetVersion`.`pkIndicatorsDatasetVersionID` = `tblIndicatorsDataset`.`fkIndicatorsDatasetVersionID`
LEFT JOIN `tblVersionInfo` ON `tblIndicatorsDatasetVersion`.`fkDatasetVersionID` = `tblVersionInfo`.`pkVersionID`
WHERE `fkIndicatorsVersionID` = '32'
AND `fldDisabled` = 0
GROUP BY `tblIndicators`.`fldIndicatorCode`
ORDER BY `fldCategoryCode`, `fldOrder`
But basically, I need to figure out how to stop CI from adding a parenthesis around the FROM clause to get it to run. The only thing I could figure out was maybe getting the compiled select statement and just removing them from the string, and then executing the string, but that seems like a pretty terrible solution. This is happening on a bunch of queries, and they are from code previously written by a coworker no longer working with us, so any help on getting this fixed without having to rewrite a whole bunch of complex queries is appreciated!
