Appendix A MySQL Workbench Frequently Asked Questions

Question

A.1.

What is a MySQL connection? Why might I need to create more than one?

Answer 1

A MySQL connection links (connects) Workbench to a MySQL server. Most actions performed within Workbench are then performed against the connected MySQL server. Each MySQL connection contains its own set of definitions, so you might define multiple MySQL connections in Workbench. For example, the connections might connect to different MySQL servers, or the same MySQL server with different user names, or enable SSL for one, or you might set up a connection to a remote MySQL server (on your web host?) using the SSH options, and so on.

As for multiple connections to the same local MySQL server, you might have one connection using "root" with another using a less privileged user. Depending on how you set up the users, they may (or may not) both have rights to see and use the same databases (information). For example, you might use Workbench to configure and use the less-privileged user that you use for your web application.

So to summarize, connections simply connect to the MySQL server. If two connections use the same exact information then the results in Workbench should be identical, although this is not a common use case. For additional information about MySQL connections in MySQL Workbench, see Chapter 5, Connections in MySQL Workbench.

Answer 2

Because TLSv1.2 requires OpenSSL, support for this protocol is available for MySQL Workbench Commercial Editions, and not for the Community Edition (which is compiled using yaSSL and supports TLSv1.1 only).

Answer 3

Open a MySQL connection to open the SQL editor.
On the left pane there is an Object Browser that contains two tabs titled Management and Schemas. Choose the schemas tab (default).
Right-click anywhere in the Schemas pane and choose Create Schema from the context-menu.
Follow the schema creation wizard by naming your new schema, and click Apply to create your new schema.

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.

Answer 4

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.

Answer 5

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.3.5, “SSL Wizard (Certificates)”.

Answer 6

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.

Answer 7

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.

Answer 8

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).

Answer 9

The Webkit system library is used on macOS, 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.

Answer 10

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:

SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;: Determines whether InnoDB performs duplicate key checks. Import is much faster for large data sets if this check is not performed. For additional information, see unique_checks.
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;: Determines whether the server should check that a referenced table exists when defining a foreign key. Due to potential circular references, this check must be turned off for the duration of the import, to permit defining foreign keys. For additional information, see foreign_key_checks.
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL';: Sets SQL_MODE to TRADITIONAL, causing the server to operate in a more restrictive mode, and ALLOW_INVALID_DATES, causing dates to not be fully validated.

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;

Answer 11

Data in the query results grid is only editable when the query results includes a primary key. For example, SELECT type FROM food is read-only if type is not a primary key, but SELECT id, type FROM food is 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, move the mouse pointer over the Read Only icon to reveal a tooltip that explains why your result set is in read-only mode.

Answer 12

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.

Answer 13

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.

Answer 14

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

Move the pointer over an acronym to view a description, and see Section 8.1.10.2, “Columns Tab”, and the documentation for the CREATE TABLE for additional details.

Answer 15

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 6.5, “Data Export and Import”.

Answer 16

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 6.5, “Data Export and Import”.

Answer 17

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 6.5, “Data Export and Import”.

Answer 18

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

A.1.	What is a MySQL connection? Why might I need to create more than one?
	A MySQL connection links (connects) Workbench to a MySQL server. Most actions performed within Workbench are then performed against the connected MySQL server. Each MySQL connection contains its own set of definitions, so you might define multiple MySQL connections in Workbench. For example, the connections might connect to different MySQL servers, or the same MySQL server with different user names, or enable SSL for one, or you might set up a connection to a remote MySQL server (on your web host?) using the SSH options, and so on. As for multiple connections to the same local MySQL server, you might have one connection using "root" with another using a less privileged user. Depending on how you set up the users, they may (or may not) both have rights to see and use the same databases (information). For example, you might use Workbench to configure and use the less-privileged user that you use for your web application. So to summarize, connections simply connect to the MySQL server. If two connections use the same exact information then the results in Workbench should be identical, although this is not a common use case. For additional information about MySQL connections in MySQL Workbench, see Chapter 5, Connections in MySQL Workbench.
A.2.	Does MySQL Workbench support the TLSv1.2 protocol?
	Because TLSv1.2 requires OpenSSL, support for this protocol is available for MySQL Workbench Commercial Editions, and not for the Community Edition (which is compiled using yaSSL and supports TLSv1.1 only).
A.3.	How do I create a MySQL database (schema) in MySQL Workbench?
	Open a MySQL connection to open the SQL editor. On the left pane there is an Object Browser that contains two tabs titled Management and Schemas. Choose the schemas tab (default). Right-click anywhere in the Schemas pane and choose Create Schema from the context-menu. Follow the schema creation wizard by naming your new schema, and click Apply to create your new schema. 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.4.	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.

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.3.5, “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 macOS, 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.
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: `SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;`: Determines whether `InnoDB` performs duplicate key checks. Import is much faster for large data sets if this check is not performed. For additional information, see `unique_checks`. `SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;`: Determines whether the server should check that a referenced table exists when defining a foreign key. Due to potential circular references, this check must be turned off for the duration of the import, to permit defining foreign keys. For additional information, see `foreign_key_checks`. `SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL';`: Sets SQL_MODE to `TRADITIONAL`, causing the server to operate in a more restrictive mode, and `ALLOW_INVALID_DATES`, causing dates to not be fully validated. 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;`

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` is read-only if `type` is not a primary key, but `SELECT id, type FROM food` is 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, move the mouse pointer 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, G) in the MySQL Workbench Table Editor mean?
	Checking these boxes alters the table column by assigning the checked constraints to the designated columns. Move the pointer over an acronym to view a description, and see Section 8.1.10.2, “Columns Tab”, and the documentation for the `CREATE TABLE` for additional details.

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 6.5, “Data Export and Import”.
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 6.5, “Data Export and Import”.
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 6.5, “Data Export and Import”.