2

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)
3
  • Have you tried using back ticks instead of single quotes? Also what version of mysql are you using?
    – Mark D
    Sep 2, 2011 at 19:57
  • What if you just change column order in the INSERT for each table? And using InnoDB?
    – gbn
    Dec 27, 2011 at 12:53
  • If I changed the insert statement to have the text column not be last then it worked. It's just a weird bug in the mysql db connect stuff. I don't know about InnoDB. We switched to a completely different solution (using MSSql instead of MySQL).
    – AndyD273
    Jan 3, 2012 at 21:38

2 Answers 2

2

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.

0
 mysql...tblState

is invalid syntax. Use dbname.tblState, or

USE dbname;

INSERT INTO tblState
1
  • I think that mysql is the name of the linked server.
    – swasheck
    Jun 21, 2012 at 21:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.