Home |  MySQL Buzz |  FAQ |  Feeds |  Submit your blog feed |  Feedback |  Archive |  Aggregate feed RSS 2.0 English Deutsch Español Français Italiano 日本語 Русский Português 中文
Showing entries 1 to 27

Displaying posts with tag: stored routines (reset)

Why delegating code to MySQL Stored Routines is poor engineering practice
+2 Vote Up -0Vote Down

I happen to use stored routines with MySQL. In fact, my open source project common_schema heavily utilizes them. DBA-wise, I think they provide with a lot of power (alas, the ANSI:SQL 2003 syntax feels more like COBOL than a sane programming language, which is why I use QueryScript instead).

However I wish to discuss the use of stored routines as integral part of your application code, which I discourage.

The common discussion on whether to user or not use stored routines typically revolves around data transfer (with stored routines you transfer less data since it's being processed on server side), security (with stored routines you can obfuscate/hide internal datasets, and

  [Read more...]
Employee_Team +1 Vote Up -0Vote Down

If you have been using certain DBMSs, or reading recent versions of the SQL standard, you are probably aware of the so-called “WITH clause” of SQL.
Some call it Subquery Factoring. Others call it Common Table Expression. A form of the WITH CLAUSE, WITH RECURSIVE”, allows to design a recursive query: a query which repeats itself again and again, each time using the results of the previous iteration. This can be quite useful to produce reports based on hierarchical data. And thus is an alternative to Oracle’s CONNECT BY. MySQL does not natively support WITH RECURSIVE, but it is easy to emulate it with a generic, reusable stored

  [Read more...]
Support for multiple triggers per table for the same value of action/timing.
Employee_Team +2 Vote Up -0Vote Down


For a long time MySQL server supported only one trigger for every action (INSERT, UPDATE, DELETE) and timing (BEFORE or AFTER). In other words, there could be at most one trigger for every pair (action, timing). It means that a user couldn’t assign for example two BEFORE INSERT triggers for the same table t1. To workaround this restriction and allow several actions to fire on some table event, a user had to implement several stored procedures (one for each activity that would be implemented as independent trigger), create trigger for a table and call this stored procedures from the trigger. As of MySQL 5.7.2 this limitation has been removed. It means that starting the MySQL 5.7.2 a user can create for example, two BEFORE INSERT triggers, three AFTER INSERT triggers and four BEFORE UPDATE triggers for table t1. And this  [Read more...]
BEFORE triggers and NOT NULL columns in MySQL
Employee_Team +0 Vote Up -0Vote Down


  For a long time there was a Bug#6295 in implementation of BEFORE triggers related to handling of NOT NULL column. The problem was that if a column is declared as NOT NULL, it wasn’t possible to do INSERT NULL (or UPDATE to NULL) even though there was associated trigger, setting NOT-NULL value.

For example:

  • There is the table ‘t1′ with a NOT NULL column ‘c1′
  • The table has BEFORE INSERT trigger which sets the ‘c1′ column to NOT NULL value (SET NEW.c1 = 1)
  • User executes the SQL statement INSERT INTO t1 VALUES(NULL) that fails with the following error:     ERROR 1048 (23000): Column ‘c1′ cannot be null
  • The user will get the same error if there is a BEFORE UPDATE trigger that sets the
  [Read more...]
common_schema & openark-kit in the media: #DBHangOps, OurSQL
+0 Vote Up -0Vote Down


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...]
Taking common_schema's rdebug to a test-drive
+1 Vote Up -0Vote Down

This is a simple step-by-step introduction to rdebug: Debugger and Debugging API for MySQL Stored Routines, as part of common_schema.

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.


  [Read more...]
common_schema 2.0.0-alpha: rdebug, GPL
+1 Vote Up -0Vote Down

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.


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:

      [Read more...]
    MySQL Stored Routines Debugger & Debugging API: sneak preview II, video
    +0 Vote Up -0Vote Down

    This is the 2nd sneak preview of common_schema's rdebug: debugger & debugging API for MySQL stored routines (see 1st preview here).

    rdebug will be released as part of common_schema, free and open sourced.

    In this sneak preview I present:

    • Compiling multiple routines with debug info
    • Starting/stopping a debug session
    • Step-over, step-in, step-out
    • Showing stack trace
    • Showing the next-statement to execute
    • Viewing and manipulating local routine variables
    • Misc. meta routines

    The quick technical overview

      [Read more...]
    MySQL Stored Routines Debugger & Debugging API: sneak preview video
    +2 Vote Up -0Vote Down

    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...]
    common_schema over traditional scripts
    +1 Vote Up -0Vote Down

    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:

    • Needs the right dependencies (OS, Perl/Python version, Perl/Python modules).
    • Needs to provide with connection params,
    • Needs to get acquainted with a lot
      [Read more...]
    Things that can't (and some that can) be done from within a MySQL stored routine
    +3 Vote Up -0Vote Down

    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:

    • Cursor for SHOW statements: can't be done -- this is explicitly blocked from operating (it once used to work).
    • Get detailed error information on exceptions: apparently 5.6 has support for this. 5.1 and 5.5 do not.
    • Change binlog_format: this is obvious, if you think about it. binlog_format dictates how the routine itself
      [Read more...]
    MySQL error handling on server side: a NO GO!
    +3 Vote Up -2Vote Down

    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?

    • We might hit a UNIQUE KEY violation
    • Or a FOREIGN KEY violation
    • my_column could be TINYINT UNSIGNED, and with strict sql_mode this makes for out-of-range
    • Or, similarly, it could be an ENUM (2,3,5,8)

    Is that it? Not remotely:

    • This could be a read-only MyISAM table
    • We may have issued a LOCK TABLES my_table READ -- this violates our lock
    • Or this could be an
      [Read more...]
    On stored routines and dynamic statements
    +4 Vote Up -0Vote Down

    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...]
    Auto caching tables
    +2 Vote Up -0Vote Down

    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?

    Hint: yes.

    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?

    Well, yes.

    This post is long, but I suggest

      [Read more...]
    Documentation in SQL: CALL for help()
    +1 Vote Up -0Vote Down

    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

      [Read more...]
    common_schema, rev. 178: foreach(), repeat_exec(), Roland Bouman, query analysis
    +1 Vote Up -0Vote Down

    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,
      [Read more...]
    Self throttling MySQL queries
    +3 Vote Up -0Vote Down

    Recap on the problem:

    • A query takes a long time to complete.
    • During this time it makes for a lot of I/O.
    • Query's I/O overloads the db, making for other queries run slow.

    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:

    • The naive approach: for every 1,000 rows, the query sleep for 1 second
    • The factor approach: for every 1,000 rows, the query sleeps for the amount of time it took to iterate those 1,000 rows (effectively doubling the total runtime of the query).

    Sample query

    We use a simple, single-table

      [Read more...]
    Test-driven SQL development
    +1 Vote Up -0Vote Down

    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...]
    MySQL eval()
    +4 Vote Up -0Vote Down

    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.

    Just what is an eval() call?

    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.

    A simple example

    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:

      [Read more...]
    Announcing common_schema: common views & routines for MySQL
    +1 Vote Up -0Vote Down

    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.

    What does it do?

    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:

    • Did you know you can work out
      [Read more...]
    ROUTINE_PRIVILEGES implementation
    +1 Vote Up -0Vote Down

    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:

      CONCAT('\'', User, '\'@\'', Host, '\'') AS GRANTEE,
      Routine_name AS
      [Read more...]
    Statement-based vs Row-based Replication
    +2 Vote Up -0Vote Down
    Replication as most people know it, has mostly been SQL statement propagation from master to slave. This is known as "statement-based" replication. But there is also another kind of replication that is available, "the row-based replication" and that has quite a lot of benefits. In this post I intend on highlighting the advantages and disadvantages of both the types of replication to help you choose the best one. I also follow up with my own recommendation.
    Pitfalls of monitoring MySQL table activity with stored routines
    +3 Vote Up -0Vote Down
    A friend of mine needed a quick method to monitor a table that was frequently updated. There were several procedures writing to the table several times per second. He needed a quick way of determining how many updates per second the table was getting.
    The table has only one row, which includes a counter that is increased at each operation. Therefore, the simple plan was:
  • Let's get the counter from the table;
  • Allow N seconds to pass;
  • Get the counter again;
  • The difference between the second counter and the first counter, divided by the number of seconds gives the updates per second.
  • The plan makes sense, and if you run the above

      [Read more...]
    Next Week’s MySQL Sessions at ODTUG Kaleidoscope
    +3 Vote Up -1Vote Down

    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...]
    Views: better performance with condition pushdown
    +3 Vote Up -0Vote Down

    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...]
    Accessing Metadata through Stored Routines
    +0 Vote Up -1Vote Down
    Accessing metadata can be optimized by using stored routines.  Stored routines provide the ability to filter the data in a more useful way.  For example, when I'm looking at table data I usually want to look at the index information also.  So I use a stored routine called tabinfo that gives me key information I need for tables and indexes. -- Create the tabinfo stored procedure. DROP PROCEDURE
    A micro-benchmark of stored routines in MySQL
    +1 Vote Up -0Vote Down

    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:

  •   [Read more...]
    Showing entries 1 to 27

    Planet MySQL © 1995, 2014, Oracle Corporation and/or its affiliates   Legal Policies | Your Privacy Rights | Terms of Use

    Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.