Index category_transaction_journal is corrupted

[bartekd123]

Hello,

I was able to get around it, but wondering if I am missing any data in the category_transaction_journal table, will it cause me any issues? I got the error that the index is corrupt, so I did check_tables, and it mentioned the table is corrupt. I ended up creating a new table with a different name, copying the data out of category_transaction_journal into that other table, then renaming the tables and I am back in business.

Is there a way to verify if this will cause me any other issues? I did a check tables against the rest of my tables/indexes and there are no glaring issues.

thanks in advance!

Full error from when I had it, it if is helpful:

Whoops! A database error occurred.
Unfortunately, this error was not recoverable :(. Firefly III broke. The error is:

SQLSTATE[HY000]: General error: 1712 Index category_transaction_journal is corrupted (Connection: mysql, SQL: select `transaction_groups`.`id` as `transaction_group_id`, `transaction_groups`.`user_id` as `user_id`, `transaction_groups`.`user_group_id` as `user_group_id`, `transaction_groups`.`created_at` as `created_at`, `transaction_groups`.`updated_at` as `updated_at`, `transaction_groups`.`title` as `transaction_group_title`, `transaction_groups`.`created_at` as `group_created_at`, `transaction_groups`.`updated_at` as `group_updated_at`, `transaction_journals`.`id` as `transaction_journal_id`, `transaction_journals`.`transaction_type_id`, `transaction_journals`.`description`, `transaction_journals`.`date`, `transaction_journals`.`order`, `transaction_types`.`type` as `transaction_type_type`, `source`.`id` as `source_transaction_id`, `source`.`account_id` as `source_account_id`, `source`.`reconciled`, `source`.`amount` as `amount`, `source`.`transaction_currency_id` as `currency_id`, `currency`.`code` as `currency_code`, `currency`.`name` as `currency_name`, `currency`.`symbol` as `currency_symbol`, `currency`.`decimal_places` as `currency_decimal_places`, `source`.`foreign_amount` as `foreign_amount`, `source`.`foreign_currency_id` as `foreign_currency_id`, `foreign_currency`.`code` as `foreign_currency_code`, `foreign_currency`.`name` as `foreign_currency_name`, `foreign_currency`.`symbol` as `foreign_currency_symbol`, `foreign_currency`.`decimal_places` as `foreign_currency_decimal_places`, `destination`.`account_id` as `destination_account_id`, `bills`.`id` as `bill_id`, `bills`.`name` as `bill_name`, `budgets`.`id` as `budget_id`, `budgets`.`name` as `budget_name`, `categories`.`id` as `category_id`, `categories`.`name` as `category_name`, `source_account`.`name` as `source_account_name`, `source_account`.`iban` as `source_account_iban`, `source_account_type`.`type` as `source_account_type`, `dest_account`.`name` as `destination_account_name`, `dest_account`.`iban` as `destination_account_iban`, `dest_account_type`.`type` as `destination_account_type` from `transaction_journals` left join `transaction_groups` on `transaction_journals`.`transaction_group_id` = `transaction_groups`.`id` left join `transactions` as `source` on `source`.`transaction_journal_id` = `transaction_journals`.`id` and `source`.`amount` < 0 left join `transactions` as `destination` on `destination`.`transaction_journal_id` = `transaction_journals`.`id` and `destination`.`amount` > 0 left join `transaction_types` on `transaction_types`.`id` = `transaction_journals`.`transaction_type_id` left join `transaction_currencies` as `currency` on `currency`.`id` = `source`.`transaction_currency_id` left join `transaction_currencies` as `foreign_currency` on `foreign_currency`.`id` = `source`.`foreign_currency_id` left join `bills` on `bills`.`id` = `transaction_journals`.`bill_id` left join `budget_transaction_journal` on `budget_transaction_journal`.`transaction_journal_id` = `transaction_journals`.`id` left join `budgets` on `budget_transaction_journal`.`budget_id` = `budgets`.`id` left join `category_transaction_journal` on `category_transaction_journal`.`transaction_journal_id` = `transaction_journals`.`id` left join `categories` on `category_transaction_journal`.`category_id` = `categories`.`id` left join `accounts` as `source_account` on `source_account`.`id` = `source`.`account_id` left join `account_types` as `source_account_type` on `source_account_type`.`id` = `source_account`.`account_type_id` left join `accounts` as `dest_account` on `dest_account`.`id` = `destination`.`account_id` left join `account_types` as `dest_account_type` on `dest_account_type`.`id` = `dest_account`.`account_type_id` where `transaction_journals`.`user_id` = 1 and `transaction_journals`.`user_id` is not null and `transaction_groups`.`deleted_at` is null and `transaction_journals`.`deleted_at` is null and `source`.`deleted_at` is null and `destination`.`deleted_at` is null and `transaction_journals`.`bill_id` = 11 and `transaction_journals`.`deleted_at` is null order by `transaction_journals`.`date` desc, `transaction_journals`.`order` asc, `transaction_journals`.`id` desc, `transaction_journals`.`description` desc, `source`.`amount` desc)

This error occured in file /var/www/html/vendor/laravel/framework/src/Illuminate/Database/Connection.php on line 822 with code HY000.

[JC5]

A broken index is usually no reason for data loss, if I recall my classes correctly. I have no clue what could have happened though.

I can only suggest a regular backup just in case.

[bartekd123]

Thanks. What is that table used for, if there is missing rows, will certain things break (reports, categories etc)?

The index itself is not only corrupt, but the table is too. Here is what I see on that old table (I renamed it to old, so I can copy the data out of it into the now working table)

MariaDB [fireflyiii]> check table old_category_transaction_journal;
+---------------------------------------------+-------+----------+--------------------------------------------------------------------------------------------------+
| Table                                       | Op    | Msg_type | Msg_text                                                                                         |
+---------------------------------------------+-------+----------+--------------------------------------------------------------------------------------------------+
| fireflyiii.old_category_transaction_journal | check | Warning  | InnoDB: Index category_transaction_journal_category_id_foreign is marked as corrupted            |
| fireflyiii.old_category_transaction_journal | check | Warning  | InnoDB: Index category_transaction_journal_transaction_journal_id_foreign is marked as corrupted |
| fireflyiii.old_category_transaction_journal | check | error    | Corrupt                                                                                          |
+---------------------------------------------+-------+----------+--------------------------------------------------------------------------------------------------+

[JC5]

It links transactions to categories, so you may notice transactions missing their category.

[bartekd123]

Thanks!