MySQL Bugs: #31295: NULL alter table operation causes falcon table to lose it's tablespace assoc.

Bug #31295	NULL alter table operation causes falcon table to lose it's tablespace assoc.
Submitted:	29 Sep 2007 20:22	Modified:	2 Nov 2007 11:53
Reporter:	Matthew Montgomery
Status:	Can't repeat
Category:	Server: Falcon	Severity:	S2 (Serious)
Version:	6.0.2-alpha-log	OS:	Any
Assigned to:	Sergey Vojtovich	Target Version:

Description:
ALTER TABLE `tbl_name` engine = <same engine> is a widely used method for reclaiming space
from a fragmented .MYD or .ibd datafile.  Doing this operation against a falcon table
within a tablespace causes the table to revert to using the main tablespace.

How to repeat:
mysql> create tablespace falcon_ts1 add datafile 'falcon_ts1.fts' engine=falcon;
Query OK, 0 rows affected (0.01 sec)

mysql> alter table t1 tablespace falcon_ts1 engine=falcon; 
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> select * from information_schema.falcon_tables;
+-------------+------------+------------+
| SCHEMA_NAME | TABLE_NAME | TABLESPACE |
+-------------+------------+------------+
| TEST        | T1         | FALCON_TS1 |
+-------------+------------+------------+
1 row in set (0.00 sec)

mysql> alter table t1 engine=falcon;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> select * from information_schema.falcon_tables;
+-------------+------------+-------------+
| SCHEMA_NAME | TABLE_NAME | TABLESPACE  |
+-------------+------------+-------------+
| TEST        | T1         | FALCON_USER |
+-------------+------------+-------------+
1 row in set (0.00 sec)

Suggested fix:
Do not change tablespace unless explicitly told to do so.

Thank you for a bug report. Verified just as described.

Sergey, Please also add this to your list of tablespace issues.  If Falcon can determine
the existing tablespace of a new file created by an alter table, then maybe it could
automatically create that tablespace for the file.

Was not able to repeat a problem with latest sources:
create table t1(a INT);
create tablespace falcon_ts1 add datafile 'falcon_ts1.fts' engine=falcon;
alter table t1 tablespace falcon_ts1 engine=falcon;
select * from information_schema.falcon_tables;
SCHEMA_NAME     TABLE_NAME      TABLESPACE
TEST    T1      FALCON_TS1
alter table t1 engine=falcon;
select * from information_schema.falcon_tables;
SCHEMA_NAME     TABLE_NAME      TABLESPACE
TEST    T1      FALCON_TS1
show create table t1;
Table   Create Table
t1      CREATE TABLE `t1` (
  `a` int(11) DEFAULT NULL
) /*!50100 TABLESPACE falcon_ts1 */ ENGINE=Falcon DEFAULT CHARSET=latin1

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/36974

ChangeSet@1.2657, 2007-11-02 14:13:47+04:00, svoj@mysql.com +2 -0
  BUG#31295 - NULL alter table operation causes falcon table to lose it's
              tablespace assoc.
  
  Added a test case to document that the problem is not repeatable
  anymore.

Pushed into 6.0.4-alpha

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/49977

2752 Vladislav Vaintroub	2008-07-17
      Bug#31295 : CREATE TABLESPACE fails sometimes if called directly after 
      DROP TABLESPACE on the same file
      
      The problem is that tablespace files are not deleted directly on client COMMIT.
      Deletion is done later, by background(Gopher) thread. Thus the file may still 
      exist when "create tablespace" is called.
      
      The solution is to to modify CREATE TABLESPACE and if the file is there and 
      there are pending  DROP operation, wait for some seconds until the file is deleted.
      
      An attempt to solve this problem was done previously, but waiting for deletion 
      was put on the wrong place. Client thread was waiting for gopher in 
      TablespaceManager::dropTablespace, which  can caused  a "soft-deadlock" :
      - Client thread was holding syncSysDDL lock and was waiting for Gopher 
        to delete file
      - Gopher did not run, was stuck in thread->sleep and waited for Scheduler 
        to wakeup
      - Scheduler was doing scavenge and was stuck waiting for syncSysDDL lock 
        in Database:updateCardinalities()
      
      This soft-deadlock was resolved after 10 seconds, client gave up and 
      returned with "tablespace file already exists".
      
      The refinement to this solution is given here . Client waits for 
      file to be deleted in StorageHandler::createTablespace while no Falcon locks 
      are held. Thus the soft-deadlock described is not possible with this solution

Please disregard the previous commit message, commit fixed a different problem problem.