Nav Aulakh's Blog


VNC startup script with XFCE

Nov. 15, 2020

I was not able to get VNC working with the default `xstartup` script created by running `vncserver`. So, I used these modifications to make it work.

#!/bin/bash
unset SESSION_MANAGER
unset DBUS_SESSION_BUS_ADDRESS
startxfce4 &

[ -x /etc/vnc/xstartup ] && exec /etc/vnc/xstartup
[ -r $HOME/.Xresources ] && xrdb $HOME/.Xresources
xsetroot -solid grey
vncconfig -iconic &

Using iSCSI disk with VirtualBox VM

Oct. 21, 2020

Assuming you have a configured and ready-to-use iSCSI target, you can use the following snippet to attach it to a VirtualBox VM.

VBoxManage storageattach "ubuntu" --storagectl "SATA" \
--port 0 --device 0 --type hdd --medium iscsi --server 10.1.1.202 \
--target "iqn.2005-10.org.freenas.ctl:freenas-iscsi-target" \
--tport 3260 --lun 0

In the above snippet:

  • "ubuntu" refers to the virtual machine name.
  • "10.1.1.202" is the IP address of my Network Attached Storage.
  • "iqn.2005-10.org.freenas.ctl:freenas-iscsi-target" is a combination of base name and target name.
  • "--lun 0" is the Logical Unit Number of the share.


I had issues running Ubuntu in legacy boot mode using iSCSI but switching to UEFI made it work.



Greatest-n-per-group Query Example

July 21, 2020

QUERY = """
CREATE TABLE purchase_orders (
  id INT NOT NULL AUTO_INCREMENT,
  number VARCHAR(255) NOT NULL,
  PRIMARY KEY(id)
) ENGINE INNODB;


CREATE TABLE email_messages (
  id INT NOT NULL AUTO_INCREMENT,
  from_address VARCHAR(255) NOT NULL,
  to_address VARCHAR(255) NOT NULL,
  subject VARCHAR(255),
  body TEXT,
  PRIMARY KEY(id)
) ENGINE INNODB;


CREATE TABLE purchase_order_emails (
  purchase_order_id INT NOT NULL,
  email_id INT NOT NULL,
  `status` ENUM('pending', 'delivered', 'bounced'),
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY(purchase_order_id, email_id, `status`),
  FOREIGN KEY (purchase_order_id) REFERENCES purchase_orders (id),
  FOREIGN KEY (email_id) REFERENCES email_messages (id)
) ENGINE INNODB;
  
  
INSERT INTO purchase_orders VALUES (NULL, "PF1");

INSERT INTO email_messages VALUES (NULL, "joe@example.com", "vendor1@example.net", "PO - PF1", "This is my purchase order");
INSERT INTO purchase_order_emails VALUES (1, 1, "pending", NULL);

INSERT INTO email_messages VALUES (NULL, "joe@example.com", "vendor2@example.net", "PO - PF1", "This is my purchase order");
INSERT INTO purchase_order_emails VALUES (1, 2, "pending", NULL);

SELECT SLEEP(2);
INSERT INTO purchase_order_emails VALUES (1, 2, "delivered", NULL);
SELECT SLEEP(3);
INSERT INTO purchase_order_emails VALUES (1, 1, "bounced", NULL);

INSERT INTO purchase_orders VALUES (NULL, "PF2");
SELECT SLEEP(1);
INSERT INTO email_messages VALUES (NULL, "jane@example.net", "john@example.com", "PO - PF2", "Please deliver this.");
INSERT INTO purchase_order_emails VALUES (2, 3, "pending", NULL);
"""

DCL

QUERY = """
SELECT 
  purchase_orders.*, 
  email_messages.to_address AS email_address,
  purchase_order_emails.status AS email_status, 
  purchase_order_emails.created_at AS email_status_updated_at
FROM purchase_orders
LEFT OUTER JOIN purchase_order_emails 
  ON purchase_order_emails.purchase_order_id = purchase_orders.id
INNER JOIN email_messages ON email_messages.id = purchase_order_emails.email_id
WHERE purchase_order_emails.created_at = (
  SELECT MAX(created_at) FROM purchase_order_emails 
  WHERE purchase_order_id = purchase_orders.id AND email_id = email_messages.id
);


SELECT 
  purchase_orders.*,
  GROUP_CONCAT(
    email_messages.to_address, 
    ' (', 
    purchase_order_emails.status,
    ' at ',
    purchase_order_emails.created_at,
    ')'      
    ORDER BY purchase_order_emails.created_at ASC
    SEPARATOR ', '
  ) AS emails
FROM purchase_orders
LEFT OUTER JOIN purchase_order_emails 
  ON purchase_order_emails.purchase_order_id = purchase_orders.id
INNER JOIN email_messages ON email_messages.id = purchase_order_emails.email_id
WHERE purchase_order_emails.created_at = (
  SELECT MAX(created_at) 
  FROM purchase_order_emails 
  WHERE purchase_order_id = purchase_orders.id 
    AND email_id = email_messages.id
)
GROUP BY purchase_orders.id;
"""

Port forwarding using SSH tunneling

April 22, 2019

When you need to expose a service to the internet or an outside network but you don't have control over your local NAT or port-forwarding, you can use SSH to create a reverse tunnel.

For this you will need a VPS instance to which you have root access and VPS is securely connected to the internet. You will need to enable the following in `/etc/ssh/sshd_config`.

AllowTcpForwarding yes
GatewayPorts yes

From your local machine, you can now use the following command to create port forwarding using SSH tunnel.

ssh -R 8080:localhost:8000 -N user@remote_server

Service running on localhost:8000 would now be accessible through remote_server:8080



Create a file with multiple lines using `cat`

Oct. 12, 2018

cat > /home/nav/afile << EOL
This
is
new
content

EOL

MySQL: Query tables across multiple databases

March 3, 2018

DROP PROCEDURE IF EXISTS `MultiDBQuery`;
DELIMITER $$

CREATE PROCEDURE `MultiDBQuery`(
	IN param_table_name VARCHAR(255) CHARACTER SET utf8
)
BEGIN

    DECLARE database_name   VARCHAR(250);
    DECLARE exit_loop       BOOLEAN;
    DECLARE union_query     TEXT DEFAULT '';

    DECLARE all_databases CURSOR FOR 
        SELECT DISTINCT `table_schema`
        FROM `information_schema`.`tables`
        WHERE `table_name` = param_table_name;

    DECLARE CONTINUE HANDLER
        FOR NOT FOUND SET exit_loop = TRUE;

    OPEN all_databases;

    get_database: LOOP
        FETCH all_databases INTO database_name;

        IF exit_loop THEN
            -- remove trailing UNION ALL statement
            SET union_query = TRIM(TRAILING ' UNION ALL ' FROM union_query);
            LEAVE get_database;
        END IF;

	SET union_query = concat(union_query, 'SELECT * FROM `', database_name, '`.`', param_table_name, '` UNION ALL ');

      END LOOP get_database;

    CLOSE all_databases;

    SET @final_query = concat('SELECT * FROM (', union_query, ') AS results;');
    PREPARE statement1 FROM @final_query;   
    EXECUTE statement1;
    DEALLOCATE PREPARE statement1;

END$$

DELIMITER ;

CALL MultiDBQuery('your_table_name');

Adapted from https://stackoverflow.com/a/22500171


Convert a table with same name in multiple databases to UTF8

March 3, 2018

DROP PROCEDURE IF EXISTS `ConvertTableToUTF8`;
DELIMITER $$

CREATE PROCEDURE `ConvertTableToUTF8`(
	IN param_table_name VARCHAR(255) CHARACTER SET utf8
)
BEGIN

	DECLARE database_name VARCHAR(250);
	DECLARE all_databases CURSOR FOR 
		SELECT DISTINCT `table_schema`
			FROM `information_schema`.`tables`
			WHERE `table_name` = param_table_name;

	OPEN all_databases; 

	get_database: LOOP
		FETCH all_databases INTO database_name;
		SET @prep_query = CONCAT('ALTER TABLE `', database_name, '`.`', param_table_name, '` CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci'); 
		PREPARE statement1 FROM @prep_query ; 
		EXECUTE statement1; 
		DEALLOCATE PREPARE statement1;
	END LOOP get_database; 
	CLOSE all_databases; 
END$$

DELIMITER ;

CALL ConvertTableToUTF8('your_table_name');

Clustering similar objects with PostgreSQL

Feb. 15, 2018

CREATE TABLE transaction_transaction (
	date date not null,
	description varchar(255) not null
);

---

WITH lags AS (
	SELECT date, description, LAG(description) OVER (ORDER BY description) AS lag
	FROM transaction_transaction WHERE description != ''
	ORDER BY description
)
SELECT CASE WHEN
	(SIMILARITY(description, lag) < 0.2)
	THEN description
	ELSE '- ' || description
	END, date
FROM lags ;

Move workspaces when external monitor is disconnected

Dec. 30, 2017

xrandr --output HDMI-1 --off

Create a new Postgresql database

July 27, 2017

CREATE DATABASE name
    [ [ WITH ] [ OWNER [=] user_name ]
           [ TEMPLATE [=] template ]
           [ ENCODING [=] encoding ]
           [ LC_COLLATE [=] lc_collate ]
           [ LC_CTYPE [=] lc_ctype ]
           [ TABLESPACE [=] tablespace ]
           [ CONNECTION LIMIT [=] connlimit ] ]

Example:

CREATE DATABASE name WITH OWNER username ENCODING utf8;

Create a new MySQL database

June 15, 2017

CREATE DATABASE `my-db` CHARACTER SET utf8 COLLATE utf8_unicode_ci;

Removed files from repository based on .gitignore

June 14, 2017

git rm --cached `git ls-files -i -X .gitignore`

Create a new MySQL user

June 4, 2017

CREATE USER 'nav'@'localhost' IDENTIFIED BY 's3kr1t';
CREATE USER 'nav'@'%' IDENTIFIED BY 's3kr1t';

GRANT ALL ON *.* TO 'nav'@'localhost';
GRANT ALL ON *.* TO 'nav'@'%';

FLUSH PRIVILEGES;

Categories