Статья подготовлена для студентов курса «MS SQL Server разработчик»
Хочу поделиться историей из одного из предыдущих проектов, которая иллюстрирует, что Collation нужно выбирать очень вдумчиво. И о том, что бывает, если этот параметр все-таки выбрали неверно, и какие варианты решения проблемы бывают.
Сначала небольшое введение о том, что же такое Collation. В SQL Server параметр Collation указывает серверу, как нужно сортировать и сравнивать строки. Вот, например, строки “Apple” и “apple”. Они разные или нет? Это зависит от указанного Collation. Если с регистром все более менее понятно, то что делать с примером “елка” и “ёлка”? Считать их как одинаковые или как разные? Это все тоже в Collation.
История случилась в проекте, функционал которого очень похож на DropBox или Google Диск. Он предоставляет возможность управлять своими синхронизированными папками и файлами на разных машинах, а также возможность другим пользователям иметь доступ к данной синхронизированной папке.
Итак, история началась с того, что на Prod серверах было 75-90% ошибок в логах (см скриншот ниже), и непонятно откуда они возникали, и в чем была их причина. Ошибка звучала так: “ReadWrtLst is not complete”. Далее шли детали пользователя и его папки.
По коду довольно быстро было найдено место, которое генерирует ошибку, но понять, почему она возникала, и как ее воспроизвести, у нас не получалось.Понятно было только то, что ошибка каким-то образом связана с тем, что пользователь как-то умудрился сделать еще одну папку с таким же именем в своей ОС.
Мы собрали информацию по пользователям, по которым выдается эта ошибка. И тут нас ждал первый сюрприз: из миллионов пользователей системы эта ошибка случалась всего у 50. И эти 50 пользователей генерируют 90% логов об ошибках. Так как ситуацию не получалось воспроизвести, то мы решили связаться с одним из пользователей и выяснить, по какой причине у него не синхронизируется одна из папок. Папка выглядела для нас так же, как и другие, единственное отличие было в том, что называлась она на языке пользователя с использованием иероглифов. А пользователь был японцем. К слову сказать, среди этих 50 пользователей, японцев было большинство.
Благодаря одному из разработчиков команды, нам удалось воспроизвести ошибку. Ошибка заключалась в том, что операционная система считала названия папок разными, а SQL Server считал их одинаковыми из-за выбранного Collation.
Collation, который использовался в проекте:
SQL_Latin1_General_CP1_CI_AS
Небольшое отступление о том, как прочесть Collation. (Если вы знакомы с ним, смело пропускайте.)
Итак, в Collation есть несколько частей:
- SQL — параметры сортировки по SQL Server (SQL в начале Collation) или Windows (тогда было бы просто Latin1_ …);
- Latin1_General — локаль или используемый язык;
- CP1 — code page — кодовая страница;
- CI — Case Insensitive — без учета регистра;
- AS — Accent Sensitive — с учетом аксонов или диакритических знаков, проще говоря 'a' не считается равным 'ấ'.
Этот Collation был когда-то Collation по умолчанию, когда устанавливали SQL Server.
Какие опции есть еще?
- _KS — с учетом японских иероглифов хирагана и катакана, если параметр не выбран, то SQL Server будет интерпретировать иероглифы хирагана и катакана как одинаковые.
- _WS — с учетом ширины символов, если параметр не выбран, то “Text” и “T e x t” считаются одинаковыми строками.
- _VSS — с учетом знаков выбора варианта написания в японском языке, появился с версии 2017.
- _UTF8 — позволяет хранить данные в UTF8.
Все текстовые поля в БД использовали тип NVARCHAR.
Получается, что, так как текущий Collation игнорировал разницу написания японских иероглифов и разницу в ширине символов, то SQL Server сравнивал строки не так, как это делала операционная система, что и вызывало проблему, т.е. пользователь мог создать папки, не мог их добавить в систему для синхронизации. Тоже самое возникло бы в дальнейшем при сравнении имен файлов.
Мы стали думать о том, как можно решить данную проблему и изменить Collation.
Collation можно выставлять на нескольких уровнях:
- SQL Server инстанс
- База данных
- Таблица
- Поле
При этом, не рекомендуется иметь внутри БД разные Collation, потому что каждый раз при сравнении строк с разным Collation нужно будет делать преобразование с помощью COLLATE, указывая серверу, какой порядок сравнения ему нужно использовать.
Какие опции есть в ситуации, когда понятно, что Collation выбран не очень правильно?
- Изменить Collation на уровне БД;
- Изменить Collation на уровне поля (в нашем случае не было смысла менять для всей таблицы);
- Добавить поле Varbinary, в которое записывать дубликат из поля с названием папки, и использовать именно его для сравнения;
- Сказать пользователям, что есть ограничения на поддержку символов в названиях директорий.
Первая опция — изменение Collation на уровне БД — наиболее сложна. В случае с БД потребовалось бы пересоздать базу данных и перезалить туда данные. Так как система работала 24/7, эта опция была отвергнута сразу.
Вторая опция про изменение поля: самый простой вариант ее реализовать — это добавить поле с нужным Collation и туда перенести данные. Но тогда нужно будет изменять код в БД, который работает с этим полем, а кода в БД было очень много.
Третья опция понравилась нам больше всего, так как в теории это вносило меньше всего изменений, так как основное поле продолжало бы существовать с текущим Collation, и мы бы не имели проблем с его преобразованием, при этом весь нужный функционал в виде учета японской азбуки или широких символов бы работал. Минус в том, что нужно было вносить изменения в часть ПО, но так как эта серверная часть, это можно было сделать.
Четвертая опция была наиболее простой в данном случае, потому что общее число пользователей было несколько миллионов, а проблема возникала только у 50. Однако, если бы приложение активно использовалось в Японии, данное решение было бы мало применимо.
После представления данных руководству, было решено известить пользователей о том, что ПО не поддерживает ряд символов, и при их использовании в названии синхронизируемых файлов и папок ПО может работать некорректно. Это временное решение, потому что при дальнейшем распространении, количество пользователей, сталкивающихся с подобной проблемой, будет нарастать, и нужно будет что-то менять, используя первые три опции.
Лучший вариант выбора Collation — исходить из требований вашего приложения. Если вам нужно, чтобы SQL Server сравнивал строки так же как ОС, то Collation по умолчанию точно неверен. К сожалению, такие нюансы редко видны на старте проекта при проектировании системы, но, надеюсь, прочитав статью, Вы вспомните об описанной ситуации и не наступите на подобные грабли сами.
Полезные материалы по Collation:
https://docs.microsoft.com/en-us/sql/relational-databases/collations/collation-and-unicode-support?view=sql-server-2017
https://www.red-gate.com/simple-talk/sql/sql-development/questions-sql-server-collations-shy-ask/
https://www.virtual-dba.com/sql-server-collation/
