1.1   2007-10-22 subquery, embedded select, select from another select results

according to http://dev.mysql.com/doc/refman/5.1/en/subqueries.html:

select * from calls where ecotypeid= (select distinct ecotypeid from calls where duplicate=4);

1.2   2008-04-11 multi-table update syntax in MySQL and PostgreSQL

The task is to update the same column in a new table according to an old table. Where to put the old table/alias is different in MySQL and PostgreSQL.

In MySQL, put all tables together:

update tmp1 t1, tmp2 t2 set t1.id=t2.id where t1.name=t2.name;

In PostgreSQL, put old table in a from clause:

update tmp2 t2 set id=t1.id from tmp1 t1 where t1.name=t2.name;

update phenotype_method, (select method_id, stddev(value) as std, min(value) as minv from phenotype_avg
group by method_id) as newt set phenotype_method.min_value=newt.minv, phenotype_method.stddev=newt.std
where phenotype_method.id=newt.method_id;

1.3   delete syntax

delete from table1 using table1 inner join table2 where table2.id=table1.table2_id and table2.method_id!=5;

1.4   2008-07-22 outer join between the same table

Two aliases of the same table, alias 1 has a narrowing condition, alias 2 has one too.

MySQL needs an extra where clause to repeat the alias 1 narrowing condition. further adding or just repeating alias 2's in where clause doesn't work (MySQL sucks)

select c1.gene_id, c1.list_type_id, c1.original_name, c2.gene_id as gene_id2, c2.list_type_id from candidate_gene_list c1
left outer join candidate_gene_list c2 on c1.gene_id=c2.gene_id and c1.list_type_id=1 and c2.list_type_id=2
where c1.list_type_id=1 order by gene_id2;

postgresql doesn't:

select c1.gene_id, c1.list_type_id, c1.original_name, c2.gene_id as gene_id2, c2.list_type_id from candidate_gene_list c1
left outer join candidate_gene_list c2 on c1.gene_id=c2.gene_id and c1.list_type_id=1 and c2.list_type_id=2
order by gene_id2;

1.5   2008-10-08 store binary data in database

Two ways of doing it. First is to use the binary data type of database. Second is to encode the binary data, store it as text in database, decode it upon checking out.

#!/usr/bin/env python
import psycopg2
import sys conn = psycopg2.connect("dbname='myforum' user='johndoe' host='localhost'")
curs = conn.cursor()
f = open(sys.argv[1], 'rb')
binary = f.read()
curs.execute("UPDATE usertable SET avatar_image = %s WHERE username = %s", (psycopg2.Binary(binary), sys.argv[2]))
conn.commit()

snp_region_plot = SNPRegionPlot.get(1)
import base64
outf = open('/tmp/snp_region_plot_1.png', 'wb')
outf.write(base64.b64decode(snp_region_plot.img_data))
outf.close()

select * from phenotype_avg where ecotype_id not in (select ecotype_id from view_call where call_method_id=32);
delete from phenotype_avg where ecotype_id not in (select ecotype_id from view_call where call_method_id=32);

2.1   management (copied from /log/log_05/node77.html 2007-05-11)

Table name length is limited to 32 characters. Any longer than that will be truncated to 32. (10-13-05, it seems larger than 32, 38 is fine.)

Dot(.) could not be used in table name.

Dump a schema and restore it. pg_dump -n schemaname -O dbname > outputfile, psql dbname < outputfile.

(10-01-05) Change the default location of POSTGRES_DATA. 1. Change the variable in /etc/postgresql/postmaster.conf. 2. symlink the default directory to a new directory. Run initdb to the new directory ahead.

(10-01-05) ident authentication maps the client's OS username to the server's database username. But when trying to connect to database server from the client, the username to connect should be the server's database username, not the client's username. i.e. psql -h gan.usc.edu -U feipan mdb; (the client's username is yh). The server's pg_ident.conf should have an entry to map yh to feipan.

(10-11-05) postgresql's own md5, password.. authentication is separate from the Unix system. But pam method can enable the unix system authentication.

(10-11-05) ssl connection.

1. Generate server.key and server.crt in the data directory, not the /etc/postgresql.
2. Ownership goes to the server user. server.key's permission should be safe(600).
3. enable ssl in postgresql.conf.
4. edit pg_hba.conf and restart it.
5. ssl connection uses the same port as the non-ssl.
6. (optional) root.crt is optional. Postgresql will authenticate the client if it's present. If it's not present, client certificates are not checked.
7. The change made to *.crt or *.key comes to effect only after the server is restarted.

2.2   substitute the old table with a new table, but the same name(2006-08-28)

• the first method you think of is "alter table xxx rename to xxx" and then "create table xxx...". But this usually doesn't work. When the table has affiliated sequence, primary key, ..., those affiliated stuff's names are not changed. And "create table xxx ..." causes conflict.
• So the correct way is create a table with different name but same structure and transfer the records to the new table, 'INSERT INTO prom_seq_bug_08_28_06 SELECT * from prom_seq;' and then delete the old table, create a new table with same name.

2.3   2007-03-20 pattern matching via regular expression:

'~' means LIKE. '*' means case-insensitive. D is non-digital. one more (back-slash) is used to escape.:

SELECT * from strain_info  where acc ~*'Yng-3\\D';

2.4   2007-05-11 create user in version 8.2

super user password 'secret' has to be put at last.:

CREATE ROLE username with LOGIN CREATEDB CREATEUSER ENCRYPTED password 'secret';

2008-02-27 normal user with select on certain tables.:

create user username; -- =create role username with LOGIN
grant select on gene.gene to username; -- select can't be granted to schema. only grant usage on schema gene to username. But usage!=select.

2.5   2007-05-11 migrate graphdb

• dump the database:

  pg_dump -O graphdb |gzip /backup/zhoudb_graphdb.gz
  

• create an empty database.

  04/17/08 The original db is in SQL_ASCII encoding. The default in createdb has become UTF8 which causes `` invalid byte sequence for encoding "UTF8": 0xa0`` error (the whole table becomes empty.) when the old db is restored in UTF8 db. db template1 has to be disconnected for createdb to run.:

  createdb -E SQL_ASCII graphdb
  

• create the script language. 04/17/08 this is not necessary. The dump file contains such a command.:

  createlang plpythonu graphdb
  

• re-construct the database. 04/17/08 & after > is used to merge stdout and stderr.:

  gunzip -c /var/lib/postgresql/zhoudb_graphdb.gz |psql graphdb >& /tmp/std
  

• output of above command: 04/17/08 log

2008-02-03 standard readme table with automatic updates on user and date:

create table readme(
        id      serial primary key,
        title   varchar,
        description     varchar,
        created_by        varchar default current_user,
        updated_by      varchar,
        date_created    timestamp WITH TIME ZONE default current_timestamp,
        date_updated   TIMESTAMP WITH TIME ZONE
        );

create function insert_readme() returns trigger as '
        begin
        if new.created_by is null then
          new.created_by := current_user;
        end if;
        if new.date_created is null then
          new.date_created := current_timestamp;
        end if;
        return new;
        end;
        '
        language plpgsql;

create trigger insert_readme before insert on readme
        for each row execute procedure insert_readme();

create function update_readme() returns trigger as '
        begin
        new.date_updated := current_timestamp;
        new.updated_by := current_user;
        return new;
        end;
        '
        language plpgsql;

create trigger update_readme before update on readme
        for each row execute procedure update_readme();  

2.6   2008-11-06 add a forein key column to a table

syntax is similar to mysql:

alter table job add how_job_ended_id varchar(512) references job_state (short_name) on delete set null on update cascade;

2.7   2009-9-16 change postgresql character encoding

copied from /research/variation/log-2007-04.

change the postgresql encoding from 'UTF8' to 'LATIN10' ('MULE_INTERNAL' can't be set because ERROR: conversion between mule_internal and UTF8 is not supported):

graphdb=# set client_encoding TO latin4;
SET
graphdb=# set client_encoding TO latin10;
graphdb=# show client_encoding ;
 client_encoding 
-----------------
 latin10
(1 row)

3.1   2009-10-25 database encoding (character set)

The default character set (latin1/latin1_swedish_ci) is not widely used, which causes problem when data is transmitted across files and databases. Output of show variables looks like:

+--------------------------+----------------------------+
| Variable_name | Value |
+--------------------------+----------------------------+
| character_set_client     | latin1 |
| character_set_connection | latin1 |
| character_set_database   | latin1 |
| character_set_filesystem | binary |
| character_set_results    | latin1 |
| character_set_server     | latin1 |
| character_set_system     | utf8 |
| collation_connection     | latin1_swedish_ci |
| collation_database       | latin1_swedish_ci |
| collation_server         | latin1_swedish_ci |
+--------------------------+-------------------+ 

To change the default character set, add following to my.cnf:

[client]
default-character-set  = utf8

[mysqld]
default-character-set = utf8

Output of show variables would look like:

+---------------------------------+-----------------------------------+
| Variable_name                   | Value                             |
+---------------------------------+-----------------------------------+
| character_set_client            | utf8                              | 
| character_set_connection        | utf8                              | 
| character_set_database          | latin1                            | 
| character_set_filesystem        | binary                            | 
| character_set_results           | utf8                              | 
| character_set_server            | utf8                              | 
| character_set_system            | utf8                              | 
| character_sets_dir              | /usr/share/mysql/charsets/        | 
| collation_connection            | utf8_general_ci                   | 
| collation_database              | latin1_swedish_ci                 | 
| collation_server                | utf8_general_ci                   |

Sometimes, character_set_database and collation_database would turn into utf8 in the beginning and then turn back into latin1. Maybe because there are still some tables in character set latin1.

3.2   2007-04-26 set the pager for the mysql client, '-p' is to let the server ask for password.

mysql --pager=less -p

3.3   2007-04-26 starting commands in mysql client

show databases;

use database_name;

show tables;

desc table_name;

pager less;

3.4   2009-9-20 stock (149SNP) database

update phenotype_method pm, (select method_id, min(value) as min_value, stddev(value) as stddev
from phenotype_avg where method_id >=340 group by method_id) as pa set pm.min_value=pa.min_value,
pm.stddev=pa.stddev where pm.id=pa.method_id;

3.5   2007-06-11 user/privilege management

mysql>GRANT ALL PRIVILEGES ON *.* TO 'crocea'@'localhost' identified by 'password';

'CREATE USER' syntax would have the same effect but still needs 'GRANT' to get all privileges. % could be used to replace localhost to specify any host.

mysql>FLUSH PRIVILEGES; tells the server to reload the grant table

Privilege types include USAGE, SELECT, etc. USAGE is like nothing, just account created.

The logic of mysql user management is two tables, mysql.user and mysql.db. Each user is identified by its name and hostname (from where the user is allowed to access database). Associating hostname with a user account is not a smart idea. This causes password entering every time a new hostname happens to a user.

ERROR 1227 (42000): Access denied; you need the SUPER privilege for this operation

SET PASSWORD FOR 'jeffrey'@'%' = PASSWORD('biscuit');

update mysql.user set Password=NULL where Host='localhost' and User='jeffrey';

update mysql.user set insert_priv='Y' where Host='localhost' and User='jeffrey';

3.6   2007-06-11 enable network access

configure /etc/mysql/my.cnf, change 'bind-address = 127.0.0.1' to 'bind-address = your-machine-ip'

3.7   2005-12-10 copy table to file

to client file (more useful generally):

mysql -h db.cisred.org -u anonymous -e "select * from cisred_Hsap_2.features" > /tmp/yuhuang/features

2007-06-11 copy table to server file:

select * from cisred_Hsap_2.features into outfile '/tmp/yuhuang/features';

3.8   2007-06-11 load data into table from a file (copy in postgresql)

examples:

mysql> load data infile '/tmp/person_reshuffled_for_table' into table person CHARACTER SET utf8;

mysql> load data infile '/tmp/magnus_192.csv' into table magnus_192 FIELDS terminated by ',';

2008-02-12 \N is taken as NULL data type, specified by 'FIELDS ESCAPED BY' of 'load data infile' syntax, which only supports one-char separator and tests show it fails to recognize multi-char separator, like NA.

2009-10-25 If the contents of the input file use a character set that differs from the default (which constantly falls back on the recalcitrant latin1), specify the character set of the file by using the CHARACTER SET clause. Omitting CHARACTER SET clause is generally ok unless the input file contains some non-English letters (like Löv-5). The database server would use the system variable character_set_database (which =``latin1``) to interpret the data and if the table to hold the data has a different character set, funny things would ensue.

3.9   2007-09-27 regular expression search

REGEXP or RLIKE, both are not case sensitive:

select * from ecotype where nativename rlike 'Col-.*';

3.10   2007-10-10 no 'full join' for mysql

check http://dev.mysql.com/doc/refman/5.1/en/join.html. example::

select distinct a.id,a.name,e.nativename from at.accession a left join ecotype e on e.nativename = a.name order by nativename, name;

3.11   2007-10-10 varchar syntax

datatype varchar has to have (M) attached to specify the maximum number of characters. like varchar(20). just 'varchar' won't work.

3.12   2008-01-09 transaction

http://dev.mysql.com/doc/refman/5.1/en/transactional-commands.html shows mysql could handle transaction. I tested it with creating a table, it didn't work.:

begin;
xxxx;
rollback;

2008-01-15 Transaction is only for the Innodb engine. Either step below transforms database into "INNODB":

1. insert default-storage-engine=innodb into [mysqld] section of /etc/mysql/my.cnf

1. append engine=innodb to the end of create table syntax:

   create table tmp (id integer) engine=innodb;
   

2. then begin; xxx; rollback; would start to work.

   create table is not something that could be rolled back.

3.13   2008-01-09 table

ALTER TABLE phenotype ENGINE = InnoDB;

SET AUTOCOMMIT = 0;



SET storage_engine=INNODB;

desc mytable;

select * from information_schema.tables where table_schema='mydatabase';

show table status; --execute this in a database, get same stuff as above

SHOW INDEXES FROM mytable;

show triggers;

show create table mytable;

SHOW ENGINE INNODB STATUS\G

alter table gene_list_type add UNIQUE (short_name);

alter table candidate_gene_rank_sum_test_result drop key results_method_id;

PRIMARY KEY  (`id`),
KEY `ix_candidate_gene_rank_sum_test_result_list_type_id` (`list_type_id`),
KEY `results_by_gene_id` (`results_by_gene_id`),
CONSTRAINT `candidate_gene_rank_sum_test_rbg_ibfk_1` FOREIGN KEY (`results_by_gene_id`) REFERENCES `results_by_gene` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `candidate_gene_rank_sum_test_result_list_type_id_fk` FOREIGN KEY (`list_type_id`) REFERENCES `candidate_gene_list_type` (`id`) 
ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=529437 DEFAULT CHARSET=latin1 | 

3.14   2008-01-28 TIMESTAMP column with CURRENT_TIMESTAMP in DEFAULT or ON UPDATE clause

There can be only one TIMESTAMP column with CURRENT_TIMESTAMP in DEFAULT or ON UPDATE clause. So a plan to have both date_created and date_modified default to CURRENT_TIMESTAMP would fail. So it's now like this:

...
date_created    timestamp default 0,
date_modified   TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
...

3.15   2008-02-03 standard mysql readme table with automatic updates on user and date

check this:

create table readme(
        id      integer auto_increment primary key,
        title    varchar(2000),
        description     varchar(60000),
        created_by      varchar(200),
        updated_by     varchar(200),
        date_created    timestamp default CURRENT_TIMESTAMP,
        date_updated   TIMESTAMP default 0
        );

DELIMITER |     -- change the delimiter ';' to '|' because ';' is used as part of one statement.

CREATE TRIGGER before_insert_readme BEFORE INSERT ON readme
  FOR EACH ROW BEGIN
        if NEW.created_by is null then
               set NEW.created_by = USER();
        end if;
        if NEW.date_created is null then
               set NEW.date_created = CURRENT_TIMESTAMP();
        end if;
  END;
|

CREATE TRIGGER before_update_readme BEFORE UPDATE ON readme
  FOR EACH ROW BEGIN
        set NEW.updated_by = USER();
        set NEW.date_updated = CURRENT_TIMESTAMP();
  END;
|

DELIMITER ;

3.16   2008-01-28 replicate mysql (master-slave)

1. create a user for replication on the master:

   GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%.domain.com' IDENTIFIED BY 'secret_password';
   

2. setting the replication master configuration:

   [mysqld]
   log-bin=mysql-bin
   server-id=1
   

3. setting the replication slave configuration

   [mysqld] server-id=2

4. obtaining the master replication information on the master:

   mysql> FLUSH TABLES WITH READ LOCK;
   mysql> SHOW MASTER STATUS;
   +---------------+----------+--------------+------------------+
   | File          | Position | Binlog_Do_DB | Binlog_Ignore_DB |
   +---------------+----------+--------------+------------------+
   | mysql-bin.003 | 73       | test         | manual,mysql     |
   +---------------+----------+--------------+------------------+
   

   unlock tables will release all locks imposed by `` FLUSH TABLES WITH READ LOCK``.

5. Creating a Data Snapshot Using mysqldump on the master:

   mysql> FLUSH TABLES WITH READ LOCK;
   
   mysqldump -h master_host --lock-all-tables -u username -p --databases DB1 DB2 >/tmp/master.dump.db
   

6. create a snapshot of master data on slave

   1. startup the slave without replication started by using the --skip-slave option (actually just /etc/init.d/mysql restart will do as far as master setup isn't put into my.cnf.):

      sudo /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --user=mysql --pid-file=/var/run/mysqld/mysqld.pid \
      --skip-external-locking --port=3306 --socket=/var/run/mysqld/mysqld.sock --skip-slave-start
      

   2. Import the dump file:

      mysql < /tmp/master.dump.db
      

7. Setting the Master Configuration on the Slave:

   mysql> CHANGE MASTER TO MASTER_HOST='master_hostname', MASTER_USER='repl', MASTER_PASSWORD='secret',MASTER_LOG_FILE='mysql-bin.003', MASTER_LOG_POS=73;
   

8. start the slave:

   mysql> START SLAVE;
   

9. Once a slave is replicating, you can find in its data directory one file named master.info and another named relay-log.info. The slave uses these two files to keep track of how much of the master's binary log it has processed.

10. add following to my.cnf on the slave:

    report-host=slave_hostname    #2008-01-29 otherwise the 'show slave hosts' on master wouldn't show the slave
    relay-log=/var/run/mysqld/mysqld-relay-bin    #2008-01-29 suggested in /var/log/syslog
    

11. Restart the slave mysql daemon:

    sudo /etc/init.d/mysql start
    

CHANGE MASTER TO \
    MASTER_HOST='master_hostname', \
    MASTER_USER='replicate', \
    MASTER_PASSWORD='password', \
    MASTER_SSL=1, \
    MASTER_LOG_FILE='mysql-bin.000477', \
    MASTER_LOG_POS=947,\
    MASTER_SSL_CA = 'ca_file_name', \
    MASTER_SSL_CAPATH = 'ca_directory_name', \
    MASTER_SSL_CERT = 'cert_file_name', \
    MASTER_SSL_KEY = 'key_file_name';

3.17   2008-02-18 foreign key

• Both tables have to be InnoDB type.

• The foreign key column and its referenced key must be of same type.

  • The size and sign of integer types must be the same.
  • The length of string types need not be the same.
  • For non-binary (character) string columns, the character set and collation must be the same.

• foreign key has to be specified. Just References doesn't work. Constraint is optional.:

  create table array_info(
   id integer auto_increment primary key,
   filename varchar(1000),
   description varchar(2000),
   strain_id integer not null,
   CONSTRAINT array_info_strain_id_fk_constraint foreign key (strain_id) references strain_info(id) on delete cascade on update cascade
   )engine=INNODB;
  

• SHOW CREATE TABLE tbl_name; or SHOW TABLE STATUS FROM db_name LIKE 'tbl_name'; display the foreign key constraint.

• SET storage_engine=INNODB; set the storage engine for this session.

alter table tg_ecotypeid add foreign key (tg_ecotypeid) references ecotype(id) on delete restrict on update cascade;

3.18   2008-04-09 rename database

MySQL doesn't have a command for that (while postgresql has ALTER DATABASE name RENAME TO newname.)

People suggest (http://www.delphifaq.com/faq/databases/mysql/f574.shtml) to manually rename the db directory name in datadir (like /var/lib/mysql) while db is shut down. It causes mysql unable to find the tables under the renamed db and conflict with a second db with same db/table name as the old name of the 1st db.

The solution:

1. dump the old one out:

   mysqldump -h hostname -u username -p db_name > /tmp/db_name.dump.sql
   

2. drop the old database and create a new database in mysql client:

   mysql> drop database db_name;
   mysql> create database new_db_name;
   

3. restore the database under the new db name:

   mysql new_db_name < /tmp/db_name.dump.sql
   

3.19   2008-08-08 poking database status

SHOW PROCESSLIST;

kill THREAD_ID

localhost:~ root# perror 150
MySQL error code 150: Foreign key constraint is incorrectly formed

Last_Error: Error 'The total number of locks exceeds the lock table size' on query. Default database: 'genome'.
Query: 'INSERT INTO genome.gene_symbol2id (tax_id, gene_symbol, gene_id, symbol_type) VALUES (13616, 'LOC100021709',
100021709, 'symbol')'

# Set buffer pool size to 50-80% of your computer's memory
innodb_buffer_pool_size=1G
innodb_additional_mem_pool_size=500M
#
# Set the log file size to about 25% of the buffer pool size
innodb_log_file_size=500M
innodb_log_buffer_size=500M

mysql> SHOW MASTER STATUS;

mysql> SHOW slave STATUS;

3.20   2008-08-11 reserved keyword

• call

3.21   2008-08-14 mysql SSL connection

shell> mysql --ssl-ca=cacert.pem

shell> mysql --ssl-ca=cacert.pem --ssl-cert=client-cert.pem --ssl-key=client-key.pem

# Create client certificate
shell> openssl req -newkey rsa:2048 -days 1000 \
         -nodes -keyout client-key.pem > client-req.pem
shell> openssl x509 -req -in client-req.pem -days 1000 \
         -CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 > client-cert.pem

3.22   2009-5-19 Procedure

Procedure in MySQL is basically a function. One tricky thing is the delimiter cuz ; is used in procedure for a different purpose. Check this:

DELIMITER |
CREATE PROCEDURE dowhile() BEGIN  DECLARE v1 INT DEFAULT 1;
  WHILE v1 <93 DO SET v1 = v1 + 1;
  insert into accession2call_method VALUES (1, v1);
  END WHILE;
END|
call dowhile()|
drop procedure dowhile|
delimiter;