• MySQL知识库 :: configuration
  • Why am I getting an error message that says the server can't create or write file?

  • Discussion

    When executing an SQL statement, if the server returns an error message that says something like "Can't create/write to file '\\sqla3fe_0.ism'," it means that the server can't create a temporary file in the temporary directory. When SQL statements like the SELECT statement are executed, a temporary table is generated--this is the results set. If there's a problem with the temporary directory, temporary tables cannnot be created and therefore results sets aren't possible.

    A problem with the temporary directory could be because the temporary directory does not exist. It could also be because the system user (i.e., mysql) does not have privileges necessary to allow it to write to the temporary directory. It could even be that the file system (i.e., the hard drive) where the temporary directory is located is full or has some other system problem. If there was an error code with the error message, you can check its meaning with the perror utility by entering the following from the command-line:

    perror 28

Error code  28:  No space left on device

    Solution

    The first step to solving this problem is to determine the cause. First run the following SQL statement to determine the location of the temporary directory:

       SHOW VARIABLES LIKE 'tmpdir';

    Once you know the name of the temporary directory, you can investigate the directory's file system level privileges and ownership and the other possibilities mentioned above. If you need to set the temporary directory to a different directory, you can use the --tmpdir option, with the path you want as the value, when starting the server. Instead, you can put this option in the configuration file (my.cnf or my.ini, depending on your system) for the server. Just enter something like the following to the file:

       [mysqld]
   tmpdir = /tmp

    Starting in version 4.1 of MySQL, you can specify multiple temporary directories. These will be used by the server as needed, in no particular order. Just list them with the --tmpdir option in colon-separated list on Unix servers and a semi-colon separated list on Windows servers.