Professional Web Applications Themes

Replikation, Transaktion and Power off - MySQL

Hi, I execute the following test scenario: - a Master/Slave-System (Statement-Based) with InnoDB. - a simple autoincrement Table (TestTable) I filled this table with data of the following script: ########################### #!/bin/bash for i in `seq 1 10000`;do echo "BEGIN;" for x in `seq 1 100`;do echo "INSERT INTO TestTable (text) values (\"Test: $x $i\");" done echo "COMMIT;" echo done ########################### And while the script executes and while the tion was active, I switched Power off. After reboot the Master starts well, and rolls the last incomplete transaction back. But the slave doesn't start! It logs: "Slave: Error 'Duplicate entry '244568' ...

  1. #1

    Default Replikation, Transaktion and Power off

    Hi,

    I execute the following test scenario:

    - a Master/Slave-System (Statement-Based) with InnoDB.
    - a simple autoincrement Table (TestTable)
    I filled this table with data of the following script:
    ###########################
    #!/bin/bash

    for i in `seq 1 10000`;do
    echo "BEGIN;"
    for x in `seq 1 100`;do
    echo "INSERT INTO TestTable (text) values (\"Test: $x $i\");"
    done
    echo "COMMIT;"
    echo
    done
    ###########################

    And while the script executes and while the tion was active, I
    switched Power off.
    After reboot the Master starts well, and rolls the last incomplete
    transaction back.

    But the slave doesn't start! It logs: "Slave: Error 'Duplicate entry
    '244568' for key 'PRIMARY'' on query."

    After a second try I start the slave with --skip-slave-start. => The
    table of the master has 600 entries less than the table of the master.

    Does that mean that the master rolls back 6 Transaktions, which the
    Slave does not roll back?

    The Slave table is always filled with hundreds of entries, so that
    means that the Slave consider the Transaktions.

    My HD does not support "hdparm -W0 /dev/hda", it returns:
    /dev/hda:
    setting drive write-caching to 0 (off)
    HDIO_DRIVE_CMD(setcache) failed: Input/output error


    Can this be the reason?

    Thanks in advance

    Markus Wenke

    Markus Wenke Guest

  2. #2

    Default Re: Replikation, Transaktion and Power off

    "Markus Wenke" <M.Wenkeweb.de> wrote:
    > And while the script executes and while the tion was active, I
    > switched Power off.
    > After reboot the Master starts well, and rolls the last incomplete
    > transaction back.
    >
    > But the slave doesn't start! It logs: "Slave: Error 'Duplicate entry
    > '244568' for key 'PRIMARY'' on query."
    [more master/slave inconsistencies]

    Using the default configuration, tion is not as fault tolerant
    (regarding crashes/powerloss) as the InnoDB transaction processing.

    Please see the discussion of --innodb-safe-binlog in the MySQL manual.

    [url]http://dev.mysql.com/doc/refman/5.0/en/innodb-parameters.html[/url]
    [url]http://dev.mysql.com/doc/refman/5.0/en/binary-log.html[/url]


    XL
    --
    Axel Schwenke, Senior Software Developer, MySQL AB

    Online User Manual: [url]http://dev.mysql.com/doc/refman/5.0/en/[/url]
    MySQL User Forums: [url]http://forums.mysql.com/[/url]
    Axel Schwenke Guest

  3. #3

    Default Re: Replikation, Transaktion and Power off


    Axel Schwenke schrieb:
    > "Markus Wenke" <M.Wenkeweb.de> wrote:
    >
    > > And while the script executes and while the tion was active, I
    > > switched Power off.
    > > After reboot the Master starts well, and rolls the last incomplete
    > > transaction back.
    > >
    > > But the slave doesn't start! It logs: "Slave: Error 'Duplicate entry
    > > '244568' for key 'PRIMARY'' on query."
    >
    > [more master/slave inconsistencies]
    >
    > Using the default configuration, tion is not as fault tolerant
    > (regarding crashes/powerloss) as the InnoDB transaction processing.
    >
    > Please see the discussion of --innodb-safe-binlog in the MySQL manual.
    >
    > [url]http://dev.mysql.com/doc/refman/5.0/en/innodb-parameters.html[/url]
    > [url]http://dev.mysql.com/doc/refman/5.0/en/binary-log.html[/url]
    >
    Hi,

    thanks s for the prompt answer.

    I use MySQL 5.1, and the manual says that this variable was removed in
    MySQL 5.0.3.
    It says that the XA Transactions avoid the problem. But do I have to
    make every transaction as a XA-Transaction to avoid an unsave binlog?
    I think the option "--innodb-save-binlog" is much easier, especially
    for existing Databases/Applications.

    thanks in advance

    Markus Wenke

    Markus Wenke Guest

  4. #4

    Default Re: Replikation, Transaktion and Power off

    "Markus Wenke" <M.Wenkeweb.de> wrote:
    > Axel Schwenke schrieb:
    >>
    >> Please see the discussion of --innodb-safe-binlog in the MySQL manual.
    >>
    >> [url]http://dev.mysql.com/doc/refman/5.0/en/innodb-parameters.html[/url]
    >> [url]http://dev.mysql.com/doc/refman/5.0/en/binary-log.html[/url]
    >
    > I use MySQL 5.1, and the manual says that this variable was removed in
    > MySQL 5.0.3.
    > It says that the XA Transactions avoid the problem. But do I have to
    > make every transaction as a XA-Transaction to avoid an unsave binlog?
    > I think the option "--innodb-save-binlog" is much easier, especially
    > for existing Databases/Applications.
    The manual is a bit short here. The option --innodb-safe-binlog is no
    longer neccessary in 5.0.3 and later, because now it can never happen
    that a transaction is written to the binlog, but not committed in the
    storage engine. This is achived by using XA features (2 phase commit;
    the binlog writer and the storage engine always agree upon transaction
    status). So the binlog will be in sync with all storage engines that
    implement XA.

    However you still need sync_binlog=1 on the master to have the binlog
    written to disk synchronously.

    Warning: some hard disk controllers [1][2] as well as some hard disks
    buffer disk writes. Make sure to turn off all write caches on hard
    disks and controllers. Otherwise you will lose the data in the cache
    in case of powerloss.

    [1] typically hardware RAID controllers
    [2] some RAID controllers have a backup battery for the cache contents
    in that case it is safe to leave the writecache turned on


    XL
    --
    Axel Schwenke, Senior Software Developer, MySQL AB

    Online User Manual: [url]http://dev.mysql.com/doc/refman/5.0/en/[/url]
    MySQL User Forums: [url]http://forums.mysql.com/[/url]
    Axel Schwenke Guest

Similar Threads

  1. aspnet_wp uses up 98~99% of CPU power
    By inho yi in forum ASP.NET Web Services
    Replies: 1
    Last Post: January 8th, 07:25 PM
  2. AIX 5.2 ad EMC Power Path
    By Michael Jones in forum AIX
    Replies: 6
    Last Post: September 12th, 01:42 PM
  3. power status?
    By lallous in forum SCO
    Replies: 1
    Last Post: August 22nd, 01:20 AM
  4. Power Consumption
    By Jenny in forum ASP.NET General
    Replies: 1
    Last Post: July 17th, 12:24 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139