[MySQL/MariaDB] About Timeout

There are 3 kinds of timeout in MySQL or MariaDB and you can change timeout modifying 'my.cnf' or executing queries.

1. CONNECT_TIMEOUT

For bad handshake timeout. When MySQL client try to connect to mysqld(daemon), CONNECT_TIMEOUT is maximum time to wait connect packet for mysqld.

About errors.
1) Aborted_connects : FAILED TO CONNECT. When connecting to server, it is failed to connect.
2) Aborted_clients : CANCEL CONNECTION FORCIBLY. When connected to server, server close connection forcibly.

2. INTERACTIVE_TIMEOUT

This is timeout in interactive mode. The interactive mode means console mode in prompt or terminal mode.
It is maximum time that wait to receive request(Query) from client.

About Errors
ERROR 2006: MySQL server has gone away  No connection. Trying to reconnect...  Connection id:    12002  Current database: xxx

3. WAIT_TIMEOUT

This is timeout in not interactive mode likes in applications for database. It is maximum time that wait to receive request(Query) from client.














✑ References : http://www.mysqlkorea.com/gnuboard4/bbs/board.php?bo_table=community_04&wr_id=705

[MySQL/MariaDB] Plugin 'unix_socket' is not loaded

When use mysql -u root -p command, I met "Plugin ‘unix_socket’ is not loaded" message. This issue relates to Unix authentication plugin.

Solution

1. Edit 50-server.cnf file

2. Add option

3. Restart MariaDB

[MySQL/MariaDB] Can't init tc.log

1. cd /var/lib/mysql

2. Remove tc.log file and start server

[Oracle] 테이블 컬럼명, Comment 확인

SELECT A.COLUMN_NAME, B.COMMENTS, A.DATA_TYPE
FROM COLS A, USER_COL_COMMENTS B
WHERE A.TABLE_NAME = '[TABLE_NAME]'
AND A.TABLE_NAME = B.TABLE_NAME
AND A.COLUMN_NAME = B.COLUMN_NAME
;

[Oracle] Oracle Thin JDBC Driver URL Format

1. SID vs Service Name

SID :  DB 인스턴스명 (Unique)
Service Name : 1개 이상의 인스턴스의 별칭. 같은 DB를 다를 용도로 사용할 때, 이를 구별하기 위해도 쓸 수 있다.

2. Thin Driver URL Format

SID (no longer recommended by Oracle to be used):

 jdbc:oracle:thin:[<user>/<password>]@<host>[:<port>]:<SID>

Services:

 jdbc:oracle:thin:[<user>/<password>]@//<host>[:<port>]/<service>

TNSNames:

 jdbc:oracle:thin:[<user>/<password>]@<TNSName>

* ref

https://stackoverflow.com/questions/43866/how-sid-is-different-from-service-name-in-oracle-tnsnames-ora
http://www.orafaq.com/wiki/JDBC

[Oracle] Oracle JDBC Charset 변환 과정

Database Charset이 US77ASCII 나 WE8ISO8859P1 일 경우, JDBC 드라이버가 데이터를 무조건 UTF16(Java에서 String 인코딩) 으로 변화하여 클라이언트로 전달.

Database Charset이 US77ASCII 나 WE8ISO8859P1 가 아닐 경우, JDBC 드라이버가 데이터를 UTF8로 변환 후, UTF16로 다시 변환하여 클라이언트로 전달.(Server-side일 경우, UTF8로 변환하는 과정 생략)

* reference : https://docs.oracle.com/cd/B10501_01/java.920/a96654/advanc.htm

[Oracle] LONG data type

Long type in Oracle is not bigint.

LOB Character Datatypes

The LOB datatypes for character data are CLOB and NCLOB. They can store up to 8 terabytes of character data (CLOB) or national character set data (NCLOB).

See Also:

"Overview of LOB Datatypes"

LONG Datatype

Note:

Do not create tables with LONG columns. Use LOB columns (CLOB, NCLOB) instead. LONG columns are supported only for backward compatibility.

Oracle also recommends that you convert existing LONG columns to LOB columns. LOB columns are subject to far fewer restrictions than LONG columns. Further, LOB functionality is enhanced in every release, whereas LONG functionality has been static for several releases.

Columns defined as LONG can store variable-length character data containing up to 2 gigabytes of information. LONG data is text data that is to be appropriately converted when moving among different systems.

LONG datatype columns are used in the data dictionary to store the text of view definitions. You can use LONG columns in SELECT lists, SET clauses of UPDATE statements, and VALUES clauses of INSERT statements.

reference : https://docs.oracle.com/cd/B28359_01/server.111/b28318/datatype.htm#CNCPT1830

[Oracle] Query for looking index

SELECT A.UNIQUENESS
     , A.INDEX_TYPE
     , A.TABLE_OWNER
     , A.TABLE_NAME
     , B.*
  FROM ALL_INDEXES A,
       ALL_IND_COLUMNS B
 WHERE A.INDEX_NAME = B.INDEX_NAME
   AND A.TABLE_NAME=UPPER('TAGPA201_ENC'); 
;

[Oracle] Oracle JDBC DatabaseMetaData.getIndexInfo() occurs analyze table compute statistics

When I called DatabaseMetaData.getIndexInfo() for checking index, Oracle database was very slow. So, I wonder why oracle is getting slow.

ResultSet getIndexInfo(String catalog, String schema, String table, boolean unique, boolean approximate) throws SQLException

Retrieves a description of the given table’s indices and statistics. They are ordered by NON_UNIQUE, TYPE, INDEX_NAME, and ORDINAL_POSITION.

Each index column description has the following columns: (snipped)

Parameters:
catalog – a catalog name; must match the catalog name as it is stored in this database; “” retrieves those without a catalog; null means that the catalog name should not be used to narrow the search
schema – a schema name; must match the schema name as it is stored in this database; “” retrieves those without a schema; null means that the schema name should not be used to narrow the search
table – a table name; must match the table name as it is stored in this database
unique – when true, return only indices for unique values; when false, return indices regardless of whether unique or not
approximate – when true, result is allowed to reflect approximate or out of data values; when false, results are requested to be accurate

When this method is called, Oracle JDBC driver execute analyze table statistics.
If approximate is true, execute analyze table schema.table estimate statistics.
On the other hand, if approximate is false, analyze table schema.table compute statistics.

Estimate occurs less overhead than compute. But analyze table makes database slow-down


reference
https://timurakhmadeev.wordpress.com/2010/01/11/databasemetadatagetindexinfo/

[Oracle] How to set different SID in Oracle JDBC Failover URL

jdbc:oracle:thin:@(DESCRIPTION_LIST=
    (FAILOVER=ON)(LOAD_BALANCE=OFF)
    (DESCRIPTION=
    (ADDRESS=(PROTOCOL=TCP)(HOST=[DB1_HOST])(PORT=[DB1_PORT]))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=[DB1_SID])))
    (DESCRIPTION=
    (ADDRESS=(PROTOCOL=TCP)(HOST=[DB2_HOST])(PORT=[DB2_PORT]))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=[DB2_SID)))
    )
 

[PostgreSQL] How to exit from PostgreSQL command line utility: psql (psql 종료 방법)

Type \q

[PostgreSQL] Install PostgreSQL

1.Install Server & Libraries

#> yum -y install postgresql-server postgresql-contrib

2. Initialize database

#> postgresql-setup initdb

3. Edit configuration

#> su - postgres
$> cd data
$> vi pg_hba.conf

TYPE    DATABASE  USER    ADDRESS         METHOD
host    all       all     127.0.0.1/32    md5
host    all       all     192.168.0.0/24  md5
host    all       all     0.0.0.0/0       md5

$> vi postgresql.conf

4. Start server

#> systemctl start postgresql
#> systemctl status postgresql

5. Create DB

#> su - postgres
$> createdb [DB_NAME]

6. Create user and grant privilege

$> psql -s [DB_NAME]
$> create user [USER_NAME] password '[PASSWORD]';
$> GRANT ALL PRIVILEGES ON DATABASE [DB_NAME] TO [USER_NAME];

[Oracle] How to show trigger or procedure error log (트리거 또는 프로시져 에러 로그 확인 방법)

-- Current user error log
SELECT * FROM SYS.USER_ERRORS;
-- Current user error log related with trigger
SELECT * FROM SYS.USER_ERRORS WHERE TYPE ='TRIGGER';
-- Current user error log related with procedure
SELECT * FROM SYS.USER_ERRORS WHERE TYPE ='PROCEDURE';

-- All user error log
SELECT * FROM SYS.DBA_ERRORS;
-- All user error log related with trigger
SELECT * FROM SYS.DBA_ERRORS WHERE TYPE ='TRIGGER';
-- All user error log related with procedure
SELECT * FROM SYS.DBA_ERRORS WHERE TYPE ='PROCEDURE';

 

[Oracle] Charset 확인

SELECT NAME, VALUE$ FROM SYS.PROPS$ WHERE NAME = 'NLS_CHARACTERSET';
SELECT NAME, VALUE$ FROM SYS.PROPS$ WHERE NAME = 'NLS_NCHAR_CHARACTERSET';
SELECT NAME, VALUE$ FROM SYS.PROPS$ WHERE NAME = 'NLS_LANGUAGE';

[Oracle] Unlock user that has expired password (사용자 계정 잠금/해제)

Check default password expiration time (기본 비밀번호 만료 기간 확인)

SELECT RESOURCE_NAME, LIMIT FROM DBA_PROFILES WHERE PROFILE='DEFAULT' AND RESOURCE_NAME LIKE 'PASSWORD%';

Set password expiration disabled (비밀번호 만료 해제)

ALTER PROFILE DEFAULT LIMIT  PASSWORD_LIFE_TIME UNLIMITED;

Check users have expired password (비밀번호 만료된 유저 확인)

SELECT USERNAME, ACCOUNT_STATUS, LOCK_DATE, EXPIRY_DATE FROM DBA_USERS WHERE ACCOUNT_STATUS = 'EXPIRED';

Unlock user (잠금계정 해제)

ALTER USER [USER_NAME] IDENTIFIED BY [USER_NAME];

[SUNDB] JDBC Connection Properties

[SUNDB] Install SUNDB (SUNDB 설치)

1. Setup Kernel Parameters related with Shared Memory (Shared Memory 관련 커널 파라미터 수정)

SUNDB architecture is not multi-thread but multi-process based on shared memory.
Therefore, you must kernel parameters related with shared memory.
(SUNDB는 multi-thread 구조가 아닌 shared memory 기반의 Multi-process 구조로 되어 있다. 그러므로 shared memory 관련된 커널 파라미터를 수정해줘야한다.)

1.1. Check Parameters

#> ipcs -l

1.2. Change parameters permanently and apply it (파라미터 변경 및 적용)

#> vi /etc/sysctl.conf

# shared memory
kernel.shmmax = 34359738368
kernel.shmall = 8388608
kernel.shmmni = 4096
# semaphore
kernel.sem = 250 32000 100 128

#> sysctl -p

2. Install DBMS

2.1. Uncompressed install file (설치파일 압축해제)

#> cd [INSTALL_DIRECTORY]
#> tar xvf [INSTALL_FILE]

2.2. Setup environment variables (환경변수 설정)

#> vi ~/.bashrc

# for SUNDB
export SUNDB_HOME=[INSTALLED_HOME_DIRECTROY]
export SUNDB_DATA=[INSTALLED_DATA_DIRECTROY]
export PATH=$SUNDB_HOME/bin:$PATH
export LD_LIBRARY=$SUNDB_HOME/lib:$LD_LIBRARY_PATH

#>. ~/.bashrc

3. Create Database (DB 생성)

3.1. create DB (DB 생성)

#> gcreatedb --help
#> gcreatedb --db_name="[DB_NAME]" --db_comment="[DB_COMMENT]" --timezone="[{+/-}{TZH:TZM}]" --character_set="[SQL_ASCII | UTF8 | UHC | GB18030] --char_length_units="[OCTETS | CHARACTERS]"

3.2. Open DB for creating dictionary schema (Dictionary Schema 생성을 위한 DB open)

#> gsql --as sysdba

gSQL> \startup open

3.3. Create dictionary schema (Dictionary Schema 생성)

#> gsql --as sysdba --import $SUNDB_HOME/admin/DictionarySchema.sql
#> gsql --as sysdba --import $SUNDB_HOME/admin/InformationSchema.sql
#> gsql --as sysdba --import $SUNDB_HOME/admin/PerformanceViewSchema.sql

4. Startup DB (DB 구동)

4.1. Startup DB (DB 구동)

#> gsql --as sysdb

gSQL> \startup nomount
gSQL> \alter system mount database;
gSQL> \alter system open database;

4.2. Startup listener (리스너 구동)

#> glsnr --start

[Redis] Scan in Java

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42

package test.redis; import java.util.List; import redis.clients.jedis.Jedis; import redis.clients.jedis.ScanResult; /* * ************************************************************************************ * * RedisScanTest.java * * ************************************************************************************ * * @version 1.0.00 2017-02-10 dorbae Initialize * @since 2017-02-10 * @author dorbae * * ************************************************************************************ */ public class RedisScanTest { public RedisScanTest() { // TODO Auto-generated constructor stub } public static void main(String[] args) { Jedis jedis = new Jedis("btcxen.esb.broker1"); System.out.println("Connection to server sucessfully"); ScanResult< String> rs = jedis.scan( "0"); List< String> list = rs.getResult(); for ( int ll = 0; ll<list.size(); ll++) { System.out.println("List of stored keys:: "+list.get(ll)); } jedis.close(); } }

[Redis] Hash in Java

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66

package test.redis; import java.util.Iterator; import java.util.Map; import java.util.Map.Entry; import redis.clients.jedis.Jedis; /* * ************************************************************************************ * * RedisHashTest.java * * ************************************************************************************ * * @version 1.0.00 2017-02-10 dorbae Initialize * @since 2017-02-10 * @author dorbae * * ************************************************************************************ */ public class RedisHashTest { public RedisHashTest() { // TODO Auto-generated constructor stub } public static void main(String[] args) { Jedis jedis = new Jedis( "btcxen.esb.broker1"); System.out.println( "Connection to server sucessfully"); jedis.hset( "hs1", "col1", "val1"); jedis.hset( "hs1", "col2", "val2"); jedis.hset( "hs1", "col3", "val3"); jedis.hset( "hs1", "col4", "val4"); java.util.Map< String, String> map = new java.util.HashMap< String, String>(); map.put( "col1", "mval1"); map.put( "col2", "mval2"); map.put( "col3", "mval3"); jedis.hmset( "hs1", map); jedis.hdel( "hs1", "col2", "col3"); System.out.println( "len=" + jedis.hlen( "hs1")); System.out.println( "hs1.col2="+ jedis.hget( "hs1", "col2")); map = jedis.hgetAll( "hs1"); Iterator< Entry< String, String>> iterator = map.entrySet().iterator(); Entry< String, String> entry = null; while ( iterator.hasNext()) { entry = iterator.next(); System.out.printf( "key:%s, value:%s%n", entry.getKey(), entry.getValue()); } jedis.close(); } }

[Redis] List in Java

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47

package test.redis; import java.util.List; import redis.clients.jedis.Jedis; /* * ************************************************************************************ * * RedisListTest.java * * ************************************************************************************ * * @version 1.0.00 2017-02-10 dorbae Initialize * @since 2017-02-10 * @author dorbae * * ************************************************************************************ */ public class RedisListTest { public RedisListTest() { // TODO Auto-generated constructor stub } public static void main(String[] args) { //Connecting to Redis server on localhost Jedis jedis = new Jedis("btcxen.esb.broker1"); System.out.println("Connection to server sucessfully"); //store data in redis list jedis.lpush("tutorial-list", "Redis"); jedis.lpush("tutorial-list", "Mongodb"); jedis.lpush("tutorial-list", "Mysql"); // Get the stored data and print it // List<String> list = jedis.lrange("tutorial-list", 0 ,1); List<String> list = jedis.lrange("tutorial-list", 0 ,6); for(int i = 0; i<list.size(); i++) { System.out.println("Stored string in redis:: "+list.get(i)); } jedis.close(); } }