MySQL

Mysql ese motor tan amplio

Obtener el MAX y el MIN en un objeto json dentro de una columna json
DBeaver (java - mysql) Public Key Retrieval is not allowed
Mysql no reduce espacio consumido tras un delete en tablas InnoDb
Mysql conexión vía socket. Como saber donde está

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 (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

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

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.

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

Si necesitas soporte profesional puedes contratar con Castris soporte profesional.

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

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 hay una respuesta válida escrita por Javier Aviles

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

Ventana de ajustes de conexión

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

Si necesitas soporte profesional puedes contratar con Castris soporte profesional.

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_tableactivado. Esto es importante, pues de lo contrario el ataque debe ser realizado con otra óptica.

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.

> 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 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

Si necesitas soporte profesional puedes contratar con Castris soporte profesional.

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

Si necesitas soporte profesional puedes contratar con Castris soporte profesional.