gmane.comp.db.mysql.devel

Re: Patch for mysqldump

Sveta Smirnova — 2013-05-07T09:49:00

Hi Marco,

thank you for the patch!

Could you please open a feature request at bugs.mysql.com, put your 
patch there and sign Oracle Contributor Agreement (OCA) as described in 
newly created bug report in "Contributions" tab?

This will make possible for Oracle to accept this patch and include into 
future versions.

Thank you,
Sveta.

On 05/05/2013 11:06 PM, Marco Gergele wrote:

Re: Patch for mysqldump

Stewart Smith — 2013-05-07T07:45:31

The conversion is essentially free, so I wouldn't worry about the cost
of converting back and forth.

Patch for mysqldump

Marco Gergele — 2013-05-05T20:06:42

Hi,

mysqldump can sort by primary key, but only ascending.

mysqldump can not limit the number of rows.

I have added two options:

--order-by-primary-desc
                      Sorts descending each table's rows by primary key, or
                      first unique key, if such a key exists.  Useful when
                      dumping a MyISAM table to be loaded into an InnoDB
table,
                      but will make the dump itself take considerably
longer.

--limit[=name]      (numeric!) Number of rows of each table to dump, 0=all
                     rows

I do need the number for "limit" as a string, so I try to avoid converting
it into numbers and back into string.

Greets - Marco Gergele
=== modified file 'client/client_priv.h'
--- client/client_priv.h2013-02-26 05:35:17 +0000
+++ client/client_priv.h2013-05-05 17:10:48 +0000
< at >< at > -13,6 +13,9 < at >< at >
    You should have received a copy of the GNU General Public License
    along with this program; if not, write to the Free Software
    Foundation, Inc.,

outputting varchars to a file to be read by load data infile

Zardosht Kasheff — 2013-05-05T15:53:57

Hello all,

I have an interesting problem. I am in the process of trying to
manually recover data from a corrupted dictionary. The table has
DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci, and there are four
columns:
  `a` varchar(100) CHARACTER SET utf8 NOT NULL,
  `b` varchar(10) COLLATE latin1_general_ci DEFAULT NULL,
  `c` varchar(20) COLLATE latin1_general_ci DEFAULT NULL,
  `d` varchar(20) COLLATE latin1_general_ci NOT NULL DEFAULT '',

The data in the corrupted dictionary is identical to what we would
find in table->record[0] as accessed by storage engine's handler. It
contains the length of each string and the bytes.

What I want to do is make a little program that will read this data
from the corrupted dictionary, and write it to a file in such a format
such that I can use "load data infile" to interpret it correctly and
reload the data.

If I just copy the bytes directly to the file, will load data infile
be able to interpret the data? Is there anything else that needs to be
done (such as put in

Re: adding an index of my own type/code

AugustQ — 2013-04-24T16:13:21

Hi to everyone,

and thanks for all your comments.

It looks like I have 2 possibilities:
#1: I modify the source-code of MySQL. It seems like sql_yacc.cc is a
godd place because here HA_KEY_ALG_HASH and others are used.

#2: I do not modify the source-code but use a given word instead.
This means: instead of 
    type  AQTREE;
in my CREATE-stmt I use
    type HASH;
so MySQL checks against a list of given keywords, accepts this statement
and sends it to the storage-engine. Then the storage-engine has to
handle this correctly. In my case it sends it to the create()-function:
    create(const char *name, TABLE *form, HA_CREATE_INFO *info)

where I can find it in:
    form->key_info[i]->algorithm 

which contains an entry of the enum of type ha_key_alg (defined in
my_base.h). In my given example it contains HA_KEY_ALG_HASH.

OK, it is a bit dirty to use the HASH-keyword but I can live with this
approach. 

This behaviour of MySQL explains this hint from the description of the
CREATE-stmt:
engine, but there is a

Re: adding an index of my own type/code

Sergei Golubchik — 2013-04-24T14:37:29

Hi, AugustQ!

On Apr 24, AugustQ wrote:

No, it is not possible. Index types are hard-coded in the parser and
only BTREE, RTEEE, and HASH are allowed.

Regards,
Sergei

Re: adding an index of my own type/code

Thomas Jones-Low — 2013-04-24T12:48:01

I believe, and it has been a while so I can't provide exact pointers, 
that the ha_create process (which is where the CREATE INDEX gets 
processed), does ask the storage engine for the supported index types.

You should read through the processing in 
<mysql_source>/sql/handler.cc, handler.h, and table.cc

It has been my experience in writing storage engines that you don't 
need to alter the MySQL source to do some interesting things. But you do 
need to know all the ins and outs of the processing.

On 4/24/13 5:59 AM, AugustQ wrote:

Re: adding an index of my own type/code

AugustQ — 2013-04-24T09:59:09

Hi,

and thanks for your hints.

Here is what I did: I created a tiny storage engine which inherits its
functionality from MyIsam. I can load this code and so I created a table
with this type of storage engine.

Next I wanted to see what my storage engine can do if I add an index to
this table. 

If I create an index without specifying an index-type everything works.
In the ::create(()-function I see the columns that describe the index.

When I create an index using an index-engine-type by giving the keyword
engine or type or using in the CREATE-stmt, it works when I use one of
the keywords given in the description like BTREE or HASH. But when I
create an index using my own keyword then I got immediately the syntax
error.

In my example I used the word AQTREE for the description of the
index-engine. I wanted to see at the storage-engine-level, what
information was given to my code but this storage engine (my code) was
never called.

What I expected: MySQL offers the choice of adding another storage
engine wi

Re: adding an index of my own type/code

Stewart Smith — 2013-04-23T16:28:48

You really want to use something more recent. 5.5.8 is ancient.


You have to do this inside a storage engine, the MySQL server itself
doesn't deal with indexing (although the optimizer obviously has some knowledge).


Pick an engine: MyISAM, Heap, InnoDB (default) or one of the third party
ones and go for it. Getting any actual code merged into MySQL if you
don't work for Oracle is... well... problematic at best.

If you're wanting to experiment with different indexing methods, it is
possible that the PostgreSQL code is a better choice - and a higher
chance of ever having code integrated.

You can, of course, write your own storage engine with whatever indexing
methods you like (and just lie to the optimizer about it being like a
BTREE :)


No. Drizle takes away some of the server side issues, but it's still all
the responsibility of the storage engine.

adding an index of my own type/code

AugustQ — 2013-04-23T13:14:21

Hi,

I'm using the source-code of MySQL-version 5.5.8

I want to implement my own type of an index and play with it. How can I
do this?

It's easy to add a storage engine. For creating the index on a table I
use a statement like:
    create index PRIMA3 
    on ABDAOK(Id, PZN, ArtikelBez) 
    type  AQTREE;

I go a syntax-error: ERROR 1064 (42000)

What do I have to do to implement my own index-code? 
Is there a newer version that supports this better?

Thanks
AugustQ

after cross compile, mysql table hidden while operation on it still work

frederic nivor — 2013-03-10T07:15:33

Hi,
I'm trying to cross compile mysql server. Most of it work, but when I access the database via root< at >localhost account (show databases;), I've got :
Database
information_schema
Applications
Developer
Library
System
bin
cores
etc
private
sbin
usr
var

Which seems to be my / directory.
When I try :
use mysql;
of course, the mysql database is unknown for him, but when I try :
select * from mysql.user;
I've got the right result.
So I thought the root user didn't have the right privileges, but when I check :
show grants for 'root'< at >'localhost';
I've got :
GRANT ALL PRIVILEGES ON *.* TO 'root'< at >'localhost' WITH GRANT OPTION
GRANT PROXY ON ''< at >'' TO 'root'< at >'localhost' WITH GRANT OPTION
Then I thought my datadir variable was not well configured, but when I try :
show variables;
I've got :
datadir /var/mobile/mysql/Library/data/
which is the right path, where mysql and performance_schema folder databases are stored.
Any idea ?
B.R.

Re: properly detecting cases where handler can silently overwrite rows

Zardosht Kasheff — 2013-03-07T21:21:53

For this specific scenario, I believe
handler::extra(HA_WRITE_CAN_REPLACE) should NOT be called.
Unfortunately, I see that it is. So either this is a bug in the
setting of HA_WRITE_CAN_REPLACE, or the semantics that InnoDB uses are
not necessarily the only acceptable semantics.

Nevertheless, if we want to match InnoDB's behavior, we cannot rely on
HA_WRITE_CAN_REPLACE for this scenario.

Is there anyway to properly detect the cases when we can silently overwrite?

Thanks
-Zardosht

On Wed, Mar 6, 2013 at 7:36 AM, Zardosht Kasheff <zardosht< at >gmail.com> wrote:

Re: effects of load data local on RSS

Michael Widenius — 2013-03-06T21:35:11

hi!


Zardosht> Here is a stack trace where for LOAD DATA LOCAL, we get a call to
Zardosht> handler::extra with operation set to HA_EXTRA_IGNORE_DUP_KEY

<cut>

I missed the following code (was not totally obvious):

  /* We can't give an error in the middle when using LOCAL files */
  if (read_file_from_client && handle_duplicates == DUP_ERROR)
    ignore= 1;

So yes, LOAD DATA LOCAL has an implicite IGNORE.

Regards,
Monty

Re: properly detecting cases where handler can silently overwrite rows

Zardosht Kasheff — 2013-03-06T12:36:50

I have, but we decided not to use it back in 5.1 because it was
causing us problems. Perhaps that is different now. Thank you for the
feedback. I will dig into old emails and try to remember what the
issues were, and then will try it.

Thanks
-Zardosht

On Wed, Mar 6, 2013 at 1:06 AM, Dmitry Lenev <Dmitry.Lenev< at >oracle.com> wrote:

Re: effects of load data local on RSS

Davi Arnaut — 2013-03-06T07:09:17

Quoting from the manual:

"With LOCAL, the default duplicate-key handling behavior is the same
as if IGNORE is specified; this is because the server has no way to
stop transmission of the file in the middle of the operation. IGNORE
is explained further later in this section."

http://dev.mysql.com/doc/refman/5.5/en/load-data.html

On Tue, Mar 5, 2013 at 6:20 PM, Zardosht Kasheff <zardosht< at >gmail.com> wrote:

Re: properly detecting cases where handler can silently overwrite rows

Dmitry Lenev — 2013-03-06T06:06:28

Hello Zardosht!

* Zardosht Kasheff <zardosht< at >gmail.com> [13/03/06 06:27]:

Have you seen HA_EXTRA_WRITE_CAN_REPLACE ?

AFAIR it was added to implement optimization similar
to the one you describe for NDB cluster.

Best regards,
Dmitry

Re: properly detecting cases where handler can silently overwrite rows

Zardosht Kasheff — 2013-03-06T02:21:32

Hello all,

Does anyone have any thoughts on this?

Thanks
-Zardosht

On Tue, Feb 26, 2013 at 4:47 PM, Zardosht Kasheff <zardosht< at >gmail.com> wrote:

Re: effects of load data local on RSS

Zardosht Kasheff — 2013-03-06T02:20:59

Hello,

So is my conclusion accurate that as far as the handler goes, load
data local basically acts like load data ignore? If not, what are the
differences?

Thanks
-Zardosht

On Fri, Mar 1, 2013 at 12:08 PM, Zardosht Kasheff <zardosht< at >gmail.com> wrote:

re: inconsistency in source-code/comments in mf_keycache

Michael Widenius — 2013-03-05T23:36:44

Hi!


Kuts> Hi,
Kuts> I am just learning the inner working of myisam key cache
Kuts> and found some inconsistency in mf_keycache.c

Kuts> here is this place and my suggested patch:
Kuts> --- mysys/mf_keycache.c2011-09-07 10:08:09 +0000
Kuts> +++ mysys/mf_keycache.c2013-03-06 07:53:02 +0000
Kuts> < at >< at > -3404,7 +3404,7 < at >< at >
Kuts>      return;
 
Kuts>    /* Error blocks are not put into the LRU ring. */
Kuts> -  if (!(block->status & BLOCK_ERROR))
Kuts> +  if (block->status & BLOCK_ERROR)
Kuts>    {
Kuts>      /* Here the block must be in the LRU ring. Unlink it again. */
Kuts>      DBUG_ASSERT(block->next_used && block->prev_used &&

Kuts> I don't know, is there really a bug, that erroneous blocks are remained in LRU-ring.

I was looking at the code and the original code kind of makes sence.

According to the comment, error blocks are not put the LRU ring,
so they should not be unlinked (as it's done inside the if).

Are you sure that error blocks are in the LRU ring?

How did you conclude that?
I looked at the

inconsistency in source-code/comments in mf_keycache

Kuts Alexey — 2013-03-04T21:59:41

Hi,

I am just learning the inner working of myisam key cache
and found some inconsistency in mf_keycache.c

here is this place and my suggested patch:
--- mysys/mf_keycache.c2011-09-07 10:08:09 +0000
+++ mysys/mf_keycache.c2013-03-06 07:53:02 +0000
< at >< at > -3404,7 +3404,7 < at >< at >
     return;
 
   /* Error blocks are not put into the LRU ring. */
-  if (!(block->status & BLOCK_ERROR))
+  if (block->status & BLOCK_ERROR)
   {
     /* Here the block must be in the LRU ring. Unlink it again. */
     DBUG_ASSERT(block->next_used && block->prev_used &&

I don't know, is there really a bug, that erroneous blocks are remained in LRU-ring.

Regards,
Alexey

Re: effects of load data local on RSS

Zardosht Kasheff — 2013-03-01T17:08:11

Here is a stack trace where for LOAD DATA LOCAL, we get a call to
handler::extra with operation set to HA_EXTRA_IGNORE_DUP_KEY

#0  ha_tokudb::extra (this=0x1aaf240,
operation=HA_EXTRA_IGNORE_DUP_KEY) at
/home/zardosht/dev/mysql-build/mysql-5.5.28/storage/tokudb/ha_tokudb.cc:5949
#1  0x000000000084be8a in mysql_load (thd=0x1a14d20, ex=0x1acc190,
table_list=0x1acc218, fields_vars=..., set_fields=..., set_values=...,
handle_duplicates=DUP_ERROR, ignore=true,
    read_file_from_client=true) at
/home/zardosht/dev/mysql-build/mysql-5.5.28/sql/sql_load.cc:474
#2  0x00000000005dcfb5 in mysql_execute_command (thd=0x1a14d20) at
/home/zardosht/dev/mysql-build/mysql-5.5.28/sql/sql_parse.cc:3173
#3  0x00000000005e32a6 in mysql_parse (thd=0x1a14d20,
    rawbuf=0x1acbfd0 "load data local infile
'/nfs/tmcsrv/sysbench-mysqldump-50000000/sbtest.txt' into table
sbtest1 fields terminated by ',' enclosed by '\"'", length=135,
parser_state=0x536e0910)
    at /home/zardosht/dev/mysql-build/mysql-5.5.28/sql/sql_parse.cc:5627
#4  0