Bug #31804 drop table doesn't drop auto_increment data in cluster
Submitted: 24 Oct 2007 2:03 Modified: 26 Oct 2007 17:00
Reporter: Monty Taylor Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Cluster: Cluster (NDB) storage engine Severity:S3 (Non-critical)
Version:mysql-5.1 OS:Any
Assigned to: CPU Architecture:Any
Tags: 5.1.22
Triage: Triaged: D2 (Serious)

[24 Oct 2007 2:03] Monty Taylor
Description:
Droppping an ndb table does not get rid of auto_increment data. When the table is dropped and recreated without an autoincrement column, the autoincrement column for that table can still be accessed by the NDB API. 

When the database is dropped, the auto_increment does go away. 

How to repeat:
first - 

drop database TEST_DB_1; 
create database TEST_DB_1; 

 CREATE TABLE `MYTABLENAME` (
  `ATTR1` int(10) unsigned NOT NULL DEFAULT '0',
  `ATTR2` int(10) unsigned NOT NULL,
  PRIMARY KEY (`ATTR1`)
) ENGINE=ndbcluster ; 

run the following snippet. You should get an error. This is expected. 

Then:

drop table MYTABLENAME;

 CREATE TABLE `MYTABLENAME` (
  `ATTR1` int(10) unsigned NOT NULL auto_increment,
  `ATTR2` int(10) unsigned NOT NULL,
  PRIMARY KEY (`ATTR1`)
) ENGINE=ndbcluster ; 

run the snippet. It should run successfully. This is expected. 

Then: 

drop table MYTABLENAME;

 CREATE TABLE `MYTABLENAME` (
  `ATTR1` int(10) unsigned NOT NULL DEFAULT '0',
  `ATTR2` int(10) unsigned NOT NULL,
  PRIMARY KEY (`ATTR1`)
) ENGINE=ndbcluster ; 

run the snippet. It will run successfully. This is the error. 

Then: 

drop database TEST_DB_1;

 CREATE TABLE `MYTABLENAME` (
  `ATTR1` int(10) unsigned NOT NULL DEFAULT '0',
  `ATTR2` int(10) unsigned NOT NULL,
  PRIMARY KEY (`ATTR1`)
) ENGINE=ndbcluster ; 

run the snippet. It will error. This is correct. 

  /**************************************************************
   * Connect to ndb cluster                                     *
   **************************************************************/

  Ndb_cluster_connection *cluster_connection=
    new Ndb_cluster_connection(); // Object representing the cluster

  if (cluster_connection->connect(5,3,1))
  {
    std::cout << "Connect to cluster management server failed.\n";
    exit(-1);
  }

  if (cluster_connection->wait_until_ready(30,30))
  {
    std::cout << "Cluster was not ready within 30 secs.\n";
    exit(-1);
  }

  Ndb* myNdb = new Ndb( cluster_connection,
			"TEST_DB_1" );  // Object representing the database
  if (myNdb->init() == -1) { 
    APIERROR(myNdb->getNdbError());
    exit(-1);
  }

  const NdbDictionary::Dictionary* myDict= myNdb->getDictionary();
  const NdbDictionary::Table *myTable= myDict->getTable("MYTABLENAME");
  
  if (myTable == NULL)
    APIERROR(myDict->getNdbError());

  /**************************************************************************
   * Fill table with tuples, using auto_increment IDs                       *
   **************************************************************************/

  {
    std::time_t before_t = std::time(0);
    for (int i = 0; i < INSERT_NUM; i++) {
      NdbTransaction *myTransaction= myNdb->startTransaction();
      if (myTransaction == NULL) APIERROR(myNdb->getNdbError());
      
      NdbOperation *myOperation= myTransaction->getNdbOperation(myTable);
      if (myOperation == NULL) APIERROR(myTransaction->getNdbError());
      
      myOperation->insertTuple();
      

      if (myNdb->initAutoIncrement() == -1)
	APIERROR(myNdb->getNdbError());
     
      Uint64 auto_id = 0; 
      if (myNdb->getAutoIncrementValue(myTable,auto_id,0) == -1)
	APIERROR(myNdb->getNdbError());
        
      if (myOperation->equal("ATTR1",(int)auto_id)==-1)
   APIERROR(myTransaction->getNdbError());

      if (myOperation->setValue("ATTR2", i)==-1)
   APIERROR(myTransaction->getNdbError());
      
      if (myTransaction->execute( NdbTransaction::Commit ) == -1)
	APIERROR(myTransaction->getNdbError());
      
      myNdb->closeTransaction(myTransaction);
    }
    
    std::time_t after_t = std::time(0);
    std::cout << "Insert time for " << INSERT_NUM << " "; 
    std::cout << after_t << " -- " << before_t;
    std::cout << " = " << after_t - before_t << std::endl;
   
  }

Suggested fix:
Drop the auto_increment information when a table is dropped, otherwise the cluster metadata will be inconsistent with expectations.
[24 Oct 2007 9:32] Hartmut Holzgraefe
I verified that the information persists after dropping the table,
but could not verify that a DROP DATABASE purges it ...

Could it be that it bailed out for you as you didn't recreate
the database after droping it (at least this is missing from your
"how to reproduce" instructions above)? So that the test program 
bailed out in the table lookup stage already?

For me the program only fails once after a fresh cluster restart,
from there on the auto_increment information for TEST_DB_1.MYTABLENAME
persists forever
[24 Oct 2007 9:44] Hartmut Holzgraefe
test program and script, start fresh cluster, do "configure --with-mysql=/where/you/installed; make test"

Attachment: bug31804-0.1.tar.gz (application/x-gunzip, text), 300.09 KiB.