udf shared library import
I was not able to load a function in udf_example.so. The response to CREATE FUNCTION metaphon RETURNS STRING SONAME 'udf_example.so'; is Error Code: 1126. Can't open shared library 'udf_example.so' (errno: 0 /usr/lib64/mysql/plugin/udf_example.so: cannot open shared object file: No such file or directory) I did follow instructions 1. compile udf_example.so with gcc -fPIC -I/usr/include/mysql/ -shared -o udf_example.so udf_example.c 2. verify my plugin directory: show variables like 'plugin%' : /usr/lib64/mysql/plugin/ 3. place udf_example.so in /usr/lib64/mysql/plugin/ 4. verify I have a table mysql.func and a column 'type': SELECT * FROM mysql.func; Any hints? Thank you.
UDF Not Compiling
I cannot author a UDF using MySQL. I use the MySQL provided udf_example.cc and compile it using the following command: sudo gcc -shared -fPIC -I /usr/include/mysql -o /usr/lib/mysql/plugin/udf_example.so udf_example.cc Next, I login to mysql, connect to my database, and author one of the provided functions found in udf_example.cc: CREATE FUNCTION myfunc_int RETURNS int SONAME 'udf_example.so'; It generates the following error: ERROR 1127 (HY000): Can't find symbol 'myfunc_int' in library Per comments in udf_example.cc, I have verified that column 'type' exists in the mysql.func. I have run mysql_upgrade and still get the same error. If I compile the udf_example.cc with the -c option, when issuing the CREATE FUNCTION ... command, I see the following error: ERROR 1126 (HY000): Can't open shared library 'udf_example.so' (errno: 0 /usr/lib/mysql/plugin/udf_example.so: only ET_DYN and ET_EXEC can be loaded) Thanks, Shahram. How-To-Repeat: code/input/activities to reproduce the problem (multiple lines) Fix: how to correct or work around the problem, if known (multiple lines) Submitter-Id: submitter ID Originator: Organization: organization of PR author (multiple lines) MySQL support: [none | licence | email support | extended email support ] Synopsis: synopsis of the problem (one line) Severity: [ non-critical | serious | critical ] (one line) Priority: [ low | medium | high ] (one line) Category: mysql Class: [ sw-bug | doc-bug | change-request | support ] (one line) Release: mysql-5.5.38-0+wheezy1 ((Debian)) C compiler:gcc-4.7.real (Debian 4.7.2-5) 4.7.2 C++ compiler: g++-4.7.real (Debian 4.7.2-5) 4.7.2 Environment: machine, os, target, libraries (multiple lines) System: Linux rdbms 3.2.0-4-amd64 #1 SMP Debian 3.2.60-1+deb7u1 x86_64 GNU/Linux Some paths: /usr/bin/perl /usr/bin/make /usr/bin/gcc /usr/bin/cc GCC: Using built-in specs. COLLECT_GCC=/usr/bin/gcc COLLECT_LTO_WRAPPER=/usr/lib/gcc/x86_64-linux-gnu/4.7/lto-wrapper Target: x86_64-linux-gnu Configured with: ../src/configure -v --with-pkgversion='Debian 4.7.2-5' --with-$ Thread model: posix gcc version 4.7.2 (Debian 4.7.2-5) Compilation info (call): CC='/usr/bin/x86_64-linux-gnu-gcc' CFLAGS='-O2 -DBIG-DBIG_JOINS=1 -fno-strict-aliasing -Wall -O2 -g -DDBUG_OFF' CXX='/usr/bin/x86_64-linux-gnu-g++' CXXFLAGS='-O3 -DBIG_JOINS=1 -felide-constructors -fno-exceptions -fno-rtti -fno-strict-aliasing -Wall -Wno-unused-parameter -fno-implicit-templates -fno-exceptions -fno-rtti -O2 -g -DDBUG_OFF' LDFLAGS='' ASFLAGS='' Compilation info (used): CC='/usr/bin/x86_64-linux-gnu-gcc' CFLAGS='-O2 -DBIG-DBIG_JOINS=1 -fno-strict-aliasing -Wall -O2 -g -DDBUG_OFF' CXX='/usr/bin/x86_64-linux-gnu-g++' CXXFLAGS='-O3 -DBIG_JOINS=1 -felide-constructors -fno-exceptions -fno-rtti -fno-strict-aliasing -Wall -Wno-unused-parameter -fno-implicit-templates -fno-exceptions -fno-rtti -O2 -g -DDBUG_OFF' LDFLAGS='' ASFLAGS='' $-linux-gnu-thread-multi Perl: This is perl 5, version 14, subversion 2 (v5.14.2) built for x86_64-linux$
RE: UDF Not Compiling
I cannot author a UDF using MySQL. I use the MySQL provided udf_example.cc and compile it using the following command: sudo gcc -shared -fPIC -I /usr/include/mysql -o /usr/lib/mysql/plugin/udf_example.so udf_example.cc Next, I login to mysql, connect to my database, and author one of the provided functions found in udf_example.cc: CREATE FUNCTION myfunc_int RETURNS int SONAME 'udf_example.so'; It generates the following error: ERROR 1127 (HY000): Can't find symbol 'myfunc_int' in library Per comments in udf_example.cc, I have verified that column 'type' exists in the mysql.func. I have run mysql_upgrade and still get the same error. If I compile the udf_example.cc with the -c option, when issuing the CREATE FUNCTION ... command, I see the following error: ERROR 1126 (HY000): Can't open shared library 'udf_example.so' (errno: 0 /usr/lib/mysql/plugin/udf_example.so: only ET_DYN and ET_EXEC can be loaded) How-To-Repeat: code/input/activities to reproduce the problem (multiple lines) Fix: how to correct or work around the problem, if known (multiple lines) Submitter-Id: submitter ID Originator: Organization: organization of PR author (multiple lines) MySQL support: [none | licence | email support | extended email support ] Synopsis: synopsis of the problem (one line) Severity: [ non-critical | serious | critical ] (one line) Priority: [ low | medium | high ] (one line) Category: mysql Class: [ sw-bug | doc-bug | change-request | support ] (one line) Release: mysql-5.5.38-0+wheezy1 ((Debian)) C compiler:gcc-4.7.real (Debian 4.7.2-5) 4.7.2 C++ compiler: g++-4.7.real (Debian 4.7.2-5) 4.7.2 Environment: machine, os, target, libraries (multiple lines) System: Linux rdbms 3.2.0-4-amd64 #1 SMP Debian 3.2.60-1+deb7u1 x86_64 GNU/Linux Some paths: /usr/bin/perl /usr/bin/make /usr/bin/gcc /usr/bin/cc GCC: Using built-in specs. COLLECT_GCC=/usr/bin/gcc COLLECT_LTO_WRAPPER=/usr/lib/gcc/x86_64-linux-gnu/4.7/lto-wrapper Target: x86_64-linux-gnu Configured with: ../src/configure -v --with-pkgversion='Debian 4.7.2-5' --with-h-bugurl=file:///usr/share/doc/gcc-4.7/README.Bugs --enable-languages=c,c++,go,fortran,objc,obj-c++ --prefix=/usr --program-suffix=-4.7 --enable-shared --enable-linker-build-id --with-system-zlib --libexecdir=/usr/lib --without-included-gettext --enable-threads=posix --with-gxx-include-dir=/usr/include/c++/4.7 --libdir=/usr/lib --enable-nls --with-sysroot=/ --enable-clocale=gnu --enable-libstdcxx-debug --enable-libstdcxx-time=yes--enable-gnu-unique-object --enable-plugin --enable-objc-gc --with-arch-32=i586 --with-tune=generic --enable-checking=release --build=x86_64-linux-gnu --host=x86_64-linux-gnu --target=x86_64-linux-gnu Thread model: posix gcc version 4.7.2 (Debian 4.7.2-5) Compilation info (call): CC='/usr/bin/x86_64-linux-gnu-gcc' CFLAGS='-O2 -DBIG-DBIG_JOINS=1 -fno-strict-aliasing -Wall -O2 -g -DDBUG_OFF' CXX='/usr/bin/x86_64-linux-gnu-g++' CXXFLAGS='-O3 -DBIG_JOINS=1 -felide-constructors -fno-exceptions -fno-rtti -fno-strict-aliasing -Wall -Wno-unused-parameter -fno-implicit-templates -fno-exceptions -fno-rtti -O2 -g -DDBUG_OFF' LDFLAGS='' ASFLAGS='' Compilation info (used): CC='/usr/bin/x86_64-linux-gnu-gcc' CFLAGS='-O2 -DBIG-DBIG_JOINS=1 -fno-strict-aliasing -Wall -O2 -g -DDBUG_OFF' CXX='/usr/bin/x86_64-linux-gnu-g++' CXXFLAGS='-O3 -DBIG_JOINS=1 -felide-constructors -fno-exceptions -fno-rtti -fno-strict-aliasing -Wall -Wno-unused-parameter -fno-implicit-templates -fno-exceptions -fno-rtti -O2 -g -DDBUG_OFF' LDFLAGS='' ASFLAGS='' $-linux-gnu-thread-multi Perl: This is perl 5, version 14, subversion 2 (v5.14.2) built for x86_64-linux$
Re: UDF behaves non-deterministic
Hi everybody, it looks like the reason for the problem was me not handling string arguments properly (I did not use the provided lengths, but relied on string being null-terminated, it's in the doc, but ...). It seems this became a problem specifically in the parallel situation, misleading me into believing it had to do with this. Thanks to everybody for help! Even though the solution was not directly provided, the comments made me think about my code and so were still helpfull. Stefan On Monday 05 November 2012 15:08:51 Michael Dykman wrote: C is not an inherently thread-safe language. Several of the standard library functions use static data, which gets stepped on during concurrent operation. Many of those do have thread-safe equivalents on many platforms such as strtok/strtok_r (the latter being the safe one). If you are confident you are not using statics or globals in your code directly, you will need to identify each function you do call. Start by reading the man page for that function (if it's in the C stdlib, there is a man page for it) which should tell you if it is safe or not; for those which are not, the man page will likely suggest a threadsafe alternative if one is available. If none are available, you might have to consider a mutex. - michael dykman On Mon, Nov 5, 2012 at 9:28 AM, Stefan Kuhn stef...@web.de wrote: Hi Dan, thanks for your answer. The UDF only contains functions (the one called in sql plus two functions called in it). There are no variables outside them and nothing is declared static. All variables inside the functions are declared just like double x=0; etc. I am not an expert on C, but my understanding is that these values are separate for each call of the function and don't influence each other. Do you have a suggestion what I should look for in my c code? Or do I need to make the code thread-safe in that sense that concurrent executions are prevented by monitors or semaphors or so (no idea about what this is called in c)? Stefan The first thing I would do is examine your UDF and ensure that it is thread-safe. No global variables, no static variables within functions, etc. Also make sure that any libc functions you call that are documented as non-threadsafe are wrapped by a mutex or otherwise protected against multiple simultaneous access. http://dev.mysql.com/doc/refman/5.5/en/adding-udf.html As for debugging, you should be able to write things to stderr which will show up in the mysql logfile, or you could open your own logfile and write to that. -- Dan Nelson dnel...@allantgroup.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Aw: Re: UDF behaves non-deterministic
Hi Dan, thanks for your answer. The UDF only contains functions (the one called in sql plus two functions called in it). There are no variables outside them and nothing is declared static. All variables inside the functions are declared just like double x=0; etc. I am not an expert on C, but my understanding is that these values are separate for each call of the function and don't influence each other. Do you have a suggestion what I should look for in my c code? Or do I need to make the code thread-safe in that sense that concurrent executions are prevented by monitors or semaphors or so (no idea about what this is called in c)? Stefan The first thing I would do is examine your UDF and ensure that it is thread-safe. No global variables, no static variables within functions, etc. Also make sure that any libc functions you call that are documented as non-threadsafe are wrapped by a mutex or otherwise protected against multiple simultaneous access. http://dev.mysql.com/doc/refman/5.5/en/adding-udf.html As for debugging, you should be able to write things to stderr which will show up in the mysql logfile, or you could open your own logfile and write to that. -- Dan Nelson dnel...@allantgroup.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Re: UDF behaves non-deterministic
C is not an inherently thread-safe language. Several of the standard library functions use static data, which gets stepped on during concurrent operation. Many of those do have thread-safe equivalents on many platforms such as strtok/strtok_r (the latter being the safe one). If you are confident you are not using statics or globals in your code directly, you will need to identify each function you do call. Start by reading the man page for that function (if it's in the C stdlib, there is a man page for it) which should tell you if it is safe or not; for those which are not, the man page will likely suggest a threadsafe alternative if one is available. If none are available, you might have to consider a mutex. - michael dykman On Mon, Nov 5, 2012 at 9:28 AM, Stefan Kuhn stef...@web.de wrote: Hi Dan, thanks for your answer. The UDF only contains functions (the one called in sql plus two functions called in it). There are no variables outside them and nothing is declared static. All variables inside the functions are declared just like double x=0; etc. I am not an expert on C, but my understanding is that these values are separate for each call of the function and don't influence each other. Do you have a suggestion what I should look for in my c code? Or do I need to make the code thread-safe in that sense that concurrent executions are prevented by monitors or semaphors or so (no idea about what this is called in c)? Stefan The first thing I would do is examine your UDF and ensure that it is thread-safe. No global variables, no static variables within functions, etc. Also make sure that any libc functions you call that are documented as non-threadsafe are wrapped by a mutex or otherwise protected against multiple simultaneous access. http://dev.mysql.com/doc/refman/5.5/en/adding-udf.html As for debugging, you should be able to write things to stderr which will show up in the mysql logfile, or you could open your own logfile and write to that. -- Dan Nelson dnel...@allantgroup.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql -- - michael dykman - mdyk...@gmail.com May the Source be with you.
Re: UDF behaves non-deterministic
can you reduce the UDF just to return 1; ? that should give you a clue what is going on. Random values usualy point to two suspects 1. mixing 32bit and 64bit 2. using void instead of int re, wh Am 04.11.2012 23:23, schrieb Stefan Kuhn: Hi all, I have a weired (for me at least) problem with a user defined function, written in C. The function seems to return different results in different runs (the code of the function does not contain random elements). Basically, the function calculates a score based on a column in a table and an input value. So I do something like this: select * from table order by udf(column, 'input_value') desc; For my understanding, this should give the same result always. But if I run many statements (execution is from a java program and I can do it in parallel threads) so that they overlap (the udf on a large table takes 5-10 s on a slow machine), the results of some queries are different. If I have enough time between statements, it seems to work, i. e. the result is always the same. I would have thought the statements are independent, even if executed on different jdbc connections in parallel. Does somebody have an idea? Or could somebody give an idea on debugging? Normally I would try to debug the code to see what goes on, but how can I do this in a udf? Can I log in the udf? Thanks for any hints, Stefan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: UDF behaves non-deterministic
2012/11/04 22:23 +, Stefan Kuhn select * from table order by udf(column, 'input_value') desc; For my understanding, this should give the same result always. But if for your data function udf returns the same for more arguments there is not enough to fix the order. In that case I have found that other accidental things affect the order, things that one would not suspect: howmuch store is used and needed for the ordering, ... a further reason for showing what the function returns. If the order varies, although the function returns the same in all cases, well, -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: UDF behaves non-deterministic
On Monday 05 November 2012 18:02:28 h...@tbbs.net wrote: 2012/11/04 22:23 +, Stefan Kuhn select * from table order by udf(column, 'input_value') desc; For my understanding, this should give the same result always. But if for your data function udf returns the same for more arguments there is not enough to fix the order. In that case I have found that other accidental things affect the order, things that one would not suspect: howmuch store is used and needed for the ordering, ... a further reason for showing what the function returns. If the order varies, although the function returns the same in all cases, well, True, but I am missing records from the top ten which should definitly be in, so this should not be the problem here. I am investigating things further... Stefan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
UDF behaves non-deterministic
Hi all, I have a weired (for me at least) problem with a user defined function, written in C. The function seems to return different results in different runs (the code of the function does not contain random elements). Basically, the function calculates a score based on a column in a table and an input value. So I do something like this: select * from table order by udf(column, 'input_value') desc; For my understanding, this should give the same result always. But if I run many statements (execution is from a java program and I can do it in parallel threads) so that they overlap (the udf on a large table takes 5-10 s on a slow machine), the results of some queries are different. If I have enough time between statements, it seems to work, i. e. the result is always the same. I would have thought the statements are independent, even if executed on different jdbc connections in parallel. Does somebody have an idea? Or could somebody give an idea on debugging? Normally I would try to debug the code to see what goes on, but how can I do this in a udf? Can I log in the udf? Thanks for any hints, Stefan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: UDF behaves non-deterministic
A couple of questions present. You mention that selecting from the whole table takes 5-10s so I assume you have a lot of records. is the data not in flux? are you sure? these conflict queries are all on the same server? i would have structured the query like so: select *, udf(column,'value') AS u from table order by u; I suspect it might reduce the number of udf invocations.. the order by clause is frequently referred to in the process of sorting.. keeping that static instead of dynamic might sanitize your issue. On 2012-11-04 4:24 PM, Stefan Kuhn stef...@web.de wrote: Hi all, I have a weired (for me at least) problem with a user defined function, written in C. The function seems to return different results in different runs (the code of the function does not contain random elements). Basically, the function calculates a score based on a column in a table and an input value. So I do something like this: select * from table order by udf(column, 'input_value') desc; For my understanding, this should give the same result always. But if I run many statements (execution is from a java program and I can do it in parallel threads) so that they overlap (the udf on a large table takes 5-10 s on a slow machine), the results of some queries are different. If I have enough time between statements, it seems to work, i. e. the result is always the same. I would have thought the statements are independent, even if executed on different jdbc connections in parallel. Does somebody have an idea? Or could somebody give an idea on debugging? Normally I would try to debug the code to see what goes on, but how can I do this in a udf? Can I log in the udf? Thanks for any hints, Stefan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: UDF behaves non-deterministic
On Sunday 04 November 2012 22:34:22 Michael Dykman wrote: A couple of questions present. You mention that selecting from the whole table takes 5-10s so I assume you have a lot of records. Yes, and the calculation of the score is fairly complicated. Plust the test server is slow (Pentium III machine, old, but working) is the data not in flux? are you sure? Yes, I am. I have a test server, where nothing happens. these conflict queries are all on the same server? Yes, one mysql instance on one server i would have structured the query like so: select *, udf(column,'value') AS u from table order by u; I tried this and whilst it gives a speedup (around 25%, I would say), it does not solve the problem (but thanks for the hint, I didn't think this makes a difference). I suspect it might reduce the number of udf invocations.. the order by clause is frequently referred to in the process of sorting.. keeping that static instead of dynamic might sanitize your issue. On 2012-11-04 4:24 PM, Stefan Kuhn stef...@web.de wrote: Hi all, I have a weired (for me at least) problem with a user defined function, written in C. The function seems to return different results in different runs (the code of the function does not contain random elements). Basically, the function calculates a score based on a column in a table and an input value. So I do something like this: select * from table order by udf(column, 'input_value') desc; For my understanding, this should give the same result always. But if I run many statements (execution is from a java program and I can do it in parallel threads) so that they overlap (the udf on a large table takes 5-10 s on a slow machine), the results of some queries are different. If I have enough time between statements, it seems to work, i. e. the result is always the same. I would have thought the statements are independent, even if executed on different jdbc connections in parallel. Does somebody have an idea? Or could somebody give an idea on debugging? Normally I would try to debug the code to see what goes on, but how can I do this in a udf? Can I log in the udf? Thanks for any hints, Stefan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: UDF behaves non-deterministic
In the last episode (Nov 04), Stefan Kuhn said: I have a weired (for me at least) problem with a user defined function, written in C. The function seems to return different results in different runs (the code of the function does not contain random elements). Basically, the function calculates a score based on a column in a table and an input value. So I do something like this: select * from table order by udf(column, 'input_value') desc; For my understanding, this should give the same result always. But if I run many statements (execution is from a java program and I can do it in parallel threads) so that they overlap (the udf on a large table takes 5-10 s on a slow machine), the results of some queries are different. If I have enough time between statements, it seems to work, i. e. the result is always the same. I would have thought the statements are independent, even if executed on different jdbc connections in parallel. Does somebody have an idea? Or could somebody give an idea on debugging? Normally I would try to debug the code to see what goes on, but how can I do this in a udf? Can I log in the udf? The first thing I would do is examine your UDF and ensure that it is thread-safe. No global variables, no static variables within functions, etc. Also make sure that any libc functions you call that are documented as non-threadsafe are wrapped by a mutex or otherwise protected against multiple simultaneous access. http://dev.mysql.com/doc/refman/5.5/en/adding-udf.html As for debugging, you should be able to write things to stderr which will show up in the mysql logfile, or you could open your own logfile and write to that. -- Dan Nelson dnel...@allantgroup.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
UDF writing to unix socket - segfaults?
I am writing a UDF for sending messages to a local daemon. I've been trying to make it use a UNIX socket, but it keeps segfaulting on connect() or sendto(). I have double and tripled checked everything, but I'm not finding anything. After a day or two, I finally decided to switch to UDP and writing to localhost instead, which works fine. I still feel writing to a UNIX socket ought to work and even if I've got a permission issue or similar, a segfault is not the appropriate handling. Any clues? -- Per Jessen, Zürich (16.5°C) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
RE: UDF writing to unix socket - segfaults?
assuming you worked out the access to network by your code permissions that means memory heap or stack is being overrun...you would be well advised to download the connect() and sendto() code from the OS vendor and toss in plenty of codeguard and debug statements and recompile and rebuild. Mit Fruendlichen Grüßen Martin (12.2°C) __ Verzicht und Vertraulichkeitanmerkung/Note de déni et de confidentialité Diese Nachricht ist vertraulich. Sollten Sie nicht der vorgesehene Empfaenger sein, so bitten wir hoeflich um eine Mitteilung. Jede unbefugte Weiterleitung oder Fertigung einer Kopie ist unzulaessig. Diese Nachricht dient lediglich dem Austausch von Informationen und entfaltet keine rechtliche Bindungswirkung. Aufgrund der leichten Manipulierbarkeit von E-Mails koennen wir keine Haftung fuer den Inhalt uebernehmen. Ce message est confidentiel et peut être privilégié. Si vous n'êtes pas le destinataire prévu, nous te demandons avec bonté que pour satisfaire informez l'expéditeur. N'importe quelle diffusion non autorisée ou la copie de ceci est interdite. Ce message sert à l'information seulement et n'aura pas n'importe quel effet légalement obligatoire. Étant donné que les email peuvent facilement être sujets à la manipulation, nous ne pouvons accepter aucune responsabilité pour le contenu fourni. From: p...@computer.org Subject: UDF writing to unix socket - segfaults? Date: Wed, 8 Aug 2012 08:26:23 +0200 To: mysql@lists.mysql.com I am writing a UDF for sending messages to a local daemon. I've been trying to make it use a UNIX socket, but it keeps segfaulting on connect() or sendto(). I have double and tripled checked everything, but I'm not finding anything. After a day or two, I finally decided to switch to UDP and writing to localhost instead, which works fine. I still feel writing to a UNIX socket ought to work and even if I've got a permission issue or similar, a segfault is not the appropriate handling. Any clues? -- Per Jessen, Zürich (16.5°C) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
RE: UDF writing to unix socket - segfaults?
Martin Gainty wrote: assuming you worked out the access to network by your code permissions When I open the unix socket, I give it 0777. that means memory heap or stack is being overrun...you would be well advised to download the connect() and sendto() code from the OS vendor I'm running on Linux, so that is glibc - they work fine in a lot of other code. I find it hard to suspect those two. /Per -- Per Jessen, Zürich (23.0°C) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
RE: UDF writing to unix socket - segfaults?
this is pure speculation unless we can get ahold of the source code for your specific version of glibc and determine what the maximum sizes are .. otherwise anything I suggest would be speculative..lets take a look at http://fossies.org/dox/glibc-2.16.0/sysdeps_2mach_2hurd_2sendto_8c_source.html ssize_t 29 __sendto (int fd, 30 const void *buf, 31 size_t n, 32 int flags, 33 const struct sockaddr_un *addr, 34 socklen_t addr_len) Here his maximum buffer length for sending is unsigned int specifically size_t so.. are both send and receive entities IPv4 or both entities are IPv6 if thats the case.. can you send 0 Bytes 2 bytes 4 bytes 8 bytes at what maximum length of buffer does the segfault occur ? Martin __ Verzicht und Vertraulichkeitanmerkung/Note de déni et de confidentialité Diese Nachricht ist vertraulich. Sollten Sie nicht der vorgesehene Empfaenger sein, so bitten wir hoeflich um eine Mitteilung. Jede unbefugte Weiterleitung oder Fertigung einer Kopie ist unzulaessig. Diese Nachricht dient lediglich dem Austausch von Informationen und entfaltet keine rechtliche Bindungswirkung. Aufgrund der leichten Manipulierbarkeit von E-Mails koennen wir keine Haftung fuer den Inhalt uebernehmen. Ce message est confidentiel et peut être privilégié. Si vous n'êtes pas le destinataire prévu, nous te demandons avec bonté que pour satisfaire informez l'expéditeur. N'importe quelle diffusion non autorisée ou la copie de ceci est interdite. Ce message sert à l'information seulement et n'aura pas n'importe quel effet légalement obligatoire. Étant donné que les email peuvent facilement être sujets à la manipulation, nous ne pouvons accepter aucune responsabilité pour le contenu fourni. From: p...@computer.org Subject: RE: UDF writing to unix socket - segfaults? Date: Wed, 8 Aug 2012 15:39:09 +0200 To: mysql@lists.mysql.com Martin Gainty wrote: assuming you worked out the access to network by your code permissions When I open the unix socket, I give it 0777. that means memory heap or stack is being overrun...you would be well advised to download the connect() and sendto() code from the OS vendor I'm running on Linux, so that is glibc - they work fine in a lot of other code. I find it hard to suspect those two. /Per -- Per Jessen, Zürich (23.0°C) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
RE: UDF writing to unix socket - segfaults?
Martin Gainty wrote: this is pure speculation unless we can get ahold of the source code for your specific version of glibc and determine what the maximum sizes are .. otherwise anything I suggest would be speculative..lets take a look at http://fossies.org/dox/glibc-2.16.0/sysdeps_2mach_2hurd_2sendto_8c_source.html ssize_t 29 __sendto (int fd, 30 const void *buf, 31 size_t n, 32 int flags, 33 const struct sockaddr_un *addr, 34 socklen_t addr_len) Here his maximum buffer length for sending is unsigned int specifically size_t so.. are both send and receive entities IPv4 or both entities are IPv6 if thats the case.. can you send 0 Bytes 2 bytes 4 bytes 8 bytes at what maximum length of buffer does the segfault occur ? Interesting that you should pick on the length - I think that might be it. When I switched to using a UDP socket, sendto() did complain about the length. (error 90, message too long). Hmm, that's probably a UDP packet-size restriction, whereas no such restriction exist for a unix socket (I presume). Thanks for the idea! -- Per Jessen, Zürich (24.4°C) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Is it it posiible to combine the C++ algorithm code, the MYSQL source code and the UDF code into a single DLL/SO?
Good afternoon, We developed a C++ class algorithm and code together with sqlite3.c and the sqlite C/C++ UDFs. Everything is combined into a single Windows DLL/UNIX-LINUX SO Now we would like to change from SQLite to MySQL to take advantage of MySQL's ability to do parallel writes on separate threads. We would still like to combine our c++ algorithm class code, MySQL.c (I am not sure of the exact name) and the MySQL C/C++ UDFS into a single Windows DLL/ Unix-Linux SO. If so, How can this be done? Thank you,
Re: Is it it posiible to combine the C++ algorithm code, the MYSQL source code and the UDF code into a single DLL/SO?
Good evening, Apparently MySQL supports a single client connection to a local MySQL server Quoting from the MySQL :: MySQL 5.0 Reference Manual :: 4.2.2 Connecting to the MySQL Server URL by Booz-Allen New York City consultants The following table shows the permissible --protocol option values on different operating systems. TCP TCP/IP connection to local or remote server All OS SOCKET Unix socket file connection to local server Unix only PIPE Named-pipe connection to local or remote server Windows only MEMORY Shared-memory connection to local server Windows only On Thu, Sep 8, 2011 at 12:46 PM, Frank Chang frankchan...@gmail.com wrote: Good afternoon, We developed a C++ class algorithm and code together with sqlite3.c and the sqlite C/C++ UDFs. Everything is combined into a single Windows DLL/UNIX-LINUX SO Now we would like to change from SQLite to MySQL to take advantage of MySQL's ability to do parallel writes on separate threads. We would still like to combine our c++ algorithm class code, MySQL.c (I am not sure of the exact name) and the MySQL C/C++ UDFS into a single Windows DLL/ Unix-Linux SO. If so, How can this be done? Thank you,
Exceptions not caught in UDF written in C++ in MySQL 5.5
Hello guys, after upgrading from MySQL 5.1 to 5.5 we encounter a problem that C++ exceptions thrown in an UDF are not catched at all. Even there is a catch (... ) at the end or our try block which avoids exceptions to be thrown outside of the UDF, the server crashes with: terminate called after throwing an instance of 'DATE::DateException' 110830 8:27:34 - mysqld got signal 6 ; Even a try to catch DATE::DateException was no success. The very same thing was verified with 5.1 and worked out of the box. Any ideas? Cheers, Heri -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
udf return column name not value
CREATE FUNCTION getcolumnvalue(id int,columnname varchar(30))
RETURNS varchar(50) DETERMINISTIC
READS SQL DATA
begin
declare retval varchar(50);
select columnname into retval from user where ID = id ;
return retval;
end;
I want get value of related column but it return column name.
ex:- ('tom' what I want but it return 'name')
plz tell what the wrong of this
thank you
RE: udf return column name not value
You'll have to do something like this:
SET @sql := CONCAT('select ',columnname,' into retval from user where ID=',id);
PREPARE mySt FROM @sql;
EXECUTE mySt;
-Original Message-
From: chamila gayan [mailto:cgcham...@gmail.com]
Sent: Monday, March 15, 2010 12:58 AM
To: mysql@lists.mysql.com
Subject: udf return column name not value
CREATE FUNCTION getcolumnvalue(id int,columnname varchar(30))
RETURNS varchar(50) DETERMINISTIC
READS SQL DATA
begin
declare retval varchar(50);
return retval;
end;
I want get value of related column but it return column name.
ex:- ('tom' what I want but it return 'name')
plz tell what the wrong of this
thank you
This message contains confidential information and is intended only for the
individual named. If you are not the named addressee, you are notified that
reviewing, disseminating, disclosing, copying or distributing this e-mail is
strictly prohibited. Please notify the sender immediately by e-mail if you
have received this e-mail by mistake and delete this e-mail from your system.
E-mail transmission cannot be guaranteed to be secure or error-free as
information could be intercepted, corrupted, lost, destroyed, arrive late or
incomplete, or contain viruses. The sender therefore does not accept liability
for any loss or damage caused by viruses or errors or omissions in the contents
of this message, which arise as a result of e-mail transmission. [FriendFinder
Networks, Inc., 220 Humbolt court, Sunnyvale, CA 94089, USA, FriendFinder.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: udf return column name not value
Thank you for your speedy reply.
I tried on your way. But it gives *error* as *Dynamic SQL is not allowed in
stored function or trigger*. I'm using *MySql 5* as my sever. How can I
solve that.
thank you
On Mon, Mar 15, 2010 at 11:43 PM, Gavin Towey gto...@ffn.com wrote:
You'll have to do something like this:
SET @sql := CONCAT('select ',columnname,' into retval from user where
ID=',id);
PREPARE mySt FROM @sql;
EXECUTE mySt;
-Original Message-
From: chamila gayan [mailto:cgcham...@gmail.com]
Sent: Monday, March 15, 2010 12:58 AM
To: mysql@lists.mysql.com
Subject: udf return column name not value
CREATE FUNCTION getcolumnvalue(id int,columnname varchar(30))
RETURNS varchar(50) DETERMINISTIC
READS SQL DATA
begin
declare retval varchar(50);
return retval;
end;
I want get value of related column but it return column name.
ex:- ('tom' what I want but it return 'name')
plz tell what the wrong of this
thank you
This message contains confidential information and is intended only for the
individual named. If you are not the named addressee, you are notified that
reviewing, disseminating, disclosing, copying or distributing this e-mail is
strictly prohibited. Please notify the sender immediately by e-mail if you
have received this e-mail by mistake and delete this e-mail from your
system. E-mail transmission cannot be guaranteed to be secure or error-free
as information could be intercepted, corrupted, lost, destroyed, arrive late
or incomplete, or contain viruses. The sender therefore does not accept
liability for any loss or damage caused by viruses or errors or omissions in
the contents of this message, which arise as a result of e-mail
transmission. [FriendFinder Networks, Inc., 220 Humbolt court, Sunnyvale, CA
94089, USA, FriendFinder.com
Re: UDF - Sequence Numbers
After some tinkering around on one of my test database servers with this sequence table design; I see what you mean. Defining the table with store_id and seq_id columns allows me to UPDATE table SET seq_id=LAST_INSERT_ID(seq_id+1) WHERE store_id=N and will block any further updates on store_id=N to that table until the entire transaction block is COMMIT'd. However, it does allow me to issue the same statement with store_id=X and is not blocked. I thought this design would allow for gaps in the sequence numbering, but it works great. Thanks! On Mon, Mar 8, 2010 at 4:10 PM, Gavin Towey gto...@ffn.com wrote: Others may correct me if I'm wrong, but if you want to maintain your own auto increment without duplicates like that, then you need to serialize inserts to your table. Which means either doing an explicit table lock by the process that's inserting, or using innodb with transactions in the SERIALIZABLE tx isolation mode. If I were you, I would maintain your incrementing sequence in a separate table. Prep the table: CREATE TABLE store_seq ( store_id int unsigned not null, nextid int unsigned not null ); insert into store_seq ( 1, 1 ); Get next id: update store_seq set nextid=LAST_INSERT_ID(nextid+1) where store_id=1; select LAST_INSERT_ID(); This will give you the next id in an atomic way, and avoid replication problems. It means your insert will take two queries, but that's a small price to pay for correctness. Regards, Gavin Towey -Original Message- From: Johnny Withers [mailto:joh...@pixelated.net] Sent: Monday, March 08, 2010 1:31 PM To: MySQL General List Subject: UDF - Sequence Numbers I have two servers, both running 5.0.77-log, one is setup as a master, the other as a replication slave. The database contains a table that holds records of loans for financial lending stores. This table has an ID column this is defined as auto increment. There is another column called store_seq_num that holds the sequence number for each loan done in each store. This column needs to work like the auto-increment field; however, it's value is dependent upon which store created the loan. Currently there is a UDF called fnNextStoreSeqNum that returns the next sequence number for the new loan for the given store. It does this by executing: SELECT MAX(store_seq_num)+1 AS NextId FROM trans_adv WHERE trans_adv.store_id=N; It uses the store_seq_num key and explain says Select tables optimized away. in the extra column. The INSERT statement for this table looks something like this: INSERT INTO trans_adv(store_id,store_seq_num,...) VALUES(fnNextStoreSeqNum(10),10,); The problem comes in on the replication server. Sometimes the sequence numbers do not match the master. The root cause of the problem seems to be when two loans are created in the same store at the same time (same second -- or even 2 seconds apart sometimes). The master duplicates the sequence number and the slave writes the correct sequence numbers. This seems to happen when the server is under heavy load (600+ queries per sec). I hvae a feeling it's due to the loan being created in a single transaction; therefore the sequence number for the first loan really didn't exist to any other connections until COMMIT was issued. Is there a better way to do these sequence numbers? Should the key be defined as UNIQUE? If it is defined as UNIQUE how can this key be added to the existing table that has duplicate sequence numbers? A partial create table statement is below for the trans_adv table. CREATE TABLE `trans_adv` ( `id` int(10) unsigned NOT NULL auto_increment, `store_id` int(10) unsigned NOT NULL default '0', `store_seq_num` int(10) unsigned NOT NULL default '0', PRIMARY KEY (`id`), KEY `store_key` (`store_id`), KEY `store_seq_num_key` (`company_id`,`store_id`,`store_seq_num`), ) ENGINE=InnoDB AUTO_INCREMENT=3049363 DEFAULT CHARSET=latin1 ; -- - Johnny Withers 601.209.4985 joh...@pixelated.net This message contains confidential information and is intended only for the individual named. If you are not the named addressee, you are notified that reviewing, disseminating, disclosing, copying or distributing this e-mail is strictly prohibited. Please notify the sender immediately by e-mail if you have received this e-mail by mistake and delete this e-mail from your system. E-mail transmission cannot be guaranteed to be secure or error-free as information could be intercepted, corrupted, lost, destroyed, arrive late or incomplete, or contain viruses. The sender therefore does not accept liability for any loss or damage caused by viruses or errors or omissions in the contents of this message, which arise as a result of e-mail transmission. [FriendFinder Networks, Inc., 220 Humbolt court, Sunnyvale, CA 94089, USA, FriendFinder.com -- - Johnny Withers 601.209.4985 joh...@pixelated.net
UDF - Sequence Numbers
I have two servers, both running 5.0.77-log, one is setup as a master, the other as a replication slave. The database contains a table that holds records of loans for financial lending stores. This table has an ID column this is defined as auto increment. There is another column called store_seq_num that holds the sequence number for each loan done in each store. This column needs to work like the auto-increment field; however, it's value is dependent upon which store created the loan. Currently there is a UDF called fnNextStoreSeqNum that returns the next sequence number for the new loan for the given store. It does this by executing: SELECT MAX(store_seq_num)+1 AS NextId FROM trans_adv WHERE trans_adv.store_id=N; It uses the store_seq_num key and explain says Select tables optimized away. in the extra column. The INSERT statement for this table looks something like this: INSERT INTO trans_adv(store_id,store_seq_num,...) VALUES(fnNextStoreSeqNum(10),10,); The problem comes in on the replication server. Sometimes the sequence numbers do not match the master. The root cause of the problem seems to be when two loans are created in the same store at the same time (same second -- or even 2 seconds apart sometimes). The master duplicates the sequence number and the slave writes the correct sequence numbers. This seems to happen when the server is under heavy load (600+ queries per sec). I hvae a feeling it's due to the loan being created in a single transaction; therefore the sequence number for the first loan really didn't exist to any other connections until COMMIT was issued. Is there a better way to do these sequence numbers? Should the key be defined as UNIQUE? If it is defined as UNIQUE how can this key be added to the existing table that has duplicate sequence numbers? A partial create table statement is below for the trans_adv table. CREATE TABLE `trans_adv` ( `id` int(10) unsigned NOT NULL auto_increment, `store_id` int(10) unsigned NOT NULL default '0', `store_seq_num` int(10) unsigned NOT NULL default '0', PRIMARY KEY (`id`), KEY `store_key` (`store_id`), KEY `store_seq_num_key` (`company_id`,`store_id`,`store_seq_num`), ) ENGINE=InnoDB AUTO_INCREMENT=3049363 DEFAULT CHARSET=latin1 ; -- - Johnny Withers 601.209.4985 joh...@pixelated.net
RE: UDF - Sequence Numbers
Others may correct me if I'm wrong, but if you want to maintain your own auto increment without duplicates like that, then you need to serialize inserts to your table. Which means either doing an explicit table lock by the process that's inserting, or using innodb with transactions in the SERIALIZABLE tx isolation mode. If I were you, I would maintain your incrementing sequence in a separate table. Prep the table: CREATE TABLE store_seq ( store_id int unsigned not null, nextid int unsigned not null ); insert into store_seq ( 1, 1 ); Get next id: update store_seq set nextid=LAST_INSERT_ID(nextid+1) where store_id=1; select LAST_INSERT_ID(); This will give you the next id in an atomic way, and avoid replication problems. It means your insert will take two queries, but that's a small price to pay for correctness. Regards, Gavin Towey -Original Message- From: Johnny Withers [mailto:joh...@pixelated.net] Sent: Monday, March 08, 2010 1:31 PM To: MySQL General List Subject: UDF - Sequence Numbers I have two servers, both running 5.0.77-log, one is setup as a master, the other as a replication slave. The database contains a table that holds records of loans for financial lending stores. This table has an ID column this is defined as auto increment. There is another column called store_seq_num that holds the sequence number for each loan done in each store. This column needs to work like the auto-increment field; however, it's value is dependent upon which store created the loan. Currently there is a UDF called fnNextStoreSeqNum that returns the next sequence number for the new loan for the given store. It does this by executing: SELECT MAX(store_seq_num)+1 AS NextId FROM trans_adv WHERE trans_adv.store_id=N; It uses the store_seq_num key and explain says Select tables optimized away. in the extra column. The INSERT statement for this table looks something like this: INSERT INTO trans_adv(store_id,store_seq_num,...) VALUES(fnNextStoreSeqNum(10),10,); The problem comes in on the replication server. Sometimes the sequence numbers do not match the master. The root cause of the problem seems to be when two loans are created in the same store at the same time (same second -- or even 2 seconds apart sometimes). The master duplicates the sequence number and the slave writes the correct sequence numbers. This seems to happen when the server is under heavy load (600+ queries per sec). I hvae a feeling it's due to the loan being created in a single transaction; therefore the sequence number for the first loan really didn't exist to any other connections until COMMIT was issued. Is there a better way to do these sequence numbers? Should the key be defined as UNIQUE? If it is defined as UNIQUE how can this key be added to the existing table that has duplicate sequence numbers? A partial create table statement is below for the trans_adv table. CREATE TABLE `trans_adv` ( `id` int(10) unsigned NOT NULL auto_increment, `store_id` int(10) unsigned NOT NULL default '0', `store_seq_num` int(10) unsigned NOT NULL default '0', PRIMARY KEY (`id`), KEY `store_key` (`store_id`), KEY `store_seq_num_key` (`company_id`,`store_id`,`store_seq_num`), ) ENGINE=InnoDB AUTO_INCREMENT=3049363 DEFAULT CHARSET=latin1 ; -- - Johnny Withers 601.209.4985 joh...@pixelated.net This message contains confidential information and is intended only for the individual named. If you are not the named addressee, you are notified that reviewing, disseminating, disclosing, copying or distributing this e-mail is strictly prohibited. Please notify the sender immediately by e-mail if you have received this e-mail by mistake and delete this e-mail from your system. E-mail transmission cannot be guaranteed to be secure or error-free as information could be intercepted, corrupted, lost, destroyed, arrive late or incomplete, or contain viruses. The sender therefore does not accept liability for any loss or damage caused by viruses or errors or omissions in the contents of this message, which arise as a result of e-mail transmission. [FriendFinder Networks, Inc., 220 Humbolt court, Sunnyvale, CA 94089, USA, FriendFinder.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
UDF Question
Hello, I am planning to write a UDF (User Defined Function) that acts like a server side client. This UDF is called by a client first. After that the UDF spwans a thread then exits. Within this spawned thread it will get work from a network socket. After that it will start executing SQL statements against some database tables in the server. What are the ramifications of the above model within the MySql Server. Thanks, -Alex
Re: JAVA UDF HOW
Thanks Everybody, I went through the project and was a little dissapointed with MySQL not supporting Java natively as yet. Let's hope we include this support in coming future. Also, there is not enough documentation for the project mentioned: http://forge.mysql.com/wiki/ProjectPage_External_Language_Stored_Procedures Thanks again !! Abhay Grewal On Thu, Jun 5, 2008 at 11:04 AM, Antony T Curtis [EMAIL PROTECTED] wrote: Hi, Check out this link for Java stored procedures with MySQL http://forge.mysql.com/wiki/ProjectPage_External_Language_Stored_Procedures It is probably what you are looking for. I have some more info on my blog at http://antbits.blogspot.com/ For more information, you can email Eric Herman [EMAIL PROTECTED] or you can email me. Currently, you will not see any Java Stored Procedure functionality in any official MySQL release but I plan to maintain a fork with this feature. Soon, I plan to be in a position to build 'preview' binaries for Linux, FreeBSD, Windows and MacOS. Regards, Antony Curtis, (Not speaking on behalf of my current or any prior employer) On 4 Jun 2008, at 01:39, Abhayjeet Singh Grewal wrote: Thanks Martin, I looked at the link, but I guess I was not able to put my question in the right way. Basically I have a Java Package and I want to call that package from MYSQL function or procedure. Any help would be much appreciated. Thanks, Abhay On Tue, Jun 3, 2008 at 9:32 PM, Martin [EMAIL PROTECTED] wrote: Abhay- It seems you are referring to the UDF Oracle specific User-defined forms Have you looked at http://download.oracle.com/docs/cd/E10391_01/doc.910/e10363/oimadm.htm#CIAEFFAD ? Martin - Original Message - From: Abhayjeet Singh Grewal [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Tuesday, June 03, 2008 9:50 AM Subject: JAVA UDF HOW Hi, I am working on oracle to mysql migration and really stuck bad with Java UDF. I did not find any documantation and the ones which I did do not work. Please help me at the earliest. I am using MySQL 5.0.51B comunity server with Java JDK1.6 on windows 2003. Regards, Abhay Grewal
Re: JAVA UDF HOW
Hi! MySQL is an open-source product. That allows anyone to contribute patches and new features from the community. In time, when the feature is mature, it may be included in some future version. Documentation on this feature is very limited because the (2) people behind the project have only pursued it during their personal free time. The code is of alpha quality and the target audience is mostly aimed at people of expert knowledge who can largely figure out how to use it without the aid of verbose documentation. Of course, if anyone is willing to contribute documentation, it would be greatly appreciated. The forge site is a wiki: Anyone may add stuff to it. Regards, Antony On 5 Jun 2008, at 05:05, Abhayjeet Singh Grewal wrote: Thanks Everybody, I went through the project and was a little dissapointed with MySQL not supporting Java natively as yet. Let's hope we include this support in coming future. Also, there is not enough documentation for the project mentioned: http://forge.mysql.com/wiki/ProjectPage_External_Language_Stored_Procedures Thanks again !! Abhay Grewal On Thu, Jun 5, 2008 at 11:04 AM, Antony T Curtis [EMAIL PROTECTED] wrote: Hi, Check out this link for Java stored procedures with MySQL http://forge.mysql.com/wiki/ProjectPage_External_Language_Stored_Procedures It is probably what you are looking for. I have some more info on my blog at http://antbits.blogspot.com/ For more information, you can email Eric Herman [EMAIL PROTECTED] or you can email me. Currently, you will not see any Java Stored Procedure functionality in any official MySQL release but I plan to maintain a fork with this feature. Soon, I plan to be in a position to build 'preview' binaries for Linux, FreeBSD, Windows and MacOS. Regards, Antony Curtis, (Not speaking on behalf of my current or any prior employer) On 4 Jun 2008, at 01:39, Abhayjeet Singh Grewal wrote: Thanks Martin, I looked at the link, but I guess I was not able to put my question in the right way. Basically I have a Java Package and I want to call that package from MYSQL function or procedure. Any help would be much appreciated. Thanks, Abhay On Tue, Jun 3, 2008 at 9:32 PM, Martin [EMAIL PROTECTED] wrote: Abhay- It seems you are referring to the UDF Oracle specific User-defined forms Have you looked at http://download.oracle.com/docs/cd/E10391_01/doc.910/e10363/oimadm.htm#CIAEFFAD ? Martin - Original Message - From: Abhayjeet Singh Grewal [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Tuesday, June 03, 2008 9:50 AM Subject: JAVA UDF HOW Hi, I am working on oracle to mysql migration and really stuck bad with Java UDF. I did not find any documantation and the ones which I did do not work. Please help me at the earliest. I am using MySQL 5.0.51B comunity server with Java JDK1.6 on windows 2003. Regards, Abhay Grewal
Re: JAVA UDF HOW
Thanks Martin, I looked at the link, but I guess I was not able to put my question in the right way. Basically I have a Java Package and I want to call that package from MYSQL function or procedure. Any help would be much appreciated. Thanks, Abhay On Tue, Jun 3, 2008 at 9:32 PM, Martin [EMAIL PROTECTED] wrote: Abhay- It seems you are referring to the UDF Oracle specific User-defined forms Have you looked at http://download.oracle.com/docs/cd/E10391_01/doc.910/e10363/oimadm.htm#CIAEFFAD ? Martin - Original Message - From: Abhayjeet Singh Grewal [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Tuesday, June 03, 2008 9:50 AM Subject: JAVA UDF HOW Hi, I am working on oracle to mysql migration and really stuck bad with Java UDF. I did not find any documantation and the ones which I did do not work. Please help me at the earliest. I am using MySQL 5.0.51B comunity server with Java JDK1.6 on windows 2003. Regards, Abhay Grewal
Re: JAVA UDF HOW
In the last episode (Jun 04), Abhayjeet Singh Grewal said: Thanks Martin, I looked at the link, but I guess I was not able to put my question in the right way. Basically I have a Java Package and I want to call that package from MYSQL function or procedure. I don't think that's possible with plain mysql. A UDF has to be a C or C++ module, according to http://dev.mysql.com/doc/refman/6.0/en/adding-udf.html (applies to all previous versions of mysql, too). There is a project to add support for other languages, though, according to http://forge.mysql.com/projects/project.php?id=239 . -- Dan Nelson [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: JAVA UDF HOW
Hi, Check out this link for Java stored procedures with MySQL http://forge.mysql.com/wiki/ProjectPage_External_Language_Stored_Procedures It is probably what you are looking for. I have some more info on my blog at http://antbits.blogspot.com/ For more information, you can email Eric Herman [EMAIL PROTECTED] or you can email me. Currently, you will not see any Java Stored Procedure functionality in any official MySQL release but I plan to maintain a fork with this feature. Soon, I plan to be in a position to build 'preview' binaries for Linux, FreeBSD, Windows and MacOS. Regards, Antony Curtis, (Not speaking on behalf of my current or any prior employer) On 4 Jun 2008, at 01:39, Abhayjeet Singh Grewal wrote: Thanks Martin, I looked at the link, but I guess I was not able to put my question in the right way. Basically I have a Java Package and I want to call that package from MYSQL function or procedure. Any help would be much appreciated. Thanks, Abhay On Tue, Jun 3, 2008 at 9:32 PM, Martin [EMAIL PROTECTED] wrote: Abhay- It seems you are referring to the UDF Oracle specific User-defined forms Have you looked at http://download.oracle.com/docs/cd/E10391_01/doc.910/e10363/oimadm.htm#CIAEFFAD ? Martin - Original Message - From: Abhayjeet Singh Grewal [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Tuesday, June 03, 2008 9:50 AM Subject: JAVA UDF HOW Hi, I am working on oracle to mysql migration and really stuck bad with Java UDF. I did not find any documantation and the ones which I did do not work. Please help me at the earliest. I am using MySQL 5.0.51B comunity server with Java JDK1.6 on windows 2003. Regards, Abhay Grewal -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
UDF output
Hello, If you use printf or some similar function inside a UDF function, where does the output goes? Or, how can I make such an output go somewhere? Thanks, Ray -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
jdbc with a UDF
I'm getting what appears to be a java reference back from values I return from a UDF. The values appear find using php, and in the mysql client. The result looks something like: [EMAIL PROTECTED] The result should be a string (some representing floats, others as integers). Any help will be appreciated. I searched on google and only found where another person had the same problem, but no solution. Dave G. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: jdbc with a UDF
Thanks for the reply. I tracked the problem down. Simple problem really, I was using an old mysql-connector-java jar. I updated that to the correct version for my database and it all worked. What version of of the JDBC driver are you using? Good question to ask. I asked myself that very question shortly after emailing the list. Thanks David Godsey Perhaps you could show us the Java code you are using to invoke this UDF? What are you expecting to get back form it? a result set? a return code from a function? What version of of the JDBC driver are you using? - michael On 6/5/07, Dave G [EMAIL PROTECTED] wrote: I'm getting what appears to be a java reference back from values I return from a UDF. The values appear find using php, and in the mysql client. The result looks something like: [EMAIL PROTECTED] The result should be a string (some representing floats, others as integers). Any help will be appreciated. I searched on google and only found where another person had the same problem, but no solution. Dave G. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- - michael dykman - [EMAIL PROTECTED] - All models are wrong. Some models are useful. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Plot UDF
Hi, Is there an aggregate UDF that would produce a simple plot (as a BLOB, e.g. in PNG format) from two columns of data? I'm pretty sure it's possible to write, but I don't want to do it if it's available somewhere. I realize that it normally should be done on the client side, in fact I've already done it, but in this particular case it would be useful to have a UDF for it. SQLyog lets you view BLOB columns as images. Thanks in advance, Sergei -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Create funtion UDF on MYSQL
Hi! can you hep? when create funtion UDF on linux then it have error ERROR 1126 Can't opened shared library (error: 0 feature disabled) i do'nt want use it ,if you knowm this problem,please help me. i hope to receive your mail thank for read it. __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com
udf return bool?
Hi, Do you know if a UDF can return a boolean value? Thanks, Ray -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: udf configuration resources
Your udf is executing in a threaded environment so you have the option of creating a datastrucutre to store options in that will persist across udf executions. However since you won't be deallocating this structure ever it's essentially leaked memory. What are you doing with udfs that you want to store configuration for? I have a feeling udfs are not he right solution. On 7/19/06, Yong Lee [EMAIL PROTECTED] wrote: Hi all, I hope I have hit the right group for this question. I would like to create a UDF that is configurable at run time. Similar to how mysql can use variables defined in the /etc/my.cnf file, I'd like my UDF to make use of configuration parameters that can be set at run time. I'm wondering if this is possible and what strategy to take to implement this, ie: a mechanism to have mysql or the UDF read something once and then have the UDF able to refer back to it everytime it is run. I know I could read a file in the _init function, but this seems very wasteful to read a file every time the function is being used. Any thoughts on this would be appreciated. thanks, Yong. -- Eric Bergen [EMAIL PROTECTED] http://www.ebergen.net -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
udf configuration
Hi all, I hope I have hit the right group for this question. I would like to create a UDF that is configurable at run time. Similar to how mysql can use variables defined in the /etc/my.cnf file, I'd like my UDF to make use of configuration parameters that can be set at run time. I'm wondering if this is possible and what strategy to take to implement this, ie: a mechanism to have mysql or the UDF read something once and then have the UDF able to refer back to it everytime it is run. I know I could read a file in the _init function, but this seems very wasteful to read a file every time the function is being used. Any thoughts on this would be appreciated. thanks, Yong.
udf configuration resources
Hi all, I hope I have hit the right group for this question. I would like to create a UDF that is configurable at run time. Similar to how mysql can use variables defined in the /etc/my.cnf file, I'd like my UDF to make use of configuration parameters that can be set at run time. I'm wondering if this is possible and what strategy to take to implement this, ie: a mechanism to have mysql or the UDF read something once and then have the UDF able to refer back to it everytime it is run. I know I could read a file in the _init function, but this seems very wasteful to read a file every time the function is being used. Any thoughts on this would be appreciated. thanks, Yong.
RE: udf configuration
Hello, If you have not already done so, you may also try the MySQL forum for UDFs at: http://forums.mysql.com/list.php?118 Thanks, Jimmy Guerrero Sr Product Manager MySQL, Inc -Original Message- From: Yong Lee [mailto:[EMAIL PROTECTED] Sent: Wednesday, July 19, 2006 6:10 PM To: mysql@lists.mysql.com Subject: udf configuration Hi all, I hope I have hit the right group for this question. I would like to create a UDF that is configurable at run time. Similar to how mysql can use variables defined in the /etc/my.cnf file, I'd like my UDF to make use of configuration parameters that can be set at run time. I'm wondering if this is possible and what strategy to take to implement this, ie: a mechanism to have mysql or the UDF read something once and then have the UDF able to refer back to it everytime it is run. I know I could read a file in the _init function, but this seems very wasteful to read a file every time the function is being used. Any thoughts on this would be appreciated. thanks, Yong. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Prepared Statement, Select and Calls in C/C++ UDF
Hi, I want to make C User Defined Function where to do some data processing using the parameters and the data in the database and to return some BIGINT result after that. My questions are: Q1. Is it possible to use Prepared Statement, Select and Calls in C/C++ UDF? Q2. If, Yes, which MySQL version support such functionallity? Q3. The big problem is how to use the data in the Database - how to get MYSQL structure with which to use Prepared Statements, Selects and Calls? Thank you in advance. -- Best Regards, Miroslav Nachev -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
UDF help, convert BLOB to BIGINT
I'm in the process of writing my first UDF and would appreciate some help.
I am pulling data from a table like:
SELECT payload_time,
SUBSTR(BINARY(frame_data),
FLOOR(foffset/8)+1,
CEIL((flength + (foffset %8 ))/8))
FROM RawMajorFrames
WHERE raw_major_frame_id=rfid
INTO ptime,fdata;
frame_data is type BLOB. It is raw data collected. The substr will get
the specific bytes I'm interested in. What I need to do, is if the data
is = 8bytes, convert it to a BIGINT, so I can do some masking on the
data.
So I am writing a UDF to do the job, but I am apparently unfamiliar with
the Mysql data types and how I can convert them.
In a procedure.
DECLARE fdata_bigint BIGINT UNSIGNED;
SELECT BlobToInt(binary(fdata)) INTO fdata_bigint;
my_bool BlobToInt_init( UDF_INIT* initid, UDF_ARGS* args, char* message )
{
if (args-arg_count != 1)
{
strcpy(message,Wrong arguments to BlobToInt; should be
BlobToInt(blob));
return 1;
}
return 0;
}
longlong BlobToInt( UDF_INIT* initid, UDF_ARGS* args, char* is_null, char
*error )
{
longlong tmplong = *((longlong*)args-args[0]);
return tmplong;
}
I guess I was just assuming I could just cast the data as the type I want,
but that doesn't seem to work. The function returns a 0.
Any help would be appreciated.
Accomplishing the impossible means only that the boss will add it to your
regular duties.
David Godsey
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: UDF help, convert BLOB to BIGINT
David Godsey [EMAIL PROTECTED] wrote on 03/22/2006 01:21:07 PM:
I'm in the process of writing my first UDF and would appreciate some
help.
I am pulling data from a table like:
SELECT payload_time,
SUBSTR(BINARY(frame_data),
FLOOR(foffset/8)+1,
CEIL((flength + (foffset %8 ))/8))
FROM RawMajorFrames
WHERE raw_major_frame_id=rfid
INTO ptime,fdata;
frame_data is type BLOB. It is raw data collected. The substr will get
the specific bytes I'm interested in. What I need to do, is if the data
is = 8bytes, convert it to a BIGINT, so I can do some masking on the
data.
So I am writing a UDF to do the job, but I am apparently unfamiliar with
the Mysql data types and how I can convert them.
In a procedure.
DECLARE fdata_bigint BIGINT UNSIGNED;
SELECT BlobToInt(binary(fdata)) INTO fdata_bigint;
my_bool BlobToInt_init( UDF_INIT* initid, UDF_ARGS* args, char* message
)
{
if (args-arg_count != 1)
{
strcpy(message,Wrong arguments to BlobToInt; should be
BlobToInt(blob));
return 1;
}
return 0;
}
longlong BlobToInt( UDF_INIT* initid, UDF_ARGS* args, char* is_null,
char
*error )
{
longlong tmplong = *((longlong*)args-args[0]);
return tmplong;
}
I guess I was just assuming I could just cast the data as the type I
want,
but that doesn't seem to work. The function returns a 0.
Any help would be appreciated.
Accomplishing the impossible means only that the boss will add it to
your
regular duties.
David Godsey
C is not my strongest language but aren't you getting a null-terminated
string as args[0] ? What if you allocated a longlong and byte-copied the
bytes from args[0] into your longlong? Maybe something like...
longlong BlobToInt( UDF_INIT* initid, UDF_ARGS* args, char* is_null, char
*error )
{
char idx, *cArg
longlong tmplong, *plonglong ;
plonglong = tmplong;
cArg = (args-args[0]);
for(idx=0;idx8;idx++) {
plonglong[idx]=cArg[idx];
}
return tmplong;
}
Again, I strongly stress that C/C++ is not my best language (I don't use
it nearly enough) but I think you can see what I was trying to do. Other
options: memcpy(), strcpy(), strncpy() etc
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
Re: UDF help, convert BLOB to BIGINT
Just figured it out without a UDF(not documented anywhere that I found).
SELECT conv(hex(fdata),16,10) INTO fdata_bigint;
So a double conversion seems to work for me.
You solution looks like it will work, but since I was able to get it to
work without a UDF, I'm not going to test it out. Thanks.
David Godsey
David Godsey [EMAIL PROTECTED] wrote on 03/22/2006 01:21:07 PM:
I'm in the process of writing my first UDF and would appreciate some
help.
I am pulling data from a table like:
SELECT payload_time,
SUBSTR(BINARY(frame_data),
FLOOR(foffset/8)+1,
CEIL((flength + (foffset %8 ))/8))
FROM RawMajorFrames
WHERE raw_major_frame_id=rfid
INTO ptime,fdata;
frame_data is type BLOB. It is raw data collected. The substr will get
the specific bytes I'm interested in. What I need to do, is if the data
is = 8bytes, convert it to a BIGINT, so I can do some masking on the
data.
So I am writing a UDF to do the job, but I am apparently unfamiliar with
the Mysql data types and how I can convert them.
In a procedure.
DECLARE fdata_bigint BIGINT UNSIGNED;
SELECT BlobToInt(binary(fdata)) INTO fdata_bigint;
my_bool BlobToInt_init( UDF_INIT* initid, UDF_ARGS* args, char* message
)
{
if (args-arg_count != 1)
{
strcpy(message,Wrong arguments to BlobToInt; should be
BlobToInt(blob));
return 1;
}
return 0;
}
longlong BlobToInt( UDF_INIT* initid, UDF_ARGS* args, char* is_null,
char
*error )
{
longlong tmplong = *((longlong*)args-args[0]);
return tmplong;
}
I guess I was just assuming I could just cast the data as the type I
want,
but that doesn't seem to work. The function returns a 0.
Any help would be appreciated.
Accomplishing the impossible means only that the boss will add it to
your
regular duties.
David Godsey
C is not my strongest language but aren't you getting a null-terminated
string as args[0] ? What if you allocated a longlong and byte-copied the
bytes from args[0] into your longlong? Maybe something like...
longlong BlobToInt( UDF_INIT* initid, UDF_ARGS* args, char* is_null, char
*error )
{
char idx, *cArg
longlong tmplong, *plonglong ;
plonglong = tmplong;
cArg = (args-args[0]);
for(idx=0;idx8;idx++) {
plonglong[idx]=cArg[idx];
}
return tmplong;
}
Again, I strongly stress that C/C++ is not my best language (I don't use
it nearly enough) but I think you can see what I was trying to do. Other
options: memcpy(), strcpy(), strncpy() etc
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
Accomplishing the impossible means only that the boss will add it to your
regular duties.
David Godsey
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: UDF Request AGGLOM()
Hi Dan, Dan Bolser wrote: Who can I prod about setting up a UDF repo at MySQL. I think 'they' should do this ;) Yep it's an existing idea, a very good one, and it's on the todo. Putting such an infrastructure into place will take some time though. I can imagine it isn't trivial to set up. Would a special forum for this purpose perhaps be a good intermediate solution? Routines could be posted there, it's searchable... Forum = list? I think it would be a good start, if only to discuss things like this :) forums.mysql.com Regards, Arjen. -- Arjen Lentz, Community Relations Manager, MySQL AB Program Chair, MySQL Users Conference MySQL Users Conference 2006 (Santa Clara CA, 24-27 April) http://www.mysqluc.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: UDF Request AGGLOM()
Hi Dan, Dan Bolser wrote: Who can I prod about setting up a UDF repo at MySQL. I think 'they' should do this ;) Yep it's an existing idea, a very good one, and it's on the todo. Putting such an infrastructure into place will take some time though. I can imagine it isn't trivial to set up. Would a special forum for this purpose perhaps be a good intermediate solution? Routines could be posted there, it's searchable... Forum = list? I think it would be a good start, if only to discuss things like this :) forums.mysql.com Thanks. Don't know if it is just me, but I can't find anything to do with UDF's on that forum. I was actually thinking that you meant an archived mailing list, but a BBS is OK. Regards, Arjen. -- Arjen Lentz, Community Relations Manager, MySQL AB Program Chair, MySQL Users Conference MySQL Users Conference 2006 (Santa Clara CA, 24-27 April) http://www.mysqluc.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: UDF
Hello. My doubt is: Is it the only way to have UDF server-side in MySQL(4.1.x)? Do I need to learn C/C++ to have a UDF? In my opinion - yes, because UFDs usually uses includes (say, mysql.h) which are written in C/C++. Speaking in general - UDF for MySQL is just a shared library, so perhaps, can be written in another language. But be a building framework (like include files) can not exists for it. Ronan Lucio wrote: Hello, I�m using MySQL-4.1.7 and I need to create a UDF. For all I have understood, I need to create it in C/C++ sintaxe. My doubt is: Is it the only way to have UDF server-side in MySQL(4.1.x)? Do I need to learn C/C++ to have a UDF? Thanks, Ronan -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
UDF
Hello, I´m using MySQL-4.1.7 and I need to create a UDF. For all I have understood, I need to create it in C/C++ sintaxe. My doubt is: Is it the only way to have UDF server-side in MySQL(4.1.x)? Do I need to learn C/C++ to have a UDF? Thanks, Ronan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
rcs udf for MySQL
Anyone out there heard of a rcs udf (or similar) for MySQL? Google is not being nice to me, too much noise in the results. -- -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- - - - Jason Pyeron PD Inc. http://www.pdinc.us - - Partner Sr. Manager 7 West 24th Street #100 - - +1 (443) 921-0381 Baltimore, Maryland 21218 - - - -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- This message is for the designated recipient only and may contain privileged, proprietary, or otherwise private information. If you have received it in error, purge the message from your system and notify the sender immediately. Any other use of the email by you is prohibited. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: rcs udf for MySQL
Jason Pyeron [EMAIL PROTECTED] wrote on 08/16/2005 01:01:03 PM: Anyone out there heard of a rcs udf (or similar) for MySQL? Google is not being nice to me, too much noise in the results. -- rcs=? Reaction Conrol System? Rear Cannon Sights? udf = User Defined Function? (just making sure) What is it, what does it do and where did you hear of such a thing? Maybe we could help if we had more information. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: rcs udf for MySQL
sorry, RCS= Revision Control Sytems it is what backends things like cvs. and yes user defined functions http://www.cs.purdue.edu/homes/trinkle/RCS/ On Tue, 16 Aug 2005 [EMAIL PROTECTED] wrote: Jason Pyeron [EMAIL PROTECTED] wrote on 08/16/2005 01:01:03 PM: Anyone out there heard of a rcs udf (or similar) for MySQL? Google is not being nice to me, too much noise in the results. -- rcs=? Reaction Conrol System? Rear Cannon Sights? udf = User Defined Function? (just making sure) What is it, what does it do and where did you hear of such a thing? Maybe we could help if we had more information. Shawn Green Database Administrator Unimin Corporation - Spruce Pine -- -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- - - - Jason Pyeron PD Inc. http://www.pdinc.us - - Partner Sr. Manager 7 West 24th Street #100 - - +1 (443) 921-0381 Baltimore, Maryland 21218 - - - -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- This message is for the designated recipient only and may contain privileged, proprietary, or otherwise private information. If you have received it in error, purge the message from your system and notify the sender immediately. Any other use of the email by you is prohibited. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: rcs udf for MySQL
After following your link, I am sure that some RCS systems that use MySQL as a backend *may* use UDFs as part of their persistence logic, but those would be specific to the RCS product you are curious about. There aren't any generic UDFs that will apply to all verson contol front-ends. Does the one in the link use any UDFs? I couldn't tell from what I read and to be perfectly honest, I couldn't figure out from the reading that RCS actually used MySQL at all. Is this what you wanted to know or have I still missed something vital to the question? It's been one of those days for me. Shawn Green Database Administrator Unimin Corporation - Spruce Pine Jason Pyeron [EMAIL PROTECTED] wrote on 08/16/2005 02:15:34 PM: sorry, RCS= Revision Control Sytems it is what backends things like cvs. and yes user defined functions http://www.cs.purdue.edu/homes/trinkle/RCS/ On Tue, 16 Aug 2005 [EMAIL PROTECTED] wrote: Jason Pyeron [EMAIL PROTECTED] wrote on 08/16/2005 01:01:03 PM: Anyone out there heard of a rcs udf (or similar) for MySQL? Google is not being nice to me, too much noise in the results. -- rcs=? Reaction Conrol System? Rear Cannon Sights? udf = User Defined Function? (just making sure) What is it, what does it do and where did you hear of such a thing? Maybe we could help if we had more information. Shawn Green Database Administrator Unimin Corporation - Spruce Pine -- -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- - - - Jason Pyeron PD Inc. http://www.pdinc.us - - Partner Sr. Manager 7 West 24th Street #100 - - +1 (443) 921-0381 Baltimore, Maryland 21218 - - - -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- This message is for the designated recipient only and may contain privileged, proprietary, or otherwise private information. If you have received it in error, purge the message from your system and notify the sender immediately. Any other use of the email by you is prohibited. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
[sorta off topic] Re: rcs udf for MySQL
On Tue, 16 Aug 2005 [EMAIL PROTECTED] wrote: After following your link, I am sure that some RCS systems that use MySQL as a backend *may* use UDFs as part of their persistence logic, but those would be specific to the RCS product you are curious about. There aren't any generic UDFs that will apply to all verson contol front-ends. Does the one in the link use any UDFs? I couldn't tell from what I read and to be perfectly honest, I couldn't figure out from the reading that RCS actually used MySQL at all. Is this what you wanted to know or have I still missed something vital to the question? It's been one of those days for me. Jason Pyeron [EMAIL PROTECTED] wrote on 08/16/2005 02:15:34 PM: http://www.cs.purdue.edu/homes/trinkle/RCS/ RCS does not use MySQL or any other SQL engine. I am looking to use a SQL backend for RCS. RCS is a specific tool unlike source control management (SCM), it is a text file database. There are no SQL based RCS implimentations that I know of, that was why I asked in the first place. A RCS file is comprised of: meta data bout the state of the file current source 0..N reverse deltas from current source meta data about the various revisions represented, including who, when, why. ex: head1.2; access; symbols; locks; strict; comment @# @; 1.2 date2005.07.31.20.05.02;author jpyeron; state Exp; branches; next1.1; 1.1 date2005.07.07.05.20.31;author jpyeron; state Exp; branches; next; desc @@ 1.2 log @my comment about this version is a BLOB TEXT delimited by the (at)s @ text @Here it the contents of my file it is many lines long line 3 line 4 line 5 line 6 line 7 line 8 line 9 then terminated by the (at) @ 1.1 log @Initial version @ text @d9 1 a9 1 line 9 original version @ -- -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- - - - Jason Pyeron PD Inc. http://www.pdinc.us - - Partner Sr. Manager 7 West 24th Street #100 - - +1 (443) 921-0381 Baltimore, Maryland 21218 - - - -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- This message is for the designated recipient only and may contain privileged, proprietary, or otherwise private information. If you have received it in error, purge the message from your system and notify the sender immediately. Any other use of the email by you is prohibited. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: [sorta off topic] Re: rcs udf for MySQL
Jason Pyeron [EMAIL PROTECTED] wrote on 08/16/2005 04:17:15 PM: On Tue, 16 Aug 2005 [EMAIL PROTECTED] wrote: After following your link, I am sure that some RCS systems that use MySQL as a backend *may* use UDFs as part of their persistence logic, but those would be specific to the RCS product you are curious about. There aren't any generic UDFs that will apply to all verson contol front-ends. Does the one in the link use any UDFs? I couldn't tell from what I read and to be perfectly honest, I couldn't figure out from the reading that RCS actually used MySQL at all. Is this what you wanted to know or have I still missed something vital to the question? It's been one of those days for me. Jason Pyeron [EMAIL PROTECTED] wrote on 08/16/2005 02:15:34 PM: http://www.cs.purdue.edu/homes/trinkle/RCS/ RCS does not use MySQL or any other SQL engine. I am looking to use a SQL backend for RCS. RCS is a specific tool unlike source control management (SCM), it is a text file database. There are no SQL based RCS implimentations that I know of, that was why I asked in the first place. A RCS file is comprised of: meta data bout the state of the file current source 0..N reverse deltas from current source meta data about the various revisions represented, including who, when, why. ex: head1.2; access; symbols; locks; strict; comment @# @; 1.2 date2005.07.31.20.05.02;author jpyeron; state Exp; branches; next1.1; 1.1 date2005.07.07.05.20.31;author jpyeron; state Exp; branches; next; desc @@ 1.2 log @my comment about this version is a BLOB TEXT delimited by the (at)s @ text @Here it the contents of my file it is many lines long line 3 line 4 line 5 line 6 line 7 line 8 line 9 then terminated by the (at) @ 1.1 log @Initial version @ text @d9 1 a9 1 line 9 original version @ Now... don't be so hasty. MS SQL has a form of RCS you can use to check in and check out stored procedures and other design elements using Visual Source Safe (VSS). VSS acutally uses extended stored procedures (the mutant cousins of UDFs) to perform the locking and unlocking of various database elements. I think this might count as one version of a database-based RCS (even though I think the deltas are stored elsewhere as text). The metadata headers you describe should be easily convertable to database fields and the actual delta data could either be stored in a blob (one blob per set of changes) or separately listed in their own table (one set of delta rows per set of changes). Doing an RCS with a database is not complex to design but implementaion may be slower than you like just due to the overhead of running a SQL query (or mulitple queries) vs. the speed of directly parsing local text files. A future optimization may be for you to use the HANDLER interface with MyISAM tables in order to quickly retrieve your delta blobs. Just something to think about. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: [sorta off topic] Re: rcs udf for MySQL
On Tue, 16 Aug 2005 [EMAIL PROTECTED] wrote: Now... don't be so hasty. MS SQL has a form of RCS you can use to check in and check out stored procedures and other design elements using Visual Source Safe (VSS). VSS acutally uses extended stored procedures (the mutant cousins of UDFs) to perform the locking and unlocking of various database elements. I think this might count as one version of a database-based RCS (even though I think the deltas are stored elsewhere as text). I was looking for RCS, I am looking to retool our CVS repository. The metadata headers you describe should be easily convertable to database fields and the actual delta data could either be stored in a blob (one blob per set of changes) or separately listed in their own table (one set of delta rows per set of changes). Started on this a few hours ago. Paused to look at Subversion, they use Berkley DB. Doing an RCS with a database is not complex to design but implementaion may be slower than you like just due to the overhead of running a SQL query (or mulitple queries) vs. the speed of directly parsing local text files. It would be a lot faster, since you could do the delta row set collation faster, if stored properly. See page 8 (section 3.2) of RCS A System for Version Control by Walter F. Tichy 1991. A future optimization may be for you to use the HANDLER interface with MyISAM tables in order to quickly retrieve your delta blobs. Just something to think about. Using Innodb, for transactions. -jason -- -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- - - - Jason Pyeron PD Inc. http://www.pdinc.us - - Partner Sr. Manager 7 West 24th Street #100 - - +1 (443) 921-0381 Baltimore, Maryland 21218 - - - -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- This message is for the designated recipient only and may contain privileged, proprietary, or otherwise private information. If you have received it in error, purge the message from your system and notify the sender immediately. Any other use of the email by you is prohibited. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Stored function problems (Was: UDF failure)
Hi,
Still having difficulty with stored functions. I downloaded and installed
MySQL 5.0.7 and
I *still* cannot get stored functions to work.
I used the example for hello from section 19.2.1 of the online documentation
for MySQL and the example code doesnt work for me either.
Below are the versions from, respectively, the MySQL cli, the MySQL server
and my Linux and following that is a transcript from the mysql cli trying to
create the hello function.
mysql Ver 14.11 Distrib 5.0.7-beta, for pc-linux-gnu (i686) using readline
4.3
mysqld Ver 5.0.7-beta-standard for pc-linux-gnu on i686 (MySQL Community
Edition - Standard (GPL)
Linux colinux 2.6.10-co-0.6.2 #5 Sat Feb 5 10:19:16 IST 2005 i686 i686 i386
GNU/Linux
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 30 to server version: 5.0.7-beta-standard
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql DELIMITER //
mysql
mysql CREATE FUNCTION hello (s CHAR(20)) RETURNS CHAR(50)
- RETURN CONCAT('Hello, ',s,'!');
-
- DELIMITER ;
- //
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual
that corresponds to your MySQL server version for the right syntax to use
near 'RETURN CONCAT('Hello, ',s,'!');
DELIMITER' at line 2
--
Nic Stevens - [EMAIL PROTECTED]
Re: Stored function problems (Was: UDF failure)
At 11:41 -0700 7/7/05, Nic Stevens wrote:
Hi,
Still having difficulty with stored functions. I downloaded and installed
MySQL 5.0.7 and
I *still* cannot get stored functions to work.
I used the example for hello from section 19.2.1 of the online documentation
for MySQL and the example code doesnt work for me either.
Below are the versions from, respectively, the MySQL cli, the MySQL server
and my Linux and following that is a transcript from the mysql cli trying to
create the hello function.
mysql Ver 14.11 Distrib 5.0.7-beta, for pc-linux-gnu (i686) using readline
4.3
mysqld Ver 5.0.7-beta-standard for pc-linux-gnu on i686 (MySQL Community
Edition - Standard (GPL)
Linux colinux 2.6.10-co-0.6.2 #5 Sat Feb 5 10:19:16 IST 2005 i686 i686 i386
GNU/Linux
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 30 to server version: 5.0.7-beta-standard
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql DELIMITER //
mysql
mysql CREATE FUNCTION hello (s CHAR(20)) RETURNS CHAR(50)
- RETURN CONCAT('Hello, ',s,'!');
-
- DELIMITER ;
- //
The example in the manual has the delimiter ; line following the //
line, not preceding it.
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual
that corresponds to your MySQL server version for the right syntax to use
near 'RETURN CONCAT('Hello, ',s,'!');
DELIMITER' at line 2
--
Nic Stevens - [EMAIL PROTECTED]
--
Paul DuBois, MySQL Documentation Team
Madison, Wisconsin, USA
MySQL AB, www.mysql.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Stored function problems (Was: UDF failure)
Nic,
At the end of the func, your sproc delimiter // needs to come before
restoration of the semi-colon as delimiter, thus:
DELIMITER //
CREATE FUNCTION hello (s CHAR(20)) RETURNS CHAR(50)
RETURN CONCAT('Hello, ',s,'!');
//
DELIMITER ;
PB
-
Nic Stevens wrote:
Hi,
Still having difficulty with stored functions. I downloaded and installed
MySQL 5.0.7 and
I *still* cannot get stored functions to work.
I used the example for hello from section 19.2.1 of the online documentation
for MySQL and the example code doesnt work for me either.
Below are the versions from, respectively, the MySQL cli, the MySQL server
and my Linux and following that is a transcript from the mysql cli trying to
create the hello function.
mysql Ver 14.11 Distrib 5.0.7-beta, for pc-linux-gnu (i686) using readline
4.3
mysqld Ver 5.0.7-beta-standard for pc-linux-gnu on i686 (MySQL Community
Edition - Standard (GPL)
Linux colinux 2.6.10-co-0.6.2 #5 Sat Feb 5 10:19:16 IST 2005 i686 i686 i386
GNU/Linux
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 30 to server version: 5.0.7-beta-standard
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql DELIMITER //
mysql
mysql CREATE FUNCTION hello (s CHAR(20)) RETURNS CHAR(50)
- RETURN CONCAT('Hello, ',s,'!');
-
- DELIMITER ;
- //
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual
that corresponds to your MySQL server version for the right syntax to use
near 'RETURN CONCAT('Hello, ',s,'!');
DELIMITER' at line 2
No virus found in this incoming message.
Checked by AVG Anti-Virus.
Version: 7.0.323 / Virus Database: 267.8.10/43 - Release Date: 7/6/2005
No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.0.323 / Virus Database: 267.8.10/43 - Release Date: 7/6/2005
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
UDF failure
Hello,
I'm trying to build a UDF for MySQL 4.1.12. I'm running on Linux
(FedoraFC1). My UDF function seemed to blow up the server so I went to the
source and tried to build udf_example.cc.
Using the precompiled binaries for Linux and using the source distribution
for the same version (4.1.12) I was unable to build udf_example.cc as a
shared object (I followed the instructions in the source -- they didnt work
so I used what seemed to make the most sense for building my object). When
building the shared object the compiler spits out a warning from
asm/atomic.h (#warning Using kernel header in userland program. BAD!)
I'm using gcc version 3.3.2 20031022 (Red Hat Linux 3.3.2-1) on a Fedore
Core 1 machine. I built the shared object with gcc -I/usr/include/mysql
-shared -o udf_example.so udf_example.cc
When attempting to load a new function with the mysql cli client the client
complains that the server has gone away and reconnects with connection id of
1. Nothing is listed in mysql.funcs either.
There needs to be better documentation of the process for building UDF's.
The code I need to run can be run nicely by MySQL but I can't declare a
stored function as documented. The stored function is fairly simple:
DELIMITER //
CREATE FUNCTION distance(z1 VARCHAR(10), z2 VARCHAR(10)) RETURNS REAL
BEGIN
DECLARE x REAL;
DECLARE y REAL;
DECALRE miles REAL;
SET x = 69.1*(lat - lat2);
SET y = 69.1*(lon1 - lon2) * COS(lon1*57.3);
SET miles = SQRT(x*x+y*y);
RETURN miles;
END
//
The mysql client spits back :
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual
that corresponds to your MySQL server version for the right syntax to use
near '(z1 VARCHAR(10), z2 VARCHAR(10)) RETURNS REAL
Using the example given in the documentation I get:
mysql CREATE FUNCTION hello (s CHAR(20)) RETURNS CHAR(50)
- RETURN CONCAT('Hello, ',s,'!');
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual
that corresponds to your MySQL server version for the right syntax to use
near '(s CHAR(20)) RETURNS CHAR(50)
RETURN CONCAT('Hello, ',s,'!')' at line 1
DELIMITER ;
//
Can someone elucidate the mystery of either stored functions or UDF's?
Thanks, in advance,
Nic
--
Nic Stevens - [EMAIL PROTECTED]
Re: UDF failure
Hello.
When attempting to load a new function with the mysql cli client the
client
complains that the server has gone away and reconnects with connection
Check MySQL error log, server could die while loading the UDF.
Stored procedures appeared only in 5 version. This works fine for me on 5.0.7.
mysql create function hello (s char(20)) returns char(50) RETURN
CONCAT('Hello, ',s,'!');//
Query OK, 0 rows affected (0.00 sec)
Nic Stevens [EMAIL PROTECTED] wrote:
Hello,
I'm trying to build a UDF for MySQL 4.1.12. I'm running on Linux
(FedoraFC1). My UDF function seemed to blow up the server so I went to
the
source and tried to build udf_example.cc.
Using the precompiled binaries for Linux and using the source
distribution
for the same version (4.1.12) I was unable to build udf_example.cc as a
shared object (I followed the instructions in the source -- they didnt
work
so I used what seemed to make the most sense for building my object).
When
building the shared object the compiler spits out a warning from
asm/atomic.h (#warning Using kernel header in userland program. BAD!)
I'm using gcc version 3.3.2 20031022 (Red Hat Linux 3.3.2-1) on a
Fedore
Core 1 machine. I built the shared object with gcc -I/usr/include/mysql
-shared -o udf_example.so udf_example.cc
When attempting to load a new function with the mysql cli client the
client
complains that the server has gone away and reconnects with connection
id of
1. Nothing is listed in mysql.funcs either.
There needs to be better documentation of the process for building
UDF's.
The code I need to run can be run nicely by MySQL but I can't declare a
stored function as documented. The stored function is fairly simple:
DELIMITER //
CREATE FUNCTION distance(z1 VARCHAR(10), z2 VARCHAR(10)) RETURNS REAL
BEGIN
DECLARE x REAL;
DECLARE y REAL;
DECALRE miles REAL;
SET x = 69.1*(lat - lat2);
SET y = 69.1*(lon1 - lon2) * COS(lon1*57.3);
SET miles = SQRT(x*x+y*y);
RETURN miles;
END
//
The mysql client spits back :
ERROR 1064 (42000): You have an error in your SQL syntax; check the
manual
that corresponds to your MySQL server version for the right syntax to
use
near '(z1 VARCHAR(10), z2 VARCHAR(10)) RETURNS REAL
Using the example given in the documentation I get:
mysql CREATE FUNCTION hello (s CHAR(20)) RETURNS CHAR(50)
- RETURN CONCAT('Hello, ',s,'!');
ERROR 1064 (42000): You have an error in your SQL syntax; check the
manual
that corresponds to your MySQL server version for the right syntax to
use
near '(s CHAR(20)) RETURNS CHAR(50)
RETURN CONCAT('Hello, ',s,'!')' at line 1
DELIMITER ;
//
Can someone elucidate the mystery of either stored functions or UDF's?
--
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.NET http://www.ensita.net/
__ ___ ___ __
/ |/ /_ __/ __/ __ \/ /Gleb Paharenko
/ /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED]
/_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET
___/ www.mysql.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: UDF failure
Looks like you're confusing Prepared Statements with Functions/Stored
Procedures. You don't compile a function, and you're using 4.1, you
need Mysql 5. (specifically 5.0.3 I think, you'd have to look that
up, but you would want 5.0.7 anyway. Maybe I have that confused with
triggers, cant remember.)
Prepared Statements are C code using the C API. You have SQL Language
code, that looks correct, but you're implementing it incorrectly.
(with the wrong version) Functions are not compiled, like you are
doing with gcc.
Prepared Statements:
http://dev.mysql.com/doc/mysql/en/c-api-prepared-statements.html
Functions:
http://dev.mysql.com/doc/mysql/en/stored-procedures.html
Hope that helps.
Greg
On 7/6/05, Gleb Paharenko [EMAIL PROTECTED] wrote:
Hello.
When attempting to load a new function with the mysql cli client the
client
complains that the server has gone away and reconnects with connection
Check MySQL error log, server could die while loading the UDF.
Stored procedures appeared only in 5 version. This works fine for me on 5.0.7.
mysql create function hello (s char(20)) returns char(50) RETURN
CONCAT('Hello, ',s,'!');//
Query OK, 0 rows affected (0.00 sec)
Nic Stevens [EMAIL PROTECTED] wrote:
Hello,
I'm trying to build a UDF for MySQL 4.1.12. I'm running on Linux
(FedoraFC1). My UDF function seemed to blow up the server so I went to
the
source and tried to build udf_example.cc.
Using the precompiled binaries for Linux and using the source
distribution
for the same version (4.1.12) I was unable to build udf_example.cc as a
shared object (I followed the instructions in the source -- they didnt
work
so I used what seemed to make the most sense for building my object).
When
building the shared object the compiler spits out a warning from
asm/atomic.h (#warning Using kernel header in userland program. BAD!)
I'm using gcc version 3.3.2 20031022 (Red Hat Linux 3.3.2-1) on a
Fedore
Core 1 machine. I built the shared object with gcc -I/usr/include/mysql
-shared -o udf_example.so udf_example.cc
When attempting to load a new function with the mysql cli client the
client
complains that the server has gone away and reconnects with connection
id of
1. Nothing is listed in mysql.funcs either.
There needs to be better documentation of the process for building
UDF's.
The code I need to run can be run nicely by MySQL but I can't declare a
stored function as documented. The stored function is fairly simple:
DELIMITER //
CREATE FUNCTION distance(z1 VARCHAR(10), z2 VARCHAR(10)) RETURNS REAL
BEGIN
DECLARE x REAL;
DECLARE y REAL;
DECALRE miles REAL;
SET x = 69.1*(lat - lat2);
SET y = 69.1*(lon1 - lon2) * COS(lon1*57.3);
SET miles = SQRT(x*x+y*y);
RETURN miles;
END
//
The mysql client spits back :
ERROR 1064 (42000): You have an error in your SQL syntax; check the
manual
that corresponds to your MySQL server version for the right syntax to
use
near '(z1 VARCHAR(10), z2 VARCHAR(10)) RETURNS REAL
Using the example given in the documentation I get:
mysql CREATE FUNCTION hello (s CHAR(20)) RETURNS CHAR(50)
- RETURN CONCAT('Hello, ',s,'!');
ERROR 1064 (42000): You have an error in your SQL syntax; check the
manual
that corresponds to your MySQL server version for the right syntax to
use
near '(s CHAR(20)) RETURNS CHAR(50)
RETURN CONCAT('Hello, ',s,'!')' at line 1
DELIMITER ;
//
Can someone elucidate the mystery of either stored functions or UDF's?
--
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.NET http://www.ensita.net/
__ ___ ___ __
/ |/ /_ __/ __/ __ \/ /Gleb Paharenko
/ /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED]
/_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET
___/ www.mysql.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
--
Greg Fischer
1st Byte Solutions
http://www.1stbyte.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: UDF Request AGGLOM()
On Mon, 11 Apr 2005, Arjen Lentz wrote: Hi Dan, On Sat, 2005-04-09 at 02:59, Dan Bolser wrote: Who can I prod about setting up a UDF repo at MySQL. I think 'they' should do this ;) Yep it's an existing idea, a very good one, and it's on the todo. Putting such an infrastructure into place will take some time though. I can imagine it isn't trivial to set up. Would a special forum for this purpose perhaps be a good intermediate solution? Routines could be posted there, it's searchable... Forum = list? I think it would be a good start, if only to discuss things like this :) All the best, Dan. Regards, Arjen. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: UDF request?
Dan Bolser [EMAIL PROTECTED] wrote on 04/08/2005 06:45:42 PM: On Fri, 8 Apr 2005 [EMAIL PROTECTED] wrote: Dan Bolser [EMAIL PROTECTED] wrote on 04/08/2005 12:41:35 PM: On Thu, 7 Apr 2005, Sean Nolan wrote: I think you'll find you can do what you want with a cross join. A cross join will join every row from the first table with every row from the second table. It will not randomly do this, so you'd have to be creative in randomly selecting rows in the WHERE clause. Here's how you could do this with your data, pardon the highly original and very scientific randomization, but it is just an example :-) Perhaps I don't understand your randomization (because I really don't understand it ;), but I don't think a CROSS join does the trick, because I want to randomly pick the same rows from either side of the join that I would have gotten with an INNER JOIN using the 1:1 primary key between the two tables. This is analagous to sampling without replacement. If I do a cross join and then just restrict the number of rows returned (hey! I just worked out what you are doing below) I don't necessarily get the same 'marginals' (or to randomly pick the same rows from either side of the join) . This is analagous to sampling with replacement. Do you see what I mean? SELECT Dept, Gender, AVG(Spending) FROM table_one a CROSS JOIN table_two b WHERE (a.Person * b.Person) % 4 = 3 GROUP BY Dept, Gender; Sean - Original Message -- Hi, I have a request for a UDF that I would find really useful. I am duplicating this question on the community list as well as the MySQL list, because I am not sure where best to make this kind of request (see the previous post http://lists.mysql.com/community/97). I think the following syntax would be really cool... SELECT a.*, b.* FROM table_one a RANDOM JOIN -- -- :) table_two b USING (ID) ; Lets say table_one and table_two have a primary key called ID in common (although of course that isn't necessary). The idea is that the RANDOM JOIN would first calculate the INNER JOIN, then scramble the 'links' (or rows) between the two tables. The result would be the same number of rows overall, the same number of distinct a.ID's and b.ID's, but those a.ID's and b.ID's would be randomly associated with the marginals given by the correct inner join. Hopefully that makes sense. I think this function would be really useful for statistical analysis of scientific data within MySQL (using randomized versions of the associations within the tables). Not sure if the above syntax is optimal, because I would like to be able to do things like this... table_one Dept Person Gender A 1 M A 2 F A 3 M B 4 F B 5 M B 6 F table_two DeptPerson Spending A 1 10 A 2 20 A 3 30 B 4 40 B 5 50 B 6 60 SELECT Dept, Gender, AVG(Spending) FROM table_one a INNER JOIN table_two b USING (Dept,Person) GROUP BY Dept, Gender ; With the above query (which I hope is sensible) I would like to keep the departments fixed, and randomize the genders of the people in the departments (keeping the number of each sex in each department the same). So we could INNER JOIN using Dept and then RANDOM JOIN (as described above) using Person - all in one 'JOIN'. All else being the same this should randomize the Gender, but keep the marginals. I guess this is overly complex given the problem, and it actually raises more questions instantly (about statistical analysis), but the function is basic (I hope), and like all SQL it is the simplicity that allows you to build complex and robust statements. Does the random join make any sense? Is this UDF material? Any feedback is welcome, Dan. Thread UDF request? - Dan Bolser, April 1 2005 12:10am _ Don?t just search. Find. Check out the new MSN Search! http://search.msn.click-url.com/go/onm00200636ave/direct/01/ I think I get it. It sounds to me like you want to take a random sampling (possibly to include the entire set) and analyze each sample. You can return the results of a query in random order if you use the RAND() function: http://dev.mysql.com/doc/mysql/en/mathematical-functions.html quote from site As of MySQL 3.23, you can retrieve rows in random order like this: mysql SELECT * FROM tbl_name ORDER BY RAND(); ORDER BY RAND() combined with LIMIT is useful for selecting a random sample of a set of rows: mysql SELECT * FROM table1, table2 WHERE a=b AND cd - ORDER BY RAND() LIMIT 1000; end quote So
Re: UDF Request AGGLOM()
Hi Dan, On Sat, 2005-04-09 at 02:59, Dan Bolser wrote: Who can I prod about setting up a UDF repo at MySQL. I think 'they' should do this ;) Yep it's an existing idea, a very good one, and it's on the todo. Putting such an infrastructure into place will take some time though. Would a special forum for this purpose perhaps be a good intermediate solution? Routines could be posted there, it's searchable... Regards, Arjen. -- Arjen Lentz, Community Relations Manager MySQL AB, www.mysql.com MySQL Users Conference (Santa Clara CA, 18-21 April 2005) Only 8 days until the event! http://www.mysqluc.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: UDF request?
On Thu, 7 Apr 2005, Sean Nolan wrote: I think you'll find you can do what you want with a cross join. A cross join will join every row from the first table with every row from the second table. It will not randomly do this, so you'd have to be creative in randomly selecting rows in the WHERE clause. Here's how you could do this with your data, pardon the highly original and very scientific randomization, but it is just an example :-) Perhaps I don't understand your randomization (because I really don't understand it ;), but I don't think a CROSS join does the trick, because I want to randomly pick the same rows from either side of the join that I would have gotten with an INNER JOIN using the 1:1 primary key between the two tables. This is analagous to sampling without replacement. If I do a cross join and then just restrict the number of rows returned (hey! I just worked out what you are doing below) I don't necessarily get the same 'marginals' (or to randomly pick the same rows from either side of the join) . This is analagous to sampling with replacement. Do you see what I mean? SELECT Dept, Gender, AVG(Spending) FROM table_one a CROSS JOIN table_two b WHERE (a.Person * b.Person) % 4 = 3 GROUP BY Dept, Gender; Sean - Original Message -- Hi, I have a request for a UDF that I would find really useful. I am duplicating this question on the community list as well as the MySQL list, because I am not sure where best to make this kind of request (see the previous post http://lists.mysql.com/community/97). I think the following syntax would be really cool... SELECT a.*, b.* FROM table_one a RANDOM JOIN -- -- :) table_two b USING (ID) ; Lets say table_one and table_two have a primary key called ID in common (although of course that isn't necessary). The idea is that the RANDOM JOIN would first calculate the INNER JOIN, then scramble the 'links' (or rows) between the two tables. The result would be the same number of rows overall, the same number of distinct a.ID's and b.ID's, but those a.ID's and b.ID's would be randomly associated with the marginals given by the correct inner join. Hopefully that makes sense. I think this function would be really useful for statistical analysis of scientific data within MySQL (using randomized versions of the associations within the tables). Not sure if the above syntax is optimal, because I would like to be able to do things like this... table_one Dept Person Gender A 1 M A 2 F A 3 M B 4 F B 5 M B 6 F table_two DeptPerson Spending A 1 10 A 2 20 A 3 30 B 4 40 B 5 50 B 6 60 SELECT Dept, Gender, AVG(Spending) FROM table_one a INNER JOIN table_two b USING (Dept,Person) GROUP BY Dept, Gender ; With the above query (which I hope is sensible) I would like to keep the departments fixed, and randomize the genders of the people in the departments (keeping the number of each sex in each department the same). So we could INNER JOIN using Dept and then RANDOM JOIN (as described above) using Person - all in one 'JOIN'. All else being the same this should randomize the Gender, but keep the marginals. I guess this is overly complex given the problem, and it actually raises more questions instantly (about statistical analysis), but the function is basic (I hope), and like all SQL it is the simplicity that allows you to build complex and robust statements. Does the random join make any sense? Is this UDF material? Any feedback is welcome, Dan. Thread UDF request? - Dan Bolser, April 1 2005 12:10am _ Dont just search. Find. Check out the new MSN Search! http://search.msn.click-url.com/go/onm00200636ave/direct/01/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
UDF Request AGGLOM()
Who can I prod about setting up a UDF repo at MySQL. I think 'they' should do this ;) http://lists.mysql.com/community/97 Anyway I am posting this request to 'community' because I still don't know the appropriate place to post UDF related stuff. This is anoter (potentially crazy) idea for a UDF that I would find very usefull in my research... AGGLOM - Simple agglomerative clustering for MySQL ... The UDF would work on any NUMBER column, and return the number of 'clusters' using agglomerative clustering with a certain threshold as an input. Agglomerative clustering merges any two numbers that are within the 'threshold', and replaces those numbers with the average of the two. The clustering proceedes smallest 'gap' first, and stops when no two numbers are within the threshold. The result would be the number (or perhaps the values) of the remaining clusters. Syntax (suggested) AGGLOM(THRESH,expr (returning a number)) For example Table1 C1 C2 A 1 A 2 A 3 A 4 A 5 A 6 A 7 B 10 B 11 B 12 B 56 B 57 B 58 B 99 B 101 SELECT C1, AGGLOM(C2,1) AS C3 FROM Table1 GROUP BY C1; C1 C3 A 4 B 6 SELECT C1, AGGLOM(C2,2) AS C3 FROM Table1 GROUP BY C1; C1 C3 A 3 B 3 SELECT C1, AGGLOM(C2,3) AS C3 Table1 GROUP BY C1; C1 C3 A 2 B 3 SELECT C1, AGGLOM(C2,4) AS C3 Table1 GROUP BY C1; C1 C3 A 1 B 3 SELECT C1, AGGLOM(C2,50) AS C3 Table1 GROUP BY C1; C1 C3 A 1 B 1 Remember, merge numbers with the smallest difference first, and replace each pair with the average of the two. Recalculate the differences for the new number, and repeat until no distance is smaller than the threshold. This is a usefull clustering 'hack' to see if a distribution is bi-modal or multi modal for example. It is very quick to calculate using a hash table, and could be a great function to add. Is this idea as crazy as I think it might be? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: UDF request?
Dan Bolser [EMAIL PROTECTED] wrote on 04/08/2005 12:41:35 PM: On Thu, 7 Apr 2005, Sean Nolan wrote: I think you'll find you can do what you want with a cross join. A cross join will join every row from the first table with every row from the second table. It will not randomly do this, so you'd have to be creative in randomly selecting rows in the WHERE clause. Here's how you could do this with your data, pardon the highly original and very scientific randomization, but it is just an example :-) Perhaps I don't understand your randomization (because I really don't understand it ;), but I don't think a CROSS join does the trick, because I want to randomly pick the same rows from either side of the join that I would have gotten with an INNER JOIN using the 1:1 primary key between the two tables. This is analagous to sampling without replacement. If I do a cross join and then just restrict the number of rows returned (hey! I just worked out what you are doing below) I don't necessarily get the same 'marginals' (or to randomly pick the same rows from either side of the join) . This is analagous to sampling with replacement. Do you see what I mean? SELECT Dept, Gender, AVG(Spending) FROM table_one a CROSS JOIN table_two b WHERE (a.Person * b.Person) % 4 = 3 GROUP BY Dept, Gender; Sean - Original Message -- Hi, I have a request for a UDF that I would find really useful. I am duplicating this question on the community list as well as the MySQL list, because I am not sure where best to make this kind of request (see the previous post http://lists.mysql.com/community/97). I think the following syntax would be really cool... SELECT a.*, b.* FROM table_one a RANDOM JOIN -- -- :) table_two b USING (ID) ; Lets say table_one and table_two have a primary key called ID in common (although of course that isn't necessary). The idea is that the RANDOM JOIN would first calculate the INNER JOIN, then scramble the 'links' (or rows) between the two tables. The result would be the same number of rows overall, the same number of distinct a.ID's and b.ID's, but those a.ID's and b.ID's would be randomly associated with the marginals given by the correct inner join. Hopefully that makes sense. I think this function would be really useful for statistical analysis of scientific data within MySQL (using randomized versions of the associations within the tables). Not sure if the above syntax is optimal, because I would like to be able to do things like this... table_one Dept Person Gender A 1 M A 2 F A 3 M B 4 F B 5 M B 6 F table_two DeptPerson Spending A 1 10 A 2 20 A 3 30 B 4 40 B 5 50 B 6 60 SELECT Dept, Gender, AVG(Spending) FROM table_one a INNER JOIN table_two b USING (Dept,Person) GROUP BY Dept, Gender ; With the above query (which I hope is sensible) I would like to keep the departments fixed, and randomize the genders of the people in the departments (keeping the number of each sex in each department the same). So we could INNER JOIN using Dept and then RANDOM JOIN (as described above) using Person - all in one 'JOIN'. All else being the same this should randomize the Gender, but keep the marginals. I guess this is overly complex given the problem, and it actually raises more questions instantly (about statistical analysis), but the function is basic (I hope), and like all SQL it is the simplicity that allows you to build complex and robust statements. Does the random join make any sense? Is this UDF material? Any feedback is welcome, Dan. Thread UDF request? - Dan Bolser, April 1 2005 12:10am _ Don?t just search. Find. Check out the new MSN Search! http://search.msn.click-url.com/go/onm00200636ave/direct/01/ I think I get it. It sounds to me like you want to take a random sampling (possibly to include the entire set) and analyze each sample. You can return the results of a query in random order if you use the RAND() function: http://dev.mysql.com/doc/mysql/en/mathematical-functions.html quote from site As of MySQL 3.23, you can retrieve rows in random order like this: mysql SELECT * FROM tbl_name ORDER BY RAND(); ORDER BY RAND() combined with LIMIT is useful for selecting a random sample of a set of rows: mysql SELECT * FROM table1, table2 WHERE a=b AND cd - ORDER BY RAND() LIMIT 1000; end quote So if you want to randomly pick 1900 rows of data from a query you would say (you need to fill in the blanks with your query): SELECT ... FROM ... WHERE ... ORDER BY RAND() LIMIT 1900; As to randomly matching rows from one table with corresponding rows from a second table, that's just an illusion. For any two
RE: UDF request?
On Fri, 8 Apr 2005 [EMAIL PROTECTED] wrote: Dan Bolser [EMAIL PROTECTED] wrote on 04/08/2005 12:41:35 PM: On Thu, 7 Apr 2005, Sean Nolan wrote: I think you'll find you can do what you want with a cross join. A cross join will join every row from the first table with every row from the second table. It will not randomly do this, so you'd have to be creative in randomly selecting rows in the WHERE clause. Here's how you could do this with your data, pardon the highly original and very scientific randomization, but it is just an example :-) Perhaps I don't understand your randomization (because I really don't understand it ;), but I don't think a CROSS join does the trick, because I want to randomly pick the same rows from either side of the join that I would have gotten with an INNER JOIN using the 1:1 primary key between the two tables. This is analagous to sampling without replacement. If I do a cross join and then just restrict the number of rows returned (hey! I just worked out what you are doing below) I don't necessarily get the same 'marginals' (or to randomly pick the same rows from either side of the join) . This is analagous to sampling with replacement. Do you see what I mean? SELECT Dept, Gender, AVG(Spending) FROM table_one a CROSS JOIN table_two b WHERE (a.Person * b.Person) % 4 = 3 GROUP BY Dept, Gender; Sean - Original Message -- Hi, I have a request for a UDF that I would find really useful. I am duplicating this question on the community list as well as the MySQL list, because I am not sure where best to make this kind of request (see the previous post http://lists.mysql.com/community/97). I think the following syntax would be really cool... SELECT a.*, b.* FROM table_one a RANDOM JOIN -- -- :) table_two b USING (ID) ; Lets say table_one and table_two have a primary key called ID in common (although of course that isn't necessary). The idea is that the RANDOM JOIN would first calculate the INNER JOIN, then scramble the 'links' (or rows) between the two tables. The result would be the same number of rows overall, the same number of distinct a.ID's and b.ID's, but those a.ID's and b.ID's would be randomly associated with the marginals given by the correct inner join. Hopefully that makes sense. I think this function would be really useful for statistical analysis of scientific data within MySQL (using randomized versions of the associations within the tables). Not sure if the above syntax is optimal, because I would like to be able to do things like this... table_one Dept Person Gender A 1 M A 2 F A 3 M B 4 F B 5 M B 6 F table_two DeptPerson Spending A 1 10 A 2 20 A 3 30 B 4 40 B 5 50 B 6 60 SELECT Dept, Gender, AVG(Spending) FROM table_one a INNER JOIN table_two b USING (Dept,Person) GROUP BY Dept, Gender ; With the above query (which I hope is sensible) I would like to keep the departments fixed, and randomize the genders of the people in the departments (keeping the number of each sex in each department the same). So we could INNER JOIN using Dept and then RANDOM JOIN (as described above) using Person - all in one 'JOIN'. All else being the same this should randomize the Gender, but keep the marginals. I guess this is overly complex given the problem, and it actually raises more questions instantly (about statistical analysis), but the function is basic (I hope), and like all SQL it is the simplicity that allows you to build complex and robust statements. Does the random join make any sense? Is this UDF material? Any feedback is welcome, Dan. Thread UDF request? - Dan Bolser, April 1 2005 12:10am _ Don?t just search. Find. Check out the new MSN Search! http://search.msn.click-url.com/go/onm00200636ave/direct/01/ I think I get it. It sounds to me like you want to take a random sampling (possibly to include the entire set) and analyze each sample. You can return the results of a query in random order if you use the RAND() function: http://dev.mysql.com/doc/mysql/en/mathematical-functions.html quote from site As of MySQL 3.23, you can retrieve rows in random order like this: mysql SELECT * FROM tbl_name ORDER BY RAND(); ORDER BY RAND() combined with LIMIT is useful for selecting a random sample of a set of rows: mysql SELECT * FROM table1, table2 WHERE a=b AND cd - ORDER BY RAND() LIMIT 1000; end quote So if you want to randomly pick 1900 rows of data from a query you would say (you need to fill in the blanks with your query): SELECT ... FROM ... WHERE ... ORDER BY RAND() LIMIT 1900; As to randomly matching rows from one table with corresponding rows from a second
RE: UDF request?
I think you'll find you can do what you want with a cross join. A cross join will join every row from the first table with every row from the second table. It will not randomly do this, so you'd have to be creative in randomly selecting rows in the WHERE clause. Here's how you could do this with your data, pardon the highly original and very scientific randomization, but it is just an example :-) SELECT Dept, Gender, AVG(Spending) FROM table_one a CROSS JOIN table_two b WHERE (a.Person * b.Person) % 4 = 3 GROUP BY Dept, Gender; Sean - Original Message -- Hi, I have a request for a UDF that I would find really useful. I am duplicating this question on the community list as well as the MySQL list, because I am not sure where best to make this kind of request (see the previous post http://lists.mysql.com/community/97). I think the following syntax would be really cool... SELECT a.*, b.* FROM table_one a RANDOM JOIN -- -- :) table_two b USING (ID) ; Lets say table_one and table_two have a primary key called ID in common (although of course that isn't necessary). The idea is that the RANDOM JOIN would first calculate the INNER JOIN, then scramble the 'links' (or rows) between the two tables. The result would be the same number of rows overall, the same number of distinct a.ID's and b.ID's, but those a.ID's and b.ID's would be randomly associated with the marginals given by the correct inner join. Hopefully that makes sense. I think this function would be really useful for statistical analysis of scientific data within MySQL (using randomized versions of the associations within the tables). Not sure if the above syntax is optimal, because I would like to be able to do things like this... table_one DeptPerson Gender A 1 M A 2 F A 3 M B 4 F B 5 M B 6 F table_two DeptPerson Spending A 1 10 A 2 20 A 3 30 B 4 40 B 5 50 B 6 60 SELECT Dept, Gender, AVG(Spending) FROM table_one a INNER JOIN table_two b USING (Dept,Person) GROUP BY Dept, Gender ; With the above query (which I hope is sensible) I would like to keep the departments fixed, and randomize the genders of the people in the departments (keeping the number of each sex in each department the same). So we could INNER JOIN using Dept and then RANDOM JOIN (as described above) using Person - all in one 'JOIN'. All else being the same this should randomize the Gender, but keep the marginals. I guess this is overly complex given the problem, and it actually raises more questions instantly (about statistical analysis), but the function is basic (I hope), and like all SQL it is the simplicity that allows you to build complex and robust statements. Does the random join make any sense? Is this UDF material? Any feedback is welcome, Dan. Thread UDF request? - Dan Bolser, April 1 2005 12:10am _ Dont just search. Find. Check out the new MSN Search! http://search.msn.click-url.com/go/onm00200636ave/direct/01/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: UDF request?
I think you'll find you can do what you want with a cross join. A cross join will join every row from the first table with every row from the second table. It will not randomly do this, so you'd have to be creative in randomly selecting rows in the WHERE clause. Here's how you could do this with your data, pardon the highly original and very scientific randomization, but it is just an example :-) SELECT Dept, Gender, AVG(Spending) FROM table_one a CROSS JOIN table_two b WHERE (a.Person * b.Person) % 4 = 3 GROUP BY Dept, Gender; Sean - Original Message -- Hi, I have a request for a UDF that I would find really useful. I am duplicating this question on the community list as well as the MySQL list, because I am not sure where best to make this kind of request (see the previous post http://lists.mysql.com/community/97). I think the following syntax would be really cool... SELECT a.*, b.* FROM table_one a RANDOM JOIN -- -- :) table_two b USING (ID) ; Lets say table_one and table_two have a primary key called ID in common (although of course that isn't necessary). The idea is that the RANDOM JOIN would first calculate the INNER JOIN, then scramble the 'links' (or rows) between the two tables. The result would be the same number of rows overall, the same number of distinct a.ID's and b.ID's, but those a.ID's and b.ID's would be randomly associated with the marginals given by the correct inner join. Hopefully that makes sense. I think this function would be really useful for statistical analysis of scientific data within MySQL (using randomized versions of the associations within the tables). Not sure if the above syntax is optimal, because I would like to be able to do things like this... table_one DeptPerson Gender A 1 M A 2 F A 3 M B 4 F B 5 M B 6 F table_two DeptPerson Spending A 1 10 A 2 20 A 3 30 B 4 40 B 5 50 B 6 60 SELECT Dept, Gender, AVG(Spending) FROM table_one a INNER JOIN table_two b USING (Dept,Person) GROUP BY Dept, Gender ; With the above query (which I hope is sensible) I would like to keep the departments fixed, and randomize the genders of the people in the departments (keeping the number of each sex in each department the same). So we could INNER JOIN using Dept and then RANDOM JOIN (as described above) using Person - all in one 'JOIN'. All else being the same this should randomize the Gender, but keep the marginals. I guess this is overly complex given the problem, and it actually raises more questions instantly (about statistical analysis), but the function is basic (I hope), and like all SQL it is the simplicity that allows you to build complex and robust statements. Does the random join make any sense? Is this UDF material? Any feedback is welcome, Dan. Thread UDF request? - Dan Bolser, April 1 2005 12:10am _ FREE pop-up blocking with the new MSN Toolbar get it now! http://toolbar.msn.click-url.com/go/onm00200415ave/direct/01/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
UDF request?
Hi, I have a request for a UDF that I would find really useful. I am duplicating this question on the community list as well as the MySQL list, because I am not sure where best to make this kind of request (see the previous post http://lists.mysql.com/community/97). I think the following syntax would be really cool... SELECT a.*, b.* FROM table_one a RANDOM JOIN -- -- :) table_two b USING (ID) ; Lets say table_one and table_two have a primary key called ID in common (although of course that isn't necessary). The idea is that the RANDOM JOIN would first calculate the INNER JOIN, then scramble the 'links' (or rows) between the two tables. The result would be the same number of rows overall, the same number of distinct a.ID's and b.ID's, but those a.ID's and b.ID's would be randomly associated with the marginals given by the correct inner join. Hopefully that makes sense. I think this function would be really useful for statistical analysis of scientific data within MySQL (using randomized versions of the associations within the tables). Not sure if the above syntax is optimal, because I would like to be able to do things like this... table_one DeptPerson Gender A 1 M A 2 F A 3 M B 4 F B 5 M B 6 F table_two DeptPerson Spending A 1 10 A 2 20 A 3 30 B 4 40 B 5 50 B 6 60 SELECT Dept, Gender, AVG(Spending) FROM table_one a INNER JOIN table_two b USING (Dept,Person) GROUP BY Dept, Gender ; With the above query (which I hope is sensible) I would like to keep the departments fixed, and randomize the genders of the people in the departments (keeping the number of each sex in each department the same). So we could INNER JOIN using Dept and then RANDOM JOIN (as described above) using Person - all in one 'JOIN'. All else being the same this should randomize the Gender, but keep the marginals. I guess this is overly complex given the problem, and it actually raises more questions instantly (about statistical analysis), but the function is basic (I hope), and like all SQL it is the simplicity that allows you to build complex and robust statements. Does the random join make any sense? Is this UDF material? Any feedback is welcome, Dan. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Central UDF project at mysql.com?
Hi, I searched for previous discussion on this topic, but didn't find any. I would like to see a centralized MySQL hosted UDF archive and development project. The only existing 'archives' seem to be somewhat poorly maintained (sorry), and suffer for their duplicated efforts and being loosely distributed throughout the web. The best I can find are here (ranked according to Google)... http://empyrean.lib.ndsu.nodak.edu/~nem/mysql/udf/ http://www.oreillynet.com/pub/wlg/2292 http://www.thecodeproject.com/Purgatory/mygroupconcat.asp http://mysql-udf.sourceforge.net/ http://www.linuxjournal.com/article/6841 I think a centralized project would do wonders for the UDF community, allowing UDF's to be discussed, suggested and developed under one roof. A first step should be to create a [EMAIL PROTECTED] mailing list. Without such a central list the UDF community can't communicate effectively. Who better than MySQL to organize the MySQL UDF community? A simple not officially supported statement is all that is needed. Good UDF's could become part of MySQL proper, and a UDF 'bundle' would be a great development. MySQL programmers could help build UDF's, and the community could vote on 'wanted' functions. You could probably guess where all this is going, and that is towards my own UDF request (where to ask?), but I will leave that for later. Any comments? Any postings that I have missed? Any reason that their is no udf mailing list? I think that their are tons of UDF's waiting to happen, given the right conditions. All the best, Dan. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Central UDF project at mysql.com?
That is a really good idea - though I am not sure there is much of those out there to justify the cause, nor many developers actually using UDFs. Still, having them all in one place could be nothing but a good thing. MarkP On Wed, 30 Mar 2005 14:39:11 +0100 (BST), Dan Bolser [EMAIL PROTECTED] wrote: Hi, I searched for previous discussion on this topic, but didn't find any. I would like to see a centralized MySQL hosted UDF archive and development project. The only existing 'archives' seem to be somewhat poorly maintained (sorry), and suffer for their duplicated efforts and being loosely distributed throughout the web. The best I can find are here (ranked according to Google)... http://empyrean.lib.ndsu.nodak.edu/~nem/mysql/udf/ http://www.oreillynet.com/pub/wlg/2292 http://www.thecodeproject.com/Purgatory/mygroupconcat.asp http://mysql-udf.sourceforge.net/ http://www.linuxjournal.com/article/6841 I think a centralized project would do wonders for the UDF community, allowing UDF's to be discussed, suggested and developed under one roof. A first step should be to create a [EMAIL PROTECTED] mailing list. Without such a central list the UDF community can't communicate effectively. Who better than MySQL to organize the MySQL UDF community? A simple not officially supported statement is all that is needed. Good UDF's could become part of MySQL proper, and a UDF 'bundle' would be a great development. MySQL programmers could help build UDF's, and the community could vote on 'wanted' functions. You could probably guess where all this is going, and that is towards my own UDF request (where to ask?), but I will leave that for later. Any comments? Any postings that I have missed? Any reason that their is no udf mailing list? I think that their are tons of UDF's waiting to happen, given the right conditions. All the best, Dan. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Mark Papadakis Head of RD Phaistos Networks, S.A -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Central UDF project at mysql.com?
On Wed, 30 Mar 2005, Mark Papadakis wrote: That is a really good idea - though I am not sure there is much of those out there to justify the cause, nor many developers actually using UDFs. Cheers :) I think with the right infra more people would start using and developing UDF's, especially if the work was a part of 'MySQL' proper and not just different groups of individuals. I know it wouldn't make any *real* difference, but it would make a psychological difference. Also a central project would be a way to boost visibility for different UDF projects, for example good (ongoing) work could be showcased in the MySQL news letter. Still, having them all in one place could be nothing but a good thing. Yeah, I totally agree :) Especially if resources like the MySQL bugtracker and mailing lists could be shared. Is this the right forum for requesting such things? All the best, Dan. MarkP On Wed, 30 Mar 2005 14:39:11 +0100 (BST), Dan Bolser [EMAIL PROTECTED] wrote: Hi, I searched for previous discussion on this topic, but didn't find any. I would like to see a centralized MySQL hosted UDF archive and development project. The only existing 'archives' seem to be somewhat poorly maintained (sorry), and suffer for their duplicated efforts and being loosely distributed throughout the web. The best I can find are here (ranked according to Google)... http://empyrean.lib.ndsu.nodak.edu/~nem/mysql/udf/ http://www.oreillynet.com/pub/wlg/2292 http://www.thecodeproject.com/Purgatory/mygroupconcat.asp http://mysql-udf.sourceforge.net/ http://www.linuxjournal.com/article/6841 I think a centralized project would do wonders for the UDF community, allowing UDF's to be discussed, suggested and developed under one roof. A first step should be to create a [EMAIL PROTECTED] mailing list. Without such a central list the UDF community can't communicate effectively. Who better than MySQL to organize the MySQL UDF community? A simple not officially supported statement is all that is needed. Good UDF's could become part of MySQL proper, and a UDF 'bundle' would be a great development. MySQL programmers could help build UDF's, and the community could vote on 'wanted' functions. You could probably guess where all this is going, and that is towards my own UDF request (where to ask?), but I will leave that for later. Any comments? Any postings that I have missed? Any reason that their is no udf mailing list? I think that their are tons of UDF's waiting to happen, given the right conditions. All the best, Dan. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Central UDF project at mysql.com?
I also think this would be a good idea. I have written some UDF's for specific internal use and often wondered if they would be useful to others. It would be nice if there was a centralized place to search for UDFs, so you don't have to re-invent the wheel everytime you need a function. I think that the community list may be the appropriate list for disscussion on how to get this going. I believe it is run by Arjen Lentz. I have CC'd him on this message. Maybe he can give some input. Regards, -- Tom Crimmins Interface Specialist Pottawattamie County, Iowa On Wednesday, March 30, 2005 09:12, Dan Bolser wrote: On Wed, 30 Mar 2005, Mark Papadakis wrote: That is a really good idea - though I am not sure there is much of those out there to justify the cause, nor many developers actually using UDFs. Cheers :) I think with the right infra more people would start using and developing UDF's, especially if the work was a part of 'MySQL' proper and not just different groups of individuals. I know it wouldn't make any *real* difference, but it would make a psychological difference. Also a central project would be a way to boost visibility for different UDF projects, for example good (ongoing) work could be showcased in the MySQL news letter. Still, having them all in one place could be nothing but a good thing. Yeah, I totally agree :) Especially if resources like the MySQL bugtracker and mailing lists could be shared. Is this the right forum for requesting such things? All the best, Dan. MarkP On Wed, 30 Mar 2005 14:39:11 +0100 (BST), Dan Bolser [EMAIL PROTECTED] wrote: Hi, I searched for previous discussion on this topic, but didn't find any. I would like to see a centralized MySQL hosted UDF archive and development project. The only existing 'archives' seem to be somewhat poorly maintained (sorry), and suffer for their duplicated efforts and being loosely distributed throughout the web. The best I can find are here (ranked according to Google)... http://empyrean.lib.ndsu.nodak.edu/~nem/mysql/udf/ http://www.oreillynet.com/pub/wlg/2292 http://www.thecodeproject.com/Purgatory/mygroupconcat.asp http://mysql-udf.sourceforge.net/ http://www.linuxjournal.com/article/6841 I think a centralized project would do wonders for the UDF community, allowing UDF's to be discussed, suggested and developed under one roof. A first step should be to create a [EMAIL PROTECTED] mailing list. Without such a central list the UDF community can't communicate effectively. Who better than MySQL to organize the MySQL UDF community? A simple not officially supported statement is all that is needed. Good UDF's could become part of MySQL proper, and a UDF 'bundle' would be a great development. MySQL programmers could help build UDF's, and the community could vote on 'wanted' functions. You could probably guess where all this is going, and that is towards my own UDF request (where to ask?), but I will leave that for later. Any comments? Any postings that I have missed? Any reason that their is no udf mailing list? I think that their are tons of UDF's waiting to happen, given the right conditions. All the best, Dan. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Queries inside UDF
From: Philippe Poelvoorde [EMAIL PROTECTED] Reply-To: 'mysql@lists.mysql.com ' mysql@lists.mysql.com To: 'mysql@lists.mysql.com ' mysql@lists.mysql.com Subject: Re: Queries inside UDF Date: Mon, 21 Mar 2005 11:05:39 + sguazt sguazt wrote: Hi! From: Philippe Poelvoorde [EMAIL PROTECTED] Reply-To: 'mysql@lists.mysql.com ' mysql@lists.mysql.com To: mysql@lists.mysql.com Subject: Re: Queries inside UDF Date: Thu, 17 Mar 2005 08:22:46 + .. You can actually access a DB within a UDF, but you should do the same than when you're using the C api of mysql. So you need a way to get database name, user/passwd and port without user-input, and do mysql_init, mysql_connect, mysql_query, and so on, like when you are accessing that database using a 'external' C program. Also bear in mind that it should be thread-safe ;) a way to extend udf, is ... stored procedure ? Yes I did it ... look my first post (http://lists.mysql.com/mysql/181361) There you can find and example of a C-code I used to write my UDF ... but when using the UDF inside MySQL I've got Can't connect to MySQL server on 'localhost' (111) you should check the permission. Instead compiling the C-code as stand-alone program (removing the xxx_init/xxx_deinit functions and adding a main) all is OK, the query is executed and the program normally exits. did you execute it on your DB server ? (not on your WS) yes I did. The strange fact is just the stand alone program works and the library doesn't even if the connection parameters (host, port, user, password) are the same. The DBMS is on my machine, so to connect to it I use: host: localhost (I've also tried 127.0.0.1, NULL, ...) port: 3306 (I've also tried 0) user: root password: NULL (I've also tried ) -- Marco _ Ricerche online più semplici e veloci con MSN Toolbar! http://toolbar.msn.it/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Queries inside UDF
sguazt sguazt wrote: Hi! From: Philippe Poelvoorde [EMAIL PROTECTED] Reply-To: 'mysql@lists.mysql.com ' mysql@lists.mysql.com To: mysql@lists.mysql.com Subject: Re: Queries inside UDF Date: Thu, 17 Mar 2005 08:22:46 + .. You can actually access a DB within a UDF, but you should do the same than when you're using the C api of mysql. So you need a way to get database name, user/passwd and port without user-input, and do mysql_init, mysql_connect, mysql_query, and so on, like when you are accessing that database using a 'external' C program. Also bear in mind that it should be thread-safe ;) a way to extend udf, is ... stored procedure ? Yes I did it ... look my first post (http://lists.mysql.com/mysql/181361) There you can find and example of a C-code I used to write my UDF ... but when using the UDF inside MySQL I've got Can't connect to MySQL server on 'localhost' (111) you should check the permission. Instead compiling the C-code as stand-alone program (removing the xxx_init/xxx_deinit functions and adding a main) all is OK, the query is executed and the program normally exits. did you execute it on your DB server ? (not on your WS) I can't use stored procedure because the target DB is the 4.0.21 Currently I'm working on MySQL 4.1.10a on my devel station ... but the production station is 4.0.21 (all installed as RPM x86). So please look at the C-code and tell me if it's wrong ... as alternative you can post me a working example of UDF containing MySQL query execution. Thanks! -- Marco -- Philippe Poelvoorde COS Trading Ltd. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] _ Personalizza MSN Messenger con sfondi e fotografie! http://www.ilovemessenger.msn.it/ -- Philippe Poelvoorde COS Trading Ltd. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Queries inside UDF
sguazt sguazt wrote: Thanks for answering! Your solution would be right if I can modify the database (and I have no control on software that populate tables). Unfortunately I can do only queries on that db; so the due date has to be recalculated every time I want to perform the main report (that use the ending date). An alternative way is to created a snapshot of main db with the added fields, but the report may contains not so updated information (e.g. if the snapshot is synch one time at day in theory the report may display info older up to a day). This violates the requirements of my boss who wants a very updated data ... :'-| So the only possible ways are: * do that via high-level language, like C, Perl, Java; I've done this but is very slow ... especially the client running the code is remote (with respect to DB server) ... so in addition to the load generated by the number of queries there's the load of network communication: (Send Queries + Receive Results) * N (where N 1 in general); * do that via SQL+UDF; the advantage of this solution is the load of computation is on DB server ... the load due to the network communication a very minimal: Send Query One Time + Receive Result When I tried UDF I'm said WOW this could be the solution to my problems Unfortunaly, I found out there's no way (at least for me) to access to current DBMS thread or connection; i.e.: SELECT foobar( ... ) FROM ...; I believed from foobar FUNCTION there would have been a way (through parameters passed by MySQL, like UDF_INIT* initid) to access to current DBMS connection object. I this is true ... what a pity! I hope MySQL staff will think to extend UDF to include this feature unless there're hidden trickly I don't see. -- Marco You can actually access a DB within a UDF, but you should do the same than when you're using the C api of mysql. So you need a way to get database name, user/passwd and port without user-input, and do mysql_init, mysql_connect, mysql_query, and so on, like when you are accessing that database using a 'external' C program. Also bear in mind that it should be thread-safe ;) a way to extend udf, is ... stored procedure ? -- Philippe Poelvoorde COS Trading Ltd. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Queries inside UDF
Hi! From: Philippe Poelvoorde [EMAIL PROTECTED] Reply-To: 'mysql@lists.mysql.com ' mysql@lists.mysql.com To: mysql@lists.mysql.com Subject: Re: Queries inside UDF Date: Thu, 17 Mar 2005 08:22:46 + ... You can actually access a DB within a UDF, but you should do the same than when you're using the C api of mysql. So you need a way to get database name, user/passwd and port without user-input, and do mysql_init, mysql_connect, mysql_query, and so on, like when you are accessing that database using a 'external' C program. Also bear in mind that it should be thread-safe ;) a way to extend udf, is ... stored procedure ? Yes I did it ... look my first post (http://lists.mysql.com/mysql/181361) There you can find and example of a C-code I used to write my UDF ... but when using the UDF inside MySQL I've got Can't connect to MySQL server on 'localhost' (111) Instead compiling the C-code as stand-alone program (removing the xxx_init/xxx_deinit functions and adding a main) all is OK, the query is executed and the program normally exits. I can't use stored procedure because the target DB is the 4.0.21 Currently I'm working on MySQL 4.1.10a on my devel station ... but the production station is 4.0.21 (all installed as RPM x86). So please look at the C-code and tell me if it's wrong ... as alternative you can post me a working example of UDF containing MySQL query execution. Thanks! -- Marco -- Philippe Poelvoorde COS Trading Ltd. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] _ Personalizza MSN Messenger con sfondi e fotografie! http://www.ilovemessenger.msn.it/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Queries inside UDF
Hi folks!
(I hope this is the right list ... if not please tell me where I can submit
this post)
I would like to create a MySQL UDF (i.e. User Defined Function) that embeds
a query; for instance, suppose the UDF is named foobar:
mysql SELECT foobar();
When foobar function receives the control from the MySQL, it attempts to
create a query. To do so it has to connect to DB (since it seems there's no
way to access to current DB connection from a UDF function -- at least I
did'nt find it any way). So the flow of execution is:
SELECT foobar()
1 -- Call foobar
2 - init MySQL
3 - connect to MySQL
4 - create/execute query
5 - get query result
6 - close MySQL connection
7 - return result
After doing step 3 I get the error: Can't connect to MySQL server on
'localhost' (111)
One may argue that error is due to the host permission ...
However, executing the same code used inside the foobar function definition
outside the UDF function (i.e. as a separate executable) all is OK!
So anyone know if is it possible do a query inside a UDF? ... And if it is,
how?!
For everyone who would make a try I've written a C-file very similar to my
...
After compiling the file remeber to do (from the MySQL console):
CREATE FUNCTION foobar RETURNS INTEGER SONAME 'libfoobar.so';
where 'libfoobar.so' is the shared file created from the C-file.
Thanks in advance to everyone
-- Marco
--- BEGIN foobar.c ---
#ifdef __WIN__
typedef unsigned __int64 ulonglong; /* Microsofts 64 bit types */
typedef __int64 longlong;
#else
typedef unsigned long long ulonglong;
typedef long long longlong;
#endif /*__WIN__*/
#include mysql.h
#include stdio.h
#include string.h
#ifdef __cpluscplus
extern C {
#endif
my_bool foobar_init( UDF_INIT* initid, UDF_ARGS* args, char* message );
void foobar_deinit( UDF_INIT* initid );
longlong foobar( UDF_INIT* initid, UDF_ARGS* args, char* is_null, char*
error );
my_bool foobar_init( UDF_INIT* initid, UDF_ARGS* args, char* message )
{
/* empty */
}
void foobar_deinit( UDF_INIT* initid )
{
/* empty */
}
longlong foobar( UDF_INIT* initid, UDF_ARGS* args, char* is_null, char*
error )
{
MYSQL* mysql = NULL;
MYSQL_RES* qryResult = NULL;
const char* query = SELECT COUNT(*) FROM tblfoobar;
longlong retval = 0;
mysql = mysql_init( mysql );
if ( !mysql ) {
fprintf( stderr, Error on Init: %s\n, mysql_error( mysql );
*error = 1;
return retval;
}
if ( !mysql_real_connect( mysql, host user password, database, 0,
NULL, 0 ) ) {
fprintf( stderr, Error on Connect: %s\n, mysql_error( mysql );
*error = 1;
return retval;
}
if ( mysql_real_query( mysql, query, strlen( query ) ) ) {
fprintf( stderr, Error on Query: %s\n, mysql_error( mysql );
*error = 1;
return retval;
}
if ( ( qryResult = mysql_store_result( mysql ) ) ) {
MYSQL_ROW row;
unsigned int num_fields;
unsigned int i;
num_fields = mysql_num_fields( qryResult );
while ( ( row = mysql_fetch_row( qryResult ) ) ) {
unsigned long *lengths = NULL;
lengths = mysql_fetch_lengths( qryResult );
for( i = 0; i num_fields; i++ ) {
fprintf( stderr, [%.*s] , (int) lengths[i], row[i] ? row[i] :
NULL);
retval = strtoll( row[i] );
}
fprintf( stderr, \n );
}
mysql_free_result( qryResult );
}
else {
if ( !mysql_field_count( mysql ) ) {
fprintf( stderr, Error on Storing Query Result: %s\n, mysql_error(
mysql );
*error = 1;
return retval;
}
}
mysql_close( mysql );
return retval;
}
#ifdef __cpluscplus
}
#endif
--- END foobar.c ---
_
Blocca le pop-up pubblicitarie con MSN Toolbar! http://toolbar.msn.it/
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Queries inside UDF
On Wednesday, March 16, 2005 09:30, sguazt sguazt wrote: Hi folks! (I hope this is the right list ... if not please tell me where I can submit this post) I would like to create a MySQL UDF (i.e. User Defined Function) that embeds a query; for instance, suppose the UDF is named foobar: mysql SELECT foobar(); When foobar function receives the control from the MySQL, it attempts to create a query. To do so it has to connect to DB (since it seems there's no way to access to current DB connection from a UDF function -- at least I did'nt find it any way). So the flow of execution is: SELECT foobar() 1 -- Call foobar 2 - init MySQL 3 - connect to MySQL 4 - create/execute query 5 - get query result 6 - close MySQL connection 7 - return result [...snip...] const char* query = SELECT COUNT(*) FROM tblfoobar; Can you explain exactly what you are using this for? What benefit does this provide over just executing the query? You can either execute SELECT foobar() or SELECT COUNT(*) FROM tblfoobar, and you don't have to do anything to make the latter work. Creating the UDF just seems like a lot of extra work. Maybe this is my ignorance, but I don't see much use for executing a query within a UDF esspecially if you are using 4.1 with subqueries. -- Tom Crimmins Interface Specialist Pottawattamie County, Iowa -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Queries inside UDF
Hi!
From: Tom Crimmins [EMAIL PROTECTED]
To: sguazt sguazt [EMAIL PROTECTED]
CC: mysql@lists.mysql.com
Subject: RE: Queries inside UDF
Date: Wed, 16 Mar 2005 10:09:16 -0600
...
Can you explain exactly what you are using this for? What benefit does this
provide over just executing the query?
You can either execute SELECT foobar() or SELECT COUNT(*) FROM tblfoobar,
and
you don't have to do anything to make the latter work. Creating the UDF
just
seems like a lot of extra work. Maybe this is my ignorance, but I don't see
much use for executing a query within a UDF esspecially if you are using
4.1
with subqueries.
ok sorry if I submitted a stupid exampe :P
but I wanted to keep the stuff simple.
My real case is a little more complicated ...
First of all, unfornately I've to use MySQL 4.0.21.
The purpose of creating a UDF, in my case, is doing something that in my
opinion cannot be done in a query.
For example, if you have to implement in SQL the following pseudo-code:
start = a unix timestamp;
duration = 100;
stop = null;
increment = 0;
while ( true ) {
start2 = 0;
stop2 = 0;
stop = start + ( duration - increment );
rs = doQuery( SELECT start,stop FROM t1 INNER JOIN t2 ON t1.c1=t2.c1
WHERE t1.start= + start + AND t1.stop= + stop );
if ( rs == null ) {
delta = 0;
start2 = rs[0]['start'];
stop2 = rs[0]['stop'];
if ( start start2 ) {
delta += start2 - start;
}
increment += delta;
start = stop2
}
else {
break;
}
}
return stop;
what do you write?
The main problem is that you can establish a-priori when you stop the main
while ... :(
I thought to use a temporary table but the problem remains ...
Furthermore that code produces an info that is to be used by another (more
complex) query.
So the only solutions are:
1. use a high-level language (C,perl,Java,PHP) and implement the entire
logic (not only the code above) in that language (but this seems to be too
slow ... I did it before exploring the UDF world ;) )
2. try to write a UDF that implement the code above; so you create a UDF,
e.g. named foobar, you can use inside other query: SELECT ...,foobar( start,
duration ) AS stop FROM a very long JOIN ...
Any idea?
bye!!!
-- Marco
--
Tom Crimmins
Interface Specialist
Pottawattamie County, Iowa
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
_
Ricerche online più semplici e veloci con MSN Toolbar!
http://toolbar.msn.it/
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Queries inside UDF
sguazt sguazt [EMAIL PROTECTED] wrote on 03/16/2005 11:54:26 AM:
Hi!
From: Tom Crimmins [EMAIL PROTECTED]
To: sguazt sguazt [EMAIL PROTECTED]
CC: mysql@lists.mysql.com
Subject: RE: Queries inside UDF
Date: Wed, 16 Mar 2005 10:09:16 -0600
...
Can you explain exactly what you are using this for? What benefit does
this
provide over just executing the query?
You can either execute SELECT foobar() or SELECT COUNT(*) FROM
tblfoobar,
and
you don't have to do anything to make the latter work. Creating the UDF
just
seems like a lot of extra work. Maybe this is my ignorance, but I don't
see
much use for executing a query within a UDF esspecially if you are
using
4.1
with subqueries.
ok sorry if I submitted a stupid exampe :P
but I wanted to keep the stuff simple.
My real case is a little more complicated ...
First of all, unfornately I've to use MySQL 4.0.21.
The purpose of creating a UDF, in my case, is doing something that in my
opinion cannot be done in a query.
For example, if you have to implement in SQL the following pseudo-code:
start = a unix timestamp;
duration = 100;
stop = null;
increment = 0;
while ( true ) {
start2 = 0;
stop2 = 0;
stop = start + ( duration - increment );
rs = doQuery( SELECT start,stop FROM t1 INNER JOIN t2 ON t1.c1=t2.c1
WHERE t1.start= + start + AND t1.stop= + stop );
if ( rs == null ) {
delta = 0;
start2 = rs[0]['start'];
stop2 = rs[0]['stop'];
if ( start start2 ) {
delta += start2 - start;
}
increment += delta;
start = stop2
}
else {
break;
}
}
return stop;
what do you write?
The main problem is that you can establish a-priori when you stop the
main
while ... :(
I thought to use a temporary table but the problem remains ...
Furthermore that code produces an info that is to be used by another
(more
complex) query.
So the only solutions are:
1. use a high-level language (C,perl,Java,PHP) and implement the entire
logic (not only the code above) in that language (but this seems to be
too
slow ... I did it before exploring the UDF world ;) )
2. try to write a UDF that implement the code above; so you create a
UDF,
e.g. named foobar, you can use inside other query: SELECT ...,foobar(
start,
duration ) AS stop FROM a very long JOIN ...
Any idea?
bye!!!
-- Marco
--
Tom Crimmins
Interface Specialist
Pottawattamie County, Iowa
--
I can almost follow the logic of your pseudocode. Can you explain what
it is you are trying to compute? It seems as though you are looking for
(or computing) the ending value to some sort of time span but I just can't
quite understand how you are getting there. I can see that you are using
some kind of varying-width (you change your increment value), sliding time
window (you move your start and stop times) but I just don't understand
why you stop when you DO get a result (the else clause of (rs==null)) when
you needed results for the previous loops (I think you meant to stop when
you didn't get a result)... And the significance of the data you finally
found escapes me...
Maybe if I understood the nature of your data better I wouldn't be so
confused. And, if you would be so kind, can you explain why you are
scrolling through the data with a shifting time window to reach some kind
of non-record (I assume)? Is there no other way to reach the same
information? Can you not just use the last record and work from there?
It's hard to get help for a complex data issue like yours unless you give
us enough information so that we understand your both your data and your
needs. Any one of us may see a path to your solution that you haven't
thought of but we can't do that unless we understand both where you are
starting from (your base data structures) and where you need to be (the
data you require). It's going to be very hard to confuse all of us. The
more information you provide, the more complete the help will be.
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
RE: Queries inside UDF
From: [EMAIL PROTECTED]
To: sguazt sguazt [EMAIL PROTECTED]
CC: mysql@lists.mysql.com,[EMAIL PROTECTED]
Subject: RE: Queries inside UDF
Date: Wed, 16 Mar 2005 12:37:59 -0500
I can almost follow the logic of your pseudocode. Can you explain what
it is you are trying to compute? It seems as though you are looking for
(or computing) the ending value to some sort of time span but I just can't
quite understand how you are getting there. I can see that you are using
some kind of varying-width (you change your increment value), sliding time
window (you move your start and stop times) but I just don't understand
why you stop when you DO get a result (the else clause of (rs==null)) when
you needed results for the previous loops (I think you meant to stop when
you didn't get a result)... And the significance of the data you finally
found escapes me...
yes you got it ... I wrote an erroneous stopping condition:
The real stopping condition is (as you told me):
if ( ts != null ) {
}
else {
break; // STOP CONDITION
}
Well I'm trying to explain what I want to do ...
I have a starting date of an event (see variable start) and it's virtual
duration (see variable duration); the word virtual will be clearer later.
For instance you have a table of research projects with the starting date
and their related duration.
In the simple vanilla world, the due date (see variable stop) would be:
start + duration (consider start as a UNIX timestamp and duration expressed
in seconds).
But in real world you've to take care about a calendar. Each project has a
project manager associated.
Each project manager is assigned a calendar because can follow different
holydays (in fact project manager for project1 could be American; project
manager for project2 could be European; and so on ...)
So for each project you've to calculate the real due date according to the
calendar associated to the project manager.
The calendar I have is a event-based calendar (that is, the calendar has
associated a series of calendar event representing the holydays).
So the pseudo-code:
int calculateDueDate( projectManager, wTsStart, duration )
{
wTsStop = 0; // Working Day stop timestamp
increment = 0; // increment of duration
// calculate the Working Day stop timestamp according to the
patching duration
while ( true ) do
nwTsStart = 0; // Non-Working Day start timestamp
nwTsStop = 0; // Non-Working Day stop timestamp
wTsStop = wTsStart + (duration increment);
// retrieve the intersecting interval for the interval
[wTsStart, wTsStop]
rs = doQuery(
SELECT CE.tsstart,CE.tsstop
+ FROM calendar C INNER JOIN calendarevent CE ON
C.id=CE.calendar
+ WHERE C.owner= + projectManager + AND (( +
wTsStart + =CE.tsstart AND + wTsStop + CE.tsstart) OR ( + wTsStart +
CE.tsstart AND + wTsStart + CE.tsstop))
+ ORDER BY CE.tsstart, CE.tsstop
);
if ( rs != null ) then
delta = 0; // offset between the start of intervals
nwTsStart = rs[0]['tsstart'];
nwTsStop = rs[0]['tsstop'];
if ( wTsStart nwTsStart ) then
delta += nwTsStart - wTsStart;
endif
increment += delta;
wTsStart = nwTsStop;
else
break;
endif
endwhile
return wTsStop;
}
the returned value is just the real due date.
The above code have to be repeated for each project manager:
SELECT , calculateDueDate( P.manager, P.startDate, P.duration ) FROM
project P INNER JOIN ...;
bye!!
-- Marco
Maybe if I understood the nature of your data better I wouldn't be so
confused. And, if you would be so kind, can you explain why you are
scrolling through the data with a shifting time window to reach some kind
of non-record (I assume)? Is there no other way to reach the same
information? Can you not just use the last record and work from there?
It's hard to get help for a complex data issue like yours unless you give
us enough information so that we understand your both your data and your
needs. Any one of us may see a path to your solution that you haven't
thought of but we can't do that unless we understand both where you are
starting from (your base data structures) and where you need to be (the
data you require). It's going to be very hard to confuse all of us. The
more information you provide, the more complete the help will be.
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
_
Scopri il nuovo MSN Htomail - 10MB di allegati
http://www.msn.it/hotmail/minisite_10
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
RE: Queries inside UDF
Marco,
I think I understand why you might want the end date of your projects to
be dynamically calculated (assuming that's why you want this calculation
to be a UDF?). You would be able to change a starting date, the project's
duration, or add or delete a holiday and your ending dates would
automatically adjust. However, as you have found out, it is not a simple
calculation to perform.
Based on the starting date and some initial duration, any number of
non-working days (which includes both holidays and weekends) may occur
within that time span. So you adjust your ending date which may now also
include more non-working days, so you repeat the process until you
achieve the duration you wanted and have accurately accounted for all
non-working days since the startdate of the project. I think your pseudo
code fits that model of calculating the finishdate rather well.
I agree with your assessment to do the finish date calculation in code,
not SQL. However I disagree with the design choice to make the finish
date a dynamic value (if that's what you did, sorry if you didn't). You
should only need to compute the finish date's value under the following
conditions:
a) initial record creation
b) if you change the start date
c) if you change the duration
d) if a non-workday was added to or removed from the calendar and that day
fell within the computed time span of the project.
It makes better sense to me to detect one of those 4 conditions and UPDATE
a field that holds the ending date by using your algorithm while
performing one of those actions. That way a query like
SELECT name, startdate, enddate
FROM projects
WHERE startdate = '2005-07-01'
AND enddate '2005-07-01';
doesn't need to compute the value for enddate twice (once for the result
and once for the WHERE test) for every project just to detect any
projects that will be active on July 1, 2005. In fact, that query may even
be able to use an index if you stored the value in a field while if you
kept it only as a calculated value, that would not be possible.
So... have we answered your original question or did we just go WAAY off
track?
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
sguazt sguazt [EMAIL PROTECTED] wrote on 03/16/2005 01:25:09 PM:
From: [EMAIL PROTECTED]
To: sguazt sguazt [EMAIL PROTECTED]
CC: mysql@lists.mysql.com,[EMAIL PROTECTED]
Subject: RE: Queries inside UDF
Date: Wed, 16 Mar 2005 12:37:59 -0500
I can almost follow the logic of your pseudocode. Can you explain
what
it is you are trying to compute? It seems as though you are looking for
(or computing) the ending value to some sort of time span but I just
can't
quite understand how you are getting there. I can see that you are
using
some kind of varying-width (you change your increment value), sliding
time
window (you move your start and stop times) but I just don't understand
why you stop when you DO get a result (the else clause of (rs==null))
when
you needed results for the previous loops (I think you meant to stop
when
you didn't get a result)... And the significance of the data you
finally
found escapes me...
yes you got it ... I wrote an erroneous stopping condition:
The real stopping condition is (as you told me):
if ( ts != null ) {
}
else {
break; // STOP CONDITION
}
Well I'm trying to explain what I want to do ...
I have a starting date of an event (see variable start) and it's virtual
duration (see variable duration); the word virtual will be clearer
later.
For instance you have a table of research projects with the starting
date
and their related duration.
In the simple vanilla world, the due date (see variable stop) would be:
start + duration (consider start as a UNIX timestamp and duration
expressed
in seconds).
But in real world you've to take care about a calendar. Each project has
a
project manager associated.
Each project manager is assigned a calendar because can follow different
holydays (in fact project manager for project1 could be American;
project
manager for project2 could be European; and so on ...)
So for each project you've to calculate the real due date according to
the
calendar associated to the project manager.
The calendar I have is a event-based calendar (that is, the calendar has
associated a series of calendar event representing the holydays).
So the pseudo-code:
int calculateDueDate( projectManager, wTsStart, duration )
{
wTsStop = 0; // Working Day stop timestamp
increment = 0; // increment of duration
// calculate the Working Day stop timestamp according to the
patching duration
while ( true ) do
nwTsStart = 0; // Non-Working Day start timestamp
nwTsStop = 0; // Non-Working Day stop timestamp
wTsStop = wTsStart + (duration increment);
// retrieve the intersecting interval
RE: Queries inside UDF
Thanks for answering! Your solution would be right if I can modify the database (and I have no control on software that populate tables). Unfortunately I can do only queries on that db; so the due date has to be recalculated every time I want to perform the main report (that use the ending date). An alternative way is to created a snapshot of main db with the added fields, but the report may contains not so updated information (e.g. if the snapshot is synch one time at day in theory the report may display info older up to a day). This violates the requirements of my boss who wants a very updated data ... :'-| So the only possible ways are: * do that via high-level language, like C, Perl, Java; I've done this but is very slow ... especially the client running the code is remote (with respect to DB server) ... so in addition to the load generated by the number of queries there's the load of network communication: (Send Queries + Receive Results) * N (where N 1 in general); * do that via SQL+UDF; the advantage of this solution is the load of computation is on DB server ... the load due to the network communication a very minimal: Send Query One Time + Receive Result When I tried UDF I'm said WOW this could be the solution to my problems Unfortunaly, I found out there's no way (at least for me) to access to current DBMS thread or connection; i.e.: SELECT foobar( ... ) FROM ...; I believed from foobar FUNCTION there would have been a way (through parameters passed by MySQL, like UDF_INIT* initid) to access to current DBMS connection object. I this is true ... what a pity! I hope MySQL staff will think to extend UDF to include this feature unless there're hidden trickly I don't see. -- Marco From: [EMAIL PROTECTED] To: sguazt sguazt [EMAIL PROTECTED] CC: mysql@lists.mysql.com,[EMAIL PROTECTED] Subject: RE: Queries inside UDF Date: Wed, 16 Mar 2005 15:20:09 -0500 Marco, I think I understand why you might want the end date of your projects to be dynamically calculated (assuming that's why you want this calculation to be a UDF?). You would be able to change a starting date, the project's duration, or add or delete a holiday and your ending dates would automatically adjust. However, as you have found out, it is not a simple calculation to perform. Based on the starting date and some initial duration, any number of non-working days (which includes both holidays and weekends) may occur within that time span. So you adjust your ending date which may now also include more non-working days, so you repeat the process until you achieve the duration you wanted and have accurately accounted for all non-working days since the startdate of the project. I think your pseudo code fits that model of calculating the finishdate rather well. I agree with your assessment to do the finish date calculation in code, not SQL. However I disagree with the design choice to make the finish date a dynamic value (if that's what you did, sorry if you didn't). You should only need to compute the finish date's value under the following conditions: a) initial record creation b) if you change the start date c) if you change the duration d) if a non-workday was added to or removed from the calendar and that day fell within the computed time span of the project. It makes better sense to me to detect one of those 4 conditions and UPDATE a field that holds the ending date by using your algorithm while performing one of those actions. That way a query like SELECT name, startdate, enddate FROM projects WHERE startdate = '2005-07-01' AND enddate '2005-07-01'; doesn't need to compute the value for enddate twice (once for the result and once for the WHERE test) for every project just to detect any projects that will be active on July 1, 2005. In fact, that query may even be able to use an index if you stored the value in a field while if you kept it only as a calculated value, that would not be possible. So... have we answered your original question or did we just go WAAY off track? Shawn Green Database Administrator Unimin Corporation - Spruce Pine sguazt sguazt [EMAIL PROTECTED] wrote on 03/16/2005 01:25:09 PM: From: [EMAIL PROTECTED] To: sguazt sguazt [EMAIL PROTECTED] CC: mysql@lists.mysql.com,[EMAIL PROTECTED] Subject: RE: Queries inside UDF Date: Wed, 16 Mar 2005 12:37:59 -0500 I can almost follow the logic of your pseudocode. Can you explain what it is you are trying to compute? It seems as though you are looking for (or computing) the ending value to some sort of time span but I just can't quite understand how you are getting there. I can see that you are using some kind of varying-width (you change your increment value), sliding time window (you move your start and stop times) but I just don't understand why you stop when you DO get a result (the else clause of (rs==null)) when you needed results for the previous loops (I think you meant to stop when you didn't get a result
compiler warning (UDF code)
HI everyone, Sorry if this question doesn't belong here... I tried to compile several open-source UDF's (downloaded from http://mysql-udf.sourceforge.net/) and got a warning. Could someone please elaborate on this: /usr/include/asm/atomic.h:40:2: warning: #warning Using kernel header in userland program. BAD! This is from gcc 3.2.2 on Red Hat 9, kernel version 2.4.20 How bad is it really? What are the implications? When I compile the same code with gcc 2.95.2, kernel 2.4.0 on another machine, I do not get this warning. Thanks in advance, Sergei
Re: Implementing xml/xpath UDF with libxml2
Joel McConaughy [EMAIL PROTECTED] wrote: I'm trying to implement a UDF that supports xpath evaluation on a column using the gnome libxml2 library. The library is thread-safe EXCEPT for the initialization and deinitialization routines which need to be called on a pre-process basis. My question: I advise you to ask this questions in [EMAIL PROTECTED] Developers read the list and you will get the needed help. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Implementing xml/xpath UDF with libxml2
I'm trying to implement a UDF that supports xpath evaluation on a column using the gnome libxml2 library. The library is thread-safe EXCEPT for the initialization and deinitialization routines which need to be called on a pre-process basis. My question: 1. Is there a good place (or any place??) to call per-process initialization and deinitialization routines for UDF's? - or - 2. Is there another way to do this? Thanks. Joel
Re: MySQL Restarts -UDF
coolcat2001 wrote: Hi, When an attempt is made to Create a Function that was dropped previously the mysql gets restarted. I have loaded the so into /usr/lib directory. The mysql is statically linked . If the mysql is statically linked, I can't see how you can load an UDF. You can either recompile MySQL dynamically, or try to find a package from you distro. the standard binary packege provided by MySQL does not allow UDF : http://dev.mysql.com/doc/mysql/en/Binary_notes-Linux.html and it cause the previous crash you experience. -- Philippe Poelvoorde COS Trading Ltd. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL Restarts -UDF
Hi, When an attempt is made to Create a Function that was dropped previously the mysql gets restarted. I have loaded the so into /usr/lib directory. The mysql is statically linked . The mysql error log stated that it has got signal 11 has the following key_buffer_size=8388600 read_buffer_size=131072 max_used_connections=1 max_connections=100 threads_connected=2 It is possible that mysqld could use up to key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = 225791 K bytes of memory Hope that's ok; if not, decrease some variables in the equation. thd=0x849a540 //some Values (hexadecimal) New value of fp=(nil) failed sanity check, terminating stack trace! Please read http://www.mysql.com/doc/en/Using_stack_trace.html and follow instructions on how to resolve the stack trace. Resolved stack trace is much more helpful in diagnosing the problem, so please do resolve it Trying to get some variables. Some pointers may be invalid and cause the dump to abort... thd-query at 0x84a7018 = create function xxx returns integer soname xxx.so thd-thread_id=3 MySQL version: 4.0.15-Standard(binary distribution). OS : RedHat Linux 8.0. Please Help me!! . Regards, coolcat Get Your Private, Free E-mail from Indiatimes at http://email.indiatimes.com Buy The Best In BOOKS at http://www.bestsellers.indiatimes.com Bid for for Air Tickets @ Re.1 on Air Sahara Flights. Just log on to http://airsahara.indiatimes.com and Bid Now! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysql restarts when creating UDF.
hi, i have a very serious problem, when i try to reinitialise the database and start my application. By reinitilisation i would remove the database i created along with the UDF functions i created. Then when i try to start my application, which creates the database tables and the UDf, MySQL Restarts . The following error was logged in the error log : Number of processes running now: 0 040712 18:26:33 mysqld restarted 040712 18:26:33 InnoDB: Database was not shut down normally. InnoDB: Starting recovery from log files... InnoDB: Starting log scan based on checkpoint at InnoDB: log sequence number 0 44378 InnoDB: Doing recovery: scanned up to log sequence number 0 44378 040712 18:26:33 InnoDB: Flushing modified pages from the buffer pool... 040712 18:26:33 InnoDB: Started /abc/MySQL4.0.15/mysql-standard-4.0.15-pc-linux-i686/bin/mysqld: ready for connections. Version: '4.0.15-standard-log' socket: '/var/lib/mysql/mysql.sock' port: 3306 040712 20:13:51 mysqld started /advent/MySQL4.0.15/mysql-standard-4.0.15-pc-linux-i686/bin/mysqld: ready for connections. Version: '4.0.15-standard-log' socket: '/var/lib/mysql/mysql.sock' port: 3306 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=8388600 read_buffer_size=131072 max_used_connections=2 max_connections=100 threads_connected=2 It is possible that mysqld could use up to key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = 225791 K bytes of memory Hope that's ok; if not, decrease some variables in the equation. thd=0x84a6aa0 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... Cannot determine thread, fp=0xbff3ef48, backtrace may not be correct. Stack range sanity check OK, backtrace follows: 0x8070640 0x8288108 0x82c2a56 0x82e3cab 0x82c50df 0x82c460a 0x82c5362 0x8283fd6 0x82c460a 0x82842d6 0x8284005 0x80ddf44 0x807d5ad 0x807e895 0x8079e03 0x807985d 0x807904f 0x82858bc 0x82bb07a New value of fp=(nil) failed sanity check, terminating stack trace! Please read http://www.mysql.com/doc/en/Using_stack_trace.html and follow instructions on how to resolve the stack trace. Resolved stack trace is much more helpful in diagnosing the problem, so please do resolve it Trying to get some variables. Some pointers may be invalid and cause the dump to abort... thd-query at 0x84af4f8 = create function getData returns integer soname sharedObject.so thd-thread_id=19 The manual page at http://www.mysql.com/doc/en/Crashing.html contains information that should help you find out what is causing the crash. When the application is started for first time, mysql restarts and the database and UDF's are not created, but however when it was started for the second time, the database and the UDF's are getin created. please help me . this is found to appear only on mysql 4.0.* , while it works fine in 3.23.* ___ALL-NEW Yahoo! Messenger - so many all-new ways to express yourself http://uk.messenger.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql restarts when creating UDF.
Hi, I've got approx. the same pb. Try the MySQL package provided by you favorite distro, recompile, and see if it works. I have no clue where the problem comes from. Prem Soman wrote: hi, i have a very serious problem, when i try to reinitialise the database and start my application. By reinitilisation i would remove the database i created along with the UDF functions i created. Then when i try to start my application, which creates the database tables and the UDf, MySQL Restarts . The following error was logged in the error log : Number of processes running now: 0 040712 18:26:33 mysqld restarted 040712 18:26:33 InnoDB: Database was not shut down normally. InnoDB: Starting recovery from log files... InnoDB: Starting log scan based on checkpoint at InnoDB: log sequence number 0 44378 InnoDB: Doing recovery: scanned up to log sequence number 0 44378 040712 18:26:33 InnoDB: Flushing modified pages from the buffer pool... 040712 18:26:33 InnoDB: Started /abc/MySQL4.0.15/mysql-standard-4.0.15-pc-linux-i686/bin/mysqld: ready for connections. Version: '4.0.15-standard-log' socket: '/var/lib/mysql/mysql.sock' port: 3306 040712 20:13:51 mysqld started /advent/MySQL4.0.15/mysql-standard-4.0.15-pc-linux-i686/bin/mysqld: ready for connections. Version: '4.0.15-standard-log' socket: '/var/lib/mysql/mysql.sock' port: 3306 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=8388600 read_buffer_size=131072 max_used_connections=2 max_connections=100 threads_connected=2 It is possible that mysqld could use up to key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = 225791 K bytes of memory Hope that's ok; if not, decrease some variables in the equation. thd=0x84a6aa0 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... Cannot determine thread, fp=0xbff3ef48, backtrace may not be correct. Stack range sanity check OK, backtrace follows: 0x8070640 0x8288108 0x82c2a56 0x82e3cab 0x82c50df 0x82c460a 0x82c5362 0x8283fd6 0x82c460a 0x82842d6 0x8284005 0x80ddf44 0x807d5ad 0x807e895 0x8079e03 0x807985d 0x807904f 0x82858bc 0x82bb07a New value of fp=(nil) failed sanity check, terminating stack trace! Please read http://www.mysql.com/doc/en/Using_stack_trace.html and follow instructions on how to resolve the stack trace. Resolved stack trace is much more helpful in diagnosing the problem, so please do resolve it Trying to get some variables. Some pointers may be invalid and cause the dump to abort... thd-query at 0x84af4f8 = create function getData returns integer soname sharedObject.so thd-thread_id=19 The manual page at http://www.mysql.com/doc/en/Crashing.html contains information that should help you find out what is causing the crash. When the application is started for first time, mysql restarts and the database and UDF's are not created, but however when it was started for the second time, the database and the UDF's are getin created. please help me . this is found to appear only on mysql 4.0.* , while it works fine in 3.23.* ___ALL-NEW Yahoo! Messenger - so many all-new ways to express yourself http://uk.messenger.yahoo.com -- Philippe Poelvoorde COS Trading Ltd. +44.(0)20.7376.2401 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Strange return behaviour in UDF (bug?)
Hi there,
I'm getting a strange behaviour when returning a value from my user defined
function. When I print the return value inside of the function, I get one
answer (the correct one), however mysql reports a different value all together.
Is this my programming error or a mysql bug?
Here's what I'm seeing:
In mysql:
+-+
| ddist(m.x, m.y, m.z, n.x, n.y, n.z, 30.0893021, 30.3757286, 32.6442299) |
+-+
|0.72 |
|0.66 |
|0.61 |
|0.47 |
+-+
my hostname.err logfile where I print debugging info:
result: 33.048230
result: 9.203189
result: 12.836390
result: 9.615359
Here's the source code for my program
#include stdio.h
#include string.h
#include my_global.h
#include my_sys.h
#include mysql.h
#include m_ctype.h
#ifdef HAVE_DLOPEN
extern C {
my_bool ddist_init (UDF_INIT *initid, UDF_ARGS *args, char *message);
void ddist_deinit (UDF_INIT *initid);
double ddist (UDF_INIT *initid, UDF_ARGS *args, char *is_null, char *error);
}
void ddist_deinit(UDF_INIT *initid){}
my_bool ddist_init(UDF_INIT *initid, UDF_ARGS *args, char *message)
{
initid-decimals=2; // We want 2 decimals in the result
initid-max_length=6; // 3 digits + . + 2 decimals
return 0;
/*
int i=0; // counter
if (!args-arg_count || args-arg_count != 9)
{
strcpy(message,dist must have exactly nine arguments);
return 2;
}
for ( i=0;i6;i++ )
{
if ( args-arg_type[i] != REAL_RESULT )
strcpy( message, dist must be passed six real
numbers);
return 2;
}
*/
}
double ddist(UDF_INIT *initid, UDF_ARGS *args, char *is_null, char *error)
{
double xi, yi, zi, xj, yj, zj, cubex, cubey, cubez ; //variables for
arguments
int shiftx, shifty, shiftz; // temp variables
double rijx, rijy, rijz; // temp variables
double result; // result
xi = *((double*)args-args[0]);
yi = *((double*)args-args[1]);
zi = *((double*)args-args[2]);
xj = *((double*)args-args[3]);
yj = *((double*)args-args[4]);
zj = *((double*)args-args[5]);
cubex = *((double*)args-args[6]);
cubey = *((double*)args-args[7]);
cubez = *((double*)args-args[8]);
shiftx = int( rint( (xi-xj)/cubex ));
shifty = int( rint( (yi-yj)/cubey ));
shiftz = int( rint( (zi-zj)/cubez ));
fprintf(stderr,sx=%d, sy=%d, sz=%d, cx=%f, cy=%f, cz=%f, xi=%f,
yi=%f, zi=%f, xj=%f, yj=%f,
zj=%f\n,shiftx,shifty,shiftz,cubex,cubey,cubez,xi,yi,zi,xj,yj,zj);
rijx = xi-xj-cubex*shiftx;
rijy = yi-yi-cubey*shifty;
rijz = zi-zj-cubez*shiftz;
fprintf(stderr,rijx=%f,rijy=%f,rijz=%f\n,rijx,rijy,rijz);
result = sqrt( (rijx*rijx) + (rijy*rijy) + (rijz*rijz) );
fprintf(stderr,test: %f\n,(rijz*rijz));
fprintf(stderr,result: %f\n,result);
return result;
}
#endif /* HAVE_DLOPEN */
Here's what I'm using to compile:
g++ -O3 -DDBUG_OFF -fno-implicit-templates -fno-exceptions -fno-rtti -shared
-I/usr/include/mysql -rdynamic dist.cc -o dist.so
Anyone have any ideas?
Thanks,
Richard
--
a professional is simply one who gets paid for doing what an amateur does for
love.
-- Ursula K. Le Guin
pgp0HcujgkHue.pgp
Description: PGP signature
MySQLd crash within a UDF on Linux
Description:
I'm experiencing a Linux-related only problem with a simple UDF which call
mysql_real_connect()
How-To-Repeat:
A very simple function:
#include stdio.h
#include stdlib.h
#include string.h
#include my_global.h
#include my_sys.h
#include mysql.h
#include m_ctype.h
#include m_string.h
longlong myfunc(UDF_INIT *initid, UDF_ARGS *args, char *is_null,char *error )
{
MYSQL mysql;
mysql_init(mysql);
if (!(mysql_real_connect(mysql,localhost,root,,test, 0 , NULL , 0 )))
{
*error=1;
return (longlong)(-1);
}
mysql_close(mysql);
return (longlong)1;
}
compiled with
gcc -I /usr/include/mysql -shared -o libmyfunc.so myfunc.c -lmysqlclient_r
The thread crashes with:
thd=0x847bc78
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...
Cannot determine thread, fp=0x43be4d0c, backtrace may not be correct.
Stack range sanity check OK, backtrace follows:
0x810cdbc
0xe420
0x1
0x43bf47c1
0x80d0042
0x80d0082
0x80c5061
0x8100ce7
0x813c64b
0x813b5d0
0x811b08b
0x811f60e
0x811a066
0x8119a57
0x8119025
0x401b39b4
0x847c89c
New value of fp=0x43be6bb0 failed sanity check, terminating stack trace!
Please read http://www.mysql.com/doc/en/Using_stack_trace.html and follow instructions
on how to resolve the stack trace. Resolved
stack trace is much more helpful in diagnosing the problem, so please do
resolve it
Trying to get some variables.
Some pointers may be invalid and cause the dump to abort...
thd-query at 0x84761b0 = select myfunc(prova)
thd-thread_id=1
I'm available to provide more details, anyway it seems crashing within a syscall.
If I run the program with --one-thread it does not crash but returns NULL.
I could provide more details on request.
Fix:
None known.
Submitter-Id: frankie
Originator:Francesco Paolo Lovergine
Organization: Debian
MySQL support: none
Synopsis: udf problem on linux
Severity: serious
Priority: medium
Category: mysql
Class: sw-bug
Release: mysql-4.0.18 (Source distribution)
C compiler:gcc (GCC) 3.3.3 (Debian 20040401)
C++ compiler: g++ (GCC) 3.3.3 (Debian 20040401)
Environment: Debian GNU/Linux, arch i386, libc and gcc below.
System: Linux klecker 2.6.5-1-686 #1 Sat Apr 24 08:47:10 EST 2004 i686 GNU/Linux
Architecture: i686
Some paths: /usr/bin/perl /usr/bin/make /usr/bin/gcc /usr/bin/cc
GCC: Reading specs from /usr/lib/gcc-lib/i486-linux/3.3.3/specs
Configured with: ../src/configure -v
--enable-languages=c,c++,java,f77,pascal,objc,ada,treelang --prefix=/usr
--mandir=/usr/share/man --infodir=/usr/share/info
--with-gxx-include-dir=/usr/include/c++/3.3 --enable-shared --with-system-zlib
--enable-nls --without-included-gettext --enable-__cxa_atexit --enable-clocale=gnu
--enable-debug --enable-java-gc=boehm --enable-java-awt=xlib --enable-objc-gc
i486-linux
Thread model: posix
gcc version 3.3.3 (Debian 20040401)
Compilation info: CC='gcc' CFLAGS='' CXX='g++' CXXFLAGS='' LDFLAGS='' ASFLAGS=''
LIBC:
lrwxrwxrwx1 root root 13 2004-04-24 22:22 /lib/libc.so.6 -
libc-2.3.2.so
-rw-r--r--1 root root 1243792 2004-04-21 20:58 /lib/libc-2.3.2.so
-rw-r--r--1 root root 2640378 2004-04-21 20:59 /usr/lib/libc.a
-rw-r--r--1 root root 204 2004-04-21 20:34 /usr/lib/libc.so
lrwxrwxrwx1 root root 28 2004-03-24 10:01
/usr/lib/libc-client.so.2002edebian - libc-client.so.2002edebian.1
-rw-r--r--1 root root 767184 2004-03-22 23:31
/usr/lib/libc-client.so.2002edebian.1
Configure command: ./configure '--prefix=/usr' '--exec-prefix=/usr'
'--libexecdir=/usr/sbin' '--datadir=/usr/share' '--sysconfdir=/etc/mysql'
'--localstatedir=/var/lib/mysql' '--includedir=/usr/include'
'--infodir=/usr/share/info' '--mandir=/usr/share/man' '--enable-shared'
'--enable-static' '--enable-thread-safe-client' '--enable-assembler'
'--enable-local-infile' '--with-raid'
'--with-unix-socket-path=/var/run/mysqld/mysqld.sock' '--with-mysqld-user=mysql'
'--with-libwrap' '--with-client-ldflags=-lstdc++' '--with-embedded-server'
'--with-vio' '--with-openssl' '--without-docs' '--without-bench' '--without-readline'
'--with-extra-charsets=all' '--with-berkeley-db' '--with-innodb' '--with-debug'
--
MySQL General Mailing List
For list archives: http
UDF: how to get chracter set or convert stinrg to another character set
Hi all, I have two question about UDF(User Defined Function) (MySQL-4.1.1-alpha-Max-log and 4.0.18-log) 1. how to get character_set_* in UDF. I want to get following character_set_* information in UDF. MySQL-4.1.1: character_set_serve character_set_system character_set_database character_set_client character_set_connection character_set_results MySQL-4.0.18 character_set 2. how to convert character encoding in UDF. eg: convert ujis stinrg which was provided by client to utf8. Thanks for any help. -- HIROSE, Masaaki -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Mysql UDF
HI! I wrote a mysql UDF that works well in older versions of mysql (3.23.*) but the same is not working for new versions like (4.0*) . The server restarts every time i invoke my function . I also compiled and linked with libmysqlclient.so.11, but still the problem persists. how to solve the problem, kindly help me. ___ WIN FREE WORLDWIDE FLIGHTS - nominate a cafe in the Yahoo! Mail Internet Cafe Awards www.yahoo.co.uk/internetcafes -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
