gmsv_mysqloo - Updated OO MySQL Module (multiple statements/stored procs!)

[Fredy]

[QUOTE=Revenge282;44186195]Just out of curiosity, what query could you possible construct that is that massive?[/QUOTE]
For example:

SELECT * FROM items where itemstackid in (123,12,16,321,193,...)

With many seperate itemstackids. (There are many more possibilities though)

KingofBeast I applaud you for this.

[Chessnut]

If this is for an inventory, why not sure the steamID and you could do

SELECT * FROM items WHERE steamID = "STEAM_0:1"

[Revenge282]

[QUOTE=syl0r;44186648]For example:

SELECT * FROM items where itemstackid in (123,12,16,321,193,...)

With many seperate itemstackids. (There are many more possibilities though)

KingofBeast I applaud you for this.[/QUOTE]

There's still something fundamentally wrong with a 32kb query. There should be proper indices so that you don't need long lists in a IN clause like that.

EDIT:
I'm derailing... My bad.

EDIT (because I just thought of this):
32kb of text is literally 9 pages of text...

[ARitz Cracker]

Trust me, I almost cried when reading over what I just done. Basically, I need to edit multiple entries in my table in a random fashion. First I selected * from my table, Do my calculations and edits in lua, and for every row I edited, I do this:

fuckinglongquery = fuckinglongquery.."UPDATE arc_test_table SET money="..v.money.." WHERE name='"..k.."';"

(Yes, I am checking my inputs)

[editline]10th March 2014[/editline]

It was either that, or send >3000 queries.

[FPtje]

[QUOTE=LegoGuy;44190706]Trust me, I almost cried when reading over what I just done. Basically, I need to edit multiple entries in my table in a random fashion. First I selected * from my table, Do my calculations and edits in lua, and for every row I edited, I do this:

fuckinglongquery = fuckinglongquery.."UPDATE arc_test_table SET money="..v.money.." WHERE name='"..k.."';"

(Yes, I am checking my inputs)

[editline]10th March 2014[/editline]

It was either that, or send >3000 queries.[/QUOTE]
O(n^2) concatenation

Also, by "I am checking my inputs" do you mean "I escape \"quotes\" through a function provided by the library and not sql.SQLStr"?

[ARitz Cracker]

[QUOTE=FPtje;44192594]O(n^2) concatenation

Also, by "I am checking my inputs" do you mean "I escape \"quotes\" through a function provided by the library and not sql.SQLStr"?[/QUOTE]

If you're referring to Database:escape( str ), yeah. I'm using that.

... and I don't know what you mean by "O(n^2) concatenation"

[Fredy]

[QUOTE=FPtje;44192594]O(n^2) concatenation

Also, by "I am checking my inputs" do you mean "I escape \"quotes\" through a function provided by the library and not sql.SQLStr"?[/QUOTE]
I am not sure how lua implements strings, but if they do it in any sensible way they are using stringbuffers in the background (god I hope so) which would reduce it to O(n).
But at the end of the day it is lua so probably not .....
On the other hand even if he has a 32 kb string at the end there would only be ~500*5 concatenations which is not really a problem unless he uses this query multiple times a tick (which he most certainly doesnt)

[FPtje]

[QUOTE=LegoGuy;44192852]If you're referring to Database:escape( str ), yeah. I'm using that.

... and I don't know what you mean by "O(n^2) concatenation"[/QUOTE]
Take the following code:
[lua]
b = "cock"
> Lua will make an "array" of size 4: [c, o, c, k]
c = "nob"
> Lua will make an "array" of size 3: [n, o, b]
a = b .. c
> This is the interesting part. Lua first creates an empty "array" of size 7:
> [_, _, _, _, _, _, _]
> Then it puts b in it:
> [c, o, c, k, _, _, _]
> Then it puts c in it:
> [c, o, c, k, n, o, b]
[/lua]
(I couldn't be bothered to put the letters in single quotes)

Think about this. Every time you concatenate two things, it creates a NEW string and copies the two strings in the new string. So here's what you're doing:

fuckinglongquery = 150 character or so text
fuckinglongquery = 150 character or so text .. 150 character or so text = 300 character or so text. Perform 300 steps to copy the other two texts
fuckinglongquery = 300 character or so text .. 150 character or so text
Perform 450 steps to create the new string
fuckinglongquery = 450 character or so text .. 150 character or so text
Perform 600 steps to create the new string.

In the end you'll be running 150 + 300 + 450 + 600 + ... + (n-1) * 150 steps. Where n is the amount of update queries you concatenate.
So for one string that's 150
for 2 strings that's 450
...

Here's the list for up to 30 strings that you concatenate:
[150,450,900,1500,2250,3150,4200,5400,6750,8250,9900,11700,13650,15750,18000,20400,22950,25650,28500,31500,34650,37950,41400,45000,48750,52650,56700,60900,65250,69750]

That's 69750 steps for concatenating 30 strings with 150 characters. And I'm not even taking the fact that you concatenate not [I]once[/I] but [I]five[/I] times. Feel free to multiply the above numbers by five for a good idea of what that does to the amount of steps you need to create the final string.

That's a quadratic increase of steps right there. Just for fun here's the amount of steps for 100 strings: 757500

[Fredy]

Apparently lua doesn't use string buffers in the background, who would've expected that :v:
@LegoGuy
First I would test if you experience any lag on the server (dropping server fps, etc.) when you run the query, if you do then use this solution [url]http://lua-users.org/wiki/SimpleStringBuffer[/url] or that one [url]http://www.lua.org/pil/11.6.html[/url]

[ARitz Cracker]

Eh, seems that the module caps the query to around 160 characters anyway. I guess I'll have to run the queries one at a time and wait 12 whole seconds for my operation to complete.

[Fredy]

[QUOTE=LegoGuy]Eh, seems that the module caps the query to around 160 characters anyway. I guess I'll have to run the queries one at a time and wait 12 whole seconds for my operation to complete.[/QUOTE]
I am pretty sure it doesnt do that, atleast I see nothing like that in the source code of the plugin.

[Revenge282]

[QUOTE=LegoGuy;44190706]Trust me, I almost cried when reading over what I just done. Basically, I need to edit multiple entries in my table in a random fashion. First I selected * from my table, Do my calculations and edits in lua, and for every row I edited, I do this:

fuckinglongquery = fuckinglongquery.."UPDATE arc_test_table SET money="..v.money.." WHERE name='"..k.."';"

(Yes, I am checking my inputs)

[editline]10th March 2014[/editline]

It was either that, or send >3000 queries.[/QUOTE]

Why are you in a situation with 3000+ queries at once though? Also, please for the love of God and all that is holy, do not use VARCHAR/TEXT primary keys (ie. your 'name' column).

[ARitz Cracker]

[QUOTE=Revenge282;44194536]Why are you in a situation with 3000+ queries at once though? Also, please for the love of God and all that is holy, do not use VARCHAR/TEXT primary keys (ie. your 'name' column).[/QUOTE]

Basically, I'm editing all my entries at once. 3000 is an exaggeration of an approximate number, it's actually directly proportional to the amount of entries there are.

[editline]11th March 2014[/editline]

I can't really use any OR clause because everything is being edited differently based on my lua calculation.

[Revenge282]

[QUOTE=LegoGuy;44198491]Basically, I'm editing all my entries at once. 3000 is an exaggeration of an approximate number, it's actually directly proportional to the amount of entries there are.

[editline]11th March 2014[/editline]

I can't really use any OR clause because everything is being edited differently based on my lua calculation.[/QUOTE]

The if the calculations are all the same type of thing (ie. increasing everything by 15%), you can hit all the rows with an UPDATE in one query. If you're doing something this ridiculously large through a module you probably might be best of trying to do it through a PHP script and calling that through the http library in Lua instead so you aren't waiting on empty result sets to return in your think hooks.

[ARitz Cracker]

[QUOTE=Revenge282;44198998]The if the calculations are all the same type of thing (ie. increasing everything by 15%), you can hit all the rows with an UPDATE in one query. If you're doing something this ridiculously large through a module you probably might be best of trying to do it through a PHP script and calling that through the http library in Lua instead so you aren't waiting on empty result sets to return in your think hooks.[/QUOTE]

The operation I'm doing cannot be done in a single query; It's a pretty big randomized calculation...
About the php thing... That's a good idea. How would I know when it's done, though? I have almost no experience in using http in GMod, and last time I tried using the http library serverside on a dedicated server, it failed.

[Revenge282]

[QUOTE=LegoGuy;44209557]The operation I'm doing cannot be done in a single query; It's a pretty big randomized calculation...
About the php thing... That's a good idea. How would I know when it's done, though? I have almost no experience in using http in GMod, and last time I tried using the http library serverside on a dedicated server, it failed.[/QUOTE]
I added you on Steam (same picture as the avatar). We will figure out something hopefully a little more efficient if you want.

[Lord of War]

Greetings,

Unfortunately I still have issues regarding the "out-of-sync" error every time I execute a query after I ran a query containing multiple statements.

I wrote myself a nice MySQL-"Class" to wrap around the mysqloo module.
Every time I get one of these errors it automatically reconnects and executes the failed query again.
But that's not the best solution for the problem, since I'm sending around large amounts of data and executing them in the main thread after reconnecting is not what I had in mind.

I wrote some example code to make it easier to understand my problem:

  require("mysqloo")
  print("Version = " .. mysqloo.VERSION)

  local database = mysqloo.connect("127.0.0.1", "garrysmod", "*****", "garrysmod", 3306, "", bit.bor(mysqloo.CLIENT_MULTI_STATEMENTS, mysqloo.CLIENT_MULTI_RESULTS))

  function database:onConnected()
    print("Successfully connected to database...")
  end

  function database:onConnectionFailed(errMsg)
    print("Unable to connect to database: " .. errMsg)
  end

  database:connect()
  database:wait()

  local q1 = database:query("INSERT INTO garrysmod.test (value) VALUES ('test1')")
  function q1:onSuccess(data) print("q1 succeeded") end
  function q1:onError(errMsg) print("q1 failed: " .. errMsg) end
  q1:start()
  q1:wait()

  local q2 = database:query("INSERT INTO garrysmod.test (value) VALUES ('test2'); INSERT INTO garrysmod.test (value) VALUES ('test3');")
  function q2:onSuccess(data) print("q2 succeeded") end
  function q2:onError(errMsg) print("q2 failed: " .. errMsg) end
  q2:start()
  q2:wait()

  local q3 = database:query("INSERT INTO garrysmod.test (value) VALUES ('test4')")
  function q3:onSuccess(data) print("q3 succeeded") end
  function q3:onError(errMsg) print("q3 failed: " .. errMsg) end
  q3:start()
  q3:wait()

In the server console I get the following output:

  Version = 8.1 r001
  Successfully connected to database...
  q1 succeeded
  q2 succeeded
  q3 failed: Commands out of sync; you can't run this command now

This happens with asynchronous queries as well.

My server is running Ubuntu 12.04 LTS (x64) with the latest updates.
I'm currently running the following version of MySQL:

SHOW VARIABLES LIKE  "%version%"

+-------------------------+-------------------------+
|      Variable_name      |          Value          |
+-------------------------+-------------------------+
| innodb_version          | 5.5.35                  |
| protocol_version        | 10                      |
| slave_type_conversions  |                         |
| version                 | 5.5.35-0ubuntu0.12.04.2 |
| version_comment         | (Ubuntu)                |
| version_compile_machine | x86_64                  |
| version_compile_os      | debian-linux-gnu        |
+-------------------------+-------------------------+

Any help is appreciated.

[Pandaman09]

Would it be possible to have this compiled for Mac

[Donkie]

Would it be possible to add prepared statements support?

[MayorBee]

The Database:status() function seems to crash my server about 10% of the time. This is running on a local 64bit windows dedicated server and running MySQL version 5.6.12 via WAMP.

Code to replicate:
[lua]require("mysqloo")

local db = mysqloo.connect("localhost", "user01", "******", "test", 3306)

function db:onConnected()
MsgC(Color(255,0,255,255), "onConnected()\n")
end

function db:onConnectionFailed(err)
MsgC(Color(255,0,255,255), "onConnectionFailed()\n")
end

MsgC(Color(255,255,0,255), "PreStatus1\n")
MsgN("status1: ".. db:status())
MsgC(Color(255,255,0,255), "PostStatus1\n")
db:connect()
MsgC(Color(255,255,0,255), "PreStatus2\n")
MsgN("status2: ".. db:status())
MsgC(Color(255,255,0,255), "PostStatus2\n")
db:wait()
MsgC(Color(255,255,0,255), "PreStatus3\n")
MsgN("status3: ".. db:status())
MsgC(Color(255,255,0,255), "PostStatus3\n")[/lua]

Image of server console right before it closes:
[img]http://puu.sh/8oTu7[/img]

[-Natekit-]

[IMG]http://i.gyazo.com/8c60176f78a6830f2929acc428936cec.png[/IMG]War of the modules

[georgeri]

[QUOTE=MayorBee;44653688]The Database:status() function seems to crash my server about 10% of the time. This is running on a local 64bit windows dedicated server and running MySQL version 5.6.12 via WAMP.

Code to replicate:
[lua]require("mysqloo")

local db = mysqloo.connect("localhost", "user01", "******", "test", 3306)

function db:onConnected()
MsgC(Color(255,0,255,255), "onConnected()\n")
end

function db:onConnectionFailed(err)
MsgC(Color(255,0,255,255), "onConnectionFailed()\n")
end

MsgC(Color(255,255,0,255), "PreStatus1\n")
MsgN("status1: ".. db:status())
MsgC(Color(255,255,0,255), "PostStatus1\n")
db:connect()
MsgC(Color(255,255,0,255), "PreStatus2\n")
MsgN("status2: ".. db:status())
MsgC(Color(255,255,0,255), "PostStatus2\n")
db:wait()
MsgC(Color(255,255,0,255), "PreStatus3\n")
MsgN("status3: ".. db:status())
MsgC(Color(255,255,0,255), "PostStatus3\n")[/lua]

Image of server console right before it closes:
[img]http://puu.sh/8oTu7[/img][/QUOTE]

db:status() is an expensive function. You're calling it 3 times in a row.

[MayorBee]

[QUOTE=georgeri;44716839]db:status() is an expensive function. You're calling it 3 times in a row.[/QUOTE]
Yes, but if you look at console output you will notice that only the "PreStatus1" and not the "PostStatus1" gets printed before it crashes, seeming to indicate that it is crashing during the first call.

[Arhenox]

Hey, I have some problems with all my addons using MySQL...
PointShop, Sourcebans... they all disconnect themselves from database.
Recently I've changed my MySQL server, I didn't have these problems before.
I've checked both servers' settings and they're all exactly the same (wait_timeout and interactive_timeout are set to 8h).
Sourcebans gives me the error every 5 minutes...
I'm getting these problems with (or without) players on server.
I don't have any idea what to do...
---------------
Update: I've tried to use different MySQL server, same problems...

[aStonedPenguin]

- snip -

[Arhenox]

[QUOTE=StonedPenguin;44730309]
MySQLOO v8.1 - OO MySQL Module
This module relies on the server's think hook. If there are no players online, think doesn't run, and that is why the module isn't working for you.
[/QUOTE]
Sorry for not replying.
I know it works only with players on server, I wrote "I'm getting these problems with (or without) players on server.". I'm getting these problems even with 18 players online, so that's not the point.

[Mista Tea]

I've noticed that performing queries such as:

SELECT EXISTS ( SELECT 1 FROM `table` WHERE Column = 'SomeValue' );

SELECT MAX( Column ) FROM `table` WHERE Column = 'SomeValue'; 

Both return part of the query itself as a key in the resulting table. For example, these queries would return:

1:
    EXISTS( SELECT 1 FROM `table` WHERE Column = 'SomeValue' )" = 1

1:
    MAX( Column ) = 1

I don't know if this is intended or not but it's rather irksome. It seems like it should be returning a predefined key in the result table instead of returning part of the query itself.

The way it is currently returning the result forces us to do something along the lines of:

for k, v in pairs( result[1] ) do  	     
    return v -- there should only  be 1 key/value pair, but we don't know the key without looping
end

Is there anything that can be changed about how this returns its values?

[Mista Tea]

Also, I've discovered that running

mysqlooObj:escape( nil )

will crash the server instantly.

[Willox]

SQL: SELECT MAX(x) AS y

Lua: row['y']

[Kingof🅱east]

[QUOTE=Mista Tea;44986678]I've noticed that performing queries such as:

SELECT EXISTS ( SELECT 1 FROM `table` WHERE Column = 'SomeValue' );

SELECT MAX( Column ) FROM `table` WHERE Column = 'SomeValue'; 

Both return part of the query itself as a key in the resulting table. For example, these queries would return:

1:
    EXISTS( SELECT 1 FROM `table` WHERE Column = 'SomeValue' )" = 1

1:
    MAX( Column ) = 1

I don't know if this is intended or not but it's rather irksome. It seems like it should be returning a predefined key in the result table instead of returning part of the query itself.

The way it is currently returning the result forces us to do something along the lines of:

for k, v in pairs( result[1] ) do  	     
    return v -- there should only  be 1 key/value pair, but we don't know the key without looping
end

Is there anything that can be changed about how this returns its values?[/QUOTE]

This is intended. It's just how MySQL works. Use this to make it prettier:

SELECT EXISTS ( SELECT 1 FROM `table` WHERE Column = 'SomeValue' ) as Exists;

SELECT MAX( Column ) as Max FROM `table` WHERE Column = 'SomeValue';