Opencart 1.5.6.1 FATAL error when saving settings

There is an issue that occurs when updating areas in the admin and experiencing a 500 error.

As a result of the error, some items in the database are deleted. In the below function which updates the database with the settings on save. The problem is that it doesn’t actually “UPDATE”. It first deletes the settings and then inserts new records.

In the code below the 500 error triggers when inserting causing a site to break.

Since MySQL 5.6, there is a new default that makes sure you are explicitly inserting every field that doesn’t have a default value set in the table definition.

This is caused by MySQL having a strict mode set which won’t allow INSERT or UPDATE commands with empty fields where the schema doesn’t have a default value set.

Location: admin/model/setting/setting.php

public function editSetting($group, $data, $store_id = 0) {
	$this->db->query("DELETE FROM " . DB_PREFIX . "setting WHERE store_id = '" . (int)$store_id . "' AND `group` = '" . $this->db->escape($group) . "'");

	foreach ($data as $key => $value) {
		if (!is_array($value)) {
			$this->db->query("INSERT INTO " . DB_PREFIX . "setting SET store_id = '" . (int)$store_id . "', `group` = '" . $this->db->escape($group) . "', `key` = '" . $this->db->escape($key) . "', `value` = '" . $this->db->escape($value) . "'");
		} else {
			$this->db->query("INSERT INTO " . DB_PREFIX . "setting SET store_id = '" . (int)$store_id . "', `group` = '" . $this->db->escape($group) . "', `key` = '" . $this->db->escape($key) . "', `value` = '" . $this->db->escape(serialize($value)) . "', serialized = '1'");
		}
	}
}

When updating settings in the admin you shouldn’t be at this risk. When installing an extension and doing big updates it is always advisable to take a database backup but just updating a setting in the admin should not require this.

The only way to recover is to have a database backup and then fix the cause of the 500 by updating the query or changing the MySQL settings.