고객사에서 MariaDB를 설치하고 있다. 그러던 와중, 잘 구동되던 Tomcat을 재구동하게 되었는데 오류메시지 "Access denied for user '계정명'@'localhost'(using password:YES)" 라는 오류가 발생한다. 이전까지는 재구동하는데 문제가 없었고, 오류가 생길만한 작업은 약 서너가지 정도가 짚히는 상황이었다. 현재 WAS 설정으로는, server.xml 및 context.xml 내에서 JNDI를 해당 MariaDB로 설정해둔 상태였다.


먼저, MariaDB에 익숙하지 않은 나인지라 용어의 혼란이 조금 있을 것 같아 아래와 같은 내용으로 이해를 하고 있다.  MariaDB는 계정별로 두 개의 권한을 갖는 듯한 모습이었다. MariaDB의 계정 테이블을 쿼리해보면 Host라고 작성된 부분이 있는데, 여기에 localhost 및 % 정도로 권한(?)이 부여가 된다. localhost는 내부에서만 접속이 가능하고, %은 내/외부에서 접속이 모두 가능한 권한 정도로 이해하고 있다.


1. 원인파악

본론으로 돌아와서 검색해보니 뭐 당연하게도 해당 계정의 localhost 권한이 없다는 문구다. 그래서 MariaDB가 설치된 서버에 접속하여 아래의 명령어를 사용했다. 해당 명령어는 shell 에서 MariaDB에 접속하기 위한 명령어이다.


1
mysql -u 계정명 -p
cs

그런데 위의 tomcat과 마찬가지로 "Access denied for user '계정명'@'localhost'(using password:YES)" 오류가 발생한다. 그래서 MariaDB의 root계정으로 접속하려는데, root 권한 또한 마찬가지 오류가 발생한다. 


이래저래 구글링을 해보니, 다음과 같은 명령어를 찾게 되었다.

1
sudo /usr/bin/mysqladmin -u root password [비밀번호]
cs

해당 명령어는 서버의 root 권한으로 서버 내에 설치된 MariaDB의 root 비밀번호를 재설정하는 것이다. 이 이후에, mysql -u root -p 명령어로 접속하였고, localhost 계정을 다시 생성해주려 하였다.


1
MariaDB[(none)]> CREATE USER '계정명'@'localhost' IDENTIFIED BY '비밀번호';
cs

했더니, 아래와 같은 오류메시지가 출력된다.


1
ERROR 1396(HY000) : Operation CREATE USER failed for '계정명'@'localhost';
cs

뭔가 해서 봤더니 직접 생성이 불가능한 것 같다. 혹시나 싶어, 계정 테이블을 직접 쿼리해서 상태를 좀 보고자 했다.


1
2
MariaDB[(none)] > use sql; --Database를 변경하는 명령어
MariaDB[(mysql)] > SELECT * FROM user WHERE user='계정명';
cs

해당 명령어로 조회해보니 우리 DB에서 사용중인 계정 - Host(권한이라고 이해중)별 비밀번호가 각각 다른것이었다. A 계정의 % Host(권한)과 A 계정의 localhost Host(권한)과 비밀번호가 각기 다른 상황. 이러니 서버나 tomcat에서 접속할 때에는 Access Denied For User 에러가 발생했을 것이고, 개발자 PC의 DB툴로는 접속이 잘 되었겠지. 이제 원인이 파악된 상황이다.


1
2
3
4
5
6
7
8
9
10
+-------------------------------------------------------------------------------+
| Host      | User        | Password              | Select_priv  | Insert_priv  |
+-------------------------------------------------------------------------------+
| localhost | mariadb.sys |                       | N            | N            |
| localhost | root        | *E1C459C5 ~~~~~~~~~   | Y            | Y            |
| localhost | mysql       | invalid               | Y            | Y            |
| localhost |             |                       | N            | N            |
| %         | test        | *0B26A ~~~~~~~~~~~~~~~| N            | N            | 
| localhost | test        | *COB5F ~~~~~~~~~~~~~~~| N            | N            | 
+-------------------------------------------------------------------------------+

cs

* test라는 계정의 % Host 비밀번호는 암호화되어 *0B26A로 시작하는데, localhost는 *COB5F로 시작한다. 이러니 외부에서 접속하는 % Host는 기존 비밀번호로 접속이 되고, 같은 서버 내에 있는 tomcat은 기존 비밀번호로 접속이 안되겠지.



2. 문제해결


일단 원인은 알았다. test 계정의 % 비밀번호와 test 계정의 localhost 비밀번호가 다르기에 생긴 문제라는 것. 일단 localhost 비밀번호가 왜 저렇게 변경되었는지는 작업자들에게 물어보면 될 것이고, 접속이 되도록 수정을 해야겠다. 이럴땐 뭐다? test 계정의 localhost Host(권한)비밀번호를 기존에 사용중인 비밀번호로 Update 쳐주면 될 것이다.


1
MariaDB[(mysql)] > UPDATE user SET password=('비밀번호') WHERE user='계정명';
cs

했더니, 다음과 같은 에러가 발생한다.


1
ERROR 1348(HY000) : Column 'Password' is not updatable.
cs

않이; 뭐냐 대체 이건. 또다시 검색해보니 MariaDB 10.4 버전 이상은 user테이블을 직접관리하지 않고, 파일이나 뭐 그런식으로 관리하기 때문에 기존의 user테이블 update 방식은 사용할 수 없다고 한다. 그래서 또다시 뒤져보니 명령어로 직접세팅하는 방법이 있었다.


1
MariaDB[(mysql)] > set password for '계정명'@'localhost=password('비밀번호');
cs

했더니, Query OK, 0 rows affected (0.014sec) 메시지가 출력되었다. 그리고 다시 user 테이블을 확인해보았고, 해당 계정의 % Host와 localhost Host의 비밀번호가 동일한 것을 확인할 수 있었다.


1
2
3
4
5
6
7
8
9
10
+-------------------------------------------------------------------------------+
| Host      | User        | Password              | Select_priv  | Insert_priv  |
+-------------------------------------------------------------------------------+
| localhost | mariadb.sys |                       | N            | N            |
| localhost | root        | *E1C459C5 ~~~~~~~~~   | Y            | Y            |
| localhost | mysql       | invalid               | Y            | Y            |
| localhost |             |                       | N            | N            |
| %         | test        | *0B26A ~~~~~~~~~~~~~~~| N            | N            | 
| localhost | test        | *0B26A ~~~~~~~~~~~~~~~| N            | N            | 
+-------------------------------------------------------------------------------+

cs

이후, tomcat을 재구동하였더니 정상동작 하였다.



3. 후기


MariaDB를 직접적으로 설정하고 사용할 일이 없어 여러모로 삽질을 많이하게 되었다. 문제해결하면서 알게된 점을 아래에 간략하게 작성해보았다.

계정은 Host(권한)별로 password가 지정된다는 점이다. 위에서도 보여지듯, test 계정임에도 불구하고 로컬(같은 서버내)에서 접속가능한 비밀번호와 외부에서 접속 가능한 비밀번호가 각기 설정되어있다.


또한, 명령어의 password('비밀번호')는 자동으로(?) 인코딩되어 설정된다는 점이었다. 혹시나 싶어 구글링한 결과 그대로 작성해보았는데, 알아서 인코딩되어 저장되는 것을 보니 어딘가에 설정이 박혀있는게 아닐까 싶다. 뭐 복잡시럽게 sha256으로 인코딩해서 직접 박아넣는게 아니라 다행이다 싶다.


위의 상황이 발생하게 된 계기는, 하나의 서버를 여러부서가 공동으로 개발장비로 사용하는 중이었고, MariaDB를 우리팀에서 설치, 사용을 하고 있었다. 그러다가 타 부서에서 MariaDB를 사용 할 일이 생기게 되었고, 우리가 설치한 MariaDB에 접속이 안되니 멋대로 서버의 root 권한으로 test계정의 localhost 비밀번호를 바꿔버려 생긴 문제였다. 거기에, 우리팀 막내가 당일 MariaDB 설정과 관련하여 파일 권한이라거나 기타 설정들을 바꾸면서 MariaDB Service를 재구동하는 등 여러 변경점이 생기다보니 우리팀 내에서만 원인 찾기가 힘든 상황.


나의 경우에는 계정-host별 비밀번호가 다르게 적용되는 줄 몰랐던터라, 애꿎은 DB보안프로그램과 서버접속 보안프로그램 탓을 했었다. tomcat 재구동 작업을 하기 바로 전날 야간에 설치가 되었던터라, 이 부분이 문제인줄 알았던 것. 여러 작업적인 이슈가 겹치다보니 생긴 당연한 인재였다. 개발서버였기에 망정이지, 실 운영이 진행중인 운영서버였다면 초대형사고였을지도 모른다. 이러한 이유로, 무슨 작업이든 작업이 있을 경우에는 연관부서에 모두 noti를 해줘야한다는 것이다.

블로그 이미지

김생선

세상의 모든것을 어장관리

댓글을 달아 주세요

개발하다보면, 특정 컬럼의 네이밍을 조회하는 경우가 있다. 가령 A 라는 컬럼을 어떤 테이블에서 더 쓰는지. 뭐 이런 경우. Oracle은 all_tab_column인지 all_object를 쓰면 되긴 하는데 mysql은 처음이다. 하지만 간단하다.


1
SELECT * FROM information_schema.columns
cs



이렇게 해주면 된다. 개꿀.

블로그 이미지

김생선

세상의 모든것을 어장관리

Tag mysql

댓글을 달아 주세요

DB 또는 시스템 작업을 하다보면 여러건에 대해 한번에 업데이트를 하는 경우가 많다. 뭐 그렇다치고 이번에는 MySQL 에서 서브쿼리를 이용하는 방법을 알아보자.


이번에 할 작업은 A_Table의 a_column의 데이터와 문자열을 합친 후, A_Table의 b_column에 업데이트를 해줄 일이 생겼다. 오라클과 다르게 한차례 더 가공을 해야 해서 손이 가는 편이지만 크게 어렵지는 않았다.


1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
UPDATE 
    'A_Table' AS AliasA 
SET 
    AliasA.b_column = 
    (
    SELECT
        AliasC.a_column_text
    FROM
        (
        SELECT
            AliasB.a_column 
            , CONCAT('TEST_' , AliasB.a_column ) AS a_column_text //MySQL에서 문자열 합치기는 concat을 이용함
        FROM
            'A_Table' AS AliasB 
        ) AS AliasC
    WHERE 
        AliasC.a_column = AliasA.a_column 
    )  
WHERE 
    AliasA.b_column IS NULL;
cs


오라클 같은 경우에는 SET 구문에서 서브쿼리를 바로 날리면 되지만, MySQL의 경우에는 SELECT 구문에 서브쿼리를 한 번 더 감싸줘야 한다.

이로써 A_Table의 b_column에 'TEST_'문자열이 합쳐진 a_column값이 update 되게 된다.

블로그 이미지

김생선

세상의 모든것을 어장관리

댓글을 달아 주세요

엑셀로 데이터를 밀어넣다 보면 데이터가 간혹 잘못들어가는 경우가 있다. 의도치 않은 tab 문자열이라거나 공백문자열이라거나.

한두건이면 그냥 update query를 실행하면 되지만 여러건일 경우에는 답이 없다.


1
2
3
UPDATE
    table_a
SET column_name = REPLACE( column_name , ' ''')
cs


table_a 라는 테이블에서 column_name 컬럼의 데이터가 공백 문자열이 있을 경우, set 구문에 replace로 해당 컬럼을 치환한 후 update 해주면 된다.

아주 간단하고 심플함.

블로그 이미지

김생선

세상의 모든것을 어장관리

댓글을 달아 주세요

[Oracle 11g Enterprise] / [Mybatis 3.2.7] 기준


varchar2 데이터타입은 최대 사이즈를 4,000byte 까지 줄 수 있다. 그런데 4,000byte 이상의 데이터를 저장할 일이 있다. base64로 인코딩된 이미지라거나, xml 파싱된 영수증내용이라거나 아주 긴 블로그의 글이라거나. 그럴 때 oracle 에서 사용하는 데이터타입은 clob 이다.


CLOB은 XML 형태로 데이터를 지정하게 되는데, DB 툴에서는 select * from table로 간단하게 데이터 확인이 가능하지만, mybatis를 사용하게 되면 일반적인 select 쿼리로는 활용이 불가능하다. 그럴 때 다음과 같이 mybatis의 select 구절에 resultmap을 설정하면 된다.


1
2
3
4
5
6
7
8
9
10
11
12
<!-- resultMap 지정 -->
<resultMap id="QID_CLOB" type="hashMap" >
    <result property="ETC" column="etc" jdbcType="CLOB" javaType="java.lang.String"/>
</resultMap>
 
<!-- 조회쿼리 -->
<select id="QID_SELECT_INFO" parameterType="hashMap" resultMap="QID_CLOB" >
SELECT
    etc
FROM
    table
</select>
cs


사용법은 심플하다.

select 쿼리의 resultMap 파라미터는 별도로 선언할 resultMap의 ID를 지정해준다.

별도로 선언할 resultMap에서는 id를 맞춘 후, clob으로 가져올 컬럼명(column)을 지정하고, jdbcType을 clob으로 지정, 이를 어떠한 column name으로 내보낼 것인지 지정(property)해주면 된다.



1
2
3
4
5
6
SELECT
    a.etc AS a_etc
    b.etc AS b_etc
FROM
    table_1 a
    LEFT OUTER JOIN table_2 b ON a.no = b.no
cs

그런데 여기서 한가지 궁금한 점이 생기게 된다. 

각기 다른 테이블을 조인 후 가져올 때, 각 테이블의 같은 이름 clob은 어떻게 가져올까 하는 점이다.

테스트를 잠시 해보았는데 생각보다 매우 간단했다.


처음에는 같은 컬럼명이니까 위의 resultMap을 그냥 가져다 쓰면 ETC 컬럼을 ETC Property로 출력해주겠거니, 했지만 전혀 그렇지 않았다.(왜인지 Java의 Service 처럼 동작할거라 생각;)

그래서 두번째 방법을 사용해보았다.



1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
<!-- resultMap 지정 -->
<resultMap id="QID_CLOB" type="hashMap" >
    <result property="A_ETC" column="a_etc" jdbcType="CLOB" javaType="java.lang.String"/>
    <result property="B_ETC" column="b_etc" jdbcType="CLOB" javaType="java.lang.String"/>
</resultMap>
 
<!-- 조회쿼리 -->
<select id="QID_SELECT_INFO" parameterType="hashMap" resultMap="QID_CLOB" >
SELECT
    a.etc AS a_etc
    b.etc AS b_etc
FROM
    table_1 a
    LEFT OUTER JOIN table_2 b ON a.no = b.no
</select>
cs


그랬더니 각기 다른 테이블에서 동일한 네이밍의 clob 컬럼을 정상적으로 가져올 수 있었다.

간단한 테스트 결과, resultMap의 동작구조는 SELECT가 우선적으로 실행된 결과를 가져온 뒤, 이를 resultMap에서 처리하는 구조로 여겨진다. 결과적으로 resultMap은 Alias로 잡힌 컬럼명을 인식한다는 말이다.

블로그 이미지

김생선

세상의 모든것을 어장관리

댓글을 달아 주세요

[Oracle 11g Enterprise] 기준


개발을 하다보면 산출물을 작성해야하고, 이 산출물에는 테이블 생성날짜 등도 필요한 경우가 있다. 몰아서 테이블들을 생성해두고 나중에 작성하려다보면 뭐 임의의 날짜로 작성해도 무방하나 혹여나 하는 1g의 불안감 때문에 확인하는 경우가 있다.


1
2
3
4
5
6
7
8
9
10
11
12
SELECT
    owner
    , object_name
    , object_type
    , created
    , timestamp
FROM 
    all_objects
WHERE
    owner = 'USER_NAME'
    AND object_name ='TABLE_NAME'
 
cs


뭐 대충 이정도. 검색을 하다보면 all_objects 테이블이 아니라 user_objects라느니 뭐라느니 하는데 아마 버전마다 다른게 아닌가 싶을 정도.

여튼, 위의 컬럼은 다음과 같은 구성을 가지고 있다.


 owner

개체 소유계정. sys 계정이라면 해당 DB 내 모든 계정을 조회 가능하다.

 object_name

(간단히 말해) 개체명

개체명이라고 표현하는 이유는 테이블만이 보이는 것이 아니기 때문임.

자세한 부분은 object_type 참조

object_type

개체 타입. table인지 index인지 sequence 인지를 표현함. 

created 

생성날짜 

timestamp 

최근 access 날짜.

생성한 이후에 데이터를 insert 혹은 delete 할 때 마다 해당 날짜가 갱신된다.


뭐 대충 이정도. owner 에는 SYS 혹은 EXFSYS, SYSTEM 등 수많은 시스템용 owner가 있기에, 가장 기본적인 필터를 owner로 지정해두고 select 하는 것이 정신건강에 이로울 듯.

블로그 이미지

김생선

세상의 모든것을 어장관리

댓글을 달아 주세요

[Oracle 11g Enterprise 기준]


테이블의 데이터를 다른 테이블로 그대로 옮겨야 할 때가 있다.

Excel Export / Inport를 통한 방법이나 기타 여러 방법들이 있긴 한데 아무래도 귀찮은 방법이기도 하고. 그래서 Select Insert Query를 주로 사용한다.


1
2
INSERT INTO A_TABLE a
SELECT * FROM B_TABLE b WHERE b.no = '1'
cs


가장 기본적으로 위와 같은 구성으로 시작할 수 있다. A_TABLE에 B_TABLE의 데이터 중, no가 1인 항목만을 넣는 쿼리이다.


만약, 특정 컬럼에 대해서만 select insert를 한다면? 다음과 같다.


1
2
3
4
5
6
7
8
INSERT INTO A_TABLE a
    ( no , title , content , insert_date , insert_user )
SELECT 
    no , title , content , insert_date , insert_user
FROM
    B_TABLE
WHERE
    no = '1'
cs


위와 별개로, 다른 user에 할당된 테이블에 넣기 위해서는? 

회사에서는 하나의 DB에 user 단위로 운용/개발 DB가 분리되어있다. 당연하게도 dba 권한으로 아래의 쿼리를 이용해주면 된다.


1
2
3
4
5
6
7
8
INSERT INTO user_1.A_TABLE a
    ( no , title , content , insert_date , insert_user )
SELECT 
    no , title , content , insert_date , insert_user
FROM
    user_2.B_TABLE
WHERE
    no = '1'
cs


user_1은 user_2에 접근할 수 없기에 dba 권한으로 둘 다 접속이 가능한 상태에서 select / insert를 수행해주면 된다.


블로그 이미지

김생선

세상의 모든것을 어장관리

댓글을 달아 주세요

지난글, 2013/06/26 - [어장 프로그래밍/어장 DBA] - [MSSQL] MAX 함수 NULL 일 때 치환 에서는 MSSQL을 기준으로 글을 작성하였다.

현재 개발중인 플젝의 DB는 informix로 되어있고, 위와 같은 방식으로 max null 값을 구하려 했더니 informix 에서는 isnull 함수가 사용불가능하더라.


검색 해 보니 NVL 이란 함수가 있었고, 아래와 같은 방식으로 사용하였다.



SELECT NVL(MAX(testColumn), '0')+1 tempName FROM testTableName


testColumn의 MAX 값을 구하고, 이 값이 null 일 경우에는 0으로 치환한 후, 그 값에 +1을 하여 tempName 이라는 임시 컬럼명으로 Select 한다는 내용이다.(내가 써놓고 뭔가 어정쩡한 설명이란 생각이 든다.)


아무튼, 테스트를 해 보니 testColumn 값이 null 이 아니더라도 정상적으로 쿼리가 작동하기 때문에 만족하고 사용중. 뭐 이렇게 하면 된다.


블로그 이미지

김생선

세상의 모든것을 어장관리

댓글을 달아 주세요

where 조건을 줄 때, form 에서 영문자를 받을 일이 흔하다.

그리고 이 영문자로 where 조건을 주게 되는데, like 검색을 할 때에는 대소문자를 구분한다.


가령 컬럼에 KimFish 라고 값이 입력이 되어있고, form 에 입력된 조건이 kimfish 일 경우엔 조회가 안된다는 말.

따라서 like 쿼리에 대소문자 구분 없이 값을 입력해주는 function을 지정해줘야 하는데, 이것이 바로 upper function 되시겠다.


SELECT 

*

FROM

   테이블명 

WHERE 

upper(조회할 컬럼명) LIKE '%'||upper('입력받은 조건')||'%'


뭐 이런식.


내가 알기로는 대부분의 디비툴에서 사용 가능한 것으로 알고 있다.


** 추가 **

검색해본 적용 가능한 DBA

Oracle , MySql , MsSql, Informix

블로그 이미지

김생선

세상의 모든것을 어장관리

댓글을 달아 주세요

java.lang.OutOfMemoryError: Java heap space


2013:08:02 14:19:00



프로그램에서 서버 쿼리를 실행중에 위와 같은 에러가 발생하였다면 자신의 톰캣 서버 메모리를 의심해보아야 한다.


그리고 문제 해결 방법은 아래와 같다.


1. 이클립스 서버탭


이클립스 서버탭의 해당 톰캣 서버를 더블클릭한다.


2013:08:02 14:22:00

(이미지 설명 : 이 글에서는 Started 된 서버를 더블클릭 한다)



2. Server Overview


2013:08:02 14:24:39

(이미지 설명 : 톰캣 서버를 더블클릭하면 보이는 서버 오버뷰)



여기에서 파랑색으로 삐뚤삐둘 밑줄 쳐진, Open launch configuration 링크를 클릭한다.



3. Arguments


2013:08:02 14:28:45

(이미지 설명 : 사실 톰캣 서버는 여기에서 선택 가능하다)


선택한 서버가 실행중이라면 저러한 붉은 표시로 Server already running 이란 메시지를 띄우게 된다. 

해당 톰캣 서버를 멈추고, 밑줄쳐진 Arguments로 이동하자.



4. 설정 추가


2013:08:02 14:31:18


붉은 밑줄 부분이 아마도 추가가 안되어있을텐데, 현 시스템 상황에 고려하여 메모리 항목을 추가해주면 된다.

필자는 아래와 같은 형식으로 추가를 하였다.


 -XX:MaxPermSize=128m -Xms128m -Xms512m




그리고 적용시키면 방금 전 에러는 빠이염!

여러분, 즐프하십셔~



블로그 이미지

김생선

세상의 모든것을 어장관리

댓글을 달아 주세요