1

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?

3
  • What is the output of mysql> show variables like 'innodb_data%';? Did you check the free space on disk?
    – quanta
    Oct 21, 2011 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.
    – ercpe
    Oct 21, 2011 at 17:20
  • How about the $ ls -lh /var/lib/mysql/ibdata1 and mysql> show variables like 'innodb_file_per_table';?
    – quanta
    Oct 21, 2011 at 17:25

1 Answer 1

2

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

1
  • 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.
    – ercpe
    Oct 22, 2011 at 7:01

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .