3.2.2 SQL Editor Preferences

This section provides configuration options that affect the SQL Editor functionality in MySQL Workbench.

The SQL Editor preferences includes three additional sections: Query Editor, Object Editors, and SQL Execution.

Preferences: SQL Editor: Main

SQL Editor

Figure 3.6 Preferences: Main SQL Editor Section

Preferences: Main SQL Editor Section

  • Save snapshot of open editors on close: Enabling will save and reload the SQL Editor tabs after closing/opening MySQL Workbench (including after an unexpected crash).

  • Auto-save scripts interval: Frequency of the auto-saves.

  • Create new tabs as Query tabs instead of File: By default, opening a new SQL Editor tab opens as an SQL File tab. Check this option if you prefer the simpler Query tabs that, for example, will not prompt to be saved when closed.

  • Restore expanded state of the active schema objects: Group nodes that were previously expanded in the active schema when the SQL editor was last closed are re-expanded and loaded. This is enabled by default.

Sidebar

  • Show Schema Contents in Schema Tree: Enumerating, populating, and drawing large numbers of items can significantly increase loading times. For this reason, this facility can be switched off for models containing large numbers of schemata and tables.

  • Show Data Dictionaries and Internal Schemas: Whether to show data directories and internal schemas in the schema tree (such as INFORMATION_SCHEMA, mysql, and schemas starting with ".").

  • Combine Management Tools and Schema Tree tab: This affects the Object Browser in the left sidebar, and this option can also be toggled from the sidebar. The management tools and schema tree can be viewable as separate tabs, or as a single long list.

MySQL Session

  • DBMS connection keep-alive interval (in seconds): [600]. Time interval between sending keep-alive messages to the DBMS. Set to 0 to not send keep-alive messages.

  • DBMS connection read time out (in seconds): [600]. The maximum amount of time the query can take to return data from the DBMS.

Other

  • Internal Workbench Schema: [.mysqlworkbench]. This schema is used by MySQL Workbench to store information required for certain operations, such as saving shared SQL snippets.

  • [ ] : "Safe Updates", forbid UPDATE and DELETE queries to execute that lack a corresponding key in a WHERE clause, or lack a LIMIT clause. Setting this option requires a MySQL server reconnection.

    This makes it possible to catch UPDATE and DELETE stateents where keys are not used properly and that would probably accidentally change or delete a large number of rows.

Preferences: SQL Editor: Query Editor

Figure 3.7 Preferences: SQL Editor: Query Editor

Preferences: SQL Editor: Query Editor

Productivity

  • Enable Code Completion in Editors: The SQL Editor offers Auto-complete functionality by either pressing the keyboard shortcut (Modifier + Space), or it will start automatically if the Automatically Start Code Completion preference is enabled.

  • Automatically Start Code Completion: Enabled by default, this will automatically execute the code auto-completion feature while editing SQL in the SQL Editor. If disabled, you will instead use the keyboard shortcut Modifier + Space to execute the auto-completion routine.

  • Use UPPERCASE keywords on completion: Normally keywords are shown and inserted as they come from the code editor's configuration file. This setting will always write completed keywords as uppercase.

  • Comment type to use for comment shortcut: [--]. Defaults to "--", with "#" as another option.

  • Max syntax error count: Large complex scripts may contain errors. Further, a syntax error early on can lead to subsequent syntax errors. For these reasons, it is possible to limit the number of errors displayed using this option. The default is 100 error messages.

  • Max number of result sets: Maximum number of result sets for SQL queries that can be opened for a single SQL editor. Defaults to 50. Reaching the limit emits a warning.

    Note

    This option was added in MySQL Workbench 6.2.4.

SQL Beautifier

  • [ ] Change keywords to UPPER CASE: Enabled by default, executing the SQL beautifier will uppercase all SQL keywords.

Preferences: SQL Editor: Object Editors

Figure 3.8 Preferences: SQL Editor: Object Editors

Preferences: SQL Editor: Object Editors

Online DDL

  • Default algorithm for ALTER table: The default algorithm selected when performing ALTER TABLE operations in MySQL Workbench. The setting can also be adjusted for each ALTER TABLE operation. Options include "In-Place" (preferred) and "Copy", see the online DDL documentation for more information.

  • Default lock for ALTER table: The default lock setting for allowing concurrent queries with ALTER TABLE in MySQL Workbench. This setting can also be adjusted for each ALTER TABLE operation. Options include "None", "Shared", and "Exclusive", see the online DDL documentation for more information.

Views

  • Reformat DDL for Views: Whether to automatically reformat the View DDL that is returned by the MySQL Server.

    Note

    The MySQL Server does not store the formatting information for View definitions.

Preferences: SQL Editor: SQL Execution

Figure 3.9 Preferences: SQL Editor: SQL Execution

Preferences: SQL Editor: SQL Execution

General

  • Max query length to store in history (in bytes): Queries that exceed this size will not be saved in the history when executed. The default is 65536 bytes, and setting to 0 means there is no limit (all queries will be saved).

  • Continue on SQL Script Error: Should an error occur while executing a script, this option causes execution to continue for the remainder of the script.

  • Leave autocommit mode enabled by default: Toggles the default autocommit mode for connections. When enabled, each statement will be committed immediately.

    Note

    All query tabs in the same connection share the same transaction. To have independent transactions, you must open a new connection.

  • Progress status update interval: When executing long running queries over a slow connection, you may need to increase this value to prevent excess load on the connection. Defaults to 500 milliseconds.

SELECT Query Results

  • Limit Rows: Queries can sometimes return an excessive number of rows, which can heavily load the connection, and take time to display in MySQL Workbench. To prevent this, you can set a more moderate value here. This limit is defined by the Limit Rows Count option.

  • Limit Rows Count: Specify the maximum number of result rows to return. Defaults to 1000.

  • Max. Field Value Length to Display: To avoid display problems due to excessive field length, it is possible to set the maximum field length to display (in bytes). Defaults to 256.

  • Treat BINARY/VARBINARY as non-binary character string: Binary byte string values are not displayed by default in the results grid, but are instead marked as BLOB values. These can then be viewed or edited with the BLOB editor. Nonbinary character string values are displayed in the results grid, and can be edited in the grid cell or using the BLOB editor.

    If this option is turned on, data truncation may result: Binary byte string values may contain null bytes as part of their valid data, whereas for nonbinary character strings, a null byte terminates the string.

  • Confirm Data Changes: In the SQL Editor, if you edit table data and then click the Applying changes to data button, MySQL Workbench launches a wizard to step you through applying your changes. This gives you a chance to review the SQL that will be applied to the live server to make the requested changes. If this option is deselected, the changes will be applied to the server without the wizard being displayed and without giving you a chance to review the changes that will be made.