Messages and limitations of the MySQL connector

Tutorials

For details about how to create a connection to a MySQL database, refer to the following tutorial:

Create a connection to a MySQL database

Message code list of MYSQL category
Exception messages of MYSQL category
Limitations of MYSQL category

For operations, refer to Common (the top page for operations).

Message code list of MYSQL category

The following message codes correspond to errors that occur in MySQL Connector.

MYSQL0000E

This message code is output when an undefined error occurs.

Cause	Solution
An undefined error occurred.	Check the error message or the help of the operation in which the error has occurred.

Corresponding error

None

MYSQL0001E

This message code corresponds to errors that occur during database processing.

Cause	Solution
The cause differs according to each error message.	Check the error message and perform the following: Check whether the cause/solution of the error is described in "Exception messages" below. Check whether the cause/solution of the error is described in Limitations of the applied connector. Check whether you can find information related to the error in documents of the connected database.

Cause

Solution

The cause differs according to each error message.

Check the error message and perform the following:

Check whether the cause/solution of the error is described in "Exception messages" below.
Check whether the cause/solution of the error is described in Limitations of the applied connector.
Check whether you can find information related to the error in documents of the connected database.

Corresponding error

java.sql.SQLException
com.mysql.jdbc.exceptions.MySQLSyntaxErrorException
com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException
java.sql.SQLSyntaxErrorException

MYSQL0002E

This message code is output when a database connection failed.

Cause	Solution
Can't connect to the database.	Check Hostname and Port number.

Corresponding error

com.mysql.jdbc.CommunicationsException
com.mysql.jdbc.exceptions.jdbc4.CommunicationsException
com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException
com.mysql.cj.jdbc.exceptions.CommunicationsException
java.sql.SQLNonTransientConnectionException

Exception messages of MYSQL category

Exception name	Cause	Solution
InputDataNotFoundException	Input data isn't specified.	Specify Input data or draw data flow.
InvalidPropertyConfigurationException	SQL statement isn't set.	Specify SQL statement.
InvalidPropertyConfigurationException Target column is not selected.	Target for Schema definition isn't selected in the Write Table operation.	Select one or more Target for Schema definition.
InvalidPropertyConfigurationException Target column is not selected.	Target for Schema definition isn't selected in the Read Table operation.	Select one or more Target for Schema definition.
InvalidPropertyConfigurationException	Table name isn't set.	Specify Table name.
InvalidPropertyConfigurationException	Procedure isn't set.	Specify Procedure.
java.sql.SQLException	A database access error or some other one has occurred.	Check the SQLException message. SQLException message is based on the specifications of the driver. The cause of the error might be output in SQLException message.
ResourceNotFoundException Resource definition could not be found. Name: []	Destination isn't specified.	Specify Destination.
ResourceNotFoundException Resource definition is not found. Name: [<connection resource name>]	The resource definition selected in Destination isn't found.	Check the connection resource specified in Destination.
com.mysql.cj.jdbc.exceptions.CommunicationsException	Can't connect to the database.	Check Host name or Port number of the connection resource.
java.sql.SQLNonTransientConnectionException	Can't connect to the database.	Check Host name or Port number of the connection resource.
java.sql.SQLSyntaxErrorException Unknown database '<database name>'	The database doesn't exist.	Specify an existing database in Database name of the connection resource.
java.sql.SQLException Access denied for user '<user name>'@'<terminal name>' (using password: YES)	The user name or the password is incorrect.	Check User name or Password of the connection resource.
java.sql.SQLSyntaxErrorException Table '<table name>' doesn't exist	The table doesn't exist.	Specify an existing table in Table name.

Limitations of MYSQL category

Specification limits of MySQL 8.4

Supported types
Read Table
Execute Select SQL
Write Table
Execute Update SQL
Execute Stored Procedure
Select SQL Wizard
Update SQL Wizard

Supported types

This connector supports the following types.

Type name	Support	Remarks
BOOL (BOOLEAN)	Yes	Alias of tinyint(1)
BIT	Limited
TINYINT	Yes
TINYINT UNSIGNED	Limited
MEDIUMINT	Yes
MEDIUMINT UNSIGNED	Limited
SMALLINT	Yes
SMALLINT UNSIGNED	Limited
INT (INTEGER)	Yes
INT UNSIGNED (INTEGER UNSIGNED)	Limited
BIGINT	Yes
BIGINT UNSIGNED	Limited
FLOAT	Limited
FLOAT UNSIGNED	Limited
DOUBLE (REAL)	Limited
DOUBLE UNSIGNED (REAL UNSIGNED)	Limited
DECIMAL (NUMERIC)	Yes
DECIMAL UNSIGNED (NUMERIC UNSIGNED)	Limited
DATE	Limited
DATETIME	Limited
TIMESTAMP	Limited
TIME	Limited
YEAR	Limited
CHAR	Yes
VARCHAR	Yes
BINARY	Yes
VARBINARY	Yes
TINYBLOB	Yes
TINYTEXT	Yes
BLOB	Yes
TEXT	Yes
MEDIUMBLOB	Yes
MEDIUMTEXT	Yes
LONGBLOB	Yes
LONGTEXT	Yes
ENUM	Yes
SET	Yes
JSON	No

Yes: Supported.
Limited: Supported with limitations.
No: Not supported.

Read Table

Specification limits

BIT
- BIT type data whose size is 1 is handled as boolean. Other sized BIT type isn't supported.
FLOAT, FLOAT UNSIGNED
- There are precautions for the precision with MySQL floating points.
  
  = Remarks =
  For details, refer to "MySQL Reference Manual" (https://dev.mysql.com/doc/).
DOUBLE, DOUBLE UNSIGNED
- There are precautions for the precision with MySQL floating points.
  
  = Remarks =
  For details, refer to "MySQL Reference Manual" (https://dev.mysql.com/doc/).
DATE, DATETIME, TIMESTAMP
- An error occurs when data is 0000-00-00 00:00:00.
TIME
- The supported range is from 00:00:00 to 23:59:59. If a value outside this range is set, an error may occur.
YEAR
- Data is handled as String.
If a table name is enclosed by quotation marks, whether the table name is case sensitive depends on the OS on which the database is running.
Even when an optional extended attribute ZEROFILL is specified, the result is returned excluding 0.
The default value for Fetch size is 500.

Execute Select SQL

Specification limits

BIT
- BIT type data whose size is 1 is handled as boolean. Other sized BIT type isn't supported.
FLOAT, FLOAT UNSIGNED
- There are precautions for the precision with MySQL floating points.
  
  = Remarks =
  For details, refer to "MySQL Reference Manual" (https://dev.mysql.com/doc/).
DOUBLE, DOUBLE UNSIGNED
- There are precautions for the precision with MySQL floating points.
  
  = Remarks =
  For details, refer to "MySQL Reference Manual" (https://dev.mysql.com/doc/).
DATE, DATETIME, TIMESTAMP
- An error occurs when data is 0000-00-00 00:00:00.
TIME
- The supported range is from 00:00:00 to 23:59:59. If a value outside this range is set, an error may occur.
YEAR
- Data is handled as String.
If a table name is enclosed by quotation marks, whether the table name is case sensitive depends on the OS on which the database is running.
Even when an optional extended attribute ZEROFILL is specified, the result is returned excluding 0.
The default value for Fetch size is 500.

Write Table

Specification limits

BIT
- BIT type data whose size is 1 is handled as boolean. Other sized BIT type isn't supported.
FLOAT, FLOAT UNSIGNED
- There are precautions for the precision with MySQL floating points.
  
  = Remarks =
  For details, refer to "MySQL Reference Manual" (https://dev.mysql.com/doc/).
DOUBLE, DOUBLE UNSIGNED
- There are precautions for the precision with MySQL floating points.
  
  = Remarks =
  For details, refer to "MySQL Reference Manual" (https://dev.mysql.com/doc/).
DATE, DATETIME
- If a non-existent date is set, it's not written correctly.
  
  Example: When the date is "0000/00/00", it will be "0002/11/30".
TIMESTAMP
- If a non-existent date is set, an error occurs.
TIME
- The supported range is from 00:00:00 to 23:59:59.
YEAR
- Data is handled as String.
If a table name is enclosed by quotation marks, whether the table name is case sensitive depends on the OS on which the database is running.

Execute Update SQL

Specification limits

BIT
- BIT type data whose size is 1 is handled as boolean. Other sized BIT type isn't supported.
FLOAT, FLOAT UNSIGNED
- There are precautions for the precision with MySQL floating points.
  
  = Remarks =
  For details, refer to "MySQL Reference Manual" (https://dev.mysql.com/doc/).
DOUBLE, DOUBLE UNSIGNED
- There are precautions for the precision with MySQL floating points.
  
  = Remarks =
  For details, refer to "MySQL Reference Manual" (https://dev.mysql.com/doc/).
DATE, DATETIME
- If a non-existent date is set, it's not written correctly.
  
  Example: When the date is "0000/00/00", it will be "0002/11/30".
TIMESTAMP
- If a non-existent date is set, an error occurs.
TIME
- The supported range is from 00:00:00 to 23:59:59.
YEAR
- Data is handled as String.
If a table name is enclosed by quotation marks, whether the table name is case sensitive depends on the OS on which the database is running.

Execute Stored Procedure

Specification limits

BIT
- BIT type data whose size is 1 is handled as boolean. Other sized BIT type isn't supported.
FLOAT, FLOAT UNSIGNED
- There are precautions for the precision with MySQL floating points.
  
  = Remarks =
  For details, refer to "MySQL Reference Manual" (https://dev.mysql.com/doc/).
DOUBLE, DOUBLE UNSIGNED
- There are precautions for the precision with MySQL floating points.
  
  = Remarks =
  For details, refer to "MySQL Reference Manual" (https://dev.mysql.com/doc/).
TIME
- The supported range is from 00:00:00 to 23:59:59.
YEAR
- Data is handled as String.
If a table name is enclosed by quotation marks, whether the table name is case sensitive depends on the OS on which the database is running.

Select SQL Wizard

Specification limits

Tables which include "." in the schema name or the table name in the database can't be used.
Test execution may fail if the data amount is large.
The maximum record count shown in the Execution result tab is 100.
SQL statements can be re-edited only if the tables listed in the SQL statement actually exist.

Update SQL Wizard

Specification limits

When the connection resource is XAResource, testing can't be executed.
Tables which include "." in the schema name or the table name in the database can't be used.
SQL statements can be re-edited only if the tables listed in the SQL statement actually exist.
In connection resources for JDBC connector, when Execute transaction processing is not selected, rollback is not performed in test execution, and the data is updated.

Specification limits of MySQL 8.0

Supported types
Read Table
Execute Select SQL
Write Table
Execute Update SQL
Execute Stored Procedure
Select SQL Wizard
Update SQL Wizard

Supported types

This connector supports the following types.

Type name	Support	Remarks
BOOL (BOOLEAN)	Yes	Alias of tinyint(1)
BIT	Limited
TINYINT	Yes
TINYINT UNSIGNED	Limited
MEDIUMINT	Yes
MEDIUMINT UNSIGNED	Limited
SMALLINT	Yes
SMALLINT UNSIGNED	Limited
INT (INTEGER)	Yes
INT UNSIGNED (INTEGER UNSIGNED)	Limited
BIGINT	Yes
BIGINT UNSIGNED	Limited
FLOAT	Limited
FLOAT UNSIGNED	Limited
DOUBLE (REAL)	Limited
DOUBLE UNSIGNED (REAL UNSIGNED)	Limited
DECIMAL (NUMERIC)	Yes
DECIMAL UNSIGNED (NUMERIC UNSIGNED)	Limited
DATE	Limited
DATETIME	Limited
TIMESTAMP	Limited
TIME	Limited
YEAR	Limited
CHAR	Limited
VARCHAR	Limited
BINARY	Yes
VARBINARY	Yes
TINYBLOB	Yes
TINYTEXT	Limited
BLOB	Yes
TEXT	Limited
MEDIUMBLOB	Yes
MEDIUMTEXT	Limited
LONGBLOB	Yes
LONGTEXT	Limited
ENUM	Limited
SET	Limited
JSON	No

Yes: Supported.
Limited: Supported with limitations.
No: Not supported.

Read Table

Specification limits

BIT
- BIT type data whose size is 1 is handled as boolean. Other sized BIT type isn't supported.
FLOAT, FLOAT UNSIGNED
- There are precautions for the precision with MySQL floating points.
  
  = Remarks =
  For details, refer to "MySQL Reference Manual" (https://dev.mysql.com/doc/).
DOUBLE, DOUBLE UNSIGNED
- There are precautions for the precision with MySQL floating points.
  
  = Remarks =
  For details, refer to "MySQL Reference Manual" (https://dev.mysql.com/doc/).
DATE, DATETIME, TIMESTAMP
- An error occurs when data is 0000-00-00 00:00:00.
TIME
- The supported range is from 00:00:00 to 23:59:59. In other cases, an error occurs.
YEAR
- Data is handled as String.
If a table name is enclosed by quotation marks, whether the table name is case sensitive depends on the OS on which the database is running.
Even when an optional extended attribute ZEROFILL is specified, the result is returned excluding 0.
The default value for Fetch size is 500.

Execute Select SQL

Specification limits

BIT
- BIT type data whose size is 1 is handled as boolean. Other sized BIT type isn't supported.
FLOAT, FLOAT UNSIGNED
- There are precautions for the precision with MySQL floating points.
  
  = Remarks =
  For details, refer to "MySQL Reference Manual" (https://dev.mysql.com/doc/).
DOUBLE, DOUBLE UNSIGNED
- There are precautions for the precision with MySQL floating points.
  
  = Remarks =
  For details, refer to "MySQL Reference Manual" (https://dev.mysql.com/doc/).
DATE, DATETIME, TIMESTAMP
- An error occurs when data is 0000-00-00 00:00:00.
TIME
- The supported range is from 00:00:00 to 23:59:59. In other cases, an error occurs.
YEAR
- Data is handled as String.
If a table name is enclosed by quotation marks, whether the table name is case sensitive depends on the OS on which the database is running.
Even when an optional extended attribute ZEROFILL is specified, the result is returned excluding 0.
The default value for Fetch size is 500.

Write Table

Specification limits

BIT
- BIT type data whose size is 1 is handled as boolean. Other sized BIT type isn't supported.
FLOAT, FLOAT UNSIGNED
- There are precautions for the precision with MySQL floating points.
  
  = Remarks =
  For details, refer to "MySQL Reference Manual" (https://dev.mysql.com/doc/).
DOUBLE, DOUBLE UNSIGNED
- There are precautions for the precision with MySQL floating points.
  
  = Remarks =
  For details, refer to "MySQL Reference Manual" (https://dev.mysql.com/doc/).
DATE, DATETIME, TIMESTAMP
- If a non-existent date is set, it's not written correctly.
  
  Example: When the date is "0000/00/00", it will be "0002/11/30".
TIME
- The supported range is from 00:00:00 to 23:59:59. In other cases, an error occurs.
YEAR
- Data is handled as String.
If a table name is enclosed by quotation marks, whether the table name is case sensitive depends on the OS on which the database is running.

Execute Update SQL

Specification limits

BIT
- BIT type data whose size is 1 is handled as boolean. Other sized BIT type isn't supported.
FLOAT, FLOAT UNSIGNED
- There are precautions for the precision with MySQL floating points.
  
  = Remarks =
  For details, refer to "MySQL Reference Manual" (https://dev.mysql.com/doc/).
DOUBLE, DOUBLE UNSIGNED
- There are precautions for the precision with MySQL floating points.
  
  = Remarks =
  For details, refer to "MySQL Reference Manual" (https://dev.mysql.com/doc/).
DATE, DATETIME, TIMESTAMP
- If a non-existent date is set, it's not written correctly.
  
  Example: When the date is "0000/00/00", it will be "0002/11/30".
TIME
- The supported range is from 00:00:00 to 23:59:59. In other cases, an error occurs.
YEAR
- Data is handled as String.
If a table name is enclosed by quotation marks, whether the table name is case sensitive depends on the OS on which the database is running.

Execute Stored Procedure

Specification limits

BIT
- BIT type data whose size is 1 is handled as boolean. Other sized BIT type isn't supported.
FLOAT, FLOAT UNSIGNED
- There are precautions for the precision with MySQL floating points.
  
  = Remarks =
  For details, refer to "MySQL Reference Manual" (https://dev.mysql.com/doc/).
DOUBLE, DOUBLE UNSIGNED
- There are precautions for the precision with MySQL floating points.
  
  = Remarks =
  For details, refer to "MySQL Reference Manual" (https://dev.mysql.com/doc/).
CHAR, VARCHAR, TEXT, TINYTEXT, MEDIUMTEXT, LONGTEXT
- An error occurs when an argument with multi-byte characters is used.
TIME
- The supported range is from 00:00:00 to 23:59:59. In other cases, an error occurs.
YEAR
- Data is handled as String.
ENUM, SET
- An error occurs when an argument with multi-byte characters is used.
TINYINT UNSIGNED, MEDIUMINT UNSIGNED, SMALLINT UNSIGNED, INT UNSIGNED, BIGINT UNSIGNED, FLOAT UNSIGNED, DOUBLE UNSIGNED, DECIMAL UNSIGNED
If a table name is enclosed by quotation marks, whether the table name is case sensitive depends on the OS on which the database is running.

Select SQL Wizard

Specification limits

Tables which include "." in the schema name or the table name in the database can't be used.
Test execution may fail if the data amount is large.
The maximum record count shown in the Execution result tab is 100.
SQL statements can be re-edited only if the tables listed in the SQL statement actually exist.

Update SQL Wizard

Specification limits

When the connection resource is XAResource, testing can't be executed.
Tables which include "." in the schema name or the table name in the database can't be used.
SQL statements can be re-edited only if the tables listed in the SQL statement actually exist.
In connection resources for JDBC connector, when Execute transaction processing is not selected, rollback is not performed in test execution, and the data is updated.