[solved] MySQL database is full

[Edison]

Hello,

I'm using OTRS 3.1.10 with MySQL as database (running on CentOS 5).
I have the problem that fetching email will slow down and stop after some mails completely.
So I checked the free space of my database and checked that htere is no free space.

Code: Select all

mysql> SELECT table_schema "otrs",
    -> sum( data_length + index_length ) / 1024 / 1024 "Data Base Size in MB",
    -> sum( data_free )/ 1024 / 1024 "Free Space in MB"
    -> FROM information_schema.TABLES
    -> GROUP BY table_schema;
+--------------------+----------------------+------------------+
| otrs               | Data Base Size in MB | Free Space in MB |
+--------------------+----------------------+------------------+
| information_schema |           0.00390625 |       0.00000000 |
| otrs               |         359.61758518 |       5.02113342 |
+--------------------+----------------------+------------------+
2 rows in set (0.01 sec)

mysql>

Within the file system there is enough free space (> 7 GB).

My question is now,
how could I get more free space for my otrs database?

Any help is welcome
Wolfgang

[wurzel]

Hi,

I think you need more storage. Ask your database admin and storage admin for more.

Florian

[crythias]

Code: Select all

df -h

might be of use.

[Edison]

Hi,

I think there is enough free space, but maybe I'm wrong.

Code: Select all

[root@otrs ~]# df -h
Filesystem            Size  Used Avail Use% Mounted on
/dev/mapper/VolGroup00-LogVol00
                       15G  7.1G  7.1G  50% /
/dev/sdb1              99M   41M   53M  44% /boot
tmpfs                 2.0G     0  2.0G   0% /dev/shm
[root@otrs ~]#

Thank You
Wolfgang

[wurzel]

Hi,

perhaps your database tables are limited (fixed size?), not the storage.

Florian

[Edison]

Hello,

the database was created by OTRS installation scripts (and as far as I know there is no fixed size definied).
But to clarify, how could I check if the database has a fixed size?

I checked following (I doesn't know if it's right):

Code: Select all

mysql> show table status from otrs;
+------------------------------+--------+---------+------------+--------+----------------+-------------+-------------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-----------------+----------+---------------             -+---------+
| Name                         | Engine | Version | Row_format | Rows   | Avg_row_length | Data_length | Max_data_length   | Index_length | Data_free | Auto_increment | Create_time         | Update_time         | Check_time          | Collation       | Checksum | Create_options              | Comment |
+------------------------------+--------+---------+------------+--------+----------------+-------------+-------------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-----------------+----------+---------------             -+---------+
| article                      | MyISAM |      10 | Dynamic    | 120756 |           1271 |   153527284 |   281474976710655 |     13853696 |         0 |         123253 | 2012-09-24 09:52:47 | 2014-08-15 11:44:44 | 2014-08-14 11:40:07 | utf8_general_ci |     NULL |                             |         |

And all "Data_length" will not reach "Max_data_length".

Any other ideas are welcome, because I have absolute no idea what's wrong here.

Wolfgang

[crythias]

With your original problem: not all emails are parsed, are you receiving any system log errors (within otrs, /var/log/syslog, /var/log/messages, etc.) that indicate what you're looking at is the problem. Or, in other words, how did you come to the conclusion that the mysql database size was the issue? For instance, if you added a 5MB attachment to a note, would that crash OTRS at this time?

[Edison]

Hello,

I started the otrs.PostMasterMailbox.pl process with debug enabled and checked that the receiving part works well.
The first 2 messages are fetched and stored without troubles.
When fetching the 3 message from mail server the problem occurs.
Find here a summary of the debugging:

Code: Select all

fetching message # 2
[...cpan-lib/Net/IMAP/Simple.pm line 1143 in sub _cmd_ok] 3 OK FETCH completed.\r\n
. . . 
[...cpan-lib/Net/IMAP/Simple.pm line 1143 in sub _cmd_ok] 4 OK STORE completed.\r\n
. . .
fetching message # 3
[...cpan-lib/Net/IMAP/Simple.pm line 1143 in sub _cmd_ok] 6 OK FETCH completed.\r\n
Here the process will stay for ever

At the step where the message should be stored, the process will "hang" (no further action).
I checked all logs, but no errors.

That's the point where I think that the no free space is causing the troubles (but maybe I'm wrong here).

Wolfgang

[crythias]

http://stackoverflow.com/questions/1943 ... l-database
http://stackoverflow.com/questions/1930 ... 0#19301420

From what I understand, think of what you're looking at as similar to adding books to a bookshelf. Let's say an extent(bookspace) is worth 5MB. If you look at your bookshelf and it has a book size remaining part to it, you can fill it, and if you need more, the bookend moves to accommodate.

[crythias]

I'd be interested in top, too.

[crythias]

also browse the mailbox and see if it's a super large attachment

[Edison]

Hello,

it's solved, I found the trouble maker.
All mails from a "special" user will cause the troubles.
After removing this mails, all worked well as before.
I have no idea what's wrong with this emails (no attachments, no bad contens).

Many Thanks to all helping here
Wolfgang