← Back to team overview

maria-developers team mailing list archive

Re: Thoughts on a variable to control high resolution temporal type format and rounding behavior?


Hi Jeremy,

On 11/12/2013 07:09 AM, Jeremy Cole wrote:

In the process of working through the bugs I've reported re. time
formats and InnoDB storage formats we noted that there isn't currently
any way to cause MariaDB to create a *new* Oracle MySQL 5.6-format
TIMESTAMP/TIME/DATETIME field, and thus there's not really very good
test coverage of them. Additionally the existence of this difference
relative to Oracle MySQL is not really desirable.

I would propose adding a new sysvar to control the temporal time format
e.g. temporal_microsecond_format={mysql|mariadb} which controls whether
newly created columns are created as Field_*_hires or Field_*f. This
would especially allow:

 1. Tests to be run in both Oracle MySQL and MariaDB modes.
 2. A single test to create a column in one format and request
    conversion to the other.
 3. A user to convert the format of their existing tables for compatibility.

Additionally I would suggest a new sysvar to control the rounding
behavior of the same temporal types, e.g.
temporal_microsecond_rounding={round|truncate} which controls whether to
round (as Oracle MySQL 5.6 does) or to truncate (as MariaDB does) when
faced with fitting a larger number of decimals in a value into a smaller
number decimals in a field. (This of course assumes that the current
MariaDB behavior and divergence from Oracle MySQL behavior is
intentional, and not a bug. Even if it is considered a bug it could
probably not be changed outright at this point and the sysvar would
still be needed.)

Any thoughts on either of these? We would of course be willing to do the
work and provide the patches for them. I just wanted to get your general
feelings on the ideas.

New user variables are usually painful for replication.
It would be nice to avoid them.

I'd propose the following approach:

1. Rounding vs truncation:

Understand a new option in SQL_MODE:


SQL_MODE is already replicated, so no changes in the replication code will be needed.

2. MySQL vs MariaDB data types:

Understand some syntax like this:

CREATE TABLE t1 (a /*M!50501 MYSQL*/ TIME(6));
CREATE TABLE t1 (a /*M!50501 MARIA*/ TIME(6));

so it's possible to make a self-containing CREATE statement.

The default type on the master side should be MariaDB.

The default type of the slave side can check whether
the master is MariaDB or MySQL (I think it should be known),
and choose the corresponding data type.



Mailing list: https://launchpad.net/~maria-developers
Post to     : maria-developers@xxxxxxxxxxxxxxxxxxx
Unsubscribe : https://launchpad.net/~maria-developers
More help   : https://help.launchpad.net/ListHelp