Changing servers mysql versions?

New Member Joined: Dec 19, 2003 Posts: 18

Hi i have 2 nuke sites
nuke7.1 mysql 4.1.x
&
nuke6.9 mysql3.2.58+

The nuke 7.1 is a fresh install with some modules added and sec fixes applied.
The nuke6.9 has no modules other than a shopping cart.

I need to switch the servers around due to bandwidth and my host has said the databse must be from the same version as the one they are using Sad

For the nuke7.1 i can simply upload the main nuke files and modules and run the fresh sql file again +any other .sql files for the modules i added.

but the nuke 6.9 site i need to backup the data from the shopping cart.
How can i extract the data from the shopping cart(mysql3.2.5 Cool

and apply it to a fresh database(mysql4.1.x).

hope i made some sense LOL

Site Admin/Owner Joined: Aug 27, 2002 Posts: 17088

Use phpMyAdmin and EXPORT the data and the structure from the old database. Then, IMPORT it to the new database.

Posted: Mon Feb 16, 2004 1:16 pm

Ive made a backup from nuke and also phpmadmin to be safe.
Does it matter that the versions of mysql differ?

As a test i tried adding the 4.1 .sql file to an extra database on the 3.2.58 sql server and it didnt work,i got an error importing the databases,1 saved from nuke the other saved from phpmyadmin.

Posted: Mon Feb 16, 2004 1:33 pm

Absolutely not if you're upgrading. Downgrading could/would depending if you were using any version 4.x functions not in 3.x but nuke does not.

Posted: Mon Feb 16, 2004 3:02 pm

Ok i shouldnt have a problem upgrading then but this error shows up when i try to add the database to mysql3.2.58

Code:

Error





SQL-query :  





CREATE TABLE `cpg_installs` (





`cpg_id` tinyint( 3 ) NOT NULL auto_increment,


`dirname` varchar( 20 ) NOT NULL default '',


`prefix` varchar( 20 ) NOT NULL default '',


`version` varchar( 10 ) default NULL ,


PRIMARY KEY ( `cpg_id` ) 


) TYPE = MyISAM DEFAULTCHARSET = latin1AUTO_INCREMENT = 2 





MySQL said: 








You have an error in your SQL syntax near 'DEFAULT CHARSET=latin1 AUTO_INCREMENT=2 ' at line 7


Back

The sql file was saved from phpmyadmin

i get this error running the nuke.sql backup

Code:

SQL-query :  





CREATE TABLE nuke_cpg_pictures(





pid int( 11 ) NOT NULL auto_increment,


aid int( 11 ) DEFAULT '0' NOT NULL ,


filepath varchar( 255 ) NOT NULL ,


filename varchar( 255 ) NOT NULL ,


filesize int( 11 ) DEFAULT '0' NOT NULL ,


total_filesize int( 11 ) DEFAULT '0' NOT NULL ,


pwidth smallint( 6 ) DEFAULT '0' NOT NULL ,


pheight smallint( 6 ) DEFAULT '0' NOT NULL ,


hits int( 10 ) DEFAULT '0' NOT NULL ,


mtime timestamp,


ctime int( 11 ) DEFAULT '0' NOT NULL ,


owner_id int( 11 ) DEFAULT '0' NOT NULL ,


owner_name varchar( 40 ) NOT NULL ,


pic_rating int( 11 ) DEFAULT '0' NOT NULL ,


votes int( 11 ) DEFAULT '0' NOT NULL ,


title varchar( 255 ) NOT NULL ,


caption text NOT NULL ,


keywords varchar( 255 ) NOT NULL ,


approved enum( 'YES', 'NO' ) DEFAULT 'NO' NOT NULL ,


user1 varchar( 255 ) NOT NULL ,


user2 varchar( 255 ) NOT NULL ,


user3 varchar( 255 ) NOT NULL ,


user4 varchar( 255 ) NOT NULL ,


url_prefix tinyint( 4 ) DEFAULT '0' NOT NULL ,


randpos int( 11 ) DEFAULT '0' NOT NULL ,


pic_raw_ip tinytext,


pic_hdr_ip tinytext,


PRIMARY KEY ( pid ) ,


KEY pic_hits( hits ) ,


KEY pic_rate( pic_rating ) ,


KEY aid_approved( aid, approved ) ,


KEY randpos( randpos ) ,


KEY pic_aid( aid ) ,


KEY search( title, caption, keywords, filename ) 


) 





MySQL said: 








BLOB column 'caption' used in key specification without a key length

some of the tables are inserted but not all of them?

Posted: Mon Feb 16, 2004 3:12 pm

Remove this from all sql

Code:

DEFAULTCHARSET = latin1

or try inserting a space between

Code:

DEFAULTCHARSET = latin1 AUTO

As to the other, hmmmm.