Results 1 to 6 of 6
  1. #1
    Join Date
    Nov 2006
    Posts
    459

    how can i import .sql form mysql 4.1 to 4.0 ?

    hi,

    i am not sure if this is the right sub-forum to post the question.


    it is that i my a forum running with php and mysql,

    the version of it is 4.1 now,


    i wnat to move the forum to another server,

    but the mysql version of the server is 4.0,

    i try to backup the .sql form phpmyadmin and import form phpmyadmin,too.

    but it failed,

    some article tell me that i should change some setting in the .sql file,

    but i try and failed,

    can anyone teach me how can i process it?



    thanks a lot.

  2. #2
    Join Date
    Mar 2004
    Location
    UK
    Posts
    215
    This is working from memory as it's a long time ago that I last moved a database from 4.1 to 4.0. But you need to edit the file with a text editor. When you export table definitions from mysql 4.1, it includes information on the character set used, so your tables will appear something like this

    PHP Code:
    CREATE TABLE `STATISTICS` (
      `
    COL1varchar(512) default NULL,
      `
    COL2varchar(64NOT NULL default '',
      `
    COL3varchar(64NOT NULL default '',
    ) DEFAULT 
    CHARSET=utf8
    note that the exact details of the syntax and the character set used will likely be differant for your database. What you need to do is remove the character set information from the table definitions so you end up with this

    PHP Code:
    CREATE TABLE `STATISTICS` (
      `
    COL1varchar(512) default NULL,
      `
    COL2varchar(64NOT NULL default '',
      `
    COL3varchar(64NOT NULL default '',
    ); 
    Use the search and replace function (carefully) of your text editor to remove that data. When you have finished, save it as a new filename and run that through phpmyadmin. Saving as a seperate file and preserving your original copy is just a safety precaution.

    All this change does is makes mysql use the default character set when you restore the database, which works fine in mysql 4.0. It won't affect your data (unless you have non ascii data in the tables).

    HTH
    Martin

  3. #3
    Join Date
    Feb 2005
    Location
    Australia
    Posts
    5,849
    Don't mess with the sql file, make a new database backup in 4.0-compatible mode. From shell:
    Code:
    mysqldump -uuser -p --compatible=mysql40 database >database.sql
    in phpMyAdmin you should find a checkbox for it somewhere on the backup page.
    Chris

    "Some problems are so complex that you have to be highly intelligent and well informed just to be undecided about them." - Laurence J. Peter

  4. #4
    Join Date
    Dec 2005
    Location
    Planet Earth
    Posts
    24
    If you have the SSH access then you can generate the MySQL4.0 compatible dump on MySQL4.1 server as:

    mysqldump -uUSERNAME -pPASSWORD --compatible=mysql40 DATABASENAME > DUMP.sql
    PhpMyAdmin also has such an option to specify the compatible mysql version while generating the dump.

    This will generate the dump that can be easily imported on the server running MySQL 4.0.
    When you say "I wrote a program that crashed Windows", people just stare at you blankly and say "Hey, I got those with the system, *for free*".

  5. #5
    Join Date
    Oct 2004
    Location
    Kerala, India
    Posts
    4,771
    Editing sql file is not recommended, as it can cause issues . Use the method foobic stated.
    David | www.cliffsupport.com
    Affordable Server Management Solutions sales AT cliffsupport DOT com
    CliffWebManager | Access WHM from iPhone and Android

  6. #6
    Join Date
    Sep 2002
    Location
    Top Secret
    Posts
    14,134
    Upgrade SQL, don't downgrade your script. Mysql is in the 5.1 area now (beta), and php5 (which you'll need to upgrade to shortly) requires mysql >= 4.1.

    Solution? Upgrade mysql to the current version, don't downgrade a script to an old, outdated one.
    Tom Whiting, WHMCS Guru extraordinaire
    Linux problems? WHMCS Problems? Give me a shout
    Check out my WHMCS Addons

Posting Permissions

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