Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I've the need to put my database outside the default datadir of mysql.

To achieve that I followed the symlink guidance in the mysql documentation.

However when I use the db if I try to create a table I'm greeted with:

ERROR 1005 (HY000): Can't create table 'test' (errno: 13)

In my search for solutions I already read Can I use symlink or data directory for ARCHIVE MySQL tables? and this answer which didn't apply for my context.

At the moment my data dir looks like this with ls -Ll

server:/var/lib/mysql# ls -Ll
total 28800
-rw-r--r-- 1 root  root         0 Jan 26 00:37 debian-5.5.flag
-rw-rw---- 1 mysql mysql    25088 Feb  8  2005 ib_arch_log_0000000000
-rw-rw---- 1 mysql mysql 18874368 May 31 15:21 ibdata1
-rw-rw---- 1 mysql mysql  5242880 May 31 15:21 ib_logfile0
-rw-rw---- 1 mysql mysql  5242880 Feb  8  2005 ib_logfile1
drwx------ 2 mysql mysql     4096 Jan 26 00:37 mysql
drwxr-xr-x 1 root  root         0 May 29 00:01 so
drwx------ 2 mysql mysql     4096 May 28 22:48 so2
drwxr-xr-x 2 mysql mysql      4096 May 31 14:34 symlink
drwxr-xr-x 2 mysql mysql     4096 Apr 20  2013 test

The two symlinks are created with the command

ln -s /mnt/nas/se/data/mysql/ so

I chown the symlinks to mysql:mysql as well as in the directory it self. For the symlink directory that worked (that is on an ext3 fs) but for the so one (that is on a nas over CIFS/SMB) I'm still out of luck.

I managed to enable NFS (ver 3) on the NAS (running BusyBox v1.1.1) and mounted that as well. I synced the UID for the mysql account on the server and the client. Unfortunately the result is the same for ls -Ll

drwxrwxr-x 2 root  rene      4096 May 31 16:51 sonfs

and for ls -l

lrwxrwxrwx 1 root  root         8 Jun  1 00:26 sonfs -> /mnt/nfs

I did su mysql and tried to cp a file to those symlinked directories and that worked which made me assume on the OS level the permissions are OK.

Within mysql I granted root all permissions to the so and the symlink database.

I'm not sure if I the problem I face is something I misconfigured at the Debian OS level or within mysql itself.

Can someone point out to me what diagnosis I need to do and which steps need to be taken to be able to create tables that get stored in those symlinked directories?

share|improve this question
    
Can you show the output of ls -Ll in that directory, just to be sure? Also what filesystem is it and what mount options do you have? –  Mat May 31 at 14:03
    
@Mat you're on to something...Now it shows root as owner and group... –  rene May 31 at 14:09
    
As root, cd so then chown mysql:mysql .. If that doesn't work, will need to know what filesystem you're using. –  Mat May 31 at 14:12
    
@Mat the symlink db now works, that is on the same box, the filesystem is ext3. The so one is on my nas and that is CIFS/SMB. –  rene May 31 at 14:21
    
Urgh. No idea if that can work with SMB. Is NFS not an option on your NAS? (Better chances of that working, but you'll need someone who actually knows MySQL to tell you if that's safe.) –  Mat May 31 at 14:23

1 Answer 1

I managed to solve this, basically by the advice given by Mat.

The first valueable tip was to use ls -Ll to show information for the file the link references rather than for the link itself.

It is clear that the symlink should show the user (and group) mysql is using.

When mouting (in this case the cifs type) I first used

mount -t cifs //nas01/myshare /mnt/nas -o user=nasuser 

where I should have used

mount -t cifs //nas01/myshare /mnt/nas -o user=nasuser,uid=mysql,gid=mysql

This resolved the errorno: 13 because ls -Ll now showed mysql as owner and group for the symlinked directory.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.