Knowledge Base knowledge-base:database-migration:setup-and-troubleshooting:mysql
https://support.ispirer.com/
2026-04-25T12:43:16+0000Knowledge Base
https://support.ispirer.com/
https://support.ispirer.com/lib/tpl/bootstrap3/images/favicon.icotext/html2025-06-03T15:46:44+0000Anonymous (anonymous@undisclosed.example.com)MySQL: Advanced Options
https://support.ispirer.com/knowledge-base/database-migration/setup-and-troubleshooting/mysql/advanced-options?rev=1748965604&do=diff
Ispirer Website
Ispirer Toolkit Overview
Free Trial
MySQL: Advanced Options
At the “Target Options” page of SQLWays you can see the button “Advanced”, which opens the window with additional options for target database:
Table type
This option allows to specify the type of the tables created in MySQL database.text/html2026-04-17T12:56:44+0000Anonymous (anonymous@undisclosed.example.com)MySQL: Bin Directory and Utilities to Perform Import
https://support.ispirer.com/knowledge-base/database-migration/setup-and-troubleshooting/mysql/bin-directory?rev=1776430604&do=diff
Ispirer Website
Ispirer Capabilities: MySQL Migration
Free Trial
MySQL: Bin Directory and Utilities to Perform Import
SQLWays is able to execute the import process automatically. To perform it, user should provide target database credentials and Bin Directory.
Bin Directory is a path to a folder containing command line utility of the target database, such as psql.exe, sqlcmd.exe, mysql.exe and others. With the help of this utility import process is performed.text/html2024-04-30T11:46:45+0000Anonymous (anonymous@undisclosed.example.com)Conversion of NUMBER Data Type Column with Comma
https://support.ispirer.com/knowledge-base/database-migration/setup-and-troubleshooting/mysql/decimal_value?rev=1714477605&do=diff
Ispirer Website
Ispirer Capabilities: MySQL Migration
Free Trial
Conversion of NUMBER Data Type Column with Comma
An Oracle table contains a column with a NUMBER data type (for example, precision = 21, scale = 5, decimal = comma):
When converting to MySQL, the fractional part may have been truncated:text/html2024-03-07T11:40:00+0000Anonymous (anonymous@undisclosed.example.com)ERROR 1067 (42000) Invalid Default Value for / ERROR 1292 (22007) Incorrect Datetime Value: - Zero Date is Not Accepted by Timestamp
https://support.ispirer.com/knowledge-base/database-migration/setup-and-troubleshooting/mysql/error-1067?rev=1709811600&do=diff
Ispirer Website
Ispirer Capabilities: MySQL Migration
Free Trial
ERROR 1067 (42000) Invalid Default Value for / ERROR 1292 (22007) Incorrect Datetime Value: - Zero Date is Not Accepted by Timestamp
Symptoms
There might be two of the following symptoms:
* On the import of DDL to the MySQL database the following message may arise:text/html2023-10-26T13:42:02+0000Anonymous (anonymous@undisclosed.example.com)ERROR 1071 (42000): Specified Key was Too Long; Max Key Length is 767 Bytes
https://support.ispirer.com/knowledge-base/database-migration/setup-and-troubleshooting/mysql/error-1071?rev=1698327722&do=diff
Ispirer Website
Ispirer Capabilities: MySQL Migration
Free Trial
ERROR 1071 (42000): Specified Key was Too Long; Max Key Length is 767 Bytes
Symptoms
During the import to MySQL, when you create a key for a InnoDB table, the “ERROR 1071 (42000)” error arises. For example,
CREATE TABLE department
(
id INT,
name VARCHAR(1000)
);
ALTER TABLE department
ADD PRIMARY KEY (id, name);
ERROR 1071 (42000) at line 8: Specified key was too long; max key length is 767 bytestext/html2025-06-03T15:46:41+0000Anonymous (anonymous@undisclosed.example.com)ERROR 1148 (42000): The Used Command is Not Allowed with this MySQL Version
https://support.ispirer.com/knowledge-base/database-migration/setup-and-troubleshooting/mysql/error-1148?rev=1748965601&do=diff
Ispirer Website
Ispirer Capabilities: MySQL Migration
Free Trial
ERROR 1148 (42000): The Used Command is Not Allowed with this MySQL Version
Error Description
While importing data to MySQL version 8.0.2 and higher, an error “ERROR 1148 (42000): The used command is not allowed with this MySQL version” may occur.text/html2023-10-26T13:40:32+0000Anonymous (anonymous@undisclosed.example.com)ERROR 1419 (HY000): You Do Not Have the SUPER Privilege and Binary Logging is Enabled
https://support.ispirer.com/knowledge-base/database-migration/setup-and-troubleshooting/mysql/error-1419?rev=1698327632&do=diff
Ispirer Website
Ispirer Capabilities: MySQL Migration
Free Trial
ERROR 1419 (HY000): You Do Not Have the SUPER Privilege and Binary Logging is Enabled
Symptoms
On the import of the function or trigger to MySQL database, the following error arises: “You do not have the SUPER privilege and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable)text/html2023-10-26T14:12:03+0000Anonymous (anonymous@undisclosed.example.com)ERROR 1598 (HY000): Binary Logging Not Possible. Message: Transaction Level READ-COMMITTED in InnoDB is Not Safe for Binlog Mode STATEMENT
https://support.ispirer.com/knowledge-base/database-migration/setup-and-troubleshooting/mysql/error-1598?rev=1698329523&do=diff
Ispirer Website
Ispirer Capabilities: MySQL Migration
Free Trial
ERROR 1598 (HY000): Binary Logging Not Possible. Message: Transaction Level READ-COMMITTED in InnoDB is Not Safe for Binlog Mode STATEMENT
Symptoms
On the import of objects to the MySQL database the following message may arise: “ERROR 1598 (HY000) at line 25: Binary logging not possible. Message: Transaction level 'READ-COMMITTED' in InnoDB is not safe for binlog mode 'STATEMENT'text/html2023-10-26T13:45:39+0000Anonymous (anonymous@undisclosed.example.com)ERROR 2003 (HY000): Can't Connect to MySQL Server on ‘IP Address’ (Port)
https://support.ispirer.com/knowledge-base/database-migration/setup-and-troubleshooting/mysql/error-2003?rev=1698327939&do=diff
Ispirer Website
Ispirer Capabilities: MySQL Migration
Free Trial
ERROR 2003 (HY000): Can't Connect to MySQL Server on ‘IP Address’ (Port)
Symptoms
On the import to MySQL the following error arises: “ERROR 2003 (HY000): Can't connect to MySQL server on '128.103.100.153' (10061)”.
Cause
The MySQL server doesn't exist or IP address or port is incorrect.text/html2025-06-03T15:46:40+0000Anonymous (anonymous@undisclosed.example.com)ERROR 3948 (42000) at Line 1: Loading Local Data is Disabled; This Must be Enabled on Both the Client and Server Sides
https://support.ispirer.com/knowledge-base/database-migration/setup-and-troubleshooting/mysql/error-3948?rev=1748965600&do=diff
Ispirer Website
Ispirer Capabilities: MySQL Migration
Free Trial
ERROR 3948 (42000) at Line 1: Loading Local Data is Disabled; This Must be Enabled on Both the Client and Server Sides
Symptoms
Loading data into a table during the import to MySQL database can lead to the following message: “ERROR 3948 (42000) at line 1: Loading local data is disabled; this must be enabled on both the client and server sidestext/html2025-06-03T16:23:28+0000Anonymous (anonymous@undisclosed.example.com)MySQL: ODBC Driver Configuration
https://support.ispirer.com/knowledge-base/database-migration/setup-and-troubleshooting/mysql/odbc-driver-configuration?rev=1748967808&do=diff
Ispirer Website
Ispirer Capabilities: MySQL Migration
Free Trial
MySQL: ODBC Driver Configuration
To set up your MySQL ODBC Driver for Windows, follow the steps below:
There are like two parts - two ways of specifying the ODBC connection:
* Specifying ODBC DSN from SQLWays;
* Specifying ODBC DSN from ODBC Data Source Administrator.text/html2024-10-24T10:12:57+0000Anonymous (anonymous@undisclosed.example.com)MySQL: ODBC Driver Installation
https://support.ispirer.com/knowledge-base/database-migration/setup-and-troubleshooting/mysql/odbc-driver-installation?rev=1729764777&do=diff
Ispirer Website
Ispirer Capabilities: MySQL Migration
Free Trial
MySQL: ODBC Driver Installation
MySQL version 4.1.1 and higher
Before you can connect to a MySQL data repository in SQLWays, you must install the MySQL ODBC Driver (Connector/ODBC).
* You can download from the MySQL official website.
* You need to choose install for Windows: “Windows MSI Installer Connector-ODBC”.text/html2023-10-26T14:44:45+0000Anonymous (anonymous@undisclosed.example.com)Sequences Conversion from Microsoft SQL Server to MySQL
https://support.ispirer.com/knowledge-base/database-migration/setup-and-troubleshooting/mysql/sequence-conversion?rev=1698331485&do=diff
Ispirer Website
Ispirer Capabilities: MySQL Migration
Free Trial
Sequences Conversion from Microsoft SQL Server to MySQL
MySQL does not support sequences, that’s why it requires code changes.
Let's consider the following piece of code:
CREATE SEQUENCE CountBy1
START WITH 1
INCREMENT BY 1 ;
GO
CREATE TABLE dbo.[MyTable1]
(
[ID] [bigint] PRIMARY KEY NOT NULL DEFAULT (NEXT VALUE FOR dbo.CountBy1),
[Title1] [nvarchar](64) NOT NULL
);
GOtext/html2024-01-16T14:34:03+0000Anonymous (anonymous@undisclosed.example.com)Returning Single Value from Stored Procedure
https://support.ispirer.com/knowledge-base/database-migration/setup-and-troubleshooting/mysql/single-value-run?rev=1705415643&do=diff
Ispirer Website
Ispirer Capabilities: MySQL Migration
Free Trial
Returning Single Value from Stored Procedure
Currently MySQL does not allow you to return a value from a procedure using the RETURN statement, but there are several workarounds for this problem :
Convert Procedure into Function
CREATE FUNCTION sp_existing_employee
(v_employee_name VARCHAR(128),
v_employee_id INTEGER)
RETURNS INT
BEGIN
IF(EXISTS(SELECT * FROM employees
WHERE employee_name = v_employee_name AN…text/html2023-12-14T14:40:06+0000Anonymous (anonymous@undisclosed.example.com)MySQL: Setting Up Source User Privileges
https://support.ispirer.com/knowledge-base/database-migration/setup-and-troubleshooting/mysql/source-user-privileges?rev=1702564806&do=diff
Ispirer Website
Ispirer Capabilities: MySQL Migration
Free Trial
MySQL: Setting Up Source User Privileges
To be able to connect to a database and to extract DDL of objects in MySQL, you need to create a user and assign select privileges to this user. As a template you can use the queries below:
* create user 'test_perm'@'%' identified by 'root';text/html2023-12-14T14:51:41+0000Anonymous (anonymous@undisclosed.example.com)MySQL: Setting Up Target User Privileges
https://support.ispirer.com/knowledge-base/database-migration/setup-and-troubleshooting/mysql/target-user-privileges?rev=1702565501&do=diff
Ispirer Website
Ispirer Capabilities: MySQL Migration
Free Trial
MySQL: Setting Up Target User Privileges
To make import into MySQL Database you can utilize the existing user or create a new one.
To create a new user you can execute the statement below:
create user tester identified by 'Pwd'
As soon as the user is created, you will need to assign appropriate privileges to that user, to be able to load the definitions and data into the target MySQL database.text/html2023-10-26T14:41:19+0000Anonymous (anonymous@undisclosed.example.com)Referring to a TEMPORARY Table More Than Once in the Same Query in MySQL
https://support.ispirer.com/knowledge-base/database-migration/setup-and-troubleshooting/mysql/temporary-table?rev=1698331279&do=diff
Ispirer Website
Ispirer Capabilities: MySQL Migration
Free Trial
Referring to a TEMPORARY Table More Than Once in the Same Query in MySQL
In MySQL you cannot refer to a TEMPORARY table more than once in the same query, that's why it requires significant code changes.
Let's consider the following piece of code:text/html2023-10-26T14:50:24+0000Anonymous (anonymous@undisclosed.example.com)TRUNC (date) Function Conversion from Oracle to MySQL
https://support.ispirer.com/knowledge-base/database-migration/setup-and-troubleshooting/mysql/trunc-function-conversion?rev=1698331824&do=diff
Ispirer Website
Ispirer Capabilities: MySQL Migration
Free Trial
TRUNC (date) Function Conversion from Oracle to MySQL
The TRUNC (date) function returns date with the time portion of the day truncated to the unit specified by the format model fmt.
MySQL does not support TRUNC function. Therefore, need to create an additional object, namely a function that simulates the operation of the TRUNC function.