#46 Another MySQL Extension
The purpose behind this extension is to allow a LambdaMOO server access to a MySQL database quickly and efficiently.
DESCRIPTION:
1) List of built in functions provided:
mysql_connect(), mysql_close(), mysql_query(), mysql_ping(), mysql_connections()
For detailed information see builtin.help
2) One interesting thing is that this patch won't allow a newline character to be returned to the MOO. Currently it replaces it with the tab character (\t) and I just code all my stuff in the MOO to recognize the tab as a newline. If you have installed a patch to sanitize the MOO DB for newlines you can remove the references to the function sanitize_result_string.
It has support for 5 concurrent connections by default but this can be changed by a #define. All result sets are returned in nested lists (IE 1 list equals one row returned).
This is currently in the alpha/beta stage. I'm fairly confident it won't corrupt your database and reasonably sure it won't crash from tests with very complex SQL statements but the code is a bit ugly right now.
Logged In: YES
user_id=1767321
Originator: YES
Oh, it also has mysql_status() as well which displays data about a particular MySQL connection.
Logged In: YES
user_id=1767321
Originator: YES
File Added: moosql.zip
MOOSQL v 0.3
Logged In: YES
user_id=1767321
Originator: YES
Updated the moosql.zip with some fixes -- Null results no longer will cause a segfault, being the primary one.
Logged In: YES
user_id=1817018
Originator: NO
Soooo, i checked out the latest version of LambdaMOO that was sitting in the CVS repository (1.8.3), and have managed to compile moosql into it, however in game when i try
;mysql_connect("localhost",3306,"root","","cec")
i get the following:
#-1:Input to EVAL, line 3: Permission denied
... called from built-in function eval()
... called from #58:eval_cmd_string (this == #106), line 19
... called from #58:eval*-d (this == #106), line 13
(End of traceback)
is there something i have done wrong, or has something changed in this 1.8.3 that makes it so different to the 1.8.1 that is available in the downloads section?
Ray
Logged In: YES
user_id=1817018
Originator: NO
Soo, i am having a bit of trouble getting moosql to work, i grabbed the very latest copy of LambdaMOO that was sitting in the CVS repository (version 1.8.3), and i have managed to successfully compile moosql into it.
I did all the steps (ie. update bf_register.h, functions.c, Makefile.in), however in game when i try to do:
;mysql_connect("localhost",3306,"root","","cec")
I get the following error:
#-1:Input to EVAL, line 3: Permission denied
... called from built-in function eval()
... called from #58:eval_cmd_string (this == #106), line 19
... called from #58:eval*-d (this == #106), line 13
(End of traceback)
Is there something i have done wrong (yes, i am set Wizard and Programmer)? (i'm going 1.8.3 because 1.8.1 seems to have errors in keywords.c when compiling under Ubuntu 7.10 (Gutsy)).
Logged In: YES
user_id=1817018
Originator: NO
Umm, sorry for the double post, didn't realise that the sourceforge comment system throws you back to the patches list.
Logged In: YES
user_id=1767321
Originator: YES
ray73864,
The likely situation you have is that you have the OUTBOUND_NETWORK compile time option disabled. Try using $network:open() to open a socket somewhere and you'll likely get a E_PERM error too.
Since the MySQL patch uses sockets also I had it respect this OUTBOUND_NETWORK option and raise E_PERM if it is disabled.
Logged In: YES
user_id=1817018
Originator: NO
Hi M,
Thanks for that, SQL has started working again now.
I am having a little problem though that i can't quite work out (i don't think it is a newline problem).
I have 2 tables in my DB (both have more than 1 record in them), one of those tables works fine however the other crashes the moo.
Below is the core dump as well as the information in the 2 tables:
ray73864@mars:~/moos/lambda$ gdb moo core
GNU gdb 6.6-debian
Copyright (C) 2006 Free Software Foundation, Inc.
GDB is free software, covered by the GNU General Public License, and you are
welcome to change it and/or distribute copies of it under certain conditions.
Type "show copying" to see the conditions.
There is absolutely no warranty for GDB. Type "show warranty" for details.
This GDB was configured as "i486-linux-gnu"...
Using host libthread_db library "/lib/tls/i686/cmov/libthread_db.so.1".
warning: Can't read pathname for load map: Input/output error.
Reading symbols from /lib/tls/i686/cmov/libm.so.6...done.
Loaded symbols for /lib/tls/i686/cmov/libm.so.6
Reading symbols from /lib/tls/i686/cmov/libcrypt.so.1...done.
Loaded symbols for /lib/tls/i686/cmov/libcrypt.so.1
Reading symbols from /usr/lib/libmysqlclient.so.15...done.
Loaded symbols for /usr/lib/libmysqlclient.so.15
Reading symbols from /lib/tls/i686/cmov/libc.so.6...done.
Loaded symbols for /lib/tls/i686/cmov/libc.so.6
Reading symbols from /lib/ld-linux.so.2...done.
Loaded symbols for /lib/ld-linux.so.2
Reading symbols from /lib/tls/i686/cmov/libpthread.so.0...done.
Loaded symbols for /lib/tls/i686/cmov/libpthread.so.0
Reading symbols from /lib/tls/i686/cmov/libnsl.so.1...done.
Loaded symbols for /lib/tls/i686/cmov/libnsl.so.1
Reading symbols from /usr/lib/libz.so.1...done.
Loaded symbols for /usr/lib/libz.so.1
Reading symbols from /lib/tls/i686/cmov/libnss_files.so.2...done.
Loaded symbols for /lib/tls/i686/cmov/libnss_files.so.2
Core was generated by `./moo changed.db changed.db.new'.
Program terminated with signal 6, Aborted.
#0 0xffffe410 in __kernel_vsyscall ()
(gdb) where
#0 0xffffe410 in __kernel_vsyscall ()
#1 0xb7c09875 in raise () from /lib/tls/i686/cmov/libc.so.6
#2 0xb7c0b201 in abort () from /lib/tls/i686/cmov/libc.so.6
#3 0x08072058 in abort_server () at server.c:195
#4 0x0807211b in panic (message=0xbfbd926c "Caught signal 11") at server.c:222
#5 0x08072154 in panic_signal (sig=11) at server.c:252
#6 <signal handler called>
#7 0x0807d5ea in sanitize_result_string (string=0x0) at moosql.c:107
#8 0x0807d655 in process_row (res_set=0x84702f0, row=0x8493aa0) at moosql.c:199
#9 0x0807d701 in process_result_set (conn=0x808da60, res_set=0x84702f0) at moosql.c:225
#10 0x0807db09 in bf_mysql_query (arglist={v = {str = 0x8470a34 "", num = 138873396, obj = 138873396, err = 138873396, list = 0x8470a34, fnum = 0x8470a34}, type = 132},
next=1 '\001', vdata=0x0, progr=106) at moosql.c:414
#11 0x08062c35 in call_bi_func (n=132, arglist={v = {str = 0x8470a34 "", num = 138873396, obj = 138873396, err = 138873396, list = 0x8470a34, fnum = 0x8470a34}, type = 132},
func_pc=1 '\001', progr=106, vdata=0x0) at functions.c:251
#12 0x0805f31d in run_interpreter (raise=0 '\0', e=E_NONE, result=0x0, is_fg=1, do_db_tracebacks=1) at execute.c:1620
#13 0x08061a3a in do_task (prog=0x83fcfc8, which_vector=-1, result=0x0, is_fg=1, do_db_tracebacks=1) at execute.c:2214
#14 0x08061d31 in do_input_task (user=106, pc=0x808d260, this=106, vh={ptr = 0x808a650}) at execute.c:2322
#15 0x08078c57 in do_command_task (tq=0x8469b08, command=0x846b584 "; mysql_query(#-2,\"SELECT * FROM menu_article\")") at tasks.c:711
#16 0x08079272 in run_ready_tasks () at tasks.c:1261
#17 0x08072a78 in main_loop () at server.c:476
#18 0x08073dd7 in main (argc=0, argv=0xbfbda670) at server.c:1271
(gdb)
Table 1:
mysql> select * from menu
-> ;
+----+-----------+-----------+-------------+
| id | label | parent_id | menu_type |
+----+-----------+-----------+-------------+
| 1 | News | 0 | news |
| 11 | Tech News | 1 | news |
| 3 | Science | 1 | news |
| 8 | About Us | 0 | information |
+----+-----------+-----------+-------------+
4 rows in set (0.00 sec)
Table 2:
mysql> select * from menu_article;
+----+--------+-------------+---------------------------------------------------------------------------------+-------------+---------------+
| id | menuid | label | bodytext | articledate | articleauthor |
+----+--------+-------------+---------------------------------------------------------------------------------+-------------+---------------+
| 2 | 1 | New Site | The new CEC website is almost up and running, just a few more bugs to iron out. | 1192847073 | NULL |
| 9 | 11 | I am a test | Hi, i am a test | 2147483647 | NULL |
+----+--------+-------------+---------------------------------------------------------------------------------+-------------+---------------+
2 rows in set (0.00 sec)
Ray.
Logged In: YES
user_id=1767321
Originator: YES
Hi again ray,
I see where its crashing. The function I made sanitize_result_string is trying to access memory at the memory location 0x0 (null) and thus causing a segmentation fault as it is not allowed to access memory at that location. I thought I had fixed this bug in the newer version.
I'm currently working on updating this patch to fix one or two other bugs as well as add multiple line transaction and result support. I believe you might be using the 1st version of my MOO SQL patch which had that bug in it. Try recompiling with the version currently available for download on this site?
michael
Logged In: YES
user_id=1817018
Originator: NO
Hi M,
Thanks for that, yeah, i may have been using an old version, had been so long without looking at my 'lambda' directory that i probably copied the wrong version over.
Anyway, it all works now, doesn't seem to be any other problems that i can find.
Ray
Logged In: YES
user_id=1767321
Originator: YES
File Added: moosql.zip
Logged In: YES
user_id=1767321
Originator: YES
Updated to version 1.0 which includes support for multi-line transactions and multi-line results. Can download from here or from
http://cs.netsville.com/moosql/
Logged In: YES
user_id=1767321
Originator: YES
Removing v1.0 of the patch for the moment due to a discovered memory leak.