forums › forums › SQLyog › SQLyog: Bugs / Feature Requests › Error When Copying Table To Different Host
- This topic is empty.
-
AuthorPosts
-
-
June 9, 2009 at 4:02 pm #11523
sdpippin
MemberThis code is the code that it tries to run to do a copy table to different host for this table.
CREATE TABLE `scgroupaccess` (
`siteid` varchar(5) NOT NULL DEFAULT '',
`groupid` varchar(25) NOT NULL,
`item` varchar(5) NOT NULL,
`accesstype` char(1) NOT NULL,
`systemid` varchar(5) NOT NULL,
PRIMARY KEY (`siteid`,`groupid`,`item`,`systemid`) USING BTREE
) ENGINE=MyISAM DEFAULT CHARSET=latin1
returns the following error:
Error Code : 1064
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'USING BTREE
) ENGINE=MYISAM DEFAULT CHARSET=latin1' at line 7
-
June 9, 2009 at 4:36 pm #29088
peterlaursen
ParticipantCan you tell the server versions: both source and target? (get it from “SELECT version()” or SQLyog menu .. tools .. show .. variables
This:
CREATE TABLE `scgroupaccess` (
`siteid` varchar(5) NOT NULL DEFAULT '',
`groupid` varchar(25) NOT NULL,
`item` varchar(5) NOT NULL,
`accesstype` char(1) NOT NULL,
`systemid` varchar(5) NOT NULL,
PRIMARY KEY (`siteid`,`groupid`,`item`,`systemid`) USING BTREE
) ENGINE=MyISAM DEFAULT CHARSET=latin1
..works fine for me on 5.1.35.
Try to omit both
“USING BTREE”
and
“ENGINE=MyISAM DEFAULT CHARSET=latin1”
I am not sure when USING was introduced for indexes in MySQL, ENGINE and table-level CHARSET parameters were introduced in 4.1.
So which of those work and which don't?
1:
CREATE TABLE `scgroupaccess` (
`siteid` varchar(5) NOT NULL DEFAULT '',
`groupid` varchar(25) NOT NULL,
`item` varchar(5) NOT NULL,
`accesstype` char(1) NOT NULL,
`systemid` varchar(5) NOT NULL,
PRIMARY KEY (`siteid`,`groupid`,`item`,`systemid`) USING BTREE
)
2:
CREATE TABLE `scgroupaccess` (
`siteid` varchar(5) NOT NULL DEFAULT '',
`groupid` varchar(25) NOT NULL,
`item` varchar(5) NOT NULL,
`accesstype` char(1) NOT NULL,
`systemid` varchar(5) NOT NULL,
PRIMARY KEY (`siteid`,`groupid`,`item`,`systemid`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
3:
CREATE TABLE `scgroupaccess` (
`siteid` varchar(5) NOT NULL DEFAULT '',
`groupid` varchar(25) NOT NULL,
`item` varchar(5) NOT NULL,
`accesstype` char(1) NOT NULL,
`systemid` varchar(5) NOT NULL,
PRIMARY KEY (`siteid`,`groupid`,`item`,`systemid`)
)
-
June 9, 2009 at 4:39 pm #29089Participant
.. and also tell the SQLyog program version!
-
June 9, 2009 at 6:07 pm #29090Member
I upgraded the one client to 5.1.35 and everyhting worked ok. I was trying to go from 5.1.35 to 5.0.45. Should the copy to a different host/DB take the difference into account?
-
June 9, 2009 at 6:33 pm #29091Participant
Yes it should. 'copy database/table' should work across versions. But we may have overlooked handling the USING clause for indexes (this is my best guess).
It would be helpful it you could reply to my questions! Which of the 3 statements would succeed? But now you upgraded one server instead so maybe not possible to tell now?
-
June 9, 2009 at 6:35 pm #29092Participant
I check on 5.0.82. Here
CREATE TABLE `scgroupaccess` (
`siteid` VARCHAR(5) NOT NULL DEFAULT '',
`groupid` VARCHAR(25) NOT NULL,
`item` VARCHAR(5) NOT NULL,
`accesstype` CHAR(1) NOT NULL,
`systemid` VARCHAR(5) NOT NULL,
PRIMARY KEY (`siteid`,`groupid`,`item`,`systemid`) USING BTREE
) ENGINE=MYISAM DEFAULT CHARSET=latin1
.. works fine too! There may have been a bug in 5.0.45. I cannot get closer tonight!
-
-
AuthorPosts
- You must be logged in to reply to this topic.
