Home / server / Questions / 323694
Accepted
ercpe
Asked: 2011-10-22 08:52:43 +0800 CST

'Table is full' error on table with 3 rows

  • 772

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?

mysql innodb

1 Answers

  1. Best Answer

    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

    • 2