InnoDB lost file pointers to partitions

Recently I went through the exercise of setting up a new replicated slave host. I proceeded through my normal list of tasks to get the new host stood up. This time, something different happened. Replication crashed. Hard.

Last_SQL_Error: Could not execute Update_rows event on table core_stats.plays_hourly; 
Can't find record in 'plays_hourly', 
Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND; 
the event's master log bin.014496, end_log_pos 495311588

This was a new experience for me. So being diligent, I looked at the error log for more information.

Thread pointer: 0x33f1be00
Attempting backtrace. You can use the following information to find out
where mysqld died. If you see no messages after this, something went
terribly wrong...
stack_bottom = 0x59f7f0f8 thread_stack 0x40000
/usr/sbin/mysqld(my_print_stacktrace+0x39)[0x7cdb99]
/usr/sbin/mysqld(handle_segfault+0x379)[0x500b59]
/lib64/libpthread.so.0[0x3f5b20eb10]
/lib64/libc.so.6(gsignal+0x35)[0x3f5aa30265]/lib64/libc.so.6 (abort+0x110)[0x3f5aa31d10]/usr/sbin/mysqld[0x84954a]/usr/sb in/mysqld[0x82c961]
/usr/sbin/mysqld(_ZN7handler13ha_delete_rowEPKh+0x54)[0x6917 f4]
/usr/sbin/mysqld(_ZN12ha_partition10delete_rowEPKh+0x7f)[0x9 7a05f]
/usr/sbin/mysqld(_ZN7handler13ha_delete_rowEPKh+0x54)[0x6917 f4]
/usr/sbin/mysqld(_Z12mysql_deleteP3THDP10TABLE_LISTP4ItemP10 SQL_I_ListI8st_orderEyy+0x8e8)[0x789b88]
/usr/sbin/mysqld(_Z21mysql_execute_commandP3THD+0x1aa7)[0x57 8df7]
/usr/sbin/mysqld(_Z11mysql_parseP3THDPcjP12Parser_state+0x38 c)[0x57d6ec]
/usr/sbin/mysqld(_ZN15Query_log_event14do_apply_eventEPK14Re lay_log_infoPKcj+0xf59)[0x74e3d9]
/usr/sbin/mysqld(_Z26apply_event_and_update_posP9Log_eventP3 THDP14Relay_log_info+0x152)[0x516be2]/usr/sbin/mysqld[0x5192 f2]
/usr/sbin/mysqld(handle_slave_sql+0xc19)[0x5208f9]
/lib64/libpthread.so.0[0x3f5b20673d]
/lib64/libc.so.6(clone+0x6d)[0x3f5aad40cd]
Trying to get some variables.
Some pointers may be invalid and cause the dump to abort.
Query (0x35d220bf): DELETE FROM [hidden schema].[hidden table] WHERE date < '2008-09-06'
Connection ID (thread ID): 11
Status: NOT_KILLED 

The solution for me was to translate "pointer" to mean INDEX. Once I made that connection, the answer was clear. Force a rebuild of the table space and indexes.

ALTER TABLE table_name ENGINE=INNODB;

Whether I was technically correct on my hypothesis, my remedy was. It's hard to argue with success.

Seconds_Behind_Master: 0

So mysql.com got hacked. What does that mean for me?

http://techie-buzz.com/tech-news/mysql-com-database-compromised-sql-injection.html

http://www.pcworld.com/businesscenter/article/240609/mysqlcom_hacked_to_serve_malware.html

There’s been so much drama recently about MySQL’s public web site getting hacked. It’s causing mis-directed hyperbole about the MySQL database. I’m sure vendors of closed source technologies love the bad press for MySQL, but as a user of production MySQL databases and database administrator specializing in MySQL technology, I’m hearing people ask the question “What does this mean for me?”

As any responsible system administrator will tell you, one must first assess what truly happened. In all cases the MySQL database did exactly what it was asked to do and never failed. How could this be, you may ask? Well, the database is the “persistance” layer in the stack of technologies used to present the mysql.com web site. There are scripts and programs in front of the database that request, accept, process and request storage from the database long before the data actually gets there.

In all cases the database stored exactly the data that requested of it to be stored. It returned exactly the data that it had stored in it. So you can see, the problem wasn’t the database itself, but further up the stack in the presentation layer where there were at least two (2) opportunities to get it right. The first opportunity was on the data input request when the application could have noticed that this data was unacceptable or could have been sanitized. The second opportunity was on the data output / request when the front end application could have used logic to sanitize output knowing it would be handed to browsers and could be malicious.

What is the lesson here?

If you have a web application that takes user input, it needs to be checked, tested and sanitized by the application accepting the data. If your database of choice provides opportunities for security, leverage those as well. These may include stored procedures and prepared statements. Build levels and layers of security. Don’t be afraid to reject user input if it does not pass your standards… and for that matter, have standards… nudge, nudge mysql.com!

Bottom line?

Nonetheless, the MySQL database wasn’t the problem, lazy programming was.

Comics for DBAs

Testing MySQL data migrations Part 1

On occasion as database administrators we have to migrate data for various reasons. No matter the reason or the origin, we have to test the migration to see if the result is what we expect. The question is: How?

In Part 1 of this series, I’m going to explore a simple “smoke test” of a data migration. What is a smoke test you ask? A smoke test is what I like to refer to as a sanity check that something is behaving I expect.

Example #1 – Count rows between new and old tables.
This can provide a low level of satisfaction, however, it’s probably the first of my tiers of testing. If row counts are off, your methodology is wrong and you can now start over and/or rollback the change.

-- Get row counts: (should match) smoke tests
SELECT COUNT(*) FROM new_table;
SELECT COUNT(*) FROM old_table;

Example #2 – Check your primary keys.
If you’re copying rows, then you expect your primary keys to be the same. If not, you’ve got a problem. Here’s a quick smoke test for key comparison. If you see a difference, your methodology is wrong and you should rollback.

-- Check PKs smoke tests
SELECT MIN(id), MAX(id) FROM new_table;
SELECT MIN(id), MAX(id) FROM old_table;

Example #3 – Find unwanted NULL values.
Say, you’re denormalizing or aggregating data and you’re maintaining a foreign key. You would expect to have NO NULL values. So you you need to test for their existence. If you find NULL columns, extend your testing to the source tables.

-- Find NULLs
SELECT COUNT(*) FROM new_table WHERE some_fk_id IS NULL;
SELECT COUNT(*) FROM old_table WHERE some_fk_id IS NULL;

…look for more to come in Part 2!

MySQL Best Practice: Forget ALTER, use RENAME instead

MySQL is notorious for a few bummer scenarios. One of them is when you have a table with a couple hundred thousand rows or more and an indexed column or two, and you’ve been asked to add a column to meet a data model requirement. You know this will cause an outage or at least a headache while MySQL copies the table contents into the new structure.

One way to handle it is to perform the atomic ALTER manually. Instead of using the ALTER TABLE command, follow these basic steps:

  1. Identify the objective of the ALTER and determine that it’s necessary.
  2. Get a picture of what current table structure is and how many rows you’re dealing with.
  3. Next, create a new table that includes the changes that would be implemented in your ALTER TABLE table_name statement.
  4. Next, write a script to loop through your new table and your old table and compare values row by row. This will come in handy later.
  5. Then, copy a few rows at a time from a set that are least likely to change to your new table. Tip: this is where those “last_updated DATETIME” columns come in handy.
  6. Once you’ve got all the old rows copied, take your service offline for maintenance, and copy the balance of rows.
  7. Since all the data has been copied, replacing your old structure is as simple as renaming your old table to table_BACKUP and your new table to table_name.
  8. Test, Test, Test and bring your service back online!

Now, let’s break it down:

1. Identify the objective of the ALTER and determine that it’s necessary. In this case, I want to repartition. Why do an alter???

#Adding partitions...
ALTER TABLE dim_datetime 
REORGANIZE PARTITION (P5) INTO 
PARTITION P5 VALUES LESS THAN (2011), 
PARTITION P6 VALUES LESS THAN (MAXVALUE);

2. Get a picture of what current table structure is and how many rows you’re dealing with.

mysql> SHOW CREATE TABLE dim_datetime\G
*************************** 1. row ***************************
       Table: dim_datetime
Create Table: CREATE TABLE `dim_datetime` (
  `date_key` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `date_datetime` datetime NOT NULL,
  `day_name` enum('SUNDAY','MONDAY','TUESDAY','WEDNESDAY','THURSDAY','FRIDAY','SATURDAY') DEFAULT NULL,
  `month_name` enum('JANUARY','FEBRUARY','MARCH','APRIL','MAY','JUNE','JULY','AUGUST','SEPTEMBER','OCTOBER','NOVEMBER','DECEMBER') DEFAULT NULL,
  `quarter` enum('Q1','Q2','Q3','Q4') DEFAULT NULL,
  `is_weekend` tinyint(3) unsigned DEFAULT NULL,
  `is_holiday` tinyint(3) unsigned DEFAULT NULL,
  PRIMARY KEY (`date_key`,`date_datetime`),
  UNIQUE KEY `date_datetime` (`date_datetime`)
) ENGINE=InnoDB AUTO_INCREMENT=219000 DEFAULT CHARSET=latin1
/*!50100 PARTITION BY RANGE (YEAR(date_datetime))
(PARTITION P1 VALUES LESS THAN (2007) ENGINE = InnoDB,
 PARTITION P2 VALUES LESS THAN (2008) ENGINE = InnoDB,
 PARTITION P3 VALUES LESS THAN (2009) ENGINE = InnoDB,
 PARTITION P4 VALUES LESS THAN (2010) ENGINE = InnoDB,
 PARTITION P5 VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */
1 row in set (0.01 sec)
mysql> SELECT COUNT(*) FROM dim_datetime;
+----------+
| COUNT(*) |
+----------+
|   218999 |
+----------+
1 row in set (0.46 sec)

3. Next, create a new table that includes the changes that would be implemented in your ALTER TABLE table_name statement.

CREATE TABLE `dim_datetime_NEW` (
  `date_key` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `date_datetime` datetime NOT NULL,
  `day_name` enum('SUNDAY','MONDAY','TUESDAY','WEDNESDAY','THURSDAY','FRIDAY','SATURDAY') DEFAULT NULL,
  `month_name` enum('JANUARY','FEBRUARY','MARCH','APRIL','MAY','JUNE','JULY','AUGUST','SEPTEMBER','OCTOBER','NOVEMBER','DECEMBER') DEFAULT NULL,
  `quarter` enum('Q1','Q2','Q3','Q4') DEFAULT NULL,
  `is_weekend` tinyint(3) unsigned DEFAULT NULL,
  `is_holiday` tinyint(3) unsigned DEFAULT NULL,
  PRIMARY KEY (`date_key`,`date_datetime`),
  UNIQUE KEY `date_datetime` (`date_datetime`)
) ENGINE=InnoDB AUTO_INCREMENT=219000 DEFAULT CHARSET=latin1
/*!50100 PARTITION BY RANGE (YEAR(date_datetime))
(PARTITION P1 VALUES LESS THAN (2007) ENGINE = InnoDB,
 PARTITION P2 VALUES LESS THAN (2008) ENGINE = InnoDB,
 PARTITION P3 VALUES LESS THAN (2009) ENGINE = InnoDB,
 PARTITION P4 VALUES LESS THAN (2010) ENGINE = InnoDB,
 PARTITION P5 VALUES LESS THAN (2011) ENGINE = InnoDB,
 PARTITION P6 VALUES LESS THAN (2012) ENGINE = InnoDB,
 PARTITION P7 VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */
;

4. Next, write a script to loop through your new table and your old table and compare values row by row. This will come in handy later.

SELECT d1.*, d2.* 
FROM dim_datetime d1  
LEFT JOIN dim_datetime_NEW d2 ON (d1.date_key = d2.date_key) 
WHERE (d1.date_datetime != d2.date_datetime) 
OR (d2.date_datetime IS NULL)
OR (d1.day_name != d2.day_name)  
OR (d2.day_name IS NULL)
OR (d1.month_name != d2.month_name) 
OR (d2.month_name IS NULL) 
OR (d1.quarter != d2.quarter)  
OR (d2.quarter IS NULL)
OR (d1.is_weekend != d2.is_weekend)  
OR (d2.is_weekend IS NULL)
OR (d1.is_holiday != d2.is_holiday) 
OR (d2.is_holiday IS NULL);

5. Then, copy a few rows at a time from a set that are least likely to change to your new table. Tip: this is where those “last_updated DATETIME” columns come in handy.

mysql> INSERT INTO dim_datetime_NEW SELECT * FROM dim_datetime WHERE date_datetime < '2009-01-01 00:00:00';
Query OK, 26303 rows affected (1.35 sec)
Records: 26303  Duplicates: 0  Warnings: 0
-- leave out a couple years for this example.....
mysql> INSERT INTO dim_datetime_NEW SELECT * FROM dim_datetime WHERE date_datetime > '2010-12-31 23:59:59';
Query OK, 175176 rows affected (8.11 sec)
Records: 175176  Duplicates: 0  Warnings: 0

6. Once you’ve got all the old rows copied, take your service offline for maintenance, and copy the balance of rows.

mysql> INSERT INTO dim_datetime_NEW SELECT * FROM dim_datetime WHERE date_datetime BETWEEN '2009-01-01 00:00:00' AND '2010-12-31 23:59:59';
Query OK, 17520 rows affected (2.22 sec)
Records: 17520  Duplicates: 0  Warnings: 0

Remember that script you wrote back in step 4?

mysql> SELECT d1.*, d2.* 
    -> FROM dim_datetime d1  
    -> LEFT JOIN dim_datetime_NEW d2 ON (d1.date_key = d2.date_key) 
    -> WHERE (d1.date_datetime != d2.date_datetime) 
    -> OR (d2.date_datetime IS NULL)
    -> OR (d1.day_name != d2.day_name)  
    -> OR (d2.day_name IS NULL)
    -> OR (d1.month_name != d2.month_name) 
    -> OR (d2.month_name IS NULL) 
    -> OR (d1.quarter != d2.quarter)  
    -> OR (d2.quarter IS NULL)
    -> OR (d1.is_weekend != d2.is_weekend)  
    -> OR (d2.is_weekend IS NULL)
    -> OR (d1.is_holiday != d2.is_holiday) 
    -> OR (d2.is_holiday IS NULL);
Empty set (10.94 sec)

That’s what you’re after.
7. Since all the data has been copied, replacing your old structure is as simple as renaming your old table to table_BACKUP and your new table to table_name.

mysql> RENAME TABLE dim_datetime TO dim_datetime_ORIG;
Query OK, 0 rows affected (0.06 sec)
mysql> RENAME TABLE dim_datetime_NEW TO dim_datetime;
Query OK, 0 rows affected (0.03 sec)
mysql> COMMIT;
Query OK, 0 rows affected (0.00 sec)

8. Test, Test, Test and bring your service back online!

Had we run the original ALTER, the minimum impact would have been:

SET AUTOCOMMIT = 0;
ALTER TABLE dim_datetime_ORIG 
REORGANIZE PARTITION P5 INTO (
PARTITION P5 VALUES LESS THAN (2011), 
PARTITION P6 VALUES LESS THAN (2012), 
PARTITION P7 VALUES LESS THAN (MAXVALUE)
);
Query OK, 183936 rows affected (9.75 sec)
Records: 183936  Duplicates: 0  Warnings: 0

Of course this example would have only caused a read lock for < 10 seconds, but even so running in a production environment, a table lock could cause an outage, or deadlock or whatever. Using RENAME is much quicker and easier to recover from than ALTER.

Creating a Date Dimension Stored Procedure

Our data warehouse was getting pretty big. In fact, it was it was getting into the hundreds of gigabytes of fact data. We started looking for ways to save hard drive space. It became apparent that storing redundant varchars, datetimes, enums and other large column types was unnecessary and causing the scale constraints. Part of the solution was to convert datetime fields to dimensional key fields since it’s cheaper to store MEDIUMINTs (3 bytes) than DATETIME (8 bytes).

Think about it 100 million rows * 8 bytes per DATETIME = 762.93MB! We’ve got dozens of fact tables with > 50 million rows. Changing those columns to MEDIUMINT means 286.10MB storage or a savings of almost half a gigabyte. Ooooh, exciting!?!

To do this I had to create a table of dimensional data for date information.

The object is to capture data now to avoid functions, joins and look-ups later.
Here’s the table specs:

CREATE TABLE dim_datetime
(
    date_key MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT,
    date_datetime DATETIME NOT NULL,
    day_name ENUM('SUNDAY','MONDAY','TUESDAY','WEDNESDAY','THURSDAY','FRIDAY','SATURDAY'),
    month_name ENUM('JANUARY','FEBRUARY','MARCH','APRIL','MAY','JUNE','JULY','AUGUST','SEPTEMBER','OCTOBER','NOVEMBER','DECEMBER'),
    quarter ENUM('Q1','Q2','Q3','Q4'),
    is_weekend TINYINT UNSIGNED,
    is_holiday TINYINT UNSIGNED,
    PRIMARY KEY(date_key),
    UNIQUE KEY(date_datetime)
);

Next create the stored procedure. Notice that I’m baking in some default holidays for Christmas, New Years and 4th of July. Probably want to add or subtract depending on your use-case.

USE some_database;
DROP PROCEDURE IF EXISTS sproc_populate_dim_datetime;
DELIMITER $$
CREATE PROCEDURE sproc_populate_dim_datetime(start_date DATETIME, hours INT)
BEGIN
    DECLARE new_date DATETIME;
    DECLARE quarter CHAR(2);
    DECLARE counter INT UNSIGNED;
    DECLARE day_name VARCHAR(20);
    DECLARE month_name VARCHAR(20);
    DECLARE is_weekend TINYINT UNSIGNED;
    DECLARE is_holiday TINYINT UNSIGNED;
    SET counter :=1;
    BEGIN
        WHILE counter < hours DO
            SET new_date := DATE_ADD(start_date, INTERVAL counter HOUR);
            SET day_name := DAYNAME(new_date);
            SET month_name := MONTHNAME(new_date);
            SET quarter := (CASE
                                WHEN month_name IN ('January','February','March') THEN 'Q1'
                                WHEN month_name IN ('April','May','June') THEN 'Q2'
                                WHEN month_name IN ('July','August','September') THEN 'Q3'
                                WHEN month_name IN ('October','November','December') THEN 'Q4'
                            END);
            SET is_weekend := (CASE WHEN day_name IN ('Saturday','Sunday') THEN 1 ELSE 0 END);
            SET is_holiday := (CASE
                                WHEN (month_name IN ('January') AND DAYOFMONTH(new_date) = 1) THEN 1
                                WHEN (month_name IN ('July') AND DAYOFMONTH(new_date) = 4) THEN 1
                                WHEN (month_name IN ('December') AND DAYOFMONTH(new_date) = 25) THEN 1
                                ELSE 0 END);
            INSERT INTO dim_datetime (`date_datetime`,`day_name`,`month_name`,`quarter`,`is_weekend`,`is_holiday`)
                VALUES (new_date, day_name, month_name, quarter, is_weekend,is_holiday);
            SET counter := counter + 1;
        END WHILE;
    END;
END$$
DELIMITER ;

Create 25 years worth of hourly keys: start_datetime, years*days*hours. Depending on the # of years you run, you'll probably want to add a day for each leap year. I just stuck with a straight 25 numeric years.

CALL sproc_populate_dim_datetime('2006-01-01 00:00:00',25*365*24);

Now let's inspect our data.
What's our start and end datetime?

SELECT MIN(date_datetime),MAX(date_datetime) FROM dim_datetime;
+---------------------+---------------------+
| MIN(date_datetime)  | MAX(date_datetime)  |
+---------------------+---------------------+
| 2006-01-01 01:00:00 | 2030-12-25 23:00:00 |
+---------------------+---------------------+
1 row in set (0.00 sec)

How many Midnight, Jan 1s do we have?

SELECT COUNT(*) FROM dim_datetime
    WHERE MONTH(date_datetime) = 1
    AND DAYOFMONTH(date_datetime) = 1
    AND HOUR(date_datetime) = 0;
+----------+
| COUNT(*) |
+----------+
|       24 |
+----------+
1 row in set (0.15 sec)

There's your date dimension. If you use this or have some suggestions, drop me a line. Otherwise, enjoy!