Mysql 8 - query fails on user profile

[jereze]

Hello,

We upgraded our mysql to version 8.0.

When opening any user profile (https://answers.dataiku.com/user/any_user), we get an error: A Question2Answer database query failed when generating this page. A full description of the failure is available in the web server's error log file.

In the logs, we have the following error:

[09-Jan-2019 16:52:29 UTC] PHP Question2Answer MySQL query error 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 'rank FROM qa_userpoints WHERE points>COALESCE((SELECT points FROM qa_userpoints ' at line 1 - Query: SELECT 1+COUNT(*) AS rank FROM qa_userpoints WHERE points>COALESCE((SELECT points FROM qa_userpoints WHERE userid=(SELECT userid FROM qa_users WHERE handle='UserName' LIMIT 1)), 0)

The SQL query is invalid and it seems that the problem is with rank, a new reserved keyword. Quoting the column name solves the issue.

I tried a quick fix, replacing line 552 of qa-db.php with the following:

        $query .= $columnfrom . (is_int($columnas) ? '' : (' AS "' . $columnas . '"')) . ', ';

It seems that it works.

[pupi1985]

For your solution to work you need this setting: https://dev.mysql.com/doc/refman/8.0/en/sql-mode.html#sqlmode_ansi_quotes The appropriate way to quote identifiers in MySQL is using backticks: `

[jereze]

Good catch. Thanks.

[svivian]

So does it work when you change to use backticks? e.g. changing

(' AS ' . $columnas)

to

(" AS `$columnas`")

[svivian]

I've pushed a fix for this. Thanks for reporting, @jereze!