Menu
Visual query builder. In case it is not shown, you can open it by clicking on the “SQL+” button located in the top bar. Create a new SQL tab for executing queries. Create a database with MySQL Workbench visual query builder. Let’s create a new database executing the following command. Upon opening the MySQL Workbench, we see the following: Figure 1. Opening the MySQL Workbench. Example 1: MySQL Workbench Create Schema. To create a schema using MySQL Workbench, we do the following: 1. Select File - New Model. Then a worksheet with an automatic schema named mydb will appear. The schema in MySQL refers to the database.
This section summarizes how the MySQL Workbench 8.0 release series progressed with each minor release. For the list of supported platforms, see https://www.mysql.com/support/supportedplatforms/workbench.html.
- The following MySQL 8.0.19 and 8.0.20 server language features are supported in MySQL Workbench 8.0.21:
DROP CHECK
,ALTER CONSTRAINT
, andDROP CONSTRAINT
inALTER TABLE
statements.- Locking clause for query expressions.
- Table values constructor.
- Short table select syntax.
- New requirements for the
CHANGE MASTER TO
replication statement (row format, primary key check). - Failed login attempts and password lock time in
ALTER USER
statements. - Format support for
EXPLAIN ANALYZE
statements. - New and changed keywords:
ARRAY
FAILED_LOGIN_ATTEMPTS
MASTER_COMPRESSION_ALGORITHM
MASTER_TLS_CIPHERSUITES
MASTER_ZSTD_COMPRESSION_LEVEL
MEMBER
OFF
Acorn 6 image editor 6 2 3.PASSWORD_LOCK_TIME
PRIVILEGE_CHECKS_USER
RANDOM
REQUIRE_ROW_FORMAT
REQUIRE_TABLE_PRIMARY_KEY_CHECK
TIMESTAMP
TIME
- The following MySQL server language features are supported in MySQL Workbench 8.0.19:
- Value references in
INSERT
statements. - New options (TLS version, cipher suite, compression, and privilege check) in
CHANGE MASTER TO
replication statements. - Random passwords in
CREATE USER
andALTER USER
statements. EXPLAIN ANALYZE
(see Obtaining Information with EXPLAIN ANALYZE).binary
collation names.
- Beginning with MySQL Workbench 8.0.17, the ANTLR4 grammar was updated to handle the language features of each supported MySQL server version from MySQL 5.6 to MySQL 8.0, including the following new language features in MySQL Server 8.0.17:
CHECK
constraints with enforcement.- ODBC table references now require the
OJ
keyword (previously an identifier was allowed). - The equal sign (
=
) operator for aliases is no longer allowed. - CHANGE MASTER supports
NETWORK_NAMESPACE
and channels. - The
CREATE DATABASE
statement permits a default encryption to be specified. - Roles now support exception lists and
GRANT AS
. Grant identifiers can be qualified with a schema name. MEMBER OF
is allowed in expressions.AS ARRAY
is allowed inCAST
expressions.- Passwords in user statements can now also be hexadecimal numbers.
- Hexadecimal numbers are also allowed in many numeric expressions.
- Minor items, such as, permit data types in number expressions, and reorganize keywords to lower conflicts in the server parser generation.
- Removed support for MySQL 5.5 in the MySQL Workbench 8.0 release series. Minimum version now is MySQL 5.6.If you still need to use MySQL Workbench on a MySQL 5.5 server, you can use MySQL Workbench 6.3, which is available from MySQL Product Archives.
- MySQL Workbench now uses
utf8mb4
as the connection and client character set, replacingutf8mb3
. - Support for the Chinese character set
gb18030
was added.
- As of MySQL Workbench 8.0.14, keyboard access was added to the home screen tab to enable navigation using Tab and Enter keys. In addition, the screen view now scrolls to display a selected item if the item was off-screen when highlighted with the Tab key.On Windows and Linux hosts, the Application key and Ctrl+F10 now open a menu of commands (context menu) related to the selection.
- Starting with MySQL Workbench 8.0.22, visual explain diagrams use the phrase
hash join
within the diamond symbol instead ofblock nested loop
when the server version is 8.0.20 or higher. - For consistency with other MySQL products, RapidJSON replaces the native JSON parser in the MySQL Workbench 8.0.18 release.
- Important change: MySQL model files last saved before MySQL Workbench 6.3 are no longer supported unless the models can be upgraded for use with the 6.3 release series.
- Beginning with MySQL Workbench 8.0.16, the script editor highlights matching pairs of parentheses when one of the pair is selected.
- SQL context help was enhanced to eliminate the requirement of having a valid MySQL connection to view the help topics and to improve the presentation of each help topic.
- A new auto-completion engine was added for use with object editors (triggers, views, stored procedures, and functions) in the visual SQL editor and in models.
- Geometry fields displayed in the result grid now include a context-menu item that opens the specific location value in a browser. The selected point opens in
openstreetmap.org
by default, but an alternative online service can be used (see Section 3.2.7, “Other Preferences” ). - Support for invisible indexes was added for use when the active server supports the feature and the index is neither a primary key index nor a unique column (see Invisible Indexes). A new option in the Indexes subtab of the table editor (for both the SQL and modeling editors) provides index visibility when it is selected.
- A new SQL export option in the Forward Engineering SQL Script wizard sorts tables alphabetically in the generated script, rather than sorting tables according to foreign-key references by default (see Section 9.4.1.1.1, “Creating a Schema”).
- The
OmitSchemas
option replaces both theUseShortNames
andOmitSchemata
options to eliminate the schema name from table names when using the Python API to generate a schema from an.mwb
file automatically.
- Output from schema validation plugins for MySQL models now is shown in a single location and reorganized to provide informational, warning, and error messages by category. A new Validate tab also provides a simple way to reselect and rerun validation tests from the output area in the right side panel (see Section 9.2.3, “Schema Validation Plugins”).
- Support for the
--incremental-with-redo-log-only
option was added to create backups directly from the redo log (see Options Tab).
- MySQL Workbench now provides a simple way to enable or disable the persisted global system variable settings introduced in MySQL 8.0 (see SET Syntax for Variable Assignment). For variables that can be persisted, a new check box enables configuration changes at runtime that also persist across server restarts and applies the persisted value, if one exists. Persistent system variables can be reset (to not persist) individually or collectively. For additional information, see Persist System Variables.
- The requirement to install the EPEL repository on enterprise Linux systems, such as Oracle Linux and Red Hat, is removed for general use with the MySQL Workbench 8.0.18 release. Working with spatial data is an exception and you can still install the repository if needed (see Installing Oracle Enterprise Linux and Similar).
- MySQL Workbench 8.0.18 switched to the C++17 programming language.
- Support for Microsoft Visual Studio 2019 was added in the MySQL Workbench 8.0.19 release. Microsoft Visual Studio support was upgraded from Visual Studio 15 to Visual Studio 17 in the MySQL Workbench 8.0.16 release.
- MySQL Workbench 8.0.19 (and higher) supports macOS 10.15 Catalina. MySQL Workbench 8.0.14 (and higher) supports macOS 10.14 Mojave, including full compatibility with the Dark Mode color scheme.
- Support for Microsoft Windows Server 2019 was added in the MySQL Workbench 8.0.19 release. Support for Microsoft Windows Server 2016 was added in the MySQL Workbench 8.0.11 release.
- MySQL Workbench source code has been reformatted according to Google style.
libgnome-keyring
was depreciated and replaced withlibsecret
in the MySQL Workbench 8.0.12 release on Linux platforms. Thelibsecret
library provides enhanced cross-platform password storage and lookup.Some users with existing stored passwords will be prompted to enter a password after upgrading.- SSH tunneling support was added to the MySQL Workbench Migration Wizard and also to the wbcopytables command-line utility for copying data.
- Setting an encryption password is required to perform MySQL Enterprise Backup operations on encrypted tables (see Options Tab).
- The SSH implementation based on Paramiko was replaced with the one based on libssh.
- MySQL Workbench now supports the
caching_sha2_password
authentication plugin introduced in MySQL 8.0 (see Caching SHA-2 Pluggable Authentication).
As of MySQL Workbench 8.0.14, the following new functions are included in the Workbench GRT module:
activateDiagram(<Diagram>)
Opens the selected EER diagram for use with theexportPNG
,exportSVG
,exportPS
, andexportPDF
functions.exportDiagramToPng(<Diagram>, <path>)
Performs a PNG export of an EER diagram to the path provided without activating it.
In MySQL Workbench 5.2.26 a new query execution command is available, where query output is sent as text to the text Output tab of the SQL Editor. Some MySQL Workbench users liked the “Results to Text” option available in Microsoft SQL Server Management Studio. Cool thing is with a few lines of Python we implemented this command using the SQL Editor scripting API.
For full documentation on scripting and plugin development, refer to the documentation pointers page.
![Mysql Workbench New Query Tab Mysql Workbench New Query Tab](https://www.got-it.ai/solutions/sqlquerychat/wp-content/uploads/2019/06/Screen-Shot-2019-06-29-at-10.54.13-PM.png)
In this post, you will learn:
- Python script for implementing “Results to Text”
- How you can customize the script to deliver your own customized results format command.
Execute Query to Text (accessible from Query -> Execute (All or Selection) to Text), will execute the query you typed in textual form into the Output tab in the SQL Editor. The output is similar to that of the MySQL command line client and can be copy/pasted as plain text. But the command line client has a different, interesting output format, activated through the –vertical command line option. It changes the output from a tabular to a form-like format, where row values are displayed as column name/value pairs:
We will try emulating that format using our modified plugin.
The Original Plugin Code
The goals for the original plugin shipped with Workbench were:
- Provide an alterntive to the Results Grid output
- Provide MySQL CLI and MS SQL Server Studio Text Formatted results
- Add “Execute to Text” to the Query Menu
You can locate the original code for the plugin we want to modify in the
sqlide_grt.py
file, in the MySQL Workbench distribution (in Windows it will be in the modules directory in the WB folder, in MacOS X it will be in MySQLWorkbench.app/Contents/PlugIns and in Linux, in /usr/lib/mysql-workbench/modules).Lines 1 to 6 import some Workbench specific Python modules: Tagr 4 6 3.
- wb, which contains various utility functions for creating plugins;
- grt, for working with Workbench objects and interfacing with it and
- mforms, for creating GUIs.
@ModuleInfo.export(grt.INT, grt.classes.db_query_QueryBuffer)
declares the return type (grt.INT by convention) and argument types of the plugin function defined further down. In the line above it, a unique identifier for the plugin is given, followed by a default caption to use in places such as menus, the input values taken by the plugin and the location in the Plugins menu where it should be placed.The plugin executes the current query, so the argument it requests is
wbinputs.currentQueryBuffer()
(the selected query buffer tab), which has a type of <a href='http://wb.fabforce.eu/doc/globals/classdb__query___query_buffer.html'>db_query_QueryBuffer</a>
. You can read more about the available types and inputs in the relevant documentation.The code itself is straightforward:
- it takes the query code,
- executes it through the SQL Editor object that owns the query buffer and
- renders the output in the text Output tab.
Custom Plugin
The goals for the custom plugin are:
- Provide an custom alterntive to the Results Grid output
- Provide text results column name/value pairs formatted output
- Add “Execute to Vertical Formatted Text” to the Query Menu
To create the modified version, we can copy the above plugin and make some changes.
- copy the plugin file from the Workbench plugins directory to some folder of yours (eg your home directory or Desktop);
- rename it to
verticalquery_grt.py
; - open it in some text editor of your liking.
First, we change the module info:
The plugin arguments are the same, so we only need to update its identifier and name:
Mysql Workbench New Query Tables
You can see the body of the function in the complete sample module file here.
Trying it Out
To install the module, you can use the Scripting -> Install Module/Script File… menu command. Select the newly created plugin file (
Once installed, restart Workbench and run it:
verticalquery_grt.py
) from the file browser and click Open.Once installed, restart Workbench and run it:
Create New Table Mysql Workbench Query
![Mysql workbench commands Mysql workbench commands](https://i.stack.imgur.com/vtog8.png)
You can download the entire, modified sample plugin here