eventual bug report for smf 1.1.12 - database data loss

[serge M]

I im running - since 3 years and with proud - a smf forum based on smf 1.1.12, hosted on a mysql 4.0 at
german 1und1 servers.

On 19th of december i faced a loss of more than 20 thousand postings after a table error 127. I have
posted about this in the support area of the community:

http://www.simplemachines.org/community/index.php?topic=413852.0

As of course not only for me as the administrator of the forum, but also for you as the developer of the
platform it will be helpful to know the reasons for this enormous loss of data, I have asked the german
host about the reasons for this, as I - so far - can exclude the possibilities of a third party manipulation.

I have, after a long long argument with them received the following information (the correspondence is
in german, if you want I can send you the original quote, but I translated it into english):

We can only confirm, that in the regarding time the server was not available and had to be re-started.
During a re-start of a server, normally, no data shall be lost. Further, all clients hosted at that server
should have faced a loss of data, but we had no further reports about the loss of data. 19th of december,
at 03.43 the server and all databases were back online.

We can only presume, that one of the scripts you are using has deleted the data in question in the
moment of restarting the server.

So, what they say, is simply that there is a problem with your script, which for me personally is rubbish
and only they want to save their ass. As I - anyhow - can not exclude the possibility of a bug that
caused the loss of data, I hereby inform you about this. My intention is only to help preventing things
like that for the future and eventually to proove that my host is wrong and it is their fault.

Therefore, I would very very much ask you for your comment, and if possible - a short statement prooving
they are wrong.

here are the reports I got directly after the problem occured:

Błąd bazy danych: Got error 127 from table handler
Plik: /homepages/12/d207288248/htdocs/jcpmain/forum/Sources/Display.php
Linia: 160

Błąd bazy danych:
Plik: /homepages/12/d207288248/htdocs/jcpmain/forum/Sources/News.php
Linia: 612

2: mysql_query() [<a href='function.mysql-query'>function.mysql-query</a>]: Unable to save result set
Plik: /homepages/12/d207288248/htdocs/jcpmain/forum/Sources/Subs.php
Linia: 321

Błąd bazy danych:
Plik: /homepages/12/d207288248/htdocs/jcpmain/forum/Sources/Display.php
Linia: 682

2: mysql_query() [<a href='function.mysql-query'>function.mysql-query</a>]: Unable to save result set
Plik: /homepages/12/d207288248/htdocs/jcpmain/forum/Sources/Subs.php
Linia: 321

and so on and so on ...

please let me know, if you need/wish any additional information and thank you a lot.

[Illori]

if this was a common issue it would have been reported many times, i would stick to your current thread for support on this issue. most issues related to the handling of the database and the contents of it would be your responsibility to back it up and your host to make sure it works correctly. randomly blaming smf for a one time issue would not tell us why it happened and how to prevent it in the future.

[serge M]

Illory, thank you a lot for the words.

In general, I completely agree with you, but what more shall the thread at the support forum do for me ?

I am not blaming SMF, it is my host who is. I do think, that it should be a matter of honour to supply me
with arguments to fight this "easy blaming" of one of the biggest hosting companies in europe. I shall
do everything to proove them they are wrong and will fight for this not beeing happening to others.

we are all depending on hosts, we should not let them do whatever they want with us.

another item is, that there is a small possibility that they may be right. I dont consider this for good,
but we should at least check it.

I have 1600 users on my forum and they all like SMF. SMF is completely uncommon in the country I am
using it - poland. 90 percent of boards there are phpBB. It was my decision to go for SMF and I was
critized for that decision. The forum has prooven, that the decision was right, after three years
there is nobody who is not happy with it.

But my users want an explaination why more than 20 thousand posts have disappeared.
If I do not get a proper argument from your side, I will have to translate the hosts statement
into polish and publish it, my users expect a final report and they have any right to do so.

If the final version will be "it most probably was a faulty SMF script" well, then guess
what. It would not be a good marketing move for SMF.

When it comes to backups, well good point. I understand it is my responsibility, I understood it from the
very beginning and I made regular backups. even still have them. the problem is, they are worthless,
just have a look:

http://www.simplemachines.org/community/index.php?topic=414073.0

well, THIS IS a bug that you should fix. for three years I was pretty sure I am safe and woke up with
nothing and a bunch of gzip-files that are worth nothing.

[Illori]

can you ask your host for proof that this issue is related to smf?

[serge M]

My correspondence with them in this matter contains 18 mails and 2 letters. In the beginning they did not want
to say anything about the reasons, so I even reported the case to the police (german law: who deletes or
manipulates data will be sentenced to money fine or prison up to 2 years) and the final version, received this
morning is what I just translated to you.

Of course i again asked them to name me the exact reasons for the data-loss, but frankly I have not much hope
to receive some proper information from their side.

It would be helpful, if one of you could give me a proper argumentation, why it is not likely (or better impossible)
that SMF would delete parts of the DB by itself. I am quite helpless in that.

[Illori]

for further information http://www.simplemachines.org/community/index.php?topic=413852

[Norv]

Błąd bazy danych: Got error 127 from table handler
Plik: /homepages/12/d207288248/htdocs/jcpmain/forum/Sources/Display.php
Linia: 160

Błąd bazy danych:
Plik: /homepages/12/d207288248/htdocs/jcpmain/forum/Sources/News.php
Linia: 612

2: mysql_query() [<a href='function.mysql-query'>function.mysql-query</a>]: Unable to save result set
Plik: /homepages/12/d207288248/htdocs/jcpmain/forum/Sources/Subs.php
Linia: 321

Błąd bazy danych:
Plik: /homepages/12/d207288248/htdocs/jcpmain/forum/Sources/Display.php
Linia: 682

2: mysql_query() [<a href='function.mysql-query'>function.mysql-query</a>]: Unable to save result set
Plik: /homepages/12/d207288248/htdocs/jcpmain/forum/Sources/Subs.php
Linia: 321

These errors are related to the MySQL server - and generally mean a problem of the database itself. Not SMF's, not a deletion performed by SMF for some reason. (which doesn't just happen anyway).
It appears that the tables were corrupted. Why were your host's MySQL servers restarted?

Please see for example (maybe not the best examples, I'm sure there could be more issues with MySQL actually - at that version):
http://bugs.php.net/bug.php?id=16906
http://forums.mysql.com/read.php?21,42839,42839#msg-42839

You may want to try running "repair" on tables, in case you didn't yet, or have your host run myisamchk.

Please note also that MySQL 4.0 is really old. You may want to suggest to your host to take into account upgrading it.

Further, all clients hosted at that server should have faced a loss of data, but we had no further reports about the loss of data. 19th of december, at 03.43 the server and all databases were back online.

Not necessarily, I'm afraid. If, for example, the server process is interrupted exactly when it writes to the database (i.e. by a crash of the database server) , data corruption may occur.

[Arantor]

Firstly, error 127 is a failure from the underlying data engine, so the MyISAM table handler is reporting data corruption in the tables. Prior to 4.1 there were issues with data corruption quasi-frequently in MyISAM, under certain load conditions, even without restarts or changes - just specific loads caused corruption.

It does happen post 4.1, though it's much more rare now, but corruption in MyISAM tables is one of the reasons MySQL is moving to InnoDB by default for 5.5; InnoDB is naturally more resilient by design, partly because it handles things at a row level rather than a table level and partly because of the way it creates pages rather than monolithic files.

SMF is almost certainly not the cause here; it is issues with MySQL ultimately.

[serge M]

Thank you so much for your effort and help.

It appears that the tables were corrupted. Why were your host's MySQL servers restarted?

Please see for example (maybe not the best examples, I'm sure there could be more issues with MySQL actually - at that version):
http://bugs.php.net/bug.php?id=16906
http://forums.mysql.com/read.php?21,42839,42839#msg-42839

You may want to try running "repair" on tables, in case you didn't yet, or have your host run myisamchk.

they informed me, that the server stopped replying for some reasons and had to be restarded.

of course, the first thing I did was running "repair" as the forum did not respond to the corrupted database.

I dont consider this problem as a bug, but as the hosts or a MySql problem. Although, I made backups thru the
admin panel, which were not executed properly by the direct function. This is definitively a bug and should be
revised by you. I propose, either to fix it or to delete the backup function completely from the admin panel
and informing the administrators about making direct backups from the database.

[Aleksi "Lex" Kilpinen]

The backup failing when reaching a certain critical size is actually a known limitation, rather than a bug, nut you are right - and it has been discussed that something should be done about it.

[Norv]

I agree, the backup function from admin panel should at least try to warn that it has limitations, or be revised one way or the other. It's meant for small databases, anything more than that should use other methods to back up.