Home / server / Questions / 176207
In Process
Eno
Asked: 2010-08-31 11:26:35 +0800 CST

Why are symbolic links not working in MySQL?

  • 772

I'm having an issue, I searched a lot but I'm not sure if it's related to a previous security patch. On the last version of MySQL on Debian Lenny ( 5.0.51a-24 ) I need to share one table between two db, those two db are in the same path ( /var/lib/mysql/db1 & db2 ). I created symbolic links for db2 pointing to the table in db1.

When I query the same table from db2 I get this : 'ERROR 1030 (HY000): Got error 140 from storage engine'

This is how it looks :

test-lan:/var/lib/mysql/test3# ls -alh
drwx------ 2 mysql mysql 4.0K 2010-08-30 13:28 .
drwxr-xr-x 6 mysql mysql 4.0K 2010-08-30 13:29 ..
lrwxrwxrwx 1 mysql mysql 28 2010-08-30 13:28 blbl.frm -> /var/lib/mysql/test/blbl.frm
lrwxrwxrwx 1 mysql mysql 28 2010-08-30 13:28 blbl.MYD -> /var/lib/mysql/test/blbl.MYD
lrwxrwxrwx 1 mysql mysql 28 2010-08-30 13:28 blbl.MYI -> /var/lib/mysql/test/blbl.MYI
-rw-rw---- 1 mysql mysql 65 2010-08-30 13:24 db.opt

I really need those symlinks, is there a way to make them working like before ? ( old MySQL-server is fine )

mysql debian symbolic-link

5 Answers

  1. This is behavior that is no longer supported.

    Refer to CVE-2008-2079: http://bugs.mysql.com/bug.php?id=32167

    • 1

  2. I think Error 140 is a permissions issue - what are the permissions inside /var/lib/mysql/test? They also need to be mysql:mysql.

    You've also got a problem here that the database is eventually going to have locking issues - what database engine are you using for these tables?

    • 0

  3. Is it possible to split this shared table into a third database, and give both programs access to it? This will probably be your least-maintenance option in the long run.

    • 0

  4. Why do you need a symlink? Why not just grant which ever user the privileges to read that table from the second DB and explicitly call query that db/table combo, like so:

    $ mysql db1
mysql> SELECT * FROM db2.table;

    Symlinking like that is definitely a non-supported way to share a table and will definitely lead to issues down the line. MySQL is expecting that a DB will own all the tables, so symlinking may cause corrupted data.

    • 0

  5. Don't use a symbolic link; use a hard link. It looks just like a regular file to the OS and therefore to MySQL as well. It's basically a file with two entries in the filesystem instead of the usual one.

    ln old_file new_file
    • 0