mysql> load data local infile "/home/test/zipcode.txt" into table zipcode;
ERROR 1148 (42000): The used command is not allowed with this MySQL version

위와 같이 오류가 발생할 경우 /etc/my.cnf 파일에 아래와 같이 설정을 추가해 주면된다.

[mysql]
local-infile=1


또는 mysql 접속시 아래와 같이 --local-infile=1 로 옵션을 주고 접속하면 된다.
mysql -uroot -p --local-infile=1 database


load data infile "roadzipcode.txt" into table roadzipcode fields terminated by '|';

ERROR 13: Can't get stat of


위와 같이 ERROR 13 에러가 발생한다면 load data local infile 으로 사용해야 한다.
2012/11/23 11:06 2012/11/23 11:06
정리 잘되어 있는 사이트
http://onlybible.tistory.com/2041
2012/08/03 13:42 2012/08/03 13:42

mysql 5.x 의 기본 모드는 Strict Mode 이며, sql_mode가 별도로 설정되어 있지 않다.

mysql> show variables like '%mode%';
+-----------------+--------+
| Variable_name   | Value  |
+-----------------+--------+
| slave_exec_mode | STRICT |
| sql_mode        |        |
+-----------------+--------+
2 rows in set (0.00 sec)

아래와 같이 test_notnull2 테이블에 bbs_id, bbs_name 컬럼에 not null로 설정하고,
bbs_name 컬럼에만 값을 삽입시 에러가 발생하지 않는다.

에러가 발생하지 않는것은 bbs_id 의 값을 입력하지 않으면,
NULL 값이 삽입되는 것이 아니라 ''값이 입력되기 때문에 쿼리가 정상 실행된다.

mysql> create table test_notnull2(bbs_id varchar(10) not null, bbs_name varchar(10) not null);
Query OK, 0 rows affected (0.00 sec)

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

에러를 발생시키고 싶다면, my.cnf 파일의 mysqld 섹션에 아래와 같이 설정해 주면 된다.
STRICT_TRANS_TABLES 이 설정에 의해 에러를 뿌려줄 것이다.

sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

설정을 변경하고 나서 똑같이 쿼리 사용시 아래와 같이 에러가 발생한 것을 확인할 수 있다.
STRICT_TRANS_TABLES 이 설정은 컬럼에 값을 넣을때 컬럼의 길이보다 클 경우 자동으로 잘라서 넣어주고
경고를 뿌려줬는데 , 5.x 버전부터는 에러를 발생시킨다.

mysql> insert into test_notnull2(bbs_name) values('test');
ERROR 1364 (HY000): Field 'bbs_id' doesn't have a default value

2012/08/02 10:25 2012/08/02 10:25

mysql 외부 IP 설정 시 CREATE VIEW, SHOW VIEW 권한이 부여됨.
mysql> show grants for [email protected];
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Grants for [email protected]                                                                                                                                                          |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'testid'@'175.118.124.221' IDENTIFIED BY PASSWORD '*4E0AE8EAAE183E4454B803A602F1EF1E8F5DE9CA'                                                                        |
| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, CREATE VIEW, SHOW VIEW ON `testid`.* TO 'testid'@'175.118.124.221' |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

localhost 및 다른 외부 IP설정에서 생성된 view 테이블은 외부 IP(175.118.124.221)에서는 접근 및 삭제는 가능하오나 alter view 문을 사용할 수 없음.
alter view 사용시 Access denied; you need the SUPER privilege for this operation 에러 발생.

1. localhost 에서 view 생성
mysql> create view view_table_name as select * from aa;
Query OK, 0 rows affected (0.00 sec)

2. view 테이블 확인
mysql> show create table view_table_name;
+-----------------+-----------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
| View            | Create View                                                                                                                                  | character_set_client | collation_connection |
+-----------------+-----------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
| view_table_name | CREATE ALGORITHM=UNDEFINED DEFINER=`testid`@`localhost` SQL SECURITY DEFINER VIEW `view_table_name` AS select `aa`.`t1` AS `t1` from `aa` | binary              | binary              |
+-----------------+-----------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
1 row in set (0.00 sec)

3. 175.118.124.221 에서 view table 접근 확인

mysql> check table view_table_name;
+----------------------------+-------+----------+----------+
| Table                      | Op    | Msg_type | Msg_text |
+----------------------------+-------+----------+----------+
| testid.view_table_name | check | status  | OK      |
+----------------------------+-------+----------+----------+
1 row in set (0.00 sec)

4. 175.118.124.221 에서 view 테이블 수정 시 에러 발생.
mysql> ALTER ALGORITHM=UNDEFINED DEFINER=`testid`@`localhost` SQL SECURITY DEFINER VIEW `view_table_name` AS select `aa`.`t1` AS `t1` from `aa`;
ERROR 1227 (42000): Access denied; you need the SUPER privilege for this operation

5. view 테이블 수정은 definer 로 지정된 곳에서 작업가능. 또는 view 테이블 drop 후 재생성

6. localhost 에서 alter view 가능
mysql> ALTER ALGORITHM=UNDEFINED DEFINER=`testid`@`localhost` SQL SECURITY DEFINER VIEW `view_table_name` AS select `aa`.`t1` AS `t1` from `aa`;
Query OK, 0 rows affected (0.01 sec) 
2012/01/15 08:27 2012/01/15 08:27

1. Table(MyISAM) 손상이 발생하는 원인

 MyISAM Table은 SQL문장이 리턴 되기전에 모두 기록되기 때문에 믿을만 하다고 mysql.com에서는 밝히고 있으며 다음과 같은 이유로 문제가 발생 할 수 있다고 한다.

1) 기록(write)되는 중간에 mysqld이 죽었을 때.

2) 갑작스럽게(의도하지 않은) 컴퓨터가 shutdown 되었을 때.

3) 하드웨어 오류

4) 외부프로그램에 의해 Table을 변경하는 동시에 서버에 의해 변경작업이 이뤄질 때.

5) MySQL이나 MyISAM코드의 버그.


전형적인 증세는 다음과 같다.

1) Table로 부터 데이터를 Select할때 아래와 같은 error가 발생.
    Incorrect key file for table: '...'. Try to repair it

2) rows를 찾을 수 없거나 불완전한 결과를 반화.


참조사이트 : http://comnic.tistory.com/45
2011/11/22 11:08 2011/11/22 11:08

아주 잘 설명되어 있다 ㅎ

http://dev.paran.com/2011/06/17/why-we-need-to-use-innodb-on-mysql-vs-myisam-comparison/
2011/11/08 11:39 2011/11/08 11:39
mysql trigger 권한은 5.1.6 이전 부터는 슈퍼권한이 있어야 사용이 가능했지만,
5.1.6 이후 부터는 trigger 권한이 추가되었다.

하지만 5.1.45 에서도 trigger 권한은 있었다... 미스테리 ㅎ

1. 설정 방법
grant trigger on ID.* to [email protected];
set global log_bin_trust_function_creators = ON

2. 확인 방법

mysql> show grants for [email protected];
+-------------------------------------------------------------------------------                                            ---------------------------------+
| Grants for [email protected]                                                                                                                               |
+-------------------------------------------------------------------------------                                            ---------------------------------+
| GRANT USAGE ON *.* TO 'hosting'@'localhost' IDENTIFIED BY PASSWORD '*4E0AE8EAA                                            E183E4454B803A602F1EF1E8F5DE9CA' |
| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, ALTER, TRIGGER ON `hosting                                            `.* TO 'hosting'@'localhost'     |
+-------------------------------------------------------------------------------                                            ---------------------------------+
2 rows in set (0.00 sec)

2011/09/10 09:02 2011/09/10 09:02
TAG ,

putty charset : utf8
export LANG=en_US

mysql> \s;
--------------
mysql  Ver 14.14 Distrib 5.1.45, for unknown-linux-gnu (x86_64) using readline 5.1


위와 같이 using readline 이라고 나와야 한글이 제대로 보인다.
아래와 같이 using EditLine wrapper 로 나오면 한글 제대로 안나옴.
아래와 같을때는 --with-readline 옵션을 추가하여 컴파일을 다시 해야 함.

mysql> status;
--------------
mysql  Ver 14.12 Distrib 5.0.51a, for redhat-linux-gnu (x86_64) using  EditLine wrapper


status 에서 나오는 캐릭터셋과는 무관하게 readline에 의한 차이임,
mysql의 readline 을 쓰느냐 os의 readline 을 쓰는냐의 차이.
요것때문에 고생함..흐미...

Server characterset:    utf8
Db     characterset:    utf8
Client characterset:    binary
Conn.  characterset:    binary

2011/08/11 19:11 2011/08/11 19:11

copying to tmp table

MYSQL 2011/07/23 14:45
tmpfs는 RAM/SWAP에 있기 때문에 disk i/o 보다는 좋다고 한다.

mkdir /tmpfs
mount tmpfs /tmpfs -t tmpfs -o size=512m

my.cnf
tmpdir=/tmpfs

버전에 따라 오류가 생길수 있다고 한다.
tmpfs로 설정해주어도 퍼포먼스가 더 좋은지는 모르겠다.. 쩝..
2011/07/23 14:45 2011/07/23 14:45
출처 : http://www.leopit.com/Leophp/board/lecture_board/view.php?id=46&board_mode=mysql

1.반드시 컴파일 하라! 10-30% 속도 향상 !

  소스를 가지고 컴파일 하세요. MySQL 메뉴얼에 따르면 10-30% 속도가 빠르다고 합니다.
  RPM 이나 바이너리 설치를 하지 마세요 !


1-2.최신 버전을 사용하라

  최신 버전이 좋은 점은 자동 튜닝 하는 것 입니다.
  버그를 수정 하구요. 되도록 이면 최신 버전을 사용하세요 !!
  현재 3.23.49 입니다.


2. HEAP 테이블이 가장 빠르다!

  일반적으로 가장 많이 쓰이는 테이블 타입은 MyISAM 타입 입니다.
  MyISAM 타입은 무자게 빠르며, 대용량에도 강합니다. 그러나 트랜잭션은 지원되지 않습니다.
  이노디비(InnoDB) 는 트랜잭션이 지원 됩니다. 쇼핑몰에서는 반드시 사용해야 합니다 ^^

  HEAP 테이블 타입은 가장 빠르며, 단점은 메모리에 있기 때문에, MySQL에 중지 될 경우 모두 날아 갑니다.
  검색을 하고 재검색을 다시 하는 경우, 임시 검색 테이블을 만들어 놓는 것도 좋은 방법입니다.

  HEAP 테이블 메뉴얼 !
   http://www.mysql.com/doc/H/E/HEAP.html

  HEAP 테이블 만들기 !
  mysql>CREATE TABLE email_addresses TYPE=HEAP (
     ->email char(55) NOT NULL,
     ->name char(30) NOT NULL,
     ->PRIMARY KEY(email) );


3.mysql 서버 top 보기

  mysql 서버의 메모리 상황을 보여 주는 프로그램 입니다.
  리눅스나 유닉스의 top 기능을 mysql 에서 가능하게 한것 입니다.

  top 정보는 튜닝의 기본 이기 땜시 자주 자주 보아야 합니다. ^^
   http://public.yahoo.com/~jzawodn/mytop/

  PHP 소스 자료실에 파일 다운 로드 하시면 됩니다.


4.mysql_connect Vs mysql_pconnect

  서버 메모리가 최소 2G 이상일 경우 mysql_pconnect 를 추천 함다 !
  연결을 계속 하지 않기 땜시 빠릅니다. !

  그러나 메모리가 2G 이하 일 경우는 mysql_connect 사용하세요 !


5.int,smallint,tinyint 데이터형 ! 

  int 는 굉장히 큰수 입니다. 4바이트를 차지 하구요.
  tinyint 는 몇백 까지만 됩니다. 1바이트 구요.

  쓸데 없이 int 를 사용하지 마세용 !!
  4바이트와 1바이트는 4배 차이 입니다.조그만것 1개 1개가 모여 서버 부하를 일으 킵니다.!!

  데이터 량이 얼마만큼 들어가는지 체크 하고 데이터형을 선택 하세요 ^^
  만약 쓸데없는 데이터 형이다 싶으면 alter table 로 데이터 형을 바꾸세요 !


6.인덱스의 사용

  인덱스는 반드시 필요한 곳에만 넣으세요 !
  인덱스를 줄 경우 하드 용량을 더 차지 하기 때문에 속도를 떨어 뜨릴 수 있습니다.

  모든 칼럼에 인덱스를 주는 것은 절대 추천 하지 않습니다.
  1개의 테이블에 주키외에 2-3 개 이상의 인덱스는 주지 마세요!

  주키는 당근 인덱스 입니다. ^^

  CREATE TABLE albums (
     id     INTEGER    NOT NULL AUTO_INCREMENT PRIMARY KEY,
     title   VARCHAR(80)NOT NULL,
   
     INDEX title_idx (title)
  );

  ☞Alter Table 로 인덱스 추가
  ALTER TABLE albums ADD INDEX title_idx (title)  

  결합 인덱스의 경우 너무 많은 인덱스를 사용할 경우 CPU 오버헤드나 하드 오버헤드를 불러 일으 킵니다.
  적당히 사용하세요 ^^

   http://www.mysql.com/doc/I/n/Indexes.html
   http://www.mysql.com/doc/M/u/Multiple-column_indexes.html
   http://www.mysql.com/doc/O/R/ORDER_BY_optimisation.html


6-1. 바보 같은 인덱스의 사용 ? 

  인덱스는 %$search% 가 먹지 않습니다.
  그런디 게시판 제목(Subject) 에 인덱스 걸어 놓고 , 검색을 %$search% 이렇게 하면 될까요?
  인덱스 거나 안거나 똑같습니다. !!

  $search% 이렇게 사용하세요. 그런디.. $search% 사용하면 제목 처음 단어 밖에 검색이 안됩니다.
  그렇다면 ? 다른 검색 방법은 ?


7.UDF의 사용

  MySQL은 스토어드 프로시져 같은 개념이 존재 하지 않습니다.
  그대신 C 언어로 만든 함수를 사용할 수 있습니다.

  조금더 빠른 쿼리를 원한다면 UDF 를 사용해보세요 !

  UDF 함수 보기
   http://empyrean.lib.ndsu.nodak.edu/~nem/mysql/udf/

   http://www.mysql.com/doc/A/d/Adding_functions.html

  스토어드 프로시져가 먼뎅?
  스토어드 프로시져는 쉽게 말해 MS-SQL 함수 입니다.
  오라클에도 아마 있을검당..^^

  그러니까 게시판에서 내용을 넘길때나 불러 올때
  mysql 쿼리가 3-4 번 정도 이루어 집니다. 또는 ms- sql 쿼리가 이루어지죵..
  3-4 번 정도 쿼리가 되면..그만큼 디비 접속이 잦아 지기 때문에..
  속도가 느려 집니다.

  많게는 10번 정도의 insert into 와 update 가 이루어집니다.

  그래서 ms - sql 자체 내에 인서트 함수 나 목록 보기 함수를 만들어 놓습니다.
  글구 1번의 ms-sql 접속만 해서 인서트 함수를 불러서 처리하는 것입니다.

  그렇기 때문에 2-3 번의 쿼리가 절약 되서 빠르다는 것이죵..ㅋㅋㅋ
  또는 10번의 쿼리 할것을 MS-SQL 스토어드 프로시져를 1번만 호출 함으로 해서 디비 접속이 절약이 되죵..ㅌㅌ


  UDF 를 꼭 사용해야 하는가?

  안해도 됩니다.만... 사용하면 좋은점 많습니다. 새로운 함수를 추가 할 수 있으므로 ^^
  MS-SQL의 스토어드 프로시져 기능 비스므리 하게 사용할 수 도 있구요...

  UDF 나 MS-SQL 스토어드 프로시져의 사용법을 익히기 보다는 캐슁을 연구하세용 ^^
  동적인 PHP 를 정적인 HTML 로 만드는 방법을요...
  또는 UDF 에서 MS-SQL 스토어드 프로시져 처럼 사용이 가능 합니다. 그 부분을 연구하세요.

  www.zdnet.co.kr 이나 www.zdnet.com 가시면 기사 파일이 1000,29920,2892.html 파일 이란것을 보게 됩니다.
  어키 구현된것일까요? zdnet 게네 들은 강좌를 원래 부터 HTML 로 만들어서 올리는 것일까용??
  HTML 로 만드는 부분도 많이 생각 해야 합니다. 강좌가 1만개 라면, 1만개의 파일이 생성 됩니다.
  zdnet 의 경우는 조회수가 10만-20만을 넘는 초대형 사이트 이기 때문에 HTML 로 만드는 것이 퍼포먼스가 좋습니다.

  UDF 배워 두면..남주지 않습니다.


8.조인보다는 쿼리를 나누어라!

  조인(Join)하는 것보다 쿼리를 2개로 나누는 것이 속도가 빠릅니다.
  조인을 생각 하기 이전에 쿼리를 나누는 것을 생각 하세요 ^^

  어쩔 수 없는 경우는 당근 조인 해야죠.

  글고 서브쿼리는 아직 지원 안됩니다.
  Ms-SQL이나 오라클에서 서브쿼리 보다는 서브쿼리를 하지 않는 방향의 데이터 정규화를 하세요 ^^

  조인 보다 서브쿼리가 느리다.
  서브 쿼리 보다는 조인을 사용하세요 ^^


9.full text index와 search

  3.23.23 부터 mysql 에서는 full text index 를 지원 합니다.  자세한 사항은 아래 ! 

   http://www.mysql.com/documentation/mysql/bychapter/manual_Reference.html#Fulltext_Search
   http://www.mysql.com/doc/F/u/Fulltext_Fine-tuning.html


10. SELECT * FROM sometable

  SELECT * FROM sometable 에서 * 모든을 사용하는 것은 무식한 방법 입니다.
  모든 칼럼을 불러오는 경우는 드물거든요.

  SELECT code,tadate,see FROM sometable

  사용할 것만 불러 오세요 ^^


11.데이터베이스 정규화

  테이블을 아무렇게나 만들면 안됩니다.
  데이터베이스 정규화 원칙에 의거, 테이블을 나눌것은 나누고 만드시는 것이 좋습니다.
  제1 정규화, 제2 정규화 정도는 사용하셔야 합니다.

  게시판을 만들때 아직도 테이블 1개에 만드시나요?
  온라인 폴 만들때 , 테이블 1개에 만드시나요?


12.REPLACE INTO문 사용하기

  REPLACE INTO albums VALUES (6, "tood.net")  
  insert 문대신에 replace 문을 사용해보세요.
  메뉴얼 보시고 연구하세요 ^^

  주키일 경우 사용하시면 됩니다.



13. explain 사용하기

  explain 를 사용하여 테이블의 키 값이 얼마나 잘 활용 되는지 알 수 있습니다.
  EXPLAIN SELECT, SHOW VARIABLES, SHOW STATUS, SHOW PROCESSLIST

   http://www.mysql.com/doc/E/X/EXPLAIN.html


17.BLOB과 TEXT는 분리하라

  BLOB과 TEXT 칼럼은 테이블을 분리 하는 것이 좋다. 다른 칼럼의 내용 보다 크기 때문이다 !

  OPTIMIZE TABLE 명령을 자주 사용해라 !
  Not null 로 지정 하는 것이 빠르다.
  varchar 보다 char 이 훨빠르다.


참고: 게시판의 특성  

  일반 게시판들 보세요. 여기 질문 게시판 만 봐도 알 수 있구요.

  읽기(select) 가 무자게 많습니다. 쓰기(insert into) 보다 30-40 배 정도 많죠.
  하룻동안 올라온 글이 30 개 라면, 하룻동안 글읽기 조회수 총 합은 최소 100 에서 900 까지 갑니다.
  물론 더 엄청날 수 있구요.

  그렇기 때문에 게시판 목록 보기와 글읽기는 HTML 파일로 만들어 놓는 것이 좋습니다.
  또는 MySQL 에서 튜닝 시에 insert 보다는 select 가 빨리 되도록 튜닝 하는 것이 좋구요 ^^


  [참고사이트]
   http://www.mysql.com/documentation/mysql/bychapter/manual_Table_types.html#SEC457
   http://www.mysql.com/documentation/mysql/bychapter/manual_Reference.html#Fulltext_Search  

  from:
   http://www.javasun.net/board/Bread.jsp?CMD=400000&lnum=19&linenum=15&maxnum=0&keykind=null&keyword=&total=0&NCNT=1&ACMD=10&BCODE=001723
2011/07/23 13:10 2011/07/23 13:10