- discuss - lists.mariadb.org

[Maria-discuss] Interval in MYsql query
by Dev C 16 May '18

16 May '18

Hello If I use below query (metrics_5.created_at)>'5/10/2018'- 28 ) it gives me some result but if i use below query (metrics_5.created_at)>'5/10/2018'- INTERVAL 28 DAY ) It does not give me any result, any one know why ?

3 2

[Maria-discuss] MySQL Quries and Error
by Dev C 16 May '18

16 May '18

Below is my queries SELECT AdData4w.`Campaign Name`, AdData4w.`Adgroup Name`, AdData4w.`4w Clicks`, AdData1w.`1w Clicks`, AdData4w.`4w Orders`, AdData1w.`1w Orders`, AdData4w.`4w Spend`, AdData1w.`1w Spend`, AdData4w.`4w Sales`, AdData1w.`1w Sales`, AdData4w.`4w Conv Rate`, AdData1w.`1w Conv Rate`, AdData4w.`4w ACOS`, AdData1w.`1w ACOS`, AdData4w.`4w CPC`, AdData1w.`1w CPC`,AdData4w.`4w Bid`, AdData1w.`1w Bid` FROM AdData4w INNER JOIN AdData1w ON (AdData4w.`Adgroup Name` = AdData1w.`Adgroup Name`) AND (AdData4w.`Campaign Name` = AdData1w.`Campaign Name`) GROUP BY AdData4w.`Campaign Name`, AdData4w.`Adgroup Name`, AdData4w.`4w Clicks`, AdData1w.`1w Clicks`, AdData4w.`4w Orders`, AdData1w.`1w Orders`, AdData4w.`4w Spend`, AdData1w.`1w Spend`, AdData4w.`4w Sales`, AdData1w.`1w Sales`, AdData4w.`4w Conv Rate`, AdData1w.`1w Conv Rate`, AdData4w.`4w ACOS`, AdData1w.`1w ACOS`, AdData4w.`4w CPC`, AdData1w.`1w CPC`, AdData4w.`4w Bid`, AdData1w.`1w Bid` From (SELECT campaigns_5.name AS `Campaign Name`, adgroups_5.name AS `Adgroup Name`, Sum(metrics_5.clicks) AS `1w Clicks`, Sum(metrics_5.orders) AS `1w Orders`, Sum(metrics_5.costs) AS `1w Spend`, Sum(metrics_5.sales) AS `1w Sales`, If(Sum(clicks)=0,0,Sum(orders)/Sum(clicks)) AS `1w Conv Rate`, If(Sum(metrics_5.sales)=0,0,Sum(metrics_5.costs)/Sum(metrics_5.sales)) AS `1w ACOS`, IF (Sum(clicks)=0,0,Sum(costs)/Sum(clicks)) AS `1w CPC`, Avg(bid_info_5.value) AS `1w Bid` FROM ((adgroups_5 INNER JOIN campaigns_5 ON adgroups_5.campaignId = campaigns_5.campaignId) INNER JOIN metrics_5 ON adgroups_5.adGroupId = metrics_5.type_id) INNER JOIN bid_info_5 ON adgroups_5.adGroupId = bid_info_5.type_id WHERE (((metrics_5.created_at)>'5/10/2018'-7)) GROUP BY campaigns_5.name, adgroups_5.name, campaigns_5.user_market_id, RIGHT(campaigns_5.name,4) HAVING (((campaigns_5.user_market_id)=12) AND ((Right(campaigns_5.name,4))="PFUS") AND ((Sum(metrics_5.impressions))>0)) ORDER BY campaigns_5.name, adgroups_5.name) AS AdData1w INNER JOIN (SELECT campaigns_5.name AS `Campaign Name`, adgroups_5.name AS `Adgroup Name`, Sum(metrics_5.clicks) AS `4w Clicks`, Sum(metrics_5.orders) AS `4w Orders`, Sum(metrics_5.costs) AS `4w Spend`, Sum(metrics_5.sales) AS `4w Sales`, If(Sum(clicks)=0,0,Sum(orders)/Sum(clicks)) AS `4w Conv Rate`, If(Sum(metrics_5.sales)=0,0,Sum(metrics_5.costs)/Sum(metrics_5.sales)) AS `4w ACOS`, If(Sum(clicks)=0,0,Sum(costs)/Sum(clicks)) AS `4w CPC`, Avg(bid_info_5.value) AS `4w Bid` FROM ((adgroups_5 INNER JOIN campaigns_5 ON adgroups_5.campaignId = campaigns_5.campaignId) INNER JOIN metrics_5 ON adgroups_5.adGroupId = metrics_5.type_id) INNER JOIN bid_info_5 ON adgroups_5.adGroupId = bid_info_5.type_id WHERE (((metrics_5.created_at)>'5/10/2018'-28)) GROUP BY campaigns_5.name, adgroups_5.name, campaigns_5.user_market_id, RIGHT(campaigns_5.name,4) HAVING (((campaigns_5.user_market_id)=12) AND ((Right(campaigns_5.name,4))="PFUS") AND ((Sum(metrics_5.impressions))>0)) ORDER BY campaigns_5.name, adgroups_5.name) AS AdData4w it gives error on as below 4 You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'From (SELECT campaigns_5.name AS `Campaign Name`, adgroups_5.name AS `Adgroup N' at line 4 Query12.sql 1 1 Please assist.

2 1

[Maria-discuss] [question] password input length
by Michal Schorm 16 May '18

16 May '18

Hello everyone, 1) I found out, that passwords longer than 79 characters aren't accepted via password prompt. Is that expected? Morie info and reproducer in RH bugzilla: https://bugzilla.redhat.com/show_bug.cgi?id=1566179 -- 2) Another password length cap at 213 characters can be found when using sha256_password authentification plugin. Is that expected too? Morie info and reproducer in RH bugzilla: https://bugzilla.redhat.com/show_bug.cgi?id=1566219 -- 3) Different theme: why aren't tests for mariadb-connector-odbc, that can be found on GitHub, packged to the source tarball ? -- Michal Schorm Associate Software Engineer Core Services - Databases Team Red Hat

2 2

[Maria-discuss] Installing MariaDB Alongside MySQL
by Muhammad Bashir Al-Noimi 15 May '18

15 May '18

4 27

[Maria-discuss] Charsets and collations
by Peter Laursen 20 Apr '18

20 Apr '18

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? -- Peter -- Webyog

2 2

[Maria-discuss] MariaDB Developers Unconference in June
by Ian Gilfillan 10 Apr '18

10 Apr '18

The next MariaDB Developers Unconference is going to be held in Tampera, Finland, from 26 June to 29 June. This will be a four-day event; the first day focusing on new contributors, the 2nd and 3rd the regular Unconference sessions and discussions, and the 4th day on getting patches reviewed and implemented, taking advantage of the chance to have in-person discussions. Details, as well as where to sign up, are at https://mariadb.org/2018-2-developers-unconference-in-finland/

1 0

[Maria-discuss] MDEV-15765 BETWEEN not working in certain cases
by Michael Caplan 08 Apr '18

08 Apr '18

Hello, I've just stalled a rollout to production of 10.2.14 because of this what I think is rather huge SQL break (https://jira.mariadb.org/browse/MDEV-15765) Anyone else holding 10.2.14 from production? Anyone running 10.2.14 in production? Trying to get some additional perspective on this issue. Thanks, Mike

1 0

[Maria-discuss] Replication New to Old
by Michael Caplan 08 Apr '18

08 Apr '18

Hello, I realize that in general replication from a newer master to an older slave is typically not recommended. This said, does anyone have an experience replicating from MariaDB 10.2 to MySQL 5.6? The compatibility matrix found here: https://mariadb.com/kb/en/library/mariadb-vs-mysql-compatibility/ Seems to be missing data on this type of setup. Thanks, Mike

5 6

[Maria-discuss] galera & restore a database
by lejeczek 28 Mar '18

28 Mar '18

hi guys I have mariadb in a three nodes galera cluster, when I restore a database on a node: $ mysql wordpress_local < db.sql it works well on that node but the remaining two nodes database tables are empty. Tables structure is there, but no records. What am I missing, doing wrong? many thanks, L.

1 0

[Maria-discuss] MDEV-10286 - Adjustment of table_open_cache according to system limits does not work when open-files-limit option is provided
by Sergey Vojtovich 21 Mar '18

21 Mar '18

Hi Monty, > diff --git a/mysql-test/mysql-test-run.pl b/mysql-test/mysql-test-run.pl > index 55543e529a6..54dc0aa17cc 100755 > --- a/mysql-test/mysql-test-run.pl > +++ b/mysql-test/mysql-test-run.pl > @@ -4469,6 +4469,7 @@ sub extract_warning_lines ($$) { > qr/InnoDB: See also */, > qr/InnoDB: Cannot open .*ib_buffer_pool.* for reading: No such file or directory*/, > qr/InnoDB: Table .*mysql.*innodb_table_stats.* not found./, > + qr/Changed limits: max_open_files/, I wonder how did you get this warning? > qr/InnoDB: User stopword table .* does not exist./ > > ); > diff --git a/sql/mysqld.cc b/sql/mysqld.cc > index b21d50a20fe..b3414279ac9 100644 > --- a/sql/mysqld.cc > +++ b/sql/mysqld.cc > @@ -4446,11 +4446,18 @@ static int init_common_variables() > > /* connections and databases needs lots of files */ > { > - uint files, wanted_files, max_open_files; > + uint files, wanted_files, max_open_files, min_tc_size, extra_files, > + min_connections; > + ulong org_max_connections= max_connections, org_tc_size= tc_size; > > + /* Number of files reserved for temporary files */ > + extra_files= 30; > + min_connections= 10; > /* MyISAM requires two file handles per table. */ > - wanted_files= (10 + max_connections + extra_max_connections + > + wanted_files= (extra_files + max_connections + extra_max_connections + > tc_size * 2); > + min_tc_size= MY_MIN(tc_size, TABLE_OPEN_CACHE_MIN); > + > /* > We are trying to allocate no less than max_connections*5 file > handles (i.e. we are trying to set the limit so that they will I believe the following is easier to read: ulong org_max_connections= max_connections; ulong org_tc_size= tc_size; ulong min_tc_size= MY_MIN(tc_size, TABLE_OPEN_CACHE_MIN); /* Number of files reserved for temporary files */ uint extra_files= 30; uint min_connections= 10; /* MyISAM requires two file handles per table. */ uint wanted_files= extra_files + max_connections + extra_max_connections + tc_size * 2; /* We are trying to allocate no less than max_connections*5 file handles (i.e. we are trying to set the limit so that they will be available). In addition, we allocate no less than how much was already allocated. However below we report a warning and recompute values only if we got less file handles than were explicitly requested. No warning and re-computation occur if we can't get max_connections*5 but still got no less than was requested (value of wanted_files). */ uint max_open_files= MY_MAX(MY_MAX(wanted_files, (max_connections + extra_max_connections)*5), open_files_limit); uint files= my_set_max_open_files(max_open_files); > @@ -4463,43 +4470,43 @@ static int init_common_variables() > */ > max_open_files= MY_MAX(MY_MAX(wanted_files, > (max_connections + extra_max_connections)*5), > - open_files_limit); > + open_files_limit); > files= my_set_max_open_files(max_open_files); > > - if (files < wanted_files) > - { > - if (!open_files_limit || IS_SYSVAR_AUTOSIZE(&open_files_limit)) > - { > - /* > - If we have requested too much file handles than we bring > - max_connections in supported bounds. > - */ > - SYSVAR_AUTOSIZE(max_connections, > - (ulong) MY_MIN(files-10-TABLE_OPEN_CACHE_MIN*2, max_connections)); > - /* > - Decrease tc_size according to max_connections, but > - not below TABLE_OPEN_CACHE_MIN. Outer MY_MIN() ensures that we > - never increase tc_size automatically (that could > - happen if max_connections is decreased above). > - */ > - SYSVAR_AUTOSIZE(tc_size, > - (ulong) MY_MIN(MY_MAX((files - 10 - max_connections) / 2, > - TABLE_OPEN_CACHE_MIN), tc_size)); > - DBUG_PRINT("warning", > - ("Changed limits: max_open_files: %u max_connections: %ld table_cache: %ld", > - files, max_connections, tc_size)); > - if (global_system_variables.log_warnings > 1) > - sql_print_warning("Changed limits: max_open_files: %u max_connections: %ld table_cache: %ld", > - files, max_connections, tc_size); > - } > - else if (global_system_variables.log_warnings) > - sql_print_warning("Could not increase number of max_open_files to more than %u (request: %u)", files, wanted_files); > - } > - SYSVAR_AUTOSIZE(open_files_limit, files); > + if (files < wanted_files && global_system_variables.log_warnings) > + sql_print_warning("Could not increase number of max_open_files to more than %u (request: %u)", files, wanted_files); > + > + /* > + If we have requested too much file handles than we bring > + max_connections in supported bounds. Still leave at least > + 'min_connections' connections > + */ > + SYSVAR_AUTOSIZE(max_connections, > + (ulong) MY_MAX(MY_MIN(files- extra_files- min_tc_size*2, > + max_connections), min_connections)); > + /* > + Decrease tc_size according to max_connections, but > + not below min_tc_size. Outer MY_MIN() ensures that we > + never increase tc_size automatically (that could > + happen if max_connections is decreased above). > + */ > + SYSVAR_AUTOSIZE(tc_size, > + (ulong) MY_MIN(MY_MAX((files - extra_files - > + max_connections) / 2, min_tc_size), > + tc_size)); > + DBUG_PRINT("warning", > + ("Current limits: max_open_files: %u max_connections: %ld table_cache: %ld", > + files, max_connections, tc_size)); > + if (global_system_variables.log_warnings > 1 && > + (max_connections < org_max_connections || > + tc_size < org_tc_size)) > + sql_print_warning("Changed limits: max_open_files: %u max_connections: %lu (%lu) table_cache: %lu (%lu)", I'd say this format is quite confusing. Probably add something like "was %lu" in brackets? > + files, max_connections, org_max_connections, > + tc_size, org_tc_size); I think the above code must be executed under "if (files < wanted_files)". We don't really want to "autosize" max_connections and tc_size if we got enough file descriptors. Also you missed to "autosize" open_files_limit. I think it should be done "if (open_files_limit != files) SYSVAR_AUTOSIZE(open_files_limit, files);". > } > > /* > - Max_connections is now set. > + Max_connections and tc_cache is now set. s/is/are/ > Now we can fix other variables depending on this variable. > */ > > diff --git a/sql/sql_const.h b/sql/sql_const.h > index 65742235bee..e28a0649f04 100644 > --- a/sql/sql_const.h > +++ b/sql/sql_const.h > @@ -127,7 +127,7 @@ > #define MAX_FIELDS_BEFORE_HASH 32 > #define USER_VARS_HASH_SIZE 16 > #define SEQUENCES_HASH_SIZE 16 > -#define TABLE_OPEN_CACHE_MIN 400 > +#define TABLE_OPEN_CACHE_MIN 200 > #define TABLE_OPEN_CACHE_DEFAULT 2000 > #define TABLE_DEF_CACHE_DEFAULT 400 > /** > diff --git a/sql/sys_vars.cc b/sql/sys_vars.cc > index de9806ab289..41317a5c259 100644 > --- a/sql/sys_vars.cc > +++ b/sql/sys_vars.cc > @@ -3477,7 +3477,7 @@ static bool fix_table_open_cache(sys_var *, THD *, enum_var_type) > static Sys_var_ulong Sys_table_cache_size( > "table_open_cache", "The number of cached open tables", > GLOBAL_VAR(tc_size), CMD_LINE(REQUIRED_ARG), > - VALID_RANGE(1, 1024*1024), DEFAULT(TABLE_OPEN_CACHE_DEFAULT), > + VALID_RANGE(10, 1024*1024), DEFAULT(TABLE_OPEN_CACHE_DEFAULT), Why? There're at least a few test cases that do table_open_cache=1: partition_open_files_limit, sp, myisam. > BLOCK_SIZE(1), NO_MUTEX_GUARD, NOT_IN_BINLOG, ON_CHECK(0), > ON_UPDATE(fix_table_open_cache)); > Regards, Sergey

2 1