Insert Into MySQL over linked server when a medium text is the last column breaks Int fields

Question

I have a table with

ID INT(16)
StateName VARCHAR(50)
Details MEDIUMTEXT

When I do an insert over the linked server connection:

Insert Into MySQL...tblState (ID, StateName, Details) Values (1,'Alabama','Test')

The row ID is set to 0 instead of 1.
If I flip the table structure to put the Details before the Statename, so that the MEDIUMTEXT is not the last column, and do the same insert statement.

Insert Into MySQL...tblState (ID, Details, StateName) Values (1,'Test','Alabama')

Then the insert works exactly like it's supposed to.

Is this a known bug, or is there a setting that I need to change to fix this?

I'm using the MySQL ODBC 5.1 Driver

Edit: In case this helps anyone, here are the results of a test I did.
In phpmyadmin:

CREATE TABLE `tblState` (
  `StateID` int(11) NOT NULL,
  `State` varchar(50) NOT NULL,
  `Details` mediumtext NOT NULL,
  PRIMARY KEY (`StateID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1


CREATE TABLE `tblState2` (
  `StateID` int(11) NOT NULL,
  `Details` mediumtext NOT NULL,
  `State` varchar(50) NOT NULL,
  PRIMARY KEY (`StateID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1

From SQL Server over linked server:

insert into mysql...tblState(StateID,State,Details) values (1,'alabama','test1')

(1 row(s) affected)


select * from mysql...tblState

StateID     State                Details
----------- -------------------- -------------------- 
0           alabama              test1

(1 row(s) affected)



insert into mysql...tblState2(StateID,Details,State) values (1,'test2','alabama' )

(1 row(s) affected)


select * from mysql...tblState2

StateID     Details              State                                              
----------- -------------------- -------------------- 
1           test2                alabama

(1 row(s) affected)

Answer 1

I'm going to go out on a limb and say that there's probably a bug somewhere - especially given the mediumtext data type. Here's a little work I did, obviously there're a few additional tests that you can run. Here is a SQLFiddle for your reference.

DDL

CREATE TABLE `tblState` (
  `StateID` int(11) NOT NULL,
  `State` varchar(50) NOT NULL,
  `Details` mediumtext NOT NULL,
  PRIMARY KEY (`StateID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;


CREATE TABLE `tblState2` (
  `StateID` int(11) NOT NULL,
  `Details` mediumtext NOT NULL,
  `State` varchar(50) NOT NULL,
  PRIMARY KEY (`StateID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

CREATE TABLE `tblState3` (
  `StateID` int(11) NOT NULL,  
  `State` varchar(50) NOT NULL,
  `Details` mediumtext NOT NULL,
  PRIMARY KEY (`StateID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

DML

INSERT INTO tblState(StateID,State,Details) values (1,'alabama','test1');
INSERT INTO tblState2(StateID,Details,State) values (1,'test2','alabama');
INSERT INTO tblState3(StateID,Details,State) values (1,'test2','alabama');

INSERT INTO tblState(StateID,Details,State) VALUES (2,'It is nice in','Colorado');
INSERT INTO tblState2(StateID,State,Details) 
    VALUES (3,'South Dakota','Is actually very nice');
INSERT INTO tblState3(StateID,State,Details) 
    VALUES (3,'South Dakota','Is actually very nice');


INSERT INTO tblState(Details, State, StateID) 
    VALUES ('It never rains in southern','California',7);
INSERT INTO tblState2(Details,StateID,State) 
    VALUES ('It used to be so hard to spell, it used to make me cry',5,'Mississippi');
INSERT INTO tblState3(Details,StateID,State) 
    VALUES ('It used to be so hard to spell, it used to make me cry',5,'Mississippi');

select * from tblState;

renders:

STATEID     STATE       DETAILS
--------------------------------------
1           alabama     test1
2           Colorado    It is nice in
7           California  It never rains in southern

while

select * from tblState2;

renders:

STATEID     DETAILS                                                 STATE
--------------------------------------------------------------------------------
1           test2                                                   alabama
3           Is actually very nice                                   South Dakota
5           It used to be so hard to spell, it used to make me cry  Mississippi

Finally,

select * from tblState3;

renders the output from table 2 in the order of table 1 (as expected):

NOW, having said all of this, this has been executed on a MySQL server directly (not through a linked) that is a recent version (MySQL 5.5). Perhaps you can run the content of the fiddle directly on your server through phpMyAdmin. This may help us understand if your MySQL version is the problem, or not.

You'll note that tblState3 preserves the original order of the problematic table while varying the order of inserts, just to add a bit of robustness to the experiment.

Answer 2

 mysql...tblState

is invalid syntax. Use dbname.tblState, or

USE dbname;

INSERT INTO tblState