Home Page > News List > cloudtech >> cloudtech

Practice of self increasing primary keys in MySQL migration to OBOracle scenarios

cloudtech Source: Network

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 t

DBCAT 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

  1. NEXTVAL =
  2. 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])

Practice of self increasing primary keys in MySQL migration

Guess you like