Home MySQL Blogs

Move to Italy

Days
Hours
Minutes
Seconds

Blogs

Support Wikipedia

Login Form



MySQL

My MySQL tipsvalid-rss-rogers




GALERA on Red Hat is 1,2,3? (Part 1) PDF Print E-mail
Written by Marco Tusa   
Wednesday, 23 November 2011 01:04

=====================

Let me say NO, but nothing really bad just annoying and thanks to Oli (always great) less annoying then how it could be.

Anyhow, I had to put all up in a RED HAT box on EC2.

So I start to take a look here an there to see what is the best way to do it, I also take the configurator from Severalnines, but for the moment I am not going to use it,

and in any case I never use it for the my.cnf parameters.

But what was the scope of all this?

Overview

=================

I have a customer that could be interested in using a scaling MySQL solution, for his business.

Requirements as usual are 100% HA (OK we know this is not possible), scaling in writes as much as possible, geographic distribution.

Data set will be starting from 40G (peanuts) to 6 Tb (well not so peanuts) in 2 years.

More the scalability need to be "out" and "in" so architecture need to be able to scale as we like in relation to the "seasons".

 

What from there?

=====================

Well first of all define the generic architecture that should serve better, then find few good candidates that could fit in from the technological part

do some POCs and take numbers...

So here we are POCs and Numbers but first of all Installation.

As said I need to do that using REDHAT 6 EL so no way to use the pre-compile version, but taking a try will not harm.

Time to test and see how it works.

What I want to have as start:

3 nodes as start

data replicate from to each node

do stress test

download and installation

=====================

Excluding the demo package (that I don't want to use)

Any how I have found the site confuse, what I have to take? the package with ONLY the wsrep patch, or also the galera provider, all none both?

wget http://launchpad.net/codership-mysql/5.5/5.5.15-21.2/+download/mysql-5.5.15-wsrep_21.2-linux-x86_64.tar.gz

I had to download and extract to see that the galera libraries where not in the wsrep version, noting bad having it clear and on the same page could help.

Then I took the file from several nine using the configurator ...

DO I REALLY need it ??

mmm let us see

use http://www.severalnines.com/galera-configurator/map

You will deploy on ec2, using redhat, and x86_64 architecture. You can also go into s9s-galera-xyz/scripts/install/.s9s/config

to make adjustments (but be careful) - if you get it wrong, refresh this page and start over with fresh deployment package.

To install:

Upload your keypair to /root/.ssh/Availng.pem on the cluster-control server

tar xvfz s9s-galera-1.0.0.tar.gz

cd s9s-galera-1.0.0/mysql/scripts/install

./deploy.sh 2>&1 |tee cc.log

A detailed log is also written to $HOME/s9s_deploy.log

As said I decide not to use it for the moment.

My directory layout will be:

 

+-- bin

+-- etc
+-- galera -> /opt/galera_templates/galera¦   +-- bi

¦   +-- lib64

¦   +-- shar

+-- games

+-- include
+-- lib
+-- lib64
¦   +-- perl5
+-- libexec
+-- mysql -> /opt/mysql
¦   +-- bi

¦   +-- data

¦   +-- docs

¦   +-- include

¦   +-- lib

¦   +-- man

¦   +-- mysql-test

¦   +-- scripts

¦   +-- share

¦   +-- sql-bench

¦   +-- support-files

+-- sbin

+-- share

¦   +-- applications

¦   +-- info

¦   +-- man

¦   +-- perl5
+-- src

 

Next I had to do the usual thing:

setup MySQL user and group

setup directories

Configuring my.cnf

 

Start Galera

==========

I am in rush (never good), so I did start and configure MySQL, but then when I run it:

111118 17:34:55 [Note] Event Scheduler: Loaded 0 events
111118 17:34:55 [Note] WSREP: wsrep_load(): loading provider library '/usr/local/galera/lib64/galera/libgalera_smm.so'
111118 17:34:55 [ERROR] WSREP: wsrep_load(): dlopen(): libssl.so.6: cannot open shared object file: No such file or directory
111118 17:34:55 [ERROR] WSREP: wsrep_load(/usr/local/galera/lib64/galera/libgalera_smm.so) failed..
111118 17:34:55 [Note] WSREP: wsrep_load(): loading provider library 'none'
111118 17:34:55 [ERROR] Aborting
111118 17:34:55 [Note] WSREP: Service disconnected.
111118 17:34:56 [Note] WSREP: Some threads may fail to exit.
111118 17:34:56  InnoDB: Starting shutdown...
111118 17:34:57  InnoDB: Shutdown completed; log sequence number 1595682
111118 17:34:57 [Note] /usr/local/mysql/bin/mysqld: Shutdown complete

Ok Ok I know they said that so no way I need to recompile and install.

As Oli wrote issue is set up all before so let me see for RED HAT:

Take MySQL as for Oli instructions http://www.fromdual.com/building-galera-replication-from-scratch

and patch: wget http://launchpad.net/codership-mysql/5.5/5.5.15-21.2/+download/mysql-5.5.15-wsrep_21.2.patch

apply patch and download the galera replicator

wget http://launchpad.net/galera/1.x/21.1.0/+download/galera-21.1.0-src.tar.gz

 

BEFORE COMPILE install

yum -y install openssl

yum -y install boost-devel

yum -y install openssl-devel.x86_64

yum -y install check-devel.x86_64

 

Then run :

scons

Have a coffee or two ...

cp libgalera_smm.so /opt/mysql/lib/plugin/

cp garb/garbd /opt/mysql/bin/

 

WOW I GOT IT

11122 18:19:53 [Note] WSREP: Flow-control interval: [8, 16]
111122 18:19:53 [Note] WSREP: Restored state OPEN -> JOINED (0)
111122 18:19:53 [Note] WSREP: Member 0 (galera1) synced with group.
111122 18:19:53 [Note] WSREP: Shifting JOINED -> SYNCED (TO: 0)
111122 18:19:53 [Note] WSREP: New cluster view: group UUID: 7bd4ac4b-1560-11e1-0800-19942f7ebcc0, conf# 1: Primary, number of nodes: 1, my index: 0, first seqno: 1, protocol version 1
111122 18:19:53 [Warning] WSREP: wsrep_notify_cmd is not defined, skipping notification.
111122 18:19:53 [Note] WSREP: Assign initial position for certification: 0, protocol version: 1
111122 18:19:53 [Note] WSREP: Synchronized with group, ready for connections
111122 18:19:53 [Warning] WSREP: wsrep_notify_cmd is not defined, skipping notification.

 

 

 

 

 

 

 

 

 

 

mysql> SHOW global STATUS LIKE "wsrep%";
+----------------------------+--------------------------------------+
| Variable_name | Value |
+----------------------------+--------------------------------------+
| wsrep_local_state_uuid | 7bd4ac4b-1560-11e1-0800-19942f7ebcc0 |
| wsrep_protocol_version | 1 |
| wsrep_last_committed | 0 |
| wsrep_replicated | 0 |
| wsrep_replicated_bytes | 0 |
| wsrep_received | 2 |
| wsrep_received_bytes | 135 |
| wsrep_local_commits | 0 |
| wsrep_local_cert_failures | 0 |
| wsrep_local_bf_aborts | 0 |
| wsrep_local_replays | 0 |
| wsrep_local_send_queue | 0 |
| wsrep_local_send_queue_avg | 0.000000 |
| wsrep_local_recv_queue | 0 |
| wsrep_local_recv_queue_avg | 0.000000 |
| wsrep_flow_control_paused | 0.000000 |
| wsrep_flow_control_sent | 0 |
| wsrep_flow_control_recv | 0 |
| wsrep_cert_deps_distance | 0.000000 |
| wsrep_apply_oooe | 0.000000 |
| wsrep_apply_oool | 0.000000 |
| wsrep_apply_window | 0.000000 |
| wsrep_commit_oooe | 0.000000 |
| wsrep_commit_oool | 0.000000 |
| wsrep_commit_window | 0.000000 |
| wsrep_local_state | 4 |
| wsrep_local_state_comment | Synced (6) |
| wsrep_cert_index_size | 0 |
| wsrep_cluster_conf_id | 1 |
| wsrep_cluster_size | 1 |
| wsrep_cluster_state_uuid | 7bd4ac4b-1560-11e1-0800-19942f7ebcc0 |
| wsrep_cluster_status | PRIMARY |
| wsrep_connected | ON |
| wsrep_local_index | 0 |
| wsrep_provider_name | Galera |
| wsrep_provider_vendor | Codership Oy <info@codership.com> |
| wsrep_provider_version | 1.dev(rXXXX) |
| wsrep_ready | ON |
+----------------------------+--------------------------------------+
38 rows IN SET (0.00 sec)
 
 

 

 

As state in the internal text file a note about the LIMITATIONS


1) Currently replication works only with InnoDB storage engine. Any writes to
tables of other types, including system (mysql.*) tables are not replicated.
However, DDL statements are replicated in statement level, and changes
to mysql.* tables will get replicated that way.
So, you can safely issue: CREATE USER...,
but issuing: INSERT INTO mysql.user..., will not be replicated.

2) DELETE operation is unsupported on tables without primary keys.
Rows in tables without primary keys may appear in different order on
different nodes. As a result SELECT...LIMIT... may return slightly
different sets.

3) Unsupported queries:
* LOCK/UNLOCK tables is not supported in multimaster configuration.
* lock functions (GET_LOCK(), RELEASE_LOCK()... )

4) Query log cannot be directed to table. If you enable query logging,
you must forward the log to a file:
log_output = FILE
Use general_log and general_log_file to choose query logging and the
log file name

5) Maximum allowed transaction size is defined by wsrep_max_ws_rows and
wsrep_max_ws_size. Anything bigger (e.g. huge LOAD DATA) will be rejected.

6) Due to cluster level optimistic concurrency control, transaction issuing
COMMIT may still be aborted at that stage. There can be two transactions
writing to same rows and committing in separate cluster nodes, and only one
of the them can successfully commit. The failing one will be aborted.
For cluster level aborts, MySQL/galera cluster gives back deadlock error
code (Error: 1213 SQLSTATE: 40001  (ER_LOCK_DEADLOCK)).

7) XA transactions can not be supported due to possible rollback on commit.

8) Currently supported only in master-slave mode without parallel applying
(will be fixed in the nearest release):
* cascading foreign key constraints,
* secondary unique indexes and
* primary varchar indexes with non-binary collation.


Creating the full environment
=======================

Now that I have all up and working, I can finally use my current EC2 as template and add boxes to my cluster.
From here the real Fun will start ...

 

NEXT
====

-> Expanding the cluster
-> Starting my dataset
-> Pushing on throttle and take numbers

 

References

http://www.codership.com/downloads/download-mysqlgalera/%22

http://www.codership.com/wiki/doku.php?id=galera_parameters

http://www.codership.com/products/mysql_galera

http://www.fromdual.com/building-galera-replication-from-scratch

http://codership.blogspot.com/2011/10/mysqlgalera-release-10-replication.html

http://www.severalnines.com/galera-configurator/map

Last Updated on Sunday, 18 August 2013 17:33
 
DISABLE/ENABLE or REPLACE=UPDATE on Triggers PDF Print E-mail
Written by Marco Tusa   
Sunday, 13 November 2011 17:54

Cold Case "DISABLE/ENABLE or REPLACE=UPDATE on Triggers"

The fact that MySQL is not supporting at his best triggers and Store Procedure, is something we all know quite well.

Because that, we normally do use them with caution, but the other day I was working on a simple action on a customer site, and I face something that I did not recognize immediately, because I was starting from a wrong assumption.

Let me describe the case. A customer needs to perform regular export of his data, and reload of it on a different site. To do that we put up an external procedure using "select into outfile" and "Load DATA in FILE".

Nothing special here.
Given we know what we were doing we also set the REPLACE or IGNORE keyword as manual: "The REPLACE and IGNORE keywords control handling of input rows that duplicate existing rows on unique key";

We disable UNIQUE and FOREIGN KEY checks as well, finally no replication or binary logging is in place, so no concern that side.

Then we run the import and got an error message :"ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'".
This time the message error was quite clear, and I should not have problem in recognize the issue, right? It is a primary key duplication, BUT and here there is the need of a BIG BUT, I am using the REPLACE keyword because I want my inserts from file overwrite whatever is in the table.

Given I have being use that for years without any issue, I (me fool) did not recognize at the first shot the rot reason of the error.

This is because in my mind the keyword "REPLACE" had a special meaning, that for obvious reasons it does not have in MySQL.

To simplify my life here I have replicate the issue using the test dataset Sakila, so if you want to have fun try it downloading the schema from MySQL site.


So what I have done:

  • Load schema/data
  • Take an export in CSV
  • take info about triggers constrains and so on:
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
DATABASE changed
mysql> SELECT T.`TRIGGER_SCHEMA`, T.`TRIGGER_NAME`, T.`EVENT_OBJECT_SCHEMA`, T.`EVENT_OBJECT_TABLE` FROM TRIGGERS T  WHERE T.`TRIGGER_SCHEMA` ='sakila';
+----------------+----------------------+---------------------+--------------------+
| TRIGGER_SCHEMA | TRIGGER_NAME         | EVENT_OBJECT_SCHEMA | EVENT_OBJECT_TABLE |
+----------------+----------------------+---------------------+--------------------+
| sakila         | customer_create_date | sakila              | customer           |
| sakila         | ins_film             | sakila              | film               |
| sakila         | upd_film             | sakila              | film               |
| sakila         | del_film             | sakila              | film               |
| sakila         | payment_date         | sakila              | payment            |
| sakila         | rental_date          | sakila              | rental             |
+----------------+----------------------+---------------------+--------------------+
6 rows IN SET (0.00 sec)
 
mysql> SELECT R.`ROUTINE_SCHEMA`, R.`ROUTINE_NAME`, R.`ROUTINE_TYPE`, R.`ROUTINE_BODY` FROM ROUTINES R 
    -> WHERE R.`ROUTINE_SCHEMA`= 'sakila';
+----------------+----------------------------+--------------+--------------+
| ROUTINE_SCHEMA | ROUTINE_NAME               | ROUTINE_TYPE | ROUTINE_BODY |
+----------------+----------------------------+--------------+--------------+
| sakila         | film_in_stock              | PROCEDURE    | SQL          |
| sakila         | film_not_in_stock          | PROCEDURE    | SQL          |
| sakila         | get_customer_balance       | FUNCTION     | SQL          |
| sakila         | inventory_held_by_customer | FUNCTION     | SQL          |
| sakila         | inventory_in_stock         | FUNCTION     | SQL          |
| sakila         | rewards_report             | PROCEDURE    | SQL          |
+----------------+----------------------------+--------------+--------------+
6 rows IN SET (0.00 sec)
 
mysql> SELECT R.`CONSTRAINT_SCHEMA`, R.`CONSTRAINT_NAME`, R.`UNIQUE_CONSTRAINT_SCHEMA`, R.`UNIQUE_CONSTRAINT_NAME`, R.`TABLE_NAME`, R.`REFERENCED_TABLE_NAME` FROM REFERENTIAL_CONSTRAINTS R 
    -> WHERE R.`CONSTRAINT_SCHEMA`='sakila' ORDER BY R.`REFERENCED_TABLE_NAME`;
+-------------------+---------------------------+--------------------------+------------------------+---------------+-----------------------+
| CONSTRAINT_SCHEMA | CONSTRAINT_NAME           | UNIQUE_CONSTRAINT_SCHEMA | UNIQUE_CONSTRAINT_NAME | TABLE_NAME    | REFERENCED_TABLE_NAME |
+-------------------+---------------------------+--------------------------+------------------------+---------------+-----------------------+
| sakila            | fk_film_actor_actor       | sakila                   | PRIMARY                | film_actor    | actor                 |
| sakila            | fk_store_address          | sakila                   | PRIMARY                | store         | address               |
| sakila            | fk_staff_address          | sakila                   | PRIMARY                | staff         | address               |
| sakila            | fk_customer_address       | sakila                   | PRIMARY                | customer      | address               |
| sakila            | fk_film_category_category | sakila                   | PRIMARY                | film_category | category              |
| sakila            | fk_address_city           | sakila                   | PRIMARY                | address       | city                  |
| sakila            | fk_city_country           | sakila                   | PRIMARY                | city          | country               |
| sakila            | fk_payment_customer       | sakila                   | PRIMARY                | payment       | customer              |
| sakila            | fk_rental_customer        | sakila                   | PRIMARY                | rental        | customer              |
| sakila            | fk_film_category_film     | sakila                   | PRIMARY                | film_category | film                  |
| sakila            | fk_film_actor_film        | sakila                   | PRIMARY                | film_actor    | film                  |
| sakila            | fk_inventory_film         | sakila                   | PRIMARY                | inventory     | film                  |
| sakila            | fk_rental_inventory       | sakila                   | PRIMARY                | rental        | inventory             |
| sakila            | fk_film_language_original | sakila                   | PRIMARY                | film          | LANGUAGE              |
| sakila            | fk_film_language          | sakila                   | PRIMARY                | film          | LANGUAGE              |
| sakila            | fk_payment_rental         | sakila                   | PRIMARY                | payment       | rental                |
| sakila            | fk_rental_staff           | sakila                   | PRIMARY                | rental        | staff                 |
| sakila            | fk_store_staff            | sakila                   | PRIMARY                | store         | staff                 |
| sakila            | fk_payment_staff          | sakila                   | PRIMARY                | payment       | staff                 |
| sakila            | fk_inventory_store        | sakila                   | PRIMARY                | inventory     | store                 |
| sakila            | fk_customer_store         | sakila                   | PRIMARY                | customer      | store                 |
| sakila            | fk_staff_store            | sakila                   | PRIMARY                | staff         | store                 |
+-------------------+---------------------------+--------------------------+------------------------+---------------+-----------------------+
22 rows IN SET (0.00 sec)
 

 


Now I have to reload the table FILM which has relation (constrains) and it has Triggers, ok let us take a look on them :

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
 
mysql> SELECT T.`TRIGGER_SCHEMA`, T.`TRIGGER_NAME`, T.`EVENT_OBJECT_SCHEMA`, T.`EVENT_OBJECT_TABLE`, T.`ACTION_STATEMENT` FROM INFORMATION_SCHEMA.TRIGGERS T  WHERE T.`TRIGGER_SCHEMA` ='sakila' AND T.`EVENT_OBJECT_TABLE` ='film';
+----------------+--------------+---------------------+--------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| TRIGGER_SCHEMA | TRIGGER_NAME | EVENT_OBJECT_SCHEMA | EVENT_OBJECT_TABLE | ACTION_STATEMENT                                                                                                                                                                                                                                                                       |
+----------------+--------------+---------------------+--------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| sakila         | ins_film     | sakila              | film               | BEGIN
    INSERT INTO film_text (film_id, title, description)
        VALUES (new.film_id, new.title, new.description);
  END 
                                                                                                                                                           |
| sakila         | upd_film     | sakila              | film               | BEGIN
    IF (old.title != new.title) OR (old.description != new.description)
    THEN
        UPDATE film_text
            SET title=new.title,
                description=new.description,
                film_id=new.film_id
        WHERE film_id=old.film_id;
    END IF;
  END |
 
| sakila         | del_film     | sakila              | film               | BEGIN
    DELETE FROM film_text WHERE film_id = old.film_id;
  END                                                                                                                                                                                                                     |
+----------------+--------------+---------------------+--------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
3 rows IN SET (0.00 sec)
 

 

My table inserts/update/delete records on the table FILM_TEXT, at any insert/update/delete using PRYMARY KEY on both involve table.

The table itself is quite simple:

 

1
2
3
4
5
6
7
8
9
10
11
12
13
mysql> SHOW CREATE TABLE film_text;
+-----------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| TABLE     | CREATE TABLE                                                                                                                                                                                                                                       |
+-----------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| film_text | CREATE TABLE `film_text` (
  `film_id` smallint(6) NOT NULL,
  `title` varchar(255) NOT NULL,
  `description` text,
  PRIMARY KEY (`film_id`),
  FULLTEXT KEY `idx_title_description` (`title`,`description`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 |
+-----------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row IN SET (0.00 sec)

 

 


It Is MyISAM because the full text (next time we will use Innodb full text ;) ), it use "film_id", as primary key.


At this point I think you should have already get why I was a fool, and where was my misunderstanding.

I was thinking that GIVEN I have REPLACE in primary key in the LOAD DATA IN FILE statement, MySQL was able to propagate this condition also to relate tables.

 

1
2
3
4
mysql> LOAD DATA INFILE '/tmp/sakila_film.csv' REPLACE INTO TABLE sakila.film  FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\"' LINES TERMINATED BY '\r\n';
ERROR 1062 (23000): Duplicate entry '1' FOR KEY 'PRIMARY'
mysql> 
 

 


So when I saw the error I was a little bit ... annoyed.

I did a quick check on the FILM_TEXT table:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
mysql> SELECT * FROM film_text LIMIT 10;
+---------+------------------+-----------------------------------------------------------------------------------------------------------------------+
| film_id | title            | description                                                                                                           |
+---------+------------------+-----------------------------------------------------------------------------------------------------------------------+
|       1 | ACADEMY DINOSAUR | A Epic Drama of a Feminist AND a Mad Scientist who must Battle a Teacher IN The Canadian Rockies                      |
|       2 | ACE GOLDFINGER   | A Astounding Epistle of a DATABASE Administrator AND a Explorer who must Find a Car IN Ancient China                  |
|       3 | ADAPTATION HOLES | A Astounding Reflection of a Lumberjack AND a Car who must Sink a Lumberjack IN A Baloon Factory                      |
|       4 | AFFAIR PREJUDICE | A Fanciful Documentary of a Frisbee AND a Lumberjack who must Chase a Monkey IN A Shark Tank                          |
|       5 | AFRICAN EGG      | A Fast-Paced Documentary of a Pastry Chef AND a Dentist who must Pursue a Forensic Psychologist IN The Gulf of Mexico |
|       6 | AGENT TRUMAN     | A Intrepid Panorama of a Robot AND a Boy who must Escape a Sumo Wrestler IN Ancient China                             |
|       7 | AIRPLANE SIERRA  | A Touching Saga of a Hunter AND a Butler who must Discover a Butler IN A Jet Boat                                     |
|       8 | AIRPORT POLLOCK  | A Epic Tale of a Moose AND a Girl who must Confront a Monkey IN Ancient India                                         |
|       9 | ALABAMA DEVIL    | A Thoughtful Panorama of a DATABASE Administrator AND a Mad Scientist who must Outgun a Mad Scientist IN A Jet Boat   |
|      10 | ALADDIN CALENDAR | A Action-Packed Tale of a Man AND a Lumberjack who must Reach a Feminist IN Ancient China                             |
+---------+------------------+-----------------------------------------------------------------------------------------------------------------------+
10 rows IN SET (0.00 sec)
 

 

And yes data is there and at this point I must assume that this is the cause of the error.

So Marco stop dreaming about REPLACE cascade action and do something.

Cool, let me DISABLE the trigger such that I can reload the table on FILM without worries.

But oops I CANNOT disable a trigger, and more... what if the data I am inserting is the same for Primary key but not for the text, and I need to replace data in the DESCRIPTION fields?

Well I cannot do it without modifying the trigger itself.

So at the end I have to do something in the triggers like so instead of using

 

1
2
3
4
5
6
So at the end I have TO do something IN the triggers LIKE so instead of USING
DELIMITER ;;
CREATE TRIGGER `ins_film` AFTER INSERT ON `film` FOR EACH ROW BEGIN
    INSERT INTO film_text (film_id, title, description)
        VALUES (new.film_id, new.title, new.description);
END;;

 

We need to use

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
DELIMITER ;;
CREATE TRIGGER `ins_film` AFTER INSERT ON `film` FOR EACH ROW BEGIN
    INSERT INTO film_text (film_id, title, description)
        VALUES (new.film_id, new.title, new.description)  ON DUPLICATE KEY UPDATE film_id=new.film_id;
  END;;  
 
  mysql> DROP TRIGGER sakila.ins_film ;
Query OK, 0 rows affected (0.07 sec)
 
mysql> DELIMITER ;;
mysql> CREATE TRIGGER `ins_film` AFTER INSERT ON `film` FOR EACH ROW BEGIN
    ->     INSERT INTO film_text (film_id, title, description)
    ->         VALUES (new.film_id, new.title, new.description)  ON DUPLICATE KEY UPDATE film_id=new.film_id;
    ->   END;;  
Query OK, 0 rows affected (0.11 sec)
 

 

Which obviously will work fine:

1
2
3
4
 mysql> LOAD DATA INFILE '/tmp/sakila_film.csv' REPLACE INTO TABLE sakila.film  FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\"' LINES TERMINATED BY '\r\n';
Query OK, 1000 rows affected (0.57 sec)
Records: 1000  Deleted: 0  Skipped: 0  Warnings: 0
 

 

Problem solve, and all fine.
Really? Not really!

I have few comments here and I met two annoying issue during my exercise.

 

The first one is that I am still not able to disable the triggers, this is a feature request pending from 2005 http://bugs.mysql.com/bug.php?id=14661, we can discuss the best way to implement it, but not the fact that it is a NEEDED feature.

 

Second if I use REPLACE on LOAD DATA INFILE, I could expect a "Cascade" behaviour of the REPLACE action, at least when I involve the primary key in the Trigger.
That actually should be quite simple, like FIRING the UPDATE trigger instead the INSERT, not rocket science, no need to check what you are doing in the trigger, REPLACE=UPDATE in Trigger action.

In term of suggestion to the others, I can only say ... "REVIEW the triggers code to include the possible case of duplicate inserts", but I don't like it.
I think that we should go for a more elegant way to run it, then modify the trigger code, which was a simple operation here, but that could not be always the case.


Is this a critical issue? No we can stay as we did so far.
Is this something easy to solve? I think so.

Would make any sense to change it as I describe? I think so.
More I would like to say that I don't understand how we can have the option to disable FOREIGN key in place and STILL not have it for Triggers.

That is for me another annoying Cold Case, also if takes me few minutes to fix it!

 

Reference:

http://dev.mysql.com/doc/refman/5.1/en/load-data.html

http://dev.mysql.com/doc/refman/5.1/en/insert-speed.html

http://dev.mysql.com/doc/refman/5.1/en/stored-program-restrictions.html

http://drdobbs.com/database/231902587?pgno=1

 

{joscommentenable}

Last Updated on Sunday, 18 August 2013 17:34
 
MySQL COLD CASE PDF Print E-mail
Written by Marco Tusa   
Sunday, 13 November 2011 17:52

In the lasts weeks, I choose to stay silent, to try to see and read other people blogs with a new eye. I was trying to go in depth of any single article, reading investigating and double-checking.

There were a lot of news and things going on, not all new and not all really interesting for me, but at the end, enough to le me say that I had spent many nights up reading and studying, instead leading ahead my own projects.

But what was surprise me as well, was that at the end what is very often interesting as a NEW feature or cool coming solution, it is not so interesting in the day by day work.

More what happens is sometime, or I should say too often, the new things that are cool development, are built on the rubble left behind.

Too often we have to still deal with annoying OLD bugs, or feature requests that are "by the facto" standard in almost all decent Database platforms.

Just in the last page (as for the time of writing) of Planet MySQL, I can see mention of the Microsecond issue from Chris Calendar, the way how MySQL handle temporary tables in "A More Perfect UNION " and last but not least my annoying lack of trigger DISABLE/ENABLE function.

So at the end I found myself looking to what I start to call "Cold Case" in my mind. I also star to thought "how I can help"? Well not forgetting them, and instead write and write again about them every time I will find, hoping to be able to propose also some works around, whenever possible.

This is why I have open a series of "Cold Case".

I also invite you all to do not forget and do not leave them behind. Write about your Cold Case and let us push for having them solve once for all.

{joscommentenable}

Last Updated on Sunday, 18 August 2013 17:35
 
What about MySQL-Proxy PDF Print E-mail
Written by Marco Tusa   
Saturday, 01 October 2011 21:08

This post is not technical!

 

Who knows me, knows that I am not a nasty person.

I try to be polite and politically correct, and that has being a problem for me several times, because it pays only in long term.

 

But to day I need to talk about MySQL-Proxy so I can say something not pleasant for someone.

sakila_proxy_256x298

I have found myself in trouble several time because MySQL-Proxy for stability reasons and performance issues, but a part from that it normally works as expected.
Is really up to you and how you uses normally if you will get in trouble or not.

But ... hey MySQL-Proxy is an ALPHA product, oh my!!

 

It is an ALPHA not even BETA or RC, more reading the documentation TODAY:
"This documentation covers MySQL Proxy 0.8.0.
Warning

MySQL Proxy is currently an Alpha release and should not be used within production environments."

 

Honestly I have being mention MySQL -Proxy several times to customer but I always say to them, IT IS ALPHA, if you want I will install it,
I will configure and write for you special LUA scripts, BUT you must sign a paper that state you take the responsibility of installing an ALPHA product in production.

Normally the 90% of customers stops to go ahead, and try to find a different solution.

 

Too bad because MySQL-Proxy if correctly maintained could be an incredible tool, and ouch!! I discover this bug http://bugs.mysql.com/bug.php?id=61474.

Well you know is an ALPHA product so, I say to myself too bad, we should not expect a fast resolution.
We wait for a while then nothing really "serious" happened there, no fix, nothing, so we decide to drop it from the architecture.
The customer was not happy and neither I.

 

But I take note of it, and given I KNOW that MySQL Enterprise monitor use MySQL-Proxy I decide to dedicate some time to check, if this is still the case or not.

 

Today, I had a discussion with my 9 years Son, and to relax a little I put myself in front of some fun ... working with MySQL, yes I still enjoy it.

So I take out the Enterprise installation I did install it and then test it.

As expected MySQL-Proxy is still there, and documentations states:
"Using the MySQL Proxy functionality built into MySQL Enterprise Agent. This is the method offered and supported since MySQL Enterprise Monitor 2.0."

All good then, we have the possibility to add our own monitor or to use the default one provided by Oracle.

Hey wait a minute, is this a GA product?
No it is an Enterprise Product!

So why the MySQL Enterprise Monitor is using as default solution for an important functionality as the Slow Query Review an ALPHA product?

This sounds really odd to me.


Given I think no one will be happy to pay money for an ALPHA product, that has also issue with the latest MySQL versions, and given I think that lawyers in Oracle knows quite well their works.
I come to the conclusion that the MySQL-Proxy included in the Enterprise version is different from the one available.

Or at least I hope that for all the ones that are paying solid money for MySQL Enterprise Manager.

 

In any case, it is very sad that good and valuable work done in the past years by Jan Kneschke, and others like GiusePPe Maxia, is left behind damaging the community.

MySQL-Proxy is still the only solution for many stupid issues left behind in the MySQL programming, like user audit.

 

I recently had to implement a detailed audit of the actions done by Administrator on a MySQL instance.
The only very good and manageable solution was at the end the use of MySQL-Proxy, I will describe the work done in another post.

 

What I want to stress and reiterate here is:question-mark

  • IF MySQL-Proxy is so good to be in the MySQL Enterprise Monitor, that we assume it should be the top of stability, why then is still declared ALPHA??
  • IF MySQL-Proxy in Enterprise is a different product, then WHY it is not state in a clear way somewhere?
  • Finally WHY MySQL-Proxy is not correctly fixed and align with the latest MySQL products?


Simple questions that comes to my mind, I am not expecting an answer but may be some paying customers,
will have the same doubts I have, and it could be that given they pay they will get a real answer back from Oracle.

In that case please let us know.


Great MySQL to ALL!!!

 

{joscommentenable}

Last Updated on Sunday, 18 August 2013 17:36
 
Oracle goes for Money PDF Print E-mail
Written by Marco Tusa   
Friday, 16 September 2011 16:57

So what? nothing new ... from MySQL AB time.

at http://blogs.oracle.com/MySQL/entry/new_commercial_extensions_for_mysql

I saw it and I smile, hey I am happy to see that seriously!!


What I saw there were feature really Enterprise oriented AND really good for them as well, I though "GOOD STUFF", her what Rob report:

MySQL Enterprise Scalability

  • Thread Pool

MySQL Enterprise High Availability

  • Oracle VM Template for MySQL Enterprise Edition
  • Windows Clustering for MySQL Enterprise Edition

MySQL Enterprise Security

  • External Authentication for PAM
  • External Authentication for Windows

This is in line with what was discussed and discussed and discussed and discussed in MYSQL AB from 2006.

What do you think it would have being happen if unless being bought by SUN MySQL would have gone public?

Do you remember we did not have "Falcon" because it was not really scaling right, and we did invest a lot of money there,
so what will have MySQL done to recover some money and to be able to find an alternative solution?

MySQL will have done exactly the same because it makes a lot of sense, it is a logical path to follow in a open market.
What stops MySQL was not the fact that people in MySQL were good and now the Oracle guys are bad.
What stop it was the SUN acquisition, and the fact that SUN was so far away from MySQL  that they don't really understand what would be good to go for, Period.

Oracle is just following not only his internal model, but something that was already up in the air from quite long.
MySQL needs to be Enterprise oriented, and in order to do that needs to have spacial features that are not available for the community version.

What I am really whatching is not if Oracle ask money for Enterprise feature, but that Oracle will not leave the community version behind.
I don't want to see MYSQL GA becoming a "B" series product, what I am concern is to have the same core feature, and to have in the enterprise ONLY the added ones specifically focus for the Enterprise customers.

I am exited to try the new features, and I will have the chance to do it downloading the MySQL enterprise trial.

I strongly recommend to read Giuseppe blog (http://datacharmer.blogspot.com/2011/09/welcome-mysql-commercial-extensions.html),

What will be tomorrow I cannot say, but so far so good, Go MySQL and (I will never imagine I would say that) go Oracle, good Job!

As usual my 2cent in supporting the cause.

 

Dilbert.com

tks to dilbert site as usual is a source of humor for full reference go to:

{joscommentenable}

Last Updated on Sunday, 18 August 2013 17:36
 
«StartPrev12345678910NextEnd»

Page 9 of 12
 

Connecting from

Your IP: 54.162.168.187

Location:

Who's Online

We have 55 guests online