www.digitalmars.com         C & C++   DMDScript  

digitalmars.D.announce - mysql-native v2.1.0

reply "Nick Sabalausky (Abscissa)" <SeeWebsiteToContactMe semitwist.com> writes:
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.


prepared parameters."

Full changelog
https://github.com/mysql-d/mysql-native/blob/master/CHANGELOG.md#v210---2018-03-02
Mar 02 2018
next sibling parent reply aberba <karabutaworld gmail.com> writes:
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.
Mar 05 2018
parent reply "Nick Sabalausky (Abscissa)" <SeeWebsiteToContactMe semitwist.com> writes:
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.
Mar 05 2018
next sibling parent reply aberba <karabutaworld gmail.com> writes:
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.
Mar 05 2018
parent reply Martin Tschierschke <mt smartdolphin.de> writes:
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.
Mar 06 2018
parent reply aberba <karabutaworld gmail.com> writes:
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".
Mar 07 2018
next sibling parent Martin Tschierschke <mt smartdolphin.de> writes:
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....
Mar 07 2018
prev sibling parent "Nick Sabalausky (Abscissa)" <SeeWebsiteToContactMe semitwist.com> writes:
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.
Mar 07 2018
prev sibling parent reply =?UTF-8?Q?S=c3=b6nke_Ludwig?= <sludwig+d outerproduct.org> writes:
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
Mar 07 2018
next sibling parent "Nick Sabalausky (Abscissa)" <SeeWebsiteToContactMe semitwist.com> writes:
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" ;)\
Mar 07 2018
prev sibling parent Steven Schveighoffer <schveiguy yahoo.com> writes:
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
Mar 07 2018
prev sibling parent reply bauss <jj_1337 live.dk> writes:
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.


 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.
Mar 06 2018
next sibling parent reply bauss <jj_1337 live.dk> writes:
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?
Mar 06 2018
parent reply bauss <jj_1337 live.dk> writes:
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?
Mar 06 2018
parent reply "Nick Sabalausky (Abscissa)" <SeeWebsiteToContactMe semitwist.com> writes:
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.
Mar 07 2018
parent reply bauss <jj_1337 live.dk> writes:
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?
Mar 07 2018
parent reply "Nick Sabalausky (Abscissa)" <SeeWebsiteToContactMe semitwist.com> writes:
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.
Mar 07 2018
parent reply bauss <jj_1337 live.dk> writes:
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 10.

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.
Mar 08 2018
next sibling parent reply "Nick Sabalausky (Abscissa)" <SeeWebsiteToContactMe semitwist.com> writes:
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.)




https://github.com/mysql-d/mysql-native/issues/170


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.
Mar 08 2018
parent bauss <jj_1337 live.dk> writes:
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 

 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.
Mar 08 2018
prev sibling parent reply Steven Schveighoffer <schveiguy yahoo.com> writes:
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 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
Mar 08 2018
parent reply bauss <jj_1337 live.dk> writes:
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?
Mar 08 2018
parent reply Steven Schveighoffer <schveiguy yahoo.com> writes:
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
Mar 08 2018
parent bauss <jj_1337 live.dk> writes:
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
Mar 08 2018
prev sibling parent reply "Nick Sabalausky (Abscissa)" <SeeWebsiteToContactMe semitwist.com> writes:
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.
Mar 07 2018
parent reply bauss <jj_1337 live.dk> writes:
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.
Mar 07 2018
parent reply bauss <jj_1337 live.dk> writes:
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.
Mar 07 2018
parent reply "Nick Sabalausky (Abscissa)" <SeeWebsiteToContactMe semitwist.com> writes:
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.)
Mar 07 2018
parent reply Bauss <jj_1337 live.dk> writes:
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
Mar 07 2018
parent "Nick Sabalausky (Abscissa)" <SeeWebsiteToContactMe semitwist.com> writes:
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.
Mar 07 2018