Bug #14849 SET CHARACTER SET ... not change locale for functions and procedures

• Submitted: 10 Nov 2005 22:20
• Modified: 13 Dec 2005 12:08
• Reporter: niknik niknik
• Status: No Feedback
• Category: MySQL Server
• Severity: S3 (Non-critical)
• Version: 5.x
• OS: Any (ALL)
• CPU Architecture: Any

[10 Nov 2005 22:20] niknik niknik

Description:
My application work with many server SQL by setting variable as startup.

Code for MySql is lagest.

MySql not support sql statment for setting current locale.

Whitout function "convert( ...) using" I have error mesage

Next SQL statement is good
SET NAMES cp1251;
SET CHARACTER SET cp1251;

But this sql not touch the "create procedure",  "create function" and inner function
_____________________________________

USE telephon;
SET NAMES cp1251;
SET CHARACTER SET cp1251;
select * from ap_users where 
ulogin = CONVERT(SUBSTRING_INDEX(USER(),_utf8'@',1) using cp1251);

OK!
______________________________________________________
USE telephon;
SET NAMES cp1251;
SET CHARACTER SET cp1251;

select * from ap_users where 
ulogin = SUBSTRING_INDEX(USER(),_utf8'@',1);

ERROR 1267 (HY000) at line 8: Illegal mix of collations (cp1251_general_ci,IMPLICIT) and (utf8_general_ci,SYSCONST) for operation '='

ERROR!
______________________________________________________

See next code

   Case 0      'MS SQL
      MS_Null = "NULL"
      MS_DL = "."
      MS_L = " "
      MS_R = " "
      MS_S = "@"
      MS_E = "execute "
      MS_Like = " like "
      MS_LkDl = "%"
      MS_DateL = ""
      MS_DateR = ""
      MS_today = " getdate() "
      MS_month = " month(getdate()) "
      MS_year = " year(getdate()) "
      MS_FirstDay = ""
      MS_LastDay = ""
      MS_MinusMonth = ""
      MS_PlusMonth = ""
      MS_LDT = "01.01.2079"
      MS_Public = "PUBLIC"
      MS_SubStr = "SUBSTRING"
      MS_TrimLeft = "rtrim(ltrim("
      MS_TrimRight = "))"
   Case 1      'Informix
      MS_Null = "NULL"
      MS_DL = "."
      MS_L = "("
      MS_R = ")"
      MS_S = ""
      MS_DateL = " date("
      MS_DateR = ") "
      MS_today = " today "
      MS_month = " month(today) "
      MS_year = " year(today) "
      MS_FirstDay = ""
      MS_LastDay = ""
      MS_MinusMonth = ""
      MS_PlusMonth = ""
      MS_E = "execute procedure "
      MS_Like = " like "
      MS_LkDl = "%"
      MS_LDT = "01.01.2099"
      MS_Public = "PUBLIC"
      MS_SubStr = "SUBSTR"
      MS_TrimLeft = "trim("
      MS_TrimRight = ")"
   Case 2      'Oracle
      MS_Null = "NULL"
      MS_DL = "."
      MS_L = "("
      'MS_R = ")}"
      MS_R = ")"
      MS_S = ""
      'MS_E = "{call tpkg."
      MS_E = "call tpkg."
      MS_Like = " like "
      MS_LkDl = "%"
      MS_DateL = ""
      MS_DateR = ""
      MS_today = " sysdate "
      MS_month = " month(sysdate) "
      MS_year = " year(sysdate) "
      MS_FirstDay = ""
      MS_LastDay = ""
      MS_MinusMonth = ""
      MS_PlusMonth = ""
      MS_LDT = "01.01.2099"
      MS_Public = "PUBLIC"
      MS_SubStr = "SUBSTR"
      MS_TrimLeft = "ltrim("
      MS_TrimRight = ")"
   Case 3      'Mysql
      MS_Null = "NULL"
      MS_DL = "."
      MS_L = "("
      MS_R = ")"
      MS_S = ""
      MS_DateL = " STR_TO_DATE("
      MS_DateR = ",""%d.%m.%Y"") "
      MS_today = " DATE_FORMAT(CURDATE(),""%d.%m.%Y"")"
      MS_month = " month(CURDATE()) "
      MS_year = " year(CURDATE()) "
      MS_FirstDay = " concat(""1."",month(CURDATE()),""."",year(CURDATE()))"
      MS_LastDay = " DATE_FORMAT(LAST_DAY(CURDATE()),""%d.%m.%Y"")"
      MS_MinusMonth = " DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL 1 MONTH),""%d.%m.%Y"")"
      MS_PlusMonth = " DATE_FORMAT(DATE_ADD(CURDATE(), INTERVAL 1 MONTH),""%d.%m.%Y"")"
      MS_E = "CALL "
      MS_Like = " like "
      MS_LkDl = "%"
      MS_LDT = "01.01.2099"
      MS_Public = "PUBLIC"
      MS_SubStr = "SUBSTRING"
      MS_TrimLeft = "trim("
      MS_TrimRight = ")"
      MS_UserName = "CONVERT(SUBSTRING_INDEX(USER(),_utf8'@',1) using cp1251)"

_____________________________________________
[client]
port=3306

[mysql]
default-character-set=cp1251

[mysqld]
port=3306
basedir="C:/MySQL/"
datadir="C:/MySQL/Data/"
default-character-set=cp1251
default-storage-engine=INNODB
sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
max_connections=100
query_cache_size=8M
table_cache=256
tmp_table_size=5M
thread_cache_size=8
myisam_max_sort_file_size=100G
myisam_max_extra_sort_file_size=100G
myisam_sort_buffer_size=8M
key_buffer_size=8M
read_buffer_size=64K
read_rnd_buffer_size=5M
sort_buffer_size=10M
innodb_additional_mem_pool_size=2M
innodb_flush_log_at_trx_commit=1
innodb_log_buffer_size=1M
innodb_buffer_pool_size=20M
innodb_log_file_size=10M
innodb_thread_concurrency=8

How to repeat:
This is no bug. This idea for next version

Suggested fix:
no bug

[13 Nov 2005 12:08] Valeriy Kravchuk

Thank you for a problem report. Please, send the results of the SHOW CREATE TABLE ap_users statement. Just to be sure what's going on. The exact version of MySQL may be useful too.

[14 Dec 2005 0:00] Bugs System

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".