Query Execution is broken in do_mysql 0.10.5

postmodern commented 13 years ago

I cannot seem to be able to perform auto_migrations or selections using do_mysql 0.10.5.

Asn.auto_migrate!
 ~   SQL (0.253ms)  DROP TABLE IF EXISTS `asns`
 ~   SQL (0.008ms)  SHOW TABLES LIKE 'asns'
 ~ near "SHOW": syntax error (code: 1, sql state: , query: SHOW TABLES LIKE 'asns'

User.first
 ~   SQL (0.171ms)  SELECT `id`, /*REDACTED*/ FROM `users` ORDER BY `id` LIMIT 1
 ~ no such table: users (code: 1, sql state: , query: SELECT `id`, /*REDACTED*/ FROM `users` ORDER BY `id` LIMIT 1,

Downgrading back to data_objects 0.10.3 and do_mysql 0.10.3 fixed the problem.

postmodern commented 13 years ago

Backtrace:

    from /home/hal/.rvm/gems/ruby-1.9.2-p180/gems/dm-do-adapter-1.1.0/lib/dm-do-adapter/adapter.rb:63:in `execute_non_query'
    from /home/hal/.rvm/gems/ruby-1.9.2-p180/gems/dm-do-adapter-1.1.0/lib/dm-do-adapter/adapter.rb:63:in `block in execute'
    from /home/hal/.rvm/gems/ruby-1.9.2-p180/gems/dm-do-adapter-1.1.0/lib/dm-do-adapter/adapter.rb:276:in `with_connection'
    from /home/hal/.rvm/gems/ruby-1.9.2-p180/gems/dm-do-adapter-1.1.0/lib/dm-do-adapter/adapter.rb:61:in `execute'
    from (irb):1

postmodern commented 13 years ago

Also noticed that an empty file with the same name as the mysql development DB was being created in the Rails root. I tried manually re-installing do_mysql-0.10.5, but the error persisted.

dkubb commented 13 years ago

@dbussink: are you able to reproduce this or do you need postmodern to supply you a stand-alone script?

dbussink commented 13 years ago

It looks like different table name quoting in the second statement (using the '). I can only suspect it being related to that. I know that all the DM specs work with the MySQL driver and the latest DO so it's not a generic issue.

@postmodern: Can you provide a simple test script that I can try?

postmodern commented 13 years ago

I've tried creating Commands directory from the Connection and executing them. It still raises an exception immediately.

This is just a wild guess, but I checked the cCommand_execute_async code, and noticed mysql_read_query_result actually returns a my_bool type (where 1 is true and 0 is false), but CHECK_AND_RAISE will raise an exception for any return-value that is not 0.

I tried reproducing in a standalone script, but could not. Which leads me to believe it's somehow specific to Rails apps.

postmodern commented 13 years ago

Ah ha, I am able to reproduce by creating a new dm-rails project, enabling dm-mysql-adapter and copying in my database.yml.

The error continues to occur even when I am clearly not connected to localhost:3306, also that empty file keeps appearing.

dkubb commented 13 years ago

I was unable to reproduce this with the following steps: http://pastie.org/1908521

NOTE: I changed the Gemfile so that it used dm-mysql-adapter instead of dm-sqlite-adapter.

In the rails console I created a User, then queried for them and was able to retrieve the expected record.

postmodern commented 13 years ago

Confirmed that this bug is triggered when including both dm-sqlite-adapter ~> 1.1.0 and dm-mysql-adapter ~> 1.1.0 in the Gemfile. Apparently do_sqlite somehow takes precedence.

database.yml

defaults: &defaults
  adapter:  mysql
  host:     localhost
  database: test
  user:     root
  password: secret

development:
  <<: *defaults
test:
  <<: *defaults
production:
  <<: *defaults

Gemfile

gem 'dm-rails',          '~> 1.1.0'
gem 'dm-sqlite-adapter', DM_VERSION
gem 'dm-mysql-adapter',  DM_VERSION

rake db:automigrate

** Invoke db:automigrate (first_time)
** Invoke environment (first_time)
** Execute environment
** Execute db:automigrate
rake aborted!
unable to open database file
/home/hal/.rvm/gems/ruby-1.9.2-p180@rails/gems/data_objects-0.10.5/lib/data_objects/connection.rb:79:in `initialize'
/home/hal/.rvm/gems/ruby-1.9.2-p180@rails/gems/data_objects-0.10.5/lib/data_objects/connection.rb:79:in `new'
/home/hal/.rvm/gems/ruby-1.9.2-p180@rails/gems/data_objects-0.10.5/lib/data_objects/pooling.rb:177:in `block in new'
<internal:prelude>:10:in `synchronize'
/home/hal/.rvm/gems/ruby-1.9.2-p180@rails/gems/data_objects-0.10.5/lib/data_objects/pooling.rb:172:in `new'
/home/hal/.rvm/gems/ruby-1.9.2-p180@rails/gems/data_objects-0.10.5/lib/data_objects/pooling.rb:119:in `new'
/home/hal/.rvm/gems/ruby-1.9.2-p180@rails/gems/data_objects-0.10.5/lib/data_objects/connection.rb:68:in `new'
/home/hal/.rvm/gems/ruby-1.9.2-p180@rails/gems/dm-do-adapter-1.1.0/lib/dm-do-adapter/adapter.rb:251:in `open_connection'
/home/hal/.rvm/gems/ruby-1.9.2-p180@rails/gems/dm-transactions-1.1.0/lib/dm-transactions/adapters/dm-do-adapter.rb:69:in `open_connection'
/home/hal/.rvm/gems/ruby-1.9.2-p180@rails/gems/dm-do-adapter-1.1.0/lib/dm-do-adapter/adapter.rb:276:in `with_connection'
/home/hal/.rvm/gems/ruby-1.9.2-p180@rails/gems/dm-do-adapter-1.1.0/lib/dm-do-adapter/adapter.rb:33:in `select'
/home/hal/.rvm/gems/ruby-1.9.2-p180@rails/gems/dm-migrations-1.1.0/lib/dm-migrations/adapters/dm-mysql-adapter.rb:22:in `storage_exists?'
/home/hal/.rvm/gems/ruby-1.9.2-p180@rails/gems/dm-migrations-1.1.0/lib/dm-migrations/auto_migration.rb:63:in `storage_exists?'
/home/hal/.rvm/gems/ruby-1.9.2-p180@rails/gems/dm-migrations-1.1.0/lib/dm-migrations/auto_migration.rb:118:in `storage_exists?'
/home/hal/.rvm/gems/ruby-1.9.2-p180@rails/gems/dm-constraints-1.1.0/lib/dm-constraints/migrations.rb:36:in `auto_migrate_down_constraints!'
/home/hal/.rvm/gems/ruby-1.9.2-p180@rails/gems/dm-migrations-1.1.0/lib/dm-migrations/auto_migration.rb:45:in `block in repository_execute'
/home/hal/.rvm/gems/ruby-1.9.2-p180@rails/gems/dm-core-1.1.0/lib/dm-core/support/descendant_set.rb:66:in `block in each'
/home/hal/.rvm/gems/ruby-1.9.2-p180@rails/gems/dm-core-1.1.0/lib/dm-core/support/subject_set.rb:212:in `block in each'
/home/hal/.rvm/gems/ruby-1.9.2-p180@rails/gems/dm-core-1.1.0/lib/dm-core/support/ordered_set.rb:321:in `block in each'
/home/hal/.rvm/gems/ruby-1.9.2-p180@rails/gems/dm-core-1.1.0/lib/dm-core/support/ordered_set.rb:321:in `each'
/home/hal/.rvm/gems/ruby-1.9.2-p180@rails/gems/dm-core-1.1.0/lib/dm-core/support/ordered_set.rb:321:in `each'
/home/hal/.rvm/gems/ruby-1.9.2-p180@rails/gems/dm-core-1.1.0/lib/dm-core/support/subject_set.rb:212:in `each'
/home/hal/.rvm/gems/ruby-1.9.2-p180@rails/gems/dm-core-1.1.0/lib/dm-core/support/descendant_set.rb:65:in `each'
/home/hal/.rvm/gems/ruby-1.9.2-p180@rails/gems/dm-migrations-1.1.0/lib/dm-migrations/auto_migration.rb:44:in `repository_execute'
/home/hal/.rvm/gems/ruby-1.9.2-p180@rails/gems/dm-constraints-1.1.0/lib/dm-constraints/migrations.rb:10:in `auto_migrate!'
/home/hal/.rvm/gems/ruby-1.9.2-p180@rails/gems/dm-rails-1.1.0/lib/dm-rails/railties/database.rake:38:in `block (3 levels) in <top (required)>'
/home/hal/.rvm/gems/ruby-1.9.2-p180@rails/gems/dm-rails-1.1.0/lib/dm-rails/railties/database.rake:37:in `each'
/home/hal/.rvm/gems/ruby-1.9.2-p180@rails/gems/dm-rails-1.1.0/lib/dm-rails/railties/database.rake:37:in `block (2 levels) in <top (required)>'
/home/hal/.rvm/gems/ruby-1.9.2-p180@rails/gems/rake-0.8.7/lib/rake.rb:636:in `call'
/home/hal/.rvm/gems/ruby-1.9.2-p180@rails/gems/rake-0.8.7/lib/rake.rb:636:in `block in execute'
/home/hal/.rvm/gems/ruby-1.9.2-p180@rails/gems/rake-0.8.7/lib/rake.rb:631:in `each'
/home/hal/.rvm/gems/ruby-1.9.2-p180@rails/gems/rake-0.8.7/lib/rake.rb:631:in `execute'
/home/hal/.rvm/gems/ruby-1.9.2-p180@rails/gems/rake-0.8.7/lib/rake.rb:597:in `block in invoke_with_call_chain'
/home/hal/.rvm/rubies/ruby-1.9.2-p180/lib/ruby/1.9.1/monitor.rb:201:in `mon_synchronize'
/home/hal/.rvm/gems/ruby-1.9.2-p180@rails/gems/rake-0.8.7/lib/rake.rb:590:in `invoke_with_call_chain'
/home/hal/.rvm/gems/ruby-1.9.2-p180@rails/gems/rake-0.8.7/lib/rake.rb:583:in `invoke'
/home/hal/.rvm/gems/ruby-1.9.2-p180@rails/gems/rake-0.8.7/lib/rake.rb:2051:in `invoke_task'
/home/hal/.rvm/gems/ruby-1.9.2-p180@rails/gems/rake-0.8.7/lib/rake.rb:2029:in `block (2 levels) in top_level'
/home/hal/.rvm/gems/ruby-1.9.2-p180@rails/gems/rake-0.8.7/lib/rake.rb:2029:in `each'
/home/hal/.rvm/gems/ruby-1.9.2-p180@rails/gems/rake-0.8.7/lib/rake.rb:2029:in `block in top_level'
/home/hal/.rvm/gems/ruby-1.9.2-p180@rails/gems/rake-0.8.7/lib/rake.rb:2068:in `standard_exception_handling'
/home/hal/.rvm/gems/ruby-1.9.2-p180@rails/gems/rake-0.8.7/lib/rake.rb:2023:in `top_level'
/home/hal/.rvm/gems/ruby-1.9.2-p180@rails/gems/rake-0.8.7/lib/rake.rb:2001:in `block in run'
/home/hal/.rvm/gems/ruby-1.9.2-p180@rails/gems/rake-0.8.7/lib/rake.rb:2068:in `standard_exception_handling'
/home/hal/.rvm/gems/ruby-1.9.2-p180@rails/gems/rake-0.8.7/lib/rake.rb:1998:in `run'
/home/hal/.rvm/gems/ruby-1.9.2-p180@rails/gems/rake-0.8.7/bin/rake:31:in `<top (required)>'
/home/hal/.rvm/gems/ruby-1.9.2-p180@rails/bin/rake:19:in `load'
/home/hal/.rvm/gems/ruby-1.9.2-p180@rails/bin/rake:19:in `<main>'

solnic commented 13 years ago

I was able to track this down but I've no idea how to fix this, here's a standalone:

#!/usr/bin/env ruby

require 'dm-mysql-adapter'
require 'dm-sqlite-adapter'

DataMapper.setup :default, "mysql://root@localhost/test"
DataMapper.setup :alternate, "sqlite::memory:"

puts "MYSQL RESULT: #{DataMapper::Repository.adapters[:default].execute("SELECT 1")}"
puts "SQLITE RESULT: #{DataMapper::Repository.adapters[:alternate].execute("SELECT 1")}"

First query doesn't throw an exception but Sqlite3::Result object is returned so probably an sqlite connection is created using mysql connection uri, hence the weird empty db files. The second query ends up with an exception because it tries opening a mysql connection using sqlite connection uri.

Here's the output:

MYSQL RESULT: #<DataObjects::Sqlite3::Result:0x92b7538>
/home/solnic/.rvm/gems/ruby-1.9.2-p180/gems/data_objects-0.10.5/lib/data_objects/connection.rb:79:in `initialize': Unknown database ':memory:' (DataObjects::SyntaxError)
    from /home/solnic/.rvm/gems/ruby-1.9.2-p180/gems/data_objects-0.10.5/lib/data_objects/connection.rb:79:in `new'
    from /home/solnic/.rvm/gems/ruby-1.9.2-p180/gems/data_objects-0.10.5/lib/data_objects/pooling.rb:177:in `block in new'
    from <internal:prelude>:10:in `synchronize'
    from /home/solnic/.rvm/gems/ruby-1.9.2-p180/gems/data_objects-0.10.5/lib/data_objects/pooling.rb:172:in `new'
    from /home/solnic/.rvm/gems/ruby-1.9.2-p180/gems/data_objects-0.10.5/lib/data_objects/pooling.rb:119:in `new'
    from /home/solnic/.rvm/gems/ruby-1.9.2-p180/gems/data_objects-0.10.5/lib/data_objects/connection.rb:68:in `new'
    from /home/solnic/.rvm/gems/ruby-1.9.2-p180/gems/dm-do-adapter-1.1.0/lib/dm-do-adapter/adapter.rb:251:in `open_connection'
    from /home/solnic/.rvm/gems/ruby-1.9.2-p180/gems/dm-do-adapter-1.1.0/lib/dm-do-adapter/adapter.rb:276:in `with_connection'
    from /home/solnic/.rvm/gems/ruby-1.9.2-p180/gems/dm-do-adapter-1.1.0/lib/dm-do-adapter/adapter.rb:61:in `execute'
    from mysql_vs_sqlite_reader_conflict.rb:10:in `<main>'

So something funky is going on as creating a command returns correct Mysql or Sqlite command object but then executing reader returns Sqlite reader for mysql connection and MySQL reader for sqlite connection.

dbussink commented 13 years ago

@solnic This is really weird, because I can't reproduce this. I've tried with 1.9.2 and 1.8.7 and I don't get any of those issues you mention. The script there runs fine for me here.

dkubb commented 13 years ago

I've added a repro to #11 for this.

dbussink commented 13 years ago

Could you guys try with latest version from git to see if the issue is still present? It fixed it for a repro using a VM I had.

solnic commented 13 years ago

Yeah it's working great now :)

postmodern commented 13 years ago

Confirmed this fixes it. Nice work!

datamapper / do