Professional Web Applications Themes

Checksum Table producing different results? - MySQL

I've got 2 database servers running the same database (cannot do tion as one is live and the other development). One of the databases needs to be synced from time to time. Because it's rather large, I'm looking to only transfer tables that have changed, and hence am using the CHECKSUM TABLE command to determine if tables have identical data. Dev server is MySQL 5.0.24a Live is MySQL 5.0.18 The problem, is that a few of our tables NEVER report identical checksums. I have verified that they are identical (by exporting from one server, importing to the other as a ...

  1. #1

    Default Checksum Table producing different results?

    I've got 2 database servers running the same database (cannot do
    tion as one is live and the other development). One of the
    databases needs to be synced from time to time. Because it's rather
    large, I'm looking to only transfer tables that have changed, and
    hence am using the CHECKSUM TABLE command to determine if tables have
    identical data. Dev server is MySQL 5.0.24a Live is MySQL 5.0.18

    The problem, is that a few of our tables NEVER report identical
    checksums. I have verified that they are identical (by exporting from
    one server, importing to the other as a different name, and doing a
    checksum on that server). Any ideas as to what I can do? Schema's are
    Identical. I've ran optimize and repair table, to no effect. Thanks!

    ircmaxell Guest

  2. #2

    Default Re: Checksum Table producing different results?

    ircmaxell wrote: 
    you can use a product called sqlyog that allows for explicit scheduled
    syncs. i used it to only sync the data that has been changed in one
    server to the other server without ting enabled.

    --
    lark -- net
    To reply to me directly, delete "despam".
    lark Guest

  3. #3

    Default Re: Checksum Table producing different results?

    On May 21, 4:05 pm, lark <net> wrote: 

    >
    > you can use a product called sqlyog that allows for explicit scheduled
    > syncs. i used it to only sync the data that has been changed in one
    > server to the other server without ting enabled.
    >
    > --
    > lark -- net
    > To reply to me directly, delete "despam".[/ref]

    I tried SQLyog, but it's just too slow (my script can run about 30
    times in the time it takes SQL yog to run once)... If I can figure
    out this checksum thing it would be perfect...

    ircmaxell Guest

  4. #4

    Default Re: Checksum Table producing different results?

    ircmaxell wrote: 
    >> you can use a product called sqlyog that allows for explicit scheduled
    >> syncs. i used it to only sync the data that has been changed in one
    >> server to the other server without ting enabled.
    >>
    >> --
    >> lark -- net
    >> To reply to me directly, delete "despam".[/ref]
    >
    > I tried SQLyog, but it's just too slow (my script can run about 30
    > times in the time it takes SQL yog to run once)... If I can figure
    > out this checksum thing it would be perfect...
    >[/ref]
    i think the checksum changes every time there is a change in the table
    and i mean any change not just data value changes. i'd be interested to
    know if somebody else can verify this.

    --
    lark -- net
    To reply to me directly, delete "despam".
    lark Guest

  5. #5

    Default Re: Checksum Table producing different results?

    On May 21, 4:05 pm, lark <net> wrote: 

    >
    > you can use a product called sqlyog that allows for explicit scheduled
    > syncs. i used it to only sync the data that has been changed in one
    > server to the other server without ting enabled.
    >
    > --
    > lark -- net
    > To reply to me directly, delete "despam".[/ref]

    I just tried their administrator (before buying the enterprise
    version), and it seems quite slow. My sync tool (that I wrote) can
    sync the entire 60 meg database in the time it uploads a 1 meg table.
    If I could just figure out the checksum issue (not a REALLY big deal,
    but would be nice)...

    ircmaxell Guest

  6. #6

    Default Re: Checksum Table producing different results?

    On May 21, 4:05 pm, lark <net> wrote: 

    >
    > you can use a product called sqlyog that allows for explicit scheduled
    > syncs. i used it to only sync the data that has been changed in one
    > server to the other server without ting enabled.
    >
    > --
    > lark -- net
    > To reply to me directly, delete "despam".[/ref]

    I just tried their administrator (before buying the enterprise
    version), and it seems quite slow. My sync tool (that I wrote) can
    sync the entire 60 meg database in the time it uploads a 1 meg table.
    If I could just figure out the checksum issue (not a REALLY big deal,
    but would be nice)...

    ircmaxell Guest

  7. #7

    Default Re: Checksum Table producing different results?

    >I've got 2 database servers running the same database (cannot do 

    I don't think there is any guarantee that CHECKSUM TABLE produces
    the same checksum even if the data contents is the same. For
    example, the records could be in different physical order. (Try
    that with a text file: run md5 on the file, re-order a couple of
    the lines, and try again. You'll almost certainly come up with a
    different checksum.) Unallocated space might make a difference,
    too.
     


    Gordon Guest

  8. #8

    Default Re: Checksum Table producing different results?

    On May 22, 7:19 pm, org (Gordon Burditt) wrote: 
    >
    > I don't think there is any guarantee that CHECKSUM TABLE produces
    > the same checksum even if the data contents is the same. For
    > example, the records could be in different physical order. (Try
    > that with a text file: run md5 on the file, re-order a couple of
    > the lines, and try again. You'll almost certainly come up with a
    > different checksum.) Unallocated space might make a difference,
    > too.
    > [/ref]

    Is there any alternative as to how I can check a table for mirrored
    data?

    ircmaxell Guest

  9. #9

    Default Re: Checksum Table producing different results?

    >> >I've got 2 database servers running the same database (cannot do 
    >>
    >> I don't think there is any guarantee that CHECKSUM TABLE produces
    >> the same checksum even if the data contents is the same. For
    >> example, the records could be in different physical order. (Try
    >> that with a text file: run md5 on the file, re-order a couple of
    >> the lines, and try again. You'll almost certainly come up with a
    >> different checksum.) Unallocated space might make a difference,
    >> too.
    >> [/ref]
    >
    >Is there any alternative as to how I can check a table for mirrored
    >data?[/ref]

    Consider this: mysqldump both tables into separate files, using
    options that produce a single insert statement per record. Sort
    both files. (at this point you've probably messed up the file to
    the point of not being able to restore it, but this doesn't matter).
    Run diff(1) between the two sorted files.


    Gordon Guest

  10. #10

    Default Re: Checksum Table producing different results?

    On May 23, 5:46 pm, org (Gordon Burditt) wrote: [/ref]
    > [/ref]
    > [/ref]

    >
    > Consider this: mysqldump both tables into separate files, using
    > options that produce a single insert statement per record. Sort
    > both files. (at this point you've probably messed up the file to
    > the point of not being able to restore it, but this doesn't matter).
    > Run diff(1) between the two sorted files.- Hide quoted text -
    >
    > - Show quoted text -[/ref]

    That's the problem I was hoping to avoid... We've got a T-1 between
    the servers, and about 100meg of data. To download all the data would
    take about 9 minutes, processing it would be quick, and updating would
    be slow (about 30 minutes, as instead of syncing, if they differ, I
    drop the entire table and re-install it). I wish there was an easier
    (and faster) way...

    ircmaxell Guest

  11. #11

    Default Re: Checksum Table producing different results?

    == Quote from ircmaxell (com)'s article 
    > > 
    > > 
    > > 
    > >
    > > Consider this: mysqldump both tables into separate files, using
    > > options that produce a single insert statement per record. Sort
    > > both files. (at this point you've probably messed up the file to
    > > the point of not being able to restore it, but this doesn't matter).
    > > Run diff(1) between the two sorted files.- Hide quoted text -
    > >
    > > - Show quoted text -[/ref]
    > That's the problem I was hoping to avoid... We've got a T-1 between
    > the servers, and about 100meg of data. To download all the data would
    > take about 9 minutes, processing it would be quick, and updating would
    > be slow (about 30 minutes, as instead of syncing, if they differ, I
    > drop the entire table and re-install it). I wish there was an easier
    > (and faster) way...[/ref]


    ok, this is a long shot but i've heard that google has some nifty tools you may be
    able to use. take a look:

    http://code.google.com/p/google-mysql-tools/



    alternatively you could get creative and use something like this:

    mysqldump -u root -proot_pwd --where="idimg between 93305 and 93346"
    --insert-ignore mydb images | mysql -h master_host -u root -proot_pwd mydb

    you may want to ignore --where clause for your purposes.
    --
    POST BY: PHP News Reader
    lark Guest

  12. #12

    Default Re: Checksum Table producing different results?

    On May 24, 11:35 am, lark <net> wrote: [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]

    > > That's the problem I was hoping to avoid... We've got a T-1 between
    > > the servers, and about 100meg of data. To download all the data would
    > > take about 9 minutes, processing it would be quick, and updating would
    > > be slow (about 30 minutes, as instead of syncing, if they differ, I
    > > drop the entire table and re-install it). I wish there was an easier
    > > (and faster) way...[/ref]
    >
    > ok, this is a long shot but i've heard that google has some nifty tools you may be
    > able to use. take a look:
    >
    > http://code.google.com/p/google-mysql-tools/
    >
    > alternatively you could get creative and use something like this:
    >
    > mysqldump -u root -proot_pwd --where="idimg between 93305 and 93346"
    > --insert-ignore mydb images | mysql -h master_host -u root -proot_pwd mydb
    >
    > you may want to ignore --where clause for your purposes.
    > --
    > POST BY: PHP News Reader[/ref]
    Not a bad idea... The only problem with something like that is that
    we are using Windows 2003 servers on both ends... I'll check into
    google's tools... Thanks!

    ircmaxell Guest

  13. #13

    Default Re: Checksum Table producing different results?

    On May 24, 11:42 am, ircmaxell <com> wrote: 
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]





    >
    > Not a bad idea... The only problem with something like that is that
    > we are using Windows 2003 servers on both ends... I'll check into
    > google's tools... Thanks!- Hide quoted text -
    >
    > - Show quoted text -[/ref]

    Alright, I figured it out... Here's what I'm doing
    SELECT BIT_XOR(
    CONCAT( col1, col2, col3, col4, col...)
    )
    FROM table1
    GROUP BY NULL

    Since order doesn't matter in an XOR, different orderings will not
    matter. There are a few limitations. For example, the following two
    sets have the same XOR value
    col1 - col2
    1 - test1234
    2 - test4321

    AND
    col1 - col2
    1 - test4321
    2 - test1234

    It checks to see if the data is the same, not necessarally the
    ordering/paring of data. This should be fine for my needs, so I think
    I have my anser... (just need to check with the boss when he gets back
    from lunch).

    ircmaxell Guest

Similar Threads

  1. DataList output is producing a blank table?
    By Todd in forum ASP.NET General
    Replies: 3
    Last Post: May 8th, 04:55 PM
  2. SHOW TABLES and CHECKSUM TABLE statements
    By estebanjang@gmail.com in forum MySQL
    Replies: 5
    Last Post: October 28th, 05:01 PM
  3. Query results don't display properly in results table.IGNORE PREVIOUS
    By JoyRose in forum Coldfusion - Advanced Techniques
    Replies: 1
    Last Post: March 24th, 07:28 PM
  4. Query results don't display properly in results table.
    By JoyRose in forum Coldfusion - Advanced Techniques
    Replies: 0
    Last Post: March 24th, 07:17 PM
  5. Multiple-row table formatting of results
    By Daniel Ruscoe in forum PHP Development
    Replies: 1
    Last Post: November 16th, 05:56 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