Exporting database with lot of tables

[oparrish2]

In a database with many, many tables - just loading the Export tab creates an error. This is a similar request to #12153 which is for filtering the export tables. My enhancement request is for pagination of the tables so as to avoid memory errors.

Steps to reproduce

1. Create a lot of tables, in my case ~7500. Name them test1 through test7500
2. Navigate to Export tab
3. Observe error

PHP Fatal error: Allowed memory size of 134217728 bytes exhausted (tried to allocate 32 bytes) in /var/www/phpMyAdmin/htdocs/phpMyAdmin/libraries/dbi/DBIMysqli.php on line 299

Also observed this error in a database with ~1100 tables & views:

PHP Fatal error: Allowed memory size of 134217728 bytes exhausted (tried to allocate 4402939 bytes) in /var/www/phpMyAdmin/htdocs/phpMyAdmin/libraries/display_export.lib.php on line 1014

Expected behaviour

View a list of tables to export with no memory error

Actual behaviour

php runs out of memory using default php.ini with 128 MB memory limit

phpMyAdmin version:
4.6.6

[mynetx]

Where exactly would you see the pagination appear on the Export tab? Can you take a screenshot and draw on it? :)

[Achilles-96]

Can you clarify if I am understanding this right?
When you click export tab, it should take you to export page as it does for a db with less tables. The tables are listed when you click Custom - display all possible options .

• Do you think we should use pagination here?
• Is the error due to getting information about all tables at once by php (because exporting same db using command line works without errors).

[WanWizard]

After upgrading to 4.7.0, I experence the same problem.

[Tue Apr 11 15:27:37.579834 2017] [:error] [pid 13205] [client 172.17.1.11:62935] PHP Fatal error:  Allowed memory size of 134217728 bytes exhausted (tried to allocate 87 bytes) in /data/exite/webroot/phpmyadmin.hosting.exite.eu/libraries/dbi/DBIMysqli.php on line 221
[Tue Apr 11 15:27:37.579863 2017] [:error] [pid 13205] [client 172.17.1.11:62935] PHP Stack trace:
[Tue Apr 11 15:27:37.579868 2017] [:error] [pid 13205] [client 172.17.1.11:62935] PHP   1. {main}() /data/exite/webroot/phpmyadmin.hosting.exite.eu/server_export.php:0
[Tue Apr 11 15:27:37.579872 2017] [:error] [pid 13205] [client 172.17.1.11:62935] PHP   2. PMA_getExportDisplay() /data/exite/webroot/phpmyadmin.hosting.exite.eu/server_export.php:48
[Tue Apr 11 15:27:37.579876 2017] [:error] [pid 13205] [client 172.17.1.11:62935] PHP   3. PMA_getHtmlForExportOptions() /data/exite/webroot/phpmyadmin.hosting.exite.eu/libraries/display_export.lib.php:1097
[Tue Apr 11 15:27:37.579880 2017] [:error] [pid 13205] [client 172.17.1.11:62935] PHP   4. PMA_getHtmlForAliasModalDialog() /data/exite/webroot/phpmyadmin.hosting.exite.eu/libraries/display_export.lib.php:880
[Tue Apr 11 15:27:37.579884 2017] [:error] [pid 13205] [client 172.17.1.11:62935] PHP   5. PMA\\libraries\\DatabaseInterface->getColumnsFull() /data/exite/webroot/phpmyadmin.hosting.exite.eu/libraries/display_export.lib.php:912
[Tue Apr 11 15:27:37.579888 2017] [:error] [pid 13205] [client 172.17.1.11:62935] PHP   6. PMA\\libraries\\DatabaseInterface->fetchResult() /data/exite/webroot/phpmyadmin.hosting.exite.eu/libraries/DatabaseInterface.php:1066
[Tue Apr 11 15:27:37.579891 2017] [:error] [pid 13205] [client 172.17.1.11:62935] PHP   7. PMA\\libraries\\DatabaseInterface->fetchAssoc() /data/exite/webroot/phpmyadmin.hosting.exite.eu/libraries/DatabaseInterface.php:1665
[Tue Apr 11 15:27:37.579894 2017] [:error] [pid 13205] [client 172.17.1.11:62935] PHP   8. PMA\\libraries\\dbi\\DBIMysqli->fetchAssoc() /data/exite/webroot/phpmyadmin.hosting.exite.eu/libraries/DatabaseInterface.php:2430
[Tue Apr 11 15:27:37.579908 2017] [:error] [pid 13205] [client 172.17.1.11:62935] PHP   9. mysqli_fetch_array() /data/exite/webroot/phpmyadmin.hosting.exite.eu/libraries/dbi/DBIMysqli.php:221

but it is intermittent. It may be related to an authentication issue we have since the upgrade, which constantly throws me out (sometimes directly, sometimes are a few clicks). We use cookie auth, with the same config for at least 3 years. Previous version running was 4.0.x.

We have 3 replicated mariadb instances behind a load balancer, but it doesn't matter which IP I pick as target (all 4 are configured as servers).

edit: if I increase the memory to 256Mb, the export page does load after quite some time looking at the "Loading" popup, and then displays the page with a second popup, which says "Input variables exceeded 1000. To increase the limit change max_input_vars in php.ini".

I did some digging, this seems to be caused by the the "Rename exported databases/tables/columns" feature, which in a hidden modal lists all columns in all tables in all database as a text input field, and we clearly have more than 1000 columns over all databases/tables. All these input fields are part of the same main form on the export page.

This feature is clearly unusable in setups with a large number of databases/tables/columns...

[khalidsharara]

@jublo, @Achilles-96 in response to your comments from Feb 15th & March 11th:

As soon as the export tab is clicked, the following pink box error appears:

And the same error message is printed in the logs:
PHP Fatal error: Allowed memory size of 134217728 bytes exhausted...

This leads us to believe the error is due to php getting information about all the tables at once.

[WanWizard]

@kasunchathuranga correct, see my edit above.

[WanWizard]

More debugging confirmed it is display_export.lib.php, PMA_getHtmlForAliasModalDialog() that is causing the problem.

For now I've worked around this issue by disabling the entire div by adding this to the top of the function:

return "";

so I have at least a working export again.

[WanWizard]

@nijel please change this from "enhancement" to "bug".

[shomaker]

Any thoughts from the phpMyAdmin dev community about the proper fix for this? Is it pagination or something else?

Is there any negative impact of @WanWizard 's workaround besides losing the ability to rename tables and columns as part of the export?

[nijel]

IMHO best approach would be to paginate the results in case there are too many and add AJAX based search, so that it's possible to quickly find table user is looking for.