MySQL Bugs: #20184: Patch to add #!/path/mysql shebang capability to cmdline client

Bug #20184	Patch to add #!/path/mysql shebang capability to cmdline client
Submitted:	1 Jun 2006 2:03	Modified:	30 Nov 2009 14:42
Reporter:	Arjen Lentz	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Command-line Clients	Severity:	S4 (Feature request)
Version:	any	OS:	Any (any)
Assigned to:		CPU Architecture:	Any
Tags:	Contribution

Description:
As described by Beat Vontobel at http://www.futhark.ch/mysql/144.html, the mysql commandline client can't deal with the shebang script notation since it always reads from stdin.

How to repeat:
#!/usr/bin/mysql
SHOW DATABASES

Suggested fix:
mysql-client-shebang-arjen-2006-06-01.diff adds a --infile option which makes the cmdline client not read from stdin but from the specified filename instead (in batch mode).
With that functionality in place, you can use #!/usr/bin/mysql --infile
The patch was created in a 5.1 tree, but is so trivial that it can probably be applied to any tree.

Note... a shell passes all params after the shebang program to the app in a single parameter, so no username, pwd, socket, or other options can be passed. We could cover that also with some additional handling, but let this just provide the basics.

===== client_priv.h 1.58 vs edited =====
--- 1.58/client/client_priv.h   2006-05-16 08:12:03 +10:00
+++ edited/client_priv.h        2006-06-01 10:31:34 +10:00
@@ -28,6 +28,7 @@
 {
   OPT_CHARSETS_DIR=256, OPT_DEFAULT_CHARSET,
   OPT_PAGER, OPT_NOPAGER, OPT_TEE, OPT_NOTEE,
+  OPT_INFILE,
   OPT_LOW_PRIORITY, OPT_AUTO_REPAIR, OPT_COMPRESS,
   OPT_DROP, OPT_LOCKS, OPT_KEYWORDS, OPT_DELAYED, OPT_OPTIMIZE,
   OPT_FTB, OPT_LTB, OPT_ENC, OPT_O_ENC, OPT_ESC, OPT_TABLES,
===== mysql.cc 1.213 vs edited =====
--- 1.213/client/mysql.cc       2006-05-20 08:08:48 +10:00
+++ edited/mysql.cc     2006-06-01 11:07:04 +10:00
@@ -165,7 +165,7 @@
                            "Aug","Sep","Oct","Nov","Dec"};
 static char default_pager[FN_REFLEN];
 static char pager[FN_REFLEN], outfile[FN_REFLEN];
-static FILE *PAGER, *OUTFILE;
+static FILE *PAGER, *INFILE= stdin, *OUTFILE;
 static MEM_ROOT hash_mem_root;
 static uint prompt_counter;
 static char delimiter[16]= DEFAULT_DELIMITER;
@@ -374,7 +374,7 @@
       strmov(default_pager, tmp);
     }
   }
-  if (!isatty(0) || !isatty(1))
+  if (INFILE != stdin || !isatty(0) || !isatty(1))
   {
     status.batch=1; opt_silent=1;
     ignore_errors=0;
@@ -391,7 +391,7 @@
     exit(1);
   }
   if (status.batch && !status.line_buff &&
-      !(status.line_buff=batch_readline_init(opt_max_allowed_packet+512,stdin)))
+      !(status.line_buff=batch_readline_init(opt_max_allowed_packet+512,INFILE)))
   {
     free_defaults(defaults_argv);
     my_end(0);
@@ -701,6 +701,9 @@
   {"tee", OPT_TEE,
    "Append everything into outfile. See interactive help (\\h) also. Does not work in batch mode.",
    0, 0, 0, GET_STR, REQUIRED_ARG, 0, 0, 0, 0, 0, 0},
+  {"infile", OPT_INFILE,
+   "Read from infile (batch mode).",
+   0, 0, 0, GET_STR, REQUIRED_ARG, 0, 0, 0, 0, 0, 0},
 #ifndef DONT_ALLOW_USER_CHANGE
   {"user", 'u', "User for login if not current user.", (gptr*) &current_user,
    (gptr*) &current_user, 0, GET_STR_ALLOC, REQUIRED_ARG, 0, 0, 0, 0, 0, 0},
@@ -830,6 +833,21 @@
     printf("WARNING: option deprecated; use --disable-tee instead.\n");
     if (opt_outfile)
       end_tee();
+    break;
+  case OPT_INFILE:
+    if (argument == disabled_my_option)
+      INFILE= stdin;
+    else if (argument && strlen(argument))
+    {
+      if (!(INFILE= my_fopen(argument, O_RDONLY | O_BINARY,MYF(0))))
+      {
+        fprintf(stderr, "Can't open infile: %s\n", argument);
+        exit(1);
+      }
+      status.batch= 1;
+      status.add_to_history= 0;
+      set_if_bigger(opt_silent,1);                         // more silent
+    }
     break;
   case OPT_PAGER:
     if (argument == disabled_my_option)

Username/pwd and other options can be handled via the ~/.my.cnf, of course.

Thank you for your bug report. This issue has already been fixed
in the latest released version of that product, which you can download at 
http://www.mysql.com/downloads/

Additional info:

It's already supported. Use

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

Sergei, please specify which versions exactly this was introduced, then pass on to Docs?
I'm afraid your earlier response, which is a mix of "already been fixed in latest released version" and "already supported, is confusing and will not actually help users.
Thanks.

It also seems to me, that the behaviour of the command line client differs for files read via input redirection opposed to the "source" command, for example for some of the commands interpreted by the client (e.g. delimiter). (I'll check this out in detail and will maybe make a separate bug report out of that.)

So I'd really prefer Arien's patch to the "-e source -e" trick, even if it's a nice one (or at least it should be documented, that would've saved me from some hassle - or is it documented somewhere?). And it's not really intuitive anyway. Just not what you're used to from most of the other UNIX command line tools.

But thanks anyway, I'm happy to see that it's possible to do!

The functionality to use several -e on the command line has been there since revision 1.121.1.43 committed 2004-05-05

It may be "in there" but that's the wrong way round. People (like Beat) are specifically looking for a way to use shebang. So, I think this should be in the manual from that angle, and get an indexterm also.
Hence the previous status "need doc info"... I've now assigned it to the Docs team so it may deal with it.
Thanks.

The mysql -e source -e trick cannot be relied on (it fails on
Gentoo Linux, for example), so it's not something we should suggest
in the manual.

Moving this back to Open status.

Seeing that apparently -e source -e is not reliable (and it's ugly anyway), we're back to square one where I submitted the patch. So can we reconsider this contribution and help our users?

There is a SQL syntax supported by Db2 and Postgres for the shebang. It does not neet #! at the beginning and the result is the same:

In Db2
--() { :; }; exec db2 -tf "$0"

In Postgres
--() { :; }; exec psql -f "$0"

References:
http://angocadb2.blogspot.com.co/2018/04/how-to-make-db2-scripts-executable.html
http://rosettacode.org/wiki/Multiline_shebang#PostgreSQL