Good day, esteemed Habr users! Allow me to introduce myself, my name is Alexander. I am a system administrator at a small but proud web studio. We really want everything to run quickly, safely, and with up-to-date software. To achieve this, we even set up a nagios+PhantomJS combo on an internal office computer and check the page loading speed every 30 minutes. As part of our service agreement, we also keep an eye on updates to 1C-Bitrix and install them regularly. One day, after yet another update, we see a message in the admin panel stating that starting summer 2019, 1C-Bitrix would no longer work with MySQL 5.5 and we need to update. The guys at ISPSystem are great and regularly expand the functionality of their panel, for which they deserve our thanks. However, this time we couldn't just click everything with the mouse. As for what we managed to do and how many gray hairs are now in my beard, you can find out below.
The only option was to install an "alternative database server" that runs in a Docker container. I understand that Docker is quite resource-efficient, but no matter how great it runs, there will still be an overhead >0. We are focused on tenths of a second, optimizing all the sites before publishing them and signing the contract. So, that option isn't for me.
Okay, what does the documentation say? Backup everything, add a file with a link to the MariaDB repository in yum.repos.d, then
rpm -e --nodeps MariaDB-server MariaDB-client MariaDB-commonYum will subsequently complain that someone has deleted or installed packages without its knowledge. But first, let it complain; it's not a big deal. Secondly, if you remove it through yum, it tries to uninstall everything that is dependent on MariaDB, which includes PHP, ISPManager, and PHPmyadmin. So we’ll deal with the complaints later.
yum clean all
yum update
yum install MariaDB-server MariaDB-client MariaDB-commonIn general, everything installed and started up. The nice thing is that the databases were picked up and there was no need to restore them from dumps. I checked the sites—they work and load quickly. I went into a couple of admin panels to make sure nothing broke and informed the director that everything was okay. Within 30 minutes, however, it turned out that things were not okay at all...
When trying to access the admin panel and add/edit anything in the content, an error message popped up.
MySQL Query Error: INSERT INTO b_iblock_element_property (ID, IBLOCK_ELEMENT_ID, IBLOCK_PROPERTY_ID, VALUE, VALUE_NUM) SELECT 10555, 2201, P.ID, '3607', 3607.0000 FROM b_iblock_property P WHERE ID = 184 [[1062] Duplicate entry '10555' for key 'PRIMARY']Since the content on the site is added by our employees, the clients were not aware yet and have not started tearing us apart. But it was just a matter of time as the information on the sites needs to be updated, and many clients closely monitor this themselves.
From the text of the error, it can be concluded that Bitrix is trying to add a new record to the database while specifying the same primary key that was used for the edited article. This means there are grounds to suspect that the problem arises on Bitrix's side. We go to their website and contact support. Almost immediately we receive a response, "it's a complex issue. We’ve handed it over to the senior engineers—please wait..."
We had to wait quite a while (the entire dialogue occurred between June 25, 2019, and July 9, 2019), and the outcome was the message: "this problem is not related to the operation of the Bitrix CMS, but is related to the database operation in mariadb 10.4.6, and unfortunately, there is no way to solve this issue from the site side; it will be necessary to downgrade to an earlier version of MariaDB."
Here we are... I thought about downgrading back at the beginning of the story, but , that there can be no downgrade whatsoever. Dump the data and set it up anew on a clean installation. server. That is, it’s good that I didn’t update all the servers at once. Just ‘only’ a hundred sites (nervous laugh :-)). Support also said: "To solve the problem when using MariaDB 10.4.6, you will need to contact MariaDB technical support, as the deletion of the record from the database will not execute in the transaction if the query is made:
$DB->Query("DELETE FROM " . $strTable . " WHERE ID = " . $res["ID"]);
$results = $DB->Query("SELECT * FROM " . $strTable . " WHERE ID = " . $res["ID"]);” Hope lingered for a couple of hours from the start of communication with MariaDB support, but then I received a letter in which I was very politely informed that since I am not a commercial user, no one would purposefully address my issue, but there is a forum on their website where I can try to look for options... I won’t bore you with the details. There are no options there.
Oh! We have a purchased license for ISP!
— Hello, support? Guys, help!
— Sorry, we do not support those who change native versions of the DBMS. If you want, there is an option with an alternative server in Docker.
— But how will users and databases get there? In Docker?
— Well, you will have to manually transfer them there...
— Yes! And don't forget that the port for MySQL will change, and you will need to go through all the configurations and rewrite them.
— Ok, thanks, I will think about it...
I thought about it and decided to manually remove 10.4 and install 10.2, with which there were no problems on other servers.
The process was not significantly different from the update process. You just need to change 10.4 to 10.2 in the repository link, reset, and recreate the cache for yum. Oh, and one more 'little thing': after removing 10.4, go to /var/lib/mysql and remove everything from there. Without this step, after installing 10.2, the service will continuously crash and you'll see
Could not connect to the database '' 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 initial communication packet', system error: 104Before importing the databases, I first installed the root password for MySQL that was specified in the ISP configs and imported the MySQL database dump. Then, since users and permissions are already there, we simply import all user databases in order with the root account.
Script text for dumping databases:
#!/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 unarchive them. So just execute the command
gunzip /BACK/*.gzAnd lastly: for some reason, hyphens are allowed in database names (if you create them through ISPmanager). However, when you try to create or upload a dump to a database with a hyphen in its name, you get a message saying the syntax of the request is incorrect.
For those who read to the end, best wishes. I apologize for the probably misplaced commas — they are a problem. If there are any suggestions regarding the substance of what was described — feel free to message me since I'm afraid I might miss something in the comments. And please don’t be too harsh — this is my first article 🙂
UPD1:
I almost forgot to mention: while I was trying to find a solution to the problem without downgrading MariaDB, I needed to somehow update the information. It was updated like this: the entire database is converted from InnoDB to MyISAM, the information is updated and then converted back to InnoDB.
UPD2:
I just received a letter from 1C-Bitrix with the following content:
The request for modification has been implemented
“After upgrading MariaDB to 10.4.6, an error occurred when saving an information block element”
Module: iblock, version: unknown
Solution: rejected
So it seems we can’t update to 10.4 for now 🙁
Source: habr.com
