gs_dump与dolphin.sql_mode配置冲突

【标题描述】:
【测试类型：工具功能】
【测试版本：5.0.0】 
问题描述
gs_dump与dolphin.sql_mode配置冲突，当sql_mode去掉pipes_as_concat时，gs_dump无法导出数据库
【操作系统和硬件信息】(查询命令: cat /etc/system-release, uname -a):

```
[root@localhost ~]# cat /etc/os-release
NAME="openEuler"
VERSION="22.03 (LTS-SP1)"
ID="openEuler"
VERSION_ID="22.03"
PRETTY_NAME="openEuler 22.03 (LTS-SP1)"
ANSI_COLOR="0;31"

[root@localhost ~]# uname -a
Linux localhost.localdomain 5.10.0-136.12.0.86.oe2203sp1.x86_64 #1 SMP Tue Dec 27 17:50:15 CST 2022 x86_64 x86_64 x86_64 GNU/Linux
```

【测试环境】(单机/1主x备x级联备):
单机
【被测功能】:
gs_dump导出数据库

【数据库版本】(查询命令: gaussdb -V):

```
[root@gauss1 ~]# su - omm -c "gaussdb -V"
gaussdb (openGauss 5.0.0 build a07d57c3) compiled at 2023-03-29 03:37:13 commit 0 last mr  
```

【预置条件】:
单机正常安装，且功能正常
【操作步骤】(请填写详细的操作步骤):
1.新建B模式htest库，且修改dolphin.sql_mode，去掉pipes_as_concat，使||用作逻辑或

```
DATABASE_PASSWORD=My@sql@49
su - omm -c "gsql -d postgres -p 15400  -U root -W ${DATABASE_PASSWORD} -c \"CREATE DATABASE IF NOT EXISTS htest DBCOMPATIBILITY ='B' ENCODING='UTF8';\""
su - omm -c "gsql -d htest -p 15400  -U omm -W ${DATABASE_PASSWORD} -c \"ALTER DATABASE htest SET dolphin.sql_mode TO 'ansi_quotes,no_zero_date,pad_char_to_full_length';\"" 
NOTICE:  database "htest" already exists, skipping
CREATE DATABASE
ALTER DATABASE
```

2.重启使参数生效

```
[root@gauss1 ~]# su - omm -c "gs_om -t restart"
Stopping cluster.
=========================================
Successfully stopped cluster.
=========================================
End stop cluster.
Starting cluster.
=========================================
[SUCCESS] gauss1
2023-10-10 11:28:40.851 6524c4e8.1 [unknown] 139668939997120 [unknown] 0 dn_6001 01000  0 [BACKEND] WARNING:  could not create any HA TCP/IP sockets
2023-10-10 11:28:40.851 6524c4e8.1 [unknown] 139668939997120 [unknown] 0 dn_6001 01000  0 [BACKEND] WARNING:  could not create any HA TCP/IP sockets
2023-10-10 11:28:40.853 6524c4e8.1 [unknown] 139668939997120 [unknown] 0 dn_6001 01000  0 [BACKEND] WARNING:  Failed to initialize the memory protect for g_instance.attr.attr_storage.cstore_buffers (1024 Mbytes) or shared memory (4470 Mbytes) is larger.
=========================================
Successfully started.
```

3.登录查看参数确认

```
root@gauss1 ~]# su - omm -c "gsql -d htest -p 15400"
gsql ((openGauss 5.0.0 build a07d57c3) compiled at 2023-03-29 03:37:13 commit 0 last mr  )
Non-SSL connection (SSL connection is recommended when requiring high-security)
Type "help" for help.

htest=# show dolphin.sql_mode;
                 dolphin.sql_mode                 
--------------------------------------------------
 ansi_quotes,no_zero_date,pad_char_to_full_length
(1 row)

htest=#
```

4.使用gs_dump导出数据库，报错，查询sql中存在||符出错

```
[root@gauss1 ~]# su - omm -c "gs_dump -U root -W My@sql@49 -f /home/omm/test.sql -p 15400 htest -F p"
gs_dump: [port='15400'] [htest] [archiver (db)] [2023-10-10 11:31:33] query failed: ERROR:  current transaction is aborted, commands ignored until end of transaction block, firstChar[Q]
gs_dump: [port='15400'] [htest] [archiver (db)] [2023-10-10 11:31:33] query was: SELECT a.attnum, a.attname, a.atttypmod, a.attstattarget, a.attstorage, t.typstorage, a.attnotnull, a.atthasdef, a.attisdropped, a.attlen, a.attalign, a.attislocal, a.attkvtype, t.oid AS typid, CASE WHEN t.typtype <> 's' THEN pg_catalog.format_type(t.oid,a.atttypmod) ELSE 'set(' || (select pg_catalog.string_agg(''''||setlabel||'''', ',' order by setsortorder) from pg_catalog.pg_set group by settypid having settypid = t.oid) || ')' END AS atttypname, pg_catalog.array_to_string(a.attoptions, ', ') AS attoptions, CASE WHEN a.attcollation <> t.typcollation THEN a.attcollation ELSE 0::Oid END AS attcollation, pg_catalog.array_to_string(ARRAY(SELECT pg_catalog.quote_ident(option_name) || ' ' || pg_catalog.quote_literal(option_value) FROM pg_catalog.pg_options_to_table(attfdwoptions) ORDER BY option_name), E',
    ') AS attfdwoptions FROM pg_catalog.pg_attribute a LEFT JOIN pg_catalog.pg_type t ON a.atttypid = t.oid WHERE a.attrelid = '21197'::pg_catalog.oid AND a.attnum > 0::pg_catalog.int2 ORDER BY a.attrelid, a.attnum
```

5.修改参数生效

```
su - omm -c "gsql -d htest -p 15400  -U omm -W ${DATABASE_PASSWORD} -c \"ALTER DATABASE htest SET dolphin.sql_mode TO 'ansi_quotes,no_zero_date,pad_char_to_full_length,pipes_as_concat';\"" 
ALTER DATABASE
[root@gauss1 ~]# su - omm -c "gs_om -t restart"
Stopping cluster.
=========================================
Successfully stopped cluster.
=========================================
End stop cluster.
Starting cluster.
=========================================
[SUCCESS] gauss1
2023-10-10 11:33:02.171 6524c5ee.1 [unknown] 140178361511872 [unknown] 0 dn_6001 01000  0 [BACKEND] WARNING:  could not create any HA TCP/IP sockets
2023-10-10 11:33:02.171 6524c5ee.1 [unknown] 140178361511872 [unknown] 0 dn_6001 01000  0 [BACKEND] WARNING:  could not create any HA TCP/IP sockets
2023-10-10 11:33:02.173 6524c5ee.1 [unknown] 140178361511872 [unknown] 0 dn_6001 01000  0 [BACKEND] WARNING:  Failed to initialize the memory protect for g_instance.attr.attr_storage.cstore_buffers (1024 Mbytes) or shared memory (4470 Mbytes) is larger.
=========================================
Successfully started.
```

6.使用gs_dump导出数据库正常

```
[root@gauss1 ~]# su - omm -c "gs_dump -U root -W My@sql@49 -f /home/omm/test.sql -p 15400 htest -F p"
gs_dump[port='15400'][htest][2023-10-10 11:33:37]: The total objects number is 429.
gs_dump[port='15400'][htest][2023-10-10 11:33:37]: [100.00%] 429 objects have been dumped.
gs_dump[port='15400'][htest][2023-10-10 11:33:37]: dump database htest successfully
gs_dump[port='15400'][htest][2023-10-10 11:33:37]: total time: 3746  ms
```

【预期输出】:
数据库正常导出
【实际输出】:

```
[root@gauss1 ~]# su - omm -c "gs_dump -U root -W My@sql@49 -f /home/omm/test.sql -p 15400 htest -F p"
gs_dump: [port='15400'] [htest] [archiver (db)] [2023-10-10 11:31:33] query failed: ERROR:  current transaction is aborted, commands ignored until end of transaction block, firstChar[Q]
gs_dump: [port='15400'] [htest] [archiver (db)] [2023-10-10 11:31:33] query was: SELECT a.attnum, a.attname, a.atttypmod, a.attstattarget, a.attstorage, t.typstorage, a.attnotnull, a.atthasdef, a.attisdropped, a.attlen, a.attalign, a.attislocal, a.attkvtype, t.oid AS typid, CASE WHEN t.typtype <> 's' THEN pg_catalog.format_type(t.oid,a.atttypmod) ELSE 'set(' || (select pg_catalog.string_agg(''''||setlabel||'''', ',' order by setsortorder) from pg_catalog.pg_set group by settypid having settypid = t.oid) || ')' END AS atttypname, pg_catalog.array_to_string(a.attoptions, ', ') AS attoptions, CASE WHEN a.attcollation <> t.typcollation THEN a.attcollation ELSE 0::Oid END AS attcollation, pg_catalog.array_to_string(ARRAY(SELECT pg_catalog.quote_ident(option_name) || ' ' || pg_catalog.quote_literal(option_value) FROM pg_catalog.pg_options_to_table(attfdwoptions) ORDER BY option_name), E',
    ') AS attfdwoptions FROM pg_catalog.pg_attribute a LEFT JOIN pg_catalog.pg_type t ON a.atttypid = t.oid WHERE a.attrelid = '21197'::pg_catalog.oid AND a.attnum > 0::pg_catalog.int2 ORDER BY a.attrelid, a.attnum
```

openGauss/openGauss-server

内容风险标识

评论 (7)

openGauss/openGauss-server .gitee-modal { width: 500px !important; }

内容风险标识