ANBOB™

数据库运行过程中在错误日志或SQL运行时报错难以避免，oracle预制了好多错误代码，也有不确定性的会在ora-600 700 7445中， 所以Oracle DBA通常是先看ORA-xxxxx编号的错误，确认是否与数据库层相关，oracle database提供了一个命令行工具oerr工具查看错误代码的message和一些很友善action简单的处理建议。 好奇其它两个主流开源数据库有没有相同的工具？这里简单的记录

# oracle

SQL> create table test(id int primary key, name varchar2(10));
Table created.

SQL> insert into test values(1,'anbob.com');
1 row created.

SQL> insert into test values(1,'anbob.com');
insert into test values(1,'anbob.com')
*
ERROR at line 1:
ORA-00001: unique constraint (SYS.SYS_C008329) violated

SQL> exec dbms_output.put_line(sqlerrm(-1));
ORA-00001: unique constraint (.) violated
PL/SQL procedure successfully completed.

[oracle@oel7db1 ~]$ oerr ora 1
00001, 00000, "unique constraint (%s.%s) violated"
// *Cause: An UPDATE or INSERT statement attempted to insert a duplicate key.
//         For Trusted Oracle configured in DBMS MAC mode, you may see
//         this message if a duplicate entry exists at a different level.
// *Action: Either remove the unique restriction or do not insert the key.

Note:
这是简单的oracle中错误代码到message和处理建议，有些event code是数据库保留用于internal 诊断使用，关于oerr list的在十几年的笔记有记录。

How to list all events?（oracle事件列表）和windows配置oerr 查询oracle错误

# mysql

mysql> create table test(id int primary key,name varchar(10));
Query OK, 0 rows affected (0.04 sec)

mysql> insert into test values(1,'anbob');
Query OK, 1 row affected (0.00 sec)

mysql> insert into test values(1,'anbob');
ERROR 1062 (23000): Duplicate entry '1' for key 'test.PRIMARY'


[root@oel7db1 ~]# perror 1062
MySQL error code MY-001062 (ER_DUP_ENTRY): Duplicate entry '%-.192s' for key %d
[root@oel7db1 ~]# perror -v 1062
MySQL error code MY-001062 (ER_DUP_ENTRY): Duplicate entry '%-.192s' for key %d
[root@oel7db1 ~]# perror -s 1062
Duplicate entry '%-.192s' for key %d

# strace -ttt -T perror 1062

[root@oel7db1 private]# strings /usr/local/mysql/bin/perror|grep -i "Duplicate entry "
Duplicate entry '%-.192s' for key %d
ALTER TABLE causes auto_increment resequencing, resulting in duplicate entry '%-.192s' for key '%-.192s'
Duplicate entry '%-.64s' for key '%-.385s'
Foreign key constraint for table '%.192s', record '%-.192s' would lead to a duplicate entry in table '%.192s', key '%.192s'
Foreign key constraint for table '%.192s', record '%-.192s' would lead to a duplicate entry in a child table
Duplicate entry for key '%-.192s'
Duplicate entry '%-.192s'.

[root@oel7db1 private]# strings /usr/local/mysql/bin/perror|grep -i ER_IB_MSG
ER_IB_MSG_UNEXPECTED_FILE_EXISTS
ER_IB_MSG_DDL_LOG_DELETE_BY_ID_OK
ER_IB_MSG_WAIT_FOR_ENCRYPT_THREAD
ER_IB_MSG_NO_ENCRYPT_PROGRESS_FOUND
ER_IB_MSG_MADV_DONTDUMP_UNSUPPORTED
ER_IB_MSG_FAILED_TO_ALLOCATE_WAIT
...
ER_IB_MSG_POST_RECOVER_DDL_LOG_RECOVER
ER_IB_MSG_POST_RECOVER_POST_TS_ENCRYPT
ER_IB_MSG_DDL_LOG_FAIL_POST_DDL
ER_IB_MSG_CLOCK_MONOTONIC_UNSUPPORTED
ER_IB_MSG_CLOCK_GETTIME_FAILED
...
ER_IB_MSG_57_UNDO_SPACE_DELETE_FAIL
ER_IB_MSG_GTID_FLUSH_AT_SHUTDOWN
ER_IB_MSG_57_STAT_SPACE_DELETE_FAIL
ER_IB_MSG_INNODB_START_INITIALIZE
ER_IB_MSG_INNODB_END_INITIALIZE
ER_IB_MSG_PAGE_ARCH_NO_RESET_POINTS
ER_IB_MSG_0
ER_IB_MSG_1
ER_IB_MSG_2
ER_IB_MSG_3
ER_IB_MSG_4
ER_IB_MSG_5
ER_IB_MSG_6
ER_IB_MSG_7
ER_IB_MSG_8
ER_IB_MSG_9
ER_IB_MSG_10
ER_IB_MSG_11
ER_IB_MSG_12
ER_IB_MSG_13
...

[root@oel7db1 private]# perror 11
OS error code  11:  Resource temporarily unavailable
MySQL error code MY-000011: Can't unlock file (OS errno %d - %s)

Note:

在mysql可见有perror对应，报错时同样有错误代码，perror显示 MySQL 或操作系统错误代码的错误消息。… 系统错误消息的含义可能取决于您的操作系统。给定的错误代码在不同的操作系统上可能意味着不同的东西。错误代码也可参考MySQL官方Chapter 2 Server Error Message Reference ，注意perror有OS的信息，如上面的11提示OS层资源不足，有点Oracle的影子了，相信在ORACLE看到是一个综合性报错。但是perror没有oerr的Action信息。

ORA-27300: OS system dependent operation:fork failed with status: 11
ORA-27301: OS failure message: Resource temporarily unavailable

PostGreSQL/ OpenGuass DB

@@@ pg
sdbo=# create table test(id int primary key, name varchar(20));
CREATE TABLE

sdbo=# insert into test values(1,'anbob');
INSERT 0 1

sdbo=# insert into test values(1,'anbob');
错误:  重复键违反唯一约束"test_pkey"
描述:  键值"(id)=(1)" 已经存在

sdbo=# select version();
                          version
------------------------------------------------------------
 PostgreSQL 13.1, compiled by Visual C++ build 1914, 64-bit
(1 行记录)

@@@  og
anbob=# select version();
                                                                       version
------------------------------------------------------------------------------------------------------------------------------------------------------
 (openGauss 2.1.0 build 590b0f8e) compiled at 2021-09-30 14:29:04 commit 0 last mr   on x86_64-unknown-linux-gnu, compiled by g++ (GCC) 7.3.0, 64-bit
(1 row)


anbob=# create table test(id int primary key, name varchar(20));
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "test_pkey" for table "test"
CREATE TABLE
anbob=# insert into test values(1,'anbob');
INSERT 0 1
anbob=# insert into test values(1,'anbob');
ERROR:  duplicate key value violates unique constraint "test_pkey"
DETAIL:  Key (id)=(1) already exists.

Note:
Pg系更直接，在SQL错误时没有代码代码，只有error message，  但是对于SQL标准中有对SQLSTATE的要求，用于应用捕捉错误，在posgresql.cn社区有错误代码查询，PostgreSQL错误代码

打赏

微信扫一扫，打赏作者吧～