View Issue Details

IDProjectCategoryView StatusLast Update
13010Bug reports[All Projects] _ Unknownpublic2018-01-30 14:29
Reporterjakebathman Assigned Toollehar  
PrioritynoneSeveritycrash 
Status closedResolutionfixed 
Product Version2.7x.x 
Target VersionFixed 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.
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 & DB-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

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
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