Month: September 2021

MySQL Cheat Sheet

MySQL Cheat Sheet

MySQL Cheat Sheet provides you with one-page that contains the most commonly used MySQL commands and statements that help you work with MySQL more effectively.

MySQL command-line client Commands

Connect to MySQL server using mysql command-line client with a username and password (MySQL will prompt for a password):

mysql -u [username] -p;

Connect to MySQL Server with a specified database using a username and password:

mysql -u [username] -p [database];

Exit mysql command-line client:


Export data using mysqldump tool

mysqldump -u [username] -p [database] > data_backup.sql;

To clear MySQL screen console window on Linux, you use the following command:

mysql> system clear;

Currently, there is no command available on Windows OS for clearing MySQL screen console window.

Working with databases

Create a database with a specified name if it does not exist in the database server


Use a database or change the current database to another database that you are working with:

USE database_name;

Drop a database with a specified name permanently. All physical files associated with the database will be deleted.

DROP DATABASE [IF EXISTS] database_name;

Show all available databases in the current MySQL database server


Working with tables

Show all tables in a current database.


Create a new table


Add a new column into a table:

ADD [COLUMN] column_name;

Drop a column from a table:

ALTER TABLE table_name
DROP [COLUMN] column_name;

Add index with a specific name to a table on a column:

ADD INDEX [name](column, ...);

Add primary key into a table:

ALTER TABLE table_name 
ADD PRIMARY KEY (column_name,...);

Remove the primary key of a table:

ALTER TABLE table_name

Drop a table:

DROP TABLE [IF EXISTS] table_name;

Show the columns of a table:

DESCRIBE table_name;

Show the information of a column in a table:

DESCRIBE table_name column_name;

Working with indexes

Creating an index with the specified name on a table:

CREATE INDEX index_name
ON table_name (column,...);

Drop an index:

DROP INDEX index_name;

Create a unique index:

ON table_name (column,...);

Working with views

Create a new view:


Create a new view with the WITH CHECK OPTION:

AS select_statement

Create or replace a view:


Drop a view:

DROP VIEW [IF EXISTS] view_name;

Drop multiple views:

DROP VIEW [IF EXISTS] view1, view2, ...;

Rename a view:

RENAME TABLE view_name
TO new_view_name;

Show views from a database:

[{FROM | IN } database_name]
WHERE table_type = 'VIEW';

Working with triggers

Create a new trigger:

CREATE TRIGGER trigger_name
ON table_name FOR EACH ROW

Drop a trigger:

DROP TRIGGER [IF EXISTS] trigger_name;

Show triggers in a database:

[{FROM | IN} database_name]
[LIKE 'pattern' | WHERE search_condition];

Working with stored procedures

Create a stored procedure:


CREATE PROCEDURE procedure_name(parameter_list)
END $$


Drop a stored procedure:

DROP PROCEDURE [IF EXISTS] procedure_name;

Show stored procedures:

[LIKE 'pattern' | WHERE search_condition];

Working with stored functions

Create a new stored function:


CREATE FUNCTION function_name(parameter_list)
RETURNS datatype
 -- statements
END $$


Drop a stored function:

DROP FUNCTION [IF EXISTS] function_name;

Show stored functions:

[LIKE 'pattern' | WHERE search_condition];

Querying data from tables

Query all data from a table:

SELECT * FROM table_name;

Query data from one or more column of a table:

    column1, column2, ...

Remove duplicate rows from the result of a query:

    DISTINCT (column)

Query data with a filter using a <a href="">WHERE</a> clause:

SELECT select_list
FROM table_name
WHERE condition;

Change the output of the column name using column alias:

    column1 AS alias_name,
    expression AS alias,

Query data from multiple tables using inner join:

SELECT select_list
FROM table1
INNER JOIN table2 ON condition;

Query data from multiple tables using left join:

SELECT select_list
FROM table1 
LEFT JOIN table2 ON condition;

Query data from multiple tables using right join:

SELECT select_list 
FROM table1 
RIGHT JOIN table2 ON condition;

Make a Cartesian product of rows:

SELECT select_list
FROM table1
CROSS JOIN table2;

Counting rows in a table.

FROM table_name;

Sorting a result set:

    column1 ASC [DESC], 
    column2 ASC [DESC];

Group rows using the GROUP BY clause.

SELECT select_list
FROM table_name
GROUP BY column_1, column_2, ...;

Filter group using the <a href="">HAVING</a> clause:

SELECT select_list
FROM table_name
GROUP BY column1
HAVING condition;

Modifying data in tables

Insert a new row into a table:

INSERT INTO table_name(column_list)

Insert multiple rows into a table:

INSERT INTO table_name(column_list)

Update all rows in a table:

UPDATE table_name
SET column1 = value1,

Update data for a set of rows specified by a condition in WHERE clause.

UPDATE table_name
SET column_1 = value_1,
WHERE condition

Update with join

INNER JOIN table1 ON table1.column1 = table2.column2
SET column1 = value1,
WHERE condition;

Delete all rows in a table

DELETE FROM table_name;

Delete rows specified by a condition:

DELETE FROM table_name
WHERE condition;

Delete with join

DELETE table1, table2
FROM table1
    ON table1.column1 = table2.column2
WHERE condition;


Search for data using the <a href="">LIKE</a> operator:

SELECT select_list
FROM table_name
WHERE column LIKE '%pattern%';

Text search using a regular expression with RLIKE operator.

SELECT select_list
FROM table_name
WHERE column RLIKE 'regular_expression';

Issue with rsync duplicate filesystem

Issue with rsync duplicate filesystem

Some issues encountered when using rsync to duplicate filesystem.

Extended attribute

When duplicating the files, rsync didn't take care of extended attributes, users might lose rights on some files.

Sparse files (Untested)

In order to create/update sparse files, to steps required.

To create new files in sparse mode

rsync --ignore-existing --sparse ...

To update all existing files (including the previously created sparse ones) inplace.

rsync --inplace ...

FreeNAS vs Synology

FreeNAS vs Synology


Devices: ds1812+ and ds2419+


  • Hardware are very stable (more than 10 years without issue)
  • Low power and low noise
  • Reasonable price
  • Mix size hard disks in volume
  • Upgrade Hard disk easily
  • Identify hard disk easily
  • Crashed volume in read-only mode, data can be retrieved
  • Many apps can be downloaded
  • Operations on NAS are organized user friendly


  • Cannot move or copy share folder after volume crashed, manual copy and resetup required
  • Bad hard disk can cause extension unit disconnected from main unit
  • Doesn't accept bad hard disk which smart testing failed, shows failling HDD list
  • Create many special folder named as @eaDir in everywhere, which can be issue when using some services, and huge number of small files in it.
    Note: This folder creation feature could not be disabled.
  • Dedup can not be handled
  • CPU is weak for virtual machines


Just started on an i7 PC with 32GB ram.


  • Opensource
  • Can be installed in a normal PC
  • Hardware upgrade is easy, and can import disks used in other NAS before
  • Insensitive to bad hard disk
  • ZFS can handle bad sector natively
  • ZFS can perform dedup natively (haven't tested)


  • Not easy to understand the tasks to be performed
  • Network configuration screens are everywhere, not easy to find them
  • Network aggregation configuration isn't easy to understand
  • Disk, pool, and dataset are highly related to ZFS
  • Share folder permission and ACL are too complex for NAS operation

FreeNAS USB Drive Installation

FreeNAS USB Drive Installation

In order to fully utilize system by FreeNAS, also like to test whether similar hanging issue happened when directly installed on USB drive without ESXi, installation had been done with following steps.

Create on USB drive from ISO image

Creating USB drive on Mac using steps mentioned below.

Preparing the Media

Using rdiskX, which is raw device (not read-only device), will be faster as mentioned in the instructions.

dd if=FreeNAS-9.3-RELEASE-x64.iso of=/dev/rdisk1 bs=64k

Boot from USB drive

Boot from the USB drive created above, and another USB drive will be used for installation.

Select BIOS mode

By choosing BIOS instead of UEFI, the PC bios could not set as auto boot from USB drive, but it can be chosen for manual boot. So choose UEFI mode instead.

Secure Boot in PC bios also requires to be set to Other OS instead of Windows UEFI, otherwise, following error will occurre.

System found unauthorized changes on the firmware error...

Configure network

To set aggragation mode, two original interfaces which had configured, will not be displayed in aggragation menu.

IP address will be configured on aggragation interface.

Creating FreeNAS VM on ESXi

Creating FreeNAS VM on ESXi

ZFS advantages

Synology uses btrfs as filesystem, which lack of support for bad sector as it natively not supported by btrfs. ZFS could be the choise because of some advantages below.

Bad sector support

Although Synology also can hand bad sector, but btrfs doesn't. Not sure how Synology handle it, but bad sectors can cause Synology volumes crash, and then volumes will be in read only mode, reconfiguration required and taking time to move volumes out from impacted volumes.

Block level dedup

This is an intersting feature of ZFS. The btrfs dedup can be done by running scripts, but ZFS can do natively.



FreeNAS/TrueNAS has many feature and got full functions of NAS feature.


FreeNAS/TrueNAS doesn't support ARM CPU, and cheap ARM board, such as Raspberry PI 4, doesn't support SATA, and 4 SATA drives should be considered for normal NAS. So not intend to use ARM board for NAS.


Decided to try FreeNAS/TrueNAS using an old PC installed ESXi, which has 32GB RAM, 8 theads CPU, and 10GB Ethernet.

  1. Assign 8GB RAM and 2 theads to FreeNAS VM, set hot plug memory and CPU in order to increase memory and CPU dynamically.

    Note: Error occurred when add memory to VM. See post Error on adding hot memory to TrueNAS VM.

  2. Create RDM disk access hard disk directly to improve disk performance.

  3. Create VM network interface which supports 10GB.

  4. Create iSCSI disk to hold VM image, because RDM disk vmdk file can not be created in NFS.

Create iSCSI storage

Although the ESXi is managed by vCenter, but could not find the place to configure iSCSI device. So login to ESXi web interface, and configure iSCSI in Storage -> Adapters.

Note: Target is IQN, not name.

Configure network on ESXi

During the creation, ESXi shows an error that two network interfaces had detected on network used by iSCSI, so I removed second standby interface during iSCSI adapter creation, then put back again after creation completed.

Create RDM disk

Following instructions given by Raw Device Mapping for local storage (1017530), to create RDM disk.

  1. Open an SSH session to the ESXi host.

  2. Run this command to list the disks that are attached to the ESXi host:

    ls -l /vmfs/devices/disks
  3. From the list, identify the local device you want to configure as an RDM and copy the device name.

    Note: The device name is likely be prefixed with t10. and look similar to:

  4. To configure the device as an RDM and output the RDM pointer file to your chosen destination, run this command:

    vmkfstools -z /vmfs/devices/disks/diskname /vmfs/volumes/datastorename/vmfolder/vmname.vmdk

    For example:

    vmkfstools -z /vmfs/devices/disks/t10.F405E46494C4540046F455B64787D285941707D203F45765 /vmfs/volumes/Datastore2/localrdm1/localrdm1.vmdk

    Note: The size of the newly created RDM pointer file appears to be the same size and the Raw Device it it mapped to, this is a dummy file and is not consuming any storage space.

  5. When you have created the RDM pointer file, attach the RDM to a virtual machine using the vSphere Client:

    • Right click the virtual machine you want to add an RDM disk to.
    • Click Edit Settings.
    • Click Add.
    • Select Hard Disk.
    • Select Use an existing virtual disk.
    • Browse to the directory you saved the RDM pointer to in step 5 and select the RDM pointer file and click Next.
    • Select the virtual SCSI controller you want to attach the disk to and click Next.
    • Click Finish.

You should now see your new hard disk in the virtual machine inventory as Mapped Raw LUN.

Create VM on iSCSI storage

Create VM using following parameters

  • VM type should be FreeBSD 12 (64 bit)
  • Memory recommented 8GB (Configured 4GB at beginning, no issue found)
  • SCSI adapter should be LSI Logic Parallel, if not, harddisk can not be detected.
  • Network adapter should be VMXNET3 to support 10GB
  • Add RDM disk into VM (I have done it after server created)

Configure FreeNAS

Configure network in FreeNAS console, and configure pool, dataset, user, sharing, ACL in FreeNAS website.

Configuration in FreeNAS console

Configure FreeNAS network

Configure IP address

Configuration in FreeNAS website

Using browser to access IP configured in previous step


Configure DNS

Default route

Default route is added in static route as to gateway.


Configure timezone


Configure Pool by giving pool name pool01


Under Pool, add Dataset, such as download


Create user to be used later in ACL.


Create SMB sharing

Configure owner in ACL

Assign owner and group to user created above, and select mode as Restrict.

Delete Pool (if needed)

Select Export/Disconnect in setting of pool.


ESXi hangs

The biggest issue encountered is, ESXi hangs, complains one PCPU freezing. Will try to install usb drive directly to see whether problem only happens on ESXi.

Network speed

Fast as excepted

Compare with Synology DS2419+

They are not comparable, because DS2419+ has more disks in the volume.

  • Slower.
  • Stopped when flushing the disks

Compare with Synology DS1812+

They are not comparable, because DS1812+ has three slow disks with raid in the volume.

TODO: Cannot set LC_CTYPE/LC_ALL to default locale: No such file or directory

Cannot set LC_CTYPE/LC_ALL to default locale: No such file or directory

Error description

Below error repeatly appears when run apt upgrade.

perl: warning: Setting locale failed.
perl: warning: Please check that your locale settings:
    LANGUAGE = (unset),
    LC_ALL = (unset),
    LC_CTYPE = "UTF-8",
    LANG = "en_US.UTF-8"
    are supported and installed on your system.
perl: warning: Falling back to a fallback locale ("en_US.UTF-8").
Scanning processes...
Scanning candidates...
Scanning linux images...
/usr/bin/locale: Cannot set LC_CTYPE to default locale: No such file or directory
/usr/bin/locale: Cannot set LC_ALL to default locale: No such file or directory

Check /etc/default/locale file,

#  File generated by update-locale

it doesn't contain following lines


Tried but failed

Tried to run following commands, the errors are still there.

locale-gen "en_US.UTF-8"
dpkg-reconfigure locales

Also added following lines in /etc/environment and /etc/default/locale, still failed


Troubleshooting ping drop packet with same interval

Troubleshooting ping drop packet wit same interval

The issue appear between 10G Qnap switch and the TPlink router. TPLink has a 2.5GB ethernet, which connects to 10G ethernet of Qnap switch. Sometimes, ping drop package, they have almost same interval!

% ping 
PING ( 56 data bytes
64 bytes from icmp_seq=0 ttl=64 time=0.464 ms
Request timeout for icmp_seq 1
64 bytes from icmp_seq=2 ttl=64 time=0.431 ms
64 bytes from icmp_seq=3 ttl=64 time=0.399 ms
64 bytes from icmp_seq=4 ttl=64 time=0.302 ms
64 bytes from icmp_seq=5 ttl=64 time=0.356 ms
64 bytes from icmp_seq=6 ttl=64 time=0.461 ms
64 bytes from icmp_seq=7 ttl=64 time=0.495 ms
64 bytes from icmp_seq=8 ttl=64 time=0.450 ms
64 bytes from icmp_seq=9 ttl=64 time=0.573 ms
64 bytes from icmp_seq=10 ttl=64 time=0.282 ms
64 bytes from icmp_seq=11 ttl=64 time=0.374 ms
64 bytes from icmp_seq=12 ttl=64 time=0.604 ms
64 bytes from icmp_seq=13 ttl=64 time=0.438 ms
64 bytes from icmp_seq=14 ttl=64 time=0.418 ms
64 bytes from icmp_seq=15 ttl=64 time=0.446 ms
64 bytes from icmp_seq=16 ttl=64 time=0.570 ms
64 bytes from icmp_seq=17 ttl=64 time=0.753 ms
64 bytes from icmp_seq=18 ttl=64 time=0.456 ms
64 bytes from icmp_seq=19 ttl=64 time=0.530 ms
64 bytes from icmp_seq=20 ttl=64 time=0.531 ms
64 bytes from icmp_seq=21 ttl=64 time=0.480 ms
64 bytes from icmp_seq=22 ttl=64 time=0.498 ms
64 bytes from icmp_seq=23 ttl=64 time=0.498 ms
64 bytes from icmp_seq=24 ttl=64 time=0.465 ms
Request timeout for icmp_seq 25
64 bytes from icmp_seq=26 ttl=64 time=0.493 ms
64 bytes from icmp_seq=27 ttl=64 time=0.520 ms
64 bytes from icmp_seq=28 ttl=64 time=0.462 ms
64 bytes from icmp_seq=29 ttl=64 time=0.459 ms
64 bytes from icmp_seq=30 ttl=64 time=0.535 ms
64 bytes from icmp_seq=31 ttl=64 time=0.468 ms
64 bytes from icmp_seq=32 ttl=64 time=0.505 ms
64 bytes from icmp_seq=33 ttl=64 time=0.539 ms
64 bytes from icmp_seq=34 ttl=64 time=0.515 ms
64 bytes from icmp_seq=35 ttl=64 time=0.504 ms
64 bytes from icmp_seq=36 ttl=64 time=0.519 ms
64 bytes from icmp_seq=37 ttl=64 time=0.415 ms
64 bytes from icmp_seq=38 ttl=64 time=0.415 ms
64 bytes from icmp_seq=39 ttl=64 time=0.384 ms
64 bytes from icmp_seq=40 ttl=64 time=0.443 ms
64 bytes from icmp_seq=41 ttl=64 time=0.456 ms
64 bytes from icmp_seq=42 ttl=64 time=0.349 ms
64 bytes from icmp_seq=43 ttl=64 time=0.345 ms
64 bytes from icmp_seq=44 ttl=64 time=0.272 ms
64 bytes from icmp_seq=45 ttl=64 time=0.456 ms
64 bytes from icmp_seq=46 ttl=64 time=0.523 ms
64 bytes from icmp_seq=47 ttl=64 time=0.553 ms
64 bytes from icmp_seq=48 ttl=64 time=0.389 ms
Request timeout for icmp_seq 49
64 bytes from icmp_seq=50 ttl=64 time=0.417 ms
64 bytes from icmp_seq=51 ttl=64 time=0.433 ms
64 bytes from icmp_seq=52 ttl=64 time=0.467 ms
64 bytes from icmp_seq=53 ttl=64 time=0.417 ms
--- ping statistics ---
54 packets transmitted, 51 packets received, 5.6% packet loss
round-trip min/avg/max/stddev = 0.272/0.461/0.753/0.083 ms

Possible issue

After a month, I found that in Qnap web console, the flow control on the switch port, always flicking, sometimes enable, sometimes disable. Due to this behavior, I think could be the issue with the connection between them could try to re-established again and again.

Then I disabled flow-control from switch side, because I can not find the port settings in TPlink router.

Flow control

Enable flow control is to reduce packet dropping, but auto-negotiate can cause issue. Most of time both ends of ethernet can leave to auto-negotiate, but prefer to set one side manual if possible, especially two side has different highest speed.


Flow Control