CODEWITHSUNDEEP
phpmysqlapachestored-procedurestemp-tables
Packet Tracer
Packet Tracer

Reputation: 3924

Stored procedure mixing results into temp tables from different invokations

I'm using a stored procedure in Mysql that creates 3 temporary tables that store records from database's tables, and then used to select the final result from those subset of stored records.

The problem is that, though it's supposed that temp tables within stored procedures are unique for each MySQL session, I get mixed results from different invocations from the stored procedure.

Let's explain the problem with a real case escenario:

We got users A, B, C that access my website doing different searches at "the same moment".

User A searches for 'AAAA', user B searches for 'BBBB' and user C searches for 'CCCC', then the webserver does 3 invocations to the database server at "the same moment".

The invocations to the stored procedure are: 

call SP('AAAA'); 
call SP('BBBB'); 
call SP('CCCC');

The results for each invocation should be something like: For call SP('AAAA');

Record_AAAA1
Record_AAAA2
Record_AAAA3

For call SP('BBBB');

Record_BBBB1
Record_BBBB2
Record_BBBB3
Record_BBBB4

For call SP('CCCC');

Record_CCCC1
Record_CCCC2

But the results I get are something like: For call SP('AAAA');

Record_AAAA1
Record_AAAA2
Record_BBBB2
Record_AAAA3
Record_BBBB4
Record_CCCC2

For call SP('BBBB');

Record_BBBB1
Record_BBBB2
Record_CCCC1
Record_CCCC2
Record_AAAA1
Record_AAAA2
Record_BBBB3
Record_BBBB4

For call SP('CCCC');

Record_CCCC1
Record_AAAA1
Record_AAAA2
Record_CCCC2

Sometimes I get none results, and sometimes I get right results, so the results depend on execution.

So I'm getting wrong results if more than 1 user uses the web search at the same time. As I've read temporary tables are unique to each different session, so each different execution of the stored procedure should use a different temporary table.

It's MySQL server issue, because I've done some tests with 2 computers connected to the same server via MySQL client through Linux Console.

This happens in production database server and in my local database server.

I'm using MySQL Server version: 5.1.67-0ubuntu0.11.10.1-log (Ubuntu)

Why could this be happening? and how could it be solved?

Thanks in advance, if you need some config data from database servers let me know.

The stored procedure is quite complex and messy (http://pastebin.com/pQ6VqHBn), so in brief it does something as:

1) receives parameters from the invocation

2) create an insert query to tmpfinal temp table depending on the parameters

3) insert into tmpdest temp table from a select from tmpfinal using a where search term

4) delete from tmpfinal the records inserted into tmp dest

5) insert into tmpnorm temp table from a select from tmpfinal using a where search term

6) select * from tmpnorm temp table order by search_term_punctuation

7) select * from tmpdest temp table order by search_term_punctuation2

Results inserted into tmpnorm and tmpdest sometimes are mixed results from different concurrent stored procedure invocation.

TABLE DEFINITION INSIDE STORED PROCEDURE

DROP TEMPORARY TABLE IF EXISTS tmpfinal;

CREATE TEMPORARY TABLE  IF NOT EXISTS tmpfinal (
    convo int,
    justo int,
    rankin int,
    even int,
    centro int,
    destacado int,
    nivel decimal(12,8),
    num_fila int AUTO_INCREMENT  PRIMARY KEY,
    num_fila_centro int 
);


DROP TEMPORARY TABLE IF EXISTS tmpdest;

CREATE TEMPORARY TABLE  IF NOT EXISTS tmpdest (
    id int AUTO_INCREMENT PRIMARY KEY,
    convo int,
    tipo_destacado enum ('superdestacado', 'destacado', 'anadido') 
);


DROP TABLE IF EXISTS tmpnorm;

CREATE TABLE  IF NOT EXISTS tmpnorm (
    id int AUTO_INCREMENT PRIMARY KEY,
    convo int 
);

AN EXAMPLE OF GENERATED INSERT INTO TMPFINAL QUERY 

insert into tmpfinal
        (convo,justo,rankin,even,nivel,destacado,centro) 
    select distinct convocatoria_id,justo,ranking,evento_id,niveldes,destacado,centro_id from  
            (select distinct MATCH (eventos_busqueda.temario_ind) AGAINST ('+salud') as ranking, 
                    MATCH (eventos_busqueda.curso_ind) AGAINST ('+salud' IN BOOLEAN MODE) as justo,  eventos.evento_id, centros.centro_id, 
                    orden_bus + 
                        (CASE 
                            WHEN convocatoria_opciones_webs.espacio_id=2 THEN 1 
                            WHEN convocatoria_opciones_webs.espacio_id=6 THEN 2
                            WHEN convocatoria_opciones_webs.espacio_id=1 THEN 3 
                            else 4 END ) * 1000 +
                            CAST( 1/( (
                            case 
                                when convocatoria_opciones_webs.nivel IS NULL then 1 
                                when convocatoria_opciones_webs.nivel=0 then 1 
                                else convocatoria_opciones_webs.nivel 
                            end)  
                            * (
                                case 
                                    when preciocupon IS NULL then 1 
                                    when preciocupon=0 then 1 
                                    else preciocupon 
                                end)) as decimal(14,10)) as niveldes ,  
                    convocatorias.convocatoria_id , 
                        CASE 
                            WHEN convocatoria_opciones_webs.orden_bus < 100 THEN 1 
                            ELSE 0 
                        END AS destacado 
                                 FROM eventos 
                                 INNER JOIN convocatorias ON eventos.evento_id = convocatorias.evento_id 
                                 INNER JOIN convocatoria_opciones_webs ON convocatoria_opciones_webs.convocatoria_id = convocatorias.convocatoria_id 
                                 LEFT JOIN aux_provincias ON convocatorias.provincia_id = aux_provincias.Provincia_id 
                                 INNER JOIN centros ON convocatorias.centro_id = centros.centro_id 
                                 INNER JOIN eventos_modalidad ON eventos_modalidad.Modalidad_id = eventos.Modalidad_id 
                                 INNER JOIN eventos_imparticion ON eventos_imparticion.Imparticion_id = eventos.Imparticion_id 
                                 INNER JOIN webs ON convocatoria_opciones_webs.web_id = webs.web_id and webs.web_id = 1
                                 INNER JOIN evento_subtemas on eventos.evento_id=evento_subtemas.evento_id
                                 INNER JOIN evento_temas on eventos.evento_id=evento_temas.evento_id                               
                                 INNER JOIN subtemas on subtemas.subtema_id=evento_subtemas.subtema_id
                                 INNER JOIN temas on temas.tema_id = evento_temas.tema_id             
                                 inner join eventos_busqueda on eventos.evento_id=eventos_busqueda.evento_id  
                        WHERE (convocatorias.publicar = 1 OR convocatorias.publicar = 3) AND (convocatorias.inicio > CURRENT_DATE() OR convocatorias.inicio IS NULL)  
                                and match   (eventos_busqueda.curso_ind, eventos_busqueda.temario_ind) AGAINST ('+salud' IN BOOLEAN MODE) 
                                AND not (convocatoria_opciones_webs.espacio_id is null) and not convocatoria_opciones_webs.nivel is null
                                and webs.web_id=1) as t

AN EXAMPLE OF GENERATED INSERT INTO TMPDEST QUERY 

INSERT INTO tmpdest (convo, tipo_destacado) 
    SELECT Z.convo, tipo_destacado FROM (
        SELECT convo, tipo_destacado FROM (
            SELECT (@lim-@r) as orden_fila,tmpfinal.centro, tmpfinal.nivel,tmpfinal.convo, 
                    @cg <> tmpfinal.centro AS centro_distinto,              
                    CASE 
                        WHEN cow.orden <= 3 THEN 'superdestacado'
                        WHEN tmpfinal.destacado = 1 THEN 'destacado'
                        ELSE NULL
                    END AS tipo_destacado, 
                    CASE 
                        WHEN @cg <>tmpfinal.centro THEN @r := @lim 
                        ELSE 1 
                    END > 0 AND (@r := @r - 1) >= 0 AND (@cg := tmpfinal.centro) IS NOT NULL
                FROM tmpfinal
                INNER JOIN convocatoria_opciones_webs AS cow ON tmpfinal.convo = cow.convocatoria_id 
            WHERE destacado=1 AND cow.web_id = 1  AND justo >= 1 
                ORDER BY  tmpfinal.justo DESC, tmpfinal.rankin DESC, tmpfinal.nivel ASC ) T
        WHERE centro_distinto = 1 OR (orden_fila < 5 AND centro_distinto = 0)
            LIMIT 15 ) Z

AN EXAMPLE OF INSERT INTO TMPNORM QUERY 

INSERT INTO tmpnorm (convo, tipo_destacado) 
    SELECT Z.convo, tipo_destacado FROM (
        SELECT convo, tipo_destacado FROM (
            SELECT (@lim-@r) as orden_fila,tmpfinal.centro, tmpfinal.nivel,tmpfinal.convo, 
                    @cg <> tmpfinal.centro AS centro_distinto,              
                    CASE 
                        WHEN cow.orden <= 3 THEN 'superdestacado'
                        WHEN tmpfinal.destacado = 1 THEN 'destacado'
                        ELSE NULL
                    END AS tipo_destacado, 
                    CASE 
                        WHEN @cg <>tmpfinal.centro THEN @r := @lim 
                        ELSE 1 
                    END > 0 AND (@r := @r - 1) >= 0 AND (@cg := tmpfinal.centro) IS NOT NULL
                FROM tmpfinal
                INNER JOIN convocatoria_opciones_webs AS cow ON tmpfinal.convo = cow.convocatoria_id 
            WHERE destacado=1 AND cow.web_id = 1  AND justo >= 1 
                ORDER BY  tmpfinal.justo DESC, tmpfinal.rankin DESC, tmpfinal.nivel ASC ) T
        WHERE centro_distinto = 1 OR (orden_fila < 5 AND centro_distinto = 0)
            LIMIT 15 ) Z

AN EXAMPLE OF GENERATED SELECT FROM TMPNORM/TMPDEST QUERY 

select distinct tmpnorm.id, IFNULL(IF(centros.acronimos<>'', centros.acronimos, centros.centro), centros.centro) as centro, centros.centro_id,
                    eventos_modalidad.color AS color, eventos_modalidad.tipo AS tipo,convocatorias.evento_id,
                    eventos_imparticion.tipo AS tipoevento,
                    convocatorias.convocatoria_id, eventos.evento, convocatorias.inicio,
                    aux_provincias.provincia,SUBSTRING(temario, LOCATE(SUBSTRING_INDEX(temario, 'salud', 1), temario), 300) AS temario, 
                    orden_home as orden,convocatorias.horasduracion, convocatorias.textoduracion AS textoduracion, convocatorias.preciocurso, 
                    convocatorias.gratuito,case  when tipofecha IS NULL  then '' else COALESCE(tipofecha , '') +' '+ COALESCE(anotipofecha , '')  end as tipofecha,
                  convocatorias.preoferta, convocatorias.finoferta,convocatorias.subvencionado, convocatorias.pais_id, centros.pais_id AS pais_cen  
    FROM eventos 
    INNER JOIN convocatorias ON eventos.evento_id = convocatorias.evento_id
    INNER JOIN convocatoria_opciones_webs ON convocatoria_opciones_webs.convocatoria_id = convocatorias.convocatoria_id 
    LEFT JOIN aux_provincias ON convocatorias.provincia_id = aux_provincias.Provincia_id 
    INNER JOIN centros ON convocatorias.centro_id = centros.centro_id 
    INNER JOIN eventos_modalidad ON eventos_modalidad.Modalidad_id = eventos.Modalidad_id 
    INNER JOIN eventos_imparticion ON eventos_imparticion.Imparticion_id = eventos.Imparticion_id 
    INNER JOIN webs ON convocatoria_opciones_webs.web_id = webs.web_id and webs.web_id = 1
    INNER JOIN evento_subtemas on eventos.evento_id=evento_subtemas.evento_id
    INNER JOIN evento_temas on eventos.evento_id=evento_temas.evento_id

    INNER JOIN tmpnorm ON tmpnorm.convo=convocatorias.convocatoria_id            

    INNER JOIN subtemas on subtemas.subtema_id=evento_subtemas.subtema_id
    INNER JOIN temas on temas.tema_id = evento_temas.tema_id 

    order by tmpnorm.id

Upvotes: 2

Views: 1733

Answers (1)

Bill Karwin
Bill Karwin

Reputation: 562558

tmpnorm is not a temporary table, so it will be shared among all your sessions. 

CREATE TABLE  IF NOT EXISTS tmpnorm (
    id int AUTO_INCREMENT PRIMARY KEY,
    convo int 
);

Each session may have its own isolated data in temporary tables tmpfinal and tmpdest, but as soon as they insert into tmpnorm, they become merged, using all the data from all the sessions. Then this data is used in your final query that joins to all your other non-temporary tables.

Upvotes: 1

Related Questions