Ошибка обмена с 1с

[ga01]

Приветствую помогите новичку.

Установил себе на свой сервер CP Vesta на CentOS 7.3.1611
Пытаюсь загрузить заказы с сайта в 1с, выдаёт ошибку при обмене с 1с
Текст ошибки :
Ошибка SQL, You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'limit 0, 4' at line 1


Выгрузка из 1с на сайт уходит без ошибок, как на моём сервере так и на сервере хостинг провайдера.

При попытке загрузить заказы с сайта хостинг провайдера - ошибок нет, заказы загружаются без ошибок

У хостинг провайдера :
OS: ‪CentOS Linux 7.2.1511
PHP 5.6.23
mariaDB - 5.5.52

На моём сервере :
OS: ‪CentOS Linux 7.3.1611
PHP 5.6.30
mariaDB - 5.5.52

Помогите решить проблему , 2 дня спать не могу. Как победить ошибку - Ошибка SQL, You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'limit 0, 4' at line 1 ?
Заранее спасибо.

[skurudo]

Помогите решить проблему , 2 дня спать не могу. Как победить ошибку - Ошибка SQL, You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'limit 0, 4' at line 1 ?

Ошибка синтаксиса, вам об этом недвусмысленно намекают.
Подгонять выгрузку под тот формат, что войдет в ваш 1с.

[ga01]

Почему тогда при загрузке с сервера хостинг провайдера ошибок нет ?

Сайт такой же - как на сервере у хостинг провайдера так и на моём сервере, база данных как на сервере у хостинг провайдера так и на моём сервере, 1с на локальном компе - одна и та же . Почему ошибка синтаксиса при загрузке с моего сервера ? Почему тогда без ошибок загружается с сервера хостинг провайдера ?

Значит в весте на моём сервере что то не так настроено.

Как узнать что не так ? И почему ошибку в одном случае выдаёт а в другом случае нет ?

[ga01]

Ниже лог бд на сайте хостинг провайдера

/usr/libexec/mysqld, Version: 5.5.52-MariaDB (MariaDB Server). started with:
Tcp port: 0 Unix socket: /var/lib/mysql/mysql.sock
Time Id Command Argument
# Time: 170326 17:35:01
# User@Host: admin[admin] @ localhost []
# Thread_id: 2 Schema: psa QC_hit: No
# Query_time: 0.032534 Lock_time: 0.032446 Rows_sent: 0 Rows_examined: 0
use psa;
SET timestamp=1490538901;
UPDATE BackupsScheduled SET processed='false';
# Time: 170326 17:35:40
# User@Host: irkutsk[irkutsk] @ localhost []
# Thread_id: 3 Schema: irkutsk QC_hit: No
# Query_time: 0.016607 Lock_time: 0.016540 Rows_sent: 1 Rows_examined: 1
use irkutsk;
SET timestamp=1490538940;
SELECT 1 FROM `SC_divisions` LIMIT 1;
# User@Host: irkutsk[irkutsk] @ localhost []
# Thread_id: 3 Schema: irkutsk QC_hit: No
# Query_time: 0.007095 Lock_time: 0.006945 Rows_sent: 0 Rows_examined: 64
SET timestamp=1490538940;
SELECT * FROM SC_categories WHERE slug=40709;
# User@Host: irkutsk[irkutsk] @ localhost []
# Thread_id: 3 Schema: irkutsk QC_hit: No
# Query_time: 0.000253 Lock_time: 0.000134 Rows_sent: 5 Rows_examined: 10
SET timestamp=1490538940;
SELECT *, `aux_page_name_ru` AS aux_page_name FROM SC_aux_pages ORDER BY `aux_page_priority` ASC;
# Time: 170326 17:36:43
# User@Host: velikijnovgorod[velikijnovgorod] @ localhost []
# Thread_id: 4 Schema: velikijnovgorod QC_hit: No
# Query_time: 0.012600 Lock_time: 0.004732 Rows_sent: 458 Rows_examined: 458
use velikijnovgorod;
SET timestamp=1490539003;
SELECT `settingsID`, `settings_constant_name`, `settings_value` FROM `SC_settings`;
# Time: 170326 17:36:47
# User@Host: admin_[admin_] @ localhost []
# Thread_id: 7 Schema: admin_ QC_hit: No
# Query_time: 0.000360 Lock_time: 0.000105 Rows_sent: 2 Rows_examined: 12
use admin_;
SET timestamp=1490539007;
SELECT a.customerID, a.Login, a.Email, a.first_name, a.last_name, b.zip, d.zone_name_ru as state, b.city, b.address, c.reg_field_value as tel FROM SC_customers a left join SC_customer_addresses b on b.addressID = a.addressID left join SC_customer_reg_fields_values c on (a.customerID = c.customerID and c.reg_field_ID = 1) left join SC_zones d on d.zoneID=b.zoneID limit 2, 2;
# User@Host: arhangelsk[arhangelsk] @ localhost []
# Thread_id: 5 Schema: arhangelsk QC_hit: No
# Query_time: 0.013606 Lock_time: 0.013230 Rows_sent: 27 Rows_examined: 29
use arhangelsk;
SET timestamp=1490539007;
SELECT xInterfaceCalled FROM SC_interface_interfaces WHERE xInterfaceCaller='51_cpt_connector';
# User@Host: admin_[admin_] @ localhost []
# Thread_id: 16 Schema: admin_ QC_hit: No
# Query_time: 0.000414 Lock_time: 0.000104 Rows_sent: 2 Rows_examined: 52
use admin_;
SET timestamp=1490539007;
SELECT a.customerID, a.Login, a.Email, a.first_name, a.last_name, b.zip, d.zone_name_ru as state, b.city, b.address, c.reg_field_value as tel FROM SC_customers a left join SC_customer_addresses b on b.addressID = a.addressID left join SC_customer_reg_fields_values c on (a.customerID = c.customerID and c.reg_field_ID = 1) left join SC_zones d on d.zoneID=b.zoneID limit 20, 2;
# User@Host: admin_[admin_] @ localhost []
# Thread_id: 22 Schema: admin_ QC_hit: No
# Query_time: 0.048768 Lock_time: 0.010843 Rows_sent: 2 Rows_examined: 159
SET timestamp=1490539007;
SELECT a.orderID, a.customerID, d.Login, a.order_time, a.customers_comment, e.product_code, b.Price, b.Quantity FROM SC_orders a left join SC_ordered_carts b on a.orderID = b.orderID left join SC_shopping_cart_items c on b.itemID = c.itemID left join SC_products e on e.productID = c.productID left join SC_customers d on a.customerID = d.customerID where a.statusID = 2 order by a.orderID limit 0, 2;
# User@Host: admin_[admin_] @ localhost []
# Thread_id: 28 Schema: admin_ QC_hit: No
# Query_time: 0.001608 Lock_time: 0.000080 Rows_sent: 2 Rows_examined: 171
SET timestamp=1490539007;
SELECT a.orderID, a.customerID, d.Login, a.order_time, a.customers_comment, e.product_code, b.Price, b.Quantity FROM SC_orders a left join SC_ordered_carts b on a.orderID = b.orderID left join SC_shopping_cart_items c on b.itemID = c.itemID left join SC_products e on e.productID = c.productID left join SC_customers d on a.customerID = d.customerID where a.statusID = 2 order by a.orderID limit 12, 2;
# Time: 170326 17:36:48
# User@Host: admin_[admin_] @ localhost []
# Thread_id: 34 Schema: admin_ QC_hit: No
# Query_time: 0.001785 Lock_time: 0.000127 Rows_sent: 2 Rows_examined: 183
SET timestamp=1490539008;
SELECT a.orderID, a.customerID, d.Login, a.order_time, a.customers_comment, e.product_code, b.Price, b.Quantity FROM SC_orders a left join SC_ordered_carts b on a.orderID = b.orderID left join SC_shopping_cart_items c on b.itemID = c.itemID left join SC_products e on e.productID = c.productID left join SC_customers d on a.customerID = d.customerID where a.statusID = 2 order by a.orderID limit 24, 2;
# Time: 170326 17:36:52
# User@Host: joshkar-ola[joshkar-ola] @ localhost []
# Thread_id: 39 Schema: joshkar-ola QC_hit: No
# Query_time: 0.013647 Lock_time: 0.013579 Rows_sent: 1 Rows_examined: 1
use joshkar-ola;
SET timestamp=1490539012;
SELECT 1 FROM `SC_divisions` LIMIT 1;
# Time: 170326 17:36:53
# User@Host: joshkar-ola[joshkar-ola] @ localhost []
# Thread_id: 39 Schema: joshkar-ola QC_hit: No
# Query_time: 0.007601 Lock_time: 0.000593 Rows_sent: 0 Rows_examined: 8302
SET timestamp=1490539013;
SELECT * FROM SC_products WHERE slug=58560;
# Time: 170326 17:40:01
# User@Host: admin[admin] @ localhost []
# Thread_id: 41 Schema: psa QC_hit: No
# Query_time: 0.000521 Lock_time: 0.000368 Rows_sent: 0 Rows_examined: 1
use psa;
SET timestamp=1490539201;
DELETE FROM `SessionContexts` WHERE (`sessionId` IN (SELECT `sessions`.`sess_id` FROM `sessions` AS `sessions` WHERE (`modified` + `lifetime` < 1490539201)));
# Time: 170326 17:40:39
# User@Host: rostov-na-donu[rostov-na-donu] @ localhost []
# Thread_id: 42 Schema: rostov-na-donu QC_hit: No
# Query_time: 0.012906 Lock_time: 0.005042 Rows_sent: 464 Rows_examined: 464
use rostov-na-donu;
SET timestamp=1490539239;
SELECT `settingsID`, `settings_constant_name`, `settings_value` FROM `SC_settings`;
# Time: 170326 17:44:30
# User@Host: irkutsk[irkutsk] @ localhost []
# Thread_id: 44 Schema: irkutsk QC_hit: No
# Query_time: 0.000083 Lock_time: 0.000030 Rows_sent: 0 Rows_examined: 10
use irkutsk;
SET timestamp=1490539470;
SELECT 1 AS isSet,set_arbitrarily,optionID FROM SC_category__product_options WHERE categoryID=40712;
# Time: 170326 17:45:25
# User@Host: admin[admin] @ localhost []
# Thread_id: 46 Schema: psa QC_hit: No
# Query_time: 0.000394 Lock_time: 0.000148 Rows_sent: 0 Rows_examined: 2
use psa;
SET timestamp=1490539525;
SELECT password, type FROM accounts AS a JOIN sys_users AS s ON (a.id = s.account_id) WHERE s.login='0' AND (SELECT count(*) FROM hosting AS h WHERE h.sys_user_id = s.id) = 0 AND (SELECT count(*) FROM web_users AS w WHERE w.sys_user_id = s.id) = 0 AND (SELECT count(*) FROM ftp_users AS f WHERE f.sys_user_id = s.id) = 0;
# Time: 170326 17:45:35
# User@Host: admin[admin] @ localhost []
# Thread_id: 49 Schema: psa QC_hit: No
# Query_time: 0.000388 Lock_time: 0.000146 Rows_sent: 0 Rows_examined: 2
SET timestamp=1490539535;
SELECT password, type FROM accounts AS a JOIN sys_users AS s ON (a.id = s.account_id) JOIN hosting AS h ON (h.sys_user_id = s.id) JOIN domains d ON (h.dom_id = d.id) WHERE d.status = 0 AND s.login='1111';
# Time: 170326 17:45:42
# User@Host: admin[admin] @ localhost []
# Thread_id: 50 Schema: psa QC_hit: No
# Query_time: 0.000152 Lock_time: 0.000050 Rows_sent: 0 Rows_examined: 0
SET timestamp=1490539542;
SELECT password, type FROM accounts AS a JOIN sys_users AS s ON (a.id = s.account_id) JOIN web_users AS w ON (w.sys_user_id = s.id) JOIN domains d ON (w.dom_id = d.id) WHERE d.status = 0 AND s.login='1234';
# Time: 170326 17:45:45
# User@Host: admin[admin] @ localhost []
# Thread_id: 51 Schema: psa QC_hit: No
# Query_time: 0.000142 Lock_time: 0.000045 Rows_sent: 0 Rows_examined: 0
SET timestamp=1490539545;
SELECT password, type FROM accounts AS a JOIN sys_users AS s ON (a.id = s.account_id) JOIN ftp_users AS f ON (f.sys_user_id = s.id) JOIN domains d ON (f.dom_id = d.id) WHERE d.status = 0 AND s.login='admin';
# Time: 170326 17:46:43
# User@Host: admin[admin] @ localhost []
# Thread_id: 73 Schema: psa QC_hit: No
# Query_time: 0.000400 Lock_time: 0.000149 Rows_sent: 0 Rows_examined: 2
SET timestamp=1490539603;
SELECT password, type FROM accounts AS a JOIN sys_users AS s ON (a.id = s.account_id) JOIN hosting AS h ON (h.sys_user_id = s.id) JOIN domains d ON (h.dom_id = d.id) WHERE d.status = 0 AND s.login='guest';
# Time: 170326 17:46:52
# User@Host: admin[admin] @ localhost []
# Thread_id: 74 Schema: psa QC_hit: No
# Query_time: 0.000172 Lock_time: 0.000065 Rows_sent: 0 Rows_examined: 0
SET timestamp=1490539612;
SELECT password, type FROM accounts AS a JOIN sys_users AS s ON (a.id = s.account_id) JOIN web_users AS w ON (w.sys_user_id = s.id) JOIN domains d ON (w.dom_id = d.id) WHERE d.status = 0 AND s.login='operator';
# Time: 170326 17:46:54
# User@Host: admin[admin] @ localhost []
# Thread_id: 75 Schema: psa QC_hit: No
# Query_time: 0.000137 Lock_time: 0.000045 Rows_sent: 0 Rows_examined: 0
SET timestamp=1490539614;
SELECT password, type FROM accounts AS a JOIN sys_users AS s ON (a.id = s.account_id) JOIN ftp_users AS f ON (f.sys_user_id = s.id) JOIN domains d ON (f.dom_id = d.id) WHERE d.status = 0 AND s.login='osmc';
# Time: 170326 17:47:02
# User@Host: admin[admin] @ localhost []
# Thread_id: 76 Schema: psa QC_hit: No
# Query_time: 0.000279 Lock_time: 0.000088 Rows_sent: 0 Rows_examined: 2
SET timestamp=1490539622;
SELECT password, type FROM accounts AS a JOIN sys_users AS s ON (a.id = s.account_id) WHERE s.login='pi' AND (SELECT count(*) FROM hosting AS h WHERE h.sys_user_id = s.id) = 0 AND (SELECT count(*) FROM web_users AS w WHERE w.sys_user_id = s.id) = 0 AND (SELECT count(*) FROM ftp_users AS f WHERE f.sys_user_id = s.id) = 0;
# Time: 170326 17:47:06
# User@Host: admin[admin] @ localhost []
# Thread_id: 78 Schema: psa QC_hit: No
# Query_time: 0.000143 Lock_time: 0.000047 Rows_sent: 0 Rows_examined: 0
SET timestamp=1490539626;
SELECT password, type FROM accounts AS a JOIN sys_users AS s ON (a.id = s.account_id) JOIN ftp_users AS f ON (f.sys_user_id = s.id) JOIN domains d ON (f.dom_id = d.id) WHERE d.status = 0 AND s.login='root';
# Time: 170326 17:47:30
# User@Host: admin[admin] @ localhost []
# Thread_id: 88 Schema: psa QC_hit: No
# Query_time: 0.000405 Lock_time: 0.000148 Rows_sent: 0 Rows_examined: 2
SET timestamp=1490539650;
SELECT password, type FROM accounts AS a JOIN sys_users AS s ON (a.id = s.account_id) JOIN hosting AS h ON (h.sys_user_id = s.id) JOIN domains d ON (h.dom_id = d.id) WHERE d.status = 0 AND s.login='telecomadmin';
# Time: 170326 17:47:36
# User@Host: admin[admin] @ localhost []
# Thread_id: 89 Schema: psa QC_hit: No
# Query_time: 0.000163 Lock_time: 0.000056 Rows_sent: 0 Rows_examined: 0
SET timestamp=1490539656;
SELECT password, type FROM accounts AS a JOIN sys_users AS s ON (a.id = s.account_id) JOIN web_users AS w ON (w.sys_user_id = s.id) JOIN domains d ON (w.dom_id = d.id) WHERE d.status = 0 AND s.login='test';
# Time: 170326 17:47:39
# User@Host: admin[admin] @ localhost []
# Thread_id: 90 Schema: psa QC_hit: No
# Query_time: 0.000229 Lock_time: 0.000092 Rows_sent: 0 Rows_examined: 0
SET timestamp=1490539659;
SELECT password, type FROM accounts AS a JOIN sys_users AS s ON (a.id = s.account_id) JOIN ftp_users AS f ON (f.sys_user_id = s.id) JOIN domains d ON (f.dom_id = d.id) WHERE d.status = 0 AND s.login='ubnt';
# Time: 170326 17:47:41
# User@Host: admin[admin] @ localhost []
# Thread_id: 91 Schema: psa QC_hit: No
# Query_time: 0.000311 Lock_time: 0.000097 Rows_sent: 0 Rows_examined: 2
SET timestamp=1490539661;
SELECT password, type FROM accounts AS a JOIN sys_users AS s ON (a.id = s.account_id) WHERE s.login='user' AND (SELECT count(*) FROM hosting AS h WHERE h.sys_user_id = s.id) = 0 AND (SELECT count(*) FROM web_users AS w WHERE w.sys_user_id = s.id) = 0 AND (SELECT count(*) FROM ftp_users AS f WHERE f.sys_user_id = s.id) = 0;
# Time: 170326 17:48:13
# User@Host: ufa[ufa] @ localhost []
# Thread_id: 100 Schema: ufa QC_hit: No
# Query_time: 0.009906 Lock_time: 0.008094 Rows_sent: 5 Rows_examined: 10
use ufa;
SET timestamp=1490539693;
SELECT *, `aux_page_name_ru` AS aux_page_name FROM SC_aux_pages ORDER BY `aux_page_priority` ASC;

[ga01]

Извините если заспамил

[ga01]

Отправил через phpmyadmin SQL запрос на свой сервер в свою БД

SELECT a.customerID, a.Login, a.Email, a.first_name, a.last_name, b.zip, d.zone_name_ru as state, b.city, b.address, c.reg_field_value as tel FROM SC_customers a left join SC_customer_addresses b on b.addressID = a.addressID left join SC_customer_reg_fields_values c on (a.customerID = c.customerID and c.reg_field_ID = 1) left join SC_zones d on d.zoneID=b.zoneID limit 2, 2;

Результат - нет ошибок, если отправляю через 1с то приходит ошибка в 1с:

Ошибка SQL, You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'limit 0, 4' at line 1

[ga01]

Где в Veste включить логирование SQL запросов и ответов ?

[ga01]

Ошибка возникает в связи что БД не может распознать запрос из 1с ?

Или ошибка возникает с связи с тем что 1с не может распознать ответный запрос из БД ?

[skurudo]

my.cnf в секции [mysqld]

для mysql < 5.1.29

Code: Select all

log   = /path/to/query.log 

или
для mysql > 5.1.29

Code: Select all

general_log_file = /path/to/query.log
general_log      = 1