Import Failing

Got a new MacBook Pro last March 2013, and I am still trying to get it set up and working?! (Oh the agony of migrations!!!)

On my old MacBook, every week I use phpMyAdmin to the “export” tab to backup my database to a backup folder.

This morning I took the hard-drive out of my old MacBook, plugged it into an external HDD enclosure, plugged it into my new MBP, and chose to import the latest backup called “doubledee_backup_2013-12-08_LastBeforeMigration.sql”

Unfortunately, phpMyAdmin keeps giving me this error shortly after the import begins…


Error

SQL query:

DELIMITER;

--
-- Dumping data for table `article_placement`
--
INSERT INTO `article_placement`
VALUES ( 1, 'finance', 'featured-finance', 'economy', 'which-states-support-main-street', 0, '2013-05-11 18:33:34', NULL ) ;

MySQL said: Documentation
#1146 - Table 'doubledee.section_dimension' doesn't exist

I don’t understand why it would complain that the “section_dimension” table doesn’t exist, when the whole point is that I am trying to import all of my old database structure into a new, bare database on my new MBP?! :rolleyes:

My old MacBook has an older version of MAMP, and thus phpMyAdmin, MySQL, PHP, etc., but you wouldn’t think that would make that big of a deal when it comes to exporting/importing a basic database?!

Any ideas what is going on?? :-/

Sincerely,

Debbie

1/ does the table exist?
2/ have you run the create table sql if there is one?
3/ did the export command export the data or the data + table structure?

Hi Debbie,

Looks like you decided to take the easy “Quick - display only the minimal options” when exporting your database.

Try exporting again select “Custom - display all possible options”.

Object creation options

Add statements:
Add DROP TABLE statement
Add CREATE PROCEDURE / FUNCTION / EVENT statement
CREATE TABLE options:
IF NOT EXISTS
AUTO_INCREMENT
Enclose table and column names with backquotes (Protects column and table names formed with special characters or keywords)

It should save you the time in re-creating all the tables and possibly the fields all over again.

It exists in my original database.

No, because my script is supposed to build everything inside the database container…

Data + Structure

Debbie

I’ve got a REALLY horrible feeling about all of this… :frowning:

Hate to break it to you, but that stuff didn’t help…

Here are screenshots of what I am doing in MAMP’s phpMyAdmin…

Create Export DBSchema

Preparing to Import DB Schema

** Importing **

Crash & Burn…

Sincerely,

Debbie

The Crash & Burn error message states that the operation timed out. I have overcome this problem by Zipping the export.

I also think that the database names must be identical.

I tried that and it makes no difference…

Besides, this is a database with 31 Tables in it and a 1.4MB file. That is TINY!!!

[ot]What freaks me out the most about all of this, is that I thought I knew how to back-up my database, and apparently all of these backups are now useless… If size was an issue, then what in the hell will happen when I do this same thing on my Production Database which has 10,000 Members in it and is several hundred MB’s in size, if not into the GB’s?! :rolleyes:
[/ot]

On my new MBP I had the same database name, and it still isn’t working…

Debbie

>>> I tried that and it makes no difference…
What was the new error messages?

>>> Besides, this is a database with 31 Tables in it and a 1.4MB file. That is TINY!!!
Is that the size of the zipped file?

I frequently download a Zip file about 1.5 meg and the raw SQL file is about three times bigger.

As far as the download file size limitations, PhpMyAdmin has a default which can easily be enlarged.

Same as above.

No, the raw .sql file.

Last night when I was trying things, the ZIP file was 123 KB and the .sql file was 1.4 MB (Both are extremely TINY!!)

One things I have noticed is that after things crash/time-out, 30 out of 31 objects are created.

The problem now seems to be with my “z_result_log” which is where I keep all of my Errors/Outcomes.

That table is only 6,377 records and has a whopping 9 columns?!

It seems like when the Import is kicked off, things hang up after maybe 1,500 records get INSERTed.

After things crash, the final “z_result_log_VIEW” View is then not being created.

When I created a separate Export of the “z_result_log” table, then it gets successfully Imported in under 3 seconds?! :-/

There are NO Foreign Keys on this table.

And I currently only have one Trigger, and it does not touch the "z_result_log_ table.

So I’m not sure what is going on here…

Debbie

The moral of the story is: don’t use phpmyadmin to backup and restore db. I’ve been there and after finding my dumps corrupted I said never again! Phpmyadmin is simply not the right tool for the job, php is not the best language for this because due to timeout limits the export/import need to be split into multiple jobs and this creates complexity and results in bugs. For exporting a simple and small table from time to time it’s okay but never for transferring large amounts of data or for stuff that is important in any way!

I don’t know of any good mysql software for the Mac, Windows users can use the free edition of SQLyog for easy backup/restore. But there’s always the good old mysqldump invoked from the command line and it’s pretty easy to use and most importantly it’s can be relied upon. Mysqldump has many options but the basic usage is quite simple: [URL=“http://forums.mysql.com/read.php?10,195091,195097”]How to load database from a dump file. But even the somewhat clumsy [URL=“http://mysqlworkbench.org/”]MySQL Workbench will do the job fine as it acts as a GUI for mysqldump and it’s multi-platform.

It appears as though you have a problem with at least one table.

I prefer to have a working export/import sample.

I would try and find the good tables by export/import a limited amount of tables (start with 16).

If the export is OK then increase the tables and if not, reduce the number of tables.

Repeat until the problematic table is found

Select only the problematic table and export/import to find the problem.

Right, as I said in Post #9

Is this table “broken”??

I don’t think so.

And that seems like a small number of records…

So what gives?!

Not sure what you are asking?

Did you want to see the .sql for when I export that one table?

As I said above…

It seems like if I leave out the “z_results_log” table from the export file, then all 30 of the 31 Tables/Views get created okay. (Although I’m not sure how you verify that is the case??)

And if I create a 2nd export for the “z_results_log” table, then by itself, it runs in a few seconds.

But that doesn’t explain why things are hanging up?!

If export #1 with 30 Tables/Views runs in 1 second, and export #2 with the “z_results_log” table only runs in maybe 3 seconds, then why doesn’t my original export with all 31 Tables/Views not run in under 5 seconds instead of crashing after 5 minutes?! :-/

Sincerely,

Debbie

But as far as I know, phpMyAdmin is also just a GUI for running mysqldump

And how much more reliable is MySQL WorkBench not only for Backup & Restores, but basics database maintenance as well??

(I’ve heard people rip on phpMyAdmin in the past, but up until now, I have found it to be a real Godsend for someone who doesn’t have the time or competency to do everything via command-line…)

Sincerely,

Debbie

OK, if you can export/import the database with 30 tables then that is “working export/import” sample.

Try first to see if the exported log table can be added to your “working export/import” and see if there are any errors.

Secondly export/import the single log table in your database and see if there are any errors.

How would I do that?

John, you’re not reading very carefully… :wink:

As I have said a couple of times…

1.) When I export all 31 Tables/Views into a single .sql file, and then try and Restore things, I get an error.

2.) When I export all Tables/Views except for my “z_results_log” table, and then try and Restore things, I get a successful restore in a few seconds.

3.) When I export just the “z_results_log” table by itself, and then try and Restore things, I get a successful restore in a few seconds.

So for some reason, phpMyAdmin won’t play nice when I export everything and then try and restore everything from one .sql file?!

Sincerely,

Debbie

With the PhpMyAdmin:
Selecting a database to export
Select “Custom - display all possible options”
Select the 30 good tables to be exported
Select the create table IF NOT EXISTS and AUTO_INCREMENT
Select the Zip option
Select Go and save the Zip file
Try importing the Zip file into an empty database.

==========================//============================

John, you’re not reading very carefully… :wink:

As I have said a couple of times…
1.) When I export all 31 Tables/Views into a single .sql file, and then try and Restore things, I get an error.
2.) When I export all Tables/Views except for my “z_results_log” table, and then try and Restore things, I get a successful restore in a few seconds.
3.) When I export just the “z_results_log” table by itself, and then try and Restore things, I get a successful restore in a few seconds.

So for some reason, phpMyAdmin won’t play nice when I export everything and then try and restore everything from one .sql file?!

When you export/import the z_results_log table is it only the table or is the table included in a database log?

With the import of the z_result_log table, If it is an isolated file can you import the z_results_log table into the database with the other 30 tables?

If the z_results_log is the only file exported inside a database can you import that database log?
If you can import the above database log, can you save the z_results_log table to the database with the 30 tables?

I appreciate that you are unable to export/import all 31 tables and at the moment do not know the solution to the problem.

This is beyond exasperating…

I just tried to export the first 29 Tables/Views and now get this error…

MySQL said: Documentation
#1050 - Table ‘article_placement_VIEW’ already exists

So the first 28 Tables/Views get created, but one of the Views pukes?!

I don’t know which version of MAMP I am running, because the idiots don’t put the version anywhere in the app or the documentation, but I do see this in my browser…


phpMyAdmin - 2.11.7.1

MySQL client version: 5.0.41

[b]Is there some way that I could just physically copy the files out of MAMP?

Maybe at this path…[/b]


/Applications/MAMP/db/mysql/doubledee/

If so, I’m not sure what all File and Directories I’d need to copy?!

I was starting to answer these, but now I can’t even import the last two tables successfully…


z_result_log
z_result_log_VIEW

It appears that “z_result_log” gets imported okay, but then this associated View fails…


 MySQL said: Documentation
#1050 - Table 'z_result_log_VIEW' already exists 

So frustrating… :wall:

Debbie

Hi Debbie,

When I export a database I always select “Create table if not exists”.
Rename the localhost database to “db_whatever_YYMMDD”
Create a new blank “db_whatever” database.
Import the “db_whatever” database.

======================//======================

There are other options that have been frustrating - “Function to use when dumping data:” - there are three options, INSERT, UPDATE, REPLACE.

======================//======================

With different Online and Localhost PhpMyAdmin versions I have also had problems with different table structures,

Oh no, of course it’s not. Phpmyadmin is written entirely in php and it’s purpose is to be as much compatible as possible with all kinds of hosting environments but many shared hosts don’t allow access to mysqldump. Phpmyadmin has been known to have problems with large databases or objects other than tables like views, stored procedures, etc., especially the old versions, and you version is ancient - if you upgraded you might have more luck…

I don’t use Workbench for basic db maintanance because I use HeidiSQL, which I find much easier to use and it’s UI is really nice and friendly (beats phpMyAdmin hands down!). I use Workbench only for graphic data modelling and it works okay for a free tool. But it can be used for general maintenance as well, you’d have to try it out.

The question is not phpMyAdmin vs command line because there are many tools other than phpMyAdmin that are not command-line. Speedwise phpMyAdmin will never catch up to standalone db management tools because the overhead of php execution and browser rendering can’t be avoided.

I would recommend HeidiSQL, it’s not for Mac but supposedly it works fine via Wine and the author supports such installations. Also, there are some paid Mac-only programs that I haven’t tested.