SQL Archivi - Petar Karan

11 Maggio 2020 1 commento

[mysql] Creare tabella pivot dinamica in MySQL (per esordienti totali)

Con il MySQL è possibile generare tabelle pivot dinamiche, ovvero che contengano nomi di colonne automatiche, in base ad una tabella di riferimento.

Quello che vogliamo ottenere sarà un risultato come il seguente:

Prima di cominciare creiamo un database con un po’ di dati in modo da poterci lavorare.

1. Creazione dati di partenza

Creiamo 3 tabelle: prodotti, agenti e vendite. Di seguito riporto le query per creare le singole tabelle ed inserire i dati di esempio.

PRODOTTI

Per creare la tabella prodotti eseguiamo la seguente query.

CREATE TABLE `prodotti` (
  `id` int(255) NOT NULL,
  `nome` varchar(1024) NOT NULL,
  `prezzo_unitario` double NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `prodotti` (

`id` int(255) NOT NULL,

`nome` varchar(1024) NOT NULL,

`prezzo_unitario` double NOT NULL

) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Inseriamo i dati nel modo seguente:

INSERT INTO `prodotti` (`id`, `nome`, `prezzo_unitario`) VALUES
(1, 'Mele', 600),
(2, 'Pere', 900),
(3, 'Banane', 750),
(4, 'Pesche', 1100),
(5, 'Kiwi', 1200);

INSERT INTO `prodotti` (`id`, `nome`, `prezzo_unitario`) VALUES

(1, 'Mele', 600),

(2, 'Pere', 900),

(3, 'Banane', 750),

(4, 'Pesche', 1100),

(5, 'Kiwi', 1200);

AGENTI

Andiamo adesso a creare la tabella degli agenti, in modo analogo:

CREATE TABLE `agenti` (
  `id` int(255) NOT NULL,
  `nome` varchar(100) NOT NULL,
  `cognome` varchar(100) NOT NULL,
  `stato` int(10) NOT NULL DEFAULT '1'
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `agenti` (

`id` int(255) NOT NULL,

`nome` varchar(100) NOT NULL,

`cognome` varchar(100) NOT NULL,

`stato` int(10) NOT NULL DEFAULT '1'

) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Ed inseriamo i dati:

INSERT INTO `agenti` (`id`, `nome`, `cognome`, `stato`) VALUES
(1, 'Mario', 'Rossi', 1),
(2, 'Luigi', 'Verdi', 1),
(3, 'Luisa', 'Neri', 1),
(4, 'Chiara', 'Celesti', 1),
(5, 'Paolo', 'Bianchi', 1),
(6, 'Anna', 'Rossi', 1);

INSERT INTO `agenti` (`id`, `nome`, `cognome`, `stato`) VALUES

(1, 'Mario', 'Rossi', 1),

(2, 'Luigi', 'Verdi', 1),

(3, 'Luisa', 'Neri', 1),

(4, 'Chiara', 'Celesti', 1),

(5, 'Paolo', 'Bianchi', 1),

(6, 'Anna', 'Rossi', 1);

VENDITE

Per le vendite andremo a creare la tabella in questo modo:

CREATE TABLE `vendite` (
  `id` int(255) NOT NULL,
  `idAgente` int(255) NOT NULL,
  `idProdotto` int(255) NOT NULL,
  `data` int(11) NOT NULL,
  `qta` int(255) NOT NULL DEFAULT '0',
  `importo` double NOT NULL,
  `stato` int(10) NOT NULL DEFAULT '1'
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `vendite` (

`id` int(255) NOT NULL,

`idAgente` int(255) NOT NULL,

`idProdotto` int(255) NOT NULL,

`data` int(11) NOT NULL,

`qta` int(255) NOT NULL DEFAULT '0',

`importo` double NOT NULL,

`stato` int(10) NOT NULL DEFAULT '1'

) ENGINE=InnoDB DEFAULT CHARSET=latin1;

A differenza delle precedenti due, qui voglio inserire dei dati casuali. Per inserire una riga di dati casuali, pescati dalle precedenti due tabelle, potremmo scrivere:

INSERT INTO `vendite`(`idAgente`, `idProdotto`, `data`, `importo`, `stato`) 
VALUES (ROUND(1+RAND()*4,0),ROUND(1+RAND()*4,0),UNIX_TIMESTAMP()-ROUND(RAND()*365*3600*24,0),ROUND(RAND()*10000,2),1);

1 2	INSERT INTO `vendite`(`idAgente`, `idProdotto`, `data`, `importo`, `stato`) VALUES (ROUND(1+RAND()4,0),ROUND(1+RAND()4,0),UNIX_TIMESTAMP()-ROUND(RAND()365360024,0),ROUND(RAND()10000,2),1);

Faccio notare che con l’istruzione ROUND(1+RAND()*4,0) inseriamo un valore intero casuale tra 1 e 5. Se volessimo generalizzare il procedimento potremmo scrivere ROUND(n+RAND()*(m-n),0) dove n è il minimo, mentre m è il massimo.

Con n = 3 e m = 8 avremmo infatti le seguenti operazioni:

RAND() genera un valore casuale decimale da 0 a 1
Moltiplicando per (8-3) il valore casuale sarà tra 0 e 5.
Sommano il risultato a 3 il valore casuale sarà un decimale tra 3 e 8. Potrebbe essere per esempio 6,4.
Usando ROUND() arrotondiamo a 0 cifre decimali ottenendo quindi un intero.

Se volessimo inserire più di una riga in una singola operazione, possiamo farlo scrivendo una procedura.

Dichiariamo quindi una procedura nel modo seguente, che inserisca 1000 valore casuali per volta.

DELIMITER //

CREATE PROCEDURE inserimenti_casuali()
BEGIN
	DECLARE tot INT UNSIGNED DEFAULT 0;
    WHILE tot <= 1000 DO
		INSERT INTO `vendite`(`idAgente`, `idProdotto`, `data`, `importo`, `stato`) 
                VALUES (ROUND(1+RAND()*4,0),ROUND(1+RAND()*4,0),UNIX_TIMESTAMP()-ROUND(RAND()*365*3600*24,0),ROUND(RAND()*10000,2),1);
        SET tot = tot + 1;
	END WHILE;
END//

DELIMITER ;

DELIMITER //

CREATE PROCEDURE inserimenti_casuali()

BEGIN

DECLARE tot INT UNSIGNED DEFAULT 0;

WHILE tot <= 1000 DO

INSERT INTO `vendite`(`idAgente`, `idProdotto`, `data`, `importo`, `stato`)

VALUES (ROUND(1+RAND()*4,0),ROUND(1+RAND()*4,0),UNIX_TIMESTAMP()-ROUND(RAND()*365*3600*24,0),ROUND(RAND()*10000,2),1);

SET tot = tot + 1;

END WHILE;

END//

DELIMITER ;

Chiamiamo la procedura digitando:

CALL inserimenti_casuali();

1	CALL inserimenti_casuali();

2. Creazione tabella pivot

Per creare una tabella pivot manualmente utilizziamo CASE WHEN ... END. A mano potremmo scrivere una query come quella di seguito:

SELECT 
	a.nome, a.cognome,
	ROUND(SUM(v.importo),2) AS totale,
	ROUND(SUM(
		CASE
			WHEN v.idProdotto = 1
			THEN v.importo
			ELSE 0
		END
	),2) AS mele,
	ROUND(SUM(
		CASE
			WHEN v.idProdotto = 2
			THEN v.importo
			ELSE 0
		END
	),2) AS pere
FROM
	agenti a LEFT JOIN vendite v ON v.idAgente = a.id
GROUP BY
	a.id

SELECT

a.nome, a.cognome,

ROUND(SUM(v.importo),2) AS totale,

ROUND(SUM(

CASE

WHEN v.idProdotto = 1

THEN v.importo

ELSE 0

END

),2) AS mele,

ROUND(SUM(

CASE

WHEN v.idProdotto = 2

THEN v.importo

ELSE 0

END

),2) AS pere

FROM

agenti a LEFT JOIN vendite v ON v.idAgente = a.id

GROUP BY

a.id

Ogni colonna della pivot sarà generata da:

ROUND(SUM(
	CASE
		WHEN v.idProdotto = 1
		THEN v.importo
		ELSE 0
	END
),2) AS mele,

ROUND(SUM(

CASE

WHEN v.idProdotto = 1

THEN v.importo

ELSE 0

END

),2) AS mele,

In questo caso noi abbiamo una tabella dei prodotti, dove sappiamo esserci all’id = 1 il prodotto chiamato mele.

Con SUM() sommiamo i valori (avremmo potuto usare COUNT(), AVG(), MAX(), MIN() ecc.) e con ROUND() arrotondiamo. Quest’ultima funzione in particolare non sarebbe necessaria, ma la utilizzo solo perché, visto che si parla di importi in euro, non ha senso calcolare un risultato al di sotto dei centesimi.

Quello che vogliamo fare ora è poter scrivere, quel pezzo di query, in modo iterativo, ripetendola per CIASCUN valore della tabella prodotti.

In particolare potremmo eseguire una query come la seguente:

SELECT
	GROUP_CONCAT(DISTINCT
		CONCAT(
        	'ROUND(SUM(
                CASE
                    WHEN v.idProdotto = ', p.id,'
                    THEN v.importo
                    ELSE 0
                END
            ),2) AS `', p.nome,'` '
        )
    )
FROM
	prodotti p;

SELECT

GROUP_CONCAT(DISTINCT

CONCAT(

'ROUND(SUM(

CASE

WHEN v.idProdotto = ', p.id,'

THEN v.importo

ELSE 0

END

),2) AS `', p.nome,'` '

)

FROM

prodotti p;

Il risultato di questa query sarebbe un singolo campo contenente i seguenti valori:

ROUND(SUM(
   CASE
	   WHEN v.idProdotto = 1
	   THEN v.importo
	   ELSE 0
   END
),2) AS `Mele` ,ROUND(SUM(
   CASE
	   WHEN v.idProdotto = 2
	   THEN v.importo
	   ELSE 0
   END
),2) AS `Pere` ,ROUND(SUM(
   CASE
	   WHEN v.idProdotto = 3
	   THEN v.importo
	   ELSE 0
   END
),2) AS `Banane` ,ROUND(SUM(
   CASE
	   WHEN v.idProdotto = 4
	   THEN v.importo
	   ELSE 0
   END
),2) AS `Pesche` ,ROUND(SUM(
   CASE
	   WHEN v.idProdotto = 5
	   THEN v.importo
	   ELSE 0
   END
),2) AS `Kiwi`

ROUND(SUM(

CASE

WHEN v.idProdotto = 1

THEN v.importo

ELSE 0

END

),2) AS `Mele` ,ROUND(SUM(

CASE

WHEN v.idProdotto = 2

THEN v.importo

ELSE 0

END

),2) AS `Pere` ,ROUND(SUM(

CASE

WHEN v.idProdotto = 3

THEN v.importo

ELSE 0

END

),2) AS `Banane` ,ROUND(SUM(

CASE

WHEN v.idProdotto = 4

THEN v.importo

ELSE 0

END

),2) AS `Pesche` ,ROUND(SUM(

CASE

WHEN v.idProdotto = 5

THEN v.importo

ELSE 0

END

),2) AS `Kiwi`

Faccio notare che grazie a GROUP_CONCAT() vengono inserite le virgole tra le singole righe.

Andiamo a creare quindi una procedura come la seguente:

DELIMITER //

CREATE PROCEDURE pivot_vendite_casuali()
BEGIN
	SET @sql = NULL;
	SELECT
		GROUP_CONCAT(DISTINCT
			CONCAT(
				'ROUND(SUM(
					CASE
						WHEN v.idProdotto = ', p.id,'
						THEN v.importo
						ELSE 0
					END
				),2) AS `', p.nome,'` '
			)
		)
	INTO @sql
	FROM
		prodotti p;
		
	SET @sql = CONCAT('
		SELECT 
			a.nome, a.cognome, ROUND(SUM(v.importo),2) AS totale, 
			', @sql, '
		FROM
			agenti a LEFT JOIN vendite v ON v.idAgente = a.id
		GROUP BY
			a.id
	');

	PREPARE esecuzione FROM @sql;
	EXECUTE esecuzione;
	DEALLOCATE PREPARE esecuzione;
END//

DELIMITER ;

DELIMITER //

CREATE PROCEDURE pivot_vendite_casuali()

BEGIN

SET @sql = NULL;

SELECT

GROUP_CONCAT(DISTINCT

CONCAT(

'ROUND(SUM(

CASE

WHEN v.idProdotto = ', p.id,'

THEN v.importo

ELSE 0

END

),2) AS `', p.nome,'` '

)

INTO @sql

FROM

prodotti p;

SET @sql = CONCAT('

SELECT

a.nome, a.cognome, ROUND(SUM(v.importo),2) AS totale,

', @sql, '

FROM

agenti a LEFT JOIN vendite v ON v.idAgente = a.id

GROUP BY

a.id

');

PREPARE esecuzione FROM @sql;

EXECUTE esecuzione;

DEALLOCATE PREPARE esecuzione;

END//

DELIMITER ;

Eseguiamo, come prima, la procedura:

CALL pivot_vendite_casuali();

1	CALL pivot_vendite_casuali();

Otterremo il risultato cercato.

Dobbiamo usare una procedura per evitare di incorrere in una serie di incongruenze legate all’interrogazione del database.

DIfatti, usando il PHPMyAdmin e lanciando solamente l’istruzione:

SET @sql = NULL;
SELECT
	GROUP_CONCAT(DISTINCT
		CONCAT(
			'ROUND(SUM(
				CASE
					WHEN v.idProdotto = ', p.id,'
					THEN v.importo
					ELSE 0
				END
			),2) AS `', p.nome,'` '
		)
	)
INTO @sql
FROM
	prodotti p;
	
SET @sql = CONCAT('
	SELECT 
		a.nome, a.cognome, ROUND(SUM(v.importo),2) AS totale, 
		', @sql, '
	FROM
		agenti a LEFT JOIN vendite v ON v.idAgente = a.id
	GROUP BY
		a.id
');

PREPARE esecuzione FROM @sql;
EXECUTE esecuzione;
DEALLOCATE PREPARE esecuzione;

SET @sql = NULL;

SELECT

GROUP_CONCAT(DISTINCT

CONCAT(

'ROUND(SUM(

CASE

WHEN v.idProdotto = ', p.id,'

THEN v.importo

ELSE 0

END

),2) AS `', p.nome,'` '

)

INTO @sql

FROM

prodotti p;

SET @sql = CONCAT('

SELECT

a.nome, a.cognome, ROUND(SUM(v.importo),2) AS totale,

', @sql, '

FROM

agenti a LEFT JOIN vendite v ON v.idAgente = a.id

GROUP BY

a.id

');

PREPARE esecuzione FROM @sql;

EXECUTE esecuzione;

DEALLOCATE PREPARE esecuzione;

Si incorrere nel seguente errore:

Fatal error: Uncaught Error: Call to a member function getClauses() on null in D:\xampp\phpMyAdmin\vendor\phpmyadmin\sql-parser\src\Utils\Query.php:564 Stack trace: #0 D:\xampp\phpMyAdmin\vendor\phpmyadmin\sql-parser\src\Utils\Query.php(681): PhpMyAdmin\SqlParser\Utils\Query::getClause(NULL, NULL, 'ORDER BY', -1, false) #1 D:\xampp\phpMyAdmin\libraries\DisplayResults.php(1385): PhpMyAdmin\SqlParser\Utils\Query::replaceClause(NULL, NULL, 'ORDER BY', '') #2 D:\xampp\phpMyAdmin\libraries\DisplayResults.php(4376): PMA\libraries\DisplayResults->_getUnsortedSqlAndSortByKeyDropDown(Array, '') #3 D:\xampp\phpMyAdmin\libraries\sql.lib.php(1689): PMA\libraries\DisplayResults->getTable(Object(mysqli_result), Array, Array, false) #4 D:\xampp\phpMyAdmin\libraries\sql.lib.php(1980): PMA_getHtmlForSqlQueryResultsTable(Object(PMA\libraries\DisplayResults), './themes/pmahom...', NULL, Array, false, 6, 6, NULL, Object(mysqli_result), Array) #5 D:\xampp\phpMyAdmin\libraries\sql.lib.php(2199): PMA_getQueryResponseForResultsReturned(Object(my in D:\xampp\phpMyAdmin\vendor\phpmyadmin\sql-parser\src\Utils\Query.php on line 564

3. Utilizzo della tabella nel PHP

Leggiamo la tabella tramite il PHP. Per farlo utilizzerò la classe MySQLdb come in questo esercizio sul MySQL Cluster.

Creiamo nella root del nostro sito un file MySQLdb.php contenente il seguente codice:

<?php defined("IN_ESEMPIO") or die("Hacking attempt!");


class MySQLdb {

	private $mysql = NULL;

	function __construct($cfg = NULL) {
	
		if( $cfg != NULL ) {
			
			$this->config($cfg['dbname'],$cfg['dbhost'],$cfg['dbuser'],$cfg['dbport'],$cfg['dbpass']);
			
			$this->connect_db();
			
		}
	
	}
	
	function __destruct() {
	
		$this->close_db();
	
	}
	
	function config( $dbname, $dbhost, $dbuser, $dbport, $dbpass ) {
	
		$this->dbname = $dbname;
		$this->dbhost = $dbhost;
		$this->dbuser = $dbuser;
		$this->dbport = $dbport;
		$this->dbpass = $dbpass;
	
	}
	
	function select_db($dbname = NULL) {
	
		$this->mysql->select_db($dbname ? $dbname : $this->dbname);
	
	}
	
	function connect_db() {
	
		if( $this->mysql == NULL ) {
		
			$this->mysql = new mysqli($this->dbhost, $this->dbuser, $this->dbpass);
			$this->select_db();
			$this->errorHandler();
		
		}
	
	}
	
	function close_db() {
	
		if( $this->mysql != NULL ) {
		
			$this->mysql->close();
			$this->mysql = NULL;
		
		}
	
	}
	
	function errorHandler() {
	
		if( $this->mysql->connect_errno ) {
		
			printf( "Connect failed: %s\n", $this->mysql->connect_error );
			die();
			
		}
	
	}
	
	function clean( $val ) {
		return $this->mysql->real_escape_string($val);
	}
	
	function in( $query ) {
	
		$status = $this->mysql->query( $query );
		
		if( $this->mysql->insert_id ) 
			return $this->mysql->insert_id;
		else 
			return $status;
	
	}
	
	function out( $query , $autoReduce = true ) {
	
		$result = $this->mysql->query( $query );
			
		if( !is_object($result) )
			return array();
		
		while ($linea = $result->fetch_assoc()) {
		
			$i = $i !== NULL ? $i + 1 : 0;
			
			foreach( $linea as $key => $value ) {
				$dati[$i][$key] = $value;
			}
			
		}
		
		$result->close();
		
		if( count($dati) == 1 && $autoReduce )
			return $dati[0];
		
		return $dati;
	
	}


}

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

<?php defined("IN_ESEMPIO") or die("Hacking attempt!");

class MySQLdb {

private $mysql = NULL;

function __construct($cfg = NULL) {

if( $cfg != NULL ) {

$this->config($cfg['dbname'],$cfg['dbhost'],$cfg['dbuser'],$cfg['dbport'],$cfg['dbpass']);

$this->connect_db();

}

function __destruct() {

$this->close_db();

}

function config( $dbname, $dbhost, $dbuser, $dbport, $dbpass ) {

$this->dbname = $dbname;

$this->dbhost = $dbhost;

$this->dbuser = $dbuser;

$this->dbport = $dbport;

$this->dbpass = $dbpass;

}

function select_db($dbname = NULL) {

$this->mysql->select_db($dbname ? $dbname : $this->dbname);

}

function connect_db() {

if( $this->mysql == NULL ) {

$this->mysql = new mysqli($this->dbhost, $this->dbuser, $this->dbpass);

$this->select_db();

$this->errorHandler();

}

function close_db() {

if( $this->mysql != NULL ) {

$this->mysql->close();

$this->mysql = NULL;

}

function errorHandler() {

if( $this->mysql->connect_errno ) {

printf( "Connect failed: %s\n", $this->mysql->connect_error );

die();

}

function clean( $val ) {

return $this->mysql->real_escape_string($val);

}

function in( $query ) {

$status = $this->mysql->query( $query );

if( $this->mysql->insert_id )

return $this->mysql->insert_id;

else

return $status;

}

function out( $query , $autoReduce = true ) {

$result = $this->mysql->query( $query );

if( !is_object($result) )

return array();

while ($linea = $result->fetch_assoc()) {

$i = $i !== NULL ? $i + 1 : 0;

foreach( $linea as $key => $value ) {

$dati[$i][$key] = $value;

}

$result->close();

if( count($dati) == 1 && $autoReduce )

return $dati[0];

return $dati;

}

Creiamo adesso un file index.php, nella stessa posizione, contenente il seguente codice:

<?php

define("IN_ESEMPIO",true);

include("MySQLdb.php");

$mysql = new MySQLdb(array(
	"dbname" => "esempio_pivot",
	"dbuser" => "root",
	"dbhost" => "127.0.0.1",
	"dbpass" => "",
	"dbport" => 3306
));

$prodotti = $mysql->out("SELECT * FROM prodotti");
$dati = $mysql->out("CALL pivot_vendite_casuali()");

?>
<table>
	<thead>
		<tr>
			<th>Nome</th>
			<th>Cognome</th>
			<th>Totale</th>
			<?php foreach( $prodotti as $p ) { ?>
			<th><?php echo $p['nome'] ?></th>
			<?php } ?>
		</tr>
	</thead>
	<tbody>
		<?php foreach( $dati as $d ) { ?>
		<tr>
			<td><?php echo $d['nome'] ?></td>
			<td><?php echo $d['cognome'] ?></td>
			<td><?php echo $d['totale'] ?></td>
			<?php foreach( $prodotti as $p ) { ?>
			<td><?php echo $d[$p['nome']] ?></td>
			<?php } ?>
		</tr>
		<?php } ?>
	</tbody>
</table>

<?php

define("IN_ESEMPIO",true);

include("MySQLdb.php");

$mysql = new MySQLdb(array(

"dbname" => "esempio_pivot",

"dbuser" => "root",

"dbhost" => "127.0.0.1",

"dbpass" => "",

"dbport" => 3306

));

$prodotti = $mysql->out("SELECT * FROM prodotti");

$dati = $mysql->out("CALL pivot_vendite_casuali()");

<table>

<thead>

<tr>

<th>Cognome</th>

<th>Totale</th>

<?php foreach( $prodotti as $p ) { ?>

<?php } ?>

</tr>

</thead>

<tbody>

<?php foreach( $dati as $d ) { ?>

<tr>

<?php foreach( $prodotti as $p ) { ?>

<?php } ?>

</tr>

<?php } ?>

</tbody>

</table>

Vediamo in particolare come nel vettore $prodotti abbiamo l’elenco di tutti i prodotti e quindi delle colonne della pivot, che si trovano in $dati. Il risultato che otterremo sarà simile a questo:

Vedi articolo

23 Ottobre 2019 Nessun commento

[mysql] Aumentare le performance del database MySQL

Questo esempio è sviluppato su un server Ubuntu con le seguenti caratteristiche:

32GB di RAM @2133 MHz, CPU Intel Xeon E3-1270v6 – quad core, 3,8GHz, 500GB disco SSD.

Le caratteristiche del server sono importanti, quanto il carico di lavoro che si intende sostenere per ottenere una giusta configurazione.

Prima di procedere oltre valutiamo anche lo stato iniziale del server ed eventualmente monitoriamo il carico di lavoro, per capire quanto margine abbiamo per le operazioni di calibrazione. A tale scopo ci saranno utili 2 programmi di Ubuntu.

Anzitutto usiamo htop per verificare l’uso di risorse della nostra macchina.

Se non dovessimo averlo installo eseguiamo:

sudo apt-get install htop

1	sudo apt-get install htop

Eseguendo htop da terminale dovremmo vedere una situazione simile a questa:

In questo caso possiamo notare che il server è scarico (in questo preciso momento).

Altro strumento che voglio utilizzare è mysqltuner. Questo ci permetterà di monitorare la situazione del server MySQL. Prima di lanciarlo è utile lasciare il server operativo per un tempo sufficiente (si parla di giorni o settimane, dipende dalla variabilità nell’utilizzo).

Se non disponiamo di mysqltuner installiamolo eseguendo:

sudo apt-get install mysqltuner

1	sudo apt-get install mysqltuner

Una volta eseguito, digitando semplicemente:

mysqltuner

1	mysqltuner

Vedremo un output con diverse statistiche e in fondo un elenco di raccomandazioni simile a questo:

A questo punto procediamo a modificare il file /etc/mysql/my.cnf impostando nuovi parametri (e poi provandoli).

Digitiamo dunque:

sudo nano /etc/mysql/my.cnf

1	sudo nano /etc/mysql/my.cnf

Andiamo ad impostare i seguenti parametri, di cui ne analizziamo alcuni:

innodb_buffer_pool_size

Questo parametro ci permette di impostare il pool del buffer. Il valore deve essere un multiplo del parametro innodb_buffer_pool_chunk_size moltiplicato per il parametro innodb_buffer_pool_instances. Il valore predefinito di innodb_buffer_pool_chunk_size è di 128MB, mentre innodb_buffer_pool_instances è impostato su 16.

Questo significa che un valore di 8GB per innodb_buffer_pool_size va bene, perché 8GB = 8192MB da cui 8192MB / ( 16 * 128MB ) = 4 (un numero intero).

Invece un valore di 15GB non andrebbe bene, perché 15GB = 15360MB da cui 15360MB / ( 16 * 128MB ) = 7,5 (un numero decimale).

query_cache_size

Il MySQL può tenere in cache le query già utilizzate, aumentando così notevolmente la velocità di esecuzione e le performance.

Anzitutto verifichiamo che la cache sia abilitata usando il commando:

SHOW VARIABLES LIKE 'have_query_cache';

1	SHOW VARIABLES LIKE 'have_query_cache';

Dopodiché possiamo impostare le seguenti variabili:

query_cache_size=2G
query_cache_limit=128M
query_cache_type=1

query_cache_size=2G

query_cache_limit=128M

query_cache_type=1

Le specifiche dimensioni dipendono dalla nostra disponibilità di RAM sul server.

max_connections

Questo parametro ci permette di stabilire il numero massimo di connessioni. Il valore predefinito è 150. Aumentando questo valore aumenterà il consumo di RAM.

Attenzione! Durante queste configurazioni assicuriamoci che la stima di memoria utilizzata da parte di mysqltuner non superi la massima memoria disponibile nel sistema, come in questo caso:

Entrambi i valori dovrebbero risultare OK, come nel seguente esempio:

Per fare una stima in anticipo sulle prestazioni possiamo usare il MySQL Calculator su questo indirizzo.

La precedente stima si ottiene sulla seguente configurazione di parametri:

innodb_buffer_pool_size=8G
innodb_buffer_pool_instances=8
query_cache_size=2G
query_cache_limit=128M
query_cache_type=1
max_connections=150
max_allowed_packet=512M
tmp_table_size=512M
max_heap_table_size=512M
thread_cache_size = 16K
table_cache = 1K
table_definition_cache = 4K
open_files_limit = 3K
key_buffer = 64M
long_query_time = 10
join_buffer_size=64M

innodb_buffer_pool_size=8G

innodb_buffer_pool_instances=8

query_cache_size=2G

query_cache_limit=128M

query_cache_type=1

max_connections=150

max_allowed_packet=512M

tmp_table_size=512M

max_heap_table_size=512M

thread_cache_size = 16K

table_cache = 1K

table_definition_cache = 4K

open_files_limit = 3K

key_buffer = 64M

long_query_time = 10

join_buffer_size=64M

Aumentando i valori naturalmente si aumenterà la quantità di risorse potenzialmente consumate dal database.

Una volta finite le modifiche al file my.cnf riavviamo il servizio MySQL digitando:

sudo service mysql restart

1	sudo service mysql restart

Come ultima operazione ottimizziamo il database, lanciando il seguente commando:

mysqlcheck -uadmin --optimize --all-databases

1	mysqlcheck -uadmin --optimize --all-databases

Se stiamo usando Plesk possiamo anche usare:

MYSQL_PWD=`cat /etc/psa/.psa.shadow` mysqlcheck -uadmin --optimize --all-databases

1	MYSQL_PWD=`cat /etc/psa/.psa.shadow` mysqlcheck -uadmin --optimize --all-databases

In questo modo la password dell’utente admin del database verrà prelevata dal file di configurazione di Plesk.

Fatto tutto questo monitoriamo il sistema e apportiamo eventuali correzioni.

Vedi articolo

28 Novembre 2018 Nessun commento

[magento] Errore SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry ‘0009032’ for key ‘UNQ_SALES_FLAT_ORDER_INCREMENT_ID’

Soluzione1: Questo errore si presenta quando si effettua un ordine su Magento, si viene indirizzati al pagamento, ma il pagamento non va a buon fine e si viene rimandati indietro sul negozio. Per risolvere questo problema è sufficiente modificare il file app/code/core/Mage/Sales/Model/Resource/Quote.php come indicato di seguito

Per risolvere il problema posizioniamoci nella cartella principale del negozio in Magento. Dopodiché andiamo a modificare il file app/code/core/Mage/Sales/Model/Resource/Quote.php

Apriamolo e cerchiamo la seguente parte di codice

    public function isOrderIncrementIdUsed($orderIncrementId)
    {
        $adapter   = $this->_getReadAdapter();
        $bind      = array(':increment_id' => (int)$orderIncrementId);
        $select    = $adapter->select();
        $select->from($this->getTable('sales/order'), 'entity_id')
            ->where('increment_id = :increment_id');
        $entity_id = $adapter->fetchOne($select, $bind);
        if ($entity_id > 0) {
            return true;
    }

        return false;
    }

public function isOrderIncrementIdUsed($orderIncrementId)

{

$adapter = $this->_getReadAdapter();

$bind = array(':increment_id' => (int)$orderIncrementId);

$select = $adapter->select();

$select->from($this->getTable('sales/order'), 'entity_id')

->where('increment_id = :increment_id');

$entity_id = $adapter->fetchOne($select, $bind);

if ($entity_id > 0) {

return true;

}

return false;

}

Alla linea quattro modifichiamo

$bind      = array(':increment_id' => (int)$orderIncrementId);

1	$bind = array(':increment_id' => (int)$orderIncrementId);

In modo che diventi:

$bind      = array(':increment_id' => $orderIncrementId);

1	$bind = array(':increment_id' => $orderIncrementId);

Salviamo il tutto.

Soluzione2: Questo errore si presenta quando per qualche ragione sono stati inseriti ordini sopra l’ultimo incrementale registrato in eav_entity_type. Per risolvere il problema è sufficiente incrementare sopra l’ultimo valore registrato il valore dell’incrementale.

Di seguito discutiamo il problema e la soluzione nel dettaglio.

Anzitutto l’errore che si presenta è simile a questo:

SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '0009032' for key 'UNQ_SALES_FLAT_ORDER_INCREMENT_ID', query was: INSERT INTO sales_flat_order (coupon_code, protect_code, shipping_description, is_virtual, store_id, customer_id, base_discount_amount, base_grand_total, base_shipping_amount, base_shipping_tax_amount, base_subtotal, base_tax_amount, base_to_global_rate, base_to_order_rate, discount_amount, grand_total, shipping_amount, shipping_tax_amount, store_to_base_rate, store_to_order_rate, subtotal, tax_amount, total_qty_ordered, customer_is_guest, customer_note_notify, customer_group_id, quote_id, base_shipping_discount_amount, base_subtotal_incl_tax, shipping_discount_amount, subtotal_incl_tax, weight, customer_dob, increment_id, applied_rule_ids, base_currency_code, customer_email, customer_firstname, customer_lastname, customer_middlename, customer_prefix, customer_suffix, customer_taxvat, discount_description, global_currency_code, order_currency_code, remote_ip, shipping_method, store_currency_code, store_name, x_forwarded_for, customer_note, created_at, updated_at, total_item_count, customer_gender, hidden_tax_amount, base_hidden_tax_amount, shipping_hidden_tax_amount, base_shipping_hidden_tax_amnt, shipping_incl_tax, base_shipping_incl_tax, gift_message_id, payment_fee_amount, base_payment_fee_amount, payment_installment_fee_amount, base_payment_installment_fee_amount, payment_tax_amount, base_payment_tax_amount, referral_code, ebizmarts_abandonedcart_flag, payment_fee_tax, base_payment_fee_tax, payment_percentage_fee, base_payment_percentage_fee) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, '1975-10-01 00:00:00', ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, '2018-11-28 11:14:46', '2018-11-28 11:14:46', ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)

Questo errore si presenta perché si sta cercando di inserire una chiave duplicata in sales_flat_order. Prima di tutto verifichiamo la cosa spostandoci in sales_flat_order ed effettuando la seguente query:

SELECT MAX(`entity_id`) FROM `sales_flat_order`

1	SELECT MAX(`entity_id`) FROM `sales_flat_order`

Nel mio caso il risultato della query era 10.009. Adesso spostiamoci su eav_entity_store. Qui dovremmo vedere una tabella con tutti gli incrementali. Cerchiamo quello che ci interessa. Per trovarlo guardiamo i campi entity_type_id e store_id. Gli entity_type_id hanno come riferimento la tabella eav_entity_type.

Per esempio il codice 5 corrisponde agli ordini, mentre il codice 6 alle fatture/ordini.

Nel mio caso mi accorgo che increment_last_id è su 9033, mentre il massimo valore inserito in sales_flat_order è di 10.009. Quindi mi è sufficiente portare questo valore al di sopra di quello attuale, per esempio impostandolo su 10.010.

Una volta fatto il problema è risolto.

Vedi articolo

23 Novembre 2018 Nessun commento

[mysql] Creare una funzione che converta un codice hex RGB di CSS in valori HSV

Oggi propongo una piccola soluzione in SQL al problema dell’ordinamento dei colori. Una cosa apparentemente elementare si rivela in realtà estremamente complessa. Per un approfondimento generale sul tema consiglio questo interessantissimo articolo: The incredibly challenging task of sorting colours

Per quello che mi interessa voglio proporre oggi la soluzione dell’utilizzo dello spazio HSV per poterli ordinare.

Supponiamo di avere un database di colori in RGB formato esadecimale del CSS (per intendersi il rosso sarebbe #ff0000). Nell’esempio riportato suppongo che il codice di colore sia sempre a 6 caratteri.

Detto tutto questo andiamo alla nostra funzione in SQL:

CREATE FUNCTION `RGB_TO_INT`(`colore` VARCHAR(100), parametro VARCHAR(1)) RETURNS INT(255) NOT DETERMINISTIC 
NO SQL SQL SECURITY DEFINER 
BEGIN
DECLARE intero INT(255);
DECLARE r INT(255);
DECLARE g INT(255);
DECLARE b INT(255);
DECLARE maxrgb INT(255);
DECLARE minrgb INT(255);
DECLARE v INT(255);
DECLARE h INT(255);
DECLARE s INT(255);
SET intero = 0;
SET r = CONVERT(CONV(SUBSTRING(colore,2,2), 16,10),UNSIGNED INTEGER);
SET g = CONVERT(CONV(SUBSTRING(colore,4,2), 16,10),UNSIGNED INTEGER);
SET b = CONVERT(CONV(SUBSTRING(colore,6,2), 16,10),UNSIGNED INTEGER);
SET v = 0;
SET h = 0;
SET s = 0;
SELECT GREATEST(r, g, b) INTO maxrgb;
SELECT LEAST(r, g, b) INTO minrgb;
SET v = maxrgb;
IF v = 0 THEN
	SET h = 0;
    SET s = 0;
ELSE
	SET s = ROUND(255 * (maxrgb-minrgb) / v);
    IF s = 0 THEN
    	SET h = 0;
    ELSE
    	IF maxrgb = r THEN
        	SET h = ROUND( 43 * ( g - b ) / (maxrgb-minrgb) );
        ELSEIF maxrgb = g THEN
        	SET h = ROUND( 85 + 43 * ( b - r ) / (maxrgb-minrgb) );
        ELSE
        	SET h = ROUND( 171 + 43 * ( r - g ) / (maxrgb-minrgb) );
        END IF;
    END IF;
END IF;
IF parametro = 'H' THEN
	SET intero = h;
ELSEIF parametro = 'S' THEN
	SET intero = s;
ELSE
	SET intero = v;
END IF;
RETURN intero;
END

CREATE FUNCTION `RGB_TO_INT`(`colore` VARCHAR(100), parametro VARCHAR(1)) RETURNS INT(255) NOT DETERMINISTIC

NO SQL SQL SECURITY DEFINER

BEGIN

DECLARE intero INT(255);

DECLARE r INT(255);

DECLARE g INT(255);

DECLARE b INT(255);

DECLARE maxrgb INT(255);

DECLARE minrgb INT(255);

DECLARE v INT(255);

DECLARE h INT(255);

DECLARE s INT(255);

SET intero = 0;

SET r = CONVERT(CONV(SUBSTRING(colore,2,2), 16,10),UNSIGNED INTEGER);

SET g = CONVERT(CONV(SUBSTRING(colore,4,2), 16,10),UNSIGNED INTEGER);

SET b = CONVERT(CONV(SUBSTRING(colore,6,2), 16,10),UNSIGNED INTEGER);

SET v = 0;

SET h = 0;

SET s = 0;

SELECT GREATEST(r, g, b) INTO maxrgb;

SELECT LEAST(r, g, b) INTO minrgb;

SET v = maxrgb;

IF v = 0 THEN

SET h = 0;

SET s = 0;

ELSE

SET s = ROUND(255 * (maxrgb-minrgb) / v);

IF s = 0 THEN

SET h = 0;

ELSE

IF maxrgb = r THEN

SET h = ROUND( 43 * ( g - b ) / (maxrgb-minrgb) );

ELSEIF maxrgb = g THEN

SET h = ROUND( 85 + 43 * ( b - r ) / (maxrgb-minrgb) );

ELSE

SET h = ROUND( 171 + 43 * ( r - g ) / (maxrgb-minrgb) );

END IF;

IF parametro = 'H' THEN

SET intero = h;

ELSEIF parametro = 'S' THEN

SET intero = s;

ELSE

SET intero = v;

END IF;

RETURN intero;

END

Per utilizzarla possiamo fare così:

SELECT 
* 
FROM 
tabella_colori tc
ORDER BY
RGB_TO_INT(tc.rgb_hex,'V'),
RGB_TO_INT(tc.rgb_hex,'H'),
RGB_TO_INT(tc.rgb_hex,'S')

SELECT

FROM

tabella_colori tc

ORDER BY

RGB_TO_INT(tc.rgb_hex,'V'),

RGB_TO_INT(tc.rgb_hex,'H'),

RGB_TO_INT(tc.rgb_hex,'S')

Vedi articolo

22 Settembre 2018 Nessun commento

[mysql] #1093 – Table is specified twice, both as a target for ‘UPDATE’ and as a separate source for data

Questo errore si presenta quando si tenta di aggiornare una tabella prelevando i dati dalla medesima. Per comprendere il problema immaginiamo di avere una tabella come la seguente:

id	indirizzo
1	http://indirizzosito.com/?opzione=
2	bianco
3	nero

Adesso supponiamo di volerla modificare in modo tale che il campo indirizzo all’id = 1 venga concatenato come prefisso su tutti gli altri campi indirizzo con id > 1. Quello che vogliamo ottenere è una tabella simile a questa:

id	indirizzo
1	http://indirizzosito.com/?opzione=
2	http://indirizzosito.com/?opzione=bianco
3	http://indirizzosito.com/?opzione=nero

Prima di cominciare possiamo generare la tabella di prova con:

CREATE TABLE `elenco_importato` (
  `id` int(255) NOT NULL,
  `indirizzo` varchar(1024) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `elenco_importato` (

`id` int(255) NOT NULL,

`indirizzo` varchar(1024) NOT NULL

) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Ed inseriamo dentro i dati con:

INSERT INTO `elenco_importato` (`id`, `indirizzo`) VALUES
(1, 'http://indirizzosito.com/?opzione='),
(2, 'bianco'),
(3, 'nero'),
(4, 'giallo'),
(5, 'verde'),
(6, 'oro'),
(7, 'blu'),
(8, 'pervinca');

INSERT INTO `elenco_importato` (`id`, `indirizzo`) VALUES

(1, 'http://indirizzosito.com/?opzione='),

(2, 'bianco'),

(3, 'nero'),

(4, 'giallo'),

(5, 'verde'),

(6, 'oro'),

(7, 'blu'),

(8, 'pervinca');

A questo punto verrebbe spontaneo pensare che si possa utilizzare la seguente query (ATTENZIONE! Questa non funziona!):

UPDATE elenco_importato AS t1 
	SET 
		t1.indirizzo = CONCAT(
			(SELECT t2.indirizzo FROM elenco_importato AS t2 WHERE t2.id = 1), 
			t1.indirizzo
		) 
	WHERE 
		t1.id > 1

UPDATE elenco_importato AS t1

SET

t1.indirizzo = CONCAT(

(SELECT t2.indirizzo FROM elenco_importato AS t2 WHERE t2.id = 1),

t1.indirizzo

)

WHERE

t1.id > 1

Questa query produce però il seguente messaggio di errore:

#1093 – Table ‘t1’ is specified twice, both as a target for ‘UPDATE’ and as a separate source for data

L’errore dipende dal fatto che il database non ci consente di modificare una tabella dalla quale preleviamo contemporaneamente i dati. Possiamo aggirare il problema “ingannando” il database con la seguente modifica:

UPDATE elenco_importato AS t1 
	SET 
		t1.indirizzo = CONCAT(
			(SELECT t2.indirizzo FROM (SELECT * FROM elenco_importato) AS t2 WHERE t2.id = 1), 
			t1.indirizzo
		) 
	WHERE 
		t1.id > 1

UPDATE elenco_importato AS t1

SET

t1.indirizzo = CONCAT(

(SELECT t2.indirizzo FROM (SELECT * FROM elenco_importato) AS t2 WHERE t2.id = 1),

t1.indirizzo

)

WHERE

t1.id > 1

Faccio notare che a:

elenco_importato AS t2

1	elenco_importato AS t2

Abbiamo sostituito:

(SELECT * FROM elenco_importato) AS t2

1	(SELECT * FROM elenco_importato) AS t2

In questo modo la tabella viene per prima cosa prelevata e poi utilizzata per i dati dell’UPDATE.

Vedi articolo

17 Settembre 2018 Nessun commento

[magento] Errore in fase di download degli ordini

Problema: quando si cerca di scaricare l’elenco degli ordini viene restituito il seguente errore MAGENTO – Errore in fase di download degli ordini System.ServiceModel.FaultException: SQLSTATE[HY000]: General error: 1030 Got error 28 from storage engine, query was: DESCRIBE sales_flat_order

Soluzione: il disco del server è pieno, probabilmente riempito dai file log di Magento stesso e non è possibile effettuare la maggior parte delle operazioni. Basta cancellare i file log oppure liberare spazio sul server.

La seguente soluzione è stata testata su Magento 1.9.3 e sistema operativo CentOS.

Anzitutto premetto che l’errore non è per forza causato da Magento, ma potrebbe trattarsi del server che per qualche motivo si è riempito (per esempio a causa di aggiornamenti). Anzitutto verifichiamo la nostra situazione digitando su server linux:

df -h

df -h

Dovremmo vedere un risultato simile al seguente:

Notiamo come la partizione / sia montata su /dev/mapper/centos-root al quale rimangono disponibili solamente 20KB di spazio.

Nella maggior parte dei casi è altamente probabile che i log di magento abbiano saturato lo spazio a disposizione. Per verificarlo spostiamoci su /var/www/sito_magento/var/log.

Digitiamo:

ls -l

ls -l

In questo modo dovremmo vedere qualcosa di simile a questo:

Notate che il file system.log ha superato l’assurda dimensione di 57GB! A questo punto puliamo tutti i file log, eventualmente li possiamo prima salvare da qualche parte se lo riteniamo necessario. Digitiamo:

rm -rf *.log

1	rm -rf *.log

In questo modo cancelleremo tutti i file log presenti nella cartella.

Verifichiamo nuovamente la situazione digitando:

df -h

df -h

Dovremmo vedere qualcosa di simile a quanto segue:

Notiamo che si sono liberati 56GB e abbiamo nuovamente il 44% dello spazio a disposizione.

Vedi articolo

18 Aprile 2018 Nessun commento

[mysql] Svuotare un database o eliminare tutte le tabelle da terminale (remove o truncate)

Obiettivo: eliminare oppure svuotare tutte le tabelle di un database con un singolo commando da terminale

Questa soluzione è molto utile specialmente se si devono svuotare numerosi database. Abbiamo due possibilità:

1. Eliminare le tabelle (drop)

mysql -u USER -p'PASSWORD' -Nse 'show tables' DATABASE | while read table; do mysql -u USER -p'PASSWORD' -e \"drop table $table\" DATABASE; done

1	mysql -u USER -p'PASSWORD' -Nse 'show tables' DATABASE \| while read table; do mysql -u USER -p'PASSWORD' -e \"drop table $table\" DATABASE; done

2. Svuotare le tabelle senza eliminarle (truncate)

mysql -u USER -p'PASSWORD' -Nse 'show tables' DATABASE | while read table; do mysql -u USER -p'PASSWORD' -e \"truncate table $table\" DATABASE; done

1	mysql -u USER -p'PASSWORD' -Nse 'show tables' DATABASE \| while read table; do mysql -u USER -p'PASSWORD' -e \"truncate table $table\" DATABASE; done

In entrambi i casi al posto di USER va messo l’utente del database, al posto di PASSWORD la password dell’utente sul database e al posto di DATABASE il nome del database sul quale vogliamo lavorare.

Vedi articolo

30 Marzo 2018 1 commento

[php] Interroghiamo un database MySQL su cluster mediante il PHP

Obiettivo: anzitutto creiamo una classe per mediare la connessione MySQL (con o senza cluster è indifferente) e testiamo il cluster MySQL creato nell’esercizio precedente

Per realizzare il nostro scopo avviamo un server apache sulla nostra macchina host, dove abbiamo già avviato 3 macchine virtuali che costituiscono il nostro MySQL Cluster.

Per avviare il server apache mi avvarrò di XAMPP.

Inoltre prima di procedere devo inoltrare la porta 3306 verso il MySQL Cluster Manager che ho configurato in precedenza. Per farlo mi è sufficiente spostarmi in File ⇒ Preferenze ⇒ Rete

Selezioniamo la nostra Rete NAT corrispondente al cluster che abbiamo realizzato e andiamo a configurare l’inoltro delle porte, per ottenere qualcosa di simile a questo:

A questo punto andiamo nel nostro PHP. Creiamo anzitutto un file /inc/MySQLDb.php contenente il seguente codice:

class MySQLdb {

	private $mysql = NULL;

	function __construct($cfg = NULL) {
	
		if( $cfg != NULL ) {
			
			$this->config($cfg['dbname'],$cfg['dbhost'],$cfg['dbuser'],$cfg['dbport'],$cfg['dbpass']);
			
			$this->connect_db();
			
		}
	
	}
	
	function __destruct() {
	
		$this->close_db();
	
	}
	
	function config( $dbname, $dbhost, $dbuser, $dbport, $dbpass ) {
	
		$this->dbname = $dbname;
		$this->dbhost = $dbhost;
		$this->dbuser = $dbuser;
		$this->dbport = $dbport;
		$this->dbpass = $dbpass;
	
	}
	
	function select_db($dbname = NULL) {
	
		$this->mysql->select_db($dbname ? $dbname : $this->dbname);
	
	}
	
	function connect_db() {
	
		if( $this->mysql == NULL ) {
		
			$this->mysql = new mysqli($this->dbhost, $this->dbuser, $this->dbpass);
			$this->select_db();
			$this->errorHandler();
		
		}
	
	}
	
	function close_db() {
	
		if( $this->mysql != NULL ) {
		
			$this->mysql->close();
			$this->mysql = NULL;
		
		}
	
	}
	
	function errorHandler() {
	
		if( $this->mysql->connect_errno ) {
		
			printf( "Connect failed: %s\n", $this->mysql->connect_error );
			die();
			
		}
	
	}
	
	function clean( $val ) {
		return $this->mysql->real_escape_string($val);
	}
	
	function in( $query ) {
	
		$status = $this->mysql->query( $query );
		
		if( $this->mysql->insert_id ) 
			return $this->mysql->insert_id;
		else 
			return $status;
	
	}
	
	function out( $query , $autoReduce = true ) {
	
		$result = $this->mysql->query( $query );
			
		if( !is_object($result) )
			return array();
		
		while ($linea = $result->fetch_assoc()) {
		
			$i = $i !== NULL ? $i + 1 : 0;
			
			foreach( $linea as $key => $value ) {
				$dati[$i][$key] = $value;
			}
			
		}
		
		$result->close();
		
		if( count($dati) == 1 && $autoReduce )
			return $dati[0];
		
		return $dati;
	
	}


}

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

class MySQLdb {

private $mysql = NULL;

function __construct($cfg = NULL) {

if( $cfg != NULL ) {

$this->config($cfg['dbname'],$cfg['dbhost'],$cfg['dbuser'],$cfg['dbport'],$cfg['dbpass']);

$this->connect_db();

}

function __destruct() {

$this->close_db();

}

function config( $dbname, $dbhost, $dbuser, $dbport, $dbpass ) {

$this->dbname = $dbname;

$this->dbhost = $dbhost;

$this->dbuser = $dbuser;

$this->dbport = $dbport;

$this->dbpass = $dbpass;

}

function select_db($dbname = NULL) {

$this->mysql->select_db($dbname ? $dbname : $this->dbname);

}

function connect_db() {

if( $this->mysql == NULL ) {

$this->mysql = new mysqli($this->dbhost, $this->dbuser, $this->dbpass);

$this->select_db();

$this->errorHandler();

}

function close_db() {

if( $this->mysql != NULL ) {

$this->mysql->close();

$this->mysql = NULL;

}

function errorHandler() {

if( $this->mysql->connect_errno ) {

printf( "Connect failed: %s\n", $this->mysql->connect_error );

die();

}

function clean( $val ) {

return $this->mysql->real_escape_string($val);

}

function in( $query ) {

$status = $this->mysql->query( $query );

if( $this->mysql->insert_id )

return $this->mysql->insert_id;

else

return $status;

}

function out( $query , $autoReduce = true ) {

$result = $this->mysql->query( $query );

if( !is_object($result) )

return array();

while ($linea = $result->fetch_assoc()) {

$i = $i !== NULL ? $i + 1 : 0;

foreach( $linea as $key => $value ) {

$dati[$i][$key] = $value;

}

$result->close();

if( count($dati) == 1 && $autoReduce )

return $dati[0];

return $dati;

}

Andiamo adesso a creare il nostro file index.php:

<?php

	include("inc/MySQLDb.php");
	
	$mysql = new MySQLdb(array(
		"dbname" => "cluster_test",
		"dbhost" => "192.168.56.1",
		"dbuser" => "root",
		"dbpass" => "password",
		"dbport" => 3306
	));
	
	$utenti = $mysql->out("SELECT * FROM cluster_test",false);
	
	print_r( $utenti );


?>

<?php

include("inc/MySQLDb.php");

$mysql = new MySQLdb(array(

"dbname" => "cluster_test",

"dbhost" => "192.168.56.1",

"dbuser" => "root",

"dbpass" => "password",

"dbport" => 3306

));

$utenti = $mysql->out("SELECT * FROM cluster_test",false);

print_r( $utenti );

Eseguendo la pagina index.php è molto probabile incorrere nel seguente output di errori:

Warning: mysqli::__construct(): MySQL server has gone away in D:\xampp\htdocs\tests\mysqlcluster\inc\MySQLDb.php on line 45

Warning: mysqli::__construct(): Error while reading greeting packet. PID=2612 in D:\xampp\htdocs\tests\mysqlcluster\inc\MySQLDb.php on line 45

Warning: mysqli::__construct(): (HY000/2006): MySQL server has gone away in D:\xampp\htdocs\tests\mysqlcluster\inc\MySQLDb.php on line 45

Warning: mysqli::select_db(): Couldn't fetch mysqli in D:\xampp\htdocs\tests\mysqlcluster\inc\MySQLDb.php on line 37
Connect failed: MySQL server has gone away 
Warning: mysqli::close(): Couldn't fetch mysqli in D:\xampp\htdocs\tests\mysqlcluster\inc\MySQLDb.php on line 57

Warning: mysqli::__construct(): MySQL server has gone away in D:\xampp\htdocs\tests\mysqlcluster\inc\MySQLDb.php on line 45

Warning: mysqli::__construct(): Error while reading greeting packet. PID=2612 in D:\xampp\htdocs\tests\mysqlcluster\inc\MySQLDb.php on line 45

Warning: mysqli::__construct(): (HY000/2006): MySQL server has gone away in D:\xampp\htdocs\tests\mysqlcluster\inc\MySQLDb.php on line 45

Warning: mysqli::select_db(): Couldn't fetch mysqli in D:\xampp\htdocs\tests\mysqlcluster\inc\MySQLDb.php on line 37

Connect failed: MySQL server has gone away

Warning: mysqli::close(): Couldn't fetch mysqli in D:\xampp\htdocs\tests\mysqlcluster\inc\MySQLDb.php on line 57

Questo tipo di errore può dipendere o dal fatto che stiamo richiedendo una query troppo grossa, oppure perché il nostro Server MySQL non è predisposto per accettare le connessioni dall’esterno.

Per risolvere il problema procediamo nel modo seguente.

Anzitutto creiamo un utente apposito con il quale ci collegheremo dall’esterno (nel mio caso gli garantirò tutti i permessi possibili su tutti i database, ovviamente andrebbe limitato ad uno specifico database).

Accediamo al mysql sul nostro cluster manager digitando:

mysql -u root -p

1	mysql -u root -p

Inseriamo la password di root e poi eseguiamo le seguenti due query per creare l’utente zelda:

GRANT ALL PRIVILEGES ON *.* TO 'zelda'@'192.168.56.1' IDENTIFIED BY 'password' WITH GRANT OPTION;
FLUSH PRIVILEGES;

1 2	GRANT ALL PRIVILEGES ON . TO 'zelda'@'192.168.56.1' IDENTIFIED BY 'password' WITH GRANT OPTION; FLUSH PRIVILEGES;

Faccio notare che con ‘zelda’@’192.168.56.1′ garantiamo l’accesso all’utente zelda dall’indirizzo 192.168.56.1 che è l’indirizzo esterno della nostra rete NAT su Virtual Box.

Digitiamo exit per uscire e andiamo a modificare il file di configurazione del mysql:

sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf

1	sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf

Cerchiamo la voce bind-address e commentiamola in modo che risulti in questo modo:

[mysqld]
pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
datadir         = /var/lib/mysql
log-error       = /var/log/mysql/error.log
# By default we only accept connections from localhost
#bind-address   = 127.0.0.1
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

[mysqld]

pid-file = /var/run/mysqld/mysqld.pid

socket = /var/run/mysqld/mysqld.sock

datadir = /var/lib/mysql

log-error = /var/log/mysql/error.log

# By default we only accept connections from localhost

#bind-address = 127.0.0.1

# Disabling symbolic-links is recommended to prevent assorted security risks

symbolic-links=0

Infine modifichiamo anche il file /etc/mysql/my.cnf

sudo nano /etc/mysql/my.cnf

1	sudo nano /etc/mysql/my.cnf

Inseriamo in fondo max_allowed_packet=16M in modo tale che il file risulti così

[mysqld]
ndbcluster
max_allowed_packet=16M

[mysqld]

ndbcluster

max_allowed_packet=16M

Quest’ultima modifica è di solito sufficiente per correggere il precedente errore, se dovesse ripresentarsi possiamo alzare ancora questo valore.

Fatto tutto questo riavviamo il servizio mysql digitando:

sudo service mysql restart

1	sudo service mysql restart

Se abbiamo fatto tutto bene non ci saranno errori.

A questo punto modifichiamo il file index.php di sopra perché appaia in questo modo:

<?php

	error_reporting(E_ALL & ~E_NOTICE);

	include("inc/MySQLDb.php");
	
	$mysql = new MySQLdb(array(
		"dbname" => "cluster_test",
		"dbhost" => "192.168.56.1",
		"dbuser" => "zelda",
		"dbpass" => "password",
		"dbport" => 3306
	));
	
	$utenti = $mysql->out("SELECT * FROM cluster_test",false);
	
	print_r( $utenti );


?>

<?php

error_reporting(E_ALL & ~E_NOTICE);

include("inc/MySQLDb.php");

$mysql = new MySQLdb(array(

"dbname" => "cluster_test",

"dbhost" => "192.168.56.1",

"dbuser" => "zelda",

"dbpass" => "password",

"dbport" => 3306

));

$utenti = $mysql->out("SELECT * FROM cluster_test",false);

print_r( $utenti );

Eseguendo la pagina dovremmo vedere qualcosa di simile a questo:

Questo significa che è tutto andato correttamente. A questo punto divertiamoci ad inserire qualche altro utente e generare una tabella con HTML corretto. Modifichiamo il file PHP come segue:

<?php

	error_reporting(E_ALL & ~E_NOTICE);

	include("inc/MySQLDb.php");
	
	// connettiamoci al database
	$mysql = new MySQLdb(array(
		"dbname" => "cluster_test",
		"dbhost" => "192.168.56.1",
		"dbuser" => "zelda",
		"dbpass" => "password",
		"dbport" => 3306
	));
	
	// inseriamo un nuovo nominativo
	$mysql->in("INSERT INTO cluster_test (nome,cognome) VALUES('Luigi','Verdi')");
	
	// leggiamo tutti gli utenti
	$utenti = $mysql->out("SELECT * FROM cluster_test",false);


?>
<html>

	<head>
		<title>Esempio Cluster MySQL e PHP</title>
	</head>
	
	<body>
	
		<table border="1">
		
			<thead>
				<tr>
					<th>Nome</th>
					<th>Cognome</th>
				</tr>
			</thead>
			
			<tbody>
				<?php foreach( $utenti as $k => $v ) { ?>
				<tr>
					<td><?php echo $v['nome'] ?></td>
					<td><?php echo $v['cognome'] ?></td>
				</tr>
				<? } ?>
			</tbody>
			
		</table>
		
	</body>

</html>

<?php

error_reporting(E_ALL & ~E_NOTICE);

include("inc/MySQLDb.php");

// connettiamoci al database

$mysql = new MySQLdb(array(

"dbname" => "cluster_test",

"dbhost" => "192.168.56.1",

"dbuser" => "zelda",

"dbpass" => "password",

"dbport" => 3306

));

// inseriamo un nuovo nominativo

$mysql->in("INSERT INTO cluster_test (nome,cognome) VALUES('Luigi','Verdi')");

// leggiamo tutti gli utenti

$utenti = $mysql->out("SELECT * FROM cluster_test",false);

<html>

<head>

<title>Esempio Cluster MySQL e PHP</title>

</head>

<body>

<thead>

<tr>

<th>Cognome</th>

</tr>

</thead>

<tbody>

<?php foreach( $utenti as $k => $v ) { ?>

<tr>

</tr>

<? } ?>

</tbody>

</table>

</body>

</html>

Il risultato, aggiornando la pagina, dovrebbe essere simile a questo:

Infine facciamo una prova per caricare di dati il nostro cluster.

Modifichiamo il file index.php alterando la parte dell’inserimento degli utenti come segue:

	// inseriamo un nuovo nominativo
	for( $i = 0; $i < 10000; $i++ )
		$mysql->in("INSERT INTO cluster_test (nome,cognome) VALUES('Luigi$i','Verdi$i')");

// inseriamo un nuovo nominativo

for( $i = 0; $i < 10000; $i++ )

$mysql->in("INSERT INTO cluster_test (nome,cognome) VALUES('Luigi$i','Verdi$i')");

Prima di aggiornare la pagina andiamo nel nostro cluster manager e digitiamo:

ndb_mgm -e 'ALL REPORT MEMORYUSAGE'

1	ndb_mgm -e 'ALL REPORT MEMORYUSAGE'

Quello che dovremmo vedere sarà un risultato simile a questo:

Inserendo 10.000 record, con il precedente script, dovremmo vedere questa “importante” differenza:

Vedi articolo

27 Marzo 2018 1 commento

[ubuntu] Creare cluster MySQL

Obiettivo: creare un cluster MySQL, ovvero un server MySQL distribuito, utilizzando 3 macchine con Ubuntu, 1 per il gestore del server e 2 per i nodi

Anche questa volta, come negli altri esempi, mi avvarrò di VirtualBox, per emulare il gruppo di server. Ovviamente la procedura è valida anche su macchina fisiche oppure utilizzando un altro tipo di macchine virtuali. La struttura che andremo a creare sarà la seguente:

1. Preparazione di Ubuntu su VirtualBox

Anzitutto scarichiamo ed installiamo VirtualBox dal sito ufficiale.

Quello che voglio creare, prima di cominciare con la configurazione del cluster, sono 3 macchine con sopra Ubuntu perfettamente identiche. Sottolineo il fatto che non sia necessario che siano identiche, ma solo che su tutte e tre sia configurato correttamente il MySQL.

Cominciamo configurando la prima macchina virtuale per metterci sopra Ubuntu.

Come parametri di configurazione, nella procedura guidata, scegliamo:

Versione: Ubuntu (64-bit)
Dimensione memoria: 1024MB
Disco virtuale: 10GB
Tipo di disco: VDI
Tipo allocazione: dinamica

Una volta creata la macchina virtuale facciamo partire ed inseriamo la ISO per l’installazione che abbiamo scaricato dal sito ufficiale di Ubuntu. Ricordo di scaricare la versione Ubuntu Server 64-bit.

Per questo esempio utilizzerò la versione 16.04.2 di Ubuntu Server.

Avviamo normalmente l’installazione, per chi non l’avesse mai fatto suggerisco di seguire la prima parte di: Installazione Ubuntu webserver pronto all’uso [per esordienti totali]

L’unica differenza è che non installeremo il server apache al momento in cui ci verrà richiesto.

Come nome del host mettiamo ubuntu-mysql-manager (si tratta comunque di un nome a piacere):

Ad un certo punto dell’installazione ci verrà proposto quanto segue:

Assicuriamoci di aver selezionato solamente le voci della figura precedente, per selezionarle muoviamoci con le frecce direzionali e spuntiamo le voci premendo SPAZIO. Con la tabulazione spostiamoci su Continua e premiamo INVIO dopo aver selezionato le voci correttamente.

Una volta completata l’installazione riavviamo la macchina virtuale ed entriamo con il nome utente che abbiamo creato.

A questo punto spegniamo la macchina virtuale digitando:

sudo shutdown -t now

1	sudo shutdown -t now

Prima di procedere vogliamo creare una rete NAT interna sulla quale testare le nostre macchine. Per farlo utilizziamo le impostazioni di virtual box andando su File ⇒ Preferenze (oppure premendo CTRL+G dal panello di controllo di virtual box). A questo punto spostiamoci su Rete nel modo seguente e scegliamo di aggiungere una nuova rete.

Una volta creata la nuova rete dovremmo vedere comparire una voce NatNetwork come nell’immagine seguente:

Una volta selezionata clicchiamo sul pulsante per modificarla ed impostiamola nel modo seguente:

Faccio notare che l’impostazione della rete è arbitraria e a nostra scelta, quella predefinita parte sulla configurazione 10.0.2.0/24 che ho modificato nella classica rete domestica col 192.168.0.0/24 a titolo di esercizio. Questo significa che avremo a disposizione 254 host dal 192.16.0.1 al 192.16.0.254.

Diamogli OK e andiamo nelle impostazioni della macchina virtuale mettendo nella configurazione di rete la rete appena creata, in maniera seguente:

Riavviamo la macchina virtuale e colleghiamoci in SSH effettuando l’inoltro delle porte.

Per farlo, una volta partita la macchina virtuale, digitiamo:

ifconfig

ifconfig

Il risultato dovrebbe essere qualcosa di simile a questo:

Nel mio caso noto che la macchina virtuale, di quello che diventerà il manager, si trova all’indirizzo 192.168.0.5 distribuito dal DHCP. Tornando su File ⇒ Preferenze andiamo sulla rete creata prima e apriamo le configurazioni, poi clicchiamo su Inoltro delle porte e aggiungiamo la seguente regola:

Diamo OK. Adesso possiamo collegarci in SSH dalla nostra macchina host (detto banalmente: dal nostro PC che ospita le macchine virtuali) usando l’indirizzo 192.168.56.1 porta 22.

Fatta questa bella impostazione possiamo passare al passaggio successivo.

2. Installazione MySQL sul primo Nodo

Anzitutto installiamo il cluster su tutti i nostri nodi, nello specifico lo installiamo sulla macchina appena creata che poi duplicheremo opportunamente. Per l’installazione possiamo seguire i passaggi indicati sul sito ufficiale, oppure scaricare dalla pagina ufficiale i file .deb necessari.

Io utilizzerò la repository APT, ma prima di cominciare installiamo le dipendenze necessarie al funzionamento del cluster:

sudo apt-get install python-paramiko libclass-methodmaker-perl libaio1

1	sudo apt-get install python-paramiko libclass-methodmaker-perl libaio1

A questo punto dobbiamo installare la MySQL APT Repository sul nostro sistema. Per farlo ci è sufficiente scaricare il file di installazione dal sito ufficiale andando qui. Nello specifico digitiamo:

wget https://dev.mysql.com/get/mysql-apt-config_0.8.9-1_all.deb

1	wget https://dev.mysql.com/get/mysql-apt-config_0.8.9-1_all.deb

Una volta scaricato il file procediamo all’installazione:

sudo dpkg -i mysql-apt-config_0.8.9-1_all.deb

1	sudo dpkg -i mysql-apt-config_0.8.9-1_all.deb

A questo punto ci verrà chiesto che cosa intendiamo configurare e scegliamo la voce MySQL Server & Cluster:

Ci verrà chiesta la versione che intendiamo installare e selezioniamo mysql-cluster-7.6:

A questo punto torniamo alla schermata precedente e selezioniamo OK:

A questo punto non ci rimane che installare il componente base per tutti i nodi digitando:

sudo apt-get update
sudo apt-get install mysql-cluster-community-server

1 2	sudo apt-get update sudo apt-get install mysql-cluster-community-server

L’update ci serve per aggiornare la repository dopo le recenti modifiche, altrimenti è probabile che sia impossibile trovare mysql-cluster-community-server.

Ad un certo punto ci verrà chiesto di impostare una password per l’utente root del database, impostiamo una password e segniamocela da qualche parte. Ricordo che l’utente root non è quello del sistema, ma del database.

A questo punto abbiamo preparato un nodo.

Replicheremo questo nodo per altre due volte, designando un singolo nodo come manager del cluster.

3. Replica dei nodi

Per replicare i nodi possiamo decidere di creare un’immagine del nostro sistema che reinstalleremo a piacere, oppure, visto che stiamo usando VirtualBox di duplicare le macchine virtuali. Quindi spegniamo la nostra macchina e creiamone una copia.

Per farlo ci è sufficiente cliccare sulla macchina virtuale e selezionare Clona…

A questo punto ci verrà mostrata una schermata delle opzioni da configurare nel modo seguente:

Spuntiamo l’inizializzazione di un nuovo indirizzo MAC (visto che vogliamo usare le macchine insieme) e diamo un nome alla nuova macchina, nel mio caso Ubuntu MySQL Nodo 1.

Ripetiamo la medesima operazione per creare un Ubuntu MySQL Nodo 2.

A questo punto sistemiamo la nostra rete per poter accedere alle macchine virtuali. Per farlo assegniamo a tutte le macchine un indirizzo IP statico, modifichiamo opportunamente i nomi e configuriamo il file hosts in modo tale che possano vedersi le une con le altre. La configurazione finale sarà così:

Cominciamo con Ubuntu MySQL Manager.

Usando ifconfig verifichiamo che la nostra macchina si trovi all’indirizzo 192.168.0.5. Scopriamo il nostro gateway digitando:

route -n

route -n

Il risultato dovrebbe assomigliare a qualcosa di simile:

Tabella di routing IP del kernel
Destination     Gateway         Genmask         Flags Metric Ref    Use Iface
0.0.0.0         192.168.0.1     0.0.0.0         UG    0      0        0 enp0s3
192.168.0.0     0.0.0.0         255.255.255.0   U     0      0        0 enp0s3

Tabella di routing IP del kernel

Destination Gateway Genmask Flags Metric Ref Use Iface

0.0.0.0 192.168.0.1 0.0.0.0 UG 0 0 0 enp0s3

192.168.0.0 0.0.0.0 255.255.255.0 U 0 0 0 enp0s3

Il gateway è quindi 192.168.0.1, ricordiamoci che la nostra rete è stata impostata con netmask /24 ovvero 255.255.255.0

Impostiamo quindi tale indirizzo come indirizzo statico, usando le informazioni che abbiamo raccolto.

sudo nano /etc/network/interfaces

1	sudo nano /etc/network/interfaces

Modifichiamo il file interfaces affinché abbia il seguente contenuto:

# This file describes the network interfaces available on your system
# and how to activate them. For more information, see interfaces(5).

source /etc/network/interfaces.d/*

# The loopback network interface
auto lo
iface lo inet loopback

# The primary network interface
auto enp0s3
iface enp0s3 inet static
address 192.168.0.5
netmask 255.255.255.0
gateway 192.168.0.1
dns-nameservers 8.8.8.8

# This file describes the network interfaces available on your system

# and how to activate them. For more information, see interfaces(5).

source /etc/network/interfaces.d/*

# The loopback network interface

auto lo

iface lo inet loopback

# The primary network interface

auto enp0s3

iface enp0s3 inet static

address 192.168.0.5

netmask 255.255.255.0

gateway 192.168.0.1

dns-nameservers 8.8.8.8

Faccio notare che per il DNS utilizziamo il DNS di Google all’indirizzo 8.8.8.8.

A questo punto riavviamo la scheda di rete digitando:

sudo ifdown enp0s3 && sudo ifup enp0s3

1	sudo ifdown enp0s3 && sudo ifup enp0s3

Faccio notare che la nostra interfaccia di rete è enp0s3, mentre in una classica configurazione fisica probabilmente sarebbe eth1.

Fatto questo modifichiamo il file hosts inserendo gli host stabiliti all’inizio:

192.168.0.5     manager.mysql.cluster
192.168.0.6     nodo1.mysql.cluster
192.168.0.7     nodo2.mysql.cluster

192.168.0.5 manager.mysql.cluster

192.168.0.6 nodo1.mysql.cluster

192.168.0.7 nodo2.mysql.cluster

Il file alla fine dovrebbe risultare qualcosa di simile a questo:

127.0.0.1       localhost
127.0.1.1       ubuntu-mysql-manager

192.168.0.5     manager.mysql.cluster
192.168.0.6     nodo1.mysql.cluster
192.168.0.7     nodo2.mysql.cluster

# The following lines are desirable for IPv6 capable hosts
::1     localhost ip6-localhost ip6-loopback
ff02::1 ip6-allnodes
ff02::2 ip6-allrouters

127.0.0.1 localhost

127.0.1.1 ubuntu-mysql-manager

192.168.0.5 manager.mysql.cluster

192.168.0.6 nodo1.mysql.cluster

192.168.0.7 nodo2.mysql.cluster

# The following lines are desirable for IPv6 capable hosts

::1 localhost ip6-localhost ip6-loopback

ff02::1 ip6-allnodes

ff02::2 ip6-allrouters

Salviamo il tutto, spegniamo la macchina (onde evitare conflitti, dal momento che sono tutte duplicate) e spostiamoci sul prossimo nodo.

Configurazione Ubuntu MySQL Nodo 1

Prima di procedere soffermiamoci un attimo a configurare le porte per l’inoltro sulla scheda di virtual box, ottenendo una configurazione simile a questa:

In questo modo collegandoci su porte diverse dell’interfaccia esterna potremo accedere, mediante SSH, alle specifiche macchine virtuali.

Una volta avviato il secondo nodo è molto probabile, essendo la prima macchina sull’indirizzo 192.168.0.5, che questo si trovi sull’indirizzo 192.168.0.6. Se facciamo tutto in questo ordine ci risparmiamo qualche piccolo lavoretto extra nella riassegnazione degli indirizzi. Colleghiamoci quindi al nostro nodo 1 (se usate Putty potete collegarvi al 192.168.56.1:23) e modifichiamo la scheda di rete con l’indirizzo statico, come abbiamo fatto prima.

sudo nano /etc/network/interfaces

1	sudo nano /etc/network/interfaces

Il risultato sarà simile a questo:

# This file describes the network interfaces available on your system
# and how to activate them. For more information, see interfaces(5).

source /etc/network/interfaces.d/*

# The loopback network interface
auto lo
iface lo inet loopback

# The primary network interface
auto enp0s3
iface enp0s3 inet static
address 192.168.0.6
netmask 255.255.255.0
gateway 192.168.0.1
dns-nameservers 8.8.8.8

# This file describes the network interfaces available on your system

# and how to activate them. For more information, see interfaces(5).

source /etc/network/interfaces.d/*

# The loopback network interface

auto lo

iface lo inet loopback

# The primary network interface

auto enp0s3

iface enp0s3 inet static

address 192.168.0.6

netmask 255.255.255.0

gateway 192.168.0.1

dns-nameservers 8.8.8.8

Modifichiamo il file hosts:

sudo nano /etc/hosts

1	sudo nano /etc/hosts

Aggiungiamo le righe di prima:

192.168.0.5     manager.mysql.cluster
192.168.0.6     nodo1.mysql.cluster
192.168.0.7     nodo2.mysql.cluster

192.168.0.5 manager.mysql.cluster

192.168.0.6 nodo1.mysql.cluster

192.168.0.7 nodo2.mysql.cluster

E notiamo che abbiamo ancora ubuntu-mysql-manager come riferimento interno. Rinominiamolo in ubuntu-mysql-nodo1. Il file hosts alla fine risulterà così:

127.0.0.1       localhost
127.0.1.1       ubuntu-mysql-nodo1

192.168.0.5     manager.mysql.cluster
192.168.0.6     nodo1.mysql.cluster
192.168.0.7     nodo2.mysql.cluster

# The following lines are desirable for IPv6 capable hosts
::1     localhost ip6-localhost ip6-loopback
ff02::1 ip6-allnodes
ff02::2 ip6-allrouters

127.0.0.1 localhost

127.0.1.1 ubuntu-mysql-nodo1

192.168.0.5 manager.mysql.cluster

192.168.0.6 nodo1.mysql.cluster

192.168.0.7 nodo2.mysql.cluster

# The following lines are desirable for IPv6 capable hosts

::1 localhost ip6-localhost ip6-loopback

ff02::1 ip6-allnodes

ff02::2 ip6-allrouters

Infine modifichiamo il hostname digitando:

sudo nano /etc/hostname

1	sudo nano /etc/hostname

Anche qui dentro sostituiamo il nome di poco fa con quello nuovo, ovvero ubuntu-mysql-nodo1.

Fatto tutto questo spegniamo la macchina e passiamo al secondo nodo.

Configurazione Ubuntu MySQL Nodo 2

A questo punto dovrebbe essere tutto semplice, basta replicare quanto fatto per il nodo 1.

Il file /etc/network/interfaces avrà quindi il seguente aspetto:

# This file describes the network interfaces available on your system
# and how to activate them. For more information, see interfaces(5).

source /etc/network/interfaces.d/*

# The loopback network interface
auto lo
iface lo inet loopback

# The primary network interface
auto enp0s3
iface enp0s3 inet static
address 192.168.0.7
netmask 255.255.255.0
gateway 192.168.0.1
dns-nameservers 8.8.8.8

# This file describes the network interfaces available on your system

# and how to activate them. For more information, see interfaces(5).

source /etc/network/interfaces.d/*

# The loopback network interface

auto lo

iface lo inet loopback

# The primary network interface

auto enp0s3

iface enp0s3 inet static

address 192.168.0.7

netmask 255.255.255.0

gateway 192.168.0.1

dns-nameservers 8.8.8.8

Il file /etc/hosts apparirà così:

127.0.0.1       localhost
127.0.1.1       ubuntu-mysql-nodo2

192.168.0.5     manager.mysql.cluster
192.168.0.6     nodo1.mysql.cluster
192.168.0.7     nodo2.mysql.cluster

# The following lines are desirable for IPv6 capable hosts
::1     localhost ip6-localhost ip6-loopback
ff02::1 ip6-allnodes
ff02::2 ip6-allrouters

127.0.0.1 localhost

127.0.1.1 ubuntu-mysql-nodo2

192.168.0.5 manager.mysql.cluster

192.168.0.6 nodo1.mysql.cluster

192.168.0.7 nodo2.mysql.cluster

# The following lines are desirable for IPv6 capable hosts

::1 localhost ip6-localhost ip6-loopback

ff02::1 ip6-allnodes

ff02::2 ip6-allrouters

Mentre il file /etc/hostname conterrà una sola riga:

ubuntu-mysql-nodo2

1	ubuntu-mysql-nodo2

A questo punto anche il secondo nodo è pronto e possiamo spegnere la macchina.

4. Installazione del MySQL Cluster Manager

Adesso riavviamo il nostro cluster manager e avviamo l’installazione del software manager:

sudo apt-get install mysql-cluster-community-management-server

1	sudo apt-get install mysql-cluster-community-management-server

Una volta installato andiamo a creare la seguente cartella nella quale metteremo il file di configurazione:

sudo mkdir /var/lib/mysql-cluster

1	sudo mkdir /var/lib/mysql-cluster

Creiamo dentro un file chiamato config.ini:

sudo nano /var/lib/mysql-cluster/config.ini

1	sudo nano /var/lib/mysql-cluster/config.ini

A questo punto procediamo alla configurazione nel modo seguente:

[ndbd default]
# opzioni che interesseranno tutti i nodi
NoOfReplicas=2          # numero di repliche
DataMemory=80M          # quantità di memoria allocata per l'archivazione dei dati
IndexMemory=18M         # quantità di memoria allocata per l'indicizzazione dei dati
ServerPort=2202         # porta di connessione, non è necessario impostarla, la metto qui a titolo di esercizio

[ndb_mgmd]
# configurazione del manager
HostName=manager.mysql.cluster  # oppure ci possiamo mettere l'indirizzo ip, nel nostro caso sarebbe 192.168.0.5
DataDir=/var/lib/mysql-cluster  # cartella che conterrà i file LOG dei nodi

[ndbd]
# opzioni nodo 1
HostName=nodo1.mysql.cluster
NodeId=2
DataDir=/usr/local/mysql/data

[ndbd]
# opzioni nodo 2
HostName=nodo2.mysql.cluster
NodeId=3
DataDir=/usr/local/mysql/data

[mysqld]
#questa è la posizione dove si troverà il daemon del MySQL, il processo in esecuzione insomma
HostName=manager.mysql.cluster

[ndbd default]

# opzioni che interesseranno tutti i nodi

NoOfReplicas=2 # numero di repliche

DataMemory=80M # quantità di memoria allocata per l'archivazione dei dati

IndexMemory=18M # quantità di memoria allocata per l'indicizzazione dei dati

ServerPort=2202 # porta di connessione, non è necessario impostarla, la metto qui a titolo di esercizio

[ndb_mgmd]

# configurazione del manager

HostName=manager.mysql.cluster # oppure ci possiamo mettere l'indirizzo ip, nel nostro caso sarebbe 192.168.0.5

DataDir=/var/lib/mysql-cluster # cartella che conterrà i file LOG dei nodi

[ndbd]

# opzioni nodo 1

HostName=nodo1.mysql.cluster

NodeId=2

DataDir=/usr/local/mysql/data

[ndbd]

# opzioni nodo 2

HostName=nodo2.mysql.cluster

NodeId=3

DataDir=/usr/local/mysql/data

[mysqld]

#questa è la posizione dove si troverà il daemon del MySQL, il processo in esecuzione insomma

HostName=manager.mysql.cluster

Ho inserito i commenti ai singoli parametri nel file stesso in modo da rendere più agevole la consultazione. I parametri in cima non sono di per se necessari, ma li mettiamo a scopo didattico per tenere presente che eventualmente li potremo modificare in base alle nostre esigenze.

Per finire installiamo il mysql-server, dal momento che abbiamo detto che il daemon del MySQL si troverà sulla medesima macchina del menager. Digitiamo:

sudo apt-get install mysql-server

1	sudo apt-get install mysql-server

Fatto.

5. Configurazione dei nodi

Adesso avviamo i nostri due nodi e configuriamoli nella maniera seguente (io qui configurerò il primo nodo, la stessa cosa va fatta sul secondo).

Anzitutto installiamo il programma per ciascun nodo digitando:

sudo apt-get install mysql-cluster-community-data-node

1	sudo apt-get install mysql-cluster-community-data-node

Quindi digitiamo:

sudo nano /etc/mysql/my.cnf

1	sudo nano /etc/mysql/my.cnf

Dentro al file scriviamo:

[mysqld]
ndbcluster

[mysql_cluster]
ndb-connectstring=manager.mysql.cluster

[mysqld]

ndbcluster

[mysql_cluster]

ndb-connectstring=manager.mysql.cluster

In questo modo collegheremo il nodo al cluster, demandando la gestione del servizio al cluster medesimo.

6. Avvio del cluster

Ora che abbiamo configurato sia il manager che gli altri nodi possiamo procedere all’avvio del nostro cluster.

Spostiamoci sul manager e digitiamo:

sudo ndb_mgmd -f /var/lib/mysql-cluster/config.ini

1	sudo ndb_mgmd -f /var/lib/mysql-cluster/config.ini

Questo avvierà il cluster sul manager importando il file di configurazione. Affinché si avvii automaticamente ad ogni riavvio modifichiamo il file di avvio nel seguente modo.

Anzitutto abilitiamo i servizi locali digitando:

sudo systemctl enable rc-local.service

1	sudo systemctl enable rc-local.service

E modifichiamo il file:

sudo nano /etc/rc.local

1	sudo nano /etc/rc.local

Inserendo, prima di exit 0, il precedente comando (questo sarà l’aspetto complessivo del file):

#!/bin/sh -e
#
# rc.local
#
# This script is executed at the end of each multiuser runlevel.
# Make sure that the script will "exit 0" on success or any other
# value on error.
#
# In order to enable or disable this script just change the execution
# bits.
#
# By default this script does nothing.

ndb_mgmd -f /var/lib/mysql-cluster/config.ini
exit 0

#!/bin/sh -e

# rc.local

# This script is executed at the end of each multiuser runlevel.

# Make sure that the script will "exit 0" on success or any other

# value on error.

# In order to enable or disable this script just change the execution

# bits.

# By default this script does nothing.

ndb_mgmd -f /var/lib/mysql-cluster/config.ini

exit 0

Adesso spostiamoci sui singoli nodi e per ciascun nodo digitiamo:

sudo mkdir -p /usr/local/mysql/data

1	sudo mkdir -p /usr/local/mysql/data

In questo modo creeremo la cartella per l’allocazione dei dati necessaria al nodo (con il comando -p diciamo ad mkdir di creare l’intero percorso).

Dopodiché avviamo il nodo digitando:

sudo ndbd

sudo ndbd

Affinché anche tutto ciò parta all’avvio digitiamo:

sudo systemctl enable rc-local.service

1	sudo systemctl enable rc-local.service

E poi ancora:

sudo nano /etc/rc.local

1	sudo nano /etc/rc.local

Modifichiamo il file affinché appaia in questo modo:

#!/bin/sh -e
#
# rc.local
#
# This script is executed at the end of each multiuser runlevel.
# Make sure that the script will "exit 0" on success or any other
# value on error.
#
# In order to enable or disable this script just change the execution
# bits.
#
# By default this script does nothing.

ndbd
exit 0

#!/bin/sh -e

# rc.local

# This script is executed at the end of each multiuser runlevel.

# Make sure that the script will "exit 0" on success or any other

# value on error.

# In order to enable or disable this script just change the execution

# bits.

# By default this script does nothing.

ndbd

exit 0

Riavviamo il tutto.

7. Verifichiamo il funzionamento del cluster

A questo punto possiamo verificare il funzionamento del cluster spostandoci sul nostro manager e digitando:

ndb_mgm

ndb_mgm

Entrando nel terminale del ndb_ngm digitiamo:

SHOW

SHOW

Quello che dovremmo vedere sarà qualcosa di simile a questo:

Se tutto è andato bene questo sarà il risultato. Qualora uno dei nodi non fosse raggiungibile, oppure non fosse possibile collegarsi al daemon ci verrebbe segnalato esplicitamente.

Per uscire dal manager è sufficiente digitare exit e premere invio.

Come ultima operazione proviamo a creare una tabella sul nostro cluster.

8. Creiamo una tabella sul cluster

Accediamo anzitutto al mysql digitando nel manager:

mysql -u root -p

1	mysql -u root -p

Questo ci permetterà di accedere con l’utente root del database e ci verrà chiesta la password a schermo.

Anche qui possiamo fare una seconda verifica di quello che ci è già noto:

Infatti se tutto è andato bene ci verrà dato il benvenuto con la versione del MySQL Cluster Community Server.

A questo punto creiamo il nostro database di prova digitando:

CREATE DATABASE cluster_test;

1	CREATE DATABASE cluster_test;

Inseriamo nel database cluster_test la nostra tabella di prova:

USE cluster_test;
CREATE TABLE cluster_test (nome VARCHAR(20), cognome VARCHAR(20)) ENGINE=ndbcluster;

1 2	USE cluster_test; CREATE TABLE cluster_test (nome VARCHAR(20), cognome VARCHAR(20)) ENGINE=ndbcluster;

ATTENZIONE! E’ molto importante che l’engine sia impostato su ndbcluster, affinché la tabella lavori e sia distribuita sul cluster.

Inseriamo dei valori di prova:

INSERT INTO cluster_test (nome,cognome) VALUES('mario','rossi');

1	INSERT INTO cluster_test (nome,cognome) VALUES('mario','rossi');

Adesso proviamo ad interrogare la tabella digitando:

SELECT * FROM cluster_test;

1	SELECT * FROM cluster_test;

Il risultato dovrebbe essere questo:

Fatto, abbiamo il nostro cluster perfettamente funzionante e pronto all’uso.

Vedi articolo

15 Febbraio 2018 3 commenti

Interrogare foglio di Excel con SQL in VBA

Obiettivo: interrogare e filtrare il contenuto di un foglio excel come se fosse una tabella di un database SQL

Prepariamo anzitutto un file Excel creando un foglio con dei dati come quelli di seguito:

Questo foglio lo chiamiamo DATI. Faccio notare che nella prima riga ho inserito le intestazioni della nostra tabella.

In un secondo foglio andiamo a creare un pulsante che richiamerà un form. L’idea sarebbe quella di creare un metodo di inserimento che guidi l’utente e gli permetta di filtrare una vasta base dati, eseguendo anche dei comandi di ricerca. Il risultato sarà il seguente:

Se proviamo a cercare qualcosa, per esempio “Mar” l’elenco dovrà essere filtrato e otterremo il risultato seguente:

Fatte queste premesse vediamo come realizzare l’interrogazione del foglio mediante VBA (sorvolo sull’elementare creazione del form).

Anzitutto creiamo una Sub che caricherà i valori nella combobox (cmbSeleziona) e nella listbox (lbSeleziona).

Private Sub LoadSelect(Optional ByVal nome As String = "")

    Dim conn As ADODB.Connection
    Dim rs As ADODB.Recordset
    Dim strFileName As String
    Dim ConnectionString As String
    
    strFileName = ThisWorkbook.FullName
    
    ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" _
    & strFileName & ";Extended Properties=""Excel 12.0 xml;HDR=YES;IMEX=1;Readonly=True"";"
    
    Set conn = CreateObject("ADODB.Connection")
    Set rs = CreateObject("ADODB.Recordset")
    
    conn.Open ConnectionString
    
    Dim query As String
    
    If nome <> "" Then
        query = "SELECT * FROM [DATI$A:C] WHERE Nome LIKE '%" & nome & "%'"
    Else
        query = "SELECT * FROM [DATI$A:C]"
    End If
    
    rs.Open query, conn
    
    cmbSeleziona.Clear
    lbSelezione.Clear
    
    pos = 0
    
    If Not rs.EOF Then
        Do
            
            cmbSeleziona.AddItem rs(1) & " [" & rs(2) & "]", pos
            cmbSeleziona.List(pos, 1) = rs(0)
            
            lbSelezione.AddItem rs(1) & " [" & rs(2) & "]", pos
            lbSelezione.List(pos, 1) = rs(0)
            
            pos = pos + 1
            rs.MoveNext
        Loop Until rs.EOF
    End If
    
    rs.Close
    
    conn.Close

End Sub

Private Sub LoadSelect(Optional ByVal nome As String = "")

Dim conn As ADODB.Connection

Dim rs As ADODB.Recordset

Dim strFileName As String

Dim ConnectionString As String

strFileName = ThisWorkbook.FullName

ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" _

& strFileName & ";Extended Properties=""Excel 12.0 xml;HDR=YES;IMEX=1;Readonly=True"";"

Set conn = CreateObject("ADODB.Connection")

Set rs = CreateObject("ADODB.Recordset")

conn.Open ConnectionString

Dim query As String

If nome <> "" Then

query = "SELECT * FROM [DATI$A:C] WHERE Nome LIKE '%" & nome & "%'"

Else

query = "SELECT * FROM [DATI$A:C]"

End If

rs.Open query, conn

cmbSeleziona.Clear

lbSelezione.Clear

pos = 0

If Not rs.EOF Then

cmbSeleziona.AddItem rs(1) & " [" & rs(2) & "]", pos

cmbSeleziona.List(pos, 1) = rs(0)

lbSelezione.AddItem rs(1) & " [" & rs(2) & "]", pos

lbSelezione.List(pos, 1) = rs(0)

pos = pos + 1

rs.MoveNext

Loop Until rs.EOF

End If

rs.Close

conn.Close

End Sub

Faccio notare che in questo caso la query filtra solamente i valori in base al Nome, inoltre il parametro nome può essere passato in via opzionale, permettendo quindi anche di visualizzare tutti i campi se non c’è nessun nome da filtrare.

Ricordiamoci che per poter eseguire la connessione ADO abbiamo bisogno di importare l’opportuna libreria dai riferimenti.

A questo punto completiamo il nostro codice aggiungendo:

Private Sub txtCerca_Change()

    LoadSelect txtCerca.Text

End Sub

Private Sub UserForm_Initialize()

    LoadSelect

End Sub

Private Sub txtCerca_Change()

LoadSelect txtCerca.Text

End Sub

Private Sub UserForm_Initialize()

LoadSelect

End Sub

In questo modo ad ogni modifica (change) di txtCerca, che è la textbox di ricerca in cima al form, verrà interrogato il nostro foglio per produrre un risultato.

Il medesimo metodo LoadSelect, questa volta senza argomenti, lo aggiungiamo anche all’avvio del form, per caricare il contenuto completo senza filtri.

Qui è possibile scaricare il file creato con Excel 2016.

Vedi articolo