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?!
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?!
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?
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.
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?!
[/ot]
On my new MBP I had the same database name, and it still isn’t working…
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!
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?! :-/
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…)
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.
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.
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
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.