Planet MySQL Planet MySQL: Meta Deutsch Español Français Italiano 日本語 Русский Português 中文
Showing entries 1 to 10 of 27 10 Older Entries

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...]
WITH RECURSIVE and MySQL
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

Introduction

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

Introduction

  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

#DBHangOps

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.

Requirements:

  [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.

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:

      [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...]
    Showing entries 1 to 10 of 27 10 Older Entries

    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.