Bug #75140 mysql workbench restore database not log inserts

• Submitted: 8 Dec 2014 12:57
• Modified: 5 Feb 2018 5:28
• Reporter: David Hodgson (OCA)
• Status: Verified
• Category: MySQL Workbench: Administration
• Severity: S4 (Feature request)
• Version: 6.3.10
• OS: Any
• CPU Architecture: Any

[8 Dec 2014 12:57] David Hodgson

Description:
Enhance MySQL workbench to have an options to not log the data inserts when restoring a database.

This makes the inserts run faster, and the logging in non-master-slave situations is not helpful - the recovery from a failure would be to re-do the restore rather than try & recover the table from the log!

How to repeat:
Run Data Export then run Data Import with general query logging and bin logging active.

Suggested fix:
The option, if set, should:

SET @save_sql_log_bin = @@session.sql_log_bin ;
SET @save_sql_log_off = @@session.sql_log_off ;
SET @@session.sql_log_bin = @save_sql_log_bin;

at the start of the script (the SET @@session... is so it crashes if the user doesn't have the appropriate authorities), then:

SET @@session.sql_log_bin = 0;
SET @@session.sql_log_off = 1;

before each set of inserts after the table is created, and:

SET @@session.sql_log_off = @save_sql_log_off; 
SET @@session.sql_log_bin = @save_sql_log_bin; 

after the last inserts for the table.

[8 Dec 2014 13:14] David Hodgson

If you wanted to be more fancy, i.e. give an option at restore time rather than backup time, you could also add a flag to the start of the generated SQL which controls switching off the logs, and then if you wanted to restore with logging you could change the flag to False and get all the logs you want.

[5 Feb 2018 5:28] MySQL Verification Team

Hello David,

Thank you for the feature request.

Thanks,
Umesh