MySQL Workbench 6.2: New Migration Source and Sync Feature

MS Access Migration

MS Access support was added to the Migration Wizard in MySQL Workbench 6.2, joining MS SQL Server, Sybase ASE, Sybase SQL Anywhere, PostgreSQL, SQLite and “Generic ODBC” as migration sources. You can read more about it in How-To: Guide to Database Migration from MS Access using MySQL Workbench.

Include Model Scripts in Forward Engineering and Synchronization

Workbench modeling has always supported attaching SQL script files to the model, usually for documentation/organization purposes. You can now include these attachments to the output script when performing forward engineering or synchronization. Screenshot 2014-09-05 14.55.35

Resume data copy in Migration Wizard. If a data copy fails during database migration (because of a timeout or network failure, for example), you can now click Resume to retry the data copy. Workbench will find the last row that was copied successfully and try to restart the copy from that row.

MySQL Workbench 6.2: Fabric and Client Connection Browser

MySQL Fabric Support

MySQL Fabric servers can now be added to the Workbench home screen. When clicked, these connections will dynamically query the Fabric server and individual connections for all the managed MySQL servers will be created. You can then connect to each instance as usual. Screenshot 2014-09-08 15.24.30

Metadata Lock Browser

MySQL uses metadata locking to manage access to objects (tables, triggers, and so forth). Sometimes that can be puzzling, as your query may block waiting on an object being manipulated by another connection from maybe another user. The Client Connections list was updated to take advantage of the metadata lock information provided in the performance_schema starting in MySQL 5.7.3, to show information about what locks a connection is waiting for and what it holds. client_connections_locks

Updated Client Connection Browser

Speaking of the Client Connection browser, a neat feature added in MySQL 5.6 is the connection attribute dictionary, which includes handy things like the name of the clients that are connected (as long as the client supports it). You can access that by clicking the Show Details button in the Client Connection screen. client_connections

MySQL Workbench 6.1: Updating accounts using the old (pre-4.1.1) authentication protocol

In MySQL each ‘user’ has its own password hash. To provide better security, pasword hashes were extended from 16 to 41 bytes in MySQL 4.1.

This change created a situation. If the user was created prior to version 4.1 and the server updated to a newer version, the password hash that was stored in the database is left in the old, deprecated format. This is because MySQL doesn’t store passwords in plain text so there’s no way to automatically regenerate a password hash. For this case, we consider two scenarios:
– If the secure_auth server option is disabled, you can login and update your password. You may also need to enable the allow old_password option in the Workbench advanced options tab for the connection.
– If secure_auth is enabled, you do not have possibility to log in to the database and the only thing you can do is to disable that option or log in as different user (such as root) to change the password. In this case, attempt to log on to the server may fail with message:
“ERROR 2049 (HY000): Connection using old (pre-4.1.1) authentication protocol refused (client option ‘secure_auth’ enabled)”
as in the screenshot below:

login_to_server

or, if you try log on in the Workbench, like this:

wb_login_to_server

In Workbench 6.1 an easy way to update your account to a format compatible with the new type of authentication has been added. Just log in to your account or some account that has permissions to manage other users (eg root) and go to the ‘Users and Privileges’ tab. Select the user whose account you want to update from the user list on the left. You should see ‘This account is using the pre-mysql-4.1.1 password hashing type. (…)’ in red in the lower right corner of the screen and the button [Upgrade] on the right, as the screenshot below:

upgrade_account

Enter a new or the current password and click the [Upgrade] button. After this operation, the user can login to his account without any further problems.

If you’re using a server 5.6 or later, we also offer a second way – generating a random password and marking it as expired. For this purpose, after selecting the user, leave the ‘password’ and ‘confirm a password’ fields untouched and immediately click on the [Upgrade] button. When you see the message shown in the screenshot below:

reset_to_expired

click on the [Reset to expired] button, it will generate a new random password, update your account and mark it as expired. The user will be able to log in to his account using the new password, but before being able to do anything else, they will need to change his password first.

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: Performance Schema Reports

The Performance Schema Reports feature from MySQL Workbench show summaries of the many statistics gathered by the MySQL performance_schema. By inspecting these reports, you can get some insight of what’s happening in a MySQL server in aspects such as:

  • I/O by amount of data
  • I/O by latency/time spent
  • Index usage
  • Performance critical operations performed by queries of the same type (table scans, index scans, temporary tables, sorts etc)

The MySQL SYS Schema

MySQL 5.5 introduced the performance_schema feature. performance_schema contains tables that log server performance and activity statistics. You can inspect it to have a clearer understanding about what kind of work is the server doing, how much time is spent doing that, resources used globally or by individual queries etc. MySQL 5.6 expanded on it and adds even more information, making it a lot more powerful.

However, the performance_schema tables mostly contain raw data and require some processing and SQL magic to make it palatable so you can get the most of it. And that is what the new SYS schema project provides. The SYS schema has many views that process data in performance_schema providing high-level reports and stored procedures that make it easier for advanced users to work with the performance_schema.

The SYS schema is bundled in MySQL Workbench 6.1 and is – after confirmation – automatically installed in supported MySQL servers. Currently, only MySQL 5.6 and 5.7 are supported, because the performance_schema in MySQL 5.5 still lacked maturity and did not yet provide enough information to be very useful.

sys_missing

Requirements

To view the reports, the SYS schema must be installed and the appropriate P_S instrumentation has to be enabled. If not, Workbench will prompt you to click a button to enable it.

ps_enable_instrumentation

The required instrumentation is (if you choose to change it manually):

  • current and history_long consumers must be enabled for all events
  • all wait instruments must be enabled and timed
  • all stage instruments must be enabled and timed
  • all statement instruments must be enabled and timed

Reports

The currently available reports are listed below. As more views are added to the SYS schema, Workbench will also be updated to list them.

  • Top File I/O Activity Report
    Show the Files doing the most IOs in bytes
  • Top I/O by File by Time
    Show highest IO usage by file and latency
  • Top I/O by Event Category
    Show the highest IO Data usage by event categories
  • Top I/O in Time by Event Categories
    Show the highest IO time consumers by event categories
  • Top I/O Time by User/Thread
    Show the top IO time consumers by User/thread
  • Statement Analysis
    Lists statements with various aggregated statistics
  • Statements in Highest 5 Percent by Runtime
    List all statements who’s average runtime, in microseconds is in highest 5 percent
  • Using Temp Tables
    Lists all statements that use temporary tables – access the highest # of disk temporary tables, then memory temp tables
  • With Sorting
    List all normalized statements that have done sorts, access in the following priority order – sort_merge_passes, sort_scans and sort_rows
  • Full Table Scans
    Lists statements that have performed a full table scan. Access query performance and the where clause(s) and if no index is used, consider adding indexes for large tables
  • Errors or Warnings
    List statements that have raised errors or warnings.
  • Schema Object Overview (High Overhead)
    Shows count by object type for each schema.
    Note: On instances with a large number of objects, this can take some time to execute.
  • Schema Index Statistics
  • Schema Table Statistics
  • Schema Table Statistics (with InnoDB buffer)
  • Tables with Full Table Scans
    Find tables that are being accessed by full table scans ordering by the number of rows scanned descending
  • Unused Indexes
    List of indexes that were never used since the server started or since P_S data collection started.
  • Waits by Time
    Lists the top wait events by their total time, ignoring idle (this may be very large)
  • Waits by User by Time
    Lists the top wait events by their total time, ignoring idle (this may be very large)
  • Wait Classes by Time
    Lists the top wait classes by total time, ignoring idle (this may be very large)
  • Waits Classes by Average Time
    Lists the top wait classes by average time, ignoring idle (this may be very large)
  • InnoDB Buffer Stats by Schema
    Summarizes the output of the INFORMATION_SCHEMA.INNODB_BUFFER_PAGE table, aggregating by schema
  • InnoDB Buffer Stats by Table
    Summarizes the output of the INFORMATION_SCHEMA.INNODB_BUFFER_PAGE table, aggregating by schema and table name

 

Screenshot 2014-03-09 15.37.08

MySQL Workbench 6.0.9 GA has been released

The MySQL developer tools team at Oracle is excited to announce the availability of MySQL Workbench 6.0.9.

MySQL Workbench 6.0 is the GUI Development and Administration tool for MySQL.

Changes in MySQL Workbench 6.0.9 (2014-02-05)

This section documents all changes and bug fixes applied to MySQL Workbench since the release of 6.0.8.

Functionality Added or Changed

  •  The restore operation can restore to a different schema.

Bugs Fixed

  •  MySQL Workbench now builds on Debian 7.0-unstable.
  •  Ubuntu 13.10 is now supported.
  •  Fedora 20 is now supported
  •  On Linux, the Cancel button was written as “Cance” on some systems, depending on the GTK theme used on the system.
  •  Sometimes, when exporting an EER diagram to SQL, the generated SQL code did not include “DROP SCHEMA” statements even with  the Generate DROP SCHEMA option checked.
  •  One tab could execute the query from a different tab, after swapping tabs.
  •  On Linux, building MySQL Workbench from source would generate many glib related warnings with newer versions of glib.
  •  After creating a new view inside a model, clicking Add View would display a new view in the editor this new could not be saved.
  •  The –routines command line option was missing from the generated mysqldump command when multiple schemas were selected for a dump into a single file backup when the routines option was enabled. This could happen after executing the dump multiple times.
  •  Executing Model, Forward Engineering with the Do not create users, only export privileges option enabled would recreate the user instead of simply changing privileges for the existing user.
  •  On Linux, renaming foreign keys and indexes would crash MySQL Workbench.
  •  On Mac OS X, switching colors with layers and tables several times could eventually cause a crash.
  •  Adding a new table to an empty EER diagram would crash MySQL Workbench.
  •  When building from source and without building the documentation, the documentation link will open the online MySQL Workbench manual.
  •  The Inserts tab under Forward Engineering would sometimes unexpectedly fail.
  •  The ENUM type now corresponds better to its definition better under Model, User Defined Types, to allow arguments.
  •  On Microsoft Windows and Mac OS X, the bundled PyCrypto library was upgraded to version 2.6.1.
  •  MySQL Workbench could crash when using a MySQL connection that connects via an SSH tunnel with success, but is defined using incorrect MySQL server credentials. A dialog now requests the correct information.
  •  The catalog treeview for an EER diagram was replaced by a central control with improved handling, which allows MySQL Workbench  to worth more smoothly with a large number of tables.
  •  Indexes that were auto-created for a Foreign Key can now be deleted in case another suitable index was available.
  •  Under certain scenarios, the File, Print to File… action would not allow models to be unchecked.
  •  Model synchronization would complete, but if repeated then MySQL Workbench would ask to update the database again.
  •  Changing the collation type would not always be detected, as “No changes detected” would be reported after attempting to apply the change.
  •  The MySQL Forums link on the MySQL Workbench home page was updated, and it now links to a MySQL Workbench page that also  links to the MySQL Workbench – Database Migration forum, when before it was difficult to find.
  •  From Database, Reverse Engineer, and after selecting a schema with multiple tables, clicking the left or right arrow buttons with the Show Filter feature to include/exclude tables would lose the cursor position, and always move the cursor to the top of the list.
  •  For models, tables can now be renamed by double-clicking the name column, and the columns edited.
  •  A large SQL script file could not be saved for a model, although a limit of around 200MB limit still exists.
  •  MySQL Workbench could crash after choosing Recover when prompted to recover a model file from a previous session after MySQL Workbench was not closed properly.
  •  Applying an large SQL Script object to a model could crash MySQL Workbench.
  •  The “Scratch” SQL editor tabs were removed, and instead standard SQL editor tabs are always opened.
  •  Editing a mandatory relationship now updates the referenced column.
  •  The Copy and Paste keyboard shortcut did not always function from within table cells in all environments.
  •  The Schema Privileges tab was hidden in the Schema editor, and is now more viewable.

and much more. For a detailed overview of what’s new in MySQL Workbench 6.0, please visit:
http://dev.mysql.com/doc/workbench/en/wb-what-is-new.html

For the full list of bugs fixed in this revision, visit
http://dev.mysql.com/doc/relnotes/workbench/en/changes-6-0.html

For discussion, join the MySQL Workbench Forums:
http://forums.mysql.com/index.php?152

Download MySQL Workbench 6.0 now, for Windows, Mac OS X 10.6+, Oracle Linux 6, Fedora 19, Fedora 20, Ubuntu 12.04 and Ubuntu 13.10 or sources, from:
http://dev.mysql.com/downloads/tools/workbench/

In Windows, you can also use the integrated MySQL Installer to update MySQL Workbench and other MySQL products. For RPM  package based Linux distributions, you can get and update Workbench using the new MySQL RPM repository at:

http://dev.mysql.com/downloads/repo/

Quick links:
– Download: http://dev.mysql.com/downloads/tools/workbench/
– Bugs: http://bugs.mysql.com
– Forums: http://forums.mysql.com/index.php?152

Read more about MySQL Workbench 6.0 and some of its features in

MySQL Workbench 6.0: What’s New:

http://mysqlworkbench.org/2013/06/mysql-workbench-6-0-whats-new/

 

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, Windows XP and SSH public key auth.

It happens that sometimes you need to access a remote box which supports ssh key authentication. Recently I was trying to reproduce a bug related to SSH public key authentication, so here I would like to share some of my experience.

There will be no explanation of the public key authentication itself here, rather the actual setup and steps to have a public key auth for Windows(client) -> Linux(server) working. Why Windows you would ask? Because interactions for Linux->Linux and for Mac OS X -> Linux simply work using the Unix way, while for Windows you may need some extra actions to do.

 

Setup

What I had at endpoints:

    Linux – Ubuntu 11.04, sshd is set up to deny password auth.
    Windows – well, it is an XP SP3 i386 box. MySQL Workbench 5.2.34+ is installed

First of all I created an encrypted pair of RSA keys, using Linux box’s ssh-keygen. After that the public key was added to ~/.ssh/authorized_keys and the private one was moved to the Windows box.

Naturally my first attempt was to simply specify path to the private key file in the server settings, just as I would do in Linux or OS X.

 

Remote management section

 

That did not work, just as the bug report had said. Moving key to $HOME/.ssh/id_rsa did not help. Could that be that paramiko can not handle openssh keys on Windows, or openssh’s encryption method?

 

Error message on connect via SSH public keys

 

Bazaar has similar issues on Windows, the solution they suggest is to either put keys into .ssh dir, or use pageant tool from PuTTY.  I tried .ssh, that did not work. So the latter way turned into conversion of the openssh private key into PuTTY ppk format. The conversion is done using PuTTYgen, then the key is loaded in the pageant. More details are given in the mentioned bazaar guide Bzr and SSH.

And this worked! Let me sum up the steps:

    1. Generate keys, using either openssh on Linux, OSX, Cygwin, or using PuTTYgen;
    2. Specify private ssh key in the appropriate section of the Workbench’s “Server Instance Editor”;
    3. Add key to pageant tool.
    4. At this moment passwords to unlock keys have to be entered both in MySQL Workbench and the pageant.
    5. Use it…