|Showing entries 1 to 25|
I had the pleasure of joining into @DBHangOps today, and speak about common_schema and openark-kit. What was meant to be a 15 minute session turned to be 50 -- sorry, people, I don't talk as much at home, but when it comes to my pet projects...
I also realized I was missing on a great event: DBHangOps is a hangout where you can chat and discuss MySQL & related technologies with friends and colleagues, with whom you typically only meet at conferences. I will certainly want to attend future events.
Thanks to John Cesario and Geoffrey Anderson who invited me to talk, and to the friends and familiar faces who attended; I was happy to talk about my work, and very interested in[Read more...]
In other words: let me take you through the steps for debugging your stored routines on your own server. We will step into, step over, step out, modify variables, set a breakpoint, run to breakpoint...
Command line geeks, this one's for you. GUI lovers, this is actually an API; I am hoping for someone wrap it up with a plugin for your favorite GUI editor.
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.
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:
rdebug will be released as part of common_schema, free and open sourced.
In this sneak preview I present:
This is a sneak peek video introduction/preview of an in-development free and open source server side debugger & debugging API for MySQL stored routines.
MySQL does not provide server side debugging capabilities for stored routines. Some tools exist, including MySQL's own, that assist in stored routine debugging. These are all GUI based and, to the best of my knowledge, MS Windows based. There is one solution in alpha stage that is developed for Java/eclipse; I did not look at the code. See discussion here and here.
An ideal solution would be to have debugging API in the server itself - independently of your client, programming language or operating system. To the[Read more...]
If you are familiar with both openark kit and common_schema, you'll notice I've incorporated some functionality already working in openark kit into common_schema, essentially rewriting what used to be a Python script into SQL/QueryScript.
What was my reasoning for rewriting good code? I wish to explain that, and provide with a couple examples.
I'm generally interested in pushing as much functionality into the MySQL server. When using an external script, one:
I'm doing a lot of stored routine programming lately, working on common_schema. I'm in particular touching at the extremes of abilities. Some things just can't be done from within a stored routine. Here's a list of can't be done:
There is no reasonable way to catch and diagnose errors on server side. It is nearly impossible to know exactly what went wrong.
To illustrate, consider the following query:
INSERT INTO my_table (my_column) VALUES (300);
What could go wrong with this query?
Is that it? Not remotely:
I very much enjoyed reading Overloading Procedures by Michael McLaughlin: good stuff!
I'm dealing with similar issues in common_schema/QueryScript, where I implement a whole new scripting language within MySQL, interpreted by stored routines. I am now finalizing the next version of common_schema/QueryScript, with a major addition to the scripting language to put yet even more power at the hands of the programmer/DBA using simple, clean syntax.
Still hush hush, the development of that feature touched at the very same issues described in Michael's post. Present in current release, these issues are intensified by the use and complexity of the new development. Here are a[Read more...]
Is there a way to create a caching table, some sort of a materialized view, such that upon selecting from that table, its data is validated/invalidated?
But to elaborate the point: say I have some table data_table. Can I rewrite all my queries which access data_table to read from some autocache_data_table, but have nothing changed in the query itself? No caveats, no additional WHEREs, and still have that autocache_data_table provide with the correct data, dynamically updated by some rule of our choice?
And: no crontab, no event scheduler, and no funny triggers on data_table? In such way that invalidation/revalidation occurs upon SELECT?
This post is long, but I suggest[Read more...]
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.
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:
Recap on the problem:
I introduce the notion of self-throttling queries: queries that go to sleep, by themselves, throughout the runtime. The sleep period means the query does not perform I/O at that time, which then means other queries can have their chance to execute.
I present two approaches:
We use a simple, single-table[Read more...]
I'm having a lot of fun writing common_schema, an SQL project which includes views, tables and stored routines.
As the project grows (and it's taking some interesting directions, in my opinion) more dependencies are being introduced, and a change to one routine or view may affect many others. This is why I've turned the development on common_schema to be test driven.
Now, just how do you test drive an SQL project?
Well, much like the way you test any other project in your favorite programming language. If its functions you're testing, that's all too familiar: functions get some input and provide some output. Hmmm, they might be changing SQL data during that time. With procedures it's slightly more complex, since they do not directly return output but result sets.
Here's the testing scheme[Read more...]
I've just implemented an eval() call for MySQL. It is implemented with SQL, using a stored procedure. So this is not some plugin: you can use it from within your normal database server.
In some programming languages it would mean: get some text, and execute it as though it were complied code. So, dynamic coding.
In SQL: get the text of query which generates SQL statements in itself (either DML or DDL), and invoke those implied SQL statements.
Best if I present Mass killing of MySQL Connections by Peter Zaitsev. The thing is to execute a query, typically on INFORMATION_SCHEMA, which uses metadata so as to generate SQL queries/commands. Peter's example is:
Today I have released common_schema, a utility schema for MySQL which includes many views and functions, and is aimed to be installed on any MySQL server.
There are views answering for all sorts of useful information: stuff related to schema analysis, data dimensions, monitoring, processes & transactions, security, internals... There are basic functions answering for common needs.
Some of the views/routines simply formalize those queries we tend to write over and over again. Others take the place of external tools, answering complex questions via SQL and metadata. Still others help out with SQL generation.
Here are a few highlights:
Following up on MySQL security: inconsistencies, and on MySQL bug #61596, I was thinking it may take a long time till the non-existent ROUTINE_PRIVILEGES view is implemented. Here’s my own implementation of the view.
I’ve followed the somewhat strange conventions used in the *_PRIVILEGES tables in INFORMATION_SCHEMA, where the IS_GRANTABLE is a separate column, although in 2nd 1st normal form.
I present it here as a query, using session variables, rather than a view definition:
[Read more...]SELECT STRAIGHT_JOIN CONCAT('\'', User, '\'@\'', Host, '\'') AS GRANTEE, NULL AS ROUTINE_CATALOG, Db AS ROUTINE_SCHEMA, Routine_name AS
By now you know that there is a MySQL Track during next week’s ODTUG Kaleidoscope in Washington, DC. Ronald Bradford and I organized the schedule at the last minute (Ronald did a lot of the work!). It was difficult to fill a schedule with 19 sessions that are either 1 hour or 1.5 hours long, and to do it I ended up with three presentations.
At each presentation I will be giving away a copy of The MySQL Administrator’s Bible, so be sure to show up! All MySQL track sessions are in Maryland C, and all times are Eastern.
On Monday, June 28th from 4 pm – 5:30 pm I will be presenting[Read more...]
Justin’s A workaround for the performance problems of TEMPTABLE views post on mysqlperformanceblog.com reminded me of a solution I once saw on a customer’s site.
The customer was using nested views structure, up to depth of some 8-9 views. There were a lot of aggregations along the way, and even the simplest query resulted with a LOT of subqueries, temporary tables, and vast amounts of data, even if only to return with a couple of rows.
While we worked to solve this, a developer showed me his own trick. His trick is now impossible to implement, but there’s a hack around this.
Let’s use the world database to illustrate. Look at the following view[Read more...]
Ever wondered how fast stored routines are in MySQL? I just ran a quick micro-benchmark to compare the speed of a stored function against a "roughly equivalent" subquery. The idea -- and there may be shortcomings that are poisoning the results here, your comments welcome -- is to see how fast the SQL procedure code is at doing basically the same thing the subquery code does natively (so to speak).
Before we go further, I want to make sure you know that the queries I'm writing here are deliberately mis-optimized to force a bad execution plan. You should never use IN() subqueries the way I do, at least not in MySQL 5.1 and earlier.
I loaded the World sample database and cooked up this query:PLAIN TEXT SQL:
|Showing entries 1 to 25|