CODEWITHSUNDEEP
mysql
Juan
Juan

Reputation: 171

Failed to create temporary table Mysql Php Pdo (mysql.column_stats: expected column 'max_value' …)

I have an extensive procedure that runs directly in mysql works, now when I put it in php I get an error from the beginning that is creating a temporary table from where I will be getting data for the remainder of the report. This is the code that I try and when executing returns OK because the $ stmt -> execute () is executing but the table is not created.

    $stmt = Conexion::Conectar()->prepare("CREATE TEMPORARY TABLE 
    resultado_metrica_temporal 
    (idMac360_FK int, idMetricaGrupo_FK int, resultado float, posibles int, 
    positivas int);");

    if($stmt -> execute()){
       echo 'OK';
    } else {
       echo 'ERROR';
    }
    $stmt = null;

If the table would be created I could insert data from another query in the temporary table to return part of a report:

INSERT INTO resultado_metrica_temporal
SELECT mrm.idMac360_FK, mrm.idMetricaGrupo_FK, mrm.resultado, mrm.posibles, 
mrm.positivas
from mac360_resultado_metrica mrm
inner join mac360 m on m.idMac360 = mrm.idMac360_FK
inner join datos_nomina dn on dn.idDatosNomina = m.idDatosNominaRecibe_FK
inner join cuenta_proceso_celula cpc on cpc.idCuentaProcesoCelula = 
dn.idCuentaProcesoCelula_FK
WHERE m.fechaContacto BETWEEN '2018-6-01' AND '2018-12-01'
AND dn.idSite_FK IN (2) AND cpc.idCuenta_FK IN (2)
AND cpc.idProceso_FK IN (17)
AND m.idEstado_FK != 3
AND m.idMacDetalle_FK != 1
AND m.idUsuarioCarga_FK NOT IN(2186, 2187, 2188, 2189, 2190);

Query in the temporary table:

SELECT SUM(mrm.posibles) as posibles, SUM(mrm.positivas) as positivas,
COUNT(DISTINCT(mrm.idMac360_FK)) as cantidad, me.detMetrica as detGenerales,
MONTH(m.fechaContacto) as mes, me.idMetrica as idGenerales
FROM resultado_metrica_temporal mrm
INNER JOIN mac360 m ON m.idMac360 = mrm.idMac360_FK
INNER JOIN metrica_grupo mg ON mg.idMetricaGrupo = mrm.idMetricaGrupo_FK
INNER JOIN metrica me ON me.idMetrica = mg.idMetrica_FK
GROUP BY MONTH(m.fechaContacto), idGenerales;

But as the temporary table is not created any of this works These are the Log MySql

2018-11-29 17:38:31 18908 [ERROR] Incorrect definition of table 
mysql.column_stats: expected column 'min_value' at position 3 to have type 
varbinary(255), found type varchar(255).

2018-11-29 17:38:31 18908 [ERROR] Incorrect definition of table 
mysql.column_stats: expected column 'max_value' at position 4 to have type 
varbinary(255), found type varchar(255).

Now if I take that query and execute it in mysql or phpmyadmin the table is created ok, and all querys executes ok:

    CREATE TEMPORARY TABLE resultado_metrica_temporal 
    (idMac360_FK int, idMetricaGrupo_FK int, resultado float, posibles int, 
    positivas int);

enter image description here

I do not understand the error, what are the errors of the log?

Upvotes: 2

Views: 572

Answers (1)

marekful
marekful

Reputation: 15361

This is likely the result of a recent MySQL server version upgrade. You may fix it by running the mysql_upgrade utility with root user.

$ mysql_upgrade -uroot

This will alter and update MySQL's internal tables.

Upvotes: 2

Related Questions