Docker boostrap fails due to single quotes escaping in SQL INSERT commands

willgarcia · 4월 25, 2015, 4:48오후

When bootstrapping a Discourse environment with Docker (samsaffron/discourse image, version 1.0.11), Rails db migration fails because of unescaped single quotes in translations’ files.

Steps to reproduce:

$ git clone https://github.com/discourse/discourse_docker.git /var/discourse

Set the default lang to French in /var/discourse/containers/app.yml
,

...
env:
  LANG: fr_FR.UTF-8
  DISCOURSE_DEFAULT_LOCALE: fr
...

DB migration error:

$ cd /var/discourse && ./launcher boostrap app
...
I, [2015-04-25T07:53:17.634912 #39]  INFO -- : > cd /var/www/discourse && su discourse -c 'bundle exec rake db:migrate'
2015-04-25 07:53:34 UTC [315-1] discourse@discourse ERROR:  syntax error at or near "équipe" at character 284
2015-04-25 07:53:34 UTC [315-2] discourse@discourse STATEMENT:  INSERT INTO categories
	                          (name, color, text_color, created_at, updated_at, user_id, slug, description, read_restricted, position)
	                   VALUES ('Staff', '283890', 'FFFFFF', now(), now(), -1, 'staff', 'Catégorie privée pour les discussions de l'équipe du forum. Les sujets ne sont visibles que pour les admins et les modérateurs.', true, 2)
	                   RETURNING id
rake aborted!
StandardError: An error has occurred, this and all later migrations canceled:

PG::SyntaxError: ERROR:  syntax error at or near "équipe"
LINE 3: ...f', 'Catégorie privée pour les discussions de l'équipe du fo...
                                                             ^
: INSERT INTO categories
                          (name, color, text_color, created_at, updated_at, user_id, slug, description, read_restricted, position)
                   VALUES ('Staff', '283890', 'FFFFFF', now(), now(), -1, 'staff', 'Catégorie privée pour les discussions de l'équipe du forum. Les sujets ne sont visibles que pour les admins et les modérateurs.', true, 2)
                   RETURNING id/var/www/discourse/vendor/bundle/ruby/2.0.0/gems/rack-mini-profiler-0.9.3/lib/patches/db/pg.rb:90:in `exec'
/var/www/discourse/vendor/bundle/ruby/2.0.0/gems/rack-mini-profiler-0.9.3/lib/patches/db/pg.rb:90:in `async_exec'
/var/www/discourse/vendor/bundle/ruby/2.0.0/gems/activerecord-4.1.9/lib/active_record/connection_adapters/postgresql/database_statements.rb:128:in `block in execute'
/var/www/discourse/vendor/bundle/ruby/2.0.0/gems/activerecord-4.1.9/lib/active_record/connection_adapters/abstract_adapter.rb:373:in `block in log'
/var/www/discourse/vendor/bundle/ruby/2.0.0/gems/activesupport-4.1.9/lib/active_support/notifications/instrumenter.rb:20:in `instrument'
/var/www/discourse/vendor/bundle/ruby/2.0.0/gems/activerecord-4.1.9/lib/active_record/connection_adapters/abstract_adapter.rb:367:in `log'
/var/www/discourse/vendor/bundle/ruby/2.0.0/gems/activerecord-4.1.9/lib/active_record/connection_adapters/postgresql/database_statements.rb:127:in `execute'
/var/www/discourse/vendor/bundle/ruby/2.0.0/gems/activerecord-4.1.9/lib/active_record/migration.rb:656:in `block in method_missing'
/var/www/discourse/vendor/bundle/ruby/2.0.0/gems/activerecord-4.1.9/lib/active_record/migration.rb:628:in `block in say_with_time'
/var/www/discourse/vendor/bundle/ruby/2.0.0/gems/activerecord-4.1.9/lib/active_record/migration.rb:628:in `say_with_time'
/var/www/discourse/vendor/bundle/ruby/2.0.0/gems/activerecord-4.1.9/lib/active_record/migration.rb:648:in `method_missing'
/var/www/discourse/db/migrate/20140227201005_add_staff_category.rb:9:in `up'
/var/www/discourse/vendor/bundle/ruby/2.0.0/gems/activerecord-4.1.9/lib/active_record/migration.rb:605:in `exec_migration'
/var/www/discourse/vendor/bundle/ruby/2.0.0/gems/activerecord-4.1.9/lib/active_record/migration.rb:586:in `block (2 levels) in migrate'
/var/www/discourse/vendor/bundle/ruby/2.0.0/gems/activerecord-4.1.9/lib/active_record/migration.rb:585:in `block in migrate'
/var/www/discourse/vendor/bundle/ruby/2.0.0/gems/activerecord-4.1.9/lib/active_record/connection_adapters/abstract/connection_pool.rb:294:in `with_connection'
/var/www/discourse/vendor/bundle/ruby/2.0.0/gems/activerecord-4.1.9/lib/active_record/migration.rb:584:in `migrate'
/var/www/discourse/vendor/bundle/ruby/2.0.0/gems/activerecord-4.1.9/lib/active_record/migration.rb:759:in `migrate'
/var/www/discourse/vendor/bundle/ruby/2.0.0/gems/activerecord-4.1.9/lib/active_record/migration.rb:998:in `block in execute_migration_in_transaction'
/var/www/discourse/vendor/bundle/ruby/2.0.0/gems/activerecord-4.1.9/lib/active_record/migration.rb:1044:in `block in ddl_transaction'
/var/www/discourse/vendor/bundle/ruby/2.0.0/gems/activerecord-4.1.9/lib/active_record/connection_adapters/abstract/database_statements.rb:201:in `block in transaction'
/var/www/discourse/vendor/bundle/ruby/2.0.0/gems/activerecord-4.1.9/lib/active_record/connection_adapters/abstract/database_statements.rb:209:in `within_new_transaction'
/var/www/discourse/vendor/bundle/ruby/2.0.0/gems/activerecord-4.1.9/lib/active_record/connection_adapters/abstract/database_statements.rb:201:in `transaction'
/var/www/discourse/vendor/bundle/ruby/2.0.0/gems/activerecord-4.1.9/lib/active_record/transactions.rb:208:in `transaction'
/var/www/discourse/vendor/bundle/ruby/2.0.0/gems/activerecord-4.1.9/lib/active_record/migration.rb:1044:in `ddl_transaction'
/var/www/discourse/vendor/bundle/ruby/2.0.0/gems/activerecord-4.1.9/lib/active_record/migration.rb:997:in `execute_migration_in_transaction'
/var/www/discourse/vendor/bundle/ruby/2.0.0/gems/activerecord-4.1.9/lib/active_record/migration.rb:959:in `block in migrate'
/var/www/discourse/vendor/bundle/ruby/2.0.0/gems/activerecord-4.1.9/lib/active_record/migration.rb:955:in `each'
/var/www/discourse/vendor/bundle/ruby/2.0.0/gems/activerecord-4.1.9/lib/active_record/migration.rb:955:in `migrate'
/var/www/discourse/vendor/bundle/ruby/2.0.0/gems/activerecord-4.1.9/lib/active_record/migration.rb:814:in `up'
/var/www/discourse/vendor/bundle/ruby/2.0.0/gems/activerecord-4.1.9/lib/active_record/migration.rb:792:in `migrate'
/var/www/discourse/vendor/bundle/ruby/2.0.0/gems/activerecord-4.1.9/lib/active_record/railties/databases.rake:34:in `block (2 levels) in <top (required)>'
Tasks: TOP => db:migrate
(See full trace by running task with --trace)
I, [2015-04-25T07:53:35.556523 #39]  INFO -- : == 20140227201005 AddStaffCategory: migrating =================================
-- execute("INSERT INTO categories\n                          (name, color, text_color, created_at, updated_at, user_id, slug, description, read_restricted, position)\n                   VALUES ('Staff', '283890', 'FFFFFF', now(), now(), -1, 'staff', 'Catégorie privée pour les discussions de l'équipe du forum. Les sujets ne sont visibles que pour les admins et les modérateurs.', true, 2)\n                   RETURNING id")

I, [2015-04-25T07:53:35.557179 #39]  INFO -- : Terminating async processes
I, [2015-04-25T07:53:35.557276 #39]  INFO -- : Sending INT to HOME=/var/lib/postgresql USER=postgres exec chpst -u postgres:postgres:ssl-cert -U postgres:postgres:ssl-cert /usr/lib/postgresql/9.3/bin/postmaster -D /etc/postgresql/9.3/main pid: 68
2015-04-25 07:53:35 UTC [68-2] LOG:  received fast shutdown request
2015-04-25 07:53:35 UTC [68-3] LOG:  aborting any active transactions
2015-04-25 07:53:35 UTC [75-2] LOG:  autovacuum launcher shutting down
2015-04-25 07:53:35 UTC [72-1] LOG:  shutting down
I, [2015-04-25T07:53:35.557509 #39]  INFO -- : Sending TERM to exec chpst -u redis -U redis /usr/bin/redis-server /etc/redis/redis.conf pid: 191
191:signal-handler (1429948415) Received SIGTERM scheduling shutdown...
191:M 25 Apr 07:53:35.607 # User requested shutdown...
191:M 25 Apr 07:53:35.607 * Saving the final RDB snapshot before exiting.
191:M 25 Apr 07:53:40.060 * DB saved on disk
191:M 25 Apr 07:53:40.060 # Redis is now ready to exit, bye bye...
I, [2015-04-25T07:53:45.593683 #39]  INFO -- : HOME=/var/lib/postgresql USER=postgres exec chpst -u postgres:postgres:ssl-cert -U postgres:postgres:ssl-cert /usr/lib/postgresql/9.3/bin/postmaster -D /etc/postgresql/9.3/main pid:68 did not terminate cleanly, forcing termination!


FAILED
--------------------
RuntimeError: cd /var/www/discourse && su discourse -c 'bundle exec rake db:migrate' failed with return #<Process::Status: pid 303 exit 1>
Location of failure: /pups/lib/pups/exec_command.rb:105:in `spawn'
exec failed with the params {"cd"=>"$home", "hook"=>"bundle_exec", "cmd"=>["su discourse -c 'bundle install --deployment --verbose --without test --without development'", "su discourse -c 'bundle exec rake db:migrate'", "su discourse -c 'bundle exec rake assets:precompile'"]}
fd361942a3688d08c9d47a84305aabb47cc52d1216b3812c0474cc50388c9835
FAILED TO BOOTSTRAP

I guess this is not acceptable to introduce single quotes escaping in the translation files because official translators do not have to be aware of database migrations.

elberet · 4월 25, 2015, 5:20오후

Looks like someone forgot to SQL-sanitize their data values…

github.com/discourse/discourse

db/migrate/20140227201005_add_staff_category.rb

main


      
          # frozen_string_literal: true
          
          class AddStaffCategory < ActiveRecord::Migration[4.2]
            def change
              # replaced by fixture
            end
          end

riking · 4월 25, 2015, 5:57오후

All the migrations that call I18n.t need to be checked for this.

db/migrate/20140122043508_add_meta_category.rb
db/migrate/20140120155706_add_lounge_category.rb
db/migrate/20141014191645_fix_tos_name.rb
db/migrate/20140929204155_migrate_tos_setting.rb
db/migrate/20140227201005_add_staff_category.rb

saper · 5월 16, 2015, 7:10오후

PostgreSQL supports bound parameters - then there is no need to quote anything. But I think ActiveRecord::Migration does not support this?

michaeld · 5월 16, 2015, 7:31오후

Regression?

https://meta.discourse.org/t/quote-in-category-name-breaks-migrations/21084

saper · 5월 16, 2015, 7:37오후

The fix was for db/migrate/20140122043508_add_meta_category.rb only, there are few more files with that problem. I think 20141014191645_fix_tos_name.rb does it properly:

class FixTosName < ActiveRecord::Migration
  def up
    execute ActiveRecord::Base.sql_fragment('UPDATE user_fields SET name = ? WHERE name = ?', I18n.t('terms_of_service.title'), I18n.t("terms_of_service.signup_form_message"))

  end
end

sam · 5월 28, 2015, 6:55오전

Fixed per:

https://github.com/discourse/discourse/commit/cbe45975e8f0d18f1176270e2fc5435e0f4caa13

글		댓글	조회수
Forum went down and rebuilding gives db:migrate error Self-hosting	5	1736	6월 22, 2021
Help! Upgrade led to complete fail Self-hosting	7	1471	3월 9, 2021
How to resolve Pups exec errors during Discourse bootstrap Support	21	565	4월 24, 2025
Failed To Bootstrap Pups::ExecError 'bundle exec rake db:migrate' failed with return exec failed with the params 2.9.0.beta4 to 2.9.0.beta5 Self-hosting	6	1573	6월 20, 2022
Failed to bootstrap on DO/Docker Fresh install Self-hosting server-resources	14	2874	8월 7, 2015

Docker boostrap fails due to single quotes escaping in SQL INSERT commands

Related topics