SPECIAL OFFER: Only $69 for access to everything in OSTraining for 1 year! You save $75!  2
Join today and get access to 1,000's of books and videos. Learn WordPress, Drupal, Magento, Joomla and more! Sign up today!

Unable to import database

3 years 8 months ago #125254 by SamStamport
Unable to import database was created by SamStamport
I carefully removed all traces of Acquia Dev Desktop & Drush, ran CCleaner, and re-booted. I'm pretty sure I got everything.

I installed XAMPP. The default Drupal installation works fine. I'm now trying to import a backup database from my old ADD configuration, but keep getting "You probably tried to upload a file that is too large. Please refer to documentation for a workaround for this limit." from phpMyAdmin. Database file size is 17MB. I had no trouble uploading to ADD.

MySQL at ADD is 5.5.44-37.3 on XAMPP it is 5.0.11-dev - 20120503 according to phpMyAdmin. Could there be a problem with these different versions?

Please Log in to join the conversation.

3 years 8 months ago #125260 by Daniel Pickering
Replied by Daniel Pickering on topic Unable to import database
Basically by default the import size is 2MB which is not big enough for a drupal DB import. Check out the steps below for how to change the settings.

Thanks
Daniel

PHP XAMPP Guide wrote: Step 2 : Import the data

It’s the best solution to open new, clean database, and import the file in phpMyAdmin. Choose file from local folder and leave all settings as is. That should be it.

But if your file exceeded file size of 2MB (that is default value in xampp installation) than you need to tweak some out. Basically we will just increase the maximum upload file size.

Open your xampp install folder and go to php folder. With some text editor file (notepad++) open the file called php.ini (by type windows describe it as configuration settings) and edit the line (cca. 770, or press ctrl+f in notepad++ to find it):

post_max_size = 2M

instead 2M, put wanted maximum file size (16MB for example but not higher than 128M), after that go to this line:

max_execution_time = 30

instead 30, increase execution time that’s in seconds (90 for example), also you need to change this line:

max_input_time = 60

instead 60, increase input time in seconds (120 for example)

and top of all you have to change this line:

upload_max_filesize = 2M

instead of 2M, increase to 16M for example.

Save the file, restart apache and mysql in xampp and try to upload again, if you follow correctly you should be able to import files through 16MB (example)

You must pay attention to following:

If the upload_max_filesize is larger than post_max_size, you must increase post_max_size so that it is bigger than upload_max_size.

If the value of post_max_size is larger than memory_limit, you must increase memory_limit so that it is larger than post_max_size.

Please Log in to join the conversation.

3 years 8 months ago #125262 by SamStamport
Replied by SamStamport on topic Unable to import database
After making changes to php.ini and restarting the server & mysql I get the following messages when I try to import. Do I need to tweak php.ini more?

Error

Static analysis:

1 errors were found during analysis.

Unrecognized keyword. (near "ON" at position 25)

SQL query: Edit Edit

SET FOREIGN_KEY_CHECKS = ON;

MySQL said: Documentation
#2006 - MySQL server has gone away
Open new phpMyAdmin window
Warning in .\libraries\dbi\DBIMysqli.class.php#261
mysqli_query(): MySQL server has gone away

...

Please Log in to join the conversation.

3 years 8 months ago #125263 by Daniel Pickering
Replied by Daniel Pickering on topic Unable to import database

SamStamport wrote: mysqli_query(): MySQL server has gone away

...


Server timed out and closed the connection. To fix, check that “wait_timeout” mysql variable and then restart your machine completely.

Thanks
Daniel

Please Log in to join the conversation.

3 years 8 months ago #125264 by SamStamport
Replied by SamStamport on topic Unable to import database
Is innodb_lock_wait_timeout in C:\xampp\mysql\my.ini what you are referring to?

Please Log in to join the conversation.

3 years 8 months ago - 3 years 8 months ago #125266 by Daniel Pickering
Replied by Daniel Pickering on topic Unable to import database
Do you have a my.cnf file? According to google it should be the "my.ini" (Windows) that you edit.

Thanks
Daniel

Please Log in to join the conversation.

3 years 8 months ago #125300 by SamStamport
Replied by SamStamport on topic Unable to import database
Daniel,

OK. I increased innodb_lock_wait_timeout = 50 to 10000. The import page now says Max: 128MiB). My actual file size is 17.4 MB.

The backup was created using ADD so all the cache tables were dumped. I think it's stuck on trying to restore these tables. Is there any way I can edit them out of the backup file?

Thanks,
Sam

Please Log in to join the conversation.

3 years 8 months ago - 3 years 8 months ago #125303 by Valentin
Replied by Valentin on topic Unable to import database
Hi Sam,

Let's try to not change sensitive files and use instead the default phpMyAdmin that comes with XAMPP; but first, restart your computer and double check localhost works for you by going to your browser to http://localhost/. phpmyadmin should be available from there to easily manage database imports.

XAMPP is designed to work after a successful installation without tweaks.

That being said, dealing with internal settings that may comes from your operative system is way beyond our support scope. For this matter I would recommend to check with Windows support or a technician.

Good luck!

Kind regards,
Valentín

Follow us on Twitter - twitter.com/OSTraining
Like us on Facebook - facebook.com/ostraining

Please Log in to join the conversation.

3 years 8 months ago #125316 by SamStamport
Replied by SamStamport on topic Unable to import database
I re-booted my computer and went to http://localhost/. There was no link on the page at http://localhost/ for phpmyadmin.

Why would you conclude that this database import problem was a Windows problem? Import via phpMyAdmin worked just fine when I was using ADD. If I were to post this problem at one of the various Windows support forums they would say that it is not a Windows problem and that they could not help me.

Please Log in to join the conversation.

3 years 8 months ago #125319 by Valentin
Replied by Valentin on topic Unable to import database
Sorry for not being clear, Sam.
Probably editing manually some sensitive files affected the performance of XAMPP.

Follow us on Twitter - twitter.com/OSTraining
Like us on Facebook - facebook.com/ostraining

Please Log in to join the conversation.

3 years 8 months ago #125322 by SamStamport
Replied by SamStamport on topic Unable to import database
OK.

I agree that editing sensitive files is not a good idea but that is what Daniel suggested.

It's too bad XAMPP did not work straight out of the box.

Now what do I do?

Please Log in to join the conversation.

3 years 8 months ago #125325 by Valentin
Replied by Valentin on topic Unable to import database
In my experience, XAMPP works fine after a successful installation, however your situation is unique and honestly hard to tackle without having access to your computer.

My apologies for not being helpful on this one.

Follow us on Twitter - twitter.com/OSTraining
Like us on Facebook - facebook.com/ostraining

Please Log in to join the conversation.

3 years 8 months ago #125326 by SamStamport
Replied by SamStamport on topic Unable to import database
Is there some safe way for you to access my computer through the Internet to speed up solving this problem?

Please Log in to join the conversation.

3 years 8 months ago #125335 by Daniel Pickering
Replied by Daniel Pickering on topic Unable to import database
Hi Sam

SamStamport wrote: I agree that editing sensitive files is not a good idea but that is what Daniel suggested.


If your database is too big that is the only way to add it. You could install a fresh drupal setup and import the database in parts. The advice I gave you was directly from the XAMPP FAQs

I am afraid we do not offer direct system support. Do you have an online host ? We could help you if it was on a hosting platform you could give us user credentials to access the site and assist with it's configuration.

Thanks
Daniel

Please Log in to join the conversation.

3 years 8 months ago #125341 by SamStamport
Replied by SamStamport on topic Unable to import database
I have a fresh Drupal setup on XAMPP. I'm trying to import a backup taken with ADD from my old dev setup so I can pick up where I left off with ADD. How do I go about importing the database in parts with phpMyAdmin?

Could you recommend a free dev hosting platform where I can give you access?

Please Log in to join the conversation.

3 years 8 months ago #125342 by Daniel Pickering
Replied by Daniel Pickering on topic Unable to import database
Hi Sam

You can use phpMyAdmin to backup individual or groups of tables to then import into your new database.
I would give that a try.

Thanks
Daniel

Please Log in to join the conversation.

3 years 8 months ago #125344 by SamStamport
Replied by SamStamport on topic Unable to import database
This technique would require me to install ADD again. I'm not willing to do this.

Please Log in to join the conversation.

3 years 8 months ago #125345 by Daniel Pickering
Replied by Daniel Pickering on topic Unable to import database
Can't say I blame you sam.

Inside the XAMPP folder do you have a php.ini file?

Thanks
Daniel

Please Log in to join the conversation.

3 years 8 months ago #125346 by SamStamport
Replied by SamStamport on topic Unable to import database
No

Please Log in to join the conversation.

3 years 8 months ago #125352 by Daniel Pickering
Replied by Daniel Pickering on topic Unable to import database
Could you do a file search please ?



Thanks
Daniel

This message has an attachment image.
Please log in or register to see it.

Please Log in to join the conversation.

3 years 8 months ago #125360 by SamStamport
Replied by SamStamport on topic Unable to import database
Sorry. I thought you meant in the XAMPP root folder.

I found php.ini in xampp/apps/drupal/htdocs (this is the one I've modified per your recommendations).

There is another one in xampp/php

Please Log in to join the conversation.

3 years 8 months ago #125362 by Daniel Pickering
Replied by Daniel Pickering on topic Unable to import database

SamStamport wrote: There is another one in xampp/php


That is the one we need editing :)

Thanks
Daniel

Please Log in to join the conversation.

Join today and get access to 1,000's of books and videos. Learn WordPress, Drupal, Magento, Joomla and more! Sign up today!