Vote #78338
完了"My account" broken with MySQL 8.0 (keyword admin should be escaped)
0%
説明
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: <%=l(:field_login)%>: <%= link_to_user(@user, :format => :username) %>
4: <%=l(:field_created_on)%>: <%= format_time(@user.created_on) %>
5:
6: <% if @user.own_account_deletable? %>
7: <%= link_to(l(:button_delete_my_account), {:action => 'destroy'}, :class => 'icon icon-del') %>
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?)
journals
What MySQL version do you use?
--------------------------------------------------------------------------------
Version 8...
<pre>
sudo docker run --detach \
--name redmine-mysql \
--env MYSQL_ROOT_PASSWORD=REPLACE_WITH_ROOT_PASSWORD \
--env MYSQL_DATABASE=redmine \
mysql:8
</pre>
<pre>
sudo docker run --detach \
--name redmine \
--publish 80:3000 \
--link redmine-mysql:mysql \
redmine
</pre>
--------------------------------------------------------------------------------
Please contact docker provider.
--------------------------------------------------------------------------------
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
--------------------------------------------------------------------------------
reopening as this is still a bug in redmine as far as I am concerned...
--------------------------------------------------------------------------------
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
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
"admin" is reserved word in SQL 99 and MySQL.
Should we change column name? (e.g. "admin" to "is_admin")
--------------------------------------------------------------------------------
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.
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
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.
--------------------------------------------------------------------------------
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.
--------------------------------------------------------------------------------
Jean-Phillipe, thanks for much smarter solution!
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------