Developers Club geek daily blog

1 year, 2 months ago
Recent series of articles ("Instruction of the evangelist of PostgreSQL: we criticize MySQL competently" 1,2,3) hooked for live.

So it turned out that my command inherited, isteorichesk the developed system, with 300+ objects where MySQL acts as one of key components of system. On some objects replication is also used. Software the using MySQL from the third-party developer.

The majority of objects is in regions, remote from "homo sapiens" (mountains, the steppe). Some objects are in ~ 200 km from the next settlement. Interruptions in power supply on these objects, business quite normal and regular. UPSes very much help out, but sometimes and they are not able to rescue from long blackout. And most often from a series of blackouts. That is the UPS was not charged yet, the equipment is already included and works, writes data to a DB, and here the EDS begins to vanish and appear, and again to vanish. Systems fall.
To MySQL upgrade to version 5.6.23, in a month it was necessary to recover manually two-three DB. Now it is necessary to recover less often, but all the same it is necessary. Since August recovered only two DB.

After one of the articles kaamos, we began to test 5.6.26 and testing showed that this version is even more hardy. However conditions of the websites, we cannot fully simulate (about 20 types of the websites). A loading profile on all these types different, though the DB model one.

So, key condition of a problem:
We have tables with foreign keys and ON DELETE CASCADE ON UPDATE CASCADE, on some tables which referentsirutsya on above-named, triggers are installed.

Quite perhaps, is a bad design of a data model for MySQL. Why? Only today came across the following statement:

The InnoDB engine is fully ACID compliant, but fails the standard definition of consistency when using a combination of InnoDB, foreign keys with cascading actions and triggers. This is the result of triggers being implemented at MySQL's SQL layer and foreign keys being implemented at the InnoDB Storage Engine level.

If this statement is right, then the letter 'C' from ACID, in our case is not guaranteed at all.

And then you should not be surprised to type error messages:
[ERROR] of Table./database/table has no primary key in InnoDB data dictionary, but has one in MySQL!

Also probably you should not hope for 100% for consistency at replication.

Perhaps this problem is solved in 5.7 and as soon as it appears in package base of our distribution kit, we will begin to update systems and we will happily forget about a problem.

However, how many still surprises the multi-tier architecture of MySQL conceals in itself?

This article is a translation of the original post at habrahabr.ru/post/270179/
If you have any questions regarding the material covered in the article above, please, contact the original author of the post.
If you have any complaints about this article or you want this article to be deleted, please, drop an email here: sysmagazine.com@gmail.com.

We believe that the knowledge, which is available at the most popular Russian IT blog habrahabr.ru, should be accessed by everyone, even though it is poorly translated.
Shared knowledge makes the world better.
Best wishes.

comments powered by Disqus