sql_mode switching for MySQL

I guess I don't understand the CNW status. I re-read and interpreted it several ways:

1. Make a D7 version where variable_get()'s default value is STRICT_ALL_TABLES until configured otherwise.
2. Make a checkbox that says "server default". I guess this would attempt to grab the current server value for sql_mode and add it to the implode for the SET query.
3. Make variable_get() in D5 and D6 default value return the current my.cnf configured sql_mode.

As it is, there is no attempt to grab the current server default because the MySQL docs say

The default value is empty (no modes set).

If there are no checkboxes selected, the module defaults to the documented default.

Note to self:
Grabbing the current sql_mode is SELECT @@SESSION.sql_mode; which could act as a "server default" option.

Here's a patch against D5. If you like it, I'll make some upgrade versions. It differs from my sandbox version by adding a server default checkbox and switches from a checkbox list to a multiselect. It also checks $db_url for MySQL.

Perhaps a 6.x patch is more desirable?

Same thing as #5 and #6, but for D7.

Here's a screenshot of the new SQL Mode fieldset on the Devel settings page. It's in the middle. The environment is Firefox in Xfce 4.6 on Drupal HEAD/7 for whatever I downloaded today.

#5, #6, and #7 already have that URL as a link in the new fieldset's description.

I hate to elevate this, but the new http://drupal.org/project/db_tweaks is doing things where this patch might fit. Should I take this patch over there?

moving to Database tweaks module

Is there a particular reason you moved it into hook_init() instead of _boot?

hook_init() doesn't load on every page request and t() isn't needed for switching sql mode.

You could register it people already using the module through an update function, but just re-saving the modules page will fire module_rebuild_cache(), which is responsible for re-setting the bootstrap to 1 where hook_boot and hook_exit are implemented. Merely activating the module will have the bootstrap set to 1 by the mere existence of db_tweaks_boot().

If you want to do the update function, there's no point in doing string replacement on a constant in db_query(), so you can use update_sql() and provide feedback to the update script. Otherwise, the update instructions would be to just re-save the modules page. I have no idea what you're talking about with _notify().

/**
 * Move sql mode switching to hook_boot(), so register with bootstrap.
 */
function db_tweaks_update_6000() {
  $ret = array();
  $ret[] = update_sql("UPDATE {system} SET bootstrap = 1 WHERE name = 'db_tweaks'");
  return $ret;
}

I have not tested it since I made the patch for devel. I'll start you a .test file.

This should get you started. The problem with saying this has been tested is that there's no feedback route to see if a MySQL setting actually stuck during bootstrap.

Perhaps adding hook_requirements() could be a way to get that feedback on REQUIREMENT_INFO level. One row for each setting perhaps? Then this test could load the core status page to assert for the strings it expects.

**
 * Implementation of hook_requirements().
 */
function db_tweaks_requirements($phase) {
  $requirements = array();
  $t = get_t();
  if ($phase == 'runtime') {
    $modes = db_result(db_query('SELECT @@SESSION.sql_mode'));
    $requirements['google_pr_rank'] = array(
      'title' => $t('SQL mode'),
      'value' => $modes,
      'severity' => REQUIREMENT_INFO
    );
  }
  return $requirements;
}

Then the requirements and test can be expanded to all the other variables based on the template of requirements and .test file.

While testing this feature, I selected POSTGRESQL on the settings page. When I submitted, it redirected to a blank page with just a forward slash / on it. It still saved the setting, but ending on a blank page wasn't productive.