common_schema 2.2 D B A ' s f r a m e w o r k f o r M y S Q L S h l o m i N o a c h h t t p : / / o p e n a r k . o r g

Copyright © 2014, Shlomi Noach common_schema D B A ' s f r a m e w o r k f o r M y S Q L common_schema 2.2 D B A ' s f r a m e w o r k f o r M y S Q L  About  Views & eval()  Routines  QueryScript  rdebug

D B A ' s f r a m e w o r k f o r M y S Q L common_schema Copyright © 2013, Shlomi Noach About myself  Shlomi Noach, geek  Software engineer, DBA  Author of open source tools common_schema, openark kit, propagator, others.  Blog on all things MySQL http://openark.org  Employed by Outbrain Inc.

D B A ' s f r a m e w o r k f o r M y S Q L common_schema Copyright © 2013, Shlomi Noach About common_schema  common_schema is an open source project, licensed under the GPL License.  Authored by Shlomi Noach http://openark.org  Major contributions by Roland Bouman http://rpbouman.blogspot.com  Several contributions & suggestions by the community (Thanks!)

D B A ' s f r a m e w o r k f o r M y S Q L common_schema Copyright © 2013, Shlomi Noach About common_schema  common_schema is a framework which includes: – Views: analytics, security, action-taking, ... – Routines: text, temporal, process, security, ... – QueryScript: an SQL oriented scripting language & interpreter – rdebug: debugger and debugging API for MySQL stored routines (alpha).

D B A ' s f r a m e w o r k f o r M y S Q L common_schema Copyright © 2013, Shlomi Noach About common_schema  It is a schema that lies next to INFORMATION_SCHEMA.  It lies completely within the MySQL server, and does not require external packages or dependencies. No Perl scripts nor UDFs or plugins.

D B A ' s f r a m e w o r k f o r M y S Q L common_schema Copyright © 2013, Shlomi Noach Getting & Installing  common_schema distribution is a SQL file.  Currently hosted on Google Code: http://code.google.com/p/common-schema/  Install by importing SQL file into MySQL: bash$ mysql < /tmp/common_schema-2.2.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!

D B A ' s f r a m e w o r k f o r M y S Q L common_schema Copyright © 2013, Shlomi Noach Views  Views providing non-trivial information about your table design, keys, grants, processes, transactions, locks, ...  While INFORMATION_SCHEMA provides with complete info, it is ofter difficult to aggregate. It is sometimes too normalized, and at other times too de-normalized.  Some metadata is simply not available in INFORMATION_SCHEMA.

D B A ' s f r a m e w o r k f o r M y S Q L common_schema Copyright © 2013, Shlomi Noach Views  common_schema's views will present with valuable knowledge  And will typically offer recommendations or possible actions based on that knowledge.  common_schema also provides the mechanism to apply those recommendations.

D B A ' s f r a m e w o r k f o r M y S Q L common_schema Copyright © 2013, Shlomi Noach Views: redundant_keys  Find duplicate/redundant keys in your schema. Recommend DROP statements. http://common-schema.googlecode.com/svn/trunk/common_schema/doc/html/redundant_keys.html – Similar to pt-duplicate-key-checker mysql> select * from redundant_keys where table_schema='sakila'\G table_schema: sakila table_name: rental redundant_index_name: rental_date_2 redundant_index_columns: rental_date redundant_index_non_unique: 1 dominant_index_name: rental_date dominant_index_columns: rental_date, inventory_id, customer_id dominant_index_non_unique: 0 sql_drop_index: ALTER TABLE `sakila`.`rental` DROP INDEX `rental_date_2`

D B A ' s f r a m e w o r k f o r M y S Q L common_schema Copyright © 2013, Shlomi Noach Views: sql_range_partitions  Analyze your range partitioned tables. Offers the ADD/REORGANIZE PARTITION statements to roll partitions: mysql> create table test.report … partition by range (…) ; mysql> select * from sql_range_partitions where table_name='report' \G table_schema: test table_name: report count_partitions: 7 sql_drop_first_partition: alter table `test`.`report` drop partition `p0` sql_add_next_partition: alter table `test`.`report` reorganize partition `p6` into ( partition `p_20090701000000` values less than (1246395600) /* 2009-07-01 00:00:00 */ , partition p_maxvalue values less than MAXVALUE )

D B A ' s f r a m e w o r k f o r M y S Q L common_schema Copyright © 2013, Shlomi Noach Views: sql grants  Provide SQL based access to user accounts and their grants.  Offers the relevant GRANT, REVOKE and DROP statements for such grants: GRANTEE: 'world_user'@'localhost' user: world_user host: localhost priv_level: `world`.* priv_level_name: schema current_privileges: INSERT, SELECT, UPDATE sql_grant: GRANT INSERT, SELECT, UPDATE ON `world`.* TO 'world_user'@'%' sql_revoke: REVOKE INSERT, SELECT, UPDATE ON `world`.* FROM 'world_user'@'%'

D B A ' s f r a m e w o r k f o r M y S Q L common_schema Copyright © 2013, Shlomi Noach SQL generation & eval()  Views above present with “SQL columns”, offering a statement to execute.  This is at the heart of common_schema's views, and is part of the server-side mechanism the framework strongly supports.  The eval() routine accepts such SQL columns and executes (evaluates) them.

D B A ' s f r a m e w o r k f o r M y S Q L common_schema Copyright © 2013, Shlomi Noach eval()  Accepts a query returning a text column. Column data is expected to be SQL statements to be executed. mysql> call eval(" SELECT sql_add_next_partition FROM sql_range_partitions WHERE table_schema='webprod'"); Query OK, 0 rows affected -- A new partition has just been added on -- all range-partitioned tables in `webprod`

D B A ' s f r a m e w o r k f o r M y S Q L common_schema Copyright © 2013, Shlomi Noach eval() KILL QUERY 123 KILL QUERY 129 KILL QUERY 598 KILL QUERY 620 KILL QUERY 702 Query eval() Execute query Execute statements

D B A ' s f r a m e w o r k f o r M y S Q L common_schema Copyright © 2013, Shlomi Noach Views: innodb_transactions  Which transactions are running? – For how long? – How long are they being idle? Locked? – What queries are they issuing?  Recommend KILL, apply with eval() mysql> call eval(" SELECT sql_kill_query FROM innodb_transactions WHERE trx_idle_seconds >= 30 "); -- All idle transactions have just been killed

D B A ' s f r a m e w o r k f o r M y S Q L common_schema Copyright © 2013, Shlomi Noach Views: others  processlist_grantees: identifies the user connection with its associated account (missing info in MySQL)  auto_increment_columns: find “free space” or “usage” for AUTO_INCREMENT values  slave_status: get Seconds_behind_master via real SQL query  last_query_profiling :aggregated profile for last executed query

D B A ' s f r a m e w o r k f o r M y S Q L common_schema Copyright © 2013, Shlomi Noach Views: processlist_grantees mysql> select * from processlist_grantees\G ID: 41165491 USER: dbuser HOST: web00.myapp:40049 DB: profile COMMAND: Query TIME: 0 STATE: updating INFO: DELETE FROM locks WHERE id = 'helper' AND dt < '2013-04-17 15:09:50' GRANTEE: 'dbuser'@'%.myapp' grantee_user: dbuser grantee_host: %.myapp is_super: 0 is_repl: 0 is_current: 0 sql_kill_query: KILL QUERY 41165491 sql_kill_connection: KILL 41165491

D B A ' s f r a m e w o r k f o r M y S Q L common_schema Copyright © 2013, Shlomi Noach Views: others  sql_accounts: block/release accounts  similar_grants: detect security roles  global_status_diff_nonzero: detect status change  innodb_locked_transactions: who locks who, on which query? Recommend KILL  more... http://common-schema.googlecode.com/svn/trunk/common_schema/doc/html/process_views.html http://common-schema.googlecode.com/svn/trunk/common_schema/doc/html/innodb_plugin_views.html http://common-schema.googlecode.com/svn/trunk/common_schema/doc/html/security_views.html http://common-schema.googlecode.com/svn/trunk/common_schema/doc/html/schema_analysis_views.html

D B A ' s f r a m e w o r k f o r M y S Q L common_schema Copyright © 2013, Shlomi Noach Routines  common_schema offers more than 60 useful stored routines, part of the framework's function library.  From text parsing & manipulation, through process diagnostics, query analysis & dynamic execution, to security routines, the function library extends and complements MySQL's own functions. http://common-schema.googlecode.com/svn/trunk/common_schema/doc/html/execution_routines.html http://common-schema.googlecode.com/svn/trunk/common_schema/doc/html/text_routines.html http://common-schema.googlecode.com/svn/trunk/common_schema/doc/html/security_routines.html http://common-schema.googlecode.com/svn/trunk/common_schema/doc/html/process_routines.html

D B A ' s f r a m e w o r k f o r M y S Q L common_schema Copyright © 2013, Shlomi Noach Routines: security  killall() kills connections by matching text with user, host or grantee.  security_audit() audits server's privileges tables and configuration to detect such threats as empty or duplicate passwords, excessive privileges, permissive hosts etc.  duplicate_grantee(): duplicates an account mysql> call killall('analytics'); mysql> call killall('localhost');

D B A ' s f r a m e w o r k f o r M y S Q L common_schema Copyright © 2013, Shlomi Noach Routines: text  extract_json_value(): Extract value from JSON notation via Xpath.  replace_sections(): Search and replace text appearing between section.  get_num_tokens(): Return number of tokens in delimited text. mysql> select common_schema.get_num_tokens( 'the quick brown fox', ' '); => 4 mysql> select common_schema.split_token( 'the quick brown fox', ' ', 3); => 'brown'

D B A ' s f r a m e w o r k f o r M y S Q L common_schema Copyright © 2013, Shlomi Noach Routines: text mysql> set @json := '{ "menu": { "id": "file", "value": "File", "popup": { "menuitem": [ {"value": "New", "onclick": "create()"}, {"value": "Open", "onclick": "open()"}, {"value": "Close", "onclick": "close()"} ] } } }'; mysql> select extract_json_value(@json, '//id') AS result; +--------+ | result | +--------+ | file | +--------+

D B A ' s f r a m e w o r k f o r M y S Q L common_schema Copyright © 2013, Shlomi Noach Routines: general  query_checksum(): Checksum the result set of a query.  crc64(): Return a 64 bit CRC of given input, as unsigned big integer.  random_hash(): Return a 64 bit CRC of given input, as unsigned big integer. mysql> call query_checksum( 'select code, name from world.Country order by code'); +----------------------------------+ | checksum | +----------------------------------+ | 79221e8d040e33cd3262f1680b4c8e54 | +----------------------------------+

D B A ' s f r a m e w o r k f o r M y S Q L common_schema Copyright © 2013, Shlomi Noach Routines: SQL & execution  eval() evaluates the queries generated by a given query.  exec(), exec_file() dynamically executes a given query or semicolon delimited list of queries.  run(), run_file() execute QueryScript code. mysql> call exec(' CREATE TABLE test.t(id INT); INSERT INTO test.t VALUES (2),(3),(5); ');

D B A ' s f r a m e w o r k f o r M y S Q L common_schema Copyright © 2013, Shlomi Noach QueryScript  A SQL oriented scripting language, offering tight integration with SQL commands, easy and familiar control flow syntax and high level abstraction of complex tasks.  common_schema implements QueryScript via interpreter, based on stored routines.  This makes QueryScript suitable for administration and bulk tasks, not for OLTP tasks. http://common-schema.googlecode.com/svn/trunk/common_schema/doc/html/query_script.html

D B A ' s f r a m e w o r k f o r M y S Q L common_schema Copyright © 2013, Shlomi Noach Why QueryScript?  Stored routine programming is a pain: – Requires one to actually store the routine within the schema: can't just run something. – Syntax is cumbersome (ANSI:SQL). – Does not offer deeper insight into MySQL's limitations and bottlenecks. – Does not provide with syntax for oh-so- common tasks – Verbose. Can't see the forest for the trees.

D B A ' s f r a m e w o r k f o r M y S Q L common_schema Copyright © 2013, Shlomi Noach Compare: stored routine DELIMITER $$ DROP PROCEDURE IF EXISTS some_proc $$ CREATE PROCEDURE some_proc() READS SQL DATA SQL SECURITY INVOKER begin declare some_id bigint unsigned default null; declare done tinyint default 0; declare my_cursor cursor for SELECT some_id FROM some_table; declare continue handler for NOT FOUND set done = 1; open my_cursor; read_loop: loop fetch my_cursor into some_id; if done then leave read_loop; end if; -- do something with some_id end loop; close my_cursor; end $$ DELIMITER ;

D B A ' s f r a m e w o r k f o r M y S Q L common_schema Copyright © 2013, Shlomi Noach Compare: QueryScript set @script := ' foreach ($some_id: select some_id from some_table) { -- do something with $some_id } '; call run(@script);  Significantly less overhead  Familiar C-family syntax  No need to store the code in schema  Can execute script directly from file call run('/path/to/script.qs');

D B A ' s f r a m e w o r k f o r M y S Q L common_schema Copyright © 2013, Shlomi Noach QueryScript: variables  Create variables which are known to initialize as NULL and are known to clean up as they exit scope.  Can be expanded and used where MySQL does not allow variables. var $count := 20; var $pop := 1000000; var $tbl := 'City'; select * from world.:${tbl} where population >= $pop limit :${count};

D B A ' s f r a m e w o r k f o r M y S Q L common_schema Copyright © 2013, Shlomi Noach QueryScript: conditions  Familiar if, while or loop-while statements.  But conditions are also tightly integrated with SQL, and so queries make for valid conditions. if (@val > 3) { pass; } while (delete from world.Country where Continent = 'Asia' limit 10) { throttle 2; }

D B A ' s f r a m e w o r k f o r M y S Q L common_schema Copyright © 2013, Shlomi Noach QueryScript: foreach  A sophisticated looping device, allowing iteration over queries, sets, numbers range, databases, tables...  But also acknowledges MySQL limitations and provides with a safer access method.  Table iteration uses INFORMATION_SCHEMA optimizations to avoid excessive locks: foreach($tbl, $scm: table like wp_posts) alter table :${scm}.:${tbl} add column post_geo_location VARCHAR(128);

D B A ' s f r a m e w o r k f o r M y S Q L common_schema Copyright © 2013, Shlomi Noach QueryScript: foreach foreach($year: 2001:2009) delete from sakila.rental where rental_date >= CONCAT($year, '-07-01') and rental_date < CONCAT($year, '-09-01'); foreach($shard: {US, GB, Japan, FRA}) create database dbshard_:${shard}; foreach($scm: schema like wp%) { create table :$scm.wp_likes( id int, data VARCHAR(128)); } foreach ($some_id: select some_id from some_table) {... }

D B A ' s f r a m e w o r k f o r M y S Q L common_schema Copyright © 2013, Shlomi Noach QueryScript: split  Automagically breaks a bulk operation into smaller chunks.  Supports DELETE, UPDATE, INSERT...SELECT, REPLACE...SELECT  Supports single and multi table statements split (delete from sakila.rental where rental_date < NOW() - interval 5 year); split (insert into world.City_duplicate select * from world.City) { throttle 1; }

D B A ' s f r a m e w o r k f o r M y S Q L common_schema Copyright © 2013, Shlomi Noach QueryScript: split split(update sakila.film_actor set last_update=now() where actor_id != 4) { select $split_columns as columns, $split_range_start as range_start, $split_range_end as range_end, $split_total_rowcount as total; } +----------------------+-------------+------------+-------+ | columns | range_start | range_end | total | +----------------------+-------------+------------+-------+ | `actor_id`,`film_id` | '1','1' | '39','293' | 978 | +----------------------+-------------+------------+-------+ +----------------------+-------------+------------+-------+ | columns | range_start | range_end | total | +----------------------+-------------+------------+-------+ | `actor_id`,`film_id` | '39','293' | '76','234' | 1978 | +----------------------+-------------+------------+-------+ +----------------------+-------------+-------------+-------+ | columns | range_start | range_end | total | +----------------------+-------------+-------------+-------+ | `actor_id`,`film_id` | '76','234' | '110','513' | 2978 | +----------------------+-------------+-------------+-------+

D B A ' s f r a m e w o r k f o r M y S Q L common_schema Copyright © 2013, Shlomi Noach QueryScript: split, example  Real world use case: need to copy 120,000,000 rows belonging to a specific partition from range-partitioned table 2013-02 2013-03 2013-04 2013-05 2013-06 2013-05

D B A ' s f r a m e w o r k f o r M y S Q L common_schema Copyright © 2013, Shlomi Noach QueryScript: split, example split ( { index: PRIMARY, start: '2013-05-01', step: 20000 } : insert into one_month_sample select * from table_with_many_month_partitions where entry_date < DATE('2013-06-01') ) { if ($split_range_start >= DATE('2013-06-01')) Break; throttle 1; }  We provide hints to the split statement: choosing index, start position, chunk size.  Terminate when out of range.

D B A ' s f r a m e w o r k f o r M y S Q L common_schema Copyright © 2013, Shlomi Noach QueryScript: example  Create shards & tables, and copy row data: -- iterate shards foreach ($shard: {US, GB, Japan, FRA}) { create database dbshard_:${shard}; -- iterate tables foreach ($tbl: table in original_shard) { create table dbshard_:${shard}.:${tbl} like original_shard.:$tbl; -- copy table data split (insert into dbshard_:${shard}.:${tbl} select * from original_shard.:$tbl) { -- show progress select $shard, $tbl, $split_total_rowcount; throttle 1; } } }

D B A ' s f r a m e w o r k f o r M y S Q L common_schema Copyright © 2013, Shlomi Noach QueryScript: example  Protect against malfunctioning 3rd party locking code: call common_schema.run(" while( 1 ) { sleep 5; try { eval select min(sql_kill_blocking_connection) from common_schema.innodb_locked_transactions group by locking_trx_mysql_thread_id having count(*) > 10; } catch {pass;} } ");

D B A ' s f r a m e w o r k f o r M y S Q L common_schema Copyright © 2013, Shlomi Noach QueryScript: example  Create 10 years worth of partitions: CREATE TABLE my_document ( my_document_id int unsigned NOT NULL AUTO_INCREMENT, creation_date DATETIME NOT NULL, data VARCHAR(127), PRIMARY KEY (my_document_id, creation_date) ) PARTITION BY RANGE (to_days(creation_date)) (PARTITION p_2006_01_01 VALUES LESS THAN (732677), PARTITION p_2006_02_01 VALUES LESS THAN (732708), PARTITION p_2006_03_01 VALUES LESS THAN (732736), PARTITION p_2006_04_01 VALUES LESS THAN (732767) ); call common_schema.run(" foreach($i : 1:240) { eval select sql_add_next_partition from sql_range_partitions where table_name='my_document'; } ");

D B A ' s f r a m e w o r k f o r M y S Q L common_schema Copyright © 2013, Shlomi Noach QueryScript: more goodies  throttle statement controls script execution time, reduces server load  Local variables auto-cleanup, can be used (expanded) in statements where variables not allowed (table names, LIMIT value etc.)  try-catch statement is available: easy error handling mechanism  echo, eval, throw statements make for easy development

D B A ' s f r a m e w o r k f o r M y S Q L common_schema Copyright © 2013, Shlomi Noach rdebug  common_schema introduces rdebug: debugger and debugging API for MySQL stored routines.  A server side solution, based in itself on stored routines.  Provides routine API calls to manage debugging sessions of other routines.  At this time (April 2013) in alpha stage http://common-schema.googlecode.com/svn/trunk/common_schema/doc/html/rdebug.html

D B A ' s f r a m e w o r k f o r M y S Q L common_schema Copyright © 2013, Shlomi Noach rdebug  Uses code injection; one must “compile” her routine with/out debug code. – Does not emulate code; retains logic of one's routines, with some limitations.  Supports: – Step into/over/out – Watch variables, modify variables – Set breakpoint, run to breakpoint – Watch stack state, statements

D B A ' s f r a m e w o r k f o r M y S Q L common_schema Copyright © 2013, Shlomi Noach rdebug  common_schema provides with stored routine API. – One may debug on command line using mysql client – Or one may wrap with GUI tool to call upon routine API  Awaiting GUI client implementations!

D B A ' s f r a m e w o r k f o r M y S Q L common_schema Copyright © 2013, Shlomi Noach rdebug mysql [debugger]> call rdebug_step_into(); +-------------+----------------+---------------+--------------+---------------------+ | stack_level | routine_schema | routine_name | statement_id | entry_time | +-------------+----------------+---------------+--------------+---------------------+ | 1 | test | review_colors | 145 | 2013-04-08 15:41:28 | +-------------+----------------+---------------+--------------+---------------------+ +----------------+---------------+---------------+---------------+----------------+ | routine_schema | routine_name | variable_name | variable_type | variable_value | +----------------+---------------+---------------+---------------+----------------+ | test | review_colors | current_color | local | white | | test | review_colors | current_count | local | 10 | | test | review_colors | done | local | 0 | +----------------+---------------+---------------+---------------+----------------+ +----------------+---------------+--------------+-----------------------------------------+ | routine_schema | routine_name | statement_id | statement | +----------------+---------------+--------------+-----------------------------------------+ | test | review_colors | 145 | call review_single_color(current_color) | +----------------+---------------+--------------+-----------------------------------------+ mysql [debugger]> call rdebug_set_variable('current_color', 'orange'); mysql [debugger]> call rdebug_step_over();

D B A ' s f r a m e w o r k f o r M y S Q L common_schema Copyright © 2013, Shlomi Noach call for help()  common_schema documentation is available in these formats: – Online documentation (directly in code repository) – Bundled HTML download – Inline: Just call for help(): call help('split'); +--------------------------------------------------------------------------------+ | help | +--------------------------------------------------------------------------------+ | QueryScript Flow Control: split statement | | | | SYNOPSIS | | | | Single table operations, autodetect mode:

D B A ' s f r a m e w o r k f o r M y S Q L common_schema Copyright © 2013, Shlomi Noach Roadmap & future plans  Everything is developed by demand. There are a lot of pending ideas.  Priority given to solutions I need today.  Nevertheless: – QueryScript will evolve. Already pending release are functions() – QueryScript debugging on roadmap – Schema snapshots and comparisons – Other ideas

D B A ' s f r a m e w o r k f o r M y S Q L common_schema Copyright © 2013, Shlomi Noach Support common_schema  Download and try it out  Report issues http://code.google.com/p/common-schema/issues/list  Happy to receive ideas and contributions http://bit.ly/UPC3vh http://code.google.com/p/common-schema/issues/entry? template=Request%20for%20new%20component  Above all else: spread the word!

D B A ' s f r a m e w o r k f o r M y S Q L common_schema Copyright © 2013, Shlomi Noach Thank you!  Visit http://openark.org for news & updates.  Other open source projects: – openark kit http://openarkkit.googlecode.com/svn/trunk/openarkkit/doc/html/introduction.html – propagator https://github.com/outbrain/propagator