How to load CSV file to Mysql database

There some tricks to know how to load csv file to mysql:

1. mysql -u <user> -p --local-infile
   (the --local-infile is necessary, otherwise you will get error during dataloading: ERROR 1148 (42000): The used command is not allowed with this MySQL version)
2. use <database>
3. create table script (create_tables.sql) in text editor
   drop table kontakt;
   create table kontakt
    (klkood        bigint(20)
    ,kontakti_liik varchar(32)
    ,vaartus       varchar(100)
    ,kasutusluba   varchar(100)
    ,keeluaeg      varchar(100)
   );
   create index kontakt_klkood on kontakt(klkood);
4. run the script from mysql prompt
   .\ create_tables.sql
5. create load data script (load_data.sql) in text editor
   LOAD DATA LOCAL INFILE '/vagrant/uptime/EA_kliendi_kontaktinfo_3012.csv' INTO TABLE kontakt
   FIELDS TERMINATED BY ';'
   LINES STARTING BY '"' TERMINATED BY '"\r\n'
   IGNORE 1 LINES;
6. run the script from mysql prompt
   .\ load_data.sql
7. If there are warnings then execute
   show warnings

For me most difficult part was to get load_data.sql to work and specially --local-infile parameter.

How to install mysql and mysql workbench in Ubuntu 12.04

Its really simple task to install mysql server and mysql workbench in Ubuntu 12.04. You shuold run from commandline



sudo apt-get install mysql-server
sudo apt-get install mysql-workbench

First row installs mysql server, asks root password and starts mysql service. To restart server run

sudo service mysql restart

Second row installs mysql-workbench and you are ready to access mysql server in localhost.

How to make Ubuntu Desktop Vagrant compliant

Its seems me reasonable to have Vagrant compliant Ubuntu Desktop 12.04 even I will not start it from commandline. These steps are necessary:

1. Create default user as vagrant/vagrant in Ubuntu setup
2. Add admin group
   groupadd admin
   useradd -a -G admin vagrant
3. update sudoers (sudo visudo; and add these lines
   Defaults:vagrant !requiretty
   Defaults env_keep = "SSH_AUTH_SOCK"
   %admin   ALL=(ALL:ALL) NOPASSWD: ALL
4. Add a port forwarding rule in Virtualbox NAT as follows:
       Name: SSH
       Protocol: TCP
       Host IP:
       Host Port: 2222
       Guest IP:
       Guest Port: 22
5. Install and configure OpenSSH Server
   sudo apt-get install -y openssh-server
   sudo vi /etc/ssh/sshd_config
   and add/modify these lines:
   Port 22
   PubKeyAuthentication yes
   AuthorizedKeysFile %h/.ssh/authorized_keys
   PermitEmptyPasswords no
   PasswordAuthentication no
   
   restart ssh server
6. Download vargant public and private key
   $ mkdir -p /home/vagrant/.ssh
   $ chmod 0700 /home/vagrant/.ssh
   $ wget --no-check-certificate \
       https://raw.github.com/mitchellh/vagrant/master/keys/vagrant.pub \
       -O /home/vagrant/.ssh/authorized_keys
   $ chmod 0600 /home/vagrant/.ssh/authorized_keys
   $ wget --no-check-certificate \
       https://raw.github.com/mitchellh/vagrant/master/keys/vagrant \
       -O /home/vagrant/.ssh/id_rsa
   $ chmod 0600 /home/vagrant/.ssh/id_rsa
   $ chown -R vagrant /home/vagrant/.ssh
7. Remove file to fix network issues afterward
   sudo rm -f /etc/udev/rules.d/70-persistent-net.rule
8. Comment out HWADDR row in /etc/sysconfig/network-scripts/ifcfg-eth1 (to fix https://github.com/mitchellh/vagrant/issues/1777 issue)

After that you are ready to create new vagrant box. There are several articles describing same process in depth.

Quick file search in Ubuntu

Quick answer to this simple question is

 locate <filename>

and to update slocate database

sudo updatedb

As always there is also long answer.

Install postgres, pgadmin in Ubuntu 12.04

Installing postgres database to Ubuntu 12.04 run

1. sudo apt-get install postgresql
2. sudo apt-get install postgresql-contrib
3. sudo apt-get install pgadmin3

There is nice article what you can to do further in postgres database.

To allow connection to postgres database from host computer these steps are necessary:

1. In VirtualBox NAT Advanced settings add port forwarding 5432 -> 65432
2. In guest start sudo vim /etc/postgresql/9.1/main/pg_hba.conf and add these lines
   host    all    all             192.168.0.0/16          md5
   host    all    all             10.0.0.0/8              md5
3. sudo vim /etc/postgresql/9.1/main/postgresql.conf
   listen_addresses = '*'
4. sudo -u postgres psql and execute
   create user vagrant with password 'vagrant';
   create database pentaho;
   GRANT ALL PRIVILEGES ON DATABASE pentaho to vagrant;
   alter user vagrant with superuser;
   \c pentaho
   CREATE SCHEMA repo;
   CREATE SCHEMA target;
   
5. In host pgadmin III create new connection
   Name: local
   Host: localhost
   Port: 65432
   Maintenance DB: pentaho
   Username: vagrant
   Password: vagrant

Add shared folder in VirtualBox

In Win7 host, Ubuntu 12.04 guest I tried to add Shared folder, but there are some simple tricks to know.

1. Add shared folder in VirtualBox (Settings -> Shared Folder -> Machine Folders)
2. Make sure that the additions are installed, and that you have added your username as a member of the vboxsf group (sudo usermod -aG vboxsf <your username>).
3. After restart shared folder  /media/sf_shared should be in place

How to install manually guest additions in linux
$ sudo apt-get install build-essential module-assistant linux-headers-$(uname -r) dkms -y
$ wget -c http://download.virtualbox.org/virtualbox/4.0.16/VBoxGuestAdditions_4.0.16.iso
$ sudo mount VBoxGuestAdditions_4.0.16.iso -o loop /mnt

Install Oracle Java 7 to Ubuntu 12.04

Easiest way to do it is to run in terminal

sudo add-apt-repository ppa:webupd8team/java
sudo apt-get update
sudo apt-get install oracle-java7-installer

Nice thing is that it keeps java version up-to-date. There is longer explanation with different alternatives in Ubuntu site.

Optional: add to .bashrc
export JAVA_HOME=/usr/lib/jvm/java-7-oracle

Install Ubuntu Desktop in Virtualbox

My idea was to use Vagrant to install and start Ubuntu Desktop. First step is to downloaded Vagrant and then start command line and move into new empty directory.  Next step is to download Ubuntu server

vagrant box add ubuntu64-13.04 http://cloud-images.ubuntu.com/vagrant/saucy/current/saucy-server-
cloudimg-amd64-vagrant-disk1.box

and start it

vagrant init ubuntu64-13.04
vagrant it


ssh into new server and install graphical user interface


vagrant ssh
sudo apt-get install ubuntu-desktop

allow graphical user interface in configuration file Vagrant


config.vm.provider :virtualbox do |vb|
  vb.gui = true
  vb.customize ["modifyvm", :id, "--memory", "1024"]
end

restart the system


vagrant reload

Actually it came out that Ubuntu Desktop graphics is really slooow, so I give up and started install Ubuntu Desktop without Vargant from scratch. Lesson that I learned today: dont use hammer when you have screws. Vargant is not meant to use with graphical desktop.

 

How to check that site is down.

I had problem with Zulutrade website and I try to figure out is problem in my pc/office network or is server actually down. I found simple service http://www.isitdownrightnow.com, where everybody who has problems can report.
Nice service and it works!

Installing ODI 11.1.1.7.0 on Windows 7 64bit

It sould be easy to install any software nowadays, but it is not. Specially painful is installation of packages from big companies, because these are huge packages with lot of dependencies.

My last challenge was to install Oracle ODI Studio on my laptop and it was real problem to even start the installer. After some investigation I find this article from google, that solve my problem.

Some important points that I discovered:

1. Win7 64bit jre and oracle installer are working together (despite some posts that claim otherwise)
2. Command order to start Oracle ODI installer
   run cmd.exe as Administrator (It does not work without it, despite installer asks Administrator password)
   setup.exe -jreLoc C:\Progra~1\Java\jdk1.7.0_29

Honestly I am disappointed that this information is not provided in Oracle readme :-(

Nexus 4 upgrade JWR66V -> JWR66Y

I have rooted Nexus 4 with Clockmod Recovery and there are always problems with OTA upgrades. After JWR66Y upgrade, which actually go smoothly. The problem is that SuperSU will be overwritten during upgrade and its not accessible anymore.

There is many articles in internet about this issue, but this one is really short description and it works :-). Another question - is rooting really needed, because it can be also security issue, but this is another story.

How to create new play2 project in github

Duplicate Postgres database

It is unbelievable how easy is to create copy of Postgres database. The only command you need is

CREATE DATABASE newdb WITH TEMPLATE originaldb OWNER dbuser

There are 2 possible issues regarding to command above:

1. In some cases database name must be quoted (In my case

   CREATE DATABASE "sirene-migration-testing2" 
     WITH TEMPLATE "sirene-migration-testing" OWNER dbuser;

2. If some other connections are active in source database then error will be thrown (ERROR: source database "sourcedb" is being accessed by other users). There is simple script to kill all other connections in postges database except current connection
   
   In PostgreSQL 9.2 and above, to disconnect everything except your session from the database you are connected to:
   
   SELECT pg_terminate_backend(procpid)
   FROM pg_stat_activity
   WHERE datname = current_database()
     AND procpid <> pg_backend_pid();
   

Virtualbox Ubuntu 12.04 admin privilegs problem

I have Ubuntu 12.04 Virtualbox guest in Win7 

I get admin privilegs problem after executing
sudo usermod -G vboxsf jack

I dont had admin(sudo) privilegs anymore and to fix it I found solution, which needed some improvements

1. boot to recovery mode during Ubuntu Virtualbox startup holding down SHIFT key
2. login as root and execute
3. mount -o remount,rw /
4. usermod -a -G sudo jack
5. reboot

HTC Desire Bravo tweaking

In previous post I wrote about my long way to install Cyanogenmod 7.2.0.1 ROM to HTC Desire Bravo. In this post I wrote about my experience how to tweak Android 2.3.7 further.

1. ADW.Launcher is default launcher application for Cyanogenmod. It's ok launcher but old version was installed so I was not able to add widgets to screen. To upgrade ADW.Launcher to latest version is not easy task as somebody can expect:

• install latest launcher version (trick is that sdcard cannot be used for installation because otherwise launcher is not accessible during bootup and I restored ROM several times before I get it);
• remove old version with Titanium backup, because old version is in /system/app directory, that must be mounted r/w first;
• hold down home button, check "default application" and choose AWD Launcher new version

There is also same problem with widgets as launcher - to be added in list of available widgets these must be installed in phone storage. Side knowledge - its really easy to try/switch to any other application launcher. After downloading launcher press home button and select from list new launcher you have installed.

2. After installation and tweaking I got call from my sister and I was really upset if after call I had black screen and any button does not change this situtation. Only solution was to remove battery and put it back. Fortunately there was also solution to this problem - "working fix for the proximity sensor in-call problem".

3. I want to remove splash screen to stock HTC version and there was solution.

4. There are lot of applications for every single need and its not easy task to find right one. There is list of my favourite tools:

• Superuser - MUST have app, for su access;
• FX with root access - best file explorer with text file editor;
• Titanium Backup - best tool to remove applications;
• ADW.Launcher - lightweight application launcher with lot of configuration options.

5. Conclusion
Mobile phone results after upgrade and tweaking

Cons:

• battery life is shorter (I will try to calibrate)
• phone storage has same limits (so low space problem is soon back again - i dont use Link2SD any more) 

Pros:

•  Android 2.3.7 vs Android 2.2 with better interface (expect contacts) and a lot more options to configure;
• ADW.Launcher vs HTC Sense - nothing to compare;
• tons of widgets vs HTC widgets - you get what you want;
• GPS positioning is working instantly.

Main thing that I learn during last 3 days - how to install custom ROM and recover system. In future I know that nowadays it is quite easy to install custom rom with different tweaks (if you have enough free time :-))

HTC Desire Bravo Cyanogenmod 7.2.0.1 ROM installation

I have never tried install any custom ROM, because I know that after upgrade there can be lot of problems and only internet can help you :-) I thought that it is wise to "not break anything if it works". From other side there were some existing small problems (black screen during call so it was not possible to choose any numbers for answering, old HTC Sense interface, ...) with my phone and if my colleague came and insested to upgrade then I thought - Why not.

Cyanogenmod 7.2.0.1 ROM was ROM that my colleague use and he has very positive experience so it was easy to decide which ROM to install. I installed ROM Manager from Google Play (despite some negative comments) and from ROM Manager I installed ClockMod Recovery and downloaded HTC Desire Bravo Cyanogenmod 7.2.0.1 ROM to my sdcard.

I backup my existing configuration and started upgrade. Problem occurs when Cyanogenmod first time bootup - new splash screen runs and runs and runs ... I was really disappointed and first thing that come to mind was restore from backup. Afterward I know this was mistake, but you must learn from you own experience.

After restoring from backup system bootup, but in really bad shape:
WIFI - ok,
Mobile network - ok,
sdcard - not recognised
usb - unkown device from win7
So I was ready that in worst case I must buy new mobile phone.

In such cases google search is your best friend, but you must be lucky guy to find right article to your problem (and if this article is not in top20 then you will not find it). This day I was lucky guy :-). Article that save my HTC Desire - "USB brick" fix.
After running "fastboot oem enableqxdm 0" and reboot I was able to connect to my sdcard and usb - long step forward :-)

Of course it take much longer to find solution - actually 1 day and there were lot of other things that I try
1. Is sdcard accessible from sdcard reader - of course it was
2. Maybe problem is 2 primary partitions in sdcard - so I do repartioning to 1 4GB FAT32 partition which does not help to recognize my sdcard from phone
3. Some other steps in wrong direction ...

I tried to install Cyanogenmod 7.2.0.1 ROM once again and got same Cyanogenmod start splash screen. Short googling gave me tutorial "How to get out of a bootloop" and it does what it promise!!

From positive side I know much more about Android Custom ROM-s, negative side is that I loose almot 2 days and its not finished yet.

Read next article: HTC Desire Bravo tweaking

Low space problem with my old HTC Desire

I had long time low space problem with my old HTC Desire, so I was forced on to ignore all application updates and new software installation means to remove some existing one. Actually I had accepted my fate and I read reviews to buy new mobile phone until I heard accidentally about Link2SD.


My phone was already rooted and there was excellent tutorial how to install Link2SD software. I have never tried before any custom ROMs so my phone has HTC stock ROM with Android 2.2.Everything go smoothly until I try to run Link2SD first time. It came out that Desire has additional obstacles to copy/move system software - S-OFF.

I find 2 solutions - first one was deprecated and second one was real beta, so not very promising situation :-( My decision was to use older one, because HTC Desire was first phone supported by this software. I followed installation instructions and everything went without problems. There was one side effect - new splash screen instead of HTC "brilliantly quiet" screen, but at this moment it does not disturb me.

So my "low space" problem was resolved and I was able to install additional software to my phone. Afterward I know that this was only small step in my long phone upgrade path :-)

Read next article: HTC Desire Bravo Cyanogenmod 7.2.0.1 ROM installation

Windows7 hotspot

During vacation I was with family in Trysil doing mountain skiing. There was problem with Internet as always in Scandinavia (in Estonia we have free Internet almost everywhere). I buy Cloud Internet for 7 days, but there were more internet devices in our team. It was for me first time to build Internet hotspot with my computer.
I knew how to do it with Android phone, but because I paid with computer there was no possibility to switch internet connection over to phone. After 5 minute googling I found this article.

So solution is simple

1. Run as Administrator cmd.exe
2. netsh wlan set hostednetwork mode=allow ssid=<network_name> key=<password>  keyUsage=persistent
3. netsh wlan start hostednetwork

• Open Control Panel and go to Network and Sharing Center.
• Click Change Adapter Settings.
• Right click your Internet connection and select Properties.
• Click the Sharing tab.
• Check the box that says “Allow other network users to connect.”
• Choose your virtual Wi-Fi adapter and click OK.

The only problem is how to keep network live when computer is not active. The solution to restart network is to run

1. netsh wlan stop hostednetwork
2. netsh wlan start hostednetwork

Performance issue with DBMS_LOB.append in plsql

We have serious performance issue in Estonian Energy live environment where batch process which reads data from database and inserts xml messages to Oracle AQ queue performed astonishing slowly in test environment and even worse in live environment. Whole batch process was written in plsql and it take 1,5days to fix this issue.

Actual problem was that it was really difficult to understand where is problem, because all database select/insert/update statements performed well and it was shortly clear that problem must be somewhere else. But where?

Database administrator pointed to procedure where xml was put together from plain text and DBMS_LOB.append was used to concatenate strings. To make things more complicated there was almost identical process which performed well in all environments. The main difference between these 2 procedures was that in first one there was DBMS_LOB.append called 30 times per xml message and in second (slooow) one 744 times.

I started to investigate in internet about fast text concatenation strategies with CLOB-s in Oracle plsql and find the article from Martin Giffy D'Souza where actual life saver came from last comment(!) (app procedure).

Idea is that to append small text chunks to CLOB use actually to buffers - VARCHAR2(32767) and CLOB. Every time you need to append new chunk to clob you first try to append to varchar2 buffer and if it fails then append varchar2 buffer to clob and assign chunk to varchar2 buffer. In this scenario we will use CLOB.append only cases where text is longer then 32767 and all other cases we use simple text concatenation. Actually I replace CLOB.append with CLOB.writeappend because in test it was little bit faster.

Procedure to implement CLOB/VARCHAR2 concatenation:
 -- fast varchar2/clob text concatenation
  PROCEDURE append_text(io_clob    IN OUT NOCOPY CLOB
                      , io_buffer  IN OUT NOCOPY VARCHAR2
                      , i_text     IN            VARCHAR2) IS
  BEGIN
    io_buffer := io_buffer || i_text;
  EXCEPTION
    WHEN VALUE_ERROR THEN
      IF io_clob IS NULL THEN
        io_clob := io_buffer;
      ELSE
        DBMS_LOB.writeappend(io_clob
                           , LENGTH(io_buffer)
                           , io_buffer);
        io_buffer := i_text;
      END IF;
  END append_text;

Everywhere in my code I replace
DBMS_LOB.append(m_xml, m_tmp);
-- m_xml temporary clob created with DBMS_LOB.createtemporary(m_xml, TRUE);
-- m_tmp varchar2(32767) - xml chunk, what must be appended to end of clob
with

append_text(m_xml, m_buffer, m_tmp);
-- m_buffer procedure varchar2(32767) variable
and additionally before to return clob from function I added this line
DBMS_LOB.writeappend (m_xml, LENGTH(m_buffer), m_buffer);
-- add varchar2 buffer to clob

It came out that only this small change made 4-5times overall performance gain, so we were able to process 80000-90000 source records per minute instead of 20000 records with previous version.

Database version: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0

Final code:
  FUNCTION get_consumption (i_intervaldata_xml_tbl IN t_intervaldata_xml_tbl)
    RETURN CLOB IS
    m_xml                     CLOB;
    m_tmp                     VARCHAR(32767);
    m_buffer                  VARCHAR(32767);    m_in_qty                  VARCHAR(100);
    m_out_qty                 VARCHAR(100);
    m_first_intervaldata_xml_rec t_intervaldata_xml_rec;
    m_last_intervaldata_xml_rec  t_intervaldata_xml_rec;
    m_timezone                VARCHAR2(30);

  BEGIN
 
    DBMS_LOB.createtemporary(m_xml, TRUE);
    m_first_intervaldata_xml_rec := i_intervaldata_xml_tbl(i_intervaldata_xml_tbl.FIRST); 
    m_last_intervaldata_xml_rec := i_intervaldata_xml_tbl(i_intervaldata_xml_tbl.LAST); 
    m_timezone := get_timezone(m_first_intervaldata_xml_rec.intervaldata_type);
 
   m_tmp := get_xml_header() || b_tools.c_eol ||
    '<EnergyReport xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="EnergyReport.xsd">' || b_tools.c_eol ||
    ' <DocumentIdentification v="' || b_tools.int2str(m_first_intervaldata_xml_rec.document_id) || '"/>' || b_tools.c_eol ||
    ' <SenderIdentification v="'|| g_sender_identification ||'"/>' || b_tools.c_eol ||
    ' <ReceiverIdentification v="'|| g_receiver_identification ||'"/>' || b_tools.c_eol ||
    ' <DocumentDateTime v="' || b_tools.dtime2str(sysdate, 'Y') || '"/>' || b_tools.c_eol ||
    ' <AccountTimeSeries>' || b_tools.c_eol ||
    '  <MeasurementUnit v="'|| g_measurement_unit ||'" />' || b_tools.c_eol ||
    '  <AccountingPoint v="' || m_first_intervaldata_xml_rec.metering_point_eic || '"/>' || b_tools.c_eol ||
    '   <Period>' || b_tools.c_eol ||
    '    <TimeInterval v="' || b_tools.dtime2str(TRUNC(m_first_intervaldata_xml_rec.start_dtime,'HH24'), 'N', m_timezone) || '/' || b_tools.dtime2str(TRUNC(m_last_intervaldata_xml_rec.stop_dtime,'HH24') +  1/24, 'N', m_timezone) || '" />' || b_tools.c_eol ||
    '    <Resolution v="'|| g_resolution ||'" />' || b_tools.c_eol;
    append_text(m_xml, m_buffer, m_tmp);
   
    FOR i IN i_intervaldata_xml_tbl.FIRST .. i_intervaldata_xml_tbl.LAST LOOP
      m_out_qty := case when i_intervaldata_xml_tbl(i).outgoing_qty is null then null else '     <OutQty v="' || b_tools.qty2str(i_intervaldata_xml_tbl(i).outgoing_qty) || '" />' end;
      m_in_qty  := case when i_intervaldata_xml_tbl(i).incoming_qty is null then null else '     <InQty v="'  || b_tools.qty2str(i_intervaldata_xml_tbl(i).incoming_qty) || '" />' end;
      
      m_tmp :=
      '    <AccountInterval>' || b_tools.c_eol ||
      '     <Pos v="' || b_tools.int2str(i_intervaldata_xml_tbl(i).position) || '"/>' || b_tools.c_eol ||
      m_out_qty || b_tools.c_eol ||
      m_in_qty || b_tools.c_eol ||
      '    </AccountInterval>' || b_tools.c_eol;
      append_text(m_xml, m_buffer, m_tmp);
    END LOOP;
   
    m_tmp :=
    '   </Period>' || b_tools.c_eol ||
    '  </AccountTimeSeries>' || b_tools.c_eol ||
    ' </EnergyReport>';
    append_text(m_xml, m_buffer, m_tmp);
    DBMS_LOB.writeappend (m_xml, LENGTH(m_buffer), m_buffer);
    RETURN m_xml;  
  END get_consumption;