Bitrix and update MariaDB to the latest stable version
Good day, dear Khabrovchians! Let me 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, they even raised a bunch of nagios + PhantomJS on the office computer and check the page loading speed every 30 minutes. According to the terms of service, we also follow the updates of 1C-Bitrix and regularly install them. And once, 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 all the mouse. But what happened and how much gray hair is now in my beard can be found under the cut.
There was only an option to put an “alternative DBMS server” which is put in the Docker container. Of course, I understand that Docker is very resource-efficient, but no matter how cool it works, the overhead will still be> 0. And here we are fighting, as it were, in tenths of a second, and at the entrance we optimize all the sites before publishing and signing a contract. So this is not my option.
Ok, what is written in the documentation? Backup total, add a file with a link to the MariaDB repository in yum.repos.d, then
Yum subsequently will swear that someone removed / installed packages without his knowledge. But firstly - let him swear, that's okay. And secondly, if you do the deletion via yum, then he tries to tear down with MariaDB everything that is related to it, and this is PHP and ISPManager and PHPmyadmin. Therefore, we will deal with scribbles.
In general, everything was set up and wound up. The nice thing is that the bases were picked up and there was no need to restore them from dumps. I checked the sites - they work and quickly. I went into a couple of admin pages to make sure that nothing fell off and wrote 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 go to the admin panel and add / edit anything, a message fell out in the content
Since our employees are adding content to the site, 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 this is what many clients follow themselves and closely.
From the error text, 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 arises on the side of Bitrix. We go to their website and turn to support. Almost immediately we get the answer “a difficult problem. Gave it to senior engineers - wait ... "
I had to wait a long time (the whole dialogue took place from 06/25/2019 to 07/09/2019) and the result was the message “this problem is not related to the work of Bitrix CMS, but to the database itself in mariadb 10.4.6 and unfortunately with side of the site there’s no way to solve this problem; it will be necessary to upgrade to the old version of MariaDB. ”
They arrived ... I thought about downgrade at the beginning of the story, but it’s said in black and whitethat there can be no downgrade. Merge dumps and deploy again on a cleanly installed server. Those. it is good that I did not update all the servers at once. Those. “Just” a hundred sites (a 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:
Nadezhda had been warming for a couple of hours since the start of communication with MariaDB support, but then a letter came in which they correctly told me that I was not a commercial user and therefore no one would deliberately solve my problem, but there was a forum on their website and you could try to look for options there ... I will not bore the details. There are no options there.
ABOUT! We have purchased a license for ISP!
- Hello, support? Guys, help!
- Sorry, we do not support the scumbags that change the native version of the DBMS. You want - there is an option with an alternative server in the docker.
- But how do 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 all the configs and rewrite it.
- Ok, thanks, I’ll 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 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, 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
Or
Before importing the databases, I first set the root password for mysql that was registered in the ISP configs and imported the mysql database dump. Well, then, since there are already users and rights, we simply import all user bases in a row with the root account.
Script text for database dump:
Before importing databases, you need to unzip them. So just execute the command
And the last: for some reason hyphens are allowed in the name of the databases (if you create through ISPmanager). But when creating or trying to fill the dump in the database with a hyphen in the name, you get a message that the query syntax is incorrect.
Read to the end of all blessings. I apologize for the most likely not placed commas - trouble with them. If there are wishes / suggestions on the essence of what is described - write in a personal message because I'm afraid to miss something in the comments. 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 somehow update the info. It was updated as follows: the entire database is converted from InnoDB to MyISAM, the info is updated, and then it is converted back to InooDB.
UPD2:
A letter has just arrived from 1C-Bitrix with the following content:
There was only an option to put an “alternative DBMS server” which is put in the Docker container. Of course, I understand that Docker is very resource-efficient, but no matter how cool it works, the overhead will still be> 0. And here we are fighting, as it were, in tenths of a second, and at the entrance we optimize all the sites before publishing and signing a contract. So this is not my option.
Ok, what is written in the documentation? Backup total, add a file with a link to the MariaDB repository in yum.repos.d, then
rpm -e --nodeps MariaDB-server MariaDB-client MariaDB-commonYum subsequently will swear that someone removed / installed packages without his knowledge. But firstly - let him swear, that's okay. And secondly, if you do the deletion via yum, then he tries to tear down with MariaDB everything that is related to it, and this is PHP and ISPManager and PHPmyadmin. Therefore, we will deal with scribbles.
yum clean all
yum update
yum install MariaDB-server MariaDB-client MariaDB-commonIn general, everything was set up and wound up. The nice thing is that the bases were picked up and there was no need to restore them from dumps. I checked the sites - they work and quickly. I went into a couple of admin pages to make sure that nothing fell off and wrote 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 go to the admin panel and add / edit anything, a message fell out in the content
MySQL Query Error: INSERTINTO b_iblock_element_property (ID, IBLOCK_ELEMENT_ID, IBLOCK_PROPERTY_ID, VAL UE, VALUE_NUM) SELECT10555 ,2201 ,P.ID ,'3607' ,3607.0000 FR OM b_iblock_property P WHEREID = 184 [[1062] Duplicate entry '10555'forkey'PRIMARY']Since our employees are adding content to the site, 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 this is what many clients follow themselves and closely.
From the error text, 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 arises on the side of Bitrix. We go to their website and turn to support. Almost immediately we get the answer “a difficult problem. Gave it to senior engineers - wait ... "
I had to wait a long time (the whole dialogue took place from 06/25/2019 to 07/09/2019) and the result was the message “this problem is not related to the work of Bitrix CMS, but to the database itself in mariadb 10.4.6 and unfortunately with side of the site there’s no way to solve this problem; it will be necessary to upgrade to the old version of MariaDB. ”
They arrived ... I thought about downgrade at the beginning of the story, but it’s said in black and whitethat there can be no downgrade. Merge dumps and deploy again on a cleanly installed server. Those. it is good that I did not update all the servers at once. Those. “Just” a hundred sites (a 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("DELETEFROM".$strTable."WHEREID = ".$res["ID"]);
$results = $DB->Query("SELECT * FROM".$strTable."WHEREID = ".$res["ID"]);”Nadezhda had been warming for a couple of hours since the start of communication with MariaDB support, but then a letter came in which they correctly told me that I was not a commercial user and therefore no one would deliberately solve my problem, but there was a forum on their website and you could try to look for options there ... I will not bore the details. There are no options there.
ABOUT! We have purchased a license for ISP!
- Hello, support? Guys, help!
- Sorry, we do not support the scumbags that change the native version of the DBMS. You want - there is an option with an alternative server in the docker.
- But how do 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 all the configs and rewrite it.
- Ok, thanks, I’ll 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 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, 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: 104Before importing the databases, I first set the root password for mysql that was registered in the ISP configs and imported the mysql database dump. Well, then, since there are already users and rights, we simply import all user bases in a row with the root account.
Script text for database dump:
#!/bin/bashecho'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 execute the command
gunzip /BACK/*.gzAnd the last: for some reason hyphens are allowed in the name of the databases (if you create through ISPmanager). But when creating or trying to fill the dump in the database with a hyphen in the name, you get a message that the query syntax is incorrect.
Read to the end of all blessings. I apologize for the most likely not placed commas - trouble with them. If there are wishes / suggestions on the essence of what is described - write in a personal message because I'm afraid to miss something in the comments. 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 somehow update the info. It was updated as follows: the entire database is converted from InnoDB to MyISAM, the info is updated, and then it is converted back to InooDB.
UPD2:
A letter has just arrived from 1C-Bitrix with the following content:
Application for revision implementedSo while it is apparently impossible to update to 10.4 :-(
“After updating mariadb to 10.4.6 an error occurred while saving the info block element”
Module: iblock, version: unknown
Solution: rejected
