lefred / mysqlshell-plugins Goto Github PK

View Code? Open in Web Editor NEW

122.0 16.0 48.0 353 KB

Plugins for MySQL Shell

License: GNU General Public License v2.0

Python 100.00%

hacktoberfest

mysqlshell-plugins's Introduction

mysql-shell-ex

A collection of MySQL Shell example plugins (MySQL Shell >= 8.0.22).

After installing this plugin collection into the .mysqlsh/plugins/ folder, all the plugins will be automatically loaded at MySQL Shell startup.

To get help about the individual plugins type \? <plugin>.

MySQL JS> \? schema_utils
NAME
      schema_utils - Schema management and utilities.

DESCRIPTION
      A collection of schema management tools and related utilities that work
      on schemas."

FUNCTIONS
      deleteProcedures([schema][, routine][, session])
            Delete stored procedures.

      help([member])
            Provides help about this object and it's members

      showDefaults(table[, schema][, session])
            Lists the default values of each column in a table.

      showInvalidDates([table][, schema][, session])
            Show Invalid Dates

      showProcedures([schema][, session])
            Lists all stored procedures of either all schemas or a given
            schema.

      showRoutines([schema][, session])
            Show Routines.


MySQL JS> schema_utils.showProcedures()
+----------------+-------------------------------------+
| ROUTINE_SCHEMA | ROUTINE_NAME                        |
+----------------+-------------------------------------+
| sys            | extract_schema_from_file_name       |
| sys            | extract_table_from_file_name        |
...

MySQL JS> \py
Switching to Python mode...

MySQL PY> schema_utils.show_procedures()
+----------------+-------------------------------------+
| ROUTINE_SCHEMA | ROUTINE_NAME                        |
+----------------+-------------------------------------+
| sys            | extract_schema_from_file_name       |
| sys            | extract_table_from_file_name        |
...

Installation

To install this demo on your machine type the following commands after you have installed the MySQL Shell and the git command line tools.

This will clone the repository and copy the files to the right path in order to be automatically loaded on MySQL Shell startup.

macOS / Linux

$ mkdir -p ~/.mysqlsh/plugins
$ git clone https://github.com/lefred/mysqlshell-plugins.git ~/.mysqlsh/plugins

Windows

$ mkdir %AppData%\MySQL\mysqlsh\plugins
$ git clone https://github.com/lefred/mysqlshell-plugins.git %AppData%\MySQL\mysqlsh\plugins

Missing Modules

It might be possible that for some plugins, your are missing some modules. Usually it is python3-requests.

You can then just install it. However, on some systems, the version of Python 3 doesn't match the version linked with MySQL Shell. This is the case with OL7/OL8 where Python 3.6 is installed and MySQL Shell Enterprise build provides Python 3.7.

If this is the case for you, you can install the missing modules within MySQL Shell too using mysqlsh --pym pip.

Example with requests:

For the router and proxySQL plugin, requests is required, if you don't have it installed, when you start MySQL Shell, you will get this message:

Error importing module 'requests', check if it's installed (Python 3.7.7)

You have 2 options, install it system wide (available for all users), if you have root access, or just for your user:

$ sudo mysqlsh --pym pip install requests

$ mysqlsh --pym pip install --user requests

That's it !

Extending the MySQL Shell

Please take a look at the demo/init.py file to learn how to extend the MySQL Shell with plugins.

mysqlshell-plugins's People

Contributors

Stargazers

Watchers

mysqlshell-plugins's Issues

Align columns in error and tail plugin

align the columns for more readability

schema_utils.showDefaults("user","mysql") does not work, see output below

MySQL 127.0.0.1:3311 ssl JS > schema_utils.showDefaults("user","mysql")
+--------------------------------+-----------------+----------------------------------------------------+---------------------------+
| ColumnName | Type | Default | Example |
+--------------------------------+-----------------+----------------------------------------------------+---------------------------+
schema_utils.showDefaults: User-defined function threw an exception:
Traceback (most recent call last):
File "/home/ted/.mysqlsh/plugins/schema/defaults.py", line 50, in show_defaults
query = session.run_sql("select {0}".format(col_expr))
mysqlsh.DBError: MySQL Error (1054): ClassicSession.run_sql: Unknown column 'N' in 'field list'
(ScriptingError)

check.getSlowerQuery() + Y to see explain

Using latest build of plugins and MySQL 8.0.23 produts.

MySQL 127.0.0.1:33110+ ssl JS > check.getSlowerQuery()
*************************** 1. row ***************************
schema_name: mysql
tot_lat: 152.29 ms
exec_count: 1
latency_per_call: 152.29 ms
first_seen: 2021-02-25 13:44:54.964195
last_seen: 2021-02-25 13:44:54.964195
query_sample_text: SELECT BINARY T.table_name AS name, IF(ANY_VALUE(T.table_type) LIKE '%VIEW', IF(COUNT()=1 AND COUNT(CASE WHEN (column_name = 'doc' AND data_type = 'json') THEN 1 ELSE NULL END)=1, 'COLLECTION_VIEW', 'VIEW'), IF(COUNT(CASE WHEN (column_name != '_json_schema') THEN 1 ELSE NULL END)-2 = COUNT(CASE WHEN (column_name != '_id' AND column_name != 'doc' AND column_name != 'json_schema' AND generation_expression RLIKE 'json_extract$doc,([[:alnum:]]+)?\\''\$((\{2})?(\[([[:digit:]]+|\)\]|\.([[:alpha:]\$][[:alnum:]\$]|\|\".\"|.*)))*\\''$') THEN 1 ELSE NULL END) AND COUNT(CASE WHEN (column_name = 'doc' AND data_type = 'json') THEN 1 ELSE NULL END)=1 AND COUNT(CASE WHEN (column_name = 'id' AND generation_expression RLIKE '^json_unquote$json_extract\(doc,([[:alnum:]]+)?\\''\$\._id\\''$\)$') THEN 1 ELSE NULL END)=1, 'COLLECTION', 'TABLE')) AS type FROM information_schema.tables AS T LEFT JOIN information_schema.columns AS C ON (BINARY T.table_schema = C.table_schema AND BIN...
Do you want to have EXPLAIN output? (y/N) y
check.getSlowerQuery: User-defined function threw an exception:
Traceback (most recent call last):
File "/home/ted/.mysqlsh/plugins/check/queries.py", line 84, in get_queries_95_perc
_get_full_details(shell, session, original_query, row[0])
File "/home/ted/.mysqlsh/plugins/check/queries.py", line 18, in _get_full_details
run_and_show(stmt,'vertical')
File "/home/ted/.mysqlsh/plugins/mysqlsh_plugins_common.py", line 73, in run_and_show
result = session.run_sql(stmt)
mysqlsh.DBError: MySQL Error (1064): Session.run_sql: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '..' at line 1
(ScriptingError)

adding screenshot of qep

No module named '_crypt' on Windows

I was attempting MySQL Router REST API on Windows based on slide 24 of https://www.slideshare.net/lefred.descamps/mysql-router-rest-api

The error I received was as follows:
MySQL JS > \py
Switching to Python mode...
MySQL Py > import crypt
Traceback (most recent call last):
File "C:\Program Files\MySQL\MySQL Shell 8.0\lib\Python3.9\lib\crypt.py", line 6, in
import _crypt
ModuleNotFoundError: No module named '_crypt'

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
File "", line 1, in
File "C:\Program Files\MySQL\MySQL Shell 8.0\lib\Python3.9\lib\crypt.py", line 9, in
raise ImportError("The crypt module is not supported on Windows")
ImportError: The crypt module is not supported on Windows

The following was also emitted in mysqlsh.log:
2022-02-23 01:14:34: Warning: Found errors loading plugins, for more details look at the log at: %APPDATA%\MySQL\mysqlsh\mysqlsh.log
2022-02-23 01:23:32: Info: Using credential store helper: C:\Program Files\MySQL\MySQL Shell 8.0\bin\mysql-secret-store-windows-credential.exe
2022-02-23 01:23:32: Info: Setting Python home to 'C:\Program Files\MySQL\MySQL Shell 8.0\lib\Python3.9'
2022-02-23 01:23:32: Info: Loading startup files...
2022-02-23 01:23:32: Info: Loading plugins...
2022-02-23 01:23:32: Error: Error loading Python file '%APPDATA%\MySQL\mysqlsh\plugins\router\init.py':
Execution failed:
Traceback (most recent call last):
File "C:\Program Files\MySQL\MySQL Shell 8.0\lib\Python3.9\lib\crypt.py", line 6, in
import _crypt
ModuleNotFoundError: No module named '_crypt'

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
File "init.py", line 5, in
File "C:\Program Files\MySQL\MySQL Shell 8.0\lib\Python3.9\lib\crypt.py", line 9, in
raise ImportError("The crypt module is not supported on Windows")
ImportError: The crypt module is not supported on Windows

Colorize other server hosts in Cluster error and tail plugin

Colorize the name of the host in the error log or tail plugin with the color assigned to the host when its name appears in other members log

Clone user like mysqluserclone

This is more a question (or suggestion).

I see there is a function user.copy (see user/copy.py), which according to its description is for Copy[ing] a user to another server. However, my aim is to clone a user (e.g. create new user user2 as user1 also copying its grants either on the same or another server) like with the old mysqluserclone utility (see scripts/mysqluserclone.py). I do not think this is possible currently (or is it)? If not possible then would you accept a suggestion for a user.clone function?

image for the wiki and InnoDB Clusrer log

The command sandboxes.stopAll () cannot be completed correctly.

MySQL Shell 8.2.1
mysql 8.0.35

1.I deployed three sandboxes, and the command is as follows.

dba.deploySandboxInstance(3310,{'password':'123456',mysqldOptions:['report_host=192.168.5.130']})
dba.deploySandboxInstance(3320,{'password':'123456',mysqldOptions:['report_host=192.168.5.130']})
dba.deploySandboxInstance(3330,{'password':'123456',mysqldOptions:['report_host=192.168.5.130']})

2 .I carried out the order to stop all Sandboxs.

MySQL  localhost  JS > sandboxes.stopAll()
Stopping sandbox running in port 3310
The MySQL sandbox instance on this host in
3310 will be stopped

Please enter the MySQL root password for the instance 'localhost:3310': ******
Failed to stop sandbox '3310': Dba.stop_sandbox_instance: The connection option 'port' has no value.

Stopping sandbox running in port 3320
The MySQL sandbox instance on this host in
3320 will be stopped

Please enter the MySQL root password for the instance 'localhost:3320': ******
Failed to stop sandbox '3320': Dba.stop_sandbox_instance: The connection option 'port' has no value.

Stopping sandbox running in port 3330
The MySQL sandbox instance on this host in
3330 will be stopped

Please enter the MySQL root password for the instance 'localhost:3330': ******
Failed to stop sandbox '3330': Dba.stop_sandbox_instance: The connection option 'port' has no value.

MySQL DB4:3306 ssl JS > proxy.configure() User-defined function threw an exception: Traceback (most recent call last): File "init.py", line 30, in <lambda> File "/home/vilutuser/.mysqlsh/plugins/proxysql/proxysql.py", line 143, in configure where member_host = 'vilreport14194.local' and member_role='PRIMARY'""") mysqlsh.DBError: MySQL Error (1267): ClassicSession.run_sql: Illegal mix of collations (ascii_general_ci,IMPLICIT) and (utf8_general_ci,SYSCONST) for operation '='

MySQL Shell 8.0.22 and ProxySQL V 2.0.15

proxysql 2.5.0+ can't work with mysql 8.x backend servers

Used the proxysql plugin to configure proxysql 2.5.x with a mysql 8.x innodb cluster, but proxysql failed to connect to the backend servers:

ERROR 9001 (HY000) at line 1: Max connect timeout reached while reaching hostgroup 2 after 10000ms

Checked /var/lib/proxysql/proxysql.log and found the following message:

2023-07-22 18:34:14 MySQL_Monitor.cpp:3745:gr_report_fetching_errs(): [ERROR] Got error. mmsd 0x7f661d860000 , MYSQL 0x7f661d8d0000 , FD 40 : SELECT command denied to user 'monitor'@'mysql1' for table 'replication_group_members'

After granting the access right on performance_schema.* to the monitor user, this issue was fixed.

This issue seems related to the code change since 2.5.0 that added queries to the performance_schema.replication_group_members table, see https://github.com/sysown/proxysql/blob/v2.5.0/lib/MySQL_Monitor.cpp#L498

Is there a manual

MySQL 172.16.8.34:4306 JS > proxy=ext.proxysql.create('[email protected]:6032')
Password: *****
Connected to ProxySQL (2.0.8-67-g877cab1)
{
"configure": ,
"connections": ,
"getUsers": ,
"hostgroups": ,
"hosts": ,
"importUsers": ,
"setUser": ,
"setUserHostgroup": ,
"status": ,
"version":
}
MySQL 172.16.8.34:4306 JS > proxy.configure()
ERROR: Please connect or provide a session to the Primary Master.

ImportError: No module named innodb

2019-10-12 10:27:21: Error: Error loading Python file '/Users/hongbin/.mysqlsh/plugins/ext/innodb/init.py':
Execution failed:
Traceback (most recent call last):
File "init.py", line 6, in
ImportError: No module named innodb

2019-10-12 10:27:21: Error: Error loading Python file '/Users/hongbin/.mysqlsh/plugins/ext/router/init.py':
Execution failed:
Traceback (most recent call last):
File "init.py", line 5, in
File "/Users/hongbin/.mysqlsh/plugins/ext/router/status.py", line 2, in
import requests
ImportError: No module named requests

README references missing extension

https://github.com/lefred/mysqlshell-plugins/blame/master/README.md#L68

Please take a look at the demo/init.py file to learn how to extend the MySQL Shell with plugins.

There is no demo extension:

=> basename $(pwd)
mysqlshell-plugins

=> find . -type d -name demo | wc -l
0

add a screenshot for locking

Configure proxysql without getting password prompt

Plugins are not working with MySQL Shell 8.0.22

There is a new Improved Python Plugin Support in MySQL Shell 8.0.22.

Pluging decorator are not yet supported in those plugins.

Indicate inconsistencies in locks.getAllLocks()

would be nice if the tool could at least somehow indicate inconsistencies it's aware of
say, it sees the performance_schema.data_lock lock_state of lock is "WAITING", but when looking into performance_schema.data_waits it can not find who blocks it (because the wait has finished meanwhile), then it could show some red question mark or "" in "blocked by" column, etc.

heatwave_utils.list_secondary_engine_tables() fails with classic protocol

JS  heatwave_utils.list_secondary_engine_tables('airline')
heatwave_utils.list_secondary_engine_tables: User-defined function threw an exception: 
Traceback (most recent call last):
  File "init.py", line 158, in list_sec_engine_tables
AttributeError: unknown attribute: get_schema
 (ScriptingError)

Unitest

add automated test for each plugins