MySQL Bugs: #10882: server crashes on "ALTER TABLE ADD" in transaction

Bug #10882	server crashes on "ALTER TABLE ADD" in transaction
Submitted:	26 May 2005 13:45	Modified:	26 May 2005 15:50
Reporter:	[ name withheld ]	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S1 (Critical)
Version:	3.23	OS:	Windows (XP PRO)
Assigned to:		CPU Architecture:	Any

Description:
Hi,

I'm developing in .NET, and using the MySQL.data library to transact.

To dynamically manage a language table in my database I add fields to a 'languageData' table and update fields values in the same transaction (see C# code below). 

The procedure worked fine at the beginning , but after having used it a few times an error occured that led the server to hang indefinetely, or dramatically slow down. Since then, this happens even when altering a field in a table from my data base, and I have no other choice but to stop and restart the server.

I should upload the application and db to a webhost soon, and I'm afraid that this happens "live", as this could mean lots of troubles.

1st question : can I use ALTER TABLE in transactions?
Is this a known bug/issue? 
If so, is it fixed in earlier releases?

Thanks for the great work...

Best regards,
hervé mayou,

How to repeat:
using MySql.Data.MySqlClient;
using MySql.Data.Types;

public static void addReferenceData(LanguageData[] langData){

 MySqlConnection myConn = MySQLDbManager.MySqlConn;
 myConn.Open();
			
 MySqlCommand myComm = myConn.CreateCommand();
 MySqlTransaction myTrans;

 //transaction.start
 myTrans = myConn.BeginTransaction(IsolationLevel.Serializable);
 myComm.Connection = myConn;
 myComm.Transaction = myTrans;

 myComm.CommandType = CommandType.Text;
 try{
  foreach( LanguageData lgData in langData ){
  myComm.CommandText = lgData.addReferenceCommand;
  // the command text generated is something like:
  // ALTER TABLE languageData ADD refPleaseEnter VARCHAR(20)	
 
  myComm.ExecuteNonQuery();
  myComm.CommandText = lgData.addDataCommand;
  // the command text generated is an update command, updating the
  // refPleaseEnter field just created, for example
  
  myComm.ExecuteNonQuery();
  }
 //	commit transaction
 myTrans.Commit();
 myConn.Close();
 }
 catch(Exception ex){
 myTrans.Rollback();
 throw ex;
 }
}

Hi!

What does SHOW INNODB STATUS say during the ALTER? Please post a few samples.

Could it be that you have not tuned my.cnf or my.ini, and the operation is simply taking long?

Does mysqld print anything to the .err log in the datadir?

You can, of course, do an ALTER in a transaction, but note that it commits the old transaction, and is run in a transaction of its own.

Regards,

Heikki

This is what mysql mysql-_max-nt prints to the err. file, in the data directory:

050526 16:31:59  InnoDB: Warning: MySQL is trying to drop table blunch/#sql2-d88-17d
InnoDB: though there are still open handles to it.
InnoDB: Adding the table to the background drop queue.
050526 16:41:45  InnoDB: Dropped table blunch/#sql2-d88-17d in background drop queue.
050526 16:45:13  InnoDB: Warning: MySQL is trying to drop table blunch/#sql2-d88-187
InnoDB: though there are still open handles to it.
InnoDB: Adding the table to the background drop queue.
MySql: ready for connections

This bug has probably been fixed in 4.0.

Regards,

Heikki