Fast import of billion lines to postgresql

This is the fastest way I've found about importing huge (3+ billion lines) text files in csv-like format in PostgreSQL. It requires python3 and psycopg2

#!/usr/bin/python3
#
# initial stuff by Cac Ko <email@gdpr_protected_stuff> 
# additional by <geroy@horizon9.org>
#
# import very large cvs-like file in postgre database
# 01.02.2019
#
# requires psycopg2 postgresql lib for python
# file format is:
# ----------------------------
# something12:otherthing2345
# something7134:otherthing4243
# .....
# License: Public Domain

import os
import sys
import psycopg2
from psycopg2.extras import execute_values

# use file in tmpfs for faster read/write
POS_FILE = '/run/.position'

# get current position from POS_FILE location
def getPosition():
    result = 0
    if os.path.exists(POS_FILE):
        with open(POS_FILE, 'rb') as f:
            try:
                result = int(f.read())
                print("result = %d" % result)
            except Exception as error: 
                print("Error read():", error)
                result = 0
    return result

# write to this file after each commit()
def storePosition(pos):
    with open(POS_FILE, 'w') as f:
        f.write(str(pos))

# uncomment the following 3 lines if you want perentage / position on stdout
#    y = pos/whole*100
#    print('{0:.2f}%'.format(y), end = '')
#    print(" pos=%s" % (str(pos)))


# store stuff in database
def storeInDb(line, bulk_data):
#    print(line)
    x = []
    x = line.split(':')
    sql = b'INSERT into secrets (somestuff1, somestuff2) VALUES %s'
    try:
        execute_values(cur, sql, bulk_data)
        connection.commit()
    except Exception as error:
        print("Error pri INSERT", error)
        pass

if __name__ == '__main__':
    try:
        if len(sys.argv) < 2:
            print('usage: ./large-file-import.py filename-to-import.csv')
            exit
        f = open(sys.argv[1], 'r', buffering=2000000, errors='replace')
        whole = os.path.getsize(sys.argv[1])
        pos = getPosition()
        print("Start reading from pos=%d" % pos)
        f.seek(pos)
        line = f.readline()
        try:
            bulk_count = 0;
            connection = psycopg2.connect(
                user="postgres", password="somestuff", host="127.0.0.1", port="5433",
 database="postgres")
            cur = connection.cursor()
            bulk_data = []
            split_line = []
            while line:
                
                split_line = line.split(':')
                try:
                    l = tuple(split_line)
                    bulk_data.append(l)
                except (Exception) as error:
                    print(error)
                    pass
                
                storePosition(f.tell())
                line = f.readline()
                bulk_count = bulk_count + 1
                # store and commit in db after X lines
                if bulk_count == 9000:
                    storeInDb(line,bulk_data)
                    bulk_count = 0;
                    bulk_data = []

                #connection.commit()
        except (Exception, psycopg2.Error) as error:
            print("Error while connecting to PostgreSQL", error)
        finally:
            if(connection):
                cur.close()
                connection.close()
                print("PostgreSQL connection is closed")

    except KeyboardInterrupt:
        storePosition(f.tell())
        if(connection):
            cur.close()
            connection.close()
            print("PostgreSQL connection is closed")

I am too lazy to explain how this script works and probably there are some errors but it works for me.

xorg.conf ModeLine for VGA connected ViewSonic monitors

Even with latest Linux distributions you can face the problem of Xorg not detecting your monitor.

My monitor is ViewSonic VG2030wm and it is capable of working at 1680x1050 resolution but Xorg can't detect that and highest resolution that I can use is 1024x768.

You need to add ModeLine so the Xorg can recognize it. Here is my Monitor section from xorg.conf:

Section "Monitor"
        Identifier   "ViewSonic"
        VendorName   "ViewSonic"
        ModelName    "VG2030wm"
        HorizSync    24.0 - 92.0
        VertRefresh  50.0 - 85.0
        Option       "DPMS"
        ModeLine "1680x1050" 170.00 1680 1784 1960 2240 1050 1053 1059 1089 +hsync +vsync
EndSection

Probably you will need just the ModeLine row but I am publishing the whole Monitor section.

You can find more ModeLine for certain monitors here: https://www.mythtv.org/wiki/Modeline_Database

Android 4.2.2 how to set default install path to external SD card

You don't need root access to do this. All you need is to enable Developer Options and then enable USB debugging. Once this is done connect adb to your phone:

root@bender:~# adb shell

shell@android:/ $ pm get-install-location
0[auto]
shell@android:/ $ pm set-install-location 2
shell@android:/ $ pm get-install-location
2[external]
shell@android:/ $ logout

root@bender:~#

as you can see from pm help page:

pm get-install-location: returns the current install location.
    0 [auto]: Let system decide the best location
    1 [internal]: Install on internal device storage
    2 [external]: Install on external media

You need to set this value to 2

Jboss / Wildfly errors when accepting socket java.io.IOException: Bad file descriptor problem

Jboss/Wildfly random crashes under heavy load:

Symptoms:

Jboss/Wildfly crashes randomly with  java.io.IOException: Bad file descriptor. Undertow HTTp server does not restart but admin console and everything else is working and nothing in log files.

How to debug it:

Put this in your configuration file (standalone.xml for Wildfly)

        <logger category="org.xnio.nio">
        <level name="DEBUG"/>
        </logger>

The error:

2018-03-03 17:19:15,273 DEBUG [org.xnio.nio.tcp.server] (default Accept) Exception accepting request, closing server channel TCP server (NIO) <69a407dd>: java.io.IOException: Bad file descriptor
    at sun.nio.ch.IOUtil.configureBlocking(Native Method)
    at sun.nio.ch.ServerSocketChannelImpl.accept(ServerSocketChannelImpl.java:264)
    at org.xnio.nio.QueuedNioTcpServer.handleReady(QueuedNioTcpServer.java:477)
    at org.xnio.nio.QueuedNioTcpServerHandle.handleReady(QueuedNioTcpServerHandle.java:38)
    at org.xnio.nio.WorkerThread.run(WorkerThread.java:591)

What is the reason for that:

The reason is that you are probably running a BSD and the bug is in Java implementation of KQueueSelectorImpl.java in latest OpenJDK for BSD (openjdk-1.8.162 at the moment). This was tested on NetBSD 7.1.

Solution:

There is no solution at the moment. You need to move your Jboss/Wildfly to Linux. You can try changing default selector with java command line arguments (example is solution for Solaris):

java -Dxnio.nio.selector.provider=sun.nio.ch.PollSelectorProvider .......

At the moment the only solution to this is not to run it under BSD. Under Linux with EPollSelectorProvider everything works just fine.

How to filter 99.99% of ssh brute force attacks

Recently I've decided to experiment with ssh ciphers / key exchange algorithms to raise the security of my servers. This is the /etc/ssh/sshd_config I've got:

HostKey /etc/ssh/ssh_host_ed25519_key
Ciphers chacha20-poly1305@openssh.com
KexAlgorithms curve25519-sha256@libssh.org

If you don't have HostKey for Ed25519 generate it:

# ssh-keygen -t ed25519

You need to use recent version of ssh / pyTTY to be able to login to this server.
It seems that using only this Cipher/Kex filters all brute force scanners probably because they do not support it. I see only this kind of messages:

Feb 13 14:41:39 horizon9 sshd[22849]: SSH: Server;Ltype: Version;Remote: xxx.x.xx.xxx-53810;Protocol: 2.0;Client: libssh2_1.7.0
Feb 13 14:41:39 horizon9 sshd[22849]: fatal: ssh_dispatch_run_fatal: no matching cipher found [preauth]

More information about ciphers/algorithms read here:

https://cr.yp.to/ecdh.html#curve25519-paper
https://en.wikipedia.org/wiki/Salsa20
https://en.wikipedia.org/wiki/Poly1305

Mounting bootable image file under linux with offset

Use fdisk to see the partitions and their offset of the image file:

# fdisk -l router-devuan-jessie-diskless-2017.img
Disk router-devuan-jessie-diskless-2017.img: 3.7 GiB, 3980394496 bytes, 7774208 sectors
Units: sectors of 1 * 512 = 512 bytes
Sector size (logical/physical): 512 bytes / 512 bytes
I/O size (minimum/optimal): 512 bytes / 512 bytes
Disklabel type: dos
Disk identifier: 0x7b19da02

Device                                                 Boot Start     End Sectors  Size Id Type
router-devuan-jessie-diskless-2017.img    *     2048 7772159 7770112  3.7G 83 Linux

According to fdisk each sector of the image has 512 bytes. We need that so we can calculate the right offset when mounting it.

The image has one linux partition starting at offset of 2048. The right offset is 512*2048.

Here is how to mount it:

# mount -o loop,offset=$((2048*512)) router-devuan-jessie-diskless-2017.img /mnt/

Mediatek LinkIt Smart 7688 with DHT22/11 sensor on custom LEDE/OpenWRT image

Examples will be with LEDE-Project latest sources from git.

check-humidity-7688 is a fork of Onion Omega2p checkHumidity which reads temperature values directly from memory.

We need LEDE Project / OpenWRT source configured for LinkIt Smart 7688 and compiled at least once. Kernel should be compiled with /dev/mem support as this is the way check-humidity-7688 works.

The checkHumidity binary should work on mediatek 7688 without problems but if you need to recompile it for different arch/platform here is how it is done:

getting the source and compiling it:

$ git clone https://github.com/devane/check-humidity-7688
$ cd check-humidity-7688
$ make clean
$ sh xCompile.sh -buildroot /home/user/lede

Cleaning...
 rm -f -r build bin
 /home/user/lede/staging_dir/toolchain-mipsel_24kc_gcc-5.4.0_musl/bin/mipsel-openwrt-linux-g++ -c -g  -I include -c -o build/fastgpioomega2.o src/fastgpioomega2.cpp
 /home/user/lede/staging_dir/toolchain-mipsel_24kc_gcc-5.4.0_musl/bin/mipsel-openwrt-linux-g++ -c -g  -I include -c -o build/main.o src/main.cpp
 /home/user/lede/staging_dir/toolchain-mipsel_24kc_gcc-5.4.0_musl/bin/mipsel-openwrt-linux-g++ -c -g  -I include -c -o build/module.o src/module.cpp
 /home/user/lede/staging_dir/toolchain-mipsel_24kc_gcc-5.4.0_musl/bin/mipsel-openwrt-linux-g++ -c -g  -I include -c -o build/dht_read.o src/dht_read.cpp
 /home/user/lede/staging_dir/toolchain-mipsel_24kc_gcc-5.4.0_musl/bin/mipsel-openwrt-linux-g++ -c -g  -I include -c -o build/common_dht_read.o src/common_dht_read.cpp
 Linking...
 /home/user/lede/staging_dir/toolchain-mipsel_24kc_gcc-5.4.0_musl/bin/mipsel-openwrt-linux-g++ build/fastgpioomega2.o build/main.o build/module.o build/dht_read.o build/common_dht_read.o -o bin/checkHumidity

We can check if it is compiled for the right architecture:

$ file bin/checkHumidity
bin/checkHumidity: ELF 32-bit LSB executable, MIPS, MIPS32 rel2 version 1 (SYSV), dynamically linked, interpreter /lib/ld-musl-mipsel-sf.so.1, with debug_info, not stripped

Now just copy the binary on your mt7688 and it should work just fine.

Notes:

xCompile.sh is a shell script (included in source) from MediaTek documentation page on how to cross-compile your own program. You should only point where is the source of the LEDE-Project / OpenWRT with -buildroot option.

Wiring diagram connecting DHT11 sensor to LinkIt Smart 7688, signal (data) wire is connected to pin 26 - GPIO19.

DHT11 Pinout

Example output of checkHumidity:

root@LEDE:~# ./checkHumidity 19 DHT11
33.000000
26.000000