Skip to main content
Skip table of contents

MySQL

MySQL is a popular open-source RDBMS (relational database management system). It has become one of the leading database choices for large-scale websites including YouTube, Twitter, Facebook, and Yahoo. MySQL is offered under two different editions: the open-source MySQL Community Server and the proprietary Enterprise Server. The MySQL Enterprise Server is differentiated by a series of proprietary extensions which install as server plugins but is built from the same code base.

Type Name

mysql / mysql5

For use with MySQL version 4.x and 5 or later, respectively.

MySQL connectors expect the database or session to be using ANSI mode. If the database is not using ANSI mode, an initialization query should be used on the pool to set ANSI mode:set SESSION sql_mode = 'ANSI'

Connection Properties

Template name: mysql

Appropriate translator name: mysql or mysql5

Properties:

  • host (default: localhost)
  • port (default: 3306)
  • serverId (default: empty)

  • db
  • user
  • password (default: empty)
  • driver (default: mysql)
  • driver-class (default: com.mysql.cj.jdbc.Driver)
  • ssl (TRUE|FALSE)

  • jdbcProperties (arbitrary extra properties; default: empty)
  • useL2Buffer

  • new-connection-sql (default: set SESSION sql_mode = 'ANSI')

  • check-valid-connection-sql (default: select 1)
  • min-pool-size (default: 2)
  • max-pool-size (default: 70)

Translator Properties

Translator Properties Shared by All JDBC Connectors

(Properties listed in alphabetical order)

To view the full table, click the expand button in its top right corner


Name

Description

Default value

comparisonStringConversion

Sets a template to convert WHERE clauses on strings

Examples

SQL
comparisonStringConversion=%s
-- no conversion will be applied

comparisonStringConversion=binary %s
-- WHERE binary someStringExpression LIKE someOtherStringExpression

comparisonStringConversion=(%s COLLATE Latin1_General_CS_AS)
-- WHERE (someStringExpression COLLATE Latin1_General_CS_AS LIKE someOtherStringExpression)


  • MS SQL Server:
    (%s COLLATE Latin1_General_CS_AS)
  • MySQL, SingleStore:
    binary %s
  • Others:
    %s

DatabaseTimeZone

Database time zone, used when fetching date, time, or timestamp values

System default time zone

DatabaseVersion

Specific database version, used to fine-tune pushdown support

Automatically detected by the server through the data source JDBC driver, if possible

forbidNonMatchingNamesOnly considered when importer.tableNamePattern is set. When set to FALSE, allows creation of tables with names that do not match the tableNamePattern. Tables with non-matching names will only be accessible until the server restartsTRUE
ForceQuotedIdentifiers

If TRUE, all identifiers in the final query (that is being generated by a translator to be sent directly to the target DBMS) will be quoted

TRUE

MaxPreparedInsertBatchSize

Maximum size of prepared insert batch

2048

OrderByStringConversion

Sets a template to convert ORDER BY clauses on strings

Examples

SQL
OrderByStringConversion=%s
-- no conversion will be applied

OrderByStringConversion=(binary %s)
-- ORDER BY (binary someStringExpression)

OrderByStringConversion=cast((%s) as varchar(2000) ccsid 1208)
-- ORDER BY cast((someStringExpression) as varchar(2000) ccsid 1208)
  • DB2 for AS/400:
    cast((%s) as varchar(2000) ccsid 1208)
  • DB2 for zOS:
    cast((%s) as varchar(2000) ccsid ASCII)
  • MS SQL Server:
    (cast(cast(%s as varchar) as varbinary)
  • MySQL, SingleStore:
    (binary %s)
  • PostgreSQL:
    (%s COLLATE \"C\")
  • Others:
    %s
supportsConvertFromClobToString If TRUE, indicates that the translator supports the CONVERT/CAST function from clob to string
  • MySQL, PostgreSQL, Redshift, Greenplum, Oracle, MS SQL Server:
    TRUE 
  • Others:
    FALSE

supportsNativeQueries

Forces a translator to issue a native() system procedure that can be used to pass native queries directly to an underlying DBMS

Example

SQL
SELECT x.* 
FROM table
  ( 
    CALL "dwh.native"("request" => 'SELECT query, pid, elapsed, substring FROM svl_qlog ORDER BY STARTTIME DESC LIMIT 200') 
  ) w
  , ARRAYTABLE( w.tuple COLUMNS query string, pid integer , elapsed string, "substring" string ) x;

More usage examples

  • MySQL, PostgreSQL, Redshift, Snowflake, Oracle, MS SQL Server; Exasol, Vertica, Bigquery, SingleStore, Azure:
    TRUE
  • Others:
    FALSE
supportsOrderByAlias

If FALSE, the Data Virtuality Server does not generate aliases in the ORDER BY clause.

If TRUE, indicates that the source supports aliases in the ORDER BY clause

  • PostgreSQL:
    TRUE
  • Others:
    • if OrderByStringConversion is not set, same as supportsOrderBy
    • if OrderByStringConversion is set, default for supportsOrderByAlias is  FALSE
supportsOrderByString

If FALSE, the Data Virtuality Server does not push down the ORDER BY clause if it contains at least one expression of string type.

If TRUE, indicates that the source supports strings in the ORDER BY clause

TrimStrings

If TRUE, trims trailing whitespace from fixed-length character strings. Please note that the Data Virtuality Server only has string or varchar type that treats trailing whitespace as meaningful

FALSE

UseBindVariables

if TRUE, indicates that PreparedStatements should be used and that literal values in the source query should be replaced with bind variables. If FALSE, only LOB values will trigger the use of PreparedStatements

TRUE

UseCommentsInSourceQuery

Embeds a / comment / leading comment with session/request id in the source SQL query for informational purposes

FALSE

The names of the translator properties are case-sensitive.

Translator Properties Specific for MySQL

To view the full table, click the expand button in its top right corner


NameDescriptionDefault value
UseStreamingResults

By default MySQL and PostgreSQL JDBC drivers try to load all results into memory. With big tables, there might be performance problems and sometimes it might result in OutOfMemory.

This option is used to have better memory usage by the JDBC driver and, in the case of MySQL, to quicken the transfer of large data sets. It is recommended to set this option to TRUE if OutOfMemory problems are experienced when reading large datasets.

However, such connections should be used only for reading data, since enabling streaming breaks the normal transactional behaviour

TRUE
uploadMode

Values:  bulkInsertSingleThread, bulkInsertMultiThread, standard

  • bulkInsertSingleThread - sends batches in a single thread
  • bulkInsertMultiThread - sends batches in multiple threads
  • standard - single data processing/no batches (slow)
bulkInsertSingleThread
maxChunkSize

Sets the approximate size in bytes for batches created 

1039597
numberOfThreads

Number of threads for the bulkInsertMultiThread mode

3
maxTableNameLength

Maximum length of a table name

64
maxColumnNameLength

Maximum length of a column name .

(info) Five chars of defined maximum length will be reserved for internal purposes and cannot be used for column-identifier

64
createStringsAsClobsWhen set to TRUE, string columns in the target table are converted to clobsFALSE
stringClobsLengthThreshold Sets a length threshold for strings to be converted to clobs when createStringsAsClobs is set to TRUE. String columns with length less than the threshold will not be converted to clobs64

Data Source Properties

Data Source Properties Shared by All JDBC Connectors

(Properties listed in alphabetical order)

To view the full table, click the expand button in its top right corner


Name

Description

Default

importer.autoCorrectColumnNames Replaces . in a column name with _ as the period character is not supported by the Data Virtuality Server in column names TRUE
importer.catalog

Database catalogs to use. Can be used if the db connection property is omitted.

Only for Microsoft SQL Server and Snowflake:

  • Multiple catalogs (multiple databases) usage is supported;
  • '%' as a wildcard is allowed: for example, importer.catalog="foo%" will import foo, foobar, etc.
  • To specify several catalog names or/and patterns, values should be comma-separated and enclosed within double quotes: importer.catalog="schema1,schema2,pattern1%,pattern2%";
  • To use all catalogs, the value should be '%': importer.catalog="%"
  • To escape a wildcard in a catalog name, use the '\' escape character. The value should be as follows: importer.catalog="test\_tables"
Empty
importer.defaultSchema
  • Only for data sources that are also supported as Analytical Storage (SQLServer, MySQL, Oracle, PostgreSQL, Redshift);
  • When the property is correctly set, SELECT INTO, CREATE, and DROP TABLE commands are enabled for that data source;
  • Must point to the original schema name in the DBMS (e.g. importer.defaultSchema=public);
  • Needs specifying where tables will be created or dropped in the source DBMS;
  • Not meant to be used with Analytical Storage data source

Please note that writing into a data source is only possible if this parameter is set.

Empty
importer.enableMetadataCache Turns on metadata cache for a single data source even when the global option is turned off. Together with importer.skipMetadataLoadOnStartup=true, it allows using materialized views after server restart when the original source is unavailable
FALSE
importer.excludeProcedures Case-insensitive regular expression that will exclude a matching fully qualified procedure name from import
Empty
importer.excludeSchemas Comma-separated list of schemas (no % or ? wildcards allowed) to exclude listed schemas from import. A schema specified in defaultSchema or schemaPattern will be imported despite being listed in excludeSchemas. Helps to speed up metadata loading

Oracle:

SQL
APEX_PUBLIC_USER,
DIP,
FLOWS_040100,
FLOWS_020100,
FLOWS_FILES,
MDDATA,
ORACLE_OCM,
SPATIAL_CSW_ADMIN_USR,
SPATIAL_WFS_ADMIN_USR,
XS$NULL,
BI,
HR,
OE,
PM,
IX,
SH,
SYS,
SYSTEM,
MDSYS,
CTXSYS

All others: empty

importer.excludeTables

Case-insensitive regular expression that will exclude a matching fully qualified table name from import. Does not speed up metadata loading

Examples

CODE
-- Exclude all tables in (source) schemas sys and INFORMATION_SCHEMA:
importer.excludeTables=(.*[.]sys[.].*|.*[.]INFORMATION_SCHEMA[.].*)
 
-- You can also negate the defined patterns for the tables to be excluded.
-- Exclude all tables except that ones starting with "public.br" and "public.mk":
importer.excludeTables=(?!public\.(br|mk)).*

Empty

importer.fetchSize

Fetch size assigned to a resultset on loading metadata

No default value

importer.importApproximateIndexes

If set to TRUE, imports approximate index information

TRUE

importer.importIndexes If set to TRUE, imports index/unique key/cardinality information FALSE
importer.importKeys If set to TRUE, imports primary and foreign keys FALSE

importer.importProcedures

If set to TRUE, imports procedures and procedure columns. Overloaded procedures can be imported with an additional option useProcedureSpecificName.

Please note that it is currently not possible to import procedures which use the same name for more than one parameter (e.g. same name for IN and OUT parameters). Such procedures can be excluded from import with the parameter excludeProcedures.
Please note that it is not always possible to import procedure result set columns due to database limitations

FALSE

importer.loadColumnsTableByTableSet to TRUE to force table by table metadata processingFALSE/TRUE only for Netsuite and SAP Advantage Database Server
importer.loadMetadataWithJdbc If set to TRUE, turns off all custom metadata load ways FALSE
importer.procedureNamePattern Procedure(s) to import. If omitted, all procedures will be imported. % as a wildcard is allowed: for example, importer.procedurePatternName=foo% will import foo , foobar, etc. W orks only in combination with importProcedures Empty

importer.quoteNameInSource

If set to FALSE, directs the Data Virtuality Server to create source queries using unquoted identifiers

TRUE

importer.renameDuplicateColumns If set to TRUE,  renames duplicate columns caused by either mixed case collisions or autoCorrectColumnNames replacing . with _. The suffix _n where n is an integer will be added to make the name unique TRUE
importer.renameDuplicateTables If set to TRUE, renames duplicate tables caused by mixed case collisions. The suffix _n where n is an integer will be added to make the name unique TRUE
importer.replaceSpecSymbsInColNamesIf set to TRUE, replaces all special symbols (any symbols not in the ^A-Za-z0-9_ sequence) to the _ symbol in column names of tables FALSE / TRUE only for BigQuery
importer.schemaPattern Schema(s) to import. If omitted or has "" value, all schemas will be imported. % as wildcard is allowed: for example, importer.schemaPattern=foo% will import foo, foobar, etc. To specify several schema names or/and patterns, values should be comma-separated and enclosed within double quotes: importer.schemaPattern="schema1,schema2,pattern1%,pattern2%". For proper escaping of special characters depending on the type of data source, check Escaping special characters in schema names or use wildcards instead: "[schema_name]" can be rewritten as "%schema%name%". Helps to speed up metadata loadingEmpty
importer.skipMetadataLoadOnStartup

If set to TRUE, allows skipping metadata loading on server startup if there's cached metadata for the data source. . Together with importer.enableMetadataCache=true, it allows using materialized views after server restart when the original source is unavailable

FALSE
importer.tableNamePattern Table(s) to import. If omitted, all tables will be imported. % as a wildcard is allowed: for example, importer.tableNamePattern=foo% will import foo, foobar, etcEmpty
importer.tableTypes

Comma-separated list (without spaces) of table types to import. Available types depend on the DBMS. Usual format: "TABLE,VIEW".

Other typical types are "SYSTEM TABLE", "GLOBAL TEMPORARY", "LOCAL TEMPORARY", "ALIAS", "SYNONYM"

Empty
importer.useCatalogName If set to TRUE, uses any non-null/non-empty catalogue name as part of the name in source, e.g. "catalogue"."table"."column", and in the Data Virtuality Server runtime name if useFullSchemaName is TRUE. If set to FALSE, will not use the catalogue name in either the name in source or the Data Virtuality Server runtime name. Should be set to FALSE for sources that do not fully support a catalogue concept, but return a non-null catalogue name in their metadata - such as HSQL TRUE / FALSE only for Hive
importer.useFullSchemaName

If set to FALSE, directs the importer to drop the source catalogue/schema from the Data Virtuality Server object name so that the Data Virtuality Server fully qualified name will be in the form of <model name>.<table name>.

Please note that this may lead to objects with duplicate names when importing from multiple schemas, which results in an exception

TRUE
importer.useProcedureSpecificName If set to TRUE, allows the import of overloaded procedures (which will normally result in a duplicate procedure error) by using the unique procedure specific name in the Data Virtuality Server. This option will only work with JDBC 4.0 compatible drivers that report specific names FALSE
importer.widenUnsignedTypes If set to TRUE, converts unsigned types to the next widest type. For example, SQL Server reports tinyint as an unsigned type. With this option enabled, tinyint would be imported as a short instead of a byte TRUE
The names of the data source properties are case-sensitive.

Escaping wildcards in importer.catalog available since v4.0.8


JavaScript errors detected

Please note, these errors can depend on your browser setup.

If this problem persists, please contact our support.