Prevent Database Name in Generated Queries

First, I love this program. It's made my life so so much better so thank you.

I'm attempting to use the MySQL option replicate-rewrite-db so that queries from my Master DB are translated to a different DB on my Slave.

It's pretty straightforward except that MySQL, for better or worse, won't translate queries that include an explicit database definition.

This will replicate:

UPDATE table SET column = value;

But this will break replication

UPDATE db.table SET column = value;

My application's SQL queries never explicitly include a database, but I noticed that when I went to insert or edit a row in HeidiSQL, that the DB name was automatically added to the table names, thus breaking my replication.

Of course I could just manually write the queries, but I do enjoy using the Editor. I'm not certain of all the ramifications for changing this behavior but would it be possible to have an option that would leave the DB name off?

Thanks again for all your work!

Most things in HeidiSQL have a reason for why they are that way :) But I can test leaving the database away from UPDATE/INSERT queries. Only such things must be done with highest caution, as they probably break other things (again) of which I am not aware of currently.

I'm voting for NOT changing the current behavior! Here is my real life scenario: You have two identical databases - one for production and one for development. Furthermore, you have a SQL script which has the first statement "use myDB_prod;" to ensure that it is manipulating only the productive environment. Let's assume you have opened one of the dev. tables to edit a cell of a specic dev. table. If you do this right after running the prod. script from the query tab, you would change the prod table because the table names in dev. and prod. are identical and because of the first command in the prod script... :-(( (the HeidiSQL database focus changed because of the script execution) IMHO this might be really dangerous...

LutzT that is an excellent example. I can see that this behavior would be dangerous. I thought I would ask as I am in a fairly unique situation and certainly a temporary one, as my goal is to deprecate the MySQL server that uses the replicate-rewrite-db option.

I was mainly curious to see if someone could think of a way around this issue, but the best solution is probably the simplest: just stop replicating to a DB with a different name.

Thanks for the responses and thank you ansgar for HeidiSQL!.

Hi Fishbreath49, in case you're working in Unix/ Linux environment: Maybe you could use 'sed' to modify the data stream on the fly while replicating... Might be tricky, but possible...? Regards, L.

It's a Windows Server environment, but you can get 'sed' for Windows these days. That's actually not a bad idea.

I'm thinking for simplicity sake I might just go with a batch script that does a mysqldump and uses 'sed' to alter the queries. Then just dump them to the database every few minutes. The pertinent tables aren't very big and again I fully plan to deprecate and shut down this slave as soon as I am able to wean folks off of using it (it was a legacy system that sat for 10 years without being upgraded, lots of systems reference it).

Thanks for the help, LutzT! Very much appreciated.