Reading data directly from MYISAM tables

MySQL & MariaDB Other MySQL® Questions
1

Hello.

I found solution for reading data directly from MYISAM tables. Now I know how to read int/float and char fields, but I don’t have any idea how to read BLOBs. I see that mi_scan returns record without BLOB part. MYISAM tests doing something incredible, it creates a record and puts it to the end of the record. May be someone has example or can tell me where I can find documentation about reading BLOBs? Or where I should set breakpoint to see how Percona Server read BLOB.

My source code:

#include "myisam.h"
#include "fulltext.h"

#include <time.h>

/*

`FIELD1` bigint(20) unsigned NOT NULL,
`FIELD2` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
`FIELD3` int(11) NOT NULL DEFAULT '0',
`FIELD4` int(10) unsigned NOT NULL DEFAULT '0',
`FIELD5` blob NOT NULL,
PRIMARY KEY (`FIELD1`)

SELECT 0, FIELD1, FIELD2, FIELD3, FIELD4, FIELD5 INTO OUTFILE '{log_file}' FIELDS TERMINATED BY '\\t' LINES
TERMINATED BY '\\n' FROM {table_name} ;
*/

//offset in info->s->rec[X].offset

#define FIELD1_OFFSET 0
#define FIELD2_OFFSET 1
#define FIELD3_OFFSET 2
#define FIELD4_OFFSET 3
#define FIELD5_OFFSET 15 // len 65536 bytes


void read_blob(uchar** var, uchar* buf, int pos, int len)
{
ulong blob_len = _mi_calc_blob_length(len, buf+pos);

*var = (uchar *) my_malloc(sizeof(char) * blob_len,MYF(MY_WME));
memcpy(*var, buf+pos, sizeof(char) * blob_len);
printf("%s\n", *var);
}

void print_record(MI_INFO *info, uchar* buf)
{
uint64 FIELD1;
time_t _FIELD2;
int32 FIELD3;
uint FIELD4;
uchar* FIELD5;

struct tm *dt;

char FIELD2[20];

printf("%u: %u\n", info->s->rec[0].offset, info->s->rec[0].length);

FIELD1 = uint8korr(buf + info->s->rec[FIELD1_OFFSET].offset);
_FIELD2 = sint4korr(buf + info->s->rec[FIELD2_OFFSET].offset);
dt = localtime(&_FIELD2);
strftime(FIELD2, sizeof(FIELD2), "%Y-%m-%d %H:%M:%S", dt);

FIELD3 = sint4korr(buf + info->s->rec[FIELD3_OFFSET].offset);
FIELD4 = uint4korr(buf + info->s->rec[FIELD4_OFFSET].offset);

if((enum en_fieldtype) info->s->rec[FIELD5_OFFSET].type == FIELD_BLOB)
{
read_blob(&FIELD5, buf, info->s->rec[FIELD5_OFFSET].offset, info->s->rec[FIELD5_OFFSET].length - portable_sizeof_char_ptr);
}

printf("0\t%llu\t%s\t%d\t%u\n",
FIELD1, FIELD2, FIELD3, FIELD4);

}

int main(int argc,char *argv[])
{
MI_INFO *info;
uchar *record;
int error, counter;

MY_INIT(argv[0]);
my_init();

if(argc != 3) {
printf("Usage: %s <input MyISAM table> <output file for append>\n", argv[0]);
return 1;
}

if (!(info=mi_open(argv[1], O_RDONLY, HA_OPEN_ABORT_IF_LOCKED))) {
printf("Error %3d. Can not read MyISAM file.", my_errno);
return 1;
}

record = (uchar*) my_malloc(info->s->base.reclength,MYF(MY_WME));

mi_scan_init(info);

counter = 0;
while(error=mi_scan(info,record) != HA_ERR_END_OF_FILE) {
counter++;
print_record(info, record);
}

printf("Records count: %d\n", counter);

my_free(record);
mi_close(info);

return 0;
}

#include "mi_extrafunc.h"

Thank you.

2 
void read_blob(uchar** var, uchar* buf, int pos, int len)
{
uchar *ptr;
ulong blob_len = _mi_calc_blob_length(len, buf+pos);

*var = (uchar *) my_malloc(sizeof(uchar*) * blob_len,MYF(MY_WME));
memcpy(&ptr, buf+pos+len, sizeof(uchar*));
memcpy(*var, ptr, sizeof(char*) * blob_len);
strcpy(*var + blob_len, "\0");
}

function for reading blogs :slight_smile:

3

Thanks for sharing your inputs guys. I believe that it is very helpful to us. :slight_smile: