March 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] 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] read_buffer_size query
by Ian Gilfillan 02 Apr '13

02 Apr '13

Hi I'm working on adding complete descriptions to the knowledgebase for all the server variables right now, and have a query on the read_buffer_size variable. The default in my MariaDB 10.0.1 seems to be 2097152, but it's listed on the MySQL docs with a default of 131072. I can't find a mention in the changelogs of this having changed - can anyone tell me in which version the change in default happened? Also, in the MySQL documentation, this variable is described as applying to MyISAM tables, while in the only short description on the Knowledgebase I can find, at https://kb.askmonty.org/en/mysqld-options-full-list/, it's described more generally, not specifically mentioning MyISAM. Does this variable apply to other storage engines as well now (Aria?), or still only MyISAM? thanks, ian gilfillan

3 4

[Maria-developers] I have a friend. Her name is Maria
by Кукушкин Александр 01 Apr '13

01 Apr '13

Hello! excuse me for off topic. I have a friend, her name is Maria and she has a birthday today. I thought maybe someone of you could write a commit message mentioning her. Wish her good performance and persistence or something like that. Then I could just show her a page like that one http://bazaar.launchpad.net/~maria-captains/maria/10.0/revision/3505 What do you think about that? is it possible? (sorry if this message is buplicate, I tried to post it yesterday to your mailing list but it seems like I failed)

1 1

Re: [Maria-developers] Rev 3497: MDEV-318 IF (NOT) EXIST clauses for ALTER TABLE (MWL #252)
by Sergei Golubchik 29 Mar '13

29 Mar '13

Hi, Holyfoot! Please push, with one little change: On Feb 19, holyfoot(a)askmonty.org wrote: > > message: > MDEV-318 IF (NOT) EXIST clauses for ALTER TABLE (MWL #252). > Syntax modified to allow statements: > ALTER TABLE ADD/DROP COLUMN > ALTER TABLE ADD/DROP INDEX > ALTER TABLE ADD/DROP FOREIGN KEY > ALTER TABLE ADD/DROP PARTITION > ALTER TABLE CHANGE COLUMN > ALTER TABLE MODIFY COLUMN > DROP INDEX > to have IF (NOT) EXISTS options. > Appropriate implementations added to mysql_alter_table(). ... > key_def: > - normal_key_type opt_ident key_alg '(' key_list ')' > + normal_key_type opt_if_not_exists_ident key_alg '(' key_list ')' > { Lex->option_list= NULL; } remove your new opt_if_not_exists_ident rule, write it like - normal_key_type opt_ident key_alg '(' key_list ')' + normal_key_type opt_if_not_exists opt_ident key_alg '(' key_list ')' Regards, Sergei

2 2

[Maria-developers] New problem with GTID patch
by Pavel Ivanov 28 Mar '13

28 Mar '13

Kristian, Unfortunately with your recent commit (revision 3546) test rpl.rpl_gtid_startpos doesn't work when GTID patch is applied on top of 10.0.1 tarball. It works okay in 10.0-mdev26 though, so I'm not sure if you'll want to look at the problem now. In particular the problematic addition is if (!is_relay_log && read_state_from_file()) DBUG_RETURN(1); to function MYSQL_BIN_LOG::open(). Without it test works. With it test breaks in the section "Test that master gives error when slave asks for empty gtid pos and binlog files have been purged". The problem is when get_gtid_list_event() is called from gtid_find_binlog_file() it returns event with count == 0. It causes contains_all_slave_gtid() to return true and master doesn't send error to slave as expected... I didn't look in any further details because I'm not familiar with everything that should happen here that much. If you want me to look at something else to better understand the problem let me know. Pavel

2 3

[Maria-developers] (no subject)
by Sergei Petrunia 28 Mar '13

28 Mar '13

Hello Igor, I was reviewing histogram code. Not much feedback so far, but I thought I would let you know: == Code questions == Is middle_point_pos() a good functio nname? It was not apparent to me what it meant. For me, "middle point" is a point in the middle of a range, that is, a point equidistant from both ends. Maybe, "position_in_range" will be better? == Histogram storage questions == * What does hist_size column mean? I suppose, it is number of columns in the histogram? * Do we want to support only one kind of histogram? If not, maybe we should provision space for histogram type? * The histogram column is VARBINARY(255). Why set a hard limit of 255 bytes? Maybe, use a BLOB from the start? == A possible bug == I have a column with lots of NULLs: MariaDB [test]> select nulls_ratio from mysql.column_stats where table_name='one_k' and column_name='tnulls'; +-------------+ | nulls_ratio | +-------------+ | 0.9000 | +-------------+ 1 row in set (0.00 sec) The following query crashes in maria-10.0-mwl253 tree. AFAIU, Timour has fixed the crash in 10.0-mdev83 tree. With that tree, I get: MariaDB [test]> explain extended select * from one_k where tnulls is not null; +------+-------------+-------+------+---------------+------+---------+------+------+----------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +------+-------------+-------+------+---------------+------+---------+------+------+----------+-------------+ | 1 | SIMPLE | one_k | ALL | NULL | NULL | NULL | NULL | 969 | 100.00 | Using where | +------+-------------+-------+------+---------------+------+---------+------+------+----------+-------------+ 1 row in set, 1 warning (0.00 sec) Note that filtered=100%. It should be 10%. On Mon, Mar 25, 2013 at 11:48:30PM -0700, Igor Babaev wrote: > At file:///home/igor/maria/maria-10.0-mwl253/ > > ------------------------------------------------------------ > revno: 3502 > revision-id: igor(a)askmonty.org-20130326064829-3x0g8p1g7nbptusd > parent: igor(a)askmonty.org-20130311144424-cwqe1m70in3bora0 > committer: Igor Babaev <igor(a)askmonty.org> > branch nick: maria-10.0-mwl253 > timestamp: Mon 2013-03-25 23:48:29 -0700 > message: > Added histogams for table columns. BR Sergei -- Sergei Petrunia, Software Developer Monty Program AB, http://askmonty.org Blog: http://s.petrunia.net/blog

1 0

Re: [Maria-developers] Bugs in GTID implementation
by Kristian Nielsen 27 Mar '13

27 Mar '13

Pavel Ivanov <pivanof(a)google.com> writes: > way to fix them. Also if you can please look at the attached patch > containing fixes to all tests after GTID addition. There are some > questionable changes in there, so I would appreciate if you could > evaluate its correctness and consider including it into MariaDB tree. Right, so now I finished going through the whole testsuite, making all necessary adjustments to .result files (and a bit to the test cases). This is a _huge_ patch, 70k lines :-( This is due to the new GTID events, every SHOW BINLOG EVENTS and mysqldump output needs updating. I did briefly consider instead trying to filter the output to make it identical to the old one, to avoid the huge .result file changes, but I'm not really sure that is any better in the long run... So, I did this in my 10.0-mdev26 tree, which is based on 10.0-base. I noticed at least two things from your patch that are not part of this: - The --let $keep_gtid_events= 1 statements. I am not sure how this $keep_gtid_events came into the 10.0 tree. It is part of the MySQL 5.6 GTID implementation, which we do not want to merge. I would suggest you instead just remove it from show_events.inc. - There were some new testcases in mysql-test/suite/perfschema in your patch that are not in my tree. They update some digests, I did not investigate what those are about, but I will handle when I merge to 10.0 of course. Thanks for your efforts on preparing the initial patch. It was quite a nice help as a starting point, though I did do some parts of it different due to style or correctness issues. I'm likely to have missed a couple cases of course, but at least this should be a starting point, just let me know if you experience more failures. I do see a non-repatable failure in main.plugin and a DBUG crash which is also in other maria trees, I'll look at those next. The fix is pushed to lp:~maria-captains/maria/10.0-mdev26 as usual. - Kristian.

2 3

[Maria-developers] MariaDB 10 galera?
by Jan Kirchhoff 26 Mar '13

26 Mar '13

Hi, we have been playing around with 5.5.28-galera and went into more serious testing with 5.5.29-galera. We are evaluating to move from a traditional mariadb 5.2 master-slave setup to a 5 or 7 node galera cluster for our production database. I do have a good feeling so far and good experiences in our tests, but as I won't be making any major upgrades any more once we really started testing all of our processes on the galera cluster, I'd like to ask if there is any timeline for a mariadb-10.0-galera release? I couldn't find any information on the mariadb-website regarding further plans for mariadb-galera. Is there any technical reason that no binaries of mariadb-10-galera are available? Thanks Jan

2 1

Re: [Maria-developers] Bugs in GTID implementation
by Kristian Nielsen 26 Mar '13

26 Mar '13

Pavel Ivanov <pivanof(a)google.com> writes: > fixes. Meanwhile I've found one more bug in your code. In > Gtid_log_event::do_apply_event() you assume that mysql_parse() always > succeeds, you don't check thd->is_error() and always return 0. I don't Ah, right, I see. Can you test the appended patch if it fixes the problem? I tried to copy the error handling to be identical to what Query_log_event::do_apply_event("BEGIN") does. - Kristian.

2 3