search

PecanProject / bety

Web-interface to the Biofuel Ecophysiological Traits and Yields Database (used by PEcAn and TERRA REF)
https://www.betydb.org
BSD 3-Clause "New" or "Revised" License
16 stars 38 forks source link

How to update BETY after a schema change? #331

Open serbinsh opened 9 years ago

serbinsh commented 9 years ago

I am running a simple script to dump BETYdb (to share with the PEcAn group) and load the newest updates. However, I am getting this message when running the commands which then cause the update to stop:

 bety
  % Total    % Received % Xferd  Average Speed   Time    Time     Time  Current
                                 Dload  Upload   Total   Spent    Left  Speed
100 6698k  100 6698k    0     0  7620k      0 --:--:-- --:--:-- --:--:-- 10.8M
Checking schema                    : EXPECTED SCHEMA version 20141211220550
Dump is from a different schema, please fix schema in database.

I know @robkooper mentioned recent updates to the schema. What script do I need to run to update the schema to get it to match the current version? I also need to update as I am trying to complete the Rails install to launch our web interface. I want to get the schema up to date before finishing that install

do I run the update.psql.sh script? I am sure there is a readme or wiki for this but I haven't yet found it.

@dlebauer @mdietze

robkooper commented 9 years ago

See https://github.com/PecanProject/bety/wiki/Updating-BETY#update-the-rails-app-and-schema

serbinsh commented 9 years ago

@robkooper @dlebauer

I ran this to update bety: RAILS_ENV="production" rake db:migrate --trace

and here is my output 

RAILS_ENV="production" rake db:migrate --trace
** Invoke db:migrate (first_time)
** Invoke environment (first_time)
** Execute environment
** Invoke db:load_config (first_time)
** Execute db:load_config
** Execute db:migrate
==  AddUniquenessConstraints: migrating =======================================
-- remove_column(:posteriors, :format_id)
   -> 0.0054s
-- execute("\n\n-- Some convenience functions\nCREATE OR REPLACE FUNCTION normalize_whitespace(\n  string text\n) RETURNS text AS $$\nDECLARE\n  result text;\nBEGIN\n  SELECT TRIM(REGEXP_REPLACE(string, '\\s+', ' ', 'g')) INTO result;\n  RETURN result;\nEND;\n$$ LANGUAGE plpgsql;\n\nCOMMENT ON FUNCTION normalize_whitespace(text) IS 'Removes leading and trailing whitespace from string '\n  'and replaces internal sequences of whitespace with a single space character.';\n\nCREATE OR REPLACE FUNCTION is_whitespace_normalized(\n  string text\n) RETURNS boolean AS $$\nBEGIN\n  RETURN string = normalize_whitespace(string);\nEND;\n$$ LANGUAGE plpgsql;\n\nCOMMENT ON FUNCTION is_whitespace_normalized(text) IS 'Returns true if text contains no leading or trailing spaces, '\n  'no whitespace other than spaces, and no consecutive spaces.';\n\nCREATE OR REPLACE FUNCTION normalize_name_whitespace()\n  RETURNS TRIGGER AS $$\nBEGIN\n  NEW.name = normalize_whitespace(NEW.name);\n  RETURN NEW;\nEND;\n$$ LANGUAGE plpgsql;\n\n\n\n-- GH #182\nALTER TABLE cultivars ALTER COLUMN name SET NOT NULL;\nALTER TABLE cultivars ALTER COLUMN specie_id SET NOT NULL;\nALTER TABLE cultivars ADD CONSTRAINT unique_name_per_species UNIQUE (name, specie_id);\n\nALTER TABLE cultivars ADD CONSTRAINT normalized_names CHECK (is_whitespace_normalized(name));\n\nDROP TRIGGER IF EXISTS normalize_cultivar_names ON cultivars;\nCREATE TRIGGER normalize_cultivar_names\n  BEFORE INSERT OR UPDATE ON cultivars\n  FOR EACH ROW\n    EXECUTE PROCEDURE normalize_name_whitespace();\n\n-- GH #183\nALTER TABLE dbfiles ADD CONSTRAINT unique_filename_and_path_per_machine UNIQUE (file_name, file_path, machine_id);\nALTER TABLE dbfiles ALTER COLUMN file_name SET NOT NULL;\nALTER TABLE dbfiles ALTER COLUMN file_path SET NOT NULL;\nALTER TABLE dbfiles ALTER COLUMN machine_id SET NOT NULL;\nALTER TABLE dbfiles ADD CONSTRAINT no_slash_in_file_name CHECK (file_name !~ '/');\nALTER TABLE dbfiles ADD CONSTRAINT file_path_sanity_check CHECK (file_path ~ '^/');\n-- Also add a comment on this table's container_type column:\nCOMMENT ON COLUMN dbfiles.container_type IS 'this and container_id are part of a polymorphic relationship, specifies table and primary key of that table';\n\n-- GH #187\nALTER TABLE inputs_runs ALTER COLUMN input_id SET NOT NULL;\nALTER TABLE inputs_runs ALTER COLUMN run_id SET NOT NULL;\nALTER TABLE inputs_runs ADD CONSTRAINT unique_input_run_pair UNIQUE (input_id, run_id);\n\n-- GH #188\nALTER TABLE inputs_variables ALTER COLUMN input_id SET NOT NULL;\nALTER TABLE inputs_variables ALTER COLUMN variable_id SET NOT NULL;\nALTER TABLE inputs_variables ADD CONSTRAINT unique_input_variable_pairs UNIQUE (input_id, variable_id);\n\n-- GH #189\nALTER TABLE likelihoods ALTER COLUMN run_id SET NOT NULL;\nALTER TABLE likelihoods ALTER COLUMN variable_id SET NOT NULL;\nALTER TABLE likelihoods ALTER COLUMN input_id SET NOT NULL;\nALTER TABLE likelihoods ADD CONSTRAINT unique_run_variable_input_combination UNIQUE (run_id, variable_id, input_id);\n\n-- GH #190\nALTER TABLE machines ALTER COLUMN hostname SET NOT NULL;\nALTER TABLE machines ADD CONSTRAINT unique_hostnames UNIQUE (hostname);\n\n-- GH #192\nALTER TABLE managements_treatments ALTER COLUMN treatment_id SET NOT NULL;\nALTER TABLE managements_treatments ALTER COLUMN management_id SET NOT NULL;\n\n-- GH #194\nALTER TABLE mimetypes ALTER COLUMN type_string SET NOT NULL;\nALTER TABLE mimetypes ADD CONSTRAINT unique_type_string UNIQUE (type_string);\nALTER TABLE mimetypes ADD CONSTRAINT valid_mime_type CHECK (type_string ~ '^(application|audio|chemical|drawing|image|i-world|message|model|multipart|music|paleovu|text|video|windows|www|x-conference|xgl|x-music|x-world)/[a-z.0-9_-]+( \\((old|compiled elisp)\\))?$');\n\n-- GH #195\nALTER TABLE pfts_priors ALTER COLUMN pft_id SET NOT NULL;\nALTER TABLE pfts_priors ALTER COLUMN prior_id SET NOT NULL;\n\n-- GH #197\nALTER TABLE pfts ADD CONSTRAINT unique_name_per_model UNIQUE (name, modeltype_id);\n\n-- GH #198\nALTER TABLE posteriors ALTER COLUMN pft_id SET NOT NULL;\n\n-- GH #200\nALTER TABLE runs ALTER COLUMN model_id SET NOT NULL;\nALTER TABLE runs ALTER COLUMN site_id SET NOT NULL;\nALTER TABLE runs ALTER COLUMN start_time SET NOT NULL;\nALTER TABLE runs ALTER COLUMN finish_time SET NOT NULL;\nALTER TABLE runs ALTER COLUMN parameter_list SET NOT NULL;\nALTER TABLE runs ALTER COLUMN ensemble_id SET NOT NULL;\nALTER TABLE runs ADD CONSTRAINT unique_time_interval_per_model_site_parameter_list_and_ensemble_combination UNIQUE (model_id, site_id, start_time, finish_time, parameter_list, ensemble_id);\nCOMMENT ON COLUMN runs.start_time IS 'beginning of time period being simulated';\nCOMMENT ON COLUMN runs.finish_time IS 'end of time period being simulated';\nCOMMENT ON COLUMN runs.started_at IS 'system time when run begins';\nCOMMENT ON COLUMN runs.finished_at IS 'system time when run ends; can be null when record is created';\n\n\n-- GH #213\nALTER TABLE citations_sites ALTER COLUMN citation_id SET NOT NULL;\nALTER TABLE citations_sites ALTER COLUMN site_id SET NOT NULL;\n\n-- GH #215\nALTER TABLE citations_treatments ALTER COLUMN citation_id SET NOT NULL;\nALTER TABLE citations_treatments ALTER COLUMN treatment_id SET NOT NULL;\n\n    ")
NOTICE:  identifier "unique_time_interval_per_model_site_parameter_list_and_ensemble_combination" will be truncated to "unique_time_interval_per_model_site_parameter_list_and_ensemble"
NOTICE:  trigger "normalize_cultivar_names" for relation "cultivars" does not exist, skipping
rake aborted!
StandardError: An error has occurred, this and all later migrations canceled:

PG::CheckViolation: ERROR:  check constraint "file_path_sanity_check" is violated by some row
:

-- Some convenience functions
CREATE OR REPLACE FUNCTION normalize_whitespace(
  string text
) RETURNS text AS $$
DECLARE
  result text;
BEGIN
  SELECT TRIM(REGEXP_REPLACE(string, '\s+', ' ', 'g')) INTO result;
  RETURN result;
END;
$$ LANGUAGE plpgsql;

COMMENT ON FUNCTION normalize_whitespace(text) IS 'Removes leading and trailing whitespace from string '
  'and replaces internal sequences of whitespace with a single space character.';

CREATE OR REPLACE FUNCTION is_whitespace_normalized(
  string text
) RETURNS boolean AS $$
BEGIN
  RETURN string = normalize_whitespace(string);
END;
$$ LANGUAGE plpgsql;

COMMENT ON FUNCTION is_whitespace_normalized(text) IS 'Returns true if text contains no leading or trailing spaces, '
  'no whitespace other than spaces, and no consecutive spaces.';

CREATE OR REPLACE FUNCTION normalize_name_whitespace()
  RETURNS TRIGGER AS $$
BEGIN
  NEW.name = normalize_whitespace(NEW.name);
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- GH #182
ALTER TABLE cultivars ALTER COLUMN name SET NOT NULL;
ALTER TABLE cultivars ALTER COLUMN specie_id SET NOT NULL;
ALTER TABLE cultivars ADD CONSTRAINT unique_name_per_species UNIQUE (name, specie_id);

ALTER TABLE cultivars ADD CONSTRAINT normalized_names CHECK (is_whitespace_normalized(name));

DROP TRIGGER IF EXISTS normalize_cultivar_names ON cultivars;
CREATE TRIGGER normalize_cultivar_names
  BEFORE INSERT OR UPDATE ON cultivars
  FOR EACH ROW
    EXECUTE PROCEDURE normalize_name_whitespace();

-- GH #183
ALTER TABLE dbfiles ADD CONSTRAINT unique_filename_and_path_per_machine UNIQUE (file_name, file_path, machine_id);
ALTER TABLE dbfiles ALTER COLUMN file_name SET NOT NULL;
ALTER TABLE dbfiles ALTER COLUMN file_path SET NOT NULL;
ALTER TABLE dbfiles ALTER COLUMN machine_id SET NOT NULL;
ALTER TABLE dbfiles ADD CONSTRAINT no_slash_in_file_name CHECK (file_name !~ '/');
ALTER TABLE dbfiles ADD CONSTRAINT file_path_sanity_check CHECK (file_path ~ '^/');
-- Also add a comment on this table's container_type column:
COMMENT ON COLUMN dbfiles.container_type IS 'this and container_id are part of a polymorphic relationship, specifies table and primary key of that table';

-- GH #187
ALTER TABLE inputs_runs ALTER COLUMN input_id SET NOT NULL;
ALTER TABLE inputs_runs ALTER COLUMN run_id SET NOT NULL;
ALTER TABLE inputs_runs ADD CONSTRAINT unique_input_run_pair UNIQUE (input_id, run_id);

-- GH #188
ALTER TABLE inputs_variables ALTER COLUMN input_id SET NOT NULL;
ALTER TABLE inputs_variables ALTER COLUMN variable_id SET NOT NULL;
ALTER TABLE inputs_variables ADD CONSTRAINT unique_input_variable_pairs UNIQUE (input_id, variable_id);

-- GH #189
ALTER TABLE likelihoods ALTER COLUMN run_id SET NOT NULL;
ALTER TABLE likelihoods ALTER COLUMN variable_id SET NOT NULL;
ALTER TABLE likelihoods ALTER COLUMN input_id SET NOT NULL;
ALTER TABLE likelihoods ADD CONSTRAINT unique_run_variable_input_combination UNIQUE (run_id, variable_id, input_id);

-- GH #190
ALTER TABLE machines ALTER COLUMN hostname SET NOT NULL;
ALTER TABLE machines ADD CONSTRAINT unique_hostnames UNIQUE (hostname);

-- GH #192
ALTER TABLE managements_treatments ALTER COLUMN treatment_id SET NOT NULL;
ALTER TABLE managements_treatments ALTER COLUMN management_id SET NOT NULL;

-- GH #194
ALTER TABLE mimetypes ALTER COLUMN type_string SET NOT NULL;
ALTER TABLE mimetypes ADD CONSTRAINT unique_type_string UNIQUE (type_string);
ALTER TABLE mimetypes ADD CONSTRAINT valid_mime_type CHECK (type_string ~ '^(application|audio|chemical|drawing|image|i-world|message|model|multipart|music|paleovu|text|video|windows|www|x-conference|xgl|x-music|x-world)/[a-z.0-9_-]+( \((old|compiled elisp)\))?$');

-- GH #195
ALTER TABLE pfts_priors ALTER COLUMN pft_id SET NOT NULL;
ALTER TABLE pfts_priors ALTER COLUMN prior_id SET NOT NULL;

-- GH #197
ALTER TABLE pfts ADD CONSTRAINT unique_name_per_model UNIQUE (name, modeltype_id);

-- GH #198
ALTER TABLE posteriors ALTER COLUMN pft_id SET NOT NULL;

-- GH #200
ALTER TABLE runs ALTER COLUMN model_id SET NOT NULL;
ALTER TABLE runs ALTER COLUMN site_id SET NOT NULL;
ALTER TABLE runs ALTER COLUMN start_time SET NOT NULL;
ALTER TABLE runs ALTER COLUMN finish_time SET NOT NULL;
ALTER TABLE runs ALTER COLUMN parameter_list SET NOT NULL;
ALTER TABLE runs ALTER COLUMN ensemble_id SET NOT NULL;
ALTER TABLE runs ADD CONSTRAINT unique_time_interval_per_model_site_parameter_list_and_ensemble_combination UNIQUE (model_id, site_id, start_time, finish_time, parameter_list, ensemble_id);
COMMENT ON COLUMN runs.start_time IS 'beginning of time period being simulated';
COMMENT ON COLUMN runs.finish_time IS 'end of time period being simulated';
COMMENT ON COLUMN runs.started_at IS 'system time when run begins';
COMMENT ON COLUMN runs.finished_at IS 'system time when run ends; can be null when record is created';

-- GH #213
ALTER TABLE citations_sites ALTER COLUMN citation_id SET NOT NULL;
ALTER TABLE citations_sites ALTER COLUMN site_id SET NOT NULL;

-- GH #215
ALTER TABLE citations_treatments ALTER COLUMN citation_id SET NOT NULL;
ALTER TABLE citations_treatments ALTER COLUMN treatment_id SET NOT NULL;

    /opt/ruby-2.1.0/lib/ruby/gems/2.1.0/gems/activerecord-3.2.21/lib/active_record/connection_adapters/postgresql_adapter.rb:650:in `exec'
/opt/ruby-2.1.0/lib/ruby/gems/2.1.0/gems/activerecord-3.2.21/lib/active_record/connection_adapters/postgresql_adapter.rb:650:in `block in execute'
/opt/ruby-2.1.0/lib/ruby/gems/2.1.0/gems/activerecord-3.2.21/lib/active_record/connection_adapters/abstract_adapter.rb:280:in `block in log'
/opt/ruby-2.1.0/lib/ruby/gems/2.1.0/gems/activesupport-3.2.21/lib/active_support/notifications/instrumenter.rb:20:in `instrument'
/opt/ruby-2.1.0/lib/ruby/gems/2.1.0/gems/activerecord-3.2.21/lib/active_record/connection_adapters/abstract_adapter.rb:275:in `log'
/opt/ruby-2.1.0/lib/ruby/gems/2.1.0/gems/activerecord-3.2.21/lib/active_record/connection_adapters/postgresql_adapter.rb:649:in `execute'
/opt/ruby-2.1.0/lib/ruby/gems/2.1.0/gems/activerecord-3.2.21/lib/active_record/migration.rb:466:in `block in method_missing'
/opt/ruby-2.1.0/lib/ruby/gems/2.1.0/gems/activerecord-3.2.21/lib/active_record/migration.rb:438:in `block in say_with_time'
/opt/ruby-2.1.0/lib/ruby/2.1.0/benchmark.rb:279:in `measure'
/opt/ruby-2.1.0/lib/ruby/gems/2.1.0/gems/activerecord-3.2.21/lib/active_record/migration.rb:438:in `say_with_time'
/opt/ruby-2.1.0/lib/ruby/gems/2.1.0/gems/activerecord-3.2.21/lib/active_record/migration.rb:458:in `method_missing'
/opt/ruby-2.1.0/lib/ruby/gems/2.1.0/gems/activerecord-3.2.21/lib/active_record/migration.rb:334:in `method_missing'
/data/software/bety/db/migrate/20150202220519_add_uniqueness_constraints.rb:7:in `up'
/opt/ruby-2.1.0/lib/ruby/gems/2.1.0/gems/activerecord-3.2.21/lib/active_record/migration.rb:370:in `up'
/opt/ruby-2.1.0/lib/ruby/gems/2.1.0/gems/activerecord-3.2.21/lib/active_record/migration.rb:410:in `block (2 levels) in migrate'
/opt/ruby-2.1.0/lib/ruby/2.1.0/benchmark.rb:279:in `measure'
/opt/ruby-2.1.0/lib/ruby/gems/2.1.0/gems/activerecord-3.2.21/lib/active_record/migration.rb:410:in `block in migrate'
/opt/ruby-2.1.0/lib/ruby/gems/2.1.0/gems/activerecord-3.2.21/lib/active_record/connection_adapters/abstract/connection_pool.rb:129:in `with_connection'
/opt/ruby-2.1.0/lib/ruby/gems/2.1.0/gems/activerecord-3.2.21/lib/active_record/migration.rb:389:in `migrate'
/opt/ruby-2.1.0/lib/ruby/gems/2.1.0/gems/activerecord-3.2.21/lib/active_record/migration.rb:528:in `migrate'
/opt/ruby-2.1.0/lib/ruby/gems/2.1.0/gems/activerecord-3.2.21/lib/active_record/migration.rb:720:in `block (2 levels) in migrate'
/opt/ruby-2.1.0/lib/ruby/gems/2.1.0/gems/activerecord-3.2.21/lib/active_record/migration.rb:775:in `call'
/opt/ruby-2.1.0/lib/ruby/gems/2.1.0/gems/activerecord-3.2.21/lib/active_record/migration.rb:775:in `block in ddl_transaction'
/opt/ruby-2.1.0/lib/ruby/gems/2.1.0/gems/activerecord-3.2.21/lib/active_record/connection_adapters/abstract/database_statements.rb:192:in `transaction'
/opt/ruby-2.1.0/lib/ruby/gems/2.1.0/gems/activerecord-3.2.21/lib/active_record/transactions.rb:208:in `transaction'
/opt/ruby-2.1.0/lib/ruby/gems/2.1.0/gems/activerecord-3.2.21/lib/active_record/migration.rb:775:in `ddl_transaction'
/opt/ruby-2.1.0/lib/ruby/gems/2.1.0/gems/activerecord-3.2.21/lib/active_record/migration.rb:719:in `block in migrate'
/opt/ruby-2.1.0/lib/ruby/gems/2.1.0/gems/activerecord-3.2.21/lib/active_record/migration.rb:700:in `each'
/opt/ruby-2.1.0/lib/ruby/gems/2.1.0/gems/activerecord-3.2.21/lib/active_record/migration.rb:700:in `migrate'
/opt/ruby-2.1.0/lib/ruby/gems/2.1.0/gems/activerecord-3.2.21/lib/active_record/migration.rb:570:in `up'
/opt/ruby-2.1.0/lib/ruby/gems/2.1.0/gems/activerecord-3.2.21/lib/active_record/migration.rb:551:in `migrate'
/opt/ruby-2.1.0/lib/ruby/gems/2.1.0/gems/activerecord-3.2.21/lib/active_record/railties/databases.rake:193:in `block (2 levels) in <top (required)>'
/opt/ruby-2.1.0/lib/ruby/gems/2.1.0/gems/rake-10.4.2/lib/rake/task.rb:240:in `call'
/opt/ruby-2.1.0/lib/ruby/gems/2.1.0/gems/rake-10.4.2/lib/rake/task.rb:240:in `block in execute'
/opt/ruby-2.1.0/lib/ruby/gems/2.1.0/gems/rake-10.4.2/lib/rake/task.rb:235:in `each'
/opt/ruby-2.1.0/lib/ruby/gems/2.1.0/gems/rake-10.4.2/lib/rake/task.rb:235:in `execute'
/opt/ruby-2.1.0/lib/ruby/gems/2.1.0/gems/rake-10.4.2/lib/rake/task.rb:179:in `block in invoke_with_call_chain'
/opt/ruby-2.1.0/lib/ruby/2.1.0/monitor.rb:211:in `mon_synchronize'
/opt/ruby-2.1.0/lib/ruby/gems/2.1.0/gems/rake-10.4.2/lib/rake/task.rb:172:in `invoke_with_call_chain'
/opt/ruby-2.1.0/lib/ruby/gems/2.1.0/gems/rake-10.4.2/lib/rake/task.rb:165:in `invoke'
/opt/ruby-2.1.0/lib/ruby/gems/2.1.0/gems/rake-10.4.2/lib/rake/application.rb:150:in `invoke_task'
/opt/ruby-2.1.0/lib/ruby/gems/2.1.0/gems/rake-10.4.2/lib/rake/application.rb:106:in `block (2 levels) in top_level'
/opt/ruby-2.1.0/lib/ruby/gems/2.1.0/gems/rake-10.4.2/lib/rake/application.rb:106:in `each'
/opt/ruby-2.1.0/lib/ruby/gems/2.1.0/gems/rake-10.4.2/lib/rake/application.rb:106:in `block in top_level'
/opt/ruby-2.1.0/lib/ruby/gems/2.1.0/gems/rake-10.4.2/lib/rake/application.rb:115:in `run_with_threads'
/opt/ruby-2.1.0/lib/ruby/gems/2.1.0/gems/rake-10.4.2/lib/rake/application.rb:100:in `top_level'
/opt/ruby-2.1.0/lib/ruby/gems/2.1.0/gems/rake-10.4.2/lib/rake/application.rb:78:in `block in run'
/opt/ruby-2.1.0/lib/ruby/gems/2.1.0/gems/rake-10.4.2/lib/rake/application.rb:176:in `standard_exception_handling'
/opt/ruby-2.1.0/lib/ruby/gems/2.1.0/gems/rake-10.4.2/lib/rake/application.rb:75:in `run'
/opt/ruby-2.1.0/lib/ruby/gems/2.1.0/gems/rake-10.4.2/bin/rake:33:in `<top (required)>'
/opt/ruby-2.1.0/bin/rake:23:in `load'
/opt/ruby-2.1.0/bin/rake:23:in `<main>'
ActiveRecord::StatementInvalid: PG::CheckViolation: ERROR:  check constraint "file_path_sanity_check" is violated by some row
:

-- Some convenience functions
CREATE OR REPLACE FUNCTION normalize_whitespace(
  string text
) RETURNS text AS $$
DECLARE
  result text;
BEGIN
  SELECT TRIM(REGEXP_REPLACE(string, '\s+', ' ', 'g')) INTO result;
  RETURN result;
END;
$$ LANGUAGE plpgsql;

COMMENT ON FUNCTION normalize_whitespace(text) IS 'Removes leading and trailing whitespace from string '
  'and replaces internal sequences of whitespace with a single space character.';

CREATE OR REPLACE FUNCTION is_whitespace_normalized(
  string text
) RETURNS boolean AS $$
BEGIN
  RETURN string = normalize_whitespace(string);
END;
$$ LANGUAGE plpgsql;

COMMENT ON FUNCTION is_whitespace_normalized(text) IS 'Returns true if text contains no leading or trailing spaces, '
  'no whitespace other than spaces, and no consecutive spaces.';

CREATE OR REPLACE FUNCTION normalize_name_whitespace()
  RETURNS TRIGGER AS $$
BEGIN
  NEW.name = normalize_whitespace(NEW.name);
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- GH #182
ALTER TABLE cultivars ALTER COLUMN name SET NOT NULL;
ALTER TABLE cultivars ALTER COLUMN specie_id SET NOT NULL;
ALTER TABLE cultivars ADD CONSTRAINT unique_name_per_species UNIQUE (name, specie_id);

ALTER TABLE cultivars ADD CONSTRAINT normalized_names CHECK (is_whitespace_normalized(name));

DROP TRIGGER IF EXISTS normalize_cultivar_names ON cultivars;
CREATE TRIGGER normalize_cultivar_names
  BEFORE INSERT OR UPDATE ON cultivars
  FOR EACH ROW
    EXECUTE PROCEDURE normalize_name_whitespace();

-- GH #183
ALTER TABLE dbfiles ADD CONSTRAINT unique_filename_and_path_per_machine UNIQUE (file_name, file_path, machine_id);
ALTER TABLE dbfiles ALTER COLUMN file_name SET NOT NULL;
ALTER TABLE dbfiles ALTER COLUMN file_path SET NOT NULL;
ALTER TABLE dbfiles ALTER COLUMN machine_id SET NOT NULL;
ALTER TABLE dbfiles ADD CONSTRAINT no_slash_in_file_name CHECK (file_name !~ '/');
ALTER TABLE dbfiles ADD CONSTRAINT file_path_sanity_check CHECK (file_path ~ '^/');
-- Also add a comment on this table's container_type column:
COMMENT ON COLUMN dbfiles.container_type IS 'this and container_id are part of a polymorphic relationship, specifies table and primary key of that table';

-- GH #187
ALTER TABLE inputs_runs ALTER COLUMN input_id SET NOT NULL;
ALTER TABLE inputs_runs ALTER COLUMN run_id SET NOT NULL;
ALTER TABLE inputs_runs ADD CONSTRAINT unique_input_run_pair UNIQUE (input_id, run_id);

-- GH #188
ALTER TABLE inputs_variables ALTER COLUMN input_id SET NOT NULL;
ALTER TABLE inputs_variables ALTER COLUMN variable_id SET NOT NULL;
ALTER TABLE inputs_variables ADD CONSTRAINT unique_input_variable_pairs UNIQUE (input_id, variable_id);

-- GH #189
ALTER TABLE likelihoods ALTER COLUMN run_id SET NOT NULL;
ALTER TABLE likelihoods ALTER COLUMN variable_id SET NOT NULL;
ALTER TABLE likelihoods ALTER COLUMN input_id SET NOT NULL;
ALTER TABLE likelihoods ADD CONSTRAINT unique_run_variable_input_combination UNIQUE (run_id, variable_id, input_id);

-- GH #190
ALTER TABLE machines ALTER COLUMN hostname SET NOT NULL;
ALTER TABLE machines ADD CONSTRAINT unique_hostnames UNIQUE (hostname);

-- GH #192
ALTER TABLE managements_treatments ALTER COLUMN treatment_id SET NOT NULL;
ALTER TABLE managements_treatments ALTER COLUMN management_id SET NOT NULL;

-- GH #194
ALTER TABLE mimetypes ALTER COLUMN type_string SET NOT NULL;
ALTER TABLE mimetypes ADD CONSTRAINT unique_type_string UNIQUE (type_string);
ALTER TABLE mimetypes ADD CONSTRAINT valid_mime_type CHECK (type_string ~ '^(application|audio|chemical|drawing|image|i-world|message|model|multipart|music|paleovu|text|video|windows|www|x-conference|xgl|x-music|x-world)/[a-z.0-9_-]+( \((old|compiled elisp)\))?$');

-- GH #195
ALTER TABLE pfts_priors ALTER COLUMN pft_id SET NOT NULL;
ALTER TABLE pfts_priors ALTER COLUMN prior_id SET NOT NULL;

-- GH #197
ALTER TABLE pfts ADD CONSTRAINT unique_name_per_model UNIQUE (name, modeltype_id);

-- GH #198
ALTER TABLE posteriors ALTER COLUMN pft_id SET NOT NULL;

-- GH #200
ALTER TABLE runs ALTER COLUMN model_id SET NOT NULL;
ALTER TABLE runs ALTER COLUMN site_id SET NOT NULL;
ALTER TABLE runs ALTER COLUMN start_time SET NOT NULL;
ALTER TABLE runs ALTER COLUMN finish_time SET NOT NULL;
ALTER TABLE runs ALTER COLUMN parameter_list SET NOT NULL;
ALTER TABLE runs ALTER COLUMN ensemble_id SET NOT NULL;
ALTER TABLE runs ADD CONSTRAINT unique_time_interval_per_model_site_parameter_list_and_ensemble_combination UNIQUE (model_id, site_id, start_time, finish_time, parameter_list, ensemble_id);
COMMENT ON COLUMN runs.start_time IS 'beginning of time period being simulated';
COMMENT ON COLUMN runs.finish_time IS 'end of time period being simulated';
COMMENT ON COLUMN runs.started_at IS 'system time when run begins';
COMMENT ON COLUMN runs.finished_at IS 'system time when run ends; can be null when record is created';

-- GH #213
ALTER TABLE citations_sites ALTER COLUMN citation_id SET NOT NULL;
ALTER TABLE citations_sites ALTER COLUMN site_id SET NOT NULL;

-- GH #215
ALTER TABLE citations_treatments ALTER COLUMN citation_id SET NOT NULL;
ALTER TABLE citations_treatments ALTER COLUMN treatment_id SET NOT NULL;

    /opt/ruby-2.1.0/lib/ruby/gems/2.1.0/gems/activerecord-3.2.21/lib/active_record/connection_adapters/postgresql_adapter.rb:650:in `exec'
/opt/ruby-2.1.0/lib/ruby/gems/2.1.0/gems/activerecord-3.2.21/lib/active_record/connection_adapters/postgresql_adapter.rb:650:in `block in execute'
/opt/ruby-2.1.0/lib/ruby/gems/2.1.0/gems/activerecord-3.2.21/lib/active_record/connection_adapters/abstract_adapter.rb:280:in `block in log'
/opt/ruby-2.1.0/lib/ruby/gems/2.1.0/gems/activesupport-3.2.21/lib/active_support/notifications/instrumenter.rb:20:in `instrument'
/opt/ruby-2.1.0/lib/ruby/gems/2.1.0/gems/activerecord-3.2.21/lib/active_record/connection_adapters/abstract_adapter.rb:275:in `log'
/opt/ruby-2.1.0/lib/ruby/gems/2.1.0/gems/activerecord-3.2.21/lib/active_record/connection_adapters/postgresql_adapter.rb:649:in `execute'
/opt/ruby-2.1.0/lib/ruby/gems/2.1.0/gems/activerecord-3.2.21/lib/active_record/migration.rb:466:in `block in method_missing'
/opt/ruby-2.1.0/lib/ruby/gems/2.1.0/gems/activerecord-3.2.21/lib/active_record/migration.rb:438:in `block in say_with_time'
/opt/ruby-2.1.0/lib/ruby/2.1.0/benchmark.rb:279:in `measure'
/opt/ruby-2.1.0/lib/ruby/gems/2.1.0/gems/activerecord-3.2.21/lib/active_record/migration.rb:438:in `say_with_time'
/opt/ruby-2.1.0/lib/ruby/gems/2.1.0/gems/activerecord-3.2.21/lib/active_record/migration.rb:458:in `method_missing'
/opt/ruby-2.1.0/lib/ruby/gems/2.1.0/gems/activerecord-3.2.21/lib/active_record/migration.rb:334:in `method_missing'
/data/software/bety/db/migrate/20150202220519_add_uniqueness_constraints.rb:7:in `up'
/opt/ruby-2.1.0/lib/ruby/gems/2.1.0/gems/activerecord-3.2.21/lib/active_record/migration.rb:370:in `up'
/opt/ruby-2.1.0/lib/ruby/gems/2.1.0/gems/activerecord-3.2.21/lib/active_record/migration.rb:410:in `block (2 levels) in migrate'
/opt/ruby-2.1.0/lib/ruby/2.1.0/benchmark.rb:279:in `measure'
/opt/ruby-2.1.0/lib/ruby/gems/2.1.0/gems/activerecord-3.2.21/lib/active_record/migration.rb:410:in `block in migrate'
/opt/ruby-2.1.0/lib/ruby/gems/2.1.0/gems/activerecord-3.2.21/lib/active_record/connection_adapters/abstract/connection_pool.rb:129:in `with_connection'
/opt/ruby-2.1.0/lib/ruby/gems/2.1.0/gems/activerecord-3.2.21/lib/active_record/migration.rb:389:in `migrate'
/opt/ruby-2.1.0/lib/ruby/gems/2.1.0/gems/activerecord-3.2.21/lib/active_record/migration.rb:528:in `migrate'
/opt/ruby-2.1.0/lib/ruby/gems/2.1.0/gems/activerecord-3.2.21/lib/active_record/migration.rb:720:in `block (2 levels) in migrate'
/opt/ruby-2.1.0/lib/ruby/gems/2.1.0/gems/activerecord-3.2.21/lib/active_record/migration.rb:775:in `call'
/opt/ruby-2.1.0/lib/ruby/gems/2.1.0/gems/activerecord-3.2.21/lib/active_record/migration.rb:775:in `block in ddl_transaction'
/opt/ruby-2.1.0/lib/ruby/gems/2.1.0/gems/activerecord-3.2.21/lib/active_record/connection_adapters/abstract/database_statements.rb:192:in `transaction'
/opt/ruby-2.1.0/lib/ruby/gems/2.1.0/gems/activerecord-3.2.21/lib/active_record/transactions.rb:208:in `transaction'
/opt/ruby-2.1.0/lib/ruby/gems/2.1.0/gems/activerecord-3.2.21/lib/active_record/migration.rb:775:in `ddl_transaction'
/opt/ruby-2.1.0/lib/ruby/gems/2.1.0/gems/activerecord-3.2.21/lib/active_record/migration.rb:719:in `block in migrate'
/opt/ruby-2.1.0/lib/ruby/gems/2.1.0/gems/activerecord-3.2.21/lib/active_record/migration.rb:700:in `each'
/opt/ruby-2.1.0/lib/ruby/gems/2.1.0/gems/activerecord-3.2.21/lib/active_record/migration.rb:700:in `migrate'
/opt/ruby-2.1.0/lib/ruby/gems/2.1.0/gems/activerecord-3.2.21/lib/active_record/migration.rb:570:in `up'
/opt/ruby-2.1.0/lib/ruby/gems/2.1.0/gems/activerecord-3.2.21/lib/active_record/migration.rb:551:in `migrate'
/opt/ruby-2.1.0/lib/ruby/gems/2.1.0/gems/activerecord-3.2.21/lib/active_record/railties/databases.rake:193:in `block (2 levels) in <top (required)>'
/opt/ruby-2.1.0/lib/ruby/gems/2.1.0/gems/rake-10.4.2/lib/rake/task.rb:240:in `call'
/opt/ruby-2.1.0/lib/ruby/gems/2.1.0/gems/rake-10.4.2/lib/rake/task.rb:240:in `block in execute'
/opt/ruby-2.1.0/lib/ruby/gems/2.1.0/gems/rake-10.4.2/lib/rake/task.rb:235:in `each'
/opt/ruby-2.1.0/lib/ruby/gems/2.1.0/gems/rake-10.4.2/lib/rake/task.rb:235:in `execute'
/opt/ruby-2.1.0/lib/ruby/gems/2.1.0/gems/rake-10.4.2/lib/rake/task.rb:179:in `block in invoke_with_call_chain'
/opt/ruby-2.1.0/lib/ruby/2.1.0/monitor.rb:211:in `mon_synchronize'
/opt/ruby-2.1.0/lib/ruby/gems/2.1.0/gems/rake-10.4.2/lib/rake/task.rb:172:in `invoke_with_call_chain'
/opt/ruby-2.1.0/lib/ruby/gems/2.1.0/gems/rake-10.4.2/lib/rake/task.rb:165:in `invoke'
/opt/ruby-2.1.0/lib/ruby/gems/2.1.0/gems/rake-10.4.2/lib/rake/application.rb:150:in `invoke_task'
/opt/ruby-2.1.0/lib/ruby/gems/2.1.0/gems/rake-10.4.2/lib/rake/application.rb:106:in `block (2 levels) in top_level'
/opt/ruby-2.1.0/lib/ruby/gems/2.1.0/gems/rake-10.4.2/lib/rake/application.rb:106:in `each'
/opt/ruby-2.1.0/lib/ruby/gems/2.1.0/gems/rake-10.4.2/lib/rake/application.rb:106:in `block in top_level'
/opt/ruby-2.1.0/lib/ruby/gems/2.1.0/gems/rake-10.4.2/lib/rake/application.rb:115:in `run_with_threads'
/opt/ruby-2.1.0/lib/ruby/gems/2.1.0/gems/rake-10.4.2/lib/rake/application.rb:100:in `top_level'
/opt/ruby-2.1.0/lib/ruby/gems/2.1.0/gems/rake-10.4.2/lib/rake/application.rb:78:in `block in run'
/opt/ruby-2.1.0/lib/ruby/gems/2.1.0/gems/rake-10.4.2/lib/rake/application.rb:176:in `standard_exception_handling'
/opt/ruby-2.1.0/lib/ruby/gems/2.1.0/gems/rake-10.4.2/lib/rake/application.rb:75:in `run'
/opt/ruby-2.1.0/lib/ruby/gems/2.1.0/gems/rake-10.4.2/bin/rake:33:in `<top (required)>'
/opt/ruby-2.1.0/bin/rake:23:in `load'
/opt/ruby-2.1.0/bin/rake:23:in `<main>'
PG::CheckViolation: ERROR:  check constraint "file_path_sanity_check" is violated by some row
/opt/ruby-2.1.0/lib/ruby/gems/2.1.0/gems/activerecord-3.2.21/lib/active_record/connection_adapters/postgresql_adapter.rb:650:in `exec'
/opt/ruby-2.1.0/lib/ruby/gems/2.1.0/gems/activerecord-3.2.21/lib/active_record/connection_adapters/postgresql_adapter.rb:650:in `block in execute'
/opt/ruby-2.1.0/lib/ruby/gems/2.1.0/gems/activerecord-3.2.21/lib/active_record/connection_adapters/abstract_adapter.rb:280:in `block in log'
/opt/ruby-2.1.0/lib/ruby/gems/2.1.0/gems/activesupport-3.2.21/lib/active_support/notifications/instrumenter.rb:20:in `instrument'
/opt/ruby-2.1.0/lib/ruby/gems/2.1.0/gems/activerecord-3.2.21/lib/active_record/connection_adapters/abstract_adapter.rb:275:in `log'
/opt/ruby-2.1.0/lib/ruby/gems/2.1.0/gems/activerecord-3.2.21/lib/active_record/connection_adapters/postgresql_adapter.rb:649:in `execute'
/opt/ruby-2.1.0/lib/ruby/gems/2.1.0/gems/activerecord-3.2.21/lib/active_record/migration.rb:466:in `block in method_missing'
/opt/ruby-2.1.0/lib/ruby/gems/2.1.0/gems/activerecord-3.2.21/lib/active_record/migration.rb:438:in `block in say_with_time'
/opt/ruby-2.1.0/lib/ruby/2.1.0/benchmark.rb:279:in `measure'
/opt/ruby-2.1.0/lib/ruby/gems/2.1.0/gems/activerecord-3.2.21/lib/active_record/migration.rb:438:in `say_with_time'
/opt/ruby-2.1.0/lib/ruby/gems/2.1.0/gems/activerecord-3.2.21/lib/active_record/migration.rb:458:in `method_missing'
/opt/ruby-2.1.0/lib/ruby/gems/2.1.0/gems/activerecord-3.2.21/lib/active_record/migration.rb:334:in `method_missing'
/data/software/bety/db/migrate/20150202220519_add_uniqueness_constraints.rb:7:in `up'
/opt/ruby-2.1.0/lib/ruby/gems/2.1.0/gems/activerecord-3.2.21/lib/active_record/migration.rb:370:in `up'
/opt/ruby-2.1.0/lib/ruby/gems/2.1.0/gems/activerecord-3.2.21/lib/active_record/migration.rb:410:in `block (2 levels) in migrate'
/opt/ruby-2.1.0/lib/ruby/2.1.0/benchmark.rb:279:in `measure'
/opt/ruby-2.1.0/lib/ruby/gems/2.1.0/gems/activerecord-3.2.21/lib/active_record/migration.rb:410:in `block in migrate'
/opt/ruby-2.1.0/lib/ruby/gems/2.1.0/gems/activerecord-3.2.21/lib/active_record/connection_adapters/abstract/connection_pool.rb:129:in `with_connection'
/opt/ruby-2.1.0/lib/ruby/gems/2.1.0/gems/activerecord-3.2.21/lib/active_record/migration.rb:389:in `migrate'
/opt/ruby-2.1.0/lib/ruby/gems/2.1.0/gems/activerecord-3.2.21/lib/active_record/migration.rb:528:in `migrate'
/opt/ruby-2.1.0/lib/ruby/gems/2.1.0/gems/activerecord-3.2.21/lib/active_record/migration.rb:720:in `block (2 levels) in migrate'
/opt/ruby-2.1.0/lib/ruby/gems/2.1.0/gems/activerecord-3.2.21/lib/active_record/migration.rb:775:in `call'
/opt/ruby-2.1.0/lib/ruby/gems/2.1.0/gems/activerecord-3.2.21/lib/active_record/migration.rb:775:in `block in ddl_transaction'
/opt/ruby-2.1.0/lib/ruby/gems/2.1.0/gems/activerecord-3.2.21/lib/active_record/connection_adapters/abstract/database_statements.rb:192:in `transaction'
/opt/ruby-2.1.0/lib/ruby/gems/2.1.0/gems/activerecord-3.2.21/lib/active_record/transactions.rb:208:in `transaction'
/opt/ruby-2.1.0/lib/ruby/gems/2.1.0/gems/activerecord-3.2.21/lib/active_record/migration.rb:775:in `ddl_transaction'
/opt/ruby-2.1.0/lib/ruby/gems/2.1.0/gems/activerecord-3.2.21/lib/active_record/migration.rb:719:in `block in migrate'
/opt/ruby-2.1.0/lib/ruby/gems/2.1.0/gems/activerecord-3.2.21/lib/active_record/migration.rb:700:in `each'
/opt/ruby-2.1.0/lib/ruby/gems/2.1.0/gems/activerecord-3.2.21/lib/active_record/migration.rb:700:in `migrate'
/opt/ruby-2.1.0/lib/ruby/gems/2.1.0/gems/activerecord-3.2.21/lib/active_record/migration.rb:570:in `up'
/opt/ruby-2.1.0/lib/ruby/gems/2.1.0/gems/activerecord-3.2.21/lib/active_record/migration.rb:551:in `migrate'
/opt/ruby-2.1.0/lib/ruby/gems/2.1.0/gems/activerecord-3.2.21/lib/active_record/railties/databases.rake:193:in `block (2 levels) in <top (required)>'
/opt/ruby-2.1.0/lib/ruby/gems/2.1.0/gems/rake-10.4.2/lib/rake/task.rb:240:in `call'
/opt/ruby-2.1.0/lib/ruby/gems/2.1.0/gems/rake-10.4.2/lib/rake/task.rb:240:in `block in execute'
/opt/ruby-2.1.0/lib/ruby/gems/2.1.0/gems/rake-10.4.2/lib/rake/task.rb:235:in `each'
/opt/ruby-2.1.0/lib/ruby/gems/2.1.0/gems/rake-10.4.2/lib/rake/task.rb:235:in `execute'
/opt/ruby-2.1.0/lib/ruby/gems/2.1.0/gems/rake-10.4.2/lib/rake/task.rb:179:in `block in invoke_with_call_chain'
/opt/ruby-2.1.0/lib/ruby/2.1.0/monitor.rb:211:in `mon_synchronize'
/opt/ruby-2.1.0/lib/ruby/gems/2.1.0/gems/rake-10.4.2/lib/rake/task.rb:172:in `invoke_with_call_chain'
/opt/ruby-2.1.0/lib/ruby/gems/2.1.0/gems/rake-10.4.2/lib/rake/task.rb:165:in `invoke'
/opt/ruby-2.1.0/lib/ruby/gems/2.1.0/gems/rake-10.4.2/lib/rake/application.rb:150:in `invoke_task'
/opt/ruby-2.1.0/lib/ruby/gems/2.1.0/gems/rake-10.4.2/lib/rake/application.rb:106:in `block (2 levels) in top_level'
/opt/ruby-2.1.0/lib/ruby/gems/2.1.0/gems/rake-10.4.2/lib/rake/application.rb:106:in `each'
/opt/ruby-2.1.0/lib/ruby/gems/2.1.0/gems/rake-10.4.2/lib/rake/application.rb:106:in `block in top_level'
/opt/ruby-2.1.0/lib/ruby/gems/2.1.0/gems/rake-10.4.2/lib/rake/application.rb:115:in `run_with_threads'
/opt/ruby-2.1.0/lib/ruby/gems/2.1.0/gems/rake-10.4.2/lib/rake/application.rb:100:in `top_level'
/opt/ruby-2.1.0/lib/ruby/gems/2.1.0/gems/rake-10.4.2/lib/rake/application.rb:78:in `block in run'
/opt/ruby-2.1.0/lib/ruby/gems/2.1.0/gems/rake-10.4.2/lib/rake/application.rb:176:in `standard_exception_handling'
/opt/ruby-2.1.0/lib/ruby/gems/2.1.0/gems/rake-10.4.2/lib/rake/application.rb:75:in `run'
/opt/ruby-2.1.0/lib/ruby/gems/2.1.0/gems/rake-10.4.2/bin/rake:33:in `<top (required)>'
/opt/ruby-2.1.0/bin/rake:23:in `load'
/opt/ruby-2.1.0/bin/rake:23:in `<main>'
Tasks: TOP => db:migrate

Are things like this OK?

NOTICE:  trigger "normalize_cultivar_names" for relation "cultivars" does not exist, skipping
rake aborted!
StandardError: An error has occurred, this and all later migrations canceled:

PG::CheckViolation: ERROR:  check constraint "file_path_sanity_check" is violated by some row
ActiveRecord::StatementInvalid: PG::CheckViolation: ERROR:  check constraint "file_path_sanity_check" is violated by some row
robkooper commented 9 years ago

Welcome to hell.

You will need to execute each sql statement in this migration and see which one fails. Then fix the database for those specific sql queries. I have done this a few times for Mike and takes about a day.

I would stay 2 versions behind right now and work with that. I think Mike is running 4.1, you can do a git checkout betydb_4.1.

gsrohde commented 9 years ago

Alternatively, as I mention in the release notes https://github.com/PecanProject/bety/releases/tag/betydb_4.2, you can run

bundle exec rake db:migrate 2>&1 | grep ERROR

to find the first error, fix it, and then repeat. Admittedly a bit tedious if there are many constraint violations.

My guess is that most violations will be in tables that PEcAn touches--those that don't get edited within the BETYdb Rail app.

(Ideally, that database sync would happen before the migration was applied since all of the violations in ebi_production have been fixed and so then your cleanup work would be limited to rows that you have in your own database that aren't in the global database. But I don't think this is the way the sync script works, probably because there are other considerations as well. The above-mentioned release notes also include a series of SQL commands that you can use to fix in your own db copy things that were only fixed recently in the production database.)

By the way, the error messages you get refer to rules in the migration file. For example "file_path_sanity_check" refers to the rule

ALTER TABLE dbfiles ADD CONSTRAINT file_path_sanity_check CHECK (file_path ~ '^/');

in migration 20150202220519_add_uniqueness_constraints.rb which simply specifies that dbfiles.file_path values must begin with a forward slash. This migration was part of release 4.1 released May 7, so backing up to that version won't help with this particular error.

gsrohde commented 9 years ago

One further comment:

If you want to take a "lazy" approach to fixing constraint violations but still want to upgrade to the latest release, you could use the following approach:

Iteratively: (1) attempt the migration; (2) instead of fixing the database immediately, comment out the statement in the migration file that defines the problem constraint.

(To comment out a single-line SQL statement, prefix the line with -- (double-hyphen). Surround multiple-line statements with /* */.)

After you fix the database, you can undo the alterations you made to the migration files and then redo those migrations.

serbinsh commented 9 years ago

@gsrohde I was able to get around this for now but wiping and reloading BETYdb. We didn't yet have any substantial results. However, hopefully I can avoid this in the future by keeping on top of updates.

mdietze commented 9 years ago

@gsrohde I tried running the first command you suggested (with grep ERROR) and I get the following message

PG::NotNullViolation: ERROR: column "year" contains null values ActiveRecord::StatementInvalid: PG::NotNullViolation: ERROR: column "year" contains null values PG::NotNullViolation: ERROR: column "year" contains null values

But it doesn't tell me which table the error is in. Am I going to have to hunt-and-peck through the whole database or it there a quicker way?

gsrohde commented 9 years ago

Yes, it’s a little annoying that the table isn’t mentioned on the same line as the ERROR message. I think you may see it if you see it if you don’t pipe the output into grep or maybe even if you use the grep –C context parameter.

Alternatively, you can at least find a list of tables that include the given column: I’ve put the full schema-spy documentation of BETYdb at https://www.betydb.org/db_docs/index.html. If you go to the columns tab and click on the “columns” heading, it will sort the rows alphabetically by column name. Scroll down to “year” and you’ll see that the citations table is the only base table that uses it (the other tables listed are views).

Scott

dlebauer commented 9 years ago

use the grep –C context parameter

ie

bundle exec rake db:migrate 2>&1 | grep -C 4 ERROR
robkooper commented 9 years ago

this is the citations table most likely

The only way i have been able to do this is paste 1 line of sql after the other and fix any issues that come up. hence I said welcome to hell and expect an hour or two to spend there.

gsrohde commented 9 years ago

@robkooper Do these problems mostly arise from data that is on private servers that isn't on ebi-forecast, or is it because the migration has to happen before synchronization?

robkooper commented 9 years ago

mostly because it is on other servers (not private). Since we never ended up setting up the database on EBI to be synced you never saw any of these issues. Hopefully once we have all databases at the same level I can set up EBI to get the data from BU and BNL.

dlebauer commented 9 years ago

@serbinsh can this be closed?

samsrabin commented 7 years ago

I'm also having problems updating betydb. I know it needs to happen because the schema on the VM seems to be different from that described here. (I'm also hoping that the irrigation issue, #486, will be fixed...)

When I do load.bety.sh, I get this error:

Checking schema                    : EXPECTED SCHEMA version 02faa8d3fa7a231a4929183882284809
Dump is from a different schema, please fix schema in database.

When I try to follow the instructions to "Update the Rails app and schema", I try this:

bundle install --without test

and after waiting for a while on "Fetching source index from http://rubygems.org", I get this error:

Bundler::HTTPError Could not fetch specs from http://rubygems.org/

Any ideas? Am I following the right steps?

gsrohde commented 7 years ago

@samsrabin You need only run bundle install if the Gem set (specified in Gemfile) has changed since you first installed BETYdb or since you last ran bundle install. And Gemfile hasn't changed since last July.

What you do need to do is update the database schema. The command to do this is

bundle exec rake db:migrate

or, if it is the database for the production environment (as specified in config/database.yml)

bundle exec rake db:migrate RAILS_ENV=production

This all assumes you've done a git pull to get any database migration files you might have been missing. If you just want to check migration status, replace db:migrate above with db:migrate:status. The db:migrate command may give you some spurious errors having to do with the schema documentation even when the migration is successful. You can use the db:migrate:status command to check that it was successful: you should see "up" next to each migration listed.

samsrabin commented 7 years ago

When I try either of the bundle exec rake db:migrate commands, I get the following error:

bundler: command not found: rake
Install missing gem executables with 'bundle install'
gsrohde commented 7 years ago

What does bundle check show?

samsrabin commented 7 years ago

bundle check gives:

git://github.com/rails/prototype_legacy_helper.git (at master@a2cd95c) is not yet checked out. Run `bundle install` first.
gsrohde commented 7 years ago

Try commenting out the prototype_legacy_helper Gem (with a #) in Gemfile (line 27, I think) and running it again.

samsrabin commented 7 years ago

Okay, bundle check now gives a bunch of missing gems, including rake (11.1.2).

sudo apt-get install rake gets stuck at 0% [Connecting to us.archive.ubuntu.com (91.189.91.26)].

robkooper commented 7 years ago

are you running the bundle exec as root or as teh carya user? It should be executed as the carya user.

samsrabin commented 7 years ago

I'm running as carya.

samsrabin commented 7 years ago

Here's the full output of bundle check:

Resolving dependencies...
The following gems are missing
 * rake (11.1.2)
 * i18n (0.7.0)
 * multi_json (1.3.6)
 * activesupport (3.2.22.2)
 * builder (3.0.4)
 * activemodel (3.2.22.2)
 * erubis (2.7.0)
 * journey (1.0.4)
 * rack (1.4.7)
 * rack-cache (1.6.1)
 * rack-test (0.6.3)
 * hike (1.2.3)
 * tilt (1.4.1)
 * sprockets (2.2.3)
 * actionpack (3.2.22.2)
 * mime-types (1.25.1)
 * polyglot (0.3.5)
 * treetop (1.4.15)
 * mail (2.5.4)
 * actionmailer (3.2.22.2)
 * arel (3.0.3)
 * tzinfo (0.3.35)
 * activerecord (3.2.22.2)
 * rgeo (0.5.3)
 * rgeo-activerecord (0.5.0)
 * activerecord-postgis-adapter (0.6.6)
 * activeresource (3.2.22.2)
 * addressable (2.3.8)
 * json (1.8.3)
 * apipie-rails (0.3.6)
 * will_paginate (3.0.4)
 * bootstrap-will_paginate (0.0.10)
 * mini_portile2 (2.0.0)
 * nokogiri (1.6.7.2)
 * xpath (2.0.0)
 * capybara (2.5.0)
 * capybara-webkit (1.7.1)
 * ffi (1.9.8)
 * childprocess (0.5.6)
 * choice (0.1.6)
 * coderay (1.1.0)
 * comma (3.0.4)
 * daemon_controller (1.1.7)
 * diff-lcs (1.2.5)
 * dynamic_form (1.1.4)
 * json-schema (2.6.1)
 * memoist (0.11.0)
 * method_source (0.8.2)
 * mustache (0.99.8)
 * narray (0.6.0.4)
 * passenger (4.0.24)
 * pg (0.17.0)
 * power_assert (0.2.2)
 * rack-ssl (1.3.4)
 * rdoc (3.12.2)
 * thor (0.19.1)
 * railties (3.2.22.2)
 * rails (3.2.22.2)
 * prototype-rails (3.2.1)
 * slop (3.6.0)
 * pry (0.10.1)
 * query_reviewer (0.1.6)
 * rabl (0.11.8)
 * railroad (0.5.0)
 * rails3-restful-authentication (3.0.1)
 * recaptcha (0.3.4)
 * rspec-support (3.4.1)
 * rspec-core (3.4.4)
 * rspec-expectations (3.4.0)
 * rspec-mocks (3.4.1)
 * rspec (3.4.0)
 * rspec-rails (3.4.2)
 * rspec_api_documentation (4.7.0)
 * ruby-graphviz (1.0.8)
 * rubyzip (1.1.7)
 * safe_attributes (1.0.10)
 * seer (0.10.0)
 * websocket (1.2.2)
 * selenium-webdriver (2.45.0)
 * test-unit (3.0.8)
 * trollop (2.1.2)
 * yajl-ruby (1.2.1)
Install missing gems with `bundle install`
gsrohde commented 7 years ago

If your Rails app is still functioning (or even if it ever did function) something else is going on. You shouldn't be missing all the Gems.

Are you running this in the Rails root directory? Are you using rvm (Ruby Version Manager)?

samsrabin commented 7 years ago

I don't know about rvm. I'm just using whatever comes with the VM...

gsrohde commented 7 years ago

@robkooper Can you help? You know much more about the VM setup than I do.

robkooper commented 7 years ago

This is what I use to install BETY: https://github.com/PecanProject/pecan/blob/master/scripts/install_pecan.sh#L408

samsrabin commented 7 years ago

Okay, I reinstalled the VM and the weirdness with all the Gems is fixed. Now I get the following error when running bundle exec rake db:migrate --trace:

carya@pecan:~/bety$ bundle exec rake db:migrate --trace
** Invoke db:migrate (first_time)
** Invoke environment (first_time)
** Execute environment
rake aborted!
ActiveRecord::AdapterNotSpecified: database configuration does not specify adapter
/home/carya/bety/vendor/bundle/ruby/2.1.0/gems/activerecord-3.2.22.2/lib/active_record/connection_adapters/abstract/connection_specification.rb:47:in `resolve_hash_connection'
/home/carya/bety/vendor/bundle/ruby/2.1.0/gems/activerecord-3.2.22.2/lib/active_record/connection_adapters/abstract/connection_specification.rb:41:in `resolve_string_connection'
/home/carya/bety/vendor/bundle/ruby/2.1.0/gems/activerecord-3.2.22.2/lib/active_record/connection_adapters/abstract/connection_specification.rb:25:in `spec'
/home/carya/bety/vendor/bundle/ruby/2.1.0/gems/activerecord-3.2.22.2/lib/active_record/connection_adapters/abstract/connection_specification.rb:130:in `establish_connection'
/home/carya/bety/vendor/bundle/ruby/2.1.0/gems/activerecord-3.2.22.2/lib/active_record/railtie.rb:88:in `block (2 levels) in <class:Railtie>'
/home/carya/bety/vendor/bundle/ruby/2.1.0/gems/activesupport-3.2.22.2/lib/active_support/lazy_load_hooks.rb:36:in `instance_eval'
/home/carya/bety/vendor/bundle/ruby/2.1.0/gems/activesupport-3.2.22.2/lib/active_support/lazy_load_hooks.rb:36:in `execute_hook'
/home/carya/bety/vendor/bundle/ruby/2.1.0/gems/activesupport-3.2.22.2/lib/active_support/lazy_load_hooks.rb:26:in `block in on_load'
/home/carya/bety/vendor/bundle/ruby/2.1.0/gems/activesupport-3.2.22.2/lib/active_support/lazy_load_hooks.rb:25:in `each'
/home/carya/bety/vendor/bundle/ruby/2.1.0/gems/activesupport-3.2.22.2/lib/active_support/lazy_load_hooks.rb:25:in `on_load'
/home/carya/bety/vendor/bundle/ruby/2.1.0/gems/activerecord-3.2.22.2/lib/active_record/railtie.rb:80:in `block in <class:Railtie>'
/home/carya/bety/vendor/bundle/ruby/2.1.0/gems/railties-3.2.22.2/lib/rails/initializable.rb:30:in `instance_exec'
/home/carya/bety/vendor/bundle/ruby/2.1.0/gems/railties-3.2.22.2/lib/rails/initializable.rb:30:in `run'
/home/carya/bety/vendor/bundle/ruby/2.1.0/gems/railties-3.2.22.2/lib/rails/initializable.rb:55:in `block in run_initializers'
/home/carya/bety/vendor/bundle/ruby/2.1.0/gems/railties-3.2.22.2/lib/rails/initializable.rb:54:in `each'
/home/carya/bety/vendor/bundle/ruby/2.1.0/gems/railties-3.2.22.2/lib/rails/initializable.rb:54:in `run_initializers'
/home/carya/bety/vendor/bundle/ruby/2.1.0/gems/railties-3.2.22.2/lib/rails/application.rb:136:in `initialize!'
/home/carya/bety/vendor/bundle/ruby/2.1.0/gems/railties-3.2.22.2/lib/rails/railtie/configurable.rb:30:in `method_missing'
/home/carya/bety/config/environment.rb:5:in `<top (required)>'
/home/carya/bety/vendor/bundle/ruby/2.1.0/gems/activesupport-3.2.22.2/lib/active_support/dependencies.rb:251:in `require'
/home/carya/bety/vendor/bundle/ruby/2.1.0/gems/activesupport-3.2.22.2/lib/active_support/dependencies.rb:251:in `block in require'
/home/carya/bety/vendor/bundle/ruby/2.1.0/gems/activesupport-3.2.22.2/lib/active_support/dependencies.rb:236:in `load_dependency'
/home/carya/bety/vendor/bundle/ruby/2.1.0/gems/activesupport-3.2.22.2/lib/active_support/dependencies.rb:251:in `require'
/home/carya/bety/vendor/bundle/ruby/2.1.0/gems/railties-3.2.22.2/lib/rails/application.rb:103:in `require_environment!'
/home/carya/bety/vendor/bundle/ruby/2.1.0/gems/railties-3.2.22.2/lib/rails/application.rb:305:in `block (2 levels) in initialize_tasks'
/home/carya/bety/vendor/bundle/ruby/2.1.0/gems/rake-11.1.2/lib/rake/task.rb:248:in `call'
/home/carya/bety/vendor/bundle/ruby/2.1.0/gems/rake-11.1.2/lib/rake/task.rb:248:in `block in execute'
/home/carya/bety/vendor/bundle/ruby/2.1.0/gems/rake-11.1.2/lib/rake/task.rb:243:in `each'
/home/carya/bety/vendor/bundle/ruby/2.1.0/gems/rake-11.1.2/lib/rake/task.rb:243:in `execute'
/home/carya/bety/vendor/bundle/ruby/2.1.0/gems/rake-11.1.2/lib/rake/task.rb:187:in `block in invoke_with_call_chain'
/usr/lib/ruby/2.1.0/monitor.rb:211:in `mon_synchronize'
/home/carya/bety/vendor/bundle/ruby/2.1.0/gems/rake-11.1.2/lib/rake/task.rb:180:in `invoke_with_call_chain'
/home/carya/bety/vendor/bundle/ruby/2.1.0/gems/rake-11.1.2/lib/rake/task.rb:209:in `block in invoke_prerequisites'
/home/carya/bety/vendor/bundle/ruby/2.1.0/gems/rake-11.1.2/lib/rake/task.rb:207:in `each'
/home/carya/bety/vendor/bundle/ruby/2.1.0/gems/rake-11.1.2/lib/rake/task.rb:207:in `invoke_prerequisites'
/home/carya/bety/vendor/bundle/ruby/2.1.0/gems/rake-11.1.2/lib/rake/task.rb:186:in `block in invoke_with_call_chain'
/usr/lib/ruby/2.1.0/monitor.rb:211:in `mon_synchronize'
/home/carya/bety/vendor/bundle/ruby/2.1.0/gems/rake-11.1.2/lib/rake/task.rb:180:in `invoke_with_call_chain'
/home/carya/bety/vendor/bundle/ruby/2.1.0/gems/rake-11.1.2/lib/rake/task.rb:173:in `invoke'
/home/carya/bety/vendor/bundle/ruby/2.1.0/gems/rake-11.1.2/lib/rake/application.rb:150:in `invoke_task'
/home/carya/bety/vendor/bundle/ruby/2.1.0/gems/rake-11.1.2/lib/rake/application.rb:106:in `block (2 levels) in top_level'
/home/carya/bety/vendor/bundle/ruby/2.1.0/gems/rake-11.1.2/lib/rake/application.rb:106:in `each'
/home/carya/bety/vendor/bundle/ruby/2.1.0/gems/rake-11.1.2/lib/rake/application.rb:106:in `block in top_level'
/home/carya/bety/vendor/bundle/ruby/2.1.0/gems/rake-11.1.2/lib/rake/application.rb:115:in `run_with_threads'
/home/carya/bety/vendor/bundle/ruby/2.1.0/gems/rake-11.1.2/lib/rake/application.rb:100:in `top_level'
/home/carya/bety/vendor/bundle/ruby/2.1.0/gems/rake-11.1.2/lib/rake/application.rb:78:in `block in run'
/home/carya/bety/vendor/bundle/ruby/2.1.0/gems/rake-11.1.2/lib/rake/application.rb:176:in `standard_exception_handling'
/home/carya/bety/vendor/bundle/ruby/2.1.0/gems/rake-11.1.2/lib/rake/application.rb:75:in `run'
/home/carya/bety/vendor/bundle/ruby/2.1.0/gems/rake-11.1.2/bin/rake:33:in `<top (required)>'
/home/carya/bety/vendor/bundle/ruby/2.1.0/bin/rake:23:in `load'
/home/carya/bety/vendor/bundle/ruby/2.1.0/bin/rake:23:in `<main>'
Tasks: TOP => db:migrate => environment
samsrabin commented 7 years ago

@robkooper When I try to do bundle install --without development:test:javascript_testing:debug --path vendor/bundle on the VM, I have the same thing where it gets stuck on Fetching source index from http://rubygems.org/ and then says Bundler::HTTPError Could not fetch specs from http://rubygems.org/.

robkooper commented 7 years ago

Can you ping the outside world from inside the VM, for example does ping 8.8.8.8 work? I have had problems where if I started the VM at work and went home it could not connect. A shutdown and start would clear this situation for me.

As for the trace, try adding RAILS_ENV=production at the end. In the config.yaml there is no development environment defined, which is what I think rake defaults to.

samsrabin commented 7 years ago

@robkooper ping does work fine. Here's what happens when I add RAILS_ENV=production as you suggested:

** Invoke db:migrate (first_time)
** Invoke environment (first_time)
** Execute environment
** Invoke db:load_config (first_time)
** Execute db:load_config
** Execute db:migrate
** Invoke db:_dump (first_time)
** Execute db:_dump
** Invoke db:structure:dump (first_time)
** Invoke environment 
** Invoke db:load_config 
** Execute db:structure:dump
/usr/lib/postgresql/9.5/bin/pg_dump: invalid option -- 'i'
Try "pg_dump --help" for more information.
rake aborted!
Error dumping database
/home/carya/bety2/vendor/bundle/ruby/2.1.0/gems/activerecord-postgis-adapter-0.6.6/lib/active_record/connection_adapters/postgis_adapter/rails3/databases.rake:208:in `block in <top (required)>'
/home/carya/bety2/vendor/bundle/ruby/2.1.0/gems/rgeo-activerecord-0.5.0/lib/rgeo/active_record/task_hacker.rb:61:in `call'
/home/carya/bety2/vendor/bundle/ruby/2.1.0/gems/rgeo-activerecord-0.5.0/lib/rgeo/active_record/task_hacker.rb:61:in `call'
/home/carya/bety2/vendor/bundle/ruby/2.1.0/gems/rake-11.1.2/lib/rake/task.rb:246:in `block in execute'
/home/carya/bety2/vendor/bundle/ruby/2.1.0/gems/rake-11.1.2/lib/rake/task.rb:243:in `each'
/home/carya/bety2/vendor/bundle/ruby/2.1.0/gems/rake-11.1.2/lib/rake/task.rb:243:in `execute'
/home/carya/bety2/vendor/bundle/ruby/2.1.0/gems/rake-11.1.2/lib/rake/task.rb:187:in `block in invoke_with_call_chain'
/usr/lib/ruby/2.1.0/monitor.rb:211:in `mon_synchronize'
/home/carya/bety2/vendor/bundle/ruby/2.1.0/gems/rake-11.1.2/lib/rake/task.rb:180:in `invoke_with_call_chain'
/home/carya/bety2/vendor/bundle/ruby/2.1.0/gems/rake-11.1.2/lib/rake/task.rb:173:in `invoke'
/home/carya/bety2/vendor/bundle/ruby/2.1.0/gems/activerecord-3.2.22.2/lib/active_record/railties/databases.rake:202:in `block (2 levels) in <top (required)>'
/home/carya/bety2/vendor/bundle/ruby/2.1.0/gems/rake-11.1.2/lib/rake/task.rb:248:in `call'
/home/carya/bety2/vendor/bundle/ruby/2.1.0/gems/rake-11.1.2/lib/rake/task.rb:248:in `block in execute'
/home/carya/bety2/vendor/bundle/ruby/2.1.0/gems/rake-11.1.2/lib/rake/task.rb:243:in `each'
/home/carya/bety2/vendor/bundle/ruby/2.1.0/gems/rake-11.1.2/lib/rake/task.rb:243:in `execute'
/home/carya/bety2/vendor/bundle/ruby/2.1.0/gems/rake-11.1.2/lib/rake/task.rb:187:in `block in invoke_with_call_chain'
/usr/lib/ruby/2.1.0/monitor.rb:211:in `mon_synchronize'
/home/carya/bety2/vendor/bundle/ruby/2.1.0/gems/rake-11.1.2/lib/rake/task.rb:180:in `invoke_with_call_chain'
/home/carya/bety2/vendor/bundle/ruby/2.1.0/gems/rake-11.1.2/lib/rake/task.rb:173:in `invoke'
/home/carya/bety2/vendor/bundle/ruby/2.1.0/gems/activerecord-3.2.22.2/lib/active_record/railties/databases.rake:196:in `block (2 levels) in <top (required)>'
/home/carya/bety2/vendor/bundle/ruby/2.1.0/gems/rake-11.1.2/lib/rake/task.rb:248:in `call'
/home/carya/bety2/vendor/bundle/ruby/2.1.0/gems/rake-11.1.2/lib/rake/task.rb:248:in `block in execute'
/home/carya/bety2/vendor/bundle/ruby/2.1.0/gems/rake-11.1.2/lib/rake/task.rb:243:in `each'
/home/carya/bety2/vendor/bundle/ruby/2.1.0/gems/rake-11.1.2/lib/rake/task.rb:243:in `execute'
/home/carya/bety2/vendor/bundle/ruby/2.1.0/gems/rake-11.1.2/lib/rake/task.rb:187:in `block in invoke_with_call_chain'
/usr/lib/ruby/2.1.0/monitor.rb:211:in `mon_synchronize'
/home/carya/bety2/vendor/bundle/ruby/2.1.0/gems/rake-11.1.2/lib/rake/task.rb:180:in `invoke_with_call_chain'
/home/carya/bety2/vendor/bundle/ruby/2.1.0/gems/rake-11.1.2/lib/rake/task.rb:173:in `invoke'
/home/carya/bety2/vendor/bundle/ruby/2.1.0/gems/rake-11.1.2/lib/rake/application.rb:150:in `invoke_task'
/home/carya/bety2/vendor/bundle/ruby/2.1.0/gems/rake-11.1.2/lib/rake/application.rb:106:in `block (2 levels) in top_level'
/home/carya/bety2/vendor/bundle/ruby/2.1.0/gems/rake-11.1.2/lib/rake/application.rb:106:in `each'
/home/carya/bety2/vendor/bundle/ruby/2.1.0/gems/rake-11.1.2/lib/rake/application.rb:106:in `block in top_level'
/home/carya/bety2/vendor/bundle/ruby/2.1.0/gems/rake-11.1.2/lib/rake/application.rb:115:in `run_with_threads'
/home/carya/bety2/vendor/bundle/ruby/2.1.0/gems/rake-11.1.2/lib/rake/application.rb:100:in `top_level'
/home/carya/bety2/vendor/bundle/ruby/2.1.0/gems/rake-11.1.2/lib/rake/application.rb:78:in `block in run'
/home/carya/bety2/vendor/bundle/ruby/2.1.0/gems/rake-11.1.2/lib/rake/application.rb:176:in `standard_exception_handling'
/home/carya/bety2/vendor/bundle/ruby/2.1.0/gems/rake-11.1.2/lib/rake/application.rb:75:in `run'
/home/carya/bety2/vendor/bundle/ruby/2.1.0/gems/rake-11.1.2/bin/rake:33:in `<top (required)>'
/home/carya/bety2/vendor/bundle/ruby/2.1.0/bin/rake:23:in `load'
/home/carya/bety2/vendor/bundle/ruby/2.1.0/bin/rake:23:in `<main>'
Tasks: TOP => db:structure:dump
robkooper commented 7 years ago

You can ignore that last error. That is an issue between a newer version of postgresql not supporting the -i flag and the gem. It should not impact the migration.

samsrabin commented 7 years ago

@robkooper Okay, so then I guess I have successfully updated the database schema. Is there anything else I need to do to get my local copy of the database up-to-date? I'm totally okay erasing what already exists, since I have not added any data of my own.

robkooper commented 7 years ago

All you need to do now is run the update script. If you have not added any data, and want to share your data with others, please request an official id first. If you don't mind open a new issue and just reference this issue.

Once you have this new ID you will need to reset your database (of the top of my head):

load.bety.sh -c -m <newid> -r 0
for x in 0 1; do
  for y in 0 1 2 4 5; do
    load.bety.sh -m <newid> -r $y
  done
done
gsrohde commented 7 years ago

In case anyone is trying to load data from the terra-ref site (machine 6), this issue is relevant: https://github.com/PecanProject/pecan/issues/1355 Please let me know if this has been an issue for you so that I know how to prioritize this.