problem for joomla 1.6 migration of webiste

[sggis]

hi, sir
I face one issued of migration of website:
if my configuration is already in one domain name under one diractory and one database name, when I migrate to any other server, or cahnge diractory name, or change the database name also cause the wesite become not workable, how to solve the problem?

when I install in one server is working, I backup the database when I restore this database in another server will come out error, what is the problem ? the error as following:
SQL query:

CREATE TABLE IF NOT EXISTS `jml_usergroups` (

`id` int( 10 ) unsigned NOT NULL AUTO_INCREMENT COMMENT 'Primary Key',
`parent_id` int( 10 ) unsigned NOT NULL DEFAULT '0' COMMENT 'Adjacency List Reference Id',
`lft` int( 11 ) NOT NULL DEFAULT '0' COMMENT 'Nested set lft.',
`rgt` int( 11 ) NOT NULL DEFAULT '0' COMMENT 'Nested set rgt.',
`title` varchar( 100 ) NOT NULL DEFAULT '',
PRIMARY KEY ( `id` ) ,
UNIQUE KEY `idx_usergroup_title_lookup` ( `title` ) ,
KEY `idx_usergroup_adjacency_lookup` ( `parent_id` ) ,
KEY `idx_usergroup_nested_set_lookup` ( `lft` , `rgt` ) USING BTREE) ENGINE = MYISAM DEFAULT CHARSET = utf8 AUTO_INCREMENT =13;

MySQL said:

#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=utf8 AUTO_INCREMENT=13' at line 10

What is the problem ,
how to solve this probem?
Thanks from David

[stutteringp0et]

There is a known problem with MySQL 5.1 you can read about here:
http://bugs.mysql.com/bug.php?id=25162

The "USING BTREE" portion of the query might be what is causing your issue.

Remove it from all queries and try again.

[jikanv]

Hi. Can you please explain how to remove "USING BTREE" from a query? I am having the same issue and I have (I am sure thousands of others neither) no idea how to do it to make joomla migration work. Thanx for your understanding.

[stutteringp0et]

edit the query file, remove the text "USING BTREE" anywhere you find it.

Take care to not disturb the surrounding text.

Code: Select all

CREATE TABLE IF NOT EXISTS `#__usergroups` (

`id` int( 10 ) unsigned NOT NULL AUTO_INCREMENT COMMENT 'Primary Key',
`parent_id` int( 10 ) unsigned NOT NULL DEFAULT '0' COMMENT 'Adjacency List Reference Id',
`lft` int( 11 ) NOT NULL DEFAULT '0' COMMENT 'Nested set lft.',
`rgt` int( 11 ) NOT NULL DEFAULT '0' COMMENT 'Nested set rgt.',
`title` varchar( 100 ) NOT NULL DEFAULT '',
PRIMARY KEY ( `id` ) ,
UNIQUE KEY `idx_usergroup_title_lookup` ( `title` ) ,
KEY `idx_usergroup_adjacency_lookup` ( `parent_id` ) ,
KEY `idx_usergroup_nested_set_lookup` ( `lft` , `rgt` ) USING BTREE) ENGINE = MYISAM DEFAULT CHARSET = utf8 AUTO_INCREMENT =13;

becomes (notice the 2nd to last line in both)

Code: Select all

CREATE TABLE IF NOT EXISTS `#__usergroups` (

`id` int( 10 ) unsigned NOT NULL AUTO_INCREMENT COMMENT 'Primary Key',
`parent_id` int( 10 ) unsigned NOT NULL DEFAULT '0' COMMENT 'Adjacency List Reference Id',
`lft` int( 11 ) NOT NULL DEFAULT '0' COMMENT 'Nested set lft.',
`rgt` int( 11 ) NOT NULL DEFAULT '0' COMMENT 'Nested set rgt.',
`title` varchar( 100 ) NOT NULL DEFAULT '',
PRIMARY KEY ( `id` ) ,
UNIQUE KEY `idx_usergroup_title_lookup` ( `title` ) ,
KEY `idx_usergroup_adjacency_lookup` ( `parent_id` ) ,
KEY `idx_usergroup_nested_set_lookup` ( `lft` , `rgt` ) ) ENGINE = MYISAM DEFAULT CHARSET = utf8 AUTO_INCREMENT =13;

[jikanv]

Thank you very much for your help, but how do I edit the query file?

[stutteringp0et]

Really?

It's a text file, edit it just like any other text/php/html file on your system. If you're on windows you can use something like notepad. On Mac I like TextMate and in Linux I like Kate or Nano.

[jikanv]

Thank you very much.

[leheros]

Thank you stutteringp0et.
Your information was very useful !

[tr1]

edit the query file, remove the text "USING BTREE" anywhere you find it.

I was having problems transferring a local copy of J 1.6 from XAMPP to a live server. I searched the database for the above text, deleted it and tried again and now it's working fine. Thanks so much for the tip.