Chapter 3 Configuration
Prev   Next

Chapter 3 Configuration

Table of Contents

3.1 User Accessibility Options
3.2 Workbench Preferences
3.2.1 General Editors Preferences
3.2.2 SQL Editor Preferences
3.2.3 Administration Preferences
3.2.4 Modeling Preferences
3.2.5 Fonts and Colors Preferences
3.2.6 Other Preferences
3.3 MySQL Workbench Settings and Log Files
3.4 Tutorial: Add a Custom Link to the Home Page

3.1 User Accessibility Options

MySQL Workbench includes methods to improve user accessibility.

Fonts

Under Preferences, modeling fonts are adjustable from the Appearance section of the Modeling menu:

Figure 3.1 Appearance Preferences

Appearance Preferences

Choose the character set under the Configure Fonts For setting (typically leave the default setting here) and then adjust the model fonts to fit your needs.

The font types and sizes for other GUI elements are set under the Fonts & Colors tab:

Figure 3.2 Fonts & Color Preferences

Fonts & Color Preferences

Note

Font changes require a refresh or restart before they take effect.

The following image shows the SQL Editor after changing the Editor font size from 10 to 30:

Figure 3.3 SQL Editor with Font size 30

SQL Editor with Font size 30

Color Presets

Here you define the colors used in EER diagrams for the tables, views, layers, and notes. You can edit or add additional color choices by entering their ASCII values.

Theming

On Windows, the Fonts & Colors preference tab also includes a "Color Scheme" configuration section. From here, you can enable the High Contrast color theme. This theme preference affects the MySQL Workbench GUI.

Figure 3.4 High Contrast Preference

High Contrast Preference

Microsoft Active Accessibility (MSAA)

On Windows, MySQL Workbench supports MSAA, which allows use of screen reader applications with MySQL Workbench.

3.2 Workbench Preferences

3.2.1 General Editors Preferences
3.2.2 SQL Editor Preferences
3.2.3 Administration Preferences
3.2.4 Modeling Preferences
3.2.5 Fonts and Colors Preferences
3.2.6 Other Preferences

Use the Preferences menu to configure MySQL Workbench to your specific needs. This menu is divided sections, as described below:

  • General Editors: General-purpose editor options, such as SQL parsing options.

  • SQL Editor: SQL editor related preferences that also includes subsections for the Query Editor, Object Editor, and SQL Execution.

  • Administration: Tools used by the Administrator functionality.

  • Modeling: Model related preferences that also includes subsections for Defaults, MySQL (MySQL specific settings), Diagram (EER), and Appearance (model colors and fonts).

  • Fonts & Colors: Change fonts for tools such as the SQL editor and results grid.

  • Others: Miscellaneous options.

A more detailed discussion of these options follows.

3.2.1 General Editors Preferences

The General Editors preferences section:

Figure 3.5 Preferences: General Editors

Preferences: General Editors

SQL Parsing in Code Editors

SQL properties that can be set include the SQL_MODE, case sensitivity of identifiers, and the SQL delimiter used.

  • Default SQL_MODE for syntax checker: Optionally configure the SQL_MODE for the SQL editor's SQL syntax checker.

    The document property SqlMode defines SQL_MODE for all operations affecting SQL parsing at the document scope. The purpose of this option is to preserve the consistency of SQL statements within the document.

    The property has the following functions:

    • Sets the SQL_MODE DBMS session variable to the value stored in the SqlMode property of the document when performing reverse engineering, forward engineering, or synchronization operations.

    • Honors the SQL_MODE values defined in SqlMode so that SQL parsing is correct.

    Only a subset of all possible SQL_MODE values affect the MySQL Workbench SQL parser. These values are: ANSI_QUOTES, HIGH_NOT_PRECEDENCE, IGNORE_SPACE, NO_BACKSLASH_ESCAPES, PIPES_AS_CONCAT. Other values do not affect the MySQL Workbench SQL parser and are ignored.

    If the value of SqlMode is not set, the default value of the SQL_MODE session variable defined by the server stays unchanged during operations with the server. However, the MySQL Workbench SQL parser behaves as if SQL_MODE is also not set. This may potentially lead to inconsistencies in parsing of SQL statements stored in the document. If you choose to not set the SqlMode property, ensure that the default SQL_MODE variable defined by the server does not contain any values from the following list: ANSI_QUOTES, HIGH_NOT_PRECEDENCE, IGNORE_SPACE, NO_BACKSLASH_ESCAPES, PIPES_AS_CONCAT.

    The SqlMode property is defined in two locations: globally and at document scope. MySQL Workbench uses the global property to initialize the document property for each new document created. For each document, the property value defined at document scope always has higher priority over the one defined globally.

  • [ ] SQL Identifiers are Case Sensitive: Whether to treat identifiers separately if their names differ only in letter case. This is enabled by default.

  • Non-Standard SQL Delimiter: [$$]. Define the SQL statement delimiter to be different from the normally used delimiter (such as ";"). Change this if the deliminator you normally use, specifically in stored routines, happens to be the current setting.

Indentation

Note

This preference section was added in MySQL Workbench 6.2.4.

  • [ ] Tab key inserts spaces instead of tabs

  • Indent width: [4] The number of spaces inserted after pressing Tab -- this assumes that the Tab key inserts spaces instead of tabs option is enabled

  • Tab width: [4] The width (number of spaces) that tab characters are displayed as in MySQL Workbench

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. Set 0 to not check the read time out.

  • DBMS connection time out (in seconds): [60]. Maximum time to wait before a connection attempt is aborted.

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 statements 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.

3.2.3 Administration Preferences

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

Figure 3.10 Preferences: Administration

Preferences: Administration

Data Export and Import

  • Path to mysqldump tool: Path to your local mysqldump binary. Leave it blank to use the bundled mysqldump binary.

  • Path to mysql tool: Path to your local mysql client binary. Leave it blank to use the bundled mysql binary.

  • Export Directory Path: Directory where your exported mysql dumps are located.

3.2.4 Modeling Preferences

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

Preferences: Modeling: Main

Figure 3.11 Preferences: Modeling

Preferences: Modeling

EER Modeler

  • Automatically reopen previous model at start: Check this if you want the model on which you previously worked to be automatically reopened when you start MySQL Workbench.

  • Force use of software based rendering for EER diagrams: MySQL Workbench will use OpenGL for rendering when available. However, due to faulty drivers, problems do occasionally occur. These issues can be resolved by selecting the software rendering option here.

  • Model undo history size: You can limit the size of the undo history here. Set this value to 0 to have an unlimited undo history.

  • Auto-save model interval: An open model that has not been saved will automatically be saved after this period. On loading a model file, MySQL Workbench will notify the user if the file was not previously saved correctly, due to a crash or power failure. MySQL Workbench can then attempt to recover the last auto-saved version. For automatic recovery to be available for a new file, it will have to have been saved at least once by the user.

Preferences: Modeling: Defaults

Sets default values for modeling object names.

Figure 3.12 Preferences: Modeling: Defaults

Preferences: Modeling: Defaults

The following tables show the object names and their default values.

Column Defaults

Object NameDefault Value
PK Column Nameid%table%
PK Column TypeINT
Column Name%table%col
Column TypeVARCHAR(45)

Foreign Key/Relationship Defaults

Object NameDefault Value
FK Namefk%stable_%dtable%
Column Name%table%_%column%
ON UPDATENO ACTION
ON DELETENO ACTION
Associative Table Name%stable%_has_%dtable%

Preferences: Modeling: MySQL

This enables you to set model related options specific to your MySQL version.

Figure 3.13 Preferences: Modeling: MySQL

Preferences: Modeling: MySQL

Model

  • Default Target MySQL Version: A limited subset of validation procedures and table editor options are affected by this MySQL version number.

    Supported MySQL Server 5.6 features include fraction seconds support for TIME, DATETIME, and TIMESTAMP, automatic initialization and updates for TIMESTAMP and DATETIME (for example, setting them to CURRENT_TIMESTAMP), and FULLTEXT index types with the InnoDB engine.

Model Table Defaults

  • Default Storage Engine: Tables created in MySQL Workbench will be defined using this default storage engine.

Forward Engineering and Synchronization

  • SQL_MODE to be used in generated scripts: Defaults to "TRADITIONAL,ALLOW_INVALID_DATES", this defines the SQL_MODE used by Forward Engineering and Synchronization.

Preferences: Modeling: Diagram

Figure 3.14 Preferences: Modeling: Diagram

Preferences: Modeling: Diagram

All Objects

  • [ ] Expand New Objects: Enabled by default.

  • [ ] Propagate Object Color Changes to All Diagrams: Enabled by default.

Tables

  • [ ] Show Column Types: Enabled by default.

  • [ ] Show Schema Name:

  • Max. Length of ENUMs and SETs to Display: [20]

  • [ ] Show Column Flags:

  • Max. Number of Columns to Display: [30] Larger tables will be truncated.

Routines

  • Trim Routine Names Longer Than: [20] characters.

Relationships / Connections

  • [ ] Draw Line Crossings (slow in large diagrams):

  • [ ] Hide Captions: Enabled by default.

  • [ ] Center Captions Over Line:

Preferences: Modeling: Appearance

Use this tab to set the available colors for the objects that appear on an EER diagram canvas. You can also add colors if you wish.

For related information, see Section 3.1, “User Accessibility Options”.

Figure 3.15 Preferences: Modeling: Appearance

Preferences: Modeling: Appearance

Color Presets

These are the available colors used while modeling, and they are divided into two sections. First, the colors used when creating tables and views. The second section are available colors for items such as layers and notes.

Fonts

These define the fonts and font sizes used while modeling.

3.2.5 Fonts and Colors Preferences

Figure 3.16 Preferences: Fonts and Colors

Preferences: Fonts and Colors

Fonts

  • SQL Editor: [Consolas 10] -- Global font for SQL text editors.

  • Resultset Grid: [Tahoma 8] -- Resultset grid in SQL editor

  • Scripting Shell: [Consolas 10] -- Scripting Shell output area

  • Script Editor: [Consolas 10] -- Code editors in scripting shell

Color Scheme

On Microsoft Windows, set the scheme that determines the code colors.

3.2.6 Other Preferences

Figure 3.17 Preferences: Others

Preferences: Others

Timeouts

  • SSH KeepAlive: [0] -- This interval (in seconds) without sending any data over the connection, a "keepalive" packet will be sent. This option applies to both SSH tunnel connections and remote management via SSH.

  • Fabric Connection Timeout: [60] -- Maximum time to wait before a connection is aborted.

Others

  • [ ] Allow more than one instance of MySQL Workbench to run. By default, only one instance of MySQL Workbench can be running at the same time. .

    Note

    All MySQL Workbench instances share the same files and settings, so enable at your own risk.

3.3 MySQL Workbench Settings and Log Files

MySQL Workbench saves configuration, cache, and log related files and directories on your system. These files are saved in your user's MySQL Workbench directory as defined by MySQL Workbench, with this base defaulting to:

Table 3.1 Default Local Configuration Base File Path

Operating SystemFile Path
Windows%AppData%\MySQL\Workbench\
OS X~username/Library/Application Support/MySQL/Workbench/
Linux~username/.mysql/workbench/

A brief description of these directories and files:

Table 3.2 Local Workbench Files and Directory Descriptions

Directory or FileDescription
cache/General behaviors are stored per-connection in *.cache files, and column widths as *.column_widths files
log/Log files include Workbench startup information, and also per-connection SQL action results performed in Workbench
modules/Home of installed plugins, for additional information see Section C.3, “Plugins / Tools”
sql_history/Queries executed in Workbench are stored here, and are available from within MySQL Workbench
snippets/Saved SQL snippets are stored here, for additional information see Section 8.1.5, “SQL Snippets tab”
audit_cache/Cache storage by the Audit Log inspector, for additional information see Section 6.6, “MySQL Audit Inspector Interface”
connections.xmlSaved MySQL server connection information, as seen on the home screen. For information about backing up and restoring this file, see Section 9.1.1.1.7, “The Tools Menu”
server_instances.xmlStores your MySQL server information, as it relates to your MySQL connections
wb_options.xmlStores your preferences, both configured and default

The cache/ directory

The cache/ directory contains cache files in the user's MySQL Workbench directory. All cache files are stored as SQLite 3 databases, and they are not meant to be edited outside of MySQL Workbench. The types of cache files are:

  • *.column_widths:

    These are the saved column widths after adjusting columns in the SQL editor's results grid. The fields include column_id, stored as column_name::db_name::table_name, and width, stored as an integer of character length.

  • *.cache:

    This information (schemas, engines, and other global information) serves as a quick lookup source for the SQL editor's auto completion functionality, and is implicitly updated whenever the schema tree is updated.

All cache/ file names begin with the MySQL connection name. For example, the column width file is named Local_instance_3306.column_widths for a MySQL connection named "Local Instance 3306".

Cached files remain after a connection is either renamed or deleted.

The log/ directory

MySQL Workbench start up and SQL actions are logged and stored in the log/ directory. This directory is in the user's MySQL Workbench directory.

Note

To find these text files, from the main Workbench navigation menu choose Help and then Show Log Files.

  • wb*.log:

    Debugging information is generated when MySQL Workbench is started and unexpectedly stopped. Information includes paths used, modules and plugins loaded, system information, and more. The log files are useful when reporting a MySQL Workbench bug.

    The log files rotates when MySQL Workbench is started, in that wb.log is renamed to wb.1.log, wb.log is reset, and the previous wb.1.log file is renamed to wb.2.log, and so on, all the way up to wb.9.log.

  • sql_actions_*.log:

    A log of all SQL execution results but without the data, for debugging purposes.

    The SQL editor's SQL history does not originate from here, as it is stored in the sql_history directory.

The modules/ directory

Custom plugins (modules) are stored in the modules directory. For additional information about MySQL Workbench plugins, see Section C.3, “Plugins / Tools”.

The sql_history/ directory

SQL statements executed in the SQL editor are saved in the sql_history directory. They are stored as plain text files that are separated one per day (such as 2014-01-15) and they contain your MySQL Workbench SQL statement history for all MySQL connections. For additional information, see Section 8.1.7, “Output History Panel”.

The snippets/ directory

SQL snippets used by the SQL editor are stored in the snippets directory. These files include bundled snippets (such as "SQL DDL Statements") and custom snippets saved under the "My Snippets" tab. For additional information, see Section 8.1.5, “SQL Snippets tab”.

3.4 Tutorial: Add a Custom Link to the Home Page

This tutorial introduces the concept of altering the MySQL Workbench home screen by adding your own Shortcut link. Here we will add a shortcut titled "Example" that opens "example.org" as its own browser tab inside MySQL Workbench.

Note

Although adding a link to the Home screen is not a common need, this tutorial is an example that demonstrates the idea of customizing MySQL Workbench.

First, create an icon for your shortcut. This step is optional, and the table below compares the icon used in this tutorial and the default icon if a custom icon is not defined.

Table 3.3 MySQL Workbench Home Screen Icons

Default IconOur Example Icon

Save your new 52x52 pixel image to a location accessible by MySQL Workbench. By default, source icons are stored here:

Table 3.4 Default Path for Home Screen Icons

Operating SystemFile Path
Windows"C:\Program Files (x86)\MySQL\MySQL Workbench CE 6.3.5\images\home\
OS X/Applications/MySQLWorkbench.app/Contents/Resources/
Linux/usr/share/mysql-workbench/images/

Next, open starters_settings.xml (this file is located under your user's MySQL Workbench directory) and add an entry for your new shortcut ID where the order determines the location on the Home screen. Follow the standard convention by appending your value to "com.mysql.wb.starter.", this tutorial uses "example": 


<link type="object">com.mysql.wb.starter.example</link>

Lastly, open predefined_starters.xml and add a new "app.Starter" entry.

Table 3.5 Default Path to predefined_starters.xml

Operating SystemFile Path
Windows"C:\Program Files (x86)\MySQL\MySQL Workbench CE 6.3.5\data\
OS X/Applications/MySQLWorkbench.app/Contents/Resources/data/
Linux/usr/share/mysql-workbench/data/

This tutorial does not describe this entry in detail, so consider it as a self-explanatory template for now. The important concepts include using the "id" you defined in the previous step, your own URL for the "command", and "smallIcon" as a 52x52 pixel image that is displayed on the MySQL Workbench home screen. Adjust these entries according to your needs, including the path to your icon. 


<value type="object" struct-name="app.Starter" id="com.mysql.wb.starter.example">
 <value type="string" key="type">Website</value>
 <value type="string" key="title">Example</value>
 <value type="string" key="description">My wonderful example.org</value>
 <value type="string" key="publisher">Example Inc.</value>
 <value type="string" key="authorHome">http://www.example.org/about</value>
 <value type="string" key="smallIcon">/usr/local/share/wb-home-screen-example-icon.png</value>
 <value type="string" key="command">browse:http://example.org</value>
</value>
Note

Upgrading MySQL Workbench will overwrite these changes, because predefined_starters.xml is stored inside the MySQL Workbench installation directory, and starters_settings.xml is reset during installation. Consider saving copies of these changes for future reference.

Restart MySQL Workbench to see the new link on your MySQL Workbench home screen.

Figure 3.18 Home Screen with Custom Link

Home Screen with Custom Link

Additional "app.Starter" options include: 


Require a specific edition of Workbench:
  Community:
    <value type="string" key="edition">ce</value>
  Commercial:
    <value type="string" key="edition">se</value>

Require a specific version (or higher) of Workbench:
  <value type="string" key="introduction">6.1.0</value>
Prev Up  Next
Chapter 2 Installation Home Chapter 4 The Home Screen