!Friendica Support
Hallo Profis, ich habe ein Problem mit meiner Instanz. In den Logs taucht folgender DB-Fehler auf:Index for table 'post-user' is corrupt; try to repair it
Ein CHECK TABLE `post-user`; in der MariaDB Konsole zeigt folgendes:
MariaDB [friendica]> CHECK TABLE `post-user`;
+---------------------+-------+----------+-----------------------------------------------------------+
| Table | Op | Msg_type | Msg_text |
+---------------------+-------+----------+-----------------------------------------------------------+
| friendica.post-user | check | Warning | InnoDB: The B-tree of index PRIMARY is corrupted. |
| friendica.post-user | check | Warning | InnoDB: Index uid_uri-id is marked as corrupted |
| friendica.post-user | check | Warning | InnoDB: Index uri-id is marked as corrupted |
| friendica.post-user | check | Warning | InnoDB: Index parent-uri-id is marked as corrupted |
| friendica.post-user | check | Warning | InnoDB: Index thr-parent-id is marked as corrupted |
| friendica.post-user | check | Warning | InnoDB: Index external-id is marked as corrupted |
| friendica.post-user | check | Warning | InnoDB: Index owner-id is marked as corrupted |
| friendica.post-user | check | Warning | InnoDB: Index author-id is marked as corrupted |
| friendica.post-user | check | Warning | InnoDB: Index causer-id is marked as corrupted |
| friendica.post-user | check | Warning | InnoDB: Index vid is marked as corrupted |
| friendica.post-user | check | Warning | InnoDB: Index contact-id is marked as corrupted |
| friendica.post-user | check | Warning | InnoDB: Index event-id is marked as corrupted |
| friendica.post-user | check | Warning | InnoDB: Index psid is marked as corrupted |
| friendica.post-user | check | Warning | InnoDB: Index author-id_uid is marked as corrupted |
| friendica.post-user | check | Warning | InnoDB: Index parent-uri-id_uid is marked as corrupted |
| friendica.post-user | check | Warning | InnoDB: Index uid_contactid is marked as corrupted |
| friendica.post-user | check | Warning | InnoDB: Index uid_unseen_contactid is marked as corrupted |
| friendica.post-user | check | Warning | InnoDB: Index uid_unseen is marked as corrupted |
| friendica.post-user | check | Warning | InnoDB: Index uid_hidden_uri-id is marked as corrupted |
| friendica.post-user | check | Warning | InnoDB: Index uid_wall_received is marked as corrupted |
| friendica.post-user | check | Warning | InnoDB: Index author-id_created is marked as corrupted |
| friendica.post-user | check | Warning | InnoDB: Index owner-id_created is marked as corrupted |
| friendica.post-user | check | Warning | InnoDB: Index replies-id is marked as corrupted |
| friendica.post-user | check | error | Corrupt |
+---------------------+-------+----------+-----------------------------------------------------------+
24 rows in set (57,683 sec)Wenn ich REPAIR TABLE `post-user`; kommt folgende Ausgabe:
MariaDB [friendica]> REPAIR TABLE `post-user`;
+---------------------+--------+----------+---------------------------------------------------------+
| Table | Op | Msg_type | Msg_text |
+---------------------+--------+----------+---------------------------------------------------------+
| friendica.post-user | repair | note | The storage engine for the table doesn't support repair |
+---------------------+--------+----------+---------------------------------------------------------+
1 row in set (1 min 53,258 sec)Ich habe keinen Plan mehr. Könnt ihr mich bitte Unterstützen?
Michael 🇺🇦
in reply to Tuxi (Backup) • • •Tuxi (Backup)
in reply to Michael 🇺🇦 • • •Trotzdem Danke. Vielleicht weiß ja jemand anderes noch ein Rat?
Tuxi (Backup)
in reply to Tuxi (Backup) • • •Es scheinen mehrere Tabellen corrupt zu sein. Ein
mysqlcheckzeigt folgendes:mysqlcheck -u root -p --check --databases friendica Enter password: friendica.2fa_app_specific_password OK friendica.2fa_recovery_codes OK friendica.2fa_trusted_browser OK friendica.account-suggestion OK friendica.account-user OK friendica.addon OK friendica.advancedcontentfilter_rules OK friendica.apcontact Warning : InnoDB: The B-tree of index PRIMARY is corrupted. Warning : InnoDB: Index uri-id is marked as corrupted Warning : InnoDB: Index addr is marked as corrupted Warning : InnoDB: Index alias is marked as corrupted Warning : InnoDB: Index followers is marked as corrupted Warning : InnoDB: Index baseurl is marked as corrupted Warning : InnoDB: Index gsid is marked as corrupted Warning : InnoDB: Index sharedinbox is marked as corrupted error : Corrupt friendica.application OK friendica.application-marker OK friendica.application-token OK friendica.arrived-activity note : The storage engine for the table doesn't support check friendica.attach OK friendica.auth_codes OK friendica.cache OK friendica.challenge OK friendica.channel OK friendica.check-full-text-search OK friendica.clients OK friendica.config OK friendica.contact Warning : InnoDB: The B-tree of index PRIMARY is corrupted. Warning : InnoDB: Index uid_name is marked as corrupted Warning : InnoDB: Index self_uid is marked as corrupted Warning : InnoDB: Index pending_uid is marked as corrupted Warning : InnoDB: Index blocked_uid is marked as corrupted Warning : InnoDB: Index uid_rel_network_poll is marked as corrupted Warning : InnoDB: Index uid_network_batch is marked as corrupted Warning : InnoDB: Index attag_uid is marked as corrupted Warning : InnoDB: Index network_uid_lastupdate is marked as corrupted Warning : InnoDB: Index uid_network_self_lastupdate is marked as corrupted Warning : InnoDB: Index uid_lastitem is marked as corrupted Warning : InnoDB: Index alias_uid is marked as corrupted Warning : InnoDB: Index addr_uid is marked as corrupted Warning : InnoDB: Index nurl_uid is marked as corrupted Warning : InnoDB: Index nick_uid is marked as corrupted Warning : InnoDB: Index baseurl is marked as corrupted Warning : InnoDB: Index batch_contact-type is marked as corrupted Warning : InnoDB: Index uid_contact-type is marked as corrupted Warning : InnoDB: Index uid_self_contact-type is marked as corrupted Warning : InnoDB: Index self_network_uid is marked as corrupted Warning : InnoDB: Index uri-id is marked as corrupted Warning : InnoDB: Index next-update is marked as corrupted Warning : InnoDB: Index local-data-next-update is marked as corrupted Warning : InnoDB: Index gsid_uid_failed is marked as corrupted error : CorruptEsko Arajärvi
in reply to Tuxi (Backup) • • •You can use ALTER TABLE tbl_name FORCE to perform a “null” alter operation that rebuilds the table. I have successfully repaired corrupted tables with this.
mariadb.com/kb/en/alter-table/…
Tuxi (Backup)
in reply to Esko Arajärvi • • •Thanks @Esko Arajärvi
Since I am not a database professional, I will ask to be on the safe side. The command is correctly
ALTER TABLE post-user FORCE;?Um, how dangerous is this execution? Will data be lost?
Esko Arajärvi
in reply to Tuxi (Backup) • • •You need to include the table name in back-ticks, because there is a dash in the name of the table.
ALTER TABLE `post-user` FORCE;
This should be completely safe to run, but as always, when manually tweaking things, take a backup beforehand, if possible.
Tuxi (Backup)
in reply to Esko Arajärvi • • •Thanks! :thumbs up:
Tuxi (Backup)
in reply to Esko Arajärvi • • •@Esko Arajärvi
Crap an
ALTER TABLE `post-user` FORCE;produces the following output:MariaDB [friendica]> ALTER TABLE `post-user` FORCE; ERROR 1177 (42000): Can't open tableEsko Arajärvi
in reply to Tuxi (Backup) • • •Damn. That sounds more serious. 🙁
I'm not sure where to start digging for the data next.
Esko Arajärvi
in reply to Tuxi (Backup) • • •Are you able to dump the table data with:
mysqldump –u user –p db_name table_name > output_file
Tuxi (Backup)
in reply to Esko Arajärvi • • •😔
mysqldump: Error 1030: Got error 1877 "Unknown error 1877" from storage engine InnoDB when dumping table `post-user` at row: 0Esko Arajärvi
in reply to Tuxi (Backup) • • •Esko Arajärvi
in reply to Tuxi (Backup) • • •Apparently, if all else fails, try these:
mariadb.com/kb/en/innodb-recov…
Tuxi (Backup)
Unknown parent • • •@Nordnick 🐘
Wie? *keinplan*
Dachte eigentlich schon, bis ich durch das Problem heute daraufgekommen bin, dass der Backup-Job unbemerkt schon seit einiger Zeit nicht mehr gelaufen ist. 🙄
Michael 🇺🇦
Unknown parent • • •Michael 🇺🇦
in reply to Tuxi (Backup) • • •Tuxi (Backup)
in reply to Michael 🇺🇦 • • •MariaDB [friendica]> OPTIMIZE TABLE `post-user`; +---------------------+----------+----------+-------------------------------------------------------------------+ | Table | Op | Msg_type | Msg_text | +---------------------+----------+----------+-------------------------------------------------------------------+ | friendica.post-user | optimize | note | Table does not support optimize, doing recreate + analyze instead | | friendica.post-user | optimize | error | Can't open table | | friendica.post-user | optimize | status | Operation failed | +---------------------+----------+----------+-------------------------------------------------------------------+ 3 rows in set, 1 warning (0,016 sec)Jerry on Friendica
in reply to Tuxi (Backup) • • •This happened to me on my instance and I tried everything to fix the corrupted indexes, using expert advice, and couldn't fix it. I finally restored from a backup, which took 12 hours. It happened to me because I made a change to Friendica and restarted it while everything was still running. I know this is not helping you in your current situation, but I want to understand what may have caused this to happen to you and to help other Admins.
Do you have a theory as to what brought this about?
Whenever I am making any changes to Friendica now, I take MariaDB and Apache2 down, and I make a complete backup of the database disk to another disk (sudo cp -rp /mnt/friendica_db_volume_nyc1_01/mariadb /mnt/friendica_backups/) because I never ever want to go through this again. I also take a disk snapshot on Digital Ocean of the external DB drive as well, and have the regular backup available. Total paranoia now.
Good luck. Keep us posted on how you overcome this and what you learn.
xy..
in reply to Tuxi (Backup) • • •Tuxi (Backup)
in reply to xy.. • • •Ich habe nichts gefunden.
xy..
in reply to Tuxi (Backup) • • •@Tuxi (Backup)
hevodata.com/learn/mysqlcheck/
Ich glaube wenn repair nicht ging hatte es bei mir drop und recreate oder so gemacht.. kann sein dass repair standardmäßig an ist. Weiss nicht..
xy..
in reply to Tuxi (Backup) • • •Tuxi (Backup)
in reply to xy.. • • •Danke. Leider aber:
The storage engine for the table doesn't support repairxy..
in reply to Tuxi (Backup) • • •xy..
in reply to Tuxi (Backup) • • •Kevin Honka
in reply to Tuxi (Backup) • • •Falls du da noch Hilfe benötigst:
1. Datenbank stoppen und backup von /var/lib/mysql machen
2. innodb_force_recovery auf 4 stellen. danach erlaubt mysql/mariadb keine Schreibzugriffe mehr dev.mysql.com/doc/refman/8.4/e…
3. DB wieder starten
4. SQL Dump der Datenbank ziehen.
5. innodb_force_recovery entfernen
6. Datenbank durchstarten
6. Datenbank löschen
6. SQL Dump importieren.
MySQL :: MySQL 8.4 Reference Manual :: 17.20.3 Forcing InnoDB Recovery
dev.mysql.comTuxi (Backup)
in reply to Kevin Honka • • •Danke. Werde ich mal durchlesen und testen.
Tuxi (Backup)
in reply to Kevin Honka • • •@Kevin Honka
Aufgrund des kaputten Indexes funktioniert leider auch kein
mysqldump. Mist.mysqldump: Error 1034: Index for table 'apcontact' is corrupt; try to repair it when dumping table `apcontact` at row: 1859034Ich werde es aber jetzt mit der
--forceOption versuchen.Johannes Brakensiek
in reply to Tuxi (Backup) • • •Tuxi (Backup)
in reply to Johannes Brakensiek • • •Habe auch das Gefühl, dass da mehr im argen ist.
Tuxi (Backup)
in reply to Tuxi (Backup) • • •Also folgender Stand:
Ich konnte gestern einen Dump der DB ziehen. Danach konnte ich seltsamerweise euch ein
mysqloptimizeerfolgreich und ohne Fehlermeldung ausführen. Ob das aminnodb_force_recovery 4liegt oder an was anderem, kann ich, ehrlich gesagt nicht sagen.Die Instanz lief danach auch für eine Zeit ohne Probleme. Allerdings sind dann nach so ca. 30 Minuten wieder die gleichen Fehlermeldungen im Friendica-Log aufgetaucht.
Aktuell läuft ein Testimport der gestern exportierten Datenbank. Wenn diese erfolgreich ist, dann hätte ich zumindest Daten auf einem Stand von gestern.
Allerdings frage ich mich, ob der mysqldump korrupt ist, oder nicht?
@Michael 🇺🇦
Frage: Wenn ich den Import erfolgreich in eine andere DB durchführen konnte, kann ich dann in der
local.config.phpden Namen der DB einfach ändern und es ist alles lauffähig? Oder ist der Name der DB auch noch irgendwo anderes hinterlegt?Michael 🇺🇦
in reply to Tuxi (Backup) • • •Tuxi (Backup)
in reply to Michael 🇺🇦 • • •Danke
Tuxi (Backup)
in reply to Tuxi (Backup) • • •Tuxi (Backup)
in reply to Tuxi (Backup) • • •Hallo Profis,
habe eine neue Datenbank erstellt, dort die Sicherung eingespielt, die ich Ende Januar für den Serverumzug auch verwendet habe. In der "local.config.php habe ich den Namen der Datenbank auf die neue geändert.
Trotzdem funktioniert die Instanz nicht und ich habe massenhaft DB Errors im friendical.og. Wieso funktioniert die Sicherung die vor ein paar Wochen für den umzug noch funktioniert hat aufeinmal nicht mehr. Das passt doch irgendwie nicht zusammen.
Auszug aus dem friendica.log:
Tuxi (Backup)
in reply to Tuxi (Backup) • • •Hallo Profis,
habe eine neue Datenbank erstellt, dort die Sicherung eingespielt, die ich Ende Januar für den Serverumzug auch verwendet habe. In der "local.config.php habe ich den Namen der Datenbank auf die neue geändert.
Trotzdem funktioniert die Instanz nicht und ich habe massenhaft DB Errors im friendical.og. Wieso funktioniert die Sicherung die vor ein paar Wochen für den umzug noch funktioniert hat aufeinmal nicht mehr. Das passt doch irgendwie nicht zusammen.
Auszug aus dem friendica.log:
2025-03-21T18:18:54Z app [ERROR]: DB Error {"code":1356,"error":"View 'friendicadb.account-user-view' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them","params":"SELECT `id`, `uid`, `pid` FROM `account-user-view` WHERE (`id` = 2467202) LIMIT 1"} - {"file":"Database.php","line":675,"function":"p","request-id":"67ddad8e2bdd7","stack":"Database::p (1494), Database::select (1379), Database::selectFirst (420), DBA::selectFirst (651), Contact::getPublicAndUserContactID (178), User::isBlocked (299), Contact::getVisitorByUrl (149), UserSession::setVisitorsContacts (318), Authentication::setForUser (182), Authentication::withSession (586), App::runFrontend (32)","uid":"2bc66a","process_id":4838} 2025-03-21T18:18:54Z app [ERROR]: DB Error {"code":1356,"error":"View 'friendicadb.account-user-view' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them","params":"SELECT `id`, `uid`, `pid` FROM `account-user-view` WHERE (`id` = 2468202) LIMIT 1"} - {"file":"Database.php","line":675,"function":"p","request-id":"67ddad8e2bdd7","stack":"Database::p (1494), Database::select (1379), Database::selectFirst (420), DBA::selectFirst (651), Contact::getPublicAndUserContactID (178), User::isBlocked (299), Contact::getVisitorByUrl (149), UserSession::setVisitorsContacts (318), Authentication::setForUser (182), Authentication::withSession (586), App::runFrontend (32)","uid":"2bc66a","process_id":4838} 2025-03-21T18:18:54Z worker [ERROR]: DB Error {"code":1356,"error":"View 'friendicadb.account-user-view' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them","params":"SELECT `platform`, `contact-type` FROM `account-user-view` WHERE (`uri-id` = 34862127) LIMIT 1"} - {"file":"Database.php","line":675,"function":"p","request-id":"67ddad8d549a0","stack":"Database::p (1494), Database::select (1379), Database::selectFirst (420), DBA::selectFirst (2038), Contact::getDefaultAvatar (2357), Contact::updateAvatar (2924), Contact::updateFromProbeArray (2704), Contact::updateFromProbe (32), UpdateContact::execute, Worker::execFunction (374), Worker::execute (109), Worker::processQueue (75)","uid":"c10296","process_id":4897} 2025-03-21T18:18:54Z worker [ERROR]: DB Error {"code":1356,"error":"View 'friendicadb.account-user-view' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them","params":"SELECT `platform`, `contact-type` FROM `account-user-view` WHERE (`uri-id` = 34862127) LIMIT 1"} - {"file":"Database.php","line":675,"function":"p","request-id":"67ddad8d6343c","stack":"Database::p (1494), Database::select (1379), Database::selectFirst (420), DBA::selectFirst (2038), Contact::getDefaultAvatar (2357), Contact::updateAvatar (2924), Contact::updateFromProbeArray (2704), Contact::updateFromProbe (2639), Contact::updateByIdIfNeeded (2654), Contact::updateByUrlIfNeeded (1763), Processor::processActivity (1691), Processor::fetchMissingActivity (34), FetchMissingActivity::execute, Worker::execFunction (374), Worker::execute (109)","uid":"00abbf","process_id":4900} 2025-03-21T18:18:54Z app [ERROR]: DB Error {"code":1356,"error":"View 'friendicadb.account-user-view' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them","params":"SELECT `id`, `uid`, `pid` FROM `account-user-view` WHERE (`id` = 4317477) LIMIT 1"} - {"file":"Database.php","line":675,"function":"p","request-id":"67ddad8e2bdd7","stack":"Database::p (1494), Database::select (1379), Database::selectFirst (420), DBA::selectFirst (651), Contact::getPublicAndUserContactID (178), User::isBlocked (299), Contact::getVisitorByUrl (149), UserSession::setVisitorsContacts (318), Authentication::setForUser (182), Authentication::withSession (586), App::runFrontend (32)","uid":"2bc66a","process_id":4838}Seltsamerweise sagt mir ein "mysqlcheck -u root -p --check --databases friendicadb" das alles in Ordnung wäre:
mysqlcheck -u root -p --check --databases friendicadb Enter password: friendicadb.2fa_app_specific_password OK friendicadb.2fa_recovery_codes OK friendicadb.2fa_trusted_browser OK friendicadb.account-suggestion OK friendicadb.account-user OK friendicadb.addon OK friendicadb.advancedcontentfilter_rules OK friendicadb.apcontact OK friendicadb.application OK friendicadb.application-marker OK friendicadb.application-token OK friendicadb.arrived-activity note : The storage engine for the table doesn't support check friendicadb.attach OK friendicadb.auth_codes OK friendicadb.cache OK friendicadb.challenge OK friendicadb.channel OK friendicadb.check-full-text-search OK friendicadb.clients OK friendicadb.config OK friendicadb.contact OK friendicadb.contact-relation OK friendicadb.conv OK friendicadb.conversation OK friendicadb.delayed-post OK friendicadb.delivery-queue OK friendicadb.diaspora-contact OK friendicadb.diaspora-interaction OK friendicadb.endpoint OK friendicadb.event OK friendicadb.fcontact OK friendicadb.fetch-entry OK friendicadb.fetched-activity note : The storage engine for the table doesn't support check friendicadb.fsuggest OK friendicadb.group OK friendicadb.group_member OK friendicadb.gserver OK friendicadb.gserver-tag OK friendicadb.hook OK friendicadb.host OK friendicadb.inbox-entry OK friendicadb.inbox-entry-receiver OK friendicadb.inbox-status OK friendicadb.intro OK friendicadb.item-uri OK friendicadb.key-value OK friendicadb.locks OK friendicadb.mail OK friendicadb.mailacct OK friendicadb.manage OK friendicadb.notification OK friendicadb.notify OK friendicadb.notify-threads OK friendicadb.oembed OK friendicadb.openwebauth-token OK friendicadb.parsed_url OK friendicadb.pconfig OK friendicadb.permissionset OK friendicadb.photo OK friendicadb.post OK friendicadb.post-activity OK friendicadb.post-category OK friendicadb.post-collection OK friendicadb.post-content OK friendicadb.post-counts OK friendicadb.post-delivery OK friendicadb.post-delivery-data OK friendicadb.post-engagement OK friendicadb.post-history OK friendicadb.post-link OK friendicadb.post-media OK friendicadb.post-origin OK friendicadb.post-question OK friendicadb.post-question-option OK friendicadb.post-searchindex OK friendicadb.post-tag OK friendicadb.post-thread OK friendicadb.post-thread-user OK friendicadb.post-user OK friendicadb.post-user-notification OK friendicadb.process OK friendicadb.processed-activity note : The storage engine for the table doesn't support check friendicadb.profile OK friendicadb.profile_check OK friendicadb.profile_field OK friendicadb.push_subscriber OK friendicadb.register OK friendicadb.report OK friendicadb.report-post OK friendicadb.report-rule OK friendicadb.search OK friendicadb.session OK friendicadb.storage OK friendicadb.subscription OK friendicadb.tag OK friendicadb.tokens OK friendicadb.user OK friendicadb.user-contact OK friendicadb.user-gserver OK friendicadb.userd OK friendicadb.verb OK friendicadb.worker-ipc note : The storage engine for the table doesn't support check friendicadb.workerqueue OKTuxi (Backup)
in reply to Tuxi (Backup) • • •