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: Utilities

MySQL Workbench is an extensible tool for database design, development, and administration – with support for python scripting and plugins, that can automate basic tasks.

Starting with MySQL Workbench 5.2.31, and with MySQL designers, developers, and administrators in mind, we’ve added a new plugin called MySQL Utilities. The MySQL Utilities is a set of python scripts that automate basic tasks. And as MySQL Workbench is about helping design, develop and administer databases, we’ve put the MySQL Utilities at your fingertips so you can quickly get to them and get your job done. Users will benefit from proven scripts, leverage the testing and ideas of the community and the experts at MySQL, and avoid having to reinvent common scripts…

Lets go on a quick tour…

The MySQL Utilities Plugin starts a shell that contains a set of python scripts that perform tasks such as copying, exporting, and importing databases, finding database objects or processes, and cloning servers and users.

To start MySQL Utilities, start Workbench and click on Plugin -> Start Shell for MySQL Utilities or click the Utilities shortcut icon from the Plugins Panel at the top of the Home tab.

For usage information just type the command name without any parameters.
For documentation of each utility look here.

For more information about MySQL Workbench: Utilities check out Mats’ post.

MySQL Workbench 5.2.25 released, goes GA

Dear MySQL Users,

We’re pleased to announce the release of MySQL Workbench 5.2.25  This release is GA (Generally Available). We hope you will make MySQL Workbench your preferred tool for Design, Development, and Administration of your MySQL database applications.

Special thanks go to all the great MySQL Beta Testers that provided valuable ideas, insights, and bug reports to the Workbench Team. Your beta feedback truly helped us improve the product.

MySQL Workbench 5.2 GA provides:
• Data Modeling
• Query (replaces the old MySQL Query Browser)
• Administration (replaces the old MySQL Administrator)

Please get your copy from our Download site. Sources and binary packages are available for several platforms, including Windows, Mac OS X and Linux.

To get started quickly, please take a look at this short tutorial.

MySQL Workbench 5.2 GA Tutorial

Workbench Documentation can be found here.

In addition to the new Query/SQL Development and Administration modules, version 5.2 features improved stability and performance – especially in Windows, where OpenGL support has been enhanced and the UI was optimized to offer better responsiveness.

For a detailed list of resolved issues, see the change log.

If you need any additional info or help please get in touch with us.

Post in our forums, leave comments on our blog pages or if you want to talk to us directly you can visit us on our IRC channel #workbench on irc.freenode.net.

– The MySQL Workbench Team

MySQL Workbench Administrator Plugin

Recently we have released Workbench 5.2.4 Alpha. This version has some new features, and amongst them there is Workbench Administrator plugin or WBA plugin for short.

The plugin aims to ease the managing process of server instances. What we offer with WBA is a simple way to check status, configure and control one server instance. Some parts will resemble discontinued MySQL Administrator.

And here is a short summary of what we will have in WBA:

  • Start/Stop server
  • Edit server configuration (my.cnf)
  • Manage user accounts
  • Monitor user connections
  • Status/Server variables browsing
  • Log browsing
  • Dump/Restore

In this alpha we have implemented a subset, which targets local usage only. Remote management and administration is upcoming. The subset includes:

  • Add new Server Profiles
  • Start/Stop the Server
  • Edit Server Configuration (my.cnf)
  • Monitor user connections
  • Status/Server variables browsing

To launch the plugin you need to run Workbench 5.2.4, register a server instance by double-clicking the Manage Icon. Once the server instance is registered you can double click the icon of the server instance. But for the moment let’s dive into what is important to specify at the server instance creation stage.

Our target is ‘System Profile’ tab of ‘Manage Server Instances’ form. Mostly, the whole process should be simple selecting the right profile. Nevertheless, for now (I hope you still remember it is an alpha release), one of the vitally important things is to check location of ini or cnf file. Also you may check commands used to start, stop and query server status. For example OS X profile has that line to check for status: ps -cxa | grep mysqld.

All the commands specified in these entries are run as a sub-processes and sometimes they may require super-user privileges, for that purpose there is ‘use sudo’ checkbox. Normally, command run has exit code which WBA checks for to determine success of failure of the command.

When adding a new Server Profile you also need to select a Connection to the server (You should create that connection in the Connection Management dialog first). That will allow you to browse the Connections and Server Variables in the WBA. It will also be used to update the Server Status periodically every second and show the current state (that is in next alpha).

The way to change the my.cnf/my.ini config file is pretty straightforward. First, if you want a value to be written to the config file, you have to enable the checkbox left to each option. After all changes have been made, you can either press Apply or Discard button. Apply will display a Dialog which shows several things: what has changed, resulting file, commands which are used to save file. If changes are correct, press Apply on the form. That is it.

Stay tuned for the updates on WBA topic.

MySQL Workbench now works on Snow Leopard

So, it turned out fixing the Workbench crash on Snow Leopard wasn’t very hard. Took a little time to find out what was causing the crash but, once that was found, the fix was quick.

The next releases of both branches — MySQL Workbench 5.1.18 this week and 5.2.3 alpha later on — will have proper support for Mac OS X 10.6

For whoever might be interested, the bug was caused by what seems to be a slight change in how object/NSView copies are handed in Cocoa. NSCollectionView items are populated by copying a “prototype” object. It seems that somehow, duplication of that object included the fields of child objects as well in Leopard, but not in Snow Leopard. The object copies were incomplete and when they were accessed, it ended up attempting to create a C++ string out of nil.

Python Scripting in Workbench

Updated: 2010-8-11 updated sample code to reflect changes in plugin registration API

Python support has been added to the latest version of MySQL Workbench.

In addition to Lua, you can now write scripts and modules or interact with the GRT shell using the Python language. The integration allows you to use GRT objects and modules mostly in the same way you would do with normal Python objects. The built-in grt module contains everything related to the GRT that’s exposed to Python, including:

  • custom types for GRT lists, dicts and objects
  • wrappers for GRT classes, that can be used and instantiated as a normal Python class;
  • wrappers for registered GRT modules, that can be used like normal modules;
  • a reference to the root node of the GRT globals tree;

You can inspect these objects with the standard dir() command and in some cases with help(), to access the built-in documentation (although still incomplete).

Below is a sample shell session demonstrating the Python support:

>>> import grt

>>> dir(grt)
[‘DICT’, ‘DOUBLE’, ‘Dict’, ‘Function’, ‘INT’, ‘LIST’, ‘List’, ‘Method’, ‘Module’, ‘OBJECT’, ‘Object’, ‘STRING’, ‘__GRT__’, ‘__doc__’, ‘__name__’, ‘classes’, ‘get’, ‘modules’, ‘root’, ‘send_error’, ‘send_info’, ‘send_output’, ‘send_warning’, ‘softspace’, ‘write’]

>>> grt.classes
<module ‘grt.classes’ (built-in)>

>>> dir(grt.classes)

[‘GrtLogEntry’, ‘GrtLogObject’, ‘GrtMessage’, ‘GrtNamedObject’, ‘GrtObject’, ‘GrtStoredNote’, ‘GrtVersion’, ‘__doc__’, ‘__name__’, ‘app_Application’, ‘app_CommandItem’, ‘app_CustomDataField’, ‘app_Document’, ‘app_DocumentInfo’, ‘app_DocumentPlugin’, ‘app_Info’, ….

>>> for schema in grt.root.wb.doc.physicalModels[0].catalog.schemata:
… print schema.name

mydb
sakila

>>> table=grt.root.wb.doc.physicalModels[0].catalog.schemata[1].tables[0]

>>> dir(table)
[‘__callmethod__’, ‘__class__’, ‘__delattr__’, ‘__dict__’, ‘__doc__’, ‘__getattribute__’, ‘__grtclassname__’, ‘__hash__’, ‘__init__’, ‘__module__’, ‘__new__’, ‘__reduce__’, ‘__reduce_ex__’, ‘__repr__’, ‘__setattr__’, ‘__str__’, ‘__weakref__’, ‘addColumn’, ‘addIndex’, ‘addPrimaryKeyColumn’, ‘avgRowLength’, ‘checksum’, ‘columns’, ‘comment’, ‘commentedOut’, ‘connection’, ‘connectionString’, ‘createDate’, ‘createForeignKey’, ‘customData’, ‘defaultCharacterSetName’, ‘defaultCollationName’, ‘delayKeyWrite’, ‘foreignKeys’, ‘indices’, ‘inserts’, ‘isDependantTable’, ‘isForeignKeyColumn’, ‘isPrimaryKeyColumn’, ‘isStub’, ‘isSystem’, ‘isTemporary’, ‘lastChangeDate’, ‘maxRows’, ‘mergeInsert’, ‘mergeUnion’, ‘minRows’, ‘modelOnly’, ‘name’, ‘nextAutoInc’, ‘oldName’, ‘owner’, ‘packKeys’, ‘partitionCount’, ‘partitionDefinitions’, ‘partitionExpression’, ‘partitionType’, ‘password’, ‘primaryKey’, ‘raidChunkSize’, ‘raidChunks’, ‘raidType’, ‘removeColumn’, ‘removeForeignKey’, ‘removeIndex’, ‘removePrimaryKeyColumn’, ‘rowFormat’, ‘subpartitionCount’, ‘subpartitionExpression’, ‘subpartitionType’, ‘tableDataDir’, ‘tableEngine’, ‘tableIndexDir’, ‘temp_sql’, ‘temporaryScope’, ‘triggers’]

>>> table.name
‘actor’

>>> table.name= table.name.upper()

>>> table.name
‘ACTOR’

>>> table.columns[1].formattedType(1)
‘VARCHAR(45)’

>>> isinstance(table, grt.classes.db_mysql_Table)
True

>>> dir(grt.modules)
[‘DbMySQL’, ‘DbUtils’, ‘MySQLDbDiffReportingModule’, ‘MySQLDbModule’, ‘MySQLEditorsModule’, ‘MySQLSchemaReportingModule’, ‘MysqlSqlFacade’, ‘PluginManager’, ‘PropelExport’, ‘PyWbUtils’, ‘WbEditorsModule’, ‘WbModel’, ‘WbModuleValidation’, ‘WbModuleValidationMySQL’, ‘WbMysqlImport’, ‘Workbench’, ‘__doc__’, ‘__name__’]

>>> from grt.modules import Workbench
>>> Workbench.copyToClipboard(“HELLO WORLD”)
1L

Modules and Plugins

Writing Workbench modules and plugins is very simple task in Python. For modules (collections of routines available to other scripts and modules, from any language); all you need to do is to import the wb module and call DefineModule() with a few arguments. Defining a function you wrote to be exported can be done using the @export decorator from the object returned by DefineModule().

To make your module also export plugins (a function that is exposed to the user in menus or others ways), the @plugin can be used in addition to @export.

Here’s a sample Python module, exporting a function which also works as a Plugin, callable from Plugins -> Catalog -> . It will simply dump a list of all columns from all tables in all schemata in your model to the Output pane.

# import the wb module, must be imported this way for the automatic module setup to work

from wb import *

# import the grt module

import grt

# define this Python module as a GRT module

ModuleInfo=DefineModule(name= “PyWbUtils”, author= “Sun Microsystems Inc.”, version=“1.0”)

# this is just a function used by the plugin, it’s not exported

def printTableLine(fields, filler= ” “):

print “|”,

for text, size in fields:

print text.ljust(size, filler), “|”,

print

# @wbexport makes this function be exported by the module and also describes the return and

# argument types of the function

# @wbplugin defines the name of the plugin to “wb.catalog.util.dumpColumns”, sets the caption to be

# shown in places like the menu, where to take input arguments from and also that it should be included

# in the Catalog submenu in Plugins.

@ModuleInfo.plugin(“wb.catalog.util.dumpColumns”, caption= “Dump All Table Columns”, input= [wbinputs.currentCatalog()], pluginMenu= “Catalog”)

@ModuleInfo.export(grt.INT, grt.classes.db_Catalog)

def printAllColumns(catalog):

lines= []

schemalen= 0

tablelen= 0

columnlen= 0

typelen= 0

for schema in catalog.schemata:

schemalen= max(schemalen, len(schema.name))

for table in schema.tables:

tablelen= max(tablelen, len(table.name))

for column in table.columns:

columnlen= max(columnlen, len(column.name))

typelen= max(typelen, len(column.formattedType(1)))

lines.append((schema.name, table.name, column.name, column.formattedType(1)))

printTableLine([(“-“, schemalen),
(“-“, tablelen),
(“-“, columnlen),
(“-“, typelen)], “-“)

printTableLine([(“Schema”, schemalen),
(“Table”, tablelen),
(“Column”, columnlen),
(“Type”, typelen)])

printTableLine([(“-“, schemalen),
(“-“, tablelen),
(“-“, columnlen),
(“-“, typelen)], “-“)

for s,t,c,dt in lines:

printTableLine([(s, schemalen), (t, tablelen), (c, columnlen), (dt, typelen)])

printTableLine([(“-“, schemalen),
(“-“, tablelen),
(“-“, columnlen),
(“-“, typelen)], “-“)

span style=”color: #cdbe70″>print len(lines), “columns printed”

return 0

Truncated output for the sakila model:

	| ------ | ------- | ----------- | ------------- |
	| Schema | Table   | Column      | Type          |
	| ------ | ------- | ----------- | ------------- |
	| sakila | actor   | actor_id    | SMALLINT      |
	| sakila | actor   | first_name  | VARCHAR(45)   |
	| sakila | actor   | last_name   | VARCHAR(45)   |
	| sakila | actor   | last_update | TIMESTAMP     |
	| sakila | address | address_id  | SMALLINT      |
	| sakila | address | address     | VARCHAR(50)   |
	| sakila | address | address2    | VARCHAR(50)   |
	| sakila | address | district    | VARCHAR(20)   |
	| sakila | address | city_id     | SMALLINT      |
	| sakila | address | postal_code | VARCHAR(10)   |
	....
	| ------ | ------- | ----------- | ------------- |
	89 columns printed

With the large amount of libraries available for Python, the possibilities you have for writing Workbench plugins and modules are now much bigger.

Also, to facilitate executing your own scripts, a new command to run script files has been added to the Tools menu.

OpenGL and Workbench Canvas News

Recently, a bug that caused excessive memory usage and slowness (specially in large models) in Workbench has been fixed in its canvas. From the testing I’ve done, the speed for displaying diagrams seems to scale much better and dragging around tables in large models is doable in an acceptable speed. This fix is in the upcoming releases of the 5.0 and 5.1 alpha versions of Workbench.

Apart from that, we’ve been doing some work on reintroducing OpenGL support to the Workbench canvas. Unlike the initial implementation, this time OpenGL is used directly instead of going through Cairo’s experimental (and not quite reliable) support through the Glitz layer. This means that in machines with a recent graphics card, hardware acceleration will be used and working with large diagrams will be a lot smoother and faster. Of course, software rendering backends (GDI+, Quartz and Xlib) will still be supported and used in case OpenGL is not the best option.

We still use Cairo to render everything (or most things) as before, but now the pre-rendered object bitmaps are uploaded to the video card and displayed through textures. Although the “software” rendering we were using was also hardware accelerated in most cases (because GDI+ et al are implemented with acceleration by the OS), using OpenGL textures seems much faster than blitting these bitmaps to screen in the old way. Naturally, this also opens the door for some totally useless and cool eye candy 🙂 But that’s for after we get the essentials done…

There are still problems to be worked out (some of the transparencies are not working well and there are some refreshing glitches), but you can try it with the --force-opengl-render option.

MySQL Workbench 5.0.25 coming next week

As you may have noticed we’re going for a major milestone on our Workbench 5.1 roadmap. So our resources are pretty much concentrated on this  task at the moment. But it’s just as important to keep improving our 5.0 GA version, so we will reserve the first days next week to make progress here as well. We will publish the next release of 5.0 mid-next-week. So stay tuned for a better 5.0 next week and the upcoming 5.1 alpha later this month.

Preparing For Next Release – MySQL Workbench 5.0.24 Coming Soon

Due to summer vacations – yes, even we take some time off – our release-cycle has slowed down quite a bit so it has already been more than 4 weeks since we aired an update of Workbench. Right in the middle of testing and preparing the next build, our build-server has raised the white flag. Looks like the cooling system above the unit decided, that its too hot, and put a cold shower right down on our machine. Our engineers are quickly gathering some new iron and we are meanwhile preparing the build manually this time. There are a few bugs remaining but next package will be ready soon and we put it on our download servers – so stay tuned for the next Workbench 5.0 release.

Nice Blog Postings About MySQL Workbench

Quite a few Blog postings pop up recently, let me outline two of them.

Gerry Narvaja talks about multi-page printing by using the MySQL Workbench community edition utilizing the PDF output. Find his posting here.

Weizh posted a nice step-by-step tutorial on how to use Workbench to show differences between two databases. Find it here.

Both workflows get you what you want by using the Community Edition. For those of you who want everything on a silver plate there is the Standard Edition of course, which simplifies these tasks even more.

On a side note: To get more information about the printing topic Gerry joined us on our IRC #workbench channel on irc.freenode.net. Somebody from the team usually hangs out there from 10:00 am European time till 18:00 EST. Don’t be shy and join us there if you have a WB-related question. We might also be able to answer general MySQL questions but the better channel for that is #mysql. For more information see this page.