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 .

By all means, learn from my mistakes as a DBA!

Here are 3 recent ' oops... wish I hadn't done that :/ ' mistakes I've made since joining moz that you might as well avoid (I'm sure there will be more, but they better not be the same)

Reviewing config files for MySQL, but not all of the defaults 

    We recently migrated a few MySQL databases to a new datacenter, and took advantage of the migration to upgrade the MySQL version(s) at the same time.  Of course I made sure to have another DBA review all my new config changes that were deprecated or new that we wanted take advantage of.  After things were migrated and the new applications were tested, things seemed fine - just a few bugs here and there, primarily around character set issues ( which, obviously, would be a dev problem, right? ).

Oops...  the default character set for MySQL is not utf8 !

     Lesson Learned:  

Don't just check that the configs have the correct settings; check for settings that are not there (defaults), as they are the ones that may be great for most, but perhaps not so great for you.

Restricting the # connections allowed for a user

    In order to introduce a quick 'throttling' mechanism for a job based system with a MySQL backend we implemented user resource restrictions.  It worked like a charm in staging, so we rolled it out to prod; as soon as the job connections exceeded 30 they were disconnected and had to wait till there other jobs completed, and all the devs had to do was change the user name for the job connection url.  Sweet!

   Later that day we started getting host denied errors...

Oops...  max_connection_errors matters !

     Lesson Learned:  

Make sure to take time to understand secondary effects of changes over time, as often issues don't show up immediately   That's why 'soak' or 'bake time' is not something that should be overlooked when implementing changes.

Upgrading percona toolkit and with Capistrano

    Awesome!  I got my Capistrano scripts working great - it's time to upgrade our percona toolkit on all our servers, so let's start with all our slave instances first ( it is production... ).  What's cool it with cap I can deploy it to all the slaves at once.

   What's not so awesome is I can also upgrade and restart mysql on all the slaves at once too :/

Oops...   'apt-get -y upgrade' percona-toolkit still upgrades everything, including MySQL !

     Lesson Learned:  

Make sure staging / test systems exactly mimic production when verifying deployment changes, and if you're working on a different distribution of Linux or MySQL than you are used to make sure to read the man pages!  ( apt-get install -y percona-toolkit is what I wanted, and what the man page said to do )

Yes, even those of us that have been around the block a few times still make mistakes .... 

MySQL binlogs - Don't forget to do your homework!

Now that I'm back doing just database stuff, I've come to realize I've gotten a little sloppy about doing my homework.  Homework's never been my favorite thing in the world, but it often reduces stress when your under the gun during an outage or upgrade...

We had a MySQL database server that's been slow on DML changes, and based on the slowest statements being 'COMMIT', we had a good mind it was an issue with the binary logs, or the disk subsystem that MySQL was writing the binary logs to.

Investigation with iostat (iostat -xtc 10), we were able to narrow it down to a disk subsystem being fully utilized and queuing.  

Easy fix right?  Just change the location of the binlogs via the startup option in my.cnf and restart.

If I had done my homework, I would have known that restarting MySQL with a new location for binlogs would reset the binary log to mysql-bin.000001, and thus break replication to the slave... Oops.

No need to panic - just check the master's log position and run the following on the slave:

 mysql> change master to master_log_file=mysql-bin.000001, master_log_pos=123

Perfect - the slave caught up and was good to go - except that I neglected to recall the fact we had enabled the application and started writing to the master before I checked the log position....  Oops. (2)

Luckily, replication was up to date when we restarted, so I was able to use the handy dandy mysqldump utility to load all the transactions from the new binary log up to the point in which I checked the master:

mysqlbinlog --stop-position=123 mysql-bin.000001 | mysql   

Moral of the story: Doing your homework is usually a good thing! 

Missed the MySQL Conference

My co-worker got back from the MySQL Conference today. Sadly I didn't make it this year, but sounds like it was great as usual.

Sorry I didn't get to see you all, but I'll do my best to catch you next year...

Firewalls... what can you say?

Every time I've had to setup something 'special' with a database server and a firewall for the first time there always seems to be way to many issues.

With MySQL it's been mostly straight forward; opening an additional port or to for supporting the enterprise monitor or something, but nothing crazy.

Oracle, however, gets me every time. :(

We were setting up a new Oracle 11g instance, and decided we needed to enable shared server mode ( used to be MTS ); Nothing fancy, no connection pooling, no RAC or anything like that.

So, having done this in the past, we setup the dispatcher to use a dedicated port (rather than the dynamic ports, which firewalls love). Started testing things and realized we kept getting forced disconnects.

somewhat cryptic errors:

org.hibernate.util.JDBCExceptionReporter: SQL Error: 17002, SQLState: 08006
org.hibernate.util.JDBCExceptionReporter: IO Error: Size Data Unit (SDU) mismatch

Then we spend a couple days working with devs, trying different dispatcher/server process scenarios, open a support ticket, etc. Nada.

So we try the next obvious thing and take the firewall out of the equation, and sure enough, everything works fine. So we go back to playing with more configurations, turning off sql inspection on the firewall, etc, etc. Then we start doing network packet traces and analyzing those (another couple days go by).

Then our network engineer notices one of the packets has a tcp option he hasn't seen before 'URG'. Hmm... Sure enough, those packets are generated on the database side, but never make it through the firewall.

A bit more reading/googleing and it becomes clear:

Oracle has a sqlnet option called disable_oob that is off by default, and this allows Oracle to send packets marked 'URGENT' for things like client disconnects, and obviously for some things around dispatchers & shared processes.

Anyway, let those packets pass through the firewall, and it starts working like a charm. One week down, more packet sniffing than I care to do again the rest of the year, all so I can get it setup and forget about it for 5 years till I run into the issue again... :)

More info: Oracle Docs on SQLNet

MySQL Remote Connections for Replication

Here is another interesting problem:

When trying to set up master to master replication from Server A to Server B, the replication user can login from A to B, but not the other way. The error thrown out by MySQL:

ERROR 1130 (HY000): Host '' is not allowed to connect to this MySQL server

Things to check:
1. Replication user password and host
2. Ability to ping the remote server
3. nslookup the remote server and then nslookup the ip for the reverse lookup

The last one solved the problem.

Much Ado about a space

After upgrading SQL Server 2005 to SP3 I had difficulty bringing SQL server back online. The error message indicated a problem when opening 'master.mdf' or a problem with start up parameters. The parameters looked ok and I did not want to reinstall the SQL server.... Online research pointed to potential master database corruption, however the problem turned out to be a simple one -there was an extra space infront the start up parameters in the Configuration Manager. SQL server started up after the extra space was removed.

Error occurred while opening file 'master.mdf' to obtain configuration information at startup. An invalid startup option might have caused the error. Verify your startup options, and correct or remove them if necessary.

Varbinary to string

I've experienced a particular frustration with SQL Server many times over the years: the seeming impossibility of programmatically fetching a varbinary value from a table, and converting it to the string value required by a procedure call in the same batch. One example would be when troubleshooting replication by extracting a xact_seqno varbinary(16) value from the MSdistribution_history table, and using it as the value for the nchar(22) @xact_seqno_start and/or @xact_seqno_end input parameters to the sp_browsereplcmds procedure.

For a long time, I just did this by hand because there didn't seem to be a way to get the value out. Later, I sometimes used the output parameter functionality of sp_executesql to bluff my way through this. However, I just discovered the there is a built-in (but undocumented) function that allows you to transform a varbinary into a string! It's master.dbo.fn_varbintohexstr(). Here's a script that accomplishes the replication troubleshooting task I cited above (I assume here you already know the publication database id and agent id - finding those is another subject altogether):

declare @last_xact_seqno varbinary(16)
,@last_xact_seqno_str nchar(22)

select @last_xact_seqno = max(xact_seqno)
from distribution..MSdistribution_history
where agent_id = 6
and error_id = 0

set @last_xact_seqno_str = master.dbo.fn_varbintohexstr(@last_xact_seqno)

exec sp_browsereplcmds
@xact_seqno_start = @last_xact_seqno_str
,@publisher_database_id = 7

solving frequent replication distribution latency errors

We have many millions of commands a day passing through a 2-node peer-to-peer replication topology. At some point, replication lost its ability to keep up with distribution. After much research, we replicated the issue to Microsoft and were told that our distribution database was too big. They recommended that we set immediate_sync on the publication to false.

To me, it made no sense why this would help. We had replication distribution history latency set to 48 hours - wouldn't transactions be retained for that amount of time regardless of whether they'd been replicated or not?

Luckily, I found this MSDN blog post, which does a great job of explaining the interaction between min transaction history retention, max transaction history retention, and immediate_sync. Our plan now is to set min retention to 8 hours (we need a buffer that will allow us to initialize from backup), max retention to 36 hours, and immediate_sync to false. This will keep all transactions for 8 hours, but replicated transactions after that buffer will be deleted as soon as they're replicated to existing subscribers. Transactions will only be retained for 36 hours if replication gets seriously backed up again.

Shrinking data files, part 1

You've probably heard admonishments to avoid shrinking data files, especially on a heavily used database. The standard usage of DBCC SHRINKDATABASE or DBCC SHRINKFILE (not using the TRUNCATEONLY option of these commands) will move any data at the tail of the file to the beginning. The most frequently cited negative consequence of this action is serious fragmentation of the data you moved as data is moved page by page to the beginning of the file with presumably no attention paid to the objects represented by the data. In my case, shrinking data files also caused very heavy I/O load on my staging server, with sustained average disk queues reaching up into the hundreds. Definitely not something I wanted to attempt on a heavily loaded production server.

However, I had a very large database file (>400 GB) from which I had moved a substantial portion (>75%) of the data to a different filegroup. After several attempts to shrink the data file and witnessing the resulting abysmal performance, I found myself wanting to know exactly what was that data at the end of the file so I could move it myself by just rebuilding the indexes. I could then just use DBCC SHRINKFILE with TRUNCATEONLY to recover the space with none of the fragmentation. Most of the indexes in my database can even be rebuilt online using ALTER INDEX . . . REBUILD WITH (ONLINE=ON).

Here's how I found out what was sitting in the back rows of my data file:

First, I already knew that the GAM pages could tell me whether a particular extent is allocated or not. If you don't know what a GAM page is, check Books Online or any one of hundreds of websites and books that can offer more detail. Basically, the GAM is a page in a data file that is a map of the next 500,000 (or so) extents that indicates whether each is allocated or not. There's a GAM page every so often in a data file to indicate the status of the next set of extents. What I wanted to look at was the last GAM in the data file.

As I already mentioned, the data file I'm working with is fairly large, so I wasn't exactly sure how to find the last GAM. I found documentation indicating that the first GAM of each data file is on the 3rd page of the data file (actually page id 2 because of zero-based counting), and another one every 511,230 pages, so I could've probably figured it out by looking at the total size, figuring how many GAMs there were, yadda yadda yadda. What I did instead was look at the last page in the file, and it told me what its parent GAM was!

I found the last page in the data file by getting the total number of pages in the data file:

select size, * from sys.database_files where file_id = 1

Size in the sys.database_files table actually represents the number of pages in the data file. In this case, the number of pages was 51380223. I then ran a DBCC PAGE for the last page in the data file: page id = 51380222 (remember the zero-based counting). Before running that command, though, I first needed to turn on trace flag 3604 so DBCC results would be returned to the client:

DBCC TRACEON (3604)
DBCC PAGE (10,1,51380222,3)

In the DBCC PAGE command above, the first parameter is the database id, second is the file id, the third value is the page you want to view, and the last parameter represents the type of output you want to see.

In the output of the DBCC PAGE command, on the Messages tab of the output (I ran this in grid mode in SQL Server Management Studio), I scrolled down to the Allocation Status section. There was a line that read "GAM (1:x) = ALLOCATED". That x is the page number of the last GAM in the data file! I then ran DBCC PAGE again with that value:

DBCC PAGE (10,1,x,3)

The output of this command included lists of page ranges, marked ALLOCATED or NOT ALLOCATED. I scrolled all the way to the bottom tolook for the last ALLOCATED page range. My output looked something like this:

(1:51372888) - (1:51372960) = NOT ALLOCATED
(1:51372968) - = ALLOCATED
(1:51372976) - (1:51373704) = NOT ALLOCATED
(1:51373712) - = ALLOCATED
(1:51373720) - (1:51374552) = NOT ALLOCATED

Pages 51373720 - 51374552 were already not allocated. I knew that if I ran a DBCC SHRINKFILE with the TRUNCATEONLY option, this range of pages would be removed from the data file, and that would be all that the command would do. However, I need to clear more than 300 GB of space in all , and a few hundred pages wasn't going to cut it. So I decided to see what was on page 51373712, and rebuild that index. Based on my reading, I assumed that when rebuilding, SQL Server would go back to the GAM and other related file allocation pages, and find the first pages in the data file that could hold the rebuilt index. This would effectively move pages from the end of the data file, although I was aware that it could end up removing only one extent worth of pages at the very end. To that end, I again ran DBCC PAGE to find out what was on that data page:

DBCC PAGE(10,1,51373712,3)

The PAGE HEADER section of the output included the following information:

Metadata: IndexId = 1
Metadata: ObjectId = 437676587

Aha! I rebuilt that index and as I expected, it cleared up some bunch of extents at the end of the file. After repeating this process several times, I was able to shrink my file by a sizable margin using DBCC SHRINKFILE with the TRUNCATEONLY option. The db file was actually less - rather than more - fragmented when I was done, and I never encountered those horrible disk queue lengths.

Of course, this doesn't work so well if the last allocated page is from a large index that doesn't allow REBUILD WITH (ONLINE=ON). When I figure out how to get past that, I'll post again.

SQL Server: Named Instance Or Default?

I was looking for an easy way to tell if the current instance of SQL Server is a default or named instance since we use both. I could not find anything online. After a little bit of poking around, I figured it out -its quite simple. Go to 'My Computer' and right click for the 'Manage' option. Then under 'Services' look for the SQL Server service entry. If its a named instance it will have the name listed as 'SQL Server (NAME_OF_INSTANCE)', however if its a default instance, it will simply say 'SQL Server (MSSQLSERVER)'

To be sure, you can right click on the SQL Server service entry. Select 'properties' and look at the path to the executable. If its a named instance, the sqlserver.exe will be followed by -s{Servername} or MSSQLSERVER if its a default instance.

You can also open SQL Server Management Studio and right click on the server name. A named instance is usually listed as 'ServerName\InstanceName'

Lost and Found ?

Sometimes you just have to laugh at the crazy things that can kill a good evening.

I had this brilliant idea to change our replication setup on one of our Master-Master replication server setups this week. I got sick of having to restart MySQL every time we wanted to add a new database and have it included in the list of replicated databases - we were using replicate-do-db in our configs.

So it seems very straight forward to change to ignore-db or ignore-table (because of cross database updates).

After a few weeks in QA and a few weeks in staging - no problems, no issues, no complaints... let's go for it!


Yea, as soon as we deploy and restart MySQL to pickup the configs, replication fails and stops!

And of course to make it lots of fun, replication on a couple other servers failing at the same time for unrelated reasons, and then a migration of another application that night having issues...)

So look around a while, check the MySQL Monitor ... and, wala - lost+found - table does not exist errors!

Yea, ext3 rears it's ugly head again. I'm sure it's probably my responsibility to make sure that lost+found directories are clean up, etc, etc, but it sure made for a headache this week.

The fix (knock on wood) was straight forward - we just added ignore-table=lost%.%

Seemed to do the trick. Maybe we should check out ReiserFS or xfs or zfs.

Either way, if there's a way to break replication, I'm sure I'll find it... ;)


On that note - if you love the file system (linux based) that you are using for your MySQL servers, I'd love to hear your comments (good, bad, or ugly)

What just happened to the database?

It's always fun when you come into work and notice that one of your database monitors/graphs has changed from showing almost no row accesses/sec to 40,000 rows/sec in a matter of minutes. And then by lunch time they are showing 90,000 rows/sec. What's up with that?

MySQL Enterprise Monitor Row Accesses:






Well, of course the first thing you do is check all your cacti monitors, because there is obviously something wrong with the monitoring system.

Cacti Innodb Row Accesses:






Hmm... it's not the monitor. Must be a change in the app...

So you check with everyone, and no, nothings changed in the app. In fact, nothing even appears to be different (same basic app response times, same # requests/sec to the app, etc, etc)

Database Activity:






So it must be a query cache issue...

Query Cache:






Apparently not... :(

So what could cause that kind of dramatic increase in index rows read / sec ?

In reality, looking at the data distribution, the optimizer apparently decided to 'explain' it all away....

Current explain plan:

mysql> \e
-> ;
+--------------+-------------------------------+---------+------+------+----------+
possible_keys
| key | key_len | ref | rows | filtered
| Extra
+--------------+-------------------------------+---------+------+------+----------+
UIDX_TOTAL_RATING_OWNER_ABOUT,IDX_TOTAL_RATING_ABOUT,IDX_TOTAL_RATING_OWNER
| UIDX_TOTAL_RATING_OWNER_ABOUT | 180 | NULL | 5 | 80.00
| Using where; Using index; Using filesort |
+--------------+-------------------------------+---------+------+------+----------+
1 row in set, 1 warning (0.00 sec)


Previous explain plan:

mysql> \e
-> ;
+--------------+-------------------------------+---------+------+------+----------+
possible_keys
| key | key_len | ref | rows | filtered
| Extra
+--------------+-------------------------------+---------+------+------+----------+
UIDX_TOTAL_RATING_OWNER_ABOUT,IDX_TOTAL_RATING_ABOUT,IDX_TOTAL_RATING_OWNER
|IDX_TOTAL_RATING_OWNER | 42 | const,const | 1 | 100.00
| Using where |
+--------------+-------------------------------+---------+------+------+----------+
1 row in set, 1 warning (0.03 sec)

Interesting what a little change in data and a different path through the optimizer can do...

Partition Management....(Don't forget the Management)

You'd think by now I'd know better than to cut corners. But for some reason, I still somehow seem to decide once in a while for short term gains over long term benefits...

A while back I was overloaded with stuff so I set up a year's worth of new monthly partitions in one of our reporting databases. I did not, however, chose to create any nice job to automatically create new partitions or anything like that, as I did not have time (or so I thought).

Well, 'A while back' is now 18 months ago.... Hmm... 6 months of data in one partition. Ouch.

Might not be a big deal with Oracle, but it's SQLServer 2005. Sadly, just splitting 1 day of data into a new partition takes 10 minutes and 9 GB of transaction log space. Very depressing. (At least I tested and timed it before I tried to split one month's data into a new partition).

Kills me some times. Don't put off till tomorrow what you can do today, if tomorrow might become next year... ;)

Hopefully I'll come up with a slick way to split those partitions faster... we'll see.

And yes, I'm going to create a job to add partitions in advance.... :)

MySQL Replication and bad assumptions

Sometimes I amaze myself in my capacity to make assumptions about how things should work, especially when it comes to test plans... ( You know what happens when we assume, right? )

I had this great idea to setup a couple slaves off a master-master replication set something like this:

MASTER A  <-------------->  MASTER B
   |                          |
   |                          |
   |                          |
   V                          V
 SLAVE C                    SLAVE D

Makes sense, huh?

Then I'll just point our read / write app servers at MASTER A, and point half of our read / only app servers at SLAVE C, and half of our read only servers at SLAVE D. Sweet. Works like a charm.

Oops... Replication shows current everywhere, no alerts going off. But app servers pointed at SLAVE C show higher row counts than app servers on SLAVE D...

Yea, I never bothered to test that. That's what I get for making assumptions - if you could even call it that. When I thought things through, I quickly realized that there was no reason for MASTER B to send any of it's replicated updates on to SLAVE D, because I had told it not to write any of it's updates it received via replication to it's binary log... :)

All you replication experts out there could have probably told me that in your sleep, but of course, I never bothered to ask...

Just a couple lines in the config file could have saved me a few headaches, and a couple embarrassing apologies...

log-slave-updates
replicate-same-server-id = 0

Figures.. ;)

MySQL Partitioned Tables with Trigger enforced FK constraints

Well, I suppose its' true you can't use the official MySQL ALTER TABLE statement to do it, but if you're willing to trust your trigger coding abilities you can.

All you need is an extra table and a couple triggers.

The concept is fairly straight forward:

1. Create a before insert trigger on the child table that validates the parent exists in the parent table.
2. If there is no parent found, then insert a row into a third table that automatically forces an error (say, dup key on index)
   
3. Create a before delete trigger on the parent table that validate there are no children in the child table.
4. If there are children found, then insert a row into the third table that automatically forces an error (dup key on index)

It could also be extended to handle cascading deletes, but I'm personally not a fan of that, so I leave that to you... :)

Here's an example:
- Note: This example uses Innodb tables, but it can be done with MyISAM tables just as well.

---

Parent Table (orders) :

CREATE TABLE IF NOT EXISTS orders
(
id             INT         NOT NULL AUTO_INCREMENT,
customer_name  VARCHAR(30) NOT NULL,
total_amount   DECIMAL,
order_date     DATETIME ,
PRIMARY KEY (id,customer_name)
)
ENGINE=innodb
PARTITION BY RANGE (id)
(
PARTITION p1    VALUES LESS THAN (10),
PARTITION p2    VALUES LESS THAN (20),
PARTITION p3    VALUES LESS THAN (30),
PARTITION p4    VALUES LESS THAN (40),
PARTITION p_max VALUES LESS THAN MAXVALUE
);

---

Child Table (order_items) :

CREATE TABLE IF NOT EXISTS  order_items
(
id           INT         NOT NULL AUTO_INCREMENT,
order_id     INT         NOT NULL ,
item_name    VARCHAR(30) NOT NULL,
item_desc    VARCHAR(4000),
item_amount  DECIMAL,
PRIMARY KEY (id,order_id)
)
ENGINE=innodb
PARTITION BY HASH (order_id)
PARTITIONS 20
;

---

Special Table (fk_trigger) used to enforce FK:

CREATE TABLE IF NOT EXISTS fk_trigger
(
id    INT NOT NULL PRIMARY KEY DEFAULT 0
)
ENGINE=innodb
;

---

Insert default row into special table (fk_trigger)
that will force all inserts to fail:

INSERT INTO fk_trigger VALUES();

---

Child Trigger (order_item_fk_trig) used to check
that parent row exists before insert:

DELIMITER /

CREATE TRIGGER order_item_fk_trig
BEFORE INSERT ON  order_items
FOR EACH ROW BEGIN
  DECLARE my_count INT;
  SELECT   count(*)
  INTO     @my_count
  FROM     orders
  WHERE    id = NEW.order_id;

  IF (@my_count = 0) THEN
      INSERT INTO fk_trigger VALUES ();
  END IF;
END;
/

DELIMITER ;


Parent Trigger (order_fk_trig) used to check
that child row exists before delete:


DELIMITER /

CREATE TRIGGER order_fk_rev_trig
BEFORE DELETE ON  orders
FOR EACH ROW BEGIN
  DECLARE my_count INT;
  SELECT  count(*)
  INTO    @my_count
  FROM    order_items
  WHERE   order_id = OLD.id;

  IF (@my_count > 0) THEN
     INSERT INTO fk_trigger VALUES ();
  END IF;
END;
/

DELIMITER ;


Insert some data into the parent table

INSERT INTO orders
(customer_name,total_amount,order_date)
VALUES
('Customer 1',300.00,now()),
('Customer 2',600.00,now());


Insert some data into the child table

INSERT INTO order_items
(order_id,item_name,item_desc, item_amount)
VALUES
(1,'This 1','Stuff 1',100),
(1,'This 2','Stuff 2',100),
(1,'This 3','Stuff 3',100),
(2,'This 1','Stuff 1',200),
(2,'This 2','Stuff 2',200),
(2,'This 3','Stuff 3',200);


Try to delete order #1 (without deleting it's children)

DELETE FROM orders
WHERE  id = 1;


Results:

ERROR 1062 (23000): Duplicate entry '0' for key 'PRIMARY'


Try to insert orderitems for order #3 (Which doesn't exist)

INSERT INTO order_items
(order_id,item_name,item_desc, item_amount)
VALUES
(3,'This 3','Stuff 1',100);

Results:

ERROR 1062 (23000): Duplicate entry '0' for key 'PRIMARY'

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 ;

Where are all the good MySQL DBAs?

We've been looking for a good MySQL DBA at work for a little over a year now. At this point I'm beginning to wonder if DBA's are a dying breed all together...

Maybe it's the fact that we're in the Northwest (Seattle), or perhaps they've all gone to work for Percona?

Perhaps everyone has more fun developing? (We haven't had much trouble filling our Sr. Development positions or QA positions..)

It's getting to the point that I've actually tried to figure out how practical it would be integrate a remote DBA group like Percona, the Pythian Group, ProvenScaling, etc, etc... but that gets very complicated and impractical from a stand point that we really need a DBA that can play on both sides of the fence (operations and development), and work on a daily basis with our development group and QA teams on multiple projects using the Agile development methodology.

I know we're not the only ones in this situation up here ( and maybe that's half the problem).

Hmm... maybe there will be a couple good WAMU DBAs looking soon.... ;)

Well, if you're a decent DBA, enjoy working with open source, like the northwest, and are looking for a new, challenging position, send me an e-mail ... ( phil.hildebrand@gmail.com )

MySQL: Improving your skills with Forums

I haven't been a big user of the MySQL Forums till recently.

I'm not sure why - partly because I like to think I know something about what I do ( I just need to remember that there is always someone else who knows more), partly because I have a need to re-invent the wheel as often as possible, and partly because I dont' like waiting around for an answer...

It can be a little overwhelming when you look at the list of all them... Everything from the newbie forum to the forum for creating a custom storage engine. I guess that's what also makes them useful, if you know how to use them to your advantage.

Here's a few of my suggestions/tips towards getting the most out of the MySQL Forums :

1. The Forums are what we make them. The more you use them, the better they get.
2. Don't be afraid to answer a post. Sure, test your suggestion if you can, or at least think about it before you post it, but if you've run across the issue before, or have a good idea of where to dig in further, then by all means post!
3. Don't be concerned about someone else having a better idea.... someone probably will, and that's your opportunity to learn ( this has held me back many times from posting - thinking that someone might have a better answer, so wait for them to post)
4. Be as precise as possible when posting a question. If you can, include a test case, or a snipit of code.
   
5. Include any trouble shooting steps you've already tried in your post.
6. If you solve your issue, remember to post your solution so that the answer is there when I go looking for the answer to that same issue :)
7. Be nice... I don't always know what I'm talking about, but at least I'm trying to contribute...
8. If you post it, everybody sees it... (a note about security, proprietary info, etc)
9. If you have a lot of experience with one area (say, partitioning...) then add that Forum to your google reader, and try to answer as many posts as often as you can. It will encourage more use of the forum, and help you keep your skills sharp as well!
10. If you don't get an answer in one Forum, you might want to check another that has a similar subject. ( A replication issue with MyISAM tables might get an answer in the MyISAM forum if one doesn't come out of the Replication forum)

If you have other suggestions - let me know (and especially if you disagree with any of my suggestions....)

MySQL: replicate-* rules should be dynamically configurable

I wonder what the best way is to get a feature request more visibility (convert a feature request to an actual work item).

We use replicate-do-db on all our slave servers , so after many, many, restarts of our slave servers, I checked the bug list for any feature requests surrounding this, and about 6 months ago, one was opened:

replicate-* rules should be dynamically configurable

Sadly, there does not appear to be a work log for it, nor any targeted version. It seems to me that it's probably a fairly common use case...

I suppose adding or removing a db from the replicate list, might require restarting the slave processes that scan binlogs for transactions to apply, or at least, some way to notify the slave processes that additional databases need to be accounted for. Then we have to make sure that we start at the right point in the binlog, right? (what if the database as been active for a while, and then I try to dynamically add it to the replicate list)

There is a worklog item for having the master filter the database replication (to reduce network traffic), which has a proposed option of allowing this to be dynamic through the use of a CHANGE MASTER command. That might work for the dynamic replicate-* rules as well...

Maybe this is an opportunity to open my MySQL Internals book, and see if I can pull those C programming skills out of my cobweb filled dba brain...

Anyway - it would be interesting to know who all out there would like this as a feature...