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
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.