Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

Every few weeks, i receive the message The table 'ttrss_users' is full from an installation of http://tt-rss.org/redmine/ (a RSS reader application written in PHP).

It's always the users table (InnoDB), which has the following layout:

id              int(11)
login           varchar(120)
pwd_hash        varchar(250)
last_login      datetime
access_level    int(11)
theme_id        int(11)
email           varchar(250)
full_name       varchar(250)
email_digest    tinyint(1)
last_digest_sent datetime
created         datetime
twitter_oauth   longtext

The query causing the error is: UPDATE ttrss_users SET last_login = NOW() WHERE id = x and affects only this table (no other table or database on this server).

The only solution i found is to restart the server. Changing MAX_ROWS and AVG_ROW_LENGTH via ALTER TABLE seems to affect MyISAM tables only.

Any ideas?

share|improve this question
    
What is the output of mysql> show variables like 'innodb_data%';? Did you check the free space on disk? –  quanta Oct 21 '11 at 17:15
    
innodb_data_file_path ibdata1:10M:autoextend:max:1024M innodb_data_home_dir The /var/lib/mysql disk has > 10GB free. As i sad: it's the only affected table. –  Johann Oct 21 '11 at 17:20
    
How about the $ ls -lh /var/lib/mysql/ibdata1 and mysql> show variables like 'innodb_file_per_table';? –  quanta Oct 21 '11 at 17:25
add comment

1 Answer

up vote 1 down vote accepted

The problem lies right in innodb_data_file_path.

According to your comment: innodb_data_file_path = ibdata1:10M:autoextend:max:1024M

The file ibdata1 houses four type of data

  1. Table Data

  2. Table Indexes

  3. MVCC (Multiversioning Concurrency Control) Data

  4. Table Metadata

There may simply be no space to write MVCC data around the old values for the row in ttrss_users that needs to be updated. Try removing the size restriction on the size of ibdata1

  • Step 01) Change the line in /etc/my.cnf from this

    innodb_data_file_path = ibdata1:10M:autoextend:max:1024M

to this

`innodb_data_file_path = ibdata1:10M:autoextend`
  • Step 02) service mysql restart

  • Step 03) Try your UPDATE statement

Give it a Try !!!

UPDATE 2011-10-21 17:03 EDT

You may want to cleanup ibdata1 and keep InnoDB tables outside of ibdata1

share|improve this answer
    
Ok, i removed the restriction to 1G because the ibdata1 file has exactly the size of 1024M. But the restriction would affect the other innodb tables as well and not just this table, right? I already had innodb_file_per_table set (as suggested in the linked question), but i thought this is a per-database setting... Since the database is running now, i cant say whether this resolved the problem or not. –  Johann Oct 22 '11 at 7:01
add comment

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.