Defect #6780
closed
PGError with queries containing something like "projects.is_public = 't'"
0%
Description
Just upgrade to Redmine 1.0.2 and migrated the database from MySQL to PostgreSQL. Now some SQL-Queries fail, so I couldn't access any page. Here is the information from the log file:
Processing WelcomeController#index (for X.X.X.X at 2010-10-31 17:14:07) [GET]
Parameters: {"action"=>"index", "controller"=>"welcome"}
ActiveRecord::StatementInvalid (PGError: FEHLER: ungültige Eingabesyntax für ganze Zahl: »t«
LINE 1: ...em.name='news')) AND (1=0 OR projects.is_public = 't'))) OR...
^
: SELECT "news"."id" AS t0_r0, "news"."project_id" AS t0_r1, "news"."title" AS t0_r2, "news"."summary" AS t0_r3,
"news"."description" AS t0_r4, "news"."author_id" AS t0_r5, "news"."created_on" AS t0_r6, "news"."comments_count"
AS t0_r7, "users"."id" AS t1_r0, "users"."login" AS t1_r1, "users"."hashed_password" AS t1_r2, "users"."firstname"
AS t1_r3, "users"."lastname" AS t1_r4, "users"."mail" AS t1_r5, "users"."mail_notification" AS t1_r6, "users"."admin"
AS t1_r7, "users"."status" AS t1_r8, "users"."last_login_on" AS t1_r9, "users"."language" AS t1_r10,
"users"."auth_source_id" AS t1_r11, "users"."created_on" AS t1_r12, "users"."updated_on" AS t1_r13, "users"."type"
AS t1_r14, "users"."identity_url" AS t1_r15, "projects"."id" AS t2_r0, "projects"."name" AS t2_r1,
"projects"."description" AS t2_r2, "projects"."homepage" AS t2_r3, "projects"."is_public" AS t2_r4, "projects"."parent_id"
AS t2_r5, "projects"."created_on" AS t2_r6, "projects"."updated_on" AS t2_r7, "projects"."identifier" AS t2_r8,
"projects"."status" AS t2_r9, "projects"."lft" AS t2_r10, "projects"."rgt" AS t2_r11 FROM "news" LEFT OUTER JOIN
"users" ON "users".id = "news".author_id AND ("users"."type" = 'User' OR "users"."type" = 'AnonymousUser' )
LEFT OUTER JOIN "projects" ON "projects".id = "news".project_id WHERE (((projects.status=1 AND projects.id
IN (SELECT em.project_id FROM enabled_modules em WHERE em.name='news')) AND
(1=0 OR projects.is_public = 't'))) ORDER BY news.created_on DESC LIMIT 5):
app/models/news.rb:38:in `latest'
app/controllers/welcome_controller.rb:22:in `index'
passenger (3.0.0) lib/phusion_passenger/rack/request_handler.rb:96:in `process_request'
passenger (3.0.0) lib/phusion_passenger/abstract_request_handler.rb:513:in `accept_and_process_next_request'
passenger (3.0.0) lib/phusion_passenger/abstract_request_handler.rb:274:in `main_loop'
passenger (3.0.0) lib/phusion_passenger/classic_rails/application_spawner.rb:321:in `start_request_handler'
passenger (3.0.0) lib/phusion_passenger/classic_rails/application_spawner.rb:275:in `send'
passenger (3.0.0) lib/phusion_passenger/classic_rails/application_spawner.rb:275:in `handle_spawn_application'
passenger (3.0.0) lib/phusion_passenger/utils.rb:479:in `safe_fork'
passenger (3.0.0) lib/phusion_passenger/classic_rails/application_spawner.rb:270:in `handle_spawn_application'
passenger (3.0.0) lib/phusion_passenger/abstract_server.rb:357:in `__send__'
passenger (3.0.0) lib/phusion_passenger/abstract_server.rb:357:in `server_main_loop'
passenger (3.0.0) lib/phusion_passenger/abstract_server.rb:206:in `start_synchronously'
passenger (3.0.0) lib/phusion_passenger/abstract_server.rb:180:in `start'
passenger (3.0.0) lib/phusion_passenger/classic_rails/application_spawner.rb:149:in `start'
passenger (3.0.0) lib/phusion_passenger/spawn_manager.rb:219:in `spawn_rails_application'
passenger (3.0.0) lib/phusion_passenger/abstract_server_collection.rb:132:in `lookup_or_add'
passenger (3.0.0) lib/phusion_passenger/spawn_manager.rb:214:in `spawn_rails_application'
passenger (3.0.0) lib/phusion_passenger/abstract_server_collection.rb:82:in `synchronize'
passenger (3.0.0) lib/phusion_passenger/abstract_server_collection.rb:79:in `synchronize'
passenger (3.0.0) lib/phusion_passenger/spawn_manager.rb:213:in `spawn_rails_application'
passenger (3.0.0) lib/phusion_passenger/spawn_manager.rb:132:in `spawn_application'
passenger (3.0.0) lib/phusion_passenger/spawn_manager.rb:275:in `handle_spawn_application'
passenger (3.0.0) lib/phusion_passenger/abstract_server.rb:357:in `__send__'
passenger (3.0.0) lib/phusion_passenger/abstract_server.rb:357:in `server_main_loop'
passenger (3.0.0) lib/phusion_passenger/abstract_server.rb:206:in `start_synchronously'
passenger (3.0.0) helper-scripts/passenger-spawn-server:99
Rendering /srv/redmine/application/redmine-1.0/public/500.html (500 Internal Server Error)
My environment is: Debian Lenny, ruby-enterprise-edition 1.8.7, rails 2.3.5 (for redmine), redmine in the 1.0.x subversion branch. Database is a postgresql 8.4.
Updated by Felix Schäfer over 14 years ago
- Status changed from New to Closed
- Resolution set to Invalid
How did you "[migrate] the database from MySQL to PostgreSQL"? Just dumping it to SQL and reimporting it often doesn't work, because the rails adapters for the different DBs adapt the schema they create to the current DB (IIRC for example an attribute defined as boolean in rails is mapped to a tinyint(1) in MySQL and to a boolean in PostgreSQL).
There are generic rails tools to migrate DBs from one type to another, but none is "tested to work with redmine", so we can't provide help with it. (IIRC one generic method would be dumping the DB through rails to yml and reimporting it to rails to the other DB).
Updated by Ingmar R. over 14 years ago
Felix Schäfer wrote:
How did you "[migrate] the database from MySQL to PostgreSQL"? Just dumping it to SQL and reimporting it often doesn't work, because the rails adapters for the different DBs adapt the schema they create to the current DB (IIRC for example an attribute defined as boolean in rails is mapped to a
tinyint(1)in MySQL and to abooleanin PostgreSQL).
Yes, I tried it with simply migrating the database content.
There are generic rails tools to migrate DBs from one type to another, but none is "tested to work with redmine", so we can't provide help with it. (IIRC one generic method would be dumping the DB through rails to yml and reimporting it to rails to the other DB).
I found some tools and finally got the database converted after trying some methods. So Problem wasn't Redmine.
Updated by Ingmar R. over 14 years ago
Sorry, some problems with the Syntax.
"Yes, I tried it with simply migrating the database content." Should be the answer for the first quote, "I found some tools and finally got the database converted after trying some methods. So Problem wasn't Redmine." the answer for the second quote.
Thanks for your help.