Как стать автором
Обновить
1612
OTUS
Цифровые навыки от ведущих экспертов

А какая разница какой Collation выбрать?

Время на прочтение4 мин
Количество просмотров34K

Статья подготовлена для студентов курса «MS SQL Server разработчик»
Хочу поделиться историей из одного из предыдущих проектов, которая иллюстрирует, что Collation нужно выбирать очень вдумчиво. И о том, что бывает, если этот параметр все-таки выбрали неверно, и какие варианты решения проблемы бывают.


Сначала небольшое введение о том, что же такое Collation. В SQL Server параметр Collation указывает серверу, как нужно сортировать и сравнивать строки. Вот, например, строки “Apple” и “apple”. Они разные или нет? Это зависит от указанного Collation. Если с регистром все более менее понятно, то что делать с примером “елка” и “ёлка”? Считать их как одинаковые или как разные? Это все тоже в Collation.


История случилась в проекте, функционал которого очень похож на DropBox или Google Диск. Он предоставляет возможность управлять своими синхронизированными папками и файлами на разных машинах, а также возможность другим пользователям иметь доступ к данной синхронизированной папке.


image


Итак, история началась с того, что на Prod серверах было 75-90% ошибок в логах (см скриншот ниже), и непонятно откуда они возникали, и в чем была их причина. Ошибка звучала так: “ReadWrtLst is not complete”. Далее шли детали пользователя и его папки.


image


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


Мы собрали информацию по пользователям, по которым выдается эта ошибка. И тут нас ждал первый сюрприз: из миллионов пользователей системы эта ошибка случалась всего у 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 выбран не очень правильно?


  1. Изменить Collation на уровне БД;
  2. Изменить Collation на уровне поля (в нашем случае не было смысла менять для всей таблицы);
  3. Добавить поле Varbinary, в которое записывать дубликат из поля с названием папки, и использовать именно его для сравнения;
  4. Сказать пользователям, что есть ограничения на поддержку символов в названиях директорий.

Первая опция — изменение 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/

Теги:
Хабы:
Всего голосов 21: ↑16 и ↓5+11
Комментарии5

Публикации

Информация

Сайт
otus.ru
Дата регистрации
Дата основания
Численность
101–200 человек
Местоположение
Россия
Представитель
OTUS