Bitrix and MariaDB update to the latest stable version

Good day, dear Habrovchane! Allow me to introduce myself, Alexander. System administrator of one small but proud WEB-studio. We really want everything to work quickly, safely and with fresh software. To do this, we even raised a bunch of nagios + PhantomJS on the intra-office computer and every 30 minutes we check the speed of loading pages. Under the terms of service, we also follow the updates 1C-Bitrix and regularly install them. And then one day after the next update we see a message in the admin panel that since the summer of 2019, 1C-Bitrix stops working with MySQL 5.5 and needs to be updated. The guys from ISPSystem are handsome and regularly expand the functionality of the panel for which a special thank you to them. But this time it was not possible to click everything with the mouse. But about what happened and how much gray hair in my beard now you can find out under the cut.

There was only an option to install an “alternative DBMS server” which is placed in the Docker container. I certainly understand that Docker is very careful with resources, but no matter how cool it works, the overhead will still be> 0. And here we are, as it were, fighting for a tenth of a second and at the entrance we optimize all the websites before publishing and signing the contract. So not my option.
Ok, what is written in the documentation? Backup all, add to yum.repos.d a file with a link to the MariaDB repository, then

rpm -e --nodeps MariaDB-server MariaDB-client MariaDB-common 

Yum will subsequently swear at the fact that someone has deleted the packages \ set without his knowledge. But first - let swears, do not worry. And secondly, if you do a deletion through yum, then he tries to demolish everything that is related to him with MariaDB, and this is both PHP and ISPManager and PHPmyadmin. Therefore, then with Rugachki then figure it out.

 yum clean all yum update yum install MariaDB-server MariaDB-client MariaDB-common 

In general, everything was set and started. The nice thing is that the bases were picked up and it was not necessary to restore them from dumps. I checked sites - work and quickly. I went into a couple of admins to make sure that nothing fell off and unsubscribed to the director that everything was OK. In less than 30 minutes it turned out that it wasn’t even OK ...
')
When I tried to log in to the admin panel and add / edit anything in the content, the message fell out

 MySQL Query Error: INSERT INTO b_iblock_element_property (ID, IBLOCK_ELEMENT_ID, IBLOCK_PROPERTY_ID, VAL UE, VALUE_NUM) SELECT 10555 ,2201 ,P.ID ,'3607' ,3607.0000 FR OM b_iblock_property P WHERE ID = 184 [[1062] Duplicate entry '10555' for key 'PRIMARY'] 

Since the content is added to the site by our female employees, clients still didn’t know anything and didn’t start tearing us apart. But it was a matter of time for the info on the sites to be updated and that’s what many clients are watching themselves and closely.

From the text of the error, we can conclude that Bitrix is ​​trying to add a new record to the database, while indicating the same primary key that the edited article had. So there is reason to suspect that the problem occurs on the side of Bitrix. Go to their website and contact support. Almost immediately we get the answer “a difficult problem. We gave them to senior engineers - wait ... ”

We had to wait for quite a long time (the entire dialogue took place from June 25, 2019 to July 9, 2019) and the result was the message “this problem is not related to the work of CMS Bitrix, but connected to the work of the database itself in mariadb 10.4.6 and unfortunately side of the site to solve this problem is missing the need to upgrade to the old version of MariaDB.

Sailed ... I thought about the downgrade at the beginning of the story, but then it was said in black and white that there could be no downgrade. Merge the dumps and deploy again on a completely installed server. Those. It's good that I did not update all the servers at once. Those. “Just something” hundreds of sites (nervous laugh :-)). They also said in support: “To solve the problem when using the MariaDB 10.4.6 database, you will need to contact MariaDB technical support that the transaction will not delete the record from the database if the request is made:

 $DB->Query("DELETE FROM ".$strTable." WHERE ID = ".$res["ID"]); $results = $DB->Query("SELECT * FROM ".$strTable." WHERE ID = ".$res["ID"]);” 

Hope has been warming for a couple of hours since the start of communication with MariaDB support, but then I received a letter in which I was very correctly informed that I was not a commercial user and therefore no one would solve my problem purposefully, but there is a forum on their website and there you can try searching for options ... I will not bore the details. There are no options there.
ABOUT! We have a purchased ISP license!
- Hello, support? Help guys!
- Sorry, we do not support scumbags which native versions of the DBMS change. Want - there is an option with an alternative server in docker.
- But how do users and databases get there? Docker?
- Well, you drag them there with your hands ...
- Yes! And do not forget that the port for mysql will change and it will be necessary to go over and rewrite all the configs.
- Ok, thanks, I will think ...
I thought and decided to demolish 10.4 with pens and put 10.2 with which there were no problems on other servers.

The process was not very different from the update process. Only it was necessary in the link to the repository to change 10.4 to 10.2, reset and recreate the cache for yum. Well and one more “trifle”: after removing 10.4, go to / var / lib / mysql and delete everything from there. Without this step after installing 10.2, the service will constantly fall and you will see

       '' Lost connection to MySQL server at 'reading initial communication packet', system error: 104 "Connection reset by peer" 

Or

 Lost connection to MySQL server at 'handshake: reading inital communication packet', system error: 104 

Before importing the databases, I first set up the root password for mysql which was registered in the ISP configs and imported a dump of the mysql database. Well, and further, since users and rights already exist, we simply import all user databases in a row with root accounting.

Script text for database dump:

 #!/bin/bash echo 'show databases' | mysql -u root --password="_" --skip-column-names | grep -v information_schema | xargs -I {} -t bash -c 'mysqldump -u root --password="_" {} | gzip > /BACK/back-$(hostname)-{}-$(date +%Y-%m-%d-%H.%M.%S).sql.gz' 

Before importing databases, you need to unzip them. Therefore, we simply execute the command

 gunzip /BACK/*.gz 

And the last thing: for some reason in the name of databases (if you create through ISPmanager) hyphens are allowed. But when you create or attempt to upload a dump to the database that has a hyphen in the name, you receive a message that the query syntax is incorrect.

Read to the end of all the benefits. I apologize for the most likely not placed commas - with them trouble. If there are wishes / suggestions on the essence of the described - write in a personal because in the comments I'm afraid to miss something. And do not swear much - this is my first article :-)

UPD1:

I almost forgot to mention: while I was trying to find a solution to the problem without downgrade MariaDB, I had to update it somehow. It was updated this way: the entire database is converted from InnoDB to MyISAM, the infa is updated and the stream is converted back to InooDB.
UPD2:

Just received a letter from 1C-Bitrix with the following content:

Request for revision implemented
“After updating mariadb to 10.4.6 an error occurred while saving an information block element”
Module: iblock, version: not known
Solution: rejected

So while it is not possible to update to 10.4 :-(