List:General Discussion« Previous MessageNext Message »
From:Lucio Chiappetti Date:February 9 2009 5:10pm
Subject:using Unix soft links
View as plain text  
I used to make Unix soft links of mysql tables back on mysql 3.23.
Say that I do an arbitrary sequence of CREATE TABLE, LOAD LOCAL DATA 
INFILE, INSERT, UPDATE etc. on a table "xxxxx".

At some stage (after the CREATE) I do (outside of mysql)

foreach i (MYD MYI frm)
  ln -s xxxxx.$i xxxxxdup.$i

so that a soft-link clone of the table comes into existence.

Then I have cases where I do queries on xxxxx left join xxxxxdup (or on 
some other table left join xxxxx left join xxxxxdup). I never perform any 
update, insert or delete on xxxxxdup.

I know I could do a query on the same table using two aliases (xxxxxx as 
a left join xxxxx as b) and this is in fact what I do when I perform 
interactive operations using the mysql client.

I know also I could do a "create view xxxxxdup select * from xxxxx" 
(although this was not possible back in 3.23, and I wonder whether using 
such an xxxxxdup would be efficient index-wise). I do use views for other 
purposes.

But there were good reasons at the time to use soft links (one was not 
existence of the views, the other is that my users do access everything 
from a java front end, which does not and shall not know that a 
"duplicated clone" is special (so it can't use aliases).

The arrangement with soft links has been working fine for years, under 
mysql 3.23, 4.x and including at least a couple of years with current 
mysql 5.0.27.

However today one of my "dup" tables was indicated as corrupted. I did a 
"repair table xxxxxdup" and it said the size of the table was shrunk from 
1469 to 213 records. I did some sequence of "repair table" on xxxxx and 
xxxxdup, and the outcome seemed unstable. Now both tables show the (WRONG) 
number of 213 records.

I will probably delete all tables and recreate them.

But I wonder if there is anything intrinsically wrong in the usage of 
soft-links, or whether this might be an indication instead of hardware 
problems with our machine or disks !

-- 
Lucio Chiappetti - INAF/IASF - via Bassini 15 - I-20133 Milano (Italy)
For more info : http://www.iasf-milano.inaf.it/~lucio/personal.html
-----------------------------------------------------------------------
"Nature" on government cuts to research       http://snipurl.com/4erid
"Nature" e i tagli del governo alla ricerca   http://snipurl.com/4erko
Thread
optimizing UNIONs ?Lucio Chiappetti10 Jun
  • using Unix soft linksLucio Chiappetti9 Feb
    • Re: using Unix soft linksJohan De Meersman10 Feb
Re: optimizing UNIONs ?Lucio Chiappetti11 Jun