Defect #25416
closed"My account" broken with MySQL 8.0 (keyword admin should be escaped)
0%
Description
Clicking "My account" when using a mysql database gives the folowing error:
Completed 500 Internal Server Error in 383ms (ActiveRecord: 12.9ms) ActionView::Template::Error (Mysql2::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 'admin = 1 AND id <> 4) LIMIT 1' at line 1: SELECT 1 AS one FROM `users` WHERE `users`.`type` IN ('User', 'AnonymousUser') AND `users`.`status` = 1 AND (admin = 1 AND id <> 4) LIMIT 1): 3: <p><%=l(:field_login)%>: <strong><%= link_to_user(@user, :format => :username) %></strong><br /> 4: <%=l(:field_created_on)%>: <%= format_time(@user.created_on) %></p> 5: 6: <% if @user.own_account_deletable? %> 7: <p><%= link_to(l(:button_delete_my_account), {:action => 'destroy'}, :class => 'icon icon-del') %></p> 8: <% end %> 9: app/models/user.rb:687:in `own_account_deletable?' app/views/my/_sidebar.html.erb:6:in `_app_views_my__sidebar_html_erb___2869233383891145181_70035919334040' app/views/my/account.html.erb:57:in `block in _app_views_my_account_html_erb___4301926150472292574_70036033743920' app/views/my/account.html.erb:56:in `_app_views_my_account_html_erb___4301926150472292574_70036033743920' lib/redmine/sudo_mode.rb:63:in `sudo_mode'
The fix is to add escapings `` for the admin keyword in the sql query like so:
[extmcm@ux310tas001 ~]$ diff user.rb user_fixed.rb 114d113 < validates_length_of :identity_url, maximum: 255 687c686 < (!admin? || User.active.where("admin = ? AND id <> ?", true, id).exists?) --- > (!admin? || User.active.where("`admin` = ? AND id <> ?", true, id).exists?)
Files
Updated by Toshi MARUYAMA over 7 years ago
- Status changed from New to Needs feedback
- Priority changed from Urgent to Normal
What MySQL version do you use?
Updated by Michael Medin over 7 years ago
Version 8...
sudo docker run --detach \ --name redmine-mysql \ --env MYSQL_ROOT_PASSWORD=REPLACE_WITH_ROOT_PASSWORD \ --env MYSQL_DATABASE=redmine \ mysql:8
sudo docker run --detach \ --name redmine \ --publish 80:3000 \ --link redmine-mysql:mysql \ redmine
Updated by Toshi MARUYAMA over 7 years ago
- Status changed from Needs feedback to Closed
- Resolution set to Invalid
Please contact docker provider.
Updated by Michael Medin over 7 years ago
Huh?
The error was from redmine. Dokerinfo was to help easily replicate the error...
Not sure why it would be related to docker?
Or is mysql 8 not supported?
ADMIN is a reserved keyword in MYSQL 8: https://dev.mysql.com/doc/refman/8.0/en/keywords.html
Updated by Michael Medin over 7 years ago
- Status changed from Closed to Reopened
reopening as this is still a bug in redmine as far as I am concerned...
Updated by Go MAEDA over 7 years ago
- Subject changed from "My account" broken with mysql (keyword admin should be escaped on mysql database) to "My account" broken with mysql (keyword admin should be escaped on MySQL 8.0)
- Category changed from Accounts / authentication to Database
- Resolution deleted (
Invalid)
Michael Medin wrote:
ADMIN is a reserved keyword in MYSQL 8: https://dev.mysql.com/doc/refman/8.0/en/keywords.html
Thank you for pointing it out.
Unfortunately current versions of Redmine don't support MySQL 8.0. Please use MySQL 5.0 - 5.5 for now.
http://www.redmine.org/projects/redmine/wiki/RedmineInstall#Supported-database-back-ends
Updated by Go MAEDA over 7 years ago
"admin" is reserved word in SQL 99 and MySQL.
Should we change column name? (e.g. "admin" to "is_admin")
Updated by Go MAEDA over 7 years ago
- File 0001-Fixed-SQL-99-reserved-word-admin-conflict.patch 0001-Fixed-SQL-99-reserved-word-admin-conflict.patch added
- Target version set to Candidate for next major release
This is a patch to fix a conflict with SQL-99 reserved word by renaming the column "admin" to "is_admin".
The reason for using alias_attribute
in the patch is to keep compatibility with existing plugins which call User#admin?
method.
Updated by Toshi MARUYAMA over 7 years ago
- Target version changed from Candidate for next major release to 4.1.0
Updated by Go MAEDA over 7 years ago
- Target version changed from 4.1.0 to 3.4.0
Since this issue is critical, I think it would be better to deliver this fix as soon as possible.
Setting target version to 3.4.0.
Updated by Jean-Philippe Lang over 7 years ago
- Status changed from Reopened to Closed
- Assignee set to Jean-Philippe Lang
- Resolution set to Fixed
I won't rename the column because `admin` is now a reserved word in mysql (BTW I don't see it in the list of SQL99 reserved words). r16449 fixes the error on my account for Mysql 8 by escaping the column name.
Updated by Go MAEDA over 7 years ago
Jean-Phillipe, thanks for much smarter solution!
Updated by Jean-Philippe Lang about 7 years ago
- Subject changed from "My account" broken with mysql (keyword admin should be escaped on MySQL 8.0) to "My account" broken with MySQL 8.0 (keyword admin should be escaped)