Failing sql import getting1064 error mysql 8

Question

CREATE  PROCEDURE `getTestById`(IN `studID` INT,IN `tID` INT)
BEGIN      
     SELECT t.id,t.name,c.name category,i.name instructor,getQuestionsInTest(t.id) questions,ts.startTime,ts.duration,ts.passPercent,ts.endTime,ts.id settingID,ts.random from groups g      
     inner join groups_has_students gs      on gs.studentID = studID and g.id = gs.groupID      
     inner join test t      on t.id = g.assignedTest      
     inner join test_settings ts      on ts.id = g.settingID      
     inner join category c      on c.id = t.categoryID      
     inner join instructor i      on i.id = t.instructorID      
     where (convert_tz(now(),@@session.time_zone,'+02:00') BETWEEN ts.startTime AND ts.endTime)      AND t.id NOT IN (SELECT testID from result where studentID = gs.studentID) AND t.id = tID;
END;

Gives following error

MySQL said: Documentation

#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 'groups g inner join groups_has_students gs on gs.studentID = studID ' at line 3

I tried enclosing groups in back quotes but didnt work

Any solution what's wrong?

`groups` is a reserved keyword since mysql 8.0.2. https://dev.mysql.com/doc/refman/8.0/en/keywords.html#keywords-8-0-detailed-G You need to escape it with backticks — Tushar, Apr 23 '23 at 15:03
@Tushar i am so sorry you were right. I did it in one place and forgot about second. — Mohit Valecha, Apr 23 '23 at 17:54

score 0 · Accepted Answer · answered Apr 23 '23 at 15:05

Since MySQL 8.0.2 groups is a reserved keyword.

To resolve the issue, you need to enclose table_name groups in backticks (`) to escape it.

CREATE PROCEDURE `getTestById`(IN `studID` INT, IN `tID` INT)
BEGIN
    SELECT t.id, t.name, c.name category, i.name instructor, getQuestionsInTest(t.id) questions, ts.startTime, ts.duration, ts.passPercent, ts.endTime, ts.id settingID, ts.random 
    FROM `groups` g 
    INNER JOIN groups_has_students gs ON gs.studentID = studID AND g.id = gs.groupID 
    INNER JOIN test t ON t.id = g.assignedTest 
    INNER JOIN test_settings ts ON ts.id = g.settingID 
    INNER JOIN category c ON c.id = t.categoryID 
    INNER JOIN instructor i ON i.id = t.instructorID 
    WHERE (convert_tz(now(), @@session.time_zone, '+02:00') BETWEEN ts.startTime AND ts.endTime) AND t.id NOT IN (SELECT testID FROM result WHERE studentID = gs.studentID) AND t.id = tID;
END;

Failing sql import getting1064 error mysql 8

1 Answers1