http://collaborate12.ioug.org   Double Down at COLLABORATE 12- The IOUG Forum with Two Ways to Save- and a Chance to Win! The user-driven Oracle event of the year is fast-approaching, and IOUG wants you to make youreducational experience a sure bet. Between … Continue reading →

The first Central Virginia MySQL Meetup was a nice little howdy-do, and as a test flight, I think it showed that the bird can get off the ground quite nicely. So, with the generous help of our meeting host Meddius, we’re going to do it regularly on the 3rd Wednesday of every month. The next event is already scheduled — I will be talking about high availability options for MySQL.

I’m interested in having outside speakers. Anyone who’d like to come and present something MySQL-relevant, please feel free to email me, or contact me via the Meetup page with the “suggest a Meetup” feature. If you’re traveling from outside the area, the airport is CHO, and it’s about 30 minutes away. Amtrak is also …

Somebody asked for a simple comparison between a PL/SQL pass-by-value function and pass-by-reference procedure, where the procedure uses only an OUT mode parameter to return the result. This provides examples of both, but please note that a pass-by-value function can be used in SQL or PL/SQL context while a pass-by-reference procedure can only be used in another anonymous of named block PL/SQL program.

The function and procedure let you calculate the value of a number raised to a power of an exponent. The third parameter lets you convert the exponent value to an inverse value, like 2 to 1/2.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

CREATE OR REPLACE FUNCTION find_root_function
( pv_number   BINARY_DOUBLE
, pv_power    BINARY_DOUBLE
, pv_inverse  BINARY_INTEGER DEFAULT 0 ) RETURN BINARY_DOUBLE IS
 
  -- Declare local variable for return value. …

I recently wrote a post about inner and outer joins, and a couple of people asked what the difference is between USING and ON.

In a nutshell, you use ON for most things, but USING is a handy shorthand for the situation where the column names are the same.

Consider this example dataset:

mysql> select * from pets;
+---------+---------+--------+-----------+
| pets_id | animal  | name   | owners_id |
+---------+---------+--------+-----------+
|       1 | fox     | Rusty  |         2 |
|       2 | cat     | Fluffy |         2 |
|       3 | cat     | Smudge |         3 |
|       4 | cat     | Toffee |         3 |
|       5 | dog     | Pig    |         3 |
|       6 | hamster | Henry  |         1 |
|       7 | dog     | Honey  |         1 | …

I recently wrote a post about inner and outer joins, and a couple of people asked what the difference is between USING and ON.

In a nutshell, you use ON for most things, but USING is a handy shorthand for the situation where the column names are the same.

Consider this example dataset:

mysql> select * from pets;
+---------+---------+--------+-----------+
| pets_id | animal  | name   | owners_id |
+---------+---------+--------+-----------+
|       1 | fox     | Rusty  |         2 |
|       2 | cat     | Fluffy |         2 |
|       3 | cat     | Smudge |         3 |
|       4 | cat     | Toffee |         3 |
|       5 | dog     | Pig    |         3 |
|       6 | hamster | Henry  |         1 |
|       7 | dog     | Honey  |         1 | …

Join me Wednesday for a free webinar on using the new Percona Toolkit v2.0.3 to verify replication integrity. If you’re not familiar with this topic, it’s one of those must-do things that no one officially tells you is necessary with MySQL. The new tools in Percona Toolkit 2.0.3 make it much easier and less complicated — and safer — than before. Well worth an hour of your time.

Further Reading:

• Free webinar on preventing MySQL downtime
• Free webinar on MySQL performance this Thursday
• …

You can pre-order the new edition of High Performance MySQL now on Amazon or via O’Reilly’s website.

Eric Bergen called the second edition “the best MySQL book on the planet“. What will the third edition be called? The best in the solar system? This reminds me of a joke, supposed to be a true story: three pizza restaurants next to each other had signs saying “best pizza in town,” “best pizza in the world,” and “best pizza on this block.”

Further Reading:

• …

As a MySQL  consultant, I spend a lot of time talking with people about their backups. More specifically, we talk a lot about recovery. I had an interesting incident myself, which illustrates some of the things that are bound to happen as time passes.

First, let me explain how I do my personal backups. I have a series of external hard drives, which are fully encrypted, as is my computer’s hard drive. I maintain a full mirror of my computer’s hard drive on these external hard drives. I occasionally switch the hard drives out, and carry one or more of them to a bank’s safe deposit box. I try to do this once a week, but sometimes it isn’t quite that often.

As a result, I have one hard drive located physically near my computer, which contains a very recent backup of all my work. I have at least one, usually 3 or more, other copies of my data in a slightly less fresh format, but durably stored in a bank.

While …

I’ve created a Central Virginia MySQL Meetup group for those of you in the area. Our first event will be in a couple of weeks at Meddius’s headquarters. I’d like to meet and get to know more people in this area who use MySQL, so come out and we’ll have a good time together! Oh, and beer. And pizza. Of course.

Further Reading:

• Central Virginia MySQL Meetup has reached cruising altitude
• Speaking at MySQL Meetup in Northern Virginia
• …

As we can see in the solution:

Expression are evaluated in the column order in the select clause.

This is the same for the where clause.  An advice is to test first the expression that get more chance to return false at the head of the where conditions :   

1. SET @BRAND=0;
2. SET @ct=0;
3. SELECT
4.  *
5. FROM
6.  (SELECT
7.    IF(@BRAND<>id_BRAND,@ct:=0 ,@ct:=@ct ) ,
8.    @ct:=@ct+1 AS ct,   
9.    @BRAND:=id_BRAND,
10.    t2.*
11.   FROM
12.    (SELECT
13.      st.ID_BRAND,
14.      st.ID_MODEL,
15.      SUM(COMPTE) AS total,
16.      AVG(totalm)
17.   …