Tag Archives: database

Oracle Database AWR error: ORA-06502 [How to Solve]

Oracle database AWR report is an important tool for DBA to analyze database performance!

Recently encountered a problem and reported an error:

ERROR:
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at "SYS.DBMS_WORKLOAD_REPOSITORY", line 919
ORA-06512: at line 1

So, how to solve this problem?

Solution:

update WRH$_SQLTEXT set sql_text = SUBSTR(sql_text, 1, 1000);
commit;

After execution, re execute sqlplus/as SYSDBA @/ RDMBS/admin/awrrpt. SQL script successfully generates AWR report!

This sharing is over~

[Solved] Error: read ECONNRESET at TCP.onStreamRead Cannot enqueue Query after fatal error

Error analysis

This error often occurs when I use node to connect to MySQL. I wonder if it is because I do not close the connection every time I query the database after creating a MySQL connection, so the database is always connected and does not respond for a long time, resulting in the following error
error1:

error2:

 

Solution:

error1:

const mysql = require('mysql');
const connInfo = require('./config');

function queryMysql(sql, callback){
    const conn = mysql.createConnection(connInfo)
    conn.query(sql, (err, result)=>{
        if(err){
            console.log(err);
        }else {
            callback(result)
        }
        conn.end(err => {
            if(err){
                console.log(err)
            }
        })
    })
}

module.exports = queryMysql;

Error2:
We only need to configure useConnectionPooling : true when instantiating SessionStore . for example:

var sessionStore = new SessionStore({
    host: 'localhost' ,
    port: 3306 ,
    user: 'root' ,
    password: 'root' ,
    database: 'session' ,
    useConnectionPooling: true 
});

[Solved] WordPress Upgrade PHP 5.6 to 7.x Fatal error: Uncaught Error: Call to undefined function mysql_connect()

Error reporting when upgrading PHP version 5.6 to 7. X under WordPress (resolved)

Cause of event:

When using WordPress, the theme needs to be upgraded. The PHP version required for viewing the theme is 7.1+

When the website is not backed up, the PHP version is changed to 7.2, which directly leads to the website error

Fatal error: Uncaught Error: Call to undefined function mysql_ connect()

Check many online tutorials, which are MySQL in wp-db.php_Change connect() to mysqli_Connect(), no result

At first, I thought that the database reported an error, checked the database in detail, wrote checkdb.php, locally connected to the database, and checked that the database server was running well

Solution:

Set define ('wp_use_ext_mysql ', true) in wp-config.php delete

error explanation:

define('WP_USE_EXT_MYSQL',true);

define(‘WP_USE_EXT_MYSQL’, true); Force WP to use MySQL by default instead of MySQL Li
so please delete this line and the problem should be solved.

Otherwise, you can check nd in the PHP 7 configuration_Mysqli extension, and disable mysqli extension – > Select the PHP version.

[Solved] PostgreSQL enumeration type usage error: operator does not exist error handling 

//Creating Enumeration Classes
CREATE TYPE USER_ROLE AS ENUM ('MALE', 'FEMALE');
//Add conversion rules
CREATE CAST (VARCHAR AS USER_ROLE) WITH INOUT AS IMPLICIT;
//Create table, add fields of enumeration type
create table sys_user
(
    row_id      bigserial          not null
        constraint sys_user_pkey primary key,
    create_time timestamp(6),
    update_time timestamp(6),
    del_flag    smallint default 0 not null,
    role        USER_ROLE      not null,
    user_name   varchar(200)       not null
);

Because the conversion rule is added, you can directly use the varchar type string as the judgment condition query in pgadmin, but if you use mybatis to query the database, the error operator does not exist will be reported

select * from sys_user where del_flag = 0 and role = 'MALE'

Solution: convert varchar type to enumeration type and compare

Method 1
select * from sys_user where del_flag = 0 and role = cast(#{role} as user_role)；
Method 2
select * from sys_user where del_flag = 0 and role = #{role}::user_role;

Error querying database.Cause:java.sql.SQLSyntaxErrorException:ORA-00911:invalid character

This problem is well positioned. At first glance, it is the problem of parameter transmission. Through this error report, the first thing to consider is the like part of the query condition.

  The main idea of this problem is 1. Special characters are passed, such as’ ‘  

                                  2. See if the like query uses #{} or ${}

                                  3. Is there a problem with where involving parameter statements

 

 

MySQL — initialize failed to generate temporary password in error log after initialization

1. Version

1) Operating system version

cat /proc/version
Linux version 3.10.0-957.1.3.el7.x86_ 64 ( [email protected] ) (gcc version 4.8.5 20150623 (Red Hat 4.8.5-36) (GCC) ) #1 SMP Thu Nov 29 14:49:43 UTC 2018

2) Database version

mysql –version
mysql   Ver 8.0.13 for linux-glibc2.12 on x86_ 64 (MySQL Community Server – GPL)

2. Problem description

   In versions after 5.7.6, MySQL will be generated in the error log after initialization (specify the — initialize parameter for initialization)   root@localhost Temporary password for the user. However, in this deployment, after starting mysql, it was found that no temporary password was generated in the errorlog

3. Problem solving

Later, it was found that the file in/etc/my.cnf was accidentally deleted   log_ The error parameter is commented out, so that when initializing the instance, all information is sent to the standard output. When MySQL is started, it is based on the log_ The default value of error generates a log_ error

ERROR 1820 (HY000): Unknown error 1820,ERROR 1046 (3D000):

After MySQL logs in, an error occurs when executing the statement:

root@localhost 09:05:  [(none)]> use mysql;
ERROR 1820 (HY000): Unknown error 1820
root@localhost 09:05:  [(none)]> show tables;
ERROR 1046 (3D000):

terms of settlement:

root@localhost 09:06:   [(none)]> Set password = password (‘password ‘);

  I hope it will help you

[Solved] MYSQL Error: unknown error 1130, unknown error 1045

When using Navicat to link mysql, the following error is reported:

Solution:

Log in to MySQL on the server and enter use MySQL:

root@localhost 09:07:  [mysql]> update user set host = '%' where user = 'root';
Query OK, 1 row affected (0.07 sec)
root@localhost 09:12:  [mysql]> flush privileges;
Query OK, 0 rows affected (0.02 sec)

Then, using the Navicat link again, the following appears:

Solution:

GRANT ALL PRIVILEGES ON *.* TO ‘root’@’%’ IDENTIFIED BY ‘yourpassword’ WITH GRANT OPTION

root@localhost 09:13:  [mysql]> GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'yourpassword' WITH GRANT OPTION;
Query OK, 0 rows affected, 1 warning (0.02 sec)

root@localhost 09:14:  [mysql]> flush privileges;
Query OK, 0 rows affected (0.02 sec)

To sum up: this kind of error is probably caused by the permissions set by the root user in MySQL in the database

The uibot database connection is unrecognized error: The object definition error

When defining the database, the connection object is too long, so the object is separated from the definition first, and then database.createdb();

In order to simplify and enhance readability when separating objects, some typesetting and possible errors will be made, as follows:

//The following two cases are possible
dim a={"a":"123","b":"fg"}
dim a={
    "a":"123",
    "b":"fg"}
//One of the following cases is not allowed
dim a={
    "a":"123",
    "b":"fg"
}
//Separating the closing curly brace from the last element does not work.

[Solved] Redis Error: org.springframework.data.redis.RedisConnectionFailureExceptionjava.net.SocketTimeoutException

Error Message:
ERROR 760004 — [http-nio-443-exec-463]o.s.boot.web.support.ErrorPageFilter : Forwarding to error page from request [/***/***] due to exception [java.net.SocketTimeoutException: Read timed out; nested exception is redis.clients.jedis.exceptions.JedisConnectionException: java.net.SocketTimeoutException: Read timed out]

org.springframework.data.redis.RedisConnectionFailureException: java.net.SocketTimeoutException: Read timed out; nested exception is redis.clients.jedis.exceptions.JedisConnectionException: java.net.SocketTimeoutException: Read timed out
	at org.springframework.data.redis.connection.jedis.JedisExceptionConverter.convert(JedisExceptionConverter.java:67) ~[spring-data-redis-1.8.9.RELEASE.jar:na]
	at org.springframework.data.redis.connection.jedis.JedisExceptionConverter.convert(JedisExceptionConverter.java:41) ~[spring-data-redis-1.8.9.RELEASE.jar:na]
	at org.springframework.data.redis.PassThroughExceptionTranslationStrategy.translate(PassThroughExceptionTranslationStrategy.java:37) ~[spring-data-redis-1.8.9.RELEASE.jar:na]
	at org.springframework.data.redis.FallbackExceptionTranslationStrategy.translate(FallbackExceptionTranslationStrategy.java:37) ~[spring-data-redis-1.8.9.RELEASE.jar:na]
	at org.springframework.data.redis.connection.jedis.JedisConnection.convertJedisAccessException(JedisConnection.java:241) ~[spring-data-redis-1.8.9.RELEASE.jar:na]
	at org.springframework.data.redis.connection.jedis.JedisConnection.hGet(JedisConnection.java:2985) ~[spring-data-redis-1.8.9.RELEASE.jar:na]
	at org.springframework.data.redis.connection.DefaultStringRedisConnection.hGet(DefaultStringRedisConnection.java:364) ~[spring-data-redis-1.8.9.RELEASE.jar:na]
	at org.springframework.data.redis.core.DefaultHashOperations$1.doInRedis(DefaultHashOperations.java:52) ~[spring-data-redis-1.8.9.RELEASE.jar:na]
	at org.springframework.data.redis.core.DefaultHashOperations$1.doInRedis(DefaultHashOperations.java:49) ~[spring-data-redis-1.8.9.RELEASE.jar:na]
	at org.springframework.data.redis.core.RedisTemplate.execute(RedisTemplate.java:207) ~[spring-data-redis-1.8.9.RELEASE.jar:na]
	at org.springframework.data.redis.core.RedisTemplate.execute(RedisTemplate.java:169) ~[spring-data-redis-1.8.9.RELEASE.jar:na]
	at org.springframework.data.redis.core.AbstractOperations.execute(AbstractOperations.java:91) ~[spring-data-redis-1.8.9.RELEASE.jar:na]
	at org.springframework.data.redis.core.DefaultHashOperations.get(DefaultHashOperations.java:49) ~[spring-data-redis-1.8.9.RELEASE.jar:na]
	at com.easyserp.service.TokenUtilImpl.getInfoByToken(TokenUtilImpl.java:62) ~[classes/:na]
	at com.easyserp.controller.logincontroller.LogincontrollerResp.wxShare(LogincontrollerResp.java:245) ~[classes/:na]
	at sun.reflect.GeneratedMethodAccessor379.invoke(Unknown Source) ~[na:na]
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:1.8.0_221]
	at java.lang.reflect.Method.invoke(Method.java:498) ~[na:1.8.0_221]
	at org.springframework.web.method.support.InvocableHandlerMethod.doInvoke(InvocableHandlerMethod.java:205) ~[spring-web-4.3.13.RELEASE.jar:4.3.13.RELEASE]
	at org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:133) ~[spring-web-4.3.13.RELEASE.jar:4.3.13.RELEASE]
	at org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:97) ~[spring-webmvc-4.3.13.RELEASE.jar:4.3.13.RELEASE]
	at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandlerMethod(RequestMappingHandlerAdapter.java:827) ~[spring-webmvc-4.3.13.RELEASE.jar:4.3.13.RELEASE]
	at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:738) ~[spring-webmvc-4.3.13.RELEASE.jar:4.3.13.RELEASE]
	at org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:85) ~[spring-webmvc-4.3.13.RELEASE.jar:4.3.13.RELEASE]
	at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:967) ~[spring-webmvc-4.3.13.RELEASE.jar:4.3.13.RELEASE]
	at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:901) ~[spring-webmvc-4.3.13.RELEASE.jar:4.3.13.RELEASE]
	at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:970) ~[spring-webmvc-4.3.13.RELEASE.jar:4.3.13.RELEASE]
	at org.springframework.web.servlet.FrameworkServlet.doGet(FrameworkServlet.java:861) ~[spring-webmvc-4.3.13.RELEASE.jar:4.3.13.RELEASE]
	at javax.servlet.http.HttpServlet.service(HttpServlet.java:624) ~[servlet-api.jar:na]
	at org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:846) ~[spring-webmvc-4.3.13.RELEASE.jar:4.3.13.RELEASE]
	at javax.servlet.http.HttpServlet.service(HttpServlet.java:731) ~[servlet-api.jar:na]
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:303) [catalina.jar:7.0.88]
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208) [catalina.jar:7.0.88]
	at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52) ~[tomcat7-websocket.jar:7.0.88]
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:241) [catalina.jar:7.0.88]
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208) [catalina.jar:7.0.88]
	at org.springframework.boot.web.filter.ApplicationContextHeaderFilter.doFilterInternal(ApplicationContextHeaderFilter.java:55) ~[spring-boot-1.5.9.RELEASE.jar:1.5.9.RELEASE]
	at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107) [spring-web-4.3.13.RELEASE.jar:4.3.13.RELEASE]
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:241) [catalina.jar:7.0.88]
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208) [catalina.jar:7.0.88]
	at org.springframework.boot.actuate.trace.WebRequestTraceFilter.doFilterInternal(WebRequestTraceFilter.java:110) ~[spring-boot-actuator-1.5.9.RELEASE.jar:1.5.9.RELEASE]
	at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107) [spring-web-4.3.13.RELEASE.jar:4.3.13.RELEASE]
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:241) [catalina.jar:7.0.88]
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208) [catalina.jar:7.0.88]
	at org.springframework.web.filter.CharacterEncodingFilter.doFilterInternal(CharacterEncodingFilter.java:197) ~[spring-web-4.3.13.RELEASE.jar:4.3.13.RELEASE]
	at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107) [spring-web-4.3.13.RELEASE.jar:4.3.13.RELEASE]
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:241) [catalina.jar:7.0.88]
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208) [catalina.jar:7.0.88]
	at org.springframework.boot.actuate.autoconfigure.MetricsFilter.doFilterInternal(MetricsFilter.java:106) ~[spring-boot-actuator-1.5.9.RELEASE.jar:1.5.9.RELEASE]
	at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107) [spring-web-4.3.13.RELEASE.jar:4.3.13.RELEASE]
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:241) [catalina.jar:7.0.88]
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208) [catalina.jar:7.0.88]
	at org.springframework.boot.web.support.ErrorPageFilter.doFilter(ErrorPageFilter.java:115) [spring-boot-1.5.9.RELEASE.jar:1.5.9.RELEASE]
	at org.springframework.boot.web.support.ErrorPageFilter.access$000(ErrorPageFilter.java:59) [spring-boot-1.5.9.RELEASE.jar:1.5.9.RELEASE]
	at org.springframework.boot.web.support.ErrorPageFilter$1.doFilterInternal(ErrorPageFilter.java:90) [spring-boot-1.5.9.RELEASE.jar:1.5.9.RELEASE]
	at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107) [spring-web-4.3.13.RELEASE.jar:4.3.13.RELEASE]
	at org.springframework.boot.web.support.ErrorPageFilter.doFilter(ErrorPageFilter.java:108) [spring-boot-1.5.9.RELEASE.jar:1.5.9.RELEASE]
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:241) [catalina.jar:7.0.88]
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208) [catalina.jar:7.0.88]
	at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:219) [catalina.jar:7.0.88]
	at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:110) [catalina.jar:7.0.88]
	at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:498) [catalina.jar:7.0.88]
	at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:169) [catalina.jar:7.0.88]
	at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:103) [catalina.jar:7.0.88]
	at org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:1025) [catalina.jar:7.0.88]
	at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:116) [catalina.jar:7.0.88]
	at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:445) [catalina.jar:7.0.88]
	at org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:1139) [tomcat-coyote.jar:7.0.88]
	at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:637) [tomcat-coyote.jar:7.0.88]
	at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1775) [tomcat-coyote.jar:7.0.88]
	at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.run(NioEndpoint.java:1734) [tomcat-coyote.jar:7.0.88]
	at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149) [na:1.8.0_221]
	at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624) [na:1.8.0_221]
	at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61) [tomcat-coyote.jar:7.0.88]
	at java.lang.Thread.run(Thread.java:748) [na:1.8.0_221]
Caused by: redis.clients.jedis.exceptions.JedisConnectionException: java.net.SocketTimeoutException: Read timed out
	at redis.clients.util.RedisInputStream.ensureFill(RedisInputStream.java:202) ~[jedis-2.9.0.jar:na]
	at redis.clients.util.RedisInputStream.readByte(RedisInputStream.java:40) ~[jedis-2.9.0.jar:na]
	at redis.clients.jedis.Protocol.process(Protocol.java:151) ~[jedis-2.9.0.jar:na]
	at redis.clients.jedis.Protocol.read(Protocol.java:215) ~[jedis-2.9.0.jar:na]
	at redis.clients.jedis.Connection.readProtocolWithCheckingBroken(Connection.java:340) ~[jedis-2.9.0.jar:na]
	at redis.clients.jedis.Connection.getBinaryBulkReply(Connection.java:259) ~[jedis-2.9.0.jar:na]
	at redis.clients.jedis.BinaryJedis.hget(BinaryJedis.java:855) ~[jedis-2.9.0.jar:na]
	at org.springframework.data.redis.connection.jedis.JedisConnection.hGet(JedisConnection.java:2983) ~[spring-data-redis-1.8.9.RELEASE.jar:na]
	... 69 common frames omitted
Caused by: java.net.SocketTimeoutException: Read timed out
	at java.net.SocketInputStream.socketRead0(Native Method) ~[na:1.8.0_221]
	at java.net.SocketInputStream.socketRead(SocketInputStream.java:116) ~[na:1.8.0_221]
	at java.net.SocketInputStream.read(SocketInputStream.java:171) ~[na:1.8.0_221]
	at java.net.SocketInputStream.read(SocketInputStream.java:141) ~[na:1.8.0_221]
	at java.net.SocketInputStream.read(SocketInputStream.java:127) ~[na:1.8.0_221]
	at redis.clients.util.RedisInputStream.ensureFill(RedisInputStream.java:196) ~[jedis-2.9.0.jar:na]
	... 76 common frames omitted

Solution:
change the redis configuration parameter and path in the project

# REDIS (RedisProperties)
# Redis database index (default is 0)
spring.redis.database=0
# Redis server address
spring.redis.host=localhost
# Redis server connection port
spring.redis.port=6379
# Redis server connection password (default is empty)
spring.redis.password=
# Maximum number of connections to the connection pool (use negative values to indicate no limit)
spring.redis.pool.max-active=1000
# Maximum connection pool blocking wait time (use negative values to indicate no limit)
spring.redis.pool.max-wait=-1
# The maximum idle connections in the connection pool
spring.redis.pool.max-idle=300
# The minimum idle connections in the connection pool
spring.redis.pool.min-idle=100
# Connection timeout (milliseconds)
spring.redis.timeout=60000

Check whether the configuration bind 127.0.0.1 in redis.windows-service.conf file is the local IP address
the maximum number of connections in the redis connection pool can only alleviate the concurrency to a certain extent. In stand-alone applications, redis is a single thread. Even if the configuration is large, it can not fundamentally solve the problem. It is recommended to build a redis cluster.