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