Archive

Archive for the ‘MySQL’ Category

MySQL loops and cursors with examples

December 16, 2013 No comments

First of all, you may not abuse of these techniques, and you must use them only when necessary. Most of the times you can find a faster solution.

Let’s see an easy loop, similar to a for loop, we will make a SELECT x with x from 1 to 9:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
DELIMITER $$
CREATE PROCEDURE simple_loop ( )
BEGIN
  DECLARE counter BIGINT DEFAULT 0;
 
  my_loop: LOOP
    SET counter=counter+1;

    IF counter=10 THEN
      LEAVE my_loop;
    END IF;

    SELECT counter;

  END LOOP my_loop;
END$$
DELIMITER ;

if we do:

1
CALL simple_loop();

We’ll see something like

+———+
| counter |
+———+
| 1 |
+———+
1 row in set (0.01 sec)

+———+
| counter |
+———+
| 2 |
+———+
1 row in set (0.01 sec)

+———+
| counter |
+———+
| 3 |
+———+
1 row in set (0.01 sec)

+———+
| counter |
+———+
| 4 |
+———+
1 row in set (0.01 sec)

+———+
| counter |
+———+
| 5 |
+———+
1 row in set (0.01 sec)

+———+
| counter |
+———+
| 6 |
+———+
1 row in set (0.01 sec)

+———+
| counter |
+———+
| 7 |
+———+
1 row in set (0.01 sec)

+———+
| counter |
+———+
| 8 |
+———+
1 row in set (0.01 sec)

+———+
| counter |
+———+
| 9 |
+———+
1 row in set (0.01 sec)

Query OK, 0 rows affected (0.01 sec)

The code we iterate is between LOOP…END LOOP. What we see right before (my_loop) is a label, just to give a name to that loop, and reference it. In this example, we simply increment counter variable, and with a condition we can exit the loop when this variable reaches the value 10. We won’t see the 10 because we leave the loop before printing this number.

Let’s do something a bit more complicated, we will record scores in a game, this game will be a skill test to do in the shortest time as possible, hopscotch jumping and with obstacles. There are two types of penalty, touch the ground with both feet and hitting an obstacle. At the end of the tests the results will be written on a table, and a score will be assigned, this score will be also stored on that table to avoid calculating it each time.

1
2
3
4
5
6
7
8
9
CREATE TABLE Runners (
    Runner_id BIGINT NOT NULL AUTO_INCREMENT,
    Name VARCHAR(120) NOT NULL,
    Time BIGINT NOT NULL,
    Penalty1 BIGINT NOT NULL,
    Penalty2 BIGINT NOT NULL,
    Points BIGINT,
    PRIMARY KEY (Runner_id)
) ENGINE=InnoDB DEFAULT CHARSET=UTF8;

Now, enter some test information:

1
2
3
4
5
6
7
INSERT INTO Runners VALUES (NULL, 'Michael', 123, 5, 2, NULL);
INSERT INTO Runners VALUES (NULL, 'Sarah', 83, 3, 3, NULL);
INSERT INTO Runners VALUES (NULL, 'John', 323, 1, 1, NULL);
INSERT INTO Runners VALUES (NULL, 'Ramon', 100, 8, 4, NULL);
INSERT INTO Runners VALUES (NULL, 'Andrew', 143, 4, 3, NULL);
INSERT INTO Runners VALUES (NULL, 'Antoine', 199, 3, 2, NULL);
INSERT INTO Runners VALUES (NULL, 'David', 101, 2, 1, NULL);

The first thing to do is a stored procedure with the basic loop, and a SELECT in each iteration, just to see that we are doing ok. (I will explain the code later):

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
DROP PROCEDURE IF EXISTS cursorTest;
DELIMITER $$
CREATE PROCEDURE cursorTest (
) BEGIN
-- Variables where we will store what the SELECT returns
  DECLARE v_name VARCHAR(120);
  DECLARE v_time BIGINT;
  DECLARE v_penalty1 BIGINT;
  DECLARE v_penalty2 BIGINT;
-- Variable to control the end of the loop
  DECLARE fin INTEGER DEFAULT 0;

-- El SELECT to query
  DECLARE runners_cursor CURSOR FOR
    SELECT Name, Time, Penalty1, Penalty2 FROM Runners;

-- Exit condition
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET fin=1;

  OPEN runners_cursor;
  get_runners: LOOP
    FETCH runners_cursor INTO v_name, v_time, v_penalty1, v_penalty2;
    IF fin = 1 THEN
       LEAVE get_runners;
    END IF;

  SELECT v_name, v_time, v_penalty1, v_penalty2;

  END LOOP get_runners;

  CLOSE runners_cursor;
END$$
DELIMITER ;

Must have in mind something. With the CURSOR we will go through the result of a SELECT statement, and we’ll have to store the values returned for each row in variables (that’s why I declared v_name, v_time, v_penalty1 and v_penalty2). In the end, each iteration will do a SELECT Name, Time, Penalty1, Penalty2 INTO v_name, v_time, v_penalty1, v_penalty2 WHERE …, so we’ll have these variables filled with the data obtained for each row in each iteration. That’s DECLARE xxx CURSOR FOR SELECT …

We must put a finish condition, usually we will finish the loop when no more results are found, that’s why we use DECLARE CONTINUE HANDLER FOR NOT FOUND SET fin=1, In this case, we will set fin to 1 when no more rows are found.

Inside the loop, we will test the value of this variable and LEAVE the loop if fin is set to 1, it’s automatically done when we reach the condition given before.

One step more, let’s create a function to assign the scores to each one of the runners with a formula. For example, if Time is the time taken in seconds, 500-Time will be the initial score and we will take away 5*penalty+3*penalty2. So:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
DROP FUNCTION IF EXISTS calculate_runner_points;
DELIMITER $$
CREATE FUNCTION calculate_runner_points (
  In_time BIGINT,
  In_penalty1 BIGINT,
  In_penalty2 BIGINT
) RETURNS BIGINT
BEGIN
  DECLARE points BIGINT;
 
  SET points = 500 - In_time - In_penalty1*5 - In_penalty2*3;
 
  RETURN points;
END$$
DELIMITER ;

Now, this is the code to calculate the final score of all players:

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
DROP PROCEDURE IF EXISTS calculate_all_points;
DELIMITER $$
CREATE PROCEDURE calculate_all_points (
) BEGIN
-- Variables where we will store what the SELECT returns
  DECLARE v_name VARCHAR(120);
  DECLARE v_time BIGINT;
  DECLARE v_penalty1 BIGINT;
  DECLARE v_penalty2 BIGINT;
  DECLARE v_runner_id BIGINT;
-- Variable to control the end of the loop
  DECLARE fin INTEGER DEFAULT 0;

-- SELECT to query
  DECLARE runners_cursor CURSOR FOR
    SELECT Runner_id, Name, Time, Penalty1, Penalty2 FROM Runners;

-- Exit condition
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET fin=1;

  OPEN runners_cursor;
  get_runners: LOOP
    FETCH runners_cursor INTO v_runner_id, v_name, v_time, v_penalty1,
v_penalty2;
    IF fin = 1 THEN
       LEAVE get_runners;
    END IF;

  UPDATE Runners SET Points=calculate_runner_points(v_time, v_penalty1,
v_penalty2) WHERE Runner_id=v_runner_id;

  END LOOP get_runners;

  CLOSE runners_cursor;
END$$
DELIMITER ;

Of course, as I said in the beginning of this post we must see if there is no other method to do the same. I know using MySQL loops is amazing, as I always say with regex, but there may be faster methods, like:

1
UPDATE Runners SET Points=calculate_runner_points(Time, Penalty1, Penalty2);

But, we can do more things with the loop, for example, if the time is greater than 250, we can swap penalties, editing the loop, with a IF statement, we could also do it in the procedure, but that’s an option.

A small example (or not so small) come to mi mind. Imagine we have a user system. Each user has its information stored in three tables: one for login, password and access info; another one for profile data and the last one for permissions. In this cas, all tables except permission table has one row per user. But as the permission table stores the access level for a user and another object (maybe a web page), and one single user can have permissions over several pages, there may be some rows for one user.

We will also have a table to store messages between users.

We will also have pages, these pages will be objects in our system, and users can see , edit, create derivates and delete them (if they are allowed to). A hierarchy may exist with pages, so we can have child pages, but when we create a new page:

  • If a user is allowed to edit a parent page, will be allowed to edit the new child page
  • If a user could create derivatives in a parent, will be also allowed in the child
  • If a user was allowd to edit and create derivatives in the parent page, will also be allowed to remove the child page.
  • We will have to send a message to the user telling him or her, about the new page and what is allowed to do with it.
  • We also have these procedures and functions:
    • can_create_derivatives(user, page) – It will return TRUE if the user can create derivatives
    • can_edit(user, page) – It will do the same, but with edit permission
    • new_permission(user, page, permission) – It will allow the user to do what permission says with this page
    • message(from, to, message) – Send a message to a user.

Functions can_create_derivatives() and can_edit() seem to be easy to understand, but what they do internally is far more complicated. It’s done by a colleague and I don’t want to fight with it. The same with new_permission() (it can insert rows or updates existent ones) or message(), it can send notifications and create a job to send real e-mail, so our procedure may be something like:

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
DROP PROCEDURE IF EXISTS create_page;
DELIMITER $$
CREATE PROCEDURE create_page (
  IN in_name VARCHAR(120),
  IN in_parent BIGINT
) BEGIN
  DECLARE v_user_id BIGINT;
  DECLARE v_create_derivatives TINYINT;
  DECLARE v_object_id BIGINT;
  DECLARE v_msg TEXT;

-- Variable to control the end of the LOOP
  DECLARE fin INTEGER DEFAULT 0;

-- The SELECT
  DECLARE users_cursor CURSOR FOR
    SELECT User_id FROM Users;

-- Exit condition
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET fin=1;

  INSERT INTO Pages (Name, Parent) VALUES (in_name, in_parent);
  SELECT LAST_INSERT_ID() INTO v_object_id;

  OPEN users_cursor;
  get_users: LOOP
    FETCH users_cursor INTO v_user_id;

    IF fin = 1 THEN
       LEAVE get_users;
    END IF;

    SET v_msg = CONCAT('New permissions on page ',in_name,': ');  

    IF can_create_derivatives(v_user_id, in_parent) THEN
      CALL new_permission(v_user_id, v_object_id, 'derivatives');
      SET v_msg = CONCAT(v_msg, 'Create derivatives ');
      SET v_create_derivatives=1;
    ELSE
      SET v_create_derivatives=0;
    END IF;

    IF can_edit(v_user_id, in_parent) THEN
      CALL new_permission(v_user_id, v_object_id, 'edit');
      SET v_msg = CONCAT(v_msg, 'Edit ');
      IF v_create_derivatives=1 THEN
         CALL new_permission(v_user_id, v_object_id, 'remove');
         SET v_msg = CONCAT(v_msg, 'Remove ');
      END IF;
    END IF;

    CALL message(1, v_user_id, v_msg);
  END LOOP get_users;

  CLOSE users_cursor;
END$$
DELIMITER ;

I’m sure I can give you better examples in the future. I accept suggestions in comments, so I can recreate them in my computer and solve them in future posts.

Photo: Kellie Bollaret (Flickr CC) Licensed CC-by

Logging to debug MySQL

October 30, 2013 No comments

It’s common, when using more or less complex stored procedures, to make mistakes that make us waste much time, and sometimes we would like to see the value of a variable or the result of a query because it could tell us what’s going wrong.

This is a little help, an easy piece of code we can insert into our database and will let is write in a table what’s happening.

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
DROP SCHEMA IF EXISTS `PBUtils` ;
CREATE SCHEMA IF NOT EXISTS `PBUtils` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci ;

USE PBUtils;

DROP TABLE IF EXISTS `PBUtils`.`LOG`;
CREATE TABLE IF NOT EXISTS `PBUtils`.`LOG` (
   `Log_id` bigint UNSIGNED NOT NULL AUTO_INCREMENT,
   `Log_date` DATETIME NOT NULL,
   `Log_description` varchar(128) NOT NULL,
   `Log_text` TEXT NULL,
   `Log_number` bigint NULL,
   `Log_float` double NULL,
   PRIMARY KEY(Log_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

DELIMITER $$
DROP PROCEDURE IF EXISTS `PBUtils`.`logText` $$
CREATE PROCEDURE `PBUtils`.`logText`(
    IN  In_description VARCHAR(128),
    IN  In_text TEXT
)
BEGIN
    INSERT INTO PBUtils.LOG (`Log_date`, `Log_description`, `Log_text`) VALUES (NOW(), In_description, In_text);
END $$
DELIMITER ;

DELIMITER $$
DROP PROCEDURE IF EXISTS `PBUtils`.`logNumber` $$
CREATE PROCEDURE `PBUtils`.`logNumber`(
    IN  In_description VARCHAR(128),
    IN  In_number bigint
)
BEGIN
    INSERT INTO PBUtils.LOG (`Log_date`, `Log_description`, `Log_number`) VALUES (NOW(), In_description, In_number);
END $$
DELIMITER ;

DELIMITER $$
DROP PROCEDURE IF EXISTS `PBUtils`.`logFloat` $$
CREATE PROCEDURE `PBUtils`.`logFloat`(
    IN  In_description VARCHAR(128),
    IN  In_float double
)
BEGIN
    INSERT INTO PBUtils.LOG (`Log_date`, `Log_description`, `Log_float`) VALUES (NOW(), In_description, In_float);
END $$
DELIMITER ;

Now, what we have to do is insert our procedure to debug values, anywhere in our code:

1
PBUtils.logText('Text to describe', variable);
1
PBUtils.logNumber('Text to describe', variable);
1
PBUtils.logFloat('Text to describe', variable);

depending on what we want to record, a text, number or float

To see what has been written in our log:

1
SELECT * FROM PBUtils.LOG ORDER BY Log_data DESC

Backup MySQL users

October 28, 2013 No comments

When we are dumping a MySQL database, is normal to ignore “mysql”, “test” and “information_schema” databases. We could have undesirable problems when restoring, for example if we change MySQL version.
But sometimes, when backing up the database we want to save also user table information. If we have several applications using the same database, we should have a user for each application and we don’t want to lose them.

So we could use the query SHOW GRANTS FOR [email protected] in MySQL. We could automate it to give us all user permissions with a little bash script:

1
2
USERINFO="-uroot -ppasswd"
mysql $USERINFO -BNe "select concat('SHOW GRANTS FOR \'',user,'\'@\'',host,'\'; ') from mysql.user where user != 'root'" | mysql $USERINFO -BN | sed 's/$/;/g' > users_backup.sql

In the USERINFO variable, we store login data for MySQL, we can use root user for this.

With the first call, we generate the queries we are going to make to the database to get user privileges generation SQL. We look who are the users, their hosts and we generate for each row the following string:

1
SHOW GRANTS FOR ‘user’@'host’;

In the second call, we make all queries, and it should return for each one of them something like this:

1
2
GRANT USAGE ON *.* TOuser’@'localhost’ IDENTIFIED BY PASSWORD ‘*1C3116A0C895332DE9EE63FFF08863CC9397ED8E’
GRANT ALL PRIVILEGES ON `database`.* TO ‘user’@'
localhost’

If the user has privileges on more tables or databases, they will appear here too.

The third call is to finish each line with ; (semicolon), so MySQL won’t give us problems when restoring them.

To restore the users just do:

1
mysql -uroot -ppaswwd < users_backup.sql

Restore root password in MySQL server

October 17, 2013 No comments

Sometimes, especially in our test server (or even production, who knows), we can lose our MySQL root password. This days we think about deleting everything, lose everything we’ve been working on, but we must be patient, we can’t destroy everything freely…

So, I leave here some easy steps (not only for you, but for me, I’m so absent-minded). We must have root privileges in the server we have MySQL installed:

1. First, stop MySQL service, depending the distribution, you may use one of these (or not, there can be more ways to do this)

1.1. $ service mysqld stop
1.2. $ /etc/init.d/mysqld stop
1.3. $ /etc/rc.d/mysqld stop

2. As root, we start MySQL service in a special way, ignoring privileges tables:

root # mysqld –skip-grant-tables

(you can use sudo on Ubuntu, for example)

Depending on your MySQL version (or distribution), it may not let you start as root, so we will specify the user, it will use:

root # mysqld -u mysql –skip-grant-tables &

In most systems the user will be mysql.

3. Access as superuser (as we are not checking privileges tables, we will have easy access). It can be done with a normal user. Use mysql database (or schema), that’s where the privileges tables are in.

$ mysql -u root mysql

4. From MySQL client we will update the password:

mysql> UPDATE mysql.user SET Password=PASSWORD(‘new password’) WHERE User=’root’;
mysql> FLUSH PRIVILEGES;

5. Exit mysql client

mysql> quit

6. Close mysqld (as root), it will take some seconds, we must be patient

$ killall mysqld

7. Restart MySQL server as we are used to, the same way we stopped it in step 1:

7.1. $ service mysqld start
7.2. $ /etc/init.d/mysqld start
7.3. $ /etc/rc.d/mysqld start

Let’s carry on working with our new root password

Top