Tag: Open Source

common_schema rev. 218: QueryScript, throttling, processes, documentation

MySQL

common_schema, revision 218 is released, with major new features, top one being server side scripting. Here are the highlights:

  • QueryScript: server side scripting is now supported by common_schema, which acts as an interpreter for QueryScript code.
  • Throttling for queries is now made available via the throttle() function.
  • Enhancements to processlist-related views, including the new slave_hosts view.
  • Inline documentation/help is available via the help() routine.
  • more…

QueryScript

common_schema makes for a QueryScript implementation for MySQL. You can run server side scripts, interpreted by common_schema, which allow for easy syntax and greater power than was otherwise previously available on the MySQL server. For example:

foreach($table, $schema, $engine: table like '%')
  if ($engine = 'ndbcluster')
    ALTER ONLINE TABLE :$schema.:$table REORGANIZE PARTITION;

QueryScript includes flow control, conditional branching, variables & variable expansion, script throttling and more.

Read more on common_schema’s QueryScript implementation.

Continue reading » “common_schema rev. 218: QueryScript, throttling, processes, documentation”

QueryScript: SQL scripting language

MySQL

Introducing QueryScript: a programming language aimed for SQL scripting, seamlessly combining scripting power such as flow control & variables with standard SQL statements or RDBMS-specific commands.

QueryScript is available fro MySQL via common_schema, which adds MySQL-specific usage.

What does QueryScript look like? Here are a few code samples:

Turn a bulk DELETE operation into smaller tasks. Throttle in between.

while (DELETE FROM archive.events WHERE ts < CURDATE() LIMIT 1000)
{
  throttle 2;
}

Convert all InnoDB tables in the ‘sakila’ database to compressed format:

foreach ($table, $schema, $engine: table in sakila)
{
  if ($engine = 'InnoDB')
    ALTER TABLE :$schema.:$table ENGINE=InnoDB ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8;
}

Shard your data across multiple schemata:

foreach($shard: {USA, GBR, JAP, FRA})
{
  CREATE DATABASE db_:$shard;
  CREATE TABLE db_:$shard.city LIKE world.City;
  INSERT INTO db_:$shard.city SELECT * FROM world.City WHERE CountryCode = $shard;
}

Continue reading » “QueryScript: SQL scripting language”

Documentation in SQL: CALL for help()

Development MySQL

Documentation is an important part of any project. On the projects I maintain I put a lot of effort on documentation, and, frankly, the majority of time spent on my projects is on documentation.

The matter of keeping the documentation faithful is a topic of interest. I’d like to outline a few documentation bundling possibilities, and the present the coming new documentation method for common_schema. I’ll talk about any bundling that is NOT man pages.

High level: web docs

This is the initial method of documentation I used for openark kit and mycheckpoint. It’s still valid for mycheckpoint. Documentation is web-based. You need Internet access to read it. It’s in HTML format.

Well, not exactly HTML format: I wrote it in WordPress. Yes, it’s HTML, but there’s a lot of noise around (theme, menus, etc.) which is not strictly part of the documentation.

While this is perhaps the easiest way to go, here’s a few drawbacks: Continue reading » “Documentation in SQL: CALL for help()”

Oracle “Technologist of the Year: Developer” Award

MySQL

I am honored to receive Oracle’s Technologist of the Year: Developer award, formerly Oracle Magazine Editors’ Choice Awards.

Technologist of the Year Award is given for individuals for their technical achievements with regard to Oracle products.

As opposed to community based awards, to win this award one must be nominated by himself or his company. There are several award categories: Developer, DBA, IT Manager etc., and many nominations per category. I have been nominated by my company and am happy to have won the award in the Developer category.

Allow me to take the pleasant opportunity to make some acknowledgements. Continue reading » “Oracle “Technologist of the Year: Developer” Award”

More MySQL foreach()

Development MySQL

In my previous post I’ve shown several generic use cases for foreach(), a new scripting functionality introduced in common_schema.

In this part I present DBA’s handy syntax for schema and table operations and maintenance.

Confession: while I love INFORMATION_SCHEMA‘s power, I just hate writing queries against it. It’s just so much typing! Just getting the list of tables in a schema makes for this heavy duty query:

SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='sakila' AND TABLE_TYPE='BASE TABLE';

When a join is involved this really becomes a nightmare. I think it’s cumbersome, and as result, many do not remember the names and meaning of columns, making for “oh, I need to read the manual all over again just to get that query right”. Anyway, that’s my opinion.

A SHOW TABLES statement is easier to type, but cannot be integrated into a SELECT query (though we have a partial solution for that, too), and besides, when filtering out the views, the SHOW statement becomes almost as cumbersome as the one on INFORMATION_SCHEMA.

Which is why foreach() offers handy shortcuts to common iterations on schemata and tables, as follows:

Use case: iterate all databases

call foreach('schema', 'CREATE TABLE ${schema}.event(event_id INT, msg VARCHAR(128))');

In the above we execute a query on each database. Hmmm, maybe not such a good idea to perform this operation on all databases? Let’s filter them:

Use case: iterate databases by name match

call foreach('schema like wordpress_%', 'ALTER TABLE ${schema}.wp_posts MODIFY COLUMN comment_author VARCHAR(96) NOT NULL');

The above will only iterate my WordPress databases (I have several of these), performing an ALTER on wp_posts for each of those databases. Continue reading » “More MySQL foreach()”

common_schema, rev. 178: foreach(), repeat_exec(), Roland Bouman, query analysis

MySQL

common_schema, revision 178 is now released, with major additions. This revision turns common_schema into a framework, rather than a set of views and functions.

common_schema provides with query scripting, analysis & informational views, and a function library, allowing for easier administration and diagnostics for MySQL. 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.

There’s no Perl nor Python, and no dependencies to install. It’s just a schema.

Some highlights for the new revision:

  • foreach(), aka $(): loop through a collection, execute callback commands per element.
  • repeat_exec(): a repeat-until device: execute queries until some condition holds.
  • exec_file(): execute files a-la SOURCE, but on server side
  • Query analysis: analyze query text, view or routine definitions to detect dependency objects.
  • Improvements to views and routines, new routines introduced.

Let’s take a closer look:

rpbouman

I’m very happy to have Roland Bouman working on this project. He introduced some sophisticated code without which some functionality could not take place. I’m sure I don’t need to introduce his great capabilities; I’ll just pass the note that it is very good working with him!

foreach()

Introducing a looping device which can iterate a collection and execute callback commands.

What’s a collection? A range of numbers; a set of constants; the result set of a SELECT query; tables in your database and more.

What is a callback? A query or set of queries to invoke on the specific elements in the collection. For example:

call foreach('table in sakila', 'ALTER TABLE ${schema}.${table} ENGINE=InnoDB ROW_FORMAT=COMPRESSED');

I’ll publish dedicated posts on foreach(), aka $(), following this post. Official documentation is here.

repeat_exec()

Repeat executing queries in a given interval, until some condition holds.

What kind of condition? You can loop forever, or until a given time has passed, a given number of iteration has passed. Continue reading » “common_schema, rev. 178: foreach(), repeat_exec(), Roland Bouman, query analysis”

Oracle ACE

MySQL

I am honored to have been nominated for, and to have received the Oracle ACE award.

Nomination for this award is made by Oracle community members, and in this case those being Oracle employees Keith Larson and Dave Stokes. The award is given by Oracle for my involvement in the Oracle/MySQL community and for my contributions.

While open source involvement is generally done in the mere purpose of sharing knowledge and solutions, recognition plays a role in it. For the most part, one who writes blogs wants them to be read, and one who writes code wants it to be downloaded and tested, which is an elemental type of recognition, and what I aim for.

The recognition given by the Oracle ACE award makes for a wonderful complement, being given by the corporate with whose products I’m involved. It is great to get a surprising “Hey, good work” acknowledgement. Sun/MySQL told me that back in 2009, and I was caught utterly unprepared. I am still unprepared!

Thank you, the community, the people from whom I learn and benefit, anyone who ever reads my blogs, who comment, anyone who tries my code, who provides feedback, the multitude of people writing, sharing, blogging, speaking, coding, fixing, spreading, who make it such a great community.

Thank you, Oracle for this award!

common_schema rev. 68: eval(), processlist_grantees, candidate_keys, easter_day()

MySQL

Revision 68 of common_schema is out, and includes some interesting features:

  • eval(): Evaluates the queries generated by a given query
  • match_grantee(): Match an existing account based on user+host
  • processlist_grantees: Assigning of GRANTEEs for connected processes
  • candidate_keys: Listing of prioritized candidate keys: keys which are UNIQUE, by order of best-use.
  • easter_day(): Returns DATE of easter day in given DATETIME’s year.

Let’s take a slightly closer look at these:

eval()

I’ve dedicated this blog post on MySQL eval() to describe it. In simple summary: eval() takes a query which generates queries (most common use queries on INFORMATION_SCHEMA) and auto-evaluates (executes) those queries. Read more

match_grantee()

As presented in Finding CURRENT_USER for any user, I’ve developed the algorithm to match a connected user+host details (as presented with PROCESSLIST) with the grantee tables (i.e. the mysql.user table), in a manner which simulates the MySQL server account matching algorithm.

This is now available as a stored function: given a user+host, the function returns with the best matched grantee. Read more

processlist_grantees

This view relies on the above, and maps the entire PROCESSLIST onto GRANTEEs. The view maps each process onto the GRANTEE (MySQL account) which is the owner of that process. Surprisingly, MySQL does not provide one with such information. Continue reading » “common_schema rev. 68: eval(), processlist_grantees, candidate_keys, easter_day()”

Speaking at “August Penguin 2011”

MySQL

I will be speaking at August Penguin 2011 (אוגוסט פינגווין), on August 12th in Ramat-Gan, Israel.

August Penguin is the annual meeting of Hamakor society: an Israeli society for Free Software and Open-Source Code (read more here).

I’ll be holding a non-technical talk about MySQL, titled “MySQL and the Open Source Sphere”. In this talk I will be presenting my impressions of the nature of open source development of MySQL and surroundings: the core server, the various forks, patches, 3rd party tools, companies involved, etc. So this is a general “get to know who’s who & what’s what in the MySQL world”.

This is a 30 minutes talk. I will surely not cover every open source development in this field, so my apologies in advance to those I leave out. The truth is, there’s so much going on lately I can hardly keep up with reading the announcements. Truly, this is a wonderful time for open source development with MySQL.

Talk will be held in Hebrew.

See you there!

common_schema: looking for contributions

MySQL

In my announcement for common_schema I have failed to deliver the following message:

I will be happy to receive contributions to common_schema, and I will be happy to have contributors on this project

What kind of contributions are wanted?

So, I’m mostly interested right now in:

  • Views: providing more insight on metadata (data types, schemata, volumes, transactions, locks, connections, etc.)
  • Views: SQL code generation (e.g. generate my FOREIGN KEYs)
  • Functions: DETERMINISTIC, NO SQL, preferably no control flow inside, making for simple calls what is usually a typical, repeating function combination.
  • Tables: small, static data that is handy to have around. But really, small.

Less interested in:

  • Procedures
  • OS dependent solutions (e.g. calling LOAD_FILE(‘/proc/meminfo’) which only makes sense on Linux)

I’ve got a few ideas already, and have received contributions and ideas from others (I’m looking at you, Roland).

If you have a good idea you’d like to share, why don’t you submit it under the Issues page (submit as “Request for new component”)

Everything is done on my spare time, and there’s not much of that. So, if you do submit something and it’s not in right away, please be patient. Thanks!