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 30 of 36 Next 6 Older Entries

Displaying posts with tag: Stored Procedures (reset)

Capture MySQL Foreign Keys
+0 Vote Up -0Vote Down

Shantanu asked a follow-up question on my Cleanup a MySQL Schema post from last month. He wanted to know if there was a way to capture foreign keys before removing them. The answer is yes, but how you do it depends on whether the primary key is based on a surrogate key using an auto incrementing sequence of a natural key using descriptive columns.

You can capture foreign keys with a simple query when they’re determined by a single column value. However, this script creates ALTER statements that will fail when a table holds a multiple column foreign key value. The SELECT statement would look like this when capturing all foreign key values in a MySQL Server:

SELECT   CONCAT('ALTER TABLE',' ',tc.table_schema,'.',tc.table_name,' '
  [Read more...]
Signal from a procedure
+1 Vote Up -0Vote Down

As I review with my students, a stored function works like a standalone program, while a stored procedure runs in the scope of another program unit. For example, you can compare the result of a function as an expression in an IF statement, like:

  IF add_numbers(1,3) > 3 THEN
    ...
  ELSE
    ...
  END IF;

You can’t call procedures inside an IF statement, but you can call the procedure and use a single OUT-mode (pass-by-reference) parameter from the procedure in a subsequent IF statement. You can implement a a wait procedure like that with the following example.

The example first creates two tables, the road_runner and coyote tables:

-- Drop road_runner table if exists.
DROP TABLE IF EXISTS road_runner;
 
-- Create roadrunner
  [Read more...]
Cleanup a MySQL Schema
+0 Vote Up -0Vote Down

My students required way to remove all their tables, views, and constraints from a MySQL database (or the alias schema). Since they’re using referential or foreign key constraints, I also wrote one procedure to drop all foreign key constraints from a database. There’s also another to drop views. The final stored procedure calls the procedure that drops foreign keys, then calls the procedure to drop views before dropping all the tables.

Here’s the dropForeignKeys stored procedure:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
-- Provide a log file debugging statement.
SELECT 'DROP PROCEDURE IF EXISTS dropForeignKeys';
 
-- Conditionally drop
  [Read more...]
SQL Injection Risks
+1 Vote Up -0Vote Down

While I tried to deflect how you perform SQL Injection attacks against a MySQL procedure, my students requested that I post examples of what to do to avoid SQL injection, and what not to do to invite attacks. The best practice to avoid SQL injection attacks is too always bind inputs to data types, and avoid providing completely dynamic WHERE clauses.

Here’s the correct way to dynamically generate a result from a MySQL Stored Procedure:

CREATE PROCEDURE hello (IN pv_input VARCHAR(50))
BEGIN
  SELECT sample_id
  ,      sample_name
  FROM   sample
  WHERE  sample_name = pv_input;
END;
$$

A call to this hello procedure will only return the row or rows where the pv_input value matches the sample_name column value. Any attempt to exploit it like the one below fails.

CALL
  [Read more...]
MySQL Database Triggers
+1 Vote Up -0Vote Down

One of the students wanted an equivalent example to an Oracle DML trigger sample that replaces a white space in a last name with a dash for an INSERT statement. Apparently, the MySQL trigger example in the Oracle Database 11g and MySQL 5.6 Developer Handbook was a bit long. I have to agree with that because the MySQL DML trigger demonstrated cursors and loops in the trigger code.

Triggers can be statement- or row-level actions. Although some databases let you define statement-level triggers, MySQL doesn’t support them. MySQL only supports row-level triggers. Row-level triggers support critical or non-critical behaviors. Critical behavior means the trigger observes an insert,

  [Read more...]
Fun with Performance Schema
+4 Vote Up -0Vote Down
I'm using a very small MariaDB instance as a datastore for my YouLess energy monitor, my own mail server (postfix, roundcube). It's a virtual machine from a commercial VPS provider.

All data fits in memory and the overhead of running with performance_schema on is not an issue.

While I was reading a blog post about performance_schema by Mark Leith I wanted to see what P_S could tell me about my own server.

This is the output from the first query:
mysql> select * from file_summary_by_event_name order by count_read desc,count_write desc limit 10;
+--------------------------------------+------------+-------------+--------------------------+---------------------------+
| EVENT_NAME








  [Read more...]
PHP for MySQL Striped View
+1 Vote Up -0Vote Down

Back in May I explained how to create MySQL striped views with session variables. A couple folks wanted to know how to implement them through PHP programs. The trick is sharing the same connection between a call to the function before a query against the striped view.

I’ve updated the MySQL example beyond the Hobbit analogy from the prior post. It now uses the following database components:

  • An APPLICATION_USER table
  • A striped AUTHORIZED_USER view
  • A FUNCTION_QUERY view to optimize function calls
  • A SET_LOGIN function
  • A GET_LOGIN_ID function
  • A GET_USER_ID function

The definition of the APPLICATION_USER table is:

  [Read more...]
Overloading Procedures
+3 Vote Up -0Vote Down

A student asked, “Can you successfully overload MySQL stored procedures, like PL/SQL does in stored packages?” I said, “MySQL doesn’t formally support overloading stored procedures, and doesn’t support packages like Oracle 11g. You can, however, mimic (or fake) overloading with prepared statements. It requires a careful combination of stored procedures and session variables.”

Unfortunately, they asked for an example. So I wrote this set of code. It uses queries as the dynamic statements to make it simple to test but you can substitute INSERT, UPDATE, or DELETE statements. I didn’t provide type validation in the example, which would be required for dates or date-timestamps.

It’s presented in steps with test programs at each level. If you see an

  [Read more...]
Implicit Commit Functions?
+1 Vote Up -0Vote Down

Somebody asked about the possibility of putting DML statements inside MySQL stored functions. DML statements like the INSERT, UPDATE, and DELETE. When I said, “Yes, you can put DML statements inside functions.” They showed me the error they encountered, which is only raised at compilation when you put an explicit COMMIT statement or a Data Definition Language (DDL) statement (CREATE, ALTER, DROP, or RENAME) inside a MySQL function. The actual error message displayed is:

ERROR 1422 (HY000): Explicit OR implicit commit IS NOT allowed IN stored FUNCTION OR TRIGGER.

While an explicit COMMIT is obvious when placed inside a function, the implicit COMMIT statement isn’t obvious unless you know a DDL

  [Read more...]
MySQL Striped Views
+1 Vote Up -0Vote Down

A question came up today about how to stripe a MySQL view, and this post shows you how. Along with the question, there was a complaint about why you can’t use session variables in a view definition. It’s important to note two things: there’s a workaround and there’s an outstanding request to add lift the feature limitation in Bug 18433.

A striped view lets authorized users see only part of a table, and is how Oracle Database 11g sets up Virtual Private Databases. Oracle provides both schema (or database) level access and fine-grained control access. Fine grained control involves setting a special session variable during a

  [Read more...]
Value or Reference?
+1 Vote Up -0Vote Down

In class today, we reviewed pass-by-value (IN-only mode) parameters and pass-by-reference (INOUT and OUT mode) parameters for stored procedures. The analogy that finally seemed to hit home for the students was linking the modes to the story of Alice in Wonderland.

Here’s the analogy and below is the code to support it:

“A pass-by-value parameter in a procedure is like sending an immutable copy of Alice into the rabbit hole, which means she

  [Read more...]
Summary Tables with MySQL
+4 Vote Up -0Vote Down
I was recently talking with a few people and the concept of summary tables came up as solutions for them. The issue is, they never thought of it. Hopefully this post will help them as well as others.
Summary Tables are not a new concept. They are exactly what they sound like, basically summary of existing data. Aggregated tables, materialized views and summary tables are very dominate in data warehouses. You can also take advantage of these concepts as part of your reporting systems as well.
So summary tables are real tables. So you can take advantage of indexes and etc with them. In the examples I am using, I consider them more of a summary table than aggregated tables . Depending on your application and needs it could grow into more of an aggregated tables and or materialized views situation.
How you separate your data and tables is dependent on your reporting and


  [Read more...]
Curious case with MySQL replication
+2 Vote Up -0Vote Down

MySQL Replication is a powerful tool and it’s hard to find a production system not using it. On the other hand debugging replication issues can be very hard and time consuming. Especially if your replication setup is not straightforward and you are using filtering of some kind.

Recently we got an alert from our monitoring system that replication stopped on production slave with the following error:

Can't find record in 'some_table', Error_code: 1032;
handler error HA_ERR_KEY_NOT_FOUND;
the event's master log binlog.000011, end_log_pos 735766642

This means that a ROW-based replication event was going to be applied on slave, but could not find the row it was supposed to be applied to. This is something I like about ROW format — it allows you to catch such data synchronization issues right away. In this particular case MIXED format was used, but if



  [Read more...]
Stored procedures and Triggers
+2 Vote Up -0Vote Down

Having multiple ways to achieve a task is something we all enjoy as developers and DBAs. We find, develop and learn new ways to do things better and faster all the time.
At the risk of starting a debate, I am curious on others opinions or practices when it comes to Stored Procedures and Triggers. To use them or not versus code based functions ? Best case use versus worst case use? There is no real wrong answer here as it depends on your development application. Certainly some lean one way over another and there are more than enough valid reasons on both sides of the debate.
Here are couple of my thoughts on the topic....
I come from the dot.com bubble era , and from that I rarely use stored procedures or triggers. Back then PHP was still new, Perl dominated websites with the cgi-bin and MySQL did not have stored procedures or triggers. Thank goodness



  [Read more...]
How to Disable/Enable Triggers on Demand
+2 Vote Up -0Vote Down
Permalink: http://bit.ly/1kBCcQu



One of the sorely wanted features missing in MySQL is the ability to disable/enable triggers on the fly. By comparison, disabling/enabling Foreign Key constraints can be simply done by setting a server system variable:

SET FOREIGN_KEY_CHECKS = [TRUE|FALSE];

Now as of version 5.6, there is no built-in server system variable TRIGGER_CHECKS in MySQL. A simple workaround is to instead use a user-defined session variable. The setting for trigger checks stored into the session variable allows the setting to be seen by all statements, including all stored procedures and functions, as long as the user is connected, which for this workaround is in effect similar to using a server system variable.









  [Read more...]
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...]
    Some hidden goods in MySQL 5.5
    +11 Vote Up -0Vote Down
    The announcement of MySQL 5.5 released as GA has outlined the improvements in this version, which indeed has enough good new features to excite most any user.
    There are two additions, though, that were lost in the noise of the bigger features, and I would like to spend a few words for each of them.The first addition is something that users of stored routines have been waiting for since MySQL 5.0. No, it is not SIGNAL and its close associate RESIGNAL, which have been publicized enough. I am talking about the stored routine parameters, for which now there is a dedicated table in the information_schema.
    Let's see

      [Read more...]
    Getting a return code from a stored procedure
    +1 Vote Up -0Vote Down

    Sometimes we have some special need for a stored procedure to call another to do something. That is fine, but what if the second stored proc failed for some reason? Maybe you want to halt the first stored procedure (the caller) and not proceed with the work until the problem is verified and resolved. So How do you do it?

    Simple, get a return code from the called procedure to determine if it worked or not!

    Here’s a sample piece of code to explain better:


    DROP PROCEDURE IF EXISTS `test`.`testing123`;
    DELIMITER $$

    CREATE
    PROCEDURE `test`.`testing123`(OUT a INT)
    BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
    SET a=2;
    END;
    SET a=0;

    # toggle the below as comment or not to see the call at the bottom working
    #










      [Read more...]
    mysqldump each object separately
    +3 Vote Up -1Vote Down

    As a continuation to a previous blog post last week and inspired by Kedar I have created a small script to export tables, stored procedures, functions and views into their respective file. It works for multiple databases where you can specify a list of databases too and although things like events, triggers and such are still missing they are easily added.

    It is especially useful to dump stored procedures separately since it is a lacking functionality in mysqldump.

    I placed the script in mysql forge for anybody to use, provide feedback and possibly enhancements to it.

    Dumping DDL – mysqldump tables, stored procedures, events, triggers (separately)
    +4 Vote Up -0Vote Down

    If you like to keep your ddl backed up in some source management tool like svn or cvs and want to do it individually for stored procedures, events, triggers, tables and such rather than having a single file you can easily do so using the below. You could even include the –skip-dump-date or –skip-comments and use the below to compare ddl daily checking for alterations thus making sure you are aware of any ddl changes done on the database.

    user=backup_user
    password=`cat ~/.backup_password`
    hostname=127.0.0.1
    port=3306
    dbname=test_db
    path=/home/mysql/ddl
    date=`date +%Y%m%d`

    mysqldump -u$user -p$password -h$hostname -P$port --no-create-info --no-data --no-create-db --skip-opt $dbname > "$path"/"$dbname"_triggers_"$date".sql
    mysqldump -u$user -p$password








      [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...]
    Getting started with MySQL 5.5
    +9 Vote Up -0Vote Down



    Some time go, we announced a new release model for MySQL. As all new things, it had some initial hiccups (with MySQL 5.4 we were still getting acquainted with the new model), but now it seems to be in full swing.
    By the time you read these lines, MySQL 5.5 will be available. If the mirrors aren't seeded yet, the impatient can compile and use the new version from the launchpad source tree..

    Overview

    What's this new release anyway? I'll leave it




      [Read more...]
    New white paper: Guide to Optimizing Performance of the MySQL Cluster Database
    Employee +3 Vote Up -0Vote Down

    MySQL Cluster Connection Pooling

    This guide explores how to tune and optimize the MySQL Cluster database to handle diverse workload requirements. It discusses data access patterns and how to build distribution awareness into applications, before exploring schema and query optimization, tuning of parameters and how to get the best out of the latest innovations in hardware design.

    The Guide concludes with recent performance benchmarks conducted with the MySQL Cluster database, an overview of how MySQL Cluster can be integrated with other MySQL storage engines, before summarizing additional resources that will enable you to optimize MySQL Cluster

      [Read more...]
    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:

    PLAIN TEXT SQL:
  • SELECT
  •   [Read more...]
    Creating a MySQL plugin to produce an integer timestamp
    Employee +2 Vote Up -0Vote Down

    This article shows how to create a MySQL-plugin that can be used to create a function which can in turn be used in stored procedures. The function will produce an integer value representing the time (to the nearest usec).

    I’m working on an article for conflict detection/resolution when using MySQL Cluster asynchronous replication which requires an integer column to store a timestamp for comparison purposes. In fact, it doesn’t actually need the timestamp to represent an absolute or even a relative point in time – all it cares about is that the if the function is called twice on 2 different hosts that the 2nd call will always result in a larger number than the 1st. Obviously, in a production environment the times on the 2 hosts would need to be kept in sync.

    The c code (inttime.c)

    #include <mysql.h>
    #include
      [Read more...]
    MySQLi result set iteration - recursive
    Employee +2 Vote Up -0Vote Down

    PHP 5.3 is released and after the release stress is over my mind is open for new ideas. While relaxing yesterday I thought about many things, among them was the Resultset iterator I recently discussed.

    Now I wondered where to go next with this and had the idea that an individual Resultset is a child of the whole result and this might be wrapped in an Recursive Iterator. For doing so we don't implement the Iterator interface but RecursiveIterator. RecursiveIterator extends a typical Iterator with two methods: hasChildren() and getChildren(). But now we have a problem: The Iterator returned by getChildren() has to be a RecursiveIterator, too, which makes sense, in general. But I want to return a MySQLi Resultset which isn't recursive - so making this a RecursiveIterator is wrong.

      [Read more...]
    MySQLi Resultset Iterator
    Employee +0 Vote Up -0Vote Down

    Over at phpdeveloper.org I was pointed to a blog post talking about MySQLi and stored procedures. That reminded me about a small thing I recently did: When using MySQLi's multi_query to send queries which return multiple result sets you have to use a rather unintuitive API which can certainly be improved.

    Recently I sat down and cooked up a small improvement for that, being an iterator fan I, of course, had to use an iterator for that and implemented the following class:

    <?php
    class MySQLi_ResultsetIterator implements Iterator {
        private $mysqli;
        private $counter = 0;
        private $current = null;
        private $rewinded = false;
    
        public function __construct(mysqli $mysqli) {
            $this->mysqli = $mysqli;
        }
        private
      [Read more...]
    Are Stored Procedures available with MySQL Cluster?
    Employee +1 Vote Up -0Vote Down

    The answer is yes – kind of.

    Stored procedures are implemented in a MySQL Server and can be used regardless of the storage engine being used for a specific table. One inference from this is that they won’t work when accessing the Cluster database directly through the NDB API.

    This leads to the question of whether or not that limitation actually restricts what you can achieve. This article gives a brief introduction to stored procedures and looks at how the same results can be achieved using the NDB API.

    Stored procedures provide a rudimentary way of implementing functionality within the database (rather than in the application code). They are implemented by the database designer and have the ability to perform computations as well as make changes to the data in the database. A typical use of stored procedures would be to control all

      [Read more...]
    Stored Procedures Are Slow Part 2
    +0 Vote Up -0Vote Down

    Last time I demonstrated a case where stored procedures are slow.  There were a few comments that I should include selects in the stored procedure to make the tests  more realistic.  From experience I already knew the answer so I didn’t go into that level of detail, but since stored procedures are all about database access this is a reasonable comment. 

    This is a simple stored procedure that selects data and then summarizes it by customer.  No one would actually write this as it is far too easy to use a SQL statement instead.    Assume the logic is more complex and can’t be done easily by the standard SQL techniques of case statements, temp tables, etc, and then this makes more sense.  

    The end result is this stored procedure takes 696 seconds to run. 

     

      [Read more...]
    Are MySQL stored procedures slow?
    +0 Vote Up -0Vote Down

     

    Yes, if compared to code in Java or C#. For example, this overly simple code took 284 seconds.

    CREATE PROCEDURE CountTest()
    begin
        declare counter int default 0;
        select now();
        repeat
            set counter = counter + 1;
        until counter > 120000000
        end repeat;
        select counter;
        select now();
    end

    Ignoring my off by one error, here is equivalent code in C# (the language I’m currently learning).  It took 419 milliseconds, or MySQL took 677 times











      [Read more...]
    Showing entries 1 to 30 of 36 Next 6 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.