The database collation of a project is utf8_unicode_ci. I get this message:
PDOException: SQLSTATE[HY000]: General error: 1267 Illegal mix of collations (utf8_general_ci,IMPLICIT) and (utf8_unicode_ci,IMPLICIT) for operation '=': DELETE FROM {blocked_ips} WHERE (ip IN (SELECT hostname FROM {httpbl} WHERE status > 0 AND expire <= :time)) ; Array ( [:time] => 1353765584 ) in httpbl_cron() (line 371 of xxx/sites/all/modules/contrib/httpbl/httpbl.module).

From a conversion from D6->D7 the table was defined as utf8_general_ci. I got the same PDOExeption. So I did an uninstall of the module (the db-table was gone) and a reinstall. The db-table now is defined as: InnoDB and utf8_unicode_ci. But I still get the same exception.
The definition of table blocked_ips is MyISAM, utf8_unicode_ci.
The db_delete call on line 373 doesn't generate an exception.

Up till now I didn't get an equal exception on other tables.

Do you know the reason / a solution?

CommentFileSizeAuthor
#2 httpbl_1849352.patch854 bytesPROMES
Support from Acquia helps fund testing for Drupal Acquia logo

Comments

bryrock’s picture

bryrock CreditAttribution: bryrock commented

I can't address this with any certainty, but the fact that you have a mix of table types and collation seems to be an artifact of your D6->D7 conversion. In particular, I find it curious that your blocked_ips table is MyISAM, because that table is new to Drupal core in D7, and the D7 sites I'm familiar with all use InnoDB table types.

The deletion attempt in line 371 would throw an exception for a comparison of two different table types/collation.

The deletion attempt in line 373 is based only on a condition of one table, of either type/collation, thus no exception.

PROMES’s picture

PROMES
Primary language Dutch
CreditAttribution: PROMES commented
FileSize
httpbl_1849352.patch854 bytes

I split up the line.
From:

      db_delete('blocked_ips')
        ->where('ip IN (SELECT hostname FROM {httpbl} WHERE status > 0 AND expire <= :time)', array(':time' => REQUEST_TIME))
        ->execute();
    }

into:

      $hn = db_query('SELECT hostname FROM {httpbl} WHERE status > 0 AND expire <= :time', array(':time' => REQUEST_TIME))->fetchCol();
      if (!empty($hn)) {
        db_delete('blocked_ips')
          ->condition('ip', $hn, 'IN')
          ->execute();
      }
    }

The PDOException is disappeared.
The patch is appended.

bryrock’s picture

bryrock CreditAttribution: bryrock commented

I guess that's one way to solve the problem, but it looks to me like this is killing the messenger. The PDO exception you're seeing is not indicative of a problem in httpbl, but rather a problem in your database. The preferred solution would be to address the problem in your database.

This might offer some help. #251938: user warning: Illegal mix of collations error, help please!

PROMES’s picture

PROMES
Primary language Dutch
CreditAttribution: PROMES commented
Status: Active » Closed (works as designed)

Hello bryrock. Thanks for your input. With help of the little program (in node/251938) the exception is diappeared.
Still it is strange. I did the same (?) manually:
1. first attempt: changing the collation via phpMyAdmin by hand. This didn't help.
2. Deleting both tables and recreating them (the database collation was manually changed too before). This didn't prevent the exception as well.

I hope to help others having the same problem.
I closed the issue.