Read fail (Devart.data.postgresql 4.95 with postgresql 8.4)

Read fail (Devart.data.postgresql 4.95 with postgresql 8.4)

Postby mg.ddev.mve » Wed 07 Jul 2010 13:52

Hi,

I'm testing the .Net connector Devart.data.postgresql 4.95 with postgresql server 8.4

-App server : OpenSUSE 11.2 64bits, Apache 2.2.13, Mono 2.4.3
-DB Server : Centos 5.5,Enterprisedb 8.4 Bêta Advanced Server


code :
bool InTransac = false;
PgSqlConnection _Connection = null;
IDbCommand command = null;
IDbTransaction _CurrentTransaction = null;
string sql = string.Empty;

StreamWriter log = null;

try
{
log = new StreamWriter("log.txt",false);

string _ConnectionString = "User ID=\"xxxx\";";
_ConnectionString += "Password=\"xxxx\";";
_ConnectionString += "Database=xxxx;";
_ConnectionString += "Server=xxx.xx.xx.xxx;";
_ConnectionString += "Port=xxxx;";

log.WriteLine("Open Connection");
_Connection = new PgSqlConnection(_ConnectionString);
_Connection.Charset = "UTF8";
_Connection.Open();

log.WriteLine("BeginTransaction");
_CurrentTransaction = _Connection.BeginTransaction();
InTransac = true;

log.WriteLine("Deleting");
sql = "DELETE FROM INDYNPARAM WHERE 1=0";
command = new PgSqlCommand(sql, _Connection);
command.ExecuteNonQuery();

log.WriteLine("Updating");
sql = "UPDATE INDYNPARAM SET numordre = numordre-1 WHERE 1=0";
command = new PgSqlCommand(sql, _Connection);
command.ExecuteNonQuery();

log.WriteLine("CommitTransaction");
_CurrentTransaction.Commit();
_CurrentTransaction.Dispose();
_CurrentTransaction = null;
InTransac = false;

log.WriteLine("Close Connection");
if (_Connection != null && _Connection.State != ConnectionState.Closed)
_Connection.Close();

log.WriteLine("FIN");
log.Close();
}
catch (Exception ex)
{
if (InTransac)
{
log.WriteLine("RollbackTransaction");
_CurrentTransaction.Rollback();
_CurrentTransaction.Dispose();
_CurrentTransaction = null;
log.WriteLine("Close Connection");
if (_Connection != null && _Connection.State != ConnectionState.Closed)
_Connection.Close();
}
log.WriteLine(ex.Message);
log.WriteLine(ex.StackTrace);
log.WriteLine("FIN");
log.Close();
}

These exceptions are thrown :

log#1
Open Connection
BeginTransaction
Read failure
at Devart.Data.PostgreSql.v.a (System.Exception A_0) [0x00000]
at Devart.Common.o.e (System.Byte[] A_0, Int32 A_1, Int32 A_2) [0x00000]
at Devart.Common.j.b (System.Byte[] A_0, Int32 A_1, Int32 A_2) [0x00000]
at Devart.Data.PostgreSql.o.d (System.Byte[] A_0, Int32 A_1, Int32 A_2) [0x00000]
at Devart.Data.PostgreSql.o.o () [0x00000]
at Devart.Data.PostgreSql.i.a (Boolean A_0, Boolean A_1, Char A_2, Boolean A_3) [0x00000]
at Devart.Data.PostgreSql.i.c (Boolean A_0, Boolean A_1) [0x00000]
at Devart.Data.PostgreSql.i.b (Boolean A_0) [0x00000]
at Devart.Data.PostgreSql.r.i () [0x00000]
at Devart.Data.PostgreSql.r.e (System.String A_0) [0x00000]
at Devart.Data.PostgreSql.r.c (Devart.Data.PostgreSql.ad A_0) [0x00000]
FIN


log#2 (other app serveur)
Open Connection
BeginTransaction
Transport channel is closed.
à Devart.Common.ab.d(Exception A_0)
à Devart.Data.PostgreSql.v.d(Exception A_0)
à Devart.Common.o.d(Byte[] A_0, Int32 A_1, Int32 A_2)
à Devart.Common.j.a(Byte[] A_0, Int32 A_1, Int32 A_2)
à Devart.Data.PostgreSql.o.a(Byte[] A_0, Int32 A_1, Int32 A_2)
à Devart.Data.PostgreSql.o.k()
à Devart.Data.PostgreSql.r.i()
à Devart.Data.PostgreSql.r.h(String A_0)
à Devart.Data.PostgreSql.r.c(ad A_0)
à Devart.Data.PostgreSql.ad.af()
à Devart.Data.PostgreSql.PgSqlCommand.InternalPrepare(Boolean implicitPrepare, Int32 startRecord, Int32 maxRecords)
à Devart.Common.DbCommandBase.ExecuteDbDataReader(CommandBehavior behavior, Boolean nonQuery)
à Devart.Common.DbCommandBase.ExecuteDbDataReader(CommandBehavior behavior)
à System.Data.Common.DbCommand.ExecuteReader()
à Devart.Common.DbCommandBase.ExecuteNonQuery()
à Devart.Data.PostgreSql.PgSqlTransaction..ctor(PgSqlConnection A_0, IsolationLevel A_1)
à Devart.Data.PostgreSql.ae.a(IsolationLevel A_0)
à Devart.Common.DbConnectionBase.BeginDbTransaction(IsolationLevel isolationLevel)
à System.Data.Common.DbConnection.BeginTransaction(IsolationLevel isolationLevel)
à Devart.Data.PostgreSql.PgSqlConnection.BeginTransaction(IsolationLevel il)
à Devart.Data.PostgreSql.PgSqlConnection.BeginTransaction()
à TestsDevartPosgresql.Program.Main(String[] args)
FIN


Do you reproduce these exceptions ? Is there any Workaround possible ?


Thanks
mg.ddev.mve
 
Posts: 9
Joined: Mon 11 Jan 2010 09:46

Postby StanislavK » Thu 08 Jul 2010 10:56

Apparently, the problem occurs during starting the transaction. The most probable reason of the issue is that there are some problems with connection to PostgreSQL server. Could you please check under debug whether the connection is opened when you are trying to begin the transaction? Also, are you able to perform any simple select query with this connection (without starting a transaction)?

The sample works properly in our environment.
StanislavK
Devart Team
 
Posts: 1710
Joined: Thu 03 Dec 2009 10:48

Postby mg.ddev.mve » Mon 26 Jul 2010 14:58

Hi,

(db server EnterpriseDB Advanced Server 8.4 )

I did another test, apparently, the problem occurs during opening the connection, I'm getting this message :

Open Connection
Read less bytes than expected.
à Devart.Data.PostgreSql.ae.ab()
à Devart.Data.PostgreSql.ae.a()
à Devart.Common.DbConnectionFactory.a(DbConnectionBase A_0)
à Devart.Common.DbConnectionClosed.Open(DbConnectionBase outerConnection)
à Devart.Common.DbConnectionBase.Open()
à Devart.Data.PostgreSql.PgSqlConnection.Open()
à TestsDevartPosgresql.Program.Main(String[] args)
FIN

code :
bool InTransac = false;
PgSqlConnection _Connection = null;
IDbCommand command = null;
IDbTransaction _CurrentTransaction = null;
string sql = string.Empty;

StreamWriter log = null;

try
{
log = new StreamWriter("log.txt",false);

string _ConnectionString = "User ID=\"****\";";
_ConnectionString += "Password=\"****\";";
_ConnectionString += "Database=****;";
_ConnectionString += "Server=****;";
_ConnectionString += "Port=****;";

log.WriteLine("Open Connection");
_Connection = new PgSqlConnection(_ConnectionString);
_Connection.Charset = "UTF8";
_Connection.Open();

log.WriteLine("Connection state :" + _Connection.State.ToString());

log.WriteLine("Deleting");
sql = "DELETE FROM INDYNPARAM WHERE 1=0";
command = new PgSqlCommand(sql, _Connection);
command.ExecuteNonQuery();

log.WriteLine("Connection state :" + _Connection.State.ToString());

log.WriteLine("Updating");
sql = "UPDATE INDYNPARAM SET numordre = numordre-1 WHERE 1=0";
command = new PgSqlCommand(sql, _Connection);
command.ExecuteNonQuery();

log.WriteLine("Connection state :" + _Connection.State.ToString());

log.WriteLine("BeginTransaction");
_CurrentTransaction = _Connection.BeginTransaction();
InTransac = true;

log.WriteLine("Connection state :" + _Connection.State.ToString());

log.WriteLine("Deleting");
sql = "DELETE FROM INDYNPARAM WHERE 1=0";
command = new PgSqlCommand(sql, _Connection);
command.ExecuteNonQuery();

log.WriteLine("Connection state :" + _Connection.State.ToString());

log.WriteLine("Updating");
sql = "UPDATE INDYNPARAM SET numordre = numordre-1 WHERE 1=0";
command = new PgSqlCommand(sql, _Connection);
command.ExecuteNonQuery();

log.WriteLine("Connection state :" + _Connection.State.ToString());

log.WriteLine("CommitTransaction");
_CurrentTransaction.Commit();
_CurrentTransaction.Dispose();
_CurrentTransaction = null;
InTransac = false;

log.WriteLine("Connection state :" + _Connection.State.ToString());

log.WriteLine("Close Connection");
if (_Connection != null && _Connection.State != ConnectionState.Closed)
_Connection.Close();

log.WriteLine("FIN");
log.Close();
}
catch (Exception ex)
{
if (InTransac)
{
log.WriteLine("RollbackTransaction");
_CurrentTransaction.Rollback();
_CurrentTransaction.Dispose();
_CurrentTransaction = null;
log.WriteLine("Close Connection");
if (_Connection != null && _Connection.State != ConnectionState.Closed)
_Connection.Close();
}
log.WriteLine(ex.Message);
log.WriteLine(ex.StackTrace);
log.WriteLine("FIN");
log.Close();
}

Thanks for your help
mg.ddev.mve
 
Posts: 9
Joined: Mon 11 Jan 2010 09:46

Postby mg.ddev.mve » Tue 27 Jul 2010 09:26

Hi again,

It seems that "Read less bytes than expected" error was due to security problem on pgbouncer

new log after changes on pgbouncer security :

Open Connection
Connection state :Open
Deleting
Transport channel is closed.
à Devart.Common.ab.d(Exception A_0)
à Devart.Data.PostgreSql.v.d(Exception A_0)
à Devart.Common.o.d(Byte[] A_0, Int32 A_1, Int32 A_2)
à Devart.Common.j.a(Byte[] A_0, Int32 A_1, Int32 A_2)
à Devart.Data.PostgreSql.o.a(Byte[] A_0, Int32 A_1, Int32 A_2)
à Devart.Data.PostgreSql.o.k()
à Devart.Data.PostgreSql.r.i()
à Devart.Data.PostgreSql.r.h(String A_0)
à Devart.Data.PostgreSql.r.c(ad A_0)
à Devart.Data.PostgreSql.ad.af()
à Devart.Data.PostgreSql.PgSqlCommand.InternalPrepare(Boolean implicitPrepare, Int32 startRecord, Int32 maxRecords)
à Devart.Common.DbCommandBase.ExecuteDbDataReader(CommandBehavior behavior, Boolean nonQuery)
à Devart.Common.DbCommandBase.ExecuteDbDataReader(CommandBehavior behavior)
à System.Data.Common.DbCommand.ExecuteReader()
à Devart.Common.DbCommandBase.ExecuteNonQuery()
à TestsDevartPosgresql.Program.Main(String[] args)
FIN

Thanks
mg.ddev.mve
 
Posts: 9
Joined: Mon 11 Jan 2010 09:46

Postby StanislavK » Wed 28 Jul 2010 09:26

Could you please check the following:
1) if the same delete command can be executed properly with a standard tool like PgAdmin;
2) if you are getting the same error when executing some simple select statement.

Also, please try setting the UnpreparedExecute property of your PgSqlCommand object to true before executing it and notify us if this helps.

We cannot reproduce the problem in our environment, the above information can help our investigation greatly.
StanislavK
Devart Team
 
Posts: 1710
Joined: Thu 03 Dec 2009 10:48

Postby mg.ddev.mve » Wed 28 Jul 2010 14:25

Hi,

1) the same command works fine with Pgadmin or when i'm using EDBDataProvider

2) Select statement works fine

3) I can't set UnpreparedExecute property of this command, the exception is thrown before, on _Connection.BeginTransaction()

new log (after replacing first delete and update command with select command):
Open Connection
Connection state :Open
Select (ExecuteNonQuery)
Connection state :Open
Select (ExecuteReader)
Connection state :Open
BeginTransaction
Transport channel is closed.
à Devart.Data.PostgreSql.v.d(Exception A_0)
à Devart.Common.o.d(Byte[] A_0, Int32 A_1, Int32 A_2)
à Devart.Data.PostgreSql.o.a(Byte[] A_0, Int32 A_1, Int32 A_2)
à Devart.Data.PostgreSql.o.k()
à Devart.Data.PostgreSql.r.i()
à Devart.Data.PostgreSql.r.c(ad A_0)
à Devart.Data.PostgreSql.ad.af()
à Devart.Data.PostgreSql.PgSqlCommand.InternalPrepare(Boolean implicitPrepare, Int32 startRecord, Int32 maxRecords)
à Devart.Common.DbCommandBase.ExecuteDbDataReader(CommandBehavior behavior, Boolean nonQuery)
à Devart.Common.DbCommandBase.ExecuteNonQuery()
à Devart.Data.PostgreSql.PgSqlTransaction..ctor(PgSqlConnection A_0, IsolationLevel A_1)
à Devart.Data.PostgreSql.ae.a(IsolationLevel A_0)
à Devart.Data.PostgreSql.PgSqlConnection.BeginTransaction(IsolationLevel il)
à TestsDevartPosgresql.Program.Main(String[] args)
FIN
mg.ddev.mve
 
Posts: 9
Joined: Mon 11 Jan 2010 09:46

Postby mg.ddev.mve » Thu 29 Jul 2010 09:25

Hi again

3) I tried to set UpreparedExecute property on delete command without transaction, it works

log.WriteLine("Connection state :" + _Connection.State.ToString());
sql = "DELETE FROM INDYNPARAM WHERE 1=0";
command = new PgSqlCommand(sql, _Connection);
((PgSqlCommand)command).UnpreparedExecute = true;
command.ExecuteNonQuery();
log.WriteLine("Connection state :" + _Connection.State.ToString());

=>
Connection state :Open
Deleting ExecuteNonQuery() UnpreparedExecute = true
Connection state :Open
mg.ddev.mve
 
Posts: 9
Joined: Mon 11 Jan 2010 09:46

Postby StanislavK » Mon 02 Aug 2010 16:19

In general, we recommend using the 2.0 protocol or the UnpreparedExecute mode under Mono. The protocol may be set using the 'Protocol' property of connection string.

Please tell us if using the 2.0 protocol helps.
StanislavK
Devart Team
 
Posts: 1710
Joined: Thu 03 Dec 2009 10:48


Return to dotConnect for PostgreSQL