homework help worksheets
cheap airlines essay
kraft paper buyer
essay writing services review
help with homework english
best resume writing service canada
writemyessay com coupon code
buy paper lunch bags
entry level resume writing services
resume writing services va
help with rutgers college essay
it is helpful to write your speech as if it were a written essay
order a white paper
custom exam past papers
term paper on best buy
assignment writers.com
essay about our helpers
personal statement for fire service
buy mulberry paper flowers
buying term papers review
cover letter resume together
where can i buy paper jamz
buy hand written research paper
english critical essay help
help me with my math homework
writemypaper.rog
buy paper sofa
cheapest essays for sale
social work personal statement help
can i write essays on my iphone
service quality in hospitality industry dissertation
help with doctoral dissertation
does homework help prepare for college
essay writing service feedback
trigonometry homework help
research paper help
green paper on online gambling services
essay writer app
dissertation help delhi
buying paper money
eating disorders essay outline
order paper questions
homework property services
essay on how to help grandparents
free online college homework help
resume writer entertainment industry
essaywriters.com login
professional resume writing service military
resume writing service for military to civilian
what should i write my research paper about
spatial order of an essay
custom paper writing services
write an essay to get a scholarship
essay writing service psychology
how to buy essays online
cover letter for outbound customer service representative
community service essay introduction
buy abstract paper
essaywritingservice
research essay helper
custom design rolling papers
resume writing services naperville il
how to write a literature review for my dissertation
penny paper writer reviews
custom paper merchandise bags
cover letter sample for client service executive
reddit thesis help
books to help write essays
how to get essay ideas
explain how to use a job announcement to help prepare a resume and cover letter
cover letter helper online
customs information papers 2013
research proposal on service delivery
certified resume writers nyc
online economics homework help
mba assignment help singapore
filipino writers and their essays
florida state application essay help
ib english literature paper 1 help
grant writer resume cover letter
university of manitoba essay help
buy wallpaper online australia
business services manager cover letter
paperbag writer tab
help writing graduate papers
automatic essay writer wikipedia
custom paper ribbon
professional resume writer utah
buy ready essays
art history homework help
cover letter for customer service in insurance
essay writer fake
homework help center library
professional resume writers for executives
dissertation buy uk
cheap juicy jays rolling papers
jk paper buy online
executive resume writing service seattle
what does it mean when a guy helps you with homework
term paper help womens studies
resume writing service new zealand
help me write my research paper for free
best cover letter writing services in new york city
buy paper punch online india
a manual for writers of research papers theses and dissertations 7th edition pdf
the aztecs homework help
sales and customer service cover letter
where to buy paper magazine
ut online homework service
list of filipino essay writers
visitor services assistant cover letter
law essay buy
help with homework sites
do you get summer assignments in college
civil services mains gs paper 2011
virtual customer service cover letter
i gonna buy a paper doll lyrics
graphic design resume writing service
resume writer medford nj
resume writer in houston
help on writing a college application essay
write an essay on my daily routine
cover letter writing services london
need help do my essay
custom paper printing and cutting
waste news paper buyer in india
homework help ontario
ways to help my child with homework
dissertation help uae
cheap scrapbook papers
thesis statement writing help
help paperless post
american general assignment services trust
help with university of phoenix homework
how to help a child with add do homework
write my essay me
how do writers make their points in a research paper
professional resume writers in durham nc
where to buy cheap bond paper
get help for assignments
college paper editing services
assignment writing service in delhi
how do i know what to write my college essay about
best essay writing service uk yahoo
music to help you concentrate on homework
paper writing service cheap
graduate thesis writing services
business finance assignment help
how does global revision help make your essay better
died on the order paper
homework support service
geography homework help ks3
ille paper services new ross
cheap paper shredder malaysia
quest homework services
how to write an essay about my academic goals
best uk essay writing services
using an essay writing service
best buy mobile resume
writing a research paper buy order
it service desk administrator cover letter
help with probability homework
cover letter help calgary
dissertation writers in delhi
i dont know how to write my college essay
community service in college essay
professional resume writers denver
essay about service in school
where to buy philosophy essay
monster professional resume writing services
scrap paper buyer in cebu
cover letter to fashion buyer
what papers are required to be in the aircraft in order to be legal to fly
order specialty paper online
write my dissertation online
sociology homework help free
cover letter to get into business school
resume writing services in austin tx
persuasive essay to get into college
civil service essay answers
paper writing service accredited
resume writing service reviews ladders
how to write a cover letter for customer service officer
live homework help library
professional resume writing service in chicago
personal statement to get into nursing school
motivate me to write my essay
the best dissertation writing services
custompapers.com writers
cheap rolling papers
homework help center online
time order expository essay
purchase essays college
buy small paper bags uk
custom paper flowers
get help with homework online
thesis binding service
how old do you have to be to buy rolling papers in nj
where can i buy a paper cutter
essay topics for young writers
 
 
Home MySQL Blogs Portable Tablespace in InnoDB I test it!
Portable Tablespace in InnoDB I test it! PDF Stampa E-mail
Scritto da Marco Tusa   
Venerdì 27 Aprile 2012 20:58

 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}



Commenti (1)
  • Sunny Bains
    To attach a tablespace across schemas is trivial, the only check that we do is to match against the name. The checks were introduced so that users don't shoot themselves in the foot. You can try it by commenting out the code in the labs release where we compare the table name in row/row0import.cc. Simply comment out the check. I think it should work.
  • Marco Tusa
    Sunny, thank will do the test for sure. And let you know
Solo gli utenti registrati possono inviare commenti!

!joomlacomment 4.0 Copyright (C) 2009 Compojoom.com . All rights reserved."

Ultimo aggiornamento Domenica 18 Agosto 2013 17:28
 
 

Who's Online

 31 visitatori online