my cli (mycli)

Created: 2018-05-08 14:17:11 -0700 Modified: 2019-04-13 10:05:53 -0700

Basics (GitHub)

my-cli is a command-line interface for MySQL that provides features like autocomplete, paginated tables, etc.

To install it, I used pip (reference).

Built-in commands
- help - see all other commands
- rehash - rebuild the autocompletions in case you added a table from outside of my-cli while my-cli is running

Troubleshooting

Can’t see non-UTF8 characters like emojis without crashing

[16:22] HiDeoo: adam13531 From one of their issue, if you have Python 3 and it being configured as default Python version, mycli should use it when reinstalling and it should work vs Python 2.X

[16:39] HiDeoo: adam13531 Just tested the mycli re-installation using Python 3, it’s not perfect but it’s better aka it’s doesn’t error or fail but still doesn’t show the emoji, it displays ”??” instead.

Collation issue (reference)

On 5/8/2018, I ran into a problem where I made a stored procedure using knex.js on a database whose collation is set as such:

mysql root@localhost:botland> SHOW VARIABLES LIKE 'collation%';
+----------------------+--------------------+
| Variable_name | Value |
+----------------------+--------------------+
| collation_connection | utf8_general_ci |
| collation_database | utf8mb4_unicode_ci |
| collation_server | utf8_general_ci |
+----------------------+--------------------+

However, from my-cli, I got this error:

mysql root@localhost:botland > call getUser(‘Adam’);

(1267, “Illegal mix of collations (utf8mb4_general_ci,IMPLICIT) and (utf8mb4_unicode_ci,IMPLICIT) for operation ’=’“)

The way that I worked around this was based on this StackOverflow post that says to add the proper collation directly to the stored procedure:

const procedure = `
  CREATE PROCEDURE \getUser\ (IN userName VARCHAR(15))
  BEGIN
  SELECT id, name, num_logins, creation_date, computer_level, last_login_date
  FROM users
  WHERE name like userName
  COLLATE utf8mb4_unicode_ci;
  END
  `;
  const createGetUserPromise = knex.schema.raw(procedure);

Apparently I could have also changed my.cnf:

14:19 HiDeoo: Adam13531 It would have worked if your my.cnf contains utf8mb4 for the client default character set

[client]

default-character-set = utf8mb4