Precautions for migrating data from MySQL to Oracle _ MySQL

Considerations for migrating data from MySQL to Oracle 1. does MySQL 5.1 Storage Program and function play a role in replication?

Yes, the standard behavior executed in the storage program and function is copied from the master MySQL server to the slave server.

2. Can the stored programs and functions created on the master server be copied to the slave server?

Yes, the storage programs and functions executed by DDL statements are copied to the slave server after they are created on the master server. Therefore, the target will exist on two servers. The ALTER and DROP statements for stored programs and functions are also copied.

3. how does the behavior occur in the copied stored programs and functions?

MySQL records every DML event that occurs in the storage program and function, and copies these individual actions to the slave server. The actual invocation of stored programs and functions is not copied.

4. Are there any special security requirements for using storage programs, functions, and replication together?

Yes, because a slave server has the permission to execute any statement to read the binary log of the autonomous server, the specified security constraint exists because of the storage program and function used together with the replication. If the replication or binary log is generally active (for point-in-time recovery), MySQL DBA has two security options:
Q: What should I pay attention to when migrating data from MySQL to Oracle?

A: The following are precautions for migrating MySQL to Oracle.

1. automatically increasing data type processing

MYSQL has a data type that increases automatically. when you insert a record, you do not need to operate on this field. the data value is automatically obtained. ORACLE does not have an auto-increasing data type. you need to create an auto-increasing serial number. when inserting a record, you need to assign the next value of the serial number to this field.

Create sequence serial number name (preferably table name + serial number mark) increment by 1 start with 1 MAXVALUE 99999 cycle nocache;

The maximum value is determined by the length of the field. if the defined auto-increment serial NUMBER (6), the maximum value is 999999.

INSERT statement INSERT this field value: name of the serial number. NEXTVAL

2. single quotes

MYSQL can use double quotes to enclose strings. ORACLE can only use single quotes to enclose strings. You must replace single quotes before inserting and modifying strings: replace all the existing single quotes with two single quotes.

3. processing of paging SQL statements

The SQL statement for MYSQL to process paging is relatively simple. it uses LIMIT to start the position and record the number. in PHP, you can also use SEEK to locate the result set. It is complicated for ORACLE to process paging SQL statements. Each result set has only one ROWNUM field to indicate its location, and only ROWNUM <100, not ROWNUM> 80 can be used.

The following two SQL statements (ID is the field name of the unique keyword) are better after analysis ):

Statement 1:

Select id, [FIELD_NAME,...] FROM TABLE_NAME where id in (select id from (select rownum as numrow, id from TABLE_NAME WHERE condition 1 order by condition 2) where numrow> 80 and numrow <100) order by condition 3;

Statement 2:

SELECT * FROM (select rownum as numrow, c. * from (select [FIELD_NAME,...] FROM TABLE_NAME WHERE condition 1 order by condition 2) c) where numrow> 80 and numrow <100) order by condition 3;

4. processing long strings

ORACLE processing of long strings also has some special features. The maximum length of a string that can be operated during INSERT and UPDATE is less than or equal to 4000 single bytes. if you want to INSERT a longer string, use the CLOB type for the field to use the DBMS_LOB package that comes with ORACLE. Before inserting a modification record, you must make a non-null and length judgment. if the field value cannot be blank or exceeds the length value, a warning should be given, and the last operation is returned.

5. processing date fields

Mysql date fields are divided into DATE and TIME. The oracle date field is only DATE, which contains information about the year, month, day, hour, minute, and second. the system TIME of the current database is SYSDATE, accurate to seconds, or use a string to convert to the date type function TO_DATE ('1970-08-01 ', 'yyyy-MM-DD') year-month-day 24 hours: minute: Second format YYYY-MM-DD HH24: MI: SS TO_DATE () has many date formats. For more information, see oracle doc. convert a datetime field to a string function TO_CHAR ('2017-08-01 ', 'yyyy-MM-DD HH24: MI: SS ')

The mathematical formulas for date fields vary greatly. MYSQL uses DATE_FIELD_NAME> SUBDATE (NOW (), INTERVAL 7 DAY) to locate the current time seven days from the current time and uses DATE_FIELD_NAME> SYSDATE-7;

6. handling of null characters

Non-empty fields in MYSQL are also empty. empty fields are not allowed in ORACLE. The ORACLE table structure is defined based on the not null value of MYSQL. errors may occur when data is imported. Therefore, when importing data, you need to judge the NULL character. if it is NULL or NULL, you need to change it to a space string.

7. fuzzy comparison of strings

MYSQL uses the field name like '% string %', and ORACLE can also use the field name like '% string %'. However, this method cannot use indexes and is not fast, if you use a string to compare the instr function (field name, 'string') to 0, you will get more accurate search results.

8. after operating the database in programs and functions, pay attention to the release of the result set and pointer.

Any user who wants to create a bucket must be granted the SUPER permission.

As an option, a DBA can set the log_bin_trust_routine_creators system variable to 1, which will allow people with the standard create routine permission to CREATE a storage program and function.

5. What are the restrictions on the replication of stored programs and functions?

Uncertain (random) or time-based rows embedded into the storage program cannot be copied as appropriate. The random result is only because of its nature. it is predictable and cannot be cloned. Therefore, the random behavior of copying to the slave server will not mirror those generated on the master server. Note: declaring a stored program or function as DETERMINISTIC or setting the system variable to 0 in log_bin_trust_routine_creators allows immediate value operations to be called.

In addition, the time base behavior cannot be re-generated from the server, because such time base behavior cannot be re-generated in the storage program by timing the binary logs used for replication, because this binary log only records DML events and does not include timing constraints.

Finally, an error occurs in a non-interactive table in a large DML behavior (such as a large number of inserts), and the non-interactive table may experience replication, the master server in the non-interactive table of the replication version can be partially updated from DML behavior. However, the slave server is not updated due to the error. For the DML behavior of the function, the workspace will be executed with the IGNORE keyword, so that error updates can be ignored on the master server and errors will not be copied to the slave server. (