05.10.2015 15:20
DVLev 27 12687 Источник
Предположим, у вас есть prod и test базы данных. В какой-то момент разработчик внес изменения в тестовую базу, но забыл внести эти изменения в боевую базу. Если это часто используемая таблица, то ситуация очень быстро становится очевидной, так как в логах появятся ошибки в SQL-запросах и вам начинает звонить начальник с упреками «какого @#$%».

Но иногда изменения затрагивают редко используемые таблицы, либо изменения на первый взгляд не совсем очевидны (например, кто-то изменил длину поля VARCHAR и у вас стали обрезаться строки, или кто-то добавил индекс, из-за которого запросы на тестовой базе выполняются на порядок быстрее).

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

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



Что приходит на ум? Делаем dump структуры одной базы, потом другой. Запускаем diff-утилиту и она показывает строки, которые отличаются в двух дампах. Для небольших изменений это может сработать. Но, во-первых, это несколько утомительно, во-вторых, может получиться ситуация как на скриншоте, когда в одной базе отсутствует таблица и diff начинает сравнивать разные таблицы.



Столкнувшись с указанными проблемами, я написал небольшой php-скрипт (https://github.com/dlevsha/compalex), который работает с MySQL, MS SQL Server, PostgreSQL (в планах поддержка Oracle) и позволяет сравнить две базы данных. Скрипт не тянет за собой никаких зависимостей, что упрощает его установку и поддержку.

Для работы необходимо установить сам скрипт (предполагается что php >= 5.4 с поддержкой PDO у вас уже установлен, если нет — то в debian / ubuntu это делается одной строчкой aptitude install php5, единственное — смотрите, чтобы был установлен php версии не ниже 5.4)

$ git clone https://github.com/dlevsha/compalex.git
$ cd compalex


Открываем config.php в папке проекта и раcкомментируем строки с нужным драйвером. Например, я хочу сравнить две mysql базы данных

// MySQL sample config
define('FIRST_DSN',  'mysql://login:password@localhost/compalex_test_1');
define('SECOND_DSN', 'mysql://login:password@localhost/compalex_test_2');


Для того, чтобы не запутаться где какая база данных — даем им имена
define('FIRST_DATABASE_NAME', 'Production database');
define('SECOND_DATABASE_NAME', 'Developer database');


После этого находясь в папке проекта запускаем web-сервер

$ php -S localhost:8000


и открываем в браузере http://localhost:8000/

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



Назначения элементов изображено на схеме.

Иногда нет возможности организовать прямое локальное подключение к базам данных и изменения необходимо посмотреть прямо на сервере из консоли.

Для этих целей я рекомендую использовать консольный браузер eLinks, который поддерживает HTML-форматирование и цвета в разметке.

Необходимо установить скрипт на сервере, запустить web-сервер (как это описано выше) и выполнить: 

$ elinks http://localhost:8000


Получим примерно следующее:



Более подробная информация на сайте (на английском) http://compalex.net/.

Здесь можно попробовать как все это работает http://demo.compalex.net/.

Комментарии (27)


  1. maxru
    05.10.2015 18:36
    #8601289
    +2

    Doctrine\DBAL\Schema\Comparator


  1. aml
    05.10.2015 19:05
    #8601319
    +2

    А не проще хранить каноническую схему базы данных в репозитарии вместе с кодом, а при выкатке новой версии запускать синхронизатор схемы, который вычислит разницу между канонической схемой и реальной, и выдаст (или автоматически выполнит) нужные SQL-операторы, чтобы привести базу к нужному состоянию?


    1. DVLev
      05.10.2015 19:10
      #8601325
      +5

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

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


      1. aml
        05.10.2015 20:58
        #8601463
        +1

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


      1. erlyvideo
        06.10.2015 10:56
        #8601919

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


      1. msfs11
        06.10.2015 15:43
        #8602421

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


        1. DVLev
          06.10.2015 16:48
          #8602523

          Посмотрите
          https://github.com/dlevsha/nasgrate
          на мой взгляд, данный вопрос более менее раскрыт в описании


  1. enleur
    05.10.2015 19:45
    #8601369

    В SQLYog можно сравнить структуры


    1. DVLev
      05.10.2015 19:55
      #8601383
      +3

      SQLYog:
      — платная
      — только для MySQL
      — только под Windows

      Compalex (мой скрипт):
      — бесплатный
      — поддерживает MySQL, PostgreSQL и MSSQL (в перспективе Oracle)
      — устанавливается везде где есть php (а он есть практически под любую платформу)


      1. maxru
        09.10.2015 15:00
        #8606913

        Postgres какой версии поддерживает?

        hstore, jsonb, всякие vartype[] понимает?


        1. DVLev
          09.10.2015 17:01
          #8607225

          hstore, jsonb, всякие vartype[] — насколько я понимаю это типы полей — должен понимать.


  1. Sayonji
    05.10.2015 19:53
    #8601381

    А сложно доделать сравнение имеющейся базы с .sql файлом, создающим её? Но без выполнения этого .sql файла. А то часто инсталлер расходится с имеющейся базой, а по патчам довольно муторно восстанавливать структуру.


    1. DVLev
      05.10.2015 20:14
      #8601413
      +1

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

      CREATE DATABASE new_database;
source /[path_to_file]


      … сравниваем с имеющейся базой

      DROP DATABASE new_database;


      1. to0n1
        05.10.2015 20:18
        #8601425

        Думаю для таких случаев, было бы хорошо иметь поддержку SQLite


  1. MikeSam
    05.10.2015 20:16
    #8601421

    Дополнительно, бывает полезно сравнить индексы, триггеры. Для MS SQL часто «разъезжаются» job's


    1. DVLev
      05.10.2015 20:20
      #8601427

      Индексы можно сравнивать уже сейчас.

      Триггеры — думаю можно добавить.


  1. ecl
    05.10.2015 22:44
    #8601575
    +3

    Обычно это Navicat-ом делаю.

    1) Вам стоит пересмотреть шрифты. Это же не нью йорк таймс. Используйте что-то более современное без засечек, а для листинга полей программерский моноширный шрифт вроде courier new.

    2) Строки коннектов нужно дать возможность ввести на лету в форму или просто input:text. Не всегда работа идет с одним и тем же проектом, иногда нужно просто сравнить что-то мелкое и забыть. Без создания конфигов и пр.

    Спасибо, попробую воспользоваться в будущем.


  1. hanovruslan
    06.10.2015 07:24
    #8601765
    +1

    При всем уважении, это похоже на инструмент для решения симптомов, но не проблемы (расхождение схемы данных).

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


    1. DVLev
      06.10.2015 11:49
      #8602007

      Добавил в текст статьи вариант использования скрипта в консоли.


  1. wir_wolf
    06.10.2015 08:33
    #8601797

    sqlyog уже не раз юзал для этих целей. navicat по-моему тоже умеет. скармливаешь системе 2 конекшена и все. ну и она не только показывает дифы, а ещё и может применить их


  1. gotch
    06.10.2015 09:14
    #8601825

    Пользовались для этого Toad.

    Текст хранимых процедур анализируете?


    1. DVLev
      06.10.2015 12:11
      #8602051

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


  1. landy
    06.10.2015 15:03
    #8602371

    Еще тогда вам feature request: сравнение ENGINE, CHARSET, COLLATE.

    А в целом действительно полезная утилитка (особенно для консоли).


  1. rieder
    06.10.2015 16:02
    #8602445

    Сравнивать схемы (помимо прочего) можно еще в DBeaver — бесплатное, опен-сорсное, мультиплатформенное и мультибазное.
    Фича правда пока сыровата, в частности не умеет генерить diff-DDL. Но если будут реквесты — доделается.


    1. landy
      06.10.2015 17:02
      #8602553

      Вот кстати, отдельная утилита сравнения гораздо привлекательней во многих случаях, чем некий (любой) DB manager, который еще надо установить на свой/рабочий ПК (а иногда и купить).

      И тем более привлекательна консольная версия, против всяких GUI.


  1. rieder
    06.10.2015 20:05
    #8602929

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


  1. iStorm
    08.10.2015 00:12
    #8604615

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