2025年1月

适用范围

设置SQL语句匹配规则,REWRITE PLUGIN 自动改写匹配的语句

MySQL8.0.12 前版本,只指定select

MySQL8.0.12+版本支持: SELECT, INSERT, REPLACE, UPDATE , DELETE语句

问题概述

问题原因

解决方案

1、 Rewriter Query Rewrite Plugin 安装

需要在安装软件包中的share目录找到脚本 install_rewriter.sql ,执行时会自动安装 rewriter.so

mysql@s2:/home/db/mysql/product/share$ ls -l rewri
-rw-r--r-- 1 mysql mysql 2216 Dec 17 2022 install_rewriter.sql
-rw-r--r-- 1 mysql mysql 1248 Dec 17 2022 uninstall_rewriter.sql

直接执行脚本install_rewriter.sql

root@localhost 16:07:59 [(none)]>source install_rewriter.sql
Query OK, 1 row affected (0.00 sec)
Query OK, 0 rows affected (0.05 sec)
Query OK, 0 rows affected (0.34 sec)
Query OK, 0 rows affected (0.69 sec)
Query OK, 0 rows affected (0.02 sec)

查看rewrite插件,已经加载

root@localhost 16:08:05 [(none)]>show plugins ;
...

RewriterACTIVEAUDITrewriter.soGPL

50 rows in set (0.00 sec)

查看rewriter_enabled状态,自动为on

root@localhost 16:08:08 [(none)]>show variables like '%rewrite%' ;
Variable_nameValue
rewriter_enabledON
rewriter_enabled_for_threads_without_privilege_checksON
rewriter_verbose1

3 rows in set (0.00 sec)

同时自动创建query_rewrite库,及规则表rewrite_rules

root@localhost 16:08:16 [(none)]>show databases ;
Database
query_rewrite
root@localhost 16:32:29 [query_rewrite]>show tables ;
Tables_in_query_rewrite
rewrite_rules

添加改下规则

select ? 替换 为 select ? + 1

root@localhost 16:09:10 [(none)]>select 1 ;
1
1

1 row in set (0.00 sec)

root@localhost 16:09:23 [(none)]>INSERT INTO query_rewrite.rewrite_rules (pattern, replacement)

-> VALUES('SELECT ?', 'SELECT ? + 1');

Query OK, 1 row affected (0.02 sec)

root@localhost 16:09:27 [(none)]>select 1 ;
1
1

1 row in set (0.00 sec)

发现不没有生效,需要把改写规则加载到共享内存中,执行flush_rewrite_rules();

root@localhost 16:09:31 [(none)]>SELECT * FROM query_rewrite.rewrite_rules G ;
1. row **

            id: 1
       pattern: SELECT ?

pattern_database: NULL

   replacement: SELECT ? + 1
       enabled: YES
       message: NULL
pattern_digest: NULL

normalized_pattern: NULL
1 row in set (0.00 sec)

ERROR:
No query specified

root@localhost 16:10:01 [(none)]>CALL query_rewrite.flush_rewrite_rules();
Query OK, 1 row affected (0.03 sec)

root@localhost 16:10:12 [(none)]>select 1 ;
1 + 1
2

1 row in set, 1 warning (0.00 sec)

再次执行select 1 ,发现结果变成2 ,说明改写规则生效。

root@localhost 16:32:34 [query_rewrite]>select * from rewrite_rules ;
idpatternpattern_databasereplacementenabledmessagepattern_digestnormalized_pattern
1SELECT ?NULLSELECT ? + 1YESNULLd1b44b0c19af710b5a679907e284acd2ddc285201794bc69a2389d77baedddaeselect ?

规则生效后 normalized_pattern和normalized_pattern 被更新。

当有错误发生时,错误信息会写入字段message。统计刷新 Rewriter_reload_error 为on

root@localhost 16:43:27 [query_rewrite]>show status like 'rewriter_reload_error' ;
Variable_nameValue
Rewriter_reload_errorOFF

匹配语句的长度受max_digest_length 限制

适用范围

openEuler-22.03-LTS + opengauss5.0.1
问题概述

 执行下面命令gs_preinstall陷入死循环,一直等待,没有结果

[root@openeuler161 script]# script/gs_preinstall -U omm -G dbgrp -X /opt/software/openGauss/cluster_config.xml

问题原因

执行gs_preinstall时,需要python赖包,openEuler-22.03自带python3.9版本,没有paramiko及依赖包

check_python_version()
check_python_compiler_option()
from base_utils.os.file_util import FileUtil
if "--unused-third-party" in sys.argv:

package_path = os.path.dirname(os.path.realpath(__file__))
lib_path = os.path.join(package_path, "..", "lib")
clib_files = os.path.join(package_path, "gspylib/clib/*.so*")
FileUtil.cleanDirectoryContent(lib_path)
FileUtil.removeFile(clib_files)

# use system pip dependecies
import psutil
import netifaces
import cryptography
import paramiko

from gspylib.common.GaussLog import GaussLog
from gspylib.common.Common import DefaultValue
from gspylib.common.ErrorCode import ErrorCode
from gspylib.common.ParallelBaseOM import ParallelBaseOM

导入paramiko包报没有

[root@localhost ~]# cat /etc/openEuler-latest
openeulerversion=openEuler-22.03-LTS
compiletime=2022-03-30-16-23-56
gccversion=10.3.1-10.oe2203
kernelversion=5.10.0-60.18.0.50.oe2203
openjdkversion=1.8.0.312.b07-11.oe2203
[root@localhost ~]#
[root@localhost ~]#
[root@localhost ~]# python3
Python 3.9.9 (main, Mar 15 2022, 00:00:00)
[GCC 10.3.1] on linux
Type "help", "copyright", "credits" or "license" for more information.

import paramiko

Traceback (most recent call last):
File "<stdin>", line 1, in <module>
ModuleNotFoundError: No module named 'paramiko'

执行gs_preinstall时会一直不进行下去

[root@openeuler161 script]# script/gs_preinstall -U omm -G dbgrp -X /opt/software/openGauss/cluster_config.xml

解决方案

1、安装paramiko包及相关的依赖包

[root@openeuler161 script]# pip3 install paramiko==3.4.0
[root@openeuler161 script]# pip3 install paramiko==3.4.0
WARNING: Running pip install with root privileges is generally not a good idea. Try pip3 install --user instead.
Looking in indexes: http://mirrors.aliyun.com/pypi/simple
Requirement already satisfied: paramiko==3.4.0 in /usr/local/lib/python3.9/site-packages (3.4.0)
Requirement already satisfied: cryptography>=3.3 in /usr/local/lib64/python3.9/site-packages (from paramiko==3.4.0) (42.0.7)
Requirement already satisfied: bcrypt>=3.2 in /usr/local/lib64/python3.9/site-packages (from paramiko==3.4.0) (4.1.3)
Requirement already satisfied: pynacl>=1.5 in /usr/local/lib64/python3.9/site-packages (from paramiko==3.4.0) (1.5.0)
Requirement already satisfied: cffi>=1.12 in /usr/local/lib64/python3.9/site-packages (from cryptography>=3.3->paramiko==3.4.0) (1.16.0)
Requirement already satisfied: pycparser in /usr/local/lib/python3.9/site-packages (from cffi>=1.12->cryptography>=3.3->paramiko==3.4.0) (2.22

2、重新执行gs_preinstall

[root@openeuler161 script]# ./gs_preinstall -U omm -G dbgrp -X ../cluster_ms.xml
Parsing the configuration file.
Successfully parsed the configuration file.
Installing the tools on the local node.
Successfully installed the tools on the local node.
Are you sure you want to create trust for root (yes/no)?yes
Please enter password for root
Password:
Successfully created SSH trust for the root permission user.
Setting host ip env
Successfully set host ip env.
Distributing package.
Begin to distribute package to tool path.
Successfully distribute package to tool path.
Begin to distribute package to package path.
Successfully distribute package to package path.
Successfully distributed package.
Are you sure you want to create the user[omm] and create trust for it (yes/no)? no
Preparing SSH service.
Successfully prepared SSH service.
Installing the tools in the cluster.
Successfully installed the tools in the cluster.
Checking hostname mapping.
Successfully checked hostname mapping.
Checking OS software.
Successfully check os software.
Checking OS version.
Successfully checked OS version.
Creating cluster's path.
Successfully created cluster's path.
Set and check OS parameter.
Setting OS parameters.
Successfully set OS parameters.
Warning: Installation environment contains some warning messages.
Please get more details by "/opt/software/openGauss/script/gs_checkos -i A -h openeuler161,openeuler162 --detail".
Set and check OS parameter completed.
Preparing CRON service.
Successfully prepared CRON service.
Setting user environmental variables.
Successfully set user environmental variables.
Setting the dynamic link library.
Successfully set the dynamic link library.
Setting Core file
Successfully set core path.
Setting pssh path
Successfully set pssh path.
Setting Cgroup.
Successfully set Cgroup.
Set ARM Optimization.
No need to set ARM Optimization.
Fixing server package owner.
Setting finish flag.
Successfully set finish flag.
Preinstallation succeeded.
[root@openeuler161 script]#

参考文档

https://gitee.com/opengauss/openGauss-OM/pulls/571

适用范围

11g RAC 打补丁时,遇到权限问题
问题概述

问题原因

解决方案

关闭集群

tar -pcvf /oracleback/app.tar app

tar -pxvf /oracleback/app.tar -C /oracle

找出文件数量较多的目录,手动删除无效trace

ls -aR /oracle 2>/dev/null | awk '/:$/{if(fileCount>999){print fileCount,dirName};dirName=substr($0,1,length($0)-1);fileCount=-2}{fileCount++}'

GI补丁
root执行

/oracle/app/11.2.0/grid/crs/install/rootcrs.pl -unlock

grid 执行

--28729234 OCW PATCH SET UPDATE 11.2.0.4.190115
$ORACLE_HOME/OPatch/opatch napply -oh $ORACLE_HOME -local /u01/soft/28813878/28729234 -ocmrf /home/grid/ocm.rsp
--28729245 ACFS PATCH SET UPDATE 11.2.0.4.190115
$ORACLE_HOME/OPatch/opatch napply -oh $ORACLE_HOME -local /u01/soft/28813878/28729245 -ocmrf /home/grid/ocm.rsp
--28729262 DB PSU 11.2.0.4.190115 (INCLUDES CPUJan2019)
$ORACLE_HOME/OPatch/opatch napply -oh $ORACLE_HOME -local /u01/soft/28813878/28729262 -ocmrf /home/grid/ocm.rsp

ORACLE 补丁
oracle 执行

[oracle@rac1 ~]$ /u01/soft/28813878/28729234/custom/server/28729234/custom/scripts/prepatch.sh -dbhome $ORACLE_HOME
/u01/soft/28813878/28729234/custom/server/28729234/custom/scripts/prepatch.sh completed successfully.

--28729234 OCW PATCH SET UPDATE 11.2.0.4.190115 注意路径深度
$ORACLE_HOME/OPatch/opatch napply -oh $ORACLE_HOME -local /u01/soft/28813878/28729234/custom/server/28729234 -ocmrf /home/oracle/ocm.rsp
--28729262 DB PSU 11.2.0.4.190115 (INCLUDES CPUJan2019)
$ORACLE_HOME/OPatch/opatch napply -oh $ORACLE_HOME -local /u01/soft/28813878/28729262 -ocmrf /home/oracle/ocm.rsp
[oracle@rac1 ~]$ /u01/soft/28813878/28729234/custom/server/28729234/custom/scripts/postpatch.sh -dbhome $ORACLE_HOME

root执行

/oracle/app/11.2.0/grid/rdbms/install/rootadd_rdbms.sh
/oracle/app/11.2.0/grid/crs/install/rootcrs.pl -patch