MySQL Forums - Custom Storage Engines

- (no replies)

Petar Korponaic — Sat, 16 Mar 2013 01:16:17 +0000

test

Cursor fetching NULL values into variables (no replies)

Clement Francis — Sat, 22 Dec 2012 06:43:54 +0000

Hi,

I am working on a solution in which I have to read data from a table parse it and load them into the target table. I though of starting with a sample code that will read data from the source table using cursor and display them. However, My cursor is fetching NULL values into the variables.
The source table has data. I tested the SELECT statement used in the cursor declaration seperately and it is working as expected.

Below is the steps I followed. Am I missing something? Is there something wrong with the code?

thanks in advance.

-- Creating the schema
CREATE SCHEMA `test_cursor` ;

-- Creating the table
CREATE TABLE `product` (
`product_id` int(5) NOT NULL AUTO_INCREMENT,
`product_code` varchar(5) DEFAULT NULL,
PRIMARY KEY (`product_id`)
) ENGINE=InnoDB ;

-- Inserting test data into the table
INSERT INTO `test_cursor`.`product` (`product_code`) VALUES ('prodA');
INSERT INTO `test_cursor`.`product` (`product_code`) VALUES ('prodB');
INSERT INTO `test_cursor`.`product` (`product_code`) VALUES ('prodC');
INSERT INTO `test_cursor`.`product` (`product_code`) VALUES ('prodD');
INSERT INTO `test_cursor`.`product` (`product_code`) VALUES ('prodE');

-- Procedure
DELIMITER $$
CREATE PROCEDURE `test_cursor`.`P_READ_PRODUCT` ()
BEGIN
/*
The procedure fetches data from the source product table and displays the same.
*/
-- VARIABLE/CONSTANT DECLARATION
DECLARE v_done INT DEFAULT FALSE; -- Variable used in the continue handler.
-- variables used to store the values fetched by the cursor itr_product from product table.
DECLARE a int(5);
DECLARE b varchar(5);

-- Declaration of cursor for iterating through the unprocessed records in the source table.
DECLARE itr_product CURSOR FOR SELECT product_id, product_code FROM test_cursor.product;

DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_done=TRUE; -- Declaring continue handler for the cursor stg_product_data

OPEN itr_product; -- Open the cursor
read_loop: LOOP -- Loop through the records
FETCH itr_product INTO a, b; -- Fetching data into variables
IF v_done THEN -- Checking if the cursor has fetched the last record
LEAVE read_loop; -- Exit if last record had been fetched
ELSE
SELECT @a, @b ; -- Displaying the values fetched by the cursor
END IF;
END LOOP;
CLOSE itr_product; -- Close the cursor
END

-- Calling the procedure
call `test_cursor`.`P_READ_PRODUCT`();

-- Result set/Output
@a @b
null null

getting NULL value in uchar * buf in write_row(uchar *buf) (no replies)

Abhishek Varshney — Mon, 29 Oct 2012 06:01:06 +0000

Hi,

I am trying to write a custom storage engine. I have successfully implemented the create(), store_lock(), open() and rnd_init() functions following the guidelines from example storage engine. My implementation also more or less looks the same as in example storage engine. Now I wish to implement the write_row() function.
After successfully creating a table (through create table statement), I am trying to get uchar * buf from write_row(), but buf points to NULL everytime I try to debug this function. Also table->records[0] also gives NULL. Please help.

Thanks

internally used 3 or 4 byte (no replies)

alberto jeeni — Mon, 17 Sep 2012 14:09:50 +0000

We have a data store that is optimized for large scan and aggregation. Real simple example would be 'select Country, Sku, sum(Qty) from sales group by Country, Sku'. In addition to our storage format we have algorithms that use knowledge of that format to do the fast on demand aggregation. Right now the users interact with the data via a custom report type interface. We would like to add a SQL front end to the data store with out losing our aggregation strength. sons of anarchy season 5 episode 2

With out having looked into it, I am assuming that the interface between the sql engine and the storage engine is table based. That is the storage engine will return data to the sql engine as rows (I am sure this a simplification, the point being it is table oriented). watch the dark knight rises online The sql engine then does it's processing of the data (filtering, join, aggregating, etc...).

My question is does the api allow a 'push down' of the aggregation info so that the storage engine can return the data already aggregated? watch the expendables 2 online

if you mean DOS Kamenicky (aka keybcs2) or DOS Latin2 (aka cp852),
then MySQL does support both of them.
watch ted online
Otherwise, you can add your character set.
Starting from 4.1, it is to be done this way:

You need to edit:

/usr/local/mysql/share/charsets/exotic.xml
(by the way, what's its real name?)
watch horror movies online
Use say latin2.xml as an example.
You need to define these tables:
- Unicode mapping,
- lower and upper conversion
- ctype
- and sort order
basketball wives la season 2 episode 2

How to read and write table by MySQL 5.1 Storage Engine Plugin (no replies)

Vishal Ahir — Wed, 12 Sep 2012 06:26:08 +0000

Hi All,

I want to create mysql storage plugin for read and write data into database table,

So how can i make this type of plugin and which header and lib files requaired for develope plugin, And if possible then can anyone give me smaple code for that into VC++ 2008.

Thank you all of you.

Thanks & Regards,

Ahir Vishal D.

Cursor Support in MySql (no replies)

Santosh Aitha — Fri, 10 Aug 2012 10:36:42 +0000

Hi,

Currently in our application we have cursors through which we are calling procedures and now we are trying to migrate our application schema to MySQL Cluster. But we are unable migrate them as MySQL doesn't support procedure call with in the cluster.

Can somebody suggest if there is any alternative way to achive this.

Thanks,
Santosh

Need help: Facing issues with MySQL UDF implementations (no replies)

Santosh Aitha — Fri, 10 Aug 2012 09:54:38 +0000

Hi

I am trying to deply a MySQL cluster for in our telecome operating
system. I could see MySQl supports only events which can be scheduled
but I would like use events which can be posted by user as and when
required.

To implement this, I am trying to use MySQL UDF functions through which
I can open a socket and send a message to server running in my
application area. But I am facing some issues and when I call UDF
function from mysql server it is getting shutdown and dumping below
errors.

Can somebody help me to resolve the issue? Thanks

09:10:29 UTC - mysqld got signal 11 ;
This could be because you hit a bug. It is also possible that this
binary
or one of the libraries it was linked against is corrupt, improperly
built,
or misconfigured. This error can also be caused by malfunctioning
hardware.
We will try our best to scrape up some info that will hopefully help
diagnose the problem, but since we have already crashed,
something is definitely wrong and this may fail.

key_buffer_size=8388608
read_buffer_size=131072
max_used_connections=2
max_threads=151
thread_count=2
connection_count=2
It is possible that mysqld could use up to
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads =
337875 K bytes of memory
Hope that's ok; if not, decrease some variables in the equation.

Thread pointer: 0x9538c10
Attempting backtrace. You can use the following information to find out
where mysqld died. If you see no messages after this, something went
terribly wrong...
stack_bottom = ffffffffa7887390 thread_stack 0x30000
../bin/mysqld(my_print_stacktrace+0x34)[0x8474785]
../bin/mysqld(handle_fatal_signal+0x2f9)[0x834d311]
[0x110420]
../bin/mysqld(my_net_write+0xe7)[0x81d7d3b]
../bin/mysqld(_ZN8Protocol5writeEv+0x3e)[0x81dd100]
../bin/mysqld(_ZN11select_send9send_dataER4ListI4ItemE+0xd8)[0x821b1e2]
../bin/mysqld(_ZN4JOIN4execEv+0x336)[0x82602ba]
../bin/mysqld(_Z12mysql_selectP3THDPPP4ItemP10TABLE_LISTjR4ListIS1_ES2_jP8st_orderSB_S2_SB_yP13select_resultP18st_select_lex_unitP13st_select_lex+0x2a9)[0x8261fad]
../bin/mysqld(_Z13handle_selectP3THDP3LEXP13select_resultm+0x151)[0x825c021]
../bin/mysqld[0x824460a]
../bin/mysqld(_Z21mysql_execute_commandP3THD+0x616)[0x823f2a4]
../bin/mysqld(_Z11mysql_parseP3THDPcjP12Parser_state+0x1d7)[0x8245ce9]
../bin/mysqld(_Z16dispatch_command19enum_server_commandP3THDPcj+0x7f9)[0x823d355]
../bin/mysqld(_Z10do_commandP3THD+0x11d)[0x823c9fb]
../bin/mysqld(_Z24do_handle_one_connectionP3THD+0x162)[0x82e3764]
../bin/mysqld(handle_one_connection+0x2b)[0x82e3319]
/lib/libpthread.so.0[0x47b832]
/lib/libc.so.6(clone+0x5e)[0x3bb46e]

Trying to get some variables.
Some pointers may be invalid and cause the dump to abort.
Query (9501218): select myfunc_client("127.0.0.1")
Connection ID (thread ID): 5
Status: NOT_KILLED

The manual page at http://dev.mysql.com/doc/mysql/en/crashing.html
contains
information that should help you find out what is causing the crash.

Below is the UDF implementation. I am calling this function as below
from MySQL.

select myfunc_client("127.0.0.1");

int myfunc_client(UDF_INIT *initid __attribute__((unused)), UDF_ARGS
*argv,
char *is_null, char *error
__attribute__((unused)),char* message __attribute__((unused)))
{
int sockfd = 0, n = 0 ,rc;
char sendBuff[1024];
struct sockaddr_in serv_addr;
char in_addr[256];
char msg[256] = "hello, sending message, to server from UDF";
unsigned int length;

if((sockfd = socket(AF_INET, SOCK_STREAM, 0)) < 0)
{
strcpy(message,"\n Error : Could not create socket \n");
return 1;
}

memset(&serv_addr, '0', sizeof(serv_addr));

serv_addr.sin_family = AF_INET;
serv_addr.sin_port = htons(2001);

length = sizeof(argv->lengths[0]);
if (length >= sizeof(in_addr))
length=sizeof(in_addr)-1;
memcpy(in_addr,argv->args[0],length);
in_addr[length]=0;

memset(sendBuff, '0',sizeof(sendBuff));
if(inet_pton(AF_INET, in_addr, &serv_addr.sin_addr)<=0)
{
strcpy(message,"\n inet_pton error occured\n");
return 1;
}
rc = connect(sockfd, (struct sockaddr *)&serv_addr,
sizeof(serv_addr));
strcpy(message,"\n Error : Connect rc=\n");
if( rc < 0)
{
strcpy(message,"\n Error : Connect Failed rc=\n");
return 1;
}
strcpy(message,"\nconected to server");
memset(msg, '0',sizeof(msg));
snprintf(sendBuff, sizeof(sendBuff), "%s\n",msg);
strcpy(message,"before writing\n");
write(sockfd, sendBuff, strlen(sendBuff));

if(n < 0)
{
strcpy(message,"\n Read error \n");
}
strcpy(message,"read completed\n");

return 0;
}

Thanks,
Santosh

Retrieve original SQL statement from storage engine source code (1 reply)

Alexandre Savaris — Thu, 14 Jun 2012 13:31:31 +0000

Hello. I'm working on a read-only storage engine for academic purposes, and I can't figure out how to retrieve the original SQL statement (e.g. SELECT field_list FROM table) from the storage engine source code (more specifically, from inside the function rnd_init()). Is it possible?
I'm using MySQL version 5.6.5 on Debian Linux.
Thanks!

Using MySQL functions in Storage Engine (1 reply)

Alan Poly — Thu, 29 Mar 2012 20:51:38 +0000

I'm generate a convention storeroom engine, and I would like to utilize some C++ functions, implemented in MySQL foundation code (for example: ha_rows filesort function from sql/filesort.cc)
What header files/libs must I include to achive this?

JSON Storage and Query Language Retrieval (no replies)

The oNEROUS — Mon, 16 Jan 2012 05:18:10 +0000

I have in-vain tried to find a pre-existing plugin, solution for dealing with json strings in mysql.

I would like to be able to store json packets in mysql. And be able to query them in standard SQL, in a map reduce approach.

I would have assumed something similar to what MSSQL provides in way of storing XML and then providing functions to query via xpath.

Hope my post is clear.

Regards,

The O

Range Queries on Custom Storage Engines (no replies)

Primal Wijesekera — Tue, 02 Aug 2011 02:25:34 +0000

Hello All,

I am just trying to figure out how a range query such as below mentioned one would pass on the storage engine.

select * from usertable where age < 45 and age > 40;

The custom engine only called for index search of 40 not for 45. I was assuming it would call for both values.

It seems that internally mysql engine is doing the filtering for 45.

Is that correct ? or am I missing something here ?

Thank You very much in advance.

- primal

_ZTV7handler not found on solaris (solved) (no replies)

Colin MacKenzie — Mon, 11 Jul 2011 22:04:00 +0000

Someone posted they had linking errors comping ha_example on Solaris. I've gotten this to work on Solaris, I even had the _ZTV7handler not found link error. I had to play around with the link flags.

Here are the relevant flags I am using. I compile with Solaris SunStudio CC. These flags may not be quite right if using GCC.

CXXFLAGS=-m64 -mt -KPIC -DPIC -G -g -DDEBUG -DMYSQL_DYNAMIC_PLUGIN -DHAVE_RWLOCK_T -DMYSQLDB -DSOLARIS -DUSE_HSREGEX

LDFLAGS=-m64 -G

I think it was the -KPIC and -DPIC that made the difference but I cant remember.

mysql cache getting in the way of my realtime custome engine (no replies)

Colin MacKenzie — Mon, 11 Jul 2011 21:43:55 +0000

I wrote a custom mysql engine that returns realtime data from one of our systems. The first time one of the "virtual" tables are queried I get the right results, but after that mysql caches this and it never changes. I am trying to figure out how to tell mysql optimizer that the data has changes and a rescan has to happen.

I figured it was part of the info() callback.

Can anyone point me in the right direction?

Thanks,
Colin

IBMDB2I and ON UPDATE CASCADE (no replies)

hugo c — Fri, 13 May 2011 16:04:09 +0000

Hello,
I'm trying to create a table with a FK with "ON UPDATE CASCADE" using IBMDB2I storage engine on an IBM i (AS400). However apparently DB2 does not support ON UPDATE CASCADE and the create command fails. Has any one experienced this problem and/or has any ideas that may help? Thanks in advance.

-Hugo

Using MySQL functions in Storage Engine (no replies)

Forrai Krisztián — Wed, 02 Feb 2011 12:00:45 +0000

Hi!

I'm creating a custom storage engine, and I would like to use some C++ functions, implemented in MySQL source code (for example: ha_rows filesort function from sql/filesort.cc)

What header files/libs must I include to achive this?

Thanks,
Krisztian

Custom Storage Engines using Java (no replies)

Alexander Rutter — Mon, 24 Jan 2011 18:30:53 +0000

Hi,

is there any possibility to write a custom storage engine in Java and load it into MySQL?

I could try compiling the java source using gcj to get the .so shared library for MySQL, I guess, however, this won't work because there is no way to include the MySQL header files required.

Thanks in advance,
Alex

How to take backup of mysql (no replies)

Sarah Johnson — Tue, 21 Dec 2010 13:26:02 +0000

Hello,

Could you please tell me how to take backup of mysql from any hosting.

waiting for your reply

Sarah Johnson
Graphics Designer
http://logo-designs.experasolution.com

[ANN] PBXT 1.5.02 Beta Released! (no replies)

Paul McCullagh — Fri, 16 Jul 2010 14:24:22 +0000

Hi All,

I have just released PBXT 1.5.02 Beta. This version adds a 2nd level (SSD based) cache!

For more information on the 2nd Level Cache, please check out my blog: http://pbxt.blogspot.com/2010/07/pbxt-1502-beta-adds-2nd-level-cache.html

The new version is available for download from: http://primebase.org/download

The documentation (http://primebase.org/documentation) has been updated to cover versions 1.0, 1.1 and 1.5.

PBXT is an ACID compliant, transactional storage engine for MySQL. It features MVCC (multi-version concurrency control), row-level locking, referential integrity and has a log-based architecture.

PBXT is an open source project, licensed under GPL 2.0. Development is done on Launchpad: https://launchpad.net/pbxt.

The current stable release version of PBXT is 1.0.11 Pre-GA, which can be downloaded primebase.org or from Launchpad.

Bugs can be reported here: https://bugs.launchpad.net/pbxt.

If you have any questions or comments, please let me know.

Best regards,

Paul

--
Paul McCullagh
PrimeBase Technologies
www.primebase.org
www.blobstreaming.org
pbxt.blogspot.com

How to adopt MySQL tests for custom storage engines? (no replies)

Sonic Wang — Sat, 10 Jul 2010 06:40:15 +0000

Hi,

I am looking into adopting MySQL tests to test my custom storage engine. I found an article (http://forge.mysql.com/wiki/How_to_Create_Good_Tests) discussing that by running the command "./myql-test-run.pl --mysqld="--default-storage-engine=" " one shall be able to run a test case with "CREATE TABLE ..." statements with the specified engine being the default one.

By I constantly got information like follows:
"main.create [ skipped ] 'ABCENGINE' not supported

Here "ABCENGINE" is the string I registered in mysql_declare_plugin macro.

I have tried both in windows and linux. All reported the same information.

Does anybody know how to make custom engine work properly with MySQL test cases?

Thanks!

Sonic

_ZTV7handler not found on solaris (no replies)

sriram k — Fri, 09 Jul 2010 08:24:17 +0000

Hi,

I am getting problem (relocation problem because of unresolved symbol _ZTV7handler) when installing compiled plugin ha_example.so on Solaris 9.

when i do a nm on downloaded mysql server for Linux i am able to see the definition

nm ./work/mysql_instance/libexec/mysqld_fromrpm |grep _ZTV7handler
00000000009a6b00 V _ZTV7handler

grep _ZTV7handler

but on Solaris (MySQL 5.1.41)
nm ./work/mysql_instance/libexec/mysqld|grep _ZTV7handler
gives no result.

Any hint to resolve this will be of great help

Thanks & Regards
Sriram

Where to begin? (no replies)

Phillip Ward — Thu, 17 Jun 2010 11:57:59 +0000

My organisation makes use of a Windows-hosted application that is supported by a proprietary database (a B-tree variant). There is no ODBC driver available for this database but we would like to be able to read (NOT update) the data using "modern" reporting tools.

I'm considering creating a custom MySql Storage Engine (to run on Windows) using either of two, possible implementations:
* Access the underlying binary data file directly. I don't fancy this as the structure is completely undocumented and liable to change with different versions of the application.
* Use the application itself (via its OLE automation API) to retrieve the data. OK; this is potentially a lot slower but, for the moment anyway, that would be bearable.

Comments/Suggestions/Recommendations on where to start, please?

Regards,
Phill W.

add custom engine (no replies)

亚伟李 — Tue, 01 Jun 2010 02:03:42 +0000

If I want to add custom engine to MySQL server on windows,which files I need to modify? And my version is 5.5.2-m2.
Thanks in advance.

[ANN] PBXT 1.0.11 Pre-GA Released! (no replies)

Paul McCullagh — Wed, 12 May 2010 15:11:14 +0000

Hi All,

I have just released PBXT 1.0.11, which I have titled "Pre-GA". Going by our internal tests, and all instances of PBXT in production and testing by the community this is a GA version!

However, although PBXT has 1000's of instances in production, it is not used in very diverse applications. So I am waiting for wider testing and usage before removing the "Pre" prefix.

You can download the source code from http://primebase.org, or pull it straight from https://launchpad.net/pbxt. Here are instructions how to compile and build the engine with MySQL:http://primebase.org/download/#qg_source. PBXT builds with MySQL 5.1.46 GA (http://primebase.org/download/mysql-5.1.46.tar.gz), and earlier 5.1 versions.

If you don't want to compile it yourself, PBXT 1.0.11 will soon be available in the 5.1.46 release of MariaDB: http://askmonty.org/wiki/MariaDB:Download. And, for the more adventurous, PBXT 1.1 is included in Drizzle: https://launchpad.net/drizzle.

A complete list of all the changes in this version are in the release notes: http://primebase.org/download/ChangeLog.

If you are testing PBXT and have any questions send me an e-mail. I will be glad to help.

Otherwise, if you are looking for development or production support for MySQL/MariaDB and PBXT then please write to: support-at-primebase-dot-org. We are working together with Percona (http://www.percona.com) and Monty Program Ab (http://askmonty.org/wiki/Main_Page) to provide the service level you require.

Best regards,
Paul

--
Paul McCullagh
PrimeBase Technologies
www.primebase.org
www.blobstreaming.org
pbxt.blogspot.com

Columns read from an index (1 reply)

Debabrata Dash — Thu, 27 May 2010 06:45:02 +0000

In mysql does the storage engine always read the entire row from the table even when only a few columns are accessed in the query?

For example if a table T as columns [A, B], and the index is on [A].

in the following query, "select A from T where A = 'x'" does it always access columns A and B from the table or just accesses A?

If it accesses only A, how is that information passed on to the storage engine?

Compiling custom engine (2 replies)

Matt Coffman — Tue, 20 Apr 2010 13:46:53 +0000

I have tried both methods that I could find in the documentation and elsewhere but cannot get the compiler to see my engine (MySQL source download 5.1.45). I did a copy/replace of the example engine and followed the instructions to replace all instances of the word "example" with "myengine". I tried both of the following:

./configure --with-myengine-storage-engine
(replies unknown parameter --with-myengine-storage-engine)

and I also just tried "make clean, make". In both cases the compiler did not touch the files in my /storage/myengine directory. Am I missing something obvious? I see in several examples that they just say creating a directory under storage directory should be enough but it doesn't seem to work.

Thanks!

Splunk Custom Storage Engine (no replies)

David Carasso — Tue, 13 Apr 2010 20:41:24 +0000

We just finished an open-source project where we tie an unstructured search engine with MySQL. We'd really like you to try it and give us feedback...

Splunk is a popular, high-performance search engine optimized for IT data. It allows highly unstructured data to be inserted without having to have a rigid schema of columns. This is highly desired when processing data, like event logs, which are constantly changing and unpredictable. Splunk provides the structure at search-time.

With this project, you can access Splunk via SQL, using our Storage Engine Plugin for MySQL. This means that the data is physically stored inside of Splunk and accessed via SQL - there is no importing or exporting into a database. By using MySQL as a front-end, there is no end to the number of tools that can drive Splunk -- reporting tools, business intelligence, and applications.

Again, we'd really like you to try it and give us feedback.

Thanks

Read about it...

http://blogs.splunk.com/2010/02/10/sql-splunk-splunkmse/

Get it...

http://bitbucket.org/rdas/splunkengine/wiki/Home

creating indexes on custom storage engines (1 reply)

Debabrata Dash — Fri, 09 Apr 2010 13:00:31 +0000

Hi,
We are trying to build a custom read-only storage engine for Hbase storage system. We want to build indexes on the table, and always it comes back with the following error:

mysql> create index t1idx on t1(c1);
ERROR 1031 (HY000): Table storage engine for '#sql-38cc_2' doesn't have this option

What all steps we should take to make sure the storage engine supports the indexes? From the debug traces it seems the engine is trying to scan the table on Hbase and after the first row it gives up.

We will appreciate any help regarding this.

Regards,
Dash.

MySQL internal data format? (2 replies)

Sonic Wang — Thu, 27 May 2010 15:18:36 +0000

Hi,

I am looking into MySQL internal data format since storage engine needs to convert a MySQL value into its own format. Does anybody know where to find some detail specification about how data types are stored in MySQL records / fields?

Thanks.

A handler instance of a table is supposed to be non-concurrent accessed? (2 replies)

Sonic Wang — Thu, 27 May 2010 15:02:25 +0000

Hi,

I looked into several other engines' codes, and found they seemed to assume a handler instance of a table would not be accessed concurrently by multiple threads.

Is my understanding correctly? If so, does this mean that when a handler instance is running in some handler::xxx() calls, MYSQL won't have another thread issueing some other handler::yyy() calls to this instance?

Thanks

How to retrieve field values in update_row (2 replies)

sriram k — Mon, 05 Jul 2010 11:13:21 +0000

Hi,

Has any one worked on retrieving field values for old_data in update_row function.

Using table->fields i am able to retrieve field values for new_data without any problem.

Sriram