MySQL Bugs: #43702: Calling setlocale() in a UDF or plugin changes the output format of SHOW CREATE

Bug #43702	Calling setlocale() in a UDF or plugin changes the output format of SHOW CREATE
Submitted:	17 Mar 2009 14:03	Modified:	11 May 2009 20:55
Reporter:	Hartmut Holzgraefe	Email Updates:
Status:	Won't fix	Impact on me:	None
Category:	MySQL Server: User-defined functions ( UDF )	Severity:	S3 (Non-critical)
Version:	5.0.72, 5.1.32	OS:	Linux
Assigned to:		CPU Architecture:	Any

Description:
When setting LC_NUMERIC to a locale where the decimal separator is not a dot the output of default values for FLOAT/DECIMAL values in SHOW CREATE TABLE is changed in a way that is not valid MySQL syntax anymore. This breaks mysqldump (amongst other things) as the backups created with it can't be imported anymore.

This is due to sprintf/sprintf %f being used in Field_float::val_str() for float to string conversion. As the printf family of functions is locale aware changing the locale settings can lead to different decimal separators than '.' being used for output, our parser does only support '.' as decimal delimiter though.

How to repeat:
* create a table like

    CREATE TABLE t1(c1 float(3,2) NOT NULL default '0.10') ENGINE=MyISAM; 

* load a UDF or plugin that calls 

    setlocale(LC_NUMERIC, "de_DE"); // or de_DE.UDF-8 or whatever German local is available on your system

* a SHOW CREATE TABLE or mysqldump will now show the default as '0,10' instead of '0.10' as the German locale defines the comma as decimal separator and not the dot

    SHOW CREATE TABLE t1;

Suggested fix:
* do not rely on printf formatting for numeric output at all
  -> safest option but probably the one requiring the most effort, too

* replace the current locales decimal delimiter as reported by e.g. 
  localeconf()  with '.' after using locale aware functionality like printf("%f") 
  -> quick&dirty hack version of the item above, printf() and friends
     cann still be used for formatting but some extra result post 
     processing is needed

* make the parser support different decimal separator characters
  on string->float conversion like other database products do
  -> clean solution that we may need to implement at some point
     anyway but not backwards compatible

* reset the locale to English or "POSIX C" before calling locale 
  dependent functions
  -> may confuse libraries/UDFs/plugins that rely on loacle functionality
     as setlocale() changes the locale settings for all threads in a process
     globally

* tell people not to use setlocale() in UDFs and plugins
  -> may be out of the scope of the UDF/plugin developer as the setlocale()
     call may be hidden in a 3rd party library used by the UDF/plugin

I made some tests with other RDBMS:

INSERT INTO t VALUES('3,14') <--- works fine in Oracle. But you need the quotes when you want to use comma instead of dot.

Joro,

I have a dark remember from a discussion with Bar that we are planning to support country/language based number form in future. I remember that I said it is not so important and not necessary before MySQL 7.0 or so. But better you will ask Bar here again.

I vote for :
"
* tell people not to use setlocale() in UDFs and plugins
  -> may be out of the scope of the UDF/plugin developer as the setlocale()
     call may be hidden in a 3rd party library used by the UDF/plugin
"

afair we have a documented usage of the system locale (there's even a special collation name for this).
And I don't think re-setting the locale after each call to the plugin is a good solution either.
So let's move this to a docs bug.

Plugins should try to avoid setlocale anyway, as it has - as far as I understand - global effect and affects all threads at once. It's hardly desired in a plugin.

Anyway, as for this bug - it should be possible to have it fixed in 6.0, where there's no reason to use %f.

Hartmut, do you see this bug in 6.0 ?

6.0 is not affected

Problem is addressed in 6.0