Aug
07

MySQL dynamic pivot table

I’m using Redmine to manage projects and bug reports, I was needing a daily report with spent time by user on each project, nothing complicated, but I wanted a cross-reference result like Excel dynamic tables. As we know, MySQL doesn’t support dynamic cross-reference queries but I make an approach:

1. Write the main query with rows: project identifier, task subject and total hours in a date range.

SELECT p.identifier, 
	e.issue_id, i.subject, round(SUM(e.hours),2) AS total_hours
	FROM redmine_nsp.time_entries e
	INNER JOIN redmine_nsp.issues i ON e.issue_id = i.id
	INNER JOIN redmine_nsp.projects p ON e.project_id = p.id
	WHERE e.spent_on BETWEEN '2014-07-01' AND '2014-07-07'
 GROUP BY p.identifier,e.issue_id;
+------------+----------+----------------------------+-------------+
| identifier | issue_id | subject                    | total_hours |
+------------+----------+----------------------------+-------------+
| bg02       |     3223 | Gestion de proyecto        |        0.25 |
| bg04       |     3256 | 1.1 Preparación del entor  |        0.63 |
| emision    |     3251 | Desarrollar la aplicación  |        3.97 |
| nsp00      |     3236 | Preparar propuesta para G  |        2.02 |
| nsp02      |     3234 | Subida al servidor de pro  |        0.52 |
| nsp02      |     3240 | Agregar funcionalidad de   |        0.55 |
| nsp02      |     3241 | Revertir el Documento      |        2.80 |
| nsp02      |     3242 | Agregar Filtros en las vi  |        0.72 |
| nsp02      |     3243 | Reportes de Cargo de entr  |        2.35 |
| nsp02      |     3254 | Control de sesiones        |        3.23 |
| nsp05      |     3252 | Mantenimiento del servido  |        0.18 |
| nsp05      |     3253 | Mantenimiento a redmine    |        0.53 |
| nsp06      |     3203 | Elaborar el visualzador d  |        0.23 |
| nsp06      |     3228 | Evitar que se tome mas de  |        0.25 |
| nsp06      |     3255 | Actualizar el porcentaje   |        1.50 |
| nsp08      |     3239 | Asistencia remota          |        1.38 |
+------------+----------+----------------------------+-------------+

2. Then list the active users between dates:

SELECT e.user_id, u.login
FROM redmine_nsp.time_entries e
	INNER JOIN redmine_nsp.users u ON e.user_id = u.id 
WHERE e.spent_on BETWEEN '2014-07-01' AND '2014-07-07'
GROUP BY e.user_id;
+---------+----------+
| user_id | login    |
+---------+----------+
|       1 | user1    |
|       4 | user2    |
|       5 | user3    |
|       6 | user4    |
+---------+----------+

3. Now, create a stored procedure to create a dynamic SQL command with the second query as column names and values with each Redmine issue. The second query is used as cursor.

DELIMITER $$
 
CREATE PROCEDURE `get_range_summary`(dDate1 DATE,dDate2 DATE)
BEGIN
 
DECLARE done INT DEFAULT 0;
DECLARE p_user_id INT;
DECLARE p_sql text;
DECLARE p_login VARCHAR(255);
DECLARE c_users cursor FOR
	SELECT e.user_id, u.login
	FROM redmine_nsp.time_entries e
		INNER JOIN redmine_nsp.users u ON e.user_id = u.id 
	WHERE e.spent_on BETWEEN  dDate1 AND dDate2 
	GROUP BY e.user_id;
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
 
SET p_sql = 'select s.codigo,s.issue_id,s.subject ';
OPEN c_users;
 
read_loop: LOOP
	FETCH c_users INTO p_user_id, p_login;
    IF done THEN
      LEAVE read_loop;
    END IF;
	SET p_sql = concat(p_sql,
      ', (select round(sum(t.hours),2) as total
		from redmine_nsp.time_entries t
		where t.spent_on between \'',dDate1,'\' and \'',dDate2,'\'  
        and t.issue_id = s.issue_id
		and t.user_id = ',p_user_id,') as `',p_login,'` ');
 
END LOOP;
 
SET @SQL = concat(p_sql,' ,s.total_hours from 
	(select p.identifier, e.issue_id, i.subject, 
         round(sum(e.hours),2) as total_hours
	from redmine_nsp.time_entries e
	inner join redmine_nsp.issues i on e.issue_id = i.id
	inner join redmine_nsp.projects p on e.project_id = p.id
	where e.spent_on between \'',dDate1,'\' and \'',dDate2,'\' group by p.identifier,e.issue_id) as s');
 
 
close c_users;
 
PREPARE stmt1 FROM @SQL;
EXECUTE stmt1;
DEALLOCATE PREPARE stmt1; 
 
END

The result of call get_range_summary(‘2014-07-01′,’2014-07-07’):

+---------+----------+----------------------------+-------+--------+---------+----------+-------------+
| identif | issue_id | subject                    | user1 | user2  |  user3  |   user4  | total_hours |
+---------+----------+----------------------------+-------+--------+---------+----------+-------------+
| bg02    |       23 | Gestion de proyecto        |  NULL |   0.25 |    NULL |     NULL |        0.25 |
| bg04    |       56 | 1.1 Preparación del entor  |  NULL |   0.63 |    NULL |     NULL |        0.63 |
| emision |       51 | Desarrollar la aplicación  |  NULL |   NULL |    3.97 |     NULL |        3.97 |
| nsp00   |       36 | Preparar propuesta para G  |  NULL |   2.02 |    NULL |     NULL |        2.02 |
| nsp02   |       34 | Subida al servidor de pro  |  NULL |   0.52 |    NULL |     NULL |        0.52 |
| nsp02   |       40 | Agregar funcionalidad de   |  NULL |   NULL |    0.55 |     NULL |        0.55 |
| nsp02   |       41 | Revertir el Documento      |  1.40 |   NULL |    1.40 |     NULL |        2.80 |
| nsp02   |       42 | Agregar Filtros en las vi  |  NULL |   NULL |    0.72 |     NULL |        0.72 |
| nsp02   |       43 | Reportes de Cargo de entr  |  NULL |   NULL |    NULL |     2.35 |        2.35 |
| nsp02   |       54 | Control de sesiones        |  NULL |   NULL |    NULL |     3.23 |        3.23 |
| nsp05   |       52 | Mantenimiento del servidor |  NULL |   0.18 |    NULL |     NULL |        0.18 |
| nsp05   |       53 | Mantenimiento a redmine    |  NULL |   0.53 |    NULL |     NULL |        0.53 |
| nsp06   |        3 | Elaborar el visualzador de |  NULL |   0.23 |    NULL |     NULL |        0.23 |
| nsp06   |       28 | Evitar que se tome mas de  |  NULL |   0.25 |    NULL |     NULL |        0.25 |
| nsp06   |       55 | Actualizar el porcentaje d |  NULL |   1.50 |    NULL |     NULL |        1.50 |
| nsp08   |       39 | Asistencia remota          |  NULL |   1.38 |    NULL |     NULL |        1.38 |
+---------+----------+----------------------------+-------+--------+---------+----------+-------------+

That’s it. I put the result easily in a php based report and added it to cron task.

Jan
09

MySQL, the strange case of a timestamp field

I discovered a feature (or bug) of MySQL timestamp fields. Maybe is documented in some place what I not read yet:

When I add a new timestamp field to a table, MySQL magically adds some features to new timestamp field like a “trigger” and a default value to CURRENT_TIMESTAMP.

There is test-case script:

-- CREATING TABLE AND INSERT SOME DUMMY DATA
mysql> CREATE TABLE t(
    -> id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
    -> val VARCHAR(50)
    -> );
Query OK, 0 ROWS affected (0.15 sec)
 
mysql> INSERT INTO t (val) VALUES ("foo") ,("var");
Query OK, 2 ROWS affected (0.08 sec)
Records: 2  Duplicates: 0  Warnings: 0
 
mysql> SELECT * FROM t;
+----+------+
| id | val  |
+----+------+
|  1 | foo  |
|  2 | var  |
+----+------+
2 ROWS IN SET (0.00 sec)
 
 
-- ADDING NEW TIMESTAMP FIELD AND ADD MORE DUMMY DATA
mysql> ALTER TABLE t ADD ts_field TIMESTAMP;
Query OK, 2 ROWS affected (0.35 sec)
Records: 2  Duplicates: 0  Warnings: 0
 
mysql> INSERT INTO t (val) VALUES ("foo 2") ,("var 2");
Query OK, 2 ROWS affected (0.06 sec)
Records: 2  Duplicates: 0  Warnings: 0
 
-- THERE IS THE MAGIC:
mysql> SELECT * FROM t;
+----+-------+---------------------+
| id | val   | ts_field            |
+----+-------+---------------------+
|  1 | foo   | 0000-00-00 00:00:00 |
|  2 | var   | 0000-00-00 00:00:00 |
|  3 | foo 2 | 2013-01-09 23:20:01 |    <---
|  4 | var 2 | 2013-01-09 23:20:01 |    <---
+----+-------+---------------------+
4 ROWS IN SET (0.00 sec)

What happened? I dont know.

The new table structure is:

CREATE TABLE `t` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `val` varchar(50) DEFAULT NULL,
  `ts_field` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1

It only happens if the table doesn’t have another timestamp field yet.

Now, this is useful? maybe. This is a bug? maybe.

Update: This is a feature documented in https://dev.mysql.com/doc/refman/5.5/en/timestamp-initialization.html, sorry about my lapsus-post.

Nov
30

MySQL get disk usage of all databases

I was tired to get manually disk space used for all MySQL databases, I just created a stored procedure to get an overview of the database sizes in our MySQL server.

MySQL don’t have a command that allows us an overall summary of the databases, something like SHOW TABLE STATUS for databases. The “SHOW DATABASES” command lists only the current databases without any other information like how many space are using or how many tables there are, etc.

The procedure that I wrote this based on INFORMATION_SCHEMA database, which contains rich information of all existing databases.

I suggest, in a previous post, place all these procedures in a database called tools, but you can still choose different location.

So when call the procedure will have a global view of information that will be useful for maintenance tasks.

 

mysql> call tools.sp_overview();
+------------------------------+---------+--------+----------+---------+
| Database                     | Charset | Tables | Routines | Size Mb |
+------------------------------+---------+--------+----------+---------+
| database1                    | utf8    |     43 |       28 |     7.0 |
| database2                    | latin1  |     43 |       28 |   205.0 |
| database3                    | utf8    |    116 |        0 |   126.2 |
| database4                    | utf8    |     99 |        0 |     0.3 |
| database5                    | utf8    |    165 |        0 |    77.4 |
| database6                    | utf8    |    121 |        2 |   719.4 |
| database7                    | utf8    |    122 |        0 |    91.3 |
| database8                    | utf8    |    116 |        0 |    89.7 |
| database9                    | utf8    |    124 |        0 |     4.5 |
| database10                   | utf8    |    113 |        0 |   147.7 |
| database11                   | latin1  |    119 |        3 |   436.4 |
| database12                   | latin1  |    122 |        0 |   439.1 |
| database13                   | latin1  |    122 |        4 |   452.7 |
| database14                   | utf8    |    115 |        0 |   273.0 |
| database15                   | utf8    |    122 |        0 |   265.5 |
| database16                   | utf8    |      0 |        0 |    NULL |
| database17                   | latin1  |     26 |        0 |     0.9 |
| database18                   | latin1  |      9 |        0 |     0.0 |
| database19                   | latin1  |     22 |        0 |     0.3 |
+------------------------------+---------+--------+----------+---------+
19 rows in set (3.01 sec)

 

The first time execution maybe you will get results in a few seconds.

There is the source code:

 

DELIMITER $$
 
DROP PROCEDURE IF EXISTS tools.sp_overview$$
CREATE PROCEDURE tools.sp_overview()
BEGIN
 
    SELECT s.SCHEMA_NAME AS `Database`, s.DEFAULT_CHARACTER_SET_NAME AS `Charset`,
        COUNT(t.TABLE_NAME) AS `Tables`,
 
        (SELECT COUNT(*) FROM information_schema.ROUTINES AS r
            WHERE r.routine_schema = s.SCHEMA_NAME) AS `Routines`,
 
         round(SUM(t.DATA_LENGTH + t.INDEX_LENGTH) / 1048576 ,1) AS `Size Mb`
 
        FROM information_schema.SCHEMATA AS s
            LEFT JOIN information_schema.TABLES t ON s.schema_name = t.table_schema
        WHERE s.SCHEMA_NAME NOT IN ('information_schema', 'performance_schema')
 
    GROUP BY s.SCHEMA_NAME;
END$$
DELIMITER ;
Feb
28

Custom auto increment values

The auto_increment for MySQL fields are very useful but what about if I need a custom auto-increment like a custom format like 001-000033,

001-000034
001-000035
001-000036
...

To make it possible we have an option based on past article MySQL Sequences:

  1. Create the table to store the current sequence values: 
    CREATE TABLE _sequence
(
    seq_name VARCHAR(50) NOT NULL PRIMARY KEY,
    seq_group VARCHAR(10) NOT NULL,
    seq_val INT UNSIGNED NOT NULL
);
  2. Create a function to get and increment the current value: 
    delimiter //
DROP FUNCTION IF EXISTS getNextCustomSeq//
CREATE FUNCTION getNextCustomSeq
(
    sSeqName VARCHAR(50),
    sSeqGroup VARCHAR(10)
) RETURNS VARCHAR(20)
BEGIN
    DECLARE nLast_val INT; 
 
    SET nLast_val =  (SELECT seq_val
                          FROM _sequence
                          WHERE seq_name = sSeqName
                                AND seq_group = sSeqGroup);
    IF nLast_val IS NULL THEN
        SET nLast_val = 1;
        INSERT INTO _sequence (seq_name,seq_group,seq_val)
        VALUES (sSeqName,sSeqGroup,nLast_Val);
    ELSE
        SET nLast_val = nLast_val + 1;
        UPDATE _sequence SET seq_val = nLast_val
        WHERE seq_name = sSeqName AND seq_group = sSeqGroup;
    END IF; 
 
    SET @ret = (SELECT concat(sSeqGroup,'-',lpad(nLast_val,6,'0')));
    RETURN @ret;
END// 
 
delimiter ;
  3. Create a stored procedure to modify a current sequence value: 
    delimiter //
DROP PROCEDURE IF EXISTS sp_setSeqCustomVal//
CREATE PROCEDURE sp_setCustomVal(sSeqName VARCHAR(50),  
              sSeqGroup VARCHAR(10), nVal INT UNSIGNED)
BEGIN
    IF (SELECT COUNT(*) FROM _sequence  
            WHERE seq_name = sSeqName  
                AND seq_group = sSeqGroup) = 0 THEN
        INSERT INTO _sequence (seq_name,seq_group,seq_val)
        VALUES (sSeqName,sSeqGroup,nVal);
    ELSE
        UPDATE _sequence SET seq_val = nVal
        WHERE seq_name = sSeqName AND seq_group = sSeqGroup;
    END IF;
END//
delimiter ;

Testing the new functions:

  1. Create a table: 
    CREATE TABLE custom_autonums
(
   id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
   seq_1 VARCHAR(20), -- custom sequence 1
   seq_2 VARCHAR(20), -- custom sequence 2
   UNIQUE(seq_1),
   UNIQUE(seq_2)
);
  2. Create trigger: 
    delimiter //
DROP TRIGGER IF EXISTS custom_autonums_bi//
 
CREATE TRIGGER custom_autonums_bi BEFORE INSERT ON custom_autonums
FOR each ROW
BEGIN
   SET NEW.seq_1 = getNextCustomSeq("seq_1","001");
   SET NEW.seq_2 = getNextCustomSeq("seq_2","DBA");
END//
 
delimiter ;
  3. Insert some values: 
    INSERT INTO custom_autonums (id) VALUES (NULL),(NULL),(NULL);
SELECT * FROM custom_autonums;
+----+------------+------------+
| id | seq_1      | seq_2      |
+----+------------+------------+
|  4 | 001-000001 | DBA-000001 |
|  5 | 001-000002 | DBA-000002 |
|  6 | 001-000003 | DBA-000003 |
+----+------------+------------+
3 ROWS IN SET (0.00 sec)
  4. Altering current values: 
    CALL sp_setCustomVal('seq_1','001',675);
 
INSERT INTO custom_autonums (id) VALUES (NULL),(NULL),(NULL);
SELECT * FROM custom_autonums;
+----+------------+------------+
| id | seq_1      | seq_2      |
+----+------------+------------+
|  4 | 001-000001 | DBA-000001 |
|  5 | 001-000002 | DBA-000002 |
|  6 | 001-000003 | DBA-000003 |
|  7 | 001-000676 | DBA-000004 |
|  8 | 001-000677 | DBA-000005 |
|  9 | 001-000678 | DBA-000006 |
+----+------------+------------+
6 ROWS IN SET (0.00 sec)

Enjoy!

Feb
25

A better SHOW TABLE STATUS

From command line we have the entire MySQL server on hands (if we have privileges too of course) but we don’t have a overall overview, at this point the show table status command is every useful, or not?.

This is what we get when run show table status in a standard 80×25 terminal screen:

We can maximize the terminal window and decrease font size, but not all the time we need that lots of info. Some time ago I develop a stored procedure to get a global overview including functions and stored procedures. The result is pretty comprehensible:

call tools.sp_status(database());
+----------------------------+--------+-------+---------+-----------------+
| Table Name                 | Engine | Rows  | Size    | Collation       |
+----------------------------+--------+-------+---------+-----------------+
| actor                      | InnoDB | 200   | 0.03 Mb | utf8_general_ci |
| actor_info                 | [VIEW] | -     | -       | -               |
| address                    | InnoDB | 589   | 0.09 Mb | utf8_general_ci |
| category                   | InnoDB | 16    | 0.02 Mb | utf8_general_ci |
| city                       | InnoDB | 427   | 0.06 Mb | utf8_general_ci |
| country                    | InnoDB | 109   | 0.02 Mb | utf8_general_ci |
| customer                   | InnoDB | 541   | 0.12 Mb | utf8_general_ci |
| customer_list              | [VIEW] | -     | -       | -               |
| film                       | InnoDB | 1131  | 0.27 Mb | utf8_general_ci |
| film_actor                 | InnoDB | 5143  | 0.27 Mb | utf8_general_ci |
| film_category              | InnoDB | 316   | 0.08 Mb | utf8_general_ci |
| film_list                  | [VIEW] | -     | -       | -               |
| film_text                  | MyISAM | 1000  | 0.31 Mb | utf8_general_ci |
| inventory                  | InnoDB | 4673  | 0.36 Mb | utf8_general_ci |
| language                   | InnoDB | 6     | 0.02 Mb | utf8_general_ci |
| nicer_but_slower_film_list | [VIEW] | -     | -       | -               |
| payment                    | InnoDB | 15422 | 2.12 Mb | utf8_general_ci |
| rental                     | InnoDB | 15609 | 2.72 Mb | utf8_general_ci |
| sales_by_film_category     | [VIEW] | -     | -       | -               |
| sales_by_store             | [VIEW] | -     | -       | -               |
| staff                      | InnoDB | 1     | 0.09 Mb | utf8_general_ci |
| staff_list                 | [VIEW] | -     | -       | -               |
| store                      | InnoDB | 2     | 0.05 Mb | utf8_general_ci |
+----------------------------+--------+-------+---------+-----------------+
23 rows in set (0.04 sec)
 
+----------------------------+-----------+---------------------+
| Routine Name               | Type      | Comment             |
+----------------------------+-----------+---------------------+
| get_customer_balance       | FUNCTION  |                     |
| inventory_held_by_customer | FUNCTION  |                     |
| inventory_in_stock         | FUNCTION  |                     |
| film_in_stock              | PROCEDURE |                     |
| film_not_in_stock          | PROCEDURE |                     |
| rewards_report             | PROCEDURE |                     |
| customer_create_date       | TRIGGER   | On INSERT: customer |
| del_film                   | TRIGGER   | On DELETE: film     |
| ins_film                   | TRIGGER   | On INSERT: film     |
| payment_date               | TRIGGER   | On INSERT: payment  |
| rental_date                | TRIGGER   | On INSERT: rental   |
| upd_film                   | TRIGGER   | On UPDATE: film     |
+----------------------------+-----------+---------------------+
12 rows in set (0.04 sec)
 
Query OK, 0 rows affected (0.04 sec)

There is the procedure source code:

DELIMITER $$
DROP PROCEDURE IF EXISTS `tools`.`sp_status` $$
CREATE PROCEDURE `tools`.`sp_status`(dbname VARCHAR(50))
BEGIN 
-- Obtaining tables and views
(
    SELECT 
     TABLE_NAME AS `Table Name`, 
     ENGINE AS `Engine`,
     TABLE_ROWS AS `Rows`,
     CONCAT(
        (FORMAT((DATA_LENGTH + INDEX_LENGTH) / POWER(1024,2),2))
        , ' Mb')
       AS `Size`,
     TABLE_COLLATION AS `Collation`
    FROM information_schema.TABLES
    WHERE TABLES.TABLE_SCHEMA = dbname
          AND TABLES.TABLE_TYPE = 'BASE TABLE'
)
UNION
(
    SELECT 
     TABLE_NAME AS `Table Name`, 
     '[VIEW]' AS `Engine`,
     '-' AS `Rows`,
     '-' `Size`,
     '-' AS `Collation`
    FROM information_schema.TABLES
    WHERE TABLES.TABLE_SCHEMA = dbname 
          AND TABLES.TABLE_TYPE = 'VIEW'
)
ORDER BY 1;
-- Obtaining functions, procedures and triggers
(
    SELECT ROUTINE_NAME AS `Routine Name`, 
     ROUTINE_TYPE AS `Type`,
     '' AS `Comment`
    FROM information_schema.ROUTINES
    WHERE ROUTINE_SCHEMA = dbname
    ORDER BY ROUTINES.ROUTINE_TYPE, ROUTINES.ROUTINE_NAME
)
UNION
(
    SELECT TRIGGER_NAME,'TRIGGER' AS `Type`, 
    concat('On ',EVENT_MANIPULATION,': ',EVENT_OBJECT_TABLE) AS `Comment`
    FROM information_schema.TRIGGERS
    WHERE EVENT_OBJECT_SCHEMA = dbname
)
ORDER BY 2,1;
END$$
DELIMITER ;

To use in your place you must call as:

mysql> call tools.sp_status(database());

Note the stored procedure has created in tools database (you can use another db), the goal of this is to call that useful procedure from any database, and it receives the name of database as parameter because is not possible obtain the current database from inside of stored procedure.

I published that code on  MySQL custom show table status entry at forge.mysql.com.

Any ideas to improving the source code to make it more useful?

 

`
Go back to top