This post is also available in: 日本語

When exporting a lot of data with phpMyAdmin, for some reason, the data may only be output halfway through.
I wonder why.
By the way, that server uses version 3.3.10.5 of phpMyAdmin.

The beginning of failure.

Just a moment ago, I made a mistake in deleting data in the database and deleted everything I needed.
I had made a quick backup, so I put it back, and out of the 48,000 total, about 2,000 were not back.

I tried to put it back again without knowing what it meant…. It didn’t come back after all.
Backup data, MySQL, so it’s a dump of the SQL export, but if you check it, it’s not exported in the first place.
Why?
I’ve erased what’s there.
What can I do about it?
Let’s see… 2,000 more to fill out… or…
I’m in a cold sweat.

The backup was exported from phpMyAdmin only the relevant table from that file, I don’t know why but it’s broken in the middle, and the INSERT statement is complete so there was no error or anything when importing it.
It’s good to know that the exported data is usually output in the order in which it was inserted, so the most recent data is missing and the screen is obviously lonely.
Safe.
It’s not safe, though.

Re.

When I cut out only the dump of a pertinent table from the slightly huge dump because I backed it up with the mysqldump command to make sure, it is not cut.
It’s all there.
I did.
I did.
The cold sweat seems to have dried up with a refreshing breeze.

When the dump file which I cut out was zipped and imported from phpMyAdmin without being disciplined, it is natural because there is a lot of data volume, but the Internal Server error of the end which took time endlessly.
What the hell?
When I checked the table, I found all the data was there, although the screen was an error.
This time, I carefully deleted the data and completed the work.

But why is it cut?
I wanted to try a lot of things, but because it was a lot of data deletion work, I already have only a small amount of data, so I can’t do a repeat test.
I tried downloading the data back in a different environment, but it was downloaded without interruption, so I’m more and more confused.

As a conclusion, I don’t know, but when there is a lot of data (I don’t know how much is too much), it might be safer to output it by command.
I’m sorry that I’m writing this at length because I can’t identify the cause.

Method of Evasion

It seems that there is a setting to export phpMyAdmin to the server directory instead of downloading it in the browser.
You may also be able to import files on the server to avoid timeouts and Internal Server errors due to too much data.

Please describe the setting in the following part of the configuration file  config.inc.php.

/* * Directories for saving/loading files from server */
$cfg['UploadDir'] = ''; 
$cfg['SaveDir'] = '';

UploadDir
SaveDir

You may feel more comfortable in this setting.
It’s easier to download it in a browser, though.

In addition, the command for dumping out/importing again is as follows.
The dump is compressed as a gzip and saved with a dated filename.
Importing is just a matter of making SQL the input.

mysqldump --default-character-set=utf8 -Q -e -h DB_SERVER_NAME -u DB_USER_NAME -pDB_PASSWORD DATABASE_NAME > DUMP_FILE.sql

mysql --default-character-set=utf8 -h DB_SERVER_NAME -u DB_USER_NAME -pDB_PASSWORD DATABASE_NAME < DUMP_FILE.sql

※The character set settings, of course, should match the database settings。

There are a lot of servers that don’t use SSH, but it’s more convenient and safe to use it in case of emergencies.
Be careful not to shorten the life of your database when updating it.

I said to myself.
I don’t know how many times though.

This post is also available in: 日本語