Thursday, October 17

A bad feature in mysql command line tool: auto-completion

One feature in the mysql command line tool is very annoying: The double tab is being interpreted as a request to auto-completion.

For example, in your editor (or workbench) you have a perfect sql:
SELECT * FROM  `abc`
WHERE id =  '6690-2'
AND result >300
order by result;
This is a legitimate sql, and it runs pretty well in workbench. But when you copy it into mysql command line tool, you get this:
mysql> select * from abc
    -> where id='6690-2'
Display all 1437 possibilities? (y or n)
    -> D result >300
    -> order by result;
What happened? The double tab before "AND" was interpreted as a request to auto-completion, so the application replied to ask whether you want to show all the auto-completion options (1437 of them). The application still accept key in "A" and "N", then decided the "N" means you don't want to see the 1437 options, so it stops the interruption, that is why you have "D" in the next line.

Yes that is annoying. I can't even maintain proper indents in my source file. It took me some time to look for tab-space auto-conversion in MySQL Workbench, as any decent editor would have this feature, but failed.

The solution is easy, and it is actually spelled out every time you change database in my mysql command line tool:

mysql> use import_temp;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
After starting the tool using -A option, the annoying feature is disabled.

So I added an alias to use -A all the time:
alias mysql 'mysql -A'
(and add this into .bashrc)

PS: To show warnings by default, change the line to:
alias mysql 'mysql -A --show-warnings'