- developers - lists.mariadb.org

Re: [Maria-developers] 72b709a: MDEV-9701: CREATE VIEW with GROUP BY or ORDER BY and constant produces invalid definition
by Sergei Golubchik 17 Mar '16

17 Mar '16

Hi, Sanja! On Mar 16, OleksandrByelkin wrote: > revision-id: 72b709ac7503ae6dd2b5e9049322fefb90b0ebbe (mariadb-10.1.12-16-g72b709a) > parent(s): 9b53d84d14a9b031d193f6beae382a232aa738e3 > committer: Oleksandr Byelkin > timestamp: 2016-03-16 19:49:17 +0100 > message: > > MDEV-9701: CREATE VIEW with GROUP BY or ORDER BY and constant produces invalid definition > > Fixed printing integer constant in the ORDER clause (MySQL solution) > Removed workaround for double resolving counter in the ORDER. > > --- > mysql-test/r/view.result | 19 +++++++++++++++++++ > mysql-test/t/view.test | 17 +++++++++++++++++ > sql/sql_lex.cc | 32 ++++++++++++-------------------- > sql/sql_select.cc | 6 +++++- > sql/sql_union.cc | 11 +++++++++++ > 5 files changed, 64 insertions(+), 21 deletions(-) > > diff --git a/mysql-test/r/view.result b/mysql-test/r/view.result > index 8ee72c3..d4a2a9b 100644 > diff --git a/sql/sql_lex.cc b/sql/sql_lex.cc > index fd84109..31fc5f9 100644 > --- a/sql/sql_lex.cc > +++ b/sql/sql_lex.cc > @@ -2689,30 +2689,22 @@ void st_select_lex::print_order(String *str, > { > if (order->counter_used) > { > - if (!(query_type & QT_VIEW_INTERNAL)) > - { > char buffer[20]; > size_t length= my_snprintf(buffer, 20, "%d", order->counter); > str->append(buffer, (uint) length); you can replace these three lines with str->append_ulonglong(order->counter); > } > else > { > - /* replace numeric reference with expression */ > + /* replace numeric reference with equivalent for ORDER constant */ > if (order->item[0]->type() == Item::INT_ITEM && > order->item[0]->basic_const_item()) > { > - char buffer[20]; > - size_t length= my_snprintf(buffer, 20, "%d", order->counter); > - str->append(buffer, (uint) length); > /* make it expression instead of integer constant */ > - str->append(STRING_WITH_LEN("+0")); > + str->append(STRING_WITH_LEN("''")); > } > else > (*order->item)->print(str, query_type); > } > - } > - else > - (*order->item)->print(str, query_type); > if (!order->asc) > str->append(STRING_WITH_LEN(" desc")); > if (order->next) > diff --git a/sql/sql_select.cc b/sql/sql_select.cc > index fff24b9..f14e8b1 100644 > --- a/sql/sql_select.cc > +++ b/sql/sql_select.cc > @@ -21874,7 +21874,11 @@ find_order_in_list(THD *thd, Item **ref_pointer_array, TABLE_LIST *tables, > */ > if (order_item->type() == Item::INT_ITEM && order_item->basic_const_item()) > { /* Order by position */ > - uint count= (uint) order_item->val_int(); > + uint count; > + if (order->counter_used) how can this happen? > + count= order->counter; // counter was once resolved > + else > + count= (uint) order_item->val_int(); > if (!count || count > fields.elements) > { > my_error(ER_BAD_FIELD_ERROR, MYF(0), > diff --git a/sql/sql_union.cc b/sql/sql_union.cc > index c835083..8145cec 100644 > --- a/sql/sql_union.cc > +++ b/sql/sql_union.cc > @@ -1161,11 +1161,22 @@ List<Item> *st_select_lex_unit::get_unit_column_types() > return &sl->item_list; > } > > + > +static void cleanup_order(ORDER *order) > +{ > + for (; order; order= order->next) > + order->counter_used= 0; > +} > + > + > bool st_select_lex::cleanup() > { > bool error= FALSE; > DBUG_ENTER("st_select_lex::cleanup()"); > > + cleanup_order(order_list.first); > + cleanup_order(group_list.first); why is it needed? > if (join) > { > DBUG_ASSERT((st_select_lex*)join->select_lex == this); Regards, Sergei Chief Architect MariaDB and security(a)mariadb.org

2 3

Re: [Maria-developers] [Commits] 1e542ec: MDEV-9382: After updating mariadb server apt-configure fails
by Sergei Golubchik 17 Mar '16

17 Mar '16

Hi, Nirbhay! Ok to push. And, please, check whether /etc/default/mariadb needs to be mentioned in the corresponding (mariadb on debian/ubuntu) knowledgebase pages. On Mar 16, Nirbhay Choubey wrote: > revision-id: 1e542ecd8af6f78db767a9b3da016b38d1ec3970 (mariadb-10.1.12-6-g1e542ec) > parent(s): b3bde93149e7135507fd7abbca2786d77552953d > author: Nirbhay Choubey > committer: Nirbhay Choubey > timestamp: 2016-03-16 19:39:19 -0400 > message: > > MDEV-9382: After updating mariadb server apt-configure fails > > When acting as a Galera receiver node, server startup may take > more than 30 secs (the current default) as it has to wait for > SST/IST operation to complete besides spending some time doing > wsrep recovery. > > Fixed by raising the default value of MYSQLD_STARTUP_TIMEOUT > to 60 secs. Also sourced /etc/default/mariadb into the init > script so that it can be used to set MYSQLD_STARTUP_TIMEOUT. > Regards, Sergei Chief Architect MariaDB and security(a)mariadb.org

1 0

[Maria-developers] NO PAD Collation for MariaDB
by Melissa Rose 17 Mar '16

17 Mar '16

Hello everyone, I am Melisa Rose from University of Leeds UK and I wish to contribute to MariaDB under GSoC 2016. I am interested in the project involving adding NO PAD collations to default character sets(MDEV-9711 <https://jira.mariadb.org/browse/MDEV-9711>). Apparently, this project does not require thorough understanding of the Server Internal code and the Instructions highlighted by Alexander Barkov on how to implement this are straight forward. I will be submitting a proposal for this project hoping i'm linked to the assigned mentor soon. Look forward to an awesome summer with MariaDB. Kind regards, Melisa Rose.

2 3

Re: [Maria-developers] [Maria-discuss] Collations, trailing spaces and unique indexes
by Alexander Barkov 17 Mar '16

17 Mar '16

Hi Binarus, On 03/16/2016 06:13 PM, Binarus wrote: >> I just wanted to split the task into these steps: >> >> 1. Default and _bin collations. >> This is what MDEV-9711 is about. >> >> 2. Unicode Collation Algorithm (UCA) based collations >> (e.g. utf8_unicode_nopad_ci) >> >> >> 3. The rest (not covered by the previous steps) >> This will include latin1_general_nopad_cs, latin2_czech_nopad_cs >> >> >> This is why asked in the previous letter if utf8_unicode_nopad_ci would >> work for you. >> Also, which MariaDB version do you need this for? Will 10.1 work? >> > > Thank you very much for this explanation. > > Regarding the version: Our O/S usually is Debian which generally uses software which is quite old. This means that I will have to compile MariaDB myself in every case. > The current project where this new collations are needed will probably take about three months before being finished. It would be very nice if we could have the new collations until then. > In the meantime, we'll continue development with MySQL and switch to MariaDB as soon as the new collations are available. Excellent. The fact that you compile from sources makes the things easier. I guess we can add new collations officially into 10.2. Then after 3 month if you'll feel that 10.2 is not mature enough for you, we can make a custom non-official 10.1 tree with the new collations back-ported (it should be trivial to do). > > ci collations would be sufficient as a starting point. Nevertheless, I think there must be a nopad collation for every collation which already exists. > Personally, since we are using utf8 (and no other encoding) in every part of every project, I would prefer to have *all utf8* nopad collations before >having any other nopad collation. But that's just my personal opinion. >We will be grateful for any progress regardless of the order of things being implemented. Thanks. Then we're flexible to implement #2 first, and wait for #1 to be done under terms of Google Summer Of Code 2016. > > Binarus > > _______________________________________________ > Mailing list: https://launchpad.net/~maria-discuss > Post to : maria-discuss(a)lists.launchpad.net > Unsubscribe : https://launchpad.net/~maria-discuss > More help : https://help.launchpad.net/ListHelp >

1 0

Re: [Maria-developers] [Maria-discuss] Collations, trailing spaces and unique indexes
by Binarus 17 Mar '16

17 Mar '16

Alexander, I'd like to continue our conversation from Maria-discuss. >> - I have found my_strnxfrm_unicode in the same file and will need more time to make my opinion of how difficult it will be >> (I don't know what a weight is, so I currently try to understand what the function does at all). > > This function is used to create sort keys for non-indexed ORDER BY, > for these cases: > > - ORDER BY on an expression > - ORDER BY on a column that does not have an index > > > The idea is exactly the same with the C function strxfrm. > See "man strxfrm". > > The code implements non-indexed ORDER BY in filesort.cc > as follows: > > 1. It calls *_strnxfrm_* functions for all records and converts > CHAR/VARCHAR/TEXT values into their fixed length binary sortable keys. > > 2 . Then executes binary sorting on these keys. > > By the way, fixing this function might be tricky. > > > Currently my_strnxfrm_unicode() pads the tail using weights of the SPACE > character. > The NO PAD version will need to pad the tail using a weight which is > less than the weight of the smallest possible character. > > This should be easy for UCA bases collations (e.g. > utf8_unicode_nopad_ci), because the smallest possible > character in UCA collations is "U+0009 HORIZONTAL TABULATION", > and its weight is 0x0201. So we can just pad the sort key > using a smaller value 0x0200. > > > But I'm not sure yet what to do with 8-bit collations, > which usually use 0x00 as weight for the smallest character. > So we don't have a smaller value. > There are two options here: > > 1. Pad with 0x00. But this will mean that 'aaa<min>' and just 'aaa' > will have unpredictable order when doing ORDER BY without an index > (where <min> is the smallest possible character in the collation). > > As the smallest character in non-UCA collations is usually > "U+0000 NULL", this will mean that 'aaa\0' and just 'aaa' > will have unpredictable order. > > 2. Reserve extra bytes at the end of the key, to store the true length, so > - 'aaa\0' will have the key '4141410004' > - 'aaa' will have the key '4141410003', and therefore will always > be sorted before 'aaa\0'. > > I'm inclined towards #2, to have consistent ORDER BY behavior > with and without indexes. Wow, thanks for explaining. I now have learned that I probably can't assist you in solving that problem (I haven't been in unicode, weights, sorting and character sets very deep until now). But if it helps, you eventually could break the problem into two steps: 1) Fix the problem with unique indexes 2) Fix ordering for any possible situation The problems we have encountered in real life so far are all related to 1), i.e. we would be happy if unique indexes would consider 'a' and 'a ' being different values. In contrast, ordering is not that important for us. I don't know what others are thinking about that, though. >> >> - 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". OK, thank you very much. I must have misunderstood something. >> Furthermore, studying the code has led to some questions; for example, there already seems to be a #define which controls the padding-when-comparing mode, but only for the _cs collations? > > Can you please clarify which lines do you mean? I am referring to the file ctype-utf8.c, line 5698. There is: #ifndef VARCHAR_WITH_DIFF_ENDSPACE_ARE_DIFFERENT_FOR_UNIQUE diff_if_only_endspace_difference= 0; #endif I haven't been able yet to thoroughly analyze where and how this is used, but at least somebody obviously already had some thoughts whether or not trailing spaces should make a difference in UNIQUEs :-) Regards, Binarus

2 1

Re: [Maria-developers] bf2eaa7: MDEV-9382: After updating mariadb server apt-configure fails
by Sergei Golubchik 16 Mar '16

16 Mar '16

Hi, Nirbhay! On Mar 02, Nirbhay Choubey wrote: > revision-id: bf2eaa7c1548f07ce5de13a406ff9183f082b8b3 (mariadb-10.1.12-4-gbf2eaa7) > parent(s): fd741c1a12e78035dcef9a5684b530fe09cbfdde > author: Nirbhay Choubey > committer: Nirbhay Choubey > timestamp: 2016-03-02 09:41:03 -0500 > message: > > MDEV-9382: After updating mariadb server apt-configure fails > > When acting as a Galera receiver node, server startup may take > more than 30 secs (the current default) as it has to wait for > SST/IST operation to complete besides spending some time doing > wsrep recovery. > > In specific case of this MDEV, the server startup timedout as > it took ~48 secs for the node to fully start. > > A viable workaround would be to adjust the MYSQLD_STARTUP_TIMEOUT > env to a higher value, but raising the default to 60 secs should > fix scenarios like these. > > --- > debian/mariadb-server-10.1.mysql.init | 2 +- > 1 file changed, 1 insertion(+), 1 deletion(-) > > diff --git a/debian/mariadb-server-10.1.mysql.init b/debian/mariadb-server-10.1.mysql.init > index 9e098b4..005d0e8 100644 > --- a/debian/mariadb-server-10.1.mysql.init > +++ b/debian/mariadb-server-10.1.mysql.init > @@ -109,7 +109,7 @@ case "${1:-''}" in > /usr/bin/mysqld_safe "${@:2}" > /dev/null 2>&1 & > > # 6s was reported in #352070 to be too little > - for i in $(seq 1 "${MYSQLD_STARTUP_TIMEOUT:-30}"); do > + for i in $(seq 1 "${MYSQLD_STARTUP_TIMEOUT:-60}"); do > sleep 1 This doesn't fix much. Next time somebody will need 61 second and you'll adjust to 120? SST might take many minutes and it's not a good idea to have every user wait this long. Instead you should do what MDEV-8509 says. Add a line like [ -r /etc/default/mariadb ] && . /etc/default/mariadb then one can have a very personal setting of MYSQLD_STARTUP_TIMEOUT in /etc/default/mariadb Regards, Sergei Chief Architect MariaDB and security(a)mariadb.org

3 3

[Maria-developers] [GSoC 2016] NO PAD Collations
by Даниил Медведев 16 Mar '16

16 Mar '16

Hi, I am Daniil Medvedev from Lomonosov Moscow State University, a 3rd year student, pursuing my bachelor’s degree in Computer Science. I was interested in project idea NO PAD Collations, and I wish to contribute to MariaDB using my skills and ideas. Technologies that i know: C/C++, git, linux, shell, cmake, some databases. My experience: Some projects on github: https://github.com/medvdanil And I contributed one open source NoSQL DBMS on github last summer. Also I am ready to perform some opening task or do commit in your repo, if it helps me to join GSoC. Thank you, Daniil Medvedev

3 2

[Maria-developers] Gsoc 2016 Mdev 371 Unique index for blob
by Sachin Setia 16 Mar '16

16 Mar '16

Dear Developers, I was doing prototype for this project as Mr Sergei Golubchik suggested "This task is about creating MI_UNIQUEDEF "uniques" instead of MI_KEYDEF "keys" for long unique constraints." Here is my output MariaDB [sachin]> create table tbl(int_key int primary key , blob_key blob unique); Query OK, 0 rows affected (0.03 sec) MariaDB [sachin]> insert into tbl values(1,1); Query OK, 1 row affected (0.01 sec) MariaDB [sachin]> insert into tbl values(2,1); ERROR 1062 (23000): Duplicate entry '1' for key 'blob_key' MariaDB [sachin]> insert into tbl values(2,2); Query OK, 1 row affected (0.00 sec) MariaDB [sachin]> insert into tbl values(3,3); Query OK, 1 row affected (0.00 sec) MariaDB [sachin]> select * from tbl; +---------+----------+ | int_key | blob_key | +---------+----------+ | 1 | 1 | | 2 | 2 | | 3 | 3 | +---------+----------+ 3 rows in set (0.00 sec) I am assuming that unique blob will have 0 zero key . Should i mail you the source code.Please let me know Regards sachin

2 4

[Maria-developers] Introduction - GSOF16
by Chandu Maddila 16 Mar '16

16 Mar '16

Hi Team, I'm Chandra Sekhar Maddila from India and currently masters student of BITS-Pilani. I'm pretty interested in working with MariaDB team as part of GSOF. Some of the items that I'm interested in are MXS-1, MDEV-8100, MDEV-5662, MDEV-371 Here's a quick summary of my work experience and Skill set. - Currently a student of BITS-Pilani <http://www.bits-pilani.ac.in/> (Masters, Computer Science). - Total experience of 6 years in IT industry, as a software developer (My last stint was in *Microsoft Research *[MSR] - India as a research SDE). - Have been working with designing and development of RDBMS systems : With Oracle earlier and SQL Server after joining Microsoft - Involved in various projects like :* Implementing column store indexes in SQL Server, Designing a cloud based monitoring systems for database performance monitoring, tools to migrate data from Oracle to SQL Server, Implementing HA Solution (AlwaysOn) for SQL Server etc...* - Languages/Frameworks: - Asp.Net, C#, Jquery, Javascript, HTML5, CSS, Bootstrap, Knockout, SOAP, REST, JSON, Web Services - WCF, MVVM, XAML, Windows App Development, Azure Cloud services, Azure web services - Java, J2EE, UNIX Shell scripting I worked extensively on both *UNIX and Windows platforms*. *Database domain expertise:* *Oracle* + In depth understanding of Oracle database internals and architecture (Memory management, storage engine and optimizer) + *Performance tuning*: Instance and SQL level tuning with the help of *AWR, Statspack, TKPROF* reports and Explain plans and tools like *OEM* and *TOAD* + Oracle *11g, 10g, 9i* Database Setup, Administration, Monitoring on various flavors of UNIX: RHEL, AIX, SUN Solaris, HPUX – Itanium chipset; Good understanding of Oracle internals and Oracle system architecture + Oracle *RAC, ASM and Grid infrastructure* setup, maintenance and administration; Non RAC to RAC, Non-ASM to ASM conversions; Familiarity with OS level clustering techniques like *HACMP* + All kinds of Database *Backup and Recovery* methodologies: *RMAN *(Hot, Cold, Active Database Duplication)*, Datapump, SQL Loader* etc…; Familiarity with 3rd party backup tools like *CA ArcServe* *SQL Server* + Working experience with SQL 2014, 2012, 2008R2, 2008 and 2005 on different versions of Windows + Very good at *troubleshooting complex issues and working on outages/showstoppers*. + Very good in *database performance and query tuning*: Analyzing *execution plans* and rewriting queries + Administering SQL Server High Availability and Disaster Recovery technologies like *AlwaysOn, Failover Clustering *and *Database mirroring*. + Proficiency and expertise in all varieties of *SQL Server Replication*: *Snapshot Replication, Transactional replication, Merge replication and Change tracking* + Expertise in handling all sorts of *backup technologies: Native, VSS, VDI, Backup to URL etc…* + Expert in handling *database corruption cases and recovering* the databases + Expertise in T-SQL writing stored procedures, triggers and functions. For any queries, feel free to reach out to me @ chandu.maddila(a)gmail.com Thanks, Chandra

2 1

Re: [Maria-developers] Gsoc 2016 Mdev 371 Unique index for blob
by Sergei Golubchik 15 Mar '16

15 Mar '16

Hi, Sachin! First: please send questions like this to maria-developers(a)lists.launchpad.net it's a public mailing list dedicated to MySQL and MariaDB internals, source code, and related things. I am subscribed, so I'll see you mail there, and you may be sure I will, because it won't be accidentally catched by my spam filter, or sorted out in some obscure folder. Furthermore other subscribers will see your question and could reply if I will be not available (e.g. I could be travelling). Thank you. On Mar 14, Sachin Setia wrote: > Thank you sir for your reply but my question is what this pack flag will do > and basically what is pack record will you please suggest source file in > which i can look into to get more understanding of pack length ,pack flag > and pack record There isn't much I can answer - your question is too generic. "pack_flag" is a set of flags that is stored per field in the frm file and it describes some aspects of the field. Search for "pack_flag" in the source tree. If you ask a specific question, I'll be able to provide a more detailed answer. Regards, Sergei Chief Architect MariaDB and security(a)mariadb.org

1 0