Fibonacci Sequence Procedure

Well it has been awhile since I posted, sorry about that. Today I was watching reruns of the TV show Fringe and when Walter referenced the Fibonacci Sequence I got side tracked with MySQL options for this.

Now this post already existed:

• http://slava-technical.blogspot.com/2010/10/fibonacci-numbers-built-with-sql.html

So I took that post and expanded on it a little, the result is a procedure that you can call and return the range within the Fibonacci Sequence that you are after.

The procedure is below:


delimiter //
CREATE PROCEDURE `Fibonacci`(IN POS INT, IN RANG INT, IN LIMTED INT)
BEGIN
       select FORMAT(Fibonacci,0) AS Fibonacci from (
       select @f0 Fibonacci, @fn:=@f1+@f0, @f0:=@f1, @f1:=@fn
       from   (select @f0:=0, @f1:=1, @fn:=1) x,
       information_schema.STATISTICS p limit LIMTED) y LIMIT POS, RANG;
END//
delimiter ;


You can call this and pass whatever values and ranges you are after.

So if you want the 5th value (starting from 0) in the sequence and the next value


> CALL Fibonacci(5,2,100);
+-----------+
| Fibonacci |
+-----------+
|         5 |
|         8 |
+-----------+


So if you want the 30th value (starting from 0) in the sequence and the next value


> CALL Fibonacci(30,2,100);
+-----------+
| Fibonacci |
+-----------+
| 832,040   |
| 1,346,269 |
+-----------+


So if you want the 150th value (starting from 0) in the sequence and the next value


> CALL Fibonacci(150,2,1000);
+--------------------------------------------+
| Fibonacci                                  |
+--------------------------------------------+
| 9,969,216,677,189,305,000,000,000,000,000  |
| 16,130,531,424,904,583,000,000,000,000,000 |
+--------------------------------------------+


 So you get the idea. Now you can also expand the range of results if you want more than 2 just change the 2nd value in the procedure call.


> CALL Fibonacci(0,10,100);
+-----------+
| Fibonacci |
+-----------+
| 0         |
| 1         |
| 1         |
| 2         |
| 3         |
| 5         |
| 8         |
| 13        |
| 21        |
| 34        |
+-----------+

> CALL Fibonacci(30,5,100);
+-----------+
| Fibonacci |
+-----------+
| 832,040   |
| 1,346,269 |
| 2,178,309 |
| 3,524,578 |
| 5,702,887 |
+-----------+


 Anyway, hope someone finds it helpful and credit for the base of query does go to the original post.

Find/parse a string from within a string

So I noticed a few different questions and posts about parsing a string out of another string recently. While some solutions included creating new functions and etc it can also be done within a single query in some cases.

For example, let us say that we are looking to pull out the domain from a URL. I will try to go into detail as to why and how this works.
We have the following table.


CREATE TABLE `parse_example` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `urldemo` varchar(150) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;
+----+----------------------------+
| id | urldemo                    |
+----+----------------------------+
|  1 | http://www.mysql.com/      |
|  2 | http://www.percona.com/    |
|  3 | https://tools.percona.com/ |
|  4 | https://mariadb.com/       |
|  5 | http://planet.mysql.com/   |
|  6 | http://dev.mysql.com/doc/  |
+----+----------------------------+


The goal for this example is to disregard the http:// or https:// and anything after the .com. So we use LOCATE to find the locations.

The .com reference is easy since that is constant so we can start with that.


SELECT LOCATE('.com', urldemo), urldemo FROM parse_example;
+-------------------------+----------------------------+
| LOCATE('.com', urldemo) | urldemo                    |
+-------------------------+----------------------------+
|                      17 | http://www.mysql.com/      |
|                      19 | http://www.percona.com/    |
|                      22 | https://tools.percona.com/ |
|                      16 | https://mariadb.com/       |
|                      20 | http://planet.mysql.com/   |
|                      17 | http://dev.mysql.com/doc/  |
+-------------------------+----------------------------+


OK so we want to remove the / , what location is that?


SELECT LOCATE('.com', urldemo) as start, LOCATE('.com', urldemo) +4 as end, SUBSTRING(urldemo FROM LOCATE('.com', urldemo) + 4 )  AS resulting , urldemo FROM parse_example;
+-------+-----+-----------+----------------------------+
| start | end | resulting | urldemo                    |
+-------+-----+-----------+----------------------------+
|    17 |  21 | /         | http://www.mysql.com/      |
|    19 |  23 | /         | http://www.percona.com/    |
|    22 |  26 | /         | https://tools.percona.com/ |
|    16 |  20 | /         | https://mariadb.com/       |
|    20 |  24 | /         | http://planet.mysql.com/   |
|    17 |  21 | /doc/     | http://dev.mysql.com/doc/  |
+-------+-----+-----------+----------------------------+

This gives us our end position, I only put the field aliases in to make the results easier to follow.

Now sorting out after http and https is actually very easy as well as they both have :// after them, so we just need the location of the second / in the string.


SELECT LOCATE('/', urldemo) as first, LOCATE('/', urldemo) +1 as second, urldemo
FROM parse_example;
+-------+--------+----------------------------+
| first | second | urldemo                    |
+-------+--------+----------------------------+
|     6 |      7 | http://www.mysql.com/      |
|     6 |      7 | http://www.percona.com/    |
|     7 |      8 | https://tools.percona.com/ |
|     7 |      8 | https://mariadb.com/       |
|     6 |      7 | http://planet.mysql.com/   |
|     6 |      7 | http://dev.mysql.com/doc/  |
+-------+--------+----------------------------+


These queries are just showing what the different aspects of the final query will be doing. So let us put it all together.


SELECT
TRIM(TRAILING SUBSTRING(urldemo FROM LOCATE('.com', urldemo) + 4 )
FROM SUBSTRING(urldemo FROM LOCATE('/', urldemo) + 2 ) )  AS parsed_domain ,
urldemo as original_url
FROM parse_example;
+-------------------+----------------------------+
| parsed_domain     | original_url               |
+-------------------+----------------------------+
| www.mysql.com     | http://www.mysql.com/      |
| www.percona.com   | http://www.percona.com/    |
| tools.percona.com | https://tools.percona.com/ |
| mariadb.com       | https://mariadb.com/       |
| planet.mysql.com  | http://planet.mysql.com/   |
| dev.mysql.com     | http://dev.mysql.com/doc/  |
+-------------------+----------------------------+


Now hopefully that helps you be able to parse out whatever you need. This example is limited to a url. But since some examples of functions already here is my example of a function that you can use to parse whatever you need.


CREATE FUNCTION PARSE_STRING(delimiterA VARCHAR(50), delimiterB VARCHAR(50),  passed_string VARCHAR(255) )
RETURNS VARCHAR(255) DETERMINISTIC
RETURN
TRIM(TRAILING SUBSTRING(passed_string FROM LOCATE(delimiterB, passed_string)  )
FROM SUBSTRING(passed_string FROM LOCATE(delimiterA, passed_string) + CHAR_LENGTH(delimiterA) ) ) ;

SELECT PARSE_STRING('//','.com', urldemo) FROM parse_example;
+------------------------------------+
| PARSE_STRING('//','.com', urldemo) |
+------------------------------------+
| www.mysql                          |
| www.percona                        |
| tools.percona                      |
| mariadb                            |
| planet.mysql                       |
| dev.mysql                          |
+------------------------------------+


Pull a last name from a full name field:


SELECT PARSE_STRING('John ','', 'John Smith') ;
+----------------------------------------+
| PARSE_STRING('John ','', 'John Smith') |
+----------------------------------------+
| Smith                                  |
+----------------------------------------+


Pull the first name


SELECT PARSE_STRING('',' Smith', 'John Smith') ;
+-----------------------------------------+
| PARSE_STRING('',' Smith', 'John Smith') |
+-----------------------------------------+
| John                                    |
+-----------------------------------------+


Granted with the name examples you would need to know the delimiter values. But this is just an example you can build on.

A MySQL PARTITION and SUBPARTITION Example

So this is just a simple example of how to set up a PARTITION and a SUBPARTITION in MySQL. The concept here is that you have data in a table with numerous values in a datetime field. You might have data that is spread across numerous years  (most likely you do). So one way to partition this data is to sort it by year but then also sort it by month within that yearly partition.

Below is an example that you can use for consideration.

Consider the test table. Your table with have many more fields of course.


CREATE TABLE `t1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `date_time` datetime DEFAULT NOW(),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;


First I will populate the test table with random values for the  date_time field.


delimiter //
CREATE PROCEDURE populate_t1( IN rowsofdata INT )
BEGIN

SET @A = 1;
SET @B = 25 - @A;

  WHILE rowsofdata > 0 DO
    SELECT  FLOOR( @A + (RAND() * @B )) INTO @randvalue;
    INSERT INTO t1
    SELECT NULL, NOW() - INTERVAL @randvalue MONTH;
    SET rowsofdata = rowsofdata - 1;
  END WHILE;
 END//
delimiter ;
call populate_t1(1000);


Check to see what kind of values I ended up with:


> SELECT COUNT(*) FROM t1 WHERE date_time BETWEEN '2012-01-01 00:00:00' AND '2013-01-01 00:00:00'\G
*************************** 1. row ***************************
COUNT(*): 43
1 row in set (0.00 sec)

> SELECT COUNT(*) FROM t1 WHERE date_time BETWEEN '2013-01-01 00:00:00' AND '2014-01-01 00:00:00'\G
*************************** 1. row ***************************
COUNT(*): 529
1 row in set (0.00 sec)

> SELECT COUNT(*) FROM t1 WHERE date_time BETWEEN '2014-01-01 00:00:00' AND NOW() \G
*************************** 1. row ***************************
COUNT(*): 428
1 row in set (0.00 sec)


Now I can alter the table so I can add my partitions and then test the values counts via the partition.


ALTER TABLE t1  DROP PRIMARY KEY, ADD PRIMARY KEY (`id`,`date_time`), LOCK=SHARED;
ALTER TABLE t1
 PARTITION BY RANGE( YEAR(date_time) )
    SUBPARTITION BY HASH(MONTH(date_time) ) (

        PARTITION p2012 VALUES LESS THAN (2013) (
            SUBPARTITION dec_2012,
            SUBPARTITION jan_2012,
            SUBPARTITION feb_2012,
            SUBPARTITION mar_2012,
            SUBPARTITION apr_2012,
            SUBPARTITION may_2012,
            SUBPARTITION jun_2012,
            SUBPARTITION jul_2012,
            SUBPARTITION aug_2012,
            SUBPARTITION sep_2012,
            SUBPARTITION oct_2012,
            SUBPARTITION nov_2012
        ),

        PARTITION p2013 VALUES LESS THAN (2014) (
            SUBPARTITION dec_2013,
            SUBPARTITION jan_2013,
            SUBPARTITION feb_2013,
            SUBPARTITION mar_2013,
            SUBPARTITION apr_2013,
            SUBPARTITION may_2013,
            SUBPARTITION jun_2013,
            SUBPARTITION jul_2013,
            SUBPARTITION aug_2013,
            SUBPARTITION sep_2013,
            SUBPARTITION oct_2013,
            SUBPARTITION nov_2013

        ),
        PARTITION p2014 VALUES LESS THAN (2015) (
            SUBPARTITION dec_2014,
            SUBPARTITION jan_2014,
            SUBPARTITION feb_2014,
            SUBPARTITION mar_2014,
            SUBPARTITION apr_2014,
            SUBPARTITION may_2014,
            SUBPARTITION jun_2014,
            SUBPARTITION jul_2014,
            SUBPARTITION aug_2014,
            SUBPARTITION sep_2014,
            SUBPARTITION oct_2014,
            SUBPARTITION nov_2014
        ),

        PARTITION pmax VALUES LESS THAN MAXVALUE (
            SUBPARTITION dec_max,
            SUBPARTITION jan_max,
            SUBPARTITION feb_max,
            SUBPARTITION mar_max,
            SUBPARTITION apr_max,
            SUBPARTITION may_max,
            SUBPARTITION jun_max,
            SUBPARTITION jul_max,
            SUBPARTITION aug_max,
            SUBPARTITION sep_max,
            SUBPARTITION oct_max,
            SUBPARTITION nov_max
        )
 );


My Show create table is very different now.


> show create table t1;
CREATE TABLE `t1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `date_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`,`date_time`)
) ENGINE=InnoDB AUTO_INCREMENT=1001 DEFAULT CHARSET=latin1
/*!50100 PARTITION BY RANGE ( YEAR(date_time))
SUBPARTITION BY HASH (MONTH(date_time))
(PARTITION p2012 VALUES LESS THAN (2013)
 (SUBPARTITION dec_2012 ENGINE = InnoDB,
  SUBPARTITION jan_2012 ENGINE = InnoDB,
  SUBPARTITION feb_2012 ENGINE = InnoDB,
  SUBPARTITION mar_2012 ENGINE = InnoDB,
  SUBPARTITION apr_2012 ENGINE = InnoDB,
  SUBPARTITION may_2012 ENGINE = InnoDB,
  SUBPARTITION jun_2012 ENGINE = InnoDB,
  SUBPARTITION jul_2012 ENGINE = InnoDB,
  SUBPARTITION aug_2012 ENGINE = InnoDB,
  SUBPARTITION sep_2012 ENGINE = InnoDB,
  SUBPARTITION oct_2012 ENGINE = InnoDB,
  SUBPARTITION nov_2012 ENGINE = InnoDB),
 PARTITION p2013 VALUES LESS THAN (2014)
 (SUBPARTITION dec_2013 ENGINE = InnoDB,
  SUBPARTITION jan_2013 ENGINE = InnoDB,
  SUBPARTITION feb_2013 ENGINE = InnoDB,
  SUBPARTITION mar_2013 ENGINE = InnoDB,
  SUBPARTITION apr_2013 ENGINE = InnoDB,
  SUBPARTITION may_2013 ENGINE = InnoDB,
  SUBPARTITION jun_2013 ENGINE = InnoDB,
  SUBPARTITION jul_2013 ENGINE = InnoDB,
  SUBPARTITION aug_2013 ENGINE = InnoDB,
  SUBPARTITION sep_2013 ENGINE = InnoDB,
  SUBPARTITION oct_2013 ENGINE = InnoDB,
  SUBPARTITION nov_2013 ENGINE = InnoDB),
 PARTITION p2014 VALUES LESS THAN (2015)
 (SUBPARTITION dec_2014 ENGINE = InnoDB,
  SUBPARTITION jan_2014 ENGINE = InnoDB,
  SUBPARTITION feb_2014 ENGINE = InnoDB,
  SUBPARTITION mar_2014 ENGINE = InnoDB,
  SUBPARTITION apr_2014 ENGINE = InnoDB,
  SUBPARTITION may_2014 ENGINE = InnoDB,
  SUBPARTITION jun_2014 ENGINE = InnoDB,
  SUBPARTITION jul_2014 ENGINE = InnoDB,
  SUBPARTITION aug_2014 ENGINE = InnoDB,
  SUBPARTITION sep_2014 ENGINE = InnoDB,
  SUBPARTITION oct_2014 ENGINE = InnoDB,
  SUBPARTITION nov_2014 ENGINE = InnoDB),
 PARTITION pmax VALUES LESS THAN MAXVALUE
 (SUBPARTITION dec_max ENGINE = InnoDB,
  SUBPARTITION jan_max ENGINE = InnoDB,
  SUBPARTITION feb_max ENGINE = InnoDB,
  SUBPARTITION mar_max ENGINE = InnoDB,
  SUBPARTITION apr_max ENGINE = InnoDB,
  SUBPARTITION may_max ENGINE = InnoDB,
  SUBPARTITION jun_max ENGINE = InnoDB,
  SUBPARTITION jul_max ENGINE = InnoDB,
  SUBPARTITION aug_max ENGINE = InnoDB,
  SUBPARTITION sep_max ENGINE = InnoDB,
  SUBPARTITION oct_max ENGINE = InnoDB,
  SUBPARTITION nov_max ENGINE = InnoDB))


So can we still count our value as expected?


> SELECT count(*) FROM t1 PARTITION (p2012) \G
*************************** 1. row ***************************
count(*): 43
> SELECT count(*) FROM t1 PARTITION (p2013) \G
*************************** 1. row ***************************
count(*): 529
> SELECT count(*) FROM t1 PARTITION (p2014) \G
*************************** 1. row ***************************
count(*): 428


So far so good, all the values matched up to the count we had before. So we can also count or select per the subpartition.


> SELECT * FROM t1 PARTITION (dec_2012) limit 5;
+-----+---------------------+
| id  | date_time           |
+-----+---------------------+
|  59 | 2012-12-19 00:59:57 |
|  68 | 2012-12-19 00:59:58 |
|  93 | 2012-12-19 00:59:59 |
| 105 | 2012-12-19 00:59:59 |
| 111 | 2012-12-19 00:59:59 |
+-----+---------------------+

> SELECT * FROM t1 PARTITION (jan_2013) limit 5;
+-----+---------------------+
| id  | date_time           |
+-----+---------------------+
|   6 | 2013-01-19 00:59:55 |
|  29 | 2013-01-19 00:59:56 |
|  55 | 2013-01-19 00:59:57 |
|  79 | 2013-01-19 00:59:58 |
| 100 | 2013-01-19 00:59:59 |
+-----+---------------------+

> SELECT * FROM t1 PARTITION (jan_2014) limit 5;
+-----+---------------------+
| id  | date_time           |
+-----+---------------------+
|  16 | 2014-01-19 00:59:55 |
| 190 | 2014-01-19 01:00:04 |
| 191 | 2014-01-19 01:00:04 |
| 229 | 2014-01-19 01:00:05 |
| 234 | 2014-01-19 01:00:06 |
+-----+---------------------+

> SELECT * FROM t1 PARTITION (jun_2014) limit 5;
+-----+---------------------+
| id  | date_time           |
+-----+---------------------+
|  13 | 2014-06-19 00:59:55 |
| 189 | 2014-06-19 01:00:04 |
| 221 | 2014-06-19 01:00:05 |
| 222 | 2014-06-19 01:00:05 |
| 238 | 2014-06-19 01:00:06 |
+-----+---------------------+

> SELECT * FROM t1 PARTITION (dec_2013) limit 5;
+-----+---------------------+
| id  | date_time           |
+-----+---------------------+
|  50 | 2013-12-19 00:59:57 |
|  74 | 2013-12-19 00:59:58 |
|  98 | 2013-12-19 00:59:59 |
| 107 | 2013-12-19 00:59:59 |
| 167 | 2013-12-19 01:00:02 |
+-----+---------------------+


This is great and handy but what happens when the year 2015 or  2016 comes around? All of that data would be in the pmax partition. So how do we add a new partition in between p2014 and pmax?

If you had no data in pmax you could drop it and add a new partition onto the end. But it is just as easy to reorganize the partition. This will take the pmax partition and alter it into our new partitions.


ALTER TABLE t1 REORGANIZE PARTITION pmax INTO (
     PARTITION p2015 VALUES LESS THAN (2016) (
            SUBPARTITION dec_2015,
            SUBPARTITION jan_2015,
            SUBPARTITION feb_2015,
            SUBPARTITION mar_2015,
            SUBPARTITION apr_2015,
            SUBPARTITION may_2015,
            SUBPARTITION jun_2015,
            SUBPARTITION jul_2015,
            SUBPARTITION aug_2015,
            SUBPARTITION sep_2015,
            SUBPARTITION oct_2015,
            SUBPARTITION nov_2015
        ),
       PARTITION pmax VALUES LESS THAN MAXVALUE (
            SUBPARTITION dec_max,
            SUBPARTITION jan_max,
            SUBPARTITION feb_max,
            SUBPARTITION mar_max,
            SUBPARTITION apr_max,
            SUBPARTITION may_max,
            SUBPARTITION jun_max,
            SUBPARTITION jul_max,
            SUBPARTITION aug_max,
            SUBPARTITION sep_max,
            SUBPARTITION oct_max,
            SUBPARTITION nov_max
        )
);


Hope this helps, best of luck. 

Recover Lost MySQL data with mysqlbinlog point-in-time-recovery example

Backup ... backup... Backup... but of course.. you also need to monitor and test those backups often otherwise they could be worthless.  Having your MySQL binlogs enabled can certainly help you in times of an emergency as well.  The MySQL binlogs are often referenced in regards to MySQL replication, for a good reason, they store all of the queries or events that alter data (row-based is a little different but this an example). The binlogs have a minimal impact on server performance when considering the recovery options they provide.


[anothermysqldba]> show variables like 'log_bin%';
+---------------------------------+--------------------------------------------+
| Variable_name                   | Value                                      |
+---------------------------------+--------------------------------------------+
| log_bin                         | ON                                         |
| log_bin_basename                | /var/lib/mysql/binlogs/mysql-binlogs       |
| log_bin_index                   | /var/lib/mysql/binlogs/mysql-binlogs.index |

show variables like 'binlog_format%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | MIXED |
+---------------+-------+


So this is just a simple example using mysqlbinlog to recover data from a binlog and apply it back to the database.

First we need something to loose. If something was to happen to our database we need to be able to recover the data or maybe it is just a way to recover from someones mistake.


CREATE TABLE `table_w_rdata` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `somedata` varchar(50) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
  `moredata` varchar(50) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;
 

We can pretend here and assume that we have developers/DBAs that are not communicating very well and/or saving copies of their code.


delimiter //
CREATE PROCEDURE populate_dummydata( IN rowsofdata INT )
BEGIN

SET @A = 3;
SET @B = 15 - @A;
SET @C = 16;
SET @D = 25 - @C;

  WHILE rowsofdata > 0 DO
    INSERT INTO table_w_rdata
    SELECT NULL, SUBSTR(md5(''),FLOOR( @A + (RAND() * @B ))) as somedata, SUBSTR(md5(''),FLOOR( @C + (RAND() * @D )))  AS moredata  ;
    SET rowsofdata = rowsofdata - 1;
  END WHILE;
 END//
delimiter ;
call populate_dummydata(50);

> SELECT NOW() \G
*************************** 1. row ***************************
NOW(): 2014-11-27 17:32:25
1 row in set (0.00 sec)

> SELECT  * from table_w_rdata  WHERE id > 45;
+----+----------------------------+------------------+
| id | somedata                   | moredata         |
+----+----------------------------+------------------+
| 46 | b204e9800998ecf8427e       | 0998ecf8427e     |
| 47 | d98f00b204e9800998ecf8427e | 8ecf8427e        |
| 48 | b204e9800998ecf8427e       | 800998ecf8427e   |
| 49 | 98f00b204e9800998ecf8427e  | e9800998ecf8427e |
| 50 | 98f00b204e9800998ecf8427e  | 998ecf8427e      |
+----+----------------------------+------------------+


While one procedure is created it is later written over by someone else incorrectly. 


DROP PROCEDURE IF EXISTS populate_dummydata ;
delimiter //
CREATE PROCEDURE populate_dummydata( IN rowsofdata INT )
BEGIN

SET @A = 3;
SET @B = 15 - @A;
SET @C = 16;
SET @D = 25 - @C;

  WHILE rowsofdata > 0 DO
    INSERT INTO table_w_rdata
    SELECT NULL, SUBSTR(md5(''),FLOOR( @C + (RAND() * @A ))) as somedata, SUBSTR(md5(''),FLOOR( @B + (RAND() * @D )))  AS moredata  ;
    SET rowsofdata = rowsofdata - 1;
  END WHILE;
 END//
delimiter ;

call populate_dummydata(50);
> SELECT NOW(); SELECT  * from table_w_rdata  WHERE id > 95;
+---------------------+
| NOW()               |
+---------------------+
| 2014-11-27 17:36:28 |
+---------------------+
1 row in set (0.00 sec)

+-----+-------------------+---------------------+
| id  | somedata          | moredata            |
+-----+-------------------+---------------------+
|  96 | 4e9800998ecf8427e | 00998ecf8427e       |
|  97 | 9800998ecf8427e   | 800998ecf8427e      |
|  98 | e9800998ecf8427e  | 204e9800998ecf8427e |
|  99 | e9800998ecf8427e  | 4e9800998ecf8427e   |
| 100 | 9800998ecf8427e   | 04e9800998ecf8427e  |
+-----+-------------------+---------------------+


The replaced version of the procedure is not generating  random values like the team wanted. The original creator of the procedure just quit from frustration. So what to do? A little time has past since it was created as well. We do know the database name, routine name and the general time frame when the incorrect procedure was created and lucky for us the bin logs are still around, so we can go get it.

We have to take a general look around since we just want a point-in-time-recovery of this procedure.We happen to find the procedure and the position in the binlog before and after it.


NOW(): 2014-11-27 19:46:17
# mysqlbinlog  --start-datetime=20141127173200 --stop-datetime=20141127173628 --database=anothermysqldba mysql-binlogs.000001  | more

 at 253053
 at 253564

# mysql anothermysqldba --login-path=local  -e "DROP PROCEDURE populate_dummydata";
# mysqlbinlog  --start-position=253053 --stop-position=253564 --database=anothermysqldba mysql-binlogs.000001 | mysql --login-path=local  anothermysqldba


> SHOW CREATE PROCEDURE populate_dummydata\G
*************************** 1. row ***************************
           Procedure: populate_dummydata
            sql_mode: NO_AUTO_VALUE_ON_ZERO,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
    Create Procedure: CREATE DEFINER=`root`@`localhost` PROCEDURE `populate_dummydata`( IN rowsofdata INT )
BEGIN

SET @A = 3;
SET @B = 15 - @A;
SET @C = 16;
SET @D = 25 - @C;

  WHILE rowsofdata > 0 DO
    INSERT INTO table_w_rdata
    SELECT NULL, SUBSTR(md5(''),FLOOR( @A + (RAND() * @B ))) as somedata, SUBSTR(md5(''),FLOOR( @C + (RAND() * @D )))  AS moredata  ;
    SET rowsofdata = rowsofdata - 1;
  END WHILE;
 END
character_set_client: utf8
collation_connection: utf8_general_ci
  Database Collation: latin1_swedish_ci
1 row in set (0.00 sec)

NOW(): 2014-11-27 19:51:03
> call populate_dummydata(50);
> SELECT  * from table_w_rdata  WHERE id > 145;
+-----+-----------------------------+------------------+
| id  | somedata                    | moredata         |
+-----+-----------------------------+------------------+
| 146 | 98f00b204e9800998ecf8427e   | 800998ecf8427e   |
| 147 | cd98f00b204e9800998ecf8427e | 800998ecf8427e   |
| 148 | 204e9800998ecf8427e         | 98ecf8427e       |
| 149 | d98f00b204e9800998ecf8427e  | e9800998ecf8427e |
| 150 | 204e9800998ecf8427e         | 9800998ecf8427e  |
+-----+-----------------------------+------------------+


We recovered our procedure from the binary log via point-in-time-recovery.
This is a simple example but it is an example of the tools you can use moving forward.

This is why the binlogs are so valuable.

Helpful URL:

• http://dev.mysql.com/doc/refman/5.6/en/binary-log.html
• http://dev.mysql.com/doc/refman/5.6/en/point-in-time-recovery.html

systemctl and MySQL

So some users complete a yum install of MySQL and expect to be able to use the following  command to start the MySQL server::  /etc/init.d/mysql start only to get "No such file or directory"

So this is a quick post to help use the systemctl command. 

You are likely to see this:

# systemctl list-unit-files | grep mysql
mysqld.service                              disabled

First I would recommend go to tools.percona.com and create a valid my.cnf file. 

So the solution is easy, we just need to enable this so the database can start on server start up.

#systemctl enable mysqld.service
ln -s '/usr/lib/systemd/system/mysqld.service' '/etc/systemd/system/mysql.service'
ln -s '/usr/lib/systemd/system/mysqld.service' '/etc/systemd/system/multi-user.target.wants/mysqld.service'
#systemctl start mysqld
#systemctl list-unit-files | grep mysql
mysql.service                               enabled
mysqld.service                              enabled 

Now you should be good to go...