Practice of self increasing primary keys in MySQL migration to OBOracle scenarios
OB Oracle MySQL DBA OracleMySQL MySQL DMP 3200 10 MySQL OB Oracle OB Oracle MySQL [email protected]
OB Oracle
MySQL DBA OracleMySQL MySQL DMP
3200 10
MySQL OB Oracle OB Oracle
MySQL
zlm@10.186.60.68 [zlm]> desc t;+-------+-------------+------+-----+---------+----------------+| Field | Type | Null | Key | Default | Extra |+-------+-------------+------+-----+---------+----------------+| id | bigint(20) | NO | PRI | NULL | auto_increment || name | varchar(10) | YES | | NULL | |+-------+-------------+------+-----+---------+----------------+2 rows in set (0.00 sec)[email protected] [zlm]> select * from t;+----+------+| id | name |+----+------+| 1 | a || 2 | b || 3 | c |+----+------+3 rows in set (0.00 sec)DBCAT
DBCAT OB DDL Oracle
cd /opt/oceanbase_package/tools/dbcat-1.9.1-RELEASE/bin./dbcat convert -H 10.186.60.68 -P 3332 --user=zlm --password=zlm --database=zlm --no-schema --no-quote --from mysql57 --to oboracle32x --table t --file=/tmpParsed args:[--no-quote] true[--no-schema] true[--table] [t][--host] 10.186.60.68[--port] 3332[--user] zlm[--password] ******[--database] zlm[--file] /tmp[--from] mysql57[--to] oboracle32x2023-08-16 14:41:58 INFO Init convert config finished.2023-08-16 14:41:58 INFO {dataSource-1} inited2023-08-16 14:41:58 INFO Init source druid connection pool finished.2023-08-16 14:41:58 INFO Register c.o.o.d.m.c.m.MySql56ObOracle22xColumnConverter 2023-08-16 14:41:58 INFO Register c.o.o.d.m.c.m.MySql56ObOracle22xIndexConverter 2023-08-16 14:41:58 INFO Register c.o.o.d.m.c.m.MySql56ObOracle22xPrimaryKeyConverter 2023-08-16 14:41:58 INFO Register c.o.o.d.m.c.m.MySql56ObOracle22xUniqueKeyConverter 2023-08-16 14:41:58 INFO Register c.o.o.d.m.c.m.MySql56ObOracle22xPartitionConverter 2023-08-16 14:41:59 INFO Load meta/mysql/mysql56.xml, meta/mysql/mysql57.xml successed2023-08-16 14:42:09 INFO Query 0 dependencies elapsed 17.35 ms2023-08-16 14:42:09 INFO Query table: "t" attr finished. Remain: 02023-08-16 14:42:09 INFO Query 1 tables elapsed 69.71 ms2023-08-16 14:42:09 WARN Include types is empty. Ignore schema: ZLM2023-08-16 14:42:09 WARN Skip to compare/convert sequences as SEQUENCE is unsupported2023-08-16 14:42:09 INFO Starting to convert schema to path: "/tmp/dbcat-2023-08-16-144209/ZLM"2023-08-16 14:42:09 INFO Successed to generate report in the path: "/tmp/dbcat-2023-08-16-144209/ZLM-conversion.html"2023-08-16 14:42:09 INFO {dataSource-1} closing ...2023-08-16 14:42:09 INFO {dataSource-1} closedcd /tmp/dbcat-2023-08-16-144209/ZLMcat TABLE-schema.sql CREATE TABLE t ( id NUMBER(19,0), name VARCHAR2(30 BYTE), CONSTRAINT PRIMARY PRIMARY KEY (id));-- CREATE SEQUENCE xxx START WITH 1 INCREMENT BY 1 ... for tDBCAT Oracle DDL SQL MySQL
OB
- SEQ_<>_<>
- CYCLE ORDER N
- CACHE 20
- MIN_VALUE MIN_VALUE
- LAST_NUMBER START WITH
ZLM[ZLM]> CREATE SEQUENCE SEQ_T_ID MINVALUE 1 MAXVALUE 999999 INCREMENT BY 1 START WITH 1;Query OK, 0 rows affected (0.03 sec)ZLM[ZLM]> SELECT SEQUENCE_NAME,MIN_VALUE,LAST_NUMBER,CYCLE_FLAG,ORDER_FLAG,CACHE_SIZE FROM DBA_SEQUENCES WHERE SEQUENCE_OWNER='ZLM';+---------------+-----------+-------------+------------+------------+------------+| SEQUENCE_NAME | MIN_VALUE | LAST_NUMBER | CYCLE_FLAG | ORDER_FLAG | CACHE_SIZE |+---------------+-----------+-------------+------------+------------+------------+| SEQ_T_ID | 1 | 1 | N | N | 20 |+---------------+-----------+-------------+------------+------------+------------+1 row in set (0.01 sec)ZLM[ZLM]> drop sequence SEQ_T_ID;Query OK, 0 rows affected (0.03 sec)ZLM[ZLM]> CREATE SEQUENCE SEQ_T_ID MINVALUE 1 MAXVALUE 999999 INCREMENT BY 1 START WITH 10;Query OK, 0 rows affected (0.03 sec)ZLM[ZLM]> SELECT SEQUENCE_NAME,MIN_VALUE,LAST_NUMBER,CYCLE_FLAG,ORDER_FLAG,CACHE_SIZE FROM DBA_SEQUENCES WHERE SEQUENCE_OWNER='ZLM';+---------------+-----------+-------------+------------+------------+------------+| SEQUENCE_NAME | MIN_VALUE | LAST_NUMBER | CYCLE_FLAG | ORDER_FLAG | CACHE_SIZE |+---------------+-----------+-------------+------------+------------+------------+| SEQ_T_ID | 1 | 10 | N | N | 20 |+---------------+-----------+-------------+------------+------------+------------+1 row in set (0.03 sec)OB
1 DDL 2
ZLM[ZLM]> CREATE TABLE "ZLM"."T" ( -> "ID" NUMBER(19,0) DEFAULT SEQ_T_ID.NEXTVAL, -> "NAME" VARCHAR2(30 BYTE), -> CONSTRAINT "PRIMARY" PRIMARY KEY ("ID"));Query OK, 0 rows affected (0.15 sec)OMS DBCAT DDL
Oracle
ID DEFAULT 2 SEQ_T_ID.NEXTVAL
DataX
DataX OB Reader Writer OB
-- DataX dataX ./job cat t.json { "job": { "setting": { "speed": { "channel": 4 }, "errorLimit": { "record": 0, "percentage": 0.1 } }, "content": [ { "reader": { "name": "mysqlreader", "parameter": { "username": "zlm", "password": "zlm", "column": [ "*" ], "connection": [ { "table": [ "t" ], "jdbcUrl": ["jdbc:mysql://10.186.60.68:3332/zlm?useUnicode=true&characterEncoding=utf8"] } ] } }, "writer": { "name": "oceanbasev10writer", "parameter": { "obWriteMode": "insert", "column": [ "*" ], "preSql": [ "truncate table T" ], "connection": [ { "jdbcUrl": "||_dsc_ob10_dsc_||jingbo_ob:ob_oracle||_dsc_ob10_dsc_||jdbc:oceanbase://10.186.65.22:2883/ZLM?useLocalSessionState=true&allowBatch=true&allowMultiQueries=true&rewriteBatchedStatements=true", "table": [ "T" ] } ], "username": "ZLM", "password":"zlm", "writerThreadCount":10, "batchSize": 1000, "memstoreThreshold": "0.9" } } } ] }}-- ./bin/datax.py job/t.jsonDataX (20220610-external), From Alibaba !Copyright (C) 2010-2017, Alibaba Group. All Rights Reserved.full db is not specified.schema sync is not specified.java -server -Xms4g -Xmx16g -XX:+HeapDumpOnOutOfMemoryError -XX:HeapDumpPath=/home/admin/datax3/log -DENGINE_VERSION=20220610-external -Xms4g -Xmx16g -XX:+HeapDumpOnOutOfMemoryError -XX:HeapDumpPath=/home/admin/datax3/log -Dloglevel=info -Dproject.name=di-service -Dfile.encoding=UTF-8 -Dlogback.statusListenerClass=ch.qos.logback.core.status.NopStatusListener -Djava.security.egd=file:///dev/urandom -Ddatax.home=/home/admin/datax3 -Dlogback.configurationFile=/home/admin/datax3/conf/logback.xml -classpath /home/admin/datax3/lib/*:. -Dlog.file.name=in_datax3_job_t_json com.alibaba.datax.core.Engine -mode standalone -jobid -1 -job /home/admin/datax3/job/t.json -fulldb false -schema false2023-08-16 14:58:41.088 [main] INFO Engine - running job from /home/admin/datax3/job/t.json2023-08-16 14:58:41.374 [main] INFO VMInfo - VMInfo# operatingSystem class => sun.management.OperatingSystemImpl2023-08-16 14:58:41.382 [main] INFO Engine - the machine info => ... 2. record average count and max count task info :PHASE | AVERAGE RECORDS | AVERAGE BYTES | MAX RECORDS | MAX RECORD`S BYTES | MAX TASK ID | MAX TASK INFO READ_TASK_DATA | 3 | 6B | 3 | 6B | 0-0-0 | t,jdbcUrl:[jdbc:mysql://10.186.60.68:3332/zlm] 2023-08-16 14:58:45.189 [job-0] INFO MetricReportUtil - reportJobMetric is turn off2023-08-16 14:58:45.189 [job-0] INFO StandAloneJobContainerCommunicator - Total 3 records, 6 bytes | Speed 3B/s, 1 records/s | Error 0 records, 0 bytes | All Task WaitWriterTime 0.000s | All Task WaitReaderTime 0.000s | Percentage 100.00%2023-08-16 14:58:45.190 [job-0] INFO LogReportUtil - report datax log is turn off2023-08-16 14:58:45.190 [job-0] INFO JobContainer - : 2023-08-16 14:58:41 : 2023-08-16 14:58:45 : 3s : 3B/s : 1rec/s : 3 : 02023-08-16 14:58:45.190 [job-0] INFO PerfTrace - reset PerfTrace.SYS[ZLM]> select * from t;+----+------+| ID | NAME |+----+------+| 1 | a || 2 | b || 3 | c |+----+------+3 rows in set (0.01 sec)SYS[ZLM]> insert into t(name) values('d');Query OK, 1 row affected (0.02 sec)SYS[ZLM]> select * from t;+----+------+| ID | NAME |+----+------+| 1 | a || 2 | b || 3 | c || 4 | d |+----+------+4 rows in set (0.00 sec)ID NEXTVAL
NEXTVAL DEFAULT
GENERATED BY DEFAULT AS IDENTITY
-- ZLM[ZLM]> DROP TABLE T;Query OK, 0 rows affected (0.10 sec)ZLM[ZLM]> CREATE TABLE "ZLM"."T" ( -> "ID" NUMBER(19,0) GENERATED BY DEFAULT AS IDENTITY MINVALUE 1 MAXVALUE 999999, -> "NAME" VARCHAR2(30 BYTE), -> CONSTRAINT "PRIMARY" PRIMARY KEY ("ID"));Query OK, 0 rows affected (0.15 sec)-- ZLM[ZLM]> SELECT SEQUENCE_NAME,MIN_VALUE,LAST_NUMBER,CYCLE_FLAG,ORDER_FLAG,CACHE_SIZE FROM DBA_SEQUENCES WHERE SEQUENCE_OWNER='ZLM';+-----------------+-----------+-------------+------------+------------+------------+| SEQUENCE_NAME | MIN_VALUE | LAST_NUMBER | CYCLE_FLAG | ORDER_FLAG | CACHE_SIZE |+-----------------+-----------+-------------+------------+------------+------------+| SEQ_T_ID | 1 | 21 | N | N | 20 || ISEQ$$_50034_16 | 1 | 1 | N | N | 20 |+-----------------+-----------+-------------+------------+------------+------------+2 rows in set (0.00 sec)# ISEQ$$_50034_16 -- ZLM[ZLM]> desc t;+-------+--------------+------+-----+------------------+-------+| FIELD | TYPE | NULL | KEY | DEFAULT | EXTRA |+-------+--------------+------+-----+------------------+-------+| ID | NUMBER(19) | NO | PRI | SEQUENCE.NEXTVAL | NULL || NAME | VARCHAR2(30) | YES | NULL| NULL | NULL |+-------+--------------+------+-----+------------------+-------+2 rows in set (0.02 sec)# ID DEFAULT SEQUENCE.NEXTVAL ISEQ$$_50034_16.NEXTVAL-- ./bin/datax.py job/t.json ...-- ZLM[ZLM]> insert into t(name) values('d'); ORA-00001: unique constraint '1' for key 'PRIMARY' violatedZLM[ZLM]> insert into t(name) values('d'); ORA-00001: unique constraint '2' for key 'PRIMARY' violatedZLM[ZLM]> insert into t(name) values('d'); ORA-00001: unique constraint '3' for key 'PRIMARY' violatedZLM[ZLM]> insert into t(name) values('d'); Query OK, 1 row affected (0.01 sec)ZLM[ZLM]> select "ISEQ$$_50034_16".CURRVAL from dual;+---------+| CURRVAL |+---------+| 4 |+---------+1 row in set (0.00 sec)3 START WITH 1
ID NEXTVAL 4
- NEXTVAL =
- START WITH
GENERATED BY DEFAULT AS IDENTITY
ORA-32794: cannot drop a system-generated sequence
1MAX
[email protected] [zlm]> SELECT MAX(id)+1 as AUTO_INCREMENT FROM t;+----------------+| AUTO_INCREMENT |+----------------+| 4 |+----------------+1 row in set (0.00 sec)2
zlm@10.186.60.68 [zlm]> select AUTO_INCREMENT from information_schema.tables where table_name='t';+----------------+| AUTO_INCREMENT |+----------------+| 4 |+----------------+1 row in set (0.00 sec)3show create table
[email protected] [zlm]> show create table tG*************************** 1. row *************************** Table: tCreate Table: CREATE TABLE `t` ( `id` bigint(20) NOT NULL AUTO_INCREMENT, `name` varchar(10) DEFAULT NULL, PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf81 row in set (0.00 sec)-- ZLM[ZLM]> drop table t;Query OK, 0 rows affected (0.02 sec)ZLM[ZLM]> CREATE TABLE "ZLM"."T" ( -> "ID" NUMBER(19,0) GENERATED BY DEFAULT AS IDENTITY MINVALUE 1 MAXVALUE 999999, -> "NAME" VARCHAR2(30 BYTE), -> CONSTRAINT "PRIMARY" PRIMARY KEY ("ID"));Query OK, 0 rows affected (0.04 sec)-- ./bin/datax.py job/t.json ...-- [root@10-186-65-73 ~]# cat init_sequence.sh#!/bin/bash## i=$(obclient -h10.186.60.68 -P3332 -uzlm -pzlm -Nse "SELECT MAX(id)+1 FROM zlm.t;" 2>/dev/null | head -1)## SQLfor ((j=1; j<=$i; j++))do obclient -h10.186.65.43 -P2883 -uZLM@ob_oracle#bobo_ob:1675327512 -pzlm -A -c -DZLM -Nse "select ISEQ$$_50037_16.nextval from dual;" 1>/dev/null 2>&1done[root@10-186-65-73 ~]# sh init_sequence.sh [root@10-186-65-73 ~]# echo $?0-- ZLM[ZLM]> insert into t(name) values('d');Query OK, 1 row affected (0.01 sec)START WITH
GENERATED BY DEFAULT AS IDENTITY
OB Oracle GENERATED BY DEFAULT AS IDENTITY
START WITH
GENERATED BY DEFAULT AS IDENTITY
- START WITH
GENERATED BY DEFAULT AS IDENTITY GENERATED BY DEFAULT AS IDENTITY
GENERATED BY DEFAULT AS IDENTITY
https://opensource.actionsky.com/
Disclaimer: The content of this article is sourced from the internet. The copyright of the text, images, and other materials belongs to the original author. The platform reprints the materials for the purpose of conveying more information. The content of the article is for reference and learning only, and should not be used for commercial purposes. If it infringes on your legitimate rights and interests, please contact us promptly and we will handle it as soon as possible! We respect copyright and are committed to protecting it. Thank you for sharing.(Email:[email protected])
Guess you like
-
Foxconn Halts Nissan Acquisition Bid Amidst Pivotal Honda Merger Talks
-
OpenAI Unveils Next-Generation Reasoning Model Series: o3 and o3-mini
-
Intel Executives Accused of Mismanagement, Facing Massive Lawsuit from Shareholder LRTrust
-
-
-
AWE's Rise: Seres and Huawei Forge a New Benchmark for Luxury Intelligent Vehicles in China
-
Apple in Talks with Tencent for AI Model Integration into iPhones in China? Tencent Remains Silent
-
Slow Waves in Deep Sleep: The Unsung Heroes of Memory Consolidation
-
-
-
-
-
The US-China Chip War: A Technological Contest Without Gunfire
-
-
-
NVIDIA's Troubles in China: Antitrust Investigation and Supply Cut Rumors Shake the Market
-
The Collapse of Jietue Auto: Failure Despite Backing from Giants, and the Employees' Plight
-
-
-
