SEO

Hi,
What I'm trying to do is to create a new InnoDB table on a different disk and
symlink it to an existing database.
I have innodb_file_per_table turned on and here is how I tried to do it


mysql> \u test
mysql> create table test (...) ENGINE = 'InnoDB';
mysql>\q

move the test.ibd file to the other disk
create a simlink in the database directory
flush tables;


This works as expected but there is something that bothers me - I inserted
about 60K rows in the new table and all queries I tried are working
including selects, inserts and updates. The "SHOW TABLE STATUS" command
displays relevant results and still the test.ibd file to which the symlink
points hasn't been changed or accessed at all.

Any ideas are welcome
Dobromir Velev

Dobromir Velev schrieb:
> Hi,
> What I'm trying to do is to create a new InnoDB table on a different disk and
> symlink it to an existing database.
> I have innodb_file_per_table turned on and here is how I tried to do it
>
>
> mysql> \u test
> mysql> create table test (...) ENGINE = 'InnoDB';
> mysql>\q
>
> move the test.ibd file to the other disk
> create a simlink in the database directory
> flush tables;
>
>
> This works as expected but there is something that bothers me - I inserted
> about 60K rows in the new table and all queries I tried are working
> including selects, inserts and updates. The "SHOW TABLE STATUS" command
> displays relevant results and still the test.ibd file to which the symlink
> points hasn't been changed or accessed at all.
>
> Any ideas are welcome

you need to setup per-table tablespace, did you?

Section 13.2.3.1, “Using Per-Table Tablespaces”.

http://dev.mysql.com/doc/refman/5.0/en/innodb-init.html

--
Sebastian Mendel

Hi,
Thanks for pointing it out - I just found the following commands.

ALTER TABLE tbl_name DISCARD TABLESPACE;
ALTER TABLE tbl_name IMPORT TABLESPACE;

I will test it and let you know if it works

Thanks
Dobromir Velev



On Wednesday 23 April 2008 16:27, Sebastian Mendel wrote:
> Dobromir Velev schrieb:
> > Hi,
> > What I'm trying to do is to create a new InnoDB table on a different disk
> > and symlink it to an existing database.
> > I have innodb_file_per_table turned on and here is how I tried to do it
> >
> >
> > mysql> \u test
> > mysql> create table test (...) ENGINE = 'InnoDB';
> > mysql>\q
> >
> > move the test.ibd file to the other disk
> > create a simlink in the database directory
> > flush tables;
> >
> >
> > This works as expected but there is something that bothers me - I
> > inserted about 60K rows in the new table and all queries I tried are
> > working including selects, inserts and updates. The "SHOW TABLE STATUS"
> > command displays relevant results and still the test.ibd file to which
> > the symlink points hasn't been changed or accessed at all.
> >
> > Any ideas are welcome
>
> you need to setup per-table tablespace, did you?
>
> Section 13.2.3.1, “Using Per-Table Tablespaces”.
>
> http://dev.mysql.com/doc/refman/5.0/en/innodb-init.html

If Linux works the same way as HP-UX (and it should), anything you do to an
open file (including deleting it) has no effect until the file is closed. The
MySQL server is still using the "old" file. The next time it stops and
restarts, it will follow the symlink. I don't know what the effect of
accessing a "stale" copy of the file will do.

Regards,

Jerry Schwartz
The Infoshop by Global Information Incorporated
195 Farmington Ave.
Farmington, CT 06032

860.674.8796 / FAX: 860.674.8341

www.the-infoshop.com
www.giiexpress.com
www.etudes-marche.com
>-----Original Message-----
>From: Sebastian Mendel [mailto:lists@sebastianmendel.de]
>Sent: Wednesday, April 23, 2008 9:27 AM
>To: Dobromir Velev
>Cc: mysql@lists.mysql.com
>Subject: Re: Symlink InnoDB tables without stoping MySQL
>
>Dobromir Velev schrieb:
>> Hi,
>> What I'm trying to do is to create a new InnoDB table on a different
>disk and
>> symlink it to an existing database.
>> I have innodb_file_per_table turned on and here is how I tried to do
>it
>>
>>
>> mysql> \u test
>> mysql> create table test (...) ENGINE = 'InnoDB';
>> mysql>\q
>>
>> move the test.ibd file to the other disk
>> create a simlink in the database directory
>> flush tables;
>>
>>
>> This works as expected but there is something that bothers me - I
>inserted
>> about 60K rows in the new table and all queries I tried are working
>> including selects, inserts and updates. The "SHOW TABLE STATUS"
>command
>> displays relevant results and still the test.ibd file to which the
>symlink
>> points hasn't been changed or accessed at all.
>>
>> Any ideas are welcome
>
>you need to setup per-table tablespace, did you?
>
>Section 13.2.3.1, "Using Per-Table Tablespaces".
>
>http://dev.mysql.com/doc/refman/5.0/en/innodb-init.html
>
>--
>Sebastian Mendel
>
>--
>MySQL General Mailing List
>For list archives: http://lists.mysql.com/mysql
>To unsubscribe: http://lists.mysql.com/mysql?unsub=jschwartz@the-
>infoshop.com

Hi,
I guessed it was something like it and that is why I wanted to make sure how
it should be done. Using the ALTER TABLE table DISCARD TABLESPACE doesn't
seem to work as expected - I succeeded to crash the test server twice. See
the mysql log details below.

What I did was the following:
- create table
- check INNODDB status and copy the table.ibd to a new location
- run ALTER TABLE table DISCARD TABLESPACE
- symlink the table.ibd copy within the database folder
- run ALTER TABLE table IMPORT TABLESPACE
- run show table status like 'table';

And here are the crash details from the log.

InnoDB: buf pool start is at 0x3666c000, end at 0xb366c000
InnoDB: Probable reason is database corruption or memory
InnoDB: corruption. If this happens in an InnoDB database recovery,
InnoDB: you can look from section 6.1 at http://www.innodb.com/ibman.html
InnoDB: how to force recovery.
080424 4:31:55InnoDB: Assertion failure in thread 68795312 in
file ./../include/buf0buf.ic line 262
InnoDB: We intentionally generate a memory trap.
InnoDB: Submit a detailed bug report to http://bugs.mysql.com.
InnoDB: If you get repeated assertion failures or crashes, even
InnoDB: immediately after the mysqld startup, there may be
InnoDB: corruption in the InnoDB tablespace. Please refer to
InnoDB: http://dev.mysql.com/doc/mysql/en/Forcing_recovery.html
InnoDB: about forcing recovery.
InnoDB: Thread 73976752 stopped in file ./../include/sync0sync.ic line 111
InnoDB: Thread 729131952 stopped in file sync0arr.c line 336
InnoDB: Thread 150207408 stopped in file sync0arr.c line 336
InnoDB: Thread 747498416 stopped in file sync0arr.c line 336
InnoDB: Thread 63421360 stopped in file ./../include/sync0sync.ic line 111
mysqld got signal 11;
This could be because you hit a bug. It is also possible that this binary
or one of the libraries it was linked against is corrupt, improperly built,
or misconfigured. This error can also be caused by malfunctioning hardware.
We will try our best to scrape up some info that will hopefully help diagnose
the problem, but since we have already crashed, something is definitely wrong
and this may fail.

key_buffer_size=1048576000
read_buffer_size=507904
max_used_connections=601
max_connections=600
threads_connected=394
It is possible that mysqld could use up to
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections =
1935995 K
bytes of memory
Hope that's ok; if not, decrease some variables in the equation.

thd=0x2c24e950
Attempting backtrace. You can use the following information to find out
where mysqld died. If you see no messages after this, something went
terribly wrong...
Cannot determine thread, fp=0x4197e0c, backtrace may not be correct.
Stack range sanity check OK, backtrace follows:
0x8136da4
0x438898
(nil)
0x8299f88
0x829a024
0x81c2f5b
0x81d6f60
0x814a563
0x814e66c
0x814f08a
0x814f8e5
0x8150330
0x432371
0x38cffe
New value of fp=(nil) failed sanity check, terminating stack trace!
Please read http://dev.mysql.com/doc/mysql/en/Us...ack_trace.html and
follow instructions on how to resolve the stack trace. Resolved
stack trace is much more helpful in diagnosing the problem, so please do
resolve it
Trying to get some variables.
Some pointers may be invalid and cause the dump to abort...
thd->query at 0xbb66460 = show table status like 'temp%'
thd->thread_id=2545123
The manual page at http://www.mysql.com/doc/en/Crashing.html contains
information that should help you find out what is causing the crash.

Number of processes running now: 0
080424 04:31:56 mysqld restarted
080424 4:31:57 InnoDB: Database was not shut down normally!
InnoDB: Starting crash recovery.

Thanks for your help
Dobromir Velev

On Wednesday 23 April 2008 22:05, Jerry Schwartz wrote:
> If Linux works the same way as HP-UX (and it should), anything you do to an
> open file (including deleting it) has no effect until the file is closed.
> The MySQL server is still using the "old" file. The next time it stops and
> restarts, it will follow the symlink. I don't know what the effect of
> accessing a "stale" copy of the file will do.
>
> Regards,
>
> Jerry Schwartz
> The Infoshop by Global Information Incorporated
> 195 Farmington Ave.
> Farmington, CT 06032
>
> 860.674.8796 / FAX: 860.674.8341
>
> www.the-infoshop.com
> www.giiexpress.com
> www.etudes-marche.com
>
> >-----Original Message-----
> >From: Sebastian Mendel [mailto:lists@sebastianmendel.de]
> >Sent: Wednesday, April 23, 2008 9:27 AM
> >To: Dobromir Velev
> >Cc: mysql@lists.mysql.com
> >Subject: Re: Symlink InnoDB tables without stoping MySQL
> >
> >Dobromir Velev schrieb:
> >> Hi,
> >> What I'm trying to do is to create a new InnoDB table on a different
> >
> >disk and
> >
> >> symlink it to an existing database.
> >> I have innodb_file_per_table turned on and here is how I tried to do
> >
> >it
> >
> >> mysql> \u test
> >> mysql> create table test (...) ENGINE = 'InnoDB';
> >> mysql>\q
> >>
> >> move the test.ibd file to the other disk
> >> create a simlink in the database directory
> >> flush tables;
> >>
> >>
> >> This works as expected but there is something that bothers me - I
> >
> >inserted
> >
> >> about 60K rows in the new table and all queries I tried are working
> >> including selects, inserts and updates. The "SHOW TABLE STATUS"
> >
> >command
> >
> >> displays relevant results and still the test.ibd file to which the
> >
> >symlink
> >
> >> points hasn't been changed or accessed at all.
> >>
> >> Any ideas are welcome
> >
> >you need to setup per-table tablespace, did you?
> >
> >Section 13.2.3.1, "Using Per-Table Tablespaces".
> >
> >http://dev.mysql.com/doc/refman/5.0/en/innodb-init.html
> >
> >--
> >Sebastian Mendel
> >
> >--
> >MySQL General Mailing List
> >For list archives: http://lists.mysql.com/mysql
> >To unsubscribe: http://lists.mysql.com/mysql?unsub=jschwartz@the-
> >infoshop.com