Thursday, January 08, 2009

MYSQL table partitioning ERROR 1 (HY000): Can't create/write to file '/tmp/root-tmp.xxx/files/xxx' (Errcode: 13)



After I read Inside MySQL 5.1 A DBA’s Perspective, so I created table partitionning and found error... 

mysql> CREATE TABLE part_tab
    -> ( c1 int default NULL,
    -> c2 varchar(30) default NULL,
    -> c3 date default NULL
    -> ) engine=myisam
    -> PARTITION BY RANGE (year(c3)) (PARTITION p0 VALUES LESS THAN (1995),
    -> PARTITION p1 VALUES LESS THAN (1996) , PARTITION p2 VALUES LESS THAN (1997) ,
    -> PARTITION p3 VALUES LESS THAN (1998) , PARTITION p4 VALUES LESS THAN (1999) ,
    -> PARTITION p5 VALUES LESS THAN (2000) , PARTITION p6 VALUES LESS THAN (2001) ,
    -> PARTITION p7 VALUES LESS THAN (2002) , PARTITION p8 VALUES LESS THAN (2003) ,
    -> PARTITION p9 VALUES LESS THAN (2004) , PARTITION p10 VALUES LESS THAN (2010),
    -> PARTITION p11 VALUES LESS THAN MAXVALUE );
ERROR 1 (HY000): Can't create/write to file '/tmp/root-tmp.2661f66/files/psyC3LzHV' (Errcode: 13)

this error should show about something wrong about permission on OS that PATH.

this PATH on tmpdir variable.

So, change permission 777 to that PATH
# chmod 777 -R /tmp/root-tmp.2661f66
After that I can created table partitioning...

mysql> CREATE TABLE part_tab
    -> ( c1 int default NULL,
    -> c2 varchar(30) default NULL,
    -> c3 date default NULL
    -> ) engine=myisam
    -> PARTITION BY RANGE (year(c3)) (PARTITION p0 VALUES LESS THAN (1995),
    -> PARTITION p1 VALUES LESS THAN (1996) , PARTITION p2 VALUES LESS THAN (1997) ,
    -> PARTITION p3 VALUES LESS THAN (1998) , PARTITION p4 VALUES LESS THAN (1999) ,
    -> PARTITION p5 VALUES LESS THAN (2000) , PARTITION p6 VALUES LESS THAN (2001) ,
    -> PARTITION p7 VALUES LESS THAN (2002) , PARTITION p8 VALUES LESS THAN (2003) ,
    -> PARTITION p9 VALUES LESS THAN (2004) , PARTITION p10 VALUES LESS THAN (2010),
    -> PARTITION p11 VALUES LESS THAN MAXVALUE );
Query OK, 0 rows affected (0.01 sec)

*** Another way that can change tmpdir = /tmp

How?

mysql> show variables like 'tmpdir';

| tmpdir        | /tmp/root-tmp.2661f66/files | 

stopped mysqld and modified /etc/my.cnf (set tmpdir=/tmp)... and then started again

[mysqld]
tmpdir=/tmp

mysql> show variables like 'tmpdir';

| tmpdir        | /tmp | 

No comments: