search

wp-cli / db-command

Performs basic database operations using credentials stored in wp-config.php.
MIT License
71 stars 58 forks source link

Export to STDOUT loads entire DB into memory at once #195

Closed jcatello closed 3 years ago

jcatello commented 3 years ago

Since upgrading to 2.5 seeing a lot of memory issues specifically with db export when exporting to STDOUT

# wp cli version
WP-CLI 2.5.0
# wp ${WPCLIFLAGS} db export - --path="${DOCROOT}" --quiet --single-transaction --quick --lock-tables=false --max_allowed_packet=1G --default-character-set=utf8mb4 --routines --triggers --events | wp ${WPCLIFLAGS} --quiet db import - --path="${DOCROOT}" --ssh="${REMOTEHOST}":"${REMOTEPORT}" --quiet --force --max_allowed_packet=1G
PHP Fatal error:  Allowed memory size of 1073741824 bytes exhausted (tried to allocate 534773792 bytes) in phar:///usr/bin/wp/vendor/wp-cli/wp-cli/php/utils.php on line 546

^ This failed within 10 seconds of running it, the database is very large.. 20GB but when exporting with 2.4.0 it has no issues.

# php wp-cli-2.4.0.phar ${WPCLIFLAGS} db export - --path="${DOCROOT}" --quiet --single-transaction --quick --lock-tables=false --max_allowed_packet=1G --default-character-set=utf8mb4 --routines --triggers --events | wp ${WPCLIFLAGS} --quiet db import - --path="${DOCROOT}" --ssh="${REMOTEHOST}":"${REMOTEPORT}" --quiet --force --max_allowed_packet=1G

Exporting to file is fine:

# wp db export
Success: Exported to 'wp2656528262db_28193-2021-05-20-6848253.sql'.

STDOUT:

# wp db export -
PHP Fatal error:  Allowed memory size of 1342177280 bytes exhausted (tried to allocate 1071644704 bytes) in phar:///usr/bin/wp/vendor/wp-cli/wp-cli/php/utils.php on line 546
schlessera commented 3 years ago

I know what the problem is: I'm using stream_get_contents() on the stream to fetch the entire STDOUT output at once, and in that case, the STDOUT output happens to be the entire database content.

What the code should do instead is chunk the stream_get_contents() and yield it to the caller. However, the caller will then be responsible to iterate over the return value, so it's probably a breaking change...

schlessera commented 3 years ago

As a work-around in the mean time if you need to provide the DB to the STDIN of another tool, you can do so by exporting into a file first and then using cat to send that file to STDOUT:

wp db export /tmp/my-db.sql
cat /tmp/my-db.sql | tool-that-needs-stdin
rm -f /tmp/my-db.sql