Category: DATABASE

Sometimes you’ll end up with larger files that, when you try to run a simple find / replace operation, will error out or not start because there is insufficient memory.

A solution I found in Ubuntu with the sed command is as follows:

sed -i "s|search|replace|g" yourFile.txt

I’m using | as separator to support URLs. This is useful when replacing URLs in SQL files when migrating from one domain to another or fixing an SSL mixed content issue.

Recently had to fix a WordPress database with entries having a duplicate key.

They were 1974 entries with the same key.

And to fix that, as suggested by every resource online, you need to increment each key by one.

Good luck doing that manually.

Instead, in Ubuntu that can be done in a matter of seconds with Perl.

Use the following command:

perl -pi.bak -pe 'BEGIN{$A=STARTING_VALUE_INTEGER;} s/KEY_TO_REPLACE/$A++/ge' yourFile.sql

That’s it.

-p processes and prints <> line by line.

-i activates in-place editing. Files are backed up using the .bak extension.