MySQL Storage Engine – Using Symlink or Data Directory for ARCHIVE Tables

MySQLstorage-engine

All mention of either using a symlink or data/index separate data directories state that MyISAM is the only supported (https://dev.mysql.com/doc/refman/5.5/en/symbolic-links.html). However, I found this bug report (http://bugs.mysql.com/bug.php?id=49808), that states "only supported for MyISAM and ARCHIVE tables". This also may be just for partitioning.

I believe just "testing" this is not an option, because the MySQL documentation mysteriously states "For files used by tables for other storage engines, you may get strange problems if you try to use symbolic links. "

Does the ARCHIVE storage engine support symlink or separate data directories?

UPDATE: Related question

Best Answer

All mention of either using a symlink or data/index separate data directories state that MyISAM is the only supported

Almost, but not quite "all."

There are three ways of symlinking in MySQL:

manually symlink the individual table files - only supported for MyISAM and even then the documentation says "as a last resort."
automatically symlink MyISAM MYD and MYI files with DATA DIRECTORY and INDEX DIRECTORY directives.
manually symlink a database (i.e. a single schema) directory to a directory in a different location

This last one is the one you want, and will work transparently with any of the normal storage engines.

Stop reading the page from the manual that you mentioned above before you get to section 8.11.3.1.2, and this will make more sense. Everything from that point forward is related to table files being symlinked, not directories. The information that follows is not relevant if your symlinks are done at the database directory level.

Though not directly related to your question, I'll point out that your entire /usr/local/mysql/data directory can also be a symlink to a directory elsewhere. I almost always do this instead of actually telling MySQL to use a different directory, so that it's obvious to any sysadmin looking at the server itself where the actual data lives.

Related Solutions

MySQL – How Concurrent Insert is Decided for MyISAM Table

While you can do what Rolando suggests and set concurrent_insert=2 to always enable concurrent inserts, to answer your question about filling holes:

we have a MyISAM table with a gap. When we insert new rows and fill those gaps, does the table "immediately" get ready to accept "concurrent inserts" for future insert queries?

Yes (emphasis mine):

If there are holes, concurrent inserts are disabled but are enabled again automatically when all holes have been filled with new data. [src]

Disclaimer: I haven't actually tested it. It seems unless you inserted the exact same data-length in the holes, you will still have holes somewhere.

You can see if there are holes from a query such as this (data_free=0 would mean no holes):

SELECT table_name, data_free FROM information_schema.tables WHERE table_schema='FOO' AND engine='myisam'

Mysql – How to forcibly create/modify MySQL user grants, without restarting the database service

You could copy just the the "mysql" database away to another location and start another daemon on it. Get the SHA1 or DES hash stored in the user table for a user with SUPER privs (usually root, but sometimes renamed for security through obscurity).

Then connect to the mysql using a modified version of the client library that makes mysql_real_connect() support using a pre-hashed password instead of having it take the password plaintext. This should be trivial.

You won't ever know the actual password, but with the hash and a modified client you'll be able to log in anyway.

You can then make any modifications to permissions, create necessary schema and tables and flush privileges.

I'll leave the security implications of such practices up to you.

Best Answer

Related Solutions

MySQL – How Concurrent Insert is Decided for MyISAM Table

Mysql – How to forcibly create/modify MySQL user grants, without restarting the database service

Related Question