Trapping Message From MYSQL

[Vell]

Versions:

• Ruby 2.0
• Capistrano 3.1
• Rake / Rails / etc 4.0.4

Platform:

• Working on.... mac os x
• Deploying to... centos 6.5

Logs:

• Please past logs (as completely as possible to a 3rd party pasting service such as pastie.org)

Files:

• Capfile
• deploy.rb
• Stage files (production.rb, staging.rb)

Hello all,

I am attempting to make it so that capistrano creates a db if it doesn't exist. Right now I am able to run the command to check for the db but I am not able to capture the "unknown database" message that comes out from MYSQL.

I'm sure there is a way to do it, I just haven't figured it out the right way to do it. The code I am starting with is below:

  desc "check if db exists on remote server. If not create it."
  task :check_db do
    on roles(:app) do |host|
      puts "-"*100
      puts capture("mysql -usomeuser -psomepassword #{fetch(:application)}_#{fetch(:rails_env)}")
    end
  end

I have also tried:

puts %x("mysql -usomeuser -psomepassword #{fetch(:application)}_#{fetch(:rails_env)}")

The response I get is:

DEBUG [242e1da5] Command: mysql -usomeuser -psomepassword app_name_dev_server
DEBUG [242e1da5] ERROR
DEBUG [242e1da5]  1049 (42000)
DEBUG [242e1da5] : Unknown database 'online_community_dev_server'

That last line, or even the one before it, I can't seem to catch it to use in a condition statement.

Any thoughts would be greatly appreciated.

[Lee Hambley]

Use the exit status and test() for it. if the database exists, then you'll exit with status 0, if not, you'll exit with non-zero.

Lee Hambley

--

http://lee.hambley.name/

+49 (0) 170 298 5667

--
You received this message because you are subscribed to the Google Groups "Capistrano" group.
To unsubscribe from this group and stop receiving emails from it, send an email to capistrano+...@googlegroups.com.
To view this discussion on the web, visit https://groups.google.com/d/msgid/capistrano/e75b0b3e-6bda-4ea6-961a-55e50eb5fe86%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Bruno Sutic]

Hi,

here's how I solved the same problem for postgresql: https://github.com/bruno-/capistrano-postgresql/blob/master/lib/capistrano/postgresql/psql_helpers.rb#L6-16

As you can see `test` is used (in psql helper) to detect success or failure.

Let me know if you need more help around this. I spent quite some time debugging and getting this to work for postgres (and I think it's still not perfect).

[Vell]

Thanks Bruno.

Unfortunately, I was not able to get test() to exit with a status 0. When I did an print on the response I couldn't see anything that exited with a status or any sort whether it be true, false, 0 or 1. Since I am fairly new to this, I am sure the problem lies on my end.

However, to get me not understanding (for now), I chose to pass an if statement to MYSQL then check the response and act accordingly.

task :check_db_existance do

    on roles(:app) do |host|

      pass = capture("echo $RAILS_DB_PASS").strip

      db_exists = capture("mysql -u someuser -p#{pass} -e \"SELECT IF(EXISTS (SELECT SCHEMA_NAME FROM INFORMATION_SCHEMA.SCHEMATA WHERE SCHEMA_NAME = '#{fetch(:application)}_#{fetch(:rails_env)}'), 'Yes','No')\"").split("\n").last

      unless db_exists == 'Yes'

        info "Creating #{fetch(:application)}_#{fetch(:rails_env)}"

        execute("mysql -u someuser -p#{pass} -e 'create database #{fetch(:application)}_#{fetch(:rails_env)}'")

      end

    end

  end

Honestly, I don't like it since I know there has to be a way to use `rake db:create` instead of me having to pass SQL code to create the DB but for now it works. I will refactor it later on when I have more time.

--

You received this message because you are subscribed to the Google Groups "Capistrano" group.
To unsubscribe from this group and stop receiving emails from it, send an email to capistrano+...@googlegroups.com.

To view this discussion on the web, visit https://groups.google.com/d/msgid/capistrano/e6f569db-5514-49d2-b5d2-9ae279817dc5%40googlegroups.com.

[Bruno Sutic]

Hi,

ok I see. I was having similar problems (and I still have) with that postgresql plugin.

Here's a suggestion how to push some of that logic "down" to the shell (changes bolded):

db_exists = test %Q{mysql -u someuser -p#{pass} -e "SELECT IF(EXISTS (SELECT SCHEMA_NAME FROM INFORMATION_SCHEMA.SCHEMATA WHERE SCHEMA_NAME = '#{fetch(:application)}_#{fetch(:rails_env)}'), 'Yes','No')" | grep -q Yes}

With the above, you should have ruby true or false stored in the `db_exists` variable. That should make your capistrano task cleaner I hope.

Here's what's been done in the above line:

 - changed capture() with test()

 - since you're using a lot of quotes, and there's even quote escapes, I suggest you change the outer quotes with %Q{...} as above. The percent notation with big Q is the same as double quotes (enables interpolation etc).

 - notice we pipe first command to the `grep -q Yes` at the end. -q option means quiet. If "Yes" is passed to grep, the overall command will evaluate to true. If "Yes" is NOT passed to grep, then it will be false.

You can see I'm using the similar trick with postgresql here. The difference is: you're using string 'Yes' and I'm using character 1, but the principle is the same.

Hope that gives you more options.

[Vell]

Outstanding. That looks much better. Thank you for the suggestion.

To view this discussion on the web, visit https://groups.google.com/d/msgid/capistrano/0f241a2b-c31c-4b4b-8f8a-040dc376bb57%40googlegroups.com.