Mysql innodb tables not symlinkable?

Today I noticed that, unlike myisam tables, innodb tables seems (are?) not symlikable, so to say, in another database.
It is really so? And no workaround for this (serious) limitation?

But there is a replacement for symlinks. So what’s the problem?

https://dev.mysql.com/doc/refman/5.7/en/symbolic-links.html

1 Like

So far, with myisam tables, I simlinked them very easy (outside mysql, via file manager).
Now I should learn this new way, specific for innodb tables, to “symlink” a table from a (local) database to another (local) one.

EDIT
My aim is to symlink the table ‘eventi’ from its database to the ‘bibliografia’ one, which share the same (near) root folder (so this is at …/bibliografia place in the PC).

I tried this code, unsuccessfully:

CREATE TABLE eventi (id INT PRIMARY KEY) TABLESPACE = innodb_file_per_table
       DATA DIRECTORY = '../bibliografia';
MySQL said: Documentation

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '= innodb_file_per_table
       DATA DIRECTORY = '../bibliografia'' at line 1

And also this:

CREATE TABLE eventi (id INT PRIMARY KEY) DATA DIRECTORY = '../bibliografia';
MySQL said: Documentation

#1103 - Incorrect table name '../bibliografia'

I noticed that giving a non-relative, but absolute path (/mnt/my-path) the above code works.
However so far I didn’t manage to get what I want: to symlink the same innodb table to other databases.
An .isl file is a very simple file: it contains an absolute path, but no way (for me, so far) to use it to symlink already existing tables.

I am not sure what is the final target of your work. Why exactly do you want to link the table from one database to another if they are on the same server? You can easily access them both from every application or you can create views what is a little bit more clean code.

For me, the only real usage of linking a table is if you need to move to another hardware because the old has not enough space.

1 Like

Yes, I know that linking tables from different db on the same server is possible (with mysql code).
But a symlink was for me the easiest way to do it :slightly_smiling_face: (maybe I am lazy).

But you, therefore, confirm that it is not possible (as far as you know, at least) link the same table to other (local) databases?

Yes you can link very easy

CREATE SQL SECURITY DEFINER VIEW targetdatabase.tablename AS SELECT
*
FROM
sourcedatabase.tablename

:wink:

1 Like

excellent! Thank you very, very much!!! :+1: :+1: :+1:

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.