Bug #32501 SHOW CREATE TABLE omits TABLESPACE

• Submitted: 19 Nov 2007 20:25
• Modified: 22 Nov 2007 9:20
• Reporter: Peter Laursen (Basic Quality Contributor)
• Status: Can't repeat
• Category: MySQL Server: DDL
• Severity: S2 (Serious)
• Version: 6.0
• OS: Any
• Assigned to: MySQL Verification Team
• CPU Architecture: Any

[19 Nov 2007 20:25] Peter Laursen

Description:
SHOW CREATE TABLE omits TABLESPACE

How to repeat:
drop database test1;

create tablespace peterspc add datafile 'peterspc' engine = falcon;

create database test1 ; 
use test1;
create table newtab (id integer) tablespace peterspc engine = falcon;

use test1;
show create table newtab;

/*

-- server 6.0

drop database test1;
drop database test2;

create tablespace peterspc add datafile 'peterspc' engine = falcon;

create database test1 ; 
use test1;
create table newtab (id integer) tablespace peterspc engine = falcon;

show create table newtab;

/*returns
CREATE TABLE `newtab` (`id` int(11) DEFAULT NULL ) 
ENGINE=Falcon DEFAULT CHARSET=latin1

-- no 'tablespace' clause returned ....
*/

Suggested fix:
I do not think this has been reported exactly like this before.
But with code insight it may be possible to see that this is a dublicate/side effect of another report!

fix ... yes, why not?

[19 Nov 2007 21:02] Peter Laursen

sorry for pasting twice!

[20 Nov 2007 11:05] MySQL Verification Team

Thank you for the bug report. I wasn't able to repeat with current source
server, please wait for the next release:

mysql> select version();
+-------------------+
| version()         |
+-------------------+
| 6.0.4-alpha-debug | 
+-------------------+
1 row in set (0.00 sec)

mysql> show create table newtab\G
*************************** 1. row ***************************
       Table: newtab
Create Table: CREATE TABLE `newtab` (
  `id` int(11) DEFAULT NULL
) /*!50100 TABLESPACE peterspc */ ENGINE=Falcon DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql>

[20 Nov 2007 11:50] Peter Laursen

I am wondering why

/*!50100 TABLESPACE peterspc */

it should not be 

/*!60100 TABLESPACE peterspc */

??

[20 Nov 2007 12:07] Peter Laursen

or rather 

/*!60002 TABLESPACE peterspc */

[21 Nov 2007 22:42] Peter Laursen

with 6.03 fresh from the mirror

create database test1 ; 
use test1;
create table newtab (id integer) tablespace peterspc engine = falcon;

use test1;
show create table newtab;

returns

CREATE TABLE `newtab` ( `id` int(11) DEFAULT NULL )
 /*!50100 TABLESPACE peterspc */ ENGINE=Falcon DEFAULT CHARSET=utf8

I think this is a bug!!!
TABLESPACE is not supported in 5.1.x 

Looks like someone copy-pasted the /*!50100 PARTITION comment */ without thinking too much!

[22 Nov 2007 9:20] Sveta Smirnova

5.1 supports TABLESPACE:

$mysql51 test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2008
Server version: 5.1.23-beta-debug Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> CREATE TABLE `newtab` ( `id` int(11) DEFAULT NULL )
    ->  /*!50100 TABLESPACE peterspc */ ENGINE=Falcon DEFAULT CHARSET=utf8;
Query OK, 0 rows affected, 2 warnings (0.24 sec)

mysql> show warnings;
+---------+------+------------------------------------------------+
| Level   | Code | Message                                        |
+---------+------+------------------------------------------------+
| Warning | 1286 | Unknown table engine 'Falcon'                  | 
| Warning | 1266 | Using storage engine MyISAM for table 'newtab' | 
+---------+------+------------------------------------------------+
2 rows in set (0.18 sec)

See also http://dev.mysql.com/doc/refman/5.1/en/create-tablespace.html