0

When I run mysqldump --routines I'm seeing multiple collations for my stored procs. Neither my DB nor columns use utf8mb (I understand the benefits of utf8mb over utf8 but let's leave it for now).

/*!50003 SET sql_mode              = @saved_sql_mode */ ;
/*!50003 SET character_set_client  = @saved_cs_client */ ;
/*!50003 SET character_set_results = @saved_cs_results */ ;
/*!50003 SET collation_connection  = @saved_col_connection */ ;
ALTER DATABASE `pushcoin` CHARACTER SET utf8 COLLATE utf8_general_ci ;
/*!50003 DROP PROCEDURE IF EXISTS `sp_cron_get_money_pool_balance` */;
ALTER DATABASE `pushcoin` CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci ;
/*!50003 SET @saved_cs_client      = @@character_set_client */ ;
/*!50003 SET @saved_cs_results     = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client  = utf8 */ ;
/*!50003 SET character_set_results = utf8 */ ;
/*!50003 SET collation_connection  = utf8_general_ci */ ;
/*!50003 SET @saved_sql_mode       = @@sql_mode */ ;
/*!50003 SET sql_mode              = 'NO_ENGINE_SUBSTITUTION' */ ;
DELIMITER ;;
CREATE DEFINER=`admin`@`%` PROCEDURE `sp_name...`

The database seems all utf8...

MySQL> SHOW VARIABLES LIKE  'char%';
+--------------------------+-----------------------------------------------+
| Variable_name            | Value                                         |
+--------------------------+-----------------------------------------------+
| character_set_client     | utf8                                          |
| character_set_connection | utf8                                          |
| character_set_database   | utf8                                          |
| character_set_filesystem | binary                                        |
| character_set_results    | utf8                                          |
| character_set_server     | utf8                                          |
| character_set_system     | utf8                                          |
+--------------------------+-----------------------------------------------+
8 rows in set (0.00 sec)

MySQL> SHOW VARIABLES LIKE  'coll%';
+----------------------+-----------------+
| Variable_name        | Value           |
+----------------------+-----------------+
| collation_connection | utf8_general_ci |
| collation_database   | utf8_general_ci |
| collation_server     | utf8_general_ci |
+----------------------+-----------------+
3 rows in set (0.00 sec)
0

(Some of this answer is 'guesses'.)

Each database has a default charset and collation. That is used only for providing the charset and/or collation for a CREATE TABLE when you don't explicitly provide it for the table. (And perhaps for Stored Routines.)

Why are there two ALTERs in that dump? I don't know. Perhaps it would make sense if there were a CREATE PROCEDURE between them.

My point is that your question is about something that is essentially irrelevant.

The SHOW VARIABLES output does not say "The database seems all utf8". It does say that certain defaults are utf8 and that connections will assume the client is using utf8 unless otherwise specified.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.