Plesk pre-migration error

Neil White MySQL, Server Issues

Plesk pre-migration check finishes with an error:

Set variable innodb_strict_mode to OFF on target to avoid the migration errors

Strict mode is enabled on the newer versions of MySQL/MariaDB by default.

The my.cnf file needs to be edited. The following is applicable to a Plesk installation on Unbuntu. You will need root access to the server, otherwise contact your hosting provider.

  1. Connect to the server using Filezilla via SSH using the root login details.
  2. Navigate to /etc/mysql/my.cnf
  3. Open my.cnf with a text editor.
  4. Add innodb_strict_mode=OFF under the [mysqld] section.
  5. Save the changes and close the file.
  6. Use Putty to log into the server with the root login details to restart the MySql service.
# service mysql restart || service mariadb restart || service mysqld restart

Migration can now continue. Once finished, re-edit the file to remove the change.

Change the value in a field to another value

Neil White MySQL

You have an entry in a MySQL database that you want to change.
ie, you may want to re-reference an image folder or in my case, archive all the events on a sports site before the new season. To do this you need the following code:

update [table_name] set [field_name] =
replace([field_name],'[string_to_find]','[string_to_replace]');

Example:

UPDATE wp_posts SET post_type = REPLACE(post_type, 'sp_event', '2018-19');

Copy one column in a table to another

Neil White MySQL

I wanted to copy one column which held postcode data and insert all that data into another column, however I only wanted the first four characters, so effectively providing a column which relates to postcode districts.

Create the new column with the new name. In my case postcode_districts. Then copy the postcode column to the postcode_district column with the following sql command in phpmyadmin:

UPDATE tablename SET postcode_district = postcode

All the data is transferred to the new column.

Then I just adjusted the column structure to reflect 4 characters and not the 10 that was previously there.

The result was as I wanted: