Showing entries 1 to 7
Displaying posts with tag: trigger (reset)
Unexpected problem with triggers and mysqldump

Some time ago, I had to convert all tables of a database from MyISAM to InnoDB on a new server. The plan was to take a logical dump on the master, exporting separately the schema and the data, then edit the CREATE TABLE statements to ensure all tables are created with InnoDB, and reload everything on the new server.

Quite easy, isn’t it? Of course I wanted to run a test first before performing the actions on a live system.

So let’s play with the sakila database.

mysqldump has options to export schema and data separately, let’s use them:

# Export schema
$ mysqldump --no-data sakila > schema.sql

# Export data
$ mysqldump --no-create-info sakila > data.sql

Just to check that everything is fine, let’s reimport the data in a new database:

mysql> CREATE DATABASE sakila2;
Query OK, 1 row affected (0.00 sec)

$ mysql sakila2 < schema.sql 
$ mysql sakila2 < data.sql …
[Read more]
How to Drop the Trigger

If we speak about MySQL triggers, does anybody need a "Howto" on this topic? It must be simple if you know the trigger name to drop, just use DROP TRIGGER, as manual explains:

mysql> use test
Database changedmysql> select current_user();
| current_user() |
| root@localhost |
1 row in set (0.00 sec)
mysql> show triggers\G
*************************** 1. row ***************************
             Trigger: tr1
               Event: INSERT
               Table: t1

[Read more]
MySQL Hacks: Preventing deletion of specific rows

Recently, someone emailed me:I have a requirement in MYSQL as follows:
we have a table EMP and we have to restrict the users not delete employees with DEPT_ID = 10. If user executes a DELETE statement without giving any WHERE condition all the rows should be deleted except those with DEPT_ID = 10.

We are trying to write a BEFORE DELETE trigger but we are not able to get this functionality.

I have seen your blog where you explained about Using an UDF to Raise Errors from inside MySQL Procedures and/or Triggers. Will it helps me to get this functionality? Could you suggest if we have any other alternatives to do this as well?Frankly, I usually refer people that write me these things to a public forum, but this time I felt like …

[Read more]
Find friends of friends using MySQL

In a previous article, I've already talked about an optimized way to connect locations in a geographic point of view by using MySQL. In this manner, locations of pubs, drugstores, barbers or even users can be obtained. Communities, or perhaps I should use the newer term Social Networks, make use of the buddy network of indiviual members in addition to the geographical mapping. This has many psychological advantages, because new members can be integrated in an established network very easily and I'm more willing to become involved when I already know some of the members.

Read More »

Validating MySQL data entry with triggers: A quick look at the SIGNAL syntax

The latest MySQL 5.5 milestone release offers support for an ANSI/ISO standard feature called the SIGNAL syntax. You can use this syntax inside stored routines (including triggers) to raise an error condition which can be used to invoke specific error handling, or otherwise abort the stored routine. In addition, you can use the SIGNAL syntax to convey information about what went wrong, which may be used by the caller to handle the error.

I have written about MySQL data entry validation procedures in the past. At the time, MySQL did not support any proper means to raise an error condition inside a stored routine, and …

[Read more]
Partitioning with non integer values using triggers

Looking at Bug#47310, which is a feature request that I hear frequently when I talk about partitions, I wrote a comment, suggesting triggers to work around the limitation.
The reason for the limitation is that allowing arbitrary functions for partitioning was too complex and it was provoking crashes and other unpleasant side effects (see the discussion under bug#18198).
But if you use a trigger, the resulting column is a plain integer, and many of the side effects disappear. The drawback is that you need to add a column to your table, and you need to use that column when searching for data. With that in mind, you can implement the workaround quite easily.

USE test;

user_id int(10) NOT NULL,
[Read more]
MySQL Trigger Woes

After a period of inactivity I was hacking back on a Drupal project, I had taken a mysql dump from a production platform and imported into my local dev setup, just to have some realistic data.

All of a sudden some forms started failing with the following error:

user warning: There is no 'user'@'nonlocalhost registered query: insert into blah (stuff,morestuff) values (x,y) in /var/vhost/drupal-tree/includes/ on line 172.

My Drupal data connection was correct and working for selects etc.. only a limited set of inserts failed.

After some debugging I realised that the error was not Drupal related, running the same query on my MySQL console gave the same error.

ERROR 1449 (HY000): There is no 'user'@'nonlocalhost' registered

The error came from a trigger on the table I was inserting data into that had been created on the …

[Read more]
Showing entries 1 to 7