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/Using_stack_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:[EMAIL PROTECTED] > >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/[EMAIL PROTECTED] > >infoshop.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
