Couldn't create database error w/ rake db:setup on MySQL

[cap10morgan]

When I run rake db:setup in a Rails 4 project, I get a "Couldn't create database for..." error.

database.yml NOTE: I need to use the adapter: jdbc syntax because it's the only one that seems to support SSL connections to the MySQL server (which we use in production and I want as few differences between dev and production as possible).

full error output & stack trace

Let me know if any other info would be helpful.

---

Want to back this issue? Post a bounty on it! We accept bounties via Bountysource.

--- Want to back this issue? **[Post a bounty on it!](https://www.bountysource.com/issues/4271332-couldn-t-create-database-error-w-rake-db-setup-on-mysql?utm_campaign=plugin&utm_content=tracker%2F136963&utm_medium=issues&utm_source=github)** We accept bounties via [Bountysource](https://www.bountysource.com/?utm_campaign=plugin&utm_content=tracker%2F136963&utm_medium=issues&utm_source=github).

[kares]

thanks, the rake tasks are mostly tested while assuming adapter: set to concrete adapter (adapter: jdbc will be deprecated for setups where it's not "needed") ... I'm suprised by your notes. there's something messy going on if it ONLY works with jdbc - there's no reason the following configuration should not work (and should be actually ~ to yours) :

defaults: &defaults
  adapter: mysql
  encoding: utf8
  collation: utf8_unicode_ci
  reconnect: true
  autocommit: false
  pool: 100
  username: <%= ENV['DB_USERNAME'].present? ? ENV['DB_USERNAME'] : 'root' %>
  password: <%= ENV['DB_PASSWORD'].present? ? ENV['DB_PASSWORD'] : '' %>
  host: <%= ENV['DB_1_PORT_3306_TCP_ADDR'].present? ? ENV['DB_1_PORT_3306_TCP_ADDR'] : 'localhost' %>
  port: <%= ENV['DB_1_PORT_3306_TCP_PORT'].present? ? ENV['DB_1_PORT_3306_TCP_PORT'] : '3306' %>
  database: turbovote_development

... there was no SSL configuration present in the url: ... please let us know if there's an issue with using adapter: mysql otherwise this is probably low priority since adapter: mysql is expected to work.

[cap10morgan]

Right, I'm not using SSL in dev, but I can only use SSL in production with adapter: jdbc and the other changes it requires (if that's wrong, I'd love to know how to use SSL with adapter: mysql instead).

So I'm just trying to run a database.yml in dev that is not wildly different from production.

I'll try it with the database.yml above and let you know how it goes.

[kares]

@cap10morgan adapter: mysql should work just fine with SSL ... show us the (DB) config if it does not.

[cap10morgan]

It works with your database.yml.

So now my question is: If this is a low-priority or a wontfix for adapter: jdbc, then how can I get SSL working with adapter: mysql?

[cap10morgan]

@kares It might be a documentation problem. With adapter: mysql, where does the trust store path and the trust store passphrase belong (I'm only verifying the server's cert, not using a client cert)?

[kares]

@cap10morgan still no database configuration with ssl params ! ... it's just about the very same if you must look at MySQL connector J documentation and specify the properties: as in the README

[cap10morgan]

It will take a bit to work up my old attempts at JDBC SSL database.yml w/ adapter: mysql but I'm working on it.

I see no references to SSL in the README.

[kares]

http://dev.mysql.com/doc/connector-j/en/connector-j-reference-configuration-properties.html

any of those (URL) properties can be set ... just like our README explains, there's a sample :

production:
  adapter: mysql
  username: blog
  password: blog
  url: "jdbc:mysql://localhost:3306/blog?profileSQL=true"
  properties: # specific to com.mysql.jdbc.Driver
    socketTimeout:  60000
    connectTimeout: 60000

.. but if you just take the adapter: jdbc configuration you have working and change the adapter it will work just fine (if it does not than that is actually a bug worth reporting) ... also you can always improve the wiki https://github.com/jruby/activerecord-jdbc-adapter/wiki/MySQL#SSL

[cap10morgan]

OK, here we go.

This works (without SSL):

defaults: &defaults
  adapter: mysql
  encoding: utf8
  collation: utf8_unicode_ci
  reconnect: true
  autocommit: false
  pool: 100
  database: turbovote_development
  username: <%= ENV['DB_USERNAME'].present? ? ENV['DB_USERNAME'] : 'root' %>
  password: <%= ENV['DB_PASSWORD'].present? ? ENV['DB_PASSWORD'] : '' %>
  host: <%= ENV['DB_1_PORT_3306_TCP_ADDR'].present? ? ENV['DB_1_PORT_3306_TCP_ADDR'] : 'localhost' %>
  port: <%= ENV['DB_1_PORT_3306_TCP_PORT'].present? ? ENV['DB_1_PORT_3306_TCP_PORT'] : '3306' %>

development:
  <<: *defaults
  database: turbovote_development

But this does not:

defaults: &defaults
  adapter: mysql
  encoding: utf8
  collation: utf8_unicode_ci
  reconnect: true
  autocommit: false
  pool: 100
  database: turbovote_development
  username: <%= ENV['DB_USERNAME'].present? ? ENV['DB_USERNAME'] : 'root' %>
  password: <%= ENV['DB_PASSWORD'].present? ? ENV['DB_PASSWORD'] : '' %>
  host: <%= ENV['DB_1_PORT_3306_TCP_ADDR'].present? ? ENV['DB_1_PORT_3306_TCP_ADDR'] : 'localhost' %>
  port: <%= ENV['DB_1_PORT_3306_TCP_PORT'].present? ? ENV['DB_1_PORT_3306_TCP_PORT'] : '3306' %>
  properties:
    useSSL: true
    trustStorePath: /Users/wmorgan/dev/dockerfiles/turbovote/staging/config/truststore
    trustStorePassword: [redacted-but-known-to-work]

development:
  <<: *defaults
  database: turbovote_development

Here's the output for the second one:

$ bundle exec rake db:setup
Couldn't create database for {"adapter"=>"mysql", "encoding"=>"utf8", "collation"=>"utf8_unicode_ci", "reconnect"=>true, "autocommit"=>false, "pool"=>100, "database"=>"turbovote_development", "username"=>"root", "password"=>"[redacted]", "host"=>"[redacted]", "port"=>3306, "properties"=>{"useSSL"=>true, "trustStorePath"=>"/Users/wmorgan/dev/dockerfiles/turbovote/staging/config/truststore", "trustStorePassword"=>"[redacted]", "zeroDateTimeBehavior"=>"convertToNull", "jdbcCompliantTruncation"=>"false", "useUnicode"=>"true", "characterEncoding"=>"utf8", "autoReconnect"=>"true"}}, {:charset=>"utf8", :collation=>"utf8_unicode_ci"}
(If you set the charset manually, make sure you have a matching collation)
Couldn't create database for {"adapter"=>"mysql", "encoding"=>"utf8", "collation"=>"utf8_unicode_ci", "reconnect"=>false, "autocommit"=>false, "pool"=>100, "database"=>"turbovote_test", "username"=>"root", "password"=>"[redacted]", "host"=>"[redacted]", "port"=>3306, "properties"=>{"useSSL"=>true, "trustStorePath"=>"/Users/wmorgan/dev/dockerfiles/turbovote/staging/config/truststore", "trustStorePassword"=>"[redacted]", "zeroDateTimeBehavior"=>"convertToNull", "jdbcCompliantTruncation"=>"false", "useUnicode"=>"true", "characterEncoding"=>"utf8", "autoReconnect"=>"true"}}, {:charset=>"utf8", :collation=>"utf8_unicode_ci"}
(If you set the charset manually, make sure you have a matching collation)
-- create_table("absentee_answers", {:force=>true})
rake aborted!
ActiveRecord::JDBCError: The driver encountered an unknown error: com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: Could not create connection to database server. Attempted reconnect 3 times. Giving up.
/Users/wmorgan/dev/turbovote/db/schema.rb:16:in `(root)'
/Users/wmorgan/dev/turbovote/db/schema.rb:14:in `(root)'
Tasks: TOP => db:schema:load
(See full trace by running task with --trace)

[cap10morgan]

@kares I will definitely be updating the wiki once we get this figured out. :)

[kares]

well you likely get the same error with adapter: jdbc if not that's a bug otherwise it's a DB setup issue

[cap10morgan]

@kares Yes, sorry, that was a bad test case (since it's the original motivation for this bug). Here's a better one with rake db:seed which works anytime it can actually connect to the MySQL server:

adapter: mysql with SSL properties as above:

$ rake db:seed
rake aborted!
ActiveRecord::JDBCError: The driver encountered an unknown error: com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: Could not create connection to database server. Attempted reconnect 3 times. Giving up.

Tasks: TOP => db:abort_if_pending_migrations

adapter: mysql without SSL properties:

$ bundle exec rake db:seed
Loading seeds into absentee_questions... done. # truncating the rest of the output; it's working

adapter: jdbc and the other changes it requires (driver, url, etc.) with useSSL=true in the url:

$ bundle exec rake db:seed
Loading seeds into absentee_questions... done. # truncating the rest of the output; it's working

Here's the database.yml for that last run:

defaults: &defaults
  adapter: jdbc
  driver: com.mysql.jdbc.Driver
  encoding: utf8
  collation: utf8_unicode_ci
  reconnect: true
  autocommit: false
  pool: 100
  username: <%= ENV['DB_USERNAME'].present? ? ENV['DB_USERNAME'] : 'root' %>
  password: <%= ENV['DB_PASSWORD'].present? ? ENV['DB_PASSWORD'] : '' %>
  host: <%= ENV['DB_1_PORT_3306_TCP_ADDR'].present? ? ENV['DB_1_PORT_3306_TCP_ADDR'] : 'localhost' %>
  port: <%= ENV['DB_1_PORT_3306_TCP_PORT'].present? ? ENV['DB_1_PORT_3306_TCP_PORT'] : '3306' %>

development:
  <<: *defaults
  database: turbovote_development
  url: jdbc:mysql://<%= ENV['DB_1_PORT_3306_TCP_ADDR'].present? ? ENV['DB_1_PORT_3306_TCP_ADDR'] : 'localhost' %>/turbovote_development?useSSL=true
  trust_store_path: /Users/wmorgan/dev/dockerfiles/turbovote/staging/config/truststore
  trust_store_password: [redacted]

That's the only way I've ever gotten SSL to work.

[cap10morgan]

@kares I found a combination that makes SSL work (I assume; do you know how I can verify SSL is being used?) with adapter: mysql but creating databases still doesn't work.

database.yml:

defaults: &defaults
  adapter: mysql
  encoding: utf8
  collation: utf8_unicode_ci
  reconnect: true
  autocommit: false
  pool: 100
  database: turbovote_development
  username: <%= ENV['DB_USERNAME'].present? ? ENV['DB_USERNAME'] : 'root' %>
  password: <%= ENV['DB_PASSWORD'].present? ? ENV['DB_PASSWORD'] : '' %>
  host: <%= ENV['DB_1_PORT_3306_TCP_ADDR'].present? ? ENV['DB_1_PORT_3306_TCP_ADDR'] : 'localhost' %>
  port: <%= ENV['DB_1_PORT_3306_TCP_PORT'].present? ? ENV['DB_1_PORT_3306_TCP_PORT'] : '3306' %>
  trust_store_path: /Users/wmorgan/dev/dockerfiles/turbovote/staging/config/truststore
  trust_store_password: [redacted]
  properties:
    useSSL: true

development:
  <<: *defaults
  database: turbovote_development

With that, rake db:seed and rake db:migrate work but I get this from rake db:create:

Couldn't create database for {"adapter"=>"mysql", "encoding"=>"utf8", "collation"=>"utf8_unicode_ci", "reconnect"=>true, "autocommit"=>false, "pool"=>100, "database"=>"turbovote_development", "username"=>"root", "password"=>"[redacted]", "host"=>"[redacted]", "port"=>3306, "trust_store_path"=>"/Users/wmorgan/dev/dockerfiles/turbovote/staging/config/truststore", "trust_store_password"=>"[redacted]", "properties"=>{"useSSL"=>true, "zeroDateTimeBehavior"=>"convertToNull", "jdbcCompliantTruncation"=>"false", "useUnicode"=>"true", "characterEncoding"=>"utf8", "autoReconnect"=>"true"}}, {:charset=>"utf8", :collation=>"utf8_unicode_ci"}
(If you set the charset manually, make sure you have a matching collation)
Couldn't create database for {"adapter"=>"mysql", "encoding"=>"utf8", "collation"=>"utf8_unicode_ci", "reconnect"=>false, "autocommit"=>false, "pool"=>100, "database"=>"turbovote_test", "username"=>"root", "password"=>"[redacted]", "host"=>"[redacted]", "port"=>3306, "trust_store_path"=>"/Users/wmorgan/dev/dockerfiles/turbovote/staging/config/truststore", "trust_store_password"=>"[redacted]", "properties"=>{"useSSL"=>true, "zeroDateTimeBehavior"=>"convertToNull", "jdbcCompliantTruncation"=>"false", "useUnicode"=>"true", "characterEncoding"=>"utf8", "autoReconnect"=>"true"}}, {:charset=>"utf8", :collation=>"utf8_unicode_ci"}
(If you set the charset manually, make sure you have a matching collation)

So now is this a bug? :)

[cap10morgan]

Any word on this?

[kares]

@cap10morgan sorry, but this is really low-priority (for me) ... I'm not exactly sure what to do here without testing this out myself, I would rather expect you to tell me :) assuming you know how the MySQL JDBC driver configuration properties.

if you take a look at https://github.com/jruby/activerecord-jdbc-adapter/blob/1-3-stable/lib/arjdbc/mysql/connection_methods.rb#L17-L69 you'll notice that we do not handle trust_store_path: or trust_store_password: configuration at all - they currently need to be set at the JVM level as stated in Connector/J's SSL documentation ... if that resolves the issue we can maybe tune the code to set them, but I'm not sure if the same trust-store files would work or they need to be in .jks format (can at least print a warning)