How do you force a MySQL command from the command line to not prompt y/n?

Question

I have a shell script that does the following

mysql -uuser -ppass -e "DROP DATABASE IF EXISTS database"

However, this brings up a prompt if you're sure you want to do that [Y/N]. I need this in a script so is there a way to force it execute? --force option in documentation talks about not stopping for errors.

EDIT: mysql client in fact does not generate prompt. It turns out I had mysqladmin client call that was generating the prompt.

Answer 1

It is evident that the shell script is waiting for the Y/N response and not the MySQL client.

You should be able to execute the line directly by just copying/pasting

mysql -uuser -ppass -e "DROP DATABASE IF EXISTS database"

at the Linux command prompt.

If you prefer, where this command appears, simply comment out the Y/N response from the shell script.

My next suggestion would be for you look into your my.cnf.

See if there is a [mysql] or [client] section with the following:

[mysql]
i-am-a-dummy
safe-updates

or

[client]
i-am-a-dummy
safe-updates

Those are real options: See safe-updates and i-am-a-dummy in the MySQL Documentation

UPDATE 2013-01-25 16:48 EDT

My next guess would be the Operating System. Why ???

If you are logged into Linux as root or you executed sudo, you have unquestioned rights to doing a DROP DATABASE IF EXISTS. At the OS level, mysqld would attempt to discard the folder for the database.

For example, if datadir is /var/lib/mysql and you execute DROp DATABASE IF EXISTS rolando;, mysqld will attempt to run rm -rf /var/lib/mysql/rolando.

if you are not root or sudo'd as root, I would expect the OS to echo that message. In fact, I have seen a message from the OS ask to delete a PID file when I was not logged in as root and attempted service mysql stop.

UPDATE 2013-01-25 16:54 EDT

mysqladmin does not cause prompting either, except for passwords. Here are all its options:

[root@***]# mysqladmin --help
mysqladmin  Ver 8.42 Distrib 5.1.47, for redhat-linux-gnu on x86_64
Copyright 2000-2008 MySQL AB, 2008 Sun Microsystems, Inc.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL license

Administration program for the mysqld daemon.
Usage: mysqladmin [OPTIONS] command command....
  -c, --count=#       Number of iterations to make. This works with -i
                      (--sleep) only.
  --debug-check       Check memory and open file usage at exit.
  --debug-info        Print some debug info at exit.
  -f, --force         Don't ask for confirmation on drop database; with
                      multiple commands, continue even if an error occurs.
  -C, --compress      Use compression in server/client protocol.
  --character-sets-dir=name
                      Directory for character set files.
  --default-character-set=name
                      Set the default character set.
  -?, --help          Display this help and exit.
  -h, --host=name     Connect to host.
  -b, --no-beep       Turn off beep on error.
  -p, --password[=name]
                      Password to use when connecting to server. If password is
                      not given it's asked from the tty.
  -P, --port=#        Port number to use for connection or 0 for default to, in
                      order of preference, my.cnf, $MYSQL_TCP_PORT,
                      /etc/services, built-in default (3306).
  --protocol=name     The protocol to use for connection (tcp, socket, pipe,
                      memory).
  -r, --relative      Show difference between current and previous values when
                      used with -i. Currently only works with extended-status.
  -O, --set-variable=name
                      Change the value of a variable. Please note that this
                      option is deprecated; you can set variables directly with
                      --variable-name=value.
  -s, --silent        Silently exit if one can't connect to server.
  -S, --socket=name   The socket file to use for connection.
  -i, --sleep=#       Execute commands repeatedly with a sleep between.
  --ssl               Enable SSL for connection (automatically enabled with
                      other flags). Disable with --skip-ssl.
  --ssl-ca=name       CA file in PEM format (check OpenSSL docs, implies
                      --ssl).
  --ssl-capath=name   CA directory (check OpenSSL docs, implies --ssl).
  --ssl-cert=name     X509 cert in PEM format (implies --ssl).
  --ssl-cipher=name   SSL cipher to use (implies --ssl).
  --ssl-key=name      X509 key in PEM format (implies --ssl).
  --ssl-verify-server-cert
                      Verify server's "Common Name" in its cert against
                      hostname used when connecting. This option is disabled by
                      default.
  -u, --user=name     User for login if not current user.
  -v, --verbose       Write more information.
  -V, --version       Output version information and exit.
  -E, --vertical      Print output vertically. Is similar to --relative, but
                      prints output vertically.
  -w, --wait[=#]      Wait and retry if connection is down.
  --connect_timeout=#
  --shutdown_timeout=#

HEY, I STAND CORRECTED

--force does prompt for DROP DATABASE

OK I guess you located the culprit. I learned something today because I do not use mysqladmin to drop databases.

Answer 2

Might have to pipe the 'yes' into the command. This site offers an idea on how to do this.

yes | mysqladmin -u[username] -p[password] drop [database]

But here is another wrinkle via this post.

mysqladmin -u[username] -p[password] -f drop [database]

Answer 3

In general, you can pass any query to mysql from shell with -e option.

mysql -u username -ppassword -D dbname -e "DROP DATABASE" 

Or you can Store your password in my.cnf but its less secure.

[client]
host     = localhost
user     = username.
password = password
socket   = /var/lib/mysql/mysql.sock