Строгий режим MySQL и почему он должен быть включен

    В MySQL есть такой специальный режим, предназначенный для введения в базу неправильных данных. Например, чтобы вместо 20000000000 вставлять в INT-поле 2147483647. Или наполнять базу несуществующими датами. Или обрезанными строками. Ну или мало ли для чего этот режим может тебе пригодится.

    Режим этот называется «обычный режим».



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

    Движок MyISAM, бывший стандартным движком MySQL до совсем недавних пор, не поддерживает транзакции. Если отваливался один из «как бы атомарного» набора запросов, база (в общем случае) теряла целостность. По сравнению с такой опасностью запись в базу неверных значений оказалась наименьшим злом.

    Но ведь все сколько-нибудь компетентные вебдевелоперы знают, что надо использовать движок InnoDB, который — в контексте хранилища для сайта — со всех сторон лучше MyISAMа. В частности, InnoDB поддерживает транзакции, и все сколько-нибудь адекватные ORM (или их живые заместители, кодирующие запросы к базе) используют эти транзакции для обеспечения атомарности изменений.

    Вот и получается, если ты используешь InnoDB и транзакции, щадящее отношение MySQL тебе только вредит. Каждая пропущенная проверка грозит засорением базы данных. Вот наглядный пример — каким полем ты хранишь URL? VARCHAR(255)? А адрес может быть и в 2 килобайта длиной. В «обычном режиме» при переполнении строки MySQL всего лишь запишет предупреждение в лог, который никто не читает.

    Строгий режим



    Из обширного списка режимов MySQL нас интересует режим STRICT_TRANS_TABLES. Он запрещает то, о чем я рассказывал выше, и бросает ошибки на любые неправильные данные.

    Говоря языком запросов,

    SET sql_mode='STRICT_TRANS_TABLES,NO_ZERO_DATE,NO_ZERO_IN_DATE';
    


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

    Выводы, касающиеся STRICT_TRANS_TABLES применительно к Ruby on Rails, можно почитать у меня в блоге.
    Share post
    AdBlock has stolen the banner, but banners are not teeth — they will be back

    More
    Ads

    Comments 42

      +6
      Да, с int полем было не приятное событие :) Поменяли архитектуру сервиса, начали использовать шардинг и глобальные id видео стали в 100 раз больше (первые 2 разряда стали обозначать номер шарда). Через какое-то время заметили, что часть функционала сервиса не верно работает, оказалось в таблицах хранящих глобальное значение id, значения выходящие за пределы int начали заменяться на 2147483647
        +3
        +2147483647 — самый популярный телефонный номер
        +2
        Да, никогда еще строгость правил и режима дебагга не делала плохого. Чем строже — тем меньше у программиста права на ошибку. У нас была проблема с идентификатором сессии, способ кодирования сменили, а поля в базе забыли. Там уж не некоторый, там уж весь функционал перестал работать.
          +7
          Но ведь все сколько-нибудь компетентные вебдевелоперы знают, что надо использовать движок InnoDB, который — в контексте хранилища для сайта — со всех сторон лучше MyISAMа.
          Скорость?
            0
            В специфических случаях. И их лучше изучать уже после того, как привыкнешь к использованию InnoDB по умолчанию.
              +6
              Какие еще специфические случаи? Какое нафиг привыкание к InnoDB? О чем вы, это же бред.

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

              А если в вашем супер пуер проекте и записывать надо данные много и читать быстро, то строите две таблицы одну InnoDB для записи, другую MyISAM для чтения и синхронизируете из InnoDB в MyISAM.
              +1
              В 5.5 Oracle, по их словам, сильно повысили производительность InnoDB.
                –4
                Оракл, конечно, хорошо, но все-же он платный, ЕМНИП.
                  +1
                  Oracle как разработчик MySQL имелось в виду.
                    +1
                    Внимательней надо быть:
                    В mysql 5.5 бесплатном сервере бд, фирма оракл, которая сейчас занимается разработкой этой бд увеличили, по их словам производительность innoDB
                      0
                      Что интересно, «Иннобейз» была куплена «Ораклом» за несколько лет до «Май-эк-кью-эля».
                        0
                        Медленно, но уверенно, они продвигаются к своей цели =)
                        Либо вскоре не будет оракла урезанного, либо мускула :)
                        Хотя я бы не проч поиметь в мускуле обьектный язык свой как в оракле
                  –3
                  xtradb наше всё.
                  +12
                  MyISAM таки очень хорош для справочников и редкоменяющихся, но часточитаемых данных.
                    –2
                    а)репост (читайте правило 2), да ещё и неполный
                    б) весь смысл поста можно было бы записать в одном абзаце.
                    в) strict-режим — скорее костыль, спасающий от кривых рук. вы в статье упоминаете RoR, так я вам напомню, что DB-specific настройки считаются вообще не-Rails way. правильно настроенные фильтры и валидации должны огораживать Rails-программистов от всего DB-specific. не говоря уже о том, что попробуйте писать под MongoDB. начнёте проектировать запросы так, чтобы база данных оставалась целостной даже без всяких транзакций (см избыточность и денормализация).
                    и на последок отмечу: не думаю, что получать ошибку от БД (а в большинстве случаев это значит отдавать клиенту 500) является лучшим решением, чем сохранять неверные данные.
                      +4
                      Бывает, ошибки обрабатывают перед тем (или даже вместо того, чтоб), как отдать клиенту 500.
                        –2
                        говоря опять же о Rails, для экземпляров классов ActiveRecord есть два метода: save и save!.. Первый в случае неудачи сохранения (не прошли валидации к примеру) возвращает false, второй поднимает exception. По понятным причинам везде рекомендуется использовать первый. а если мы включим strict-режим, то первый метод будет тоже периодически поднимать exception. или вы предлагаете все запросы в коде оборачивать в rescue?
                          +7
                          Без обид, но Вы находитесь в блоге Mysql, и здесь практически никого не волнует, какими проблемами грозит mysql-strict-режим пользователям одного из множества фреймворков. В конце концов, никто не заставляет его включать, если Вы считаете кривые данные в базе меньшим злом, чем взрыв из-за кривых лапок разработчика, который поверил в то, что правильно составленный запрос не может взорваться по естественным причинам (и кстати не только при стрикт-режиме).

                          В остальном — насчет валидации Вы совершенно правы, по большому счету программер действительно должен заботиться о том, чтобы неверные данные в базу не приходили в принципе. И когда Вы один человек в команде, можно позволить себе роскошь быть уверенным в том, что они туда никогда не придут, хотя и это неправда. Проблема в том, что скорее всего Вы узнаете о пропущенной валидации уже постфактум, когда не сможете адекватно обработать заведомо кривые данные. Стрикт-режим же сыграет роль дополнительного теста, и Вы сможете узнать о проблеме в коде при первом ее появлении.
                            +1
                            о rails я упомянул только потому, что автор написал статью в его контексте (перейдите по ссылке в конце поста чтобы убедиться). и я не говорю, чем грозит режим strict конкретно для рельсов. я лишь считаю, что допущение ошибок БД по валидным данным — это очень яркий антипаттерн.
                              +2
                              Какие же они валидные, если они не соответствуют типам в БД? Тут либо БД кривая, либо данные не укладываются в те допущения, исходя из которых разрабатывалась структура базы. Разруливать ситуацию в любом случае придется программеру, но в вашем случае ему вероятно еще и придется думать, что делать с неверными данными в хранилище.
                                0
                                если вы укладываете (даже по случайности) данные в несоответствующие типы БД, то вы либо недоделали валидации и обработку этих данных, либо надо подумать над выбором другого инструмента (другой БД возможно), всё-таки БД выбираются для хранения данных, а не данные отбираются под любимую БД.
                                  +2
                                  Я об этом говорил тремя комментами выше. Если укладывать даже по недосмотру данные в несоответствующие типы БД (ну для конкретики — засуну 300.25 в tinyint) — человек со стриктом это сможет заметить при первой вставке таких данных и поправит либо код, из которого эта цифра приехала, либо валидатор, либо тип в базе. Человек без стрикта поймет свою проблему только тогда, когда к нему придут с жалобой на конкретную кривую запись, потом он посчитает COUNT(*) WHERE myField=255 и пойдет за водкой.
                                    –1
                                    сделайте тест о том, что если вы кладёте в поле 300.25, то вы должны получить из него потом ровно 300.25.
                                    я вам не говорю о том, что надо позволять класть любые данные куда угодно. я вам пытаюсь сказать, что опираться в проектировании поведения БД на ошибки, которые выдаёт СУБД — крайне плохо поддерживаемое и не гибкое решение.
                        0
                        MongoDB вообще забавная штука. Вроде бы и не NoSQL и в то же время не заставляет не спать ночи стряпая map/reduce функции на все случае жизни.
                          +2
                          MongoDB не NoSQL?
                            0
                            Опечатался конечно. Сначала написал MongoDB конечно не SQL, потом заменил на NoSQL.
                          0
                          > правильно настроенные фильтры и валидации должны огораживать Rails-программистов от всего DB-specific
                          Это не так. Например, в официальной документации по validates_uniqueness_of предлагается создавать уникальные индексы на уровне БД.

                          RoR возник в результате работы над веб-приложением, и функционал его расчитан на веб-приложения. Этот функционал очень богат и покрывает практически все потребности несложного приложения. Но для более сложных (необязательно масштабных, просто с нетипичными для ror требованиями) уже приходится спускаться на уровень БД.

                          > DB-specific настройки считаются вообще не-Rails way
                          Что не помешало разработчикам RoR включить во фреймворк опцию config.active_record.schema_format.
                            0
                            > Это не так. Например, в официальной документации по validates_uniqueness_of предлагается создавать уникальные индексы на уровне БД.
                            там же не предлагается отказаться от validates_uniqueness_of в пользу индексов базы и все INSERT-запросы оборачивать в rescue и проверять, не нарушили ли мы индекс. там использование индексов на уровне БД рекомендуется для повышения производительности и исключения вероятности параллельно исполняемых запросов. и между прочим, указание индекс-ключей в миграциях БД-независимое, см. метод add_index.

                            > с нетипичными для ror требованиями
                            можно конкретнее требования к веб-приложению, которые явно заставляют спускаться на уровень БД?

                            >Что не помешало разработчикам RoR включить во фреймворк опцию config.active_record.schema_format.
                            в рельсах и вовсе все запросы можно писать на чистом непараметризованном SQL. только я не знаю людей, которые ставят рельсы, чтобы это делать.

                            короче мы снова скатились к спору ORM vs pure SQL.
                              0
                              О том, чтоб " все INSERT-запросы оборачивать в rescue", речи и не идёт. Речь идёт о том, что «правильно настроенные фильтры и валидации должны огораживать Rails-программистов от всего DB-specific». Даже правильно настроенный validates_uniqueness_of в практически стандартной ситуации (у нас же не по одному запросу в секунду приходит, не так ли?) не выполняет своей прямой обязанности — проверять уникальность полей. Да и не может выполнить, веб-приложение на это неспособно. Поэтому одними средствами фреймворка не обойдёшься.

                              > указание индекс-ключей в миграциях БД-независимое, см. метод add_index
                              Само наличие индекса — уже зависимость от БД. Это уже уход от концепции чистого веб-приложения, где можно обойтись только средствами фреймворка.

                              > можно конкретнее требования к веб-приложению, которые явно заставляют спускаться на уровень БД?
                              Указанный выше контроль уникальности, внешние ключи, специфичные типы данных (inet, macaddr, polygon etc). Применение SQL'ных вьюшек или хранимых процедур. Одним словом, ситуация, когда веб-приложение является лишь одной из частей бОльшей системы.

                              > в рельсах и вовсе все запросы можно писать на чистом непараметризованном SQL
                              А это здесь причём? Наличие schema_format говорит о том, что использование специфичных для БД вещей достаточно распространено, чтобы предусмотреть отдельный механизм работы с ними.

                              > короче мы снова скатились к спору ORM vs pure SQL.
                              Хм. Я не предлагал писать на чистом SQL. Вы тоже не предлагали. Кто же тогда с кем спорит?
                                0
                                > Поэтому одними средствами фреймворка не обойдёшься
                                а add_index — это не средство фреймворка? используя его, а не SQL запрос на добавление индекса, я могу не поменяв ни строчки кода перейти с MySQL на Postgres, а с него на Oracle.

                                И вообще, я не понимаю, почему в споре о strict mode вы ухватились за уникальные ключи, которые этого strict-mode никак не касаются?

                                > Само наличие индекса — уже зависимость от БД
                                Само наличие БД — уже зависимость от БД. в концепции фреймворка заложено существование БД и существование у неё индексов. это есть в AR, это есть в DataMapper и т.д. вплоть до MongoID. и используя DSL ActiveRecord, фреймворк не тянет данные из БД напрямую, он просто через адаптер использует те же самые DB-specific вещи, которые _вы_ не должны использовать, следуя rails-way.

                                >контроль уникальности
                                add_index
                                >внешние ключи
                                foreigner
                                >специфичные типы данных (inet, macaddr, polygon etc). Применение SQL'ных вьюшек или хранимых процедур
                                когда веб-приложение является одной из частей действительно бОльшей системы, этими вещами вы и уменьшаете гибкость и сопровождаемость системы, и rails-way именно это и проповедует. это моя точка зрения, и я настаиваю по этому пункту прекратить спор, ибо десяток топиков можно найти на хабре с этой же темой спора. это не более, чем очередной холивор.

                                алсо, вам наличие schema_format говорит об этом, а мне его наличие говорит о том же, о чём и наличие ActiveRecord::Base.connection.execute(sql).

                                в общем говоря, я ещё раз настаиваю приводить аргументы о преимуществах strict mode, а не разводить очередной холивор.
                          0
                          Кстати, если строгий режим не включен, то в поля типа enum и set можно спокойно пытаться писать всякую ересь.
                          В enum тогда запишется пустое значение (и не важно, что в списке возможных подобного нет; enum, согласно документации, имеет зарезервированное значение 0, которое отображается как пустая строка в строковом контексте).
                          Set же просто проигнорирует «лишние» значения и молча запишет только допустимы.
                            0
                            Также и с отрицательными значениями для поля, значения в котором должны быть только положительные.
                            0
                            Больше всего бесят даты типа 0000-00-00. Когда «пустую» дату можно записать двумя способами, один из которых явно не соответствует слову «пустой» — это трындец. Некоторые умельцы даже ставят на date-поля модификатор типа NOT NULL — вместо того, чтобы научить свой код писать в базу NULL вместо заведомой ерунды, либо не писать ничего вообще :(
                              +1
                              NULL в SQL — «значение неизвестно», а совсем не «значения нет» или «пустое значение», в частности поэтому проверки в WHERE на = NULL не работают.
                                +2
                                NULL — он и в Африке NULL.
                                А проверки надо делать так: WHERE a IS NULL и WHERE a IS NOT NULL.
                                  0
                                  Ну почему, в java null означает, ссылка указывающая «вникуда». Это совершенно другой контекст, и соответственно в отличие от SQL в Java null == null.
                                  +1
                                  Вы пытаетесь сказать, что некорректно писать NULL в поле даты, как признак отсутствия информации? Или смысл в том, что народ не любит NULL, т.к. из-за него требуется менять конструкцию SQL-запроса?
                                0
                                в последней строке «касающиеся *TRICT_TRANS_TABLES применительно»?
                                  –2
                                  Но ведь, даже если предположить, что реально писать непробиваемую валидацию, которая не пропустит в базу ни одного неправильного поля, то все равно не все поля можно покрыть валидаторами.

                                  Допустим, что ты будешь делать, если переполнится поле-счетчик, обновляемое по counter_cache?

                                  Или если кто-то повесит на поле, отвечающее за полиморфизм, скажем, длину в 10 символов, а потом в проекте случится класс, у которого имя длиннее 10 символов, и его запишут в это поле?

                                  Можно, естественно, в каждом таком случае писать проверку вручную, но гораздо дешевле включить строгий режим.

                                  Уважаемый автор, во-первых, попрошу не писать по поводу обсуждения и комментариев в личку других сайтов, тем более гитхаба. Гитхаб у меня не для этих целей, да и вообще, хотелось бы, чтобы всё, что происходит на хабре, оставалось на хабре.
                                  по части counter_cache и polymorphyc-type полей: для одного на крайний случай можно сделать bigint, а ограничивать второе я вообще не вижу смысла, varchar всё равно хранится не максимальной а актуальной длины строки. просто создавать себе трудности?
                                  в этих примерах использование strict — совсем пароноидальное решение. при чём вместо устранения причины проблемы, включение strict просто не даёт проявиться её симптомам.
                                    0
                                    то, о чем написано в преамбуле статьи ближе к TRADITIONAL mode, заставляющий сервер кидать исключения на большинство ошибкок. по возможности использую:

                                    SET sql_mode ='TRADITIONAL,ANSI';
                                      0
                                      Немногие из популярных СУБД могут «похвастать» наличием обычного режима. Разработчики в Oracle наверное выпадали в осадок, когда принимали приобретенный продукт.
                                        0
                                        Я бы ещё рекоммендовал использовать NO_UNSIGNED_SUBTRACTION, если вы привыкли пользоваться unsigned полями для PK, для избежания вот таких ситуаций.

                                        mysql> SET sql_mode = '';
                                        mysql> SELECT CAST(0 AS UNSIGNED) - 1;
                                        +-------------------------+
                                        | CAST(0 AS UNSIGNED) - 1 |
                                        +-------------------------+
                                        | 18446744073709551615 |
                                        +-------------------------+

                                        mysql> SET sql_mode = 'NO_UNSIGNED_SUBTRACTION';
                                        mysql> SELECT CAST(0 AS UNSIGNED) - 1;
                                        +-------------------------+
                                        | CAST(0 AS UNSIGNED) - 1 |
                                        +-------------------------+
                                        | -1 |
                                        +-------------------------+

                                        http://dev.mysql.com/doc/refman/5.1/en/server-sql-mode.html#sqlmode_no_unsigned_subtraction

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

                                        Only users with full accounts can post comments. Log in, please.