 |
aslanthekat
Novice
Joined: 17 Sep 2007
Posts: 44
Location: Missouri
|
 Posted: Sun Nov 04, 2012 3:29 pm
MySQL revisited
|
I kind of wanted to reopen a topic that has been discussed a few times and has been spread out over the forum the last couple of years and make the info about this a little easier to find for other people that want to work with sql databases. The ability to connect to a mysql database is the sole reason I bought cmud pro. I've opened a few threads about the subject while I've played around with it, but then I get bored/frustrated/interested in doing something else and forget about it for months at a time. It also seems some people have run into similar issues and either gave up or didn't post their fixes. Well, now I'm interested in trying it again and want to try to bring life back into the subject.
Below are some topics that helped me get started.
Last go round with this I decided to go the route of opening an ssh connection and passing information back and forth between my main session and my ssh session. This works pretty well except ssh returns the results looking like
| Quote: |
+------+---------+------------+
| ID | this | that | the other |
+------+---------+----------------+
| 3317 | data1 | data2 | data3 |
+------+---------+----------------+ |
The above didn't turn out how I wanted, but anyone thats messed with sql knows what it looks like. Anyhow, this kind of becomes a pain to format and put into cmud variables that can be worked with, but it works and it is fast. Even formatting, setting variable values, and sending it back to my main session is fast. Which brings me to my first real problem. Using %sql and %sqldb to insert and return data is really slow, in terms of how fast a database query should be. Hopefully, I'm just not doing it properly, because as it is right now, the lag it gives my characters after one of my queries is triggered, makes it not an option. Here is a sample of the syntax I'm using.
| Code: |
row = %sql( mydb, "SELECT * FROM history WHERE channel='"@channel"' order by id desc limit "@numreturn"")
#WHILE (!@row.Eof()) {#print @row.Item("time") @row.Item("Message")
#CALL @row.Next} |
| Code: |
| #CALL @db.Execute(%concat("insert into history (channel, message) values ('buddy','{",%1,"} ",$message,"');")) |
Both of these usually cause maybe half a second to a second or two of lag. Its quite noticeable when cmud is thinking and my prompt or other mud info isn't returned on time. Using ssh to perform the same queries and then formatting and returning the results to my main sessions gives absolutely no lag.
I usually open the database connection when I first open my session, so my triggers aren't constantly having to open a connection before executing the query. Each trigger starts off by checking if the connection is open, and if not, reopens it.
Which leads me to my second issue.
| Quote: |
| SQL Error: MySQL server has gone away. |
I periodically receive this error. I can't recreate it when I'm testing fixes for it. It seems to happen after my query triggers haven't fired for a while and then try to fire. So I was thinking it was because my connection to my database has closed. But as I mentioned, my triggers check my db connection and reopen it if needed.
The second issue I can probably track down the culprit myself, its the first issue that is my biggest concern.
As always, I appreciate any help. |
|
|
|
|
aslanthekat
Novice
Joined: 17 Sep 2007
Posts: 44
Location: Missouri
|
Posted: Sun Nov 04, 2012 4:16 pm
|
A little update about the SQL Error:
I just had it happen again. Again it was after I hadn't queried my database for a short while. Immediately after I got the error I did #print @db.connected and it showed true. Then a query tried to run again and I got the error again. So I called @db.close then @db.open, triggered my query again and I didn't get the error. Still not sure what is causing the error. |
|
|
|
|
Daern
Sorcerer
Joined: 15 Apr 2011
Posts: 809
|
Posted: Sun Nov 04, 2012 6:06 pm
|
The sql commands are a fairly new addition to CMUD and I don't think they were ever widely used, so they probably are a bit buggy. I experimented with them once a couple years ago to connect to mysql and didn't get very far. I ended up using lua instead of zScript. Maybe you could look into that?
|
|
|
|
|
MattLofton
GURU
Joined: 23 Dec 2000
Posts: 4834
Location: USA
|
Posted: Sun Nov 04, 2012 7:00 pm
|
I stuck with SQLite, since all other database formats (MySQL, Oracle, Jet/ADO, etc) require the user to be running CMud Pro instead of just regular CMud. I don't use SSH, but I am getting zero lag in my queries. I suspect your problem is fundamental to your design, if not your choice of format or possibly the use of the ORDER BY statement.
|
|
_________________
EDIT: I didn't like my old signature |
 |
|
|
aslanthekat
Novice
Joined: 17 Sep 2007
Posts: 44
Location: Missouri
|
Posted: Mon Nov 05, 2012 12:52 am
|
I'm not using ssh at the moment. I am trying to get away from ssh and use cmuds built in functions. I had thought about the lag being because of order by, but I have tried it without the order by. I've also tried to return results of a tiny(only one record) database with 3 or 4 fields, with no conditions. Finally, this happens on my insert statements too, and I'm not inserting an absurd amount of data at once. As my example above showed its only two fields, and the trigger fires maybe once a second at its most frequent.
I'll try it out on a sqlite database just to test it out, but I'd really like to use mysql. |
|
|
|
|
MattLofton
GURU
Joined: 23 Dec 2000
Posts: 4834
Location: USA
|
Posted: Mon Nov 05, 2012 6:31 am
|
I didn't see it earlier, but part of your issue is that %concat() is handled at run time rather than at compile time. %eval() is a similar type of function, and I believe %exec()/#EXEC are as well. It might just be these hanging you up, as they are all slow in and of themselves because of this run/compile difference.
There are certain (and usually infrequent) cases where you'll need to use these functions instead of their faster compiled shortcuts, but otherwise it's:
"literal string "@variable" another literal string" //implicit concatenation
(x operator y) //compiled evaluation
//just don't ever use #EXEC/%exec, there's always a better (and faster!) way |
|
_________________
EDIT: I didn't like my old signature |
|
|
|
aslanthekat
Novice
Joined: 17 Sep 2007
Posts: 44
Location: Missouri
|
Posted: Tue Nov 06, 2012 1:53 pm
|
Thanks Matt and Daern.
Getting rid of %concat did seem to help a little. In fact at the start of the day it was running as good as could be expected. However, I did end up getting the "SQL Error: MySQL server has gone away." error after my database had not been queried for a short while(maybe 10-15 minutes). @db.connected still showed true, but each time a query was triggered I would get that error. @db.close, then @db.open seems to fix that problem, so I rewrote my script to instead of checking if the connection was open, to just close it and reopen it before each query.
This also worked for a while, but it seems that after several queries things returned to how they were, slow and unuseable. I'm not sure if my connection just got worse later in the day, or if closing and opening the connection over and over created too many connections or something and slowed me down.
I didn't think to shut down cmud and reopen it to clear anything that might be tying things up. I'm going to play with it some more to see if closing and opening the connection is causing a problem, but if I can figure out the "SQL Error: MySQL server has gone away." I won't have to do this.
I suppose a work around could be to just send some sort of command to the server every so often, but thats kind of ugly. |
|
|
|
|
|
|
|
|
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum
|
|
