Defect #6218

Search feature is broken

Added by Sahand S about 7 years ago. Updated over 6 years ago.

Status:ClosedStart date:2010-08-25
Priority:NormalDue date:
Assignee:-% Done:

0%

Category:Search engine
Target version:-
Resolution: Affected version:1.0.1

Description

Version: Redmine 1.0.1.stable.4040 (MySQL)

How to reproduce: Searching for anything.

Logs:

Processing SearchController#index (for ********* at 2010-08-25 19:24:06) [GET]
  Parameters: {"action"=>"index", "q"=>"test", "controller"=>"search"}

ActiveRecord::StatementInvalid (Mysql::Error: Unknown column 'text' in 'where clause': SELECT DISTINCT `wiki_pages`.id FROM `wiki_pages`  LEFT OUTER JOIN `wikis` ON `wikis`.id = `wiki_pages`.wiki_id  LEFT OUTER JOIN `projects` ON `projects`.id = `wikis`.project_id WHERE ((projects.status=1 AND projects.id IN (SELECT em.project_id FROM enabled_modules em WHERE em.name='wiki')) AND (((LOWER(title) LIKE '%test%') OR (LOWER(text) LIKE '%test%'))))  ORDER BY wiki_pages.created_on DESC LIMIT 11):
  app/controllers/search_controller.rb:77:in `index'
  app/controllers/search_controller.rb:76:in `each'
  app/controllers/search_controller.rb:76:in `index'
  /usr/lib64/ruby/1.8/webrick/httpserver.rb:104:in `service'
  /usr/lib64/ruby/1.8/webrick/httpserver.rb:65:in `run'
  /usr/lib64/ruby/1.8/webrick/server.rb:173:in `start_thread'
  /usr/lib64/ruby/1.8/webrick/server.rb:162:in `start'
  /usr/lib64/ruby/1.8/webrick/server.rb:162:in `start_thread'
  /usr/lib64/ruby/1.8/webrick/server.rb:95:in `start'
  /usr/lib64/ruby/1.8/webrick/server.rb:92:in `each'
  /usr/lib64/ruby/1.8/webrick/server.rb:92:in `start'
  /usr/lib64/ruby/1.8/webrick/server.rb:23:in `start'
  /usr/lib64/ruby/1.8/webrick/server.rb:82:in `start'

Rendering /home/redmine/redmine-1.0/public/500.html (500 Internal Server Error)

Related issues

Related to Redmine - Feature #6887: Upgrade to Rails 2.3.11 Closed 2010-11-13

History

#1 Updated by Jean-Baptiste Barth about 7 years ago

  • Category set to Search engine
  • Priority changed from Urgent to Normal

Can you read SubmittingBugs and provide at least a ruby script/about output, if not all the requested informations ? And give us your exact version of Mysql, the OS behind and the way you installed it ?

I change the priority back to normal as I wasn't able to reproduce at first sight.

#2 Updated by Sahand S about 7 years ago

Thank you for the response.

Mysql Version: mysql Ver 14.14 Distrib 5.1.45, for pc-linux-gnu (x86_64) using readline 5.1
Rake version: rake, version 0.8.7

Output from script/about:

About your application's environment
Ruby version              1.8.7 (x86_64-linux)
RubyGems version          1.3.7
Rack version              1.1
Rails version             2.3.8
Active Record version     2.3.8
Active Resource version   2.3.8
Action Mailer version     2.3.8
Active Support version    2.3.8
Application root          /home/redmine/redmine-1.0
Environment               production
Database adapter          mysql
Database schema version   20100819172912

#3 Updated by Sahand S about 7 years ago

Forgot to mention the install: this was an upgrade from version 0.9.3. Search was working fine before the upgrade.

The database clearly does not have a column text in the wiki_pages table for me. Instead, the wiki_contents table has the column. Perhaps the database upgrade script is not fully working?

#4 Updated by Jean-Baptiste Barth about 7 years ago

Rails 2.3.8 is not supported yet. Can you please try with Rails 2.3.5 and tell us if it works better ? I don't have the same exact SQL query generated on my test environment with sqlite, and don't have currently access to the logs on my redmine/mysql server..

#5 Updated by Sahand S about 7 years ago

Thank you Jean-Baptiste for the responses.

I could try to use Rails 2.3.5 but I highly doubt that that is what's causing the issue. As I mentioned, in my database, the wiki_pages table does not have a column named text which is the cause of this problem. Please let me know if you still believe downgrading to Rails 2.3.5 might be a possible fix.

I believe this is a database migration issue. However, the wiki pages themselves work just fine.

#6 Updated by Sahand S about 7 years ago

Anything on this? Any help would be much appreciated.

#7 Updated by Jaap Prickartz about 7 years ago

i can confirm this issue (Redmine 1.0.1.devel.4087 (MySQL))

it looks like you are searching through the text field in wiki_contents but this table is not joined; this query (as taken from the logfile output) returns the same error as above:

 SELECT DISTINCT `wiki_pages`.id
FROM `wiki_pages`
LEFT OUTER JOIN `wikis` ON `wikis`.id = `wiki_pages`.wiki_id
LEFT OUTER JOIN `projects` ON `projects`.id = `wikis`.project_id
WHERE (
(
projects.status =1
AND projects.id
IN (

SELECT em.project_id
FROM enabled_modules em
WHERE em.name = 'wiki'
)
AND wikis.project_id
IN ( 9 )
)
AND (
(
(
LOWER( title ) LIKE '%serverkast%'
)
OR (
LOWER( text ) LIKE '%serverkast%'
)
)
)
)
ORDER BY wiki_pages.created_on DESC
LIMIT 11 

when i add the wiki_contents table manually it doesn't give an error anymore:

SELECT DISTINCT `wiki_pages`.id
FROM `wiki_pages`
LEFT OUTER JOIN `wikis` ON `wikis`.id = `wiki_pages`.wiki_id
LEFT OUTER JOIN `projects` ON `projects`.id = `wikis`.project_id
LEFT JOIN `wiki_contents` ON `wiki_contents`.page_id
WHERE (
(
projects.status =1
AND projects.id
IN (

SELECT em.project_id
FROM enabled_modules em
WHERE em.name = 'wiki'
)
AND wikis.project_id
IN ( 9 )
)
AND (
(
(
LOWER( title ) LIKE '%serverkast%'
)
OR (
LOWER( text ) LIKE '%serverkast%'
)
)
)
)
ORDER BY wiki_pages.created_on DESC
LIMIT 11

Hope this helps!

#8 Updated by Jaap Prickartz about 7 years ago

Downgrading Rails 2.3.8 to 2.3.5 solves this issue for me!
if it helps anybody: here you'll find how to downgrade.

best of luck!

Jaap

#9 Updated by Jaap Prickartz about 7 years ago

  • Status changed from New to Resolved

#10 Updated by Felix Schäfer about 7 years ago

  • Status changed from Resolved to Closed

Sahand S wrote:

I could try to use Rails 2.3.5 but I highly doubt that that is what's causing the issue. As I mentioned, in my database, the wiki_pages table does not have a column named text which is the cause of this problem. Please let me know if you still believe downgrading to Rails 2.3.5 might be a possible fix.

I believe this is a database migration issue. However, the wiki pages themselves work just fine.

2.3.8 is not supported, period. We won't support errors with it. Downgrading to 2.3.5 also seems to solve the problem for Jaap.

#11 Updated by Jean-Baptiste Barth about 7 years ago

Same as Felix.

Sahand S wrote:

I could try to use Rails 2.3.5 but I highly doubt that that is what's causing the issue. As I mentioned, in my database, the wiki_pages table does not have a column named text which is the cause of this problem. Please let me know if you still believe downgrading to Rails 2.3.5 might be a possible fix.

Just to explain you a bit more : the "text" column is not in the table you think, but in an other one that should be joined in the query. Mysql (or whatever your database engine is) understands that there's no column named "text" in the wiki_pages table and take the one in the joined table. It's standard SQL behavior as long as there's no ambiguous column name.

The request is probably not generated the same way between 2.3.5 sql adapter and 2.3.8 one. We cannot support 2.3.8 now, we already have far too many OS/ruby versions/database engines/locales supported.

Hope you'll understand.

#12 Updated by Sahand S about 7 years ago

Understood. I downgraded rails to 2.3.5 and that solved the problem. Thank you all for the support.

#13 Updated by Gordon Isnor over 6 years ago

I think I found a simple fix for this:

Class WikiPage => line 33:

acts_as_searchable :columns => ['title', 'wiki_contents.text'],

Where I just added the wiki_contents table indicator

#14 Updated by Etienne Massip over 6 years ago

Still needed with Rails 2.3.11 ?

Also available in: Atom PDF