Migrating from MySQL to DM8
Guidance Suggestion 1
Guidance Suggestion 2
Before migrating data, set the compatibility parameters to be compatible with the MySQL database:
- Set COMPATIBLE_MODE=4 in the dm.ini configuration file specified by the instance.
- Use the statement ALTER SYSTEM SET 'COMPATIBLE_MODE'=4 SPFILE; for modification, and then restart the database service for the modification to take effect.
Guidance Suggestion 3
In MySQL, the default character-encoding format for table creation is UTF8_GENERAL_CI (case insensitive). Therefore, you are advised to set case insensitive for DM when migrating data from MySQL to DM. Refer to the following measures:
- When initializing an instance, set CASE_SENSITIVE=0 (case insensitive). Note that this setting cannot be modified once it is set.
- If CASE_SENSITIVE=1 (case sensitive) is set during instance initialization, deselect "Retain Object Name Case" when using DEM for migration.
For details, see:
- Initializing parameters: https://eco.dameng.com/document/dm/zh-cn/start/mysql_dm.html#3.4.2%20%E7%9B%AE%E7%9A%84%E7%AB%AF%E8%BE%BE%E6%A2%A6%E5%87%86%E5%A4%87
- FAQs: How to keep the case of table names unchanged after migrating from MySQL to DM and How to remove double quotation marks from MyBatis objects.
Guidance Suggestion 4
In MySQL 5.0.3 and later versions, the varchar type is stored by character. varchar(1) stores a Chinese character. In DM, the varchar type is stored by byte by default. varchar(3) stores a Chinese character. During the migration from MySQL 5.0.3 (or later) to DM, character truncation may occur. If a table contains Chinese characters, refer to the following measures:
- Increase the field length. For example, if the field length in MySQL is 10, the field length in DM table needs to be increased to 30 during the table creation.
- Reinitialize the instance and set the length of the VARCHAR type object to characters (LENGTH_IN_CHAR=1).
Guidance Suggestion 5
If "Syntax analysis error" is reported when creating an object such as a table in DM, the possible cause is that the object name is a system reserved word. Run the SQL statement SELECT * FROM V$RESERVED_WORDS WHERE RESERVED='Y' AND KEYWORD=Object_name; to check whether the object name is a reserved word. If it is, refer to the following measures:
- Use double quotation marks to enclose the object name and use quotation marks during query.
- (Recommended) Configure the dm_svc.conf file on the client. In the corresponding database service configuration section, add the KEYWORDS option to mask keywords: KEYWORDS=Object_name.
- Modify the SQL statements and object names related to system keywords.
Guidance Suggestion 6
The int type does not support setting data precision in DM. Check whether precision has been set for the int type in the field definition in the table. If yes, remove the precision, for example, change create table test1(v1 int(10),v2 varchar(20)); to create table test1(v1 int,v2 varchar(20));.
Guidance Suggestion 7
In MySQL, the default setting for the TIMESTAMP type is "0000-00-00 00:00:00." In DM, the setting must range from "0001-01-01 00:00:00.000000" to "9999-12-31 23:59:59.999999." If the error message is reported indicating that the TIMESTAMP type is "0000-00-00 00:00:00" during the migration, change it to a valid date type.
Guidance Suggestion 8
In DM, the usages of single and double quotation marks are different. Double quotation marks are used for keywords, object names, field names, and aliases. Single quotation marks are used for character string constants. For the default setting of a field, the default character (or character string) value must be enclosed in single quotation marks, for example, char(10)default 'abc'.
Guidance Suggestion 9
If the DATE_FORMAT() function is used in MySQL, you can use the to_char or to_date function in DM for modification. For example:
- MySQL: select date_format(sysdate(), '%Year%Month') from dual
- DM: select translate(to_char(sysdate, 'yyyy-mm#'),'-#','Year_Month') from dual"
Guidance Suggestion 10
In MySQL, when using the convert() function, value is entered before type. In DM, value is entered after type. If the convert() function is used in DM, change the parameter sequence. For example:
- MySQL: CONVERT(CASE WHEN TEMP_STA.c_data_value THEN NULL ELSE TEMP_STA.c_data_value END, SIGNED) AS "ONLINEUSER"
- DM: CONVERT(INTEGER,CASE WHEN TEMP_STA.c_data_value THEN NULL ELSE TEMP_STA.c_data_value END) AS "ONLINEUSER"
Guidance Suggestion 11
The usage of cast() in DM is the same as that in MySQL, but the effect is different. For example, in MySQL, there is no restriction on the value conversion from the numeric type to char type, but there is a restriction in DM. Convert cast(numeric type value as char) to cast(numeric type value as varchar). For the unsigned type, decide whether to perform conversion based on your requirements.
Guidance Suggestion 12
In MySQL, if the date_add function is used to evaluate the expression for adding a time interval, use the TIMESTAMPADD function instead in DM. For example:
- MySQL: select DATE_ADD(sysdate(), INTERVAL 1 YEAR);
- DM: select TIMESTAMPADD(SQL_TSI_YEAR, 1,sysdate());
Guidance Suggestion 13
If an interval expression such as interval 1 year is used in MySQL, single quotation marks ('') must be added to the positive number following the interval keyword in DM. For example, interval '1' year. Negative numbers must be modified, for example, interval '-1' year.