View Issue Details

Related ChangesetsJump to NotesJump to History
This bug affects 1 person(s).
 6
IDProjectCategoryView StatusDate SubmittedLast Update
13010Bug reports_ Unknownpublic2017-12-14 23:132018-01-30 14:29
Reporterjakebathman Assigned Toollehar  
PrioritynoneSeverityblock 
Status closedResolutionfixed 
Product Version2.7x.x 
Fixed in Version3.1.x 
Summary13010: MySQL 8 reserved word used as table alias in query
Description

When attempting to view a survey, using MySQL v8, the page fails to load with the error below show to the user:

"CDbCommand failed to execute the SQL statement: 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 'groups WHERE questions.gid=groups.gid AND questions.sid=465429 AND questions.l' at line 1."

Steps To Reproduce
  1. Install LimeSurvey using MySQL 5.7 (or another version before 8)
  2. Update MySQL to v8 (in this case, I'm on 8.0.3-rc-log)
  3. Attempt to view a survey in the admin panel (e.g. https://websurvey.local/index.php?r=admin/survey/sa/view/surveyid/465429)
  4. The page fails to load with the above warning
Additional Information

The full query being executed is:

SELECT * FROM lime_questions as questions, lime_groups as groups WHERE questions.gid=groups.gid AND questions.sid=465429 AND questions.language='en' AND questions.parent_qid=0 AND groups.language='en' ORDER BY group_order, question_order

This fails because 'groups' is now reserved starting in MySQL v8.0.2 (https://dev.mysql.com/doc/refman/8.0/en/keywords.html)

TagsNo tags attached.
Bug heat6
Complete LimeSurvey version number (& build)2.71.1+170927
I will donate to the project if issue is resolvedYes
BrowserChrome 63.0.3239.90
Database type & versionMySQL v8.0.3-rc-log
Server OS (if known)Ubuntu 16.04.2
Webserver software & version (if known)nginx 1.13.6
PHP Version7.1.4

Users monitoring this issue

jakebathman

Activities

c_schmitz

c_schmitz

2018-01-24 16:47

administrator   ~46033

@jakebathman: Can you activate debug mode in /application/config/config.php and attach the full error message here?
@olle: Can you add Travis CI testing for MySQL 8 ?
ollehar

ollehar

2018-01-29 13:19

administrator   ~46128

Fix committed to master branch: http://bugs.limesurvey.org/plugin.php?page=Source/view&id=26252
ollehar

ollehar

2018-01-29 14:24

administrator   ~46129

Isn't the latest version of MySQL 5.7?
Travis uses 5.6.

https://dev.mysql.com/downloads/mysql/
ollehar

ollehar

2018-01-29 14:27

administrator   ~46130

"MySQL 8.0 is a development series not recommended for production use. "

https://dev.mysql.com/doc/refman/8.0/en/

Related Changesets

LimeSurvey: master 8f84b486

2018-01-29 13:18:49

ollehar

Details Diff 		Fixed issue 13010: MySQL 8 reserved word used as table alias in query Affected Issues
13010
mod - application/helpers/common_helper.php Diff File
mod - application/models/Question.php Diff File

Issue History

Date Modified Username Field Change
2017-12-14 23:13 jakebathman New Issue
2017-12-14 23:13 jakebathman Issue Monitored: jakebathman
2018-01-24 16:46 c_schmitz Assigned To => ollehar
2018-01-24 16:46 c_schmitz Status new => assigned
2018-01-24 16:47 c_schmitz Note Added: 46033
2018-01-29 13:19 ollehar Changeset attached => LimeSurvey master 8f84b486
2018-01-29 13:19 ollehar Note Added: 46128
2018-01-29 13:19 ollehar Resolution open => fixed
2018-01-29 13:23 ollehar Status assigned => resolved
2018-01-29 13:23 ollehar Fixed in Version => 3.1.x
2018-01-29 14:24 ollehar Note Added: 46129
2018-01-29 14:27 ollehar Note Added: 46130
2018-01-30 14:29 ollehar Status resolved => closed
2021-08-02 17:55 guest Bug heat 4 => 6