Solved

importing sql file thorugh phpmyadmin

Posted on 2009-04-01
14
955 Views
Last Modified: 2013-12-07
I backed up a DB file for a Drupal website locally using phpmyadmin, but I can't upload it again. Either it fails because of duplicate keys, or if I edit the DB to prevent that (which I haven't succeeded in doing without making a nonsense of some data), it truncates lots of content, apparently where there are non-alphanumeric characters. BTW, I don;t think the duplicate keys are real, just looks that way if some key fields are truncated. SO perhaps truncation at non-alphanumeric characters on import is the whole problem.

IF I look at the data in e.g. wordpad, it seems to be fine - it isn't truncated. So I think the problem is reimporting through phpmyadmin onto a shared hosting service.

BTW, I have no real expertise in writing sql queries- I just followed instructions to export and import the DB through phpmyadmin.

I am desperate to get this data up again - help much appreciated.
0
Comment
Question by:timdp
  • 7
  • 7
14 Comments
 
LVL 4

Expert Comment

by:paisleym
Comment Utility
Hi,

Does the script also create the tables?
If not are the tables created with the same datatypes?
What is the encoding of the original database and the new database?
Have you tried copy / pasting the sql into the sql tab in phpmyadmin?

Marcelle
0
 

Author Comment

by:timdp
Comment Utility
Does the script also create the tables? I think so - see snippet...
If not are the tables created with the same datatypes?
What is the encoding of the original database and the new database? seems to be utf8 for both (if I understand the question correctly)
Have you tried copy / pasting the sql into the sql tab in phpmyadmin? Actually yes - but after 15 mins I presumed it had hung - though it is a 4Mb file


CREATE TABLE IF NOT EXISTS `access` (

  `aid` int(11) NOT NULL auto_increment,

  `mask` varchar(255) NOT NULL default '',

  `type` varchar(255) NOT NULL default '',

  `status` tinyint(4) NOT NULL default '0',

  PRIMARY KEY  (`aid`)

) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;
 

--

-- Dumping data for table `access`

--

Open in new window

0
 
LVL 4

Expert Comment

by:paisleym
Comment Utility
Hi

utf-8 Yes, that is what I was asking.

Are the databases of the same mySQL version?
I suspect not, and that is why the underlying truncation would be happening.
http://dev.mysql.com/doc/refman/5.0/en/storage-requirements.html
http://dev.mysql.com/doc/refman/4.1/en/storage-requirements.html

Failing because of duplicate keys, means that table has a primary key that should be an index, because of the duplicates in that field.

What I would suggest is to do it one table at a time - create table and data
If the table has any indexes referring to keys on other tables do that last.

phpmyAdmin can take a while, so I do these kind of things during a coffee break so I don't stress about how long it is taking.

Let me know
Marcelle
0
 

Author Comment

by:timdp
Comment Utility
The server version of SQL is 5.0.67-community-log. I backed up yesterday from the same server, so I presume that the versions are likely to be the same (thanks for the links, but I don't understand them - might after some study perhaps).

There are maybe 170 tables - if I do them one-buy-one, how do I do that??

thanks


0
 
LVL 4

Expert Comment

by:paisleym
Comment Utility
Yikes, that is a lot of tables.

The sql dump file goes table / data / table / data with the order being alphabetical by table name. The only issue is that sometimes table customers depends on employee being set up first, for instance.

However, I am getting worried that there might be more to this than my mysql experience can help. Have you looked at http://drupal.org/upgrade/ - specifically the backing up and restoring sections.

thx
Marcelle
0
 

Author Comment

by:timdp
Comment Utility
I followed their instructions for back-up, and I can't find anything on restoring the DB - though it is apparently simple in phpmyadmin. But in the meantime, it seems pretty certain that certain speical characters are causing the truncation of fields, so even if I edit the ones that led to duplicate keys, large chunks of text content are truncated, so I don;t get my data imported properly anyway.

export and import were both supposed to be utf8, but characters like m-dashes in the text wreck the import.

I have been at this 12 hours straight and I am nowhere near a solution   ;-(
0
 
LVL 4

Expert Comment

by:paisleym
Comment Utility
Does this http://drupal.org/upgrade/copying-your-live-site-via-command-line - near the bottom help?

Yes, done many 12 hour stints.
Marcelle
0
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 

Author Comment

by:timdp
Comment Utility
I have seen a couple of solutions today where people say that command line works where php import does not - but I haven't figured out where to start to write a command line. My host has cPanel - can I get to a command line input from there??

thanks again
0
 
LVL 4

Expert Comment

by:paisleym
Comment Utility
You would need mysql administrator http://dev.mysql.com/downloads/gui-tools/5.0.html
That will allow you to connect to your mysql via ip address and has backup and restore options, not quite command line, but closer.

hth
Marcelle
0
 

Author Comment

by:timdp
Comment Utility
I found someone else had apparently used this to fix the same problem... here's his/her instructions(I forget where I found them). But I have already backed up and lost the original file so I am not sure this will help - even if I could understand it)

1. Installed mySQL administrator on my desktop
2. Via Putty, setup a SSH tunnel to my primary blog database as follows: L13306 localhost:3306 (Yes it is odd to refer to destination as localhost, but it works)
3. Fire up mySQL Administrator and connected to the main database via localhost:13306. Started a Backup project and exported a SQL file of the database.
4. Another SSH tunnel, this time to the test server with a slightly different forwarding setup - L14306 localhost:3306.
5. Connected to the test database on localhost:14306. Chose Restore, Open Backup file. Since I've used mySQL administrator to export (and only mySQL administrator generated backup files work in Restore) other settings are fine, so "Start Restore".
6. Look at the homepage and not only are all the posts complete with no cut off characters, I don't have weird characters from charset conversions either. Awesome!
0
 
LVL 4

Expert Comment

by:paisleym
Comment Utility
Hi

http://www.putty.org/ is for the download of putty, which gives you a secure tunnel while working in mysql administrator with your database.

I suppose the only gotcha is how was the backup made?
Is it possible to make a new backup or is the old db trashed?

Marcelle
0
 

Accepted Solution

by:
timdp earned 0 total points
Comment Utility
Hi, Michelle, I hadn't seen an email for your reply, so sorry for delay.

There is no possibility to remake a backup, however, I seem to have found a solution. 14 hours to find it, 1 minute to implement.

Open in Notepad, save as utf8, reimport. Haven't put the site back up yet, but the database imported without errors and apparently without truncations. So I am now a much happier bunny. Not sure why it wasn't utf8 already, which is what I exported it as, and I had a t least one backup that had never been opened, but anyhow, that seems to be the answer.





0
 

Author Comment

by:timdp
Comment Utility
Sorry, Marcelle....      :-(
0
 
LVL 4

Expert Comment

by:paisleym
Comment Utility
Good that you've got the solution. No worries, Michelle is one of the most normal variations.
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Join & Write a Comment

I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
Creating and Managing Databases with phpMyAdmin in cPanel.
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now