Translating Data

Tagged: mysql Toggle Comment Threads | Keyboard Shortcuts

• julie 2:17 pm on July 23, 2016 Permalink
  Tags: data ( 17 ), mysql   

  find and replace SQL 

  UPDATE `tablename`
  SET `fieldname` = replace(`fieldname`, ‘UNWANTED-TEXT’, ‘WANTED-TEXT’)

   

• julie 2:45 pm on July 10, 2016 Permalink
  Tags: data ( 17 ), mysql, rat.systems, schema ( 2 )   

  Updated schema for RAT.systems 

  nmr_data = this is the main data, currently not live, but a lot of archive in there to play with
  nmr_index = look-up table for ID to tag matching
  nmr_readers = list of the reader serial numbers
  nmr_records = stores date logged changes in mass, rank, genitalia and teats of each animal (many NULL)
  nmr_ethogram = describes the codes used in the manual observation table
  nmr_manual_obs = contains a set of behaviours for each animal in the colony (currently 11 are temporary placeholders that have the repeated oos code)
  nmr_nest_distances = distances in mm from one sensor to another for the mapping animation
  nmr_sensors = will store light and sound fluctuations
  nmr_env = environment data, temperature (per change of 2 degrees or more) and humidity (once per hour)

  The latest schema is here: https://github.com/misslake/rat.systems/blob/master/sql/nmr%20schema.sql

   

• julie 2:22 pm on April 27, 2016 Permalink
  Tags: macmini, mysql, tech n, tech_notes ( 2 )   

  Set-up MacMini 1.4Ghz Intel Core i5 4GB (OS X El Capitan 10.11.2) 

  $ sudo easy_install pip
  $ sudo pip install -U setuptools already installed on El Cap
  $ sudo pip install pyserial

  install MariaDB as that is what is installed on pew
  ==

  • make sure Xcode is uptodate or install it   (this triggered an OS install, do it)

  $ xcode-select –install

  • install homebrew

  $ ruby -e “$(curl -fsSL https://raw.githubusercontent.com/Homebrew/install/master/install)”

  • Check Homebrew

  $ brew doctor

  This will give you a message – “Your system is ready to brew.”

  • Update Homebrew

  $ brew update

  • Check MariaDB version in brew repo

  $ brew info mariadb

  • Install MariaDB

  $ brew install mariadb

  • Run the Database Installer (change the version number in the path to match where MariaDB was installed in previous step)

  $ unset TMPDIR
  $ cd /usr/local/Cellar/mariadb/10.1.13/
  $ mysql_install_db

  • Version first installed 10.1.13 (on 27-4-16)
  • To have launchd start mariadb now and restart at login:

  $  brew services start mariadb

  • Start MariaDB

  $ mysql.server start

  • Secure the Installation

  $ mysql_secure_installation

  • Connect to MariaDB

  Follow security questions (remove anon users, enable access as root user remotely, set up root password)

  $ mysql -u root -p

  • Verify MariaDB Version

  $ select @@version;

  ===

  • $ sudo pip install MySQL-python

  $ sudo pip install PyMySQL

  version at 27-4-16 PyMySQL 0.7.2

  ===

  • Generate rsa key for SSH access to server:
    $ ssh-keygen -t rsa
  • Copy public key to clipboard
    $ pbcopy < ~/.ssh/id_rsa.pub

  • plug in USB to serial convertor and a sensor. You may have FTDI drivers installed – check using from root dir:

  $ ls /dev/cu.*

  You are looking for /dev/cu.usbserial or /dev/tty.usbserial (see http://pbxbook.com/other/mac-tty.html )

  If not showing up install driver at http://www.prolific.com.tw/US/ShowProduct.aspx?p_id=229&pcid=41 and restart. [note Macmini didn’t need driver install]

  ===

  • Change paths in python script to be correct serial port paths, note these are in a weird order:

  ser0 = serial.Serial(‘/dev/tty.usbserial’, 9600, timeout=5)

  Use CoolTerm to establish which serial port is which reader: http://freeware.the-meiers.org/

   

  ===
  To get up and running on the command line:
  $ mysql.server start
  then
  $ mysql -u root -p
  created nmr db on iMac and created tables using latest schema hosted on github.

   

• julie 4:00 pm on March 17, 2016 Permalink
  Tags: mysql, raspi ( 4 ), tech notes ( 27 )   

  Raspberry Pi 3B setup 

  install NOOBS from https://www.raspberrypi.org/downloads/noobs/ v 1.8.0 released 2016-02-29

  Get online using wifi dongle and QMUL Guest login (fuck eduroam for now)

  >sudo apt-get update

  >sudo apt-get install mysql-server

  >sudo apt-get upgrade

  >sudo pip install PyMySQL

  Changed mind and went for MariaDB as that is what I use on PEW
  >sudo apt-get install mariadb-server
  As MySQL was already installed this process migrates MySQL dbs etc to MariaDB and removes old MySQL stuff.

  Set-up remote access:
  VNC
  >sudo apt-get install tightvncserver
  >tightvncserver
  — Start a VNC server from the terminal: This example starts a session on VNC display one (:1) with full HD resolution:

  >vncserver :1 -geometry 1920×1080 -depth 24

  NOTE: With RPi3 and NOOBS 29-2-2016 DO NOT RUN the stop service as described on the RPi website as you’ll lose the GUI desktop you are working on… >service lightdm stop

  After this I couldn’t access the Pi from my mac like this – cmd-k from Finder and try to login vnc://PI-IPADDRESS :1
  Probably something to do with the university network bullshit.
  (automation set-up/start on boot instructs are here: https://www.raspberrypi.org/documentation/remote-access/vnc/README.md)

  Decided to try weaved:

  Weaved Connectd https://www.weaved.com
  >create account on https://www.weaved.com/
  >sudo apt-get install weavedconnectd

  To set-up SSH access:
  >sudo weavedinstaller
  — enter account details (email and passw)
  Then Option 1
  Option 1 again
  Option 1 again (SSH)
  yes to default port num
  Enter name
  Exit

  The above ref’d from: https://www.weaved.com/installing-weaved-raspberry-pi-raspbian-os/

  For VNC access via Safari on MacOSX:
  Check tightvnc is running (if not start a server as above)
  >ps ax | grep tightvnc
  Display what port(s) tightvnc is listening to
  >`sudo netstat -apn | grep tcp | grep LISTEN
  >
  Pick lowest port number and install a new Weaved Service Attachment for VNC using the default port of PORTNUMBER.
  >sudo weavedinstaller
  — enter account details (email and passw)
  Then Option 1
  Option 3 (you can change Port number if default isn’t what you want)
  Enter name
  Exit

  Sign in to Weaved account in Safari at weaved.com
  Your connections will be listed.
  Click on the one you want and follow instructs. You’ll need your TightVNC password.
  et voila.

  Generate key to SSH into pew:
  > ssh-keygen -t rsa

  Copy public key to clipboard
  > pbcopy < ~/.ssh/id_rsa.pub Add public key to PEW in the authorized_keys file in .ssh ----- Change hostname, first replace the default name in here: >sudo nano /etc/hostname

  ++ new name nmrpi ++

  Then find the line starting with 127.0.0.1, and change the name following it to your new hostname:
  > sudo nano /etc/hosts
  —–
  Find MAC address:
  > ifconfig

  The HWaddr is the MAC address
  —-

   

• julie 9:07 pm on November 17, 2015 Permalink
  Tags: imac ( 2 ), mysql, python ( 6 ), tech notes ( 27 )   

  Local iMac db setup 

  status at 20151117:
  Python script ok. Failing at the MySQL commit as the primary key stuff is not letting the data into the table.
  To do: add data to the sensor table. check PKs and FKs. check them again. Look at using python to check for duplicates instead of doing it this way.

   

• julie 3:23 pm on October 1, 2015 Permalink
  Tags: mariaDB, mysql, python ( 6 ), tech notes ( 27 )   

  Setting up iMac (OSXYosemite 10.10.5) 

  $ sudo easy_install pip
  $ sudo pip install -U setuptools
  $ sudo pip install pyserial

  install MariaDB as that is what is installed on pew
  ==

  • make sure Xcode is uptodate or install it

  $ xcode-select –install

  • install homebrew

  $ ruby -e “$(curl -fsSL https://raw.githubusercontent.com/Homebrew/install/master/install)”

  • Check Homebrew

  $ brew doctor

  • Update Homebrew

  $ brew update

  • Check MariaDB version in brew repo

  $ brew info mariadb

  • Install MariaDB

  $ brew install mariadb

  • Run the Database Installer (change the version number in the path to match where MariaDB was installed in previous step)

  $ unset TMPDIR
  $ cd /usr/local/Cellar/mariadb/10.0.21/
  $ mysql_install_db

  • Start MariaDB

  $ mysql.server start

  • Secure the Installation

  $ mysql_secure_installation

  • Connect to MariaDB

  $ mysql -u root -p

  • Verify MariaDB Version

  $ select @@version;

  Fabulous source of the above instructions – https://mariadb.com/blog/installing-mariadb-10010-mac-os-x-homebrew

  ===

  • $ sudo pip install MySQL-python

  $ sudo pip install PyMySQL

  ===

  • plug in USB to serial convertor and a sensor. You may have FTDI drivers installed – check using from root dir:

  $ ls /dev/cu.*

  You are looking for /dev/cu.usbserial or /dev/tty.usbserial (see http://pbxbook.com/other/mac-tty.html )

  If not showing up install driver at http://www.prolific.com.tw/US/ShowProduct.aspx?p_id=229&pcid=41 and restart.

  ===

  • Change path in conn.py to be correct serial port path:

  ser0 = serial.Serial(‘/dev/tty.usbserial’, 9600, timeout=5)

  • and fuck me this all worked straight away. I am learning after all.

  Update 13th Nov 2015
  To get up and running on the command line:
  $ mysql.server start
  then
  $ mysql -u root -p
  created nmr db on iMac and created tables using latest schema hosted on github.

  TODO – export database from pew and install on imac so that nmr_index contains a tag ID that it can match.

   

• julie 3:57 pm on July 14, 2015 Permalink
  Tags: database ( 3 ), mysql   

  using MariaDB instead of MySQL 

  10 reasons to migrate to MariaDB (if still using MySQL) – Seravo.

   

• julie 12:07 pm on July 14, 2015 Permalink
  Tags: database ( 3 ), mysql   

  Time-Series Databases blog from Xaprb 

  Time-Series DB requirements

  Time-Series Databases and InfluxDB · Xaprb.

  Why use MySQL?

   

• julie 12:50 pm on July 8, 2015 Permalink
  Tags: mysql, tech notes ( 27 )   

  MySQL TIPS 

  for pew:

  • sudo mysql (to give permissions)
  • create database ; (only needs setting up once)
  • use ; (before you start working on a db, make sure you are using the right one)
  • create table
    …etc see below.
  • show tables;
  • describe
    ;

  DB: nmr
  TABLE: datatest

  • CREATE TABLE datatest (time TIMESTAMP, recID TINYINT(10), avidID VARCHAR(16));

  Check status (on pew, RHEL)
  > mysqladmin -u root -p status

  Start Mariadb (on pew, RHEL)
  > sudo systemctl start mariadb

  Deleting rows
  > delete from nmr_records where id = >”799″;

  > delete from nmr_index where colony = “800”;

  Add columns:
  > alter table nmr_records add column worker_pc smallint(4);