CODEWITHSUNDEEP
mysqlbash
user7988893
user7988893

Reputation:

ERROR 1046 (3D000) at line 1: No database selected

I want to update some table with bash instead of mysql terminal directly.
The normal way is to ebedded sql command in bash with here doc string.

mysqlword="xyzzy"
tmpdb="yyyy"
mysql -u root -p$mysqlword  <<EOF
    USE ${tmpdb};
    UPDATE wp_posts SET post_content = replace(post_content, 'domain1', 'domain2');
EOF

Try another way to do the same job.

mysqlword="xyzzy"
tmpdb="yyyy"
mysql -u root -p$mysqlword  -e"USE ${tmpdb};"
sql="UPDATE wp_posts SET post_content = replace(post_content, 'domain1', 'domain2');"
mysql -u root -p$mysqlword  -e"$sql"
ERROR 1046 (3D000) at line 1: No database selected

I have selected database with mysql -u root -p$mysqlword -e"USE ${tmpdb};" before mysql -u root -p$mysqlword -e"$sql",how to fix it?

Upvotes: 1

Views: 2829

Answers (2)

Paul Campbell
Paul Campbell

Reputation: 1976

You can fix/simplify this in a couple of ways:

  1. By removing the separate USE statement.
  2. By removing the user credentials from the command line.

Reference the database name directly in the UPDATE statement:

sql="UPDATE wp_posts SET ${tmpdb}.post_content = replace(post_content, 'domain1', 'domain2');"

If you have version mysql >= 5.6.6, use mysql_config_editor to put the login credentials into a .mylogin.cnf file. It'll save typing and the password will be encrypted.

e.g. 

mysql_config_editor --login-path=root --user=root --host=localhost --password

Once set, your bash command would then be simpler (and safer)

tmpdb=yyyy
sql="UPDATE wp_posts SET ${tmpdb}.post_content = replace(post_content, 'domain1', 'domain2');"
mysql --login-path=root -e "$sql"

or, since the variable aren't really necessary here, 

mysql --login-path=root -e "UPDATE wp_posts SET yyyy.post_content = replace(post_content, 'domain1', 'domain2');"

Upvotes: 0

Kokogino
Kokogino

Reputation: 1056

You have to USE the database every time you open a connection. So you have to combine the two commands:

mysqlword="xyzzy"
tmpdb="yyyy"
sql="USE ${tmpdb}; UPDATE wp_posts SET post_content = replace(post_content, 'domain1', 'domain2');"
mysql -u root -p$mysqlword  -e"$sql"

Upvotes: 2

Related Questions