Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: C# .NET MySQL
public void enqueue(object e)
{
    try
    {
        DataChangeEventArgs e113 = (DataChangeEventArgs)e;
        resetdatasource();
        for (i=0; i < e113.sts.Length; i++)
        {
            int hour113 = e113.sts[i].TimeStampNet.Hour;
            int minute113 = e113.sts[i].TimeStampNet.Minute;
            int second113 = e113.sts[i].TimeStampNet.Second;
            int millisecond113 = e113.sts[i].TimeStampNet.Millisecond;
            int year113 = e113.sts[i].TimeStampNet.Year;
            int month113 = e113.sts[i].TimeStampNet.Month;
            int day113 = e113.sts[i].TimeStampNet.Day;
            DateTime sdate113 = new DateTime(year113, month113, day113, hour113, minute113, second113, millisecond113);
 
            DataRow row1 = dt.NewRow();
            row1["itemID"] = e113.sts[i].HandleClient;
            row1["paramvalue"] = convert.ToString(e113.sts[i].DataValue);
            row1["date_logged1"] = sdate113.ToString("dd-MM-yyyy HH:mm:ss.fff");
            row1["Quality"] = e113.sts[i].Quality;
            row1["date_logged"] = DateTime.FromFileTime(e113.sts[i].TimeStamp);
            dt.Rows.Add(row1);
        }
        var threadupdate = new Thread(update);//start new thread & instance
        threadupdate.Start(dt);
    }
    catch { }
}
 
public void update(object dtnew)
        {
            try
            {
                   MySqlConnection con = new MySqlConnection(LocalConnection.GetLocalConnetionStringmysql());
                    DataSet oldvalueds=new DataSet();
                    DataTable newupdateddata = new DataTable();
                    MySqlDataAdapter da;
                
                    
                    MySqlTransaction trans;
                    if (con.State == ConnectionState.Closed)
                        con.Open();//throws exception
                    trans = con.BeginTransaction();
                    da = new MySqlDataAdapter();
                    da.InsertCommand = new MySqlCommand("INSERT INTO parameter (itemID,paramvalue, date_logged1,Quality,date_logged) " +
                                           "VALUES (@itemID, @paramvalue,@date_logged1,@Quality,@date_logged)" +
                                           "ON DUPLICATE KEY UPDATE " +
                                           "itemID=VALUES(itemID),paramvalue=VALUES(paramvalue),date_logged1=VALUES(date_logged1),Quality=VALUES(Quality),date_logged=VALUES(date_logged)", con);
 
                    da.InsertCommand.Parameters.Add("@itemID", MySqlDbType.VarChar, 250, "itemID");
                    da.InsertCommand.Parameters.Add("@paramvalue", MySqlDbType.VarChar, 250, "paramvalue");
                    da.InsertCommand.Parameters.Add("@date_logged1", MySqlDbType.VarChar, 250, "date_logged1");
                    da.InsertCommand.Parameters.Add("@Quality", MySqlDbType.VarChar, 250, "Quality");
                    da.InsertCommand.Parameters.Add("@date_logged", MySqlDbType.VarChar, 250, "date_logged");
                  
                   da.InsertCommand.Transaction = trans;
                     DataTable newupdateddt = (DataTable)dtnew;
                  int k = da.Update(newupdateddt);//exception thrown 2nd
                       trans.Commit();
                       con.Close();
 
              }
        
            catch(Exception ex) {
             
               }
            finally
            {
 
              
            }
        }
 
It will be thrown an exception like "Error connecting: Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool. This may have occurred because all pooled connections were in use and max pool size was reached."
 
I will be set the timeout=200 in connection string this exception was thrown
"Deadlock found when trying to get lock; try restarting transaction"
 
Thanks in advance
Posted 30-May-11 3:02am
Edited 30-May-11 4:00am
Kim Togo26.1K
v5
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 4

Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool
 
This error may also cause because u may not have saved ur database at proper location.
 
I think firstly set timeout to Default. Then save your database at proper location.Close ur database and then try to check ur application for Update query.This may solve ur timeout problem
 
Regards
Amul V V Wayangankar
  Permalink  
v2
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

please check your database connection and set proper timeout... I think it is set to default...
  Permalink  
Comments
sangita navale at 30-May-11 8:11am
   
please see my updates
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 2

Try to disable Connection pool and see how your code is working.
 
Pooling=false
 
Perhaps change Connection Lifetime or Max Pool Size
 
See link from MySql about connection string that is valid. http://dev.mysql.com/doc/refman/5.5/en/connector-net-connection-options.html[^]
  Permalink  
v2
Comments
sangita navale at 31-May-11 0:30am
   
when i will be set pooling in connection string it will be throws exception "Input string was not in a correct format."
Kim Togo at 31-May-11 2:19am
   
Strange. Can you post the connection string you are using?
Kim Togo at 31-May-11 2:23am
   
What version of MySql Connector/Net are you using ?
sangita navale at 31-May-11 2:29am
   
in connection string i will be set the pool size & then set the pooling=false ,this exception solved but new exception is coming at
int k = da.Update(newupdateddt);this line "too many connections"
Kim Togo at 31-May-11 2:43am
   
Okay, then I think that the problems is at the server end. In MySql you can limit the maximum connection.
But then, maybe you should rethink your design. Right now your flow is:
 
Connect -> Begin Transaction -> Update -> End Transaction -> Close
 
And if you do this many many time very fast, then you create a huge amount of connection to MySql.
 
On MySql server, try run a "SHOW PROCESSLIST" then you can see how many connections you have.
sangita navale at 31-May-11 3:06am
   
i will be check the list 101 connections are in the list
sangita navale at 31-May-11 3:14am
   
"Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding"
solution on tis exception actually when the connection beyond the 101 connection it will be thrown exception too many connection . but when i will be create the new instance of the function of update using the thread then when the updates function finished the work it will be release the connection then why this problem is coming
sangita navale at 31-May-11 3:19am
   
when i will be check for one connection it will be add the two process connection in process list why this will be happed.
sangita navale at 31-May-11 3:39am
   
please help me
Kim Togo at 31-May-11 3:41am
   
What version of MySql Connector/Net are you using ?
sangita navale at 31-May-11 3:50am
   
version 5.2.2.0
Kim Togo at 31-May-11 4:01am
   
That is an old driver. The latest version is version 6.3.6 - http://dev.mysql.com/downloads/connector/net/
 
And I know there has been bugfixs about connection pool not releasing connection to mysql etc..
If I where you, I would upgrade to version 6.3.6.
sangita navale at 31-May-11 5:32am
   
i will be download the version 6.3.6 but it will be display the same exception
string ConnectionString = "Server=localhost;" +
"UID=root;" +
"DataBase=globasys;" +
"Allow Zero Datetime = false;" +
"Convert Zero Datetime=true;" +
"Pooling=false;";
my connection string given above
sangita navale at 31-May-11 5:52am
   
any suggestion about this when i will be delete the pooling=false then it will be shown another exception as "error connecting: Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool. This may have occurred because all pooled connections were in use and max pool size was reached." it will be increased the size of the processlist
Kim Togo at 31-May-11 5:54am
   
Try to set Connection Lifetime=2
It seams that connections to MySql is never closed and you hit the maximum connection allowed by the MySql.
sangita navale at 31-May-11 6:48am
   
i will be set the Max_connections =200 & command timeout =90
it will be take the process list greater than 101 but after some time it will be show another exception as "lock wait timeout exceeded try restarting transaction"
sangita navale at 31-May-11 6:57am
   
how to set the innodb_lock_wait_timeout in MYSQL
Kim Togo at 31-May-11 6:59am
   
Is it necessary to start a transaction ? You only have 1 SQL statement.
Kim Togo at 31-May-11 7:04am
   
How to change innodb_lock_wait_timeout
http://dev.mysql.com/doc/refman/5.0/en/innodb-parameters.html#sysvar_innodb_lock_wait_timeout
sangita navale at 31-May-11 7:01am
   
the all process will be execute at every 1 seconds
Kim Togo at 31-May-11 7:13am
   
Try remove transaction.
sangita navale at 31-May-11 7:56am
   
i will be use this transaction for improve the performance of the int k = da.Update(newupdateddt); function because when the rows are increased into the datatable then it takes lots of time to update the table using the transaction it will be update the table within millisecond so i want speedup the performance also
Kim Togo at 31-May-11 8:13am
   
I think the main problem is, that you have to many connection to MySql and resource is not free up again.
 
Perhaps a better design is to queue all SQL statements in a ConcurrentQueue<T>, and have 1 connection that takes from that queue and execute the SQL statements.
sangita navale at 31-May-11 8:14am
   
can please tell how to improve the performance of the dataadapter.update() without transaction
sangita navale at 31-May-11 8:16am
   
but it will be takes lots of time to dequeue i wants to update the at every 250 millisecond upto the 128000 rows
Kim Togo at 31-May-11 9:08am
   
That is many update :-)
It still think you have to redesign how you what to update the database.
 
If it was me, I will make 1 thread that has 1 connection to MySql. And when there is data in the ConcurrentQueue≶T>, then dequeue it and update database.
 
The one that feeds the ConcurrentQueue≶T> only has to enqueue a SQL statement.
sangita navale at 31-May-11 8:22am
   
it will not update the #innodb_lock_wait_timeout = 500
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 3

  Permalink  

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
0 OriginalGriff 240
1 Kamal Rocks 184
2 CPallini 155
3 PIEBALDconsult 150
4 BillWoodruff 148
0 OriginalGriff 5,695
1 DamithSL 4,506
2 Maciej Los 4,007
3 Kornfeld Eliyahu Peter 3,480
4 Sergey Alexandrovich Kryukov 3,180


Advertise | Privacy | Mobile
Web03 | 2.8.141216.1 | Last Updated 31 May 2011
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100