MySql的临时表后生存连不上（从.NET） [英] MySql temp table survives after connection closed (from .NET)

问题描述

我要创建MySQL中的临时表中，并期待它不是在那里我断开后。我已经剥离出来的一切我的code和仅此留下：

I'm creating a temporary table in MySql and expecting it to not be there after I disconnect. I've stripped everything out of my code and left only this:

        //create the first connection
        var connection = new MySqlConnection(ConnectionString);
        connection.Open();

        //create a temp table
        var cmd = connection.CreateCommand();
        cmd.CommandText = "CREATE TEMPORARY TABLE SomeDummyTable(Column1 float)";
        cmd.ExecuteNonQuery();

        //read the new temp table to proove it's created
        cmd = connection.CreateCommand();
        cmd.CommandText = "select * from SomeDummyTable";
        cmd.ExecuteNonQuery();

        //close and dispose of the first connection
        connection.Close();
        connection.Dispose();

        //create new connection
        MySqlConnection connection2 = new MySqlConnection(ConnectionString);
        connection2.Open();
        var cmd2 = connection2.CreateCommand();

        cmd2.CommandText = "select * from SomeDummyTable";
        //this does not throw an exception, even though SomeTable is 
        //a temp table from the previous connection
        cmd2.ExecuteNonQuery();

所以我创建使用一个连接一个临时表，然后关闭连接，并打开另一个。余可使用第二连接见临时表。怎么办？我在想什么？

So I create a temp table using one connection, then close that connection and open another. I can see the temp table using the second connection. What gives? What am I missing?

此外：如果我关闭应用程序，然后重新打开该临时表是不存在的。难道是一些连接池还是什么？

Additionally: If I close the application and reopen it the temp table is not there. Could it be some connection pooling or something?

推荐答案

这似乎是与MySQL的连接池，或者特定的供应商，您正在使用中的错误。根据我能找到的粗略搜索的临时表时它们被创建的连接被关闭，应自动删除。我的没有的期望连接池来改变这种行为。

This seems like a bug with MySQL's connection pooling, or maybe the particular provider you're using. Based on what I could find in cursory search temporary tables should be dropped automatically when the connection in which they were created is closed. I would not expect connection pooling to alter that behavior.

两种解决方法，然后浮现在脑海中：

Two workarounds then come to mind:

1. 显式删除临时表，当你使用它做。
2. 加入池= false禁用连接池; 来连接字符串
。

1. Explicitly drop the temporary table when you're done using it.
2. Disable connection pooling by adding Pooling=False; to your connection string.

这个MySQL错误报告似乎中肯。显然，其他连接属性都不会被重置。也许临时表没有被立即丢弃（或以其他方式正在向无法进入后续连接）是同一个问题的一部分。

This MySQL bug report seems pertinent. Apparently other connection properties aren't being reset. Maybe the temporary table not being dropped immediately (or otherwise being made inaccessible to subsequent connections) is part of the same problem.

您应该报告的错误。我搜索了错误的临时表连接池，但没有找到任何明确涉及您的方案。

You should report the bug. I searched the bugs for "temporary table connection pooling" but didn't find any that covered your scenario explicitly.

这篇关于MySql的临时表后生存连不上（从.NET）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！