Re: [Maria-discuss] SQL_MODE=ORACLE documentation

[Alexander Barkov]

Hello Federico,

On 03/02/2018 07:12 PM, Federico Razzoli wrote:
With SQL_MODE='ORACLE' a completely new syntax has been introduced. 
Basic syntax constructs of MariaDB become unavailable, but not all 
Oracle syntax seems to be supported. Is there any plan to document the 
supported syntax? Current documentation looks like an unfinished draft 
and doesn't save much time for the poor user.


The difference between the native MariaDB (SQL/PSM based) syntax
and the Oracle PL/SQL syntax is described as a summary here:

https://mariadb.com/kb/en/library/sql_modeoracle-from-mariadb-103/

Yes, the documentation will be improving.

We need to decide if we want two separate SQL/PSM and PL/SQL pages
on every topic.

For example, for the WHILE loop syntax,
do we need an new PL/SQL article similar to
https://mariadb.com/kb/en/library/while/ ,
or should we put the PL/SQL WHILE syntax directly
on this page.

What do you think would be more readable?

I'm slightly inclined towards having separate pages
for SQL/PSM and PL/SQL, with links to each other
in the "See Also" section.






Regards,
Federico



___
Mailing list: https://launchpad.net/~maria-discuss
Post to : maria-discuss@lists.launchpad.net
Unsubscribe : https://launchpad.net/~maria-discuss
More help   : https://help.launchpad.net/ListHelp



___
Mailing list: https://launchpad.net/~maria-discuss
Post to : maria-discuss@lists.launchpad.net
Unsubscribe : https://launchpad.net/~maria-discuss
More help   : https://help.launchpad.net/ListHelp

Re: [Maria-discuss] Charsets and collations

[Alexander Barkov]

Hi Peter,

On 06/16/2017 04:01 PM, Peter Laursen wrote:

Does anybody know if there are any plans to bring collations added in
MySQL 8 - refer
http://mysqlserverteam.com/new-collations-in-mysql-8-0-0/ - to MariaDB
  and are there any plans to change the default charset to a
unicode-based one?


We should eventually do it. But it's not a high priority for now.
It's very unlikely that we'll have time to do it before autumn.






-- Peter
-- Webyog


___
Mailing list: https://launchpad.net/~maria-discuss
Post to : maria-discuss@lists.launchpad.net
Unsubscribe : https://launchpad.net/~maria-discuss
More help   : https://help.launchpad.net/ListHelp



___
Mailing list: https://launchpad.net/~maria-discuss
Post to : maria-discuss@lists.launchpad.net
Unsubscribe : https://launchpad.net/~maria-discuss
More help   : https://help.launchpad.net/ListHelp

Re: [Maria-discuss] Collations, trailing spaces and unique indexes

[Alexander Barkov]

Hi,

On 03/16/2016 09:59 AM, Alexander Barkov wrote:
...
>>
>> - My main problem: I did not find my_strnncollsp_utf8_general_ci anywhere 
>> (nor in the same neither in any other file). Where is it?
> 
> 
> The function name is just "my_strnncollsp_utf8".
> 

A correction.

In 10.0 and earlier versions the name was "my_strnncollsp_utf8".


In 10.1 it was renamed to "my_strnncollsp_utf8_general_ci".

It's defined in this piece of the code in ctype-utf8.c:

#define MY_FUNCTION_NAME(x)my_ ## x ## _utf8_general_ci
#define WEIGHT_ILSEQ(x)(0xFF + (uchar) (x))
#define WEIGHT_MB1(x)  my_weight_mb1_utf8_general_ci(x)
#define WEIGHT_MB2(x,y)my_weight_mb2_utf8_general_ci(x,y)
#define WEIGHT_MB3(x,y,z)  my_weight_mb3_utf8_general_ci(x,y,z)
#include "strcoll.ic"


and the function body is defined in a shared file strcoll.ic,
which is reused for many character sets to avoid duplicate code,
here:

> static int
> MY_FUNCTION_NAME(strnncollsp)(CHARSET_INFO *cs __attribute__((unused)),
>   const uchar *a, size_t a_length, 
>   const uchar *b, size_t b_length,
>   my_bool diff_if_only_endspace_difference
>   __attribute__((unused)))
> {

___
Mailing list: https://launchpad.net/~maria-discuss
Post to : maria-discuss@lists.launchpad.net
Unsubscribe : https://launchpad.net/~maria-discuss
More help   : https://help.launchpad.net/ListHelp

Re: [Maria-discuss] Collations, trailing spaces and unique indexes

[Alexander Barkov]

FYI, I have added a new task for this:

https://jira.mariadb.org/browse/MDEV-9711


On 03/11/2016 03:20 PM, Alexander Barkov wrote:
> Hello Binarus, Kristian,
> 
> On 03/11/2016 02:25 PM, Kristian Nielsen wrote:
>> Binarus <li...@binarus.de> writes:
>>
>>> "All MySQL collations are of type PADSPACE. This means that all CHAR,
>>> VARCHAR, and TEXT values in MySQL are compared without regard to any
>>> trailing spaces. “Comparison” in this context does not include the
>>
>> Yes, I have always found this terminally stupid as well. But I think it
>> comes from the SQL standard.
> 
> Probably this comes from here:
> 
>> 2) A  specifies the name of a character set 
>> that is defined by a national or
>> international standard. The character repertoire of CS is defined by the 
>> standard defining the character set
>> identified by that . The default collation of 
>> the character set is defined by
>> the order of the characters in the standard and has the PAD SPACE 
>> characteristic.
> 
> So we follow the PAD SPACE requirement for default collations here.
> 
> I guess the reasoning here is to treat CHAR and VARCHAR in a similar way
> by default. I agree with the standard here. We don't want different
> behavior for CHAR and VARCHAR.
> 
> 
> Btw, we don't follow the requirement that the default collation must be
> defined according to "the order of the characters in the standard".
> Default collations are traditionally case insensitive in MariaDB/MySQL.
> 
>>
>> The only workaround I know of is to use VARBINARY instead of VARCHAR. I
>> think it works much the same in most respects. But obviously some semantics
>> is lost when the server no longer is aware of the character set used.
> 
> Correct.
> 
>>
>>> Since the index behaviour obviously depends on the collation, would
>>> building an own collation which does not PADSPACE be an option? I have
>>
>> That would be interesting, actually. I don't know what support there is for
>> non-PADSPACE collations. Maybe bar knows (Cc:'ed)?
> 
> We don't have NO PAD collations yet.
> I don't remember that anybody ever asked for this before.
> 
> From a glance, this should not be too hard to implement.
> 
> You previously wrote you need utf8.
> So there are three options to make a new collation,
> based on one of these existing collations:
> 
> - utf8_general_ci
> - utf8_unicode_ci
> - utf8_bin
> 
> but with NO PAD characteristics.
> 
> Suppose you need utf8_general_nopad_ci
> (i.e. based on utf8_general_ci)
> 
> 
> utf8_general_ci is implemented in strings/ctype-utf8.c
> 
> A new collation handler should be added, similar to this one:
> 
> static MY_COLLATION_HANDLER my_collation_utf8_general_ci_handler =
> {
> NULL,   /* init */
> my_strnncoll_utf8_general_ci,
> my_strnncollsp_utf8_general_ci,
> my_strnxfrm_unicode,
> my_strnxfrmlen_unicode,
> my_like_range_mb,
> my_wildcmp_utf8,
> my_strcasecmp_utf8,
> my_instr_mb,
> my_hash_sort_utf8,
> my_propagate_complex
> };
> 
> 
> but these three virtual functions must be redefined to new
> similar functions that do not ignore trailing spaces:
> 
> - my_strnncollsp_utf8_general_ci - this is used for BTREE indexes
> - my_hash_sort_utf8  - this is used for HASH indexes
> - my_strnxfrm_unicode- this is used for filesort
>(non-indexed ORDER BY)
> 
> All other functions can be reused from the existing PAD SPACE collation.
> 
> 
> 
> Then a new "struct charset_info_st" should be defined,
> similar to my_charset_utf8_general_ci.
> 
> Sounds like a few hours of work.
> 
> 
> But then thorough testing will be needed,
> which will be the most time consuming part.
> 
> 
> Also, for consistency, it's worthy to implement
> at least utf8_nopad_bin and utf8_unicode_nopad_ci at once,
> and then eventually NO PAD collations for all other character sets.
> 
>>
>>  - Kristian.
>>

___
Mailing list: https://launchpad.net/~maria-discuss
Post to : maria-discuss@lists.launchpad.net
Unsubscribe : https://launchpad.net/~maria-discuss
More help   : https://help.launchpad.net/ListHelp

Re: [Maria-discuss] Collations, trailing spaces and unique indexes

[Alexander Barkov]

Hello Binarus, Kristian,

On 03/11/2016 02:25 PM, Kristian Nielsen wrote:
> Binarus  writes:
> 
>> "All MySQL collations are of type PADSPACE. This means that all CHAR,
>> VARCHAR, and TEXT values in MySQL are compared without regard to any
>> trailing spaces. “Comparison” in this context does not include the
> 
> Yes, I have always found this terminally stupid as well. But I think it
> comes from the SQL standard.

Probably this comes from here:

> 2) A  specifies the name of a character set that 
> is defined by a national or
> international standard. The character repertoire of CS is defined by the 
> standard defining the character set
> identified by that . The default collation of 
> the character set is defined by
> the order of the characters in the standard and has the PAD SPACE 
> characteristic.

So we follow the PAD SPACE requirement for default collations here.

I guess the reasoning here is to treat CHAR and VARCHAR in a similar way
by default. I agree with the standard here. We don't want different
behavior for CHAR and VARCHAR.


Btw, we don't follow the requirement that the default collation must be
defined according to "the order of the characters in the standard".
Default collations are traditionally case insensitive in MariaDB/MySQL.

> 
> The only workaround I know of is to use VARBINARY instead of VARCHAR. I
> think it works much the same in most respects. But obviously some semantics
> is lost when the server no longer is aware of the character set used.

Correct.

> 
>> Since the index behaviour obviously depends on the collation, would
>> building an own collation which does not PADSPACE be an option? I have
> 
> That would be interesting, actually. I don't know what support there is for
> non-PADSPACE collations. Maybe bar knows (Cc:'ed)?

We don't have NO PAD collations yet.
I don't remember that anybody ever asked for this before.

From a glance, this should not be too hard to implement.

You previously wrote you need utf8.
So there are three options to make a new collation,
based on one of these existing collations:

- utf8_general_ci
- utf8_unicode_ci
- utf8_bin

but with NO PAD characteristics.

Suppose you need utf8_general_nopad_ci
(i.e. based on utf8_general_ci)


utf8_general_ci is implemented in strings/ctype-utf8.c

A new collation handler should be added, similar to this one:

static MY_COLLATION_HANDLER my_collation_utf8_general_ci_handler =
{
NULL,   /* init */
my_strnncoll_utf8_general_ci,
my_strnncollsp_utf8_general_ci,
my_strnxfrm_unicode,
my_strnxfrmlen_unicode,
my_like_range_mb,
my_wildcmp_utf8,
my_strcasecmp_utf8,
my_instr_mb,
my_hash_sort_utf8,
my_propagate_complex
};


but these three virtual functions must be redefined to new
similar functions that do not ignore trailing spaces:

- my_strnncollsp_utf8_general_ci - this is used for BTREE indexes
- my_hash_sort_utf8  - this is used for HASH indexes
- my_strnxfrm_unicode- this is used for filesort
   (non-indexed ORDER BY)

All other functions can be reused from the existing PAD SPACE collation.



Then a new "struct charset_info_st" should be defined,
similar to my_charset_utf8_general_ci.

Sounds like a few hours of work.


But then thorough testing will be needed,
which will be the most time consuming part.


Also, for consistency, it's worthy to implement
at least utf8_nopad_bin and utf8_unicode_nopad_ci at once,
and then eventually NO PAD collations for all other character sets.

> 
>  - Kristian.
> 

___
Mailing list: https://launchpad.net/~maria-discuss
Post to : maria-discuss@lists.launchpad.net
Unsubscribe : https://launchpad.net/~maria-discuss
More help   : https://help.launchpad.net/ListHelp

Re: [Maria-discuss] select using date_sub produces unwanted milliseconds in results

[Alexander Barkov]

Hi Dan,

On 09/24/2015 12:35 AM, Dan Ragle wrote:



On 9/22/2015 5:12 PM, Alexander Barkov wrote:


On 09/23/2015 01:00 AM, Alexander Barkov wrote:

Hi Dan,


On 09/20/2015 12:53 AM, Dan Ragle wrote:

We're converting from MySQL 5.1 to MariaDB 5.5.44 and I'm hoping
someone
can help clarify for me what's going on in the following scenario. In
brief, we're getting milliseconds displayed in a particular date_sub
calculation (and possibly other selects with date arithmetic, trying to
figure out how to track them down is part of the goal of this post)
where we aren't expecting them.

MariaDB [testdb]> show variables like '%version%';
+-+-+
| Variable_name   | Value   |
+-+-+
| innodb_version  | 5.5.43-MariaDB-37.2 |
| protocol_version| 10  |
| slave_type_conversions  | |
| version | 5.5.44-MariaDB-log  |
| version_comment | MariaDB Server  |
| version_compile_machine | x86_64  |
| version_compile_os  | Linux   |
+-+-+
7 rows in set (0.00 sec)

MariaDB [testdb]> show create table test \G
*** 1. row ***
Table: test
Create Table: CREATE TABLE `test` (
   `datetime` datetime NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

MariaDB [testdb]> select datetime from test;
Field   1:  `datetime`
Catalog:`def`
Database:   `velocitynorep`
Table:  `test`
Org_table:  `test`
Type:   DATETIME
Collation:  binary (63)
Length: 19
Max_length: 19
Decimals:   0
Flags:  NOT_NULL BINARY NO_DEFAULT_VALUE

+-+
| datetime|
+-+
| 2015-09-18 09:23:21 |
+-+
1 row in set (0.00 sec)

MariaDB [testdb]> select date_sub(datetime,interval 1 second) from
test;
Field   1:  `date_sub(datetime,interval 1 second)`
Catalog:`def`
Database:   ``
Table:  ``
Org_table:  ``
Type:   DATETIME
Collation:  binary (63)
Length: 19
Max_length: 19
Decimals:   0
Flags:  BINARY


+--+
| date_sub(datetime,interval 1 second) |
+--+
| 2015-09-18 09:23:20  |
+--+
1 row in set (0.00 sec)

MariaDB [testdb]> select substr(datetime,1,13) from test;
Field   1:  `substr(datetime,1,13)`
Catalog:`def`
Database:   ``
Table:  ``
Org_table:  ``
Type:   VAR_STRING
Collation:  utf8_general_ci (33)
Length: 39
Max_length: 13
Decimals:   31
Flags:  NOT_NULL

+---+
| substr(datetime,1,13) |
+---+
| 2015-09-18 09 |
+---+
1 row in set (0.00 sec)

MariaDB [testdb]> select concat(substr(datetime,1,13),':00:00')
 -> from test;
Field   1:  `concat(substr(datetime,1,13),':00:00')`
Catalog:`def`
Database:   ``
Table:  ``
Org_table:  ``
Type:   VAR_STRING
Collation:  utf8_general_ci (33)
Length: 57
Max_length: 19
Decimals:   31
Flags:  NOT_NULL

++
| concat(substr(datetime,1,13),':00:00') |
++
| 2015-09-18 09:00:00|
++
1 row in set (0.00 sec)

MariaDB [testdb]> select concat('2015-09-18 09',':00:00');
Field   1:  `concat('2015-09-18 09',':00:00')`
Catalog:`def`
Database:   ``
Table:  ``
Org_table:  ``
Type:   VAR_STRING
Collation:  utf8_general_ci (33)
Length: 57
Max_length: 19
Decimals:   31
Flags:  NOT_NULL


+--+
| concat('2015-09-18 09',':00:00') |
+--+
| 2015-09-18 09:00:00  |
+--+
1 row in set (0.00 sec)

MariaDB [testdb]> select date_sub(concat('2015-09-18 09',':00:00'),
 -> interval 1 second);
Field   1:  `date_sub(concat('2015-09-18 09',':00:00'),
interval 1 second)`
Catalog:`def`
Database:   ``
Table:  ``
Org_table:  ``
Type:   STRING
Collation:  binary (63)
Length: 19
Max_length: 19
Decimals:   0
Flags:  BINARY


+---+
| date_sub(concat('2015-09-18 09',':00:00'),
interval 1 second) |
+---+
| 2015-09-18 08:59:59   |
+---+
1 row in set (0.00 sec)

MariaDB [testdb]> select
 -> date_sub(concat(substr(datetime,1,13),':00:00'),
 -> interval 1 second) from test;
Field   1:  `date_sub(concat(substr(datetime,1,13),':00:00'),
interval 1 second)`
Catalog:`def`
Database:   ``
Table:  ``
Org_table:  ``
Type:   STRING
Collation:  binary (63)
Length:   

Re: [Maria-discuss] select using date_sub produces unwanted milliseconds in results

[Alexander Barkov]

Hi Dan,


On 09/20/2015 12:53 AM, Dan Ragle wrote:

We're converting from MySQL 5.1 to MariaDB 5.5.44 and I'm hoping someone
can help clarify for me what's going on in the following scenario. In
brief, we're getting milliseconds displayed in a particular date_sub
calculation (and possibly other selects with date arithmetic, trying to
figure out how to track them down is part of the goal of this post)
where we aren't expecting them.

MariaDB [testdb]> show variables like '%version%';
+-+-+
| Variable_name   | Value   |
+-+-+
| innodb_version  | 5.5.43-MariaDB-37.2 |
| protocol_version| 10  |
| slave_type_conversions  | |
| version | 5.5.44-MariaDB-log  |
| version_comment | MariaDB Server  |
| version_compile_machine | x86_64  |
| version_compile_os  | Linux   |
+-+-+
7 rows in set (0.00 sec)

MariaDB [testdb]> show create table test \G
*** 1. row ***
Table: test
Create Table: CREATE TABLE `test` (
   `datetime` datetime NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

MariaDB [testdb]> select datetime from test;
Field   1:  `datetime`
Catalog:`def`
Database:   `velocitynorep`
Table:  `test`
Org_table:  `test`
Type:   DATETIME
Collation:  binary (63)
Length: 19
Max_length: 19
Decimals:   0
Flags:  NOT_NULL BINARY NO_DEFAULT_VALUE

+-+
| datetime|
+-+
| 2015-09-18 09:23:21 |
+-+
1 row in set (0.00 sec)

MariaDB [testdb]> select date_sub(datetime,interval 1 second) from test;
Field   1:  `date_sub(datetime,interval 1 second)`
Catalog:`def`
Database:   ``
Table:  ``
Org_table:  ``
Type:   DATETIME
Collation:  binary (63)
Length: 19
Max_length: 19
Decimals:   0
Flags:  BINARY


+--+
| date_sub(datetime,interval 1 second) |
+--+
| 2015-09-18 09:23:20  |
+--+
1 row in set (0.00 sec)

MariaDB [testdb]> select substr(datetime,1,13) from test;
Field   1:  `substr(datetime,1,13)`
Catalog:`def`
Database:   ``
Table:  ``
Org_table:  ``
Type:   VAR_STRING
Collation:  utf8_general_ci (33)
Length: 39
Max_length: 13
Decimals:   31
Flags:  NOT_NULL

+---+
| substr(datetime,1,13) |
+---+
| 2015-09-18 09 |
+---+
1 row in set (0.00 sec)

MariaDB [testdb]> select concat(substr(datetime,1,13),':00:00')
 -> from test;
Field   1:  `concat(substr(datetime,1,13),':00:00')`
Catalog:`def`
Database:   ``
Table:  ``
Org_table:  ``
Type:   VAR_STRING
Collation:  utf8_general_ci (33)
Length: 57
Max_length: 19
Decimals:   31
Flags:  NOT_NULL

++
| concat(substr(datetime,1,13),':00:00') |
++
| 2015-09-18 09:00:00|
++
1 row in set (0.00 sec)

MariaDB [testdb]> select concat('2015-09-18 09',':00:00');
Field   1:  `concat('2015-09-18 09',':00:00')`
Catalog:`def`
Database:   ``
Table:  ``
Org_table:  ``
Type:   VAR_STRING
Collation:  utf8_general_ci (33)
Length: 57
Max_length: 19
Decimals:   31
Flags:  NOT_NULL


+--+
| concat('2015-09-18 09',':00:00') |
+--+
| 2015-09-18 09:00:00  |
+--+
1 row in set (0.00 sec)

MariaDB [testdb]> select date_sub(concat('2015-09-18 09',':00:00'),
 -> interval 1 second);
Field   1:  `date_sub(concat('2015-09-18 09',':00:00'),
interval 1 second)`
Catalog:`def`
Database:   ``
Table:  ``
Org_table:  ``
Type:   STRING
Collation:  binary (63)
Length: 19
Max_length: 19
Decimals:   0
Flags:  BINARY


+---+
| date_sub(concat('2015-09-18 09',':00:00'),
interval 1 second) |
+---+
| 2015-09-18 08:59:59   |
+---+
1 row in set (0.00 sec)

MariaDB [testdb]> select
 -> date_sub(concat(substr(datetime,1,13),':00:00'),
 -> interval 1 second) from test;
Field   1:  `date_sub(concat(substr(datetime,1,13),':00:00'),
interval 1 second)`
Catalog:`def`
Database:   ``
Table:  ``
Org_table:  ``
Type:   STRING
Collation:  binary (63)
Length: 26
Max_length: 26
Decimals:   6
Flags:  BINARY

+-+
| date_sub(concat(substr(datetime,1,13),':00:00'),
interval 1 second) |

Re: [Maria-discuss] select using date_sub produces unwanted milliseconds in results

[Alexander Barkov]

On 09/23/2015 01:00 AM, Alexander Barkov wrote:

Hi Dan,


On 09/20/2015 12:53 AM, Dan Ragle wrote:

We're converting from MySQL 5.1 to MariaDB 5.5.44 and I'm hoping someone
can help clarify for me what's going on in the following scenario. In
brief, we're getting milliseconds displayed in a particular date_sub
calculation (and possibly other selects with date arithmetic, trying to
figure out how to track them down is part of the goal of this post)
where we aren't expecting them.

MariaDB [testdb]> show variables like '%version%';
+-+-+
| Variable_name   | Value   |
+-+-+
| innodb_version  | 5.5.43-MariaDB-37.2 |
| protocol_version| 10  |
| slave_type_conversions  | |
| version | 5.5.44-MariaDB-log  |
| version_comment | MariaDB Server  |
| version_compile_machine | x86_64  |
| version_compile_os  | Linux   |
+-+-+
7 rows in set (0.00 sec)

MariaDB [testdb]> show create table test \G
*** 1. row ***
Table: test
Create Table: CREATE TABLE `test` (
   `datetime` datetime NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

MariaDB [testdb]> select datetime from test;
Field   1:  `datetime`
Catalog:`def`
Database:   `velocitynorep`
Table:  `test`
Org_table:  `test`
Type:   DATETIME
Collation:  binary (63)
Length: 19
Max_length: 19
Decimals:   0
Flags:  NOT_NULL BINARY NO_DEFAULT_VALUE

+-+
| datetime|
+-+
| 2015-09-18 09:23:21 |
+-+
1 row in set (0.00 sec)

MariaDB [testdb]> select date_sub(datetime,interval 1 second) from test;
Field   1:  `date_sub(datetime,interval 1 second)`
Catalog:`def`
Database:   ``
Table:  ``
Org_table:  ``
Type:   DATETIME
Collation:  binary (63)
Length: 19
Max_length: 19
Decimals:   0
Flags:  BINARY


+--+
| date_sub(datetime,interval 1 second) |
+--+
| 2015-09-18 09:23:20  |
+--+
1 row in set (0.00 sec)

MariaDB [testdb]> select substr(datetime,1,13) from test;
Field   1:  `substr(datetime,1,13)`
Catalog:`def`
Database:   ``
Table:  ``
Org_table:  ``
Type:   VAR_STRING
Collation:  utf8_general_ci (33)
Length: 39
Max_length: 13
Decimals:   31
Flags:  NOT_NULL

+---+
| substr(datetime,1,13) |
+---+
| 2015-09-18 09 |
+---+
1 row in set (0.00 sec)

MariaDB [testdb]> select concat(substr(datetime,1,13),':00:00')
 -> from test;
Field   1:  `concat(substr(datetime,1,13),':00:00')`
Catalog:`def`
Database:   ``
Table:  ``
Org_table:  ``
Type:   VAR_STRING
Collation:  utf8_general_ci (33)
Length: 57
Max_length: 19
Decimals:   31
Flags:  NOT_NULL

++
| concat(substr(datetime,1,13),':00:00') |
++
| 2015-09-18 09:00:00|
++
1 row in set (0.00 sec)

MariaDB [testdb]> select concat('2015-09-18 09',':00:00');
Field   1:  `concat('2015-09-18 09',':00:00')`
Catalog:`def`
Database:   ``
Table:  ``
Org_table:  ``
Type:   VAR_STRING
Collation:  utf8_general_ci (33)
Length: 57
Max_length: 19
Decimals:   31
Flags:  NOT_NULL


+--+
| concat('2015-09-18 09',':00:00') |
+--+
| 2015-09-18 09:00:00  |
+--+
1 row in set (0.00 sec)

MariaDB [testdb]> select date_sub(concat('2015-09-18 09',':00:00'),
 -> interval 1 second);
Field   1:  `date_sub(concat('2015-09-18 09',':00:00'),
interval 1 second)`
Catalog:`def`
Database:   ``
Table:  ``
Org_table:  ``
Type:   STRING
Collation:  binary (63)
Length: 19
Max_length: 19
Decimals:   0
Flags:  BINARY


+---+
| date_sub(concat('2015-09-18 09',':00:00'),
interval 1 second) |
+---+
| 2015-09-18 08:59:59   |
+---+
1 row in set (0.00 sec)

MariaDB [testdb]> select
 -> date_sub(concat(substr(datetime,1,13),':00:00'),
 -> interval 1 second) from test;
Field   1:  `date_sub(concat(substr(datetime,1,13),':00:00'),
interval 1 second)`
Catalog:`def`
Database:   ``
Table:  ``
Org_table:  ``
Type:   STRING
Collation:  binary (63)
Length: 26
Max_length: 26
Decimals:   6
Flags:  BINARY

+-

[Maria-discuss] Stored Procedure debugger from Peter Gulutzan and Trudy Pelzer

[Alexander Barkov]

Hi,

Peter Gulutzan and Trudy Pelzer announced their GUI debugger for stored 
procedures.


Sources, binaries and a demo are available here:

http://ocelot.ca/blog/blog/2015/03/02/the-ocelotgui-debugger/

This is a great news.

Greetings.

___
Mailing list: https://launchpad.net/~maria-discuss
Post to : maria-discuss@lists.launchpad.net
Unsubscribe : https://launchpad.net/~maria-discuss
More help   : https://help.launchpad.net/ListHelp

Re: [Maria-discuss] Converting the whole database and tables from one charset+collation to another

[Alexander Barkov]

On 12/10/2014 01:48 PM, Honza Horak wrote:

Google does not provide any source I would be sure of, many different
approaches.. so rather asking here..

Given I have a database with latin1 and some latin* collation, the same
for tables and I want to convert database, tables and data to utf8.

Are the following two commands really all I need to do? I mean if that
is really enough to change the data as well..

   ALTER DATABASE db_name DEFAULT CHARACTER SET utf8 COLLATE
utf8_unicode_ci;


Note, ALTER DATABASE does not convert any tables.
It only changes the database default character set and collation.
They will be used as defaults for all CREATE TABLE executed
after this ALTER DATABASE.

So, you need to loop ALTER TABLE (like the one below) for all tables
you want to convert.



   ALTER TABLE db_table CONVERT TO CHARACTER SET utf8 COLLATE
utf8_unicode_ci;

Thanks ahead for clarification.

Honza

___
Mailing list: https://launchpad.net/~maria-discuss
Post to : maria-discuss@lists.launchpad.net
Unsubscribe : https://launchpad.net/~maria-discuss
More help   : https://help.launchpad.net/ListHelp


___
Mailing list: https://launchpad.net/~maria-discuss
Post to : maria-discuss@lists.launchpad.net
Unsubscribe : https://launchpad.net/~maria-discuss
More help   : https://help.launchpad.net/ListHelp

Re: [Maria-discuss] Issues using the connect engine with ODBC on Linux

[Alexander Barkov]

Hi Eric,

Which version of MariaDB, distribution (source or binary)
and operating system are you using?


On 10/14/2013 11:30 PM, eric hernandez wrote:

Hi,
I am trying to use the Connect engine to read data from Microsoft SQL
Server 2008 R2 from a linux based MariaDB instance.

I am getting the following error.

create table tablea  (

 -  id bigint(20) NOT NULL,

)

 - engine=connect table_type=ODBC

 - block_size=10

 - tabname='tablea'

 - Connection='DSN=MSSQLTestServer'

ERROR 1105 (HY000): Unsupported table type ODBC


I have the underlying linux box talking fine to the MSSQLTestServer
via odbc through the iSQL command.

You can see here where i connect via isql from the command line with no
issue

isql -v MSSQLTestServer sa $sanitized

+---+

| Connected!|

|   |

| sql-statement |

| help [tablename]  |

| quit  |

|   |

+---+

  So odbc is installed and working properly on the linux box. Do you
have any advice on how to get past this error?


Thanks,

-Eric



___
Mailing list: https://launchpad.net/~maria-discuss
Post to : maria-discuss@lists.launchpad.net
Unsubscribe : https://launchpad.net/~maria-discuss
More help   : https://help.launchpad.net/ListHelp



___
Mailing list: https://launchpad.net/~maria-discuss
Post to : maria-discuss@lists.launchpad.net
Unsubscribe : https://launchpad.net/~maria-discuss
More help   : https://help.launchpad.net/ListHelp