Home |  MySQL Buzz |  FAQ |  Feeds |  Submit your blog feed |  Feedback |  Archive |  Aggregate feed RSS 2.0 English Deutsch Español Français Italiano 日本語 Русский Português 中文
Showing entries 1 to 5

Displaying posts with tag: row (reset)

InnoDB Redundant Row Format
Employee_Team +0 Vote Up -0Vote Down

Introduction

This article describes the InnoDB redundant row format. If you are new to InnoDB code base (a new developer starting to work with InnoDB), then this article is for you. I'll explain the row format by making use of a gdb session. An overview of the article is given below:

  • Create a simple table and populate few rows.
  • Access the page that contains the rows inserted.
  • Access a couple of rows and explain its format.
  • Give summary of redundant row format.
  • Useful gdb commands to analyse the InnoDB rows.
  • Look at a GNU Emacs Lisp function to traverse rows in an InnoDB index page.

To get the most out of this article, the reader is expected to repeat the gdb session as described here.

The Schema

Consider the

  [Read more...]
Temporary Tables and Replication
+2 Vote Up -0Vote Down

I recently wrote about non-deterministic queries in the replication stream. That’s resolved by using either MIXED or ROW based replication rather than STATEMENT based.

Another thing that’s not fully handled by STATEMENT based replication is temporary tables. Imagine the following:

  • Master: CREATE TEMPORARY TABLE rpltmpbreak (i INT);
  • Wait for slave to replicate this statement, then stop and start mysqld (not just STOP/START SLAVE)
  • Master: INSERT INTO rpltmpbreak VALUES (1);
  • Slave: SHOW SLAVE STATUS \G
  • If for any reason a slave server shuts down and restarts after the temp table creation, replication will break because the temporary table will no longer exist on the restarted slave server. It’s obvious when you think about it, but

      [Read more...]
    Non-Deterministic Query in Replication Stream
    +0 Vote Up -0Vote Down

    You might find a warning like the below in your error log:

    130522 17:54:18 [Warning] Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statements writing to a table with an auto-increment column after selecting from another table are unsafe because the order in which rows are retrieved determines what (if any) rows will be written. This order cannot be predicted and may differ on master and the slave.
    Statement: INSERT INTO tbl2 SELECT * FROM tbl1 WHERE col IN (417,523)

    What do MariaDB and MySQL mean with this warning? The server can’t guarantee that this exact query, with STATEMENT based replication, will always yield identical results on the slave.

    Does that mean that you have to use ROW based (or MIXED) replication? Possibly, but not


      [Read more...]
    [MySQL] Deleting/Updating Rows Common To 2 Tables – Speed And Slave Lag Considerations
    +1 Vote Up -0Vote Down

    Introduction

    A question I recently saw on Stack Overflow titled Faster way to delete matching [database] rows? prompted me to organize my thoughts and observations on the subject and quickly jot them down here.

    Here is the brief description of the task: say, you have 2 MySQL tables a and b. The tables contain the same type of data, for example log entries. Now you want to delete all or a subset of the entries in table a that exist in table b.

    Solutions Suggested By Others

    DELETE FROM a WHERE EXISTS (SELECT b.id FROM b WHERE b.id = a.id);
    DELETE a FROM a INNER JOIN b on a.id=b.id;
    DELETE FROM a WHERE id IN (SELECT id FROM b)

    The Problem With  [Read more...]

    Will you use row-based replication by default?
    +0 Vote Up -0Vote Down



    MySQL 5.1 introduces row based replication, a way of replicating data that fixes many inconsistencies of the statement based replication, the standard method used by MySQL so far.

    The good: row based replication solves some problems when replicating the result of non deterministic functions, such as UUID() or NOW().
    The bad: row-based replication may break existing applications, where you count on the quirks of statement based replication to execute conditionally (updates base on @@server_id, for example), and may perform badly on updates





      [Read more...]
    Showing entries 1 to 5

    Planet MySQL © 1995, 2014, Oracle Corporation and/or its affiliates   Legal Policies | Your Privacy Rights | Terms of Use

    Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.