New answers tagged

1

While MS releases a patch (not sure if will ever do), this is a quick fix: Get and save the source code of this procedure [distribution].[sys].[sp_MSdetect_nonlogged_shutdown] Edit the source and find the variable @message, change its length to 4000. Then find the column message in the temp table #JobHistory and do the same Start SQL Server in Single mode ...


0

I found the problem, it's due to the inactivity on the database. Since there are no changes in the database outside office hours there are no changes to be consumed by the replica e.g. the Fivetran connector, therefore the LSN of the WAL is not advancing and the replication lag increases. It seems that others had the same problem and came up with a solution ...


0

This is what max_slot_wal_keep_size was implemented for in PostgreSQL v13. It sacrifices the replica when it demands too much WAL be retained on the master. I have never used it on RDS, but I do see it is available for users to set via the RDS control panel, so presumably it works.


0

I'm not exactly sure of the reason for this, but after a while I noticed that my IDE would run a USE database; statement before each set of ALTERs was executed. I tried this on the command line and it worked.


1

In the trigger, change IF NEW.delivery_date = "" to IF NEW.delivery_date < '1971-01-01' That will catch empty string and '1970-01-01' and any timezone variation on that "zero" date. It will fail if it is already NULL`, but that won't change the action of the Trigger. (In the future, be sure to include any relevant Triggers in the ...


0

I've done some tests and it looks like that restoring a mysqldump backup on the primary using mysql will get written into the binary log correctly and the changes automatically applied to the replicas. i.e. mysql -u root -p < db-backup.sql will work fine!


0

I just fixed a similar issue for a server that was replicating to Azure SQL. I had to script out, drop and re-create the replication. Unfortunately, only the on-prem subscribers caught up with transactions and azure SQL subscribers threw the error, The process could not connect to the Subscriber. Just right-click on the subscriber name from replication ...


0

Yes, finally found it. Actually, some SQL order programmatically disabled replication using following PHP order : $db->exec('SET sql_log_bin=OFF'). I don't know why it was there and it took us a lot of time to understand where it came from, but we finally did it. I wish nobody experience this, but if it happens, maybe this comment will help


0

The solution that will truly allow no replication lag is an OSC tool like pt-online-schema-change or gh-ost. You can read and write to the table while the ALTER is running either on the source or replica instances. They run the ALTER concurrently on the source and replica instances. That is, the ALTER starts on the replica soon after it starts on the source;...


0

Newer versions of MySQL have a way to have multiple slave execution threads. But, beware, an ALTER being executed by one thread will block other treads from doing some actions with that table. The latest version allows some flavors of ADD COLUMN and ADD/DROP INDEX are virtually non-blocking. And they run faster, so they are clogging the replication stream ...


0

It sounds like you want to drop the merge subscription, if so there is a system SP you can use. https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-dropmergesubscription-transact-sql?view=sql-server-ver15


3

You should look at the MariaDB error log. The authoritative documentation on this is found in the MariaDB Knowledgebase: Error Log. If you're using Ubuntu, then I assume you have systemd, which means you can also see the error log with: journalctl -u mariadb.service ... although I personally don't think this is as good/nice as MariaDB's own error log file. ...


0

I don't have access right now to a replicated instance to verify but I believe the system tables and DMVs should help. sys.dm_repl_schemas "Returns information about table columns published by replication." Those will be the ones currently in the published article. sys.columns will list all the columns in the source table. (You can join to sys....


0

There are many reason when mixed binary log format the statement-based replication is converted to row-based replication. Here, I think it matches to your case : When one or more tables with AUTO_INCREMENT columns are updated and a trigger or stored function is invoked. Like all other unsafe statements, this generates a warning if binlog_format = STATEMENT. ...


0

Jesus, I finally got it. So, in short, the problem was the server id, which was the same as the one from the old master, before reinstall. When I prepared the reinstall, I made a snapshot on the master that will be remaining live, redirecting all traffic to the master that will be reinstalled. So queries with the server id of the master that would be ...


0

Replica set should have ONLY one arbiter and ONLY when there is an even number of data-bearing nodes. Your replica set has already an odd number of data-bearing nodes (m1, m2, and m3), so there should not be any arbiters. Those error messages means: a1 - rs.conf() don't have that a1 node in the list of that replica sets' config, it may have wrong IP or port-...


Top 50 recent answers are included