Tag Info

New answers tagged

2

Sorry for waking up this dead thread, but I hate it when questions remains inconclusive. Anyway, I had the same problem. Apparently SQL server replication saves foreign keys in "dbo.MSsavedforeignkeys", which is where I found my blocker FK. A quick delete dbo.MSsavedforeignkeys where constraint_name = N'FK_TableOnlyInDestDB_MyReplicatedTable' solved my ...


0

I had answered a question back in January 2013 concerning the thread state "System Lock" : What does 'system lock' mean in mysql profiling a LOAD DATA INFILE statement? Here is what could be happening, please follow along In my old post, I brought up the following The thread is going to request or is waiting for an internal or external system ...


0

According to Publish Data and Database Objects in the section Considerations for Publishing - Limitations on Publishing Objects: XML schema collections can be replicated but changes are not replicated after the initial snapshot. I agree, this appears to be a deficiency. As a workaround, you could perform the XML schema update on the Publisher and ...


0

The answer to my question comes from an article I found this afternoon and I completely understand what I was doing wrong before. http://demarcsek92.blogspot.com/2014/05/mongodb-ssl-setup.html I'll explain a little more because of the suggestion from Markus. Originally I was generating client and server key/certification pairs from a root CA that I had ...


3

Instead of doing TRUNCATE TABLE (which locks up any connections accessing the table), try making an empty copy of the table, swapping it in, and dropping the old table. EXAMPLE Suppose the table is called mydb.mytable. Do it like this USE mydb CREATE TABLE mytable_new LIKE mytable; ALTER TABLE mytable RENAME mytable_old; ALTER TABLE mytable_new RENAME ...


2

Although TRUNCATE TABLE is definitely faster than DELETE FROM I would stick to deleting the records in small chunks. The TRUNCATE TABLE sometimes can be still slow because a lot of stuff is going on behind scenes: it has to grab exclusive lock on the dictionary, it still has to delete ibd file and re-create one, it has to evict pages from the buffer pool. ...


2

Use TRUNCATE TABLE (that will empty the table in the fastest way possible, by droping it and recreate it in a non-rollable-back way. If that takes too much time for you (can happen in older versions of mysql using innodb_file_per_table), you can run it independently on master and each slave with SET sql_log_bin = 0; The underlying bug is probably this ...


0

Although replication may not be High Availability in design, it depends on your definition of HA. Certainly it has been used for HA by many people. If replication is down long enough it can be marked as Inactive. To automatically reactivate a replication, you could try using Kin's response: SQL Server replication subscriptions marked as inactive This ...


1

What you're seeing is completely unacceptable, and developers should not be expected to work around it. You've suppressed it in your answer, and I don't know if you didn't notice it, or didn't consider it significant, or something is broken in your setup. Here's what you should have seen: mysql> insert into t1 values (uuid_short()); Query OK, 1 row ...


0

There are a variety of ways you can do this. If you only need a portion of the database, you can have copies of the tables which you refresh on the destinations periodically. The idea is that you have your users reading one copy of the data, then you have an identical table in a different schema, and you reload that copy while the users are reading. Then in ...


2

I've been looking into reclaiming disk space this morning. I'd like to add that as of version 2.6 Mongo permits use of repairDatabase on the secondaries. So the process of recovery is to simply execute db.repairDatabase() on the offending databases.


0

Here are some disadvantages For you MyISAM users out there, concurrent inserts are prohibited. Data changes cannot be read unless you use mysqlbinlog with the options --base64-output=DECODE-ROWS and --verbose. BLOB data is written "as is" rather than the SQL that created the BLOB For more information, see the MySQL Documentation


5

You could also solve this with a trigger that populates the copy of the table on insert/update/delete. It wasn't clear in the question that these tables are actually on different servers, and that the subscriber was unreliable. In that case you could simply log ship to the subscriber - you can get pretty close to real time here, though you will have to kick ...


1

Transaction replication would be the solution I would choose for this. More info about transaction replication can be found here: http://msdn.microsoft.com/en-us/library/ms151176.aspx To only choose the columns needed, you need to use a column filter: http://msdn.microsoft.com/en-us/library/ms151775.aspx AlwaysOn availability groups would not work ...


0

It is possible that current replication thread is taking a lot of time updating a (big) change to a (big) table causing a long lock on the table. You may want to examine what the sql thread is trying to execute using mysqlbinlog with --start-position parameter. Hope this helps.


0

Check this : Link That is Below one : Can't you use Triggers? 22.5.5: Is it possible for a trigger to update tables on a remote server? Yes. A table on a remote server could be updated using the FEDERATED storage engine. From : http://dev.mysql.com/doc/refman/5.0/en/faqs-triggers.html#qandaitem-22-5-1-5 I wouldn't recommend this because you ...


0

How I would do it, without having to reload all data: Take the slave out of production- it has drifted and has wrong data Skip replication errors until the replication is running again (but remember, with wrong data) with slave-skip-errors. Not always possible. Use pt-table-checksum to identify the master-slave differences Use pt-table-sync on the ...


2

The safest approach is to stay far, far away from trying to filter replication... and tweaking sql_log_bin on and off is a recipe for replication problems, too. In a replicated environment, the general idea is that both servers have identical data sets, and to whatever extent they don't, your setup becomes exponentially more complex. There is an easier and ...


0

You can disable binary log in a session and then create the table, use it and the drop: SET sql_log_bin = 0; CREATE TABLE t(...); ... DROP TABLE t;


0

Replication filtering isn't bulletproof. Due to how the filtering is implemented the events responsible for your errors are being generated because the default database at query runtime is the my-database schema as expected and the query being executed is fully qualified INSERT INTO phpmyadmin.pma_column_info ... PZ explains the scenario well in this post; ...


0

You asked the same question on Reddit. Here is a copy&paste of my answer there (without the self-promotion link): Replication is tricky. This is not enough space to tell you everything that can be problematic with it and what you should be aware of, but in summary it is: Asynchronous Single-master Statement-based No automatic consistency checking ...


3

Since replication isn't normally used on slave even though it's running That statement doesn't quite make sense. In master/master, both servers are each other's master and slave and there is no real distinction between them -- only an administrative distinction, as you seem to understand. If set up correctly, then yes, SET GLOBAL READ_ONLY = 0; on the ...



Top 50 recent answers are included