CODEWITHSUNDEEP
mysqltemp-tables
Jorge Andres Delgado Medina
Jorge Andres Delgado Medina

Reputation: 11

MySQL temporary table SELECT COUNT(*) returning varying results

I have a problem with temporary tables in mysql. I created this temporary table:

CREATE TEMPORARY TABLE IF NOT EXISTS tmp_general_detalle_entrada_salida (

    numero_registro INT(10), 
    numero_admision INT(10), 
    id_referencia INT(10), 
    facturable CHAR(1), 
    fecha_acceso DATE, 
    cod_via INT(10), 
    anexo2 VARCHAR(40), 
    anexo3 VARCHAR(40), 
    cod_bodega CHAR(3), 
    id_centro_costo INT(10), 
    cod_medico INT(10), 
    cantidad FLOAT(15,2), 
    precio_venta_bruto FLOAT(15,2), 
    descuento FLOAT(15,2), 
    precio_venta_neto FLOAT(15,2), 
    copago FLOAT(15,2), 
    consumido CHAR(1) 

)ENGINE = INNODB;

When I do a simple select count(*) from tmp_general_detalle_entrada_salida on the temporary table, the query returns random values (5, 4, 5, 5, 0, 'table doesn't exist', etc). I'm working with mysql 5.0.51b and PHP 5.2.6.

Upvotes: 1

Views: 1159

Answers (1)

atxdba
atxdba

Reputation: 5216

The changing numbers makes it seem as the data is mutating between your selects. The table does not exist indicates your connection was closed in between selects. Temporary tables go away when your connection closes.

You could try running

set global general_log='ON';
set global general_log_file='/tmp/mysql.log'

This will log every single query from connections to selects /tmp/mysql.log. This will give you more insight to what the servers seeing.

Upvotes: 2

Related Questions