DELIMITER $$
DROP PROCEDURE IF EXISTS drop_all_tables $$
CREATE PROCEDURE drop_all_tables(IN scheme VARCHAR(255))
BEGIN
    DECLARE v_done INT DEFAULT FALSE;
    DECLARE v_tableName VARCHAR(255);
    DECLARE v_cursor CURSOR FOR
        SELECT table_name 
        FROM   information_schema.TABLES
        WHERE  table_schema = scheme;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_done = TRUE;

    SET FOREIGN_KEY_CHECKS = 0;

    OPEN v_cursor;
    REPEAT FETCH v_cursor INTO v_tableName;

    IF NOT v_done THEN
        SET @stmt_sql = CONCAT('DROP TABLE ', v_tableName);
        PREPARE stmt1 FROM @stmt_sql;
        EXECUTE stmt1;
        DEALLOCATE PREPARE stmt1;
    END IF;

    UNTIL v_done END REPEAT;

    CLOSE v_cursor;
    SET FOREIGN_KEY_CHECKS = 1;
END$$
DELIMITER ;

Recently I restored some older MySQL 5.1 database. In the meantime some minor patches were applied and suddenly I received an error (while calling from a java app):

MySQL Database Error: Illegal mix of collations (utf8_general_ci,IMPLICIT) and (utf8_unicode_ci,IMPLICIT) for operation …

Usually my tables are all created (something) like this:

create table apl_users
(usr_id                 int(10)        unsigned not null auto_increment primary key
,usr_login              varchar(40)             not null
,usr_password           varchar(40)             not null
,usr_name               varchar(80)
,index (usr_login)
) engine=InnoDB default charset=utf8;

And a stored procedure who uses this is not really fancy:

create procedure sp_get_usr( in p_in_usr_login varchar(40) )
begin
  select usr_id
  ,      usr_login
  ,      usr_password
  ,      usr_name
  from   apl_users
  where  usr_login = p_in_usr_login;
end;

But somehow (after an upgrade) I got the error mentioned above. After some experimenting on how to collate, I found out maybe it has something to do with the database settings itself (for some odd reason, a bugmantis installation in the past was defaulted to a latin_swedish character set; I’d never paid much attention to that). So I recreated the database from:

create database my_database;

to:

create database my_database default character set 'utf8' collate 'utf8_general_ci';

An now, so far (the fat lady is not singing yet), no errors calling stored procedures from my Java application…