List:General Discussion« Previous MessageNext Message »
From:Ananda Kumar Date:November 10 2009 8:54am
Subject:Re: Temp file issues on Ubuntu 9.10
View as plain text  
does the table ur trying to delete has any primary-foreign key relation.

do "show create table table_name\G"

Also instead of delete, use truncate, i t will be faster.

regards
anandkl

On Tue, Nov 10, 2009 at 3:19 AM, Sebastiaan van Erk <sebster@stripped>wrote:

> Hi,
>
> I followed the instructions but still get:
>
> mysql> delete from mytable;
>
> ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key
> constraint fails (temp file operation failed)
> mysql>
>
> Regards,
> Sebastiaan
>
>
> Krishna Chandra Prajapati wrote:
>
>> Hi Sebastiaan,
>>
>> Steps to fix the issue.
>> 1. Do proper shutdown of mysql server.
>> 2. Check the error log file that mysql server is shutdown properly.
>> 3. Remove log files (ib_logfile0 and ib_logfile1).
>> 4. Start mysql server (The log files will be created automatically)
>>
>> Thanks,
>> Krishna
>>
>>  On Sun, Nov 8, 2009 at 2:51 PM, Sebastiaan van Erk
> <sebster@stripped<mailto:
>> sebster@stripped>> wrote:
>>
>>    Hi all,
>>
>>    I just recently upgraded to Ubuntu 9.10, but now I'm having all
>>    sorts of temp file problems. For example, when I try to delete a row
>>    and violate a contraint I get:
>>
>>    ERROR 1451 (23000): Cannot delete or update a parent row: a foreign
>>    key constraint fails (temp file operation failed)
>>
>>    Instead of telling me which constraint is violated, it tells me the
>>    temp file creation failed. I have no reason why it failed, I don't
>>    see any error messages in the log.
>>
>>    To solve this problem I tried to make a tmpfs partition (I thought,
>>    maybe somehow my using ext4 might be a problem):
>>
>>    mkdir /tmpfs
>>    mount -t tmpfs -o size=1g tmpfs /tmpfs
>>    mkdir /tmpfs/mysql
>>    chown mysql:mysql
>>
>>    and changed the tmpdir in the mysql config to /tmpfs/mysql
>>
>>    tmpdir=/tmpfs/mysql
>>
>>    But then mysql fails on startup:
>>
>>    /usr/sbin/mysqld: Can't create/write to file '/tmpfs/mysql/ibGgjPv7'
>>    (Errcode: 13)
>>    091108 10:12:46  InnoDB: Error: unable to create temporary file;
>>    errno: 13
>>    091108 10:12:46 [ERROR] Plugin 'InnoDB' init function returned error.
>>    091108 10:12:46 [ERROR] Plugin 'InnoDB' registration as a STORAGE
>>    ENGINE failed.
>>
>>    I checked error code 13, which is permission denied, but I don't
>>    understand this, because if I change tmpdir to /tmp/mysql it does
>>    work, and I have:
>>
>>    $ ls -ld /tmp/mysql
>>    drwxr-xr-x 2 mysql mysql 4096 2009-11-08 10:14 /tmp/mysql
>>
>>    $ ls -ld /tmpfs/mysql
>>    drwxr-xr-x 2 mysql mysql 40 2009-11-08 10:12 /tmpfs/mysql
>>
>>    So I don't see the difference....
>>
>>    Has anyone encountered similar problems, or know what's going on here?
>>
>>    Best regards,
>>    Sebastiaan
>>
>>
>>

Thread
Temp file issues on Ubuntu 9.10Sebastiaan van Erk8 Nov
  • Re: Temp file issues on Ubuntu 9.10John List8 Nov
    • Re: Temp file issues on Ubuntu 9.10Sebastiaan van Erk8 Nov
      • Re: Temp file issues on Ubuntu 9.10Johnny Withers9 Nov
        • Re: Temp file issues on Ubuntu 9.10Sebastiaan van Erk9 Nov
  • Re: Temp file issues on Ubuntu 9.10Krishna Chandra Prajapati9 Nov
    • Re: Temp file issues on Ubuntu 9.10Sebastiaan van Erk9 Nov
      • Re: Temp file issues on Ubuntu 9.10Ananda Kumar10 Nov
        • Re: Temp file issues on Ubuntu 9.10Sebastiaan van Erk10 Nov