shlomi-noach / common_schema

DBA's framework for MySQL

common_schema

DBA's framework for MySQL

common_schema Documentation

common_schema is a framework for MySQL server administration. It provides with:

• A function library (text functions, security routines, execution and flow control, more...)
• A set of informational and analysis views (security, schema design, processes, transactions, more...)
• • QueryScript interpreter, allowing for server side scripting.
• • rdebug: a debugger and debugging API for MySQL stored routines (alpha)

It introduces SQL based tools which simplify otherwise complex shell and client scripts, allowing the DBA to be independent of operating system, installed packages and dependencies.

It is a self contained schema, compatible with all MySQL >= 5.1 servers. Installed by importing the schema into the server, there is no need to configure nor compile. No special plugins are required, and no changes to your configuration.

common_schema has a small footprint (under 1MB).

The common_schema is to MySQL as jQuery is to javaScript

Baron Schwartz, High Performance MySQL, 3rd Edition

Documentation

A thorough guide to routines, views, the QueryScript language

Bug reports

Migrated from google code, please now refer to Issues

Requirements

common_schema supports MySQL 5.1, 5.5, 5.6. It supports Percona Server & MariaDB.

common_schema enables features, upon installation, according to available server features.

On some versions you may need to set stack_size = 256K in your MySQL configuration file (256K is the default value as of MySQL 5.5)

License

As of July 2016, common_schema is licensed under the MIT license (Previously GPL and the New BSD License).

common_schema's Issues

MySql 8 Support

Is there any intention of making this work for mySql 8?

describe/automate updating `gh-pages` after documentation is updated on `master`

At the moment, the documentation is written in the master branch in /doc/. However, for github.io Project Pages, the gh-pages branch is used.

For now, I just manually merged all doc/html files in the gh-pages branch, but this process should be documented/automated.

MySQL 5.7: Unknown column 'password' in 'field list'

I'm getting the following on MySQL 5.7:

ERROR 1054 (42S22) at line 12498 in file: 'structure/common-schema.sql': Unknown column 'password' in 'field list'

Looks like they renamed the password column to authentication_string.

json_to_xml returns NULL if text value begins with -

When using json_to_xml parse fails (returns NULL) if there is a text value beginning with "-" signal.

Doesn't work:

select common_schema.json_to_xml('{"key":"-123456"}')
select common_schema.json_to_xml('{"key":"-abcde"}')

Work:
select common_schema.json_to_xml('{"key":-123456}')

change urls from `code.google.com` to `github.com` in `doc/html/documentation.html`

At the moment, this repository just converted from code.google.com. the urls still link code.google.com.

Escaped double quotes don't work in json_to_xml

SQL: select common_schema.json_to_xml('{"key":"ab"cd"}')
Expected result: ab\cd
Current result: NULL

The problem is in _get_json_token line 166, as substr won't find the "" char as it expects.

Don't know how to solve it yet.

ERROR 1171 (42000) at line 195: All parts of a PRIMARY KEY must be NOT NULL; if you need NULL in a key, use UNIQUE instead

Hi, i'm trying install common_schema in mysql 5.7.24 but i'm getting this error: ERROR 1171 (42000) at line 195: All parts of a PRIMARY KEY must be NOT NULL; if you need NULL in a key, use UNIQUE instead.

How to build the project?

@shlomi-noach would you mind to give a quick overview of the build requirements?

Returns NULL, if the value is empty object/array

When the value is empty, the parser fails and returns NULL.
In the example below name is empty object. So when you try to extract the value, it returns null. The behaviour is the same if its an empty array as well.

{ "menu": { "id": "file", "value": "File", "name" : {}, "popup": { "menuitem": [ {"value": "New", "onclick": "CreateNewDoc()"}, {"value": "Open", "onclick": "OpenDoc()"}, {"value": "Close", "onclick": "CloseDoc()"} ] } } }

SQL Command used:
SELECT common_schema.extract_json_value(@json, '//menu//value') AS result;

foreach error on 10.0.22-MariaDB

Executing this:

CALL foreach(
"SELECT TABLE_SCHEMA
FROM INFORMATION_SCHEMA.TABLES WHERE table_name LIKE 'devices'",
"SELECT username, group, acl_group_id FROM ${1}.users");

MySQL Version : 10.0.21-MariaDB ---> works fine

MySQL Version : 10.0.22-MariaDB ---> error:

1 queries executed, 0 success, 1 errors, 0 warnings

Query: CALL foreach( "SELECT TABLE_SCHEMA FROM INFORMATION_SCHEMA.TABLES WHERE table_name LIKE 'devices'", "SELECT username, `grou...

Error Code: 1644
QueryScript error: [Unsupported token: "startg"] at 7: "SELECT
username, group, acl_group_id FROM abm.users "

ERROR 1305 (42000): PROCEDURE *.run does not exist

HI Shlomi,

thanks for the great tool.I installed it fine with:

[martin@pozitano ~]$ sandboxes/msb_5_6_28/use -u root < software/common_schema-2.3.sql 
complete
- Base components: installed
- InnoDB Plugin components: installed
- Percona Server components: not installed
- TokuDB components: partial install: 1/2

Installation complete. Thank you for using common_schema!
[martin@pozitano ~]$ sandboxes/msb_5_6_28/use -u root 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 17
Server version: 5.6.28 MySQL Community Server (GPL)

Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

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

mysql [localhost] {root} ((none)) > SELECT * FROM common_schema.status \G
*************************** 1. row ***************************
                       project_name: common_schema
                            version: 2.2
                           revision: 0
                       install_time: 2017-05-31 22:27:37
                    install_success: 1
          base_components_installed: 1
 innodb_plugin_components_installed: 1
percona_server_components_installed: 0
        tokudb_components_installed: 0
              install_mysql_version: 5.6.28
                   install_sql_mode: NO_AUTO_VALUE_ON_ZERO,NO_ENGINE_SUBSTITUTION
1 row in set (0.00 sec)

and after trying to test it i got this error:

mysql [localhost] {root} (event_scheduler) > call run(" while (DELETE FROM events_for_processing_ipgw1 WHERE EventExecutionTime < NOW() - INTERVAL 3 DAY limit 1000)        {                                                                                                                                                                             throttle 2;                                                                                                                                                           }                                                                                                                                                                       ");
ERROR 1305 (42000): PROCEDURE event_scheduler.run does not exist

Can you please advise what is causing this error?

Risks page still mentions GPL licensing

http://shlomi-noach.github.io/common_schema/risks.html

Installation issues on Percona Server 5.6

I installed version 2.3 on Percona Server 5.6.25. The installation summary reported that the Percona Server components were not installed even though Percona is installed. The summary reported that TokuDB components were partially installed even though TokuDB is not installed.

mysql < common_schema-2.3.sql
complete
- Base components: installed
- InnoDB Plugin components: installed
- Percona Server components: not installed
- TokuDB components: partial install: 1/2

# rpm -qa | grep ^Percona
Percona-Server-server-56-5.6.25-rel73.1.el6.x86_64
Percona-Server-client-56-5.6.25-rel73.1.el6.x86_64
Percona-Server-devel-56-5.6.25-rel73.1.el6.x86_64
Percona-Server-shared-56-5.6.25-rel73.1.el6.x86_64

Server version:     5.6.25-73.1-log Percona Server (GPL), Release 73.1, Revision 07b797f
CentOS release 6.6 (Final)

Broken characters in 2.3 release

Moving on from #1
I mentioned some messed chars in text strings of 2.3 release.
Here is an example diff for 1 line:

-Show info for ''gromit''@''localhost'' account:
+Show info for ''�''g�gr�ro�om�mi�it�t''�''@�@''�''l�lo�oc�ca�al�lh�ho�os�st�t''�'' account:

Nothing critical, but would be nice if fixed.