Практический опыт миграции с MySQL на SQL Express. Сравнение схем и перенос данных.


Миграция 1С-БИТРИКС с MySQL на SQL Server Express


Алексей Шуленин, Microsoft; Денис Шаромов, Битрикс


 


Окончание. Начало - см. http://blogs.msdn.com/alexejs/archive/2009/07/07/mysql-sql-express.aspx;   http://blogs.msdn.com/alexejs/archive/2009/07/07/mysql-sql-express-mysql.aspx; http://blogs.msdn.com/alexejs/archive/2009/07/10/mysql-sql-express-sql-express.aspx; http://blogs.msdn.com/alexejs/archive/2009/07/10/200710_5F00_02.aspx.


  


 


7.     Сравнение структур баз


 


Итак на данный момент у нас имеется старый контент сайта в БД bsm_demo на MySQL и новый, но пустой сайт в БД bitrix на SQL Express. Необходимо перенести старый контент в новую базу. В связи с этим встал вопрос, насколько тождественны структуры баз Битрикса в случае MySQL и SQL Express. Для исследования этого вопроса использовался прилинкованный сервер со стороны SQL Express на MySQL. Прилинкованный сервер использует MSDASQL (OLE DB поверх ODBC) в связи с тем, что доступного OLE DB-провайдера на MySQL не нашлось (см. п. 2). На машину с установленными MySQL и SQL Express был установлен ODBC-драйвер для MySQL 5.1 (http://dev.mysql.com/downloads/connector/odbc/5.1.html) и создан прилинкованный сервер без создания DSN:


 


if exists (select 1 from sys.servers where name = 'MySQL')


 exec sp_dropserver @server = 'MySQL', @droplogins = 'droplogins'


go


exec sp_addlinkedserver @server = 'MySQL', @srvproduct = 'MySQLDatabase', @provider = 'MSDASQL',


 @provstr = 'DRIVER={MySQL ODBC 5.1 Driver}; SERVER=localhost; PORT=31006; DATABASE=bsm_demo; UID=root; PWD=; OPTION=3'


 go


exec sp_addlinkedsrvlogin @rmtsrvname = 'MySQL', @useself = 'false', @locallogin = NULL,


 @rmtuser = 'root', @rmtpassword = ''


go


exec sp_serveroption @server = 'MySQL', @optname = 'rpc', @optvalue = 'true'


exec sp_serveroption @server = 'MySQL', @optname = 'rpc out', @optvalue = 'true'


 


Скрипт  7.1


 


что позволяет выполнять на MySQL запросы, адресованные к SQL Server, и возвращать результат MySQL, как если бы это был результат SQL Server:


 


image001



Рис. 7.1


 


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


 


use bitrix


 


with


tbl_rows_sqlsrv as (


select t.name, p.n from sys.tables t


join (select object_id, sum(row_count) n from sys.dm_db_partition_stats where index_id in (0, 1) group by object_id) p


on t.object_id = p.object_id


)


, tbl_rows_mysql as (


select * from openquery(mysql, 'select table_name name, table_rows n from information_schema.tables where table_schema = ''bsm_demo''')


)


select sqlsrv.name, sqlsrv.n, mysql.name, mysql.n from tbl_rows_sqlsrv sqlsrv full outer join tbl_rows_mysql mysql on sqlsrv.name = mysql.name


where sqlsrv.name is null or mysql.name is null or sqlsrv.n <> mysql.n


 







































name


n


name


n


B_OPTION


112


b_option


113


B_STAT_SESSION_DATA


0


b_stat_session_data


1


B_FILE_ACTION


0


NULL


NULL


B_POSTING_LOCK


0


NULL


NULL


B_FAVORITE_LANG


0


NULL


NULL


Скрипт  7.2


 


Вывод. Все 319 таблиц в базе при инсталляции MySQL имеют соответствия (т.е. таблицы с тем же именем) в SQL Serverной инсталляции.


В SQL Serverной инсталляции имеются 3 таблицы, не имеющих соответствия в MySQLной инсталляции:



  • B_FILE_ACTION
  • B_POSTING_LOCK
  • B_FAVORITE_LANG

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


 


Из 319 таблиц MySQLной инсталляции 2 не совпадают по числу строк с соответствющими им таблицами в SQL Serverной инсталляции:


 

















SQL Server


MySQL


B_OPTION


112


b_option


113


B_STAT_SESSION_DATA


0


b_stat_session_data


1


 


Этот факт имеет, скорее, роль комментария, поскольку в данном случае инсталляции Битрикса как в случае MySQL, так и SQL Server "чистые". В реальной жизни клиент уже будет работать продолжительный период с MySQLной базой, поэтому данных там, очевидно, будет больше, чем в "свежей" SQL Serverной БД. Речь не идет о каком-то слиянии, реконсилиации, просто данные из MySQL требуется перенести в соответствующие таблицы SQL Server, перетерев все, что туда уже успел добавить процесс инсталляции Битрикс. Инсталляция Битрикса в варианте SQL Server создает, по сути, готовые структуры для приема данных со стороны SQL Server и избавляет нас от необходимости рассматривать миграцию метаданных.


 


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


 


use bitrix


 


with


col_sqlsrv as (


select t.name as tbl_name, c.name as col_name from sys.columns c join sys.tables t on c.object_id = t.object_id


)


, col_mysql as (


select * from openquery(mysql, 'select table_name tbl_name, column_name col_name from information_schema.columns where table_schema = ''bsm_demo''')


)


select * from col_sqlsrv sqlsrv full outer join col_mysql mysql


on sqlsrv.tbl_name = mysql.tbl_name and sqlsrv.col_name = mysql.col_name


where sqlsrv.col_name is null or mysql.col_name is null


Скрипт  7.3


 


Сравнение выявило абсолютное тождество наборов полей в соответствующих таблицах. Исключением являются три таблицы в  SQL Server, которых нет в варианте установки MySQL (см. Скрипт  7.2).


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


 


with


col_sqlsrv(tbl_name, col_name,  col_pos) as (


select object_name(object_id), name, column_id from sys.columns


)


, col_mysql(tbl_name, col_name,  col_pos) as (


select * from openquery(mysql, 'select table_name, column_name, ordinal_position from information_schema.columns where table_schema = ''bsm_demo''')


)


select * from col_sqlsrv sqlsrv join col_mysql mysql


on sqlsrv.tbl_name = mysql.tbl_name and sqlsrv.col_name = mysql.col_name


where sqlsrv.col_pos <> mysql.col_pos


Скрипт  7.4


 


Порядковый номер колонки не совпадает в 80 случаях, что необходимо учитывать при переносе данных.


 


Наконец, интересовало соответствие типов и длины в одноименных колонках. Были получены типы колонок, использующиеся Битриксом в случае MySQLной и SQL Serverной инсталляций:


 


select * from openquery(mysql, 'select distinct data_type from information_schema.columns where table_schema = ''bsm_demo''')


select distinct type_name(c.user_type_id) from sys.columns c join sys.tables t on c.object_id = t.object_id where type = 'U'


Скрипт  7.5


В SQL Server их оказалось 11, в MySQL – 16. Например, в  MySQL используется тип smallint, а в SQL Server его не нашли. В MySQL есть разновидности блобовских типов, которых действительно нет в SQL Server, например, mediumtext, longtext. В случае SQL Server им соответствует один тип text. Кстати говоря, Битрикс в SQL Server почему-то до сих пор вовсю использует text/image, хотя они скоро выйдут из употребления, т.к. уже больше 3-х лет назад появились varchar(max), varbinary(max). Также, например, Битрикс использует тип date в MySQL, а в SQL Server по старинке datetime. Вручную была построена таблица соответствия типов, т.е. какой тип MySQL в какой тип SQL Server можно без потерь переносить. Если существует экземпляр типа А, который не перенесется в тип Б без обрезания или дополнительных преобразований, такой перенос считается невозможным. Все типы и в MySQL, и в SQL Server можно разбить на числовые, строковые, бинарные и календарные. Перенос внутри каждой категории считается допустимым, при этом длина поля приемника должна быть не меньше, чем у источника, а в случае численных полей с фиксированной точкой то же распространяется и на кол-во знаков после запятой. Вот запрос, который проверяет нарушения этого правила:


 


with


col_sqlsrv(tbl_name, col_name, col_type, col_len, col_prec, col_scal) as (


select object_name(object_id), name, type_name(user_type_id), max_length, precision, scale from sys.columns


),


col_sqlsrv1(tbl_name, col_name, col_type, col_len, col_prec, col_scal) as (


select tbl_name, col_name,


       case when col_type in ('bigint', 'int', 'tinyint', 'decimal', 'numeric') then 'N'


            when col_type in ('float') then 'F'


            when col_type in ('datetime') then 'D'


            when col_type in ('char', 'varchar', 'text') then 'C'


            when col_type in ('image') then 'B'


       end,


       case when col_type = 'text' then power(cast(2 as bigint), 31) - 1


            else col_len


       end,


       col_prec, col_scal from col_sqlsrv


)      


, col_mysql(tbl_name, col_name, col_type, col_len, col_prec, col_scal) as (


select * from openquery(mysql, 'select table_name, column_name, data_type, character_maximum_length, numeric_precision, numeric_scale from information_schema.columns where table_schema = ''bsm_demo''')


)


, col_mysql1(tbl_name, col_name, col_type, col_len, col_prec, col_scal) as (


select tbl_name, col_name,


       case when col_type in ('bigint', 'int', 'tinyint', 'decimal', 'smallint') then 'N'


            when col_type in ('float') then 'F'


            when col_type in ('datetime', 'timestamp') then 'D'


            when col_type in ('char', 'varchar', 'text', 'mediumtext', 'longtext') then 'C'


            when col_type in ('longblob') then 'B' end,


       col_len, col_prec, col_scal from col_mysql


)      


select sqlsrv.*, mysql.col_type, mysql.col_len, mysql.col_prec, mysql.col_scal from col_sqlsrv1 sqlsrv join col_mysql1 mysql


on sqlsrv.tbl_name = mysql.tbl_name and sqlsrv.col_name = mysql.col_name


where mysql.col_type <> sqlsrv.col_type


   or mysql.col_type = sqlsrv.col_type and mysql.col_len > sqlsrv.col_len


   or mysql.col_type = sqlsrv.col_type and (mysql.col_prec > sqlsrv.col_prec or mysql.col_scal > sqlsrv.col_scal)


order by 1, 2


Скрипт  7.6


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


 




















































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































SQL Express


MySQL


Таблица


Колонка


Категория типа


Размер поля


Численная длина


После запятой


Категория типа


Размер поля


Численная длина


После запятой


B_ADV_BANNER


COMMENTS


C


1000


0


0


C


65535


NULL


NULL


B_ADV_BANNER


KEYWORDS


C


1000


0


0


C


65535


NULL


NULL


B_ADV_BANNER


STATUS_COMMENTS


C


500


0


0


C


65535


NULL


NULL


B_ADV_BANNER


URL


C


8000


0


0


C


65535


NULL


NULL


B_ADV_CONTRACT


ADMIN_COMMENTS


C


500


0


0


C


65535


NULL


NULL


B_ADV_CONTRACT


DESCRIPTION


C


2000


0


0


C


65535


NULL


NULL


B_ADV_CONTRACT


KEYWORDS


C


1000


0


0


C


65535


NULL


NULL


B_ADV_TYPE


DESCRIPTION


C


500


0


0


C


65535


NULL


NULL


B_CATALOG_LOAD


VALUE


C


2000


0


0


C


65535


NULL


NULL


B_EVENT


C_FIELDS


C


2147483647


0


0


C


4294967295


NULL


NULL


B_EVENT_LOG


REQUEST_URI


C


2000


0


0


C


65535


NULL


NULL


B_EVENT_LOG


USER_AGENT


C


2000


0


0


C


65535


NULL


NULL


B_EVENT_TYPE


LID


C


2


0


0


C


201


NULL


NULL


B_FAVORITE


COMMENTS


C


8000


0


0


C


65535


NULL


NULL


B_FAVORITE


URL


C


8000


0


0


C


65535


NULL


NULL


B_FORM


DESCRIPTION


C


8000


0


0


C


65535


NULL


NULL


B_FORM


FILTER_RESULT_TEMPLATE


C


8000


0


0


C


65535


NULL


NULL


B_FORM


TABLE_RESULT_TEMPLATE


C


8000


0


0


C


65535


NULL


NULL


B_FORM_2_GROUP


PERMISSION


N


1


3


0


N


NULL


10


0


B_FORM_ANSWER


FIELD_PARAM


C


8000


0


0


C


65535


NULL


NULL


B_FORM_ANSWER


MESSAGE


C


8000


0


0


C


65535


NULL


NULL


B_FORM_FIELD


COMMENTS


C


8000


0


0


C


65535


NULL


NULL


B_FORM_FIELD


FILTER_TITLE


C


8000


0


0


C


65535


NULL


NULL


B_FORM_FIELD


RESULTS_TABLE_TITLE


C


8000


0


0


C


65535


NULL


NULL


B_FORM_FIELD


TITLE


C


8000


0


0


C


65535


NULL


NULL


B_FORM_RESULT_ANSWER


ANSWER_TEXT


C


8000


0


0


C


65535


NULL


NULL


B_FORM_RESULT_ANSWER


ANSWER_TEXT_SEARCH


C


2147483647


0


0


C


4294967295


NULL


NULL


B_FORM_RESULT_ANSWER


ANSWER_VALUE_SEARCH


C


2147483647


0


0


C


4294967295


NULL


NULL


B_FORM_RESULT_ANSWER


USER_TEXT


C


2147483647


0


0


C


4294967295


NULL


NULL


B_FORM_RESULT_ANSWER


USER_TEXT_SEARCH


C


2147483647


0


0


C


4294967295


NULL


NULL


B_FORM_STATUS


DESCRIPTION


C


8000


0


0


C


65535


NULL


NULL


B_FORUM


DESCRIPTION


C


1000


0


0


C


65535


NULL


NULL


B_FORUM_FILTER


USE_IT


C


1


0


0


C


50


NULL


NULL


B_FORUM_MESSAGE


ID


N


4


10


0


N


NULL


19


0


B_FORUM_MESSAGE


TOPIC_ID


N


4


10


0


N


NULL


19


0


B_FORUM_PRIVATE_MESSAGE


ID


N


4


10


0


N


NULL


19


0


B_FORUM_PRIVATE_MESSAGE


IS_READ


C


1


0


0


C


50


NULL


NULL


B_FORUM_PRIVATE_MESSAGE


USE_SMILES


C


1


0


0


C


50


NULL


NULL


B_FORUM_STAT


ID


N


4


10


0


N


NULL


19


0


B_FORUM_SUBSCRIBE


NEW_TOPIC_ONLY


C


1


0


0


C


50


NULL


NULL


B_FORUM_TOPIC


ID


N


4


10


0


N


NULL


19


0


B_FORUM_TOPIC


LAST_MESSAGE_ID


N


4


10


0


N


NULL


19


0


B_FORUM_TOPIC


TOPIC_ID


N


4


10


0


N


NULL


19


0


B_FORUM_USER


ID


N


4


10


0


N


NULL


19


0


B_FORUM_USER_TOPIC


ID


N


4


10


0


N


NULL


19


0


B_IBLOCK_ELEMENT


DETAIL_TEXT


C


2147483647


0


0


C


4294967295


NULL


NULL


B_IBLOCK_ELEMENT


PREVIEW_TEXT


C


2000


0


0


C


65535


NULL


NULL


B_IBLOCK_ELEMENT_PROPERTY


VALUE


C


2000


0


0


C


65535


NULL


NULL


B_IBLOCK_FIELDS


DEFAULT_VALUE


C


2147483647


0


0


C


4294967295


NULL


NULL


B_LDAP_SERVER


DESCRIPTION


C


5000


0


0


C


65535


NULL


NULL


B_LDAP_SERVER


FIELD_MAP


C


2000


0


0


C


65535


NULL


NULL


B_LEARN_CHAPTER


DETAIL_TEXT


C


2147483647


0


0


C


4294967295


NULL


NULL


B_LEARN_LESSON


DETAIL_TEXT


C


2147483647


0


0


C


4294967295


NULL


NULL


B_LIST_RUBRIC


DESCRIPTION


C


2000


0


0


C


65535


NULL


NULL


B_MAIL_FILTER


ACTION_VARS


C


5000


0


0


C


65535


NULL


NULL


B_MAIL_FILTER


DESCRIPTION


C


2000


0


0


C


65535


NULL


NULL


B_MAIL_FILTER_COND


STRINGS


C


5000


0


0


C


65535


NULL


NULL


B_MAIL_MAILBOX


DESCRIPTION


C


5000


0


0


C


65535


NULL


NULL


B_MAIL_MESSAGE


BODY


C


2147483647


0


0


C


4294967295


NULL


NULL


B_MAIL_MESSAGE


FULL_TEXT


C


2147483647


0


0


C


4294967295


NULL


NULL


B_MAIL_MSG_ATTACHMENT


FILE_DATA


B


16


0


0


B


4294967295


NULL


NULL


B_PERF_ERROR


ERRFILE


C


2000


0


0


C


65535


NULL


NULL


B_PERF_ERROR


ERRSTR


C


2000


0


0


C


65535


NULL


NULL


B_PERF_HIT


REQUEST_URI


C


2000


0


0


C


65535


NULL


NULL


B_PERF_HIT


SCRIPT_NAME


C


2000


0


0


C


65535


NULL


NULL


B_PERF_SQL


COMPONENT_NAME


C


2000


0


0


C


65535


NULL


NULL


B_PERF_SQL


MODULE_NAME


C


2000


0


0


C


65535


NULL


NULL


b_search_content


PARAM1


C


1000


0


0


C


65535


NULL


NULL


b_search_content


PARAM2


C


1000


0


0


C


65535


NULL


NULL


b_search_content


SEARCHABLE_CONTENT


C


2147483647


0


0


C


4294967295


NULL


NULL


b_search_custom_rank


PARAM1


C


2000


0


0


C


65535


NULL


NULL


b_search_custom_rank


PARAM2


C


2000


0


0


C


65535


NULL


NULL


B_SEC_SESSION


SESSION_DATA


C


2147483647


0


0


C


4294967295


NULL


NULL


B_STAT_ADV


DESCRIPTION


C


8000


0


0


C


65535


NULL


NULL


B_STAT_EVENT


DESCRIPTION


C


8000


0


0


C


65535


NULL


NULL


B_STAT_EVENT_LIST


REDIRECT_URL


C


8000


0


0


C


65535


NULL


NULL


B_STAT_EVENT_LIST


REFERER_URL


C


8000


0


0


C


65535


NULL


NULL


B_STAT_EVENT_LIST


URL


C


8000


0


0


C


65535


NULL


NULL


B_STAT_GUEST


FIRST_URL_FROM


C


8000


0


0


C


65535


NULL


NULL


B_STAT_GUEST


FIRST_URL_TO


C


8000


0


0


C


65535


NULL


NULL


B_STAT_GUEST


LAST_CITY_INFO


C


8000


0


0


C


65535


NULL


NULL


B_STAT_GUEST


LAST_COOKIE


C


8000


0


0


C


65535


NULL


NULL


B_STAT_GUEST


LAST_URL_LAST


C


8000


0


0


C


65535


NULL


NULL


B_STAT_GUEST


LAST_USER_AGENT


C


8000


0


0


C


65535


NULL


NULL


B_STAT_HIT


COOKIES


C


8000


0


0


C


65535


NULL


NULL


B_STAT_HIT


URL


C


8000


0


0


C


65535


NULL


NULL


B_STAT_HIT


URL_FROM


C


8000


0


0


C


65535


NULL


NULL


B_STAT_HIT


USER_AGENT


C


8000


0


0


C


65535


NULL


NULL


B_STAT_PAGE


URL


C


2000


0


0


C


65535


NULL


NULL


B_STAT_PATH


PAGES


C


8000


0


0


C


65535


NULL


NULL


B_STAT_PATH_CACHE


PATH_PAGES


C


8000


0


0


C


65535


NULL


NULL


B_STAT_PHRASE_LIST


URL_FROM


C


8000


0


0


C


65535


NULL


NULL


B_STAT_PHRASE_LIST


URL_TO


C


8000


0


0


C


65535


NULL


NULL


B_STAT_REFERER_LIST


URL_FROM


C


8000


0


0


C


65535


NULL


NULL


B_STAT_REFERER_LIST


URL_TO


C


8000


0


0


C


65535


NULL


NULL


B_STAT_SEARCHER


USER_AGENT


C


8000


0


0


C


65535


NULL


NULL


B_STAT_SEARCHER_HIT


URL


C


8000


0


0


C


65535


NULL


NULL


B_STAT_SEARCHER_HIT


USER_AGENT


C


8000


0


0


C


65535


NULL


NULL


B_STAT_SESSION


URL_FROM


C


8000


0


0


C


65535


NULL


NULL


B_STAT_SESSION


URL_LAST


C


8000


0


0


C


65535


NULL


NULL


B_STAT_SESSION


URL_TO


C


8000


0


0


C


65535


NULL


NULL


B_STAT_SESSION


USER_AGENT


C


8000


0


0


C


65535


NULL


NULL


B_STAT_SESSION_DATA


SESSION_DATA


C


8000


0


0


C


65535


NULL


NULL


B_STOP_LIST


COMMENTS


C


8000


0


0


C


65535


NULL


NULL


B_STOP_LIST


MESSAGE


C


8000


0


0


C


65535


NULL


NULL


B_STOP_LIST


URL_FROM


C


8000


0


0


C


65535


NULL


NULL


B_STOP_LIST


URL_REDIRECT


C


8000


0


0


C


65535


NULL


NULL


B_STOP_LIST


URL_TO


C


8000


0


0


C


65535


NULL


NULL


B_STOP_LIST


USER_AGENT


C


8000


0


0


C


65535


NULL


NULL


B_TICKET


AUTO_CLOSE_DAYS


N


1


3


0


N


NULL


10


0


B_TICKET


LAST_MESSAGE_SID


C


8000


0


0


C


65535


NULL


NULL


B_TICKET


OWNER_SID


C


8000


0


0


C


65535


NULL


NULL


B_TICKET


SUPPORT_COMMENTS


C


8000


0


0


C


65535


NULL


NULL


B_TICKET


TITLE


C


2000


0


0


C


65535


NULL


NULL


B_TICKET_DICTIONARY


DESCR


C


8000


0


0


C


65535


NULL


NULL


B_TICKET_MESSAGE


EXTERNAL_FIELD_1


C


8000


0


0


C


65535


NULL


NULL


B_TICKET_MESSAGE


MESSAGE


C


2147483647


0


0


C


4294967295


NULL


NULL


B_TICKET_MESSAGE


MESSAGE_SEARCH


C


2147483647


0


0


C


4294967295


NULL


NULL


B_TICKET_MESSAGE


OWNER_SID


C


8000


0


0


C


65535


NULL


NULL


B_TICKET_SLA


DESCRIPTION


C


8000


0


0


C


65535


NULL


NULL


B_VOTE


DESCRIPTION


C


5000


0


0


C


65535


NULL


NULL


B_VOTE_ANSWER


FIELD_TYPE


N


1


3


0


N


NULL


10


0


B_VOTE_ANSWER


MESSAGE


C


5000


0


0


C


65535


NULL


NULL


B_VOTE_EVENT_ANSWER


MESSAGE


C


8000


0


0


C


65535


NULL


NULL


B_VOTE_QUESTION


QUESTION


C


5000


0


0


C


65535


NULL


NULL


B_WORKFLOW_DOCUMENT


COMMENTS


C


8000


0


0


C


65535


NULL


NULL


B_WORKFLOW_LOG


COMMENTS


C


8000


0


0


C


65535


NULL


NULL


B_WORKFLOW_STATUS


DESCRIPTION


C


8000


0


0


C


65535


NULL


NULL


 


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


 


 


8.     Перенос данных


 


Однако использовать MySQL ODBC Connector 5.1 для переноса данных оказалось не лучшим вариантом, поскольку были выявлены ситуации, в которых его работоспособность нарушалась. В их числе отсутствие поддержки типа longtext:


 


select * from openquery(mysql, 'select DETAIL_TEXT from b_learn_lesson')


-----------


Msg 0, Level 11, State 0, Line 0


A severe error occurred on the current command.  The results, if any, should be discarded.


 


Скрипт  8.1


 


Можно обеспечить перенос подстроками по 4000 символов, но такой способ нельзя признать оптимальным. В связи с этим мы рекомендуем использовать для переноса данных MySQL Connector/Net 6.0, который можно свободно скачать по адресу http://dev.mysql.com/downloads/connector/net/6.0.html. Его установка не вызывает каких-либо сложностей:


 



image003


Рис. 8.1


 


image005



Рис. 8.2


 


image007



Рис. 8.3


 


Под словом "провайдер" понимается расширение функциональности .NET. Connector/Net 6.0 добавляет новые пространства имен MySql.Data в .NET, что позволяет работать c MySQL из .NET-приложений столь же элегантно, как, например, с SQL Server при помощи System.Data.SqlClient


 


image009



Рис. 8.4


 


В плане OLE DB-провайдеров никаких новшеств он не привносит, т.к., вероятно, используется прямой доступ, подобно SQL Native Client, следовательно, сказать что-то новое про создание прилинкованного сервера по сравнению с п.7 на его основе нельзя. В качестве рабочего инструмента для миграции можно использовать Visual Studio, в частности, ее бесплатную редакцию - http://www.microsoft.com/express/download/. Откройте Visual Studio, выберите в меню File -> New -> Project


 



image011


Рис. 8.5


 


Выберите в качестве шаблона проекта создание нового консольного приложения на C#:


 


image013



Рис. 8.6


 


Добавьте к ссылкам (References) проекта пространство имен MySql.Data, которое добавляет MySql Connector/Net 6.0, как показано на Рис. 8.4.


 


image015


Рис. 8.7


 


и напишите следующий код:


 


using System;


using System.Text;


 


using MySql.Data.MySqlClient;


using System.Data;


using System.Diagnostics;


using System.Data.SqlClient;


 


class Program


{


    static MySqlConnection mySqlCnn;


    static SqlConnection sqlSrvCnn;


 


    static void Main(string[] args)


    {


        sqlSrvCnn = new SqlConnection(@"server=(local)\SQLExpress;database=bitrix;trusted_connection=true;MultipleActiveResultSets=true");


        sqlSrvCnn.Open();


        mySqlCnn = new MySqlConnection("server=127.0.0.1;port=31006;uid=root;pwd=;database=bsm_demo;Pooling=False");


        mySqlCnn.Open();


 


        DisEnableFKConstraints(true);


        DataTable tblList = GetSourceTablesFromMySQLDB();


        CleanDestTablesInSQLSrvDB(tblList);


        TransferData(tblList);


        DisEnableFKConstraints(false);


 


        mySqlCnn.Close();


        sqlSrvCnn.Close();


    }


 


    /// <summary>


    /// Копирует данные из таблицы в MySQL в одноименную таблицу в SQL Server


    /// Предполагается, что множества имен полей в таблицах совпадают. Порядок может отличаться.


    /// </summary>


    /// <param name="tblName">Имя таблицы</param>


    static void CopyDataFromMySQLTblToCorrespondingSQLSrvTbl(string tblName)


    {


        //Читаем по порядку поля в таблице-назначения


        SqlCommand sqlSrvCmd = sqlSrvCnn.CreateCommand();


        sqlSrvCmd.CommandText = "select name from sys.columns where object_id = object_id(@tblName) order by column_id";


        sqlSrvCmd.Parameters.AddWithValue("@tblName", tblName);


        SqlDataReader sqlSrvDr = sqlSrvCmd.ExecuteReader(CommandBehavior.SingleResult);


        //Составляем строку запроса для источника, перечисляя туда поля в том порядке, как они следуют в назначении


        StringBuilder mySqlCmdText = new StringBuilder("select ");


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


        while (sqlSrvDr.Read()) mySqlCmdText.Append("`" + sqlSrvDr.GetSqlString(0).Value + "`,");


        sqlSrvDr.Close();


        mySqlCmdText.Remove(mySqlCmdText.Length - 1, 1);


        mySqlCmdText.Append(" from " + tblName);


 


        MySqlCommand mySqlCmd = new MySqlCommand(mySqlCmdText.ToString(), mySqlCnn);


        MySqlDataReader mySqlDr = mySqlCmd.ExecuteReader();


 


        SqlBulkCopy bcp = new SqlBulkCopy(sqlSrvCnn, SqlBulkCopyOptions.KeepIdentity, null);


        //KeepIdentity означает set identity_insert <tblName> on/off


        //Поскольку в mySqlDr поля идут в том же порядке, что и в назначении, SqlBulkCopy.ColumnMappings не требуется.


        bcp.DestinationTableName = tblName;


// Заправляем шланг ридера объекту SqlBulkCopy, чтобы он качал из него содержимое в bcp.DestinationTableName


        bcp.WriteToServer(mySqlDr);


 


        mySqlDr.Close();


    }


 


    /// <summary>


    /// Получает список таблиц из MySQLной базы


    /// </summary>


    /// <returns>Список таблиц</returns>


    static DataTable GetSourceTablesFromMySQLDB()


    {


        DataTable tbl = new DataTable();


        tbl.Load(new MySqlCommand("show tables", mySqlCnn).ExecuteReader());


        return tbl;


    }


 


    /// <summary>


    /// Удаляет в каждой таблице из списка все ее записи


    /// </summary>


    /// <param name="tblList">Список таблиц</param>


    static void CleanDestTablesInSQLSrvDB(DataTable tblList)


    {


        Debug.WriteLine("Очистка таблиц назначения...");


        foreach (DataRow r in tblList.Rows)


        {


            new SqlCommand("delete " + r[0].ToString(), sqlSrvCnn).ExecuteNonQuery();


            Debug.WriteLine("Очищена таблица " + r[0].ToString());


        }


        Debug.WriteLine("Очистка закончена.");


    }


 


    static void TransferData(DataTable tblList)


    {


        Debug.WriteLine("Загрузка данных...");


        foreach (DataRow r in tblList.Rows)


        {


            CopyDataFromMySQLTblToCorrespondingSQLSrvTbl(r[0].ToString());


            Debug.WriteLine("Перенесена таблица " + r[0].ToString());


        }


        Debug.WriteLine("Загрузка завершена.");


    }


 


    /// <summary>


    /// Процедура отключает/включает все ограничения внешнего ключа над таблицами в БД SQL Server


    /// </summary>


    /// <param name="switchOff">Если да, то отключить, нет - включить</param>


    static void DisEnableFKConstraints(bool switchOff)


    {


        string prefix = switchOff ? "От" : "В";


        Debug.WriteLine(prefix + "ключение FK-ограничений...");


        SqlDataReader sdr = new SqlCommand("select name, object_name(parent_object_id) from sys.foreign_keys", sqlSrvCnn).ExecuteReader();


        while (sdr.Read())


        {


            string fkName = sdr.GetString(0), tblName = sdr.GetString(1);


            new SqlCommand(String.Format("alter table {0} {1}check constraint {2}", tblName, switchOff ? "no" : "", fkName), sqlSrvCnn).ExecuteNonQuery();


            Debug.WriteLine(String.Format("{0}ключено ограничение {1} в таблице {2}", prefix, fkName, tblName));


        }


        sdr.Close();


        Debug.WriteLine(prefix + "ключение FK-ограничений завершено.");


    }


}


Скрипт  8.2


 


Необходимо сделать некоторые комментарии к коду.


Как показывает


 


select * from sys.objects where type = 'F'


 


(или sys.foreign_keys/ sys.foreign_key_columns) в базе имеются ограничения внешнего ключа.  Следовательно, первоначально следует вставлять данные в referenced_object (РК), а затем в parent_object (FK), чтобы избежать нарушений ограничений внешнего ключа. Возможны ситуации, когда referenced_object сам, в свою очередь, имеет referenced_object. Следовательно, требуется упорядочить таблицы, выбрав сначала те referenced_objects, которые не имеют FK-ограничений, вставить данные в них, затем в те таблицы, для которых они являются РК-таблицами и т.д. Чтобы не усложнять скрипт миграции, было принято решение на время переноса данных отключить все FK-ограничения, вставить данные, а затем снова включить. Отключение FK-ограничений выполняется при помощи команды ALTER TABLE <имя FK-таблицы> NOCHECK CONSTRAINT <имя ограничения>, а включение, соответственно, - CHECK. (От/в)ключение ограничений внешнего ключа делает процедура DisEnableFKConstraints(bool switchOff). В том, что FK-ограничения отключены, можно убедиться по запросу


select * from sys.foreign_keys


в результатах которого колонка is_disabled стала 1 для всех записей.


 


Перед загрузкой данных содержимое таблиц SQL Express следует очистить. Несмотря на отключенные ограничения чистить таблицы при помощи TRUNCATE TABLE не получится. Приходится использовать команду DELETE <имя таблицы> для удаления из каждой таблицы всех ее записей.


Можно видеть


select * from sys.columns where is_identity = 1


или select * from sys.identity_columns, что на некоторых таблицах имеются колонки с автоинкрементом. Однако специально отключать автоинкремент перед вставкой SET IDENTITY_INSERT <имя таблицы> ON | OFF не требуется, т.к. это "за сценой" делает объект SqlBulkCopy при помощи параметра KeepIdentity.


Последовательность действий выглядит следующим образом.



  • Открываются соединения с БД MySQL и SQL Express. MARS в SQL Serverном соединении потребовалось включить из-за процедуры DisEnableFKConstraints, где мы держим на соединении открытый DataReader со списком FK, по которому бежим, и на каждой записи выполняем ExecuteNonQuery() на том же соединении.
  • Отключаем все ограничения внешнего ключа в БД SQL Express, чтобы не заботиться о последовательности очистки и заливки.
  • Получаем список таблиц из БД MySQL. Он сохраняется в DataTable tblList.
  • Пробегаемся по этому списку и очищаем в нем все таблицы.
  • Пробегаемся по этому списку и переносим данные из каждой таблицы MySQL в одноименную таблицу SQL Express.

Из п.7 мы можем быть уверены, что каждая таблица в MySQL имеет соответствие в SQL Express и набор полей приемника тождественен источнику с точностью до порядка следования. Колонки, для которых возможны потери при копировании, перечислены в Скрипт  7.6.


 


Перед выполнением загрузки из MySQL на всякий случай лучше выполнить резервное копирование базы данных на SQL Express несмотря на то, что она пуста, т.е. содержит только "заводские" установки и весь наработанный контент хранится в базе MySQL. Резервное копирование базы данных SQL Server можно выполнить при помощи команды


 


backup database Bitrix to  disk = 'c:\Bitrix\bitrix.bak' with noformat, init, name = N'Bitrix-Full Database Backup', skip, stats = 10


 


Скрипт  8.3


 


а восстановление (при необходимости) -


alter database Bitrix set single_user with rollback immediate


use master


restore database Bitrix from disk = 'c:\Bitrix\Bitrix.bak' with recovery, stats = 20


 


Скрипт  8.4


 


 


9.     То же самое на PowerShell


 


Если у клиента нет Visual Studio и он по каким-либо причинам не может установить Express-редакцию, ниже приводится вариация Скрипта  8.2, мигрирующего базу Битрикс с MySQL на SQL Express, на языке сценариев PowerShell:


cls


 


function DisEnableFKConstraints([bool] $switchOff)


{


        [string] $prefix; if ($switchOff) { $prefix = "От" } else { $prefix = "В" };


        Write-Host ($prefix + "ключение FK-ограничений...")


            [System.Data.SqlClient.SqlDataReader] $sdr = (New-Object System.Data.SqlClient.SqlCommand("select name, object_name(parent_object_id) from sys.foreign_keys", $sqlSrvCnn)).ExecuteReader()


            while ($sdr.Read())


        {


            [string] $fkName = $sdr.GetString(0); [string] $tblName = $sdr.GetString(1)


                  [string] $prefix1 = ""; if ($switchOff) {$prefix1 = "no"}


                  [string] $cmdText = "alter table {0} {1}check constraint {2}" -f $tblName,  $prefix1, $fkName


            (New-Object System.Data.SqlClient.SqlCommand($cmdText, $sqlSrvCnn)).ExecuteNonQuery()


            Write-Host ("{0}ключено ограничение {1} в таблице {2}" -f $prefix, $fkName, $tblName)


        }


        $sdr.Close();


        Write-Host ($prefix + "ключение FK-ограничений завершено.")


}


 


function CleanDestTablesInSQLSrvDB([System.Data.DataTable] $tblList)


{


    Write-Host "Очистка таблиц назначения..."


    foreach ($r in $tblList.Rows)


    {


        [string] $cmdText = "delete " + $r[0]


            (New-Object System.Data.SqlClient.SqlCommand($cmdText, $sqlSrvCnn)).ExecuteNonQuery()


        Write-Host ("Очищена таблица " + $r[0])


    }


    Write-Host "Очистка закончена."


}


 


function TransferData([System.Data.DataTable] $tblList)


{


    Write-Host "Загрузка данных..."


    foreach ($r in $tblList.Rows)


    {


        CopyDataFromMySQLTblToCorrespondingSQLSrvTbl($r[0])


        Write-Host ("Перенесена таблица " + $r[0])


    }


    Write-Host "Загрузка завершена."


}


 


function CopyDataFromMySQLTblToCorrespondingSQLSrvTbl([string] $tblName)


{


    [System.Data.SqlClient.SqlCommand] $sqlSrvCmd = $sqlSrvCnn.CreateCommand()


      $sqlSrvCmd.CommandText = "select name from sys.columns where object_id = object_id(@tblName) order by column_id"


    $sqlSrvCmd.Parameters.AddWithValue("@tblName", $tblName)


      [System.Data.SqlClient.SqlDataReader] $sqlSrvRdr = $sqlSrvCmd.ExecuteReader()


    [System.Text.StringBuilder] $mySqlCmdText = New-Object System.Text.StringBuilder("select ")


    while ($sqlSrvRdr.Read()) { $mySqlCmdText.Append("``" + $sqlSrvRdr.GetSqlString(0) + "``,") }


    $sqlSrvRdr.Close()


    $mySqlCmdText.Remove($mySqlCmdText.Length - 1, 1)


    $mySqlCmdText.Append(" from " + $tblName)


      [MySql.Data.MySqlClient.MySqlCommand] $mySqlCmd = New-Object MySql.Data.MySqlClient.MySqlCommand($mySqlCmdText.ToString(), $mySqlCnn);


    [MySql.Data.MySqlClient.MySqlDataReader] $mySqlRdr = $mySqlCmd.ExecuteReader()


 


    [System.Data.SqlClient.SqlBulkCopy] $bcp = New-Object System.Data.SqlClient.SqlBulkCopy($sqlSrvCnn, [System.Data.SqlClient.SqlBulkCopyOptions]::KeepIdentity, $null)


    $bcp.DestinationTableName = $tblName


    $bcp.WriteToServer($mySqlRdr)


      $bcp.Close()


 


    $mySqlRdr.Close()


}


 


################################################################ MAIN ##############################################################################################################################


 


[System.Data.SqlClient.SqlConnection] $sqlSrvCnn = New-Object System.Data.SqlClient.SqlConnection("server=(local)\SQLExpress;database=bitrix;trusted_connection=true;MultipleActiveResultSets=true")


$sqlSrvCnn.Open()


[void][system.reflection.Assembly]::LoadWithPartialName("MySql.Data")


[MySql.Data.MySqlClient.MySqlConnection] $mySqlCnn = New-Object MySql.Data.MySqlClient.MySqlConnection("server=127.0.0.1;port=31006;uid=root;pwd=;database=bsm_demo;Pooling=False")


$mySqlCnn.Open()


 


$mySqlRdr = (New-Object MySql.Data.MySqlClient.MySqlCommand("show tables;", $mySqlCnn)).ExecuteReader()


[System.Data.DataTable] $tblList = New-Object System.Data.DataTable


$tblList.Load($mySqlRdr)


$mySqlRdr.Close()


 


DisEnableFKConstraints $true | Out-Null


CleanDestTablesInSQLSrvDB $tblList


TransferData $tblList | Out-Null


DisEnableFKConstraints $false | Out-Null


 


$sqlSrvCnn.Close()


$mySqlCnn.Close()


 


Скрипт  9.1


 


 


10.   Последовательность действий клиента


 


Клиент располагает MySQLным вариантом установки Битрикс. Для перехода с MySQL на SQL Express ему необходимо



  • Установить SQL Express, как показано в п.5.
  • Установить .NET Connector к MySQL, как показано на Рис. 8.1 - Рис. 8.3.
  • Переименовать каталог www в папке Bitrix Environment и установить Битрикс на SQL Express, как показано в п.6.


image017


Рис. 10.1



  • Закрыть окно Рис. 10.1. Остановить процесс Bitrix Environment:


image019


Рис. 10.2


Проверить, что MySQL по-прежнему запущен (mysqld-opt.exe значится в числе работающих процессов). Если нет, запустить, как показано в Скрипте 4.1.



  • Выполнить Скрипт  9.1:

image021 



Рис. 10.3



  • Вновь запустить Bitrix Environment:

 


image023



Рис. 10.4


Мы видим, что информация из MySQL перенеслась в SQL Express.


При возникновении непредвиденной ситуации вернуться на исходную позицию можно, переименовав переименованную папку обратно в www.


 


Skip to main content