Good day, dear Khabrovites! 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 the nagios + PhantomJS bundle on the intra-office computer and check the page loading speed every 30 minutes. According to the terms of service, we also monitor 1C-Bitrix updates and install them regularly. And then one day, after the next update, we see a message in the admin panel stating 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 special thanks to them. But this time it was not possible to click everything with the mouse. But what happened and how many gray hairs are now in my beard can be found under the cut.
There was only an option to install an βalternative DBMS serverβ that is installed in the Docker container. Of course, I understand that Docker is very frugal with resources, but no matter how great it works, the overhead will still be > 0. And here we are, as it were, fighting in tenths of seconds and optimizing all sites at the entrance before publishing and signing an agreement. So not my choice.
Ok, what's in the documentation? Backup everything, add a file with a link to the MariaDB repository to yum.repos.d, then
rpm -e --nodeps MariaDB-server MariaDB-client MariaDB-commonYum will subsequently swear at the fact that someone removed the packages without his knowledge. But firstly - let him swear, it's okay. And secondly, if you do the deletion through yum, then it tries to demolish, along with MariaDB, everything that is related to it by dependencies, and this is PHP and ISPManager and PHPmyadmin. So we'll deal with the bugs later.
yum clean all
yum update
yum install MariaDB-server MariaDB-client MariaDB-commonIn general, everything was set up and started. The nice thing is that the bases were picked up and it was not necessary to restore them from dumps. I checked the sites - they work and quickly. I went to a couple of admin panels 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 was not even OK at all ...
When I tried to go to the admin panel and add edit anything in the content, a 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 on the site is added by our employees, the clients still did not know anything and had not yet begun to tear us apart. But it was a matter of time, because the information on the sites needs to be updated, and many clients follow this very closely.
From the text of the error, we can conclude that Bitrix is ββtrying to add a new record to the database, while specifying the same primary key that the article being edited 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 βdifficult problem. Given to senior engineers - wait ... "
I had to wait quite a long time (the whole dialogue took place from 25.06.2019/9.07.2019/10.4.6 to XNUMX/XNUMX/XNUMX) and the result was the message βthis problem is not related to the operation of the Bitrix CMS, but is related to the operation of the database itself in mariadb XNUMX and, unfortunately, with side of the site to solve this problem is missing, it will be necessary to migrate to an older version of MariaDB.β
Sailed ... I thought about downgrade at the beginning of the story, but that there can be no downgrade. Merge dumps and redeploy on a freshly installed server. Those. it's good that I didn't update all the servers at once. Those. βonlyβ a hundred sites (nervous chuckle :-)). 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 a record from the database if a request is made:
$DB->Query("DELETE FROM ".$strTable." WHERE ID = ".$res["ID"]);
$results = $DB->Query("SELECT * FROM ".$strTable." WHERE ID = ".$res["ID"]);β
Hope glimmered for a couple of hours from the moment we started communicating with MariaDB support, but then I received a letter in which I was extremely correctly informed that I was not a commercial user and therefore no one would purposefully solve my problem, but there is a forum on their website and you can try to look for options there β¦ I won't bore you with details. There are no options there.
ABOUT! We have purchased a license for ISP!
Hello, support? Guys, help!
- Sorry, we do not support thugs 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? To docker?
- Well, you drag them there with your hands ...
- Yes! And do not forget that the port for mysql will change and you will need to go through and rewrite all the configs.
Ok thanks, I'll think about it...
I thought and decided to demolish 10.4 with handles and install 10.2 with which there were no problems on other servers.
The process was not much different from the upgrade process. Only it was necessary to change 10.4 to 10.2 in the link to the repository, reset and re-create the cache for yum. Well, one more βtrifleβ: after removing 10.4, we go to /var/lib/mysql and delete everything from there. Without this step, after installing 10.2, the service will constantly crash 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: 104Before importing the databases, I first set the mysql root password that was specified in the ISP configs and imported the mysql database dump. Well, then, since there are already users and rights, we simply import all the user databases in a row with the root account.
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. So just run the command
gunzip /BACK/*.gzAnd the last thing: for some reason, hyphens are allowed in database names (if you create them using ISPmanager). But when creating or trying to upload a dump to a database that has a hyphen in the name, you get a message that the query syntax is incorrect.
Read to the end of all the blessings. I apologize for the most likely not spaced commas - they are in trouble. If there are wishes for a proposal essentially described - write in a personal because in the comments I'm afraid to miss something. And don't swear too much - 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 had to somehow update the information. It was updated like this: the entire database is converted from InnoDB to MyISAM, infa is updated and then converted back to InooDB.
UPD2:
Just received a letter from 1C-Bitrix with the following content:
Revision request completed
"After updating mariadb to 10.4.6, an error occurred when saving the infoblock element"
Module: iblock, version: unknown
Solution: rejected
So for now, apparently it is impossible to update to 10.4 π
Source: habr.com