Faced issue in executing script during update row

[sabir-piludiya]

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)

[TroyWitthoeft]

That SQL statement

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

has got QUITE a few special characters, literals, escapes, etc .... wow.
The error message received from mysql seems to hint at some invalid syntax.

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

That is some complicated syntax, and this is a syntax error. I understand it may work in the cli, but DbUp is going to have to do some parsing of your command. With that many special characters, I'm not surprised that it stumbled a little. Is it possible for you to rewrite the statement? If not, is it possible that you can capture the statement issued by DbUp BEFORE it hits the database and compare it to the one you are running in the cli? Are they different?