Appendix A MySQL Workbench Frequently Asked Questions

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?

From the schema navigator, hover over the table and click the icon. This executes a "SELECT * FROM schema.table" query and loads the results into the result grid. From there you can view or edit the data.

Alternatively, right-click on a table and select Select Rows - Limit 1000 form the context menu.

Workbench Functionality

Questions

Questions and Answers

A.1: 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.2: How can I view my MySQL Workbench query history?

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

Alternatively, the SQL statement history is stored as plain text on your system. To find these text files, from the main Workbench navigation menu choose Help and then Show Log Files. The files prefixed with "sql_actions_" followed by MySQL connection information store this information. In other words, the query history is split into separate files per MySQL connection. Open these files to view the MySQL Workbench query history.

A.3: 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.4: 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?

The results view panel in Workbench has an "Import records from external file" option that imports CSV data directly into the result set. Execute that on your CSV file and click Apply to review and commit the import.

Note

This is different than the Data Import wizard that imports your saved MySQL files to your MySQL server. For additional information about that, see Section 5.5, “Database 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 Section 7.1.9, “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, “Database 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, “Database 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.