Problema al subir CSV con campos date a MySQL/MariaDB

Básicamente estoy construyendo un script para subir cualquier tipo de fichero CSV a una tabla en MySQL.

El objetivo de mi script es hacerlo sin saber el tipo de datos a priori, sin intervención humana, por lo que mi programa debe «averiguar» el tipo de cada uno de los campos.

Imaginemos que soy capaz de hacer de forma automática el CREATE (lo he hecho, pero no es lo que quería comentar aquí).

Una vez creada la tabla, quiero rellenarla con mis datos del CSV.

En principio, sería fácil, simplemente ejecutando una consulta LOAD DATA INFILE.

Primer problema a recordar:

Sí es una tontería, pero se me olvidó y hasta que me dí cuenta… Básicamente estoy subiendo un fichero desde un formulario, lo cambio de la carpeta temporal del servidor a la definitiva de mi hosting con move_uploaded_file, y entonces paso a hacer el LOAD DATA INFILE. Bueno, pues se me olvidó el «LOCAL», si no lo pones, big trouble, aunque pongas la ruta exacta, no va a funcionar.

El problema de verdad:

Básicamente tenemos

LOAD DATA LOCAL INFILE ‘fichero.csv’ INTO TABLE `mitabla`
FIELDS TERMINATED BY ‘;’
LINES TERMINATED BY ‘\r\n’
IGNORE 1 LINES;

El problema de esto es que si tenemos algún campo fecha en la tabla y el formato en el csv no es yyyy-mm-dd nos va a fallar.

Como yo todos los CSV los tengo en formato dd/mm/yyyy la solución es añadir las siguientes líneas:

(campo1, campo2, campo3, @campo4, @campo5)

La arroba la pondremos delante de todos los campos que sean fecha

y posteriormente ponemos:

SET campo4=str_to_date(@campo4, ‘%d/%m/%Y’), campo5=str_to_date(@campo5, ‘%d/%m/%Y’)

Esto sirve para muchísimas cosas. La @ nos marca una variable temporal donde se va a almacenar el valor leído (como un string) y nosotros podremos transformalo a nuestro antojo en el SET.

 

Averiguar el número de tablas de una base de datos

La consulta es sencilla:

select count(*) from information_schema.tables where table_schema = ‘nombredelabasededatos‘;

Dentro de cada servidor de base de datos de MySQL/MariaDB tendremos una base de datos llamada «information_schema«. Dentro de la misma, una tabla llamada tables. Es en esta en la que encontraremos el catálogo de todas las tablas del servidor.

En un hosting que utilizo para mis pruebas he utilizado esta consulta y funciona, por lo que imagino que tengo privilegios (de lectura) de la tabla. (Me planteo si podré consultar las tablas del resto de usuarios, por ahora me voy a quedar con la duda).

Esta tabla tiene los siguientes campos:

TABLE_CATALOG varchar(512) No
TABLE_SCHEMA varchar(64) No
TABLE_NAME varchar(64) No
TABLE_TYPE varchar(64) No
ENGINE varchar(64) Sí NULL
VERSION bigint(21) UNSIGNED Sí NULL
ROW_FORMAT varchar(10) Sí NULL
TABLE_ROWS bigint(21) UNSIGNED Sí NULL
AVG_ROW_LENGTH bigint(21) UNSIGNED Sí NULL
DATA_LENGTH bigint(21) UNSIGNED Sí NULL
MAX_DATA_LENGTH bigint(21) UNSIGNED Sí NULL
INDEX_LENGTH bigint(21) UNSIGNED Sí NULL
DATA_FREE bigint(21) UNSIGNED Sí NULL
AUTO_INCREMENT bigint(21) UNSIGNED Sí NULL
CREATE_TIME datetime Sí NULL
UPDATE_TIME datetime Sí NULL
CHECK_TIME datetime Sí NULL
TABLE_COLLATION varchar(32) Sí NULL
CHECKSUM bigint(21) UNSIGNED Sí NULL
CREATE_OPTIONS varchar(2048) Sí NULL
TABLE_COMMENT varchar(2048) No

.

Trabajar en MySQL/MariaDB con un campo varchar en lugar de date

Sí, hay veces que cometes el error de importar datos desde un CSV, un TXT o un XLS a una BD MySQL/MariaDB y no te das cuenta de que has almacenado un campo, que en realidad es una fecha, como una cadena de caracteres.

Es decir, tengo mi campo «Cumpleaños» como Varchar en lugar de Date.

Cuando comiences a hacer consultas te darás cuenta de que tienes un problema, sobre todo cuando empieces a consultar por años, meses, días de la semana, etc.

Lo lógico es hacer un ALTER TABLE seguido de un UPDATE, generando un campo nuevo, ahora sí como date y llenándolo de los valores que inicialmente almacenaste en el varchar, pero hay veces que ya no se puede. Imposible. Porque has desarrollado ya todo y no puedes cambiar la estructura de la BD.

Chapuza para solucionarlo: la función STR_TO_DATE

Esta función recibe 2 parámetros, el nombre del campo y el patrón que sigue la fecha almacenada en esa cadena.

Ejemplo:

select sum(aviones) as 'Número de viajes', 
year(STR_TO_DATE(fechaida, '%d/%m/%Y')) as Año 
from viajes 
group by Año

Lo único «complicado» es el patrón. Tienes que ponerlo exactamente como está almacenado en la base de datos en la cadena de caracteres. En el ejemplo sería algo como:

20/05/2017

.

Pasar a DATE un campo VARCHAR en MySQL/MariaDB

Nos enfrentamos a lo siguiente:

Por ejemplo, hemos importado un fichero CSV a MySQL/MariaDB utilizando phpMyAdmin. Este proceso de importación funcionó perfectamente, pero, como no habíamos creado la estructura de la tabla previamente, los campos de tipo fecha nos los ha generado como cadena en la base de datos. Es decir, tengo varios campos VARCHAR y quiero convertirlos en tipo DATE.

En primer lugar nos creamos un campo tipo DATE, sobre el que haremos el UPDATE.

Si nuestro campo VARCHAR tiene el formato: 07/05/2017 (es lo típico que nos pasará si importamos desde un CSV), tenemos que convertirlo en 2017-05-07 para que funcione correctamente.

La sentencia UPDATE sería algo tal que así:

UPDATE `mitabla` SET `campofecha` = CONCAT(SUBSTRING(`campocadena`,7,4),’-‘,SUBSTRING(`campocadena`,4,2),’-‘,SUBSTRING(`campocadena`,1,2)) WHERE 1

Posteriormente, eliminamos el campocadena y renombramos el campofecha para que tenga el nombre primitivo.

Mostrar con PHP tabla de MySQL/MariaDB con número variable de campos

Partiendo de una tabla o consulta de MySQL/MariaDB, queremos obtener un código HTML utilizando PHP y la librería MySQLi en forma OO.

El código fuente lo puedes encontrar en:

https://github.com/pedroguirado/bd_mysql/blob/master/tabla_dinamica.php

<?php

/* ————————————— */
function conectar($maq, $usu, $con, $bas){
$conexion = new mysqli($maq, $usu, $con, $bas);

if ($conexion->connect_error) {
    die(‘Error de Conexión (‘ . $conexion->connect_errno . ‘) ‘. $conexion->connect_error);
}
$acentos = $conexion->query(«SET NAMES ‘utf8′»);

return $conexion;
}

/* ————————————— */

function tabla_dinamica (){
  include_once(«paramconexion.php»);
  $conexion=conectar($maquina,$usuario,$contrasena,$basededatos);
  $resultado=$conexion->query(«Select * from viajes»);
  //printf(«<p>La selección devolvió %d filas y %d columnas.</p>\n», $resultado->num_rows, $resultado->field_count);
  echo «<table><tr>»;
  /* Obtener la información del campo para todas las columnas */
  $info_campo = $resultado->fetch_fields();
  foreach ($info_campo as $valor) {
    printf(«<td>%s</td>»,$valor->name);
  }
  echo «</tr>»;
  /* obtener el array de objetos */
  while ($fila = $resultado->fetch_row()) {
      echo «<tr>»;
      foreach ($fila as $valor)
        printf(«<td>%s</td>»,$valor);
      echo «</tr>»;
  }

  echo «</table>»;

  $resultado->free();
  $conexion->close();
}

?>

 

Desde nuestro documento llamaremos a la función tabla_dinamica()

Modifica la consulta y en el fichero paramconexion.php define las variables para establecer la conexión.

Eliminar contraseña de root en MariaDB/MySQL

En una terminal escribiremos:

/etc/init.d/mysql stop
sudo killall mysqld_safe
sudo killall mysqld
sudo mysqld_safe --skip-grant-tables &
mysql -u root
use mysql;
update user set password=PASSWORD("minuevacontraseña") where User='root';
update user set plugin="mysql_native_password";
quit;
/etc/init.d/mysql stop
sudo kill -9 $(pgrep mysql)
/etc/init.d/mysql start

Esto lo tuve que hacer cuando, tras instalar MariaDB incorrectamente, me comenzó a dar el error siguiente al hacer un msql -u root -p:

ERROR 1524 (HY000): Plugin ‘unix_socket’ is not loaded

Problema con phpMyAdmin, mbstring extension y Ubuntu 16.04

Supongo que esto dentro de poco tiempo no servirá para nada porque habrán arreglado la instalación de PHP7. Pero en mayo de 2016 ha estado curioso dejar el servidor fino.

Vale, he actualizado Ubuntu a 16.04, además de pasarme a php7 sobre Apache. La cantidad de problemas que estoy encontrando está siendo interesante.

El último ha sido con la configuración de phpmyadmin (en este caso es sobre MariaDB, pero vamos, que sobre MySQL creo que pasa lo mismo).

Instalo mi paquete de phpmyadmin y no funciona, a pesar de reiniciar el servicio. Lo que sucede es que no se ha cargado la configuración de phpmyadmin en apache. Para ello:

sudo nano /etc/apache2/apache2.conf

y dentro incluimos:

Include /etc/phpmyadmin/apache.conf

Tras instalarlo y acceder a http://localhost/phpmyadmin te encuentras el siguiente error:

The mbstring extension is missing. Please check your PHP configuration.

La solución es:

sudo apt-get install php-mbstring php7.0-mbstring php-gettext
sudo service apache2 restart

Me estoy encontrando en muchos casos que el php7 no va NADA FINO y tienes que reinstalar paquetes o instalar algunos que normalmente tienes instalados, véase el php-curl.
Además, el apt-get install php7 me ha estado fallando por culpa del paquete php-fpm. Lo he solucionado descargando los fuentes de PHP e instalando a la vieja usanza, con sus configures, sus makes y sus cosas.

Es lo que pasa por pasarte a una versión «estable» antes de la cuenta.

Instalando o actualizando MariaDB

Si tu versión de MariaDB es la 5 deberías pensar ya en actualizar a la 10.1 estable.

https://mariadb.com/kb/en/mariadb/upgrading-from-mariadb-55-to-mariadb-100/

Si quieres instalar MariaDB desde 0:

https://downloads.mariadb.org/mariadb/repositories

Fácilmente obtendrás las siguientes órdenes (para Ubuntu 16.04 en 64bits como es mi caso):

sudo apt-get install software-properties-common

sudo apt-key adv –recv-keys –keyserver hkp://keyserver.ubuntu.com:80 0xF1656F24C74CD1D8

sudo add-apt-repository ‘deb [arch=amd64] http://tedeco.fi.upm.es/mirror/mariadb/repo/10.1/ubuntu xenial main’

sudo apt-get update
sudo apt-get install mariadb-server

Recuerda probar todo haciendo un:

mysql -u root -p

Desinstalar completamente MySQL en Ubuntu

Si haces un

apt-get remove –purge mysql-server

esperas que se desinstale completamente mysql de tu equipo. Pues no. Lo comprobarás si al cabo del tiempo intentas instalar otra versión del mismo o si decides pasarte a MariaDB. Te va a pedir la contraseña del root y hay de ti como no te acuerdes o, imagina que te acuerdas, se restaurarán todos los datos que tenías por ahí desperdigados y que creías que se habían borrado.

Para desinstalar completamente sigue los siguientes pasos:

 

sudo -i


service mysql stop


killall -KILL mysql mysqld_safe mysqld


apt-get --yes purge mysql-server mysql-client


apt-get --yes autoremove --purge


apt-get autoclean


deluser --remove-home mysql


delgroup mysql


rm -rf /etc/apparmor.d/abstractions/mysql /etc/apparmor.d/cache/usr.sbin.mysqld /etc/mysql /var/lib/mysql /var/log/mysql* /var/log/upstart/mysql.log* /var/run/mysqld


updatedb

exit

Desinstalamos, borramos datos, eliminamos usuario y grupo.

Y si quieres borrar el log:

rm ~/.mysql_history

 

Fuente:

http://askubuntu.com/questions/640899/uninstall-mysql-completely

Exportar/Importar tablas de MySQL/MariaDB desde consola

Si quiero exportar una base de datos para luego importarla en otro servidor desde un terminal:

mysqldump –u usuario -p nombre_basededatos > fichero_exportación.sql

Nos pedirá la contraseña y nos genera el fichero en el directorio que especifiquemos o en la carpeta actual.

Si ese fichero quiero importarlo, podré hacerlo con la opción de Importar de phpMyAdmin o desde consola con el siguiente comando:

mysql –u usuario -p nombre_basededatos < fichero_importación.sql

En el formato en el que importamos/exportamos tendremos nuestro DROP TABLE seguido de CREATE TABLE, por lo que podemos olvidarnos de tener que crear las tablas por nuestra cuenta.

Si lo que queremos es exportar una tabla concreta de una bse de datos:

mysqldump –u usuario -p nombre_basededatos nombre_tabla > fichero_exportación.sql