mysql hangs at login

Question

I am using InfiniDB (not by choice). When I use the linux shell, I am able to connect to a database just fine:

<user>@<host>:~$ /usr/local/Calpont/mysql/bin/mysql -u <user> -p -h <host>
Enter password: <password>

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1725
Server version: 5.1.39 MySQL Community / Calpont InfiniDB Community 2.2.11-1 Fin                                                                             al (COSS LA)
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> use <database_name>
Database changed
mysql>

But when I use a bash script, it hangs:

<user>@<host>:~$ bash -x /home/aliceell/db_export.sh
+ DB=al05_09
+ export_dir=/tmp/
++ /usr/local/Calpont/mysql/bin/mysql -u <user> -p -h <host> <db_name>
Enter password:

It stops responding after I enter my password and press 'enter'. I have to use ctrl-C to get out of it.

Here is the code that I have:

DB=<db_name> 
export_dir="/tmp/"

err_exit()
{
 echo -e 1>&2
 exit 1
}

mysql_data_dir=$(/usr/local/Calpont/mysql/bin/mysql -u <user> -p -h <host> <db_name>) #it hangs at this stage

if ! (mysqlshow $DB 1>/dev/null); then
  echo ERROR: unable to access database
  exit 1
fi

echo "connected"

if ! [ -w $export_dir ]; then
  echo ERROR: export dir is not writable
  exit 1 
fi

ExportTable()
{
 /usr/local/Calpont/mysql/bin/mysql -u <user> -p -h <host> <db_name>" 
   SELECT * INTO OUTFILE '/tmp/$T.csv' 
   FIELDS TERMINATED BY '|' 
   LINES TERMINATED BY '\n' 
   FROM $T
 "
}

/usr/local/Calpont/mysql/bin/mysql -u <user> -p -h <host> <db_name> "show tables" | 
while read T; do 
  echo exporting $T
  ExportTable || err_exit
  sudo mv $mysql_data_dir/$DB/$T.psv $export_dir/ || err_exit
done

I'm new to bash and trying to use it to loop through all the tables in my database and export them (similar to this script), but it's no use if I can't even connect to the database...

Thanks in advance for your help.

It looks like the script that I linked in the post. I edited my post to include the code. Except I have to use /usr/local/Calpont/mysql/bin/mysql instead of just mysql because that's how it works for infinidb (afaik). — ale19, Feb 22 '17 at 14:46

score 1 · Accepted Answer · answered Feb 22 '17 at 14:56

1

The command substitution that you are assigning to mysql_data_dir is not run interactively so the mysql command must run, generate its output and terminate before the output will be returned to the shell variable. Look closely at your linked example (excerpted below) and you will see that his mysql commmand is just this kind of command line; it includes a mysql command that gets executed and piped into an awk command for post-processing.

mysql_data_dir=$(mysql $myopts "show variables like 'datadir'" | awk '{sub(/\/$/,"");print$NF}')

What you need to do is identify what your "one-liner" mysql command is and put that into your $().

answered Feb 22 '17 at 14:56

Greg Tarsa

1,622
13
18

Ah, thanks! I didn't realize that's how the syntax worked. I found the command that I needed and now I can connect. Unfortunately, I can't seem to run the SELECT INTO OUTFILE command: it doesn't seem to use the $T variable correctly. When I run it, I get `"exporting /usr/local/Calpont/mysql/bin/mysql Ver 14.14 Distrib 5.1.39, for unknown-linux-gnu (x86_64) using readline 5.1 Copyright 2000-2008 MySQL AB, 2008 Sun Microsystems, Inc...."` and lists all the options for MySQL. But it doesn't actually export anything...Any chance you know what might be going on? – ale19 Feb 22 '17 at 16:03
I added the rest of the code to my post, just in case that helps anyone – ale19 Feb 22 '17 at 16:11
Are you sure that the output of the `mysql` command that you are piping into your loop is producing only table names? It could have some header lines that need to be skipped for your code to work. Also, if my response, above, answers your base question, please mark the answer as correct. – Greg Tarsa Feb 22 '17 at 21:44
When I type "show tables" into the mysql shell (after typing "use "), it shows the tables and nothing else: http://i.imgur.com/wv79i5t.png I'm not sure what would be causing it to list off the "help" options for InfiniDB (which is what the "Ver 14.14 Distrib 5.1.39, for unknown-linux-gnu..." nonsense is). – ale19 Feb 23 '17 at 13:21
I do not have a `mysql` config here, but it looks like your current problem is that the output from mysql has a lot of "visual junk" in it. All those `+---+` and`|`sare confu sing the subsequent command. [This SO question](http://stackoverflow.com/questions/1636977/bash-script-select-from-database-into-variable) talks about how to put a list of tables into a variable. Put your tables into a `variable`, and then replace your `while read T` with a `for T in ${variable}`. Scripting in Bash is a great skill, learn to look at command i/o from the computer's perspective. – Greg Tarsa Feb 23 '17 at 16:24

score -1 · Answer 2 · answered Feb 22 '17 at 15:01

-1

You can usually include the password on the command line.

/usr/local/Calpont/mysql/bin/mysql -u [user] -p[pwd] -h [host] [db_name]

NOTE: there must be NO SPACE between the -p and the password.

If your password has certain punctuation characters you may have to wrap it in double quotes.

Obviously this is the least secure way to do it, but it is the simplest.

answered Feb 22 '17 at 15:01

KenP_olb

91
4

I can enter my password just fine, so I don't think this is the issue...I tried it anyway just in case and it still hangs after I run the program... – ale19 Feb 22 '17 at 16:03

mysql hangs at login

2 Answers2