데이터 일괄 삭제 작업과 관련해 transaction을 이용해 처리하는 stored procedure가 있었다.
로직을 좀 변경한 뒤에 테스트를 하려고 하는데, 다 지워버리면 다시 테스트하기가 어려워 transaction을 걸고 테스트 후 rollback할 생각으로 테스트를 했는데...

예상과는 다르게 trasaction이 rollback이 되지 않는 것이었다. --;;
원인은 drop table!!

fk가 걸린 테이블이 있어 지우고자 하는 원본 데이터를 임시테이블에 담아 지우는 로직이 있는데, 거기서 기존 임시테이블 남아있으면 안되서, drop table을 하고 있었는데 이 drop table문의 경우에는 transaction이 시작되면 기존 transaction을 commit하고 수행해버린 것이다.

그래서 메뉴얼을 찾아봤는데...
Note

DROP TABLE automatically commits the current active transaction, unless you use the TEMPORARY keyword.


이렇게 잘 나와있었다.

그런데 마지막 부분에... unless use the TEMPORARY keyword!!

테이블을 만들 땐 CREATE TEMPORARY로 잘 만들고는 drop할때는 drop table을 한것이다.

상황에따라 다르겠지만, 임시테이블의 경우에는 transaction처리가 문제없이 된다는 점을 잘 알아둬야겠다.


TEMPORARY table의 경우에는 drop문도 transaction 처리가 가능하다!

'programming > MySQL' 카테고리의 다른 글

mysql 5.x index 생성 구문과 ORDER BY A DESC, B ASC...  (0) 2010.02.03
mysql을 잘 설치하고 잘 띄우기  (0) 2009.06.01
Query Optimize를 제대로 못하는 mysql 길들이는 7가지 방법  (0) 2009.04.30
ORDER BY 구문을 이용할 때 아래와 같이 order 방식을 다르게 SELECT를 할 필요가 있는 경우가 있다.

SELECT *
FROM tablename
ORDER BY A DESC, B ASC, C ASC

위와 같이 쿼리를 날리게되면, index를 만들 때 A,B,C 컬럼에 composit index가 생성이 되어 있더라도, 정렬이 모두 DESC나 ASC로 되어 있다면 결국 index를 제대로 활용 못하고 filesort가 나게 된다.

도움말을 찾아보면 아래와 같이 INDEX 생성 시점에 columnname [ASC | DESC] 와 같이 정렬 순서를 컬럼별로 줄 수 있다고 써있다.



CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name 
[index_type] 
ON tbl_name (index_col_name,...) 
[index_option ...] 

index_col_name: 
col_name [(length)] [ASC | DESC] 

index_type: 
USING {BTREE | HASH | RTREE} 

index_option: 
KEY_BLOCK_SIZE value | index_type | WITH PARSER parser_name


그래서 실제로 적용해 봤는데, 그래도 계속 filesort가 발생해서 도움말을 좀 더 읽어봤더니... 역시나 --;;

An index_col_name specification can end with ASC or DESC. These keywords are allowed for future extensions for specifying ascending or descending index value storage. Currently, they are parsed but ignored; index values are always stored in ascending order.

앞으로 추가될 기능이라는 얘기가 있었다. parse는 잘 되지만 무시한다고 --;;

'programming > MySQL' 카테고리의 다른 글

drop table 문과 transaction  (0) 2011.03.02
mysql을 잘 설치하고 잘 띄우기  (0) 2009.06.01
Query Optimize를 제대로 못하는 mysql 길들이는 7가지 방법  (0) 2009.04.30
음.. 제목이 낚시성이긴 한데... 하도 가끔 설치를 하다보니 한번 설치하면 담에 설치할 때 까먹고 해서.. 정리할 겸 적어본다.

다른 건 다 상관없는데, mysql사이트의 install 방법을 보다보면, mysql설치시 다음과 같이 db를 초기화 하는 부분이 있다.
shell> scripts/mysql_install_db --user=mysql

이렇게 초기화를 하게되면, 내가 원하는 위치에 설치되지 않고 mysql이 정한 기본 경로를 따르게 된다.
또한 mysql 프로세스를 띄우는 방식 역시 메뉴얼에 나온데로 아래와 같이

bin/mysqld_safe  --user=mysql &

띄울 경우, 처음 설치한 사람은 그렇다쳐도 다른 사람이 해당 서버에서 process만 확인해서는 my.cnf가 어디에 있는지, 혹시 여러개의 my.cnf가 존재하는 경우 어떤 my.cnf를 사용해 서버가 시작되었는지 헷갈리는 문제가 있다.

File Name Purpose
/etc/my.cnf Global options
SYSCONFDIR/my.cnf Global options
$MYSQL_HOME/my.cnf Server-specific options
defaults-extra-file The file specified with --defaults-extra-file=path, if any
~/.my.cnf User-specific options

위 순서는 unix 기준이고, windows는 
File Name Purpose
WINDIR\my.ini, WINDIR\my.cnf Global options
C:\my.ini, C:\my.cnf Global options
INSTALLDIR\my.ini, INSTALLDIR\my.cnf Global options
defaults-extra-file The file specified with --defaults-extra-file=path, if any

그래서 아래와 같은 방법으로 설치를 하고 프로세스를 띄우는 것이 좋은것 같다.
일단 my.cnf를 먼저 준비(my.cnf파일 내부 옵션들에 대한 설명)를 하고, 해당 설정파일 내부에 설치를 원하는 위치 등의 정보를 먼저 수정한 후,초기화 script시 parameter로 해당 my.cnf의 위치를 전달해 주고
shell> scripts/mysql_install_db --defaults-file=/etc/my.cnf --user=mysql

mysql 프로세스를 띄울 때도, 아래와 같이 parameter로 my.cnf 파일의 위치를 전달해 주면,
bin/mysqld_safe --defaults-file=/etc/my.cnf --user=mysql &

설치자 외에도 해당 서버에서 process만 mysql을 grep해 보면, my.cnf 위치가 명시되어 있어 보다 쉽게 파악이 가능하다는 점이 있다.

보다 자세한 설치 방법은 이글을 참고하면 좋을 것 같다.

'programming > MySQL' 카테고리의 다른 글

mysql 5.x index 생성 구문과 ORDER BY A DESC, B ASC...  (0) 2010.02.03
Query Optimize를 제대로 못하는 mysql 길들이는 7가지 방법  (0) 2009.04.30
MySQL unique index생성 시 size 제한  (0) 2009.02.17
mysql index관련 검색을 하다 재미있는 내용의 글을 발견 정리하려고 올립니다.
mysql의 경우에도 버젼업이 많이 되고 있어, 최신 버젼의 optimizer에 대해서 아는바가 없지만, 현재 4.x대를 사용 중이라 optimizer의 문제를 너무 많이 느끼고 있어서 공감했던 글입니다.


위 글의 내용을 정리하면 대략 다음과 같습니다.

CREATE TABLE t (
  id INT UNSIGNED AUTO_INCREMENT,
  type INT UNSIGNED,
  level TINYINT unsigned,
  ...
  PRIMARY KEY(id),
  KEY `type` (type)
) ENGINE=InnoDB;

위와 같은 테이블이 있고, 해당 테이블에 대해 아래와 같은 쿼리를 사용하려고 합니다.

SELECT id FROM data
WHERE type=12345 AND level > 3
ORDER BY id

정확한 이해를 위해 보다 자세한 내용을 소개하자면

  • t라는 테이블에는 약 10,000,000개의 row가 있다.
  • 'type'이라는 index의 선택도는 대략 하나의 value에 대해 평균 100row정도 (예를 들어 type 'x'를 검색하면 결과가 평균 100개정도 나온다는 의미)
  • 쿼리 수행 시간이 오래 걸린다.
  • EXPLAIN 결과에서 보면 key는 primary key를 이용한다고 하고 rows는 10,000,000, extra 정보에는 using where라고 나온다.
  • 'type' index를 이용한 EXPLAIN 결과에서는 rows는 110rows, extra정보에는 "using where;using filesort"라고 나온다.

실제 실행 시간은 mysql이 제시한 방법 보다는 'type' index를 이용한 쿼리가 나은 결과를 보이는데, 그럼 쿼리를 어떻게 고치면 'type' index를 사용할 수 있게 할 수 있는지에 대해 7가지 방법에 대해 소개를 하고 있습니다.

몰랐던 방법들도 있네요.
내용이 어렵진 않으니 원문을 참고하세요~

'programming > MySQL' 카테고리의 다른 글

mysql을 잘 설치하고 잘 띄우기  (0) 2009.06.01
MySQL unique index생성 시 size 제한  (0) 2009.02.17
mysql의 'ON DUPLICATE KEY UPDATE' 구문  (4) 2007.08.27
mysql 5.1에서 innodb인 테이블에 데이터 타입은 varchar에 collation은 latin1으로 size가 1000 정도인 컬럼에 unique index를 걸려고 했는데 다음과 같은 에러가 났다.

Specified key was too long; max key length is 767 bytes

그냥 index의 경우도 걸어보면 다 걸린것 같지만 내부적으로는 부분 index만 걸린다는 사실.
unique index의 경우에는 부분적으로 unique를 걸 수 없으므로 아예 에러가 난다.

레퍼런스를 찾아보면... db engine별로 지원하는 unique index의 크기가 틀렸다.
또 collation이 latin1인지 utf8인지에 따라 다르다 latin1인 경우 1글자가 1byte이지만, utf8인 경우 한글자가 3byte이므로 1000을 3으로 나누면 size 333까지만 지원하게 된다.
다음과 같은 내용을 mysql online document에서 확인할 수 있다.

Prefix lengths are storage engine-dependent (for example, a prefix can be up to 1000 bytes long for MyISAM tables, 767 bytes for InnoDB tables). Note that prefix limits are measured in bytes, whereas the prefix length inCREATE INDEX statements is interpreted as number of characters for non-binary data types (CHAR, VARCHAR,TEXT). Take this into account when specifying a prefix length for a column that uses a multi-byte character set. For example, utf8 columns require up to three index bytes per character.

참고로 InnoDB의 index page size는 16Kb이다. (컬럼의 길이가 너무 길면 index 자체도 별 효과가 없을 수 있으니 제한을 두는건 맞는 것 같은데. 767이라는 제한은 어디서 온걸까 싶어서.. 대충 256*3하면 768인데...)

p.s. 구글에서 'mysql column 767' 이라고 검색하면 제일 상단에 MySQL Bugs...라고 나오길레 버그인가 했더니 초기 5.0버젼에서 768-1024 length인 primary key가 만들어진다는 버그에 대한 리포팅이었다.

'programming > MySQL' 카테고리의 다른 글

Query Optimize를 제대로 못하는 mysql 길들이는 7가지 방법  (0) 2009.04.30
mysql의 'ON DUPLICATE KEY UPDATE' 구문  (4) 2007.08.27
mysql의 rlike (REGEXP)  (0) 2007.02.01
mysql을 사용하면서 처음 알게된 구문인데... INSERT 구문에 같이 쓰이게 된다.

명령어만 봐도 직관적으로 이해할 수 있는데...

INSERT할 때 KEY가 duplicate가 되었을 때, UPDATE하는 방법에 대해 정의할 수 있다.

예를 들어,
INSERT INTO table (a,b,c) VALUES (1,2,3)

ON DUPLICATE KEY UPDATE c=c+1;
컬럼 a가 key라고 가정할 경우, 이미 컬럼 a의 값이 1인 row가 있을 경우 해당 row의 c컬럼의 값을 1 증가시키라는 의미이다.

프로그래머 입장에서는 귀찮게 select한 후 없으면 insert 있으면 update하는 것을 한방에 해결해 주긴 하지만, ANSI SQL이 아니라 그점이 좀 맘에 걸린다.

오늘 하고자 하는 얘기는... 쉽게 이해하는것 처럼 단순히 key에 대해서만 동작하지는 않는 다는 것이다.

메뉴얼을 잘 읽어본 분은 아시겠지만...

아래와 같은 경우에 동작하게 된다.
1. key
2. unique index

1번은 알고 있었는데, 2번을 모르고 있어서 만들어진 코드가 이해가 안가 살펴보다가 결국 메뉴얼을 정독하고 알게되었다. ^^

저 처럼 실수하는 분 없기를...

'programming > MySQL' 카테고리의 다른 글

MySQL unique index생성 시 size 제한  (0) 2009.02.17
mysql의 rlike (REGEXP)  (0) 2007.02.01
database like 검색에서 '%x'와 ('x%' 또는 '%x%')의 차이  (0) 2007.01.09

rlike (REGEXP 의 synonyms임)
: like와 유사한데 뒤에 오는 표기법이 정규식임

관련 도움말 : http://dev.mysql.com/doc/refman/5.0/en/pattern-matching.html

<example>
SELECT *
FROM CLIP
WHERE daumname rlike '^고[[:alnum:]]?미' LIMIT 10;

정규식 사용을 해 보신분은 아시겠지만... 표기법이 희한하죠?
다른 언어에서 사용하는 방법과 좀 다릅니다. 보다 자세한 표기법은 아래에서 참고하시면 될 듯
http://dev.mysql.com/doc/refman/5.0/en/regexp.html

주의!
프로그래밍하긴 편할지 몰라도 성능은 좋지 못하다는 것.
mysql optimizer는 정규식 기반의 쿼리를 최적화하지 못합니다.

'programming > MySQL' 카테고리의 다른 글

mysql의 'ON DUPLICATE KEY UPDATE' 구문  (4) 2007.08.27
database like 검색에서 '%x'와 ('x%' 또는 '%x%')의 차이  (0) 2007.01.09
MySQL utf-8 글자 깨짐 해결  (0) 2006.09.26
아는 사람은 다 알겠지만...

특정 컬럼에 index를 걸어 둔 경우,
'x%'는 index를 이용할 수 있지만, '%x' 또는 '%x%'는 index를 이용할 수 없다.

조금만 생각해 보면 알겠지만, index는 문자의 왼쪽을 기준으로 인덱스를 만들어 두었기 때문이다.
그런데 만약 반드시 '%x'를 구현해야 한다면 어떻게 해야 할까??
요즘 읽는 책에 간단한 방법이 나왔길래 적어본다.

컬럼 하나를 추가해서 문자열을 뒤집어 저정한 후 index를 걸어두는 것이다.

쩝.. 알고보면 간단한데, 이런 생각은 해 본적이 없다.

사람이름을 예로들어...

col1
이하늘
김하늘
이기택
...

에서 '하늘'이라는 이름을 언능 찾고 싶으면, 아래와 같이 이름을 뒤집어 놓은 컬럼을 추가하고

col1        col2
이하늘       늘하기
김하늘       늘하김
이기택       택기이
...


col2에도 index를 걸고,

SELECT * FROM user WHERE col2 like '늘하%'

라고하면 간단히 해결~


사실 이 얘기를 쓰려고 한 것은 아니었는데...

mysql쪽 책을 보다가 mysql에는 rlike라는 구문이 있음을 알게 됐다.
이건 다시 정리해서~~

'programming > MySQL' 카테고리의 다른 글

mysql의 rlike (REGEXP)  (0) 2007.02.01
MySQL utf-8 글자 깨짐 해결  (0) 2006.09.26
mysql에서 stored procedure 만들면서 주의 할 것  (2) 2006.09.22
mysql을 사이트에 나온데로만 설치하고 아무 변경 없이 실행 후...

database와 table을 utf8로 만들고, stored procedure를 만들어 java에서 call하는데 한글이 깨지는 현상이 있었다.

db url에도 characterEncoding도 넣어보고, property에 charset을 utf-8로도 해 보고 하다가...
이것 저것 해 봐도 안됐었는데...

어디선가 보고 /etc밑에 my.cnf 파일이 없어서 mysql안에 있는 것 복사해서 넣고, 아래 내용을 추가해서 mysql을 재시작 하고나서 해결

[client]
character-set=utf8

[mysqld]
init_connect=SET collation_connection = utf8_general_ci
init_connect=SET NAMES utf8
default-character-set=utf8
character-set-server=utf8
collation-server=utf8_general_ci

이 넘의 한글 문제... 머리아프다.. 어디 제대로 정리 된 곳 없을까?

'programming > MySQL' 카테고리의 다른 글

database like 검색에서 '%x'와 ('x%' 또는 '%x%')의 차이  (0) 2007.01.09
mysql에서 stored procedure 만들면서 주의 할 것  (2) 2006.09.22
java, mysql stored procedure 호출  (0) 2006.09.22
주로 MS-SQL에서 작업을 하다, mysql에서 처음으로 stored procedure(이하 sp)를 만들면서, 5분이면 될 것을 수십분은 걸린 것 같다 --;;
에러가 정확한 위치를 알려주질 않는다.

아래의 정리한 내용은 MS-SQL에 익숙하고 mysql을 사용해 보지 않은 분이 보면 무자게 도움이 될 것 같다.

- IF문을 쓸 때 END IF문이 있어야 하며, END IF문 뒤에는 ;를 붙여야 한다.
   ex> IF (test IS NULL) THEN
            SELSET 'ok';
         END IF;

- auto increment값을 얻어올 때는 LAST_INSERT_ID()을 이용한다.
   ex> SET serverId = LAST_INSERT_ID();

- CREATE PROCEDURE문 전에 delimeter문을 이용해 delimeter를 ';'가 아닌 다른 걸로 변경하고 뒤에 다시 변경할 것
   ex> delimiter //                  # delimeter를 //로 변경
         CREATE PROCEDURE xxx
                  .....
                  .....
         END;
         //                                # 실제 한 command가 종료됨을 의미

         delimeter ;                     # 다시 ;로 변경



일단 여기까지

'programming > MySQL' 카테고리의 다른 글

MySQL utf-8 글자 깨짐 해결  (0) 2006.09.26
java, mysql stored procedure 호출  (0) 2006.09.22
DBDesigner 4와 MySQL 4.1의 연결  (2) 2006.01.25
mysql에 stored procedure 만들기
delimiter //        

CREATE PROCEDURE getServerId(ip VARCHAR(15), OUT serverId tinyint)
BEGIN
SELECT id INTO serverId FROM tServer WHERE serverIp = ip;
END;
//

delimiter ;

- mysql에서는 ;가 command의 종료를 의미하는데 stored procedure의 경우 여러 query의 조합이라 중간에 ;가 들어갈 수 있는데, 그러면 문법 오류로 간주되므로 delimiter를 임시로 다른 걸로 바꾼 후 sp를 만들고 다시 예전 delimiter로 변경하는 것임.


java에서 call하는 코드
try {
   String CLASS_NAME = "com.mysql.jdbc.Driver";
   String DB_URL = "jdbc:mysql://localhost:3306/test";
   String USR = "id";
   String PASSWD = "pw";
  
   Class.forName(CLASS_NAME);
   Connection con = DriverManager.getConnection(DB_URL, USR, PASSWD);
             
   CallableStatement stat = conn.prepareCall("call getServerId(?, ?)");
   stat.setString(1, "127.0.0.1");
   stat.registerOutParameter(2, Types.TINYINT);
   stat.execute();
          
   System.out.println(stat.getByte(2));
} catch (ClassNotFoundException e) {
   // TODO Auto-generated catch block
   e.printStackTrace();
} catch (SQLException e) {
   // TODO Auto-generated catch block
   e.printStackTrace();
}

'programming > MySQL' 카테고리의 다른 글

mysql에서 stored procedure 만들면서 주의 할 것  (2) 2006.09.22
DBDesigner 4와 MySQL 4.1의 연결  (2) 2006.01.25
DECLARE @var1 int  (5) 2006.01.20

MySQL에는 MS SQL 처럼 DB 설계에 사용할 수 있는 기본 도구가 제공되지 않는다.

최근에 추가된 MySQL Administator, MySQL Query Broswer에도 그런 기능은 없다.
opensource 진영에서 제공하는 툴 중에 DBDesigner 4라는 넘을 많이 사용한다고 하던데,
설치해 놓고 이것 저것 해 보는 데...

MySQL에 연결이 안되는 문제가 있었다.


그래서 http://www.fabforce.net/dbdesigner4/faq.php
여기서 찾아보니, mysql 4와의 연결에 문제가 있으면 odbc를 사용하라는 말을 발견하고 문제를 해결했음.

 

'programming > MySQL' 카테고리의 다른 글

java, mysql stored procedure 호출  (0) 2006.09.22
DECLARE @var1 int  (5) 2006.01.20
MySQL 5.0 Standards Compliance  (5) 2006.01.16

... 쩝... MySQL을 계속 다뤄왔던 사람들이라면 다 알고 있는 내용이겠지만...

 

MS-SQL을 계속 사용하다가 이번에 MySQL로 간단하게 뭘 좀 해 보려고 5.0을 설치해서 이것 저것 해 보고 있다.

 

MS-SQL을 사용하는 사람이라면 당연히 자주 사용하게 되는 저장 프로시저가 MySQL의 경우 5.0에 새로 추가가 됐다.

책을 하나 사서 보려 해도 5.0책은 없어서 걍 MySQL 도움말 보며 이것 저것 하다가...

 

제목에 있는 저 문장 때문에 20분 정도는 해맸다..

MS-SQL에서 저장 프로시저를 만들때, 일반적으로 쿼리 분석기에서 저장 프로시저의 내용들을 만들어 실행해 보고 문제가 없으면 CREATE PROCEDURE... 를 추가해서 저장 프로시저를 만든다.

 

MySQL에서도 비슷하게 하려고.. 다 만들었는데 온통 문법에러 투성 --;;

도움말에는 예제도 많지 않고...

 

회사에도 5.0은 쓰지 않아 특별히 물어볼 사람도 없고...

하나씩 찾다보니 맨 위의

이 문장에서 오류가 났다..

 

DECLARE @var1 int;

 

도대체 이해가 되질 않았다.

메뉴얼에도..

 

DECLARE var_name[,...] type [DEFAULT value]

이렇게 나와 있는데.. 도대체 틀린게 뭘까??

 

한참 해매다가 메뉴얼을 제대로 읽어보고 허탈했다..

 

DECLARE may be used only inside a BEGIN ... END compound statement and must be at its start, before any other statements.

 

반드시 BEGIN과 END 사이에 기술해야 한다 --;;

쩝... 저 한 줄만 미리 읽어봤더라도...

 

이번 일로 여러 가지를 느꼈다.

 

- 고정 관념을 깨자

- 메뉴얼이 아무리 영어라도 제대로 읽자

 

^^''

 

매일 매일 안하던거만 하니까 좀 막연하기도 하지만 재미있네요..

 

'programming > MySQL' 카테고리의 다른 글

java, mysql stored procedure 호출  (0) 2006.09.22
DBDesigner 4와 MySQL 4.1의 연결  (2) 2006.01.25
MySQL 5.0 Standards Compliance  (5) 2006.01.16

MySQL 5.0 Manual 중에

- What's New in MySQL 5.0

- MySQL 5.0 Standards Compliance

 

이 두 부분을 정리한 내용이다.

 

MySQL을 한 번도 접해보지 않아서 공부하는 샘치고 보면서 정리한 내용


>더보기


'programming > MySQL' 카테고리의 다른 글

java, mysql stored procedure 호출  (0) 2006.09.22
DBDesigner 4와 MySQL 4.1의 연결  (2) 2006.01.25
DECLARE @var1 int  (5) 2006.01.20

+ Recent posts

티스토리툴바