MySQL Bugs: #4759: Parameter expanded without single quotes during Sql Server replication

Bug #4759	Parameter expanded without single quotes during Sql Server replication
Submitted:	26 Jul 2004 21:15	Modified:	28 Jul 2004 22:25
Reporter:	[ name withheld ]	Email Updates:
Status:	Won't fix	Impact on me:	None
Category:	Connector / ODBC	Severity:	S3 (Non-critical)
Version:	MyODBC standard-3.51.8-win	OS:	Windows (Windows XP Prof. 2002 SP1)
Assigned to:		CPU Architecture:	Any

Description:
Using Sql Server Developer Edition 8.00.760 (SP3), purpose is to implement replication to a MyODBC DSN (for a detailed description please see thread "Sql Server replication using MyODBC as a heterogenious subscriber", http://lists.mysql.com/myodbc/9221). The mysql server is version 4.0.18-log running on debian linux.

I created a MyODBC DSN named MyBorg3518. On Sql server, I set up a publication that includes one table, the Northwind Employees table. I then
created a heterogenious subscriber using MyBorg3518. I then pushed the
publication to the subscriber.

The initial snapshot of the publication is replicated to the MyODBC DSN flawlessly. I then use Sql Query Analyzer and issue the following sql statement:

update Northwind.dbo.Employees set lastname='Hardy' where firstname='Nancy'

In sql server the query is executed without error and the table is updated, but in the Sql Server Enterprise Manager "Replication Monitor" the following error occurs:

Unknown column 'Hardy' in 'field list'.

The mysql log on the server shows:

update `Employees` set `LastName` = Hardy where `EmployeeID` = 1

I assume that MyODBC should have placed quotes around Hardy to update the mysql table correctly.

How to repeat:
On Windows XP, install Sql server version above with example Northwind database. Also install MyODBC standard-3.51.8-win, being the latest production release to date.

On debian linux install mysql version 4.0.18-log and turn on text logging. Create a test database with a test user that can connect from a remote ip address and can drop and create tables, select and delete rows, etc.

On sql server create a MyODBC DSN to the mysql test database. In Sql Server create a publication with one article, the Northwind Employees table. Then create a heterogenious subscriber using the MyODBC DSN set up previously. Push the publication to the subscriber and wait for the following tables to be created on the mysql database, Employees and MSrepl7.

Then run Sql Query Analyzer and issue the following statement:

update Northwind.dbo.Employees set lastname='Hardy' where firstname='Nancy'

Then watch Sql Server Enterprise Manager and wait for the following error message to show up in Replication Monitor under Publishers:

Unknown column 'Hardy' in 'field list'

Tail the mysql server log file and search for the following entry:

update `Employees` set `LastName` = Hardy where `EmployeeID` = 1

Suggested fix:
Upon reading the source for function insert_param in execute.c of MyODBC v3.51.06 source, I assume that the ? in the prepared statements must be expanded to include single quotes.

Trace file from MyODBC v3.51.06 driver exhibiting same behavior

Attachment: myodbc.log (application/octet-stream, text), 199.45 KiB.

If you look at the trace file from the MyODBC v3.51.06 version, you'll see some ctypes and Sqltypes that have negative values. I believe Sql server is passing sqldmo values instead of odbc compliant values. Here's an excerpt from sqldmo.h which (I beleive) is distributed with Sql server products:

typedef SQLDMO_HELPID(SQLDMO_QUERY_DATATYPE) enum {
   // Indexed as per ..\common\inc\sql.hpp and sql.h sqlext.h datatype constants.
        SQLDMO_DTypeUnknown     = 0,
        SQLDMO_DTypeChar        = 1,                         // SQL_CHAR
        SQLDMO_DTypeText        = -1,                        // SQL_LONGVARCHAR
        SQLDMO_DTypeVarchar     = 12,                        // SQL_VARCHAR
        SQLDMO_DTypeVarBinary   = -3,                        // SQL_VARBINARY
        SQLDMO_DTypeBinary      = -2,                        // SQL_BINARY
        SQLDMO_DTypeImage       = -4,                        // SQL_LONGVARBINARY
        SQLDMO_DTypeFloat4      = 7,                         // SQL_REAL
        SQLDMO_DTypeFloat8      = 8,                         // SQL_DOUBLE
        SQLDMO_DTypeInt1        = -6,                        // SQL_TINYINT
        SQLDMO_DTypeInt2        = 5,                         // SQL_SMALLINT
        SQLDMO_DTypeInt4        = 4,                         // SQL_INTEGER
        SQLDMO_DTypeMoney4      = 3,                         // SQL_DECIMAL
        SQLDMO_DTypeMoney       = 3,                         // SQL_DECIMAL
        SQLDMO_DTypeDateTime    = -2,                        // SQL_BINARY
        SQLDMO_DTypeDateTime4   = 93,                        // SQL_TYPE_TIMESTAMP
        SQLDMO_DTypeBit         = -7,                        // SQL_BIT
        SQLDMO_DTypeUChar       = -8,                        // SQL_WCHAR
        SQLDMO_DTypeUVarchar    = -9,                        // SQL_WVARCHAR
        SQLDMO_DTypeGUID        = -11,                       // SQL_GUID
        SQLDMO_DTypeNText       = -10,                       // SQL_WLONGVARCHAR
        SQLDMO_DTypeBigint      = -5,                        // SQL_BIGINT
        SQLDMO_DTypeSQLVariant  = -150,                      // SQL_VARIANT
} SQLDMO_QUERY_DATATYPE;

Turns out I was trying to replicate a table that contained an nvarchar column (unicode). I assume this won't be supported until MyODBC v3.52.x

Hi, Jeffrey.  I'm sorry, but you're right - we can't fix Unicode-related problems in 3.51.

A new version, 3.53, is being developed now with full compliance with the ODBC spec as the target, and with support for all MySQL features.