shlomi-noach / common_schema

DBA's framework for MySQL

Software engineer and database geek

I am enthusiast about writing software and solving problems. I’ve worked with big, busy and highly available systems and services. I in particular enjoy sharing my solutions with the world via blogging (http://code.openark.org/blog/) and via open source.

I author or authored a bunch of open source projects:

• orchestrator
• gh-ost
• freno
• common_schema
• ccql
• zookeepercli
• and some more.

I curate a list of MySQL quality open source software: awesome-mysql

Recipient of MySQL Community Member of the Year, Oracle ACE, and Oracle Technologist of the Year award, I'm involved in the MySQL ecosystem community. I frequently present on conferences, see https://code.openark.org/blog/presentations for a collection of some of my recorded presentations + slide decks.

Some blog posts from my role when working as a database engineer at GitHub:

• Automating MySQL schema migrations with GitHub Actions and more
• MySQL High Availability at GitHub
• Mitigating replication lag and reducing read load with freno
• MySQL infrastructure testing automation at GitHub
• gh-ost: GitHub’s online schema migration tool for MySQL

My take on CAP Theorem: not what we thought it was, not what we are looking for.

The peak of my carreer is the infamous SQL Pie Chart query.

common_schema's Issues

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.

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)

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}')

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;

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.

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.

Risks page still mentions GPL licensing

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

How to build the project?

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

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.

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 "

MySql 8 Support

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

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.

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 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?