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 ;
Mar
16

Converting MS Access to MySQL with relationships

I have used tools like MySQL Migration Toolkit (*) and Bullzip’s Access To MySQL utility, both do a excellent job but without relationships. We can spend a lot of hours to identifying and creating relationships until now:

I have write a VBA script to identify MS-Access relationships and create MySQL code with SQL-CREATE sentences, it will be useful after migration process using any free tool:

'Put this function in new/existing MS-Access module.
'
' Version History:
'
' 2014-02-09 - Seamus Casey
' a modification to Ivan's handy Access to MySQL relationship/constraint generator
'
' changes include:
'  1) skip Access system tables (TableDefAttributeEnum.dbSystemObjec)
'  2) add support for cascading updates/deletes
'
 
Public Sub printRelations()
    Dim sql, fk As String
    Dim I, J As Integer
    Dim db As Database
    Dim Table As TableDef
    Dim TableName As String
 
    ' grab a reference to this once, otherwise when we retrieve a table below,
    ' we will get an 'Object Invalid or No Longer Set' error.
    Set db = CurrentDb
 
    For I = 0 To db.Relations.Count - 1
 
        Set Table = db.TableDefs.Item(db.Relations(I).Table)
 
        If ((Table.Attributes And TableDefAttributeEnum.dbSystemObject) = 0) Then
 
           sql = "ALTER TABLE `" & db.Relations(I).ForeignTable & _
               "` ADD CONSTRAINT `" & db.Relations(I).Name & "` FOREIGN KEY ("
           fk = "("
           For J = 0 To db.Relations(I).Fields.Count - 1
               sql = sql & "`" & db.Relations(I).Fields(J).ForeignName & "` ,"
               fk = fk & "`" & db.Relations(I).Fields(J).Name & "` ,"
           Next J
 
           sql = Left(sql, Len(sql) - 1)
           fk = Left(fk, Len(fk) - 1)
           fk = fk & ")"
           sql = sql & ") REFERENCES `" & db.Relations(I).Table & "`" & fk
 
           If (db.Relations(I).Attributes And RelationAttributeEnum.dbRelationUpdateCascade) Then
               sql = sql & " ON UPDATE CASCADE"
           End If
 
           If (db.Relations(I).Attributes And RelationAttributeEnum.dbRelationDeleteCascade) Then
               sql = sql & " ON DELETE CASCADE"
           End If
 
           sql = sql & ";"
 
           Debug.Print sql
        End If
    Next I
End Sub

Gist Source

To run the adobe code go to intermediate windows (Ctrl+G) and execute: printRelations and copy the generated SQL code.

Enjoy!

(*)MySQL Migration Toolkit is discontinued but is still available from mirrors like:
http://mirrors.dotsrc.org/mysql/Downloads/MySQLGUITools/

Mar
12

MySQL must improve error messages

I just finished a database modification, a new foreign key creation  shouldn’t be take more than 5 mins, but I spent 2 hours because MySQL still have some useless error messages.

There is a way to create a new foreign key:

-- Create two tables foo and bar
CREATE TABLE foo (
	id INTEGER NOT NULL PRIMARY KEY,
	bar_id INT NOT NULL    -- foreign key
);
CREATE TABLE bar (
	id INTEGER NOT NULL PRIMARY KEY
);
-- Try to create a foreign key on `foo`
ALTER TABLE foo
	ADD FOREIGN KEY(bar_id) REFERENCES bar(SOME_FIELD) ;

The last sentence returns a generic error message:

Error Code: 1005. Can't create table 'temp.#sql-4bd7_11' (errno: 150)

Everything would have been easier if I had noticed that wrong field name bar(SOME_FIELD), sometimes happens,  but if MySQL would have shown a different message like "field bar.SOME_FIELD don't exists" I would not be awake until 2 am.

I’m using MySQL 5.5.21 community edition.

if I had noticed that wrong field name

Mar
02

Twitter bug found!

Some days ago while I’m looking for what are saying about a mysql.com server down I found a twitter bug:

Is not a big deal, to repeat this bug you must follow these steps:

1. Find any term, in this case “mysql.com” then in results looking for a word that have the search term as a part of them (ex dev.mysql.com) and select the a part or entire word:

twitter bug 01

2. Press Ctrl + C,  some HTML codes appear from nowhere:

twitter bug 02

3. Do it again and again, you will see an strange twit like this:

twitter bug 03

I’m using Firefox 8.0  under Ubuntu, but you will get the same result using Chrome and (maybe) other web clients… you can try with other searches like twitter.com and select pic.twitter.com results.

UPDATE:

@jeremycole  says: “Your URL-selection Twitter bug was fixed and shipped yesterday afternoon! Thanks!” on Mar 14 2012

Mar
01

Alias shortcuts to MySQL CLI

Do you get write laziness in the command line everything what you need to connect to a MySQL server every time?

It may take less than minute, but sometimes one minute is vital (especially if we’re near the end of the world):

ivancp@ubuntu$ mysql -u root -p -h mysqlhost database

When we are hurry, these commands often fail several times per minute.

The solution: we can create shortcuts with bash alias commands in file ~/.bashrc :

# File ~ /. Bashrc
 
# Command "my" to connect to a local server
alias my='mysql -u root -p'
 
# Command "my2" to connect to a remote server
alias my2='mysql -u root -h 192.168.1.56 -p'

Next time if you want to access the local server just type the command my [database name] , there only ask for database password. You can use any command aliases, I prefer  “my” and “my2” they are short and useful.

But if you have several servers comes another problem, how to know in which server I’m?

Open a mysql-cli can be super fast with alias shortcuts, but all terminals have the same default prompt: mysql>  To avoid disasters (ex. run DROP in wrong place) you can change mysql-cli prompt with option --prompt , then finally our .bashrc look like this:

#improved ~/.bashrc 
 
# Command "my" to connect to a local server
alias my='mysql -u root --password=secret --prompt="local> "'
 
# Command "my2" to connect to a remote server
alias my2='mysql -u root --password=secret  -h 192.168.1.56 --prompt="server 1> "'

Note I added --password parameter, it can be dangerous, use it under your own risk.

Enjoy!

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?

 

`
Feb
23

Creating sequences in MySQL

Those who have used PostgresSQL usually gets lazy when write the SQL code to auto-numeric fields, but we miss that feature when we are in a MySQL database.

As we know, MySQL have auto-increment fields, that are very useful too, but we miss that feature when we are in a PostgreSQL database.

I will try to explain how to simulate sequences like PostgreSQL in MySQL. But in what situations we would need sequences in MySQL? some ideas:

  • When we need more than one auto-numeric field in the same table.
  • When we need a global counter to use in more than one table, procedures, etc.
  • I can’t think more situations, but I’m sure at some point we can found one.

OK let’s start:

We need to create a table to store the current sequence value (if this table is created inside mysql database  is not bad idea)

create table _sequence
(
	seq_name varchar(50) not null primary key,
	seq_val int unsigned not null
);

Now we need a function to get the next sequence value, including these cases:

    • Create a sequence name if the sequence name don’t exists (is not necessary get complicated).
    • Increase the sequence number each function call.

There is the function  getNextSeq it receive sequence name as parameter:

delimiter //
drop function if exists getNextSeq//
 
create function getNextSeq(sSeqName varchar(50)) returns int unsigned
begin
    declare nLast_val int;
 
    set nLast_val =  (select seq_val
                          from _sequence
                          where seq_name = sSeqName);
    if nLast_val is null then
        set nLast_val = 1;
        insert into _sequence (seq_name,seq_val)
        values (sSeqName,nLast_Val);
    else
        set nLast_val = nLast_val + 1;
        update _sequence set seq_val = nLast_val
        where seq_name = sSeqName;
    end if;
 
    return nLast_val;
end//
 
delimiter ;

 

The next step is to create  (to maintain purposes) a stored procedure to modify the current sequence value:

drop procedure if exists sp_setSeqVal//
 
create procedure sp_setSeqVal(sSeqName varchar(50), nVal int unsigned)
begin
    if (select count(*) from _sequence where seq_name = sSeqName) = 0 then
        insert into _sequence (seq_name,seq_val)
        values (sSeqName,nVal);
    else
        update _sequence set seq_val = nVal
        where seq_name = sSeqName;
    end if;
end//
 
delimiter ;

 

That’s it!, just one more thing: testing. Creating a table with two auto-numeric fields using simulated sequences:

create table multiple_autonums
(
   id int not null primary key auto_increment,
   seq_1 int, -- sequence 1
   seq_2 int, -- sequence 2
   unique(seq_1),
   unique(seq_2)
);

We must create a before-insert trigger on table:

delimiter //
drop trigger if exists multiple_autonums_ai//
 
create trigger multiple_autonums_bi before insert on multiple_autonums
for each row
begin
   set new.seq_1 = getNextSeq("seq_1");
   set new.seq_2 = getNextSeq("seq_2");
end//
 
delimiter ;

 

Run some tests:

-- inserting new values.
insert into multiple_autonums (id) values (null),(null),(null);
 
-- getting the inserted values
select * from multiple_autonums;
+----+-------+-------+
| id | seq_1 | seq_2 |
+----+-------+-------+
|  1 |     1 |     1 |
|  2 |     2 |     2 |
|  3 |     3 |     3 |
+----+-------+-------+
3 rows in set (0.00 sec)
 
-- Changing sequence current values
 
call sp_setSeqVal("seq_1",47);
call sp_setSeqVal("seq_2",9786);
 
-- Inserting new values
insert into multiple_autonums (id) values (null),(null),(null);
 
-- Seeing what happen.
select * from multiple_autonums;
+----+-------+-------+
| id | seq_1 | seq_2 |
+----+-------+-------+
|  1 |     1 |     1 |
|  2 |     2 |     2 |
|  3 |     3 |     3 |
|  4 |    48 |  9787 |
|  5 |    49 |  9788 |
|  6 |    50 |  9789 |
+----+-------+-------+

 

What do you think?

This simulated sequence don’t consider some stuffs but it works and can be helpful. Even you can modify the function to make more complex generation of next value.

Hope you find it useful.
Go back to top