SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    SitePoint Member clubafri's Avatar
    Join Date
    Mar 2005
    Posts
    3
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question 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!

  2. #2
    Chessplayer kleineme's Avatar
    Join Date
    Apr 2004
    Location
    Germany
    Posts
    608
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi,

    take a look at http://www.sitepoint.com/forums/showthread.php?t=232784. And look for another host
    Never ascribe to malice,
    that which can be explained by incompetence.
    Your code should not look unmaintainable, just be that way.

  3. #3
    SitePoint Member clubafri's Avatar
    Join Date
    Mar 2005
    Posts
    3
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Thumbs up

    Kleineme:

    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....?

  4. #4
    Chessplayer kleineme's Avatar
    Join Date
    Apr 2004
    Location
    Germany
    Posts
    608
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi,

    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:

    Code:
    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;
    for 4.0.x you have to delete all references to collation and character sets:

    Code:
    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;
    Never ascribe to malice,
    that which can be explained by incompetence.
    Your code should not look unmaintainable, just be that way.

  5. #5
    SitePoint Member clubafri's Avatar
    Join Date
    Mar 2005
    Posts
    3
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question

    Kleineme:

    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.

    QUESTIONS:
    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.

  6. #6
    Chessplayer kleineme's Avatar
    Join Date
    Apr 2004
    Location
    Germany
    Posts
    608
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi,

    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.
    Never ascribe to malice,
    that which can be explained by incompetence.
    Your code should not look unmaintainable, just be that way.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •