Table ... doesn't exist
Table ... doesn't exist
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).
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).
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...
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...
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
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
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
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