Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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)
share|improve this question
    
Have you tried using back ticks instead of single quotes? Also what version of mysql are you using? –  Mark D Sep 2 '11 at 19:57
    
What if you just change column order in the INSERT for each table? And using InnoDB? –  gbn Dec 27 '11 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 '12 at 21:38
add comment

migrated from serverfault.com Sep 2 '11 at 19:33

This question came from our site for professional system and network administrators.

2 Answers

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.

share|improve this answer
add comment
 mysql...tblState

is invalid syntax. Use dbname.tblState, or

USE dbname;

INSERT INTO tblState
share|improve this answer
    
I think that mysql is the name of the linked server. –  swasheck Jun 21 '12 at 21:37
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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