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 21

Displaying posts with tag: Triggers (reset)

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...]
“Duplicate Entry” error for key PRIMARY on UPDATE query (RBR + Triggers)
+0 Vote Up -0Vote Down
Recently, I have faced one interesting issue with Master(Active)-Master(Passive) replication (RBR + Triggers). Passive master was stopped due to “Duplicate Entry” error with update statement. It was something like this. Last_Error: Error ‘Duplicate entry ’29014131′ for key ‘PRIMARY” on query. Continue reading →   [Read more...]
Duplicate entries while deleting records?
+0 Vote Up -0Vote Down

This weekend I got a call from the support engineer on duty that night. He noticed in one of our clusters all slave machines were failing replication but he could not figure out why.

At first I thought it would be an easy problem of the master and slaves being out of sync and the row-based replication failing on not finding the row, but then I noticed all machines were actually still running statement-based replication. As far as I could recall we did that to circumvent another issue that has already been solved months ago but for some reason we never put it back to row-based replication.

A simple SHOW SLAVE STATUS revealed something similar to this:

  [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...]
Limiting table disk quota in MySQL
+1 Vote Up -0Vote Down

Question asked by a student: is there a way to limit a table’s quote on disk? Say, limit a table to 2GB, after which it will refuse to grow? Note that the requirement is that rows are never DELETEd. The table must simply refuse to be updated once it reaches a certain size.

There is no built-in way to limit a table’s quota on disk. First thing to observe is that MySQL has nothing to do with this. It is entirely up to the storage engine to provide with such functionality. The storage engine is the one to handle data storage: how table and keys are stored on disk. Just consider the difference between MyISAM’s .MYD & .MYI to InnoDB’s shared tablespace ibdata1 to InnoDB’s file-per table .ibd files.

The only engine I know of that has a quota is the MEMORY engine: it

  [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.
Thoughts and ideas for Online Schema Change
+6 Vote Up -0Vote Down

Here’s a few thoughts on current status and further possibilities for Facebook’s Online Schema Change (OSC) tool. I’ve had these thoughts for months now, pondering over improving oak-online-alter-table but haven’t got around to implement them nor even write them down. Better late than never.

The tool has some limitations. Some cannot be lifted, some could. Quoting from the announcement and looking at the code, I add a few comments. I conclude with a general opinion on the tool’s abilities.

“The original table must have PK. Otherwise an error is returned.”

  [Read more...]
On “Replace Into”, “Insert Ignore”, Triggers, and Row Based Replication
+0 Vote Up -0Vote Down

In posts on June 30 and July 6, I explained how implementing the commands “replace into” and “insert ignore” with TokuDB’s fractal trees data structures can be two orders of magnitude faster than implementing them with B-trees. Towards the end of each post, I hinted at that there are some caveats that complicate the story a little. On July 21st I explained one caveat, secondary keys, and on August 3rd, Rich explained another caveat. In this

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

password=`cat ~/.backup_password`
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...]
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...]
Triggers Use Case Compilation, Part III
+0 Vote Up -0Vote Down

The previous two parts have looked at some solutions offered by triggers. Let’s look now at some wishful triggers solutions, which are currently unavailable because of triggers limitations.

Triggers Use Case Compilation, Part I

Triggers Use Case Compilation, Part II

Limitations and wishful features

Triggers are slow

The overhead of adding triggers is usually an even breaker. But I would like to believe speed will improve in time!

Triggers cannot act on the same table which activated them.

A thing I would like to do is have a rotating table. A log table is a perfect example: I only want to store logs up to 7 days back, or up to 1M rows. ON INSERT, (or

  [Read more...]
Triggers Use Case Compilation, Part II
+0 Vote Up -0Vote Down

In Triggers Use Case Compilation, Part I, I’ve demonstrated some triggers use scenarios.

We continue our examples of triggers usage.

Counters and aggregations bookkeeping

Consider the City table: each city belongs to a certain country. Some questions we may be interested in are:

  • How many cities are there per country?
  • What’s the sum of cities population per country?
  • What’s the population of the largest city per country?

Answering any of these questions is an easy SQL excercise. But aggregation is required, and full table scan (or full index scan, if we’re lucky) is essentially part of any execution plan. What if we can’t pay the price for these queries? What if we need immediate, or near immediate

  [Read more...]
Restoring from a mysqldump into tables with triggers
+0 Vote Up -0Vote Down
This is actually old news, but I never thought to file a bug report (until now) or say anything to anyone about it. If you use mysqldump to dump and restore a MySQL table that has INSERT triggers, you can get different data in your restored database than you had when you dumped. [...]
Triggers Use Case Compilation, Part I
+0 Vote Up -0Vote Down

I’ve run by quite a few triggers lately on production systems. In previous posts, I’ve written about problems solved with triggers. So here’s a compilation of some solutions based on triggers; and some problems which are not (yet?) solvable due to current triggers limitations.

Triggers can be used to:

  • Maintain integrity
  • Enhance security
  • Enhance logging
  • Assist with archiving
  • Restrict table size
  • Manage caching
  • Manage counters

Triggers are not fast. In fact, they can add quite an overhead if misused. Some of the triggers presented here are known to work on real life production systems, though, and work well. But make sure you benchmark before embarking on extensive application changes.

I’ll be using

  [Read more...]
Using triggers to block malicious code: an example
+0 Vote Up -0Vote Down

Web applications face constant exploitation attempts. Those with a user base must keep their users’ private data, well… private.

While the MySQL security model allows restricting users access to databases, tables and even columns, it has no built in feature for restricting the rows access within the given table.

One cannot allow a user to only update rows 0 through 99, but restrict that user from updating rows 100 to 199. Such restrictions are usually managed in the application level, by adding a necessary “… AND filtering_column = some_value…”

Many web application have the notion of an ‘admin’ account, or several such accounts, which provide greater control over the application. The ‘admin’ account is one account to which many attacks are targeted. One such attack is an attempt to

  [Read more...]
Using memcached functions for MySQL; an automated alternative to Query Cache
+0 Vote Up -0Vote Down

There’s a lot of buzz around memcached. memcached is widely used, and has clients for many programming languages and platforms. TangentOrg have developed a memcached client in the form of MySQL UDFs (User Defined Functions).

I wish to discuss the memcached functions for MySQL: if and how they should be used.

Disclaimer: I do not work with memcached functions for MySQL on a production system; all that is written here reflects my opinion on how things should be done.

With memcached functions for MySQL, we can do the following:

SELECT memc_set('mykey', 'The answer is 42');
SELECT memc_get('mykey');

(See my previous post on how to

  [Read more...]
MySQL Partitioned Tables with Trigger enforced FK constraints
+0 Vote Up -0Vote Down
Well, I suppose its' true you can't use the official MySQL ALTER TABLE statement to do it, but if you're willing to trust your trigger coding abilities you can.All you need is an extra table and a couple triggers.The concept is fairly straight forward:Create a before insert trigger on the child table that validates the parent exists in the parent table.If there is no parent found, then insert a
MySQL: Getting Creative with Partitioning (Performance Results)
+0 Vote Up -0Vote Down
I decided to run some very basic performance test comparing the non-partitioned table with a primary key, and a partitioned table with a primary key and a unique constraint enforced via a secondary table explained in my previous post.Overall, it appears that with partitioning, as the data/rows scale, the inserts actually get faster :) This is what I would expect theoretically, so score one for
MySQL: Getting Creative with Partitioning
+0 Vote Up -0Vote Down
Lately, I've been trying to keep up with at least one of the MySQL Forums: Partitioning.It's a great way to keep on top of issues surrounding partitioning, and also get an idea of what people are trying to do with the new 5.1 feature. Richard came up with an interesting problem that I jumped into only to realize that I hadn't done my homework, and my initial suggestion wouldn't work at all due
Showing entries 1 to 21

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.