maria-discuss team mailing list archive

[Weldon Whipple <weldon@xxxxxxxxxxx>]

Davi, Sergei (and everyone else),

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 ...):

3. SHOW DATABASE BINLOG STATUS;

which shows--for each database being binlogged to its own file:

a. the database name
b. whether binlogging is active
c. the name of the current binlog file
d. the binlog position in that file.

The script captures the coordinates, then

4. Creates a copy of the database(s) using mysqldump or scp/tar/gzip,
etc. (MyISAM databases will probably just be copied "raw," because it
is much faster. Innodb will probably have to use mysqldump or
something similar.)

5. UNLOCK TABLES:

On the new server, the copied/mysqldump'ed files will be used to bring
up the mysql on the new server.

At some future time--hopefully SOON--the tables could be locked again.
For any where the coordinates have changed:

The binlog file(s) would be copied to the new server, "played" by
mysqlbinlog, and the new server would be brought online on the new box
and taken offline on the old.

When the migration is determined to be complete, issue this command
(implemented by me) on the old box:

6.  STOP BINLOG FOR DATABASE <db>;

for all the user's databases

... and the migration is done.

QUESTIONS/ASSUMPTIONS:

1. In playing with the new FLUSH TABLES t1, t2, ..., tn command, I've
noticed that if the connection drops, the locks are all unlocked. ...
So our scripts will have to make sure the connection stays up. (ANY
SUGGESTIONS?)

2. The UNLOCK TABLES command unlocks all the tables locked earlier in
the same session. (So it doesn't need the variable argument list of
tables to unlock ...)

A post on dev.mysql.com shows the following possible sequence of commands:

FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS;
SYSTEM xfs_freeze -f /var/lib/mysql;
SYSTEM YOUR_SCRIPT_TO_CREATE_SNAPSHOT.sh;
SYSTEM xfs_freeze -u /var/lib/mysql;
UNLOCK TABLES;
EXIT;

(The FLUSH TABLES command would correspond to #2 above. SHOW MASTER
STATUS would correspond to #3 above. UNLOCK TABLES would correspond to
#5 above. The SYSTEM commands would have to be replaced by our own
commands/scripts.)

Would running (sub-)scripts from SYSTEM commands inside MySQL be
sufficient to hold the locks?

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];

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?

Thanks in advance for your feedback/help/advice!!

We.don Whipple




On Mon, Oct 18, 2010 at 12:48 PM, Davi Arnaut <davi.arnaut@xxxxxxxxxx> wrote:
> On 10/18/10 3:16 PM, Weldon Whipple wrote:
>>
>> Well, I finished a working prototype that does everything except the
>> locking and unlocking of databases. Someone mentioned that it's now
>> possible to lock individual tables. Would you--anyone--be willing to
>> point me toward places in the 5.5 code that use that
>> locking/unlocking?
>>
>
> Do you want to lock the database, or individual tables, or both? Anyway,
> take a look at mysql_rm_db as the locking is all very similar. Roughly, the
> algorithm of a DROP DATABASE is:
>
>        // Protect against other schema operations
>        lock_schema_name("db1");
>        // Build list of tables in the db1 directory
>        table_list = ..
>        // Initialize metadata lock request
>        table_list->mdl_request.init(MDL_key::TABLE,
>                table_list->db,
>                table_list->table_name, MDL_EXCLUSIVE);
>        // Lock tables
>        lock_table_names(thd, tables, NULL ...)
>        // Remove from cache and drop the table...
>
> The best thing at this point is to study (and debug) a similar statement and
> understand how metadata locks work (mdl prefix) or how tables are
> open/locked.
>
> Regards,
>
> Davi
>
> --
> MySQL Internals Mailing List
> For list archives: http://lists.mysql.com/internals
> To unsubscribe:    http://lists.mysql.com/internals?unsub=weldon@xxxxxxxxxxx
>
>