Troubleshooting Mysql

Questa volta sono stato contattato perche' un server, che in passato ho montato ed amministrato direttamente per un annetto, aveva iniziato a fare le bizze.
Una rapida occhiata con htop ha mostrato che la macchina era in picco con il processore tra 85% e 100% e con tutti i processi principali occupati da Mysql

Htop

Una volta capito il responsabile c'era da capire il motivo. Il primo sospetto, visto il tempo di esecuzione di ogni processo, era che le query fossero talmente lunghe e frequenti da sovrapporsi nel tempo da creare un effetto a cascata che rendeva dopo un po' il tempo il server non utilizzabile

Per definire il problema ho usato il comando

mysqladmin -u root -p -i 1 processlist

in questo modo si ottiene una lista dei processi in corso sul server MySql 
evidenziando anche la query ed il tempo di esecuzione di ciascuna query.

In questo modo si ottiene una  lista dei processi in corso sul server Mysql evidenziando anche la query in corso ed il tempo di esecuzione. Impostando il parametro i ad 1 si ottiene i dati relativi ad 1 secondo di tempo. Nel parziale listato si e' visto che in un secondo venivano eseguite oltre 120 query complesse con il tempo in secondo impiegato da ciascun  thread

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

+-------+----------------------+--------------------------+--------+---------+------+----------------------+------------------------------------------------------------------------------------------------------+----------+
| Id    | User                 | Host                     | db     | Command | Time | State                | Info                                                                                                 | Progress |
+-------+----------------------+--------------------------+--------+---------+------+----------------------+------------------------------------------------------------------------------------------------------+----------+
| 12296 | utente               | localhost                | db | Query   | 2    | Sending data         | SELECT AVG(VAL) AS VAL
                                        FROM db.f     
                                        WHERE NET = (SELECT NET.NET_ID
         | 0.000    |
| 12299 | utente               | localhost                | db | Query   | 16   | Sending data         | SELECT AVG(VAL) AS VAL
                                        FROM db.f     
                                        WHERE NET = (SELECT NET.NET_ID
         | 0.000    |
| 12303 | utente               | localhost                | db | Query   | 4    | Sending data         | SELECT AVG(VAL) AS VAL
                                        FROM db.f     
                                        WHERE NET = (SELECT NET.NET_ID
         | 0.000    |
| 12308 | utente               | localhost                | db | Query   | 33   | Sending data         | SELECT AVG(VAL) AS VAL
                                        FROM db.f     
                                        WHERE NET = (SELECT NET.NET_ID
         | 0.000    |
| 13392 | utente               | localhost                | db | Query   | 35   | Sending data         | SELECT AVG(VAL) AS VAL
                                        FROM db.E     
                                        WHERE NET = (SELECT NET.NET_ID
                     | 0.000    |
| 13396 | utente               | localhost                | db | Query   | 11   | Sending data         | SELECT AVG(VAL) AS VAL
                                        FROM db.E     
                                        WHERE NET = (SELECT NET.NET_ID
                     | 0.000    |
| 13399 | utente               | localhost                | db | Query   | 5    | Sending data         | SELECT AVG(VAL) AS VAL
                                        FROM db.E     
                                        WHERE NET = (SELECT NET.NET_ID
                     | 0.000    |
| 13403 | utente               | localhost                | db | Query   | 26   | Sending data         | SELECT AVG(VAL) AS VAL
                                        FROM db.E     
                                        WHERE NET = (SELECT NET.NET_ID
                     | 0.000    |
| 13411 | utente               | localhost                | db | Query   | 40   | Sending data         | SELECT AVG(VAL) AS VAL
                                        FROM db.E     
                                        WHERE NET = (SELECT NET.NET_ID
                     | 0.000    |
| 14407 | utente               | localhost                | db | Query   | 45   | Sending data         | SELECT AVG(VAL) AS VAL
                                        FROM db.E     
                                        WHERE NET = (SELECT NET.NET_ID
                     | 0.000    |
| 14411 | utente               | localhost                | db | Query   | 54   | Sending data         | SELECT AVG(VAL) AS VAL 
------------------------------------------------------------------------
ovviamente un traffico che ha messo in ginocchio il server.

Un metodo alternativo e' quello di installare mytop (un clone del comando top specifico per MySql)
Il comando si puo' lanciare passando i parametri da file di configurazione oppure tramite switch sulla linea di comando. Ho usato la seconda strada

mytop -u root -d databasename --prompt
(e' necessario inserire il nome del database, non accetta il monitoraggio di tutti i db)

Mytop del server quando sono state interrotte le query lunghe

In questa modalita' vengono mostrate le query per secondo e le query lente con il traffico di rete

Resync Mysql

In questo post avevo provato a fare il mirroring master/slave di un database Mysql su macchine differenti

Prova di mysql mirroring su macchine virtuali Debian in VMWare

In generale la sincronia dei dati anche se la macchina slave e' spenta od offline. Al momento di ristabilire la connessione lo slave e' in grado di stabilire le differenze tra le proprie tabelle e quelle del master per richiedere la differenza

In alcuni casi questa procedura puo' entrare in crisi ed e' necessario effettuare un resync manuale con la procedura indicata qui passando da un file dump dei dati del master

In pratica si procede stoppando lo slave

stop slave;

si blocca il master e si effettua il dump dei dati in modalita' lock

reset master;
flush tables with read lock;

si esce dalla shell di Mysql e si crea il dump dei dati

mysqldump -u root -p mirrdatabase > dump.sql

si rientra nella shell di Mysql e si sbloccano le tabelle

unlock tables;

a questo punto si copiano i dati sullo slave (che ha il mirroring bloccato)

mysql -u root -p mirrdatabase < dump.sql

(nello script dump.sql ci sono le informazioni per cancellare le tabelle precedentemente presenti per cui non e' necessario farlo a mano)

si riavvia il mirroring
reset slave;
change master to master_log_file='mysql-bin.000001', master_log_pos=1;start slave;


questa procedura puo' essere utile nel momento di creare un mirroring di una macchina gia' in produzione da diverso tempo con una grande quantita' di dati che potrebbero creare problemi in fase di sincronia sia alla CPU che al traffico di rete

Mysql tunnel over SSH

Alcune volte, un po' per passare dati riservati, un po' per bucare firewall non particolarmente permissivi si puo' creare un tunnel SSH per far passare anche informazioni derivanti da altri server. In questo caso si puo' vedere come interagire direttamente con un database MySql nel caso in cui la porta 3306 del server sia filtrata mentre la porta 22 risulta accessibile

In pratica la connessione funziona in questo modo

server Mysql (3306 remota) <-> SSH remoto (22) <-> Internet <-> client locale ssh <->porta locale del tunnel

Linux
Nel caso Linux la procedura per creare un tunnel Mysql over SSH e' piuttosto banale e veloce
Si crea il tunnel con la seguente riga di comando

ssh login@server -L 3306:127.0.0.1:3306 -N &

login e server sono le credenziali del server SSH

in pratica ci si connette con la porta 3306 remota (passando dal tunnel SSH) ed creando una porta 3306 in ascolto. (Ovviamente sulla macchina client non deve essere presente nessun servizio che occupi la porta ... in caso contrario il numero della porta locale puo' essere modificato a piacere)

(importante la & finale per mandare il processo di tunnel in background...in caso si ometta si deve aprire un'altra shell per gestire il collegamento Mysql)

Fatto cio' ci si connette come se il server fosse installato in localhost

mysql -h 127.0.0.1 -p -u user_mysql
(ovviamente le credenziali di Mysql sono quelle dell'host remoto)

Windows

In questo caso il client SSH e' molto piu' spesso Putty. Per creare un tunnel SSH con Putty, dopo aver creato come di norma una sessione, 

si deve andare in Connection/SSH/Tunnels ed impostare 

Source port : 3306

Destination : 127.0.0.1:3306 

Anche in questo caso non deve esserci un altro servizio che occupa la porta 3306 ed in ogni caso la Destination port puo' essere modificata

Di preme Add e la schermata dovrebbe essere identica a quella sottostante

Fatto cio' si puo' aprire MysqlWorkbench e connettersi in localhost per creare una connessione con Mysql remoto

Mysql SSH Tunnel

In questo precedente post avevo indicato come mettere in ascolto un demone SSH su due schede di rete

Mi e' stato chiesto, sulla stessa macchina di poter interagire con il server Mysql, sia dalla scheda con indirizzo interno che da quella con indirizzo pubblico. Nonostante i vari tentativi di modificare il bind-address (impostandolo per esempio a 0.0.0.0 per il server SSH) non sono riuscito a mettere il server in ascolto.
A questo punto entra in gioco un piccolo trucco; visto che l'accesso SSH e' garantito si puo' creare un tunnel mysql over ssh, in pratica il traffico mysql viene instradato con canale SSH ed il client non si collega direttamente all'indirizzo remoto ma ad un propria porta locale

Il comando per fare cio' e' il seguente
ssh -fNg -L 3307:127.0.0.1:3306 luca@10.1.1.238

ci si collega dal client alla macchina remota (10.1.1.238) collegando la porta remota 3306 (Mysql) con una porta locale 3307

per collegarsi ed interrogare il database si puo' usare quindi la sintassi
mysql -h 127.0.0.1 -P 3307 -u luca 

(testato su Os X e Linux)

SismoArduino

Dopo i recenti terremoti vicino a Firenze mi e' tornata la voglia di lavorare ad un progetto che avevo in mente da tempo e che con somma fantasia ho denominato SismoArduino. Si tratta di un progetto da un giorno ovvero che non risolve in 10 minuti collegando 4 fili ma che non necessita comunque troppo impegno

Per questo progettino ho tirato fuori l'Arduino Uno (la scelta e' stata obbligata perche' Arduino Due non entra fisicamente nella scatola da esterni in cui alla fine deve essere alloggiato il tutto per metterlo in produzione), uno shield Ethernet originale Arduino e un accelerometro triassiale ADXL335

Per comodita' ho piegato il pin ST in modo da fare contatto ed ho poi inserito i pin Z,Y e X nelle porte A3,A4 ed A5 patchando poi al volo i cavi di alimentazione (collegati a 3.3V e GND)

Lo sketch montato su Arduino semplicemente legge i dati sulle porte analogiche e poi invia una stringa mediante UDP client ad un server
(Arduino e' 192.168.1.177 mentre il server e' 192.168.1.120 in ascolto su porta 5005)

-----------------------------------------------------------------------
#include <SPI.h>      
#include <Ethernet.h>
#include <EthernetUdp.h>
//Arduino
byte mac[] = { 0xDE, 0xAD, 0xBE, 0xEF, 0xFE, 0xED };
IPAddress ip(192, 168, 1, 177);
unsigned int localPort = 8888;    

//Server
byte remoteIP[4] = {192, 168, 1, 120};
int remotePort = 5005;

String dati;
char charBuf[1024];

EthernetUDP Udp;

void setup() {
  Serial.begin(9600);
  Ethernet.begin(mac,ip);
  Udp.begin(localPort);
}

void loop() {
  int x = analogRead(A5);
  delay(2);
  int y = analogRead(A4);
  delay(2);
  int z = analogRead(A3);
  delay(2);
  dati = String(x)+","+String(y)+","+String(z);
  Serial.println(dati);
  dati.toCharArray(charBuf, 1024);
  Udp.beginPacket(remoteIP, remotePort);
  Udp.write(charBuf);
  Udp.endPacket();
  delay(10);    
}
-----------------------------------------------------------------------

Sul server e' stato messo uno script Python che riceve i dati con un server UDP e li scrive su un database Mysql
-----------------------------------------------------------------------

#!/usr/bin/python

import socket
import MySQLdb as mdb
import  time

current_milli_time = lambda: int(round(time.time()*1000))

con = mdb.connect('localhost','root','chiara','sismoarduino')

sock = socket.socket(socket.AF_INET,socket.SOCK_DGRAM)
sock.bind(("192.168.1.120",5005))
while True:
data,addr=sock.recvfrom(1024)
with con:
cur = con.cursor()
cur.execute("INSERT INTO sisard (x1,y1,z1,milli,tempo) VALUES ("+data+","+str(current_milli_time())+",now())")

print data

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

Per la tabella Mysql c'e' un piccolo trucco. Mysql non accetta in modo semplice il formato tempo con i millisecondi. Per cui e' stato creato un campo milli come BIGINT dove e' registrato il tempo completo con i millisecondi ed un campo tempo come DATETIME per il lettore umano
-----------------------------------------------------------------------
CREATE TABLE `sisard` (
  `x1` int(11) DEFAULT NULL,
  `y1` int(11) DEFAULT NULL,
  `z1` int(11) DEFAULT NULL,
  `milli` bigint(20) DEFAULT NULL,
  `tempo` datetime NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
------------------------------------------------------------------------

La presentazione dei dati e' effettuata in tempo reale mediante un lettore fatto con le librerie Dygraph
I dati per la presentazione a video vengono generati dal seguente scritp php (in pratica viene generato al volo un CSV che viene dato in pasto a DyGraph
------------------------------------------------------------------------

<?php header('Content-Type: text/csv; charset=utf-8'); 

header('Content-Disposition: attachment; filename=data.csv');

$output = fopen('php://output', 'w');


$dbhost = 'localhost';
$dbuser = 'root';
$dbpass = 'chiara';
$dbdb = 'sismoarduino';
$conn = mysql_connect($dbhost, $dbuser, $dbpass);
if(! $conn )
{
  die('Could not connect: ' . mysql_error());
}
mysql_select_db($dbdb);

$result = mysql_query("SELECT * FROM sisard ORDER BY milli DESC LIMIT 1000");
while ($row = mysql_fetch_array($result)) {

$arr[0] = $row['milli'];
//$arr[0]=date("d-m-Y H:i:s",$row['milli']/1000);
$arr[1] = $row['x1'];
$arr[2] = $row['y1'];
$arr[3] = $row['z1'];
fputs($output,implode($arr,',')."\n");
}

mysql_close($conn);
?>

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

E questa e' la pagina html che ospita Dygraph con aggiornamento automatico ogni secondo (la finestra e' degli ultimi 1000 dati
------------------------------------------------------------------------

<html>

<META HTTP-EQUIV="refresh" CONTENT="900">
<head>
<script type="text/javascript"
  src="http://localhost/dygraph-combined.js"></script>
</head>
<body>
<div id="graphdiv2"
  style="width:500px; height:300px;"></div>
  <br>

<script type="text/javascript">
  g2 = new Dygraph(
    document.getElementById("graphdiv2"),
    "http://localhost/sismodati.php", 
    {title: 'SismoArduino'}          // options
  );

 var update = function(){
g2.updateOptions({'file': 'http://localhost/sismodati.php'});  
};
  window.setInterval(update,1000);  
</script>
</body>
</html>
------------------------------------------------------------------------

il passo di campionamento misurato e' stato di circa 15-16 campioni al secondo (15 Hz) che non sono assolutamente paragonabili a strumenti professionali (i sismografi reali hanno passi di campionamento che vanno da 50 a 100 Hz)
Qui il video della prova

L'aspetto finale e' da sottolineare e' che il server (di recupero) impiegato e' stato un P3 a 700 Mhz con 128 Mb di ram con Debian Wheezy, Apache, Php, Mysql e Xwindow in funzione ed ha comunque funzionato dignitosamente

MYIsam vs InnoDB

Recentemente e' stato deciso un aggiornamento di un server interno basato su una vetusta Ubuntu 10.10 per passare ad una macchina e ad una distribuzione piu' recente
Nella migrazione un applicativo proprietario (per altro scritto in Java) basato su MySql ha iniziato a dare improvvisamente problemi con una lentezza delle query (con tempi di risposta aumentati da 10 a 100 volte)

Dopo un  po' di navigazione a vuoto per fortuna lo sviluppatore ha fornito l'indicazione corretta
bisogna impostare il motore dati di Mysql da InnoDB a MyIsam

con il tempo (e piu' precisamente da Ubuntu 12.04) le tabelle di default sono passate da InnoDB a MyIsam
In generale riprendendo dal sito MySql le MyIsam sono piu' performanti nelle SELECT (l'applicativo in uso nel server invece effettua circa 40 INSERT al secondo e solo sporadiche select)

per vedere il tipo di motore associato ad una tabella si puo' usare la seguente query

SELECT TABLE_NAME, ENGINE FROM information_schema.TABLES where TABLE_SCHEMA = 'xxxxxxx';
Per convertire una tabella di InnoDB a MyIsam si puo' usare invece il comando

ALTER TABLE tablename ENGINE=MYISAM;

Una volta effettuata la modifica il programma e' ritornato a lavorare in modo corretto

Un altro aspetto non trascurabile di differenze tra InnoDB e MyIsam e' che le InnoDB di fatto non sono corruttibili. Se con MYIsam era necessario il comando

mysqlcheck --repair --all-databases -u root -p

adesso in caso di uso InnoDB si mostra l'avviso "The storage engine for the table doesn't support repair"

Debugging Mysql

In alcuni casi c'e' la necessita' di effettuare debugging su Mysql, in particolare quando si ha a che fare con una applicazione proprietaria che scrive sul database e non se ne conoscono le  specifiche

Per tracciare il lavoro di Mysql si puo' attivare il log delle query; cio' puo' essere fatto anche senza stoppare il servizio entrando in shell con il comando (questi comandi sono eseguibili solo da amministratore di MySql)

mysql -u root -p

e digitando

SET GLOBAL general_log = 'ON';

fatto cio' sara' creato il file di log (normalmente in /var/lib/mysql/)

nel caso che mi e' capitato e' stato osservato il database ha effettuato circa 4500 queries in 105 secondi (poco meno di 43 queries al secondo)

sospettando un carico eccessivo del db ho attivato il log delle query che richiedono tempo per essere eseguite in modo da vedere se ci fosse una coda di lavoro

Sempre dalla shell di mysql questa funzione si puo' attivare con 

SET GLOBAL slow_query_log = 'ON';

(di default la lunghezza di una query lunga e' settata a 10 secondi su Ubuntu, si puo osservare con il comando "show variables like 'long_query_time';")

per modificare il limite della query lunga, per esempio a 50 ms

set global long_query_time = 0.05;

per leggere il file delle slow query puo' essere utile il comando mysqldumpslow

Reading mysql slow query log from Ubuntu-1404-trusty-64-minimal-slow.log

Count: 1  Time=11.38s (11s)  Lock=0.00s (0s)  Rows=9553.0 (9553), xxxxx[xxxxxx]@localhost

  SELECT * FROM `xxxxxxxx`.`xxxxxxxxxxxx`

che tradotto indica che la query e' stata eseguita una sola volta (Count 1) con un tempo medio di 11.38 secondi (il tempo tra parentesi e' il tempo cumulato nel caso in cui il count fosse maggiore di 1) e che ha richiesto 9553 righe

Mysql Mirroring su Debian

Il problema: creare due database su due server differenti (master 192.168.1.1 e slave 192.168.1.2) in cui il secondo risulta essere la copia del primo
Si parte dal presupposto che entrambe abbiamo mysql installato (apt-get install mysql-server mysql-client) ed in funzione

Per la configurazione di base di Debian la porta 3306 risulta filtrata per cui sulla macchina master e' necessario modificare le regole di iptables. Per non abbassare troppo la guardia la porta 3306 e' aperta solo per le connessioni che provengono dallo slave

/sbin/iptables -A INPUT -i eth0 -s 192.168.1.1 -p tcp --destination-port 3306 -j ACCEPT
a questo punto ci sono due sistemi (si parte considerando database vuoti)

Sistema completo

Macchina Master
si edita il file /etc/mysql/my.cnf modificando
---------------------------------
bind-address = 192.168.1.1
server-id = 1 (basta decommentare)
log_bin = /var/log/mysql/mysql-bin.log (basta decommentare)

binlog_do_db = mirrdatabase (e' il nome del database su cui fare mirroring

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

si riavvia il servizio mysql e  si entra in shell mysql (mysql -u root -p). 

Si crea un database 

CREATE DATABASE mirrdatabase;

A questo punto si crea un utente (slave_user) con password con privilegi di replica 

GRANT REPLICATION SLAVE ON *.* TO 'slave_user'@'%' IDENTIFIED BY 'password';

FLUSH PRIVILEGES;

a questo punto si digita il comando 

SHOW MASTER STATUS;

che risponde qualcosa di simile a questo

+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 |      1080| mirrdatabase |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

si annotino il numero di posizione ed il file perche' serviranno per la configurazione dello slave

Macchina Slave
si edita il file /etc/mysql/my.cnf modificando
---------------------------------
server-id = 2 
relay-log = /var/log/mysql/mysql-relay-bin.log
log_bin = /var/log/mysql/mysql-bin.log (basta decommentare)

binlog_do_db = mirrdatabase (e' il nome del database su cui fare mirroring

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

si riavvia il servizio mysql e si entra in shell mysql (mysql -u root -p). e si crea il database

CREATE DATABASE mirrdatabase;

si digita quindi
CHANGE MASTER TO MASTER_HOST='192.168.1.1',MASTER_USER='slave_user', MASTER_PASSWORD='password', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS= 1080;

i valori in giallo sono ricopiati da quelli del master. A questo punto si attiva la replicazione (per essere sicuri che ci sia connessione tra le due macchine puo' essere comodo prima verificare con il comando mysql -h 192.168.1.1 -u slave_user -p che ci sia accesso alla shell di mysql)

Si avvia quindi la replicazione con il seguente comando

START SLAVE;
a questo punto tutti i comandi eseguiti sulla macchina Master saranno replicati sulla macchina Slave compresi quelli di creazione di tabelle o popolazione dei dati


Sistema speditivo
Con il metodo speditivo e' necessario che sulle due macchine siano presenti e gia' configurati database e strutture tabelle identiche. Per popolare i dati si puo' usare uno script Php con due connessioni (una verso localhost, l'altra verso la macchina di copia)

<?php 

$dbmaster = 'localhost'; 

$dbusermaster = 'root1'; 

$dbpassmaster = 'password1'; 

$dbslave = '192.168.1.2'; 

$dbuserslave = 'root2'; 

$dbpassslave = 'password2'; 

$conn_master = mysql_connect($dbmaster, $dbusermaster, $dbpassmaster); 

if(! $conn_master ) 

      { 

      die('Could not connect: ' . mysql_error()); 

      } 

$conn_slave = mysql_connect($dbslave, $dbuserslave, $dbpassslave); 

if(! $conn_slave ) 

      { 

      die('Could not connect: ' . mysql_error()); 

      } 

$sql = 'INSERT INTO employee '. '(emp_name,emp_address, emp_salary, join_date) '. 'VALUES ( "guest", "XYZ", 2000, NOW() )'; 

mysql_select_db('mirrdatabase'); 

$retval = mysql_query( $sql, $conn_master ); 

if(! $retval ) 

      { 

      die('Could not enter data in master: ' . mysql_error()); 

      } 

$retval2 = mysql_query( $sql, $conn_slave ); 

if(! $retval2 ) 

      { 

      die('Could not enter data in slave : ' . mysql_error()); 

      } 

echo "Entered data successfully\n"; 

mysql_close($conn_master); 

mysql_close($conn_slave); 

?>

JSON da Mysql ad Android

Per poter effettuare lo scambio dati (unidirezionale) tra un server ed un client mobile Android puo' essere utile impiegare JSon

Per prima cosa si deve configurare il lato server installando Apache,Php e le sue estensioni (in particolare php5-json) e mysql


sudo apt-get install apache2
sudo apt-get install php5
sudo apt-get install libapache2-mod-php5
sudo apt-get install php5-json
sudo /etc/init.d/apache2 restart
sudo apt-get install mysql-server phpmyadmin

A questo punto si crea la base dati. Nell'esempio e' stato creato un database "test" con una sola tabella "test" con una struttura di questo tipo
----------------------------------------------------
-- phpMyAdmin SQL Dump
-- version 3.4.10.1deb1
-- http://www.phpmyadmin.net
--
-- Host: localhost
-- Generation Time: Apr 23, 2014 at 04:15 PM
-- Server version: 5.5.35
-- PHP Version: 5.3.10-1ubuntu3.11

SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;

--
-- Database: `test`
--

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

--
-- Table structure for table `test`
--

CREATE TABLE IF NOT EXISTS `test` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `nome` varchar(50) NOT NULL,
  `cognome` varchar(50) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;

--
-- Dumping data for table `test`
--

INSERT INTO `test` (`id`, `nome`, `cognome`) VALUES
(1, 'Luca', 'Innocenti'),
(2, 'Alessio', 'Parauda');

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
----------------------------------------------------

per interrogare il db sul lato server e' stato inserito il seguente semplice script Python che effettua una query e crea un oggetto JSon con i dati risultanti dalla query

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

<?php

$mysql_db_hostname = "localhost";

$mysql_db_user = "xxxxx";

$mysql_db_password = "xxxxxx";

$mysql_db_database = "test";

$con = @mysqli_connect($mysql_db_hostname, $mysql_db_user, $mysql_db_password,$mysql_db_database);

if (!$con) {

echo('Could not connect to MySQL: ' . mysqli_connect_error());

}

$var = array();

$sql = "SELECT * FROM test";

$result = mysqli_query($con, $sql);

while($obj = mysqli_fetch_object($result)) {

$var[] = $obj;

}

echo json_encode($var);

?>

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

si puo' testare l'output chiamando la pagina da browser

L'applicazione Android di seguito riportata apre una connessione Http verso la pagina php precedentemente predisposta ed effettua il parsing dell'oggetto JSon

Ovviamente deve essere impostato nel file Manifest il permesso per l'uso di Internet

----------------------------------------------------
package com.luca.json;

import java.io.BufferedReader;
import java.io.IOException;
import java.io.InputStream;
import java.io.InputStreamReader;

import org.apache.http.HttpEntity;
import org.apache.http.HttpResponse;
import org.apache.http.StatusLine;
import org.apache.http.client.ClientProtocolException;
import org.apache.http.client.HttpClient;
import org.apache.http.client.methods.HttpGet;
import org.apache.http.impl.client.DefaultHttpClient;
import org.json.JSONArray;
import org.json.JSONException;
import org.json.JSONObject;

import android.support.v7.app.ActionBarActivity;
import android.support.v4.app.Fragment;
import android.os.Bundle;
import android.util.Log;
import android.view.LayoutInflater;
import android.view.Menu;
import android.view.MenuItem;
import android.view.View;
import android.view.ViewGroup;
import android.widget.Toast;


public class MainActivity extends ActionBarActivity {

@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);

 
setContentView(R.layout.activity_main);

if (savedInstanceState == null) {
getSupportFragmentManager().beginTransaction()
.add(R.id.container, new PlaceholderFragment()).commit();
}

Thread thread = new Thread(new Runnable(){
   @Override
   public void run() {
     String result = readJson();
     JSONArray jArray = null;
try {
jArray = new JSONArray(result);
} catch (JSONException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
     JSONObject json_data=null;
       for(int i=0;i<jArray.length();i++){
               try {
json_data = jArray.getJSONObject(i);
} catch (JSONException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
               try {
String nr = Integer.toString(json_data.getInt("id"));
String nome = json_data.getString("nome");
String cognome = json_data.getString("cognome");
Log.d("JSON",nr+","+nome+","+cognome);
} catch (JSONException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
       }
   }   
});
thread.start(); 
}

private String readJson() {
      String line = null;
  StringBuilder builder = new StringBuilder();
   HttpClient client = new DefaultHttpClient();
   HttpGet httpGet = new HttpGet("http://192.168.0.100/json.php");
   try {
     HttpResponse response = client.execute(httpGet);
     StatusLine statusLine = response.getStatusLine();
     int statusCode = statusLine.getStatusCode();
     if (statusCode == 200) {
       HttpEntity entity = response.getEntity();
       InputStream content = entity.getContent();
       BufferedReader reader = new BufferedReader(new InputStreamReader(content));
       while ((line = reader.readLine()) != null) {
         builder.append(line);
       }
     } else {
       Log.d("JSON", "Failed to download file");
     }
   } catch (ClientProtocolException e) {
     e.printStackTrace();
   } catch (IOException e) {
     e.printStackTrace();
   }

//Log.d("JSON",line);
   return builder.toString();
}

@Override
public boolean onCreateOptionsMenu(Menu menu) {

// Inflate the menu; this adds items to the action bar if it is present.
getMenuInflater().inflate(R.menu.main, menu);
return true;
}

@Override
public boolean onOptionsItemSelected(MenuItem item) {
// Handle action bar item clicks here. The action bar will
// automatically handle clicks on the Home/Up button, so long
// as you specify a parent activity in AndroidManifest.xml.
int id = item.getItemId();
if (id == R.id.action_settings) {
return true;
}
return super.onOptionsItemSelected(item);
}

/**
* A placeholder fragment containing a simple view.
*/
public static class PlaceholderFragment extends Fragment {

public PlaceholderFragment() {
}

@Override
public View onCreateView(LayoutInflater inflater, ViewGroup container,
Bundle savedInstanceState) {
View rootView = inflater.inflate(R.layout.fragment_main, container,
false);
return rootView;
}
}

}

Database antenne telefonia mobile

Per ottenere la posizione delle antenne cellulari, oltre all'interfaccia web, e' possibile scaricare anche l'intero database in modo da effettuare una consultazione offline

Il database si scarica all'indirizzo http://downloads.opencellid.org/ ed e' particolarmente corposo perche' e' attualmente costituito da un file da 57 Mb compressi di posizioni testuali di celle (corrispondenti a  2390305entrate) e da oltre 4.5 Gb compressi di dati di misure (divisi in 12 file di cui ogni file contiene circa 500.000 misure)

Ovviamente e' impossibile consultare con un editor di testo tale mole di dati e quindi e' necessaria la conversione in database, in particolare MySQL

Per prima cosa si crea la struttura della tabella
----------------------------------------------------------------

-- MySQL dump 10.13  Distrib 5.5.33, for debian-linux-gnu (x86_64)

--

-- Host: localhost    Database: celle

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

-- Server version 5.5.33-1

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;

/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;

/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;

/*!40101 SET NAMES utf8 */;

/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;

/*!40103 SET TIME_ZONE='+00:00' */;

/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;

/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;

/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;

/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--

-- Table structure for table `celle`

--

DROP TABLE IF EXISTS `celle`;

/*!40101 SET @saved_cs_client     = @@character_set_client */;

/*!40101 SET character_set_client = utf8 */;

CREATE TABLE `celle` (

  `mcc` int(11) NOT NULL,

  `mnc` int(11) NOT NULL,

  `lac` int(11) NOT NULL,

  `cellid` int(11) NOT NULL,

  `long` double NOT NULL,

  `lat` double NOT NULL

) ENGINE=InnoDB DEFAULT CHARSET=latin1;

/*!40101 SET character_set_client = @saved_cs_client */;

/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;

/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;

/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;

/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;

/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;

/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

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

In seguito ci si logga dentro mysql (mysql -u root -p) e si digita il seguente comando

load data infile '/home/luca/Desktop/cell_towers.csv' into table celle columns terminated by ',';

per il seguente risultato

Query OK, 2390305 rows affected, 65535 warnings (1 min 33.18 sec)

Records: 2390305  Deleted: 0  Skipped: 0  Warnings: 2390386

A questo punto si puo' fare una semplice pagina web di consultazione
--------------------------------------------------------
<form action="search.php" method="post">     
MCC: <input type="text" name="mcc" />
MNC: <input type="text" name="mnc" />
LAC: <input type="text" name="lac" />
CellId: <input type="text" name="cellid" />
<input type="Submit" /></form>
--------------------------------------------------------

ed il corrispondente script in Php
--------------------------------------------------------

<?php

$username="root";
$password="xxxxxx";
$database="celle";
mysql_connect(localhost,$username,$password);
@mysql_select_db($database) or die( "Unable to select database");
$query="SELECT * FROM celle WHERE mcc='".$_POST['mcc']."' AND mnc='".$_POST['mnc']."' AND lac  LIKE '".$_POST['lac']."%' AND cellid  LIKE '".$_POST['cellid']."%'";

print $query."<br>";
$result=mysql_query($query);
$num=mysql_numrows($result);


while($row = mysql_fetch_array($result))
  {
  echo "<a target=blank href=\"http://www.openstreetmap.org/?mlat=".$row['lat']."&mlon=".$row['long']."&zoom=12\">MCC:".$row['mcc']."-MNC:".$row['mnc']."-LAC:".$row['lac']."-CellId:".$row['cellid']."</a>";
  echo "<br>";
  }

mysql_close();
?>
--------------------------------------------------------
Lo script riporta un link, cliccando sopra si evidenzia la posizione su Openstreetmap

Similarmente si puo' fare per il database delle misure

Effettuando una esportazione dei punti che sono compresi tra 10-11.9° di longitudine e 42-44.5° latitudine (piu' o meno i confini della Toscana) si osserva che sono censite 6780 antenne di telefonia cellulare ma la loro distribuzione e' molto disomogenea con una curiosa predilezione per le grandi vie di comunicazione

Mysql in Qt

In questo post viene mostrata la connessione e l'esecuzione di query da Qt verso un database MysqlS riprendendo le basi gia' poste nel post SQlite
In sostanza deve essere modificata solo la parte relativa alla connessione mentre l'esecuzione delle query e' sostanzialmente identica

(come sempre si deve modificare il file .pro aggiungendo la libreria sql con il comando
QT       += core gui sql
)

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

QSqlDatabase db = QSqlDatabase::addDatabase("QMYSQL");

db.setDatabaseName("test");
db.setHostName("localhost");
db.setUserName("root");
db.setPassword("");

if (db.open()) {
            QSqlQuery query;
            query.exec("create table punteggio (id int primary key, "
                                             "nome varchar(20), "
                                             "punti int)");

         query.exec("insert into punteggio values(0, 'Luca', 2)");
         query.exec("insert into punteggio values(1, 'Federico', 102)");
         query.exec("insert into punteggio values(2, 'Alessio', 15)");
         db.close();
}

Installazione minima per Wordpress con Lighttpd

Per avere un server su cui far lavorare Wordpress in pochi passi si puo' procedere cosi'

apt-get install mysql-server mysql-client

apt-get install lighttpd

apt-get install php5-cgi php5-curl php5-mysql

si modifica

vi /etc/php5/cgi/php.ini

aggiungendo 

cgi.fix_pathinfo = 1

si modifica

vi /etc/lighttpd/lighttpd.conf

aggiungendo php5 fastcgi

server.modules              = (
            "mod_access",
            "mod_alias",
            "mod_accesslog",
            "mod_fastcgi",
#           "mod_rewrite",
#           "mod_redirect",

ed aggiungendo alla fine

fastcgi.server = ( ".php" => ((
                     "bin-path" => "/usr/bin/php5-cgi",
                     "socket" => "/tmp/php.socket"
                 ))) 

infine si riavvia il server per caricare le modifiche

/etc/init.d/lighttpd restart

per la cronaca e' stato scelto lighttpd perche' pesa solo 350 Kb

Connettersi a MySql da Android

Android ha varie possibilita' di salvare dati persistenti ma non c'e' una via semplice per connettersi a database esterni in particolare MySql
Usando JDBC ho avuto diversi problemi (il sorgente si compila bene ma non riesce a connettersi) per cui un sistema alternativo e' quello di passare per un intermediario dato da uno script in PHP
per prima cosa si deve impostare il permesso


<uses-permission android:name="android.permission.INTERNET"></uses-permission>

il server e' 192.168.0.1 e lo script si trova in http://192.168.0.1/zenith/insert.php
vengono passate due variabili (piatto e tavolo) popolate

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


               InputStream is;
        String piatto = spin.getSelectedItem().toString();
//String tavolo = tav_txt.getText().toString();
        String tavolo = spin_tav.getSelectedItem().toString();

        ArrayList <NameValuePair> nameValuePairs = new ArrayList <NameValuePair>();
        nameValuePairs.add(new BasicNameValuePair("piatto",piatto));
        nameValuePairs.add(new BasicNameValuePair("tavolo",tavolo));
       
        try{
        HttpClient httpclient = new DefaultHttpClient();
       
        HttpPost httppost = new HttpPost("http://192.168.0.1/zenith/insert.php");
        httppost.setEntity(new UrlEncodedFormEntity(nameValuePairs));
        HttpResponse response = httpclient.execute(httppost);
        HttpEntity entity = response.getEntity();
        is = entity.getContent();
        Log.d("Zenith","Dato salvato");
           Toast.makeText( getApplicationContext(),R.string.riuscito,Toast.LENGTH_SHORT ).show();
               // suona per conferma
               Uri notification = RingtoneManager.getDefaultUri(RingtoneManager.TYPE_NOTIFICATION);
               Ringtone r = RingtoneManager.getRingtone(getApplicationContext(), notification);
               r.play();


        }
        catch(Exception e)
        {
           Toast.makeText( getApplicationContext(),R.string.non_riuscito,Toast.LENGTH_SHORT ).show();
        Log.e("Zenith", "Error in http connection"+e.toString());
        }
-------------------------------------------------------------------------------------------
la struttura del database Mysql e' data dal codice

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

CREATE TABLE IF NOT EXISTS `comande` (

  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `tavolo` int(11) NOT NULL,
  `piatto` text NOT NULL,
  `data` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=22 ;

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

lo script Php insert.php invece risulta essere

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

<?

$hostname_localhost ="localhost";
$database_localhost ="zenith";
$username_localhost ="xxxxxxxxx";
$password_localhost ="xxxxxxxxxxx";
var_dump($_REQUEST); 
$localhost = mysql_connect($hostname_localhost,$username_localhost,$password_localhost)
or
trigger_error(mysql_error(),E_USER_ERROR);

mysql_select_db("zenith");
$sql_string = "INSERT INTO comande (id,tavolo,piatto,data) VALUES (NULL,'".$_REQUEST['tavolo']."','".$_REQUEST['piatto']."',CURRENT_TIMESTAMP)";
$sql=mysql_query($sql_string);
mysql_close();
?>