Using different users for migrations -- sqlite3 adapter and ddl errors

[Rafael Fernández López]

Hi there,

Disclaimer: at the beginning it looks like this message should be on
rubyonrails-talk, but please, keep reading.

I have a Rails 3 project in which I'd like to migrate my databases with
a different database user than the one used for querying, inserting and
deleting records.

I have defined in my config/database.yml my new connections. The
situation is: test, development, test_migrations, development_migrations
are sqlite3 databases. production and production_migrations are mysql
databases.

Now, in my migrations I have the following:

def connection

ActiveRecord::Base.establish_connection("migrations_#{Rails.env}").connection
end

When running "rake db:migrate" I get using the sqlite3 adapter (test and
development) the following error:

ArgumentError: prepare called on a closed database: rollback transaction

with the following rake trace:

http://pastebin.com/2NvqzEtP

Is important to see that what actually is reporting is that it tries to
do a rollback and that is failing, but the rollback comes because a
previous operation failed.

As far as I've seen it looks like the problem is related with DDL
transactions. If I modify
activerecord-3.2.2/lib/active_record/migration.rb ddl_transaction method
with the next method:

def ddl_transaction(&block)
block.call
end

instead of

def ddl_transaction(&block)
if Base.connection.supports_ddl_transactions?
Base.transaction { block.call }
else
block.call
end
end

it seems to work fine. So the whole problem seems to be related with DDL
transactions. Any ideas ? I am willing to help if it's needed.

Best regards,
Rafael Fern�ndez L�pez.

[Ken Collins]

I would wager that your issue is with how you are patching migrations to work and the code you implemented vs a rails core issue. Perhaps the #connection method below is not enough? A few things that come to mind:

I thought the latest ActiveRecord now solicits all connections for transactions? Maybe somehow you are creating an orphan pool after rails boots and connects, which is right before you shim in a new connection? If so, solving that would involve a deep code dive into a potential area that is not advertised as configurable.

Have you considered that your workflow may be better served by over riding the db:migrate task in your own databases.rake and setting either some ENV variables or hooks to get what you need? Rake allows you to stack tasks, your would be last in, first run. You could even make your database.yml use ERB and use said ENV hooks to just make the simple "development" or "test" connection name go to the migration DBs. If indeed your issue is related to your implementation, the exploring other options could help.

- Ken

[Rafael Fernández López]

> I thought the latest ActiveRecord now solicits all connections for transactions? Maybe somehow you are creating an orphan pool after rails boots and connects, which is right before you shim in a new connection? If so, solving that would involve a deep code dive into a potential area that is not advertised as configurable.
>

AFAIK and from the current experience ActiveRecord isn't opening all connections. It does only on the current environment one.

> Have you considered that your workflow may be better served by over riding the db:migrate task in your own databases.rake and setting either some ENV variables or hooks to get what you need? Rake allows you to stack tasks, your would be last in, first run. You could even make your database.yml use ERB and use said ENV hooks to just make the simple "development" or "test" connection name go to the migration DBs. If indeed your issue is related to your implementation, the exploring other options could help.

In Rails3 connection() method in migrations was added in order to be able to run migrations in other databases. This also includes the case that I am working on: I prefer to have my database user for production that can only modify data, and have another user with all privileges that is only used on migrations. This is a key security concept. This way it is impossible to use a hypothetical sql injection for modifying database schema.

I prefer to do The Right Thing™ and not to patch it by overriding my db:migrate task. It would be a solution, but I would like to find the root cause of this problem.

I am going to dig a bit more on the code, and see if I find something interesting.

Thanks for your reasoning Ken.

Best regards,
Rafael Fernández López.

[Rafael Fernández López]

Hello all,

Ok, so here is the report. Studying the source code I got to the interesting part:

connection_specification.rb (in activerecord/connection_adapters/abstract)

def self.establish_connection(spec = ENV["DATABASE_URL"])
…
remove_connection
connection_handler.establish_connection name, spec
end

And here is where all the work comes… I tracked all this process, and the "problem" resides in that sqlite3 gem as well as the sqlite_adapter.rb (in active_record/connection_adapters) does not implement the "connect" method.

The sqlite gem only performs a sqlite3_open call in the initialize method. There is a close method, but there isn't an open method. So the dummy one is called that does nothing, and you end with only the new closed connection. And a nice point to remark is also that this only happens when we have DDL transactions. With DDL transactions disabled (as in my previous email) the connection is recreated instead of reopened, and for this reason this works out of the box.

Given that this is important for me, I am willing to implement those in the sqlite3 gem, as well as in the sqlite_adapter.rb. This way the connection can be opened.

Changes are not complex, and they'd give us the same behavior as if it were the mysql adapter.

What do you think ?

[Xavier Noria]

Let me ask you a question before getting into Active Record internals.

Would it work for you to have pairs of entries

    development

    development_migrations

in config/database.yml equal except for user credentials? Then pass the corresponding key in RAILS_ENV in the shell command for migrations?

[Aaron Patterson]

On Fri, Apr 20, 2012 at 06:11:02PM +0200, Rafael Fernández López wrote:
> Hello all,
>
> Ok, so here is the report. Studying the source code I got to the interesting part:
>
> connection_specification.rb (in activerecord/connection_adapters/abstract)
>
> def self.establish_connection(spec = ENV["DATABASE_URL"])
> …
> remove_connection
> connection_handler.establish_connection name, spec
> end
>
> And here is where all the work comes… I tracked all this process, and the "problem" resides in that sqlite3 gem as well as the sqlite_adapter.rb (in active_record/connection_adapters) does not implement the "connect" method.
>
> The sqlite gem only performs a sqlite3_open call in the initialize method. There is a close method, but there isn't an open method. So the dummy one is called that does nothing, and you end with only the new closed connection. And a nice point to remark is also that this only happens when we have DDL transactions. With DDL transactions disabled (as in my previous email) the connection is recreated instead of reopened, and for this reason this works out of the box.

I don't understand this at all. Simply instantiating a SQLite database
object will give you an open connection:

db = SQLite3::Database.new ':memory:' # => it's open now


--
Aaron Patterson
http://tenderlovemaking.com/

[Rafael Fernández López]

Yes, Aaron. I stated that before. And when you instantiate this object is in the only place where connection is opened. There is no open method to use after a close.

To make it clear: with MySQL adapter this works, it only happens with the SQLite adapter.

Let me create a dummy example and you'll get the problem instantly, but to recap:

- I want to separate the connection used to run migrations and the connection used for the runtime.
- I override connection method on my migrations and inside I call establish_connection.
- I am using the sqlite3 gem and adapter locally.

I am going to dig even more, but my guess is that the sqlite3 adapter instance is reused by the pool, getting to a 'closed database problem' after calling to establish_connection.


Best regards,
Rafael Fernández López

[Rafael Fernández López]

Hello,

Here is the sample project: https://github.com/ereslibre/migrationtest

It only contains two commits, the initial commit of no interest and another one that adds the behavior that makes the problem pop.

To trigger the problem, just run "rake db:migrate".

[Rafael Fernández López]

Hmm… yes, that could work. However as far as I understand it would be great to fix the problem itself, if it is considered to be any.

I have sent couple minutes ago another mail with the repo details. If you guys consider that it needs be fixed I'd be more than happy to do it.