on 2014 Jun 06 7:53 AM
Something is wrong with client side (.NET) connection pooling when connecting to SQL Anywhere 12 and 16 servers. When connection is returned back to the pool it loses its name (connection name becomes empty). When trying to find a connection in the pool next time, nothing is found because connection strings do not match (the new connection has a name and the one in the pool doesn't). So a new connection is established everytime, i. e. pooling does not work.
Here is a sample C# code:
//test.aspx.cs: protected void Page_Load(object sender, EventArgs e) { if (!Page.IsPostBack) { HttpContext.Current.Session["user_name"] = "CONN_TEST_USER"; } } protected void Button1_Click(object sender, EventArgs e) { DBUtil db = new DBUtil(); for (int i = 0; i < 10; i++) { object obj1 = db.ExecuteNonQuery("MESSAGE string('test ', connection_property('number'), ', ', connection_property('name'), ', ', current timestamp);"); } }
//DBUtil.cs: #region Variables public SAConnection conn; protected SATransaction trans; #endregion public DBUtil() { } public int ExecuteNonQuery(string SQLStatement) { int n = 0; SACommand cmd = this.GetAsaCommand(SQLStatement); try { n = cmd.ExecuteNonQuery(); } catch (Exception ex) { throw (ex); } finally { CloseConnection(); if (cmd != null) cmd.Dispose(); } return n; } protected SACommand GetAsaCommand(string SQLStatement) { SACommand cmd = new SACommand(SQLStatement, this.GetConnection(), this.trans); return cmd; } protected SAConnection GetConnection() { try { if (this.conn == null) { string application = "WEB 2.0"; this.conn = new SAConnection("ENG=...;DBN=...;LINKS=tcpip;POOLING=TRUE;Max Pool Size=100;Min Pool Size=0;PROWS=100;Idle=60;UID=web_user;PWD=sql;APP=" + application + " ();Con=" + HttpContext.Current.Session["user_name"].ToString()); } if (this.conn.State != ConnectionState.Open) { this.conn.Open(); } } catch (Exception ex) { } return this.conn; } public void CloseConnection() { try { if (this.conn != null && this.conn.State != ConnectionState.Closed) { this.conn.Close(); } } catch (Exception ex) { } }
After clicking the button, I get server messages with the following query:
select msg_id, msg_text from sa_server_messages() order by msg_id desc
The results:
1856,test 255, CONN_TEST_USER, 2014-06-06 09:48:18.152 1855,test 254, CONN_TEST_USER, 2014-06-06 09:48:18.148 1854,test 253, CONN_TEST_USER, 2014-06-06 09:48:18.144 1853,test 252, CONN_TEST_USER, 2014-06-06 09:48:18.140 1852,test 251, CONN_TEST_USER, 2014-06-06 09:48:18.137 1851,test 250, CONN_TEST_USER, 2014-06-06 09:48:18.132 1850,test 249, CONN_TEST_USER, 2014-06-06 09:48:18.127 1849,test 248, CONN_TEST_USER, 2014-06-06 09:48:18.123 1848,test 247, CONN_TEST_USER, 2014-06-06 09:48:18.119 1847,test 246, CONN_TEST_USER, 2014-06-06 09:48:18.088
sa_conn_info() shows that last connection (255) with ReqType='CONNECT_POOL_CACHE' and empty name.
SA client version: 12.0.1.4104.
SA server versions: 12.0.1.4104, 16.0.0.1691. But it works correctly with server version 11.0.1.x.
Visual Studio 2013 Professional, .Net framework 4.0, IIS7.
Besides, the problem can be reproduced only on compiled version and not running in Visual Studio environment.
Can anybody else reproduce this problem and is there any workaround available? Or maybe we are missing something?
Thanks in advance.
This bug should already be fixed in CR #743743 in 12.0.1.3941. Are you positive that you're picking up the correct assembly version for the client?
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
You mentioned:
Besides, the problem can be reproduced only on compiled version and not running in Visual Studio environment.
Is this perhaps because the compiled version is linked against an older version of the assembly and your development environment is picking up the correct version...?
How are you referencing the SQL Anywhere assembly in your ASP.NET project? Are you adding a direct reference to the assembly DLL file, or to a GAC reference, or are you using the web.config
file to specify a <connectionStrings>
element?
Direct reference to the DLL file is being used. And web.config file is being used to specify connection strings (although I've hardcoded it in the question above) but not in <connectionstrings> element but in something like this:
<configuration> <appsettings> <add key="ConnectString" value="ENG=...;DBN=...;LINKS=tcpip;POOLING=TRUE;Max Pool Size=100;Min Pool Size=0;PROWS=100;Idle=60;UID=web_user;PWD=sql"/> </appsettings> ... </configuration>
Try running Process Explorer to determine which version of the DLL is actually loaded at runtime. You might have to run it with elevated permissions.
Hi Arthoor,
I can reproduce this issue - you are right, while the connection is connected, the name appears but once it goes into the connection pool, the connection name disappears. I am going to investigate this behaviour further underneath CR #768516.
However, I'm finding that if I close the connection, it is re-used as expected from the connection pool. Are you seeing the same behaviour?
Jeff,
Are there any news about this problem? It is still very important as we can't migrate to newer versions of SA.
Hi Arthoor,
The issue is still open. However, like I replied originally, for myself, the actual pooling of the connection still works, up to the default maximum number of connections in the pool. How many connections are you seeing?
If you would like to expedite this issue, you should open an incident underneath your SAP Support Plan and explain why this bug is important to your migration and we can provide more detailed tracking information as the bug is examined.
This conforms to the question Connection pooling and connection parameters there the answer was yes the CON parameter counts for finding a reusable connection, so I would say it is a bug, that the connection name is emptied on close.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
66 | |
10 | |
10 | |
10 | |
10 | |
8 | |
6 | |
5 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.