Rebuilding bad bitcask partitions in Riak

We had one of our Riak nodes that in a couple weeks time started eating up all it's disk recently.

Here's what we noticed:

1. Two of the twenty or so partitions on the node were 5 to 10 x the average size of the other partitions. The average partitions size was between 20 and 30 GB, and yet 2 of the partitions were 160GB and 210GB.
2. The logs showed that we had run out of open files even though we have the riak user set for max_open_files at 100k.  As it turns out, during the hardware maint I had started riak from a sudo -i session which gave the shell the default 1024 max_open_files setting.
3. After restarting Riak with the correct max_open_files setting, we noticed a lot of 0 byte bitcask files which we removed, as well as some invalid bitcask hint files which we cleaned up.
4. Once all the invalid bitcask files were cleaned up, we realized that any merge process against the 2 large partitions always failed, implying there were some corrupt bitcask files, or the merge process was timing out.
5. Rather than rebuild the whole node, we decided just to rebuild the specific partitions.

Here's the process we used for rebuilding the specific partitions:

- Stop Riak

# riak stop

- Move the bad partitions elsewhere for backup purposes

# mv /{riak_datadir}/riak/bitcask/{partition} /{backup_dir}/

- Start Riak

# riak start

- Wait for riak_kv process to start

# riak-admin wait-for-service riak_kv riak@{riak_node_name}

- Attach to riak and start the repair process

# riak attach
(riak@{node}) 1> Partitions =  [{part 1},{part 2},...{part n}].
(riak@{node}) 2> [riak_kv_vnode:repair(P) || P <- Partitions].

Note: to quit the riak attach shell, use cntl-D, not cntl-C  (otherwise you will stop riak)

- Check status of the repair process

# riak-admin transfers

All in all, Riak recovers quite nicely, and it wasn't terribly difficult to find out what was going on.

On a side note, Basho does a great job if you have the benefit of using their support .

MySQL Data Woes... or, Making Use of the Information_Schema

To be fair - I highly doubt it has anything to do with MySQL.

Database corruption - that's a different issue, often related to a bug in database code; but this was a data corruption issue, which is always a pain in the you know what to figure out and get fixed.

I spent the last day trying to figure out the weirdness in a couple of our databases, digging around, explaining to developers that there's no way it could be an issue with the database - it must be in their caching layer somewhere.

It never really occurred to me that the issue might be the data itself... duh. Especially when it turned out to be an issue with a few fields in a few rows with a little extra utf8 character in front of the actual data that happened to map to an ASCII zero (end of line char, I think). Nice.

Now we have to go check every stinkin database, every table, every char based column and look for that character.... Ugh.

Oh yea - we have an information_schema now in 5.1.x ... :)

So about an hour later after a quick code review with the developers and some validation tests, we had a cool little data issue checker to quickly run everywhere, that uses the information_schema database. I'm sure there's a slicker way to do it (and who knows, there might even be one up on MySQL Forge already). I suppose I should have checked there first, but I then again, it's always fun to re-invent the wheel...

Here's what we came up with:

DELIMITER /

DROP PROCEDURE IF EXISTS check_invalid_data /

CREATE PROCEDURE check_invalid_data(db_to_check varchar(128)) -- pass in db name
BEGIN
      DECLARE done INT DEFAULT 0;
      DECLARE exec_str TEXT;
      DECLARE sql_str TEXT;
      DECLARE exec_cur CURSOR FOR
              SELECT check_string FROM validate_data;
      DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

      DROP TEMPORARY TABLE IF EXISTS validate_data;

      CREATE TEMPORARY TABLE validate_data
      (
              id              INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
              check_string    TEXT
      );

      DROP TEMPORARY TABLE IF EXISTS validate_results;

      CREATE TEMPORARY TABLE validate_results
      (
              id              INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
              table_info      VARCHAR (256),
              column_info     VARCHAR (256),
              column_length   INT
      );

      INSERT INTO validate_data (check_string)
      SELECT  CONCAT ('INSERT INTO validate_results (table_info,column_info, column_length) SELECT ''',table_name,''',''',
                      column_name, ''',LENGTH(',column_name,') FROM ', db_to_check ,
                      '.' , table_name, ' WHERE ASCII (', column_name ,' ) = 0 AND LENGTH (', column_name ,') > 0 ;')
      FROM     information_schema.columns
      WHERE    table_schema = db_to_check
      AND      data_type IN ('char','varchar','text','mediumtext','tinytext','long','long varchar','enum','set');

      OPEN exec_cur;

      REPEAT
              FETCH exec_cur INTO sql_str;
              set @exec_str = sql_str;
              IF NOT done THEN
                      PREPARE validate_stmt FROM @exec_str;
                      EXECUTE validate_stmt;
                      DEALLOCATE PREPARE validate_stmt;
              END IF;
      UNTIL done END REPEAT;

      CLOSE exec_cur;

      SELECT * FROM validate_results;
END
/
DELIMITER ;