Bug #108902 Auto-generated queries include database name, potentially breaking replication
Submitted: 27 Oct 2022 16:10 Modified: 28 Oct 2022 4:18
Reporter: Mark Bench Email Updates:
Status: Analyzing Impact on me:
Category:MySQL Workbench: SQL Editor Severity:S2 (Serious)
Version:8.0.21 OS:Windows (Microsoft Windows 10 Pro)
Assigned to: MySQL Verification Team CPU Architecture:Any
Tags: WBBugReporter

[27 Oct 2022 16:10] Mark Bench
----[For better reports, please attach the log file after submitting. You can find it in C:\Users\Benjamin\AppData\Roaming\MySQL\Workbench\log\wb.log]

When applying data edits in the results grid, the auto-generated update script includes the database name in the query.  This breaks replication if "replicate-rewrite-db" is being used to rewrite the master's database name to a different name on the replica.

How to repeat:
Set up a master with a database name, e.g. "master".

Set up a replica with a different name, e.g. "replica".

Use "replicate-rewrite-db" to map from the master's database name to a different name on the replica, e.g. replicate-rewrite-db="master->replica"

Perform a query on the master, e.g. "SELECT * from test_table"

Manually modify a value in the result grid and click "Apply."

When the confirmation screen appears, note that the auto-generated query includes the database name, e.g. UPDATE `master`.`test_table` SET `test_col` = '1' WHERE (`id` = '1');

Click "Apply" on the confirmation screen.

When the query makes its way to the replica, the replica will error because the rewriting-db doesn't apply within a query.

Suggested fix:
After the default database schema has been selected, either by configuring the connection or selecting after the connection has been opened, leave the default schema's name out of auto-generated queries.  

If "master" had been selected as the default database schema, the new query would be, e.g.: UPDATE `test_table` SET `test_col` = '1' WHERE (`id` = '1');

At the very least there should be a configuration option, so those who use a replicated database can turn off the database name appending.