Is there an existing issue for this?

• I have searched the existing open and closed issues

Description

MySql.Data is no longer compatible with MariaDB and fails when using MariaDB version 10+. Research indicates that switching from MySql.Data to MySqlConnector will resolve this.

What is the impact?

dbup using the dbup-mysql reference fails to execute against an MariaDB version 10+.

When using dbup v4 if a statement contains the word 'delimiter' then the word and the following word are dropped from the statement. This causes a problem if the word occurs in a string that's being inserted or updated for example. See the unit test below. This was not an issue in v3.3.5.

Expecting 'Some text with delimiter in it' to be written to the database but what actually gets written is 'Some text with it'.

        [Fact]
        public void DoesNotParseOutTheWordDelimiterWhenItsInAString()
        {
            var multiCommand = "USE `test`;";
            multiCommand += "CREATE TABLE IF NOT EXISTS 'FOO';";
            multiCommand += Environment.NewLine;
            multiCommand += "DELIMITER $$";
            multiCommand += Environment.NewLine;
            multiCommand += "CREATE TABLE 'ZIP'$$";
            multiCommand += Environment.NewLine;
            multiCommand += "CREATE TABLE IF NOT EXISTS 'BAR';";
            multiCommand += Environment.NewLine;
            multiCommand += "INSERT INTO mytable(description) VALUES ('Some text with delimiter in it');";

            var connectionManager = new MySqlConnectionManager("connectionstring");
            var result = connectionManager.SplitScriptIntoCommands(multiCommand);

            var enumerable = result as string[] ?? result.ToArray();
            enumerable.Length.ShouldBe(4);
            enumerable[0].IndexOf("DELIMITER", StringComparison.Ordinal).ShouldBe(-1);
            enumerable[1].IndexOf("DELIMITER", StringComparison.Ordinal).ShouldBe(-1);
            enumerable[2].IndexOf("DELIMITER", StringComparison.Ordinal).ShouldBe(-1);
            enumerable[3].IndexOf("DELIMITER", StringComparison.OrdinalIgnoreCase).ShouldBeGreaterThan(-1);
        }

I defined my connection string using 'initial catalogue' instead of 'database'. However, this line of code then does not work correctly:
https://github.com/DbUp/DbUp/blob/master/src/dbup-mysql/MySqlExtensions.cs#L27

I suggesting using the MySqlConnectionStringBuilder to parse the connection string.

Fails to retrieve the schema name from the connection string when there is an extra space in the connection string - From Azure Portal the Connection strings have an extra space among the parts.

Potential fix is in the foreach loop where the token is trimmed any extra spaces.

File: src\dbup-mysql\MySqlExtensions.cs

public static class MySqlExtensions
{
    /// <summary>
    /// Creates an upgrader for MySql databases.
    /// </summary>
    /// <param name="supported">Fluent helper type.</param>
    /// <param name="connectionString">MySql database connection string.</param>
    /// <returns>
    /// A builder for a database upgrader designed for MySql databases.
    /// </returns>
    public static UpgradeEngineBuilder MySqlDatabase(this SupportedDatabases supported, string connectionString)
    {
        foreach (var pair in connectionString.Split(';').Select(s => s.Split('=')).Where(pair => pair.Length == 2).Where(pair => pair[0].Trim().ToLower() == "database"))
        {
            return MySqlDatabase(new MySqlConnectionManager(connectionString), pair[1]);
        }

        return MySqlDatabase(new MySqlConnectionManager(connectionString));
    }

Afternoon,

I'm trying to use DbUp to create a new Store Proc and I'm getting a syntax error when execute PerformUpgrade... My script works directly on a query window on the MySQL database.

here is the query

DROP PROCEDURE IF EXISTS My_StoreProc;

CREATE PROCEDURE My_StoreProc;(strEmail VARCHAR(255))

BEGIN
SELECT id FROM table WHERE email = strEmail
ORDER BY id DESC limit 1;
END;

Here is the error:
Script block number: 1; Message: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'END' at line 1
MySql.Data.MySqlClient.MySqlException (0x80004005): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'END' at line 1
at MySql.Data.MySqlClient.MySqlStream.ReadPacket()
at MySql.Data.MySqlClient.NativeDriver.GetResult(Int32& affectedRow, Int64& insertedId)
at MySql.Data.MySqlClient.Driver.GetResult(Int32 statementId, Int32& affectedRows, Int64& insertedId)
at MySql.Data.MySqlClient.Driver.NextResult(Int32 statementId, Boolean force)
at MySql.Data.MySqlClient.MySqlDataReader.NextResult()
at MySql.Data.MySqlClient.MySqlCommand.ExecuteReader(CommandBehavior behavior)
at MySql.Data.MySqlClient.MySqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader()
at DbUp.Support.ScriptExecutor.ExecuteAndLogOutput(IDbCommand command)
at DbUp.Support.ScriptExecutor.<>c__DisplayClass18_2.b__1()
at DbUp.MySql.MySqlScriptExecutor.ExecuteCommandsWithinExceptionHandler(Int32 index, SqlScript script, Action executeCommand)

Any suggestions, is there a work arround, is this a know bug?

Please fix this security vulnerability.

Hi, I'm not able to execute the script through DbUp during one of the update rows in script. However it's executed fine from mysql-cli.

Here is the update command within sql script:

UPDATE `block` SET `name`='from enum import Enum\nimport json\n\nhost_url = \"http://abcURL:80/\"\nservice_url = \"http://e..............';

Here is the error I got:

Checking whether journal table exists..
MySql exception has occured in script: 'Script.sql'
Script block number: 1; MySql error code: 0; Number 1064; Message: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''from enum import Enum\nimport json\n\nhost_url = \"http://abcURL:80/\"\nse' at line 1
MySql.Data.MySqlClient.MySqlException (0x80004005): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''from enum import Enum\nimport json\n\nhost_url = \"http://abcURL:80/\"\nse' at line 1
   at MySql.Data.MySqlClient.MySqlStream.ReadPacket()
   at MySql.Data.MySqlClient.NativeDriver.GetResult(Int32& affectedRow, Int64& insertedId)
   at MySql.Data.MySqlClient.Driver.GetResult(Int32 statementId, Int32& affectedRows, Int64& insertedId)
   at MySql.Data.MySqlClient.Driver.NextResult(Int32 statementId, Boolean force)
   at MySql.Data.MySqlClient.MySqlDataReader.NextResult()
   at MySql.Data.MySqlClient.MySqlCommand.ExecuteReader(CommandBehavior behavior)
   at MySql.Data.MySqlClient.MySqlCommand.ExecuteReader()
   at MySql.Data.MySqlClient.MySqlCommand.ExecuteNonQuery()
   at DbUp.Support.ScriptExecutor.ExecuteNonQuery(IDbCommand command)
   at DbUp.Support.ScriptExecutor.<>c__DisplayClass18_2.<Execute>b__1()
   at DbUp.MySql.MySqlScriptExecutor.ExecuteCommandsWithinExceptionHandler(Int32 index, SqlScript script, Action executeCommand)

The following script is failing when running in mySQL.

DROP TABLE IF EXISTS test;

CREATE TABLE test (
id int DEFAULT NULL,
message varchar(100) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

/*!40000 ALTER TABLE test DISABLE KEYS /;
INSERT INTO test VALUES
(1,'CCP\'s valuation') ;
INSERT INTO test VALUES
(2,'UTI = ;') ;
/!40000 ALTER TABLE test ENABLE KEYS */;

The following script runs successfully. Only change is removing the "\'" and having "CCPs" instead of "CCP\'s".
DROP TABLE IF EXISTS test;

CREATE TABLE test (
id int DEFAULT NULL,
message varchar(100) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

/*!40000 ALTER TABLE test DISABLE KEYS /;
INSERT INTO test VALUES
(1,'CCPs valuation') ;
INSERT INTO test VALUES
(2,'UTI = ;') ;
/!40000 ALTER TABLE test ENABLE KEYS */;

The error of the failing script is:
error.txt

this is some kind of relation between the quoted "\'" and the ";"

Appreciate if you can run this script and see it is failing since it is 3rd time I am opening this bug and getting irrelevant answers