I am mikek's Blog

The personal blog of Mike Karthauser, who you may know from Brightstorm Limited or the internet.

Cleat Drupal cache via SQL

A handy little bit of sql to clear the cache tables just before you back up a drupal db to sql.

Steps to follow:

  1. Replace {db_name} by the real project’s database name.
  2. Execute the sentence.
  3. Copy the generated string (TRUNCATE list, one for each table).
  4. Execute the string  copied in the previous step (TRUNCATE list).


SELECT
CONCAT('TRUNCATE TABLE ', TABLE_SCHEMA, '.', TABLE_NAME, ';') AS table_name
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = '{db_name}'
AND TABLE_NAME LIKE 'cache_%'
AND TABLE_TYPE = 'BASE TABLE'

The resulting string must be something like this (will vary depending on the Drupal version and the installed modules):


TRUNCATE TABLE {db_name}.cache_admin_menu;
TRUNCATE TABLE {db_name}.cache_block;
TRUNCATE TABLE {db_name}.cache_bootstrap;
TRUNCATE TABLE {db_name}.cache_field;
TRUNCATE TABLE {db_name}.cache_filter;
TRUNCATE TABLE {db_name}.cache_form;
TRUNCATE TABLE {db_name}.cache_image;
TRUNCATE TABLE {db_name}.cache_menu;
TRUNCATE TABLE {db_name}.cache_metatag;
TRUNCATE TABLE {db_name}.cache_page;
TRUNCATE TABLE {db_name}.cache_path;
TRUNCATE TABLE {db_name}.cache_token;
TRUNCATE TABLE {db_name}.cache_update;
TRUNCATE TABLE {db_name}.cache_variable;
TRUNCATE TABLE {db_name}.cache_views;
TRUNCATE TABLE {db_name}.cache_views_data;

From
http://zonesoftware.co/en/blog/article/clear-cache-in-drupal-via-sql

Advertisements

Filed under: development, ,

Max Allowed Packets

The max_allowed_packet variable can be set globally by running a query.

To change the setting for everyone until the server restarts:

SET GLOBAL max_allowed_packet=1073741824;

Filed under: development,

Exporting to a text file in mysql

Getting data out of a mysql database into a text file is pretty straight forward using mysqldump

mysqldump5 -u USERNAME -p DATABASE_NAME TABLE_NAMES > /PATH/TO/FILENAME

TABLE_NAMES are optional and need to separated by a space if you are exporting a couple.

A couple of useful additional flags you can add after table names

  • –xml – converts sql output to xml which makes for an easier read
  • –no-create-info – outputs just data and no schema data.
  • –no-data – the opposite of above, which gives you just the schema
  • –complete-insert – to get column names within your insert statements
  • –skip-triggers –compact –no-create-info – for just insert statements

Filed under: development, , ,

A better like for searching

I’ve been using LIKE for ages which is a useful way to search for part of a word or phrase in a db field.
$conditions = array( 'TABLE.field LIKE'=>'%'.$search_term.'%');

Now i didn’t realise that this wasn’t case sensitive meaning that ‘Ultra’ returned results but ‘ultra’ didn’t.

So to make it case insensitive you can do the following:

$conditions = array('UPPER(TABLE.field) LIKE'=>'%'.strtoupper($search_term).'%');

Not much different but now a useful(UPPER) addition to the sql arsenal.

Filed under: development, , , ,

Converting sql from latin to utf-8

a useful tip from andygale to convert sql from latin to utf-8

iconv -f ISO-8859-1 -t UTF-8 dump.sql > dump_utf8.sql

Filed under: development, ,

what's hot

Flickr Photos

Sponsor Mike run the Bath Half marathon

mikek on twitter