July 2013 - developers - lists.mariadb.org

Re: [Maria-developers] Implementing implicit primary key in mysql server
by Lixun Peng 30 Jul '13

30 Jul '13

Hi Sergei, You are right, let users add primary key is best. But I can't let users who don't want to create primary key can't use our MySQL service. Amazon RDS also allow users to create the tables without primary key, just change binlog_format to MIXED to solve replication problem. I think this feature is very useful for any MySQL cloud service providers, all of them will face this problem in one day. I will try to do some analysis/research in this feature implement first, I will need your help :-) I will notice any new updates in this email. Thanks, Lixun On Wed, Jun 19, 2013 at 5:14 PM, Sergei Golubchik <serg(a)mariadb.org> wrote: > Hi, Lixun! > > On Jun 18, Lixun Peng wrote: > > Hi, > > > > As we know, InnoDB has implicit primary key if a table hasn't defined > > a primary key. However MySQL server doesn't know this primary key, so > > this primary key will not apear in binlog. > > > > When we are using ROW format binlog for replication, if a table has no > any > > indexes, that's a disaster. If a table without indexes do a DML > > (UPDATE/DELETE), of course it will run a long time in master, but in > slave, > > it still need a long time. It will cause serious slave replication delay. > ... > > I think mysql server can do the same thing as InnoDB do, if user doesn't > > define the primary key, mysql can add the primary key automatically. > > > > How do you think? > > Well, that's doable. A much easier solution would be to require a user > to create a primary key. It's a one-line change: > > - Table_flags ha_table_flags() const { return cached_table_flags; } > + Table_flags ha_table_flags() const { return cached_table_flags | > HA_REQUIRE_PRIMARY_KEY; } > > But what you suggest is possible too, I believe. > > Regards, > Sergei > -- Senior Database Engineer @ Taobao.com Mobile Phone: +86 18658156856 (Hangzhou) Gtalk: penglixun(at)gmail.com Twitter: http://www.twitter.com/plinux Blog: http://www.penglixun.com

4 8

Re: [Maria-developers] [Commits] Rev 3792: main.gis test fixed. in file:///home/hf/wmar/10.0-serg/
by Sergey Vojtovich 27 Jul '13

27 Jul '13

Hi Alexey, I just checked 5.6 and 10.0-base, this test is passing as is. I'm a bit worried that something functional was broken during the merge. Regards, Sergey 26.07.2013, в 16:13, <holyfoot(a)askmonty.org> написал(а): > At file:///home/hf/wmar/10.0-serg/ > > ------------------------------------------------------------ > revno: 3792 > revision-id: holyfoot(a)askmonty.org-20130726110356-9s17c1kw558ls14a > parent: svoj(a)mariadb.org-20130726091143-o2v0tt08n81n923m > committer: Alexey Botchkov <holyfoot(a)askmonty.org> > branch nick: 10.0-serg > timestamp: Fri 2013-07-26 16:03:56 +0500 > message: > main.gis test fixed. > === modified file 'mysql-test/r/gis.result' > --- a/mysql-test/r/gis.result 2013-03-27 09:03:28 +0000 > +++ b/mysql-test/r/gis.result 2013-07-26 11:03:56 +0000 > @@ -918,25 +918,25 @@ SELECT Overlaps(@horiz1, @point2) FROM D > Overlaps(@horiz1, @point2) > 0 > DROP TABLE t1; > -create table t1(f1 geometry, f2 point, f3 linestring); > +create table t1(f1 geometry, f2 linestring, f3 linestring); > select f1 from t1 union select f1 from t1; > f1 > -insert into t1 (f2,f3) values (GeomFromText('POINT(1 1)'), > +insert into t1 (f2,f3) values (GeomFromText('LINESTRING(1 1, 2 2)'), > GeomFromText('LINESTRING(0 0,1 1,2 2)')); > select AsText(f2),AsText(f3) from t1; > AsText(f2) AsText(f3) > -POINT(1 1) LINESTRING(0 0,1 1,2 2) > +LINESTRING(1 1,2 2) LINESTRING(0 0,1 1,2 2) > select AsText(a) from (select f2 as a from t1 union select f3 from t1) t; > AsText(a) > -POINT(1 1) > +LINESTRING(1 1,2 2) > LINESTRING(0 0,1 1,2 2) > create table t2 as select f2 as a from t1 union select f3 from t1; > desc t2; > Field Type Null Key Default Extra > -a point YES NULL > +a linestring YES NULL > select AsText(a) from t2; > AsText(a) > -POINT(1 1) > +LINESTRING(1 1,2 2) > LINESTRING(0 0,1 1,2 2) > drop table t1, t2; > SELECT 1; > > === modified file 'mysql-test/t/gis.test' > --- a/mysql-test/t/gis.test 2013-03-27 09:03:28 +0000 > +++ b/mysql-test/t/gis.test 2013-07-26 11:03:56 +0000 > @@ -609,9 +609,9 @@ DROP TABLE t1; > # > # Bug#28763: Selecting geometry fields in UNION caused server crash. > # > -create table t1(f1 geometry, f2 point, f3 linestring); > +create table t1(f1 geometry, f2 linestring, f3 linestring); > select f1 from t1 union select f1 from t1; > -insert into t1 (f2,f3) values (GeomFromText('POINT(1 1)'), > +insert into t1 (f2,f3) values (GeomFromText('LINESTRING(1 1, 2 2)'), > GeomFromText('LINESTRING(0 0,1 1,2 2)')); > select AsText(f2),AsText(f3) from t1; > select AsText(a) from (select f2 as a from t1 union select f3 from t1) t; > > _______________________________________________ > commits mailing list > commits(a)mariadb.org > https://lists.askmonty.org/cgi-bin/mailman/listinfo/commits

1 0

[Maria-developers] we have seen sporadic crashes in the Protocol::net_store_data function
by Rich Prohaska 26 Jul '13

26 Jul '13

Crash occurs when using the TokuDB storage engine in MySQL 5.5.30. Any insight into what may be causing this problem would be really nice. Sorry, but this is all the info that i have. Here is a stack trace from the mysql error log: #1 0x00000000006b760f in handle_fatal_signal (sig=11) at mysql-5.5.30-tokudb-7.0.1-linux-x86_64-build/mysql-5.5.30-tokudb-7.0.1/sql/signal_handler.cc:247 #2 <signal handler called> #3 0x00007ff213a3b447 in __strncasecmp_l_sse2 () from /lib64/libc.so.6 #4 0x00000000005651a7 in Protocol::net_store_data (this=0x0, this@entry=0x7ff18f01e4b8, from=0x0, length=1936615494) at mysql-5.5.30-tokudb-7.0.1-linux-x86_64-build/mysql-5.5.30-tokudb-7.0.1/sql/protocol.cc:59 #5 0x00000000005662e4 in Protocol::store_string_aux (this=this@entry=0x7ff18f01e4b8, from=<optimized out>, length=<optimized out>, fromcs=<optimized out>, tocs=tocs@entry=0x1022920 <my_charset_latin1>) at mysql-5.5.30-tokudb-7.0.1-linux-x86_64-build/mysql-5.5.30-tokudb-7.0.1/sql/protocol.cc:960 #6 0x00000000005663a5 in Protocol_text::store (this=0x7ff18f01e4b8, field=<optimized out>) at mysql-5.5.30-tokudb-7.0.1-linux-x86_64-build/mysql-5.5.30-tokudb-7.0.1/sql/protocol.cc:1119 #7 0x0000000000565ea0 in Protocol::send_result_set_row (this=0x7ff18f01e4b8, row_items=<optimized out>) at mysql-5.5.30-tokudb-7.0.1-linux-x86_64-build/mysql-5.5.30-tokudb-7.0.1/sql/protocol.cc:847 #8 0x000000000059fc37 in select_send::send_data (this=0x7fe338243b60, items=...) at mysql-5.5.30-tokudb-7.0.1-linux-x86_64-build/mysql-5.5.30-tokudb-7.0.1/sql/sql_class.cc:2184 #9 0x00000000005f920c in end_send (join=0x7fe77f4a4010, join_tab=<optimized out>, end_of_records=<optimized out>) at mysql-5.5.30-tokudb-7.0.1-linux-x86_64-build/mysql-5.5.30-tokudb-7.0.1/sql/sql_select.cc:12762 #10 end_send (join=0x7fe77f4a4010, join_tab=<optimized out>, end_of_records=<optimized out>) at mysql-5.5.30-tokudb-7.0.1-linux-x86_64-build/mysql-5.5.30-tokudb-7.0.1/sql/sql_select.cc:12739 #11 0x00000000005e8a32 in evaluate_join_record (join=join@entry=0x7fe77f4a4010, join_tab=join_tab@entry=0x7fe77f470a60, error=<optimized out>) at mysql-5.5.30-tokudb-7.0.1-linux-x86_64-build/mysql-5.5.30-tokudb-7.0.1/sql/sql_select.cc:11925 #12 0x00000000005e8c36 in sub_select (join=0x7fe77f4a4010, join_tab=0x7fe77f470a60, end_of_records=<optimized out>) at mysql-5.5.30-tokudb-7.0.1-linux-x86_64-build/mysql-5.5.30-tokudb-7.0.1/sql/sql_select.cc:11778 #13 0x00000000005f681d in do_select (join=join@entry=0x7fe77f4a4010, fields=fields@entry=0x7ff18f020130, table=table@entry=0x0, procedure=0x0) at mysql-5.5.30-tokudb-7.0.1-linux-x86_64-build/mysql-5.5.30-tokudb-7.0.1/sql/sql_select.cc:11543 #14 0x000000000060655e in JOIN::exec (this=0x7fe77f4a4010) at mysql-5.5.30-tokudb-7.0.1-linux-x86_64-build/mysql-5.5.30-tokudb-7.0.1/sql/sql_select.cc:2385 #15 0x0000000000602095 in mysql_select (thd=thd@entry=0x7ff18f01e000, rref_pointer_array=rref_pointer_array@entry=0x7ff18f0201f8, tables=<optimized out>, wild_num=<optimized out>, fields=..., conds=<optimized out>, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2147748608, result=result@entry=0x7fe338243b60, unit=0x7ff18f01fa00, select_lex=select_lex@entry=0x7ff18f020020) at mysql-5.5.30-tokudb-7.0.1-linux-x86_64-build/mysql-5.5.30-tokudb-7.0.1/sql/sql_select.cc:2604 #16 0x0000000000608235 in handle_select (thd=0x7ff18f01e000, lex=0x7ff18f01f950, result=0x7fe338243b60, setup_tables_done_option=0) at mysql-5.5.30-tokudb-7.0.1-linux-x86_64-build/mysql-5.5.30-tokudb-7.0.1/sql/sql_select.cc:297 #17 0x00000000005c23d1 in execute_sqlcom_select (thd=thd@entry=0x7ff18f01e000, all_tables=0x7fe338243270) at mysql-5.5.30-tokudb-7.0.1-linux-x86_64-build/mysql-5.5.30-tokudb-7.0.1/sql/sql_parse.cc:4604 #18 0x00000000005ca700 in mysql_execute_command (thd=thd@entry =0x7ff18f01e000) at mysql-5.5.30-tokudb-7.0.1-linux-x86_64-build/mysql-5.5.30-tokudb-7.0.1/sql/sql_parse.cc:2154 #19 0x00000000005cd7c2 in mysql_parse (parser_state=0x7ff21213a7e0, thd=0x7ff18f01e000, rawbuf=<optimized out>, length=<optimized out>) at mysql-5.5.30-tokudb-7.0.1-linux-x86_64-build/mysql-5.5.30-tokudb-7.0.1/sql/sql_parse.cc:5641 #20 mysql_parse (thd=0x7ff18f01e000, rawbuf=<optimized out>, length=65, parser_state=0x7ff21213a7e0) at mysql-5.5.30-tokudb-7.0.1-linux-x86_64-build/mysql-5.5.30-tokudb-7.0.1/sql/sql_parse.cc:5565 #21 0x00000000005cebcc in dispatch_command (command=COM_QUERY, thd=0x7ff18f01e000, packet=<optimized out>, packet_length=65) at mysql-5.5.30-tokudb-7.0.1-linux-x86_64-build/mysql-5.5.30-tokudb-7.0.1/sql/sql_parse.cc:1041 #22 0x000000000066495f in do_handle_one_connection (thd_arg=thd_arg@entry =0x7ff18f01e000) at mysql-5.5.30-tokudb-7.0.1-linux-x86_64-build/mysql-5.5.30-tokudb-7.0.1/sql/sql_connect.cc:853 #23 0x0000000000664a11 in handle_one_connection (arg=0x7ff18f01e000) at mysql-5.5.30-tokudb-7.0.1-linux-x86_64-build/mysql-5.5.30-tokudb-7.0.1/sql/sql_connect.cc:772 #24 0x00007ff214711851 in start_thread () from /lib64/libpthread.so.0

1 0

[Maria-developers] help with mdev-4687 - aria engina problem with <= operator
by Roberto Spadim 26 Jul '13

26 Jul '13

hi guys i have a problem with aria https://mariadb.atlassian.net/browse/MDEV-4687 some queries that depend on <= operator don't return right rows (return different from myisam or innodb) i'm getting back to myisam, or innodb these are read intensive tables, and innodb runs slower, and myisam have a crash safe problem (but runs...) please any help is very wellcome thanks guys -- Roberto Spadim

2 4

[Maria-developers] innodb key comparisons
by Rich Prohaska 25 Jul '13

25 Jul '13

i am interested in the performance of in memory join queries on tokudb and innodb and, for innodb, i would like to know how many key comparisons were needed when running the query. i counted the number of calls to a function called "cmp_dtuple_rec_with_match". does this include ALL of the key comparisons? thanks rich prohaska

1 0

[Maria-developers] Documentation for old regex library calls
by Sudheera Palihakkara 25 Jul '13

25 Jul '13

Hi all, I'm replacing the current regular expression library with modern library(pcre) for mariadb. I need some information about the ways that code uses the old regex library. The relevant codes are in sql/item_cmpfunc.cc directory. For exapmle in the function : int Item_func_regex::regcomp(bool send_error) I can't understand most of the lines, and the code is not well-commented. In the new library(pcre) uses different way to compile a regEx so I have to understand this and code for pcre preserving the functionalists of the class. Is there any documentation for this file or the way they used to work with old regex library ? thanks. -- *Sudheera Palihakkara.* Undergraduate Department of *Computer Science and Engineering, *Faculty of Engineering, *University of Moratuwa*, Sri Lanka.

2 2

[Maria-developers] some bugs in dingqing parallel replication
by nanyi607rao 22 Jul '13

22 Jul '13

Hi,guys I have worked on this branch https://code.launchpad.net/~knielsen/maria/dingqi-parallel-replication for some days, and found bugs listed below.May this would be helpful to you. 1, when slave switch on table filter,this bug could lead server crash. how to reappear: on slave set replicate-wild-ignore-table = test.t5 in config file on master do these operations CREATE TABLE test.t3 (a INT AUTO_INCREMENT PRIMARY KEY, b DECIMAL(20,20), c INT); SET INSERT_ID=1; SET @c=2; SET @@rand_seed1=10000000, @@rand_seed2=1000000; INSERT INTO t3 VALUES (NULL, RAND(), @c); codes lead this bug: In execute_single_transaction() case RAND_EVENT: need_remove_from_trans= true; if(!rli->is_deferred_event(ev)) delete ev; break; reason: Rand Event object is deleted in execute_single_transaction(), but it's pointer would be used is slave_execute_deferred_events() later. 2, SQL thread could read and apply some log events repeated. how to reappear: it's a little hard to reappear. if you set max_relay_log_size=100M and keep SQL thread closed to IO thread, this bug may reappear. codes lead this bug: In reopen_relay_log() rli->event_relay_log_pos= max(rli->event_relay_log_pos, BIN_LOG_HEADER_SIZE); my_b_seek(cur_log,rli->event_relay_log_pos); reason: when SQL thread use a hot log,but the hot log was closed by IO thread just recently, SQL thread need to reopen this log and set read offset to rli->event_relay_log_pos, while rli->event_relay_log_pos could be set new value in other thread for there are many threads apply log events.so rli->event_relay_log_pos could be less then rli->future_event_relay_log_pos. 3, SQL thread do not report error information in result of "show slave status"and replication do not stop, when the slave insert duplicate record into a table with primary key. how to reappear: Just need to change master_log_pos to read duplicate records from master. codes lead this bug: In execute_single_transaction() retry_transaction: ev= trans->event_list_head; ... ... if (ret && rli->trans_retries < slave_trans_retries) { ... goto retry_transaction; } reason: as I have sayed in other email: Rows_log_event::do_apply_event() do twice but return different results for m_curr_row==m_rows_end in the second time. 4, when do oparetions such as "show slave status" and "stop slave", it could be blocked for a long time. how to reappear: just do "show slave status" again and again. codes lead this bug: In the queue_event() case FORMAT_DESCRIPTION_EVENT: ... wait_for_all_dml_done(&mi->rli, true); and in process_io_rotate() wait_for_all_dml_done(&mi->rli, true); reason: IO thread could wait in wait_for_all_dml_done() while holding the rpl_mi->data_lock, so operations like "show slave status" could be blocked for waiting rpl_mi->data_lock. 5, "START SLAVE UNTIL" make replication stop in different place. how to reappear: suppose log events in relay log like: BEGIN; ------->pos1 LOG_EVENT1; LOG_EVENT2; COMMIT; ------>pos2 BEGIN; ------>pos3 LOG_EVENT3; --->stop_pos LOG_EVENT4; COMMIT; ------>pos4 If we do START SLAVE UNTIL relay_log_pos=stop_pos; The replication should stop at pos4 but it stop pos2. 6, log_event->thd is wrong. suppose log_event was read is thread_1 so the log_event->thd==thread_1, but this log_event may be dispatch to other thread (suppose thread_2).the log_event is applyed in thread_2 but the log_event->thd==thread_1.this problem can make log event apply failed in MySQL, but in mariaDB it seems ok. 2013-07-19 nanyi607rao

2 1

[Maria-developers] RFC getting information from image (blob)
by Roberto Spadim 21 Jul '13

21 Jul '13

Hi guys, i will implement a gis system, but my problem isn't gis, my problem is raster (bitmap) images I will save images at mariadb using longblobs And i will create a table with, datetime and image I will need read each r,g,b value from each pixel, example.. Select rvalue(1,2,loadimage(blobfield)) >From table Order by datetime Rvalue= red value of x1 and y2 What's the faster or at least, how to use less cpu and memory I didn't search about images udf functions for mysql Any help is wellcome and ideas too :) Well i will use others sum, max, min functions too I don't want to read all images at client side, i want that server do this job I don't know the image size (it can be any pratical size, max near to 16mega pixels), image format could be png, jpg, or another one but i can use client side to rewrite image file to right format I'm considering implement some imagemagick functions inside mariadb, what you think? Thanks guys

2 2

[Maria-developers] Make error - cannot find -lregex
by Sudheera Palihakkara 21 Jul '13

21 Jul '13

Hi all, I have done some editing to sql/item_cmpfunc.cc file and CMakeList filesand then when I run the make command( after running cmake) it halts with this error in the middle. ([68%]) Linking CXX executable explain_filename-t /usr/bin/ld.bfd.real: cannot find -lregex collect2: error: ld returned 1 exit status make[2]: *** [unittest/mysys/explain_filename-t] Error 1 make[1]: *** [unittest/mysys/CMakeFiles/explain_filename-t.dir/all] Error 2 make: *** [all] Error 2 what might have gone wrong. :( -- *Sudheera Palihakkara.* Undergraduate Department of *Computer Science and Engineering, *Faculty of Engineering, *University of Moratuwa*, Sri Lanka.

2 3

[Maria-developers] Structure of MariaDB 10.0 binary logs
by Giuseppe Maxia 20 Jul '13

20 Jul '13

Hi, Where can I find a detailed structure of the binary logs supported by MariaDB 10.0.x? Specifically, I would like to know * what are the differences between binary logs in MariaDB 5.5 and 10.0 * what are the differences between binary logs in MariaDB 10.0 and MySQL 5.6. The purpose of this request id to parse the binary logs for usage in external applications. Thanks Giuseppe

2 2