Archivo de la categoría: MySQL

Cómo obtener la clave primaria o única de una tabla mediante una consulta MySQL

SELECT COLUMN_NAME, COLUMN_KEY FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = ‘[schema_db_name]’ AND TABLE_NAME = ‘[name_of_table]’ and COLUMN_KEY IN(‘PRI’, ‘UNI’);

Con esta consulta obtenemos el nombre/s de la columna/s de la tabla [name_of_table] del esquema de base de datos [schema_db_name] que cumplen que sean PRIMARY o UNIQUE.

Por ejemplo si tenemos la tabla:

CREATE TABLE `companies` (
`company_id` INT(11) NOT NULL AUTO_INCREMENT,
`name` VARCHAR(100) NOT NULL DEFAULT '',
`cif` VARCHAR(20) NULL DEFAULT '',
`created_by` INT(11) NOT NULL,
`creation_date` DATETIME NOT NULL,
`modified_by` INT(11) NULL DEFAULT NULL,
`modification_date` DATETIME NULL DEFAULT NULL,
`deleted_by` INT(11) NULL DEFAULT NULL,
`deleted_date` DATETIME NULL DEFAULT NULL,
PRIMARY KEY (`company_id`),
INDEX `companies_fk01` (`created_by`),
INDEX `companies_fk02` (`modified_by`),
INDEX `companies_fk03` (`deleted_by`),
CONSTRAINT `companies_fk01` FOREIGN KEY (`created_by`) REFERENCES `users` (`user_id`),
CONSTRAINT `companies_fk02` FOREIGN KEY (`modified_by`) REFERENCES `users` (`user_id`),
CONSTRAINT `companies_fk03` FOREIGN KEY (`deleted_by`) REFERENCES `users` (`user_id`)
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB;

Al realizar la búsqueda mostrará

COLUMN_NAME, COLUMN_KEY
company_id, PRIMARY KEY

 

Comparte, comenta y colabora.

Anuncios

SQL Error (1701): Cannot truncate a table referenced in a foreign key constraint

Contexto: Base de datos MySQL InnoDB.

Significado: Error al querer borrar una tabla que existe una foreign key que lo referencia.

Situación: En ocasiones deseamos modificar/eliminar unos registros/tabla y no podemos porque nos dice que existe otro registro que lo enlaza. En palabras más técnicas: SQL Error (1701): Cannot truncate a table referenced in a foreign key constraint

Solución: Eliminamos todos los registros en el orden de las tablas correcto (primero las hojas y por último la raiz) o atajamos desactivando el control de foreign keys.

Aquí muestro la segunda opción.  Pon a 0 (inhabilita) y a 1 (habilita)  entre el código de MySQL el siguiente flag:

SET FOREIGN_KEY_CHECKS = 0;
[mi codigo]
SET FOREIGN_KEY_CHECKS = 1;

Aunque no es muy recomendable utilizar estos flags, me han sido de utilidad en numerosas ocasiones. Siempre hay que utilizarlos con cabeza y no abusar de ellos.

Ahora ya puedes borrar las tablas a cascoporro.

Comparte, comenta y colabora. Juntos haremos un mundo mejor.

MySQL MAX Ejemplo

Tres funciones de mysql y tres resultados distintos.

Sea la siguiente tabla simplificada en MySQL ·client_states.

state_id | end_date
8;2000-01-01 00:00:00
9;2012-05-15 00:00:00

Quería obtener el último estado del usuario (la fecha más alta de las registradas del usuario) y ésto es lo que me he encontrado trasteando con la función MAX()

select state_id
from states_clients
where client_id=99996
having max(end_date);

Resultado Fail

8;2000-01-01 00:00:00

select max(state_id), max(end_date)
from states_clientes
where client_id=99996;

Resultado Win

9;2012-05-15 00:00:00

select state_id, max(end_date)
from states_clients
where client_id=99996;

Resultado Fail
8;2012-05-15 00:00:00

¿Por qué?

Conclusiones + stackoverfow:

  • Having siempre va con group by. Es por ello por lo que lo devuelve el resultado esperado.  Documentación SELECT de MySQL.
  • OJICO. Aunque estés seleccionado el valor máximo de una columna, no devuelven los valores en concordancia con las filas.  Como se ve en el último ejemplo está devolviendo ‘8;2012-05-15 00:00:00 ‘en vez de devolver  ‘8;2000-01-01 00:00:00’.

Error duplicate entry MySQL

Situación: Quiero añadir un índice UNIQUE para dos índices de una tabla que no está vacía.

Error: Error duplicate entry ‘1-10’. Informa que ya existe una clave duplicada.

Solución: Hay que borrar una de las tuplas duplicadas para que nos deje añadir la restricción UNIQUE:

Secuencia de modificación de la tabla

ALTER TABLE `protocolo_alarma`
ADD UNIQUE INDEX `alarma_generada_id_tipo_id` (`tipo_id`, `alarma_generada_id`);
/* SQL Error (1062): Duplicate entry '1-10' for key 'alarma_generada_id_tipo_id' */


Estas añadiendo un UNIQUE y tienes ya tuplas duplicadas. Borramos una de ellas. 

DELETE FROM `protocolo_alarma` WHERE  `protocolo_alarma_id`=16 LIMIT 1;

Ya podemos modificar la tabla.

ALTER TABLE `protocolo_alarma`
ADD UNIQUE INDEX `alarma_generada_id_tipo_id` (`tipo_id`, `alarma_generada_id`);
/* 0 rows affected, 0 rows found. Duration for 1 query: 0,202 sec. */

Saludos!

SQL Error (1093): You can’t specify target table for update

SQL Error (1093): You can’t specify target table ‘menus_items’ for update in FROM clause

But why? Si estoy haciendo un insert!!

Os voy a comentar el motivo de mi sorpresa al encontrar el error y su preciosa solución.

Tengo una tabla con los diferentes campos  del menus, en “menus_items”. La tabla tiene una clave primaria menu_item_id que es AUTO_INCREMENT (por lo tanto está omitida en el insert).

La tabla tiene los siguientes campos:

menu_item_id,

menu_id,

parent_menu_item_id,

`code`,

`name`

Quiero que cuando inserte una secuencia en la tabla, parent_menu_item_id sea el identificador menu_item_id resultado de una búsqueda en la misma tabla por código.
Es decir, resultado de la query:

(select menu_item_id from menus_items where code='core-system' limit 1)

Así que ejecuto mi insert molón:

INSERT INTO
`menus_items`
(
<p dir="ltr">menu_id,</p>
<p dir="ltr">parent_menu_item_id,</p>
<p dir="ltr">`code`,</p>
<p dir="ltr">`name`</p>
)
VALUES
(
<p dir="ltr">1,</p>
<p dir="ltr">(select menu_item_id from menus_items where code='core-system' limit 1), 'system-auth-users',</p>
<p dir="ltr">'Usuarios'</p>
)
);

ERROR!!

SQL Error (1093): You can’t specify target table ‘menus_items’ for update in FROM clause

Efectivamente, estoy buscando sobre  una tabla en la cual estoy modificando. Si quisiera hacer una subquery a otra tabla no hay problema. Por ejemplo:

INSERT INTO
`menus_items`
(
<p dir="ltr">parent_menu_item_id,</p>
<p dir="ltr">`code`,</p>
<p dir="ltr">`name`</p>
)
VALUES
(
<p dir="ltr">(select menu_id from menus where code='BACKEND'),</p>
<p dir="ltr">'system-auth-users',</p>
<p dir="ltr">'Usuarios'</p>
)
);

Pero al ser la misma tabla MySQL no lo permite.

¿Entonces?

Bien, como solución podemos hace uso de variables:

select @MENU_ITEM_ID:=menu_item_id from menus_items where code='core-system' LIMIT 1;

Y ahora haciendo uso de la variable:

INSERT INTO
`menus_items`
(
parent_menu_item_id,
 `code`,
`name`
)
VALUES
(
@MENU_ITEM_ID,
'system-auth-users',
'Usuarios'
);

OHHHH

Me encanta :))

Si es necesario más información, si no queda claro, o si no es lo que estabais buscando. Comentarlo para que pueda mejorarlo 😉

Un saludo!


Cómo activar y visualizar el log de MySQL

En ocasiones no sabemos lo que MySQL hace por debajo. No sabemos si ha llegado a lanzarse la consulta  o qué error está dando porque no estamos capturando la excepción en la aplicación que estamos desarrollando.

La alternativa: activar y visualizar el log de MySQL.

Escribe las siguientes secuencias entrando en mysql desde consola.

Primero entra en mysql:

mysql -p

Luego activa la tabla de log de mysql:

set global general_log=ON

Pasos básicos: veamos la tabla general_log:

mysql> describe mysql.general_log;
+--------------+------------------+------+-----+-------------------+-----------------------------+
| Field | Type | Null | Key | Default | Extra |
+--------------+------------------+------+-----+-------------------+-----------------------------+
| event_time | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
| user_host | mediumtext | NO | | NULL | |
| thread_id | int(11) | NO | | NULL | |
| server_id | int(10) unsigned | NO | | NULL | |
| command_type | varchar(64) | NO | | NULL | |
| argument | mediumtext | NO | | NULL | |
+--------------+------------------+------+-----+-------------------+-----------------------------+
6 rows in set (0.07 sec)

Así que esta tabla la puedes utilizar como una tabla común del esquema “mysql”. Ejemplos:

Si deseamos encontrar las últimas llamadas a mysql. 

select * from mysql.general_log order by event_time desc limit 5;

Si deseamos buscar por una IP concreto que ha lanzado la llamada a esa base de datos:


select * from mysql.general_log where user_host like '%192.168.%' order by event_time desc limit 5;

OJO! Esta tabla crece y crece sin parar. Es recomendable desactivarla


set global general_log=OFF

y de vez en cuando limpiarla:


truncate table mysql.general_log;

Toda ayuda para ver los errores es poca! Espero os sea de utilidad

Agradecimientos a @rcasas

mysqldump: Got error: 1045: Access denied for user ”@’%’ (using password: YES) when using LOCK TABLES

Este error puede dar en caso de volcados de datos de mysql con el comando desde consola mysldump. En concreto:

mysqldump   -u <nombreDelUsuario> -h <IPDelServidor> -p <nombreDeLaBaseDeDatosAVolcar> backups/2012_11_30/<NombreDelFicheroDondeQuedanLosDatos>.sql

Resultado por pantalla:

mysqldump: Got error: 1045: Access denied for user ‘asist’@’%’ (using password: YES) when using LOCK TABLES

Más datos: el usuario ‘asist’ ya estaba dado de alta y con permisos. grant all….

¿Entonces?

En foros no conseguí solucionar el error. En uno comentaba apagar mysql y reiniciar, cosa que seguía sin funcionar.

Solución encontrada!

Añadir a mysqldump –single-transaction

mysqldump  –single-transaction  -u <nombreDelUsuario> -h <IPDelServidor> -p <nombreDeLaBaseDeDatosAVolcar> backups/2012_11_30/<NombreDelFicheroDondeQuedanLosDatos>.sql

Si la base de datos está en producción. Esta no es una buena solución. Se perderán todos los datos que en el momento del volcado se estén manipulando.

Cualquier comentario/sugerencia/mejora será de agradecer!! saludos!!

Multi update MySQL

Aquí hay una solución a “Multi update mysql” sin romperse mucho la cabeza y sin programar.

Imaginemos que tenemos una tabla ‘grabaciones’ que tiene los campos ‘fecha_grabacion’ y ‘fecha_creacion’ y queremos hacer un update por cada grabacion de forma que la fecha de grabación pase a ser la fecha de creación.

Es hacer por cada fila:

update grabaciones set fecha_grabacion=’fecha de creacion de la grabacion id 1835’’ where grabacion_id=1835;

En esta imagen vemos en la pestaña “grabaciones” sus contenidos.

tabla_contenidos

Hacemos una query que con ‘CONCAT’ creemos el ‘UPDATE’ a partir de la query de las filas que queremos modificar:

select concat(‘update grabaciones set fecha_grabacion=\”,fecha_creacion,’\’ where grabacion_id=’,grabacion_id) from grabaciones where fecha_grabacion = ‘0000-00-00 00:00:00’;

Eso nos devuelve una única columna con updates. Como se muestra en la figura.

resultados mostrados en la query

La exportamos como texto, y ya tenemos nuestros comandos updates para aplicarlos.

update grabaciones set fecha_grabacion=’2012-10-08 09:55:44′ where grabacion_id=1775;
update grabaciones set fecha_grabacion=’2012-10-08 09:55:48′ where grabacion_id=1831;
update grabaciones set fecha_grabacion=’2012-10-08 09:55:48′ where grabacion_id=1835;
update grabaciones set fecha_grabacion=’2012-10-08 09:56:17′ where grabacion_id=2228;
update grabaciones set fecha_grabacion=’2012-10-08 09:56:43′ where grabacion_id=2573;
update grabaciones set fecha_grabacion=’2012-10-08 09:56:52′ where grabacion_id=2693;
update grabaciones set fecha_grabacion=’2012-10-08 09:57:00′ where grabacion_id=2806;
update grabaciones set fecha_grabacion=’2012-10-08 09:57:14′ where grabacion_id=3000;
update grabaciones set fecha_grabacion=’2012-10-08 09:57:20′ where grabacion_id=3081;
update grabaciones set fecha_grabacion=’2012-10-08 09:57:25′ where grabacion_id=3149;
update grabaciones set fecha_grabacion=’2012-10-10 19:30:46′ where grabacion_id=3326;

….

Ya está!

Para un resultado de más de 1000 updates, esta forma tardará mucho en ejecutarse, así que es mejor otras alternativas.

Comentar mejores opciones!! y si te parece interesante… compartir es vivir!

Gracias!

Cómo modificar tablas a UTF8 MySQL

Imaginemos una aplicación con la base de datos de mysql. En ella editamos información por ejemplo, de usuarios.

Todo es bonito y precioso.

Hasta! que decides exportar información a csv, hacer un volcado de datos y exportarlo a otra base de datos… etc.

¿Por qué? ¡Se ven mal los acentos, ñññññ y muchos caracteres!

Si estás en este caso, es que tienes mezclados los charset. Familiarizate con COLLATION y CHARSET.

http://dev.mysql.com/doc/refman/5.0/es/charset-mysql.html

Es mejor que utilices por compatibilidad UTF8.

Mysql por defecto crea collation a ‘latin1_swedish_ci’. Para arreglar los datos deberás hacer lo siguiente:

Si lo haces desde consola:

  1. Modificar la tabla

ALTER TABLE `caracteristicas_subtipos`
COLLATE=’utf8_general_ci’;

  1. Exportar los datos por medio de inserts Hacer truncate de la tabla.
    • Mediante volcado mysqldump -p –default-character-set=utf8  base_de_datos tabla_a_modificar > tabla_a_modificar.sql
  2. Volcar los datos.
    • Mediante mysql -p base_de_datos < tabla_a_modificar.sql

Si lo haces desde una herramienta de mysql:

Cambia sólo la forma de obtener los datos. Exporta los datos a INSERTS. Haz un truncate de la tabla. Antes de volcar los inserts pon la sentencia:

set names utf8;

FAQ:

Se puede hacer aplicado a todo un esquema?: No, tendrás que hacerlo tabla por tabla. Si alguien lo ha conseguido que lo comente!! sería un gran descubrimiento!

Agradecimientos:

A @rcasas  que me enseñó estas cosas tan chulas.

Can’t create table (errno: 150)

Este error es tan típico, que una vez que se ya has sufrido estos fallos, se intentan evitar teniendo un poco de cuidado al crear las tablas. Muchos ya lo conocéis y hay mil páginas…. pero he querido completarlo con algún detalle! Ahí van!

  1. Opción:  Estás añadiendo una clave foránea cuyo tipo no coincide al tipo que hace referencia.

OJO! con INT(11) VS INT(10) : algunas herramientas que hacen de interfaz de bases de datos crean PRIMARY_KEY como INT(11) y el resto de campos de tipo numérico como INT(10).

OJO! con UNSIGNED. Si recibes código heredado y te estás volviendo loco puede que esté indicado como UNSIGNED.

OJO! que la clave foránea sea pueda ser NULL, no influye. Ejemplo:

`modified_by` INT(11) NULL DEFAULT NULL.

 

  1. Opción: Estás añadiendo una clave foránea y el campo al que estás haciendo referencia no existe con ese valor.

Es decir. Imaginemos que a la clave ‘modified_by’ lo tienes inicializado a 5. Te has dado cuenta que has añadido el campo, pero no has añadido la propiedad de que es una foreing key a la tabla de usuarios! Si luego al poner que modified_by es fk te sale este error, comprueba que el usuario 5 existe en la tabla de usuarios.

OJO! Cuando modifiques una tabla con un campo existente ‘modified_by’ que es nulo y pongas que la foreing key no puede ser nulo. Ejemplo:

`modified_by` INT(11) NOT NULL.

No te va a dejar modificar la tabla a no ser de que le pongas un valor inicializado y que sea correcto. Tendrás que actualizar el campo modified_by por un valor correcto y luego crear la tabla. (el mismo caso que antes, pero ahora con NULL).

 

  1. Opción: Si pones “On Delete Set Null” pero el campo está definido como NOT NULL.

OJO! Te contradices más que el programa electoral de Rajoy!