Migrating from SQL Server to MariaDB / MySQL. What to do with XML
Colleagues, I want to offer you a description of the solutions that helped in the project for the migration of a large application from SQL Server to MariaDB / MySQL.
Perhaps this will help someone in a similar project.
MariaDb / MySQL functionality, let's say ... less than that of SQL Server, but in this project I did not have to deal with such aspects as:
I had to solve performance problems and look for the causes of deadlocks, with which the Percona Toolkit helped. As well as modify the DAC (Data Access Layer) so that the application works with SQL Server and MariaDb.
')
The biggest problem was the conversion of procedures simply because there were a lot of them. Nobody seriously considered the possibility of switching to NHybernate or another ORM, the procedures were full of logic and no one was going to transfer it to the business layer (you guessed correctly that the application was on .NET).
The principle feasibility of the project was based on the fact that the T-SQL syntax and the MariaDb / MySQL syntax are similar and there was a theoretical possibility of automation for converting procedures. At least for trivial CRUD cases.
I especially liked the fact that several result sets can be returned from the procedures in MariaDb / MySQL as well as in T-SQL. I was always tormented by why the developers of Oracle or PostgreSQL did not provide for such a simple feature. That is, a simple SELECT * FROM users statement at the end of the procedure leads to the fact that its result can be read in the DAC on the client side. Moreover, it can be any number of SELECT statements not redirected to a table (INSERT INTO ... SELECT) or to a variable (SELECT ... INTO ...) and even not necessarily at the end of the procedure.
Neither, of course, helped by the integration tests written earlier. If you do not have them, there is reason to think about writing them in the course of the project. The more DAC tests cover, the greater the likelihood that a project will end in luck.
By the way, MySQL and MariaDb can be installed on Windows, but it is better to start development right away on Linux. On Linux, the instrumental environment is better (like the same Percona Toolkit) and you will immediately encounter the problem of the correct interection for identifiers more precisely for table names. For example, on Linux, the SELECT * FROM users and SELECT * FROM Users query is not at all the same. Because the data tables are in the files, and on Unix / Linux file system case sense. And, not surprisingly, MariaDb inside Linux with Linux works faster than Windows as a “native” Windows service on the Windows host. Apparently, MySQL has nothing to do with this beautiful desktop operating system.
The XML application was used as follows:
The first problem is solved trivially and it is worth mentioning only to ensure that the audience does not have a gap pattern, if there is no XML data type, then where does it all fall into the procedure. The rest are worth more details.
In general, MariaDb / MySQL has a ExtractValue function that can perform XPath for an XML document passed to parameters as a BLOB and returns the result as text.
Another question is what to do if you were given an XML document in which there is a certain collection of values ​​and size, do you not know it? How to determine how many items are in a collection? And how to remove a certain element of the collection?
For counting elements in XPath, there is a count () function that can return the number of XML elements corresponding to a specific XPath expression. (http://www.w3schools.com/xpath/xpath_functions.asp)
Having determined the number of elements in the collection, we can write an XPath to get a specific element.
and a loop to get all the items in the collection.
Against expectations, it works very quickly, since the XML document is not parsed in each ExtractValue call. In the example, only one XML attribute of the id element is retrieved, but, of course, there can be any number of these attributes.
In order not to write the WHILE loop time after time in each procedure for each parameter with XML, you can write a procedure (s) that will create temporary tables and populate them with XML data. We did just that.
The negative side of such a solution is the overhead of serializing / deserializing the data collection in XML. You can prepare the data in a temporary table before calling the procedure and in the procedure it is easy to use. But then, looking at the procedure code, it will not be obvious where this or that temporary table came from. Readability will deteriorate.
How to replace such syntax in T-SQL?
(https://msdn.microsoft.com/ru-ru/library/ms178107.aspx)
As a result of the request, one XML document containing the following text is obtained:
That is, it is an aggregation of the result set and its transformation into one structured document.
Can this be repeated on MariaDb / MySQL?
It is possible, and all that is needed is the aggregate function GROUP_CONCAT. (https://mariadb.com/kb/en/mariadb/group_concat/)
It allows to concatenate the lines of the result set. Something like this:
The result is a single line of the form
But we can concatenate not just a value, but also expressions of the form calculated for each line:
For example:
That is what we got as a result? Correct - XML ​​document.
But like everywhere, there are definitely a couple of nuances:
It's about modifying XML documents stored in the fields of a table in a database.
Such a need arises, as a rule, if you have mistaken somewhere in the design of the data model and added some non-self-containing data to XML and then periodically there is a need to go through the stored XML documents and make changes to them.
But since the mistake is made, the question, as a rule, rests on the fact that it will cost to redo everything (so that it is right) and that it will cost to periodically bypass the XML and modify them according to certain rules.
In SQL Server, you can do it like this:
And oddly enough, though not quickly, but still it can be done in MariaDb. For this there is a function UpdateXml .
In addition, we need the ExtractValue function to find the desired rows in the table. In order for this to work in a reasonable time, you must first find them and put them in a temporary table:
And then we make an update:
In general, it's all about XML in MariaDB.
If this article seems useful to someone, it will be possible to continue the description of other problems and solutions related to the migration to MariaDb from SQL Server.
Perhaps this will help someone in a similar project.
What we did NOT do
MariaDb / MySQL functionality, let's say ... less than that of SQL Server, but in this project I did not have to deal with such aspects as:
- Migration of analytics built on top of SSAS;
- or ETL processes using SSIS;
- Full text search has already been done on Apache SOLR (as it should be done).
What we did
- There was a scheme on 150 tables and ~ 20 I twist;
- > 900 procedures;
- recursive CTE (Recursive Common Table Expressions);
- working with XML;
- table valued parameters;
- dynamic SQL;
- analytical functions (for example, ROW_NUMBER () OVER ()) that are not in MariaDb, but sometimes you can still do something ...;
- and of course UNIQIDENTIFIER as a clustered index on a significant part of the tables (and why are some developers so fond of this type of data?
I had to solve performance problems and look for the causes of deadlocks, with which the Percona Toolkit helped. As well as modify the DAC (Data Access Layer) so that the application works with SQL Server and MariaDb.
')
Main problems
The biggest problem was the conversion of procedures simply because there were a lot of them. Nobody seriously considered the possibility of switching to NHybernate or another ORM, the procedures were full of logic and no one was going to transfer it to the business layer (you guessed correctly that the application was on .NET).
What was good
The principle feasibility of the project was based on the fact that the T-SQL syntax and the MariaDb / MySQL syntax are similar and there was a theoretical possibility of automation for converting procedures. At least for trivial CRUD cases.
I especially liked the fact that several result sets can be returned from the procedures in MariaDb / MySQL as well as in T-SQL. I was always tormented by why the developers of Oracle or PostgreSQL did not provide for such a simple feature. That is, a simple SELECT * FROM users statement at the end of the procedure leads to the fact that its result can be read in the DAC on the client side. Moreover, it can be any number of SELECT statements not redirected to a table (INSERT INTO ... SELECT) or to a variable (SELECT ... INTO ...) and even not necessarily at the end of the procedure.
Neither, of course, helped by the integration tests written earlier. If you do not have them, there is reason to think about writing them in the course of the project. The more DAC tests cover, the greater the likelihood that a project will end in luck.
By the way, MySQL and MariaDb can be installed on Windows, but it is better to start development right away on Linux. On Linux, the instrumental environment is better (like the same Percona Toolkit) and you will immediately encounter the problem of the correct interection for identifiers more precisely for table names. For example, on Linux, the SELECT * FROM users and SELECT * FROM Users query is not at all the same. Because the data tables are in the files, and on Unix / Linux file system case sense. And, not surprisingly, MariaDb inside Linux with Linux works faster than Windows as a “native” Windows service on the Windows host. Apparently, MySQL has nothing to do with this beautiful desktop operating system.
Let's start with the simplest - what to do with XML?
The XML application was used as follows:
- Passed as procedure parameters and returned to result sets. The solution is simple; we use the LONGTEXT data type instead of XML, everything works fine with the Oracle MySQL NET Connector.
- Retrieved from the XML parameters of the data collection. This was the approach in the code and it was used along with the coma separated lists and table valued parameters (https://msdn.microsoft.com/en-us/library/bb510489.aspx) where as, the product history was long ...
- We used constructions like SELECT ... FOR XML
- Changed XML in the fields of the tables directly in the procedure. Constructions like –UPDATE table1 SET field1.modify (...)
The first problem is solved trivially and it is worth mentioning only to ensure that the audience does not have a gap pattern, if there is no XML data type, then where does it all fall into the procedure. The rest are worth more details.
Collections of data in XML parameters
In general, MariaDb / MySQL has a ExtractValue function that can perform XPath for an XML document passed to parameters as a BLOB and returns the result as text.
SELECT ExtractValue ('<a> <b> Brown Seal </ b> </a>', '/ a / b / text ()') Another question is what to do if you were given an XML document in which there is a certain collection of values ​​and size, do you not know it? How to determine how many items are in a collection? And how to remove a certain element of the collection?
For counting elements in XPath, there is a count () function that can return the number of XML elements corresponding to a specific XPath expression. (http://www.w3schools.com/xpath/xpath_functions.asp)
SELECT ExtractValue ('<a> <b> Brown </ b> <b> Seal </ b> </a>', 'count (/ a / b)') Having determined the number of elements in the collection, we can write an XPath to get a specific element.
SELECT ExtractValue ('<a> <b> Brown </ b> <b> Seal </ b> </a>', '/ a / b [1] / text ()') and a loop to get all the items in the collection.
CREATE PROCEDURE `sproc1` (p1 LONGTEXT)
BEGIN
...
SET vCount = ExtractValue (p1, 'count (/ ids / id)');
WHILE vCount> 0 DO
INSERT INTO __temptable1__ (att1)
VALUES (ExtractValue (p1, CONCAT ('/ ids / id [', CAST (vCount AS CHAR), '] / @ att1')));
SET vCount = vCount - 1;
END WHILE;
...
Against expectations, it works very quickly, since the XML document is not parsed in each ExtractValue call. In the example, only one XML attribute of the id element is retrieved, but, of course, there can be any number of these attributes.
In order not to write the WHILE loop time after time in each procedure for each parameter with XML, you can write a procedure (s) that will create temporary tables and populate them with XML data. We did just that.
The negative side of such a solution is the overhead of serializing / deserializing the data collection in XML. You can prepare the data in a temporary table before calling the procedure and in the procedure it is easy to use. But then, looking at the procedure code, it will not be obvious where this or that temporary table came from. Readability will deteriorate.
SELECT ... FOR XML
How to replace such syntax in T-SQL?
SELECT [PropertyName] AS [@Name], [PropertyValue] AS [@Value] FROM [dbo] .props1 AS [Property] WHERE ... FOR XML PATH (N'Property '), ROOT (N'Properties')
(https://msdn.microsoft.com/ru-ru/library/ms178107.aspx)
As a result of the request, one XML document containing the following text is obtained:
<Properties> <Property Name = ”abc” Value = ”def” /> ... </ Properties>
That is, it is an aggregation of the result set and its transformation into one structured document.
Can this be repeated on MariaDb / MySQL?
It is possible, and all that is needed is the aggregate function GROUP_CONCAT. (https://mariadb.com/kb/en/mariadb/group_concat/)
It allows to concatenate the lines of the result set. Something like this:
SELECT GROUP_CONCAT (student_name) FROM student.
The result is a single line of the form
“Vasya Petya Kohl ...“
But we can concatenate not just a value, but also expressions of the form calculated for each line:
CONCAT ('<user name = "', u.User, '" host = "', u.Host, '" />') For example:
SELECT CONCAT ('<users>',
GROUP_CONCAT (
CONCAT ('<user name = "', u.User, '" host = "', u.Host, '" />')
SEPARATOR ''), '</ users>') FROM user u
That is what we got as a result? Correct - XML ​​document.
But like everywhere, there are definitely a couple of nuances:
- First, GROUP_CONCAT () defaults to making lines longer than 1024 characters, but this is regulated by the parameter: group_concat_max_len. mariadb.com/kb/en/mariadb/server-system-variables/#group_concat_max_len . It is quite safe to increase it to 1024 * 1024 (that is, to 1Mb)
- Secondly, text fields can contain & <> ”'symbol which can make your XML invalid. They need to be exported. For example:
REPLACE (REPLACE (REPLACE (REPLACE (REPLACE (tagvalue, '&', '& amp;'), '<', '& lt;'), '>', '& gt;'), '"', '& quot;' ), '\' ',' & apos; ');
But, of course, it’s better to write a function likeCREATE FUNCTION `xml_escape` (tags value VARCHAR (2000)) RETURNS varchar (2000).
And everywhere to use it where necessary.
Modification of XML documents in the database
It's about modifying XML documents stored in the fields of a table in a database.
Such a need arises, as a rule, if you have mistaken somewhere in the design of the data model and added some non-self-containing data to XML and then periodically there is a need to go through the stored XML documents and make changes to them.
But since the mistake is made, the question, as a rule, rests on the fact that it will cost to redo everything (so that it is right) and that it will cost to periodically bypass the XML and modify them according to certain rules.
In SQL Server, you can do it like this:
UPDATE user_profile up
SET Fields.modify ('replace value of (/ fields / field [@ key = sql: variable ("@ Name")] / text ()) [1] with sql: variable ("@ Value")')
WHERE Fields.value ('data ((/ fields / field [@ key = sql: variable ("@ Name")] / text ()) [1])', 'nvarchar (256)') = @OldValue
And oddly enough, though not quickly, but still it can be done in MariaDb. For this there is a function UpdateXml .
In addition, we need the ExtractValue function to find the desired rows in the table. In order for this to work in a reasonable time, you must first find them and put them in a temporary table:
INSERT INTO __ProfilesToUpdate__ (id)
SELECT id FROM user_profile up
WHERE ExtractValue (up.`Fields`, vValueXPath) = vOldValue;
Where SET vValueXPath = CONCAT ('/ fields / field [@key = "', XML_ESCAPE (vName), '"] [1] / text ()');
And then we make an update:
UPDATE user_profile up
SET up.`Fields` = UpdateXML (up.`Fields`, vReplacementXPath, vReplacementXml)
WHERE id IN (SELECT id FROM __ProfilesToUpdate__);
Where SET vReplacementXPath = CONCAT ('/ fields / field [@key = "', XML_ESCAPE (vFieldName), '"] [1]');
and SET vReplacementXml = CONCAT ('<field key = "', XML_ESCAPE (vFieldName), '">', XML_ESCAPE (pValue), '</ field>');
In general, it's all about XML in MariaDB.
If this article seems useful to someone, it will be possible to continue the description of other problems and solutions related to the migration to MariaDb from SQL Server.
Source: https://habr.com/ru/post/249259/
All Articles