Rails attempts to create ar_internal_metadata on read replica

[squidarth]

Hi there,

We've been using Octopus successfully to support a master database and read replicas, using Postgres. After upgrading to Rails 5, we ran into this error during rake db:migrate:

ActiveRecord::StatementInvalid: PG::ReadOnlySqlTransaction: ERROR:  cannot execute CREATE TABLE in a read-only transaction
: CREATE TABLE "ar_internal_metadata" 

Rails 5 introduces a new activerecord table called ar_internal_metadata, and creates this while instantiating the migrator. While normal migrations have logic that determine which Octopus shards those migrations should run on, Octopus simply ran the logic to create this table on all shards. The logic to do this is in

# https://github.com/thiagopradi/octopus/blob/master/lib/octopus/migration.rb#L126
connection.send_queries_to_multiple_shards(connection.shard_names) do
     up_without_octopus(migrations_paths, target_version, &block)
end

The initialize method for up creates the new table:

    # https://github.com/rails/rails/blob/master/activerecord/lib/active_record/migration.rb#L1129
    def initialize(direction, migrations, target_version = nil)
      @direction         = direction
      @target_version    = target_version
      @migrated_versions = nil
      @migrations        = migrations

      validate(@migrations)

      ActiveRecord::SchemaMigration.create_table
      ActiveRecord::InternalMetadata.create_table
    end

and this runs on each shard, which will cause an error on read replica databases.

We solved this problem by manually creating the table in psql, and then deploying the upgrade to rails 5.

It would be helpful if Octopus included logic to handle this particular case. The issue here is that since there's no migration file, it's difficult to know what shards the user would like this table to be created on.

I propose the following solution

Similarly to how migrations work, monkey-patch the initialize method on Migrator to check if the connection is either the master shard or in the Octopus.config[:default_migration_group], and only create the table in these cases.

If the user would like the ar_internal_metadata table to be created on other databases at this point, they'll have to do it themselves, but this probably covers a lot of cases.

Let me know what you think, and thanks for the work on this awesome gem!

[squidarth]

I think this is related to https://github.com/thiagopradi/octopus/issues/363

[afomera]

Also chiming in because we just ran into this issue at my work.

I was able to get it working by doing just as @squidarth said and created the table myself prior to deployment.

Here's what I ran for my postgresql database in three separate sql statements

CREATE TABLE ar_internal_metadata (
    key character varying NOT NULL,
    value character varying,
    created_at timestamp without time zone NOT NULL,
    updated_at timestamp without time zone NOT NULL
);

ALTER TABLE ONLY ar_internal_metadata
    ADD CONSTRAINT ar_internal_metadata_pkey PRIMARY KEY (key);

INSERT INTO ar_internal_metadata(key, value, created_at, updated_at) values('environment', 'production', NOW(), NOW());```

[dondeng]

My dirty hack to this was turning off replication when doing our initial Rails 5.1 deployment. This allowed the ar_internal_metadata table to be created, then turned replication back on. Not elegant but it did the job