MySQL Bugs: #46162: insert into decimal type column from sql server is 100x; update is x.

Bug #46162	insert into decimal type column from sql server is 100x; update is x.
Submitted:	14 Jul 2009 8:40	Modified:	17 Jul 2009 20:16
Reporter:	eric williams	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	Connector / ODBC	Severity:	S2 (Serious)
Version:	Connector 5.1.5 x64, server: 5.0.51a-15	OS:	Windows (2003 server 64bit)
Assigned to:		CPU Architecture:	Any

Description:
i'm using the mysql odbc connector to link to sql server 2005. when i run an insert, the value is multiplied by 100 (for some reason the driver seems to be stripping out the decimal point, i guess?

#mysql table
CREATE TABLE promo
(
	id BIGINT AUTO_INCREMENT
,	discount decimal(20, 2)
)
COLLATE cp1250_general_ci
ENGINE = INNODB;

#sql command (via mysql ODBC linked server) - tried all of these
insert into mysql_dash_dev...promo (discount) values (40)
insert into mysql_dash_dev...promo (discount) values (40.00)
insert into mysql_dash_dev...promo (discount) values ('40')
insert into mysql_dash_dev...promo (discount) values ('40.00')
# all result in output below:

# myodbc.sql:
select * from promo;
INSERT INTO `promo`(`id`,`discount`) VALUES (DEFAULT,4000);
COMMIT;
####
# but update doesnt have the same issue:
update mysql_dash_dev...promo set discount = 40 where discount = 4000

#myodbc.sql
select * from promo;
UPDATE `dash`.`promo` SET `discount`=40.00 WHERE `id`=12 AND `discount`=4000.00;
COMMIT;

How to repeat:
1) create mysql table with decimal (x, 2) column
2) install mysql/ODBC connector 5.1
3) install sql server 2005 (express edition should work fine) - note, i'm not sure if this issue is confined to sql server 2005...
3) set up an ODBC connection for your mysql instance
4) run the following script to create a linked server between sql server 2005 and your mysql instance (filling in your odbc name on line 2)

declare @odbc nvarchar(50)
set @odbc = 'mysql' --your ODBC name here

-- drop and recreate, if already present
if exists (select * from sys.servers where name = @odbc)
exec master.dbo.sp_dropserver @server = @odbc

EXEC master.dbo.sp_addlinkedserver
  @server = @odbc
,	@srvproduct = @odbc
,	@provider = N'MSDASQL'
,	@datasrc = '@odbc

--this sets the sql server options for the linked server. none of these options has any effect on the outcome
EXEC master.dbo.sp_serveroption @server = @this_name, @optname = N'collation compatible', @optvalue = N'false'
EXEC master.dbo.sp_serveroption @server = @this_name, @optname = N'data access', @optvalue = N'true'
EXEC master.dbo.sp_serveroption @server = @this_name, @optname = N'dist', @optvalue = N'false'
EXEC master.dbo.sp_serveroption @server = @this_name, @optname = N'pub', @optvalue = N'false'
EXEC master.dbo.sp_serveroption @server = @this_name, @optname = N'rpc', @optvalue = N'false'
EXEC master.dbo.sp_serveroption @server = @this_name, @optname = N'rpc out', @optvalue = N'false'
EXEC master.dbo.sp_serveroption @server = @this_name, @optname = N'sub', @optvalue = N'false'
EXEC master.dbo.sp_serveroption @server = @this_name, @optname = N'lazy schema validation', @optvalue = N'false'
EXEC master.dbo.sp_serveroption @server = @this_name, @optname = N'query timeout', @optvalue = N'0'
EXEC master.dbo.sp_serveroption @server = @this_name, @optname = N'use remote collation', @optvalue = N'true'

5) run the sql statements in the description on this setup, to reproduce. i've reproduced this on 2 separate machines

i should add that i have the following ODBC options set:
Flags 1: allow big result sets, use compression
Flags 2: dont cache results of forward-only cursors
Flags 3: read options from my.cnf, allow multiple statements

i've tried every combination of these ODBC settings, but none seem to affect the bug, however.

here is some additional testing to hopefully save some time, and show the omitting of the decimal point:

create table testing
(
	foo decimal(20, 2)
,	bar decimal(10, 0)
,	baz decimal(10, 2)
,	wee decimal(20, 1)
);

#then from sql server
insert into mysql_dash_dev...testing (foo, bar, baz, wee) values (25, 25, 25, 25)
insert into mysql_dash_dev...testing (foo, bar, baz, wee) values (25.34, 25.34, 25.34, 25.34)

#results:
foo	bar  baz	wee
2500.00	25   2500.00	250.0
2534.00	25   2534.00	253.0

the same results came up for, e.g., '25.00', or '25', on the insert.

I have the same issue. Microsoft SQL Server 2005 on Windows Server 2003 R2 x64, Connector/ODBC 5.1.5, MySQL 5.0.51a.

When inserting data into DECIMAL columns, the decimal separator is moved to the right as if all the digits of the value inserted formed just the integral part of the number, regardless of the position of the decimal separator in the value passed to INSERT.

For example, inserting 1234, 1234.56 and 1234.5678 into a DECIMAL(14,4) column results in values 12340000.0000, 12345600.0000 and 12345678.0000 respectively being stored in MySQL.

Values INSERTed directly into MySQL are returned correctly when SELECTing from MS SQL.

Hi Eric and thanks for your report.

Eric, Peter, if I read this correctly, the failing scenario is when MSSQL is inserting MSSQL DECIMAL field into MySQL via c/ODBC, right? If so, I would like to see the actual command MSSQL is sending c/ODBC for this job. I'd prefer some MSSQL log but if not possible then please attach ODBC DM trace file, we'll try figuring out something.
Do your boxes have comma separator or decimal point separator? How do you control that in MSSQL?

Hi Tonci,

no, this isn't quite correct. the decimal field is in mysql (that's where the table is being created), from mssql i'm just passing a hard-coded value in various formats (string, int, decimal, etc). when inserting into mysql via c/odbc, mysql is converting this value, regardless of format, with the behavior described.

I tried to do an ODBC trace, but i'm getting no written logfile for some reason. i just ran a trace from mssql, and here are the results:

Time Status	Duration (ms)	CPU Time (ms)	Reads	Writes	Command
7/15/2009 6:50 PM	Trace Started					
7/15/2009 6:50 PM	StatementCompleted	0	0	0	0	SELECT DB_NAME()
7/15/2009 6:50 PM	StatementCompleted	2929	0	2	0	insert into mysql_dash_dev...testing (foo, bar, baz, wee) values (25, 25, 25, 25)
7/15/2009 6:50 PM	StatementCompleted	2929	0	2	0	insert into mysql_dash_dev...testing (foo, bar, baz, wee) values (25.34, 25.34, 25.34,25.34)
7/15/2009 6:50 PM	Trace Stopped

so it seems that the issue isn't with the sql client i'm using to run the command, at least. maybe Peter will have better luck with an ODBC trace? i'll keep trying.

thanks for your time, Tonci!

ok, oddly, when i click "test" on my odbc connection, the odbc trace is written correctly (the built-in MS trace, using the "tracing" tab in "ODBC data source administrator"). however, it doesnt seem to receive data when i run sql commands. i know that these are being passed through the c/odbc mysql driver though, because myodbc.sql is written when i run commands and have tracing enabled on the c/odbc side.

Eric, that particular tracing is what goes through driver manager (DM). Leave it on *before* you start any app that uses ODBC and attach trace. It will tell us what goes into c/ODBC and c/ODBC query log (or MySQL server general query log) will tell us what actually got to MySQL server. So please attach at least one of them if not both.

My question regarding "decimal separator" used on your boxes is still unanswered.

Guys, this should be duplicate of Bug#39961 which is fixed in source trees. Can you just please confirm you're using server-side cursors (client-side cursors are not affected)?

Tonci, to be honest, I have no idea. I'm no SQL Server and/or ODBC expert. I'm running the single query, standalone, from the SQL Server Management Studio.

I wasn't able to successfully capture the query in the ODBC trace. However, when I created another linked server using a system DSN and enabled tracing in the driver configuration, this popped up in the myodbc.sql log file:

-- Query logging
--
--  Driver name: MySQL ODBC 5.1 Driver  Version: 05.01.0005
-- Timestamp: 090716 16:57:54

select * from decimal_test;
INSERT INTO `decimal_test`(`id`,`price`) VALUES (DEFAULT,1234500);
COMMIT;
select * from decimal_test;

The original query entered in the SQL Server Management Studio was:
INSERT INTO sync_test_dsn...decimal_test (price) VALUES (123.45);

Regional and Language Options | Standards and Formats is set to "Slovak", so my system uses comma as the decimal separator. Switching to "English (United States)" and restarting the SQL Server made no difference. Moreover, AFAIK it's not possible to use anything else than decimal point in SQL Server, the decimal separator is not locale-aware. (But I might be wrong.) Decimal attributes stored in both MS SQL and MySQL are returned with decimal point on my system.

When I run the same query with the price attribute being of the VARCHAR type, this ends up in the myodbc.sql log file:

-- Query logging
--
--  Driver name: MySQL ODBC 5.1 Driver  Version: 05.01.0005
-- Timestamp: 090716 17:18:15

select * from decimal_to_varchar_test limit 1;
select * from decimal_to_varchar_test;
INSERT INTO `decimal_to_varchar_test`(`id`,`price_varchar`) VALUES (DEFAULT,'123.45');
COMMIT;

Are there any nightly binaries of Connector/ODBC available for testing?

Hi Peter,
The snapshot build is available at:
http://downloads.mysql.com/snapshots.php

You can backup your existing driver DLL and replace it with this one. I am almost certain this will solve your problem.

yes, these are server-side cursors. my decimal separator is period (.).

i installed the latest snapshot on the server, i still see the same behavior, unfortunately. also, just like Peter, regardless of when i begin the DM trace, i can't seem to get it to log...

i installed a vanilla ODBC query tool on my sql server and queried via the same connection, but in this case, the data was inserted correctly (at least, with the latest). this probably means that the error is occurring in the layer before ODBC (i believe this is MSDASQL, what microsoft uses to connect ODBC to OLEDB). i've found no known way to trace or log this, unfortunately.

Jess, thanks for the link. I really couldn't find it.

In my case (standalone query ran from SQL Server Management Studio), the issue disappeared using the snapshot version on a 32bit test server. Got less luck on the 64bit production server. It's still using the old stable ODBC driver probably. I'll report later when I'll be able to restart the server.

Based on Peters experience and the fact that we were convinced where the problem lies, I declare this report to be a duplicate of Bug#39961.

Please see snapshots page for new version of driver or use client-side cursor.

still an issue on my 64 bit system, if Peter still has an issue, maybe this fix is only present in 32 bit?

i didnt think a reboot was necessary, as i overwrote the dll's (Actually did a complete install). however, i'll reboot the server later tonight also, and post a response. interested to see what Peter's response is from the 64bit version.

In the end, I found out that the customer is actually running 32bit SQL Server on 64bit Windows. Manually installing the 32bit development snapshot into sysWOW64 resolved my issues.

Eric, maybe you could have the same issue? Is your SQL Server x64?

We have both a 64 bit and a 32 bit copy of SQL Server 2005 running on corresponding 32 & 64 bit servers. We are currently getting the same decimal bug on both of our systems. I tried copying the 32 bit dlls into syswow64 on our 64 bit server but that didn't change the results any.

Brian,
MySQL Connector/ODBC 5.1 installs the 32-bit driver on a 64-bit system to "C:\Program Files (x86)\MySQL\Connector ODBC 5.1". Check in this directory for the DLLs you need to overwrite.

I've overwritten the DLLs, reregistered them, restarted my SQL service and the problem still persists. 

We are also experiencing this same issue on our secondary SQL Server which is 32bit Windows running 32bit SQL Server 2005.

Brian,
just to be doublesure, did you verify in both the ODBC managers, both in \Windows\system32\odbcad32.exe and in \Windows\SysWOW64\odbcad32.exe, that the version stated for the MySQL ODBC driver is 5.01.06? I can't talk about 64bit programs, but on my system, the development snapshot did solve the problem with the 32bit program.

After copying the 32-bit snapshot onto our 32-bit server, and the 64-bit snapshot to our 64-bit server, and verifying the version number in both odbcad32.exe programs, this seems to be working fine. Not sure why this didn't work last time I tried it.

Thanks for all the help everyone has provided,
Brian

Brian, welcome to the world of connectors :-)

> Not sure why this didn't work last time I tried it.

That's what I'm wondering most of my time... There's just too many variables involved.