Universal Code Completion using ANTLR

While reworking our initial code completion implementation in MySQL Workbench I developed an approach that can potentially be applied for many different situations/languages where you need code completion. The current implementation is made for the needs of MySQL Workbench, but with some small refactorings you can move out the MySQL specific parts and have a clean core implementation that you can easily customize to your needs.

Since this implementation is not only bound to MySQL Workbench I posted the full description on my private blog.

Parsing in MySQL Workbench: the ANTLR age

Some years ago I posted an article about the code size in the MySQL Workbench project and talked a bit about the different subprojects and modules. At that time the project consisted of ~400K LOC (including third-party code) and already then the parser was the second biggest part with nearly a forth of the size of the entire project. This parser project back then used the yacc grammar from the MySQL server codebase and was our base for all parsing tasks in the product. Well, things have changed a lot since these days and this blog post discusses the current parsing infrastructure in MySQL Workbench.
Continue reading “Parsing in MySQL Workbench: the ANTLR age”

MySQL Workbench 6.2: Usability improvements and more

Direct Schema Tree Action Buttons

Screenshot 2014-09-05 15.00.21 The schema tree in the SQL Editor now has some very convenient buttons for accessing the most used functions for each object type:

  • Table or Schema Inspector
  • Object structure editor
  • Table data browser/editor
  • Call Stored Procedure or Function

Format Note Objects in Diagrams

Note objects in diagrams can now be resized and have its contents automatically rearranged. You can also change style attributes like font, background color and text color. Screenshot 2014-09-05 15.07.27

Other improvements and bug fixes that make a difference

MySQL password is remembered for the session, even if not stored in the keychain, so you don’t need to re-enter it when a new connection is needed.

Keyboard shortcuts now work in the Scripting Shell.

Platform Updates

MySQL Workbench 6.2 also finally adds native 64bit support for Windows. This should allow working with larger data sets and script files. Oracle Linux/RHEL 7 support was added. To improve quality and user experience, we will be providing 64-bit binaries for Linux. Linux users who want 32-bit binaries, can compile from source.

MySQL Workbench 6.2: It’s all about the Query

Improved Visual Explain

In MySQL 5.7, the Optimizer Team has been doing great work in refactoring as well as innovation with the new Cost Model. The improved Visual Explain enables the DBA to now get deeper insights into Optimizer decision making, for improved performance tuning of queries. explain   The UI was also improved to allow easier navigation in large query plans.

Streamlined Query Results Panel

The query results panel was updated to centralize the many features related to result sets into a single location. Result Grid, Form Editor, Field Types, Query Stats, Execution Plan (including the traditional and Visual Explain) and the new Spatial Viewer are all easily accessible from a single interface. Screenshot 2014-09-05 14.35.39

Run SQL Script

It often happens that people try to load gigantic SQL script files into the Workbench SQL editor just to execute them. That will rarely work, as loading files for editing uses a lot of memory and Workbench does a lot of processing in the editor (syntax highlighting, syntax checking, code folding etc). To execute arbitrarily large scripts easily, you can now use the dialog at File -> Run SQL Script: Screenshot 2014-09-05 14.48.35   The dialog lets you preview a part of the script, specify a default schema (in case it’s not already defined) and a default character set to use when importing it. The output window shows warnings, messages and a nice progress bar.

Shared Snippets

SQL Snippets are useful to store queries and commands that are used often, but until now they could only be stored locally. In 6.2, you can now store snippets in the MySQL server you’re connected to and anyone anywhere who can access the .mysqlworkbench schema can also use these snippets.

Small changes

Resultset grid columns are now automatically resized to fit – and if you manually resize a column, the customized size is remembered, so next time you run that query again, the columns will be back to the size you left them.

Customize font for resultset grid – some people want to cram more text in the resultset grid, some people prefer bigger, easier to read text. Now you can pick what you like in Preferences.

Improved state saving for the SQL Editor – Opened, closed and reordered tabs are now properly saved and restored. The scroll position and cursor location is also remembered.

MySQL Workbench 6.2: Spatial Data

The Spatial Viewer

MySQL 5.7 will include much awaited GIS support for InnoDB tables. To make it easier to quickly visualize spatial/geometry data in geographic context, Workbench 6.2 includes a viewer for resultsets containing that type of data. The viewer will render data from each row as a separate clickable element. When clicked, you can view the rest of the data from that row in the textbox. If you have multiple queries with geometry data, you can overlay them in the same map.

spatial_viewer

But that’s not all the features. The Spatial Data Viewer give you the possibility to display the data using different projection systems. Right now you can use Robinson, Mercator, Equirectangular, Bonne. There’s option to even merge different resultsets, execute all of them and switch to Spatial View, you’ll notice several layers for each resultset. You can also zoom in/out, and jump to specific location.

 

The Geometry Viewer

Both the Field and Form Editors were updated to support the GEOMETRY datatype. You can view geometry data like polygons from a single row as an image or as text, in any of the common WKT, GeoJSON, GML or KML formats. form_editor

MySQL Workbench 6.1: Server Variables grouping

MySQL Workbench has an option to view MySQL server variables divided into groups [img. 1], for example: Binlog, General, Keycache, Performance, etc. This is okay if we just wanted to look around, but it can become overwhelming as sometimes we only want to monitor specific variables from different groups.

Server Variables main view
img.1. Server Variables main view

In MySQL Workbench 6.1, we solve this by implementing Custom Groups. It’s a special group that can be created by the user. At the end of the Category List, there is already one defined group, called Custom. When selected, you’ll find a description in the Variable List [img. 2].

Server Variables custom group
img. 2. Server Variables custom group

 

Variable grouping is easy. You simply right-click the chosen variable, and choose an option from the context menu.

Server Variables multiple selection
pct. 3. Server Variables multiple selection add to group

 

The “Add to Custom Category…” menu item popups a mini editor that allows you to create or remove your own custom variables groups [img. 4].

Server Variables group editor
img. 4. Server Variables Group Editor

 

You can also directly add a variable to a group by using the menu items that are located below the “Add to Custom Category…” context menu item. The groups you create will be shown in the Category list, and you only need to select them [img 5].

Server Variables Custom Group Variables
img. 5. Server Variables Custom Group Variables

 

To remove a variable from a custom group, select the corresponding group, and then right-click to open the context menu for the variable you want to remove, and choose the remove option [img. 6].

Server Variables Variable Removal
img. 6. Server Variables Variable Removal

 

Variables Groups are stored on the user level. In other words, each connection will have the same category groups.

We hope this new feature will help you organize your work a little bit better.

MySQL Workbench 6.1: Query Result Enhancements

The SQL Editor in MySQL Workbench 6.1 adds a new interface for query results.  This addition offers 4 new views to your query results, 2 for the result data itself and 2 for meta-information about the query that was executed.

Query Result Set Grid

The traditional result set grid. Run a SELECT query on a table with a primary key and you can edit the data. You must click the Edit button to enter edit mode.

Note: Until Workbench 6.1.1, the check was being done automatically for every SELECT query, but since that requires extra queries to MySQL, the check is now done on demand.

 Screenshot 2014-01-29 15.05.44

 

Result Set Form Editor

The new form editor for result sets comes in handy when you want to closely inspect the fields of each record (specially if it has multi-line text). You can also edit the individual records, if your result set is editable.

Screenshot 2014-01-29 15.05.56

Result Set Field Types

Here, you can inspect information about the fields that were returned by the MySQL server in your query results. Similar to the —column-type-info option from the command line client, it will show you the schema and table from where the field comes from and type information.

Screenshot 2014-01-29 15.06.04

Performance Schema Statistics

This tab uses data gleaned from the performance_schema (in MySQL 5.6) to gather some key statistics collected about the execution from your query, as collected by the server. To have this tab, you need to have the performance_schema enabled with statement instrumentation.

Screenshot 2014-01-29 15.14.36

You can read about the meaning of each item in the MySQL performance_schema documentation, but here’s a summary of some key items:

  • Timing: the timing information shown in the Action Output area in Workbench is the query execution time as measured at the client side, so it will include network travel time. But here you also have the timing as instrumented by the server itself. This includes the amount of time waiting for table locks, as a separate value.
  • Rows Processed: the number of rows that were evaluated and then generated to be sent back to the client
  • Temporary Tables: the number of temporary tables that had to be created for the query to be executed
  • Joins per Type: the number of JOINs that were executed for the query, broken down by type. This is similar to the info you’d get from EXPLAIN.
  • Sorting: number of data that had to be sorted by the server.
  • Index Usage: you can see here whether table scans had to be performed without using an index.

You can disable fetching of this information from the Query -> Collect Performance Schema Stats menu item. You may want to do that if you don’t need the stats, since an extra query has to be executed for every query you run.

 

 

MySQL Workbench: Vertical Query Output

MySQL Workbench have one nice feature which is probably a stranger for some of us. The name of this feature is vertical query output, it help in situations where the standard Workbench output will not be very useful. This functionality is very easy to use and in this post I’ll try to visualize some of it’s benefits.

First we need to know how to use it, so we’ve provided you two options to execute the query with vertical output. One of them is the menu bar where you can find item named Execute vertically, you’ll also find hint about the shortcut for that option it’s CTRL+ALT+RETURN.

After you know how to get the vertical query output, I’ll show you some screen shots to compare it with command line output.

Let’s take the command that suits best to this type of output, it’s SHOW ENGINE INNODB STATUS. Normally to understand the output, you probably copy it to some notepad app, and add line breaks. Well it was a little annoying, especially when you know how does it look in command line client with \G. So let’s take a look for the output of console  and Workbench.

Vertical output console preview Vertical Output Workbench preview

You should find out that it’s the same view as in the console. Below you’ll see how it looks in Standard Output

vertical_output_show_engine_normal

and with Text Output.

vertical_output_show_engine_text

Here is also one more screen shot of the EXPLAIN query:

vertical_output_explain

Please fell free to comment this, and let us know how do you like it.

MySQL Workbench 6.0: Help is on the way…

Do you know this scenario: you are writing down  a stored procedure but you can’t for the life of you remember the exact syntax of that CASE statement? Has it to end with CASE or not? Can I use more than one WHEN part and how should that be written? Usually you end up opening a web page and read through the excellent MySQL online docs. However, this might cost too much time if you quickly need different statements and other detail info. Here’s where MySQL Workbench’s context help jumps in.

Continue reading “MySQL Workbench 6.0: Help is on the way…”

MySQL Workbench 6.0: Table Data Search

scr 1. Location of Search table data on the main toolbar

One of the new features of MySQL Workbench 6.0 is Table Data Search. The main purpose of this was to ease data searching through the whole instance. Previously, we needed to use some tricks to get the query to run over all schemas that we’ve got on the server. Now it’s easy to find the searched term with much less hassle. This functionality is easy to use and provides searching through all columns and even all types. However, we can’t forget that due to the nature of this tool we must take some precautions to not overload your server.

To use this functionality we pick it up from the Database menu called “Search Table Data…” or just click the icon on the main toolbar (scr 1). The third option is to select Search Table Data.. from context menu when you right click on the schema list on some schema.
After that you will see the new screen (scr 2) with a few options which you must provide to get started working with it.

scr 2. Search table data window

As you can see, the interface is very simple, but I’ll still try to explain some of the options. The first thing is the Search for Text input, where you just put the phrase that you’d like to find. The type of the phrase depends on the select box that is located below this input (scr 3). That select box has three different options of search type which are:

Search using =
Search using LIKE
Search using REGEXP

The first one Search using =, is the simplest one, it just matches fields using the = operator.
Second option is little more powerful, it allows you to search using the database LIKE  operator where you can provide wild cards like % (match any character any number of times) or _ (match any char, a single time).
The third option allows you to use regular expressions.

scr 3. Search table data match options

Next to the selection box, you’ll see two inputs described as Max. matches per table, and Max. total matches. The first one is responsible for limiting search occurrence through one table. The second one will limit the whole search, so when there will be over 10000 (initial value) entries, search will stop. Last option is the check box named Search columns of all types. Initially searching is done through only text fields, when this option is enabled, then all columns will be used and will be cast to the char data type. This check box have a great impact over whole server performance use it with caution!

Now that you know how each option relates to the searching, I’ll try to step through a sample search and describe the results. I assume that you’ve got sakila database, cause I’ll make a sample using that database.  

  1. open the Search table data…
  2. enter text mary into the Search for Text field
  3. check if the Search using option is set to the equal sign
  4. check if the Search columns of all types check box is unchecked
  5. select the schema (or column), that you’d like to be searched for the matching phrase in the schema selector
  6. press the Start Search button.
scr 4. Search table data results view

As in the screen shot (scr 4), your result should be the same. You can click on the arrow in the result list to expand details of the row. The columns are as follows:

Schema – name of the database that holds the columns that matched the criteria
Table – name of the table
Key – primary key value assigned to the data that match criteria
Column – column name that holds the matched data
Data – the top row will contain information, how many times the phrase were matched in the details it will contains the matched data

There is also context menu for the result set, it’s available when you right click on the result set. The menu allows you to copy query that where used to find the matching rows. you can also copy query that will match the rows against primary key, and the last option allows to copy the key values.

And that’s all, you’ve done your first search using the new Search Table Data option. Please remember that using this feature have very big impact on general server performance because you’re generally doing full table scans. Stay with us to get more information cool information.