April 2013 - developers - lists.mariadb.org

[Maria-developers] Flashback Feature patch review
by Lixun Peng 22 Aug '13

22 Aug '13

Hi Monty, I patched my flashback feature to 10.0-base, and this is the diff file, please help me to review it. How to use this feature? I added several new arguments for mysqlbinlog: --flashback (-B), it will let mysqlbinlog working on FLASHBACK mode. --table (-T), it likes -d, but this argument is for table. --review, it will le mysqlbinlog print the SQL for reviewing. Reviewing feature will create a new "review" table to record the data that will be changed by FLASHBACK feature. If you aren't special the --review-dbname/tablename, "review" table will created on current DB, and the table name will be "__"+original_table_name. And for getting the original table struct, mysqlbinlog need to connect mysql, so --user/--host/--password is needed if necessary. --review-dbname, the DB that you want to store the review table. --review-tablename the TABLE that you want to store the changed data. Only if you set -T, this argument is useful. For example: I have a table "t" in database "test", CREATE TABLE `t` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `info` varchar(32) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=9043880 DEFAULT CHARSET=utf8; And I done this operation, root@localhost : (none) 14:52:50> reset master; root@localhost : (none) 14:53:02> use test root@localhost : test 14:53:08> delete from t limit 10; root@localhost : test 14:53:25> insert into t (info) select "你好"; root@localhost : test 14:54:01> update t set info ='hello你好' limit 5; We can compare the output with --flashback or not. #client/mysqlbinlog /data/mysqldata_10.0/binlog/mysql-bin.000001 -vv -d test -T t --start-datetime="2013-03-27 14:54:00" > /tmp/1.sql #client/mysqlbinlog /data/mysqldata_10.0/binlog/mysql-bin.000001 -vv -uroot -d test -T t --start-datetime="2013-03-27 14:54:00" -B --review > /tmp/2.sql How to implement Flashback: 1. mysqlbinlog tool can parse the binlog files and print them, so we can use the most of code in mysqlbinlog. 2. Changing the event type (INSERT -> DELETE, DELETE -> INSERT) 3. Exchanging the SET/WHERE part in UPDATE (UPDATE table SET x=1 WHERE y=2 -> UPDATE table SET y=2 WHERE x=1) 4. Invert the event output, from end to beginning (binlog: e1,e2,e3... -> flashback_log: en...e3,e2,e1) Then, importing the flashback output can flashback database/table to the special time. But binlog_format should be ROW. Thanks, Lixun -- Senior MySQL Developer @ Taobao.com Mobile Phone: +86 18658156856 (Hangzhou) Gtalk: penglixun(at)gmail.com Twitter: http://www.twitter.com/plinux Blog: http://www.penglixun.com

2 3

[Maria-developers] GSoC 2013 - Interested Student - Roles Project
by Vicentiu Ciorbaru 28 May '13

28 May '13

Hello, My name is Vicențiu Ciorbaru. I am 3'rd year undergraduate student at the Polytechnic University of Bucharest, Romania and I am very interested in contributing to MariaDB as part of Google Summer of Code. I have not used MariaDB previously but I have taken it for a spin just now. I have participated in GSoC once before and would like to do so again. I have contributed to other open source organisations (Mozilla, Samba, ROSEdu) and I submit patches when time permits. The project that interests me the most is MDEV-4397: Roles. I want to ask you what would be a good place to start in the codebase to help me get an idea what I need to do for this project? Looking forward to hearing from you, Vicențiu

2 7

[Maria-developers] unexpected "Duplicate entry" error
by Kazuhiko Shiozaki 21 May '13

21 May '13

Hello, We sometimes (not always, not often but sometimes) have unexpected "Duplicate entry" error on the following table and with the following query : * table CREATE TABLE `category` ( `uid` bigint(20) unsigned NOT NULL, `category_uid` bigint(20) unsigned NOT NULL DEFAULT '0', `base_category_uid` bigint(20) unsigned NOT NULL DEFAULT '0', `category_strict_membership` tinyint(1) NOT NULL DEFAULT '0', PRIMARY KEY (`uid`,`category_uid`,`base_category_uid`,`category_strict_membership`), KEY `Membership` (`category_uid`,`base_category_uid`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci * query BEGIN; (some queries for other tables) DELETE FROM category WHERE uid=14026268; INSERT INTO category VALUES (14026268, 3131502, 2074, 1),(14026268, 2992, 2992, 0),(14026268, 3133077, 191, 1),(14026268, 1212264, 2992, 1); (some queries for other tables) COMMIT; * error Duplicate entry '14026268-3131502-2074-1' for key 'PRIMARY' But this duplicate entry error should not happen because DELETE is called in the same transaction, even if similar queries are sent from another connection at the same time. We confirmed that this kind of issue happens on the following environments using MySQL_python-1.2.3 : * MariaDB 5.5.25, XtraDB * MariaDB 5.5.29, XtraDB * MariaDB 5.5.29, InnoDB plugin Since it happens SOMETIMES, I cannot reproduce this issue by small query. Do you have any idea so that we can investigate this issue further ? Thanks in advance ! -- Kazuhiko Shiozaki Nexedi: Consulting and Development of Free / Open Source Software http://www.nexedi.com/ ERP5: Full Featured High End Open Source ERP http://www.erp5.com/

4 12

[Maria-developers] MYSQL_TYPE_DECIMAL type
by Zardosht Kasheff 08 May '13

08 May '13

Hello all, Under what circumstances can a table be created with MYSQL_TYPE_DECIMAL as being one of the columns? I thought MYSQL_TYPE_DECIMAL was deprecated pre 5.1. I thought all new tables use MYSQL_TYPE_NEWDECIMAL. We have a user reporting a table he wants to alter has that type. However, when I create that table, I don't get that type. As an aside, we don't support that type, mainly because I thought it was impossible to have a table created with it (I am apparently wrong). Is there a way to ensure that tables undergoing an alter convert that column to MYSQL_TYPE_NEWDECIMAL? Thanks -Zardosht

3 3

[Maria-developers] is this a bug?
by jhx1008 08 May '13

08 May '13

hi all: I have a problem about the type cast when I have read the source of Maria & MySQL I notice that when cast from string into int, Maria & MySQL do it like the C function atoi code: for (ul= 0 ; str < end9 && (ch= (uchar) (*str - '0')) < 10; str++) { ul= ul * 10 + ch; } but cast from hex into int, the code is: code: char *end=(char*) str_value.ptr()+str_value.length(), *ptr=end-min(str_value.length(),sizeof(longlong)); ulonglong value=0; for (; ptr != end ; ptr++) value=(value << 8)+ (ulonglong) (uchar) *ptr; so I do the test blew: create table t(id int auto_increment primary key, a int)engine=innodb; insert into t(a) values('1'); insert into t(a) values(0x31); everything is ok, I get the result: mysql> select * from t; +----+------+ | id | a | +----+------+ | 1 | 1 | | 2 | 49 | +----+------+ the value 0x31 is the ascii of the string '1', but we got the different result after inserting maybe it's not a problem, but in the replication environment, it maybe cause the data of slave not the same as master In the master, when we set the connection characterset into multi-bytes characterset and this characterset may escape with backslash is dangerous (like gbk, cp932) and then we use the prepared statement to do the insert, the string value in the insert must be cast into hex before write into the binlog, so we do the insert(insert into t(a) values('1')) on the master, but in the slave it does the another(insert into t(a) values(0x31)) , the data is not the same. how to reappear: master(binlog format=statement): create table t(id int auto_increment primay key, a int) engine=innodb; set names gbk; prepare stmt from 'insert into t(a) values(?)'; set @a='1'; execute stmt using @a; the result in master is: mysql> select * from t; +----+------+ | id | a | +----+------+ | 1 | 1 | +----+------+ and result in slave is: mysql> select * from t; +----+------+ | id | a | +----+------+ | 1 | 49 | +----+------+ I test the newest release version mariadb-10.0.2 and MySQL-5.6.10, the problem is still exist, is this a bug? 2013-04-28 jhx1008 @netease

4 7

[Maria-developers] Missing locking around THD::set_db() ?
by Kristian Nielsen 07 May '13

07 May '13

Hi, I was looking at a Valgrind warning in Buildbot (appended below). What happens seems to be this: Thread 1 is running SHOW PROCESSLIST, it grabs the pointer THD::db to the current database of thread 2. Thread 2 then does THD::set_db(), freeing the old THD::db pointer and allocating a new one with the new data. Thread 1 then resumes, doing strdup() of the _old_, now invalid, THD::db pointer, which reads garbage data (or could even segfault if we get really unlucky). This seems like a genuine bug. I see absolutely no locking protecting against this race :-( Any suggestions for how to deal with this? The only locking I see in mysqld_list_processes() is holding LOCK_thread_count, but taking that for every THD::set_db() sounds quite bad for scalability (for example, set_db() is run by every query replicated by a slave thread). Should we use LOCK_thd_data also for THD::db ? Or maybe we could have a fixed buffer in THD for the db? Seems we would need 193 bytes. Then SHOW PROCESSLIST could sometimes show a random mix of old and new db name if we race, but at least we would not read invalid memory or crash due to mummap() ... Any better ideas? - Kristian. ----------------------------------------------------------------------- Original valgrind warning, for reference: rpl.rpl_stop_slave 'mix,xtradb' w5 [ fail ] Found warnings/errors in server log file! Test ended at 2013-04-22 22:05:12 line ==9897== Thread 20: ==9897== Invalid read of size 1 ==9897== at 0x4C2826B: memcpy (mc_replace_strmem.c:878) ==9897== by 0xBAF9E8: strmake_root (my_alloc.c:424) ==9897== by 0x63C74E: mysqld_list_processes(THD*, char const*, bool) (sql_class.h:771) ==9897== by 0x5C8574: mysql_execute_command(THD*) (sql_parse.cc:3149) ==9897== by 0x5CC844: mysql_parse(THD*, char*, unsigned int, Parser_state*) (sql_parse.cc:5759) ==9897== by 0x5CDDC9: dispatch_command(enum_server_command, THD*, char*, unsigned int) (sql_parse.cc:1068) ==9897== by 0x5CE4BD: do_command(THD*) (sql_parse.cc:794) ==9897== by 0x6A4E23: do_handle_one_connection(THD*) (sql_connect.cc:1266) ==9897== by 0x6A4F23: handle_one_connection (sql_connect.cc:1181) ==9897== by 0x8F0027: pfs_spawn_thread (pfs.cc:1015) ==9897== by 0x4E3506F: start_thread (in /lib64/libpthread-2.9.so) ==9897== by 0x62F713C: clone (in /lib64/libc-2.9.so) ==9897== Address 0x1a06a973 is 3 bytes inside a block of size 5 free'd ==9897== at 0x4C257D8: free (vg_replace_malloc.c:446) ==9897== by 0xBB96BB: my_free (my_malloc.c:115) ==9897== by 0x838C86: Query_log_event::do_apply_event(Relay_log_info const*, char const*, unsigned int) (sql_class.h:2846) ==9897== by 0x53904C: apply_event_and_update_pos(Log_event*, THD*, Relay_log_info*) (log_event.h:1230) ==9897== by 0x5446DD: exec_relay_log_event(THD*, Relay_log_info*) (slave.cc:2766) ==9897== by 0x545BD9: handle_slave_sql (slave.cc:3627) ==9897== by 0x8F0027: pfs_spawn_thread (pfs.cc:1015) ==9897== by 0x4E3506F: start_thread (in /lib64/libpthread-2.9.so) ==9897== by 0x62F713C: clone (in /lib64/libc-2.9.so)

4 4

Re: [Maria-developers] [Gsoc] Regex enhancements Project
by Sergei Golubchik 04 May '13

04 May '13

Hi, Sudheera! On Apr 19, Sudheera Palihakkara wrote: > Hi, > I went through other threads on this topic. In one thread you mentioned to > choose a suitable regex library. > > *( Preliminary research - only about chosing a regex library to use in > MariaDB. You should be able to explain why we should use this library and > not some other one.) > > * > What do you mean by "choosing"? don't we have to enhance the exiting regex > library? Or choose from exiting already implemented libraries which are > free to use? sorry if it's a stupid question, but I'm confused. :O Enhancing our old regex library to support all modern features and multiple charsets is complex and bug-prone work. I don't see why we should bother doing it, when there are plenty of regex libraries available. There's PHP's mb_regex, there's prce, and many others too. We'd better just pick one that works better for MariaDB, and put it instead of Henry Spencer's library. Regards, Sergei P.S. Please, don't reply to me only, use reply-to-all, so that your mails appear on the mailing list.

3 9

[Maria-developers] Fwd: MariaDB release numbering
by Jorge Manuel B. S. Vicetto 03 May '13

03 May '13

Hi. I've emailed Kristian about this subject and he suggested I sent this to your mailing list to get a more complete picture. On our work packaging MariaDB for Gentoo, me and Robin need to decide how to deal with the packaging of MySQL vs MariaDB and to that it would be really helpful to have an idea of how you plan to number your future releases. AFAIK, currently we can compare MySQL vs MariaDB as: MySQL MariaDB 5.1 5.1 / 5.2 / 5.3 5.5 5.5 (including MariaDB features from 5.2/5.3) 5.6 10.0 (based on MySQL 5.5 with backports from MySQL 5.6 and features from previous MariaDB versions) 5.7 ??? The first question for us is what version number will you use for a newer version based on MySQL-5.7. Another question is if you plan to port all your work to the 5.7 codebase or if you'll keep working on the current 10.0 codebase and do backports from 5.7 (like you did for MySQL 5.6). A second question is whether you still claim binary compatibility with MySQL[1] and how would you compare the latest versions of MariaDB and MySQL (10.0 vs 5.6, 5.7). [1] - https://kb.askmonty.org/en/mariadb-versus-mysql-compatibility/ Any info you can share on the release versioning plans would be really helpful. If this is still too early for any definitions or comments, is there any way we can follow or learn about such decisions? Thank you and regards, Jorge Manuel B. S. Vicetto Gentoo Developer

2 1

Re: [Maria-developers] Installing jemalloc on build VMs
by Kristian Nielsen 26 Apr '13

26 Apr '13

Daniel Bartholomew <dbart(a)mariadb.org> writes: > I'm getting ready to install jemalloc on the buildslaves for TODO-459 - > https://mariadb.atlassian.net/browse/TODO-459 , and wanted to know if > there was a preferred way to do it. Since we want to use a specific > version of jemalloc (3.1.0), I figured I would build from source on all > of the build machines. Has this been thought through properly with respect to packaging? My guess is it has not. For .debs, which are what I know about, it is generally expected that one can rebuild from `apt-get source mariadb-server`. This requires proper build dependencies. It doesn't work to install random stuff on random machines. It does not appear that jemalloc 3.1 is available on most .deb distros we support. Wheezy seems to have 3.0.0. It is said that tokutek "requires" jemalloc - maybe it would be better to fix tokutek to work with standard libraries? The proper way to produce .debs with custom jemalloc requires creating new custom packages for libjemalloc and including them in our repos. - Kristian.

8 11

Re: [Maria-developers] MariaDB and jemalloc
by Axel Schwenke 24 Apr '13

24 Apr '13

Hi Sanja, Oleksandr Byelkin wrote: > 24.04.2013 19:00, Axel Schwenke пишет: > [skip] >> >> OLTP ro transactions per second relative to glibc malloc >> ---------------------------------------------------------------------- >> Threads jemalloc-3.3.1 glibc malloc tcmalloc jemalloc-3.1.0 >> 4 +3.2% +0.0% +11.6% +61.9% >> 8 +0.9% +0.0% -2.3% +8.0% >> 16 +9.6% +0.0% +4.7% +12.9% >> 32 +1.8% +0.0% +0.9% +5.8% >> 64 -5.1% +0.0% +2.1% +5.4% >> 128 -4.8% +0.0% +0.2% +4.5% >> 256 -4.2% +0.0% +0.7% +5.0% >> 512 -13.3% +0.0% +1.8% +4.1% > > Strange that results jumps up and down when percona had quite smooth > one. Is it possible that computer was doing something else? or we need > more tests to take average? It's not so "jumpy" when you look at the absolute numbers. The difference to Percona is, that they tested their own server, where MariaDB tries to minimize the number of malloc() calls. I wouldn't give too much on the numbers for 4 threads, since this is the buffer pool warmup phase. Also I've run each test for only 100 seconds to get results fast. Re "jumpy" - see the attached pdf with the scatter plot. Each point represents the tps from a 5 second interval. Numbers are in fact rather smooth, except for 512 threads. The main takeaway is, that I can reproduce the regression in jemalloc 3.3. With TokuDB there is no way around jemalloc anyway. XL

2 1