A.1: 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?
A.2: How does MySQL Workbench increase import performance?
A.4: I'm forced to use MySQL Workbench 5.2.x, is its documentation available?
Questions and Answers
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.
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:
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
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
TRADITIONAL, causing the
server to operate in a more restrictive mode, and
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.3: 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.
Although the 5.2.x branch is no longer maintained, its documentation is archived at http://dev.mysql.com/doc/index-archive.html.