Showing entries 1 to 8
Displaying posts with tag: procedure (reset)
Access Shard-Query with the MySQL client without using MySQL proxy

One of the great features of Shard-Query is the ability to use MySQL proxy to access resultsets transparently. While this is a great tool, many people have expressed reservations about using MySQL Proxy, an alpha component in their production environment.

I recognize that this is a valid concern, and have implemented an alternate method of retrieving resultsets directly in the MySQL client, without using a proxy. This means that any node can easily act as the “head” node without any extra daemon, instead of having to run many proxies.

The sq_helper() routine has been checked into the git repository and is available now.

The function takes a few parameters:

  • sql to run
  • shard-query schema name (empty string or null for default schema)
  • schema to store temp table in
  • temp table name (where results are sent to)
  • return result (boolean, 1 returns …
[Read more]
Use of DECLARE and SET in MySQL stored program

DELIMITER $$                    -- Change the delimiter

DROP PROCEDURE IF EXISTS demo$$ -- Remove if previously exists
CREATE PROCEDURE demo()         -- Create Stored Procedure
BEGIN                           -- Start of definition
    DECLARE my_int INT;         -- Declare variables
    DECLARE my_big_int BIGINT;
    DECLARE my_text TEXT;

    DECLARE my_name VARCHAR(30) -- Declare variables with 
        DEFAULT 'Rookie Dev';   -- default value set

    SET my_int = 20;            -- Set variables to values

[Read more]
MySQL Global status difference using MySQL procedures / functions

As a MySQL DBA, I guess you use the SHOW GLOBAL STATUS command or the corresponding INFORMATION_SCHEMA.GLOBAL STATUS table to show current mySQL status quite often. And many of us use different tools to collect that data and view it.

But sometimes we use same command from the SQL prompt, because we have to, because it's the only option or just because that is the most convenient way. And often you kick of two such command in a row, a few seconds apart, just to see how things are moving:
... DBA picks his or her nose for a few seconds ...

And then you can see how things are advancing. But how much? To figure of how much, you have to calculate the differnce between the values returned by those two statements. And then there is another issue. How much is the difference per second? To know what the difference is per second, we …

[Read more]
EVENTs - The hidden MySQL 5.1+ feature

MySQL 5.1 and up has EVENTs, but this features seems to be little used. I started using them some time ago, and once I was using them, I found them more and more useful. There are a few bugs with them, I reported this one for example, and this alone tells me that probably not too many use this feature.

I will post a few example soon of things I use EVENTs for, but right now, let me tell you a few basics:

  • One IMPORTANT reason I like them: In comparison to, say, crontab jobs, I run them inside the database itself, which I do not have to put MySQL passwords in files or scripts on the server. BIG advantage.
  • For anything but the most simple of actions, write a procedure and then call the procedure from the EVENT, instead of having everything in the event itself, this just makes things difficult to manage.
  • Error handling: the …
[Read more]
Using MySQL Partitioning Instead of MERGE Tables

One common question I get is how to use partitioning instead of MERGE tables. The process I use involves using stored procedures to create and drop partitions. This article will go over the stored procedures I use; special thanks to Roland Bouman for taking a look and giving great feedback to optimize this process.

First, a simple table, not partitioned (yet):

use test;
  id int NOT NULL,
  creationDate datetime NOT NULL,
  PRIMARY KEY (id,creationDate)

In real, life there is more to the table than just id and creationDate. The most important part is that the partitioned field(s) need to be part of the primary key.

Now, add the partition definition. This can be done in the CREATE statement, but I have found that it is easier for me to think …

[Read more]
OpenNMS JDBC Stored Procedure Poller with MySQL

Since a few months we are monitoring our infrastructure at Days of Wonder with OpenNMS. Until this afternoon we were running the beta/final candidate version 1.5.93.

We are monitoring a few things with the JDBC Stored Procedure Poller, which is really great to monitor complex business operations without writing remote or GP scripts.

Unfortunately the migration to OpenNMS 1.6.1 led me to discover that the JDBC Stored Procedure poller was not working anymore, crashing with a NullPointerException in the MySQL JDBC Driver while trying to fetch the output parameter.

In fact it turned out I was plain wrong. I was using a MySQL PROCEDURE:

CREATE PROCEDURE `check_for_something`()
[Read more]
Source Controlling the Database Schema

In a linkage of biblical proportions, Log Buffer #83 pointed me to Tom Kyte’s reiteration which pointed me to Coding Horror’s rant about source controlling the database schema. Now, for starters, I agree with Tom’s sarcasm and Coding Horror’s rant — the database schema really should be source controlled in the same place as the application [...]

Hiding SQL in a Stored Procedure

I recently wrote a blog entry (on my Postgres blog) about hiding SQL in a stored procedure, Hiding SQL in a Stored Procedure. I decided to see if I could convert that same concept to a MySQL stored procedure.

It doesn't work exactly the same. For one, the syntax is a little different. I expected that and the syntax differences really aren't that bad. Minor tweaks really.

The second issue is the major one. While I could write the proc and return a result set, I am not, as far as I can tell, able to treat the procedure as a table. In Postgres, I created a function with a set output. Unfortunately, MySQL does not allow sets as a function result. You can return a set from a procedure though, as odd as that sounds.

So here is what I found.

My create table command and inserts ran …

[Read more]
Showing entries 1 to 8