华为云用户手册

  • FULLTEXT全文索引 GaussDB (DWS)不支持FULLTEXT全文索引。DSC工具迁移时会根据GaussDB(DWS)的特性进行相应适配。 内联FULLTEXT全文索引。 输入示例 1 2 3 4 5 6 7 8 9101112131415161718192021222324 ## A.CREATE TABLE `public`.`test_create_table02` (`ID` INT(11) NOT NULL PRIMARY KEY,`TITLE` CHAR(255) NOT NULL,`CONTENT` TEXT NULL,`CREATE_TIME` DATETIME NULL DEFAULT NULL, FULLTEXT (`CONTENT`));## B. CREATE TABLE IF NOT EXISTS `public`.`runoob_dataType_test`( `id` INT PRIMARY KEY AUTO_INCREMENT, `name` VARCHAR(128) NOT NULL, FULLTEXT INDEX (name));## C.CREATE TABLE IF NOT EXISTS `public`.`runoob_dataType_test`( `id` INT PRIMARY KEY AUTO_INCREMENT, `name` VARCHAR(128) NOT NULL, FULLTEXT INDEX (name ASC)); 输出示例 1 2 3 4 5 6 7 8 910111213141516171819202122232425262728293031323334 -- A.CREATE TABLE "public"."test_create_table02"( "id" INTEGER NOT NULL PRIMARY KEY, "title" CHAR(1020) NOT NULL, "content" TEXT, "create_time" TIMESTAMP WITHOUT TIME ZONE DEFAULT NULL) WITH ( ORIENTATION = ROW, COMPRESSION = NO ) NOCOMPRESS DISTRIBUTE BY HASH ("id");CREATE INDEX "idx_test_create_table02_content" ON "public"."test_create_table02" USING GIN(to_tsvector(coalesce("content",'')));-- B. CREATE TABLE IF NOT EXISTS "public"."runoob_datatype_test"( "id" SERIAL PRIMARY KEY, "name" VARCHAR(512) NOT NULL) WITH ( ORIENTATION = ROW, COMPRESSION = NO ) NOCOMPRESS DISTRIBUTE BY HASH ("id");CREATE INDEX "idx_runoob_datatype_test_name" ON "public"."runoob_datatype_test" USING GIN(to_tsvector(coalesce("name",'')));-- C.CREATE TABLE IF NOT EXISTS "public"."runoob_datatype_test"( "id" SERIAL PRIMARY KEY, "name" VARCHAR(512) NOT NULL) WITH ( ORIENTATION = ROW, COMPRESSION = NO ) NOCOMPRESS DISTRIBUTE BY HASH ("id");CREATE INDEX "idx_runoob_datatype_test_name" ON "public"."runoob_datatype_test" USING GIN(to_tsvector(coalesce("name",''))); ALTER TABLE创建全文索引。 输入示例 123456789 CREATE TABLE `public`.`test_create_table05` ( `ID` INT(11) NOT NULL AUTO_INCREMENT, `USER_ID` INT(20) NOT NULL, `USER_NAME` CHAR(20) NULL DEFAULT NULL, `DETAIL` VARCHAR(100) NULL DEFAULT NULL, PRIMARY KEY (`ID`));ALTER TABLE TEST_CREATE_TABLE05 ADD FULLTEXT INDEX USER_ID_INDEX_02(USER_ID);ALTER TABLE TEST_CREATE_TABLE05 ADD FULLTEXT USER_NAME_INDEX_02(USER_NAME); 输出示例 1 2 3 4 5 6 7 8 910111213 CREATE TABLE "public"."test_create_table05"( "id" SERIAL NOT NULL, "user_id" INTEGER NOT NULL, "user_name" CHAR(80) DEFAULT NULL, "detail" VARCHAR(400) DEFAULT NULL, PRIMARY KEY ("id")) WITH ( ORIENTATION = ROW, COMPRESSION = NO ) NOCOMPRESS DISTRIBUTE BY HASH ("id");CREATE INDEX "user_id_index_02" ON "public"."test_create_table05" USING GIN(to_tsvector(coalesce("user_id",'')));CREATE INDEX "user_name_index_02" ON "public"."test_create_table05" USING GIN(to_tsvector(coalesce("user_name",''))); CREATE INDEX创建全文索引。 输入示例 1234567 CREATE TABLE `public`.`test_index_table02` (`ID` INT(11) NOT NULL PRIMARY KEY,`TITLE` CHAR(255) NOT NULL,`CONTENT` TEXT NULL,`CREATE_TIME` INT(10) NULL DEFAULT NULL);CREATE FULLTEXT INDEX CON_INDEX ON TEST_INDEX_TABLE02(CONTENT); 输出示例 1 2 3 4 5 6 7 8 91011 CREATE TABLE "public"."test_index_table02"( "id" INTEGER NOT NULL PRIMARY KEY, "title" CHAR(1020) NOT NULL, "content" TEXT, "create_time" INTEGER DEFAULT NULL) WITH ( ORIENTATION = ROW, COMPRESSION = NO ) NOCOMPRESS DISTRIBUTE BY HASH ("id");CREATE INDEX "con_index" ON "public"."test_index_table02" USING GIN(to_tsvector(coalesce("content",''))); 父主题: 索引
  • 索引重命名 DSC工具支持索引重命名,对索引名前加表名前缀防止索引名冲突(只支持创建有具体索引名的DDL语句,目前不支持删除索引的重命名,修改该参数需慎重)。 修改配置 打开表1 features-mysql.properties文件中的配置参数配置文件,修改如下参数为true。(默认false:不进行重命名) 12 # 创建索引时,是否重新命名索引名table.index.rename=true 输入示例 123456 CREATE TABLE IF NOT EXISTS `CUSTOMER`( `NAME` VARCHAR(64) PRIMARY KEY, ID INTEGER, ID2 INTEGER);CREATE INDEX ID_INDEX USING BTREE ON CUSTOMER (ID);ALTER TABLE CUSTOMER ADD INDEX ID3_INDEX(ID2); 输出示例 123456 CREATE TABLE IF NOT EXISTS "public"."customer" ( "name" VARCHAR(256) PRIMARY KEY, "id" INTEGER, "id2" INTEGER) WITH (ORIENTATION = ROW, COMPRESSION = NO) NOCOMPRESS DISTRIBUTE BY HASH ("name");CREATE INDEX customer_id_index ON "public"."customer" USING BTREE ("id");CREATE INDEX customer_id3_index ON "public"."customer" ("id2"); 父主题: 索引
  • 数据库 在MySQL中,DATABASE 是一种模式对象,等同于Oracle、GaussDB(DWS)数据库的SCHEMA概念。DSC工具迁移时考虑了以下两个场景。 创建数据库 输入示例 create database IF NOT EXISTS dbname1 CHARACTER SET=utf8 COLLATE=utf8_unicode_ci;create database IF NOT EXISTS dbname2;drop database if exists dbname1;drop database if exists dbname2; 输出示例 CREATE SCHEMA "dbname1";CREATE SCHEMA "dbname2";DROP SCHEMA IF EXISTS "dbname1";DROP SCHEMA IF EXISTS "dbname2"; 使用数据库 输入示例 drop database if exists test;create database if not exists test;use test; 输出示例 DROP SCHEMA IF EXISTS "test";CREATE SCHEMA "test";SET CURRENT_SCHEMA = "test"; 父主题: MySQL语法迁移
  • GROUP BY转换 MySQL/ADB分组查询的时候允许查询非分组字段,不报错;DWS分组查询时只能查询分组字段和聚集函数,报错。因此补齐没有group by的查询分组字段。 输入示例 1 SELECT e.department_id, department_name, ROUND(AVG(salary), 0) avg_salary FROM employees e JOIN departments d on e.department_id = d.department_id GROUP BY department_name ORDER BY department_name; 输出示例 1 2 3 4 5 6 7 8 9101112 SELECT e.department_id, department_name, ROUND (AVG(salary), 0) AS "avg_salary"FROM employees "e" JOIN departments "d" ON e.department_id = d.department_idGROUP BY department_name, 1ORDER BY department_name; 父主题: 数据操作语句(DML)
  • 术语表 下表包含缩略语、术语及其说明。 术语 描述 C 公用表表达式(CTE) 公用表表达式是一个在查询中定义的临时命名结果集,仅可用于更大的查询范围。 D 数据库(DB) 数据库是一组相关信息的集合,通常是为了使通用的检索变得简单和高效而组织起来的。 数据库属性: 数据库名称。 Endian文件格式(BIG_ENDIAN大端或LITTLE_ENDIAN小端)。 关系。 不存在无关系的数据库。 数据库管理员(DBA) 数据库管理员是负责组织中数据库的安装、配置、升级、管理、监控和维护的人员。 该角色包括开发和设计数据库策略、监控和优化数据库性能和容量,以及规划未来的扩展需求。数据库管理员亦可计划、协调和实施安全措施,以保障资料库的安全。 E 编码 在信息处理中,编码是一种规则系统,它把字母、单词、声音、图像或手势等信息转换成另一种规则。有时,它以缩短或秘密的形式通过通道进行通讯或存储在介质中。 I 索引 数据库管理系统中的一种有序数据结构,可加速表内数据的查询和更新。 M 迁移 迁移是指将源数据库(如Teradata)中的脚本、查询、模式、数据等迁移到目标数据库(如GaussDB (DWS))。 元数据 元数据是关于数据的数据。元数据定义了数据的属性,用于指定数据的存储位置、历史数据、检索资源数据、记录信息等。 O 操作系统(OS) 操作系统是管理计算机中所有其他程序的程序,这些程序最初通过引导程序加载到计算机中。 Q 查询 查询是向数据库发出的信息请求。查询执行SQL语句,并返回该语句定义的结果集。 S 结构化查询语言(SQL) 一种编程语言,广泛用于访问、更新、管理和查询关系数据库中的数据。 模式 模式是数据库管理系统支持的正式语言中描述的结构。它是指数据的组织,描述数据库是如何构建的。(在关系数据库中,它描述了如何将数据库划分为表。) T Teradata Teradata是一种关系数据库管理系统。它可用于同时运行多个复杂查询。它支持使用SQL的即席查询,并广泛用于管理大型仓储操作。 表 紧密相关的列的集合。表由包含相同列的不同值的行组成。 V 视图 视图限制对表的特定行或列的访问。视图可以从一个或多个表中创建,并且由用于创建视图的查询决定。 父主题: DSC
  • SET MySQL REPLACE支持使用SET设置值,DSC工具将对其转换。 输入 12345 replace INTO `runoob_datatype_test` VALUES (100, 100, 100, 0, 1);replace INTO `runoob_datatype_test` VALUES (100.23, 100.25, 100.26, 0.12,1.5);replace INTO `runoob_datatype_test` (dataType_numeric,dataType_numeric1) VALUES (100.23, 100.25);replace INTO `runoob_datatype_test` (dataType_numeric,dataType_numeric1,dataType_numeric2) VALUES (100.23, 100.25, 2.34);replace into runoob_datatype_test set dataType_numeric=23.1, dataType_numeric4 = 25.12 ; 输出 12345 INSERT INTO "public"."runoob_datatype_test" VALUES (100,100,100,0,1);INSERT INTO "public"."runoob_datatype_test" VALUES (100.23,100.25,100.26,0.12,1.5);INSERT INTO "public"."runoob_datatype_test" ("datatype_numeric","datatype_numeric1") VALUES (100.23,100.25);INSERT INTO "public"."runoob_datatype_test" ("datatype_numeric","datatype_numeric1","datatype_numeric2") VALUES (100.23,100.25,2.34);INSERT INTO "public"."runoob_datatype_test" ("datatype_numeric","datatype_numeric4") VALUES (23.1,25.12);
  • VALUES MySQL REPLACE支持一条语句插入或删除多值,以逗号分隔。 输入 12345 #有数据的话则替换replace,没有的话则插入新的数据同INSERTReplace INTO exmp_tb1 (tb1_id,tb1_name,tb1_gender,tb1_address,tb1_number) VALUES(17,'David','male','NewYork11','01015827875'),(18,'Rachel','female','NewYork22','01015827749'),(20,'Monica','female','NewYork','010158996743');Replace INTO exmp_tb1 (tb1_id,tb1_name,tb1_gender,tb1_address,tb1_number) VALUES(17,'David1','male','NewYork11','01015827875'),(21,'Rachel','female','NewYork22','01015827749'),(22,'Monica','female','NewYork','010158996743');Replace INTO exmp_tb1 (tb1_id,tb1_name,tb1_gender,tb1_address,tb1_number,tb1_date) VALUES(17,'David2',DEFAULT,'NewYork11','01015827875',DEFAULT),(18,'Rachel','female',DEFAULT,'01015827749','2018-12-14 10:44:20'),(DEFAULT,'Monica','female',DEFAULT,DEFAULT,'2018-12-14 10:44:20');Replace INTO exmp_tb1 VALUES(DEFAULT,'David',DEFAULT,'NewYork11','01015827875',DEFAULT),(18,'Rachel','female',DEFAULT,'01015827749','2018-12-14 10:44:20'),(DEFAULT,'Monica','female',DEFAULT,DEFAULT,'2018-12-14 10:44:20'); 输出 1 2 3 4 5 6 7 8 910111213 --有数据的话则替换replace,没有的话则插入新的数据同INSERTINSERT INTO "public"."exmp_tb1" ("tb1_id","tb1_name","tb1_gender","tb1_address","tb1_number") VALUES (17,'David','male','NewYork11','01015827875');INSERT INTO "public"."exmp_tb1" ("tb1_id","tb1_name","tb1_gender","tb1_address","tb1_number") VALUES (18,'Rachel','female','NewYork22','01015827749');INSERT INTO "public"."exmp_tb1" ("tb1_id","tb1_name","tb1_gender","tb1_address","tb1_number") VALUES (20,'Monica','female','NewYork','010158996743');INSERT INTO "public"."exmp_tb1" ("tb1_id","tb1_name","tb1_gender","tb1_address","tb1_number") VALUES (17,'David1','male','NewYork11','01015827875');INSERT INTO "public"."exmp_tb1" ("tb1_id","tb1_name","tb1_gender","tb1_address","tb1_number") VALUES (21,'Rachel','female','NewYork22','01015827749');INSERT INTO "public"."exmp_tb1" ("tb1_id","tb1_name","tb1_gender","tb1_address","tb1_number") VALUES (22,'Monica','female','NewYork','010158996743');INSERT INTO "public"."exmp_tb1" ("tb1_id","tb1_name","tb1_gender","tb1_address","tb1_number","tb1_date") VALUES (17,'David2',DEFAULT,'NewYork11','01015827875',DEFAULT);INSERT INTO "public"."exmp_tb1" ("tb1_id","tb1_name","tb1_gender","tb1_address","tb1_number","tb1_date") VALUES (18,'Rachel','female',DEFAULT,'01015827749','2018-12-14 10:44:20');INSERT INTO "public"."exmp_tb1" ("tb1_id","tb1_name","tb1_gender","tb1_address","tb1_number","tb1_date") VALUES (DEFAULT,'Monica','female',DEFAULT,DEFAULT,'2018-12-14 10:44:20');INSERT INTO "public"."exmp_tb1" VALUES (DEFAULT,'David',DEFAULT,'NewYork11','01015827875',DEFAULT);INSERT INTO "public"."exmp_tb1" VALUES (18,'Rachel','female',DEFAULT,'01015827749','2018-12-14 10:44:20');INSERT INTO "public"."exmp_tb1" VALUES (DEFAULT,'Monica','female',DEFAULT,DEFAULT,'2018-12-14 10:44:20');
  • DELAYED DELAYED插入和替换在MySQL 5.6中被弃用。在MySQL 5.7中,DELAYED不支持。服务器识别但忽略DELAYED关键字,将替换处理为非延迟替换,并生成ER_WARN_LEGACY_SYNTAX_CONVERTED警告。 REPLACE DELAYED不再被支持,语句被转换为REPLACE。 DELAYED 关键字将在未来版本中被删除。 输入 123456789 #DELAYED INSERT DELAYED works only with MyISAM, MEMORY, ARCHIVE, and BLACKHOLE tables. #If you execute INSERT DELAYED with another storage engine, #you will get an error like this: ERROR 1616 (HY000): DELAYED option not supported Replace DELAYED INTO exmp_tb2 VALUES(10, 128.23, 'nice', '2018-10-11 19:00:00');Replace DELAYED INTO exmp_tb2 VALUES(6, DEFAULT, 'nice', '2018-12-14 19:00:00');Replace DELAYED INTO exmp_tb2 VALUES(7, 20, 'nice', DEFAULT);Replace DELAYED INTO exmp_tb2 (tb2_id, tb2_price) VALUES(11, DEFAULT);Replace DELAYED INTO exmp_tb2 (tb2_id, tb2_price, tb2_note) VALUES(12, DEFAULT, DEFAULT);Replace DELAYED INTO exmp_tb2 (tb2_id, tb2_price, tb2_note, tb2_date) VALUES(13, DEFAULT, DEFAULT, DEFAULT); 输出 123456789 --DELAYED INSERT DELAYED works only with MyISAM, MEMORY, ARCHIVE, and BLACKHOLE tables. --If you execute INSERT DELAYED with another storage engine, --you will get an error like this: ERROR 1616 (HY000): DELAYED option not supported. INSERT INTO "public"."exmp_tb2" VALUES (10,128.23,'nice','2018-10-11 19:00:00');INSERT INTO "public"."exmp_tb2" VALUES (6,DEFAULT,'nice','2018-12-14 19:00:00');INSERT INTO "public"."exmp_tb2" VALUES (7,20,'nice',DEFAULT);INSERT INTO "public"."exmp_tb2" ("tb2_id","tb2_price") VALUES (11,DEFAULT);INSERT INTO "public"."exmp_tb2" ("tb2_id","tb2_price","tb2_note") VALUES (12,DEFAULT,DEFAULT);INSERT INTO "public"."exmp_tb2" ("tb2_id","tb2_price","tb2_note","tb2_date") VALUES (13,DEFAULT,DEFAULT,DEFAULT);
  • PARTITION MySQL REPLACE支持使用PARTITION关键字和分区,子分区或两者的逗号分隔名称列表显式分区选择。 输入 12345 replace INTO employees PARTITION(p3) VALUES (19, 'Frank1', 'Williams', 1, 2);replace INTO employees PARTITION(p0) VALUES (4, 'Frank1', 'Williams', 1, 2);replace INTO employees PARTITION(p1) VALUES (9, 'Frank1', 'Williams', 1, 2);replace INTO employees PARTITION(p2) VALUES (10, 'Frank1', 'Williams', 1, 2);replace INTO employees PARTITION(p2) VALUES (11, 'Frank1', 'Williams', 1, 2); 输出 12345 INSERT INTO "public"."employees" VALUES (19,'Frank1','Williams',1,2);INSERT INTO "public"."employees" VALUES (4,'Frank1','Williams',1,2);INSERT INTO "public"."employees" VALUES (9,'Frank1','Williams',1,2);INSERT INTO "public"."employees" VALUES (10,'Frank1','Williams',1,2);INSERT INTO "public"."employees" VALUES (11,'Frank1','Williams',1,2);
  • LOW_PRIORITY MySQL REPLACE支持使用LOW_PRIORITY ,DSC工具将对其进行转换。 输入 123456 # LOW_PRIORITY 低优先级Replace LOW_PRIORITY INTO exmp_tb2 VALUES(1, '128.23', 'nice', '2018-10-11 19:00:00');Replace LOW_PRIORITY INTO exmp_tb2 VALUES(2, DEFAULT, 'nice', '2018-12-14 19:00:00' );Replace LOW_PRIORITY INTO exmp_tb2 VALUES(3, DEFAULT, 'nice', DEFAULT);Replace LOW_PRIORITY INTO exmp_tb2 (tb2_id, tb2_price) VALUES(5, DEFAULT);Replace LOW_PRIORITY INTO exmp_tb2 (tb2_id, tb2_price, tb2_note) VALUES(4, DEFAULT, DEFAULT); 输出 123456 -- LOW_PRIORITY 低优先级INSERT INTO "public"."exmp_tb2" VALUES (1,'128.23','nice','2018-10-11 19:00:00');INSERT INTO "public"."exmp_tb2" VALUES (2,DEFAULT,'nice','2018-12-14 19:00:00');INSERT INTO "public"."exmp_tb2" VALUES (3,DEFAULT,'nice',DEFAULT);INSERT INTO "public"."exmp_tb2" ("tb2_id","tb2_price") VALUES (5,DEFAULT);INSERT INTO "public"."exmp_tb2" ("tb2_id","tb2_price","tb2_note") VALUES (4,DEFAULT,DEFAULT);
  • ROLLUP MySQL中的group by column with rollup需要转换为GaussDB(DWS)中的group by rollup (column); 输入示例 123 select id,product_id,count(1) from czb_account.equity_account_logwhere id in (6957343,6957397,6957519,6957541,6957719)group by 1, 2 with rollup; 输出示例 1 2 3 4 5 6 7 8 910 SELECT id, product_id, count(1)FROM czb_account.equity_account_logWHERE id IN (6957343, 6957397, 6957519, 6957541, 6957719)GROUP BY ROLLUP(1, 2); 父主题: 数据操作语句(DML)
  • INTERVAL MySQL中使用interval表达式格式为INTERVAL N时间单位,DWS不支持,需要转换为INTERVAL 'N'时间单位。 输入示例 123456 SELECT CURRENT_TIME() - INTERVAL 4 DAY;SELECT NOW() - INTERVAL 5 HOUR;SELECT CURRENT_TIME() - INTERVAL '4' DAY;SELECT NOW() - INTERVAL '5' HOUR;SELECT CURRENT_TIME() - INTERVAL "4" DAY;SELECT NOW() - INTERVAL "5" HOUR; 输出示例 123456 SELECT (CURRENT_TIME () - INTERVAL '4' DAY);SELECT (NOW () - INTERVAL '5' HOUR);SELECT (CURRENT_TIME () - INTERVAL '4' DAY);SELECT (NOW () - INTERVAL '5' HOUR);SELECT (CURRENT_TIME () - INTERVAL '4' DAY);SELECT (NOW () - INTERVAL '5' HOUR); 父主题: 数据操作语句(DML)
  • 创建连接故障 gsql: could not connect to server: No route to host 此问题一般是指定了不可达的地址或者端口导致的。请检查-h参数与-p参数是否添加正确。 gsql: FATAL: Invalid username/password,login denied. 此问题一般是输入了错误的用户名和密码导致的,请联系数据库管理员,确认用户名和密码的正确性。 The "libpq.so" loaded mismatch the version of gsql, please check it. 此问题是由于环境中使用的libpq.so的版本与gsql的版本不匹配导致的,请通过“ldd gsql”命令确认当前加载的libpq.so的版本,并通过修改LD_LIBRARY_PATH环境变量来加载正确的libpq.so。 gsql: symbol lookup error: xxx/gsql: undefined symbol: libpqVersionString 此问题是由于环境中使用的libpq.so的版本与gsql的版本不匹配导致的(也有可能是环境中存在PostgreSQL的libpq.so),请通过“ldd gsql”命令确认当前加载的libpq.so的版本,并通过修改LD_LIBRARY_PATH环境变量来加载正确的libpq.so。 gsql: connect to server failed: Connection timed out Is the server running on host "xx.xxx.xxx.xxx" and accepting TCP/IP connections on port xxxx? 此问题是由于网络连接故障造成。请检查客户端与数据库服务器间的网络连接。如果发现从客户端无法PING到数据库服务器端,则说明网络连接出现故障。请联系网络管理人员排查解决。 ping -c 4 10.10.10.1PING 10.10.10.1 (10.10.10.1) 56(84) bytes of data.From 10.10.10.1: icmp_seq=2 Destination Host UnreachableFrom 10.10.10.1 icmp_seq=2 Destination Host UnreachableFrom 10.10.10.1 icmp_seq=3 Destination Host UnreachableFrom 10.10.10.1 icmp_seq=4 Destination Host Unreachable--- 10.10.10.1 ping statistics ---4 packets transmitted, 0 received, +4 errors, 100% packet loss, time 2999ms gsql: FATAL: permission denied for database "postgres" DETAIL: User does not have CONNECT privilege. 此问题是由于用户不具备访问该数据库的权限,可以使用如下方法解决。 使用管理员用户dbadmin连接数据库。 gsql -d postgres -U dbadmin -p 8000 赋予该用户访问数据库的权限。 GRANT CONNECT ON DATABASE postgres TO user1; 实际上,常见的许多错误操作也可能产生用户无法连接上数据库的现象。如用户连接的数据库不存在,用户名或密码输入错误等。这些错误操作在客户端工具也有相应的提示信息。 gsql -d postgres -p 8000gsql: FATAL: database "postgres" does not existgsql -d postgres -U user1 -W gauss@789 -p 8000gsql: FATAL: Invalid username/password,login denied. gsql: FATAL: sorry, too many clients already,active/non-active: 197/3. 此问题是由于系统连接数量超过了最大连接数量。请联系数据库DBA进行会话连接数管理,释放无用会话。 关于查看用户会话连接数的方法如表1。 会话状态可以在视图PG_STAT_ACTIVITY中查看。无用会话可以使用函数pg_terminate_backend进行释放。 select datid,pid,state from pg_stat_activity; datid | pid | state -------+-----------------+-------- 13205 | 139834762094352 | active 13205 | 139834759993104 | idle(2 rows) 其中pid的值即为该会话的线程ID。根据线程ID结束会话。 SELECT PG_TERMINATE_BACKEND(139834759993104); 显示类似如下信息,表示结束会话成功。 PG_TERMINATE_BACKEND---------------------- t(1 row) 表1 查看会话连接数 描述 命令 查看指定用户的会话连接数上限。 执行如下命令查看连接到指定用户USER1的会话连接数上限。其中-1表示没有对用户user1设置连接数的限制。 SELECT ROLNAME,ROLCONNLIMIT FROM PG_ROLES WHERE ROLNAME='user1'; rolname | rolconnlimit---------+-------------- user1 | -1(1 row) 查看指定用户已使用的会话连接数。 执行如下命令查看指定用户USER1已使用的会话连接数。其中,1表示USER1已使用的会话连接数。 SELECT COUNT(*) FROM V$SESSION WHERE USERNAME='user1'; count------- 1(1 row) 查看指定数据库的会话连接数上限。 执行如下命令查看连接到指定数据库postgres的会话连接数上限。其中-1表示没有对数据库postgres设置连接数的限制。 SELECT DATNAME,DATCONNLIMIT FROM PG_DATABASE WHERE DATNAME='postgres'; datname | datconnlimit----------+-------------- postgres | -1(1 row) 查看指定数据库已使用的会话连接数。 执行如下命令查看指定数据库postgres上已使用的会话连接数。其中,1表示数据库postgres上已使用的会话连接数。 SELECT COUNT(*) FROM PG_STAT_ACTIVITY WHERE DATNAME='postgres'; count ------- 1(1 row) 查看所有用户已使用会话连接数。 执行如下命令查看所有用户已使用的会话连接数。 SELECT COUNT(*) FROM V$SESSION; count------- 10(1 row) gsql: wait xxx.xxx.xxx.xxx:xxxx timeout expired gsql在向数据库发起连接的时候,会有5分钟超时机制,如果在这个超时时间内,数据库未能正常的对客户端请求进行校验和身份认证,那么gsql会退出当前会话的连接过程,并报出如上错误。 一般来说,此问题是由于连接时使用的-h参数及-p参数指定的连接主机及端口有误(即错误信息中的xxx部分),导致通信故障;极少数情况是网络故障导致。要排除此问题,请检查数据库的主机名及端口是否正确。 gsql: could not receive data from server: Connection reset by peer. 同时,检查CN日志中出现类似如下日志“ FATAL: cipher file "/data/coordinator/server.key.cipher" has group or world access”,一般是由于数据目录或部分关键文件的权限被误操作篡改导致。请参照其他正常实例下的相关文件权限,修改回来便可。 gsql: FATAL: GSS authentication method is not allowed because XXXX user password is not disabled. 目标CN的pg_hba.conf里配置了当前客户端IP使用"gss"方式来做认证,该认证算法不支持用作客户端的身份认证,请修改到"sha256"后再试。 请不要修改pg_hba.conf中数据库集群主机的相关设置,否则可能导致数据库功能故障。 建议业务应用部署在数据库集群之外,而非集群内部。
  • 连接性能问题 数据库内核执行初始化语句较慢导致的性能问题。 此种情况定位较难,可以尝试使用Linux的跟踪命令:strace。 strace gsql -U MyUserName -W {password} -d postgres -h 127.0.0.1 -p 23508 -r -c '\q' 此时便会在屏幕上打印出数据库的连接过程。比如较长时间停留在下面的操作上: sendto(3, "Q\0\0\0\25SELECT VERSION()\0", 22, MSG_NOSIGNAL, NULL, 0) = 22poll([{fd=3, events=POLLIN|POLLERR}], 1, -1) = 1 ([{fd=3, revents=POLLIN}]) 此时便可以确定是数据库执行"SELECT VERSION()"语句较慢。 在连接上数据库后,便可以通过执行“explain performance select version()”语句来确定初始化语句执行较慢的原因。更多信息请参考《开发指南》中的“SQL执行计划介绍”章节。 另外还有一种场景不太常见:由于数据库CN所在机器的磁盘满或故障,此时所查询等受影响,无法进行用户认证,导致连接过程挂起,表现为假死。解决此问题清理数据库CN的数据盘空间便可。 TCP连接创建较慢问题。 此问题可以参考上面的初始化语句较慢排查的做法,通过strace跟踪,如果长时间停留在: connect(3, {sa_family=AF_FILE, path="/home/test/tmp/gaussdb_llt1/.s.PGSQL.61052"}, 110) = 0 或者 connect(3, {sa_family=AF_INET, sin_port=htons(61052), sin_addr=inet_addr("127.0.0.1")}, 16) = -1 EINPROGRESS (Operation now in progress) 那么说明客户端与数据库端建立物理连接过慢,此时应当检查网络是否存在不稳定、网络吞吐量太大的问题。
  • 其他故障 出现因“总线错误”(Bus error)导致的core dump或异常退出 一般情况下出现此种问题,是进程运行过程中加载的共享动态库(在Linux为.so文件)出现变化;或者进程二进制文件本身出现变化,导致操作系统加载机器的执行码或者加载依赖库的入口发生变化,操作系统出于保护目的将进程终止,产生core dump文件。 解决此问题,重试便可。同时请尽可能避免在升级等运维操作过程中,在集群内部运行业务程序,避免升级时因替换文件产生此问题。 此故障的core dump文件的可能堆栈是dl_main及其子调用,它是操作系统用来初始化进程做共享动态库加载的。如果进程已经初始化,但是共享动态库还未加载完成,严格意义上来说,进程并未完全启动。
  • 任务示例 查看gsql的帮助信息。具体执行命令请参见表1。 表1 使用gsql联机帮助 描述 示例 查看版权信息 \copyright 查看GaussDB(DWS)支持的SQL语句的帮助 查看GaussDB(DWS)支持的SQL语句的帮助 例如,查看GaussDB(DWS)支持的所有SQL语句: 123456 \hAvailable help: ABORT ALTER DATABASE ALTER DATA SOURCE ... ... 例如,查看CREATE DATABASE命令的参数可使用下面的命令: 1 2 3 4 5 6 7 8 910111213 \help CREATE DATABASECommand: CREATE DATABASEDescription: create a new databaseSyntax:CREATE DATABASE database_name [ [ WITH ] {[ OWNER [=] user_name ]| [ TEMPLATE [=] template ]| [ ENCODING [=] encoding ]| [ LC_COLLATE [=] lc_collate ]| [ LC_CTYPE [=] lc_ctype ]| [ DBCOMPATIBILITY [=] compatibility_type ]| [ TABLESPACE [=] tablespace_name ]| [ CONNECTION LIMIT [=] connlimit ]}[...] ]; 查看gsql命令的帮助 例如,查看gsql支持的命令: 1234567 \?General \copyright show PostgreSQL usage and distribution terms \g [FILE] or ; execute query (and send results to file or |pipe) \h(\help) [NAME] help on syntax of SQL commands, * for all commands \q quit gsql... ...
  • 操作步骤 连接数据库时,可以使用如下命令获取帮助信息。 gsql --help 显示如下帮助信息: ......Usage: gsql [OPTION]... [DBNAME [USERNAME]]General options: -c, --command=COMMAND run only single command (SQL or internal) and exit -d, --dbname=DBNAME database name to connect to (default: "postgres") -f, --file=FILENAME execute commands from file, then exit...... 连接到数据库后,可以使用如下命令获取帮助信息。 help 显示如下帮助信息: You are using gsql, the command-line interface to gaussdb.Type: \copyright for distribution terms \h for help with SQL commands \? for help with gsql commands \g or terminate with semicolon to execute query \q to quit
  • Netezza配置 设置Netezza配置参数可在迁移Netezza数据库脚本时自定义迁移工具的行为。 打开config文件夹中的features-netezza.properties文件,并根据实际需要设置表1中的参数。 表1 features-netezza.properties文件中的配置参数 参数 说明 取值范围 默认值 样例 rowstoreToColumnstore 是否将rowstore迁移为columnstore。 true false false rowstoreToColumnstore=false cstore_blob cstore_blob取值如下: bytea none bytea none bytea cstore_blob=bytea keywords_addressed_using_as 关键字“addressed_using_as”的取值如下: OWNER ATTRIBUTE SOURCE FREEZE OWNER ATTRIBUTE SOURCE FREEZE OWNER keywords_addressed_using_as=OWNER keywords_addressed_using_doublequote 关键字“addressed_using_doublequote”的可能取值。 FREEZE FREEZE keywords_addressed_using_doublequote=FREEZE 父主题: 配置DSC
  • LOCK DSC工具在迁移MySQL事务处理锁表语句时会根据GaussDB(DWS)特性进行相应适配。 输入示例 1 2 3 4 5 6 7 8 91011121314 ## A.START TRANSACTION;LOCK TABLES `mt`.`runoob_tbl` WRITE,`mt`.`runoob_tb2` READ;commit;## B.START TRANSACTION;LOCK TABLES `mt`.`runoob_tbl` WRITE;commit;## C.START TRANSACTION;LOCK TABLES `mt`.`runoob_tbl` READ,`mt`.`runoob_tbl` AS t1 READ;commit; 输出示例 1 2 3 4 5 6 7 8 9101112131415 -- A.START TRANSACTION;LOCK TABLE "mt"."runoob_tbl" IN AC CES S EXCLUSIVE MODE;LOCK TABLE "mt"."runoob_tb2" IN ACCESS SHARE MODE;COMMIT WORK;-- B.START TRANSACTION;LOCK TABLE "mt"."runoob_tbl" IN ACCESS EXCLUSIVE MODE;COMMIT WORK;-- C.START TRANSACTION;LOCK TABLE "mt"."runoob_tbl" IN ACCESS SHARE MODE;COMMIT WORK;
  • TRANSACTION DSC工具在迁移MySQL事务处理语句时会根据GaussDB(DWS)特性进行相应适配。 输入示例 1 2 3 4 5 6 7 8 910111213141516 ##该声明仅适用于会话中执行的下一个单个事务SET TRANSACTION ISOLATION LEVEL READ COMMITTED;SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;SET TRANSACTION READ ONLY;SET TRANSACTION READ WRITE;SET TRANSACTION ISOLATION LEVEL READ COMMITTED,READ ONLY;SET TRANSACTION ISOLATION LEVEL SERIALIZABLE,READ WRITE;##使用SESSION关键字,适用于当前会话中执行的所有后续事务START TRANSACTION;SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;commit ; 输出示例 1 2 3 4 5 6 7 8 910111213141516 --该声明仅适用于会话中执行的下一个单个事务SET LOCAL TRANSACTION ISOLATION LEVEL READ COMMITTED;SET LOCAL TRANSACTION ISOLATION LEVEL REPEATABLE READ;SET LOCAL TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;SET LOCAL TRANSACTION ISOLATION LEVEL SERIALIZABLE;SET LOCAL TRANSACTION READ ONLY;SET LOCAL TRANSACTION READ WRITE;SET LOCAL TRANSACTION ISOLATION LEVEL READ COMMITTED READ ONLY;SET LOCAL TRANSACTION ISOLATION LEVEL SERIALIZABLE READ WRITE;--使用SESSION关键字,适用于当前会话中执行的所有后续事务START TRANSACTION;SET SESSION CHARACTERIS TICS AS TRANSACTION ISOLATION LEVEL READ COMMITTED;SET SESSION CHARACTERISTI CS AS TRANSACTION ISOLATION LEVEL READ COMMITTED;SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL SERIALIZABLE;SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL SERIALIZABLE;COMMIT WORK;
  • 删除索引 MySQL支持DROP INDEX和ALTER TABLE DROP INDEX两种删除索引的语句。DSC工具迁移时会根据GaussDB(DWS)的特性进行相应适配。 DROP INDEX 输入示例 1 2 3 4 5 6 7 8 91011121314151617 CREATE TABLE `test_create_table03` (`DEMAND_ID` INT(11) NOT NULL,`DEMAND_NAME` CHAR(100) NOT NULL,`THEME` VARCHAR(200) NULL DEFAULT NULL,`SEND_ID` INT(11) NULL DEFAULT NULL,`SEND_NAME` CHAR(20) NULL DEFAULT NULL,`SEND_TIME` DATETIME NULL DEFAULT NULL,`DEMAND_CONTENT` TEXT NOT NULL)COLLATE='utf8_general_ci'ENGINE=InnoDB;CREATE UNIQUE INDEX DEMAND_NAME_INDEX ON TEST_CREATE_TABLE03(DEMAND_NAME);DROP INDEX DEMAND_NAME_INDEX ON TEST_CREATE_TABLE03;CREATE INDEX SEND_ID_INDEX ON TEST_CREATE_TABLE03(SEND_ID);DROP INDEX SEND_ID_INDEX ON TEST_CREATE_TABLE03; 输出示例 1 2 3 4 5 6 7 8 910111213141516171819 CREATE TABLE "public"."test_create_table03"( "demand_id" INTEGER NOT NULL, "demand_name" CHAR(400) NOT NULL, "theme" VARCHAR(800) DEFAULT NULL, "send_id" INTEGER DEFAULT NULL, "send_name" CHAR(80) DEFAULT NULL, "send_time" TIMESTAMP WITHOUT TIME ZONE DEFAULT NULL, "demand_content" TEXT NOT NULL) WITH ( ORIENTATION = ROW, COMPRESSION = NO ) NOCOMPRESS DISTRIBUTE BY HASH ("demand_id");CREATE INDEX "demand_name_index" ON "public"."test_create_table03" ("demand_name");DROP INDEX "public"."demand_name_index" RESTRICT;CREATE INDEX "send_id_index" ON "public"."test_create_table03" USING BTREE ("send_id");DROP INDEX "public"."send_id_index" RESTRICT; ALTER TABLE DROP INDEX 输入示例 1 2 3 4 5 6 7 8 91011121314 CREATE TABLE `test_create_table03` (`DEMAND_ID` INT(11) NOT NULL,`DEMAND_NAME` CHAR(100) NOT NULL,`THEME` VARCHAR(200) NULL DEFAULT NULL,`SEND_ID` INT(11) NULL DEFAULT NULL,`SEND_NAME` CHAR(20) NULL DEFAULT NULL,`SEND_TIME` DATETIME NULL DEFAULT NULL,`DEMAND_CONTENT` TEXT NOT NULL)COLLATE='utf8_general_ci'ENGINE=InnoDB;ALTER TABLE TEST_CREATE_TABLE03 ADD UNIQUE INDEX TEST_CREATE_TABLE03_NAME_INDEX(DEMAND_NAME(50));ALTER TABLE TEST_CREATE_TABLE03 DROP INDEX TEST_CREATE_TABLE03_NAME_INDEX; 输出示例 1 2 3 4 5 6 7 8 910111213141516 CREATE TABLE "public"."test_create_table03"( "demand_id" INTEGER NOT NULL, "demand_name" CHAR(400) NOT NULL, "theme" VARCHAR(800) DEFAULT NULL, "send_id" INTEGER DEFAULT NULL, "send_name" CHAR(80) DEFAULT NULL, "send_time" TIMESTAMP WITHOUT TIME ZONE DEFAULT NULL, "demand_content" TEXT NOT NULL) WITH ( ORIENTATION = ROW, COMPRESSION = NO ) NOCOMPRESS DISTRIBUTE BY HASH ("demand_id");CREATE INDEX "test_create_table03_name_index" ON "public"."test_create_table03" ("demand_name");DROP INDEX "public"."test_create_table03_name_index" RESTRICT; 父主题: 索引
  • SPATIAL空间索引 GaussDB(DWS)不支持SPATIAL空间索引。DSC工具迁移时会根据GaussDB(DWS)的特性进行相应适配。 内联SPATIAL空间索引。 输入示例 1 2 3 4 5 6 7 8 91011121314151617 CREATE TABLE `public`.`test_create_table04` (`ID` INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,`A` POINT NOT NULL,`B` POLYGON NOT NULL,`C` GEOMETRYCOLLECTION NOT NULL,`D` LINESTRING NOT NULL,`E` MULTILINESTRING NOT NULL,`F` MULTIPOINT NOT NULL,`G` MULTIPOLYGON NOT NULL,SPATIAL INDEX A_INDEX(A),SPATIAL INDEX B_INDEX(B),SPATIAL INDEX C_INDEX(C),SPATIAL KEY D_INDEX(D),SPATIAL KEY E_INDEX(E),SPATIAL KEY F_INDEX(F),SPATIAL INDEX G_INDEX(G)); 输出示例 1 2 3 4 5 6 7 8 9101112131415161718192021 CREATE TABLE "public"."test_create_table04"( "id" SERIAL NOT NULL PRIMARY KEY, "a" POINT NOT NULL, "b" POLYGON NOT NULL, "c" GEOMETRYCOLLECTION NOT NULL, "d" POLYGON NOT NULL, "e" BOX NOT NULL, "f" BOX NOT NULL, "g" POLYGON NOT NULL) WITH ( ORIENTATION = ROW, COMPRESSION = NO ) NOCOMPRESS DISTRIBUTE BY HASH ("id");CREATE INDEX "a_index" ON "public"."test_create_table04" USING GIST ("a");CREATE INDEX "b_index" ON "public"."test_create_table04" USING GIST ("b");CREATE INDEX "c_index" ON "public"."test_create_table04" USING GIST ("c");CREATE INDEX "d_index" ON "public"."test_create_table04" USING GIST ("d");CREATE INDEX "e_index" ON "public"."test_create_table04" USING GIST ("e");CREATE INDEX "f_index" ON "public"."test_create_table04" USING GIST ("f");CREATE INDEX "g_index" ON "public"."test_create_table04" USING GIST ("g"); ALTER TABLE创建SPATIAL空间索引。 输入示例 1 2 3 4 5 6 7 8 910111213 CREATE TABLE `public`.`test_create_table04` ( `ID` INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY, `A` POINT NOT NULL, `B` POLYGON NOT NULL, `C` GEOMETRYCOLLECTION NOT NULL, `D` LINESTRING NOT NULL, `E` MULTILINESTRING NOT NULL, `F` MULTIPOINT NOT NULL, `G` MULTIPOLYGON NOT NULL);ALTER TABLE `test_create_table04` ADD SPATIAL INDEX A_INDEX(A);ALTER TABLE `test_create_table04` ADD SPATIAL INDEX E_INDEX(E) USING BTREE; 输出示例 1 2 3 4 5 6 7 8 91011121314151617 CREATE TABLE "public"."test_create_table04"( "id" SERIAL NOT NULL PRIMARY KEY, "a" POINT NOT NULL, "b" POLYGON NOT NULL, "c" GEOMETRYCOLLECTION NOT NULL, "d" POLYGON NOT NULL, "e" BOX NOT NULL, "f" BOX NOT NULL, "g" POLYGON NOT NULL) WITH ( ORIENTATION = ROW, COMPRESSION = NO ) NOCOMPRESS DISTRIBUTE BY HASH ("id");CREATE INDEX "a_index" ON "public"."test_create_table04" USING GIST ("a");CREATE INDEX "e_index" ON "public"."test_create_table04" USING GIST ("e"); CREATE INDEX创建SPATIAL空间索引。 输入示例 1 2 3 4 5 6 7 8 9101112 CREATE TABLE `public`.`test_create_table04` (`ID` INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,`A` POINT NOT NULL,`B` POLYGON NOT NULL,`C` GEOMETRYCOLLECTION NOT NULL,`D` LINESTRING NOT NULL,`E` MULTILINESTRING NOT NULL,`F` MULTIPOINT NOT NULL,`G` MULTIPOLYGON NOT NULL);CREATE SPATIAL INDEX A_INDEX ON `test_create_table04`(A); 输出示例 1 2 3 4 5 6 7 8 910111213141516 CREATE TABLE "public"."test_create_table04"( "id" SERIAL NOT NULL PRIMARY KEY, "a" POINT NOT NULL, "b" POLYGON NOT NULL, "c" GEOMETRYCOLLECTION NOT NULL, "d" POLYGON NOT NULL, "e" BOX NOT NULL, "f" BOX NOT NULL, "g" POLYGON NOT NULL) WITH ( ORIENTATION = ROW, COMPRESSION = NO ) NOCOMPRESS DISTRIBUTE BY HASH ("id");CREATE INDEX "a_index" ON "public"."test_create_table04" USING GIST ("a"); 父主题: 索引
  • 错误码 表2 错误码 错误码 错误信息 Teradata DSC_ERR_003_001 Query/statement is not supported since the Teradata view "dbc.indices" is supported only for the indextype P and Q. DSC_ERR_003_002 Error in Bteq processing. Something went wrong while processing the BTEQ commands. DSC_ERR_003_003 Query/statement is not supported in ddl DSC. Please check the same and refer user manual for the supported feature list. DSC_ERR_003_004 Unsupported format decimal format like ZZZ99Z, ZZZ.ZZ9. DSC_ERR_003_005 The tool does not support the "IN/NOT IN to EXISTS/NOT EXISTS conversion" for the query in which its outer query refers multiple tables and the column(s) specified with IN / NOT IN operator do not have table reference. DSC_ERR_003_006 The tool does not support the query in which its outer query refers multiple tables and the column(s) specified with IN / NOT IN operator do not have table reference. DSC_ERR_003_007 Primary Index without column is not supported. DSC_ERR_003_008 TeradataQuerySplitter config file contains list is not supported. DSC_ERR_003_009 Gauss does not support WITH CHECK OPTION in CREATE VIEW. Please enable the config_param tdMigrateVIEWCHECKOPTION to comment the WITH CHECK OPTION syntax in the statement. DSC_ERR_003_010 Gauss does not have an equivalent syntax for CHARACTER SET & CASE SPECIFIC option in column-level. Please enable the config_param tdMigrateCharsetCase to comment the CHARACTER SET & CASE SPECIFIC option syntax in the statement. DSC_ERR_003_011 Gauss does not have equivalent syntax for LOCK option in CREATE VIEW and INSERT statement. You can rewrite this statement or set the configuration parameter tdMigrateLOCKOption to TRUE to comment the LOCK syntax in this statement. DSC_ERR_003_012 Invalid width (Number of rows) parameter in MDIFF function. DSC_ERR_003_013 First 2 parameters should be present in MDIFF function. DSC_ERR_003_014 Query/statement is not supported as ORDER BY clause is not present in TOP WITH TIES. Please check the same and refer user manual. DSC_ERR_003_015 Column mismatch for the TITLE conversion. DSC_ERR_003_016 Query/statement is not supported as same Table alias is addressed in both inner and outer query. Please check the same and refer user manual for the supported feature list. DSC_ERR_003_017 Sub query list does not have columns. DSC_ERR_003_018 Number of expressions specified in the outer query does not match with inner query. DSC_ERR_003_019 Error while loading the .RUN FILE from given location. DSC_ERR_003_020 Unable to delete the file, file not found. DSC_ERR_003_021 Unable to delete the file, failed with IOEXception. DSC_ERR_003_022 Please specify the value for environment_file_path parameter in features-teradata.properties. Application DSC_ERR_004_001 Application has timed out, exceeded the hours specified in the config file. Please configure the Timeout parameter in the application.properties to higher value. DSC_ERR_004_002 Error while loading the property files from config directory. DSC_ERR_004_003 Error while loading the property files from config directory, directory is not readable. DSC_ERR_004_004 Error while loading the property file. DSC_ERR_004_005 Unable to load the JSON file. DSC_ERR_004_006 DSC tool does not support this Conversion type provided. DSC_ERR_004_007 Error occurred while framing output replacement query. DSC_ERR_004_008 Invalid index value while parsing the script. DSC_ERR_004_009 Error in conversion process, unable to convert the script. DSC_ERR_004_010 No SQL files found in the input directory with the extension specified in the fileExtension property in application.properties. DSC_ERR_004_011 The query length parameter (MaxSqlLen) value is not valid. DSC_ERR_004_012 Since the input folder has write privileges to Group and/or Others, process is stopped due to security reason. DSC_ERR_004_013 Since the output directory has write privileges to Group and/or Others, process is stopped due to security reason. DSC_ERR_004_014 Disk is almost full. Please clear the space and re-run the tool. DSC_ERR_004_015 DSC has been cancelled as configured by the user. DSC_ERR_004_016 Error occurred while formatting the sql scripts. DSC_ERR_004_017 Invalid index specified for fetching the element from list while formatting the scripts DSC_ERR_004_018 Error occurred while converting from string to integer. DSC_ERR_004_019 Input File is modified while DSC is in progress. DSC_ERR_004_020 Process is null, unable to read encoding format. DSC_ERR_004_021 Target File does not have write permissions. DSC_ERR_004_022 The target directory does not have write privileges to Group and/or Others, process is stopped due to security reason. DSC_ERR_004_023 PL/SQL object contains incorrect DDL/Query. Please check the script for the query position specified in the log. DSC_ERR_004_024 PreQueryValidation failed due to bracket mismatch or invalid terminator. DSC_ERR_004_025 Conversion task name is not valid. DSC_ERR_004_026 Database entered by the user is not supported by the DSC tool. DSC_ERR_004_027 Gauss db password should not be empty. DSC_ERR_004_028 Gauss db password should not be empty. DSC_ERR_004_029 Target db entered in the Gaussdb.properties is not valid. DSC_ERR_004_030 User name entered in the Gaussdb.properties is empty. DSC_ERR_004_031 Port entered in the Gaussdb.properties is not valid. DSC_ERR_004_032 IP entered in the Gaussdb.properties is not valid. DSC_ERR_004_033 Database name entered in the Gaussdb.properties is empty. DSC_ERR_004_034 DSC Application failed to start. DSC_ERR_004_035 Since the environment variable path has write privileges to Group and/or Others, process is stopped due to security reason. DSC_ERR_004_036 Error while loading environment parameter File. DSC_ERR_004_037 Invalid input (empty/space/string value) for the parameter NoOfThreads in application.properties. Hence taking the default processes. DSC_ERR_004_038 Input for the parameter NoOfThreads in application.properties is less than 1. Hence taking the default processes. DSC_ERR_004_039 Error in processing the DDL query. DSC_ERR_004_040 Error in processing the PL/SQL query. DSC_ERR_004_041 Error in post processing the query. DSC_ERR_004_042 Invalid application timeout value, default to 4 hours. DSC_ERR_004_043 Error in writing the output file. DSC_ERR_004_044 Error in reading the input file. DSC_ERR_004_045 No valid files found in the input directory for migration. DSC_ERR_004_046 Query is not converted as it contains unsupported keyword. DSC_ERR_004_047 Error while reading the property. DSC_ERR_004_048 PreQueryValidation failed due to query exceeds maximum length (MaxSqlLen config parameter). DSC_ERR_004_049 Thread count entered in the Gaussdb.properties is not valid. Wrapper DSC_ERR_005_003 Reading file Failed with error: File not found Exception. DSC_ERR_005_004 Reading file Failed with error: IOException. DSC_ERR_005_005 Root privileged users are not allowed to execute the DSC tool. DSC_ERR_005_006 Error while getting the id of os user used to execute the DSC tool. DSC_ERR_005_007 Arguments specified is not valid, please check the user manual for the command line arguments. DSC_ERR_005_008 File name is not specified for reading the encoding type. DSC_ERR_005_009 Invalid argument specified for the encoding parameter. DSC_ERR_005_010 Source database is not set. Please enter a valid source db and refer the user manual for syntax. DSC_ERR_005_011 Commandline database specified for source to target is not supported by the DSC tool. DSC_ERR_005_012 Error in loading config file with IOException. DSC_ERR_005_013 Initial JVM memory is greater than maximum JVM memory. DSC_ERR_005_014 Invalid value specified for configValue. DSC_ERR_005_015 Invalid source database specified for source-db option. DSC_ERR_005_016 Invalid target database specified for target-db option. DSC_ERR_005_017 Invalid conversion type specified for dsc-type option. DSC_ERR_005_018 Invalid application language specified for application-lang option. DSC_ERR_005_019 Conversion-type should be DDL for application-lang type as perl. DSC_ERR_005_020 Source-db should be teradata for application-lang type as perl. DSC_ERR_005_021 Please use "-VN [V1R7 | V1R8_330]" or "--version-number [V1R7 | V1R8_330]" to specify the kernel version which can be either V1R7 or V1R8_330. DSC_ERR_005_022 Input directory does not exist. DSC_ERR_005_023 Getting path for input directory failed with IOException. DSC_ERR_005_024 Getting path for output directory failed with IOException. DSC_ERR_005_025 Setting file permission for output directory failed with IOException. DSC_ERR_005_026 Creating output directory failed. DSC_ERR_005_027 Setting file permissions for log directory/file failed with FileException. DSC_ERR_005_028 Error while connecting to GaussDB, Failed with error. DSC_ERR_005_029 Error occurred due to file permission while creating or executing the file. DSC_ERR_005_030 No arguments specified in the commandline. DSC_ERR_005_031 Error occurred in creating output directory.
  • SET MySQL INSERT...SET语句的形式插入基于明确指定的值的行。 输入示例 1234 # INSERT INTO SET 可以针对性的执行插入操作,但是一次只能插入一行数据,不能批量添加数据INSERT INTO exmp_tb2 SET tb2_price=56.1,tb2_note='unbelievable',tb2_date='2018-11-13';INSERT INTO exmp_tb2 SET tb2_price=99.9,tb2_note='perfect',tb2_date='2018-10-13';INSERT INTO exmp_tb2 SET tb2_id=9,tb2_price=99.9,tb2_note='perfect',tb2_date='2018-10-13'; 输出示例 1234 -- INSERT INTO SET 可以针对性的执行插入操作,但是一次只能插入一行数据,不能批量添加数据INSERT INTO "public"."exmp_tb2" ("tb2_price","tb2_note","tb2_date") VALUES (56.1,'unbelievable','2018-11-13');INSERT INTO "public"."exmp_tb2" ("tb2_price","tb2_note","tb2_date") VALUES (99.9,'perfect','2018-10-13');INSERT INTO "public"."exmp_tb2" ("tb2_id","tb2_price","tb2_note","tb2_date") VALUES (9,99.9,'perfect','2018-10-13');
  • ON DUPLICATE KEY UPDATE INSERT使用ON DUPLICATE KEY UPDATE子句可以使现有行更新。 输入示例 12345 #ON DUPLICATE KEY UPDATE 若该数据的主键值/ UNIQUE KEY 已经在表中存在,则执行更新操作, 即UPDATE;否则执行插入操作INSERT INTO exmp_tb2(tb2_id,tb2_price) VALUES(3,12.3) ON DUPLICATE KEY UPDATE tb2_price=12.3;INSERT INTO exmp_tb2(tb2_id,tb2_price) VALUES(4,12.3) ON DUPLICATE KEY UPDATE tb2_price=12.3;INSERT INTO exmp_tb2(tb2_id,tb2_price,tb2_note) VALUES(10,DEFAULT,DEFAULT) ON DUPLICATE KEY UPDATE tb2_price=66.6;INSERT INTO exmp_tb2(tb2_id,tb2_price,tb2_note,tb2_date) VALUES(11,DEFAULT,DEFAULT,DEFAULT) ON DUPLICATE KEY UPDATE tb2_price=66.6; 输出示例 12345 --ON DUPLICATE KEY UPDATE 若该数据的主键值/ UNIQUE KEY 已经在表中存在,则执行更新操作, 即UPDATE;否则执行插入操作INSERT INTO "public"."exmp_tb2" ("tb2_id","tb2_price") VALUES (3,12.3);INSERT INTO "public"."exmp_tb2" ("tb2_id","tb2_price") VALUES (4,12.3);INSERT INTO "public"."exmp_tb2" ("tb2_id","tb2_price","tb2_note") VALUES (10,DEFAULT,DEFAULT);INSERT INTO "public"."exmp_tb2" ("tb2_id","tb2_price","tb2_note","tb2_date") VALUES (11,DEFAULT,DEFAULT,DEFAULT);
  • VALUES INSERT使用 VALUES语法的语句可以插入多行,以逗号分隔。 输入示例 1 INSERT INTO exmp_tb1 (tb1_name,tb1_gender,tb1_address,tb1_number) VALUES('David','male','NewYork','01015827875'),('Rachel','female','NewYork','01015827749'),('Monica','female','NewYork','010158996743'); 输出示例 123 INSERT INTO "public"."exmp_tb1" ("tb1_name","tb1_gender","tb1_address","tb1_number") VALUES ('David','male','NewYork','01015827875');INSERT INTO "public"."exmp_tb1" ("tb1_name","tb1_gender","tb1_address","tb1_number") VALUES ('Rachel','female','NewYork','01015827749');INSERT INTO "public"."exmp_tb1" ("tb1_name","tb1_gender","tb1_address","tb1_number") VALUES ('Monica','female','NewYork','010158996743');
  • IGNORE MySQL INSERT语句如果使用IGNORE修饰符,则执行INSERT语句时发生的错误将被忽略。 输入示例 1 2 3 4 5 6 7 8 910 # 如果表中已经存在相同的记录,则忽略当前新数据INSERT IGNORE INTO exmp_tb2 VALUES(189, '189.23','nice','2017-11-12');INSERT IGNORE INTO exmp_tb2 VALUES(130,'189.23','nice','2017-11-12');INSERT IGNORE INTO exmp_tb2 VALUES(120,15.68,'good','2018-11-12');INSERT IGNORE INTO exmp_tb2 VALUES(DEFAULT,128.23,'nice','2018-10-11');INSERT IGNORE INTO exmp_tb2 VALUES(DEFAULT,DEFAULT,'nice','2018-12-14');INSERT IGNORE INTO exmp_tb2 VALUES(DEFAULT,DEFAULT,'nice',DEFAULT);INSERT IGNORE INTO exmp_tb2 (tb2_id,tb2_price) VALUES(DEFAULT,DEFAULT);INSERT IGNORE INTO exmp_tb2 (tb2_id,tb2_price,tb2_note) VALUES(DEFAULT,DEFAULT,DEFAULT);INSERT IGNORE INTO exmp_tb2 (tb2_id,tb2_price,tb2_note,tb2_date) VALUES(DEFAULT,DEFAULT,DEFAULT,DEFAULT); 输出示例 1 2 3 4 5 6 7 8 910 -- 如果表中已经存在相同的记录,则忽略当前新数据INSERT INTO "public"."exmp_tb2" VALUES (101,'189.23','nice','2017-11-12');INSERT INTO "public"."exmp_tb2" VALUES (130,'189.23','nice','2017-11-12');INSERT INTO "public"."exmp_tb2" VALUES (120,15.68,'good','2018-11-12');INSERT INTO "public"."exmp_tb2" VALUES (DEFAULT,128.23,'nice','2018-10-11');INSERT INTO "public"."exmp_tb2" VALUES (DEFAULT,DEFAULT,'nice','2018-12-14');INSERT INTO "public"."exmp_tb2" VALUES (DEFAULT,DEFAULT,'nice',DEFAULT);INSERT INTO "public"."exmp_tb2" ("tb2_id","tb2_price") VALUES (DEFAULT,DEFAULT);INSERT INTO "public"."exmp_tb2" ("tb2_id","tb2_price","tb2_note") VALUES (DEFAULT,DEFAULT,DEFAULT);INSERT INTO "public"."exmp_tb2" ("tb2_id","tb2_price","tb2_note","tb2_date") VALUES (DEFAULT,DEFAULT,DEFAULT,DEFAULT);
  • LOW_PRIORITY MySQL INSERT插入语句使用LOW_PRIORITY修饰符时,则执行该INSERT延迟。 输入示例 123456 # LOW_PRIORITY 低优先级INSERT LOW_PRIORITY INTO exmp_tb2 VALUES( DEFAULT, '128.23', 'nice', '2018-10-11');INSERT LOW_PRIORITY INTO exmp_tb2 VALUES(DEFAULT, DEFAULT, 'nice', '2018-12-14' );INSERT LOW_PRIORITY INTO exmp_tb2 VALUES(DEFAULT, DEFAULT, 'nice', DEFAULT);INSERT LOW_PRIORITY INTO exmp_tb2 (tb2_id, tb2_price) VALUES(DEFAULT, DEFAULT);INSERT LOW_PRIORITY INTO exmp_tb2 (tb2_id, tb2_price, tb2_note) VALUES(DEFAULT, DEFAULT, DEFAULT); 输出示例 123456 -- LOW_PRIORITY 低优先级INSERT INTO "public"."exmp_tb2" VALUES (DEFAULT,'128.23','nice','2018-10-11');INSERT INTO "public"."exmp_tb2" VALUES (DEFAULT,DEFAULT,'nice','2018-12-14');INSERT INTO "public"."exmp_tb2" VALUES (DEFAULT,DEFAULT,'nice',DEFAULT);INSERT INTO "public"."exmp_tb2" ("tb2_id","tb2_price") VALUES (DEFAULT,DEFAULT);INSERT INTO "public"."exmp_tb2" ("tb2_id","tb2_price","tb2_note") VALUES (DEFAULT,DEFAULT,DEFAULT);
  • PARTITION 当插入到分区表中时,可以控制哪些分区和子分区接受新行。 输入示例 12345 INSERT INTO employees PARTITION(p3) VALUES (19, 'Frank1', 'Williams', 1, 2);INSERT INTO employees PARTITION(p0) VALUES (4, 'Frank1', 'Williams', 1, 2);INSERT INTO employees PARTITION(p1) VALUES (9, 'Frank1', 'Williams', 1, 2);INSERT INTO employees PARTITION(p2) VALUES (10, 'Frank1', 'Williams', 1, 2);INSERT INTO employees PARTITION(p2) VALUES (11, 'Frank1', 'Williams', 1, 2); 输出示例 12345 INSERT INTO "public"."employees" VALUES (19,'Frank1','Williams',1,2);INSERT INTO "public"."employees" VALUES (4,'Frank1','Williams',1,2);INSERT INTO "public"."employees" VALUES (9,'Frank1','Williams',1,2);INSERT INTO "public"."employees" VALUES (10,'Frank1','Williams',1,2);INSERT INTO "public"."employees" VALUES (11,'Frank1','Williams',1,2);
  • DELAYED 在MySQL 5.7中,DELAYED关键字被接受,但被服务器忽略。 输入示例 123456789 # DELAYED 延迟INSERT DELAYED INTO exmp_tb2 VALUES(99, 15.68, 'good', '2018-11-12');INSERT DELAYED INTO exmp_tb2 VALUES(80, 12.3, 'cheap', '2018-11-11');INSERT DELAYED INTO exmp_tb2 VALUES(DEFAULT, 128.23, 'nice', '2018-10-11');INSERT DELAYED INTO exmp_tb2 VALUES(DEFAULT, DEFAULT, 'nice', '2018-12-14');INSERT DELAYED INTO exmp_tb2 VALUES(DEFAULT, DEFAULT, 'nice', DEFAULT);INSERT DELAYED INTO exmp_tb2 (tb2_id, tb2_price) VALUES(DEFAULT, DEFAULT);INSERT DELAYED INTO exmp_tb2 (tb2_id, tb2_price, tb2_note) VALUES(DEFAULT, DEFAULT, DEFAULT);INSERT DELAYED INTO exmp_tb2 (tb2_id, tb2_price, tb2_note, tb2_date) VALUES(DEFAULT, DEFAULT, DEFAULT, DEFAULT); 输出示例 123456789 -- DELAYED 延迟INSERT INTO "public"."exmp_tb2" VALUES (99,15.68,'good','2018-11-12');INSERT INTO "public"."exmp_tb2" VALUES (80,12.3,'cheap','2018-11-11');INSERT INTO "public"."exmp_tb2" VALUES (DEFAULT,128.23,'nice','2018-10-11');INSERT INTO "public"."exmp_tb2" VALUES (DEFAULT,DEFAULT,'nice','2018-12-14');INSERT INTO "public"."exmp_tb2" VALUES (DEFAULT,DEFAULT,'nice',DEFAULT);INSERT INTO "public"."exmp_tb2" ("tb2_id","tb2_price") VALUES (DEFAULT,DEFAULT);INSERT INTO "public"."exmp_tb2" ("tb2_id","tb2_price","tb2_note") VALUES (DEFAULT,DEFAULT,DEFAULT);INSERT INTO "public"."exmp_tb2" ("tb2_id","tb2_price","tb2_note","tb2_date") VALUES (DEFAULT,DEFAULT,DEFAULT,DEFAULT);
共100000条