New answers tagged

0

I don't know the rationale, but it is a problem that has bitten many users. It is not just DDL statements, but also DML: replicate_do_db = A or binlog_do_db = A or ... (several syntaxes possible) USE B; INSERT INTO A ...; Won't perform the INSERT.


0

OK, I found the issue. I hadn't properly configured passwordless ssh login between my master and slave. So despite being able to login into my master from my slave the issue was it couldn't copy files because ssh wold require a password. My only issue is error reporting is horrible for repmgr. Following this guide I missed a very small step. Always make ...


2

Yes, you can replicate from Enterprise edition to Standard edition. The only caveat is Peer-to-Peer Replication requires all nodes to be Enterprise Edition, and Web and Express editions are Subscriber only. Features Supported by the Editions of SQL Server - Replication


0

Create the server and client cert/key files on the master server and then copy only the client cert/key files to each slave server and configure SSL replication. So basically you have the same client cert and key on all slave servers. If a slave gets compromised change the replication user's credentials.


2

An alter procedure would not normally be replicated and so would not cause any problems. More than likely you're doing DDL operations and changing the tables and this is breaking the replication. Michael Keleher has suggested your replication may not be set up to replicate schema changes: Open the properties of the publication, go to the Subscription ...


0

What you are looking for is called Transactional Replication, which is very commonly used and a good solution for avoiding contention on a transactional database. There are three databases in a transactional replication system, which are the publisher, the distributor and the subscriber. The publisher is the source database, the distributor is where all ...


1

Do not see the problem. When somebody enters the email address a second time then this happens. Nothing to do with replication. This is what the unique key is used for. If this error would break the replication then I do not understand how replication could ever work. Every user could easily break the replication by, in this case, entering an email address ...


1

Solution for restoring a published database We faced a similar problem: A published database is stored on Server1. Every day this database will be backed up and restored on Server2. We frequently got error Messages "LOG full due to REPLICATION". log_reuse_wait_desc was set to REPLICATION. Replication could not be removed, because this database was not ...


0

Your code exits the loop on the first exception. Election of the new primary is not instantaneous. I think you would have to move the try catch into the loop but then you face the problem that you have to keep track of the refused records. My approach would be no for loop but taking one document off of the stack, try, wait on fail, retry, etc.


0

Slave_SQL_Running_State: Reading event... Suggests that it's working on a large event. If you have MIXED or Row based binary logging and have changed a large amount of data on the master you might end up with a large event or series of large events. Use the mysqlbinlog tool to see what's going on with your relaylog. The master might show a concentration of ...


0

In Data Warehousing, "Summary tables" are very important for performance. A Summary table contains counts and subtotals for each day (or other time period). "Reports", such as your query are then run using a summary table, and can run much faster than running against the 'raw' data. For your case, build and incrementally maintain a table with, say: day, ...


0

Give this a try: Add this composite index to user_activity: INDEX(user_id, action). Then SELECT SUM(x.ct), u.user_type, x.action FROM ( SELECT user_id, action, COUNT(*) AS ct FROM user_activity GROUP BY user_id, action ) x JOIN user u ON x.user_id = u.id GROUP BY x.action, u.user_type;


0

When seeking a dump which provides binlog position you will instruct MySQL to call Flush Tables With Read Lock. The acquisition this lock can block. This where you could impact the system. If you have long running transactions you may see problems but you can check this before starting the dump. Your command looks fine to me.


0

Replicating from a newer version (5.7) to an older version (5.1) is neither supported, nor likely to work. The other direction 'works'.


0

binlog_do_db = dbname says to replicate any statement when the default database is dbname. So be careful what USE statement precedes the statement: USE non_repl_db; CREATE TABLE ...;


0

First, examine what the distribution agent saved about the error by executing select * from [dbo].[MSrepl_errors] order by id desc in the distribution database. When it comes to poor performance in the distribution database, I've experienced several problems. Besides excessive blocking, which I'm sure you are watching, there are several maintenance related ...


0

Most likely problem: there is an error in the relay log. Usually this is the result of a sudden database shutdown (e.g. server crashed). The fix for this is to simply tell the database to discard the current relay logs, and start again. To do this: SHOW SLAVE STATUS; and note down: relay_master_log_file exec_master_log_pos then run: STOP SLAVE; ...


0

As shown by SHOW SLAVE STATUS\G it seems Master Host is not known to Slave, Slave_IO_State: Waiting for master to send event **Master_Host: ...** Use this for Change Master, CHANGE MASTER TO MASTER_HOST='12.34.56.789',MASTER_USER='slave_user', MASTER_PASSWORD='password', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS= 107; Refer ...


0

--STOP the Distribution Agent: sp_MSstopdistribution_agent @publisher, @publisher_db, @publication, @subscriber, @subscriber_db --START the Distribution Agent: sp_MSstartdistribution_agent @publisher, @publisher_db, @publication, @subscriber, @subscriber_db</pre> Example: exec distribution.dbo.sp_MSstartdistribution_agent @publisher = '...


-1

If the node can find no free nodes that show as SYNCED, the joining node reports: Requesting state transfer failed: -11(Resource temporarily unavailable). Will keep retrying every 1 second(s). The joining node continues to retry the state transfer request. refer this http://galeracluster.com/documentation-webpages/error.html


0

Actually the problem was solved without modifying the way the CMS handles the database. The real problem : The issue was with the MariaDB server configuration, where the Replicate_Ignore_Table option did not worked : (root@server2) [moodle]> SHOW SLAVE STATUS; [...] Replicate_Ignore_Table: moodle.mdl_temp_%,moodle.mdl_backup_ids_temp,moodle....


0

If you open the properties of the publication, then select the Subscription Options section, you will see a property named Replicate Schema Changes. Set that to true and any schema change will be replicated and display in the Distributor to Subscriber History in Replication Monitor as DDL change. No need to stop and restart any agents. I have found that you ...


0

You can use Linked Server for SQL Server side. Creata a stored procedure to check online mysql tables data. If product sold decrease the local product count on the Local SQL Server. Why do you cover this problem on the application side using transaction ?


1

I believe whenever transactional replication is initialized, the Publisher snapshots the articles in the publication and pushes it across to the subscriber. The remote Distributor and pull subscriber are used for the transactions after replication has been setup and initialized.


0

Plan A: Include the index(es) in the CREATE TABLE statement. Plan B: Use this to add an index: ALTER TABLE moodle.mdl_temp_stats_daily ADD INDEX(courseid); (I have not tested either.)


2

Set up slave You can set it up from the nightly backups. That's actually a common practice to provision slaves from backups to also have them tested. You can go back as far as you have binlogs stored on the master (expire_log_days variable). The impact of having a slave starting from older backup is that it has to fetch the logs from the master but that ...


1

In the answer you linked to, Craig says: It's possibly practical to do it with minor changes to PostgreSQL( if all the servers are read-only, but I haven't investigated it as it's a pretty uninteresting use-case. (emphasis mine). The problem is, there is no way to start a PostgreSQL server and tell it "you are only allowed to look at this data ...


0

yes, it will work. generally it is recommended that slaves be the same or of higher version than its master.


0

You can use Talend Open Studio for this transformation process. Here is an example -- Talend ETL to migrate data from MongoDB to Postgres After that build your Talend ETL Job. Deploy the jar file to SpagoBI Server. Again here is the idea of how to --Talend ETL Job deployment guidelines Then Schedule your Job in SpagoBI Server UI admin console on a certain ...


0

Master-Master has a lot of problems if you are writing to both. It is better to write to only one of them until you need to failover. Setting up Dual-Master is simply setting up both servers as being both Master and Slave, much like you already did to make your current Master-Slave topology.


7

A solution that will work with SQL Express would be ideal, SQL Standard is fine, but I dont want to have to upgrade to SQL Enterprise. If you want something straight out of the box, that's going to be difficult to find. However, 2016 does bring Standard Edition Availability Groups. Standard Edition before 2016 does have Mirroring. Additionally if you'...


0

Answers to A few things You didn't understand: 1)How does it have a duplicate key, when I just pulled in a fresh dump? Issue here is that you added dump with latest data but slave is reading from a previous position on the log as mentioned by you in your answer , so there is a duplicate entry for that. 2)why is it loading from mysql-bin.000001 when the ...


0

When you do a failover, a role reversal happens i.e. your primary becomes secondary and your secondary becomes primary. ALTER DATABASE [yourDatabase] SET PARTNER FAILOVER GO Doing so, only the role reversal happens (this is what happens when you initiate a failover using above command) and mirroring direction is reversed - principal becomes mirror and ...


2

In a Galera cluster, any node can play the part of a Master node. Such a node can execute DML (INSERTs, UPDATEs, and DELETEs). If DML statements are spread across multiple Master nodes and are targeting the same tables, you must expect log conflicts. Why ? Galera wsrep libraries were designed to commit DML changes if all nodes in a cluster are ready to do ...


0

In this context, I usually create a specific table to see the last replication status. On your PostgreSql master server, you create a simple table, example: CREATE TABLE check_replication ( now_date timestamp ); Then you create a batch or job, that performs every minute, or more often: INSERT INTO check_replication ( now_date ) VALUES (now()); Then, ...


1

This should not a problem, provided you do not execute any DDL related to the Storage Engine on the Master. I have written about this many times Jun 01, 2011 : What can we do in MySQL 5.0 Replication to address bandwidth concerns? Nov 16, 2011 : Log MySQL DB changing queries and users Mar 13, 2012 : http://dba.stackexchange.com/questions/14943/setting-up-...


0

I would use Visual Studio's Schema Comparison tool, or some third party equivalent; it will visually show you what's different and optionally create a script to sync them. Just beware that depending on the change, it may try to copy the table to a work table and then drop the source and rename the new table, which will fail if the table is replicated.


0

What is the best way of achieving this? The best way is to script out replication and edit the scripts. exec sp_addarticle ... ,@destination_table = N'Your_Subscriber_TableName' , @destination_owner = N'NewSchema' Then run the snapshot agent and the log reader agent to sync data.


2

This information may not be of interest to the DBA. If the application is handling conflicts correctly, there is no need to log them. It would be expected behaviour. You would only enable this level of logging if you were debugging a specific issue. Otherwise it would generate a lot of spam in the logs.


3

A fast refresh would copy incremental changes over the network but requires that a materialized view log be created on the master site on the source table. That adds some overhead to the inserts happening on the master table but would generally make the refresh more efficient. A complete refresh would copy every row over the network every time the ...



Top 50 recent answers are included