MySQL Blogs

 Overview

I have recently blog on the company site about portable Tablespaces

 

What I was saying is that is one of the things that could make us, people who work with MySQL/InnoDB happy.
This because it is a useful feature for administration and not just a "cool" thing to have.
My words were "This is a huge improvement that only people working daily with MySQL/InnoDB can understand,
so far it is still in the lab version but we all really hope to have it deliver with the new MySQL 5.6 GA"
From there I decide to try it right away, and to also try to extend the test.
So what I have done is to take the MySQL version from lab and start to play with tables and tablespaces.
Below my results and final considerations.
After having downloaded and install the Lab version
(root@localhost) [(none)]>status;
--------------
/home/mysql/templates/mysql-56p/bin/mysql  Ver 14.14 Distrib 5.6.6-labs-april-2012, for linux2.6 (i686) using  EditLine wrapper
 
Connection id:        1
Current database:    
Current user:        root@localhost
SSL:            Not in use
Current pager:        stdout
Using outfile:        ''
Using delimiter:    ;
Server version:        5.6.6-labs-april-2012-log MySQL Community Server (GPL)
Protocol version:    10
Connection:        Localhost via UNIX socket
Server characterset:    latin1
Db     characterset:    latin1
Client characterset:    utf8
Conn.  characterset:    utf8
UNIX socket:        /home/mysql/instances/my56testm/mysql.sock
Uptime:            21 sec
 
Threads: 1  Questions: 5  Slow queries: 0  Opens: 16  Flush tables: 1  Open tables: 9  Queries per second avg: 0.238

 

Real work now and create a schema, tables and feed them.

 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

(root@localhost) [(none)]>use test_tablespace1
Database changed
(root@localhost) [test_tablespace1]>show tables;
Empty set (0.00 sec)
(root@localhost) [test_tablespace1]>create table tbtest(a int auto_increment PRIMARY KEY, 
b char(3) DEFAULT 'AAA', dat TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP);
Query OK, 0 rows affected (0.24 sec)
(root@localhost) [test_tablespace1]>
(root@localhost) [test_tablespace1]>insert into tbtest (b) values ('aaa'),('bbb'),('ccc'),('dddd');
Query OK, 4 rows affected, 1 warning (0.05 sec)
Records: 4  Duplicates: 0  Warnings: 1
(root@localhost) [test_tablespace1]>insert into tbtest (b) select b from tbtest;
Query OK, 4 rows affected (0.05 sec)
Records: 4  Duplicates: 0  Warnings: 0
....
(root@localhost) [test_tablespace1]>insert into tbtest (b) select b from tbtest;
Query OK, 4096 rows affected (0.44 sec)
Records: 4096  Duplicates: 0  Warnings: 0
 

 

Now what we do have on fs ?
1
2
3
4
5
6

rwx------ 2 mysql mysql   4096 2012-04-27 14:42 ./
drwxr-xr-x 4 mysql mysql   4096 2012-04-27 14:28 ../
-rw-rw---- 1 mysql mysql     65 2012-04-27 14:28 db.opt
-rw-rw---- 1 mysql mysql   8606 2012-04-27 14:42 tbtest.frm
-rw-rw---- 1 mysql mysql 360448 2012-04-27 14:45 tbtest.ibd
root@000033:/home/mysql/instances/my56testm/data/test_tablespace1#

 

Time to RAISE THE COMMAND
1
2

(root@localhost) [test_tablespace1]>FLUSH TABLES tbtest WITH READ LOCK;
Query OK, 0 rows affected (0.00 sec)

 

Check on the file system to see the new cfg file
1
2
3
4
5
6
7

drwx------ 2 mysql mysql   4096 2012-04-27 14:51 ./
drwxr-xr-x 4 mysql mysql   4096 2012-04-27 14:28 ../
-rw-rw---- 1 mysql mysql     65 2012-04-27 14:28 db.opt
-rw-rw---- 1 mysql mysql    440 2012-04-27 14:51 tbtest.cfg <--------------IS THERE!!!
-rw-rw---- 1 mysql mysql   8606 2012-04-27 14:42 tbtest.frm
-rw-rw---- 1 mysql mysql 360448 2012-04-27 14:45 tbtest.ibd
root@000033:/home/mysql/instances/my56testm/data/test_tablespace1#

 

And given I am curious, I read insight:
we have there:
1
2
3
4
5
6
7
8

Name of the machine:     000033
Schema/Table name:       test_tablespace1/tbtest
Table definition:        a, b, dat
last ROW_ID:             DB_ROW_ID
Transaction ID:          DB_TRX_ID
Rollback pointer:        DB_ROLL_PTR
Primary kye and value :  PRIMARY a
Last transaction valuea: DB_TRX_ID DB_ROLL_PTR b dat

 

Now let us copy then remove it and see what happens.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

root@000033:/home/mysql/instances/my56testm/data/test_tablespace1# mkdir -p /home/mysql/backup
root@000033:/home/mysql/instances/my56testm/data/test_tablespace1# cp tbtest.* /home/mysql/backup/
root@000033:/home/mysql/instances/my56testm/data/test_tablespace1# ll
total 380
drwx------ 2 mysql mysql   4096 2012-04-27 14:59 ./
drwxr-xr-x 4 mysql mysql   4096 2012-04-27 14:28 ../
-rw-rw---- 1 mysql mysql     65 2012-04-27 14:28 db.opt
-rw-rw---- 1 mysql mysql    440 2012-04-27 14:51 tbtest.cfg
-rw-rw---- 1 mysql mysql   8606 2012-04-27 14:42 tbtest.frm
-rw-rw---- 1 mysql mysql 360448 2012-04-27 14:45 tbtest.ibd
root@000033:/home/mysql/instances/my56testm/data/test_tablespace1# ll /home/mysql/backup
total 376
drwxr-xr-x  2 root  root    4096 2012-04-27 14:59 ./
drwxrwxr-- 30 mysql mysql   4096 2012-04-27 14:59 ../
-rw-r-----  1 root  root     440 2012-04-27 14:59 tbtest.cfg
-rw-r-----  1 root  root    8606 2012-04-27 14:59 tbtest.frm
-rw-r-----  1 root  root  360448 2012-04-27 14:59 tbtest.ibd
root@000033:/home/mysql/instances/my56testm/data/test_tablespace
 

 

I have the files in the backup dir
Now is time to unlock the tables:
1

UNLOCK TABLES;

 

And check what happened:
1
2
3
4
5
6

drwx------ 2 mysql mysql   4096 2012-04-27 15:01 ./
drwxr-xr-x 4 mysql mysql   4096 2012-04-27 14:28 ../
-rw-rw---- 1 mysql mysql     65 2012-04-27 14:28 db.opt
-rw-rw---- 1 mysql mysql   8606 2012-04-27 14:42 tbtest.frm
-rw-rw---- 1 mysql mysql 360448 2012-04-27 14:45 tbtest.ibd
 

 

No more cfg file.
Copy the table to something else and Drop the table:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

(root@localhost) [test_tablespace1]>create table tbtest2 select * from tbtest;
Query OK, 8192 rows affected (1.02 sec)
Records: 8192  Duplicates: 0  Warnings: 0
(root@localhost) [test_tablespace1]>
Drop tbtest:
(root@localhost) [test_tablespace1]>drop table tbtest;
Query OK, 0 rows affected (0.08 sec)
(root@localhost) [test_tablespace1]>show tables;
+----------------------------+
| Tables_in_test_tablespace1 |
+----------------------------+
| tbtest2                    |
+----------------------------+
1 row in set (0.00 sec)
 

 

Create a fake table tbtest
1
2
3
4
5
6
7
8
9
10
11
12
13

(root@localhost) [test_tablespace1]>create table tbtest(a char(1));
Query OK, 0 rows affected (0.23 sec)
(root@localhost) [test_tablespace1]>ALTER TABLE tbtest DISCARD TABLESPACE;
Query OK, 0 rows affected (0.04 sec)
(root@localhost) [test_tablespace1]>
drwx------ 2 mysql mysql   4096 2012-04-27 15:05 ./
drwxr-xr-x 4 mysql mysql   4096 2012-04-27 14:28 ../
-rw-rw---- 1 mysql mysql     65 2012-04-27 14:28 db.opt
-rw-rw---- 1 mysql mysql   8606 2012-04-27 15:02 tbtest2.frm
-rw-rw---- 1 mysql mysql 425984 2012-04-27 15:02 tbtest2.ibd
-rw-rw---- 1 mysql mysql   8554 2012-04-27 15:04 tbtest.frm
-rw-rw---- 1 mysql mysql  98304 2012-04-27 15:04 tbtest.ibt <----------- DETACHED tablespace
 

 

COPY back the files:
1
2
3
4
5
6
7
8
9
10
11
12

root@000033:/home/mysql/instances/my56testm/data/test_tablespace1# cp /home/mysql/backup/* .
root@000033:/home/mysql/instances/my56testm/data/test_tablespace1# ll
total 904
drwx------ 2 mysql mysql   4096 2012-04-27 15:06 ./
drwxr-xr-x 4 mysql mysql   4096 2012-04-27 14:28 ../
-rw-rw---- 1 mysql mysql     65 2012-04-27 14:28 db.opt
-rw-rw---- 1 mysql mysql   8606 2012-04-27 15:02 tbtest2.frm
-rw-rw---- 1 mysql mysql 425984 2012-04-27 15:02 tbtest2.ibd
-rw-r----- 1 root  root     440 2012-04-27 15:06 tbtest.cfg
-rw-rw---- 1 mysql mysql   8606 2012-04-27 15:06 tbtest.frm
-rw-r----- 1 root  root  360448 2012-04-27 15:06 tbtest.ibd
-rw-rw---- 1 mysql mysql  98304 2012-04-27 15:04 tbtest.ibt

 

Fix permissions
1
2
3
4
5
6
7
8
9
10
11
12

root@000033:/home/mysql/instances/my56testm/data/test_tablespace1# chown mysql:mysql tbtest.*
root@000033:/home/mysql/instances/my56testm/data/test_tablespace1# ll
total 904
drwx------ 2 mysql mysql   4096 2012-04-27 15:06 ./
drwxr-xr-x 4 mysql mysql   4096 2012-04-27 14:28 ../
-rw-rw---- 1 mysql mysql     65 2012-04-27 14:28 db.opt
-rw-rw---- 1 mysql mysql   8606 2012-04-27 15:02 tbtest2.frm
-rw-rw---- 1 mysql mysql 425984 2012-04-27 15:02 tbtest2.ibd
-rw-r----- 1 mysql mysql    440 2012-04-27 15:06 tbtest.cfg
-rw-rw---- 1 mysql mysql   8606 2012-04-27 15:06 tbtest.frm
-rw-r----- 1 mysql mysql 360448 2012-04-27 15:06 tbtest.ibd
-rw-rw---- 1 mysql mysql  98304 2012-04-27 15:04 tbtest.ibt

 

Remove fake table space and import back the old one
1
2
3

root@000033:/home/mysql/instances/my56testm/data/test_tablespace1# rm -f tbtest.ibt
(root@localhost) [test_tablespace1]>ALTER TABLE tbtest IMPORT TABLESPACE;
ERROR 1801 (HY000): InnoDB: Number of columns don't match, table has 4 columns but the tablespace meta-data file has 6 columns

 

Error, well that was easy to understand, my fault I was suppose to create a fake table with the same structure not different.
Let me repeat the process of the fake table.
I did:
  • attach the ibt table space (I had it saved in backup)
  • drop it
  • recreate table as for initialal structure
  • detach it again.
  • copy back the old idb file and cfg
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

(root@localhost) [test_tablespace1]>ALTER TABLE tbtest IMPORT TABLESPACE;
Query OK, 0 rows affected (2.93 sec)
(root@localhost) [test_tablespace1]>check table tbtest;
+-------------------------+-------+----------+----------+
| Table                   | Op    | Msg_type | Msg_text |
+-------------------------+-------+----------+----------+
| test_tablespace1.tbtest | check | status   | OK       |
+-------------------------+-------+----------+----------+
1 row in set (0.02 sec)
(root@localhost) [test_tablespace1]>select count(*) from tbtest;
+----------+
| count(*) |
+----------+
|     8192 |
+----------+
1 row in set (0.00 sec)

 

WOW it works that could make me happy but given I am never happy (enough)
Let me try a crazy thing.
  • Create a fake table tbtest3
  • change the info in the cfg file and the table space filename
  • try to import it.
Let's go ...
First copy and modify the cfg file:
1
2
3

root@000033:/home/mysql/backup# cp tbtest.ibd tbtest3.ibd
root@000033:/home/mysql/backup# cp tbtest.cfg tbtest3.cfg
root@000033:/home/mysql/backup# vi tbtest3.cfg

 

Try to attach it:
1
2

(root@localhost) [test_tablespace1]>ALTER TABLE tbtest3 IMPORT TABLESPACE;
ERROR 1712 (HY000): InnoDB: While reading table name: 'I/O error'.

 

No luck, it would have being to cool and easy.

Ok so Conclusions

====================
1) we can now do export - import of table spaces a little bit more easily
2) we cannot move tablespace cross schemas
3) we cannot attach a tablespace to another table
I understand that would be too cool and we must accept what we have, and it would be also nice to take a look at the code.

The full procedure in a thumb

========================================
Assuming you already have the table in place
  1. take the table creation to replicate the structure
    SHOW CREATE TABLE tbtest\G
  2. Lock the table to copy it
    FLUSH TABLE tbtest WITH READ LOCK;
  3. Copy somewhere the files DON'T forget the .cnf
  4. UNLOCK TABLES;
  5. Drop the table
  6. Create a fake table using the create statement stored before
  7. Detach the table
  8. ALTER TABLE tbtest DISCARD TABLESPACE;
  9. MOVE !!! the *.ibt file in a safe place
  10. Copy over the previous files from the backup directory
  11. CHECK PERMISSION!!
  12. Import back the table space
  13. ALTER TABLE tbtest IMPORT TABLESPACE;
  14. check table;

 

{joscommentenable}



Related Articles

Latest conferences

We have 2053 guests and no members online

login

Remember Me