I welcome everyone again to the pages of my blog, we continue to study lessons on 1C Bitrix. The other day I got one physical server moved to another data center, comrades from this data center started doing the work earlier and did not give the opportunity to properly turn off the virtual machines. After moving to one of the sites, I saw this error, let's look at how to solve it and know what to do in the future, it will take you no more than 5 minutes.

Solving db query error

Previously I had a bitrix installation on centos. There may be several reasons for this error and we will consider them all. To begin with, I’ll give an example of a screenshot of what the db query error please later looks like, you’ll agree that it’s not very informative.

Running out of free space

Yes, it happens, you may run out of space on local disk and Centos simply cannot write anywhere anymore, and you catch this error. To check if you have free space you need to connect to the server via the ssh protocol and enter the following command.

You will receive a summary of the sections, in my example there is enough free space.

Mysql database is corrupted

The second reason is more serious and it lies in the fact that your tablet or, if you like, Mysql database has broken. To find out, you will need to enable the debugger. We need to include one parameter in this file.

/bitrix/php_interface/dbconn.php

In order to edit it, I advise you to log in via sftp-ssh connection, as I described how to do this in the article How to log into an ftp hosting server. The only thing is that when you create a connection, select sftp-ssh. The WinSCP 5.7.6 utility can also help, it does the same thing. Both are just file managers.

Open this file and find the variable $DBDebug = false; Change its value to $DBDebug = true ; Please note that this file contains useful parameters such as:

• $DBLogin > database login
• $DBPassword > password
• $DBName > database name

As a result, now by refreshing the site page I got

MySQL Query Error: SELECT ID, SESSION_DATA FROM b_stat_session_data WHERE GUEST_MD5 = "71dbd5e22cd7ad7065e17a7eb6940a59" and DATE_LAST > DATE_ADD(now(), INTERVAL - 1440 SECOND) LIMIT 1

From here you can see that my edisclosure2009 database has crumbled, which means it needs to be restored, do not forget to set $DBDebug = false again, as it can slow down the site wildly. I use the EMS SQL utility for this Manager for MySQL. Open it and go to Run SQL Manager for MySQL.

Leave the host name as localhost, that’s how it should be, your port 3306 should not be exposed. Next, specify the login and password for the database and check the Use tunneling box.

Set the IP address or dns name server to which we will connect via ssh connection, and indicate the login and password for access.

We indicate the name of the site database on Bitrix 1C. I also usually set the Auto-connect option at startup.

Articles

Typical Bitrix errors

07.07.2018

1.Duplicate entry ‘H1vyYZlZDAeRMeZJ76uKHWozRqctgvK1’ for key ‘PRIMARY’
You need to delete the contents of the database and try again to restore the site from backup copy, you also need to look at whether storing sessions in a separate database is enabled in the advanced security mode settings of the administrator group, and disable this setting.

2. Database size limit exceeded
Shared hosting has limitations on database sizes. If the size of the database exceeds the limit, notifications will begin to be sent to you. If exceeded maximum size a warning is sent followed by blocking until the problem is resolved. In this case, you need to either increase the tariff or look at the contents of the database. In my case, the b_event_log table took up a lot of space, b_event took up a lot of space, b_captcha also took up a lot of space. You need to log in to Phpmyadmin and clear the specified tables in the database.

3. Allowed memory size
Open the php.ini file and find the line there: memory_limit = 256M, increase the value to 9 for tariffs virtual hosting there are restrictions)

4. Cannot use BitrixMainTextString as String because ‘String’
there are no Bitrix updates for the version of PHP being used; the problem usually occurs when restoring the site on another hosting. Try starting with PHP version 5.3

5. DB query error. Please try later
Typically a database access error
1) Open the file /bitrix/php_interface/dbconn.php and insert the line $DBDebug=true;

6. max_input_vars
In the .htaccess file set php_value max_input_vars 10000, but it doesn’t always help to change this directive you need to edit the php.ini file: max_input_vars = 10000

7. Maximum execution time of 60 seconds exceeded
The following options may help:
1) after making changes to php.ini, a restart of apache is required.
2) if the set_time_limit variable is defined in the file /bitrix/php_interface/dbconn.php then it takes precedence over what is stored in php.ini in max_execution_time
3) the error may occur due to insufficient value of the memory_limit parameter (also defined in these files)

8. MySQL server has gone away
While the request was being executed, the server dropped the connection. The problem is related to setting up MySQL and often occurs when the server has a small time limit for the connection.
Install in bitrix/php_interface/after_connect.php:
$DB->Query("SET wait_timeout=28800");

9. opcache.revalidate_freq for Bitrix
The link in the .htaccess file helps me, especially on the host-0 reg.ru tariff, which is not very suitable for the Bitrix tariff:
php_value opcache.revalidate_freq 0
php_value max_input_vars 10000
php_value default_socket_timeout 60
Switching to PHP 7.0 also helps

10. Query Error SET LOCAL time_zone
on the Bitrix virtual machine
$DB->Query("SET LOCAL time_zone="".date('P')."'");

11. Table encoding (cp1251) differs from base encoding (utf8)
For the table that the error points to, you will need to run a series of SQL queries in the database:
ALTER TABLE table_name CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci To change the encoding of tables, please run the query for each table:
ALTER TABLE table_name CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;

12. Error large files import
You will need to increase the file size limits in the php.ini file.

13. Error! The database structure has errors - the fields are different
We look at the names of the tables in the logs
Please go to the sql line (Settings > Tools > SQL query) and execute the query there:
ALTER TABLE `b_ticket` MODIFY `TITLE` varchar(255) NOT NULL ;
ALTER TABLE `b_ticket` MODIFY `OWNER_SID` varchar(255) NULL DEFAULT NULL ;
ALTER TABLE `b_ticket` MODIFY `LAST_MESSAGE_SID` varchar(255) NULL DEFAULT NULL ;
ALTER TABLE `b_ticket` MODIFY `SUPPORT_COMMENTS` varchar(255) NULL DEFAULT NULL ;

After that, reinstall (uninstall and install again) the search module without saving
tables (Settings-Product Settings-Modules).

Then you need to perform a complete reindexing. To do this, in the administrative part of the site, go to
please go to “Settings > Search > Reindexing”. Uncheck "Reindex only changed"
set the step, for example, 10 seconds and click the “Reindex” button.

• An error occurs on any page of the site: The script encountered an error and will be aborted. To view extended error messages, enable this feature in .settings.php.
• An error occurs on any page of the site: DB query error. Please try later.
• An error occurs on the site: Mysql connect error: Can"t connect to local MySQL server through socket "/var/lib/mysqld/mysqld.sock" (2) (400)
• An error occurs with any MySQL query of the form: MySQL Query Error: SELECT ... FROM ...

After the transfer, the entire public part is not visible without login and password authorization

If after the transfer you see home page Instead of news, there is a login form and all menu items are marked with a lock icon:

... and after authorization everything is displayed correctly - go to the Bitrix administrative panel and follow the path: Settings → System settings → Sites → List of sites, select your site and check the value of the “Path to web server root folder for this site” field. the web server root folder of this site)", most likely the wrong path is specified there.

When entering the administrative interface, an error message appears: “The encoding of the MySql database you are using is “utf8”, the update system requires the encoding “cp1251”. Contact your MySql administrator to run the query alter database DATABASE_NAME default character set cp1251"

By default all services virtual machine VMBitrix work in UTF-8 encoding. Compared to the CP1251 encoding (Windows-1251), UTF-8 provides greater opportunities for storing information on various languages, more details can be found in Wikipedia.

If for some reason you cannot switch to using UTF-8, run the specified request yourself.

To do this, go to the administrative section of Bitrix and follow the path Settings → Tools → SQL query, copy and paste the query from the error message and run it.

This query changes a property of the database being used without affecting the data itself, and you can change the old value back if necessary.

Reindexing of static site files freezes

When trying to update search index your website process freezes (it takes a very long time and the status of already re-indexed documents is not updated). If you re-index by individual modules, the freeze occurs only when you select the “Static files” module.

To solve the problem, you need to change the mbstring parameters in the /etc/php.ini file:

Mbstring.func_overload = 0 mbstring.internal_encoding = CP1251 and restart Apache web server for the new settings to take effect: /etc/init.d/apache2 restart

An error occurs on any page of the site:

The script encountered an error and will be aborted. To view extended error messages, enable this feature in .settings.php.

In the new Bitrix core, parameters are configured in the file bitrix/.settings.php(note that the file name starts with a dot). Previously, the file was used for these tasks .

By default, Bitrix hides any error messages, as this significantly reduces the level of system security. Therefore, if any error occurs, the above message will be displayed instead.

Therefore, first of all, you need to log into the server (via SSH or sFTP) and edit the file bitrix/.settings.php. You should find the line in it:

"debug" => false, and change the value of the debug parameter to true, that is, the line should look like: "debug" => true,

Then, when you access the error page, you will see the full error message. After fixing the error, do not forget to return the parameter debug to its original meaning.

Bolle detailed description all file parameters .settings.php available on the developer's website.

An error occurs on any page:

DB query error. Please try later.

This error is absolutely similar to the one described above, but occurs only in the old Bitrix core. To enable the full error message, log into the server (via SSH or sFTP) and edit the file bitrix/php_interface/dbconn.php. Find the line in it:

$DBDebug = false; and change the value of the $DBDebug variable to true, that is, the line should look like: $DBDebug = true;

Then, when you access the error page, you will see the full error message. After fixing the error, do not forget to return the $DBDebug parameter to its original value.

An error occurs on the site:

Mysql connect error : Can"t connect to local MySQL server through socket "/var/lib/mysqld/mysqld.sock" (2) (400)

This error means that the database server MySQL data not available. This can happen in a number of cases and often requires a separate analysis of the situation to identify the exact cause. But in most cases, this error occurs due to the following problems:

1. Lack of RAM.

   If during the development of the project it was not calculated huge pressure(on the part of users or the volume of data being processed), or an error was made during development, or the MySQL and Apache parameters are not optimal for the project, then an overflow may occur with a surge in activity random access memory(RAM). In this case, operating system, to maintain its own security, it forcibly terminates the largest process (for web servers this is usually MySQL).

   In this case, in the system log /var/log/syslog and the server console (which can be accessed in the control panel) will contain a message like:

   Kernel: Out of memory: Kill process 1543 (mysqld) score 146 or sacrifice child In this case, reboot the server to free up memory and restore all services. Then involve developers to optimize scripts and database queries to prevent errors from occurring in the future. Or, if optimization is not possible, increase tariff plan, thereby increasing the amount of available memory.

2. The server cannot start due to a configuration error.

   As a rule, this can occur after editing configuration file my.cnf or system updates. In this case, you should roll back the changes and restart the server or MySQL separately.