Category Archives: Archives

Blog posts from db4free.blogspot.com

Dolphins in the Sky

January 21, 2009 Markus Popp Leave a comment

A cloudless night sky is truly amazing, but have you expected to see a dolphin in the sky?

What you see here is the Summer Triangle (all Southern hemisphere residents, please apologize), but did you notice the group of stars left to Altair?

This group of stars is the constellation named “Delphinus” (as can be guessed easily, it’s the Latin word for Dolphin), which can be observed best at almost all latitudes around late spring and early summer (in fact it can be seen almost around the year, but that’s the time of the year that you find this constellation highest in the sky).

So what is this all about? You may have heard that 2009 is the International Year of Astronomy. So there is astronomy, there is a dolphin and we all know that a dolphin (named Sakila) is the mascot of MySQL. Can that be brought together? Sure thing!

Hereby I declare that my contribution to the International Year of Astronomy shall be a series of articles that combine the fun of astronomy with the fun of MySQL. I am by no means a professional astronomer, it is only a little more than a year that I intensified my interest in astronomy (which had been dozing inside me all my life). What ignited the fire was that I stumbled over some great software, and I’d like to especially mention Celestia and Stellarium (Stellarium being what created the 2 images above). Now prepare for the good news: they are free (not only gratis, but Open Source as well) and available for Windows, MacOS and Linux (chances are good that your distribution includes them already). By installing them and exploring all their features (don’t forget to download the manuals! … they have a huge amount of functionality almost too much to discover all on your own) you are making the first big step into this fascinating world!

You don’t need much to do astronomy. A PC (or Mac), some software, an Internet connection (have I mentioned Wikipedia yet?) and a pair of binoculars will do. No more do I have. And chances are good that you have them as well. If you live in a big town or city, you may want to find an observation spot a bit outside of the urban area. You probably won’t need to drive hundreds of kilometers or miles though. I live in a small town (with plenty of street lights nearby) and can still spot celestial objects and features such as the Saturnian rings, Saturn’s moon Titan, the Galilean moons, all planets, some asteroids and dwarf planets such as Ceres, Vesta, Juno, Pallas or Nebulas, Star Clusters and Galaxies only with binoculars, all from my backyard. Don’t let more advanced astronomers who spend thousands of bucks for their hobby scare you. You need much less to get started, but if you like you can of course expand your budget without boundaries.

Watching the sky is only part of the fun. Many things are often observed but never much thought about. For example, have you ever wondered why in winter the moon is much higher in the sky than in summer? By getting involved in astronomy, many things like these become crystal clear and help you to get a deeper comprehension of the incomprehensible (imagine billions of galaxies each containing billions of stars spread across billions of light years). But there are even more interesting discoveries awaiting you. Did you know that you can calculate the position of any celestial object based on its coordinates for exactly your location for any time? Sure you know that it’s possible, but did you think that it’s fairly easy if you have some hints available? Much of astronomy is about data and mathematics and where data comes into play, there is also a playground for MySQL. Here is where the circle closes, but stay tuned for more exciting articles to come.

Archives

Running Stored Procedures on MySQL 4.x

November 11, 2006 Markus Popp 1 Comment

Is it possible to run Stored Procedures on MySQL 4.x? Yes and no – of course the feature is not available directly in MySQL 4.x, but if you have a MySQL 5.x server with the FEDERATED Storage Engine available, it’s no big deal to accomplish that.

Here’s how it works – we start with this on a MySQL 4.x server:

mysql> SELECT version();
+-----------+
| version() |
+-----------+
| 4.0.18-nt |
+-----------+
1 row in set (0.03 sec)

mysql> SHOW CREATE TABLE tt \G
*************************** 1. row ***************************
       Table: tt
Create Table: CREATE TABLE `tt` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `val` int(10) unsigned NOT NULL default '0',
  `ts` timestamp(14) NOT NULL,
  PRIMARY KEY  (`id`)
) TYPE=MyISAM
1 row in set (0.03 sec)

mysql> SELECT * FROM tt;
Empty set (0.03 sec)

The next step is to create a FEDERATED table with the same definition on a MySQL 5.x server:

mysql> SELECT version();
+-----------------+
| version()       |
+-----------------+
| 5.1.13-beta-log |
+-----------------+
1 row in set (0.00 sec)

mysql> CREATE TABLE `tt` (
    ->   `id` int(10) unsigned NOT NULL auto_increment,
    ->   `val` int(10) unsigned NOT NULL default '0',
    ->   `ts` timestamp(14) NOT NULL,
    ->   PRIMARY KEY  (`id`)
    -> ) ENGINE=FEDERATED
    -> CONNECTION='mysql://user:password@remote_4x_server/database/tt';
Query OK, 0 rows affected (0.72 sec)

All we need to do is to create a Stored Procedure on the MySQL 5.x server which accesses the tt FEDERATED table:

DROP PROCEDURE IF EXISTS fill_tt;

DELIMITER //

CREATE PROCEDURE fill_tt (_rows INT)
BEGIN
  DECLARE cnt INT DEFAULT 0;

  WHILE cnt < _rows DO
    INSERT INTO tt (val) VALUES (FLOOR(RAND()*1000));

    SET cnt := cnt + 1;
  END WHILE;
END //

DELIMITER ;

Now it’s simple to insert a few rows using this Stored Procedure:

mysql> CALL fill_tt(10);
Query OK, 1 row affected (0.69 sec)

mysql> SELECT * FROM tt;
+----+-----+---------------------+
| id | val | ts                  |
+----+-----+---------------------+
|  1 | 952 | 2006-11-23 01:57:54 |
|  2 | 591 | 2006-11-23 01:57:54 |
|  3 |  98 | 2006-11-23 01:57:54 |
|  4 | 718 | 2006-11-23 01:57:54 |
|  5 | 295 | 2006-11-23 01:57:54 |
|  6 | 324 | 2006-11-23 01:57:54 |
|  7 | 733 | 2006-11-23 01:57:54 |
|  8 | 693 | 2006-11-23 01:57:54 |
|  9 | 269 | 2006-11-23 01:57:54 |
| 10 | 264 | 2006-11-23 01:57:54 |
+----+-----+---------------------+
10 rows in set (0.09 sec)

Running the same query on the MySQL 4.x server proves that the records have been properly inserted:

mysql> SELECT version();
+-----------+
| version() |
+-----------+
| 4.0.18-nt |
+-----------+
1 row in set (0.03 sec)

mysql> SELECT * FROM tt;
+----+-----+----------------+
| id | val | ts             |
+----+-----+----------------+
|  1 | 952 | 20061123015754 |
|  2 | 591 | 20061123015754 |
|  3 |  98 | 20061123015754 |
|  4 | 718 | 20061123015754 |
|  5 | 295 | 20061123015754 |
|  6 | 324 | 20061123015754 |
|  7 | 733 | 20061123015754 |
|  8 | 693 | 20061123015754 |
|  9 | 269 | 20061123015754 |
| 10 | 264 | 20061123015754 |
+----+-----+----------------+
10 rows in set (0.03 sec)

Archives

Sorting of numeric values mixed with alphanumeric values

June 29, 2006 Markus Popp 13 Comments

Assume that you have a list of numeric values mixed with numeric
values appended with characters – like 2a, 12, 5b, 5a, 10, 11, 1, 4b.

Sorting on a list like this would usually end up so:

mysql> SELECT version
-> FROM version_sorting
-> ORDER BY version;
+---------+
| version |
+---------+
| 1       |
| 10      |
| 11      |
| 12      |
| 2a      |
| 4b      |
| 5a      |
| 5b      |
+---------+
8 rows in set (0.03 sec)

To sort the list based on the numeric values, simply do this:

mysql> SELECT version
-> FROM version_sorting
-> ORDER BY CAST(version AS UNSIGNED), version;
+---------+
| version |
+---------+
| 1       |
| 2a      |
| 4b      |
| 5a      |
| 5b      |
| 10      |
| 11      |
| 12      |
+---------+

8 rows in set, 4 warnings (0.00 sec)

Archives

A little (?) brain exercise

April 12, 2006 Markus Popp Leave a comment

I wanted to try out something that first seemed to be quite simple, but then I had to see that I didn’t manage to get the desired result in one single query. Maybe one of you has an idea how to do it.

Here’s the table that you need for this example and a little Stored Procedure to quickly fill the table:

CREATE TABLE tt (
  id int unsigned NOT NULL auto_increment,
  d1 int unsigned NOT NULL,
  d2 int unsigned NOT NULL,
  PRIMARY KEY (id),
  KEY d1 (d1)
) ENGINE=InnoDB;

… and the Stored Procedure …

DELIMITER //

DROP PROCEDURE IF EXISTS insertIntoTT //
CREATE PROCEDURE insertIntoTT(in _rows int)
begin
declare counter int default 0;

while counter < _rows do
  set @a := floor(rand() * 20) + 1;
  set @b := floor(rand() * 1000000);
  insert into tt (d1, d2) values (@a, @b);
  set counter := counter + 1;
end while;

end //

DELIMITER ;

With

CALL insertIntoTT(10000);

you can insert 10,000 rows and end up with a table like this:

mysql> SELECT * FROM tt LIMIT 10;
+----+----+--------+
| id | d1 | d2     |
+----+----+--------+
| 1  | 16 | 578121 |
| 2  | 12 | 209260 |
| 3  | 6  | 772868 |
| 4  | 1  | 801153 |
| 5  | 19 | 266106 |
| 6  | 11 | 845643 |
| 7  | 13 | 672265 |
| 8  | 9  | 162255 |
| 9  | 11 | 32461  |
| 10 | 14 | 156472 |
+----+----+--------+
10 rows in set (0.00 sec)

The numbers in column d1 are in the range from 1 to 20 and those in d2 are from 0 to 999,999.

Now I wanted to get the top 3 values from the d2 column for each value in d1, the output should look like this (at the bottom of this article, I’ll show you the Stored Procedure that produced the output):

mysql> CALL getOutput();
+------+----+--------+
| id   | d1 | d2     |
+------+----+--------+
| 8590 | 1  | 992748 |
| 5378 | 1  | 991187 |
| 8010 | 1  | 989804 |
| 4271 | 2  | 995870 |
| 6825 | 2  | 995212 |
| 7195 | 2  | 993449 |
| 7518 | 3  | 998659 |
| 327  | 3  | 994499 |
| 7523 | 3  | 992530 |
| 9637 | 4  | 997258 |
| 9505 | 4  | 986400 |
| 3577 | 4  | 984371 |
| 4474 | 5  | 998324 |
| 9616 | 5  | 990006 |
| 7856 | 5  | 987046 |
| 8149 | 6  | 995602 |
| 5339 | 6  | 994606 |
| 4791 | 6  | 991693 |
| 5956 | 7  | 999078 |
| 6360 | 7  | 995711 |
| 1280 | 7  | 994592 |
| 1449 | 8  | 997588 |
| 4993 | 8  | 995742 |
| 3966 | 8  | 994909 |
| 3755 | 9  | 996587 |
| 2537 | 9  | 996559 |
| 5372 | 9  | 994984 |
| 4907 | 10 | 998818 |
| 2512 | 10 | 997335 |
| 1156 | 10 | 996677 |
| 1653 | 11 | 999390 |
| 9901 | 11 | 998331 |
| 8307 | 11 | 998075 |
| 2526 | 12 | 998190 |
| 5922 | 12 | 996840 |
| 3005 | 12 | 991681 |
| 2632 | 13 | 991479 |
| 906  | 13 | 991448 |
| 2023 | 13 | 990165 |
| 3903 | 14 | 998612 |
| 7725 | 14 | 993921 |
| 5169 | 14 | 993021 |
| 6371 | 15 | 999566 |
| 5788 | 15 | 996766 |
| 7331 | 15 | 996545 |
| 8483 | 16 | 998472 |
| 1061 | 16 | 997509 |
| 6350 | 16 | 994854 |
| 2927 | 17 | 998590 |
| 8081 | 17 | 993340 |
| 6595 | 17 | 993104 |
| 4960 | 18 | 999275 |
| 6432 | 18 | 998478 |
| 2029 | 18 | 996769 |
| 1971 | 19 | 999992 |
| 1067 | 19 | 997117 |
| 3812 | 19 | 996488 |
| 9869 | 20 | 999248 |
| 8045 | 20 | 999102 |
| 3520 | 20 | 998395 |
+------+----+--------+
60 rows in set (0.11 sec)

Query OK, 0 rows affected (0.11 sec)

My question is, can I get the same output without a Stored Procedure, only with a normal query (including a subquery or a join). I tried three approaches.

My first one:

mysql> SELECT id, d1, d2
    -> FROM tt AS a
    -> WHERE id IN
    ->   (
    ->   SELECT id
    ->   FROM tt
    ->   WHERE d1 = a.d1
    ->   ORDER BY d2 DESC
    ->   LIMIT 3
    ->   )
    -> ORDER BY d1, d2 DESC;
ERROR 1235 (42000): This version of MySQL doesn't 
yet support 'LIMIT & IN/ALL/ANY/SOME subquery'

The subquery should retrieve the IDs of the rows that have values in the top 3 for column d2 – the where clause should make the connection. Unfortunately, the LIMIT clause is not allowed here.

My second attempt:

mysql> SELECT id, d1, d2
    -> FROM tt AS a
    -> WHERE d2 >=
    ->   (
    ->   SELECT d2
    ->   FROM tt
    ->   WHERE d1 = a.d1
    ->   ORDER BY d2 DESC
    ->   LIMIT 3, 1
    ->   )
    -> ORDER BY d1, d2 DESC;
ERROR 1242 (21000): Subquery returns more than 1 row

This time, I tried to get the third highest d2 value for each d1 value inside the subquery and get all columns, where d2 is greater or equal the retrieved value. Even though the subquery can only result in one row (because of the LIMIT clause), MySQL complains that it returns more than one row.

My third attempt was a bit desperate and might look a bit strange:

mysql> SELECT a.id, a.d1, a.d2
    -> FROM tt AS a INNER JOIN
    ->   (
    ->   SELECT id
    ->   FROM tt
    ->   WHERE d1 = a.d1
    ->   ORDER BY d2 DESC
    ->   LIMIT 3
    ->   ) AS b;
ERROR 1054 (42S22): Unknown column 'a.d1' in 
'where clause'

The derived second table in this join should again retrieve the IDs of all the top 3 rows. But the WHERE clause in the subquery can’t get the connection to the outer query.

Any ideas how to get the result in one query?

So, here’s the promised Stored Procedure that solved the problem in quite a complex manner:

DELIMITER //

DROP PROCEDURE IF EXISTS getOutput //

CREATE PROCEDURE getOutput()
BEGIN
DECLARE _found INT DEFAULT 0;
DECLARE _d1 INT DEFAULT 0;
DECLARE cur CURSOR FOR
  SELECT DISTINCT d1
  FROM tt
  ORDER BY d1;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET _found := 1;

DROP TEMPORARY TABLE IF EXISTS tt_temp;

CREATE TEMPORARY TABLE tt_temp (
  id INT UNSIGNED NOT NULL,
  d1 INT UNSIGNED NOT NULL,
  d2 INT UNSIGNED NOT NULL);

OPEN cur;

d1_loop:LOOP

  FETCH cur INTO _d1;

  IF _found = 1 THEN
    LEAVE d1_loop;
  END IF;

  INSERT INTO tt_temp
    SELECT id, d1, d2
    FROM tt
    WHERE d1 = _d1
    ORDER BY d2 DESC
    LIMIT 3;

END LOOP;

SELECT * FROM tt_temp;

END //


DELIMITER ;

Archives

The downside of information_schema

April 7, 2006 Markus Popp 2 Comments

In general, information_schema is a great thing – you can get all kinds of information out of it in quite a simple way.

But it has a downside – on a server with lots of databases and tables, it can be very slow. However, more and more applications rely on information_schema, and that can be a problem.

I just tried to update phpMyAdmin for db4free.net from 2.7.0-pl1 to 2.8.0.3. The update itself was a smooth thing, but when I tested the new version and tried to open a database, phpMyAdmin just couldn’t make it.

So I looked for the cause, and I found it quite quickly. I searched the whole phpMyAdmin directory for the string “information_schema” and there were quite a lot of results. When I did the same for the directory of phpMyAdmin 2.7.0-pl1, there were no results at all.

So while information_schema is good for many things, it might not be so good to use it extensively in administration applications, if it can be avoided.

Archives

Still no access to the original view definition

March 12, 2006 Markus Popp Leave a comment

One of the biggest annoyances in the current MySQL versions is that you still have no access to the original view definition (at least by SQL, there is a way through the file system, which I described in a former article).

So again, if you create a view like

CREATE VIEW v_checktest AS
SELECT id, val FROM checktest\n  
  WHERE val >= 1 AND val <= 5\n  
  WITH CHECK OPTION

SHOW CREATE VIEW v_checktest returns

mysql> SHOW CREATE VIEW v_checktest\G

*************************** 1. row ***************************
       View: v_checktest
Create View: CREATE ALGORITHM=UNDEFINED 
  DEFINER=`mpopp`@`localhost` 
  SQL SECURITY DEFINER VIEW `v_checktest` AS 
select `checktest`.`id` AS `id`,
  `checktest`.`val` AS `val` 
  from `checktest` 
  where ((`checktest`.`val` >= 1) 
    and (`checktest`.`val` <= 5)) 
  WITH CASCADED CHECK OPTION
1 row in set (0.00 sec)

A feature request about this issue was filed about 9 months ago, and there was a discussion at http://lists.mysql.com/internals/25571. I have added a little comment to the feature request, hoping that someone pays attention to this request again.

I have heard of other people too, who are waiting to see this being implemented. So if you do, don’t hesitate to add comments to this feature request. This might raise its priority.

I have also filed a feature request (I hope that it’s no duplicate – I have been looking for existing feature request, but didn’t find any) for SHOW TRIGGER STATUS, SHOW CREATE TRIGGER and DROP TRIGGER IF EXISTS commands.

I suppose that it’s most intuitive, if procedures, functions (where the according commands are already available), triggers, events, etc. work all with the same syntax.

Archives

How to calculate the sizes of your databases

March 12, 2006 Markus Popp Leave a comment

You may have seen several great queries how to get useful information out of information_schema – so here’s one more:

SELECT TABLE_SCHEMA,
  sum((DATA_LENGTH + INDEX_LENGTH) / (1024 * 1024)) as size_mb
FROM information_schema.TABLES
GROUP BY TABLE_SCHEMA
ORDER BY size_mb DESC

This query lists all of your databases with their sizes in megabytes in descending order.

If you only want to see those databases exceeding 1 MB, do the following:

SELECT TABLE_SCHEMA,
  sum((DATA_LENGTH + INDEX_LENGTH) / (1024 * 1024)) as size_mb
FROM information_schema.TABLES
GROUP BY TABLE_SCHEMA
HAVING size_mb > 1
ORDER BY size_mb DESC

Quite simple, but very effective!

Archives

The PROCEDURE ANALYSE option

March 1, 2006 Markus Popp Leave a comment

Andrew recently wrote about his plans to take the MySQL Certification exams and that he finds interesting new things that he learns from his studies. I can really confirm this – I also learnt a lot when I studied for my exams, which I took (and passed) in autumn last year.

This is a great opportunity to tell you about the PROCEDURE ANALYSE option, which I learn about in the Study Guide. I believe that this option isn’t well known, since I didn’t hear about it anywhere else than in the Study Guide – so it might be new to some people.

The best way to show you what it does is by an example – lets execute it on the sakila.film table (using the Sakila sample database, version 0.6):

 mysql> SELECT * FROM film PROCEDURE ANALYSE(10, 30)\G
*************************** 1. row ***************************
             Field_name: sakila.film.film_id
              Min_value: 1
              Max_value: 1000
             Min_length: 1
             Max_length: 4
       Empties_or_zeros: 0
                  Nulls: 0
Avg_value_or_avg_length: 500.5000
                    Std: 288.6750
      Optimal_fieldtype: SMALLINT(4) UNSIGNED NOT NULL
*************************** 2. row ***************************
             Field_name: sakila.film.title
              Min_value: ACADEMY DINOSAUR
              Max_value: ZORRO ARK
             Min_length: 8
             Max_length: 27
       Empties_or_zeros: 0
                  Nulls: 0
Avg_value_or_avg_length: 14.2350
                    Std: NULL
      Optimal_fieldtype: VARCHAR(27) NOT NULL
*************************** 3. row ***************************
             Field_name: sakila.film.description
             Min_value: A Action-Packed Character Study 
of a Astronaut And a Explorer who must Reach a 
Monkey in A MySQL Convention 
             Max_value: A Unbelieveable Yarn of a 
Student And a Database Administrator who must Outgun 
a Husband in An Abandoned Mine Shaft
             Min_length: 70
             Max_length: 130
       Empties_or_zeros: 0
                  Nulls: 0
Avg_value_or_avg_length: 93.8420
                    Std: NULL
      Optimal_fieldtype: VARCHAR(130) NOT NULL
*************************** 4. row ***************************
             Field_name: sakila.film.release_year
              Min_value: 2006
              Max_value: 2006
             Min_length: 4
             Max_length: 4
       Empties_or_zeros: 0
                  Nulls: 0
Avg_value_or_avg_length: 2006.0000
                    Std: 0.0000
      Optimal_fieldtype: ENUM('2006') NOT NULL
*************************** 5. row ***************************
             Field_name: sakila.film.language_id
              Min_value: 1
              Max_value: 1
             Min_length: 1
             Max_length: 1
       Empties_or_zeros: 0
                  Nulls: 0
Avg_value_or_avg_length: 1.0000
                    Std: 0.0000
      Optimal_fieldtype: ENUM('1') NOT NULL
*************************** 6. row ***************************
             Field_name: sakila.film.original_language_id
              Min_value: NULL
              Max_value: NULL
             Min_length: 0
             Max_length: 0
       Empties_or_zeros: 0
                  Nulls: 1000
Avg_value_or_avg_length: 0.0
                    Std: 0.0
      Optimal_fieldtype: CHAR(0)
*************************** 7. row ***************************
             Field_name: sakila.film.rental_duration
              Min_value: 3
              Max_value: 7
             Min_length: 1
             Max_length: 1
       Empties_or_zeros: 0
                  Nulls: 0
Avg_value_or_avg_length: 4.9850
                    Std: 1.4109
      Optimal_fieldtype: TINYINT(1) UNSIGNED NOT NULL
*************************** 8. row ***************************
             Field_name: sakila.film.rental_rate
              Min_value: 0.99
              Max_value: 4.99
             Min_length: 6
             Max_length: 14
       Empties_or_zeros: 0
                  Nulls: 0
Avg_value_or_avg_length: 2.980000
                    Std: 1.645570
      Optimal_fieldtype: DECIMAL(13, 2) NOT NULL
*************************** 9. row ***************************
             Field_name: sakila.film.length
              Min_value: 46
              Max_value: 185
             Min_length: 2
             Max_length: 3
       Empties_or_zeros: 0
                  Nulls: 0
Avg_value_or_avg_length: 115.2720
                    Std: 40.4061
      Optimal_fieldtype: TINYINT(3) UNSIGNED NOT NULL
*************************** 10. row ***************************
             Field_name: sakila.film.replacement_cost
              Min_value: 9.99
              Max_value: 29.99
             Min_length: 14
             Max_length: 14
       Empties_or_zeros: 0
                  Nulls: 0
Avg_value_or_avg_length: 19.984000
                    Std: 6.047807
      Optimal_fieldtype: DECIMAL(13, 2) NOT NULL
*************************** 11. row ***************************
             Field_name: sakila.film.rating
              Min_value: G
              Max_value: R
             Min_length: 1
             Max_length: 5
       Empties_or_zeros: 0
                  Nulls: 0
Avg_value_or_avg_length: 2.9260
                    Std: NULL
      Optimal_fieldtype: VARCHAR(5) NOT NULL
*************************** 12. row ***************************
             Field_name: sakila.film.special_features
              Min_value: Behind the Scenes
              Max_value: Trailers,Deleted Scenes,Behind the Scenes
             Min_length: 8
             Max_length: 54
       Empties_or_zeros: 0
                  Nulls: 0
Avg_value_or_avg_length: 28.0510
                    Std: NULL
      Optimal_fieldtype: SET NOT NULL
*************************** 13. row ***************************
             Field_name: sakila.film.last_update
              Min_value: 2006-02-14 22:03:42
              Max_value: 2006-02-14 22:03:42
             Min_length: 19
             Max_length: 19
       Empties_or_zeros: 0
                  Nulls: 0
Avg_value_or_avg_length: 19.0000
                    Std: NULL
      Optimal_fieldtype: ENUM('2006-02-14 22:03:42') NOT NULL
13 rows in set (0.01 sec)

You see, you get an interesting output about table statistics which can help you to optimize the table. Use the parameters after PROCEDURE ANALYSE to specify 1) the maximum number of elements and 2) the maximum number of characters to be used in the Optimal_fieldtype field. This can avoid the output of long ENUM lists (which might not be useful in most cases).

Lets change the values and see the effect:

 mysql> SELECT * FROM film PROCEDURE ANALYSE(3, 10)\G
*************************** 1. row ***************************
             Field_name: sakila.film.film_id
              Min_value: 1
              Max_value: 1000
             Min_length: 1
             Max_length: 4
       Empties_or_zeros: 0
                  Nulls: 0
Avg_value_or_avg_length: 500.5000
                    Std: 288.6750
      Optimal_fieldtype: SMALLINT(4) UNSIGNED NOT NULL
*************************** 2. row ***************************
             Field_name: sakila.film.title
              Min_value: ACADEMY DINOSAUR
              Max_value: ZORRO ARK
             Min_length: 8
             Max_length: 27
       Empties_or_zeros: 0
                  Nulls: 0
Avg_value_or_avg_length: 14.2350
                    Std: NULL
      Optimal_fieldtype: VARCHAR(27) NOT NULL
*************************** 3. row ***************************
             Field_name: sakila.film.description
             Min_value: A Action-Packed Character Study 
of a Astronaut And a Explorer who must Reach a 
Monkey in A MySQL Convention 
             Max_value: A Unbelieveable Yarn of a 
Student And a Database Administrator who must Outgun 
a Husband in An Abandoned Mine Shaft
             Min_length: 70
             Max_length: 130
       Empties_or_zeros: 0
                  Nulls: 0
Avg_value_or_avg_length: 93.8420
                    Std: NULL
      Optimal_fieldtype: VARCHAR(130) NOT NULL
*************************** 4. row ***************************
             Field_name: sakila.film.release_year
              Min_value: 2006
              Max_value: 2006
             Min_length: 4
             Max_length: 4
       Empties_or_zeros: 0
                  Nulls: 0
Avg_value_or_avg_length: 2006.0000
                    Std: 0.0000
      Optimal_fieldtype: YEAR NOT NULL
*************************** 5. row ***************************
             Field_name: sakila.film.language_id
              Min_value: 1
              Max_value: 1
             Min_length: 1
             Max_length: 1
       Empties_or_zeros: 0
                  Nulls: 0
Avg_value_or_avg_length: 1.0000
                    Std: 0.0000
      Optimal_fieldtype: TINYINT(1) UNSIGNED NOT NULL
*************************** 6. row ***************************
             Field_name: sakila.film.original_language_id
              Min_value: NULL
              Max_value: NULL
             Min_length: 0
             Max_length: 0
       Empties_or_zeros: 0
                  Nulls: 1000
Avg_value_or_avg_length: 0.0
                    Std: 0.0
      Optimal_fieldtype: CHAR(0)
*************************** 7. row ***************************
             Field_name: sakila.film.rental_duration
              Min_value: 3
              Max_value: 7
             Min_length: 1
             Max_length: 1
       Empties_or_zeros: 0
                  Nulls: 0
Avg_value_or_avg_length: 4.9850
                    Std: 1.4109
      Optimal_fieldtype: TINYINT(1) UNSIGNED NOT NULL
*************************** 8. row ***************************
             Field_name: sakila.film.rental_rate
              Min_value: 0.99
              Max_value: 4.99
             Min_length: 6
             Max_length: 14
       Empties_or_zeros: 0
                  Nulls: 0
Avg_value_or_avg_length: 2.980000
                    Std: 1.645570
      Optimal_fieldtype: DECIMAL(13, 2) NOT NULL
*************************** 9. row ***************************
             Field_name: sakila.film.length
              Min_value: 46
              Max_value: 185
             Min_length: 2
             Max_length: 3
       Empties_or_zeros: 0
                  Nulls: 0
Avg_value_or_avg_length: 115.2720
                    Std: 40.4061
      Optimal_fieldtype: TINYINT(3) UNSIGNED NOT NULL
*************************** 10. row ***************************
             Field_name: sakila.film.replacement_cost
              Min_value: 9.99
              Max_value: 29.99
             Min_length: 14
             Max_length: 14
       Empties_or_zeros: 0
                  Nulls: 0
Avg_value_or_avg_length: 19.984000
                    Std: 6.047807
      Optimal_fieldtype: DECIMAL(13, 2) NOT NULL
*************************** 11. row ***************************
             Field_name: sakila.film.rating
              Min_value: G
              Max_value: R
             Min_length: 1
             Max_length: 5
       Empties_or_zeros: 0
                  Nulls: 0
Avg_value_or_avg_length: 2.9260
                    Std: NULL
      Optimal_fieldtype: VARCHAR(5) NOT NULL
*************************** 12. row ***************************
             Field_name: sakila.film.special_features
              Min_value: Behind the Scenes
              Max_value: Trailers,Deleted Scenes,Behind the Scenes
             Min_length: 8
             Max_length: 54
       Empties_or_zeros: 0
                  Nulls: 0
Avg_value_or_avg_length: 28.0510
                    Std: NULL
      Optimal_fieldtype: SET NOT NULL
*************************** 13. row ***************************
             Field_name: sakila.film.last_update
              Min_value: 2006-02-14 22:03:42
              Max_value: 2006-02-14 22:03:42
             Min_length: 19
             Max_length: 19
       Empties_or_zeros: 0
                  Nulls: 0
Avg_value_or_avg_length: 19.0000
                    Std: NULL
      Optimal_fieldtype: TIMESTAMP NOT NULL
13 rows in set (0.01 sec)

You can see, there are no longer any ENUM fields.

Archives

Trigger privileges in MySQL 5.1.6

February 10, 2006 Markus Popp Leave a comment

There are more great news about MySQL 5.1.6.

MySQL 5.1.6 will introduce the new TRIGGER privilege. From then on, you won’t have to grant SUPER privilege (which allows much more than creating and dropping triggers, so it might not be a good choice to grant the SUPER privilege to non-administrators) anymore for users who should be able to deal with triggers. The exact details are described in the Change log for MySQL 5.1.6.

Archives

Foreign key dependencies

February 6, 2006 Markus Popp Leave a comment

Let me give you another great example that demonstrates the power of information_schema.

Assume that we create two tables which are combined by a foreign key constraint:

mysql> CREATE TABLE tt1 (
    ->   id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    ->   val INT UNSIGNED NOT NULL) ENGINE=InnoDB;
Query OK, 0 rows affected (0.08 sec)

mysql> CREATE TABLE tt2 (
    ->   id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    ->   id_1 INT UNSIGNED NOT NULL,
    ->   val INT UNSIGNED NOT NULL,
    ->   FOREIGN KEY (id_1) REFERENCES tt1(id)) ENGINE=InnoDB;
Query OK, 0 rows affected (0.02 sec)

What if you want to know if there are any tables that reference on table tt1 and which ones they are? Well, you could try to delete the table and you’ll get an error message telling you the foreign key constraints, if there are some. But if there aren’t any, your table will be lost – so definitely no good idea.

The solution is simple with MySQL 5 – information_schema provides all information that you need to pack this into a Stored Procedure (that I store in a database ‘funcs’):

mysql> DELIMITER //
mysql>
mysql> DROP PROCEDURE IF EXISTS getDeps //
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE PROCEDURE getDeps(pDatabase VARCHAR(200), 
                                pTable VARCHAR(200))
    -> SQL SECURITY INVOKER
    -> BEGIN
    ->
    ->   SET @sql := CONCAT('SELECT
    '>           CONCAT(REFERENCED_COLUMN_NAME, '' -> ('',
    '>                  TABLE_SCHEMA, ''.'',
    '>                  TABLE_NAME, '') '',
    '>                  COLUMN_NAME) AS `references`
    '>           FROM information_schema.KEY_COLUMN_USAGE
    '>           WHERE REFERENCED_TABLE_SCHEMA = ''', pDatabase, '''
    '>             AND REFERENCED_TABLE_NAME = ''', pTable, '''
    '>           ORDER BY TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME');
    ->
    ->   PREPARE pSql FROM @sql;
    ->
    ->   EXECUTE pSql;
    ->
    -> END //
Query OK, 0 rows affected (0.01 sec)

mysql>
mysql> DELIMITER ;

So try it out:

mysql> call funcs.getDeps(database(), 'tt1');
+-----------------------+
| references            |
+-----------------------+
| id -> (test.tt2) id_1 |
+-----------------------+
1 row in set (0.61 sec)

Query OK, 0 rows affected (0.61 sec)

Let’s add another table that references to tt1:

mysql> CREATE TABLE tt3 LIKE tt2;
Query OK, 0 rows affected (0.00 sec)

mysql> ALTER TABLE tt3
    ->   ADD FOREIGN KEY (id_1) REFERENCES tt1(id);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> call funcs.getDeps(database(), 'tt1');
+-----------------------+
| references            |
+-----------------------+
| id -> (test.tt2) id_1 |
| id -> (test.tt3) id_1 |
+-----------------------+
2 rows in set (0.53 sec)

Query OK, 0 rows affected (0.53 sec)

Works great!

However, initially I wanted to automatically use the current database inside the Stored Procedure, but there was a problem: the Stored Procedure resides in the database ‘funcs’. If we call the procedure from database ‘test’, the procedure would return the database ‘funcs’ if the procedure asks for database() – not the database ‘test’. That’s not very intuitive, I think – is it a bug?

However – you can still do great things with information_schema. Just let your mind flow.