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

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

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...]
“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.

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...]
Showing entries 1 to 10 of 21 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.