Devart Forums

Since we upgraded to version 3.50 of the MySQL Direct driver, we occasionally get the exception below (2-3 times a week). The table it is complaining about is there (and it always complains about the same table [so far]). We are using VS 2005 and MySQL 5.0.22. If I simply re-execute the same line that gave the exception, it runs fine. I do not know how to duplicate it. It did not happen with the previous version of MySQLDirect.

CoreLab.MySql.MySqlException occurred
Message="Table 'Global.Item' doesn't exist"
Source="CoreLab.MySql"
ErrorCode=-2147467259
Code=1146
SqlState="42S02"
StackTrace:
at CoreLab.MySql.a2.n()
at CoreLab.MySql.a2.c()
at CoreLab.MySql.c.a(f[]& A_0, Int32& A_1)
at CoreLab.MySql.c.a(Byte[] A_0, Int32 A_1, Boolean A_2)
at CoreLab.MySql.w.d()
at CoreLab.MySql.MySqlCommand.a(CommandBehavior A_0, IDisposable A_1, Int32 A_2, Int32 A_3)
at CoreLab.Common.DbCommandBase.b(CommandBehavior A_0)
at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior)
at Samys.DataAccessLayer.DAL.ExecuteReader(String sqlString) in G:\devel\dotNet\Samys\DataAccessLayer\DAL.cs:line 119

The line giving the exception is:
return command.ExecuteReader(System.Data.CommandBehavior.CloseConnection);

Please advise.

Thanks,
Lars

Ps. The only write-operation that ever takes place against the table mentioned in the exception is the code below (pseudo code), and it only runs at once a day (at 5.30 am in the morning):

LOCK TABLES $Destination_Table WRITE
DROP TABLE IF EXISTS `$Destination_Table`
ALTER TABLE `$Source_Table` RENAME `$Destination_Table`
UNLOCK TABLES

Even if an application tried to read from the table at the same time as this happened, it should only result in a very brief delay (not an exception).

Didn't you migrate from one database to another? Or changed some MySQL settings?

No, I did not do any MySQL changes / DB changes since we upgraded to the new version.

Lars

Please provide us with your code snippets which may have something to do with the problem.

See below (but I do not know that it would be very helpful):

public IDataReader ExecuteReader(string sqlString)
{
IDbCommand command = _providerFactory.CreateCommand(sqlString, this.Connection);
try
{
if (_connection.State == ConnectionState.Closed)
_connection.Open();

return command.ExecuteReader(System.Data.CommandBehavior.CloseConnection);
}
catch( Exception e )
{
throw new DALException("ExecuteReader failed. SQL: \n" + sqlString, e);
}
}

The line that fails is the command.ExecuteReader...

This code doesn't help much. What is your sqlString?
We think that this problem might be absent in newer version of MySQL Server. Could you try to upgrade?
Is it possible to check this out with another data provider?

sqlString is:

SELECT ItemNo, ItemDescr, ItemDescr2, Cat, SubCat, Mfg, ItmStat, OrdStat, Vendor, AvgCst, LstCst FROM Item WHERE ItemNo = 'SANDSDDR92A15'

I could update MySQL, but realize that this version of MySQL was working just fine before upgrading MySQL direct to version 3.50 (but with the older version, we had the intermittent problem with "object pushed second time").

The application is in production in a 24x7 mission critical environment, so I am not sure how soon I can update the MySQL server version. What version of MySQL would you recommend?

Thanks,
Lars

As seen from the StackTrace, this problem has nothing to do with a data provider you use to connect to the database. We think it will occur with any other client tool as well.
Refer to MySQL AB recommendations to make a decision about server upgrade.

Alexy,

I have found the problem. It turned out to be in this code (that I posted in my original post as well):
LOCK TABLES $Destination_Table WRITE
DROP TABLE IF EXISTS `$Destination_Table`
ALTER TABLE `$Source_Table` RENAME `$Destination_Table`
UNLOCK TABLES

The problem is obvious (in retrospect): The lock gets released with the DROP TABLE, so anyone trying to access the table before the ALTER TABLE statement has executed will not find a table there.

The above statements are in a function called Replace_Table($Source_Table, $Destination_Table), and is used for reloading data in production tables on-hours: I create a temp-table and load the new data in there. After this, I call Replace_Table to switch over to the new data (=make the temp table the production table).

So this was not a problem with the driver, as you already told me.

I have re-written the above code so that I just truncate the table instead of dropping it and then copy the records over from the temp table. This is more expensive in terms of server load and time, so I am not thrilled with it. If anyone has a better approach, please let me know.

Thanks,
Lars

It's good that you have solved the problem.