user this to log knowledge of database.

-- Sam 05:14 16/11/2019

how to enlarge the max package in MySQL?

in MySQL installation folder, there's many file like my.ini/mylarge.ini/myhuge.ini/............Open each of them, find the line of max_allowed_packet=1M, and change them into max_allowed_packet=10M, restart the MySQL, done!
for older version than V5.5, just change in the my.ini or ~/.my.cnf file by including the single line under [mysqld] section in your file: max_allowed_packet=10Mthen restart the MySQL service and you are done.

-- Sam 06:26 13/03/2018

.....

-- JustPrint 01:03 22/10/2017

use not exist to clean up the bigtag table:

my=# delete from bigtag where NOT EXISTS (select * from twitter where twitter.twittertag_id=bigtag.id) and not exists(select * from content where content.uncommonbigtag_id=bigtag.id) and bigtag.type not in ('admin','Simon','Nicholas','Tao','Grace');

performance improvement:

where delete from bigtag where bigtag.id not in (select bigtag_id from twitter) and bigtag.id not in (slecte uncommonbigtag_id from content) and bigtag.type not in ('admin','Simon','Nicholas','Tao','Grace');

-- Sam 06:01 01/12/2016

数据库名区分大小写的。
postgresQL shell的提示/帮助信息显示乱码的解决办法chcp 437/* 设置英文环境 */，或者chcp 936/*设置简体中文环境 */

-- Sam 23:20 28/11/2016

linux下最常用的几个数据库命令：connect with Bitvise SSH Client first (it's better than putty ssh)
进去数据库客户端
paql
列举所有的表？
\d
列举某个表中每个字段？
\d [tablename]
退出psql
\q
modifi the text(256)
ALTER TABLE textcontent alter content type text; 改变列的类型为不定长文本，注意“；”不能少，否则不work

ALTER USER 'root'@'localhost' IDENTIFIED BY '1234';

-- Sam 19:02 11/01/2022

how to back up database in openShift?

[china-rightnow.rhcloud.com 530b6118e0b8cd3cf9000347]\> pg_dump china | gzip>$OPENSHIFT_DATA_DIR/dbbk0321.bak.gz or [spa-rightnow.rhcloud.com 52ea731ce0b8cddb92000986]\> pg_dump spa >$OPENSHIFT_DATA_DIR/spa0321.sql if it not too big.)

then download the gz or file to local disk.

how to restore database?

upload the spa0321.sql from local to it's data folder.

(if the table is already created, and you don't want to menuly run drop table commmand for each table, then create a droptablees.sql contains all the drop command, and upload it to the folder, then run psql -f $OPENSHIFT_DATA_DIR/droptables.sql drop all the tables in old database. (upload the droptables.sql from local to $OPENSHIFT_DATA_DIR/data folder first)

then run psql -f $OPENSHIFT_DATA_DIR/spa0321.sql to restore the database.

if restore to local postgres,

$OPENSHIFT_DATA_DIR= /var/lib/openshift/5749d7767628e17849000077/app-root/data

-- Sam 23:50 23/11/2016

~~内存数据库所放的位置问题~~

~~Openshift有时没理由的就报错了，说什么数据库驱动为null或无效路径。~~

~~rhc restart app 【appname】执行后，报不同的错误，（PublicController.java Line56)~~

~~短期解决办法。但重新git发布后就能正常~~

~~长期来看，可能应该换成OpenShif支持的PostgresQL，可能会好？~~

~~（-----果然是内存数据库所放的位置问题，当系统维护后，那个位置就变成写保护了）~~

~~---------------------------~~

貌似HSQLDB所在的路径如：git/you.git/bigbang.* 就会变成只读，因为我们会得到错误类似驱动为null，曾经追踪过，深层原因，记得是因为往该位置写东西失败。

~~解决方法，我这次随便打开了个文件，在尾巴删除一个空行，保存，再用rds发布就好了。~~

-- Sam 00:53 01/08/2014

Saving passwords

paAdmin password are stored in plain text in your home directory on *nix systems, or in your user profile on Windows.

On *nix systems, the password will be stored in ~/.pgpass,
whilst on Windows systems it will be stored in %APPDATA%\PostgreSQL\pgpass.conf (%APPDATA% is the 'Application Data' folder in your user profile).

This mechanism is used by default by all programs that use the libpq library to access the server, which includes command line applications such as pg_dump and pg_restore, other GUI applications, and drivers such as psqlODBC. This means that those applications may automatically connect to the server using your stored password. If you do not want this to happen, you should not use the Store Password option in pgAdmin.

-- Sam 00:48 01/08/2014