Bug #591 Shell Script
Submitted: 5 Jun 2003 5:23 Modified: 6 May 2004 15:21
Reporter: Alexandr Staroverov Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Command-line Clients Severity:S4 (Feature request)
Version:4.0.13 OS:Linux (RedHat Linux)
Assigned to: Sergei Golubchik CPU Architecture:Any
Triage: D5 (Feature request)

[5 Jun 2003 5:23] Alexandr Staroverov
Description:
Dear Sirs,

I'm trying to migrate from Sybase to MySQL and I have found, that using 'sqsh' more convinient, than 'mysql'.
I've made some small changes and now I can run scripts like this:

myscript.sql
--------------------------------------------------------
#!/usr/bin/mysql -c

use mydb;
show tables;
--------------------------------------------------------

How to repeat:
#!/usr/bin/mysql -c

use mydb;
show tables;

Suggested fix:
mysql.cc.diff

315a316,322
>   if (!isatty(0) || !isatty(1))
>   {
>     status.batch=1; opt_silent=1;
>     ignore_errors=0;
>   }
>   else
>     status.add_to_history=1;
325,332d331
<   if (!isatty(0) || !isatty(1))
<   {
<     status.batch=1; opt_silent=1;
<     ignore_errors=0;
<   }
<   else
<     status.add_to_history=1;
< 
460,461d458
<   {"command-file", 'c', "Read input from file", 0,
<    0, 0, GET_STR, REQUIRED_ARG, 0, 0, 0, 0, 0, 0},
652,655d648
<     break;
<   case 'c':
<     fclose(stdin);
<     stdin = fopen(argument, "r");
[5 Jun 2003 6:09] Alexander Keremidarski
What is benefit of adding such option? 

How it is better than:

mysql < file.sql
cat file.sql | mysql

cat | mysql

If you only want to rid of mysql> prompt you can just use prompt command.

mysql> prompt ;
PROMPT set to ''
use test;
Database changed
[5 Jun 2003 7:32] Alexandr Staroverov
$ chmod +x file.sql
$ ./file.sql
May be for adbanved users and developers no problem to run
$ myqsl < file.sql
but fo end-users first case more easy.
Secondly, you can run script from mc or other shell just by clicking to file name.
[5 Jun 2003 14:02] Alexander Keremidarski
To make my comment more clear.

Without adding any new option you can write shell scripts like this:

#!/usr/bin/mysql

use mydb;
show tables;

...

mysql reads from stdin

Written like this such script makes sense only with combination of ~/.my.cnf files otherwise it will not let you login without user/password.

It makses sense to write a script which will parse all input parameters as you might want to use it with different combination of options. Not -u -p only, but also -h -P -S and so on and so on.

Now same questionagain: 

How such script will be better or easier to use than mysql - command line tool?
[5 Jun 2003 22:11] Alexandr Staroverov
~/.my.cnf
[client]
user = myuser
password = mypassword
database = mydatabase

The script you wrote failed with
ERROR 1102: Incorrect database name './test.sql'
This is reason to add a new option -c (or other simular).
Or if you can fix mysql to tun yours sctipt it's Ok.

How such script will be better or easier to use than mysql - command
line tool?
1) I have a huge set of scrits writen in this way. It's much more convinient.
Sybase has command line utility 'isql', but I use 'sqsh' only because of readline and posibility to write sctips in simple way.
2) I can rewrite script using perl or C and for end-users will be no differens to execute sctipt.
3) I never execute perl scripts in this way
$ perl < file.pl
[6 Jun 2003 3:14] Alexander Keremidarski
Ok.
You made your point. 

I still don't see much improvement for general usage, but as your proposal is very small as source change I don't see any harm if we add it. 

Chance is that it will get widely used thus proving how wrong I am.

I will ask our CTO for comment.
[29 Aug 2003 5:45] Are you mortal Then prepare to die.
I would love to see this too, 
I have had exactly the same request!
[6 May 2004 15:21] Sergei Golubchik
Thank you for your bug report. This issue has been committed to our
source repository of that product and will be incorporated into the
next release.

If necessary, you can access the source repository and build the latest
available version, including the bugfix, yourself. More information 
about accessing the source trees is available at
    http://www.mysql.com/doc/en/Installing_source_tree.html

Additional info:

There are two problems with the patch:

1. stdin=... is not portable (doesn't work on FreeBSD, e.g.) the correct function to use is freopen()

2. You have moved testing for isatty() after processing all options - of course, to see whether -c was used to change stdin - it means that a user will have no way of changing default behavior for non-tty - e.g. he cannot set ignore_errors=1 (--force). If you'll have isatty() where it is now and copy the code to -c option processing, the user will have no way of using --force if -c is used, because -c should be the last option on the command line.

What I did, instead, was allowing multiple -e options:

mysql -e 'show tables;' -f -e 'select * from tbl;'

All the options put after the first -e won't be disabled by the following -e, so -f (--force) will be in effect. Now you can start a script with

#!/usr/bin/mysql -e source -e

having whatever options you need between two -e's