Workstation Pro 12.5.5 - vmci.sys

Workstation Pro 12.5.5 has vmci issue specific to the release.

You have an incorrect version of driver 'vmci.sys'. Try reinstalling Vmware Workstation.

Locate the VMX file from the explorer.

Change the following within the VM VMX file.

vmci0.present = "TRUE"

TO

vmci0.present = "FALSE"

Here is the reason why VMCI set to True by default.

https://pubs.vmware.com/vsphere-51/index.jsp?topic=%2Fcom.vmware.vmci.pg.doc%2FvsockSecurity.7.3.html

RocksDB: Back up

This blog will illustrate how to backup RocksDB database with ldb too. ldb is LevelDB tool, it works the same on RocksDB as long as the Comparator is compatible. 

By default, ldb would have been built along the RocksDB.

If not, please refer to the following link for more details.
https://github.com/facebook/rocksdb/wiki/Administration-and-Data-Access-Tool#ldb-tool


Assuming the following is how I have setup my RocksDB:

Database name is "dbtest1".
RocksDB installed in "/home/ubuntu/RocksDB"
Created a backup directory to "/home/ubuntu/backup_directory"

Command to initiate ldb "ldb --db=rocksdb_dir --backup_dir=where_it_backup_to

~/rocksdb$ ./ldb --db=/home/ubuntu/dbtest1 backup --backup_dir=/home/ubuntu/backup_directory/x.bk

open db OK

open backup engine OK

create new backup OK

The backup is in a form of a bundle of  3 directories where meta contains sst. All the unnecessary files such as *.log will be omitted.

/home/ubuntu/backup_directory/x.bk

ls --full -Gg

total 12

drwxr-xr-x 2 4096 2018-04-18 12:13:38.812297927 -0700 meta

drwxr-xr-x 3 4096 2018-04-18 12:13:38.804297785 -0700 private

drwxr-xr-x 2 4096 2018-04-18 12:13:38.800297715 -0700 shared

While creating the backup make sure the RocksDB do not have any applications accessing it, otherwise, they will place a lock on it and be preventing ldb from backing up. So, the RocksDB has to be in a "closed" state. 

Failed: IO error: While lock file: /home/ubuntu/dbtest1/LOCK: Resource temporarily unavailable

That's it. 

Bulk insert a flat file from Linux to SQL Server

This blog will demonstrate how to port a flat file data retrieved from Unix environment to SQL Server.


First, create a test table with 2 columns where I will use to store my data.

USE [blockchain]

GO

/****** Object:  Table [dbo].[blockchain]    Script Date: 3/30/2018 1:12:30 PM ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE TABLE [dbo].[blockchain](

       [akey] [ntext] NULL,

       [bvalue] [ntext] NULL

) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

Verify if the blockchain table gets created correctly. 
select * from blockchain;

Performing the data porting. To let you know ahead of time, this method will fail. I will explain later on.

BULK INSERT blockchain.dbo.blockchain FROM 'C:\Users\Administrator\Desktop\blockchain\data_output2.txt' WITH (  DATAFILETYPE = 'char', FIELDTERMINATOR =':');

It would import one row because the database all look like one row in notepad even though it looks like many rows in notepad++

From Windows Notepad, the data file looks like the following. That explains it why only 1 row gets imported. The second column stores everything else. 

The flat file was ported out a Linux environment where hex requires. The ROWTERMINATOR can't be set to "\r\n" but should be a hex value of "0x0a".

After changing the parameter to the hex value. The following would works perfectly fine.

BULK INSERT blockchain.dbo.blockchain FROM 'C:\Users\Administrator\Desktop\blockchain\data_output2.txt' WITH (  DATAFILETYPE = 'char', FIELDTERMINATOR =':', FIRSTROW=1,ROWTERMINATOR = '0x0a')

What I learned, The data being ported from look like 2 column from the Notepad++ but on a notepad, all the data presented in only one whole line.  The data will get ported over but only in 1 row and the second column filled up the rest of the data.

Porting flat file into Postgresql

Understanding the postgres environment

Postgresql universally have 2 distinct parts. The binary and the data directory. The binary part contains Postgresql executable and the data contains the all the database (tables, indexes, pg_toasts and etc), transaction logs (WALs), postgresql.conf, indent (security).

The following are showing where the binary and data resides and the essential Postgres services.

Note: the binary and the data directories are highly custimizable. Different postgresql vendors may decide there get installed and reside. What's shown here is not a definitely path.

-bash-4.1$ ps -ef|egrep postgres

avahi     1791     1  0 Mar14 ?        00:00:00 avahi-daemon: running [postgres6ec-001.local]

postgres  2065     1  0 Mar14 ?        00:00:25 /usr/pgsql-9.2/bin/postmaster -p 5432 -D /var/lib/pgsql/9.2/data

postgres  2067  2065  0 Mar14 ?        00:00:00 postgres: logger process

postgres  2069  2065  0 Mar14 ?        00:00:00 postgres: checkpointer process

postgres  2070  2065  0 Mar14 ?        00:00:08 postgres: writer process

postgres  2071  2065  0 Mar14 ?        00:00:08 postgres: wal writer process

postgres  2072  2065  0 Mar14 ?        00:00:35 postgres: autovacuum launcher process

postgres  2073  2065  0 Mar14 ?        00:01:15 postgres: stats collector process

Creating basic database for testing. Roles and constraints are all omitted in this test.

-bash-4.1$ psql

psql (9.3.5, server 9.2.9)

Type "help" for help.

postgres=# create database blockchain;

CREATE DATABASE

postgres=#

Listing the database

postgres=# \l

                                  List of databases

    Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges

------------+----------+----------+-------------+-------------+-----------------------

blockchain | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |

postgres   | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |

template0  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +

            |          |          |             |             | postgres=CTc/postgres

template1  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +

            |          |          |             |             | postgres=CTc/postgres

vcdb       | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =Tc/postgres         +

            |          |          |             |             | postgres=CTc/postgres+

            |          |          |             |             | vc=CTc/postgres

(5 rows)

postgres=#

You are now connected to database "blockchain" as user "postgres".

blockchain=# \d+

No relations found.

blockchain=#

Check current connection user and db.

blockchain=# select current_user, current_database();

current_user | current_database

--------------+------------------

postgres     | blockchain

(1 row)

or

blockchain-# \conninfo

You are connected to database "blockchain" as user "postgres" via socket in "/tmp" at port "5432".

--note: column data type char(250) will not be large enough. Have to use text.

create table blockchain (

akey text,

bvalue text

);

CREATE TABLE

Making sure the dump file exist in the correct path.

blockchain-# \! pwd; ls

/var/lib/pgsql/blockchain

blocktest.txt  data_output  data_output.txt

blockchain=# \d blockchain

Table "public.blockchain"

Column | Type | Modifiers

--------+------+-----------

key    | text |

bvalue | text |

blockchain=# COPY blockchain FROM '/var/lib/pgsql/blockchain/data_output.txt' (DELIMITER(':'));

COPY 9493

blockchain=# select count(*) from blockchain;

count

-------

  9493

(1 row)

\pset columns 250

\pset format wrapped

Viewing with Pg_Admin 4

https://www.postgresql.org/ftp/pgadmin/pgadmin4/v2.1/windows/

Porting flat file into Oracle database

Porting blockchain data into Oracle database.

Example of data 

0x01000000010000000000000000000000000000000000000000030A000000000000 : 0x00000000000000000000000000000000000000000000000A25EC09444F92A7D0

0x01000000010000000000000000000000000000000000000000100A000000000000 : 0x00000000000000000000000000000000000000000000000A25EC09444F92A7D0

0x010000000100000000000000000000000000000000000000001203000000000000 : 0x00000000000000000000000000000000000000000000000735BEF46E4566A000

0x010000000100000000000000000000000000000000000000001604000000000000 : 0x00000000000000000000000000000000000000000000000796E3F357D922A000

0x010000000100000000000000000000000000000000000000001605000000000000 : 0x00000000000000000000000000000000000000000000000796E3F357D922A7D0

0x010000000100000000000000000000000000000000000000001705000000000000 : 0x00000000000000000000000000000000000000000000000796E3F357D922A7D0

0x010000000100000000000000000000000000000000000000001B01000000000000 : 0x000000000000000000000000000000000000000000000006F05B59D3B2000000

0x010000000100000000000000000000000000000000000000002A03000000000000 : 0x000000000000000000000000000000000000000000000007439FAB21ECCAA000

Step 1: Create tablespace, datafile and user

CREATE SMALLFILE TABLESPACE "BLOCKCHAIN" DATAFILE '/data/blockchain.dbf' SIZE 1G AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED LOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;

CREATE USER "BLOCKCHAIN" PROFILE "DEFAULT" IDENTIFIED BY "oracle" DEFAULT TABLESPACE "BLOCKCHAIN" ACCOUNT UNLOCK;

grant connect to blockchain;

grant resource to blockchain;

grant create table to blockchain;

grant unlimited tablespace to blockchain;

Step 2: ETL (Extract, Transform, Load) process setup on Oracle12C database. Note: Oracle11G and below works different. 

SQL> create directory user_dir as '/home/oracle/Downloads';

Directory created.

SQL> grant read, write on directory user_dir to blockchain;

Grant succeeded.

SQL> grant read, write on directory user_dir to blockchain;

Grant succeeded.

SQL> conn blockchain/oracle

Connected.

The flat file resides here and making sure it chmod 777 on Downloads directory. Oracle has tendency to fail without providing any error. 

 [oracle@centosoracle12 Downloads]$ pwd ; ls -las data_output.txt

/home/oracle/Downloads

1748 -rwxrwxrwx. 1 oracle oracle 1787548 Mar 26 16:59 data_output.txt

Creating the a table to store blockchain data. For simplicity, I am using varchar2(200) and table have absolutely no constraints just as proof of concept. I do not know if the data will be larger, if it does, we may need to use BLOB (Large Binary Object) data type which will have adverse impact to the data as it grows. Once the following completed, it would created a table with data.

create table block_load(akey varchar2(200), bvalue varchar2(200))

organization external

(default directory user_dir

access parameters

(fields terminated by ':')

location('data_output.txt')

);

Table created.

The table above it created based on external data link. I would create a real physical table out of it.

SQL> alter table block_load reject limit unlimited;

Table altered.

SQL> create table real_block_load as select* from block_load;

Table created.

Formatting a report

SQL> set lines 130;

SQL> col bvalue format a50;

SQL> col akey format a50;

Sample of the data in real block load table

SQL> select * from real_block_load where rownum < 5;

AKEY

--------------------------------------------------------------------------------

BVALUE

--------------------------------------------------------------------------------

0x01000000010000000000000000000000000000000000000000030A000000000000

 0x00000000000000000000000000000000000000000000000A25EC09444F92A7D0

0x01000000010000000000000000000000000000000000000000100A000000000000

 0x00000000000000000000000000000000000000000000000A25EC09444F92A7D0

0x010000000100000000000000000000000000000000000000001203000000000000

 0x00000000000000000000000000000000000000000000000735BEF46E4566A000

Number of total data in real block load table.

SQL> select count(*) from real_block_load;

  COUNT(*)

----------

      6960

Porting flat file to MariaDB/MySQL

Example of how to port a flat file to MariaDB/MySQL.

Sample data of the data.

MariaDB [(none)]> use mysql

Reading table information for completion of table and column names

You can turn off this feature to get a quicker startup with -A

Database changed

Create a blockchain table to host the data and verifying the table spec.

MariaDB [mysql]> create table blockchain (

    -> akey varchar(300),

    -> bvalue varchar(300)

    -> );

Query OK, 0 rows affected (0.08 sec)

MariaDB [mysql]> select count(*) from blockchain;

+----------+

| count(*) |

+----------+

|        0 |

+----------+

1 row in set (0.00 sec)

MariaDB [mysql]> desc blockchain;

+--------+--------------+------+-----+---------+-------+

| Field  | Type         | Null | Key | Default | Extra |

+--------+--------------+------+-----+---------+-------+

| akey   | varchar(300) | YES  |     | NULL    |       |

| bvalue | varchar(300) | YES  |     | NULL    |       |

+--------+--------------+------+-----+---------+-------+

2 rows in set (0.00 sec)

Load the data file with INFILE. Make sure the flat file resides in the correct location.

MariaDB [mysql]> load data infile '/home/xxxxx/datafile/data_output.txt' into table blockchain fields terminated by  ':';

Data are loading into the table.

MariaDB [mysql]> select count(*) from blockchain;

+----------+

| count(*) |

+----------+

|     9493 |

+----------+

1 row in set (0.00 sec)

MariaDB [mysql]> select * from blockchain limit 2;

+-----------------------------------------------------------------------+---------------------------------------------------------------------+

| akey                                                                  | bvalue                                                              |

+-----------------------------------------------------------------------+---------------------------------------------------------------------+

| 0x01000000010000000000000000000000000000000000000000030A000000000000  |  0x00000000000000000000000000000000000000000000000A25EC09444F92A7D0 |

| 0x01000000010000000000000000000000000000000000000000100A000000000000  |  0x00000000000000000000000000000000000000000000000A25EC09444F92A7D0 |

+-----------------------------------------------------------------------+---------------------------------------------------------------------+

2 rows in set (0.00 sec)

Verifying the relationship.

MariaDB [mysql]> select bvalue from blockchain where akey = '0x01000000010000000000000000000000000000000000000000030A000000000000';

+---------------------------------------------------------------------+

| bvalue                                                              |

+---------------------------------------------------------------------+

|  0x00000000000000000000000000000000000000000000000A25EC09444F92A7D0 |

+---------------------------------------------------------------------+

1 row in set (0.00 sec)

That's it.

Porting flat file to Redis database

Download Redis

https://redis.io/download


Install  Redis

make

start Redis Server

~/redis-4.0.9/src$ ./redis-server

Sample Data

Redis has a specific format requirement. So, the flat file will need to be processed. 'sed' can easily altering this.

sed -e 's/^/SET /;s/://g' data_output.txt > data_output_processed.txt 

Using --pipe to import the data into Redis. I was getting errors. 

~/redis-4.0.9/src$ cat ~/datafile/data_output_processed.txt | ./redis-cli --pipe

ERR unknown command 'ET'

ERR unknown command 'ET'

ERR unknown command 'ET'

ERR unknown command 'ET'

ERR unknown command '2'

ERR unknown command '$4'

ERR wrong number of arguments for 'echo' command

ERR unknown command '$20'

ERR unknown command '�Bl���'

No replies for 30 seconds: exiting.

errors: 9498, replies: 9498

Using unix2dos to alter the data_output_processed.txt

unix2dos ~/datafile/data_output_processed.txt

unix2dos: converting file /home/xxxxxx/datafile/data_output_processed.txt to DOS format ...

Attempt to import the data with --pipe again and it works.

~/redis-4.0.9/src$ cat ~/datafile/data_output_processed.txt | ./redis-cli --pipe

All data transferred. Waiting for the last reply...

Last reply received from server.

errors: 0, replies: 9493

Query the data with redis-cli --scan

~/redis-4.0.9/src$ ./redis-cli --scan --pattern '*'

0x010000000154DAEB3E8A6BBC797E4AD2B0339F134B186E46376D0B000000000000

0x0100000001DC2D15A69F6BB33B246AEF40450751C2F6756AD28904000000000000

0x01000000013FE698DDD00CE16BFB0F2B875D29CF6B91EB677DB501000000000000

0x010000000157BDDF078834009C89D88E6282759DC45335B4707E07000000000000

0x020000003504000000000000

0x02000000F908000000000000

0x0100000001EB5F44D72343DF39680AAF77B2CED6586E6918C2090A000000000000

0x0100000003589EA787B46DA08EC8FC081678335C5D0081010EBA03000000000000

0x02000000BB02000000000000

0x010000000132BE343B94F860124DC4FEE278FDCBD38C102D88EC0D000000000000

0x01000000016CF94E97B229262AF23908265C2CF7E810686FA7D409000000000000

0x0100000001E6A7A1D47FF21B6321162AEA7C6CB457D5476BCAEF02000000000000

0x020000009B03000000000000

0x01000000012910543AF39ABA0CD09DBB2D50200B3E800A63D29705000000000000

0x010000000154DAEB3E8A6BBC797E4AD2B0339F134B186E46370D0B000000000000

0x02000000CB07000000000000

Query the data with redis-cli on single key.

~/redis-4.0.9/src$ ./redis-cli

127.0.0.1:6379> get 0x01000000012910543AF39ABA0CD09DBB2D50200B3E800A63D29705000000000000

"0x000000000000000000000000000000000000000000009B2550188E70759173F2"

127.0.0.1:6379>

That's it.