Database Backup Problems

[#1]

---

@bobpullen wrote:

@spraxyt, very odd. Have you tried reinstalling the app?

---

I get the warning messages with Adminer 4.3.1 and with a separate installation of Adminer 4.3.0 using both the Fastcgi and Apache PHP5.6 API.. Access to Information Schema content seems more restrictive now than it used to be with occurrences of the unhelpful system error log page. It's possible Hostopia have tightened things up, The presence of those warnings doesn't appear to affect normal day-to-day use of the database.

With regard to zero-length database exports produced by phpMyAdmin I think this is file-size/memory/execution time related. I recalled this used to happen with End User exports from Rumpus and Humbug on in-house CCGI. We had to export subsets of the database to avoid the problem.

On Hostopia using phpMyAdmin to export parts of the database works OK. However Adminer can export my 26MB database.into a single file.

[#2]

---

@bobpullen wrote:

Any idea when a response from Plusnet’s contractors (your comment here) can be expected? Or in view of export working on your installation is that no longer a line we are following?

I hadn't raised anything to them, however I have now opened a support ticket asking them to look at what's up with your phpMyAdmin instance. I wouldn't hold your breath for a definitive solution/explanation though 

Our vendor has come back to us to suggest that they're able to export your database successfully if it's zipped or gzipped using the custom option in the Export tab. I'm assuming they're satisfied with that as a solution because the support ticket has since been closed. As there are multiple alternative methods of backing up your data, I don't think we've much to gain from pushing for further explanation.

[#3]

I’m still chewing on the last few posts, and there are a couple more things I want to explore before this thread wraps up, but it looks as though we may have a workaround if not a complete solution.

Thanks to Bob for PM, that link works.

[#4]

I’ve spent a while chewing on all that has gone before, and we seem to have some viable work arounds if not a complete explanation.

To Bob Pullen:

Thanks for trying the phpMyAdmin reinstall anyway, and at least I now know that a reinstall doesn’t jeopardise data.

Thanks for the backup at ‘db_bob’ too.

Am I mistaken, I wonder, in suspecting that the “missing” export.php file is closely related to Hostopia having ‘some funky symlinking or something’ mentioned here?

I’m trying not to think about why selecting .zip or .gz when doing a myPhpAdmin Export bypasses the “missing” export.php problem, but it does undoubtedly yielding a pukka looking .sql backup file when decompressed. Many thanks again.

Spraxyt:

I regret that I struggled to get Adminer running on my site and failed, so I haven’t tried it: undoubtedly my bungling. However I did find that I have MySQL Adminidtrator, which I used to make backups of the database last July, on one of my desktops. This works too, but I noticed one interesting thing. The host setting stored in it from long ago uses the address;

sql5c51a.megasqlservers.eu

not

ccgi.[MyUserName].plus.com.c51.previewmysite.eu/

… which there has been so much discussion here.

I mention that only for the sake of completeness and in case it throws any light on the situation for others more learned than myself.

Both:

Two final points:

The only thing that still troubles me is that different backups have different sizes:

Bob’s backup stored on server:                                                                                  34.68Mb

Mine using MySQL Administrator on an old XP machine:                                            35.52

Mine using MySQL Administrator on a Win7 machine:                                                38.15

Export from MyPhpAdmin when unpacked from .zip:                                                   35.60

Is this just to do with different set-ups using different metrics I wonder? I have AMPPS installed, so at the weekend I can try importing different backup files to a localhost version of myPhpAdmin to see if there are any obvious discrepancies in table size.

In the meantime, I am very grateful for the time, patience, and trouble that you’ve both put into this problem. Without it I’d certainly still have my head in my hands.

Moderator's note by Dick (Strat) Post released from spam filter.

[#5]

---

@TonyN wrote:

The host setting stored in it from long ago uses the address;
sql5c51a.megasqlservers.eu 

not

ccgi.[MyUserName].plus.com.c51.previewmysite.eu/

That's to be expected. The hostname for the sql servers is sql5c51a.megasqlservers.eu. You can also use the old Plusnet aliases rumpus.plus.net and humbug.plus.net. This is the sort of thing you should be entering when logging into Adminer:

The only thing that still troubles me is that different backups have different sizes:

Bob’s backup stored on server:                                                                                  34.68Mb

Mine using MySQL Administrator on an old XP machine:                                            35.52

Mine using MySQL Administrator on a Win7 machine:                                                38.15

Export from MyPhpAdmin when unpacked from .zip:                                                   35.60

Is this just to do with different set-ups using different metrics I wonder?

When were your backups taken? Remember, I both repaired and optimised your database which could easily have altered the file size.

Another thing to consider is how the databases have been exported. Try looking at the raw data in a text editor. There's likely to be headers in some of the exports that isn't in others, e.g:

-- Adminer 4.1.0 MySQL dump

Depending on the options you selected for exporting, some files will contain SQL instructions for dropping/creating/inserting into tables too, whereas others might not, e.g:

DROP DATABASE IF EXISTS `database_name`;
CREATE DATABASE `database_name` /*!40100 DEFAULT CHARACTER SET latin1 */;
USE `database_name`;

[#6]

Enabling PHP error logging into a file.has provided additional diagnostic information for the phpMyAdmin export problems. The log entries confirm the problems are due to exceeding the process memory limit which is 128MB.

When doing a phpMyAdmin export and running the Apache module version of PHP the log shows

[25-Apr-2017 22:10:53 Europe/London] PHP Fatal error:
  Allowed memory size of 134217728 bytes exhausted
  (tried to allocate 36127571 bytes) in
  /services/websiteos/phpmyadmin/phpMyAdmin-4.0.5-
  all-languages/libraries/OutputBuffering.class.php
  on line 138

[25-Apr-2017 22:10:53 Europe/London] PHP Fatal error:
  Unknown: Cannot use output buffering in output
  buffering display handlers in Unknown on line 0

In this case I think I requested plain text output. I assume the first fatal error results in a child process exiting with the second fatal error reflecting the parent process unsuccessfully trying to work out what was going on.

Doing a similar test with the fastcgi version of PHP the log shows

[27-Apr-2017 19:18:54 Europe/London] PHP Fatal error:
   Out of memory (allocated 39059456) (tried to allocate
  33284096 bytes) in
  /services/websiteos/phpmyadmin/phpMyAdmin-4.0.5-
  all- languages/export.php on line 342

The request in this case was for gzipped output which might explain the error occurring in a different place. Interestingly the fatal error is in export.php, so it's definitely not missing.

[#7]

Hi Spraxyt

That’s comes as a bit of a surprise as in my case I very much doubt if size can be a problem.

My database backup files are approx. 35Mb, and this seems reasonable as my blog used relatively few images or sound/video files although there are probably about ½ million words of text in the form of posts and comments.

There is a great deal of material stored on the site that does have a heavy storage overhead, but it is accessed by links from the blog and it is not in …/public/blog/ where all my Wordpress files are. Incidentaly my wp-content folder amounts to only about 57Mb and total for all the blog files is about 65Mb.

This evening I’ll try to unearth the log for my site and see if there are any similar entries.

[#8]

No luck with the phpMyAdmin error log unfortunately.

If I go into Status and then Monitor, so far as I can make out I am supposed to set the log to Output to Table, but I’m d****d if I can see how.

[#9]

To get the phpMyAdmin dump information I didn't change any settings within that application. What I did was add the following directives to my .htaccess file in /public

php_flag log_errors 1
php_value error_log /services/webpages/c/c/ccgi.username.plus.com/public/php_error.log

Obviously change username as appropriate. These directives should cause any PHP errors to be written to the log file defined. If the file doesn't exist it will be created the first time it is used.

To check that logging works I suggest creating syntax_error.php in /public containing

<?php
// test file with PHP syntax error
echo "Hello everyone<br>\n";
$yes = "yes"
$no = "no";
echo "The answer is $yes and $no!";
?>

Line 4 lacks a terminating semi-colon causing a syntax error to be reported on line 5 when you browse to that file.

To view the error log you will have to download it using FTP and open it in a text reader.

Assuming error logging is working, log into the hosting control panel and launch phpMyAdmin as normal. Then try exporting the database. Assuming this fails, download and view php_error.log again and check for new messages.