Results 1 to 6 of 6
Mar 14, 2005, 20:32 #1
Migrating a mySQL database from web server to another!
Here is my problem in a nutshell:
I am dissatisfied with my current host and would like to move two databases to a new host:
Current Host Runs: MySQL 4.1.7
New Host Runs: MySQL 4.0.16
I have tried using phpMyAdmin ...but to no avail. The problem seems to stem from the fact that I am downgrading to a lower version of mySQL. Here is the error message that I am getting:
ERROR 1064 at line 12: You have an error in your SQL syntax. Check
the manual that corresponds to your MySQL serve r version for the right
syntax to use near 'DEFAULT CHARACTER SET latin1 COLLATE
latin1_swedish_ci' at line
Tech support for my new web hosting company has been giving me the run-around ...usually an excuse for incompetence!
Now, ladies and gentlemen - I have reached 'boiling point', so to speak ....I need expert help......remember, I am not a database expert in any way!
Any help will be highly appreciated!
Mar 15, 2005, 03:46 #2
take a look at http://www.sitepoint.com/forums/showthread.php?t=232784. And look for another host
Mar 15, 2005, 15:09 #3
Thanks for your help!
It looks like I have edit something manually. Like I have indicated in my first post, I am no database expert...but what the heck...it's time to learn. I am comfortable with editing code and very willing to learn.
Now, please let me know what to use and how to proceed in chopping of what's causing this incompatibility. If I fail, I will at least have the satisfaction of having tried. Four years ago html, javscript & php etc. were "Greek" to me, but persisitent trial and error has paid off handsomely. This is yet another challenge that I am very willing to take on ...
As for changing hosts, Ipowerweb is a superb company, I have been hosting another website with them for the last 4 years and a Database transfer problem will not make me lose faith in them........the one I am using right now, Globat is a big joke!
Now, where do I start....?
Mar 16, 2005, 07:00 #4
as I don't have your CREATE statements I'll take the example from the other thread. Say you have such a 4.1 statement:
CREATE TABLE `admin` ( `ADMIN_ID` int(11) NOT NULL auto_increment, `username` varchar(40) collate latin1_general_ci NULL, `password` varchar(6) collate latin1_general_ci NULL, PRIMARY KEY (`ADMIN_ID`), UNIQUE KEY `password` (`password`), KEY `IDX_AUTH1` (`ADMIN_ID`) ) ENGINE=MyISAM CHARSET=latin1 COLLATE=latin1_general_ci;
CREATE TABLE `admin` ( `ADMIN_ID` int(11) NOT NULL auto_increment, `username` varchar(40) NULL, `password` varchar(6) NULL, PRIMARY KEY (`ADMIN_ID`), UNIQUE KEY `password` (`password`), KEY `IDX_AUTH1` (`ADMIN_ID`) ) ENGINE=MyISAM;
Mar 16, 2005, 15:23 #5
Thanks for your help, I really do appreciate you taking your time to help me out.
OK, I need further instructions. I can export the databases using phpMyadmin to my hard drive in .zip or .sql form etc.
I am assuming that I need to export in .sql form and then edit it in some editor and the import it using phpMyadmin into my new host. Please correct me if I am wrong.
1. What 'options' should I select in phMyadmin
2. Is a text editor sufficient or should I use something more specialized
I will appreciate very detailed instructions.
Thanking you in advance.
Mar 17, 2005, 03:20 #6
1. usually you can use the default settings of phpMyAdmin
2. an ordinary text editor should be sufficient. All you have to do is to delete all occurrences of "collate latin1_general_ci" et al.