# MySQL

Mysql ese motor tan amplio

# Obtener el MAX y el MIN en un objeto json dentro de una columna json

### Información previa

A veces tenemos que obtener información contenida en columnas tipo json, existentes en nuestra base de datos.

Aquí vamos a ver cómo podemos hacerlo.

> En el momento de escribir este artículo no encontré forma de hacerlo en Laravel 9. Si te animas hay un post en [Laracast - Is there a way to get the max of a JSON array in MYSQL](https://laracasts.com/discuss/channels/eloquent/is-there-a-way-to-get-the-max-of-a-json-array-in-mysql) (no hace falta ser usuario de pago) 

### Escenario

Tabla con una columna tipo json llamada `data`. Su contenido tipo es el de abajo, y buscamos el MIN y el MAX del valor `response_time`

En este escenario el valor está normalizado `number`

```json
{
	"data": {
		"csq": -62,
		"icc": "8944501801213377498",
		"imei": "869640059692007",
		"device": "modem",
		"sunset": 1652071940,
		"sunrise": 1652124780,
		"datetime": 1652082623,
		"response_time": 11.29
	},
	"message": "successful",
	"success": true
}
```

### Sql

```sql
mysql> SELECT MAX(JSON_EXTRACT(data, "$.data.response_time")) as response  FROM table;
+----------+
| response |
+----------+
| 55.88    |
+----------+
1 row in set (0,03 sec)

mysql> SELECT MIN(JSON_EXTRACT(data, "$.data.response_time")) as response  FROM table;
+----------+
| response |
+----------+
| 6.15     |
+----------+
1 row in set (0,04 sec)
```

### Bonus 

Es posible, sobre todo si trabajamos en un proyecto no depurado todavia en el que guardamos datos a los bruto, sin normalización o verificación, que nos salgan valores anormales. 

Entonces es bueno obtener los datos de un número limitado de la respuesta ordenados para ver si es algo que falla.

```sql
SELECT id, JSON_EXTRACT(data, "$.data.response_time") as response  FROM tabla ORDER BY JSON_EXTRACT(data, "$.data.response_time") DESC LIMIT 20;
+--------+----------+
| id     | response |
+--------+----------+
| 224623 | 600.47   |
| 200807 | 598.7    |
| 177004 | 598.54   |
| 177006 | 598.29   |
| 177005 | 598.29   |
| 177008 | 597.68   |
| 153187 | 596.45   |
| 153189 | 596.33   |
| 129369 | 595.87   |
| 129368 | 595.85   |
| 129371 | 595.33   |
|  10806 | 588.38   |
|  10807 | 588.35   |
|  10809 | 588.19   |
| 273886 | 25.08    |
|  24709 | 22.62    |
| 265865 | 22.32    |
|  92768 | 20.92    |
| 275543 | 20.91    |
| 122073 | 20.15    |
+--------+----------+
20 rows in set (1,30 sec)
```

Evidentemente aquí hay una desviación bastante clara que debemos limpiar o revisar.



##### Aviso
Esta documentación y su contenido, no implica que funcione en tu caso o determinados casos. 
También implica que tienes conocimientos sobre lo que trata, y que en cualquier caso tienes copias de seguridad.
El contenido el contenido se entrega, tal y como esta, sin que ello implique ningún obligación ni responsabilidad por parte de [Castris](https://castris.com)

Si necesitas soporte profesional puedes contratar con Castris [soporte profesional](https://intranet.castris.com/cart.php?gid=18).

# DBeaver (java - mysql) Public Key Retrieval is not allowed

## Introducción
DBeaver es una app para manejo remoto de bases de datos de distinto motores, basada en Java. 

![Dbeaver](https://multimedia.castris.com/imagenes/wiki/programacion/dbeaver.png)

Sin embargo a veces aparacen errores un tanto extraños para quienes no estamos acostumbrados a Java, como puede ser, **Public Key Retrieval is not allowed** al tratar de configurar un tunle de accesso SSH a una máquina remota.

### Solución

En el post [Connection Java - MySQL : Public Key Retrieval is not allowed](https://stackoverflow.com/questions/50379839/connection-java-mysql-public-key-retrieval-is-not-allowed) hay una respuesta válida escrita por Javier Aviles 

1. Editar conexión
2. Driver Propierties
3. Añadir en user propierties dos nuevas propiedades: **useSSL** con valor **false** y **allowPublicKeyRetrieval** con valor **true**

![Ventana de ajustes de conexión](https://multimedia.castris.com/imagenes/wiki/programacion/dbeaver._driver_properties.png)

> Esto puede diferrir entre versiones.

##### Aviso
Esta documentación y su contenido, no implica que funcione en tu caso o determinados casos. 
También implica que tienes conocimientos sobre lo que trata, y que en cualquier caso tienes copias de seguridad.
El contenido el contenido se entrega, tal y como está, sin que ello implique ningún obligación ni responsabilidad por parte de [Castris](https://castris.com)

Si necesitas soporte profesional puedes contratar con Castris [soporte profesional](https://intranet.castris.com/cart.php?gid=18).

# Mysql no reduce espacio consumido tras un delete en tablas InnoDb

## Introducción 
Muchas veces los administradores de sistemas, o los clientes de estos, no son conscientes que no es lo mismo jugar o trabajar con bases de datos de tamaño medio, y otra muy distinta es enfrentarse a databases de 40GB o más.

En estos entornos, mysqldump no es una opción viable, y se hace necesario el uso de una estructura maestro-esclavo como mínimo, para hacer los backups en el esclavo, en lugar de en el maestro, con sus correspondientes problemas además de ser un medio eficaz, por si algo ocurre.

> Muchos Sysadmin jamás se han enfrentado a una restore de un servidor mysql de más de 50GB y los que lo han hecho saben la de **cositas** que pueden salir por ahí.

## Recuperación de espacio
Bien, muchas veces en algunos escenarios hay que vaciar tablas pesadas, ya sea por fechas o contenidos, pero tras el vaciado este no suele ocurrir una reducción del espacio en el servidor.

En este documento hablo de mi experiencia con Mysql 8 en una base de datos con InnoDb, y además con el valor `innodb_file_per_table`activado. Esto es importante, pues de lo contrario el ataque debe ser realizado con otra óptica.

```bash
mysql> show variables like "innodb_file_per_table";
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| innodb_file_per_table | ON    |
+-----------------------+-------+
1 row in set (0.00 sec)
```


### Liberar espacio 

Por ejemplo queremo liberar datos en una tabla entre dos fechas.

```bash
> mysql 
mysql> use mi_database;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> DELETE FROM logger_xxxxx WHERE created_at BETWEEN '2023-01-01 00:00:00'  AND '2023-04-30 23:59:59';
Query OK, 1849983 rows affected (5 min 55,64 sec)
mysql> ALTER TABLE logger_xxxxx ENGINE=InnoDB;
Query OK, 0 rows affected (6 min 3,97 sec)
Records: 0  Duplicates: 0  Warnings: 0
```

Evidentemente la tabla ya estaba en formato InnoDb, pero el comando en sí mismo realizado, una optimización, como la que haríamos con `mysqlcheck --auto-repair --optimize database` sólo que con ese comando las tablas InnoDb no se optimizarán.

## Agradecimientos
En particular [mysqldump.guru](https://mysqldump.guru/) donde encontrarás maravillosos snipets para usar con mysql.

##### Aviso
Esta documentación y su contenido, no implica que funcione en tu caso o determinados casos. 
También implica que tienes conocimientos sobre lo que trata, y que en cualquier caso tienes copias de seguridad.
El contenido el contenido se entrega, tal y como está, sin que ello implique ningún obligación ni responsabilidad por parte de [Castris](https://castris.com)

Si necesitas soporte profesional puedes contratar con Castris [soporte profesional](https://intranet.castris.com/cart.php?gid=18).

# Mysql conexión vía socket. Como saber donde está

## Introducción
Soy amigo de usar los sockets para casi todo lo que puedo por eficacia. Asi que una que suele ocurrirme, es cuando entro a una máquina nueva que no se donde esta.


### Revisar el Archivo de Configuración de MySQL
El archivo de configuración de MySQL generalmente se encuentra en /etc/my.cnf o /etc/mysql/my.cnf. Puedes buscar la línea que define la ubicación del socket.

```
grep -i 'socket' /etc/my.cnf /etc/mysql/my.cnf

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

A veces no te da nada o te da error porque no encuentra alguno de los ficheros.

### Usar el Cliente MySQL

Puedes conectarte al servidor MySQL y ejecutar un comando SQL para obtener la información del socket

```
mysql -u root -p -e "SHOW VARIABLES LIKE 'socket';"

+---------------+-----------------------------+
| Variable_name | Value                       |
+---------------+-----------------------------+
| socket        | /var/run/mysqld/mysqld.sock |
+---------------+-----------------------------+
```

### Revisar el Estado del Servicio MySQL

No suele decir mucho proque depende de como esta configurado el servicio en `systemctl`

```
grep -i 'socket' /var/log/mysql/*.log /var/log/mysqld.log

● mysql.service - MySQL Community Server
     Loaded: loaded (/lib/systemd/system/mysql.service; enabled; vendor preset: enabled)
     Active: active (running) since Sun 2024-07-07 15:39:12 UTC; 4 weeks 2 days ago
       Docs: man:mysqld(8)
             http://dev.mysql.com/doc/refman/en/using-systemd.html
   Main PID: 992459 (mysqld)
     Status: "Server is operational"
      Tasks: 66 (limit: 48144)
     Memory: 7.8G
     CGroup: /system.slice/mysql.service
             └─992459 /usr/sbin/mysqld

Warning: journal has been rotated since unit was started, output may be incomplete.
```

### lsof

```
❯  lsof -U | grep mysql
mysqld     992459            mysql    1u  unix 0xffff9411ca168000      0t0  96369986 type=STREAM
mysqld     992459            mysql    2u  unix 0xffff9411ca168000      0t0  96369986 type=STREAM
mysqld     992459            mysql    3u  unix 0xffff9411ca16b740      0t0  96369989 type=DGRAM
mysqld     992459            mysql   21u  unix 0xffff941565e79540      0t0  96373782 /var/run/mysqld/mysqlx.sock type=STREAM
mysqld     992459            mysql   25u  unix 0xffff941565e7ee80      0t0  96373785 /var/run/mysqld/mysqld.sock type=STREAM
mysqld     992459            mysql  235u  unix 0xffff941730f6c840      0t0 272319038 /var/run/mysqld/mysqld.sock type=STREAM
mysqld     992459            mysql  242u  unix 0xffff94194a1d1980      0t0 272319039 /var/run/mysqld/mysqld.sock type=STREAM
mysqld     992459            mysql  243u  unix 0xffff941646f8aec0      0t0 272319040 /var/run/mysqld/mysqld.sock type=STREAM
mysqld     992459            mysql  521u  unix 0xffff94173c81ea40      0t0 272290305 /var/run/mysqld/mysqld.sock type=STREAM
mysqld     992459            mysql  524u  unix 0xffff94190a088440      0t0 272290325 /var/run/mysqld/mysqld.sock type=STREAM
mysqld     992459            mysql  525u  unix 0xffff941837156a40      0t0 272290403 /var/run/mysqld/mysqld.sock type=STREAM
mysqld     992459            mysql  528u  unix 0xffff9417321b2200      0t0 272289661 /var/run/mysqld/mysqld.sock type=STREAM
mysqld     992459            mysql  529u  unix 0xffff9417321b3b80      0t0 272289664 /var/run/mysqld/mysqld.sock type=STREAM
```


##### Aviso
Esta documentación y su contenido, no implica que funcione en tu caso o determinados casos. 
También implica que tienes conocimientos sobre lo que trata, y que en cualquier caso tienes copias de seguridad.
El contenido el contenido se entrega, tal y como está, sin que ello implique ningún obligación ni responsabilidad por parte de [Castris](https://castris.com)

Si necesitas soporte profesional puedes contratar con Castris [soporte profesional](https://intranet.castris.com/store/soporte-profesional).

# MAX_STATEMENT_TIME en versiones de MariaDB Setdart

## Introducción 

Es de uso en **SETDART** No editar al público.

### Asignar por usuario el valor max_statement_time

tiempo en segundos
```sql
GRANT USAGE ON *.* TO 'setdart_office'@'77.230.222.66'  REQUIRE NONE WITH MAX_QUERIES_PER_HOUR 0 MAX_CONNECTIONS_PER_HOUR 0 MAX_UPDATES_PER_HOUR 0 MAX_USER_CONNECTIONS 0 MAX_STATEMENT_TIME 10;

# Verificacion

SELECT User, Host, max_questions, max_updates, max_connections, max_user_connections, max_statement_time  FROM mysql.user WHERE User = 'setdart_office' AND Host = '89.26.178.18';
+----------------+--------------+---------------+-------------+-----------------+----------------------+--------------------+
| User           | Host         | max_questions | max_updates | max_connections | max_user_connections | max_statement_time |
+----------------+--------------+---------------+-------------+-----------------+----------------------+--------------------+
| setdart_office | 89.26.178.18 |             0 |           0 |               0 |                    0 |          10.000000 |
+----------------+--------------+---------------+-------------+-----------------+----------------------+--------------------+
1 row in set (0.001 sec)
```