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.

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
Mar 02
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
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
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
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
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
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
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
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
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
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
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.

 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.
Mar 06
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
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
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
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
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
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
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.) 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.
Mar 08
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 
 #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.
Mar 08
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
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
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
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
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
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
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
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
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
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