problems importing mysql dump

Problems with the Windows version of XAMPP, questions, comments, and anything related.

problems importing mysql dump

Postby JRTphotog » 10. October 2011 21:36

Win 7 64
xampp 1.7.7

Hi and thank you for your help, warning! mysql noob... :shock:

before uninstalling a previous version of xampp I exported all of my databases in one mysql dump or at least I think I did. Now with fresh install of xampp 1.7.7 & mysql when I try to import the localhost.sql file I am hitting all kinds of roadblocks.

I am getting errors saying that "cds database already exists" and "information schema cannot be changed" ect when importing. Hindsight being 20/20 I wish I would have exported each database individually instead of checking all of them. I hope someone has seen this or understands what I'm saying.

major kudos for whoever can solve this one as I am screwed and have lost everything(2 complete sites, one of them using joomla) if I can't fix this. :cry:

thanks
Last edited by JRTphotog on 11. October 2011 00:52, edited 1 time in total.
JRTphotog
 
Posts: 12
Joined: 10. October 2011 08:02

Re: problems importing mysql dump

Postby Sharley » 10. October 2011 22:21

I have your version details from your previous post but it pays to include it/them as the first lines of any post in case it is not me who answers. :)

I have tested this only on a database sql export file that gave me issues but were fixed after editing the sql file manually to remove the database entries that gave me problems.

How did you create this dump file?
So did the dump create an sql file?

You dumped the MySQL system files, which as indicated, already exist.

So you can drag holding the Ctrl key the whole xampp installation folder to create a copy of (backup) file.

Now open \xampp\mysql\data folder and rename the system files (folders that are not named as your own databases or move them out of that folder so they don't exist in the data folder:
cdcol
mysql
performance_schema
test
webauth


You can also do the same for your own database folders as your dump should replace them if this works.

Now try and import your dump again.

Don't forget to restart MySQL and Apache.

See how you go as this method means you don't lose anything, hopefully, and will allow you to revert if required. ;)

Good luck. :)
User avatar
Sharley
AF Moderator
 
Posts: 3316
Joined: 03. October 2008 05:10
Location: Yeppoon, Australia Time Zone: GMT/UTC+10
Operating System: Win 7 Pro 32bit/XP Pro SP3

Re: problems importing mysql dump

Postby JRTphotog » 10. October 2011 23:59

:shock:
Original dump was done in phpmyadmin>export>select all>go

tried your above suggestions with renaming folders in sql/data,...

without the mysql/data/mysql folder: tried to launch mysql with xampp control panel, "running" flashes once then nothing
mysql error log below:
111010 15:49:08 InnoDB: Waiting for the background threads to start
111010 15:49:09 InnoDB: 1.1.8 started; log sequence number 1595685
111010 15:49:09 [ERROR] Fatal error: Can't open and lock privilege tables: Table 'mysql.host' doesn't exist

added mysql folder back to mysql/data: launced via control panel, tried to launch phpmyadmin admin via control panel, browser launches and outputs below error:

Error
MySQL said:

#2002 - The server is not responding (or the local MySQL server's socket is not correctly configured)
Connection for controluser as defined in your configuration failed.


mysql error log:


111010 15:51:50 InnoDB: 1.1.8 started; log sequence number 1595685
111010 15:51:50 [ERROR] Native table 'performance_schema'.'events_waits_current' has the wrong structure
111010 15:51:50 [ERROR] Native table 'performance_schema'.'events_waits_history' has the wrong structure
111010 15:51:50 [ERROR] Native table 'performance_schema'.'events_waits_history_long' has the wrong structure
111010 15:51:50 [ERROR] Native table 'performance_schema'.'setup_consumers' has the wrong structure
111010 15:51:50 [ERROR] Native table 'performance_schema'.'setup_instruments' has the wrong structure
111010 15:51:50 [ERROR] Native table 'performance_schema'.'setup_timers' has the wrong structure
111010 15:51:50 [ERROR] Native table 'performance_schema'.'performance_timers' has the wrong structure
111010 15:51:50 [ERROR] Native table 'performance_schema'.'threads' has the wrong structure
111010 15:51:50 [ERROR] Native table 'performance_schema'.'events_waits_summary_by_thread_by_event_name' has the wrong structure
111010 15:51:50 [ERROR] Native table 'performance_schema'.'events_waits_summary_by_instance' has the wrong structure
111010 15:51:50 [ERROR] Native table 'performance_schema'.'events_waits_summary_global_by_event_name' has the wrong structure
111010 15:51:50 [ERROR] Native table 'performance_schema'.'file_summary_by_event_name' has the wrong structure
111010 15:51:50 [ERROR] Native table 'performance_schema'.'file_summary_by_instance' has the wrong structure
111010 15:51:50 [ERROR] Native table 'performance_schema'.'mutex_instances' has the wrong structure
111010 15:51:50 [ERROR] Native table 'performance_schema'.'rwlock_instances' has the wrong structure
111010 15:51:50 [ERROR] Native table 'performance_schema'.'cond_instances' has the wrong structure
111010 15:51:50 [ERROR] Native table 'performance_schema'.'file_instances' has the wrong structure
111010 15:51:50 [Note] Event Scheduler: Loaded 0 events
111010 15:51:50 [Note] mysql\bin\mysqld.exe: ready for connections.
Version: '5.5.16' socket: '' port: 3306 MySQL Community Server (GPL)


argh! :twisted:
JRTphotog
 
Posts: 12
Joined: 10. October 2011 08:02

Re: problems importing mysql dump

Postby Sharley » 11. October 2011 00:35

Moved this MySQL Topic here because it is still specific to XAMPP for Windows. 8)

The System databases fro your old XAMPP installation are not compativble with this 1.7.7 version.


JRTphotog wrote:tried your above suggestions with renaming folders in sql/data,...
This method was worth a try but not if you are using MySQL via the GUI but rather by the CLI.

JRTphotog wrote:#2002 - The server is not responding (or the local MySQL server's socket is not correctly configured)
Connection for controluser as defined in your configuration failed.
This error or part of it can be a fixed here:
viewtopic.php?p=186480#p186480

Ok, save the database folders that have a name that reflect those when you created the databases in phpMyAdmin not the above mentioned system databases - we will replace those perhaps corrupted system databases when you reinstall.


Now uninstall XAMPP saving (backing up) your own files and folders elsewhere if you need to.

Now reinstall XAMPP (sorry, again) might be the quickest solution to get the MySQL system databases back to normal and then copy back to the mysql/data folder your own database folders - these should now be showing as additional to the installed system database folders.

Return you backed up files and folders and now try and Start Apache and MySQL (no ticks in services) to test your installation is good then phpMyAdmin and see how many more errors we have created. :)

Good luck. :)
User avatar
Sharley
AF Moderator
 
Posts: 3316
Joined: 03. October 2008 05:10
Location: Yeppoon, Australia Time Zone: GMT/UTC+10
Operating System: Win 7 Pro 32bit/XP Pro SP3

Re: problems importing mysql dump

Postby Sharley » 11. October 2011 01:00

Sorry, there may be a solution for the incompatible earlier version you dumped and that is to import your dump, close phpMyAdmin and then type
Code: Select all
mysql_upgrade.exe --force
in the mysql/bin folder using the xampp-shell in CP3.

This should correct the older databases.

Restart Apache and MySQL and then go to MySQL Admin.

BTW, if you get the red warning for a root password then that is set using the http://loacalhost Security menu item.

I must be tired. :shock:
User avatar
Sharley
AF Moderator
 
Posts: 3316
Joined: 03. October 2008 05:10
Location: Yeppoon, Australia Time Zone: GMT/UTC+10
Operating System: Win 7 Pro 32bit/XP Pro SP3

Re: problems importing mysql dump

Postby JRTphotog » 11. October 2011 01:37

had to uninstall twice to clear errors. moved vhosts file over with no issues. back to clean working version.

I dont have any db's to move over as they were lost when I originally uninstalled xampp to upgrade. All I have is the .txt file that was exported from phpmyadmin before the unistall.

When I try to import the local.sql I have from the original dump I get this output from phpmyadmin

Error
SQL query:
--
-- Database: `cdcol`
--
CREATE DATABASE `cdcol` DEFAULT CHARACTER SET latin1 COLLATE latin1_general_ci;
MySQL said:
#1007 - Can't create database 'cdcol'; database exists

I'm back to square 1, xampp working but unable to restore my old databases. You mentioned the first technique works via cli? If you talk me through that I can give it a shot unless you have an easier fix.
JRTphotog
 
Posts: 12
Joined: 10. October 2011 08:02

Re: problems importing mysql dump

Postby Sharley » 11. October 2011 01:46

You can ignore that error, close phpMyAdmin and run with force the mysql_upgrade file.

Restart MySQL and see if you have databases.

Check in the data folder and then run phpMyAdmin again.

I just replicated your issue using an All 1.7.4 dump to an sql file, imported it in 1.77 and had the same error and followed the above procedure but on investigation my own databases were not imported.

For my own interest I will try this with a huge collection of databases I have in 1.6.8 and see if it cooperates.

No, for some reason that did not import my own databases in the sql file - I need to investigate this further as I simply used the default settings offered by phpMyAdmin, which may need tweaking.
User avatar
Sharley
AF Moderator
 
Posts: 3316
Joined: 03. October 2008 05:10
Location: Yeppoon, Australia Time Zone: GMT/UTC+10
Operating System: Win 7 Pro 32bit/XP Pro SP3

Re: problems importing mysql dump

Postby JRTphotog » 11. October 2011 03:02

thxs i look forward to hearing the solution
JRTphotog
 
Posts: 12
Joined: 10. October 2011 08:02

Re: problems importing mysql dump

Postby Sharley » 11. October 2011 05:00

Try this using the CP3 xampp-shell.

First copy your sql dump file in the xampp installation folder.

Make sure MySQL is running.

Type:
Code: Select all
mysql -u root -p < yoursqldumpfilename.sql --force

You will see it reporting errors but will continue to scroll till it has imported all your databases and skips the system databases and any other databases like the demo cdcol database, that caused it to stop before it had finished previously when using phpMyAdmin.

Check the data folder and you should see your databases.

Now type in the shell:
Code: Select all
mysql_upgrade.exe --force
to finish this exercise.

Restart MySQL

Open phpMyAdmin and you should also see your database where you can work on them if required.

Check with the scripts that use the databases ie Joomla all access the databases when using your browser.

Phew :D

Let me know how it goes, please.

:idea: Only tick the databases you want to dump instead of selecting All so you don't get this issue with system files and XAMPP demo files again - but now at least you know how to do a full database dump if required. :)

To select the databases required in Export Tab select Custom - display all possible options, usually the remaining defaults should be fine.

If your dump file is over 8M and under 100M then you need to edit the php.ini file's 8M limit.
Code: Select all
post_max_size = 100M


Apologies for the delays etc.

Best wishes and good luck. :)
User avatar
Sharley
AF Moderator
 
Posts: 3316
Joined: 03. October 2008 05:10
Location: Yeppoon, Australia Time Zone: GMT/UTC+10
Operating System: Win 7 Pro 32bit/XP Pro SP3

Re: problems importing mysql dump

Postby JRTphotog » 12. October 2011 02:10

I tried everything you suggested and it seemed to be working but after closer examination I don't think my .sql file is complete. I was reading through the code, 3000+ lines, and I noticed "create db cdcol" and "create db information schema" but never saw instructions to create my other db's. I saw mention many times of the tables from my previous db's in the "COLUMNS" and "KEY COLUMN USAGE" under the information schema creation but nothing specific to creating additional db's to hold those tables. It seems the file is still creating tables for information schema when it ends with the below:
Code: Select all
<br />
<b>Fatal error</b>:  Maximum execution time of 300 seconds exceeded in <b>C:\TestingServer\xampp\phpMyAdmin\libraries\dbi\mysqli.dbi.lib.php</b> on line <b>176</b><br />

that doesn't look good to me but again :shock: sql noob

Unless there is some way to harvest the table data from the "COLUMNS" and "KEY COLUMN USAGE" in the information schema data then I think throwing in the towel is applicable.

Maybe I should have tested the backup before deleting. I read somewhere that a backup isn't a valid backup until it is tested, good advice.

At this point I may have spent as much time trying to recover the db as it would take to rebuild from scratch, another priceless lesson.

I'd love to hear your thoughts
JRTphotog
 
Posts: 12
Joined: 10. October 2011 08:02

Re: problems importing mysql dump

Postby Sharley » 12. October 2011 02:26

You could try in your sql file to add the same lines to create a database that is present for cdcol at the start of each of your other database entries - in your text editor use search then copy and paste to speed this up.

Not sure if ir will work but here is an example from the file I used above.
Code: Select all
--
-- Database: `cdcol`
--
CREATE DATABASE `cdcol` DEFAULT CHARACTER SET latin1 COLLATE latin1_general_ci;
USE `cdcol`;

-- --------------------------------------------------------

--
-- Table structure for table `cds`
--
find your other databases which may start like so
Code: Select all
--
-- Database: `bb3`
--
minus the CREATE DATABASES, so just add that line but change the USE line to the database name you are editing.
Code: Select all
--
-- Database: `bb3`
--
CREATE DATABASE `bb3` DEFAULT CHARACTER SET utf8 COLLATE utf8_bin;
USE `bb3`;

-- --------------------------------------------------------
--
-- Table structure for table `acl_groups`
--
Don't edit the information_schema database entries.

Best I can offer to recover your databases.
Good luck. :)
User avatar
Sharley
AF Moderator
 
Posts: 3316
Joined: 03. October 2008 05:10
Location: Yeppoon, Australia Time Zone: GMT/UTC+10
Operating System: Win 7 Pro 32bit/XP Pro SP3

Re: problems importing mysql dump

Postby JRTphotog » 13. October 2011 03:36

I dont think I have enough data in my sql file. It seems to begin with cdcol db, then move to information_schema. As it appears that the information_schema was not completed, there is no specific mention of additional db's before the error.

I have to be done with recovery attempts and work on rebuilding db's.

Thank you for all your help, you have provided quite the service. It appears the fail was on my part, live and learn.

thanks again...
JRTphotog
 
Posts: 12
Joined: 10. October 2011 08:02

Re: problems importing mysql dump

Postby Sharley » 13. October 2011 03:44

Your welcome and thanks for the feed back for which was a difficult exercise that unfortunately did not have a positive result, but was not due in any part to the lack of effort on your part. 8)

Good luck and best wishes. :)
User avatar
Sharley
AF Moderator
 
Posts: 3316
Joined: 03. October 2008 05:10
Location: Yeppoon, Australia Time Zone: GMT/UTC+10
Operating System: Win 7 Pro 32bit/XP Pro SP3

Re: problems importing mysql dump

Postby Mesmerized » 18. October 2011 13:42

I am getting the same error and tried about everything:
"cds database already exists" and "information schema cannot be changed" when I import.

Can someone suggest a solution. I have been trying and trying but still get the same error
Mesmerized
 
Posts: 1
Joined: 18. October 2011 13:38
Operating System: Windows 7

Re: problems importing mysql dump

Postby JonB » 18. October 2011 15:11

UMMM -

Don't import 'ALL' the databases??? :shock:

just a thought
8)
User avatar
JonB
AF Moderator
 
Posts: 3210
Joined: 12. April 2010 16:41
Location: Land of the Blazing Sun
Operating System: Windows XP/7 - Fedora 15 1.7.7

Next

Return to XAMPP for Windows

Who is online

Users browsing this forum: No registered users and 121 guests