phpMyAdmin: trouble importing .sql files II

Now that he could successfully load large sql files into MySQL database, Maheshwor wrote back to me saying he stumbled upon another  bit of problem. The sql files contained duplicate data which he didnt want. For example,

INSERT INTO table1 VALUES 
("a", "apple", "fruit"), 
("a", aeroplane", "object"),
("b", "ball", "object");
... some more values;

INSERT INTO table1 VALUES
("a", "apple", "fruit"), 
("b", "ball", "object"), 
("c", "cat", "animal");

... and so on....

So an easy solution to this problem would be,

1. Open some Word Processor (or some text-editor which has  ’Find and Replace‘ facility). I shall guide you with Microsoft Word 2003/2007.
2. Now open the sql file from the text editor. For this, press Crtl+O. When the dialog box opens up, Change the Files of type to All files and select the sql file.  If the file is large, it may take a while to open the doc. Dont worry about formatting. Dont worry if it looks scrambled and irritating.
3. Now press Ctrl+F, a dialog box should open up with the Find tab selected. Get to Replace tab next to it.
4. In the Find what: box, type in INSERT and in Replace with: box, type INSERT IGNORE.
5. Save the document (in the sql format).

If you’ve already run the sql script and dont have much patience to do so again, try this:

1. The process is pretty simple, select all the unique data
2. Create a temporary table using that data
3. Delete the previous table
4. Rename the current table to the previous one.

Example:

mysql> CREATE TABLE tmp SELECT last_name, first_name, sex
    ->                  FROM person_tbl;
    ->                  GROUP BY (last_name, first_name);
mysql> DROP TABLE person_tbl;
mysql> ALTER TABLE tmp RENAME TO person_tbl;

If you found that boring (” yestooooo jhyau lagdo.. ” in Nepali), declare a primary key. Like this:

mysql> ALTER IGNORE TABLE person_tbl
    -> ADD PRIMARY KEY (last_name, first_name);

Declaring  a field as  a primary key doesnt let duplicate entries. Plus, you can add all the fields to the primary key. Example,

-> ADD PRIMARY KEY (last_name, first_name, sex, <blah>, <blah>, <so on>);

Okay Mahesh, hope this helps…

Later,

phpMyAdmin: trouble importing .sql files

My friend Maheshwor had a bit of a problem handling the MySQL database. He had an SQL script with him of about 25M. He didn’t want to install some resource hogging MySQL tools and wanted me to suggest something lightweight. I had recommended him to install EasyPHP since that was the lightest thing i’ve used for web programming. But then he had another problem. The phpMyAdmin could not ingest the SQL files more that 5M. So after googling a bit, i finally found a neat solution here.

I shall brief you a bit here.

• Open the console or the command prompt.
• If you’re using  EasyPHP, start EasyPHP server.
• Go to the installation directory. It was C:\Program Files\EasyPhp 2.0b1\ in my case.
• Change the directory further to mysql\bin
• Log into MySQL. eg. mysql -h localhost -u root
• You should see the prompt change from C:\Program Files\EasyPHP 2.0b1\mysql\bin> to mysql> and some other messages like:
  

  C:\Program Files\EasyPHP 2.0b1\mysql\bin>mysql -h localhost -u root
  
  Welcome to the MySQL monitor.  Commands end with ; or \g.
  Your MySQL connection id is 13 to server version: 5.0.27-community-log
  Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
  mysql>

• To use the database, type in use <db_name>
• Now here comes the good part, the solution to the problem type in source c:/<sql_filename>.sql

NOTE:

When specifying the path to the the sql file, please mind the slashes. Its a Backslash ‘/’ NOT frontslash ‘\’ which is typically used in windows. and the path doesnt require the quotes. Incase you encounter problems, please check your sql file again. (I got couple of syntax errors and some more…)

Later,