June 2014 - developers - lists.mariadb.org

[Maria-developers] Doubt in bin logging for CREATE VIEW
by sriram patil 24 Jun '14

24 Jun '14

Hi All, I was checking the behavior of bin logging in various methods. I found out CREATE VIEW differs a little bit than others in bin logging. The query is written into bin log if query is executed successfully. If there is any error the query is not logged. This behavior is observed across all the commands. In case of CREATE VIEW (method: mysql_create_view file: sql/sql_view.cc), while the bin logging is skipped for most of the errors by jumping to "err" label, it is not skipped in following code snippet (line number: 616) res= mysql_register_view(thd, view, mode); /* View TABLE_SHARE must be removed from the table definition cache in order to make ALTER VIEW work properly. Otherwise, we would not be able to detect meta-data changes after ALTER VIEW. */ if (!res) tdc_remove_table(thd, TDC_RT_REMOVE_ALL, view->db, view->table_name, false); if (mysql_bin_log.is_open()) { // this is where the log is written } If an error occurs in mysql_register_view, e.g. ER_OUT_OF_RESOURCES, ER_TABLE_EXISTS_ERROR, etc. the query is still logged. Is there a specific reason why it is logged even though an error occurs? Or is it a bug? Thanks & Regards, Sriram

4 9

Re: [Maria-developers] [Commits] 7d386bb: MDEV-4549 [PATCH] Clean up code working with ACL tables
by Sergey Vojtovich 23 Jun '14

23 Jun '14

Hi Sergei, ok to push. A few minor questions inline. On Mon, Jun 16, 2014 at 09:34:21AM +0200, serg(a)mariadb.org wrote: > revision-id: 7d386bbce216745ba28df728f45a0b796fcced98 > parent(s): f61f36b386e8d0c6448297bb84c92e8d9b82be6a > committer: Sergei Golubchik > branch nick: maria > timestamp: 2014-06-15 17:12:57 +0200 > message: > > MDEV-4549 [PATCH] Clean up code working with ACL tables > > enum values to index different ACL tables, instead of hard-coded numbers > (even different in diffent functions). Also factor out TABLE_LIST > initialization into a separate function. > > --- > mysql-test/r/not_embedded_server.result | 4 +- > mysql-test/r/sp-destruct.result | 3 - > .../suite/rpl/r/rpl_tmp_table_and_DDL.result | 22 +- > mysql-test/t/not_embedded_server.test | 2 +- > mysql-test/t/sp-destruct.test | 1 - > sql/sql_acl.cc | 533 +++++++++------------ > 6 files changed, 235 insertions(+), 330 deletions(-) > > diff --git a/mysql-test/r/not_embedded_server.result b/mysql-test/r/not_embedded_server.result > index 2295276..6dda855 100644 > --- a/mysql-test/r/not_embedded_server.result > +++ b/mysql-test/r/not_embedded_server.result > @@ -1,4 +1,4 @@ > -call mtr.add_suppression("Can't open and lock privilege tables: Table 'host' was not locked with LOCK TABLES"); > +call mtr.add_suppression("Can't open and lock privilege tables: Table 'roles_mapping' was not locked with LOCK TABLES"); > SHOW VARIABLES like 'slave_skip_errors'; > Variable_name Value > slave_skip_errors OFF Why did it change (here and in other tests)? I didn't notice any table order change in the code. ...skip... > diff --git a/mysql-test/r/sp-destruct.result b/mysql-test/r/sp-destruct.result > index 172e40c..fe68229 100644 > --- a/mysql-test/r/sp-destruct.result > +++ b/mysql-test/r/sp-destruct.result > @@ -128,11 +128,8 @@ CREATE FUNCTION f1() RETURNS INT RETURN 1; > RENAME TABLE mysql.procs_priv TO procs_priv_backup; > FLUSH TABLE mysql.procs_priv; > DROP FUNCTION f1; > -ERROR 42S02: Table 'mysql.procs_priv' doesn't exist > SHOW WARNINGS; > Level Code Message > -Error 1146 Table 'mysql.procs_priv' doesn't exist > -Warning 1405 Failed to revoke all privileges to dropped routine > # Restore the procs_priv table > RENAME TABLE procs_priv_backup TO mysql.procs_priv; > FLUSH TABLE mysql.procs_priv; Hmm... why? ...skip... > diff --git a/sql/sql_acl.cc b/sql/sql_acl.cc > index 5e8e0e7..442d512 100644 > --- a/sql/sql_acl.cc > +++ b/sql/sql_acl.cc > @@ -380,7 +380,7 @@ class ACL_PROXY_USER :public ACL_ACCESS > MYSQL_PROXIES_PRIV_PROXIED_USER, > MYSQL_PROXIES_PRIV_WITH_GRANT, > MYSQL_PROXIES_PRIV_GRANTOR, > - MYSQL_PROXIES_PRIV_TIMESTAMP } old_acl_proxy_users; > + MYSQL_PROXIES_PRIV_TIMESTAMP } proxy_table_fields; > public: > ACL_PROXY_USER () {}; > > @@ -760,6 +760,99 @@ static int traverse_role_graph_down(ACL_USER_BASE *, void *, > int (*) (ACL_USER_BASE *, ACL_ROLE *, void *)); > > /* > + Enumeration of ACL/GRANT tables in the mysql database > +*/ > +enum enum_acl_tables > +{ > + USER_TABLE, > + DB_TABLE, > + TABLES_PRIV_TABLE, > + COLUMNS_PRIV_TABLE, > +#define FIRST_OPTIONAL_TABLE HOST_TABLE > + HOST_TABLE, > + PROCS_PRIV_TABLE, > + PROXIES_PRIV_TABLE, > + ROLES_MAPPING_TABLE, > + TABLES_MAX // <== always the last > +}; > +// bits for init_priv_tables > +static const int Table_user= 1 << USER_TABLE; > +static const int Table_db= 1 << DB_TABLE; > +static const int Table_tables_priv= 1 << TABLES_PRIV_TABLE; > +static const int Table_columns_priv= 1 << COLUMNS_PRIV_TABLE; > +static const int Table_host= 1 << HOST_TABLE; > +static const int Table_procs_priv= 1 << PROCS_PRIV_TABLE; > +static const int Table_proxies_priv= 1 << PROXIES_PRIV_TABLE; > +static const int Table_roles_mapping= 1 << ROLES_MAPPING_TABLE; > + > +const LEX_STRING acl_table_names[]= // matches enum_acl_tables > +{ > + { C_STRING_WITH_LEN("user") }, > + { C_STRING_WITH_LEN("db") }, > + { C_STRING_WITH_LEN("tables_priv") }, > + { C_STRING_WITH_LEN("columns_priv") }, > + { C_STRING_WITH_LEN("host") }, > + { C_STRING_WITH_LEN("procs_priv") }, > + { C_STRING_WITH_LEN("proxies_priv") }, > + { C_STRING_WITH_LEN("roles_mapping") } > +}; > + > +/** > + Initialize a TABLE_LIST array to a set of acl/grant tables. > + All tables will be opened with the same lock type, either read or write. > + > + @retval !0 a pointer to the first initialized element in the TABLE_LIST > + @retval 0 replication filters matched. Abort operation, but return OK (!) > +*/ > +TABLE_LIST *init_priv_tables(THD *thd, TABLE_LIST *tables, > + enum thr_lock_type lock_type, int tables_to_open) Nice clean-up, I assume there was a reason not rewrite open_grant_tables() instead. > +{ > + int prev= -1; > + bzero(tables, sizeof(TABLE_LIST) * TABLES_MAX); > + for (int cur=0, mask=1; mask <= tables_to_open; cur++, mask <<= 1) > + { > + if ((tables_to_open & mask) == 0) > + continue; > + tables[cur].init_one_table(C_STRING_WITH_LEN("mysql"), > + acl_table_names[cur].str, > + acl_table_names[cur].length, > + acl_table_names[cur].str, lock_type); > + tables[cur].open_type= OT_BASE_ONLY; > + if (lock_type >= TL_WRITE_ALLOW_WRITE) > + tables[cur].updating= 1; > + if (cur >= FIRST_OPTIONAL_TABLE) > + tables[cur].open_strategy= TABLE_LIST::OPEN_IF_EXISTS; > + if (prev != -1) > + tables[cur].next_local= tables[cur].next_global= & tables[prev]; > + prev= cur; > + } > + > +#ifdef HAVE_REPLICATION > + if (lock_type >= TL_WRITE_ALLOW_WRITE && thd->slave_thread && !thd->spcont) > + { > + /* > + GRANT and REVOKE are applied the slave in/exclusion rules as they are > + some kind of updates to the mysql.% tables. > + */ > + Rpl_filter *rpl_filter= thd->system_thread_info.rpl_sql_info->rpl_filter; > + if (rpl_filter->is_on() && !rpl_filter->tables_ok(0, tables)) > + return NULL; > + } > +#endif Was it a bug that mysql_grant_role() didn't have this code? open_grant_tables() does updating= 0 after tables_ok(). Is that needed? ...skip... > @@ -5783,8 +5796,9 @@ bool mysql_routine_grant(THD *thd, TABLE_LIST *table_list, bool is_proc, > } > } > > - if (replace_routine_table(thd, grant_name, tables[1].table, *Str, > - db_name, table_name, is_proc, rights, > + if (no_such_table(tables + PROCS_PRIV_TABLE) || > + replace_routine_table(thd, grant_name, tables[PROCS_PRIV_TABLE].table, > + *Str, db_name, table_name, is_proc, rights, > revoke_grant) != 0) > { > result= TRUE; Why no_such_table() here? ...skip... > @@ -6250,7 +6232,7 @@ bool mysql_grant(THD *thd, const char *db, List <LEX_USER> &list, > ulong db_rights= rights & DB_ACLS; > if (db_rights == rights) > { > - if (replace_db_table(tables[1].table, db, *Str, db_rights, > + if (replace_db_table(tables[DB_TABLE].table, db, *Str, db_rights, > revoke_grant)) > result= -1; > } Tabs here (and in a few hunks below). > @@ -6262,9 +6244,11 @@ bool mysql_grant(THD *thd, const char *db, List <LEX_USER> &list, > } > else if (is_proxy) > { > - if (replace_proxies_priv_table (thd, tables[1].table, Str, proxied_user, > - rights & GRANT_ACL ? TRUE : FALSE, > - revoke_grant)) > + if (no_such_table(tables + PROXIES_PRIV_TABLE) || > + replace_proxies_priv_table (thd, tables[PROXIES_PRIV_TABLE].table, > + Str, proxied_user, > + rights & GRANT_ACL ? TRUE : FALSE, > + revoke_grant)) > result= -1; > } > if (Str->is_role()) Why no_such_table() here? ...skip... Regards, Sergey

2 2

[Maria-developers] [GSoC] Week Five!
by sriram patil 22 Jun '14

22 Jun '14

Hi All, Following is the link to my blog post of fifth week into GSoC. http://goo.gl/x7dtcn Suggestions and/or review are welcome! Thanks, Sriram

1 0

[Maria-developers] Java Connector Coding till 21st June
by Puneet Dewan 21 Jun '14

21 Jun '14

Hi Everyone. Test case class(ServersidePreparedStatementTest.java) was written for checking the value of statement id. This week code review was done by Massimo and Georg. There pointers were really valuable as data was not being send in a proper format to the server for execute(com_stmt_execute) method : 1.NullBitMap was set incorrectly. 2.Creation of a new class extending PacketOuptutStream e.g integer of 4 bytes can be sent.Currently if an integer is sent it is sent as a byte. 3.Also I was expecting when we do a *prepare* for a statement like insert into XYZ(id) values(?) where id is of type Integer. Then server shall be returning integer as a type. This was a false assumption.It does not return the type. So now on the basis of value set for *id as example*,decide on the type to be sent from execute. So this week will fix the above things. Looking forward for suggestions. Regards Puneet.

1 0

Re: [Maria-developers] [Commits] 22f532c: MDEV-4260 Don't create frm files for temporary tables
by Sergey Vojtovich 20 Jun '14

20 Jun '14

Hi Sergei, ok to push. One note inline. On Fri, Jun 20, 2014 at 11:29:36AM +0200, serg(a)mariadb.org wrote: > revision-id: 22f532cfb2f40132e2f7b079a3aafb109af92c26 > parent(s): fb8818c1aff9ce67b74f965c019cad93addd811e > committer: Sergei Golubchik > branch nick: maria > timestamp: 2014-06-20 11:29:12 +0200 > message: > > MDEV-4260 Don't create frm files for temporary tables > > * Don't write frm for tmp tables > * pass frm image down to open_table_uncached, when possible > * don't use truncate-by-recreate for temp tables - cannot recreate > without frm, and delete_all_rows is faster anyway > * remove HTON_FLUSH_AFTER_RENAME (BDB-ism, unused for years) > > --- > mysql-test/r/temp_table_frm.result | 20 +++++++++++ > mysql-test/t/temp_table_frm.test | 13 ++++++++ > sql/handler.h | 2 +- > sql/sql_base.cc | 14 +++++++- > sql/sql_base.h | 3 +- > sql/sql_table.cc | 27 +++------------ > sql/sql_truncate.cc | 68 +------------------------------------- > sql/unireg.cc | 3 +- > 8 files changed, 56 insertions(+), 94 deletions(-) > ...skip... > diff --git a/sql/sql_base.cc b/sql/sql_base.cc > index 0558066..7d69ccb 100644 > --- a/sql/sql_base.cc > +++ b/sql/sql_base.cc > @@ -5561,7 +5563,17 @@ TABLE *open_table_uncached(THD *thd, handlerton *hton, > strend(saved_cache_key)+1, tmp_path); > share->db_plugin= ha_lock_engine(thd, hton); > > - if (open_table_def(thd, share, GTS_TABLE | GTS_USE_DISCOVERY)) > + /* > + Use the frm image, if possible, open the file otherwise. > + > + The image might be unavailable in ALTER TABLE, when the discovering > + engine took over the ownership (see TABLE::read_frm_image). > + */ > + int res= frm->str > + ? res= share->init_from_binary_frm_image(thd, false, frm->str, frm->length) Excessive "res="? Regards, Sergey

2 1

[Maria-developers] [GSoC] Week Four!
by sriram patil 18 Jun '14

18 Jun '14

Hi All, Sorry for a little delay in posting the blog link this week. Following is the blog link about my fourth week into GSoC. http://goo.gl/W54cvE Suggestions and/or reviews are welcome! Thanks, Sriram

4 4

Re: [Maria-developers] [Commits] Rev 4258: MDEV-6039 - WebScaleSQL patches in lp:maria/10.0
by Sergei Golubchik 18 Jun '14

18 Jun '14

Hi, Sergey! On Jun 18, Sergey Vojtovich wrote: > At lp:maria/10.0 > > ------------------------------------------------------------ > revno: 4258 > revision-id: svoj(a)mariadb.org-20140618141504-kqq6ws9gm93r2b2d > parent: svoj(a)mariadb.org-20140618081243-jxs6yo9tx9cahdv6 > committer: Sergey Vojtovich <svoj(a)mariadb.org> > branch nick: 10.0 > timestamp: Wed 2014-06-18 18:15:04 +0400 > message: > MDEV-6039 - WebScaleSQL patches > > Stop spawning dummy threads on client library initialization > > Let's revert the fix for Bug#24507. To quote Monty from 2006: > > "After 1/2 a year, when all glibc versions are updated, we can delete > this code." > > Note: The upstream glibc bug was fixed in 2006. Ok to push Regards, Sergei

1 0

Re: [Maria-developers] [GSoC] self-tuning optimizer
by Sergei Golubchik 18 Jun '14

18 Jun '14

Hi, Anshu! Please, don't forget to cc: maria-developers@ when discussing development topics. On Jun 18, Anshu Avinash wrote: > > What I meant to say was we need N "linearly independent" equations to > solve for N variables and that might not be the case. Some of the > queries might not use any factor at all or use one factor more often. Yes, you're right, of course. > > Later we'll see what to do - perhaps we'll need to decrease the limit, > > or set it exactly to N (which means, the solution won't be "best > > fitting" anymore). But this will be not in this GSoC, not your project. > > I'm sure that in this project we won't have enough factors for memory > > usage to become an issue :) > > Yes, I'll start with a basic least squares fitting model without caring > much about the memory usage for now. Sounds good. Regards, Sergei

1 0

Re: [Maria-developers] [GSoC] self-tuning optimizer
by Sergei Golubchik 18 Jun '14

18 Jun '14

Hi, Anshu! On Jun 18, Anshu Avinash wrote: > Hi serg, > > Sorry that I had been out of touch for 1-2 days. I got caught up in some > other work. > > I have read through the comments you made on MDEV and I have just started > the implementation for the first approach, the approximate method. I have > pushed a prototype at: > https://github.com/igniting/server/commit/3bd4f5b6fd20d11575207ac36e8a3cc2c… > > Here is what I'm thinking, we add to the array of measurement data, after > each query and try to solve the system of equations because we don't know > when the system of equations would become solvable. Eh. To solve a system of linear equations with N unknowns, you need N equations. So, you wait until you get N equations and solve it. Few thoughts: 1. it might be that out of all factors that you have in your data structures (currently it's 2 + 2 * MAX_HA), only few will be actually used in queries (for example, only TIME_FOR_COMPARE and read_time for InnoDB). Then you only need two queries to solve that, not 2+2*MAX_HA. But it's an optimization, try to do the basic implementation without it first. 2. You normally store these data (for N queries) in the THD, right? It might be that the thread will disconnect before it'll do the required number of queries. To avoid throwing the useful data away, you can store one extra set of data globally and when some THD disconnects and it doesn't have enough data to solve the equation, you append this data to the global set and solve that as a whole. Again, this is an extension, only do it when the basic implementation works. > However this might be time consuming, trying to solve equations after > each query. Can we store the entire data (from all the queries) and > apply a best fitting model, instead of solving them exactly? This we > will do at disconnect. I don't have a good answer for that. It's a trade-off. Either you collect exactly N equations and solve them. Then you collect the next N equations (or you get another one and delete the first one) - and solve that. And so on. Or you collect many equations, more than N. And find a best fitting solution. This way you do less solving, but you'll need more memory. I think, for now you can do that - use a best fitting solution. We only have very few factors at the moment. Still, even now you'll need some limit to how many equations you'll collect. Later we'll see what to do - perhaps we'll need to decrease the limit, or set it exactly to N (which means, the solution won't be "best fitting" anymore). But this will be not in this GSoC, not your project. I'm sure that in this project we won't have enough factors for memory usage to become an issue :) > Should it be in ~THD() or somewhere else? Yes, ~THD is fine, just make sure you only do it for real connection THD's, where SELECT queries were issues and optimizer was run. Not for various internal THD's, like the one you create in cost_factors::init. For example, as you've noticed, I've mentioned in the last comment about using read_time() result and Nrows_estimated (the same applies to scan_time(), of course). So, when ha_read_time() is called, you need to remember (in your data set) the return value of read_time() and Nrows_estimated (that's the last argument of read_time()). This way, you only solve your equations if you have this read_time() and Nrows_estimated (or scan_time()) stored. If not - the optimizer was never run and you don't do anything. Regards, Sergei

1 0

Re: [Maria-developers] [Commits] Rev 4257: Empty commit to check email setting in commit mails in http://bazaar.launchpad.net/~maria-captains/maria/10.0
by Roberto Spadim 18 Jun '14

18 Jun '14

working :) 2014-06-18 7:53 GMT-03:00 <knielsen(a)knielsen-hq.org>: > At http://bazaar.launchpad.net/~maria-captains/maria/10.0 > > ------------------------------------------------------------ > revno: 4257 > revision-id: knielsen(a)knielsen-hq.org-20140618105333-44ez01iye29sljur > parent: knielsen(a)knielsen-hq.org-20140618090308-frd0tuo8rycgyi32 > committer: Kristian Nielsen <knielsen(a)knielsen-hq.org> > branch nick: work-10.0 > timestamp: Wed 2014-06-18 12:53:33 +0200 > message: > Empty commit to check email setting in commit mails > _______________________________________________ > commits mailing list > commits(a)mariadb.org > https://lists.askmonty.org/cgi-bin/mailman/listinfo/commits > -- Roberto Spadim SPAEmpresarial Eng. Automação e Controle

1 0