Как исправить ошибку sql

Привет, Хабр! Представляю вашему вниманию перевод статьи «Error and Transaction Handling in SQL Server. Part One – Jumpstart Error Handling» автора Erland Sommarskog.

1. Введение

Эта статья – первая в серии из трёх статей, посвященных обработке ошибок и транзакций в SQL Server. Её цель – дать вам быстрый старт в теме обработки ошибок, показав базовый пример, который подходит для большей части вашего кода. Эта часть написана в расчете на неопытного читателя, и по этой причине я намеренно умалчиваю о многих деталях. В данный момент задача состоит в том, чтобы рассказать как без упора на почему. Если вы принимаете мои слова на веру, вы можете прочесть только эту часть и отложить остальные две для дальнейших этапов в вашей карьере.

С другой стороны, если вы ставите под сомнение мои рекомендации, вам определенно необходимо прочитать две остальные части, где я погружаюсь в детали намного более глубоко, исследуя очень запутанный мир обработки ошибок и транзакций в SQL Server. Вторая и третья части, так же, как и три приложения, предназначены для читателей с более глубоким опытом. Первая статья — короткая, вторая и третья значительно длиннее.

Все статьи описывают обработку ошибок и транзакций в SQL Server для версии 2005 и более поздних версий.

1.1 Зачем нужна обработка ошибок?

Почему мы обрабатываем ошибки в нашем коде? На это есть много причин. Например, на формах в приложении мы проверяем введенные данные и информируем пользователей о допущенных при вводе ошибках. Ошибки пользователя – это предвиденные ошибки. Но нам также нужно обрабатывать непредвиденные ошибки. То есть, ошибки могут возникнуть из-за того, что мы что-то упустили при написании кода. Простой подход – это прервать выполнение или хотя бы вернуться на этап, в котором мы имеем полный контроль над происходящим. Недостаточно будет просто подчеркнуть, что совершенно непозволительно игнорировать непредвиденные ошибки. Это недостаток, который может вызвать губительные последствия: например, стать причиной того, что приложение будет предоставлять некорректную информацию пользователю или, что еще хуже, сохранять некорректные данные в базе. Также важно сообщать о возникновении ошибки с той целью, чтобы пользователь не думал о том, что операция прошла успешно, в то время как ваш код на самом деле ничего не выполнил.

Мы часто хотим, чтобы в базе данных изменения были атомарными. Например, задача по переводу денег с одного счета на другой. С этой целью мы должны изменить две записи в таблице CashHoldings и добавить две записи в таблицу Transactions. Абсолютно недопустимо, чтобы ошибки или сбой привели к тому, что деньги будут переведены на счет получателя, а со счета отправителя они не будут списаны. По этой причине обработка ошибок также касается и обработки транзакций. В приведенном примере нам нужно обернуть операцию в BEGIN TRANSACTION и COMMIT TRANSACTION, но не только это: в случае ошибки мы должны убедиться, что транзакция откачена.

2. Основные команды

Мы начнем с обзора наиболее важных команд, которые необходимы для обработки ошибок. Во второй части я опишу все команды, относящиеся к обработке ошибок и транзакций.

2.1 TRY-CATCH

Основным механизмом обработки ошибок является конструкция TRY-CATCH, очень напоминающая подобные конструкции в других языках. Структура такова:

BEGIN TRY
   <обычный код>
END TRY
BEGIN CATCH
   <обработка ошибок>
END CATCH

Если какая-либо ошибка появится в <обычный код>, выполнение будет переведено в блок CATCH, и будет выполнен код обработки ошибок.

Как правило, в CATCH откатывают любую открытую транзакцию и повторно вызывают ошибку. Таким образом, вызывающая клиентская программа понимает, что что-то пошло не так. Повторный вызов ошибки мы обсудим позже в этой статье.

Вот очень быстрый пример:

BEGIN TRY
   DECLARE @x int
   SELECT @x = 1/0
   PRINT 'Not reached'
END TRY
BEGIN CATCH 
   PRINT 'This is the error: ' + error_message()
END CATCH

Результат выполнения: This is the error: Divide by zero error encountered.

Мы вернемся к функции error_message() позднее. Стоит отметить, что использование PRINT в обработчике CATCH приводится только в рамках экспериментов и не следует делать так в коде реального приложения.

Если <обычный код> вызывает хранимую процедуру или запускает триггеры, то любая ошибка, которая в них возникнет, передаст выполнение в блок CATCH. Если более точно, то, когда возникает ошибка, SQL Server раскручивает стек до тех пор, пока не найдёт обработчик CATCH. И если такого обработчика нет, SQL Server отправляет сообщение об ошибке напрямую клиенту.

Есть одно очень важное ограничение у конструкции TRY-CATCH, которое нужно знать: она не ловит ошибки компиляции, которые возникают в той же области видимости. Рассмотрим пример:

CREATE PROCEDURE inner_sp AS
   BEGIN TRY
      PRINT 'This prints'
      SELECT * FROM NoSuchTable
      PRINT 'This does not print'
   END TRY
   BEGIN CATCH
      PRINT 'And nor does this print'
   END CATCH
go
EXEC inner_sp

Выходные данные:

This prints
Msg 208, Level 16, State 1, Procedure inner_sp, Line 4
Invalid object name 'NoSuchTable'

Как можно видеть, блок TRY присутствует, но при возникновении ошибки выполнение не передается блоку CATCH, как это ожидалось. Это применимо ко всем ошибкам компиляции, таким как пропуск колонок, некорректные псевдонимы и тому подобное, которые возникают во время выполнения. (Ошибки компиляции могут возникнуть в SQL Server во время выполнения из-за отложенного разрешения имен – особенность, благодаря которой SQL Server позволяет создать процедуру, которая обращается к несуществующим таблицам.)

Эти ошибки не являются полностью неуловимыми; вы не можете поймать их в области, в которой они возникают, но вы можете поймать их во внешней области. Добавим такой код к предыдущему примеру:

CREATE PROCEDURE outer_sp AS
   BEGIN TRY
      EXEC inner_sp
   END TRY
   BEGIN CATCH
      PRINT 'The error message is: ' + error_message()
   END CATCH
go
EXEC outer_sp

Теперь мы получим на выходе это:

This prints
The error message is: Invalid object name 'NoSuchTable'.

На этот раз ошибка была перехвачена, потому что сработал внешний обработчик CATCH.

2.2 SET XACT_ABORT ON

В начало ваших хранимых процедур следует всегда добавлять это выражение:

SET XACT_ABORT, NOCOUNT ON

Оно активирует два параметра сессии, которые выключены по умолчанию в целях совместимости с предыдущими версиями, но опыт доказывает, что лучший подход – это иметь эти параметры всегда включенными. Поведение SQL Server по умолчанию в той ситуации, когда не используется TRY-CATCH, заключается в том, что некоторые ошибки прерывают выполнение и откатывают любые открытые транзакции, в то время как с другими ошибками выполнение последующих инструкций продолжается. Когда вы включаете XACT_ABORT ON, почти все ошибки начинают вызывать одинаковый эффект: любая открытая транзакция откатывается, и выполнение кода прерывается. Есть несколько исключений, среди которых наиболее заметным является выражение RAISERROR.

Параметр XACT_ABORT необходим для более надежной обработки ошибок и транзакций. В частности, при настройках по умолчанию есть несколько ситуаций, когда выполнение может быть прервано без какого-либо отката транзакции, даже если у вас есть TRY-CATCH. Мы видели такой пример в предыдущем разделе, где мы выяснили, что TRY-CATCH не перехватывает ошибки компиляции, возникшие в той же области. Открытая транзакция, которая не была откачена из-за ошибки, может вызвать серьезные проблемы, если приложение работает дальше без завершения транзакции или ее отката.

Для надежной обработки ошибок в SQL Server вам необходимы как TRY-CATCH, так и SET XACT_ABORT ON. Среди них инструкция SET XACT_ABORT ON наиболее важна. Если для кода на промышленной среде только на нее полагаться не стоит, то для быстрых и простых решений она вполне подходит.

Параметр NOCOUNT не имеет к обработке ошибок никакого отношения, но включение его в код является хорошей практикой. NOCOUNT подавляет сообщения вида (1 row(s) affected), которые вы можете видеть в панели Message в SQL Server Management Studio. В то время как эти сообщения могут быть полезны при работе c SSMS, они могут негативно повлиять на производительность в приложении, так как увеличивают сетевой трафик. Сообщение о количестве строк также может привести к ошибке в плохо написанных клиентских приложениях, которые могут подумать, что это данные, которые вернул запрос.

Выше я использовал синтаксис, который немного необычен. Большинство людей написали бы два отдельных выражения:

SET NOCOUNT ON
SET XACT_ABORT ON

Между ними нет никакого отличия. Я предпочитаю версию с SET и запятой, т.к. это снижает уровень шума в коде. Поскольку эти выражения должны появляться во всех ваших хранимых процедурах, они должны занимать как можно меньше места.

3. Основной пример обработки ошибок

После того, как мы посмотрели на TRY-CATCH и SET XACT_ABORT ON, давайте соединим их вместе в примере, который мы можем использовать во всех наших хранимых процедурах. Для начала я покажу пример, в котором ошибка генерируется в простой форме, а в следующем разделе я рассмотрю решения получше.

Для примера я буду использовать эту простую таблицу.

CREATE TABLE sometable(a int NOT NULL,
                       b int NOT NULL,
                       CONSTRAINT pk_sometable PRIMARY KEY(a, b))

Вот хранимая процедура, которая демонстрирует, как вы должны работать с ошибками и транзакциями.

CREATE PROCEDURE insert_data @a int, @b int AS 
   SET XACT_ABORT, NOCOUNT ON
   BEGIN TRY
      BEGIN TRANSACTION
      INSERT sometable(a, b) VALUES (@a, @b)
      INSERT sometable(a, b) VALUES (@b, @a)
      COMMIT TRANSACTION
   END TRY
   BEGIN CATCH
      IF @@trancount > 0 ROLLBACK TRANSACTION
      DECLARE @msg nvarchar(2048) = error_message()  
      RAISERROR (@msg, 16, 1)
      RETURN 55555
   END CATCH

Первая строка в процедуре включает XACT_ABORT и NOCOUNT в одном выражении, как я показывал выше. Эта строка – единственная перед BEGIN TRY. Все остальное в процедуре должно располагаться после BEGIN TRY: объявление переменных, создание временных таблиц, табличных переменных, всё. Даже если у вас есть другие SET-команды в процедуре (хотя причины для этого встречаются редко), они должны идти после BEGIN TRY.

Причина, по которой я предпочитаю указывать SET XACT_ABORT и NOCOUNT перед BEGIN TRY, заключается в том, что я рассматриваю это как одну строку шума: она всегда должна быть там, но я не хочу, чтобы это мешало взгляду. Конечно же, это дело вкуса, и если вы предпочитаете ставить SET-команды после BEGIN TRY, ничего страшного. Важно то, что вам не следует ставить что-либо другое перед BEGIN TRY.

Часть между BEGIN TRY и END TRY является основной составляющей процедуры. Поскольку я хотел использовать транзакцию, определенную пользователем, я ввел довольно надуманное бизнес-правило, в котором говорится, что если вы вставляете пару, то обратная пара также должна быть вставлена. Два выражения INSERT находятся внутри BEGIN и COMMIT TRANSACTION. Во многих случаях у вас будет много строк кода между BEGIN TRY и BEGIN TRANSACTION. Иногда у вас также будет код между COMMIT TRANSACTION и END TRY, хотя обычно это только финальный SELECT, возвращающий данные или присваивающий значения выходным параметрам. Если ваша процедура не выполняет каких-либо изменений или имеет только одно выражение INSERT/UPDATE/DELETE/MERGE, то обычно вам вообще не нужно явно указывать транзакцию.

В то время как блок TRY будет выглядеть по-разному от процедуры к процедуре, блок CATCH должен быть более или менее результатом копирования и вставки. То есть вы делаете что-то короткое и простое и затем используете повсюду, не особо задумываясь. Обработчик CATCH, приведенный выше, выполняет три действия:

1. Откатывает любые открытые транзакции.
2. Повторно вызывает ошибку.
3. Убеждается, что возвращаемое процедурой значение отлично от нуля.

Эти три действия должны всегда быть там. Мы можете возразить, что строка

IF @@trancount > 0 ROLLBACK TRANSACTION

не нужна, если нет явной транзакции в процедуре, но это абсолютно неверно. Возможно, вы вызываете хранимую процедуру, которая открывает транзакцию, но которая не может ее откатить из-за ограничений TRY-CATCH. Возможно, вы или кто-то другой добавите явную транзакцию через два года. Вспомните ли вы тогда о том, что нужно добавить строку с откатом? Не рассчитывайте на это. Я также слышу читателей, которые возражают, что если тот, кто вызывает процедуру, открыл транзакцию, мы не должны ее откатывать… Нет, мы должны, и если вы хотите знать почему, вам нужно прочитать вторую и третью части. Откат транзакции в обработчике CATCH – это категорический императив, у которого нет исключений.

Код повторной генерации ошибки включает такую строку:

DECLARE @msg nvarchar(2048) = error_message()

Встроенная функция error_message() возвращает текст возникшей ошибки. В следующей строке ошибка повторно вызывается с помощью выражения RAISERROR. Это не самый простой способ вызова ошибки, но он работает. Другие способы мы рассмотрим в следующей главе.

Замечание: синтаксис для присвоения начального значения переменной в DECLARE был внедрен в SQL Server 2008. Если у вас SQL Server 2005, вам нужно разбить строку на DECLARE и выражение SELECT.

Финальное выражение RETURN – это страховка. RAISERROR никогда не прерывает выполнение, поэтому выполнение следующего выражения будет продолжено. Пока все процедуры используют TRY-CATCH, а также весь клиентский код обрабатывает исключения, нет повода для беспокойства. Но ваша процедура может быть вызвана из старого кода, написанного до SQL Server 2005 и до внедрения TRY-CATCH. В те времена лучшее, что мы могли делать, это смотреть на возвращаемые значения. То, что вы возвращаете с помощью RETURN, не имеет особого значения, если это не нулевое значение (ноль обычно обозначает успешное завершение работы).

Последнее выражение в процедуре – это END CATCH. Никогда не следует помещать какой-либо код после END CATCH. Кто-нибудь, читающий процедуру, может не увидеть этот кусок кода.

После прочтения теории давайте попробуем тестовый пример:

EXEC insert_data 9, NULL

Результат выполнения:

Msg 50000, Level 16, State 1, Procedure insert_data, Line 12
Cannot insert the value NULL into column 'b', table 'tempdb.dbo.sometable'; column does not allow nulls. INSERT fails.

Давайте добавим внешнюю процедуру для того, чтобы увидеть, что происходит при повторном вызове ошибки:

CREATE PROCEDURE outer_sp @a int, @b int AS
   SET XACT_ABORT, NOCOUNT ON
   BEGIN TRY
      EXEC insert_data @a, @b
   END TRY
   BEGIN CATCH
      IF @@trancount > 0 ROLLBACK TRANSACTION
      DECLARE @msg nvarchar(2048) = error_message()
      RAISERROR (@msg, 16, 1)
      RETURN 55555
   END CATCH
go
EXEC outer_sp 8, 8

Результат работы:

Msg 50000, Level 16, State 1, Procedure outer_sp, Line 9
Violation of PRIMARY KEY constraint 'pk_sometable'. Cannot insert duplicate key in object 'dbo.sometable'. The duplicate key value is (8, 8).

Мы получили корректное сообщение об ошибке, но если вы посмотрите на заголовки этого сообщения и на предыдущее поближе, то можете заметить проблему:

Msg 50000, Level 16, State 1, Procedure insert_data, Line 12
Msg 50000, Level 16, State 1, Procedure outer_sp, Line 9

Сообщение об ошибке выводит информацию о расположении конечного выражения RAISERROR. В первом случае некорректен только номер строки. Во втором случае некорректно также имя процедуры. Для простых процедур, таких как наш тестовый пример, это не является большой проблемой. Но если у вас есть несколько уровней вложенных сложных процедур, то наличие сообщения об ошибке с отсутствием указания на место её возникновения сделает поиск и устранение ошибки намного более сложным делом. По этой причине желательно генерировать ошибку таким образом, чтобы можно было определить нахождение ошибочного фрагмента кода быстро, и это то, что мы рассмотрим в следующей главе.

4. Три способа генерации ошибки

4.1 Использование error_handler_sp

Мы рассмотрели функцию error_message(), которая возвращает текст сообщения об ошибке. Сообщение об ошибке состоит из нескольких компонентов, и существует своя функция error_xxx() для каждого из них. Мы можем использовать их для повторной генерации полного сообщения, которое содержит оригинальную информацию, хотя и в другом формате. Если делать это в каждом обработчике CATCH, это будет большой недостаток — дублирование кода. Вам не обязательно находиться в блоке CATCH для вызова error_message() и других подобных функций, и они вернут ту же самую информацию, если будут вызваны из хранимой процедуры, которую выполнит блок CATCH.

Позвольте представить вам error_handler_sp:

CREATE PROCEDURE error_handler_sp AS
 
   DECLARE @errmsg   nvarchar(2048),
           @severity tinyint,
           @state    tinyint,
           @errno    int,
           @proc     sysname,
           @lineno   int
           
   SELECT @errmsg = error_message(), @severity = error_severity(),
          @state  = error_state(), @errno = error_number(),
          @proc   = error_procedure(), @lineno = error_line()
       
   IF @errmsg NOT LIKE '***%'
   BEGIN
      SELECT @errmsg = '*** ' + coalesce(quotename(@proc), '<dynamic SQL>') + 
                       ', Line ' + ltrim(str(@lineno)) + '. Errno ' + 
                       ltrim(str(@errno)) + ': ' + @errmsg
   END
   RAISERROR('%s', @severity, @state, @errmsg)

Первое из того, что делает error_handler_sp – это сохраняет значение всех error_xxx() функций в локальные переменные. Я вернусь к выражению IF через секунду. Вместо него давайте посмотрим на выражение SELECT внутри IF:

SELECT @errmsg = '*** ' + coalesce(quotename(@proc), '<dynamic SQL>') + 
                 ', Line ' + ltrim(str(@lineno)) + '. Errno ' + 
                 ltrim(str(@errno)) + ': ' + @errmsg

Цель этого SELECT заключается в форматировании сообщения об ошибке, которое передается в RAISERROR. Оно включает в себя всю информацию из оригинального сообщения об ошибке, которое мы не можем вставить напрямую в RAISERROR. Мы должны обработать имя процедуры, которое может быть NULL для ошибок в обычных скриптах или в динамическом SQL. Поэтому используется функция COALESCE. (Если вы не понимаете форму выражения RAISERROR, я рассказываю о нем более детально во второй части.)

Отформатированное сообщение об ошибке начинается с трех звездочек. Этим достигаются две цели: 1) Мы можем сразу видеть, что это сообщение вызвано из обработчика CATCH. 2) Это дает возможность для error_handler_sp отфильтровать ошибки, которые уже были сгенерированы один или более раз, с помощью условия NOT LIKE ‘***%’ для того, чтобы избежать изменения сообщения во второй раз.

Вот как обработчик CATCH должен выглядеть, когда вы используете error_handler_sp:

BEGIN CATCH
   IF @@trancount > 0 ROLLBACK TRANSACTION
   EXEC error_handler_sp
   RETURN 55555
END CATCH

Давайте попробуем несколько тестовых сценариев.

EXEC insert_data 8, NULL
EXEC outer_sp 8, 8

Результат выполнения:

Msg 50000, Level 16, State 2, Procedure error_handler_sp, Line 20
*** [insert_data], Line 5. Errno 515: Cannot insert the value NULL into column 'b', table 'tempdb.dbo.sometable'; column does not allow nulls. INSERT fails.
Msg 50000, Level 14, State 1, Procedure error_handler_sp, Line 20
*** [insert_data], Line 6. Errno 2627: Violation of PRIMARY KEY constraint 'pk_sometable'. Cannot insert duplicate key in object 'dbo.sometable'. The duplicate key value is (8, 8).

Заголовки сообщений говорят о том, что ошибка возникла в процедуре error_handler_sp, но текст сообщений об ошибках дает нам настоящее местонахождение ошибки – как название процедуры, так и номер строки.

Я покажу еще два метода вызова ошибок. Однако error_handler_sp является моей главной рекомендацией для читателей, которые читают эту часть. Это — простой вариант, который работает на всех версиях SQL Server начиная с 2005. Существует только один недостаток: в некоторых случаях SQL Server генерирует два сообщения об ошибках, но функции error_xxx() возвращают только одну из них, и поэтому одно из сообщений теряется. Это может быть неудобно при работе с административными командами наподобие BACKUPRESTORE, но проблема редко возникает в коде, предназначенном чисто для приложений.

4.2. Использование ;THROW

В SQL Server 2012 Microsoft представил выражение ;THROW для более легкой обработки ошибок. К сожалению, Microsoft сделал серьезную ошибку при проектировании этой команды и создал опасную ловушку.

С выражением ;THROW вам не нужно никаких хранимых процедур. Ваш обработчик CATCH становится таким же простым, как этот:

BEGIN CATCH
   IF @@trancount > 0 ROLLBACK TRANSACTION
   ;THROW
   RETURN 55555
END CATCH

Достоинство ;THROW в том, что сообщение об ошибке генерируется точно таким же, как и оригинальное сообщение. Если изначально было два сообщения об ошибках, оба сообщения воспроизводятся, что делает это выражение еще привлекательнее. Как и со всеми другими сообщениями об ошибках, ошибки, сгенерированные ;THROW, могут быть перехвачены внешним обработчиком CATCH и воспроизведены. Если обработчика CATCH нет, выполнение прерывается, поэтому оператор RETURN в данном случае оказывается не нужным. (Я все еще рекомендую оставлять его, на случай, если вы измените свое отношение к ;THROW позже).

Если у вас SQL Server 2012 или более поздняя версия, измените определение insert_data и outer_sp и попробуйте выполнить тесты еще раз. Результат в этот раз будет такой:

Msg 515, Level 16, State 2, Procedure insert_data, Line 5
Cannot insert the value NULL into column 'b', table 'tempdb.dbo.sometable'; column does not allow nulls. INSERT fails.
Msg 2627, Level 14, State 1, Procedure insert_data, Line 6
Violation of PRIMARY KEY constraint 'pk_sometable'. Cannot insert duplicate key in object 'dbo.sometable'. The duplicate key value is (8, 8).

Имя процедуры и номер строки верны и нет никакого другого имени процедуры, которое может нас запутать. Также сохранены оригинальные номера ошибок.

В этом месте вы можете сказать себе: действительно ли Microsoft назвал команду ;THROW? Разве это не просто THROW? На самом деле, если вы посмотрите в Books Online, там не будет точки с запятой. Но точка с запятой должны быть. Официально они отделяют предыдущее выражение, но это опционально, и далеко не все используют точку с запятой в выражениях T-SQL. Более важно, что если вы пропустите точку с запятой перед THROW, то не будет никакой синтаксической ошибки. Но это повлияет на поведение при выполнении выражения, и это поведение будет непостижимым для непосвященных. При наличии активной транзакции вы получите сообщение об ошибке, которое будет полностью отличаться от оригинального. И еще хуже, что при отсутствии активной транзакции ошибка будет тихо выведена без обработки. Такая вещь, как пропуск точки с запятой, не должно иметь таких абсурдных последствий. Для уменьшения риска такого поведения, всегда думайте о команде как о ;THROW (с точкой с запятой).

Нельзя отрицать того, что ;THROW имеет свои преимущества, но точка с запятой не единственная ловушка этой команды. Если вы хотите использовать ее, я призываю вас прочитать по крайней мере вторую часть этой серии, где я раскрываю больше деталей о команде ;THROW. До этого момента, используйте error_handler_sp.

4.3. Использование SqlEventLog

Третий способ обработки ошибок – это использование SqlEventLog, который я описываю очень детально в третьей части. Здесь я лишь сделаю короткий обзор.

SqlEventLog предоставляет хранимую процедуру slog.catchhandler_sp, которая работает так же, как и error_handler_sp: она использует функции error_xxx() для сбора информации и выводит сообщение об ошибке, сохраняя всю информацию о ней. Вдобавок к этому, она логирует ошибку в таблицу splog.sqleventlog. В зависимости от типа приложения, которое у вас есть, эта таблица может быть очень ценным объектом.

Для использования SqlEventLog, ваш обработчик CATCH должен быть таким:

BEGIN CATCH
   IF @@trancount > 0 ROLLBACK TRANSACTION
   EXEC slog.catchhandler_sp @@procid
   RETURN 55555
END CATCH

@@procid возвращает идентификатор объекта текущей хранимой процедуры. Это то, что SqlEventLog использует для логирования информации в таблицу. Используя те же тестовые сценарии, получим результат их работы с использованием catchhandler_sp:

Msg 50000, Level 16, State 2, Procedure catchhandler_sp, Line 125
{515} Procedure insert_data, Line 5
Cannot insert the value NULL into column 'b', table 'tempdb.dbo.sometable'; column does not allow nulls. INSERT fails.
Msg 50000, Level 14, State 1, Procedure catchhandler_sp, Line 125
{2627} Procedure insert_data, Line 6
Violation of PRIMARY KEY constraint 'pk_sometable'. Cannot insert duplicate key in object 'dbo.sometable'. The duplicate key value is (8, 8).

Как вы видите, сообщение об ошибке отформатировано немного не так, как это делает error_handler_sp, но основная идея такая же. Вот образец того, что было записано в таблицу slog.sqleventlog:

logid	logdate	errno	severity	logproc	linenum	msgtext
1	2015-01-25 22:40:24.393	515	16	insert_data	5	Cannot insert …
2	2015-01-25 22:40:24.395	2627	14	insert_data	6	Violation of …

Если вы хотите попробовать SqlEventLog, вы можете загрузить файл sqleventlog.zip. Инструкция по установке находится в третьей части, раздел Установка SqlEventLog.

5. Финальные замечания

Вы изучили основной образец для обработки ошибок и транзакций в хранимых процедурах. Он не идеален, но он должен работать в 90-95% вашего кода. Есть несколько ограничений, на которые стоит обратить внимание:

1. Как мы видели, ошибки компиляции не могут быть перехвачены в той же процедуре, в которой они возникли, а только во внешней процедуре.
2. Пример не работает с пользовательскими функциями, так как ни TRY-CATCH, ни RAISERROR нельзя в них использовать.
3. Когда хранимая процедура на Linked Server вызывает ошибку, эта ошибка может миновать обработчик в хранимой процедуре на локальном сервере и отправиться напрямую клиенту.
4. Когда процедура вызвана как INSERT-EXEC, вы получите неприятную ошибку, потому что ROLLBACK TRANSACTION не допускается в данном случае.
5. Как упомянуто выше, если вы используете error_handler_sp или SqlEventLog, мы потеряете одно сообщение, когда SQL Server выдаст два сообщения для одной ошибки. При использовании ;THROW такой проблемы нет.

Я рассказываю об этих ситуациях более подробно в других статьях этой серии.

Перед тем как закончить, я хочу кратко коснуться триггеров и клиентского кода.

Триггеры

Пример для обработки ошибок в триггерах не сильно отличается от того, что используется в хранимых процедурах, за исключением одной маленькой детали: вы не должны использовать выражение RETURN (потому что RETURN не допускается использовать в триггерах).

С триггерами важно понимать, что они являются частью команды, которая запустила триггер, и в триггере вы находитесь внутри транзакции, даже если не используете BEGIN TRANSACTION.
Иногда я вижу на форумах людей, которые спрашивают, могут ли они написать триггер, который не откатывает в случае падения запустившую его команду. Ответ таков: нет способа сделать это надежно, поэтому не стоит даже пытаться. Если в этом есть необходимость, по возможности не следует использовать триггер вообще, а найти другое решение. Во второй и третьей частях я рассматриваю обработку ошибок в триггерах более подробно.

Клиентский код

У вас должна быть обработка ошибок в коде клиента, если он имеет доступ к базе. То есть вы должны всегда предполагать, что при любом вызове что-то может пойти не так. Как именно внедрить обработку ошибок, зависит от конкретной среды.

Здесь я только обращу внимание на важную вещь: реакцией на ошибку, возвращенную SQL Server, должно быть завершение запроса во избежание открытых бесхозных транзакций:

IF @@trancount > 0 ROLLBACK TRANSACTION

Это также применимо к знаменитому сообщению Timeout expired (которое является не сообщением от SQL Server, а от API).

6. Конец первой части

Это конец первой из трех частей серии. Если вы хотели изучить вопрос обработки ошибок быстро, вы можете закончить чтение здесь. Если вы настроены идти дальше, вам следует прочитать вторую часть, где наше путешествие по запутанным джунглям обработки ошибок и транзакций в SQL Server начинается по-настоящему.

… и не забывайте добавлять эту строку в начало ваших хранимых процедур:

SET XACT_ABORT, NOCOUNT ON

Ошибки SQL являются неотъемлемой частью работы с базами данных. Важно понимать их причины и способы решения, чтобы успешно разрабатывать и поддерживать приложения. В этой статье мы рассмотрим различные виды ошибок SQL, их возможные причины, а также методы решения таких проблем. Мы также предоставим ответы на часто задаваемые вопросы ошибках SQL и предложим полезные советы для профессионалов в области баз данных.

Основные виды ошибок SQL:

1. Синтаксические ошибки
2. Ошибки времени выполнения
3. Логические ошибки

Синтаксические ошибки

a. Неправильное использование ключевых слов (H4 Heading) b. Ошибки в структуре запроса (H4 Heading) c. Проблемы с кавычками и скобками (H4 Heading)

Ошибки времени выполнения

a. Ошибки доступа к данным (H4 Heading) b. Ошибки ограничений целостности (H4 Heading) c. Проблемы с производительностью (H4 Heading)

Логические ошибки

a. Неправильный выбор операторов (H4 Heading) b. Ошибки в вычислениях (H4 Heading) c. Проблемы с агрегацией данных (H4 Heading)

Чтобы разобраться подробнее – приходите на бесплатный курс

1. Определение типа ошибки (H3 Heading)
2. Анализ причин ошибки (H3 Heading)
3. Применение соответствующего решения (H3 Heading)

Определение типа ошибки

• Используйте сообщения об ошибках
• Отслеживайте контекст запроса

Анализ причин ошибки

• Проверьте синтаксис
• Проверьте права доступа
• Убедитесь, что данные корректны

Применение соответствующего решения

• Исправьте синтаксические ошибки
• Решите проблемы с данными
• Оптимизируйте запросы

Часто задаваемые вопросы

1. Как наиболее эффективно найти и исправить ошибки SQL?
2. Какие инструменты могут помочь в диагностике и исправлении ошибок SQL?
3. Влияет ли версия базы данных на возникновение ошибок SQL?
4. Как предотвратить ошибки SQL при разработке приложений?

Чтобы разобраться подробнее – приходите на бесплатный курс

Как наиболее эффективно найти и исправить ошибки SQL?

• Используйте подходящие инструменты и ресурсы для отладки
• Разбивайте сложные запросы на простые
• Протестируйте запросы с разными наборами данных

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

• Редакторы кода с поддержкой SQL, такие как Visual Studio Code или Sublime Text
• Среды разработки баз данных, такие как SQL Server Management Studio или MySQL Workbench
• Специализированные инструменты для анализа и оптимизации запросов, такие как SQL Sentry Plan Explorer или EverSQL

Влияет ли версия базы данных на возникновение ошибок SQL?

Да, версия базы данных может влиять на возникновение ошибок SQL из-за различий в поддерживаемых функциях, синтаксисе и стандартах. Важно использовать актуальную версию базы данных и знать о возможных ограничениях или различиях между версиями.

Как предотвратить ошибки SQL при разработке приложений?

• Используйте хорошие практики проектирования баз данных и написания запросов
• Тестируйте ваш код на разных этапах разработки
• Внедряйте контроль версий и процессы код-ревью для обеспечения качества кода
• Обучайте разработчиков основам SQL и принципам работы с базами данных

Заключение:

Ошибки SQL являются неизбежным аспектом работы с базами данных, но с правильными знаниями и инструментами их можно успешно диагностировать и исправлять. Надеемся, что эта статья помогла вам лучше понять различные типы ошибок SQL, их причины и способы решения. Следуйте нашим рекомендациям и советам, чтобы свести к минимуму вероятность возникновения ошибок SQL и обеспечить надежную и эффективную работу ваших приложений с базами данных.

Приглашаем разобраться в важных темах – устранения ошибок MS SQL и восстановления базы данных. Получите представление о разных способах устранения проблемы, которые следует использовать при различных обстоятельствах.

Восстановление баз данных

Специалисты пользуются несколькими способами восстановления баз данных (БД). Наиболее простой и удобный ­– воспользоваться программой (SSMS) SQL Server Management Studio.

Как восстановить

Узнать, где находится SQL Server Management Studio, довольно легко. Microsoft Windows Server 2012 R2 располагается в стандартном перечне программных продуктов. В Microsoft Windows Server 2008 R2 следует зайти в меню Пуск и отыскать Microsoft Windows Server 2012. Там смотреть Microsoft SQL Server Management Studio.

Далее следует ввести тип сервера с именем, а чтобы подтвердить подлинность – информацию, требуемую для прохождения авторизации. Нажать Соединить (Connect).

В левом углу из обозревателя (Object Explorer) раскрыть Базы данных (Server Objects). Из представленного перечня отобрать базу, подлежащую восстановлению либо ту, данные которой будут восстанавливаться. На выбранном файле кликнуть мышкой и в выпавшем перечне выбрать Задачи (Tasks), затем Восстановить (Restore), потом База данных… (Databases …).

Проделанные шаги дадут старт процессу Restore Database, а значит требуемая база данных начнет восстанавливаться. Следует сделать выбор источника для Restore Database.

Чтобы возобновить базу данных, при запуске мастер восстановления сделает попытку автоматом определить очередность файлов резервных копий. В том случае, когда базу данных нужно загружать из определенной папки либо устройства, понадобится:

1. Переключить соответствующую кнопку на Устройство (From device).
2. Прописать, откуда восстановится БД.
3. Выбрать инфобазу, в которую произведется загрузка данных (Destination for restore). Ею может выступать любая БД, которая регистрировалась на SQL Server (в том числе и база, с которой создавалась резервная копия).

В программе реализована возможность указания времени, необходимого для восстановления БД. Для этого необходимо просто кликнуть по кнопке Временная шкала… (Timeline). Если существует скопированный журнал транзакций или checkpoint в нем, то требуемый промежуток времени может быть указан с высокой точностью (вплоть до секунды).

Если требуется провести копирование БД, то во вкладке Файлы (Files) нужно будет прописать путь к файлам выбранной инфобазы.

Настройка дополнительных параметров

Также в программе реализована возможность настройки дополнительных параметров. Выставляя тот или иной флажок, можно регулировать необходимые действия. Следует выбрать соответствующий флажок, чтобы в процессе восстановления БД:

• Которая опубликована не на сервере, где она создавалась, сохранились настройки репликации, поможет отметка «Сохранить параметры репликации). Он важен, если при резервном копировании была реплицирована БД;
• была проведена перезапись файлов БД с именем, которое указывалось в качестве базы назначения – нужно поставить отметку «Перезаписать существующую базу данных»;
• сузить доступность к базе всем, кто не sysadmin, db_owner, dbcreator – нужно поставить флажок «Ограничение доступа к восстановленной базе данных»;
• старту должен предшествовать перевод БД в режим одного пользователя, а по его завершению вернуть в пользование для множества пользователей – поставить отметку «Закрыть существующие соединения»;
• чтобы провести требуемое резервное копирование завершающего фрагмента журнала транзакций, следует поставить отметку «Создание резервной копии заключительного фрагмента журнала перед восстановлением». Если в окошке Временная шкала резервного копирования (Backup Timeline) для временной точки требуется эта резервная копия, то отметка будет поставлена системой, без возможности снятия;
• чтобы после завершения восстановления каждой резервной копии уточнялась необходимость продолжения процесса – следует поставить отметку «Выдавать приглашение перед восстановлением каждой резервной копии» (Prompt before restoring each backup). Достаточно полезен, т.к. после того, как восстановлено определенное количество резервных копий можно остановить дальнейшую цепочку восстановительных процессов.

Настроив все важные параметры следует нажать ОК. Тем самым запустится процесс. Соответствующее уведомление сообщит об его окончании.

Восстановление базы в новое место

Чтобы перенести базу данных MSSQL Server по другому пути каталога либо сделать ее копию, следует знать, как восстановить БД в новую папку. Полезно знать как ее переименовывать. Для этого можно воспользоваться вышеупомянутой программой SSMS и T-SQL.

Подготовка к восстановлению базы данных

Перед стартом процесса восстановления нужно соблюдать ряд требований:

1. Когда осуществляется процесс восстановления базы, доступ к ней может быть только у системного администратора. Для остальных пользователей доступ должен быть ограничен.
2. Перед восстановлением нужно сделать резервную копию активного журнала транзакций.
3. Чтобы восстановить зашифрованную базу необходим доступ к сертификату либо ассиметричному ключу, который применялся в качестве ее шифратора. Не имея доступа к ним, восстановление зашифрованной БД становится невозможным. Потому, такой сертификат следует хранить, пока может понадобиться резервное копирование.

После того, как база данных версии SQL Server 2005 (9.x) либо более поздней, восстановится, произойдет автоматическое обновление, и она станет доступной.

Если присутствуют полнотекстовые индексы

В том случае, когда в БД SQL Server 2005 (9.x) присутствуют полнотекстовые индексы, в момент ее обновления произойдет импорт, сброс либо перестроение. Результат зависит от того, какое значение проставлено в свойствах сервера upgrade_option.

При обновлении такие индексы станут недоступны, если upgrade_option имеет значения:

• 2 в режиме импорта;
• 0 в режиме перестроения.

Продолжительность поцессов импорта и перестроения зависит от того, какой объем занимают данные. Импорт может длиться пару часов, а процесс перестроения – гораздо дольше (может продолжаться в 10 раз дольше).

В том случае, когда выбран процесс Импорт, а доступ к полнотекстовому каталогу отсутствует, то произойдет перестроение одноименных индексов, которые связаны с ним. Для изменения свойств upgrade_option необходимо воспользоваться процедурой sp_fulltext_service.

Соблюдение правил безопасности

Чтобы обезопасить себя, крайне не рекомендуется проводить присоединение либо восстановление БД, которые были получены из ненадежных или вовсе неизвестных источников. Они могут содержать вредоносные коды, способные:

• запускать выполнение инструкций T-SQL, не предусмотренных системой;
• вызывать ошибки в результате изменения схемы либо самой структуры БД

Если БД получена из источников, не внушающих доверия, то перед началом ее использования необходимо:

• протестировать по инструкции DBCC CHECKDB;
• исследовать исходный и иные коды БД, изучить процедуры.

Инструкции RESTORE

На ход реализации этих инструкций влияет факт существования восстанавливаемой базы. Если база:

• присутствует, то разрешения получают пользователи sysadmin, dbcreator, dbo (владелец БД) по умолчанию;
• отсутствует, то пользователям потребуются разрешения CREATE DATABASE.

Разрешения на реализацию таких инструкций выдаются в соответствии с ролями. В соответствии с ними сервер всегда имеет доступ к данным о членстве. Разрешение RESTORE отсутствует у пользователей с ролями db_owner. Причина в том, что членство может быть проверено лишь в тех случаях, когда к базе данных всегда есть доступ и она не повреждена. А это иногда не соблюдается в процессе выполнения инструкций RESTORES.

Пошаговая инструкция восстановления БД в новую папку в SSMS

1. Открыть SSMS и произвести подключение к SQL Server Database Engine.
2. Щелкнуть мышкой по имени сервера, чтобы развернулось его дерево.
3. Кликнуть мышкой на Базы данных, потом – по Восстановить базу данных.
4. В разделе Источник выбрать Общие, чтобы определить соответственное расположение и источник копий, подлежащих восстановлению. Пользователю предлагается выбрать нужный вариант (Базы данных либо Устройства). Особенности:
5. При выборе Базы данных открывается перечень БД, где можно выбрать нужную. В нем представлены лишь те базы, у которых резервные копии создавались по журналу msdb. Стоит отметить, что для БД на целевом сервере, резервные копии которых поступили с иных серверов, подобный журнал будет отсутствовать. В таких ситуациях следует выбирать вариант Устройство. Это позволит руками прописать файл, а в случае необходимости – обозначить устройство для выполнения восстановления.
6. Устройство можно выбрать, воспользовавшись кнопкой обзора (…). В результате появится окошко Выбор устройств резервного копирования. Перейти в окошко Тип носителя резервной копии, в котором из списка выбрать необходимый тип устройства. Если требуется добавить ряд устройств, это можно сделать с помощью кнопки Добавить в окошке Носитель резервной копии. Когда все необходимые устройства добавлены, необходимо вновь перейти на страницу Общие. Для этого следует нажать ОК в списке Носитель резервной копии. Обратившись к списку Источник: Устройство: База данных обозначить название БД, куда будет производиться восстановление. Пользователь может воспользоваться данным списком только при выборе Устройства. Можно выбирать лишь те БД, у которых на отобранном устройстве имеются резервные копии.
7. Название новой базы для проведения восстановления автоматом сформируется в поле База данных в разделе Назначение. При желании оно может быть изменено. Для этого желаемое название вводится в окошке База данных.
8. Далее перейти к Восстановить до. Пользователь может оставить значение До последней выбранной резервной копии (по умолчанию) либо кликнуть по Временной шкале. При выбре второго варианта всплывет соответствующее окошко Временная шкала …. В нем нужно указывать точное время.
9. Необходимые резервные копии для восстановления можно выбрать в соответствующей сетке. В ней отражены все наборы, доступные в выбранном месте. Система сама предложит план восстановления отобранных копий, который будет использован по умолчанию. Он может быть переопределен, если в сетке изменить отобранные элементы.
10. Для указания другого места расположения файлов базы, необходимо выбрать страницу Файлы после чего нажать на Переместить все файлы в папку. Следует указать вновь выбранное место расположения папок файлов данных и журнала.
11. Если возникла необходимость – провести настройку параметров, как было рассказано выше.

Чтобы начать процесс, в котором будет восстанавливаться БД в новую папку с возможностью переименовывать ее, можно воспользоваться инструкциями Transact-SQL.

Как просмотреть отчет

Стандартный отчет «События резервного копирования и восстановления» позволяет получить сведения о том, когда проводилось:

• Резервное копирование определенной БД;
• операции восстановления базы MS SQL из них.

Данный отчет включает данные, касающиеся создания резервных копий:

• время, затраченное на это в среднем (Average Time Taken For Backup Operations);
• операции, которые прошли успешно (Successful Backup Operations);
• ошибки, которые были допущены (Backup Operation Errors);
• удачно прошедших восстановлений баз (Successful Restore Operations).

Чтобы он начал формироваться, следует в Обозревателе объектов выбрать нужную БД и щелкнуть по ней мышкой. Выбрать в меню Отчеты, а затем – Стандартный отчет. После этого кликнуть на События резервного копирования и восстановления.

Чтобы просмотреть информацию из сформированного отчета следует выбрать нужную группировку и раскрыть данные по ней.

Для восстановления поврежденной БД можно воспользоваться еще одним инструментом.

Как исправить ошибки в MS SQL с помощью Recovery Toolbox for SQL Server

Для восстановления поврежденной базы данных можно обратиться к помощи Recovery Toolbox for SQL Server. Для исправления ошибки (Error), следует воспользоваться пошаговой инструкцией восстановления данных из файла *.mdf, который был поврежден. Для этого необходимо:

1. Скачать Recovery Toolbox for SQL Server.
2. Установить программу следуя инструкциям и запустить ее.
3. Из списка файлов выбрать файл *.mdf, который был поврежден.
4. Осуществить предварительный просмотр тех данных, которые в процессе выполнения программы могут быть подвергнуты извлечению из базы MS SQL сервер, которая подверглась повреждению.
5. Выбрать наиболее приемлемый способ, которым будут экспортироваться данные:
6. сохранением на диск в качестве SQL-скрипта;
7. выполнением SQL-скрипта в самой БД.
8. Произвести выборку информации, требующей восстановления и сохранения.
9. Начать восстановление нажатием Start recovery.

Данная программа создавалась, чтобы облегчить процесс восстановления поврежденных БД. Специально разработанная, оптимизированная для восстановления SQL Server, утилита поможет устранить ошибки и внести правки в разные типы повреждений *.mdf файлов и базы данных MS SQL Server.

Как становится понятно, для исправления ошибок и восстановления БД необходимо уметь пользоваться различными инструментами. Читайте, изучайте материалы по данной теме. Если возникнут вопросы – обязательно задавайте.

Также приглашаем на специальный курс по MS SQL в Otus.

Одной из ярких причин возникновения ошибок в базе данных может послужить неправильная остановка сервера MySQL. Как это обычно бывает, сбой в электропитании сервера, либо иные причины, повлекшие за собой банальное отключение машины, либо перезагрузку. Иногда, и нередко подобного рода сбои могут повлечь за собой проблемы, которые решаются лишь путем восстановления данных из бэкапа, и это к вопросу для чего нужно делать бэкапы. Наличие ошибок в базе данных может проявиться не сразу, однако если они есть, то вы их рано или поздно обязательно заметите. Проблемы, как правило, проявляются в виде ошибок после запросов к базе, либо база начинает уходить в раздумье на не свойственное для этого время.
Давайте посмотрим, что можно предпринять первым делом, чтобы попытаться исправить ситуацию. Утилита mysqlcheck как правило устанавливается по умолчанию с сервером MySQL может быть использована для проверки баз данных на ошибки. Рассмотрим пример ее использования.

Использование утилиты mysqlcheck

# mysqlcheck -u root -p --optimize --auto-repair --all-databases

Назначение параметров:

--optimize – Оптимизируем все базы.
--auto-repair – Ремонтируем все базы.
--all-databases – Проверяем ошибки у всех баз.

# mysqlcheck -r имя_базы имя_таблицы_в_базе -u root -p

# service mysqld stop

Анализируем базу данных на наличие ошибок

# myisamchk -s /var/db/mysql/наша_база/*.MYI

Обратите внимание, что путь к базам данных у вас может отличаться, этот момент вам следует так же уточнить. Если есть ошибки, то появиться сообщение подобного вида:

myisamchk: warning: 2 client is using or hasn't closed the table properly
MyISAM-table '/var/db/mysql/наша_база/ary_mem.MYI' is usable but should be fixed

Исправляем ошибки с помощью myisamchk

# myisamchk -r /var/db/mysql/наша_база/ary_mem.MYI

Исправляем ошибки по всем таблицам в базе (рекурсивно)

# myisamchk -r /var/db/mysql/наша_база/*.MYI

Процесс исправления ошибок будет сопровождаться подобным выводом сообщений в окне консоли:

- Fixing index 1
- Fixing index 2
- Fixing index 3

После того как ошибки исправлены, можно запустить сервер MySQL, чтобы убедиться что всё в порядке.
Запуск сервера MySQL

# service mysqld start

Хотелось бы отметить один момент, что команды остановки и запуска сервера MySQL, а так же пути расположения баз данных на вашем сервере могут отличаться, поэтому вам так же следует эти моменты учесть.