MySQL Workbench 6.0: What’s New

With the first beta of MySQL Workbench 6.0 just released, we’ll go through the list of improvements we’ve made since 5.2.47

New Home Screen

The Home screen went through a renovation and now has a modernized look. As part of the SQL Editor and Administration GUI unification, there’s now a single list for MySQL connections. Recently opened model files and other major features are also accessible from it.

You can organize different connections into “folders” by right clicking on a connection and selecting “Move to Group…” in the context menu.

New server connections can be added by clicking the + button next to the MySQL Connections heading. By clicking the Configure Remote Management… button in the new connection setup dialog, you can add server management capabilities to the connection. As before, SSH access with “sudo” is needed for remote management.

The wrench icon next to the heading brings up the connection editor, which lets you change connection and management parameters from an editor interface. Configuration was simplified compared to 5.2.

SQL Connections

In MySQL Workbench 6.0, the SQL Editor and Administrator interfaces were merged together. You can now access administration functionality, such as restarting the server or listing connections from the same database connection tab. The primary sidebar now has both the familiar Schema tree and the administration items.

If you’d like more space for the Schema tree, you can click the expand button next to the SCHEMAS heading and give it more vertical space.

Schema Inspector

Schema Inspector, allows you to browse general information from schema objects in the server. For tables, there’s also a Table Maintenance panel, from where you can perform maintenance tasks such as ANALYZE, OPTIMIZE, CHECK and CHECKSUM TABLEs. To access, right click a schema and select Schema Inspector.

Table Data Search

You can select schemas and/or tables to perform client-side searches for arbitrary strings and patterns on their contents.

Table Templates

If you find yourself wishing for more control over the default column definition and often create tables having the same common set of columns, you can now create templates for them. The same templates can be used in the SQL Editor and also in the EER Modeling tool.

 

Improved Server Status

More information about your server in a glance.

Context Sensitive Help

Online, context sensitive help is available in query editors. Place the cursor over a SQL keyword and the help tables in the server will be queried for it. This is equivalent to the HELP keyword from the command line client. To disable help, hide or switch the sidebar pane to a different tab.

Vertical Query Output

A new text mode, vertical query output was introduced. This is equivalent to the \G option from the command line client and outputs the results of a query laid out in Column/Value pairs, one value per row. This improves readability of certain types of resultsets. Ctrl+G/Cmd-G can be used as a shortcut for that command.

Cascaded Delete Statement Generator

You can now generate the list of DELETE statements that would be needed to delete a given row from a table, in case there are other tables with foreign keys that reference them (which would prevent the row to be deleted). Select a table in the Schemas tree and from the context menu, select Copy to Clipboard -> Delete with References. A similar feature for SELECT statements will generate the queries that would list the rows to be deleted from other tables.

 

Improvements to Log Viewer

The log file viewer was improved for when browsing files that are not readable by the current user.

Modeling

Synchronization

DB modeling got several bug fixes, specially in Synchronization. You can now Synchronize a schema with another having a different name (like sakila in your model vs sakila_test in the server). You can also fix object name mapping issues (when a table or column cannot be automatically recognized as being the same, because of renames) using the new Table and Column Mapping editor.

Syntax Highlighting was also added to the various places where SQL code is visualized.

Table Templates

Quickly add tables using table templates, having any number of columns with the attributes you want.

Improved SQL Editors

A standardized toolbar was added to all SQL code editors, where you can export/import files, reformat the SQL, perform find/replace etc.

Migration Wizard

The migration wizard was extended to support 2 new database sources. You can now also migrate from Sybase SQL Anywhere and SQLite. That makes the total list of supported sources to:

  • MS SQL Server 2000, 2005, 2008, 2012
  • Sybase Adaptive Server Enterprise
  • PostgreSQL
  • Sybase SQL Server
  • SQL Anywhere
  • SQLite
  • and MySQL to MySQL migrations/database copies
This should get you started with the new stuff, but we’ll keep posting articles with details about each feature in the coming weeks, so make sure to come back for more info!

MySQL Workbench 5.2.36: What’s New

MySQL Workbench 5.2.36 is now out and brings a lot of improvements across the board, with special focus on the Query Editor. We’ll cover some of the changes in this post, jump to the full post for details.

MySQL Workbench 5.2.36 is now out and brings a lot of improvements across the board, with special focus on the Query Editor. We’ll cover some of that here:

Redesigned Query Editor

    • The log of executed commands and server responses is now always visible while resultset grids and the query editor can be resized according to your needs. Resultsets are also grouped in the same tab as the query editor that generated them.
    • SELECT queries are now analyzed as in the old MySQL Query Browser tool and, if possible, its resultset can be edited in the grid. If the resultset cannot be edited, you can place the mouse over the ReadOnly label and view the reason.

  • Improved snippets manager and editor, allows having snippets list always at hand, while editing can be done without disrupting work on the main query area.
  • Editor state is now properly saved between sessions. Sidebar sizes, the last selected schema and other state information is now properly remembered between sessions.
  • Script and resultset tabs can be reordered.
  • Keyboard navigation of resultsets has been fixed to properly handle Tab key navigation in all platforms.
  • The schema tree will now work with multiple selections, allowing the same action to be performed with more than one object at a time. You can select the columns to appear in a SELECT statement.
  • A filter box was added to the live schema tree, allowing you to restrict the number of visible items in the tree to what you’re interested in.
  • The schema tree was expanded to show more information about objects. In addition to schemas, tables, column, views and routines it also displays information about triggers, indexes and foreign keys. The object information box has been improved.
  • Export recordsets to Excel, JSON and XML files that match the format used by MySQL. The export code has also been revamped to make writing custom export formats easier, which will be explained in a future post.

Improved Administrator

The Administrator was also improved in the following areas:

  • Server Start and Shutdown page was updated to include server error log output
  • The Log browser support was improved to work with log files, in addition to log tables, when managing local and remote servers.
  • Export/Import layout was cleaned up and is now roomier and less cluttered.

Modeling

The focus of this release was on the SQL Editor, so there isn’t much new here. But heavy modeling users on Linux and Mac should be happy to know that the catalog tree has been finally fixed to stop scrolling back to the top.

 

We’re still working hard on adding and finishing more improvements to the major features we already have in MySQL Workbench, to make it the best and easiest to use database tool; but we think this is a good step forward.

MySQL Workbench: PHP development helper plugins

In the new MySQL Workbench 5.2.35, a plugin that will be of interest to PHP developers, both experienced and newbies, has been added.
The plugin contains a couple of functions that allows you to create PHP code straight out of your current work in the Workbench SQL Editor, ready to be pasted to your PHP program.

Screenshot of SQL Editor with PHP Plugins

Copy as PHP Code (Connect to Server) This first plugin will take the parameters from your currently open connection to MySQL and create PHP code to connect to it.

$host="p:localhost";
$port=3306;
$socket="/var/mysql/mysql.sock";
$user="root";
$password="";
$dbname="";

$con = new mysqli($host, $user, $password, $dbname, $port, $socket)
	or die ('Could not connect to the database server' . mysqli_connect_error());

//$con->close();

Not a big deal, but saves some typing for getting something going quickly.

Copy as PHP Code (Iterate SELECT Results) This one will get your query and generate code to execute it and then iterates through the results. It will also parse the SQL and substitute any SQL @variables you use in it with PHP variables that will be bound to the statement before execution. Resultset rows will be bound to PHP variables with the same name as the field (or alias, if your query specified one).

So for the following query:

set @before_date = '1990-01-01';
set @after_date = '1980-01-01';

SELECT 
    emp_no, first_name, last_name, hire_date
FROM
    `employees`.`employees`
WHERE
    `hire_date` < @before_date AND `hire_date` > @after_date;

you would get this back:

$query = "SELECT      emp_no, first_name, last_name, hire_date FROM     `employees`.`employees` WHERE     `hire_date` < ? AND `hire_date` > ?";
$before_date = '';
$after_date = '';

$stmt->bind_param('ss', $before_date, $after_date); //FIXME: param types: s- string, i- integer, d- double, b- blob

if ($stmt = $con->prepare($query)) {
    $stmt->execute();
    $stmt->bind_result($emp_no, $first_name, $last_name, $hire_date);
    while ($stmt->fetch()) {
        //printf("%s, %s, %s, %s\n", $emp_no, $first_name, $last_name, $hire_date);
    }
    $stmt->close();
}

This should be enough for letting you quickly create a PHP program for doing something with the results of a parameterized query, straight out from your normal SQL development workflow and as a bonus, and be safe from injection bugs as a bonus.

Adding your own plugins

The plugins are simple, but more along these lines will be added in the future. And, more importantly, you can modify it to support your own needs. Here’s how:

First of all, find where’s the plugin file. The filename is code_utils_grt.py and you should be able to find it searching in the WB installation folder. To have your own version, rename it to something else like my_code_utils_grt.py, change a few identifiers so it won’t collide with the original built-in plugin as described below and use Scripting -> Install Plugin/Module… to install it to the correct place.

You can use the plugins there as a starting point for your own or modify them to match your coding style, choice of PHP driver etc

The important things you need to change in the plugin copy before installing are:

  1. the plugin name from CodeUtils to something else.
  2. ModuleInfo = DefineModule(name= "CodeUtils", author= "Oracle Corp.", version="1.0")
    
  3. the individual plugin names and identifiers (or just comment them out) and maybe the function names.
  4. @ModuleInfo.plugin("wb.sqlide.copyAsPHPConnect", caption= "Copy as PHP Code (Connect to Server)", input= [wbinputs.currentSQLEditor()], pluginMenu= "SQL/Utilities")
    @ModuleInfo.export(grt.INT, grt.classes.db_query_Editor)
    def copyAsPHPConnect(editor):
    

    The first parameter in @ModuleInfo.plugin is the plugin name and the 2nd is the caption. You can leave everything else, especially the metadata about the input parameters etc.

Here’s a sample plugin that you can use as a template. It’s stripped to the basics for easier understanding:

@ModuleInfo.plugin("wb.sqlide.copyAsPHPQuery", caption= "Copy as PHP Code (Run Query)", input= [wbinputs.currentQueryBuffer()], pluginMenu= "SQL/Utilities")
@ModuleInfo.export(grt.INT, grt.classes.db_query_QueryBuffer)
def copyAsPHPQuery(qbuffer):
    sql= qbuffer.selectedText or qbuffer.script

    text = 'print "the query is %s\n"' % sql.replace('"', r'\"')

    mforms.Utilities.set_clipboard_text(text)

    mforms.App.get().set_status_text("Copied PHP code to clipboard")
    return 0

MySQL Workbench Plugin: Execute Query to Text Output

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.

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

# import the wb module
from wb import *
# import the grt module
import grt
# import the mforms module for GUI stuff
import mforms

# define this Python module as a GRT module
ModuleInfo = DefineModule(name= "SQLIDEUtils", author= "Oracle Corp.", version="1.0")

@ModuleInfo.plugin("wb.sqlide.executeToTextOutput", caption= "Execute Query Into Text Output", input= [wbinputs.currentQueryBuffer()], pluginMenu= "SQL/Utilities")
@ModuleInfo.export(grt.INT, grt.classes.db_query_QueryBuffer)
def executeQueryAsText(qbuffer):
  editor= qbuffer.owner
  sql= qbuffer.selectedText or qbuffer.script
  resultsets= editor.executeScript(sql)
  editor.addToOutput("Query Output:\n", 1)
  for result in resultsets:
    editor.addToOutput("> %s\n\n" % result.sql, 0)
    line= []
    column_lengths=[]
    ncolumns= len(result.columns)
    for column in result.columns:
      line.append(column.name + " "*5)
      column_lengths.append(len(column.name)+5)

    separator = []
    for c in column_lengths:
        separator.append("-"*c)
    separator= " + ".join(separator)
    editor.addToOutput("+ "+separator+" +\n", 0)

    line= " | ".join(line)
    editor.addToOutput("| "+line+" |\n", 0)

    editor.addToOutput("+ "+separator+" +\n", 0)

    rows = []
    ok= result.goToFirstRow()
    while ok:
      line= []
      for i in range(ncolumns):
        value = result.stringFieldValue(i)
        if value is None:
          value = "NULL"
        line.append(value.ljust(column_lengths[i]))
      line= " | ".join(line)
      rows.append("| "+line+" |\n")
      ok= result.nextRow()
    # much faster to do it at once than add lines one by one
    editor.addToOutput("".join(rows), 0)

    editor.addToOutput("+ "+separator+" +\n", 0)
    editor.addToOutput("%i rows\n" % len(rows), 0)

  return 0

Lines 1 to 6 import some Workbench specific Python modules:

  • 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.plugin("wb.sqlide.executeToTextOutput", caption= "Execute Query Into Text Output", input= [wbinputs.currentQueryBuffer()], pluginMenu= "SQL/Utilities")
@ModuleInfo.export(grt.INT, grt.classes.db_query_QueryBuffer)
def executeQueryAsText(qbuffer):

@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 db_query_QueryBuffer. You can read more about the available types and inputs in the relevant documentation.

The code itself is straightforward:

  1. it takes the query code,
  2. executes it through the SQL Editor object that owns the query buffer and
  3. 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.

  1. copy the plugin file from the Workbench plugins directory to some folder of yours (eg your home directory or Desktop);
  2. rename it to verticalquery_grt.py;
  3. open it in some text editor of your liking.

First, we change the module info:

ModuleInfo = DefineModule(name= "QueryToVerticalFormat", author= "WB Blog", version="1.0")

The plugin arguments are the same, so we only need to update its identifier and name:

@ModuleInfo.plugin("wbblog.executeToTextOutputVertical", caption= "Execute Query Into Text Output (vertical)", input= [wbinputs.currentQueryBuffer()], pluginMenu= "SQL/Utilities")
@ModuleInfo.export(grt.INT, grt.classes.db_query_QueryBuffer)
def executeQueryAsTextVertical(qbuffer):

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 (verticalquery_grt.py) from the file browser and click Open.
Once installed, restart Workbench and run it:

You can download the entire, modified sample plugin here