List:General Discussion« Previous MessageNext Message »
From:Eric Bergen Date:December 8 2004 4:13am
Subject:Re: Replication update bug/error/problem.
View as plain text  
Jay,
  Are you using the replicate-do-db option on the slave? This option
relies on 'use' being set correctly when the query is issued. A quote
from the manual explains it better than I can:

"Tells the slave to restrict replication to statements where the
default database (that is, the one selected by USE) is db_name. To
specify more than one database, use this option multiple times, once
for each database. Note that this will not replicate cross-database
statements such as UPDATE some_db.some_table SET foo='bar' while
having selected a different database or no database"

URL:http://dev.mysql.com/doc/mysql/en/Replication_Options.html

Other possibilities are to use show slave status; and show master
status; to make sure queries are actually being sent from the master
to the slave.

-Eric


On Wed, 08 Dec 2004 00:57:42 +0100, Jay Ess <lists@stripped> wrote:
> I have a problem with an update query not replicating through to the slave.
> 
> The query is "update content_review_site as a,site_rating_factors as b set
> a.overall_rating = 77 where a.content_id=243"
> 
> Version : 4.0.22
> OS : Linux X86
> 
> How to replicate the error.
> 
> CREATE TABLE content_review_site (
>    content_id int(11) unsigned NOT NULL default '0',
>    site_id int(10) unsigned NOT NULL default '0',
>    overall_rating float(4,2) unsigned NOT NULL default '0.00',
>    rating_software_and_graphics tinyint(4) unsigned NOT NULL default '0',
>    rating_game_variety tinyint(4) unsigned NOT NULL default '0',
>    rating_maximum_bonus tinyint(4) unsigned NOT NULL default '0',
>    rating_bonus_match tinyint(4) unsigned NOT NULL default '0',
>    rating_wagering_requirements tinyint(4) unsigned NOT NULL default '0',
>    rating_payout_ratio tinyint(4) unsigned NOT NULL default '0',
>    rating_multiplayer_games tinyint(4) unsigned NOT NULL default '0',
>    rating_loyalty_bonus tinyint(4) unsigned NOT NULL default '0',
>    rating_deposit_withdrawal_options tinyint(4) unsigned NOT NULL default '0',
>    rating_support tinyint(4) unsigned NOT NULL default '0',
>    rating_progressive_jackpots tinyint(4) unsigned NOT NULL default '0',
>    rating_languages tinyint(4) unsigned NOT NULL default '0',
>    rating_reputation_fairness tinyint(4) unsigned NOT NULL default '0',
>    rating_free_bonus tinyint(4) unsigned NOT NULL default '0',
>    create_ts datetime NOT NULL default '0000-00-00 00:00:00',
>    modify_ts datetime NOT NULL default '0000-00-00 00:00:00',
>    PRIMARY KEY  (content_id,site_id)
> ) TYPE=MyISAM;
> 
> CREATE TABLE site_rating_factors (
>    site_id int(11) unsigned NOT NULL default '0',
>    factor_software_and_graphics tinyint(4) unsigned NOT NULL default '0',
>    factor_game_variety tinyint(4) unsigned NOT NULL default '0',
>    factor_bonus_amount tinyint(4) unsigned NOT NULL default '0',
>    factor_bonus_clauses tinyint(4) unsigned NOT NULL default '0',
>    factor_payout_ratio tinyint(4) unsigned NOT NULL default '0',
>    factor_multiplayer_games tinyint(4) unsigned NOT NULL default '0',
>    factor_loyalty_bonus tinyint(4) unsigned NOT NULL default '0',
>    factor_deposit_withdrawal_options tinyint(4) unsigned NOT NULL default '0',
>    factor_support tinyint(4) unsigned NOT NULL default '0',
>    factor_graphics tinyint(4) unsigned NOT NULL default '0',
>    factor_progressive_jackpots tinyint(4) unsigned NOT NULL default '0',
>    factor_languages tinyint(4) unsigned NOT NULL default '0',
>    factor_reputation_fairness tinyint(4) unsigned NOT NULL default '0',
>    factor_free_bonus tinyint(4) unsigned NOT NULL default '0',
>    create_ts datetime NOT NULL default '0000-00-00 00:00:00',
>    modify_ts datetime NOT NULL default '0000-00-00 00:00:00',
>    PRIMARY KEY  (site_id)
> ) TYPE=MyISAM;
> 
> insert into site_rating_factors values (1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,now(),now());
> insert into content_review_site values
> (243,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,now(),now());
> 
> On the slave get the data with "load data from master".
> Both slave and master shows :
> +------------+----------------+
> | content_id | overall_rating |
> +------------+----------------+
> |        243 |           1.00 |
> +------------+----------------+
> 
> update content_review_site set overall_rating = 666;
> updates the tables just fine on both slave and master.
> 
> But the troubling query below does NOT it only updates on the master.
> 
> update content_review_site as a,site_rating_factors as b set a.overall_rating =
> 77 where a.content_id=243;
> 
> Any suggestions?
> 
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:    http://lists.mysql.com/mysql?unsub=1
> 
> 


-- 
Eric Bergen
eric.bergen@stripped
http://www.bleated.com
Thread
Replication update bug/error/problem.Jay Ess8 Dec
  • Re: Replication update bug/error/problem.Eric Bergen8 Dec
    • Re: Replication update bug/error/problem.Jay Ess8 Dec
      • Re: Replication update bug/error/problem.Ware Adams9 Dec