Re: mysql-native v2.1.0
On Thursday, 8 March 2018 at 16:24:22 UTC, Steven Schveighoffer wrote: On 3/8/18 11:14 AM, bauss wrote: On Thursday, 8 March 2018 at 15:09:07 UTC, Steven Schveighoffer wrote: The point of a pool is to avoid some costly setup. In my case, I'm not even closing the connection because I feel the "cost" of allocating a connection from the heap isn't worth worrying about. But I also limit the pool so it's only going to allow X concurrent Db connections per thread. If you limit the amount of concurrent db connections will the task wait until a connection can be acquired or will it throw an exception? It uses a semaphore with a max count, so it would just pause the fiber. https://github.com/vibe-d/vibe.d/blob/master/core/vibe/core/connectionpool.d#L55 -Steve Great
Re: mysql-native v2.1.0
On 3/8/18 11:14 AM, bauss wrote: On Thursday, 8 March 2018 at 15:09:07 UTC, Steven Schveighoffer wrote: The point of a pool is to avoid some costly setup. In my case, I'm not even closing the connection because I feel the "cost" of allocating a connection from the heap isn't worth worrying about. But I also limit the pool so it's only going to allow X concurrent Db connections per thread. If you limit the amount of concurrent db connections will the task wait until a connection can be acquired or will it throw an exception? It uses a semaphore with a max count, so it would just pause the fiber. https://github.com/vibe-d/vibe.d/blob/master/core/vibe/core/connectionpool.d#L55 -Steve
Re: mysql-native v2.1.0
On Thursday, 8 March 2018 at 15:09:07 UTC, Steven Schveighoffer wrote: The point of a pool is to avoid some costly setup. In my case, I'm not even closing the connection because I feel the "cost" of allocating a connection from the heap isn't worth worrying about. But I also limit the pool so it's only going to allow X concurrent Db connections per thread. -Steve If you limit the amount of concurrent db connections will the task wait until a connection can be acquired or will it throw an exception?
Re: mysql-native v2.1.0
On 3/8/18 6:57 AM, bauss wrote:
On Thursday, 8 March 2018 at 06:49:52 UTC, Nick Sabalausky (Abscissa)
wrote:
On 03/07/2018 02:32 PM, bauss wrote:
Wait why has it been updated to array() ? So it's not a real range
anymore? Or was it always represented as an array behind the scenes?
I just feel like allocating it into an additional array is a waste of
memory? But if it was always like that I guess it doesn't matter.
query() returns an input range. You can only access one element at a
time (as its read from the network) and you don't know how many there
are ahead of time, BUT it avoids allocating a whole array to store
everything.
In addition to query(), there used to also be a querySet(). The
querySet() would allocate an array and read ALL the results into it so
you could get random-access. But that's exactly what you already get
when you call array() on an input range (such as the input range
returned by query), so querySet was deemed redundant and eliminated.
So if you had code that *did* need an array allocated to store all the
results, then "querySet()" has been replaced with "query().array". But
like you said, if you don't really need the array, then there's no
need to call array() and waste the memory.
However idk what I changed, but the issue stopped for me.
However I still have this issue:
https://github.com/mysql-d/mysql-native/issues/153
(Currently trying to see if I can make a minimal example, but it's
kinda hard to make a minimal example since it's from my Diamond MVC
(vibe.d) library and it isn't used until deep nesting of the
application.
Anyway before I report anything else I could easily be doing
something wrong. There hasn't exactly been any good examples on how
to use it with vibe.d so it has pretty much been a trial and error
thing for me.
Using mysql-native with vibe.d isn't any different from using it
without vibe.d.
It's recommended to use MySQLPool to make a Connection rather than
doing "new Connection" directly simply because connecting is faster
that way (though "new Connection" will still work).
But aside from that, there is absolutely nothing different about
mysql-native whether you're using vibe.d or not.
So basically I keep an associative array of connection pools based on
connection strings like below:
private static __gshared MySQLPool[string] _pools;
And then I retrieve a connection with the function below.
Perhaps I'm not supposed to make a new pool every time, but there is
someway to retrieve a pool already? Maybe that's what I'm doing wrong?
private static shared globalPoolLock = new Object;
private Connection getMySqlConnection(string connectionString)
{
auto pool = _pools.get(connectionString, null);
if (!pool)
{
synchronized (globalPoolLock)
{
pool = new MySQLPool(connectionString);
_pools[connectionString] = pool;
}
}
return pool.lockConnection();
}
After I retrieve the connection then it's basically like the code I
showed you, but that seem to be correct, yes?
Does your application need to support multiple connection strings
while it's running? That's pretty rare unless you're making something
like phpMyAdmin (and even then, I'd probably do it a little
differently). Normally you'd just make one connection pool:
MySQLPool pool;
Then "new" that once with your connection string when you start up,
and you're good.
I guess I can imagine some potential use-cases that get more
complicated than that, but that's really up to your own project's needs.
> However I still have this issue:
>
> https://github.com/mysql-d/mysql-native/issues/153
>
> (Currently trying to see if I can make a minimal example, but
it's kinda
> hard to make a minimal example since it's from my Diamond MVC
(vibe.d)
> library and it isn't used until deep nesting of the
application.
I'm only guessing here, but I wonder if that might be because you seem
to be trying to share pools and connections across threads. I don't
know whether vibe is designed to share TCP connections across threads
or not. I'd say, try ripping out all that
shared/__gshared/synchronized stuff and see how that works.
But if you can't store the pools anywhere, how are you supposed to use
them with vibe.d?
Creating a new pool for every thread seems expensive and dosn't that
defeat the purpose of using pools in the first place?
I mean a 1000 people could connect to a website and potentially that
could create a thousand threads (It probably won't, BUT it could.) and
thus it can't be afforded to create a pool per request.
Well, vibe.d shouldn't be doing that. It creates fibers per request, not
threads, no?
I would expect no more than one thread per core to be created, and you
can afford that many pools (I'd create them at program/thread startup in
any case).
The point of a pool is to avoid some costly setup. In my case, I'm not
even closing the connection because I feel the "cost" of allocating
Re: mysql-native v2.1.0
On Thursday, 8 March 2018 at 13:33:32 UTC, Nick Sabalausky (Abscissa) wrote: Sonke figured out just a few hours ago, and I confirmed, that #170 is caused by a bug in MySQLPool.lockConnection introduced in v2.1.0. I plan to have that fixed with a new release today. That bug turned out to be caused by MySQLPool.lockConnection allowing the LockedConnection!Connection it received from vibe to degrade down to a plain Connection (because of alias this allowing implicit conversion). So MySQLPool.lockConnection would then return the *raw* Connection instead, thus allowing LockedConnection to go out of scope and reclaim the connection as soon as MySQLPool.lockConnection returned. Sonke's been putting thought into how to adjust vibe's LockedConnection so this can't happen by accident. Maybe your lib fell prey to the same accident mine did? And yes I need support for multiple connection strings. It's not an application, it's a library for writing enterprise websites/webapis and thus it should be scalable, since most enterprise solutions uses multiple databases. Ah, I see. Fair enough. That's possible. Looking forward to the release, then I'll test it out and see what happens.
Re: mysql-native v2.1.0
On 03/08/2018 06:57 AM, bauss wrote: But if you can't store the pools anywhere, how are you supposed to use them with vibe.d? You can store the pools wherever you need to, just don't hold onto a Connection past the end of a vibe task. Creating a new pool for every thread seems expensive and dosn't that defeat the purpose of using pools in the first place? I mean a 1000 people could connect to a website and potentially that could create a thousand threads (It probably won't, BUT it could.) and thus it can't be afforded to create a pool per request. This is more a question for vibe's Sonke, to be honest. What I do know is that vibe's basic task distribution typically involves one task/request per *fiber*, not per thread. And that vibe is based heavily around handling multiple simultaneous requests/tasks/fibers within a single thread. Beyond that, I have no idea how vibe deals with threads, hence my suggestion that if you're having trouble, it might be worth seeing whether going totally thread-local with everything might help. Just a thought. At the very last it might help narrow it down. I mean it's kind of a corner case, but it's a common corner case for big applications. I don't create any explicit threads. But like I said, it seems to work after I stopped returning the connection and just the pool. so I think the problem wasn't the pool, but the connection itself. At least I can't reproduce it and tried with hundreds of queries at once and before I could reproduce with less than 10. I admit I'm unclear at this point which problem you're referring to, and whether or not you're saying it's fixed now. (Pardon my confusion - been a long day.) If you mean issue #153, then I wonder if that might be the same cause as #170 (Same symptoms at least: "Acquiring waiter that is already in use"): #170: https://github.com/mysql-d/mysql-native/issues/170 Sonke figured out just a few hours ago, and I confirmed, that #170 is caused by a bug in MySQLPool.lockConnection introduced in v2.1.0. I plan to have that fixed with a new release today. That bug turned out to be caused by MySQLPool.lockConnection allowing the LockedConnection!Connection it received from vibe to degrade down to a plain Connection (because of alias this allowing implicit conversion). So MySQLPool.lockConnection would then return the *raw* Connection instead, thus allowing LockedConnection to go out of scope and reclaim the connection as soon as MySQLPool.lockConnection returned. Sonke's been putting thought into how to adjust vibe's LockedConnection so this can't happen by accident. Maybe your lib fell prey to the same accident mine did? And yes I need support for multiple connection strings. It's not an application, it's a library for writing enterprise websites/webapis and thus it should be scalable, since most enterprise solutions uses multiple databases. Ah, I see. Fair enough.
Re: mysql-native v2.1.0
On Thursday, 8 March 2018 at 06:49:52 UTC, Nick Sabalausky
(Abscissa) wrote:
On 03/07/2018 02:32 PM, bauss wrote:
Wait why has it been updated to array() ? So it's not a real
range anymore? Or was it always represented as an array behind
the scenes?
I just feel like allocating it into an additional array is a
waste of memory? But if it was always like that I guess it
doesn't matter.
query() returns an input range. You can only access one element
at a time (as its read from the network) and you don't know how
many there are ahead of time, BUT it avoids allocating a whole
array to store everything.
In addition to query(), there used to also be a querySet(). The
querySet() would allocate an array and read ALL the results
into it so you could get random-access. But that's exactly what
you already get when you call array() on an input range (such
as the input range returned by query), so querySet was deemed
redundant and eliminated.
So if you had code that *did* need an array allocated to store
all the results, then "querySet()" has been replaced with
"query().array". But like you said, if you don't really need
the array, then there's no need to call array() and waste the
memory.
However idk what I changed, but the issue stopped for me.
However I still have this issue:
https://github.com/mysql-d/mysql-native/issues/153
(Currently trying to see if I can make a minimal example, but
it's kinda hard to make a minimal example since it's from my
Diamond MVC (vibe.d) library and it isn't used until deep
nesting of the application.
Anyway before I report anything else I could easily be doing
something wrong. There hasn't exactly been any good examples
on how to use it with vibe.d so it has pretty much been a
trial and error thing for me.
Using mysql-native with vibe.d isn't any different from using
it without vibe.d.
It's recommended to use MySQLPool to make a Connection rather
than doing "new Connection" directly simply because connecting
is faster that way (though "new Connection" will still work).
But aside from that, there is absolutely nothing different
about mysql-native whether you're using vibe.d or not.
So basically I keep an associative array of connection pools
based on connection strings like below:
private static __gshared MySQLPool[string] _pools;
And then I retrieve a connection with the function below.
Perhaps I'm not supposed to make a new pool every time, but
there is someway to retrieve a pool already? Maybe that's what
I'm doing wrong?
private static shared globalPoolLock = new Object;
private Connection getMySqlConnection(string connectionString)
{
auto pool = _pools.get(connectionString, null);
if (!pool)
{
synchronized (globalPoolLock)
{
pool = new MySQLPool(connectionString);
_pools[connectionString] = pool;
}
}
return pool.lockConnection();
}
After I retrieve the connection then it's basically like the
code I showed you, but that seem to be correct, yes?
Does your application need to support multiple connection
strings while it's running? That's pretty rare unless you're
making something like phpMyAdmin (and even then, I'd probably
do it a little differently). Normally you'd just make one
connection pool:
MySQLPool pool;
Then "new" that once with your connection string when you start
up, and you're good.
I guess I can imagine some potential use-cases that get more
complicated than that, but that's really up to your own
project's needs.
> However I still have this issue:
>
> https://github.com/mysql-d/mysql-native/issues/153
>
> (Currently trying to see if I can make a minimal example, but
it's kinda
> hard to make a minimal example since it's from my Diamond MVC
(vibe.d)
> library and it isn't used until deep nesting of the
application.
I'm only guessing here, but I wonder if that might be because
you seem to be trying to share pools and connections across
threads. I don't know whether vibe is designed to share TCP
connections across threads or not. I'd say, try ripping out all
that shared/__gshared/synchronized stuff and see how that works.
But if you can't store the pools anywhere, how are you supposed
to use them with vibe.d?
Creating a new pool for every thread seems expensive and dosn't
that defeat the purpose of using pools in the first place?
I mean a 1000 people could connect to a website and potentially
that could create a thousand threads (It probably won't, BUT it
could.) and thus it can't be afforded to create a pool per
request.
I mean it's kind of a corner case, but it's a common corner case
for big applications.
I don't create any explicit threads.
But like I said, it seems to work after I stopped returning the
connection and just the pool.
so I think the problem wasn't the pool, but the connection itself.
At least I can't reproduce it and tried with hundreds of queries
at once and before I could reproduce with less than 1
Re: mysql-native v2.1.0
On 03/08/2018 02:14 AM, Bauss wrote: By any chance, are you ever storing a Connection or a ResultRange anywhere? I don't mean as a function-local variable or a a function parameter: I mean like as a class/struct member or as a global? (Well, not that D really has true globals, but a "global" at the module-level.) If you keep around a Connection (or a ResultRange, which has a reference to its own Connection) past the end of the vibe.d task that was using it, then that can definitely cause this kind of problem. (AIUI, vibe.d wants to make the connections you obtain from a pool be Scoped. That would help prevent users from accidentally storing something they shouldn't and running into this issue.) Yeah I stores the result range which I assume was the issue, but I'm returning results as arrays now so it probably solved it. That behavior should be documented though, I don't recall reading that anywhere and it's kind of a gotcha Agreed. Please file a ticket for this so I don't forget.
Re: mysql-native v2.1.0
On Thursday, 8 March 2018 at 07:03:15 UTC, Nick Sabalausky (Abscissa) wrote: On 03/07/2018 04:53 PM, bauss wrote: I can't seem to reproduce it now, but I'll keep an eye for it and see if it still happens, but I think the problem is when you return the connection from a function. I had similar issues returning a raw connection created. By any chance, are you ever storing a Connection or a ResultRange anywhere? I don't mean as a function-local variable or a a function parameter: I mean like as a class/struct member or as a global? (Well, not that D really has true globals, but a "global" at the module-level.) If you keep around a Connection (or a ResultRange, which has a reference to its own Connection) past the end of the vibe.d task that was using it, then that can definitely cause this kind of problem. (AIUI, vibe.d wants to make the connections you obtain from a pool be Scoped. That would help prevent users from accidentally storing something they shouldn't and running into this issue.) Yeah I stores the result range which I assume was the issue, but I'm returning results as arrays now so it probably solved it. That behavior should be documented though, I don't recall reading that anywhere and it's kind of a gotcha
Re: mysql-native v2.1.0
On 03/07/2018 04:53 PM, bauss wrote: I can't seem to reproduce it now, but I'll keep an eye for it and see if it still happens, but I think the problem is when you return the connection from a function. I had similar issues returning a raw connection created. By any chance, are you ever storing a Connection or a ResultRange anywhere? I don't mean as a function-local variable or a a function parameter: I mean like as a class/struct member or as a global? (Well, not that D really has true globals, but a "global" at the module-level.) If you keep around a Connection (or a ResultRange, which has a reference to its own Connection) past the end of the vibe.d task that was using it, then that can definitely cause this kind of problem. (AIUI, vibe.d wants to make the connections you obtain from a pool be Scoped. That would help prevent users from accidentally storing something they shouldn't and running into this issue.)
Re: mysql-native v2.1.0
On 03/07/2018 02:32 PM, bauss wrote:
Wait why has it been updated to array() ? So it's not a real range
anymore? Or was it always represented as an array behind the scenes?
I just feel like allocating it into an additional array is a waste of
memory? But if it was always like that I guess it doesn't matter.
query() returns an input range. You can only access one element at a
time (as its read from the network) and you don't know how many there
are ahead of time, BUT it avoids allocating a whole array to store
everything.
In addition to query(), there used to also be a querySet(). The
querySet() would allocate an array and read ALL the results into it so
you could get random-access. But that's exactly what you already get
when you call array() on an input range (such as the input range
returned by query), so querySet was deemed redundant and eliminated.
So if you had code that *did* need an array allocated to store all the
results, then "querySet()" has been replaced with "query().array". But
like you said, if you don't really need the array, then there's no need
to call array() and waste the memory.
However idk what I changed, but the issue stopped for me.
However I still have this issue:
https://github.com/mysql-d/mysql-native/issues/153
(Currently trying to see if I can make a minimal example, but it's kinda
hard to make a minimal example since it's from my Diamond MVC (vibe.d)
library and it isn't used until deep nesting of the application.
Anyway before I report anything else I could easily be doing something
wrong. There hasn't exactly been any good examples on how to use it with
vibe.d so it has pretty much been a trial and error thing for me.
Using mysql-native with vibe.d isn't any different from using it without
vibe.d.
It's recommended to use MySQLPool to make a Connection rather than doing
"new Connection" directly simply because connecting is faster that way
(though "new Connection" will still work).
But aside from that, there is absolutely nothing different about
mysql-native whether you're using vibe.d or not.
So basically I keep an associative array of connection pools based on
connection strings like below:
private static __gshared MySQLPool[string] _pools;
And then I retrieve a connection with the function below.
Perhaps I'm not supposed to make a new pool every time, but there is
someway to retrieve a pool already? Maybe that's what I'm doing wrong?
private static shared globalPoolLock = new Object;
private Connection getMySqlConnection(string connectionString)
{
auto pool = _pools.get(connectionString, null);
if (!pool)
{
synchronized (globalPoolLock)
{
pool = new MySQLPool(connectionString);
_pools[connectionString] = pool;
}
}
return pool.lockConnection();
}
After I retrieve the connection then it's basically like the code I
showed you, but that seem to be correct, yes?
Does your application need to support multiple connection strings while
it's running? That's pretty rare unless you're making something like
phpMyAdmin (and even then, I'd probably do it a little differently).
Normally you'd just make one connection pool:
MySQLPool pool;
Then "new" that once with your connection string when you start up, and
you're good.
I guess I can imagine some potential use-cases that get more complicated
than that, but that's really up to your own project's needs.
> However I still have this issue:
>
> https://github.com/mysql-d/mysql-native/issues/153
>
> (Currently trying to see if I can make a minimal example, but it's kinda
> hard to make a minimal example since it's from my Diamond MVC (vibe.d)
> library and it isn't used until deep nesting of the application.
I'm only guessing here, but I wonder if that might be because you seem
to be trying to share pools and connections across threads. I don't know
whether vibe is designed to share TCP connections across threads or not.
I'd say, try ripping out all that shared/__gshared/synchronized stuff
and see how that works.
Re: mysql-native v2.1.0
On Wednesday, 7 March 2018 at 19:36:57 UTC, bauss wrote:
On Wednesday, 7 March 2018 at 11:04:10 UTC, Nick Sabalausky
(Abscissa) wrote:
On 03/06/2018 01:31 PM, bauss wrote:
I can't seem to find any examples on how they were updated
and what exactly to change in my code.
Also, FWIW, mysql-native uses semantic versioning (semver), so
anything that worked in v2.0.0 should still continue working
in all v2.x.x.
I was all the way down at 1.1.2, because of other issues that I
can't remember on top of my head, but they have since been
resolved. There were only one issue back for which was the
locked connection thing, which my post above has a link to.
So I changed my code to do this with retrieving the pool and
creating it:
/// Collection of connection pools.
private static __gshared MySQLPool[string] _pools;
/// Global pool lock to ensure we don't attempt to create a
connection pool twice on same connection string.
private static shared globalPoolLock = new Object;
/**
* Gets a new mysql connection from the pool.
* Params:
* connectionString = The connection string for the connection.
* Returns:
* The mysql connection.
*/
private MySQLPool getPool(string connectionString)
{
auto pool = _pools.get(connectionString, null);
if (!pool)
{
synchronized (globalPoolLock)
{
pool = new MySQLPool(connectionString);
_pools[connectionString] = pool;
}
return getPool(connectionString);
}
return pool;
}
And when using it:
auto pool = getPool(useDbConnectionString);
auto connection = pool.lockConnection();
auto prepared = connection.prepare(sql);
prepared.setArgs(params);
Rather than just returning the connection from it.
I can't seem to reproduce it now, but I'll keep an eye for it and
see if it still happens, but I think the problem is when you
return the connection from a function.
I had similar issues returning a raw connection created.
Re: mysql-native v2.1.0
On Wednesday, 7 March 2018 at 11:04:10 UTC, Nick Sabalausky (Abscissa) wrote: On 03/06/2018 01:31 PM, bauss wrote: I can't seem to find any examples on how they were updated and what exactly to change in my code. Also, FWIW, mysql-native uses semantic versioning (semver), so anything that worked in v2.0.0 should still continue working in all v2.x.x. I was all the way down at 1.1.2, because of other issues that I can't remember on top of my head, but they have since been resolved. There were only one issue back for which was the locked connection thing, which my post above has a link to.
Re: mysql-native v2.1.0
On Wednesday, 7 March 2018 at 10:14:08 UTC, Nick Sabalausky
(Abscissa) wrote:
On 03/06/2018 01:54 PM, bauss wrote:
On Tuesday, 6 March 2018 at 18:36:45 UTC, bauss wrote:
Like more specifically do I still call lockConnection() on a
MySQLPool?
If you're using vibe.d and MySQLPool, then yes. But that's
completely unrelated to prepared statements, it has nothing to
do with whether or not you're using them, or how you're using
them.
I think it would be easier to help me if I put some examples.
I just tried changing stuff and I can't seem to get it working.
I kept using lockConnection() as before, I assume it's the
right way.
Then I changed the way I retrieve prepared statements from:
auto prepared = prepare(connection, sql);
prepared.setArgs(params);
to:
auto prepared = connection.prepare(sql);
prepared.setArgs(params);
Either way works. That's just a matter of whether you're using
D's "UFCS" (uniform function-call syntax) feature or not.
Then ex. for reading many entries:
From:
return prepared.querySet().map!((row)
{
auto model = new TModel;
model.row = row;
model.readModel();
return model;
});
To:
return connection.query(prepared).map!((row)
{
auto model = new TModel;
model.row = row;
model.readModel();
return model;
});
But it doesn't seem to work.
I get the following exception:
"Attempting to popFront an empty map" which I assume is
because the result is empty.
Ok, now that one is a little weird. Should work as far as I can
tell. I'd say file a ticket here with a minimized test case I
can just run on my machine to reproduce the error. Please make
sure the test case shows the return type of the function in
question (and whether or not it's simply "auto") and how its
used that leads to the error:
https://github.com/mysql-d/mysql-native/issues
Also, be aware that the updated equivalent to `querySet` is
`query(...).array()`, not plain `query(...)`. However, based on
the portion of code you've posted, I don't see why it shouldn't
work as-is. I'd have to examine a complete test-case to get to
the bottom of that.
My best guess is that the code which is CALLING your functions
above may be doing something wrong with the range being
returned. But again, without a complete test-case, the best I
can do is make guesses.
Wait why has it been updated to array() ? So it's not a real
range anymore? Or was it always represented as an array behind
the scenes?
I just feel like allocating it into an additional array is a
waste of memory? But if it was always like that I guess it
doesn't matter.
However idk what I changed, but the issue stopped for me.
However I still have this issue:
https://github.com/mysql-d/mysql-native/issues/153
(Currently trying to see if I can make a minimal example, but
it's kinda hard to make a minimal example since it's from my
Diamond MVC (vibe.d) library and it isn't used until deep nesting
of the application.
Anyway before I report anything else I could easily be doing
something wrong. There hasn't exactly been any good examples on
how to use it with vibe.d so it has pretty much been a trial and
error thing for me.
So basically I keep an associative array of connection pools
based on connection strings like below:
private static __gshared MySQLPool[string] _pools;
And then I retrieve a connection with the function below.
Perhaps I'm not supposed to make a new pool every time, but there
is someway to retrieve a pool already? Maybe that's what I'm
doing wrong?
private static shared globalPoolLock = new Object;
private Connection getMySqlConnection(string connectionString)
{
auto pool = _pools.get(connectionString, null);
if (!pool)
{
synchronized (globalPoolLock)
{
pool = new MySQLPool(connectionString);
_pools[connectionString] = pool;
}
}
return pool.lockConnection();
}
After I retrieve the connection then it's basically like the code
I showed you, but that seem to be correct, yes?
Re: mysql-native v2.1.0
On 3/7/18 5:23 AM, Sönke Ludwig wrote: Am 06.03.2018 um 05:31 schrieb Nick Sabalausky (Abscissa): (...) Also, AFAIK, vibe doesn't offer socket support like it does TCP, so vibe users would loose out on the automatic yield-on-io that's a cornerstone of vibe's concurrency design. There currently appears to be something broken, but vibe-core does support UDS (by setting a UDS address in the connectTCP call; the legacy name of that function is going to get changed during the next releases). There is also an open PR for the legacy core module: https://github.com/vibe-d/vibe.d/pull/2073 Hm.. I've wanted to do the same thing, yet for redis (as redis is super unsecure when using a network socket, but can have fine-grained permissions when using a unix socket). Will that be possible? -Steve
Re: mysql-native v2.1.0
On 03/06/2018 01:31 PM, bauss wrote: I can't seem to find any examples on how they were updated and what exactly to change in my code. Also, FWIW, mysql-native uses semantic versioning (semver), so anything that worked in v2.0.0 should still continue working in all v2.x.x.
Re: mysql-native v2.1.0
On 03/07/2018 05:23 AM, Sönke Ludwig wrote: Am 06.03.2018 um 05:31 schrieb Nick Sabalausky (Abscissa): (...) Also, AFAIK, vibe doesn't offer socket support like it does TCP, so vibe users would loose out on the automatic yield-on-io that's a cornerstone of vibe's concurrency design. There currently appears to be something broken, but vibe-core does support UDS (by setting a UDS address in the connectTCP call; the legacy name of that function is going to get changed during the next releases). There is also an open PR for the legacy core module: https://github.com/vibe-d/vibe.d/pull/2073 Ahh, thanks. Filed under "I'm glad to be wrong" ;)\
Re: mysql-native v2.1.0
Am 06.03.2018 um 05:31 schrieb Nick Sabalausky (Abscissa): (...) Also, AFAIK, vibe doesn't offer socket support like it does TCP, so vibe users would loose out on the automatic yield-on-io that's a cornerstone of vibe's concurrency design. There currently appears to be something broken, but vibe-core does support UDS (by setting a UDS address in the connectTCP call; the legacy name of that function is going to get changed during the next releases). There is also an open PR for the legacy core module: https://github.com/vibe-d/vibe.d/pull/2073
Re: mysql-native v2.1.0
On 03/06/2018 01:54 PM, bauss wrote:
On Tuesday, 6 March 2018 at 18:36:45 UTC, bauss wrote:
Like more specifically do I still call lockConnection() on a MySQLPool?
If you're using vibe.d and MySQLPool, then yes. But that's completely
unrelated to prepared statements, it has nothing to do with whether or
not you're using them, or how you're using them.
I think it would be easier to help me if I put some examples.
I just tried changing stuff and I can't seem to get it working.
I kept using lockConnection() as before, I assume it's the right way.
Then I changed the way I retrieve prepared statements from:
auto prepared = prepare(connection, sql);
prepared.setArgs(params);
to:
auto prepared = connection.prepare(sql);
prepared.setArgs(params);
Either way works. That's just a matter of whether you're using D's
"UFCS" (uniform function-call syntax) feature or not.
Then ex. for reading many entries:
From:
return prepared.querySet().map!((row)
{
auto model = new TModel;
model.row = row;
model.readModel();
return model;
});
To:
return connection.query(prepared).map!((row)
{
auto model = new TModel;
model.row = row;
model.readModel();
return model;
});
But it doesn't seem to work.
I get the following exception:
"Attempting to popFront an empty map" which I assume is because the
result is empty.
Ok, now that one is a little weird. Should work as far as I can tell.
I'd say file a ticket here with a minimized test case I can just run on
my machine to reproduce the error. Please make sure the test case shows
the return type of the function in question (and whether or not it's
simply "auto") and how its used that leads to the error:
https://github.com/mysql-d/mysql-native/issues
Also, be aware that the updated equivalent to `querySet` is
`query(...).array()`, not plain `query(...)`. However, based on the
portion of code you've posted, I don't see why it shouldn't work as-is.
I'd have to examine a complete test-case to get to the bottom of that.
My best guess is that the code which is CALLING your functions above may
be doing something wrong with the range being returned. But again,
without a complete test-case, the best I can do is make guesses.
Re: mysql-native v2.1.0
On 03/07/2018 04:16 AM, aberba wrote: On Tuesday, 6 March 2018 at 10:15:30 UTC, Martin Tschierschke wrote: On Tuesday, 6 March 2018 at 07:39:00 UTC, aberba wrote: UNIX sockets provide a way to securely connect in an enclosed/isolated environment without exposing connection externally. This is used in my company in our microservice infrastructure on Google Cloud: we connect to our db instance using a proxy and its the recommended approach in microservices. Its a very common security practice. The default approach on Google Cloud. I would do the same for any db I want to prevent external access to. If vibe.d doesn't support it then its missing a big piece of a puzzle. Having sockets would be better, but you may configure your mysql to allow only local connects. So external requests are blocked. https://dba.stackexchange.com/questions/72142/how-do-i-allow-remote-mysql-access-to-all-users Look at the first answer to set the right privileges for your environment. Additionally blocking the mysql port 3306 (beside many others) from outside the network would make sense. The MySQL instance is running in a managed cloud instance. You don't get to tweak things like with vps. Proxy based connection its what's used. Not just in my case...it supported in all major mysql libraries "socketPath". I'd say, please file a ticket here: https://github.com/mysql-d/mysql-native/issues The more clearly the case is presented, the more likely it is to be given appropriate priority. I'd also encourage yourself, and others who may care about this issue, to please consider working on a PR for this. I am only one person and only have so many resources to go around, so if those who do find this important can offer an implementation, that's the best way to get a feature included ASAP. If it's left to me to implement, then it has to compete with all the rest of my projects and priorities. I'd be more than glad to offer any help I can in either understanding the codebase, or in any other way I can help improve the "bus factor" of this project. Just ping me through a ticket on github, or privately via https://semitwist.com/articles/contact/form/contact-us (and yes, I know the captcha system there is woefully out-of-date :/ ) To be clear, please understand, this ISN'T a "no" by any means. I am fully open to this feature getting implemented, and I want this lib to be as useful to as many people as possible. It's just that I only have so much resources of my own, and I don't get paid for this, so if it's left completely up to me then it has to compete with everything else vying for my attention.
Re: mysql-native v2.1.0
On Wednesday, 7 March 2018 at 09:16:42 UTC, aberba wrote: On Tuesday, 6 March 2018 at 10:15:30 UTC, Martin Tschierschke wrote: [...] [...] [...] The MySQL instance is running in a managed cloud instance. You don't get to tweak things like with vps. Proxy based connection its what's used. Not just in my case...it supported in all major mysql libraries "socketPath". OK, so, no useful hint from me
Re: mysql-native v2.1.0
On Tuesday, 6 March 2018 at 10:15:30 UTC, Martin Tschierschke wrote: On Tuesday, 6 March 2018 at 07:39:00 UTC, aberba wrote: On Tuesday, 6 March 2018 at 04:31:42 UTC, Nick Sabalausky (Abscissa) wrote: [...] [...] UNIX sockets provide a way to securely connect in an enclosed/isolated environment without exposing connection externally. This is used in my company in our microservice infrastructure on Google Cloud: we connect to our db instance using a proxy and its the recommended approach in microservices. Its a very common security practice. The default approach on Google Cloud. I would do the same for any db I want to prevent external access to. If vibe.d doesn't support it then its missing a big piece of a puzzle. Having sockets would be better, but you may configure your mysql to allow only local connects. So external requests are blocked. https://dba.stackexchange.com/questions/72142/how-do-i-allow-remote-mysql-access-to-all-users Look at the first answer to set the right privileges for your environment. Additionally blocking the mysql port 3306 (beside many others) from outside the network would make sense. The MySQL instance is running in a managed cloud instance. You don't get to tweak things like with vps. Proxy based connection its what's used. Not just in my case...it supported in all major mysql libraries "socketPath".
Re: mysql-native v2.1.0
On Tuesday, 6 March 2018 at 18:36:45 UTC, bauss wrote:
On Tuesday, 6 March 2018 at 18:31:08 UTC, bauss wrote:
On Saturday, 3 March 2018 at 07:37:38 UTC, Nick Sabalausky
(Abscissa) wrote:
[...]
I'm unsure how I'd go about implementing prepared statements
in a vibe.d application correctly.
[...]
Like more specifically do I still call lockConnection() on a
MySQLPool?
I think it would be easier to help me if I put some examples.
I just tried changing stuff and I can't seem to get it working.
I kept using lockConnection() as before, I assume it's the right
way.
Then I changed the way I retrieve prepared statements from:
auto prepared = prepare(connection, sql);
prepared.setArgs(params);
to:
auto prepared = connection.prepare(sql);
prepared.setArgs(params);
Then ex. for reading many entries:
From:
return prepared.querySet().map!((row)
{
auto model = new TModel;
model.row = row;
model.readModel();
return model;
});
To:
return connection.query(prepared).map!((row)
{
auto model = new TModel;
model.row = row;
model.readModel();
return model;
});
But it doesn't seem to work.
I get the following exception:
"Attempting to popFront an empty map" which I assume is because
the result is empty.
So what am I doing wrong in using prepared statements with the
new updates?
Re: mysql-native v2.1.0
On Tuesday, 6 March 2018 at 18:31:08 UTC, bauss wrote: On Saturday, 3 March 2018 at 07:37:38 UTC, Nick Sabalausky (Abscissa) wrote: [...] I'm unsure how I'd go about implementing prepared statements in a vibe.d application correctly. [...] Like more specifically do I still call lockConnection() on a MySQLPool?
Re: mysql-native v2.1.0
On Saturday, 3 March 2018 at 07:37:38 UTC, Nick Sabalausky
(Abscissa) wrote:
An all-D MySQL/MariaDB client library:
https://github.com/mysql-d/mysql-native
==
Tagged 'v2.1.0', which mainly adds a few new features,
including greatly simplified shortcut syntax for prepared
statements (with automatic, implicit caching and re-use):
---
int i = 5;
string s = "Hello world";
conn.exec("INSERT INTO table_name VALUES (?, ?)", i, s);
conn.query("SELECT * FROM table_name WHERE id=? AND name=?", i,
s);
// Also works:
Prepared stmt = conn.prepare("INSERT ...blah... (?, ?)");
conn.exec(stmt, i, s);
---
As well as additional tools for optional micro-management of
registering/releasing prepared statements.
It also fixes #28: "MYXProtocol thrown when using large
integers as prepared parameters."
Full changelog
https://github.com/mysql-d/mysql-native/blob/master/CHANGELOG.md#v210---2018-03-02
I'm unsure how I'd go about implementing prepared statements in a
vibe.d application correctly.
With older versions it didn't work properly and I'm not sure how
to implement it with the new version of prepared statements.
I can't seem to find any examples on how they were updated and
what exactly to change in my code.
It would be nice with some more examples that aren't basics.
Especially, because I call setArgs() to set arguments from an
array since I have no control over the arguments passed to the
function as they're generated elsewhere and thus I cannot
manually specify the arguments.
I guess I will try to dig around and see what works and what
doesn't, unless you can pinpoint me in the correct direction.
Re: mysql-native v2.1.0
On Tuesday, 6 March 2018 at 07:39:00 UTC, aberba wrote: On Tuesday, 6 March 2018 at 04:31:42 UTC, Nick Sabalausky (Abscissa) wrote: [...] I'm not opposed to it being added, but I'm not aware of what benefit it would provide that would big enough to make it a priority. Also, AFAIK, vibe doesn't offer socket support like it does TCP, so vibe users would loose out on the automatic yield-on-io that's a cornerstone of vibe's concurrency design. UNIX sockets provide a way to securely connect in an enclosed/isolated environment without exposing connection externally. This is used in my company in our microservice infrastructure on Google Cloud: we connect to our db instance using a proxy and its the recommended approach in microservices. Its a very common security practice. The default approach on Google Cloud. I would do the same for any db I want to prevent external access to. If vibe.d doesn't support it then its missing a big piece of a puzzle. Having sockets would be better, but you may configure your mysql to allow only local connects. So external requests are blocked. https://dba.stackexchange.com/questions/72142/how-do-i-allow-remote-mysql-access-to-all-users Look at the first answer to set the right privileges for your environment. Additionally blocking the mysql port 3306 (beside many others) from outside the network would make sense.
Re: mysql-native v2.1.0
On Tuesday, 6 March 2018 at 04:31:42 UTC, Nick Sabalausky (Abscissa) wrote: On 03/05/2018 09:23 AM, aberba wrote: On Saturday, 3 March 2018 at 07:37:38 UTC, Nick Sabalausky (Abscissa) wrote: An all-D MySQL/MariaDB client library: https://github.com/mysql-d/mysql-native == [...] Is unix socket connection supported? I'm not seeing any information about it in the docs. It's not currently supported. From the "Additional Notes" section of the readme: "Normally, MySQL clients connect to a server on the same machine via a Unix socket on *nix systems, and through a named pipe on Windows. Neither of these conventions is currently supported. TCP is used for all connections." - https://github.com/mysql-d/mysql-native#additional-notes I'm not opposed to it being added, but I'm not aware of what benefit it would provide that would big enough to make it a priority. Also, AFAIK, vibe doesn't offer socket support like it does TCP, so vibe users would loose out on the automatic yield-on-io that's a cornerstone of vibe's concurrency design. UNIX sockets provide a way to securely connect in an enclosed/isolated environment without exposing connection externally. This is used in my company in our microservice infrastructure on Google Cloud: we connect to our db instance using a proxy and its the recommended approach in microservices. Its a very common security practice. The default approach on Google Cloud. I would do the same for any db I want to prevent external access to. If vibe.d doesn't support it then its missing a big piece of a puzzle.
Re: mysql-native v2.1.0
On 03/05/2018 09:23 AM, aberba wrote: On Saturday, 3 March 2018 at 07:37:38 UTC, Nick Sabalausky (Abscissa) wrote: An all-D MySQL/MariaDB client library: https://github.com/mysql-d/mysql-native == [...] Is unix socket connection supported? I'm not seeing any information about it in the docs. It's not currently supported. From the "Additional Notes" section of the readme: "Normally, MySQL clients connect to a server on the same machine via a Unix socket on *nix systems, and through a named pipe on Windows. Neither of these conventions is currently supported. TCP is used for all connections." - https://github.com/mysql-d/mysql-native#additional-notes I'm not opposed to it being added, but I'm not aware of what benefit it would provide that would big enough to make it a priority. Also, AFAIK, vibe doesn't offer socket support like it does TCP, so vibe users would loose out on the automatic yield-on-io that's a cornerstone of vibe's concurrency design.
Re: mysql-native v2.1.0
On Saturday, 3 March 2018 at 07:37:38 UTC, Nick Sabalausky (Abscissa) wrote: An all-D MySQL/MariaDB client library: https://github.com/mysql-d/mysql-native == [...] Is unix socket connection supported? I'm not seeing any information about it in the docs.
mysql-native v2.1.0
An all-D MySQL/MariaDB client library:
https://github.com/mysql-d/mysql-native
==
Tagged 'v2.1.0', which mainly adds a few new features, including greatly
simplified shortcut syntax for prepared statements (with automatic,
implicit caching and re-use):
---
int i = 5;
string s = "Hello world";
conn.exec("INSERT INTO table_name VALUES (?, ?)", i, s);
conn.query("SELECT * FROM table_name WHERE id=? AND name=?", i, s);
// Also works:
Prepared stmt = conn.prepare("INSERT ...blah... (?, ?)");
conn.exec(stmt, i, s);
---
As well as additional tools for optional micro-management of
registering/releasing prepared statements.
It also fixes #28: "MYXProtocol thrown when using large integers as
prepared parameters."
Full changelog
https://github.com/mysql-d/mysql-native/blob/master/CHANGELOG.md#v210---2018-03-02
mysql-native v2.1.0-rc3
Third release candidate, `v2.1.0-rc3`: Snuck in a long-overdue fix for #28: "MYXProtocol thrown when using large integers as prepared parameters." https://github.com/mysql-d/mysql-native/issues/28 --- In other news, there will likely be another release immediately after v2.1.0 (Though I'll probably skip the release-candidate period on that one). Current progress on the follow-up to v2.1.0: https://github.com/mysql-d/mysql-native/milestone/4?closed=1
Re: mysql-native v2.1.0-rc1: New features
On 2/25/18 2:59 AM, Nick Sabalausky (Abscissa) wrote:
On 02/25/2018 02:01 AM, Suliman wrote:
What about string interpolation like:
conn.exec("INSERT INTO table_name VALUES ({i}, {s})"); ?
Instead of:
conn.exec("INSERT INTO table_name VALUES (?, ?)", i, s);
The syntax is purely, 100% server-side. Mysql-native just passes the
whole string, question marks and all, straight off to the server. So
whatever syntax the server supports, mysql-native supports. Whatever the
server doesn't, mysql-native doesn't.
I've heard about a MySQL (I think) syntax like this:
"INSERT INTO table_name VALUES (:i, :s)"
But I haven't given it a try, and I don't know about its compatability.
I've been thinking about something more like this:
conn.exec("INSERT INTO table_name VALUES (", i, s, ")");
What I like about this, is that a real SQL insert can have lots of
fields. Getting them all straight can be a pain in the ass, especially
if you are inserting a couple somewhere.
But if the SQL library rearranges this for us, so we can put the data
where it should be, it would be much nicer.
The one wrinkle that makes this difficult is strings that should be
parameters. Are they parameters or SQL statement? I suppose you could
wrap the statement strings into something, or the string values into
something.
String interpolation would be really useful here. e.g.:
https://forum.dlang.org/post/odb9hk$2jqm$1...@digitalmars.com
-Steve
Re: mysql-native v2.1.0-rc1: New features
On 02/25/2018 02:01 AM, Suliman wrote:
What about string interpolation like:
conn.exec("INSERT INTO table_name VALUES ({i}, {s})"); ?
Instead of:
conn.exec("INSERT INTO table_name VALUES (?, ?)", i, s);
The syntax is purely, 100% server-side. Mysql-native just passes the
whole string, question marks and all, straight off to the server. So
whatever syntax the server supports, mysql-native supports. Whatever the
server doesn't, mysql-native doesn't.
I've heard about a MySQL (I think) syntax like this:
"INSERT INTO table_name VALUES (:i, :s)"
But I haven't given it a try, and I don't know about its compatability.
Re: mysql-native v2.1.0-rc1: New features
What about string interpolation like:
conn.exec("INSERT INTO table_name VALUES ({i}, {s})"); ?
Instead of:
conn.exec("INSERT INTO table_name VALUES (?, ?)", i, s);
Re: mysql-native v2.1.0-rc1: New features
Minor second release candidate, 'v2.1.0-rc2'. Only thing this changes is to update the example in the readme to include the new simplified prepared statement interface.
Re: mysql-native v2.1.0-rc1: New features
On Friday, 23 February 2018 at 22:15:37 UTC, Nick Sabalausky (Abscissa) wrote: An all-D MySQL/MariaDB client library: https://github.com/mysql-d/mysql-native == [...] That's a very useful feature. Will simplify some code. As well as additional tools for optional micro-management of registering/releasing prepared statements. [...]
mysql-native v2.1.0-rc1: New features
An all-D MySQL/MariaDB client library:
https://github.com/mysql-d/mysql-native
==
Tagged 'v2.1.0-rc1', release candidate for v2.1.0, which mainly adds a
few new features, inlcuding greatly simplified shortcut syntax for
prepared statements (with automatic, implicit caching and re-use):
---
int i = 5;
string s = "Hello world";
conn.exec("INSERT INTO table_name VALUES (?, ?)", i, s);
conn.query("SELECT * FROM table_name WHERE id=? AND name=?", i, s);
// Also works:
Prepared stmt = conn.prepare("INSERT ...blah... (?, ?)");
conn.exec(stmt, i, s);
---
As well as additional tools for optional micro-management of
registering/releasing prepared statements.
Full changelog for this release is in the 'v2.1.x' branch:
https://github.com/mysql-d/mysql-native/blob/v2.1.x/CHANGELOG.md
Final v2.1.0 release is tentatively scheduled for one week from today.
