Vote #76234
完了Column 'address' in where clause may be ambiguous
0%
説明
Hello.
i`ve got the following error after upgrading from 2.6.1 to 3.0.1
Mysql2::Error: Column 'address' in where clause is ambiguous:
SELECT DISTINCT `users`.* FROM `users` INNER JOIN `email_addresses` ON `email_addresses`.`user_id` = `users`.`id` WHERE `users`.`type`
IN ('User', 'AnonymousUser') AND (LOWER(address) IN ('myemail@mail.com')) ORDER BY `users`.`id` ASC LIMIT 1
emails received by cronjob every minute.
rake redmine:email:receive_imap RAILS_ENV="production" host=imap.gmail.com port=993 username=email@email password=password ssl=true move_on_success=read project=public-project247 category=HelpDesk unknown_user=create no_permission_check=1 allow_override=tracker,priority,project,status,category,assigned_to
Please give advise for solving this issue
Environment:
Redmine version 3.0.1.stable
Ruby version 1.9.3-p194 (2012-04-20) [x86_64-linux]
Rails version 4.2.0
Environment production
Database adapter Mysql2
SCM:
Git 1.7.10.4
Filesystem
Redmine plugins:
redmine_agile 1.3.8
redmine_email_fetcher 0.3.0
redmine_issue_history 1.0.0
redmine_wysiwyg 1.0.0
plugins
Redmine Agile plugin (Light version)
redmine_issue_history
redmine_wysiwyg
journals
--------------------------------------------------------------------------------
are you sure that you don't have any other plugins?
In my logs sql look the same and is working:
<pre>
SELECT DISTINCT `users`.* FROM `users`
INNER JOIN `email_addresses` ON `email_addresses`.`user_id` = `users`.`id`
WHERE `users`.`type` IN ('User', 'AnonymousUser') AND (LOWER(address) IN ('l.ka@company.com'))
ORDER BY `users`.`id` ASC LIMIT 1
</pre>
--------------------------------------------------------------------------------
I think your "users" table has "address" column created by plugin which you use or used.
--------------------------------------------------------------------------------
The same error is reported in #19595 and that issue has a workaround.
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
A workaround by Andrey Sennikov (#19595).
<pre><code class="diff">
Index: app/models/user.rb
===================================================================
--- app/models/user.rb (revision 14160)
+++ app/models/user.rb (working copy)
@@ -130,7 +130,7 @@
scope :having_mail, lambda {|arg|
addresses = Array.wrap(arg).map {|a| a.to_s.downcase}
if addresses.any?
- joins(:email_addresses).where("LOWER(address) IN (?)", addresses).uniq
+ joins(:email_addresses).where("LOWER(email_addresses.address) IN (?)", addresses).uniq
else
none
end
</code></pre>
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
For us, it was the "People plugin":http://www.redminecrm.com/projects/people/pages/1 which caused this problem. In any case, less ambiguity in the @SELECT@ statement wouldn't be a bad thing. Can this patch be integrated? It seems we fixed it in the same way.
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
Fixed in r14284.
--------------------------------------------------------------------------------
related_issues
duplicates,Closed,19595,Broken "receiving emails"
duplicates,Closed,19884,address column in WHERE clause is ambiguous