Appendix A MySQL Workbench Frequently Asked Questions

FAQ Categories

Basic Usage

Questions

Questions and Answers

A.1: How do I create a MySQL database (schema) in MySQL Workbench?

Other options include clicking the "Create Schema" icon on the main navigation bar, or executing a "CREATE SCHEMA your_db_name" query in the SQL editor.

A.2: Is there an easy way to select all data from a table, and then see the results?

Workbench Functionality

Questions

Questions and Answers

A.1: How do I use the SSL Certificate wizard to enable SSL for both my MySQL server and MySQL client?

Execute the wizard to generate the SSL certificates, and then modify your MySQL server's configuration file (my.cnf or my.ini) accordingly. You can copy-n-paste entries for the SSL options from the generated sample-my.cnf sample file. Next, confirm that the SSL CA File, CERT File, and Key File values are properly set under the SSL tab for your MySQL connection. Set Use SSL to either Require (recommended) or If available, and then execute Test Connection. This should report that SSL is enabled.

Failed SSL connections are logged in the MySQL Workbench log file. For additional information about the log file's location, see Section 3.3, “MySQL Workbench Settings and Log Files”.

For additional information, see Section 5.1.2.3.4, “SSL Wizard (Certificates)”.

A.2: How do I copy my saved MySQL connections in Workbench to a different computer?

From the main navigation menu, choose Tools, Configuration, and then Backup Connections to create a Zip file with your configured MySQL connections. Next, load this file into your new Workbench instance by using the related Restore Connections option.

A.3: How can I view my MySQL Workbench query history?

In bottom pane, change Action Output to History and then choose the appropriate date.

The SQL statement history is stored as plain text on your system under your user's MySQL Workbench configuration path in the sql_history directory. These files are organized per date (such as 2014-01-15) and contain your MySQL Workbench SQL statement history for all MySQL connections.

A.4: Can I preserve a results tab rather than have it refresh every time I execute a statement?

Yes, you can pin the results tab to force it to remain and be unaffected by UPDATE and other statements. Do that by right-clicking the result tab and choose "Pin Tab" from the context-menu, or left-click the little pin icon to toggle it. Now, execute your other queries and then refresh the pinned tab (there is a "refresh" icon in the result grid's menu).

A.5: How does the embedded web browser functionality work? For example, clicking Workbench Forum on the Home screen opens the forum in its own embedded MySQL Workbench tab.

The Webkit system library is used on OS X, Internet Explorer is used on Windows, and Linux opens the default browser externally rather than an embedded browser. Pressing Modifier + Arrow moves the browser history forward and back.

Additionally, for information about creating your own Home screen links, see Section 3.4, “Tutorial: Add a Custom Link to the Home Page”.

A.6: How does MySQL Workbench increase import performance?

When a model is exported (Database, Forward Engineer...), some MySQL server variables are temporarily set to enable faster SQL import by the server. The statements added at the start of the code are:

SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0; 
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0; 
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL,ALLOW_INVALID_DATES'; 

These statements function as follows:

These server variables are then reset at the end of the script using the following statements:

SET SQL_MODE=@OLD_SQL_MODE; 
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS; 
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS; 

Workbench Behavior

Questions

Questions and Answers

A.1: Why do my query results sometimes say Read Only but other times I can edit data in the results grid?

Data in the query results grid is only editable when the query results includes a primary key. For example, "SELECT type FROM food" will be read-only if "type" is not a primary key, but "SELECT id, type FROM food" will be editable when "id" is a primary key. Typically, "SELECT *" syntax is used in Workbench which often includes query results with a primary key.

For additional information, hover over the "Read Only" icon to reveal a tooltip that explains why your result set is in read-only mode.

A.2: I'm attempting to execute a DELETE query but the query fails with an "Error Code: 1175" error. How do I proceed?

By default, Workbench is configured to not execute DELETE or UPDATE queries that do not include a WHERE clause on a KEY column. To alter this behavior, open your Workbench Preferences, select the SQL Editor section, and disable the following preference:

[ ] "Safe Updates". Forbid UPDATEs and DELETEs with no key in WHERE clause or no LIMIT clause.

Changing this preference requires you to reconnect to your MySQL server before it can take affect.

A.3: My MySQL server connection is timing out with an error like "Error Code: 2013. Lost connection to MySQL server during query". Can I adjust the timeout?

Yes, go to Preferences, SQL Editor, and adjust the DBMS connection read time out option that defaults to 600 seconds. This sets the maximum amount of time (in seconds) that a query can take before MySQL Workbench disconnects from the MySQL server.

A.4: What do the column flag acronyms (PK, NN, UQ, BIN, UN, ZF, AI) in the MySQL Workbench Table Editor mean?

Checking these boxes will alter the table column by assigning the checked constraints to the designated columns.

Hover over an acronym to view a description, and see the Section 7.1.11.2, “The Columns Tab” and MySQL CREATE TABLE documentation for additional details.

Data Management

Questions

Questions and Answers

A.1: How do I import comma-separated values (CSV) data into MySQL using Workbench?

Importing CSV data into a new or existing table: the Table Data Import wizard imports configurable CSV data into a new or existing table. This option was added in MySQL Workbench 6.3.

Importing CSV data into a result set: the Import records from external file wizard imports CSV data directly into a result set's view.

Alternatively, the Data Import wizard imports your saved MySQL files into your MySQL server. For additional information, see Section 5.5, “Data Export and Import”.

If you are importing Excel files, then consider using the official MySQL for Excel Add-on for Excel.

A.2: How do I export MySQL data to a plain text file with a format such as CSV, JSON, or XML?

The results view panel in Workbench has an "Export recordset to an external file" option that exports your result set to a wide variety of formats. For additional information, see Export a Result Set.

Note

This is different than the Data Export wizard that exports your MySQL data to standard MySQL formats. For additional information about that, see Section 5.5, “Data Export and Import”.

If you are exporting to Excel, then consider using the official MySQL for Excel Add-on for Excel.

A.3: How to export (save) a MySQL database to a text file?

Open a MySQL connection, and select Server from the main navigation menu and choose Data Export to open the data export wizard. Alternatively, choose Data Export from the left Management pane for the desired MySQL selection.

Here you can choose which databases to export, whether or not to include the data, dump to a single file or multiple files (one per table), and more. For additional details, see Section 5.5, “Data Export and Import”.

General

Questions

Questions and Answers

A.1: I'm forced to use MySQL Workbench 5.2.x, is its documentation available?

Although the 5.2.x branch is no longer maintained, its documentation is archived at http://dev.mysql.com/doc/index-archive.html.