Active Record 5.1 Failing to insert version number about activerecord-cockroachdb-adapter HOT 12 OPEN

Recreating the schema_migrations table with INT works:

drop table schema_migrations;

create table schema_migrations(versions INT PRIMARY KEY);

rails db:schema:load

Success

from activerecord-cockroachdb-adapter.

Additionally, I'm unable to create a model in rails: generate_subscripts is used somewhere, my guess is using it to inspect the attributes of the table at runtime so it can insert the record:

FactoryGirl.create(:user)

ActiveRecord::StatementInvalid: PG::UndefinedFunction: ERROR:  unknown function: generate_subscripts()
: SELECT a.attname
  FROM (
         SELECT indrelid, indkey, generate_subscripts(indkey, 1) idx
           FROM pg_index
          WHERE indrelid = '"users"'::regclass
            AND indisprimary
       ) i
  JOIN pg_attribute a
    ON a.attrelid = i.indrelid
   AND a.attnum = i.indkey[i.idx]
 ORDER BY i.idx

        from (irb):4

Edit: Yes, it's trying to inspect the primary keys, see https://github.com/rails/rails/blob/471bec310d1ecc50311f18b545df1d789d6bac0d/activerecord/lib/active_record/connection_adapters/postgresql/schema_statements.rb#L360

from activerecord-cockroachdb-adapter.

@jordanlewis plz file the following separate issues in the lang project:

• insert int into string column (note: we may want to address this by pattern-matching the query instead of weird typing rule)
• implement generate_subscripts

from activerecord-cockroachdb-adapter.

@knz this PR has the solution, can @jordanlewis review it? https://github.com/cockroachdb/activerecord-cockroachdb-adapter/pull/20/files#diff-a6efa568aab40b05159b3148c29a54f9R200

from activerecord-cockroachdb-adapter.

The PR #20 didn't solve the initial issue for me. I could not run any rails tests or anything that required schema loading. Normal migrations using db:migrate are working fine.

Im using:

• cockroachdb v2.0.1
• cockroach adapter 0.2.3 (the readme still points to 0.2.2)
• rails 5.2.0

Looks like, this line is the error source: https://github.com/rails/rails/blob/73d1f5f310e4dc3acb1ea502143d679f99931840/activerecord/lib/active_record/connection_adapters/abstract/schema_statements.rb#L1057

The schema version like ActiveRecord::Schema.define(version: 2018_05_04_205847) do is always cast to an int. When executing the insert statement, the int value is not inside single quotes. Postgres allows automatic int to varchar conversions that cockroach seem to prevent: sqlfiddle

If i remove the version block from the schema.rb file, the error disappears.

I resolved the issue by monkeypatching the cockroachdb_adapter and 'fix' the assume_migrated_upto_version method as proof of concept:

module CockroachDb
    module SchemaFix
      def assume_migrated_upto_version(version, migrations_paths)
        migrations_paths = Array(migrations_paths)
        version = version.to_i
        sm_table = quote_table_name(ActiveRecord::SchemaMigration.table_name)

        migrated = ActiveRecord::SchemaMigration.all_versions.map(&:to_i)
        versions = migration_context.migration_files.map do |file|
          migration_context.parse_migration_filename(file).first.to_i
        end

        unless migrated.include?(version)
          execute "INSERT INTO #{sm_table} (version) VALUES ('#{quote(version)}')"
        end

        inserting = (versions - migrated).select { |v| v < version }
        if inserting.any?
          if (duplicate = inserting.detect { |v| inserting.count(v) > 1 })
            raise "Duplicate migration #{duplicate}. Please renumber your migrations to resolve the conflict."
          end
          if supports_multi_insert?
            execute insert_versions_sql(inserting)
          else
            inserting.each do |v|
              execute insert_versions_sql(v)
            end
          end
        end
      end
    end
end

require 'active_record/connection_adapters/cockroachdb_adapter'

ActiveRecord::ConnectionAdapters::CockroachDBAdapter.include(CockroachDb::SchemaFix)

Note the additional quotes around #{quote(version)} in execute "INSERT INTO #{sm_table} (version) VALUES ('#{quote(version)}')"

Im currently learning rails, so i don't know if this may break other thinks. By github search, this method seams only relevant to schema loading, so i hope its good. 🤔

I've seen that this adapter already has its own schema_statements.rb, so maybe we could add this method override to it?

from activerecord-cockroachdb-adapter.

Thanks for the hint!

from activerecord-cockroachdb-adapter.

I tried moving the monkeypatch to the cockroach/SchemaStatements.

Diff here

If this is an acceptable solution i could create a pull request i you want.

from activerecord-cockroachdb-adapter.

oh yes that would be nice

from activerecord-cockroachdb-adapter.

@jordanlewis Can we get a release with this fix?

from activerecord-cockroachdb-adapter.

@jordanlewis @knz any updates on this? What is blocking the next release? :)

from activerecord-cockroachdb-adapter.

@eric-christian thanks for the note. I think the PR #27 was merged successfully. @jordanlewis what does it take to make a "release"?

from activerecord-cockroachdb-adapter.

I think somebody needs to build and push the gem, and maybe update its version number. I can try to look at it. Sorry to everyone who's been waiting on this - we don't really have the bandwidth to maintain this gem.

from activerecord-cockroachdb-adapter.