rlwrap: mysql autocompletion not working

Question

Normally, autocompletion works fine in mysql command-line client (mysql-client-5.5). I have a table called booking. When I type the letter b and pres TAB the name of my table gets autocompleted:

SELECT * FROM b<TAB>

However, when I use rlwrap (alias mysql='/usr/bin/rlwrap -s 99999 -a -pRED /usr/bin/mysql'), autocompletion stops working.

It looks as if rlwrap is interfering with the autocompletion feature.

I am using Debian Wheezy.

UPDATE 1:

When I start my command with strace, ie:

strace /usr/bin/rlwrap -s 99999 -a -pRED /usr/bin/mysql --auto-rehash

I see following error

access("/usr/share/rlwrap/completions/mysql", R_OK) = -1 ENOENT (No such file or directory)

could it be, that rlwrap needs some file with mysql completions?

UPDATE 2

OK, I have created the file /usr/share/rlwrap/completions/mysql and put 3 sample lines there:

select
from
where

Now when I start mysql and type sele<TAB>, the tab actually completes the word select. That is progress, but that does not solve the whole problem. I mainly need autocompletion to help me complete obscure table names which I don't (want to) remember. But I cannot edit the autocomplete file every time I change/add/rename a table.

Is it not possible to tell rlwrap to use the same autocomplete file as mysql does? (I presume mysql must use some such file)

UPDATE 3:

following are the other errors from strace

access("/etc/ld.so.nohwcap", F_OK)      = -1 ENOENT (No such file or directory)
access("/etc/ld.so.preload", R_OK)      = -1 ENOENT (No such file or directory)
access("/etc/ld.so.nohwcap", F_OK)      = -1 ENOENT (No such file or directory)
access("/etc/ld.so.nohwcap", F_OK)      = -1 ENOENT (No such file or directory)
access("/etc/ld.so.nohwcap", F_OK)      = -1 ENOENT (No such file or directory)
access("/etc/ld.so.nohwcap", F_OK)      = -1 ENOENT (No such file or directory)
access("/etc/ld.so.nohwcap", F_OK)      = -1 ENOENT (No such file or directory)
access("/etc/ld.so.nohwcap", F_OK)      = -1 ENOENT (No such file or directory)
stat("/home/martin/.terminfo", 0x7fff27ee6a70) = -1 ENOENT (No such file or directory)
access("/etc/terminfo/x/xterm-256color", R_OK) = -1 ENOENT (No such file or directory)
access("/home/martin/.mysql_completions", R_OK) = -1 ENOENT (No such file or directory)
access("/usr/share/rlwrap/completions/mysql", R_OK) = -1 ENOENT (No such file or directory)
connect(4, {sa_family=AF_FILE, path="/var/run/nscd/socket"}, 110) = -1 ENOENT (No such file or directory)
connect(4, {sa_family=AF_FILE, path="/var/run/nscd/socket"}, 110) = -1 ENOENT (No such file or directory)
access("/etc/ld.so.nohwcap", F_OK)      = -1 ENOENT (No such file or directory)
access("/etc/ld.so.nohwcap", F_OK)      = -1 ENOENT (No such file or directory)
access("/etc/ld.so.nohwcap", F_OK)      = -1 ENOENT (No such file or directory)
access("/etc/ld.so.nohwcap", F_OK)      = -1 ENOENT (No such file or directory)
stat("/home/martin/.inputrc", 0x7fff27ee7710) = -1 ENOENT (No such file or directory)

Answer 1

This will enable completion on plain mysql server:

mysql --auto-rehash

For rlwrap, check if you have RLWRAP_HOME set. See here for more info. The page also reports possible race conditions if your system is too busy.

Answer 2

Edit my.cnf:

[mysql]
auto-rehash

If that doesn't work, add it to debian.cnf instead.

Even you can do it:

mysql> \#

Answer 3

I found this bit on a FreeBSD forum but it should work for you in your case as well. The forum thread was titled: Why no autocomplete in mysql with bash?.

The solutions mentioned in that thread are as follows.

Option #1 - via mysql client

$ mysql --auto-rehash -p

This is directly out of the mysql man page.

--auto-rehash

Enable automatic rehashing. This option is on by default, which enables 
database, table, and column name completion. Use --disable-auto-rehash to 
disable rehashing. That causes mysql to start faster, but you must issue the 
rehash command if you want to use name completion.

To complete a name, enter the first part and press Tab. If the name is 
unambiguous, mysql completes it. Otherwise, you can press Tab again to see 
the possible names that begin with what you have typed so far. Completion 
does not occur if there is no default database.

Option #2 - via the server

You can also fix this on the server end by commenting out that same feature line in the server's /etc/my.cnf file. It depends on where your my.cnf file came from. MySQL provides several out of the box sample .cnf files.

Example

This is on my CentOS system buy your Debian system likely has these as well.

$ rpm -ql mysql-server |grep cnf 
/usr/share/doc/mysql-server-5.0.95/my-huge.cnf
/usr/share/doc/mysql-server-5.0.95/my-innodb-heavy-4G.cnf
/usr/share/doc/mysql-server-5.0.95/my-large.cnf
/usr/share/doc/mysql-server-5.0.95/my-medium.cnf
/usr/share/doc/mysql-server-5.0.95/my-small.cnf
/usr/share/mysql/my-huge.cnf
/usr/share/mysql/my-innodb-heavy-4G.cnf
/usr/share/mysql/my-large.cnf
/usr/share/mysql/my-medium.cnf
/usr/share/mysql/my-small.cnf

If one of these files is the basis for your /etc/my.cnf file then you may have this line enabled within it. For example these files have it enabled:

$ grep rehash /usr/share/doc/mysql-server-5.0.95/*
/usr/share/doc/mysql-server-5.0.95/my-huge.cnf:no-auto-rehash
/usr/share/doc/mysql-server-5.0.95/my-innodb-heavy-4G.cnf:no-auto-rehash
/usr/share/doc/mysql-server-5.0.95/my-large.cnf:no-auto-rehash
/usr/share/doc/mysql-server-5.0.95/my-medium.cnf:no-auto-rehash
/usr/share/doc/mysql-server-5.0.95/my-small.cnf:no-auto-rehash

So you'll want to comment this line out if you have it enabled in your /etc/my.cnf file, like so:

#no-auto-rehash

Your alias

Given the method you're employing to call the mysql client using rlwrap you could append the --auto-hash -p switches to it like so:

$ alias mysql='/usr/bin/rlwrap -s 99999 -a -pRED /usr/bin/mysql --auto-rehash -p'