Replication is crashing the whole server, virtual generated is a possible reason

MySQL & MariaDB MariaDB Server & Utilities
1

Hello
I have a simple master-slave replication set on the server that’s being used for Mautic only (like google analytics, I guess, I just installed it because “it is needed”). The server stopped responding today and I think it had something to do with the memory leaks (that apparently are occurring every time the slave gets restarted). In the error logs I can see a lot of lines like:

InnoDB: Assertion failure in file/build/mariadb-10.3-7fJlau/mariadb-10.3-10.3.38/storage/innobase/row/row0ins.cc line 221
and some like:
2023-03-08 15:59:49 10 [ERROR] InnoDB: Record in index mt_generated_email_domain of table mautic.mt_leads was not found on update: TUPLE (info_bits=0, 2 fields): {NULL,[8] <(0x000000000000E03C)} at: COMPACT RECORD(info_bits=0, 2 fields): {NULL,[8] ;(0x000000000000E03B)}

I’ve managed to find the bug report on jira (MDEV-27145) and the comment made by Miguel P at 2023-01-20 23:32. According to him mysql is going crazy because of the index on the generated column. I’ve checked the table and there is one:

generated_email_domain ![Index] varchar(255) utf8mb4_unicode_ci Yes NULL (DC2Type:generated) VIRTUAL GENERATED

Another thing, that’s making me worried is that the master’s table has got next autoindex set to 0. The last id is 61876, so I guess the problem with the full memory finally showed up at that moment. The first record was added on 13th of Feb, and the first assert failed occurred on 15th of Feb (~3000 records between first insert and the first error report).

So:

  1. Is it safe for me to change the autoindex on the master to the correct value?

  2. I’ve never heard about virtual generated columns, so I’m not sure what I should do with that column? Do I need to create a trigger that will do the same code as the one that’s already attached?

2

Hello @januzi,
I would run a REPAIR TABLE on the table in question to fix any secondary index issues.
Yes, you can ALTER TABLE and fix the auto-increment value.
VIRTUAL COLUMNS were added in MySQL 8. You don’t need any triggers. VC’s manage themselves. I suggested reading the MySQL manual to get a grasp on what VC’s are and what they can do.

3

I’ve ran check, repair and optimize on that table (at one point the error message pointed out that the index is bugged out and is bigger by one element). Unfortunately it didn’t help. The check shows “ok”, but the thing is that it would work correctly for a few minutes, or for an hour, and then error log in the secondary mysql instance would fill up with dozens of error messages like “InnoDB: Failing assertion: !cursor->index->is_committed()”. At one point there were more than 10 restarts of the secondary instance of mysql, because it would get into failed assertion loop.

So, if I replace the virtual column with the real content it won’t change how the php scripts are seeing the data, except for that the data will take real space on the disk? If that’s so, then I’m more than willing to “pay” with the disk space for the stable MariaDB.

4

Correct. But then you probably need to alter your PHP scripts so that they INSERT/UPDATE that column with regular application processing. For instance, if the virtual column was previously doing something simple like “col2 * 4.4”, you now need to add PHP code which, on INSERT, explicitly inserts the calculated value. I would advise against doing this with a trigger, as triggers introduce additional processing and potential locking issues.

Have you done a complete table rebuild?

ALTER TABLE foo ENGINE=InnoDB

If you alter an innodb table to itself, this forces a complete table-disk-index rebuild. If you do that and still have these issues, then yes, you’ve got a fine and dandy bug on your hands.

You appear to be on a older MariaDB version. Is upgrading out of the question? MariaDB 10.9/10.10 should be easy paths to upgrade.

5

Yep, I’ve tried the rebuild without any success.
So, I’ve upgraded the MariaDB to 10.9 (which took some time, and error hunting). I had to stop mautic and cron job, get the database dump and re-import it to the secondary mysql, because there were just too many differences between tables. Right now I’m sitting and waiting for something bad to happen. If there won’t be any errors in the next two-three days, then it should probably mean that the everything is running as it should. Well … until the next server crash that is.

Edit: 10.03, so far so good. No error messages on each of the instances and replication is still going on. No memory leaks either.

Edit2: Still no errors and the memory usage isn’t going up like crazy.

I just wonder, what kind of new bugs were introduced in 10.9 :wink: