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 8

Displaying posts with tag: stored_routines (reset)

How MariaDB makes Stored Procedures usable
+2 Vote Up -0Vote Down

I already wrote how MariaDB makes the debug of stored procedures much easier via the SQL Error Log. But I recently found out that MariaDB provides a usable workaround for some big limitations of their procedural SQL.

First, SELECT is not the only SQL statement which returns a resultset. Other examples are DELETE RETURNING, CALL, SHOW, EXPLAIN and administrative commands like ANALYZE TABLE or CHECK TABLE. But these commands cannot be used in place of SELECT in the following contexts:

  • Subqueries, derived tables, JOINs,

  [Read more...]
Debugging MariaDB stored procedures with the SQL Error Log
+1 Vote Up -0Vote Down

A very old version of the MySQL site contained a nice header: Speed, Power, Ease of Use. I don’t think that stored programs (routines, triggers, events) were supported, at that time. Now they are, and… developing them is amazingly hard.

There are many problems, for example the language is not flexible and the execution is sloooow. But the biggest problem is that there is no debug API.

In details, one of the biggest problems is that, if you have complex procedures (accessing multiple tables, having error handling, and/or calling other procedures) in practice you have no idea of what warnings occur within your procedures.

MariaDB 10.0 makes things much easier by adding the

  [Read more...]
Stored Routines to easily work with the SQL_MODE
+1 Vote Up -0Vote Down

Versione italiana

Working with the SQL_MODE can be tricky, because it’s a command separated list involving long keywords. To be honest, I hate to rewrite the flags, trying to read an unreadable list, etc. Of course some string functions can help (Justin Swanhart suggests to use REPLACE() to make comma-separated lists readable).

I made a small set of Stored Routines wich allow me to easily show SQL_MODE, add a flag, drop a flag and check if a flag is set. These routines work with the GLOBAL SQL_MODE; if you don’t like this, simply replace “@@global.” with “@@session.” in the

  [Read more...]
MariaDB/MySQL: Procedures to easily work with the Diagnostics Area
+1 Vote Up -0Vote Down

Versione italiana

UPDATE 2013-08-30: I fixed 2 bugs and create a GitHub repo called sql_diagnostix. Sorry for not doing this before. If I (or someone else) find new bugs, I’ll update the repo.

The problem

To quickly see information about the errors and warnings generated by the last executed statement, we can use SHOW WARNINGS. However this statement’s results cannot be accessed via SQL, so they cannot be used for error handling within a stored program.

For that purpose we can use GET DIAGNOSTICS, which has two problems:

  • It requires a lot of code
  • There is not a
  [Read more...]
MariaDB/MySQL Error HANDLERs: advanced uses
+0 Vote Up -0Vote Down

This article explains some advanced uses for MySQL and MariaDB’s error HANDLERs. Some information is in MySQL documentation, but it’s rather sparse. Also, while MySQL manual is probably the best manual ever, I’ve found a major bug in errors documentation. So, these use cases are not so obvious. At least they weren’t for me, so I had to test them.

Notes about the examples in this article

All the examples are tested on MariaDB 10.0.3, but I’m sure that there is not difference in MySQL 5.6, and there should be no difference in any 5.* version.

In these examples we need to produce an error to test the server’s behavior. We’ll always use the same method: we’ll declare a `cur`

  [Read more...]
MariaDB/MySQL Cursors: a brief Tutorial
+0 Vote Up -0Vote Down

Versione italiana

In MariaDB and MySQL, Cursors can only be used within a Stored Program, are slow, and have very limited functionalities. That said, they can still be useful in some cases. This page explains how to use them in action, with a trivial example.

Here is our example Stored Procedure which uses a Cursor:

DELIMITER ||
    
DROP TABLE IF EXISTS `test`.`tab1`;
CREATE TABLE `test`.`tab1` (`c` TINYINT UNSIGNED);
INSERT INTO `test`.`tab1` (`c`) VALUES
    (1),
    (2),
    (3);
    
DROP PROCEDURE IF EXISTS `test`.`demo`;
CREATE PROCEDURE `test`.`demo`()
    READS SQL DATA
BEGIN

  [Read more...]
levenshtein and levenshtein_ratio Functions for MySQL
+0 Vote Up -0Vote Down

!!!WARNING!!!

Once again, WordPress managed to silently change my code and make it unusable. And this time, it does it too well: I can’t fix it. This (code killing) is the only WP feature which works good, but I must admit that it is definitely perfect.

For this reason, please ignore the code examples below, and download this archive, which contains my Stored Functions, a Test Case for my Functions, and Arjen Lentz’s Stored Function.

I found a Levenshtein Distance function, implemented as SQL Stored Function for MySQL and MariaDB, written by Arjen Lentz. That post also contains a short but interesting discussion about

  [Read more...]
Quoting MySQL & MariaDB identifiers
+0 Vote Up -0Vote Down

Versione italiana

MySQL/MariaDB identifiers are names for databases, tables, columns, etc. They can be quoted with `backticks` (AKA backquotes), and in that case they can contain characters which are normally illegal for identifiers (even spaces or the backtick itself), or they can be reserved words. Both quoting and not quoting cause some problems.

If you don’t quote names you will need to avoid illegal chars and reserved words – which is a good practice, anyway. But when you upgrade MariaDB, the new version could add some reserved words.

If you quote names, you should be sure to do it everywhere. It is a good practice, but if a developer doesn’t use backticks (or forgets to do it), he may see strange errors.

If you use

  [Read more...]
Showing entries 1 to 8

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.