phpmyadmin dump import problems

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
mikebr
Forum Contributor
Posts: 243
Joined: Sat Sep 28, 2002 7:05 am

phpmyadmin dump import problems

Post by mikebr »

I continually get frustrated when importing phpmyadmin database dumps, I find it not to work more often than it works.

Exporting files seems to go without problem but on trying to import these dump files I nearly always seem to get errors, if phpmyadmin dumps the data then why can't it import it without errors?

For example:

some table structure dumped from phpmyadmin:

Code: Select all

CREATE TABLE dst (
  dst_id tinyint(4) unsigned NOT NULL auto_increment,
  dst_string varchar(85) default NULL,
  dst_start varchar(10) default NULL,
  dst_end varchar(10) default NULL,
  PRIMARY KEY  (dst_id),
  UNIQUE KEY id (dst_id),
  KEY id_2 (dst_id)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=21 ;

INSERT INTO dst VALUES (2, 'Last Sunday in March - Last Sunday in October (Europe)', '-1/0/3', '-1/0/10');
INSERT INTO dst VALUES (1, 'First Sunday in April - Last Sunday in October (USA, Canada etc.)', '+1/0/4', '-1/0/10');
INSERT INTO dst VALUES (4, 'April 1 - Last Sunday in October (Cuba)', '1//4', '-1/0/10');
INSERT INTO dst VALUES (5, 'April 1 - October 1 (Syria, Iraq)', '1//4', '1//10');
INSERT INTO dst VALUES (6, 'First Friday in April - First Friday in September (Israel)', '+1/5/4', '+1/5/9');
INSERT INTO dst VALUES (7, 'Last Sunday in March - Last Sunday in September (Lebanon)', '-1/0/4', '-1/0/9');
INSERT INTO dst VALUES (8, 'First Friday on or after 5 April - First Friday on or after 5 October (Palestine)', '+5/5/4', '+5/5/10');
INSERT INTO dst VALUES (9, 'The first day of Farvardin - The first day of Mehr (Iran)', '21//4', '23//9');
INSERT INTO dst VALUES (10, 'Last Friday in April - Last Thursday in September (Egypt)', '-1/5/4', '-1/4/9');
INSERT INTO dst VALUES (11, 'First Sunday in October - Last Sunday in Februrary (Brazil)', '+1/0/10', '-1/0/2');
INSERT INTO dst VALUES (12, 'First Sunday on or after 9 October - First Sunday on or after 9 March (Chilie)', '+9/0/10', '+9/0/3');
INSERT INTO dst VALUES (13, 'First Sunday on or after 8 September - First Sunday on or after 6 April (Falklands)', '+8/0/9', '+8/0/4');
INSERT INTO dst VALUES (14, 'First Sunday in October - Last Saturday in February (Paraguay)', '+1/0/10', '-1/6/2');
INSERT INTO dst VALUES (15, 'First Sunday in September - First Sunday in April (Namibia)', '+1/0/9', '+1/0/4');
INSERT INTO dst VALUES (16, 'Last Sunday in October - Last Sunday in March (Australia)', '-1/0/10', '-1/0/3');
INSERT INTO dst VALUES (17, 'First Sunday in November - Last Sunday in February (Fiji)', '+1/0/11', '-1/0/2');
INSERT INTO dst VALUES (18, 'First Sunday in October - First Sunday on or after 5 March (New Zealand)', '+1/0/10', '+5/0/3');
INSERT INTO dst VALUES (19, 'First Sunday in October - First Sunday on or after 15 April (Tonga)', '+1/0/10', '+15/0/4');
INSERT INTO dst VALUES (20, 'First Sunday in October - Last Sunday in March  (Tasmania)', '+1/0/10', '-1/0/3');
On Importing the file...

MySQL said:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'dst_id tinyint(4) unsigned NOT NULL auto_increment,
dst_string varchar(85) def' at line 2

I have tried adding backquotes to the data but it makes no difference.

Exports and imports are being done on the same version but on different servers: phpMyAdmin 2.6.0-pl3

Is there some secret on how to get myphpadmin exported dumps to import successfully?

Thanks
Mike
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

works for me in command line MySQL 5... :?

[edit] grr fingers
Last edited by feyd on Sat Aug 27, 2005 11:19 am, edited 1 time in total.
User avatar
neophyte
DevNet Resident
Posts: 1537
Joined: Tue Jan 20, 2004 4:58 pm
Location: Minnesota

Post by neophyte »

Yeah, I've had problems with phpmyadmin too. I usually find deleting the lines that start with "--" solves most of the problems. I don't have any idea why. I just do it.

Somtimes I execute something like this to clean up the file if there are a lot of tables and what not:

Code: Select all

$limit = 60*60*60;
set_time_limit($limit);
ini_set("max_execution_time", $limit);
	$new_sql = 'newfilename.txtl';
	if(!is_file($new_sql)){
		touch($new_sql);	
	}
	$new_line = '';
	$filename = 'existingfile.txt';
	if(!$fh = fopen($filename, 'r')){
		die('can\'t open');
	}
	$f2 = fopen($new_sql, 'a+');
	while(feof($fh) === false){
		$line = fgets($fh, filesize($filename));
		if ($line{0} == "-"){
			$line = '';
		}
		$new_line = $line;
		echo $new_line;
		fwrite($f2, $new_line);
	}
	fclose($f2);
mikebr
Forum Contributor
Posts: 243
Joined: Sat Sep 28, 2002 7:05 am

Post by mikebr »

neophyte,

when I read your post and tried this it didn't work but then I tried it along with removing...

ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=21

from the table creation, the import then worked.

What I find puzzeling is why this would be when the inport is an export from the same verion of phpmyadmin, e.g. why does phpmyadmin add the "--" lines and the 'ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=21' if it can't handle them on inport.

Thanks for the suggestion.

Mike
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

I'm going to guess, it doesn't like the AUTO_INCREMENT setting or DEFAULT CHARSET. ENGINE should be fine.

What I'd like to understand is why you have a primary key, unique key and standard key on the same field. The unique and standard keys are useless.
mikebr
Forum Contributor
Posts: 243
Joined: Sat Sep 28, 2002 7:05 am

Post by mikebr »

Firstly, feyd thanks for pointing out a couple of changes that can be made to the structure, I didn't create the original table and never noticed them, but he problem I was having wasn't just with the last table, it seems to be with most, here is another example that caused a problem, removing the "--" lines and the 'ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=8' stopped phpmyadmin from returning errors.

Code: Select all

CREATE TABLE `continents` (
  `continent_id` int(5) NOT NULL auto_increment,
  `continent_name` varchar(50) NOT NULL default '',
  PRIMARY KEY  (`continent_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=8 ;
Thanks
Post Reply