überEllis.com

Drupal version: n/a
Module: n/a

I've run into several issues running different versions of MySQL between development server and live server.

The following tip from a thread on drupal.org:
dump 2 versions of the database

For version 4.0, it would be:

mysqldump --opt --compatible=mysql40 -v -u DATABASE_USER -p DATABASE_NAME > database.40.mysql

For version 4.1, use:

mysqldump --opt -v -u DATABASE_USER -p DATABASE_NAME > database.mysql

I've learnt something new with regards to dumping databases for different versions of MySQL - A source of many grey hair in the past..

Often, once a 4.0 database has been imported into a 4.1 server, the following error message is common:

Warning: Illegal mix of collations (utf8_general_ci,COERCIBLE) and (latin1_swedish_ci,IMPLICIT) for operation 'like' query: SELECT * FROM access WHERE status = 1 AND type = 'host' AND LOWER('IP Address') LIKE LOWER(mask) in \includes\database.mysql.inc on line 120

To get by this, you need to convert the character set of each of the tables (this solution courtesy of Willie):

ALTER DATABASE {DATABASE_NAME} DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;

And then:

ALTER TABLE {TABLE_NAME} CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;

for each table in the error message.

For the reverse, editing the dump file can have pleasing results:

1. Search for ENGINE=MyISAM and replace with TYPE=MyISAM
2. Search for DEFAULT CHARSET=latin1 and replace with nothing Note: the character set might be something else, like "utf8"
3. Search for character set latin1 collate latin1_bin and replace with nothing Note: the character set might be something else, like "utf8"

Module: Drupal core
Drupal Version: 4.7.2

Instead of using the default drupal error handling settings (administer > settings > error handling), consider using user/login as the default 403 page (instead of a node), or consider writiing a script that uses the referrer of the error (for 404) and passing onto the search module.

This tip brought to you from DrupalCamp.

Module: user.module, node.module
Drupal Version: 4.6.6

Oh sad day. Without thinking, I've deleted a user from a client's site and into the ether vanished a couple hours' work of static content..

Not against my better knowledge, I confess. When working with Drupal, always unpublish content, and block users.

Unpublish:
Administer > Content > Edit (the relevant node) > Uncheck "Published" > Submit

Block:
Administer > Users > Edit (relevant user) > Set Role to "Blocked"

*update* Oh, happy day! All my content is salvaged. If you have ssh access to your webserver, here's how you can too:

Step 1: Find out what the user's uid (or user id) was

Access your Drupal database from your secure shell terminal, eg:

mysql -u <username> -p <database>

where <username> = The drupal database user and <database> = the drupal database name

After entering the drupal database password, you will be taken to a mysql prompt.

Run the following database query:

select nid, uid from node where nid = <broken>;

where <broken> = A node id of a post that's no longer accessable.

This returns a table showing the user id of a broken node and should resemble something like this:

mysql> select nid, uid from node where nid = 13;
+-----+-----+
| nid | uid |
+-----+-----+
|  13 |   2 |
+-----+-----+
1 row in set (0.00 sec)

Step 2: Replace the user id with an existing user id

From the mysql prompt, execute the following:

update node set uid = <existing> where uid = <missing>;

where <existing> = The user id of an existing user, or a new user created to replace the deleted one and <missing> = the user id established from the above, eg:

mysql> update node set uid = 10 where uid = 2;
Query OK, 19 rows affected (0.43 sec)
Rows matched: 19  Changed: 19  Warnings: 0

Voila! Your content has been restored!