MySQL

From CSBLwiki

Revision as of 10:47, 2 March 2011 (view source) Gnusnah (Talk | contribs) ← Older edit		Revision as of 08:15, 23 May 2011 (view source) Igchoi (Talk | contribs) (→Command) Newer edit →
Line 43:		Line 43:
	</pre>		</pre>
	===Command===		===Command===
-	*temporary table	+	;temporary table
	<pre>		<pre>
	CREATE TEMPORARY TABLE tmptable SELECT * FROM table1...		CREATE TEMPORARY TABLE tmptable SELECT * FROM table1...
	</pre>		</pre>
-	*check warnings	+
		+	;check warnings
	<pre>		<pre>
	SHOW WARNINGS LIMIT 1		SHOW WARNINGS LIMIT 1
		+	</pre>
		+
		+	;Pfam
		+	<pre>
		+	# archaea에서 발견되는 pfam domain의 수
		+	select distinct(reg.auto_pfamA) from pfamA_reg_full_significant reg, pfamseq seq where reg.auto_pfamseq=seq.auto_pfamseq AND seq.taxonomy like '%Archaea;%'
	</pre>		</pre>

---

Revision as of 08:15, 23 May 2011

Contents 1 Tutorial 2 Configuration 3 Command 4 Error Log 5 Database 5.1 UMLS

Tutorial

• Mysql database tutorial - written in Korean; defunct
• start & shutdown
  • restart mysql - use 'mysqld_safe' instead of 'mysqld' (microbio.korea.ac.kr)
• adding new user

mysql> CREATE USER 'monty'@'%' IDENTIFIED BY 'some_pass';
mysql> GRANT ALL PRIVILEGES ON *.* TO 'monty'@'%' WITH GRANT OPTION;
mysql> FLUSH PRIVILEGES;

• change user password

• loading a flat data file

LOAD DATA INFILE 'filename' INTO TABLE 'tablename';
LOAD DATA LOCAL INFILE 'filename' INTO TABLE 'tablename' FIELDS ENCLOSED BY "\'";

• running a sql script

mysql -u user -p 'database' < script.sql

• change default data directory

Configuration

• ERROR 1206 lock table size - modify my.cnf in /etc/mysql (added innodb_buffer_pool_size = 64M)
  • Restart mysqld

>/etc/init.d/mysql restart # be careful to the command (actually a script) in the same directory

• my.cnf 변경(2월13일. 한글깨짐 현상 극복위하여)

[client]
default-character-set=utf8
[mysqld]
character-set-client-handshake=FALSE
default-character-set=utf8
character-set-server=utf8
[mysqldump]
default-character-set=utf8
[mysql]
default-character-set=utf8

Command

temporary table

CREATE TEMPORARY TABLE tmptable SELECT * FROM table1...

check warnings

SHOW WARNINGS LIMIT 1

Pfam

# archaea에서 발견되는 pfam domain의 수
select distinct(reg.auto_pfamA) from pfamA_reg_full_significant reg, pfamseq seq where reg.auto_pfamseq=seq.auto_pfamseq AND seq.taxonomy like '%Archaea;%'

Error Log

• 2011.3.2
  • mediawiki Main_page 뜨지 않음. 다시 설치해도 마찬가지.
    • mysql 문제였던 것으로 생각됨.
  • 재부팅 후 mysqld 로드 안됨
    • /var/lib/mysql/mysqld_error.err에서 문제 발견
    • ibdata1, ib_logfile0, ib_logfile1을 tmp 폴더로 옴겨버리고 mysqld_safe로 데몬 올림. 이후 앞의 세 파일이 새롭게 생성됨.
  • mediawiki는 새로운 에러메시지를 발생. 다음과 같음

MediaWiki internal error.

Exception caught inside exception handler

• • • DB를 새로 만들고 mediawiki를 다시 설치. 성공.

Database

UMLS

• UMLS loading load script manual