Using myql2 as database

[cvandermeer]

I have configured a vps with a node that uses mysql2. However when i run deploy i get this error:

ActiveRecord::StatementInvalid: Mysql2::Error: CREATE command denied to user 'stageapp'@'localhost' for table 'schema_migrations': CREATE TABLE `schema_migrations` (`version` varchar(255) NOT NULL) ENGINE=InnoDB

[ghost]

@cvandermeer can you please provide your node config?

[cvandermeer]

{
  "run_list":["role[mysql]","role[rails_passenger]"],
  "mysql": {
    "server_debian_password": "[[password]]",
    "server_root_password": "[[password]]",
    "server_repl_password": "[[password]]"
  },
  "ssh_deploy_keys": [
    "[[deploy key]]"
  ],
  "active_applications": {
    "stageapp_production": {
      "rails_env": "production",
      "packages": ["nodejs"],
      "domain_names": ["stage.cvandermeerdev.nl", "www.stage.cvandermeerdev.nl"],
      "ruby_version": "2.1.2",
      "env_vars": {
        "key_1": "val_1",
        "key_2": "val_2"
      },
      "database_info": {
        "adapter": "mysql2",
        "host": "localhost",
        "username": "stageapp",
        "password": "[[password]]",
        "database": "stageapp_production"
      }
    }
  }
}

[ghost]

After running your config I've got the following grants on the host db:

mysql> use stageapp_production;
Database changed
mysql> show grants;
+-----------------------------------------------------------------------------------------------------------------+
| Grants for stageapp@localhost                                                                                   |
+-----------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'stageapp'@'localhost' IDENTIFIED BY PASSWORD '*94BDCEBE19083CE2A1F959FD02F964C7AF4CFC29' |
| GRANT ALL PRIVILEGES ON `stageapp_production`.`*` TO 'stageapp'@'localhost'                                     |
+-----------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

The above looks good, but I still cannot create any table with the user:

mysql> CREATE TABLE `schema_migrations` (`version` varchar(255) NOT NULL) ENGINE=InnoDB;
ERROR 1142 (42000): CREATE command denied to user 'stageapp'@'localhost' for table 'schema_migrations'

[ghost]

Weird, after creating a user and granting it manually I was able to create tables successfully. With root user:

mysql> drop user stageapp@localhost;
Query OK, 0 rows affected (0.01 sec)

mysql> create user stageapp@localhost identified by 'test';
Query OK, 0 rows affected (0.01 sec)

mysql> grant all privileges on `stageapp_production`.* to 'stageapp'@'localhost';
Query OK, 0 rows affected (0.00 sec)

Then I logged in with stageapp:test and was able to create a table:

mysql> use stageapp_production;
Database changed
mysql> CREATE TABLE `schema_migrations` (`version` varchar(255) NOT NULL) ENGINE=InnoDB;
Query OK, 0 rows affected (0.02 sec)

The grants for the manually created user:

mysql> show grants;
+-----------------------------------------------------------------------------------------------------------------+
| Grants for stageapp@localhost                                                                                   |
+-----------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'stageapp'@'localhost' IDENTIFIED BY PASSWORD '*94BDCEBE19083CE2A1F959FD02F964C7AF4CFC29' |
| GRANT ALL PRIVILEGES ON `stageapp_production`.* TO 'stageapp'@'localhost'                                       |
+-----------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

[jvanbaarsen]

Just to be sure I get the problem, when using the chef-repo the database grants are not correct, but when running commands manually it does work?

[ghost]

@jvanbaarsen yeah, and I probably know why:

`stageapp_production`.`*`

is not correct

[jvanbaarsen]

you mean the backticks?

[ghost]

yes

[jvanbaarsen]

Its really weird, since we use this also for Intercityup.com and we don't have this issue there. Any suggestions?

[ghost]

@jvanbaarsen there is a table option which is set to "*" in our database.rb? https://github.com/opscode-cookbooks/database/blob/v2.3.1/libraries/provider_database_mysql_user.rb#L69

our recipe: https://github.com/intercity/chef-repo/blob/master/vendor/cookbooks/rails/recipes/databases.rb#L25

[jvanbaarsen]

@oiuzikov Not sure what you mean with "our database.rb"?

[ghost]

@jvanbaarsen https://github.com/intercity/chef-repo/blob/master/vendor/cookbooks/rails/recipes/databases.rb#L25

[jvanbaarsen]

Ok, but that does mean we dont use the backticks there, so that should be correct right?

[ghost]

take a look at this line: https://github.com/opscode-cookbooks/database/blob/v2.3.1/libraries/provider_database_mysql_user.rb#L69

If we set table property in the mysql_database resource definition to "*", the following

.#{@new_resource.table ? "`#{@new_resource.table}`" : '*'}

will end up with

.`*`

which is not correct

[ghost]

The database cookbook has a change in that line. The migration from v2.2.0 to v2.3.1 made our databases.rb recipe broken