MariaDB client has no prompt in Emacs sql-mode

Question

I just upgraded from an old MySQL client to mariadb-clients-10.0.21-3 on Arch Linux. After the upgrade, I no longer see a prompt when using Emacs's sql-mysql function.

It seems mysql is buffering the prompt, because it shows up in the first line of output:

Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MySQL connection id is 19662
Server version: 4.1.11-standard-log

Copyright (c) 2000, 2015, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

show tables;
MySQL [dbname]> +---------------------------------------------------------+
| Tables_in_dbname                                           |
+---------------------------------------------------------+
...
+---------------------------------------------------------+
80 rows in set (0.02 sec)

help
MySQL [dbname]> 
General information about MariaDB can be found at
http://mariadb.org

List of all MySQL commands:
...
For server side help, type 'help contents'

?
MySQL [dbname]> 
General information about MariaDB can be found at
http://mariadb.org

List of all MySQL commands:
...
For server side help, type 'help contents'

exit
MySQL [dbname]> Bye

In all cases, the line before "MySQL [dbname]>" is what I typed. (... indicates output I omitted.)

How can I get the prompt to display properly? I've tried the -n option of mysql; it had no effect. If I run mysql in a terminal, it works fine.

Answer 1

You forgot to escape the special characters. Elisp-regex is quite backslash heavy since the first \ is swallowed by the lisp string. See https://www.emacswiki.org/emacs/RegularExpression

To catch MariaDB and MySQL I have this in my config:

(sql-set-product-feature 'mysql :prompt-regexp "^\\(MariaDB\\|MySQL\\) \\[[_a-zA-Z]*\\]> ")

Answer 2

If adding to .emacs, do so as follows:

(require 'sql)
(sql-set-product-feature 'mysql :prompt-regexp 
            "^\\(MariaDB\\|MySQL\\) \\[[_a-zA-Z]*\\]> ")

The (require 'sql) is needed for sql-set-product-feature function to become globally available.

Answer 3

The problem turns out to be that the prompt doesn't match what sql-mode is expecting. MariaDB uses "MySQL [dbname]> " as its default prompt, and sql-mode only allows "mysql> ".

So one fix is to add "--prompt=mysql> " to sql-mysql-options:

(setq sql-mysql-options '("--prompt=mysql> "))

A better one would be to fix the regexp to allow either prompt style. But I'm having some difficulty getting that to work, so I'll award the bounty if someone posts how to do that.

I've tried

(sql-set-product-feature 'mysql :prompt-regexp "^[mM]y[sS][qQ][lL][^>]*> ")

but it doesn't work unless the prompt is "mysql>" or "MySQL>".