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.

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.

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

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" ;)\

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.

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

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?

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.

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.

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.