При работе в крупных базах данных на платформе 1С:Предприятие, особенно использующих конфигурацию 1С:Управление производственным предприятием (УПП) или аналогичные решения на обычных формах, администраторы и разработчики могут столкнуться со специфической системной ошибкой СУБД MS SQL Server. Ошибка возникает при попытке проведения, отмены проведения или перезаписи конкретного документа и сопровождается сообщением: «Ошибка использования операции 'ОБЪЕДИНИТЬ' ('UNION'). Допустимо объединение не более 256 результатов запросов».
Эта проблема парализует работу пользователей с конкретным документом, а стандартное «Тестирование и исправление» (ТиИ) встроенными средствами 1С часто оказывается невозможным из-за колоссального объема базы данных или падает с этой же ошибкой. В этой статье мы подробно разберем причины возникновения данной ошибки, проанализируем механизм работы СУБД и платформы 1С, а также рассмотрим пошаговые методы локализации и решения проблемы средствами MS SQL Server и оптимизации кода.
Для начала проанализируем, почему платформа 1С генерирует SQL-запрос, содержащий такое огромное количество объединений (UNION). Существуют две основные причины возникновения этой ситуации:
ДокументСсылка, включающий в себя более 256 возможных типов документов), транслятор запросов 1С при формировании T-SQL запроса будет вынужден соединить таблицы всех этих типов документов. В зависимости от контекста это выливается либо в бесконечные цепочки LEFT OUTER JOIN, либо в гигантские конструкции UNION. Как только число типов превышает жесткий лимит MS SQL (256 таблиц в объединении), сервер баз данных возвращает ошибку.Ниже мы подробно рассмотрим пошаговые сценарии борьбы с обеими причинами.
Если конфигурация дорабатывалась или ошибка плавающая, нам необходимо увидеть «врага в лицо» — то есть получить сам T-SQL запрос, на который ругается MS SQL Server. Для этого мы воспользуемся утилитой SQL Server Profiler.
Разберем по шагам, как настроить трассировку:
RPC:Completed (выполнение хранимых процедур);SQL:BatchCompleted (выполнение пакетов инструкций SQL);User Error Message (для фиксации самой ошибки).DatabaseName) и по тексту ошибки в поле TextData (например, добавим фильтр со значением %UNION%).В окне профайлера мы увидим событие ошибки и непосредственно перед ним — гигантский SQL-запрос, отправленный платформой. Скопируем этот запрос в SQL Server Management Studio (SSMS) для дальнейшего анализа. Если в запросе фигурируют обращения к сотням таблиц документов через UNION, переходим к исправлению кода.
Если анализ через профайлер показал, что платформа пытается разыменовать реквизит составного типа, программисту необходимо вмешаться в код проведения документа (модуль объекта или связанные общие модули проведения).
Для предотвращения неявного левого соединения или объединения со всеми таблицами типов, входящих в составной тип, мы должны жестко ограничить список допустимых типов в запросе с помощью конструкции ВЫРАЗИТЬ.
Рассмотрим пример. Допустим, у нас есть запрос, который получает реквизит Организация из составного реквизита ДокументОснование:
ВЫБРАТЬ
Продажи.ДокументОснование.Организация КАК Организация,
Продажи.СуммаОборот КАК Сумма
ИЗ
РегистрНакопления.Продажи.Обороты КАК Продажи
Если в состав типа ДокументОснование входит 300 различных документов, СУБД попытается объединить их все. Чтобы избежать этого, перепишем запрос, жестко указав типы, которые реально могут выступать основанием в данном контексте:
ВЫБРАТЬ
ВЫБОР
КОГДА Продажи.ДокументОснование ССЫЛКА Документ.РеализацияТоваровУслуг
ТОГДА ВЫРАЗИТЬ(Продажи.ДокументОснование КАК Документ.РеализацияТоваровУслуг).Организация
КОГДА Продажи.ДокументОснование ССЫЛКА Документ.ОтчетОРозничныхПродажах
ТОГДА ВЫРАЗИТЬ(Продажи.ДокументОснование КАК Документ.ОтчетОРозничныхПродажах).Организация
ИНАЧЕ ЗНАЧЕНИЕ(Справочник.Организации.ПустаяСсылка)
КОНЕЦ КАК Организация,
Продажи.СуммаОборот КАК Сумма
ИЗ
РегистрНакопления.Продажи.Обороты КАК Продажи
Такой подход полностью снимает нагрузку с СУБД, убирает лишние `UNION` и оптимизирует скорость проведения документа в разы.
Если конфигурация абсолютно типовая, код запросов не менялся, а ошибка возникает только на одном конкретном документе, мы имеем дело с нарушением логической или физической целостности БД. Для исправления этой ситуации воспользуемся возможностями СУБД.
Важно: Перед выполнением любых операций по восстановлению и исправлению структуры базы данных в MS SQL обязательно сделайте резервную копию (бэкап) базы данных!
Рассмотрим последовательность выполнения команд в консоли SQL Server Management Studio (SSMS):
Шаг 1. Выполним проверку базы данных на наличие логических и физических ошибок. Запустим следующий скрипт:
DBCC CHECKDB ('Имя_Вашей_Базы') WITH NO_INFOMSGS, ALL_ERRORMSGS;
DBCC CHECKCATALOG ('Имя_Вашей_Базы');
Если утилита сообщила о наличии ошибок согласованности (consistency errors) или ошибок распределения страниц (allocation errors), базу необходимо лечить.
Шаг 2. Переведем базу данных в однопользовательский режим, чтобы заблокировать любые подключения пользователей и фоновые задания платформы 1С:
ALTER DATABASE [Имя_Вашей_Базы] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
Шаг 3. Запустим процедуру восстановления. Обратите внимание, что исправление системных таблиц часто возможно только с параметром REPAIR_ALLOW_DATA_LOSS (исправление с возможной потерей поврежденных данных). С СУБД MS SQL 2008 это наиболее действенный инструмент:
DBCC CHECKDB ('Имя_Вашей_Базы', REPAIR_ALLOW_DATA_LOSS);
Шаг 4. Вернем базу данных в нормальный многопользовательский режим функционирования:
ALTER DATABASE [Имя_Вашей_Базы] SET MULTI_USER;
После проведения этих манипуляций логические связи в СУБД будут восстановлены, и ошибка трансляции запроса должна исчезнуть.
В некоторых случаях «битые» данные могут локализоваться в индексах таблиц итогов регистров сведений, накопления или бухгалтерии. Полное перестроение индексов решает эту проблему без риска потери данных.
Выполним последовательно следующие команды в SSMS:
EXEC sp_msforeachtable 'ALTER INDEX ALL ON ? REBUILD';
EXEC sp_updatestats;
DBCC FREEPROCCACHE;
Если база данных настолько огромна, что проверка всей базы через DBCC CHECKDB занимает слишком много времени, а битый документ нужно оперативно удалить, мы можем прибегнуть к прямой очистке таблиц СУБД. Это крайняя мера, требующая осторожности.
Проанализируем алгоритм точечного удаления документа:
1. Сначала нам необходимо узнать имя физической таблицы документа в СУБД. В платформе 1С для этого выполним код (например, во внешней обработке):
Сообщить(ПолучитьСтруктуруХраненияБазыДанных("Документ.ПоступлениеТоваровУслуг").ИмяТаблицыХранения);
Допустим, имя таблицы СУБД — _Document150.
2. Найдем уникальный идентификатор (GUID) битого документа. Это можно сделать в самой 1С, получив ссылку на документ и выполнив метод ДокументСсылка.УникальныйИдентификатор(). Пример GUID: 80e10015-5d01-0a01-11e3-c88012345678.
3. В СУБД MS SQL идентификаторы хранятся в бинарном формате binary(16) в поле _IDRRef. Для поиска битого документа по его дате и номеру (чтобы не запутаться с конвертацией GUID), выполним запрос в SSMS:
SELECT _IDRRef, _Number, _Date_Time
FROM dbo._Document150
WHERE _Number = '00000000123' AND _Date_Time >= '2023-01-01';
Скопируем шестнадцатеричное значение _IDRRef (оно будет выглядеть примерно так: 0x9CE800155D010A0111E3C88012345678).
4. Удалим запись из основной таблицы документа и таблиц его табличных частей (например, _Document150_VT1234):
DELETE FROM dbo._Document150_VT1234 WHERE _Document150_IDRRef = 0x9CE800155D010A0111E3C88012345678;
DELETE FROM dbo._Document150 WHERE _IDRRef = 0x9CE800155D010A0111E3C88012345678;
5. Также необходимо удалить зависшие записи движений по регистрам накопления и бухгалтерии для этого регистратора. Имена таблиц движений можно получить аналогично через ПолучитьСтруктуруХраненияБазыДанных(). В этих таблицах поиск ведется по полю _RecorderRRef:
DELETE FROM dbo._AccumReg123 WHERE _RecorderRRef = 0x9CE800155D010A0111E3C88012345678;
После удаления битого документа напрямую из SQL, зайдем в 1С и убедимся, что документ исчез из журналов и больше не вызывает критических ошибок блокировки транзакций.
При использовании старых версий MS SQL Server (таких как MS SQL 2008 / 2008 R2) базы данных 1С иногда по наследству несут уровень совместимости (Compatibility Level), соответствующий SQL Server 2000 (уровень 80). В этом режиме СУБД накладывает критические ограничения на структуру планов запросов и использование некоторых видов индексации.
Рекомендуется проверить это свойство базы данных:
Подводя итог, отметим: появление ошибки ограничения количества объединений UNION — это всегда сигнал к действию. Совместное использование SQL Profiler для анализа кода и утилит проверки базы данных DBCC гарантирует быстрое и безопасное восстановление работоспособности системы без риска парализовать работу крупного предприятия.