SitePoint Sponsor

User Tag List

Results 1 to 10 of 10
  1. #1
    SitePoint Zealot
    Join Date
    Nov 2006
    Posts
    151
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question importing a mysql database that running in asp to phpmyadmin

    Dear all,

    I have no experience in importing a .sql file that exported from asp platform and then importing it to phpmyadmin so it can be run in php.

    May I know anything need to be done to the exported database (such as converting it...) before I can import it to phpmyadmin?
    Attached Images Attached Images
    newbie...

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,251
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    please show the first dozen or so line of the SQL file
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Zealot
    Join Date
    Nov 2006
    Posts
    151
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Dear r937,

    Below is the first few lines of the exported data in .sql file:

    -- MySQL dump 10.11
    --
    -- Host: localhost Database: mydatabase
    -- ------------------------------------------------------
    -- Server version 5.0.51a-community

    /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
    /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
    /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
    /*!40101 SET NAMES utf8 */;
    /*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
    /*!40103 SET TIME_ZONE='+00:00' */;
    /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
    /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
    /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
    /*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

    --
    -- Table structure for table `tb_activity_log`
    --

    DROP TABLE IF EXISTS `tb_activity_log`;
    SET @saved_cs_client = @@character_set_client;
    SET character_set_client = utf8;
    CREATE TABLE `tb_activity_log` (
    `id` bigint(20) unsigned NOT NULL auto_increment,
    `originating_ip` varchar(15) NOT NULL,
    `user_name` varchar(40) default NULL,
    `activity` varchar(90) NOT NULL,
    `status` char(1) default NULL,
    `remark` text,
    `log_datetime` datetime NOT NULL,
    PRIMARY KEY (`id`)
    ) ENGINE=InnoDB AUTO_INCREMENT=16281 DEFAULT CHARSET=utf8;
    SET character_set_client = @saved_cs_client;

    --
    -- Dumping data for table `tb_activity_log`
    --

    LOCK TABLES `tb_activity_log` WRITE;
    /*!40000 ALTER TABLE `tb_activity_log` DISABLE KEYS */;
    INSERT INTO `tb_activity_log` VALUES (something here);
    /*!40000 ALTER TABLE `tb_activity_log` ENABLE KEYS */;
    UNLOCK TABLES;

    I tried to import using the setting as in the attachment but it takes too long for the process...
    What should be my next step?

    Thank you!
    newbie...

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,251
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    you don't need to do anything to this sql file before running it

    except if, as you say, "it takes too long for the process"

    you could split it up into smaller chunks
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Addict Mal Curtis's Avatar
    Join Date
    Jul 2009
    Location
    New Zealand
    Posts
    327
    Mentioned
    14 Post(s)
    Tagged
    0 Thread(s)
    The phpMyAdmin checkbox there which allows the script to stop and restart is pretty useful. It can break transactions, but from the looks of your sql it's just a plain sql dump.

    Basically when the time limit runs out, it just displays the page again - you select the file again - and it starts where it left off.

    It could cause table locking issues but hopefully that won't be an issue. It's always easy to drop all the tables and start again anyway.

  6. #6
    SitePoint Zealot
    Join Date
    Nov 2006
    Posts
    151
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    you don't need to do anything to this sql file before running it

    except if, as you say, "it takes too long for the process"

    you could split it up into smaller chunks
    Thanks for your reply

    Is it using the method suggested by Mal Curtis?
    newbie...

  7. #7
    SitePoint Zealot
    Join Date
    Nov 2006
    Posts
    151
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Mal Curtis View Post
    The phpMyAdmin checkbox there which allows the script to stop and restart is pretty useful. It can break transactions, but from the looks of your sql it's just a plain sql dump.

    Basically when the time limit runs out, it just displays the page again - you select the file again - and it starts where it left off.

    It could cause table locking issues but hopefully that won't be an issue. It's always easy to drop all the tables and start again anyway.
    Thanks for your suggestion...

    What should I select for "SQL compatibility mode" ?
    newbie...

  8. #8
    SitePoint Addict Mal Curtis's Avatar
    Join Date
    Jul 2009
    Location
    New Zealand
    Posts
    327
    Mentioned
    14 Post(s)
    Tagged
    0 Thread(s)
    You can leave that for your first try. If you get an issue with 'NONE' then try mysql40.

    Hopefully it won't be an issue.

  9. #9
    SitePoint Zealot
    Join Date
    Nov 2006
    Posts
    151
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks again Mal Curtis..

    I'll try now and update the status here later ...
    newbie...

  10. #10
    SitePoint Zealot
    Join Date
    Nov 2006
    Posts
    151
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi all,

    Finally, I managed to import the 9mb++ sql file to my phpmyadmin after splitting it into few chunks.

    Thank you for your guidance
    newbie...


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
  •