存储过程的混合结果从不同invokations临时表 [英] Stored procedure mixing results into temp tables from different invokations
问题描述
我使用的是在MySQL创建存储记录从数据库的表，然后用于选择从存储的记录那些子集的最终结果3个临时表的存储过程。
问题在于，尽管它应该是存储过程中的临时表是每个MySQL的会话是唯一的，我得到从存储过程调用不同的结果好坏参半。
让我们以一个真实的案例escenario解释这个问题：
我们得到的用户A，B，C ，在同一时刻访问自己的网站做不同的搜索。
对于'AAAA'，为'BBBB'用户B搜索和用户C搜索中交用户A的搜索，然后在 Web服务器做3调用数据库服务器在同一时刻
在调用存储过程是：
通话SP（'AAAA'）;
叫SP（'BBBB'）;
叫SP（CCCC'）;
在结果每次调用应是这样的：
对于呼叫SP（'AAAA'）;
Record_AAAA1
Record_AAAA2
Record_AAAA3
有关调用SP（'BBBB'）;
Record_BBBB1
Record_BBBB2
Record_BBBB3
Record_BBBB4
有关调用SP（CCCC'）;
Record_CCCC1
Record_CCCC2
但在我的结果得到的是是这样的：
对于呼叫SP（'AAAA'）;
Record_AAAA1
Record_AAAA2
Record_BBBB2
Record_AAAA3
Record_BBBB4
Record_CCCC2
有关调用SP（'BBBB'）;
Record_BBBB1
Record_BBBB2
Record_CCCC1
Record_CCCC2
Record_AAAA1
Record_AAAA2
Record_BBBB3
Record_BBBB4
有关调用SP（CCCC'）;
Record_CCCC1
Record_AAAA1
Record_AAAA2
Record_CCCC2
有时我没有结果，有时我得到正确的结果，所以结果取决于执行。
所以我得到错误的结果，如果超过1个用户使用，同时网络搜索。正如我读过的临时表是唯一的每个不同的会话，因此各个不同的执行存储过程中应使用不同的临时表。
它的 MySQL服务器的问题后，因为我已经做了一些测试，通过Linux控制台通过MySQL客户端连接到同一台服务器2台电脑。
这发生在生产数据库服务器，并在我的本地数据库服务器。
我使用的 MySQL服务器版本：67年5月1日 -0ubuntu0.11.10.1日志（Ubuntu的）
为什么会这样发生？怎么可能是解决？
在此先感谢，如果你需要从数据库服务器配置的一些数据让我知道。
存储过程是相当复杂和凌乱（ http://pastebin.com/pQ6VqHBn ），所以在简要它做一些事情为：
1）从调用接收参数
2），这取决于所述参数创建一个插入查询以tmpfinal临时表
3）从选择从tmpfinal使用WHERE搜索词插入tmpdest临时表
4）tmpfinal插入TMP DEST记录删除
5）tmpfinal使用WHERE搜索词插入tmpnorm临时表从选择
6）SELECT * FROM tmpnorm临时表顺序由search_term_punctuation
7）SELECT * FROM tmpdest临时表顺序由search_term_punctuation2
结果插入tmpnorm和tmpdest有时是不同的并发存储过程调用不同的结果。
TABLE定义中的存储过程
DROP TEMPORARY TABLE IF EXISTS tmpfinal;CREATE TEMPORARY TABLE IF NOT EXISTS tmpfinal（
康沃INT，
胡斯托INT，
兰INT，
甚至INT，
炫酷INT，
destacado INT，
NIVEL十进制（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，
康沃INT，
tipo_destacado枚举（'superdestacado'，'destacado'，'anadido'）
）;
DROP TABLE IF EXISTS tmpnorm;CREATE TABLE IF NOT EXISTS tmpnorm（
ID INT AUTO_INCREMENT PRIMARY KEY，
康沃INT
）;
GENERATED INSERT INTO TMPFINAL查询示例
插入tmpfinal
（康沃，胡斯托，兰，甚至，NIVEL，destacado，中心）
选择不同的convocatoria_id，胡斯托，排名，evento_id，niveldes，destacado，从centro_id
（AGAINST（'+每期）选择不同的MATCH（eventos_busqueda.temario_ind）的排名中，
MATCH（eventos_busqueda.curso_ind）AGAINST（'+每期IN BOOLEAN MODE）为胡斯托，eventos.evento_id，centros.centro_id，
orden_bus +
（案件
WHEN convocatoria_opciones_webs.espacio_id = 2，则1
WHEN convocatoria_opciones_webs.espacio_id = 6 THEN 2
WHEN convocatoria_opciones_webs.espacio_id = 1，则3
其他4 END）* 1000 +
CAST（1 /（（
案件
当convocatoria_opciones_webs.nivel为null，则1
当convocatoria_opciones_webs.nivel = 0则1
其他convocatoria_opciones_webs.nivel
结束）
*（
案件
当preciocupon为NULL，则1
当preciocupon = 0，1
否则preciocupon
结束））为十进制（14,10））作为niveldes，
convocatorias.convocatoria_id，
案件
WHEN convocatoria_opciones_webs.orden_bus＆LT; 100 THEN 1
ELSE 0
END AS destacado
从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网ON convocatoria_opciones_webs.web_id = webs.web_id和webs.web_id = 1
INNER JOIN evento_subtemas上eventos.evento_id = evento_subtemas.evento_id
INNER JOIN evento_temas上eventos.evento_id = evento_temas.evento_id
INNER JOIN subtemas上subtemas.subtema_id = evento_subtemas.subtema_id
INNER JOIN temas上temas.tema_id = evento_temas.tema_id
内部联接eventos_busqueda上eventos.evento_id = eventos_busqueda.evento_id
WHERE（convocatorias.publicar = 1或convocatorias.publicar = 3）和（convocatorias.inicio＆GT; CURRENT_DATE（）或convocatorias.inicio IS NULL）
和匹配（eventos_busqueda.curso_ind，eventos_busqueda.temario_ind）AGAINST（'+每期IN BOOLEAN MODE）
而不是（convocatoria_opciones_webs.espacio_id为空），而不是convocatoria_opciones_webs.nivel为空
和webs.web_id = 1）为t
GENERATED INSERT INTO TMPDEST查询示例
INSERT INTO tmpdest（康沃，tipo_destacado）
SELECT Z.convo，tipo_destacado FROM（
SELECT康沃，tipo_destacado FROM（
SELECT（@ lim- @ R）作为orden_fila，tmpfinal.centro，tmpfinal.nivel，tmpfinal.convo，
@cg＆LT;＆GT; tmpfinal.centro AS centro_distinto，
案件
WHEN cow.orden＆LT; = 3，那么'superdestacado
WHEN tmpfinal.destacado = 1那么'destacado
ELSE NULL
END AS tipo_destacado，
案件
WHEN @cg＆LT;＆GT; tmpfinal.centro THEN @r：= @lim
ELSE 1
END＆GT; 0与（@r：= @r - 1）＆GT; = 0 AND（@cg：= tmpfinal.centro）IS NOT NULL
从tmpfinal
INNER JOIN convocatoria_opciones_webs AS母牛tmpfinal.convo = cow.convocatoria_id
WHERE destacado = 1 AND cow.web_id = 1 AND胡斯托＆GT; = 1
ORDER BY tmpfinal.justo DESC，tmpfinal.rankin DESC，ASC tmpfinal.nivel）笔
WHERE centro_distinto = 1 OR（orden_fila小于5与centro_distinto = 0）
LIMIT 15）z
INSERT INTO TMPNORM查询示例
INSERT INTO tmpnorm（康沃，tipo_destacado）
SELECT Z.convo，tipo_destacado FROM（
SELECT康沃，tipo_destacado FROM（
SELECT（@ lim- @ R）作为orden_fila，tmpfinal.centro，tmpfinal.nivel，tmpfinal.convo，
@cg＆LT;＆GT; tmpfinal.centro AS centro_distinto，
案件
WHEN cow.orden＆LT; = 3，那么'superdestacado
WHEN tmpfinal.destacado = 1那么'destacado
ELSE NULL
END AS tipo_destacado，
案件
WHEN @cg＆LT;＆GT; tmpfinal.centro THEN @r：= @lim
ELSE 1
END＆GT; 0与（@r：= @r - 1）＆GT; = 0 AND（@cg：= tmpfinal.centro）IS NOT NULL
从tmpfinal
INNER JOIN convocatoria_opciones_webs AS母牛tmpfinal.convo = cow.convocatoria_id
WHERE destacado = 1 AND cow.web_id = 1 AND胡斯托＆GT; = 1
ORDER BY tmpfinal.justo DESC，tmpfinal.rankin DESC，ASC tmpfinal.nivel）笔
WHERE centro_distinto = 1 OR（orden_fila小于5与centro_distinto = 0）
LIMIT 15）z
的例子生成的SELECT FROM TMPNORM / TMPDEST QUERY
选择不同的tmpnorm.id，IFNULL（IF（centros.acronimos＆LT;＆GT;''，centros.acronimos，centros.centro），centros.centro）为中心，centros.centro_id ，
eventos_modalidad.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，'德拉萨卢'，1），temario），300）的AS temario，
orden_home为奥登，convocatorias.horasduracion，convocatorias.textoduracion AS textoduracion，convocatorias。preciocurso，
convocatorias.gratuito，情况下，当tipofecha为null，则'其他COALESCE（tipofecha，''）+''+ COALESCE（anotipofecha，''）结束的tipofecha，
convocatorias。preoferta，convocatorias.finoferta，convocatorias.subvencionado，convocatorias.pais_id，centros.pais_id AS pais_cen
从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网ON convocatoria_opciones_webs.web_id = webs.web_id和webs.web_id = 1
INNER JOIN evento_subtemas上eventos.evento_id = evento_subtemas.evento_id
INNER JOIN evento_temas上eventos.evento_id = evento_temas.evento_id INNER JOIN tmpnorm ON tmpnorm.convo = convocatorias.convocatoria_id INNER JOIN subtemas上subtemas.subtema_id = evento_subtemas.subtema_id
INNER JOIN temas上temas.tema_id = evento_temas.tema_id 为了通过tmpnorm.id
tmpnorm 不是一个临时表，所以它会将所有会话之间共享。
CREATE TABLE IF NOT EXISTS tmpnorm（
ID INT AUTO_INCREMENT PRIMARY KEY，
康沃INT
）;
每个会话可以 tmpfinal 和 tmpdest ，但只要它们插在临时表自己的孤立数据到 tmpnorm ，他们成为合并后，利用所有会话的所有数据。然后，这个数据在你联接到所有其他非临时表最终的查询使用。
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.
这篇关于存储过程的混合结果从不同invokations临时表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
