Command history in mysql client only showing last line of multiline query

Question

I'm using Mysql's command-line client in Screen/Tmux, from Bash in OSX's Terminal.app. When using arrow-up to re-display a previously run query that spanned more than on line, AND when the cursor is on the very last line of the Terminal, the command in mysql's command history gets 'truncated', or cut off. This never happens when I use the same tools on my Ubuntu workstation

Here's a visual representation of what happens:

Typing some query; nothing wrong here.

+-------------------------------------------+
|mysql>                                     |
|mysql>                                     |
|mysql>                                     |
|mysql>select * from tables where legs = 4 a|
|nd colour = 'green';                       |
+-------------------------------------------+

Run it, results are displayed:

+-------------------------------------------+
|| 2 | ....... | ..... |                    |
|+---+---------+-------+                    |
| x rows in set (0.00 sec)                  |
|                                           |
|mysql>                                     |
+-------------------------------------------+

Hitting [arrow-up] to re-display last query leaves me with:

+-------------------------------------------+
|| 2 | ....... | ..... |                    |
|+---+---------+-------+                    |
| x rows in set (0.00 sec)                  |
|                                           |
|nd colour = 'green';                       |
+-------------------------------------------+

Hit [arrow-up] again, I get:

+-------------------------------------------+
|| 2 | ....... | ..... |                    |
|+---+---------+-------+                    |
| x rows in set (0.00 sec)                  |
|mysql>select * from tables where legs = 4 a|
|nd colour = 'green';                       |
+-------------------------------------------+

Could the be solved by changing a setting? Or is it a bug in Mysql's client?

Software versions:

OSX 10.7.3
Terminal Version 2.2.2 (303)
GNU bash, version 3.2.48(1)-release (x86_64-apple-darwin11)
mysql  Ver 14.14 Distrib 5.5.19, for osx10.7 (i386) using readline 5.1
Screen version 4.00.03 (FAU) 23-Oct-06
tmux 1.6

Answer 1

You have a few options I can think of to make your life easier in this regard:

• readline commands combined with mysql options: readline accepts commands similar to basic emacs commands (can be set to vi as well), or example CTRL-a returns you to the start of a line.

  this combined with set horizontal-scroll-mode On, a readline that you set in ~/.inputrc, which allows you to get your input in 1 straight line, putting your cursor at the very end. Combine it with CTRL-a to immediately jump to the beginning and it's quite convenient.

  ~/.inputrc:

  $if Mysql
      set horizontal-scroll-mode On`
      # uncomment the commands below to use vi keybindings
      #set keymap vi
      #set editing-mode vi
  $endif
  

  (some systems, OSX 10.5 for certain I believe use libedit instead of readline, in which case you need to put it all in ~/.editrc, if you're not sure about your system issue the mysql --version command )

  To look for commands you issued you also have CTRL-r, which allows you to type in a term and your history will be searched for the last occurence.

  interesting commands are:

  • CTRL-P go to the Previous command in your history
  • CTRL-N go to the Next command in your history
  • CTRL-R Reverse-search through your history
  • CTRL-S Search forward through your history
  • CTRL-A Move the cursor to the beginning of the line
  • CTRL-E Move the cursor to the end of the line
  • CTRL-W delete a Word backwards
  • ALT-D delete a word forwards
  • CTRL-F move the cursor Forward 1 character
  • CTRL-B move the cursor Backward 1 character
  • ALT-F move the cursor Forward 1 word
  • ALT-B move the cursor Backward 1 word
  • ALT-_ undo
    
    
    

  Depending on your shell and the underlying they might not all work or be intercepted though. For example on Konsole, which I use on kde, I had to disable flow-control in advanced settings to allow for CTRL-s amongst others.

  Finally mysql also gives you the \e command which allows you to edit your commands in your general file editor, if vi or emacs isn't your thing, try nano, it's easy and works well. The main disadvantage of using this is that when scrolling up newlines are ignored bu tabs and spaces aren't. It's a unix only thing but OS X should do fine there, can't check since I don't own any Apple computers, sorry. :)

  To more easily use this command you could put a readline string macro in ~/.inputrc e.g. Control-o: "\\e;\n" would bind CONTROL-o to \e; followed by enter for instant execution. (look at the Keybindings Section)
  
  

• use an alternate shell: such as altSQL: it gives syntax colouring, nice history scrolling and some other niceties, big bonus is that you have the source so you can adapt what you want.
  
  

• drop the shell and go GUI: Finally I'd like to plug the nice MySQL workbench to work on your databases, it's cross-platform, free and in my humble opinion a nice tool to work with.

I realise it's not a perfect solution and each have advantages and drawbacks but I hope this has helped you along somewhat.