List:Internals« Previous MessageNext Message »
From:Dmitry Lenev Date:October 22 2010 5:15am
Subject:Re: How to lock individual tables in 1-db-per-file binlogging
View as plain text  
Hello Weldon!

* Weldon Whipple <weldon@stripped> [10/10/20 00:26]:
> Thanks for the great feedback. It got me looking at the capabilities
> of MySQL 5.5.
> 
> It's POSSIBLE that the new syntax:
> 
> FLUSH TABLES t1, t2, t3, ... , tn WITH READ LOCK
> 
> is PRECISELY the functionality we need!
> 
> Our (perl) migration script MIGHT (for example) do the following:
> 
> Issue the following command (implemented by me ...):
> 
> 1. START BINLOG FOR DATABASE <db>;
> 
> (for each database a given user owns. [Hmmm... Maybe my implementation
> allows multiple <db> arguments. I need to check.] Each database would
> start binlogging to its own file. [We will configure--in my.cnf--the
> 1-database-per-file binlogs to be stored on a very fast solid-state
> drive.])
> 
> Then the script creates a list of all tables in all the user's
> databases being migrated to another server and issues the command (new
> to MySQL 5.5):
> 
> 2. FLUSH TABLES t1, t2, t3, ..., tn WITH READ LOCK;
> 
> (where the tables t1, t2, ..., tn are the tables being migrated.)
> 
> Then issue the following command (implemented by me ...):

AFAIU you also want to prohibit creation of tables in this database
after this point as otherwise you can't guarantee binlog stability.
Also what about non-table objects like stored routines - don't you
want to block their modification as well? The above statement won't
provide this as it locks only *existing* *tables*.

OTOH in my opinion it should be fairly easy to implement functionality
that you need (i.e. blocking of all DDL and DML that changes data in
database) on the basis of FLUSH TABLES <table_list> WITH READ LOCK
implementation. You just need to take database-scope S metadata lock
in addition to locking and flushing all tables in database. Such lock
will block creation of new tables and any DDL on stored routines in
the database.

> 
> BUG IDENTIFIED.
> 
> I noticed the following mysqld-crashing bug--in 5.5.6 rc as well as
> the one I modified:
> 
> 1. FLUSH TABLES t1, t2, t3, ..., tn WITH READ LOCK;
> 2. FLUSH TABLES;
> 
> The second FLUSH TABLES (with no added tokens) crashes at mdl.cc:1563 :
> 
> DBUG_ASSERT(mdl_request->type != MDL_EXCLUSIVE ||
>            is_lock_owner(MDL_key::GLOBAL, "", "", MDL_INTENTION_EXCLUSIVE));
> 
> because is_lock_owner() returns NULL when FLUSH TABLES is called
> immediately after FLUSH TABLES [with additional args];

I have taken the liberty to report this issue as bug #57649
(http://bugs.mysql.com/bug.php?id=57649).

Thanks for pointing it out!

> 
> QUESTION:
> I read that Larry Ellison stated (at a conference) that 5.5 would be
> out by year end. How likely is that? Our company generally waits for
> some point releases before changing our customer boxes to the next
> release.
> 
> Should I try to back port those locks to 5.1 in the interim? ... or
> leave well enough alone?

I don't think it makes sense to backport metadata locks to 5.1 as it is
likely to require back-porting of too much other changes from 5.5.

OTOH implementing what you need on top of infrastructure already present
in 5.1 is likely to require significant efforts as well.

So I think it is better to base your work on 5.5 tree.

Best regards,
-- 
Dmitry Lenev, Software Developer
Oracle Development SPB/MySQL, www.mysql.com

Are you MySQL certified?  http://www.mysql.com/certification
Thread
How to lock individual tables in 1-db-per-file binlogging (Was: Re:Per-db binlogging)Weldon Whipple18 Oct
  • Re: How to lock individual tables in 1-db-per-file binlogging (Was:Re: Per-db binlogging)Davi Arnaut18 Oct
    • Re: How to lock individual tables in 1-db-per-file binlogging (Was:Re: Per-db binlogging)Weldon Whipple18 Oct
    • Re: How to lock individual tables in 1-db-per-file binlogging (Was:Re: Per-db binlogging)Weldon Whipple19 Oct
      • Re: How to lock individual tables in 1-db-per-file binlogging(Was: Re: Per-db binlogging)Dmitry Lenev22 Oct
        • Re: How to lock individual tables in 1-db-per-file binlogging (Was:Re: Per-db binlogging)Weldon Whipple28 Oct
          • Re: How to lock individual tables in 1-db-per-file binlogging (Was: Re: Per-db binlogging)Mark Leith28 Oct
            • Re: How to lock individual tables in 1-db-per-file binlogging (Was:Re: Per-db binlogging)Weldon Whipple29 Oct