Category Archives: Database

SQLite – Reduce db file size after removed the records

After i have removed some log entries in a SQLite DB, I found that the database file size stay the same. This is because SQLite will keep the used space and reuse them later.

We can use the VACUUM command to release the empty space.

sqlite3 <your_sqlite_db_file> "VACUUM;"


Done =)



MSSQL – Select rows which are created within a specific period

Assume the table has a column called CREATED_DATE which stores the creation date. The following SQL make use of the DATEADD() function to select records which are created within the recent 10 days.

  CREATED_DATE > DATEADD(day, -10, getdate())


We can use different unit in the datepart (The first argument in DATEADD()) as stated in the 2nd reference link below.

Done =)


OS X – Fail to start MySQL server which is installed by Homebrew

After i reboot my OS X, i find that i couldn’t start the MySQL server and it throws the following error:

hatuey:5.6.19 yingkityuen$ mysql.server start
Starting MySQL
. ERROR! The server quit without updating PID file (/usr/local/var/mysql/


Go to the above folder and delete the mysql error log which is ended with .err. In my case, i executed the following command:

hatuey:5.6.19 yingkityuen$ rm /usr/local/var/mysql/hatuey.local.err

Where hatuey is my Mac machine name.

This should fix the problem.

Done =)

Reference: Mahdi Yusuf – MySQL ‘The server quit without updating PID file’


Install and setup PostgreSQL for Django

1. Deactivate the virtualenv if there is.

pyenv deactivate


2. Install the required libraries.

sudo apt-get install libpq-dev python-dev


3. Install PostgreSQL.

sudo apt-get install postgresql postgresql-contrib

Continue reading Install and setup PostgreSQL for Django


PostgreSQL – Set and Get Timezone

There is a timezone configuration on PostgreSQL server. If you want to change the default globally, you can edit the postgresql.conf. On Ubuntu, it is located at

  • /etc/postgresql/<version>/main/postgresql.conf
# - Locale and Formatting -

datestyle = 'iso, mdy'
#intervalstyle = 'postgres'
#timezone = 'Hongkong'
timezone = 'UTC'


We could also tweak the timezone only in your current PostgreSQL session.

1. Get the current timezone.

SELECT current_setting('TIMEZONE');

Continue reading PostgreSQL – Set and Get Timezone


Meteor – Connect to the app local MongoDB in command line

1. Start the meteor app.

2. Open another shell and connect to the app database which is also called meteor.

  • mongo

3. Check if you could find the collections there.

  • show collections


Done =)


Check if the MSSQL Server is 32-bit or 64-bit

Run the following SQL to get the info of the installed SQL Server.

SELECT @@Version


Continue reading Check if the MSSQL Server is 32-bit or 64-bit


phpMyAdmin – SQL import timeout

If you have problem on importing the SQL in phpMyAdmin. First, try increasing the following two values in php.ini.

  • max_execution_time
  • max_input_time


Second, Add the following line in the phpMyAdmin

$cfg['ExecTimeLimit'] = 0;


Done =)

Reference: Best way to import large .sql files to PHPmyAdmin via WAMP


Export Microsoft Access .mdb to MySQL

Access To MySQL is a freeware which help converting the data in .mdb in Microsoft Access to .sql in MySQL. Check this out.
Bullzip – Access To MySQL

1. Download and install Access To MySQL.

2. Run the Access To MySQL.
Continue reading Export Microsoft Access .mdb to MySQL


MySQL – Show all database users

Use the following SQL to list out all database users in MySQL.

select host, user from mysql.user;


Done =)

Reference: Alvin Alexander – MySQL show users – how to show the users in a MySQL database