Skip to content

# Tag: Open Source

## mycheckpoint revision 231 released

MySQL Development Monitoring mycheckpoint Open Source May 23, 2013May 23, 2013

A new release for mycheckpoint: lightweight, SQL oriented MySQL monitoring solution.

If you’re unfamiliar with mycheckpoint, well, the one minute sales pitch is: it’s a free and open source monitoring tool for MySQL, which is extremely easy to install and execute, and which includes custom queries, alerts (via emails), and out of the box HTTP server and charting.

This is mostly a maintenance release, with some long-time requested features, and of course solved bugs. Here are a few highlights:

- Supports MariaDB and MySQL 5.6 (issues with new variables, space padded variables, text-valued variables)
- Supports alerts via function invocation on monitored host (so not only checking alerts via aggregated data like ‘Seconds_behind_master’ but also by SELECT my_sanity_check_function() on monitored instance). See alerts.
- Supports single-running-instance via “–single” command line argument
- Supports strict sql_mode, including ONLY_FULL_GROUP_BY, overcoming bug #69310.
- Supports sending of pending email HTML report
- Better re-deployment process
- Better recognizing of SIGNED/UNSIGNED values
- Some other improvements in charting, etc.

mycheckpoint is released under the BSD license.

Downloads are available from the project’s page.

## opeark-kit revision 196 released

MySQL Development Open Source openark kit QueryScript May 7, 2013

This is a long due maintenance release of openark-kit. This release includes bugfixes and some enhancements, mainly to oak-online-alter-table.

oak-online-alter-table Changes / bug fixes include:

- Support for keyword-named columns
- Use of FORCE INDEX due to lack of MySQL’s ability for figure out the chunking key at all times
- –sleep-ratio option added; allows for sleep time proportional to execution time (as opposed to constant sleep time with –sleep)
- Support for chunk-retry (in case of deadlock) via –max-lock-retries)
- Fixed order of cleanup
- Fixed bug with verbose messages with non-integer chunking key
- Fixed bug with single-row tables (people, no need for this tool for single row tables :))
- Friendly verbose messages to remind you what’s being executed

oak-chunk-update changes includes:

- Verbosing query comment if exists (friendly printing of what’s being executed)

Continue reading » “opeark-kit revision 196 released”

## common_schema 2.0.0-alpha: rdebug, GPL

MySQL common_schema Development Open Source rdebug Stored routines tools April 9, 2013April 9, 2013

A new release for common_schema: an alpha version of rdebug: MySQL Debugger and Debugging API is now included with common_schema.

With a different license in mind for rdebug, common_schema changes license to GPL (2 or above).

common_schema 2.0 is ready for download. All things rdebug, it is alpha — otherwise it’s a stable release.

#### rdebug

I’m very happy to release this alpha version of rdebug, and urge everyone to try it out.

The idea is to have an open, free, server side debugger and debugging API for MySQL stored routines. To elaborate:

- It’s server side by that it’s implemented by stored routines. Not by a connector; not an emulator; not a GUI tool hack. The entire functionality lies within common_schema, a schema in your server.
- It’s a debugger: you can debug your own stored routines (with limitations)
- It’s a debugging API: there’s a distinct specification and a set of calls which makes for a debugging process
- It’s open since the source code is yours to browse.
- It’s free as in free beer.
- It’s free as it makes you independent of a specific debugger. It provides an API that anyone can use. You can run the API yourself from the command line; or plugins for your favorite GUI editor can be developed to use this API.

On a separate blog post I will take you to a rdebug test drive.

As always, nothing is released before extensive documentation is in place.

I’d love to get input on this. Continue reading » “common_schema 2.0.0-alpha: rdebug, GPL”

## Assistance wanted: DotOrg Pavilion on Percona Live 2013

MySQL Open Source PerconaLive April 2, 2013April 2, 2013

I’m looking for assistance in manning a DotOrg Pavilion on the upcoming Percona Live conference in Santa Clara.

As you may or may not know, free and open source projects are eligible to ask for a free booth on the Expo hall. This is a good chance to promote one’s project.

Of course, someone must stand behind the counter and answer [technical] questions.

I should like to have a booth for common_schema & openark-kit, but am limited in resources — namely the number of bodies I can occupy simultaneously, just one at this time. Along with some duties I have throughout the conference I do not expect to be able to be present for long periods behind the counter.

Are you familiar with common_schema/openark-kit? Would you like to lend a hand and contribute some hours at the conference?

For a full time attendee I can lend a hand and provide with a free conference pass + my gratitude.

Please comment below if you are willing to commit yourself to this task — thank you!

## Re: MySQL 5.1 vs. MySQL 5.5: Floats, Doubles, and Scientific Notation

MySQL Data Types Open Source Percona Toolkit January 24, 2013

Reading Sheeri’s MySQL 5.1 vs. MySQL 5.5: Floats, Doubles, and Scientific Notation, I was baffled at this change of floating point number notation.

However, I was also concerned about the final action taken: using “–ignore-columns” to avoid comparing the FLOAT/DOUBLE types.

The –float-precision option for pt-table-checksum currently only uses ROUND() so as to disregard minor rounding issues. But it can very easily extend to handle the difference in floating point notation. Consider again the problem:

>     mysql> create table tf(f float);
>     Query OK, 0 rows affected (0.11 sec)

>     mysql> insert into tf values(0.0000958084);
>     Query OK, 1 row affected (0.04 sec)

>     mysql-5.1> select * from tf;
>     +-------------+
>     | f           |
>     +-------------+
>     | 9.58084e-05 |
>     +-------------+

>     mysql-5.5> select * from tf;
>     +--------------+
>     | f            |
>     +--------------+
>     | 0.0000958084 |
>     +--------------+

How can we normalize the notation?

Easily: CAST it as DECIMAL. Consider: Continue reading » “Re: MySQL 5.1 vs. MySQL 5.5: Floats, Doubles, and Scientific Notation”

## common_schema: 1.3: security goodies, parameterized split(), json-to-xml, query checksum

MySQL common_schema Development New Features Open Source QueryScript scripts Security January 14, 2013January 14, 2013

common_schema 1.3 is released and is available for download. New and noteworthy in this version:

- Parameterized split(): take further control over huge transactions by breaking them down into smaller chunks, now manually tunable if needed
- duplicate_grantee(): copy+paste existing accounts along with their full set of privileges
- similar_grants: find which accounts share the exact same set of privileges (i.e. have the same role)
- json_to_xml(): translate any valid JSON object into its equivalent XML form
- extract_json_value(): use XPath notation to extract info from JSON data, just as you would from XML
- query_checksum(): given a query, calculate a checksum on the result set
- random_hash(): get a 40 hexadecimal digits random hash, using a reasonably large changing input

Let’s take a closer look at the above:

#### Parameterized split()

split takes your bulk query and automagically breaks it down into smaller pieces. So instead of one huge UPDATE or DELETE or INSERT..SELECT transaction, you get many smaller transactions, each with smaller impact on I/O, locks, CPU.

As of 1.3, split() gets more exposed: you can have some control on its execution, and you also get a lot of very interesting info during operation.

Here’s an example of split() control:

>     set @script := "
>       split({start:7015, step:2000} : UPDATE sakila.rental SET return_date = return_date + INTERVAL 1 DAY)
>         throttle 1;
>     ";
>     call common_schema.run(@script);

In the above we choose a split size of 2,000 rows at a time; but we also choose to only start with 7015, skipping all rows prior to that value. Just what is that value? It depends on the splitting key (and see next example for just that); but in this table we can safely assume this is the rental_id PRIMARY KEY of the table.

You don’t have to use these control parameters. But they can save you some time and effort. Continue reading » “common_schema: 1.3: security goodies, parameterized split(), json-to-xml, query checksum”

## common_schema 1.2: security, partition management, processes, QueryScript goodies

MySQL common_schema Open Source partitioning QueryScript Security November 13, 2012

common_schema 1.2 is released! This version comes shortly after 1.1, yet contains quite a few interesting goodies:

- Account blocking
- Security audit
- RANGE partition management
- Slave status
- Better blocking and idle transaction management
- QueryScript goodies:
  - echo, report
  - while-otherwise statement; foreach-otherwise statement
  - Better variable scope handling
  - Complete support for variable expansion
  - Transaction support within QueryScript
- More summary info and SQL statements in processlist-related views

A closer look at these follows:

#### Account blocking

A new view called sql_accounts, inspired by oak-block-account (also see here and here) provides with the means of blocking use accounts (and releasing them, of course) without revoking their privileges. It offers the SQL statements to block an account (by modifying its password in a symmetric way) and to release an account (by modifying its password back to normal). It really works like a charm. Together with killall() and sql_accounts this gives the administrator great control over accounts.

#### Security audit

Imported from openark kit, and implemented via QueryScript, the security_audit() procedure will audit your accounts, passwords and general settings to find problems, pitfalls and security hazards. I will write more on this later.

#### RANGE partition management

The sql_range_partitions view manages your RANGE and RANGE COLUMNS partitioned tables by providing with the SQL statements to drop oldest partitions and to create the next (in sequence) partitions. See my earlier post. Continue reading » “common_schema 1.2: security, partition management, processes, QueryScript goodies”

## common_schema 1.1 released: split(), try-catch, killall(), profiling

MySQL Analysis common_schema Development Open Source QueryScript scripts September 4, 2012

I’m very happy to announce the release of common_schema, version 1.1 (revision 300).

This version boasts with compelling new features: innovative QueryScript syntax, libraries, views which add to your skills as a DBA, making some maintenance and management tasks a breeze.

- QueryScript, split statement: automagically break long queries into smaller chunks, avoid long locks and reduce query/transaction overhead
- QueryScript, try-catch statement: just try { something; } catch { act_on_error; }.
- killall(): quickly kill connections based on grantee/user/host information.
- profiling/profiling_last: utility views to assist in query profiling diagnostics
- 1 size fits all: a single installer which auto-recognizes available server features and enables respective common_schema features accordingly.
- QueryScript performance boost
- much much more…

Not familiar with common_schema? It allows you to do stuff on server side, by selecting from views, calling upon useful routines or writing easy-to-manage scripts.

I’m suggesting that common_schema should be a really-should-have tool to accompany your MySQL install. Did I say “tool”? It’s merely a schema. But it makes for a great framework:

In High Performance MySQL, 3rd edition, Baron Schwartz describes common_schema:

> The common_schema is to MySQL as jQuery is to javaScript

Reviewing highlights for version 1.1:

#### QueryScript

QueryScript is a scripting language. It sees some major improvements here. I’ve made some speed boosts by avoiding using temporary tables, and by using string parsing instead.

Without doubt the two most handy statements added to QueryScript are: Continue reading » “common_schema 1.1 released: split(), try-catch, killall(), profiling”

## sphinx, sphinx_snippets() & MySQL 5.5

MySQL Development Open Source Sphinx March 21, 2012March 21, 2012

I’ve written a patch which completes Sphinx’s integration with MySQL 5.5.

Up until a couple months ago, Sphinx would not compile with MySQL 5.5 at all. This is, thankfully, resolved as of Sphinx 2.0.3.

However, to my disdain, I’ve found out that it only partially work: the sphinx_snippets() user defined function is not included within the plugin library. After some quick poking I discovered that it was not added to the build, and when added, would not compile.

I rely on sphinx_snippets() quite a lot, and like it. Eventually I wrote the fix to the snippets_udf.cc which allows it to run in a MySQL 5.5 server.

Here are the changes for the 2.0.4 version of Sphinx:

- snippets_udf.cc
- CMakeLists.txt

Replace your 2.0.4 files with these two and get on compiling your MySQL server.

#### Compilation guide

For completeness, here’s how to compile Percona Server 5.5 with Sphinx 2.0.4 including the above patches: Continue reading » “sphinx, sphinx_snippets() & MySQL 5.5”

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

MySQL Administration common_schema Development Open Source QueryScript scripts February 8, 2012

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”