问:
no matching function for call to sort()
I'm teaching myself through Programming: Principles and Practice Using C++ (2nd Edition) and I'm at the vectors section. I can copy and paste their code and it still won't work. My code is as follows.

```<code>#include "iostream"
#include "vector"
#include "algorithm"

using namespace std;

int main()
{
    cout << "Please enter the temperatures for the last five days\n";
    vector<double> temperatures;

    for(double temp; cin >> temp;){
        temperatures.push_back(temp);
    }
    double sum = 0;
    for (double temp : temperatures){
        sum += temp;
    }
    cout << "Mean temperature is " <<(sum / temperatures.size()) << endl;

    sort(temperatures);
    cout << "Median temperature is " << temperatures[temperatures.size()/2];
}
</code>```

Help is appreciated and an explanation is better.

答:
First of all, regarding the headers, the code from the book requires the Header "std_lib_facilities.h", which can be found at the support website for the book, and integration of the header in your project in the Appendix C of the book.

In the code, he did:
#include "std_lib_facilities.h" which is correct way for User defined Headers in the Project, but including Standard Library Headers by that way is completely wrong. Standard Library Headers must be included using the #include <...> notation. He, in his book, never included Standard Library Headers with #include "..."notation.

Now, regarding the actual question of sort(temperatures),
This version of sort() is available in his own header, "std_lib_facilities.h" to make it easy for beginners to use sort() without knowing the technicalities of Iterators at an early stage.

So, to use that version of sort, either #include "std_lib_facilities.h", or use the other versions of sort, one of them being:

```<code>sort(temperatures.begin(), temperatures.end());
</code>```

Title: no matching function for call to sort()

Body:
I'm teaching myself through Programming: Principles and Practice Using C++ (2nd Edition) and I'm at the vectors section. I can copy and paste their code and it still won't work. My code is as follows.

#include "iostream"
#include "vector"
#include "algorithm"

using namespace std;

int main()
{
    cout << "Please enter the temperatures for the last five days\n";
    vector<double> temperatures;

    for(double temp; cin >> temp;){
        temperatures.push_back(temp);
    }
    double sum = 0;
    for (double temp : temperatures){
        sum += temp;
    }
    cout << "Mean temperature is " <<(sum / temperatures.size()) << endl;

    sort(temperatures);
    cout << "Median temperature is " << temperatures[temperatures.size()/2];
}

Help is appreciated and an explanation is better.

Body:
First of all, regarding the headers, the code from the book requires the Header "std_lib_facilities.h", which can be found at the support website for the book, and integration of the header in your project in the Appendix C of the book.

In the code, he did:
#include "std_lib_facilities.h" which is correct way for User defined Headers in the Project, but including Standard Library Headers by that way is completely wrong. Standard Library Headers must be included using the #include <...> notation. He, in his book, never included Standard Library Headers with #include "..."notation.

Now, regarding the actual question of sort(temperatures),
This version of sort() is available in his own header, "std_lib_facilities.h" to make it easy for beginners to use sort() without knowing the technicalities of Iterators at an early stage.

So, to use that version of sort, either #include "std_lib_facilities.h", or use the other versions of sort, one of them being:

sort(temperatures.begin(), temperatures.end());


来源: stackexchange

问:
id showing undefined when trying to implement delete in angularjs
Trying to implement delete funtion in angularjs. The backend in asp.net is working fine. when i check with postman its working fine. now am trying to the front end for that. when i hit the delete button. its not getting the UId. its showing like this.

```<code>http://localhost:50802/api/User/BUser/[object%20Object]
</code>```

am totally new to angular. i dont know whether this angular code is correct or not.And another thing is i need to implement user confirmation popup window also. please help me...

angular controller.

```<code>$scope.DeleteUser = function (data, UId) {
        $http({
            method: "post",
            url: urls.api + 'User/BUser/' + UId,

            data: JSON.stringify(data)
        }).then(function (response) {
            alert(response.data);

        })
    };
</code>```

HTML

```<code> <tr ng-repeat="d in UserList">

                        <td>{{d.description}}</td>
                        <td>{{d.path}}</td>
                        <td><button ng-click="DeleteUser()">Delete </button></td>

                    </tr>
</code>```

here my Asp.net Controller

```<code>[HttpDelete]
        [Route("DeleteBanner/{UId}")]
        public async Task<IHttpActionResult> DeleteBanner(int UId)
        {
            int? result = await _service.DeleteBannerAsync(UId);

            return Ok();

        }
</code>```

答:
There are many patterns to follow. But let me answer your question first, then suggest a better pattern:
Your ng-click event is not taking any parameter, it should look like:

```<code><tr ng-repeat="d in UserList">
  ...
  <td><button ng-click="DeleteUser(d.UId)">Delete </button></td>
</tr>
</code>```

And since your .NET API Controller expects a parameter called UId as shown below,

```<code>[HttpPost]
[Route("BUser/{UId}")]
public async Task<IHttpActionResult> DeleteUser(int UId) {
//Implementation
}
</code>```

then in Angular, you must tag that parameter with that tag name: params: { UId: id }. Also, since you want to prompt the user to confirm the action, I'd suggest you to use Bootbox Js as follows:
    It's a small, powerful, easy to use javascript library. Just provide a link     to it on your index file.

```<code>$scope.DeleteUser = function (id) {
    var url = urls.api + 'User/BUser/' + id;
    bootbox.confirm("Are you sure you want to delete this user?", function (result) {
        if (result) {
            $http.post(url, { params: { UId: id } })
            .then(function (response) {
                alert(response.data);
            });
        }
    });
}
</code>```

Better Pattern: I would suggest controller-service pattern as follows:
The service would look like:

```<code>function deleteUser(id) {
    var url = urls.api + 'User/BUser/' + id;
    var deferred = $q.defer();
    $http.post(url, { params: { UId: id } })
        .then(success).catch(deferred.reject);
    return deferred.promise;

    function success(response) {
        var data = response && response.data;
        deferred.resolve(data);
    }
}
</code>```

Then in the controller:

```<code>$scope.DeleteUser = function (id) {
    bootbox.confirm("Are you sure you want to delete this user?", function (result) {
        if (result) {
            /*
             * You can use Angular blockUI to show progress here
            */
            return userService.deleteUser(id).then(function (response) {
                alert(response);
            }).catch(function (error) {
                alert(error);
            }).finally(function () {
                 //You can stop the blockUI instance here.
                //appBlockUI.stop();
            });
        }
    });
}
</code>```

That's just a tip for you to research in that line of thought.

ContentLicense: CC BY-SA 3.0

Body:
Trying to implement delete funtion in angularjs. The backend in asp.net is working fine. when i check with postman its working fine. now am trying to the front end for that. when i hit the delete button. its not getting the UId. its showing like this.

http://localhost:50802/api/User/BUser/[object%20Object]

am totally new to angular. i dont know whether this angular code is correct or not.And another thing is i need to implement user confirmation popup window also. please help me...

angular controller.

$scope.DeleteUser = function (data, UId) {
        $http({
            method: "post",
            url: urls.api + 'User/BUser/' + UId,

            data: JSON.stringify(data)
        }).then(function (response) {
            alert(response.data);

        })
    };

HTML

                        {{d.description}}
                        {{d.path}}
                        <button ng-click="DeleteUser()">Delete </button>

here my Asp.net Controller

[HttpDelete]
        [Route("DeleteBanner/{UId}")]
        public async Task<IHttpActionResult> DeleteBanner(int UId)
        {
            int? result = await _service.DeleteBannerAsync(UId);

            return Ok();

        }

Body:
There are many patterns to follow. But let me answer your question first, then suggest a better pattern:
Your ng-click event is not taking any parameter, it should look like:

<tr ng-repeat="d in UserList">
  ...
  <td><button ng-click="DeleteUser(d.UId)">Delete </button></td>
</tr>

And since your .NET API Controller expects a parameter called UId as shown below,

[HttpPost]
[Route("BUser/{UId}")]
public async Task<IHttpActionResult> DeleteUser(int UId) {
//Implementation
}

then in Angular, you must tag that parameter with that tag name: params: { UId: id }. Also, since you want to prompt the user to confirm the action, I'd suggest you to use Bootbox Js as follows:
    It's a small, powerful, easy to use javascript library. Just provide a link     to it on your index file.

$scope.DeleteUser = function (id) {
    var url = urls.api + 'User/BUser/' + id;
    bootbox.confirm("Are you sure you want to delete this user?", function (result) {
        if (result) {
            $http.post(url, { params: { UId: id } })
            .then(function (response) {
                alert(response.data);
            });
        }
    });
}

Better Pattern: I would suggest controller-service pattern as follows:
The service would look like:

function deleteUser(id) {
    var url = urls.api + 'User/BUser/' + id;
    var deferred = $q.defer();
    $http.post(url, { params: { UId: id } })
        .then(success).catch(deferred.reject);
    return deferred.promise;

    function success(response) {
        var data = response && response.data;
        deferred.resolve(data);
    }
}

Then in the controller:

$scope.DeleteUser = function (id) {
    bootbox.confirm("Are you sure you want to delete this user?", function (result) {
        if (result) {
            /*
             * You can use Angular blockUI to show progress here
            */
            return userService.deleteUser(id).then(function (response) {
                alert(response);
            }).catch(function (error) {
                alert(error);
            }).finally(function () {
                 //You can stop the blockUI instance here.
                //appBlockUI.stop();
            });
        }
    });
}

That's just a tip for you to research in that line of thought.


问:
iOS - Detecting touches in a UIView?
So I have a Subclass of UIView that is suppose to detect touches. The view detect touches only if the touches started inside the current view. When the touches start outside of the view and they move inside my custom view touchesMoved doesn't get called. Any solution to detect moving touches that have not started in the current view?</p>

```<code>@implementation MycustomView

- (void)touchesMoved:(NSSet *)touches withEvent:(UIEvent *)event
{
   // This only gets called if touches have started in the current View
}

@end
</code>```

答:
Try this....</p>

```<code>-(void)touchesMoved:(NSSet *)touches withEvent:(UIEvent *)event
{
    for(UITouch *touch in touches)
    {
        CGPoint touchPointFirstBtn = [touch locationInView:self.ChordView];
        if(CGRectContainsPoint(_btnC.frame, touchPointFirstBtn))
        {
            if (!_btnC.isHighlighted)
            {
                if(!Boolean)
                {
                    title = @"C";
                    [_tlbView reloadData];
                    NSLog(@"%@",@"touches C");

                }
                [_btnC setHighlighted:YES];
                Boolean = YES;

            }
        }
        else
        {
            [_btnC setHighlighted:NO];
            Boolean = NO;
        }
}
</code>```

Title: iOS - Detecting touches in a UIView?

Body:
So I have a Subclass of UIView that is suppose to detect touches. The view detect touches only if the touches started inside the current view. When the touches start outside of the view and they move inside my custom view touchesMoved doesn't get called. Any solution to detect moving touches that have not started in the current view?

@implementation MycustomView

- (void)touchesMoved:(NSSet *)touches withEvent:(UIEvent *)event
{
   // This only gets called if touches have started in the current View
}

@end

Body:
Try this....

-(void)touchesMoved:(NSSet *)touches withEvent:(UIEvent *)event
{
    for(UITouch *touch in touches)
    {
        CGPoint touchPointFirstBtn = [touch locationInView:self.ChordView];
        if(CGRectContainsPoint(_btnC.frame, touchPointFirstBtn))
        {
            if (!_btnC.isHighlighted)
            {
                if(!Boolean)
                {
                    title = @"C";
                    [_tlbView reloadData];
                    NSLog(@"%@",@"touches C");

                }
                [_btnC setHighlighted:YES];
                Boolean = YES;

            }
        }
        else
        {
            [_btnC setHighlighted:NO];
            Boolean = NO;
        }
}


问:
HTML/CSS – How to get a perfect size match between a contained resized-image and a parent container with Firefox and Internet Explorer?
I have an issue with Firefox and IE in the code below : Everything is working extremely well with Chrome and Safari, but in Firefox and IE each image container does not respect the dimensions of its contained image. The image is resized proportionally with "height" command ; that does work. But IE and Firefox seem to take as a "width" value for the container of the image the original width of the jpeg file. How could we get a perfect result as in Chrome and Safari (with the contained image and its parent-container getting the same width and height each other) ?

Here is the HTML code :

```<code><!-- Gallery -->
<div class="thegallery">
<div class="eachimage"><img src="images/image-1.jpg" /><div class="rollovertxt"><p>Caption</p></div><div class="rolloverbg"></div></div>
<div class="eachimage"><img src="images/image-2.jpg" /><div class="rollovertxt"><p>Caption</p></div><div class="rolloverbg"></div></div>
<div class="eachimage"><img src="images/image-3.jpg" /><div class="rollovertxt"><p>Caption</p></div><div class="rolloverbg"></div></div>
</div>
</code>```

Here is the Css code :

```<code>.thegallery {
position:absolute;
top:6em; bottom:4em;
left:50%;
white-space:nowrap;
background-color:#36F;
z-index:1;
}

.eachimage {
position:relative;
height:100%; /* Full height of div.gallery */
display: inline-block;
margin-right:1em;
background-color:#63F;
}

.eachimage img {
height:100%; /* Full height of div.eachimage */
width:auto;
}

.eachimage:hover .rollovertxt { visibility:visible; }
.eachimage:hover .rolloverbg { visibility:visible; }

.rollovertxt {
position: absolute;
left:0.5em;
top:0.5em;
width:160px;
white-space:normal;
color:#FFF;
visibility:hidden;
z-index:3;
background-color:#F0F;
}

.rolloverbg {
position: absolute;
left:0;
top:0;
width:100%;
height:100%;
visibility:hidden;
z-index:2;
filter: alpha(opacity=50);
opacity: 0.5;
background-color:#000;
}
</code>```

答: Problem fixed. It seems the problem was coming from the fact Firefox and IE do not process correctly (as Chrome and Safari would do) a relative "height" of an element when you just give these two browsers an "em" top and bottom positioning of that element. I fixed the problem by adding a calc() function (height = 100% - ((top-position expressed in "em")+(bottom-position expressed in "em"))).</p>

Body:
I have an issue with Firefox and IE in the code below : Everything is working extremely well with Chrome and Safari, but in Firefox and IE each image container does not respect the dimensions of its contained image. The image is resized proportionally with "height" command ; that does work. But IE and Firefox seem to take as a "width" value for the container of the image the original width of the jpeg file. How could we get a perfect result as in Chrome and Safari (with the contained image and its parent-container getting the same width and height each other) ?

Here is the HTML code :

Caption

Caption

Caption

Here is the Css code :

.thegallery {
position:absolute;
top:6em; bottom:4em;
left:50%;
white-space:nowrap;
background-color:#36F;
z-index:1;
}

.eachimage {
position:relative;
height:100%; /* Full height of div.gallery */
display: inline-block;
margin-right:1em;
background-color:#63F;
}

.eachimage img {
height:100%; /* Full height of div.eachimage */
width:auto;
}

.eachimage:hover .rollovertxt { visibility:visible; }
.eachimage:hover .rolloverbg { visibility:visible; }

.rollovertxt {
position: absolute;
left:0.5em;
top:0.5em;
width:160px;
white-space:normal;
color:#FFF;
visibility:hidden;
z-index:3;
background-color:#F0F;
}

.rolloverbg {
position: absolute;
left:0;
top:0;
width:100%;
height:100%;
visibility:hidden;
z-index:2;
filter: alpha(opacity=50);
opacity: 0.5;
background-color:#000;
}


问:
how to print reverse number pattern triangle in java
This is my code:

```<code>for (int i = 4; i >= 1; i--) {
    for (int j = 1; j < i; j++) {
        System.out.print(" ");
    }
    for (int k = i; k <= 4; k++) {
        System.out.print(k+"");
    }
    System.out.println();
}
</code>```

Current output:

```<code>   4
  34
 234
1234
</code>```

Desired output:

```<code>   1
  21
 321
4321
</code>```

What changes are necessary in order for me to get the desired output as shown above?

答:
Your loops are incorrect, you can refer the below code with inline comments:</p>

```<code>for (int i = 1; i <= 4; i++) { //iterate from 1 to 4
    //Loop from i+1 to insert spaces first
    for (int j = i+1; j <= 4; j++) {
        System.out.print(" ");
    }
    //Loop from i to insert the number next to each other
    for (int j = i; j >= 1; j--) {
        System.out.print(j);
    }
    System.out.println(); //insert a new line
}
</code>```

Body:
This is my code:

for (int i = 4; i >= 1; i--) {
    for (int j = 1; j < i; j++) {
        System.out.print(" ");
    }
    for (int k = i; k <= 4; k++) {
        System.out.print(k+"");
    }
    System.out.println();
}

Current output:

   4
  34
 234
1234

Desired output:

   1
  21
 321
4321

What changes are necessary in order for me to get the desired output as shown above?

Body:
Your loops are incorrect, you can refer the below code with inline comments:

for (int i = 1; i <= 4; i++) { //iterate from 1 to 4
    //Loop from i+1 to insert spaces first
    for (int j = i+1; j <= 4; j++) {
        System.out.print(" ");
    }
    //Loop from i to insert the number next to each other
    for (int j = i; j >= 1; j--) {
        System.out.print(j);
    }
    System.out.println(); //insert a new line
}


问:
Visual studio 2017 installer won't run after extracting
visual studio 2017 community.exe won't run after extracting to the temp.

OS: windows 10 x64bit

Setup : community version

in the temp folder bootstrapper log says,

Beginning of the log. Start Time: 3/26/2017 1:14:54 AM
VisualStudio Bootstrapper:3/26/2017 1:14:54 AM: Current Optin root path does not exists

VisualStudio Bootstrapper:3/26/2017 1:14:55 AM: Commandline arguments =

and the dd_vs_community_decompression_log says

[3/26/2017, 11:17:47] === Logging started: 2017/03/26 11:17:47 ===

[3/26/2017, 11:17:47] Executable: C:\Users\Sameera\Downloads\Programs\vs_community.exe v15.0.26228.0

[3/26/2017, 11:17:47] --- logging level: standard ---

[3/26/2017, 11:17:47] Directory
'C:\Users\Sameera\AppData\Local\Temp\4ceac4b7b9cd9fdf2489526c66\' has been selected for file extraction

[3/26/2017, 11:17:48] Extracting files to: C:\Users\Sameera\AppData\Local\Temp\4ceac4b7b9cd9fdf2489526c66\

[3/26/2017, 11:17:48] Extraction took 360 milliseconds

[3/26/2017, 11:17:48] Executing extracted package: 'vs_bootstrapper_d15\vs_setup_bootstrapper.exe ' with commandline ' '

[3/26/2017, 11:18:10] The entire Box execution exiting with result code: 0x0

[3/26/2017, 11:18:10] Launched extracted application exiting with result code: 0xc000000d

[3/26/2017, 11:18:10] === Logging stopped: 2017/03/26 11:18:10 ===

can't find proper solution..

答:
It's the same answer as in question visual studio 2017 install break : it not get start just in case you don't observe the other question.

I had the same Problem on two different Pc's (both win10) and the only thing that worked for me, was reinstalling Windows's and rerun the visual studio installer. I know, that's not a good, fast or easy solution, but it works.


问:
Regex. Find paragraph containing some word
In text like this:

1 bla bla bla bla bla

2 bla bla bla bla TEXT bla

3 bla bla bla bla bla

4 bla bla bla bla TEXT bla

5 bla bla bla bla bla

I have to find paragraphs (between p tags) that contain string "TEXT".

I tried
.*?(TEXT).*?<\/p>

and I tried
(?!
).*?(TEXT).*?<\/p>

But it doesnt solve the problem.

答:
Since it is a string, (said by @Rajesh), just create a div element and appent to it.

get All the p tags using querySelectorAll and then use forEach function.

Check the innerHTML for /TEXT/ and if found, push it into array.

In the below program, the array a contains the 2 matching tags

var str="
1 bla bla bla bla bla

2 bla bla bla bla TEXT bla

3 bla bla bla bla bla

4 bla bla bla bla TEXT bla

5 bla bla bla bla bla
";
var div=document.createElement("div");
div.innerHTML=str;
var a=[];
div.querySelectorAll("p").forEach(x=>{if(/TEXT/.test(x.innerHTML)) a.push(x);});
console.log(a);```
</div>
</div>
</p>

<p>If you don't want the <code><p></p></code> tags, just push the <code>textContent</code></p>

<p><div class="snippet" data-lang="js" data-hide="false" data-console="true" data-babel="false">
<div class="snippet-code">
<pre class="snippet-code-js lang-js prettyprint-override"><code>var str="<p>1 bla bla <em>bla</em> bla bla</p><p>2 bla bla <em>bla</em> bla TEXT bla</p><p>3 bla bla <em>bla</em> bla bla</p><p>4 bla bla <em>bla</em> bla TEXT bla</p><p>5 bla bla <em>bla</em> bla bla</p>";
var div=document.createElement("div");
div.innerHTML=str;
var a=[];
div.querySelectorAll("p").forEach(x=>{if(/TEXT/.test(x.innerHTML)) a.push(x.textContent);});
console.log(a);</code>```

Body:
Since it is a string, (said by @Rajesh), just create a div element and appent to it.

get All the p tags using querySelectorAll and then use forEach function.

Check the innerHTML for /TEXT/ and if found, push it into array.

In the below program, the array a contains the 2 matching tags

var str="<p>1 bla bla <em>bla</em> bla bla</p><p>2 bla bla <em>bla</em> bla TEXT bla</p><p>3 bla bla <em>bla</em> bla bla</p><p>4 bla bla <em>bla</em> bla TEXT bla</p><p>5 bla bla <em>bla</em> bla bla</p>";
var div=document.createElement("div");
div.innerHTML=str;
var a=[];
div.querySelectorAll("p").forEach(x=>{if(/TEXT/.test(x.innerHTML)) a.push(x);});
console.log(a);

If you don't want the <p></p> tags, just push the textContent

var str="<p>1 bla bla <em>bla</em> bla bla</p><p>2 bla bla <em>bla</em> bla TEXT bla</p><p>3 bla bla <em>bla</em> bla bla</p><p>4 bla bla <em>bla</em> bla TEXT bla</p><p>5 bla bla <em>bla</em> bla bla</p>";
var div=document.createElement("div");
div.innerHTML=str;
var a=[];
div.querySelectorAll("p").forEach(x=>{if(/TEXT/.test(x.innerHTML)) a.push(x.textContent);});
console.log(a);


问:
Get joined reulst from 2 table and when value not exist any of them, fill with empty value
I have tables clients and partners

Table clients

```<code>company_id | name
------------------
11         | UA prods.
12         | CI corp.
13         | UAC
</code>```

Table partners

```<code>company_id | name
------------------
11         | UA prods.
12         | CI corp.
20         | BDP Group
30         | UFF
</code>```

I need get all company names from both table. When company exists in both tables, then theyr names, when doesn't exist, just leave it empty.

I tried left joins, inner joins but it gives me not what I want.

I want result like this

```<code>clients    | partners
--------------------
UA prods.  | UA prods.
CI corp.   | CI corp.
UAC        |
           | BDP Group
           | UFF
</code>```

答:
Use this:</p>

```<code>(select clients.name as clients, partners.name as partners from clients
LEFT JOIN partners
ON clients.company_id = partners.company_id)
UNION  ALL
(select clients.name as clients, partners.name as partners from partners
LEFT JOIN clients
ON clients.company_id = partners.company_id
WHERE clients.company_id IS NULL)
</code>```

Body:
I have tables clients and partners

Table clients

company_id | name
------------------
11         | UA prods.
12         | CI corp.
13         | UAC

Table partners

company_id | name
------------------
11         | UA prods.
12         | CI corp.
20         | BDP Group
30         | UFF

I need get all company names from both table. When company exists in both tables, then theyr names, when doesn't exist, just leave it empty.

I tried left joins, inner joins but it gives me not what I want.

I want result like this

clients    | partners
--------------------
UA prods.  | UA prods.
CI corp.   | CI corp.
UAC        |
           | BDP Group
           | UFF

Body:
Use this:

(select clients.name as clients, partners.name as partners from clients
LEFT JOIN partners
ON clients.company_id = partners.company_id)
UNION  ALL
(select clients.name as clients, partners.name as partners from partners
LEFT JOIN clients
ON clients.company_id = partners.company_id
WHERE clients.company_id IS NULL)


问:
Computer crashes while training with tensorflow on multiple GPUs
My servers crash and automatically restart while training with tensorflow using more than one GPUs.

My task is like:

```<code>CUDA_VISIBLE_DEVICES=0 python train.py [LSTM training]
CUDA_VISIBLE_DEVICES=1 python train.py [Another LSTM training]
</code>```

When the tasks are both running, the server crashes and automatically restarts. I tried with different servers, but all servers behave the same.

My hardware configuration:

Dell T7610 Server

2* E5-2609 CPU

1* Nvidia GTX 1080 (Inno 3D Foundation Edition)

1* Nvidia GTX 1080Ti (Gigabyte Foundation Edition)

64G memory

My software configuration:

Ubuntu 14.04 LTS (Linux 3.13.0-87-generic)

Nvidia Driver version 378.13 (nvidia-378 from ppa:graphics-drivers)

CUDA 8.0 (CuDNN 5.1.5)

Python 2.7.13 |Anaconda 4.3.0 (64-bit)

tensorflow-gpu-1.0.1 (from pip install)

The PSU has enough capacity for the graphics cards. And from memtest results, it's not a memory-related problem. There are not any syslog and kern.log log that helps to locate the problem.

I am aware of a similar question. However, a workaround that downgrades the Linux kernel is unacceptable since I share the machines with lab folks.

答:
Now I've solved the problem. I decided to answer my own question.

It's been a power issue. When the GPU draws power more than the PSU can provide, the computer restarts. Although the PSU has sufficient capacity, a 18A PCIe cable has a power limit of 217W.

My workaround is to use two PCIe power cable to power the GTX1080 with a double 6-pins to 8-pins connector.

Body:
My servers crash and automatically restart while training with tensorflow using more than one GPUs.

My task is like:

CUDA_VISIBLE_DEVICES=0 python train.py [LSTM training]
CUDA_VISIBLE_DEVICES=1 python train.py [Another LSTM training]

When the tasks are both running, the server crashes and automatically restarts. I tried with different servers, but all servers behave the same.

My hardware configuration:

Dell T7610 Server

2* E5-2609 CPU

1* Nvidia GTX 1080 (Inno 3D Foundation Edition)

1* Nvidia GTX 1080Ti (Gigabyte Foundation Edition)

64G memory

My software configuration:

Ubuntu 14.04 LTS (Linux 3.13.0-87-generic)

Nvidia Driver version 378.13 (nvidia-378 from ppa:graphics-drivers)

CUDA 8.0 (CuDNN 5.1.5)

Python 2.7.13 |Anaconda 4.3.0 (64-bit)

tensorflow-gpu-1.0.1 (from pip install)

The PSU has enough capacity for the graphics cards. And from memtest results, it's not a memory-related problem. There are not any syslog and kern.log log that helps to locate the problem.

I am aware of a similar question. However, a workaround that downgrades the Linux kernel is unacceptable since I share the machines with lab folks.


问:
Generate GUID in Windows with batch file
How can I generate a GUID in a batch file running using the commandline in Windows?</p>

答:
1.Create a file named  myuuid.vbs with the content

```<code>set obj = CreateObject("Scriptlet.TypeLib")
WScript.StdOut.WriteLine Mid(obj.GUID, 2, 36)
</code>```

2.goto command prompt

cscript //NoLogo myuuid.vbs

Using  JAVA code

```<code>    UUID uuid = UUID.randomUUID();
    String randomUUIDString = uuid.toString();
</code>```

Body:
1.Create a file named  myuuid.vbs with the content

set obj = CreateObject("Scriptlet.TypeLib")
WScript.StdOut.WriteLine Mid(obj.GUID, 2, 36)

2.goto command prompt

cscript //NoLogo myuuid.vbs

Using  JAVA code

    UUID uuid = UUID.randomUUID();
    String randomUUIDString = uuid.toString();


问:
Can't connect to SQL Server from Node
I use these versions:

```<code>node v4.7.3
mssql v3.2.1
</code>```

I use mssql module to connect server, the doc for mssql is here.

SQL server is run on Windows, I run the following command in Windows system to confirm the status of SQL server:

```<code>> netstat -ano | findstr 1433
 TCP    0.0.0.0:1433           0.0.0.0:0              LISTENING       1452
 TCP    127.0.0.1:1433         127.0.0.1:50093        ESTABLISHED     1452
 TCP    127.0.0.1:50093        127.0.0.1:1433         ESTABLISHED     3636
</code>```

Then I use nc to test the port for server:

```<code>$ nc -z msserver 1433
Connection to msserver port 1433 [tcp/ms-sql-s] succeeded!
</code>```

which indicates that I can connect the port.

However, I can't connect the server by mssql module with the following config:

```<code>{
  user: 'sa',
  password: 'pwd',
  server: 'msserver',
  port: '1433',
  database: 'dbname',
  pool: {
    max: 10,
    min: 0,
    idleTimeoutMillis: 30000,
  },
  requestTimeout: 0,
  connectionTimeout: 15000,
  debug: true,
}
</code>```

I use debug: true to get the debug info as follows:

```<code>connected to msserver:1433

Sent
  type:0x12(PRELOGIN), status:0x01(EOM), length:0x002F, spid:0x0000, packetId:0x01, window:0x00
  PreLogin - version:0.0.0.1 1, encryption:0x02(NOT_SUP), instopt:0x00, threadId:0x00000000, mars:0x00(OFF)
State change: Connecting -> SentPrelogin
socket ended
State change: SentPrelogin -> Final
connection to msserver:1433 closed
State is already Final
</code>```

My code is here:

```<code>mssql.connect(config.mssql).then(function() {
  console.log('connected');
  new mssql.Request().query('select top 1 * from qmx.dbo.QMXStockPool order by ID desc', (err, records) => {
    console.log(err, records);
  });
}).catch(function(err) {
  console.log(err);
});
</code>```

no output, no error, finished soon.

答:
From the comments, it appears that this error was logged in SQL Server's Error log file:

Could not connect because the maximum number of ‘4’ user connections has already been reached.

This means that someone explicitly set a maximum concurrent connection limit. The default is unlimited connections.

The solution is to change the value back to the default 0 = unmlimited. The GUI way is to open SSMS, go to the Connections tab in the server's properties and set Maximum number of concurrent connections back to 0.

The other option, described in Error while connecting to SQL Server – “Could not connect because the maximum number of ‘1’ user connections has already been reached.” and numerous other articles, is to connect from the command line using sqlcmd and change the configuration setting :

```<code>sp_configure 'show advanced options', 1;
go

reconfigure
go

sp_configure 'user connections', 0
go

reconfigure
go
</code>```

Title: Can't connect to SQL Server from Node

Body:
I use these versions:

node v4.7.3
mssql v3.2.1

I use mssql module to connect server, the doc for mssql is here.

SQL server is run on Windows, I run the following command in Windows system to confirm the status of SQL server:

> netstat -ano | findstr 1433
 TCP    0.0.0.0:1433           0.0.0.0:0              LISTENING       1452
 TCP    127.0.0.1:1433         127.0.0.1:50093        ESTABLISHED     1452
 TCP    127.0.0.1:50093        127.0.0.1:1433         ESTABLISHED     3636

Then I use nc to test the port for server:

$ nc -z msserver 1433
Connection to msserver port 1433 [tcp/ms-sql-s] succeeded!

which indicates that I can connect the port.

However, I can't connect the server by mssql module with the following config:

{
  user: 'sa',
  password: 'pwd',
  server: 'msserver',
  port: '1433',
  database: 'dbname',
  pool: {
    max: 10,
    min: 0,
    idleTimeoutMillis: 30000,
  },
  requestTimeout: 0,
  connectionTimeout: 15000,
  debug: true,
}

I use debug: true to get the debug info as follows:

connected to msserver:1433

Sent
  type:0x12(PRELOGIN), status:0x01(EOM), length:0x002F, spid:0x0000, packetId:0x01, window:0x00
  PreLogin - version:0.0.0.1 1, encryption:0x02(NOT_SUP), instopt:0x00, threadId:0x00000000, mars:0x00(OFF)
State change: Connecting -> SentPrelogin
socket ended
State change: SentPrelogin -> Final
connection to msserver:1433 closed
State is already Final

My code is here:

mssql.connect(config.mssql).then(function() {
  console.log('connected');
  new mssql.Request().query('select top 1 * from qmx.dbo.QMXStockPool order by ID desc', (err, records) => {
    console.log(err, records);
  });
}).catch(function(err) {
  console.log(err);
});

no output, no error, finished soon.

Body:
From the comments, it appears that this error was logged in SQL Server's Error log file:

Could not connect because the maximum number of ‘4’ user connections has already been reached.

This means that someone explicitly set a maximum concurrent connection limit. The default is unlimited connections.

The solution is to change the value back to the default 0 = unmlimited. The GUI way is to open SSMS, go to the Connections tab in the server's properties and set Maximum number of concurrent connections back to 0.

The other option, described in Error while connecting to SQL Server – “Could not connect because the maximum number of ‘1’ user connections has already been reached.” and numerous other articles, is to connect from the command line using sqlcmd and change the configuration setting :

sp_configure 'show advanced options', 1;
go

reconfigure
go

sp_configure 'user connections', 0
go

reconfigure
go


问:
How can I loop through the JSON response object to remove the property labels?
```<code>// GET - Get login settings
router.route('/login/settings').get((req, res) => {
  let settings =[];
  pool.connect((err, client, done) => {
    client.query('SELECT * FROM public.settings', (err, result) => {
      settings = result.rows;
      res.json({
          settings,
      });
    });
  });
});
</code>```

I have this code in my API routing that returns the following JSON object:

```<code>{
  "settings": [
    {
      "key": "TwoFactorAuth",
      "value": "false"
    },
    {
      "key": "sessionTimeout",
      "value": "200"
    }
  ]
}
</code>```

How can I write a loop in the routing code to change the JSON response object to look like this:

```<code>{
  "settings": [
    {
      "TwoFactorAuth": "false"
    },
    {
      "sessionTimeout": "200"
    }
  ]
}
</code>```

答:
Try this working snippet :

var jsonObj = {
  "settings": [
    {
      "key": "TwoFactorAuth",
      "value": "false"
    },
    {
      "key": "sessionTimeout",
      "value": "200"
    }
  ]
};

var res = jsonObj.settings.map(function(item) {
  var x={};
  x[item.key] = item.value;
  return x;
});

console.log(res);```

Body:
// GET - Get login settings
router.route('/login/settings').get((req, res) => {
  let settings =[];
  pool.connect((err, client, done) => {
    client.query('SELECT * FROM public.settings', (err, result) => {
      settings = result.rows;
      res.json({
          settings,
      });
    });
  });
});

I have this code in my API routing that returns the following JSON object:

{
  "settings": [
    {
      "key": "TwoFactorAuth",
      "value": "false"
    },
    {
      "key": "sessionTimeout",
      "value": "200"
    }
  ]
}

How can I write a loop in the routing code to change the JSON response object to look like this:

{
  "settings": [
    {
      "TwoFactorAuth": "false"
    },
    {
      "sessionTimeout": "200"
    }
  ]
}


问:
Resizable UILabel which scrolls across the screen
Right, I'm trying to get a label with text in it (done already obviously), which scrolls across the screen.

The text that is input into the label is done by a UITextField and a UIButton. This updates fine.

But I'm trying to get the UILabel to resize accordingly to the amount of text input, so that the WHOLE lot of text scrolls across the screen.

This is the code I have at the moment for the scrolling label:

```<code>[lblMessage setText: txtEnter.text];

CABasicAnimation *scrollText;

scrollText=[CABasicAnimation animationWithKeyPath:@"position.x"];
scrollText.duration = 3.0;
scrollText.repeatCount = 10000;
scrollText.autoreverses = NO;
scrollText.fromValue = [NSNumber numberWithFloat:500];
scrollText.toValue = [NSNumber numberWithFloat:-120.0];

[[lblMessage layer] addAnimation:scrollText forKey:@"scrollTextKey"];
</code>```

The problem is, sometimes is starts scrolling in the middle of the screen, and sometimes vanishes before it has fully gone acrosss.

It also cuts of text due to the label being one size.. I don't know how to change this.

Thanks in advance.

Dom

答:
The label resize worked great thanks!

Now, i've put the label into the scrollview and i've got that showing up.. but I'm now not sure of the exact animations to add to that. I tried some of the ones on the link you gave me, but they're not working.

EDIT: Nevermind, I've got it all working now.

CGPointMake(x, x) is what i needed for the contentOffset.

ContentLicense: CC BY-SA 2.5

Body:
Right, I'm trying to get a label with text in it (done already obviously), which scrolls across the screen.

The text that is input into the label is done by a UITextField and a UIButton. This updates fine.

But I'm trying to get the UILabel to resize accordingly to the amount of text input, so that the WHOLE lot of text scrolls across the screen.

This is the code I have at the moment for the scrolling label:

[lblMessage setText: txtEnter.text];

CABasicAnimation *scrollText;

scrollText=[CABasicAnimation animationWithKeyPath:@"position.x"];
scrollText.duration = 3.0;
scrollText.repeatCount = 10000;
scrollText.autoreverses = NO;
scrollText.fromValue = [NSNumber numberWithFloat:500];
scrollText.toValue = [NSNumber numberWithFloat:-120.0];

[[lblMessage layer] addAnimation:scrollText forKey:@"scrollTextKey"];

The problem is, sometimes is starts scrolling in the middle of the screen, and sometimes vanishes before it has fully gone acrosss.

It also cuts of text due to the label being one size.. I don't know how to change this.

Thanks in advance.

Dom

LastEditorDisplayName: Chris Hanson


问:
Adding consecutive x values to a list
Suppose I have a list with items [123, 124, 125, ... 9820] and from that list I want to append to a second list with a string of every 8 items separated by a space up until the end. For example the list would have:

["123 124 125 126 127 128 129 130", "131, 132, 133, 134, 135, 136, 137, 138",..] etc.

What is the best way to do this in python? I have tried a naive solution of looping from 123 to 9820 but this takes way too much runtime and times out some of my simple tests I have set up. Are there any functions that would be useful to me?

Title: Adding consecutive x values to a list

Body:
We have to jump by 8 index to get next item from items list.

Demo

Consider items list from 1 to 999 numbers, Length of items list is 999.

Then use for loop with range function to jump by 8 index in a items list.

Use append method of string to get final result.

code:

>>> items = range(1, 1000)
>>> len(items)
999
>>> output_str = ""
>>> for i in range(0, 999, 8):
...    output_str += " " + str(items[i])
...
>>> output_str.strip()
'1 9 17 25 33 41 49 57 65 73 81 89 97 105 113 121 129 137 145 153 161 169 177 185 193 201 209 217 225 233 241 249 257 265 273 281 289 297 305 313 321 329 337 345 353 361 369 377 385 393 401 409 417 425 433 441 449 457 465 473 481 489 497 505 513 521 529 537 545 553 561 569 577 585 593 601 609 617 625 633 641 649 657 665 673 681 689 697 705 713 721 729 737 745 753 761 769 777 785 793 801 809 817 825 833 841 849 857 865 873 881 889 897 905 913 921 929 937 945 953 961 969 977 985 993'
>>>


问:
Insert into temporary table query
I'm looking to optimise a query that takes an excessive amount of time to run (around 1.5 hours), and through looking at the execution plan one of the biggest drains seems to be inserting the data from the main query into a temporary table. When I insert the data into a temporary table there is the following process :

Process (cost %)

```<code>Compute Scalar (0%) <-- Stream Aggregate (0%) <-- Sort(0%) <-- Table Spool (eager spool) (10%)
</code>```

Which makes sense, but what doesn't to me is that this appears 9 times?

I can include the query / index information if this would help, but the query itself is quite long and I don't want to make this question into an essay!

Thanks,
Davin.

Edit to include query - sorry for the length!

```IF object_id('tempdb..#category') IS NOT NULL DROP TABLE #category
CREATE TABLE #category
(
contact_id NVARCHAR(255)
,donor_class NVARCHAR(255)
,payment_type NVARCHAR(255)
,donation_type NVARCHAR(255)
,approach_channel NVARCHAR(255)
,count1987 INT
,sum1987 DECIMAL(18,3)
,count1988 INT
,sum1988 DECIMAL(18,3)
,count1989 INT
,sum1989 DECIMAL(18,3)
,count1990 INT
,sum1990 DECIMAL(18,3)
,count1991 INT
,sum1991 DECIMAL(18,3)
,count1992 INT
,sum1992 DECIMAL(18,3)
,count1993 INT
,sum1993 DECIMAL(18,3)
,count1994 INT
,sum1994 DECIMAL(18,3)
,count1995 INT
,sum1995 DECIMAL(18,3)
,count1996 INT
,sum1996 DECIMAL(18,3)
,count1997 INT
,sum1997 DECIMAL(18,3)
,count1998 INT
,sum1998 DECIMAL(18,3)
,count1999 INT
,sum1999 DECIMAL(18,3)
,count2000 INT
,sum2000 DECIMAL(18,3)
,count2001 INT
,sum2001 DECIMAL(18,3)
,count2002 INT
,sum2002 DECIMAL(18,3)
,count2003 INT
,sum2003 DECIMAL(18,3)
,count2004 INT
,sum2004 DECIMAL(18,3)
,count2005 INT
,sum2005 DECIMAL(18,3)
,count2006 INT
,sum2006 DECIMAL(18,3)
,count2007 INT
,sum2007 DECIMAL(18,3)
,count2008 INT
,sum2008 DECIMAL(18,3)
,count2009 INT
,sum2009 DECIMAL(18,3)
,count2010 INT
,sum2010 DECIMAL(18,3)
,category05 NVARCHAR(255)
,category06 NVARCHAR(255)
,category07 NVARCHAR(255)
,category08 NVARCHAR(255)
,category09 NVARCHAR(255)
,category10 NVARCHAR(255)
,[1987] NVARCHAR(4)
,[1988] NVARCHAR(4)
,[1989] NVARCHAR(4)
,[1990] NVARCHAR(4)
,[1991] NVARCHAR(4)
,[1992] NVARCHAR(4)
,[1993] NVARCHAR(4)
,[1994] NVARCHAR(4)
,[1995] NVARCHAR(4)
,[1996] NVARCHAR(4)
,[1997] NVARCHAR(4)
,[1998] NVARCHAR(4)
,[1999] NVARCHAR(4)
,[2000] NVARCHAR(4)
,[2001] NVARCHAR(4)
,[2002] NVARCHAR(4)
,[2003] NVARCHAR(4)
,[2004] NVARCHAR(4)
,[2005] NVARCHAR(4)
,[2006] NVARCHAR(4)
,[2007] NVARCHAR(4)
,[2008] NVARCHAR(4)
,[2009] NVARCHAR(4)
,[2010] NVARCHAR(4)
)
CREATE INDEX #idx_category ON #category (contact_id)

;WITH complete_finances as
(
(
SELECT
don.supporter_id AS contact_id
,don.id AS gift_id
,YEAR(don.date_received) AS calendar_year
,YEAR(don.date_received) - CASE WHEN MONTH(don.date_received) < 4 THEN 1 ELSE 0 END AS financial_year
,don.date_received AS date_received
,don.event_id AS event_id
,SUM(CASE   WHEN don.gift_aid_status <> 4 THEN don.value_gross * ((dfa.percentage) / 100)
            WHEN don.gift_aid_status = 4 AND don.value_net > don.value_gross
            AND don.value_net <> 0       THEN don.value_net  * ((dfa.percentage) / 100)
            ELSE don.value_gross  * ((dfa.percentage) / 100)
            END
        )   AS donation_value
,(CASE  WHEN don.payment_method IN (1, 2, 10, 11, 12, 200000, 200001, 200002, 200003, 200004, 200007, 200008, 200009 --single payments
                                            ,7,13,14) -- single payment refunds
                    THEN 'single'
        WHEN don.payment_method IN (3,4,5,9,200006)
                     THEN 'regular'
        ELSE 'other' END) AS type1
,CASE WHEN dfa.fund_id IN --all emergency funds
    (
    select f.id
    FROM _audit a
    INNER JOIN _fund f ON a.article_id = f.id
    WHERE a.entity_name = 'Fund'
    AND a.Changes LIKE
    '%finance_code2%OldValue>3%'
    UNION
    select
    id
    FROM _fund
    WHERE (finance_code2 LIKE '3%'
    OR finance_code2 LIKE '9%')
    AND finance_code2 IS NOT NULL
    ) THEN 'Emergency' else 'Non-emergency' end as type2
FROM donation don WITH (nolock)
INNER JOIN donation_fund_allocation dfa WITH (nolock) ON dfa.donation_id = don.id
WHERE don.supporter_id IS NOT NULL
AND don.status = 4 -- posted donations only
AND don.value_gross <> 0 -- to include refunds as well
GROUP BY don.supporter_id
,don.id
,don.date_received
,don.event_id
,don.payment_method
,dfa.fund_id
)
UNION ALL
(
SELECT
caw.contact_id AS contact_id
,caw.id AS gift_id
,YEAR(caw.bank_date) AS calendar_year
,YEAR(caw.bank_date) - CASE WHEN MONTH(caw.bank_date) < 4 THEN 1 ELSE 0 END AS financial_year
,caw.bank_date AS date_received
,caw.event AS event_id
,(CASE      WHEN  caw.gift_aid_status <> 4 THEN caw.amount_exc_gift_aid
            WHEN  caw.gift_aid_status = 4   THEN amount_inc_gift_aid
            END
        )   AS donation_value
,'CAW gift' AS type1
,'Non-emergency' AS type2
FROM gifts caw WITH (nolock)
WHERE caw.contact_id IS NOT NULL
AND caw.amount_exc_gift_aid <> 0
)
)
,category AS
(
SELECT
cmf.contact_id AS contact_id
,lfu.description AS donor_class
,ISNULL(cmf.type1,'Total') AS payment_type
,ISNULL(lac.description, 'Total') AS approach_channel
--,ISNULL(CASE  WHEN type1 = 'CAW gift' THEN 'CAW Gift'
--      WHEN type1 =  'Single'  THEN 'Non CAW gift'
--      WHEN type1 =  'Regular' THEN 'Non CAW gift'
--      WHEN type1 =  'Other'   THEN 'Non CAW gift'
--      END,'Total') AS payment_type2
,ISNULL(cmf.type2,'Total') AS donation_type
,SUM(CASE WHEN cmf.financial_year = '1987' THEN 1 ELSE 0 END)                   AS count1987
,SUM(CASE WHEN cmf.financial_year = '1988' THEN cmf.donation_value ELSE 0 END)  AS sum1987
,SUM(CASE WHEN cmf.financial_year = '1988' THEN 1 ELSE 0 END)               AS count1988
,SUM(CASE WHEN cmf.financial_year = '1988' THEN cmf.donation_value ELSE 0 END)  AS sum1988
,SUM(CASE WHEN cmf.financial_year = '1989' THEN 1 ELSE 0 END)               AS count1989
,SUM(CASE WHEN cmf.financial_year = '1989' THEN cmf.donation_value ELSE 0 END)  AS sum1989
,SUM(CASE WHEN cmf.financial_year = '1990' THEN 1 ELSE 0 END)               AS count1990
,SUM(CASE WHEN cmf.financial_year = '1990' THEN cmf.donation_value ELSE 0 END)  AS sum1990
,SUM(CASE WHEN cmf.financial_year = '1991' THEN 1 ELSE 0 END)               AS count1991
,SUM(CASE WHEN cmf.financial_year = '1991' THEN cmf.donation_value ELSE 0 END)  AS sum1991
,SUM(CASE WHEN cmf.financial_year = '1992' THEN 1 ELSE 0 END)               AS count1992
,SUM(CASE WHEN cmf.financial_year = '1992' THEN cmf.donation_value ELSE 0 END)  AS sum1992
,SUM(CASE WHEN cmf.financial_year = '1993' THEN 1 ELSE 0 END)               AS count1993
,SUM(CASE WHEN cmf.financial_year = '1993' THEN cmf.donation_value ELSE 0 END)  AS sum1993
,SUM(CASE WHEN cmf.financial_year = '1994' THEN 1 ELSE 0 END)               AS count1994
,SUM(CASE WHEN cmf.financial_year = '1994' THEN cmf.donation_value ELSE 0 END)  AS sum1994
,SUM(CASE WHEN cmf.financial_year = '1995' THEN 1 ELSE 0 END)               AS count1995
,SUM(CASE WHEN cmf.financial_year = '1995' THEN cmf.donation_value ELSE 0 END)  AS sum1995
,SUM(CASE WHEN cmf.financial_year = '1996' THEN 1 ELSE 0 END)               AS count1996
,SUM(CASE WHEN cmf.financial_year = '1996' THEN cmf.donation_value ELSE 0 END)  AS sum1996
,SUM(CASE WHEN cmf.financial_year = '1997' THEN 1 ELSE 0 END)               AS count1997
,SUM(CASE WHEN cmf.financial_year = '1997' THEN cmf.donation_value ELSE 0 END)  AS sum1997
,SUM(CASE WHEN cmf.financial_year = '1998' THEN 1 ELSE 0 END)               AS count1998
,SUM(CASE WHEN cmf.financial_year = '1998' THEN cmf.donation_value ELSE 0 END)  AS sum1998
,SUM(CASE WHEN cmf.financial_year = '1999' THEN 1 ELSE 0 END)               AS count1999
,SUM(CASE WHEN cmf.financial_year = '1999' THEN cmf.donation_value ELSE 0 END)  AS sum1999
,SUM(CASE WHEN cmf.financial_year = '2000' THEN 1 ELSE 0 END)               AS count2000
,SUM(CASE WHEN cmf.financial_year = '2000' THEN cmf.donation_value ELSE 0 END)  AS sum2000
,SUM(CASE WHEN cmf.financial_year = '2001' THEN 1 ELSE 0 END)               AS count2001
,SUM(CASE WHEN cmf.financial_year = '2001' THEN cmf.donation_value ELSE 0 END)  AS sum2001
,SUM(CASE WHEN cmf.financial_year = '2002' THEN 1 ELSE 0 END)               AS count2002
,SUM(CASE WHEN cmf.financial_year = '2002' THEN cmf.donation_value ELSE 0 END)  AS sum2002
,SUM(CASE WHEN cmf.financial_year = '2003' THEN 1 ELSE 0 END)               AS count2003
,SUM(CASE WHEN cmf.financial_year = '2003' THEN cmf.donation_value ELSE 0 END)  AS sum2003
,SUM(CASE WHEN cmf.financial_year = '2004' THEN 1 ELSE 0 END)               AS count2004
,SUM(CASE WHEN cmf.financial_year = '2004' THEN cmf.donation_value ELSE 0 END)  AS sum2004
,SUM(CASE WHEN cmf.financial_year = '2005' THEN 1 ELSE 0 END)               AS count2005
,SUM(CASE WHEN cmf.financial_year = '2005' THEN cmf.donation_value ELSE 0 END)  AS sum2005
,SUM(CASE WHEN cmf.financial_year = '2006' THEN 1 ELSE 0 END)               AS count2006
,SUM(CASE WHEN cmf.financial_year = '2006' THEN cmf.donation_value ELSE 0 END)  AS sum2006
,SUM(CASE WHEN cmf.financial_year = '2007' THEN 1 ELSE 0 END)               AS count2007
,SUM(CASE WHEN cmf.financial_year = '2007' THEN cmf.donation_value ELSE 0 END)  AS sum2007
,SUM(CASE WHEN cmf.financial_year = '2008' THEN 1 ELSE 0 END)               AS count2008
,SUM(CASE WHEN cmf.financial_year = '2008' THEN cmf.donation_value ELSE 0 END)  AS sum2008
,SUM(CASE WHEN cmf.financial_year = '2009' THEN 1 ELSE 0 END)               AS count2009
,SUM(CASE WHEN cmf.financial_year = '2009' THEN cmf.donation_value ELSE 0 END)  AS sum2009
,SUM(CASE WHEN cmf.financial_year = '2010' THEN 1 ELSE 0 END)               AS count2010
,SUM(CASE WHEN cmf.financial_year = '2010' THEN cmf.donation_value ELSE 0 END)  AS sum2010
FROM complete_finances cmf
INNER JOIN contact con WITH (nolock) ON con.id = cmf.contact_id
INNER JOIN [l_function] lfu WITH (nolock) ON lfu.code = con.class
LEFT OUTER JOIN event eve with (nolock) on eve.id = cmf.event_id
INNER JOIN [l_approach channel] lac WITH (nolock) ON lac.code = eve.class
WHERE cmf.contact_id IS NOT NULL
GROUP BY
cmf.contact_id
,cmf.type1
,cmf.type2
,lfu.description
,lac.description
WITH CUBE
)
INSERT INTO #category
SELECT
contact_id
,donor_class
,payment_type
,donation_type
,approach_channel
,count1987
,sum1987
,count1988
,sum1988
,count1989
,sum1989
,count1990
,sum1990
,count1991
,sum1991
,count1992
,sum1992
,count1993
,sum1993
,count1994
,sum1994
,count1995
,sum1995
,count1996
,sum1996
,count1997
,sum1997
,count1998
,sum1998
,count1999
,sum1999
,count2000
,sum2000
,count2001
,sum2001
,count2002
,sum2002
,count2003
,sum2003
,count2004
,sum2004
,count2005
,sum2005
,count2006
,sum2006
,count2007
,sum2007
,count2008
,sum2008
,count2009
,sum2009
,count2010
,sum2010
,CASE   WHEN count2005 > 0 AND count1987+count1988+count1989+count1990+count1991
            +count1992+count1993+count1994+count1995+count1996
            +count1997+count1998+count1999+count2000+count2001
            +count2002+count2003+count2004 = 0
            THEN 'New donor'
        WHEN count2005 > 0 AND count2004 = 0 AND
            count1987+count1988+count1989+count1990+count1991
            +count1992+count1993+count1994+count1995+count1996
            +count1997+count1998+count1999+count2000+count2001
            +count2002+count2003 > 0
            THEN 'Reactivated donor'
        WHEN count2005 > 0 AND count2004 > 0 AND count2003 > 0
            AND count2002 > 0 AND count2001 > 0
            THEN '5+yrs consecutive'
        WHEN count2005 > 0 AND count2004 > 0 AND count2003 > 0
            AND count2002 > 0
            THEN '4yrs consecutive'
        WHEN count2005 > 0 AND count2004 > 0 AND count2003 > 0
            THEN '3yrs consecutive'
        WHEN count2005 > 0 AND count2004 > 0
            THEN '2yrs consecutive'
        WHEN count2005 = 0 AND count1987+count1988+count1989+count1990+count1991
            +count1992+count1993+count1994+count1995+count1996
            +count1997+count1998+count1999+count2000+count2001
            +count2002+count2003+count2004 > 0
            THEN 'Lapsed Donor'
        WHEN    count1987+count1988+count1989+count1990+count1991
            +count1992+count1993+count1994+count1995+count1996
            +count1997+count1998+count1999+count2000+count2001
            +count2002+count2003+count2004+count2005 = 0
            THEN 'Non donor'
END AS category05
,CASE   WHEN count2006 > 0 AND count1987+count1988+count1989+count1990+count1991
            +count1992+count1993+count1994+count1995+count1996
            +count1997+count1998+count1999+count2000+count2001
            +count2002+count2003+count2004+count2005 = 0
            THEN 'New donor'
        WHEN count2006 > 0 AND count2005 = 0 AND
            count1987+count1988+count1989+count1990+count1991
            +count1992+count1993+count1994+count1995+count1996
            +count1997+count1998+count1999+count2000+count2001
            +count2002+count2003+count2004 > 0
            THEN 'Reactivated donor'
        WHEN count2006 > 0 AND count2005 > 0 AND count2004 > 0
            AND count2003 > 0 AND count2002 > 0
            THEN '5+yrs consecutive'
        WHEN count2006 > 0 AND count2005 > 0 AND count2004 > 0
            AND count2003 > 0
            THEN '4yrs consecutive'
        WHEN count2006 > 0 AND count2005 > 0 AND count2004 > 0
            THEN '3yrs consecutive'
        WHEN count2006 > 0 AND count2005 > 0
            THEN '2yrs consecutive'
        WHEN count2006 = 0 AND count1987+count1988+count1989+count1990+count1991
            +count1992+count1993+count1994+count1995+count1996
            +count1997+count1998+count1999+count2000+count2001
            +count2002+count2003+count2004+count2005 > 0
            THEN 'Lapsed Donor'
        WHEN    count1987+count1988+count1989+count1990+count1991
            +count1992+count1993+count1994+count1995+count1996
            +count1997+count1998+count1999+count2000+count2001
            +count2002+count2003+count2004+count2005+count2006 = 0
            THEN 'Non donor'
END AS category06
,CASE   WHEN count2007 > 0 AND count1987+count1988+count1989+count1990+count1991
            +count1992+count1993+count1994+count1995+count1996
            +count1997+count1998+count1999+count2000+count2001
            +count2002+count2003+count2004+count2005+count2006 = 0
            THEN 'New donor'
        WHEN count2007 > 0 AND count2006 = 0 AND
            count1987+count1988+count1989+count1990+count1991
            +count1992+count1993+count1994+count1995+count1996
            +count1997+count1998+count1999+count2000+count2001
            +count2002+count2003+count2004+count2005 > 0
            THEN 'Reactivated donor'
        WHEN count2007 > 0 AND count2006 > 0 AND count2005 > 0
            AND count2004 > 0 AND count2003 > 0
            THEN '5+yrs consecutive'
        WHEN count2007 > 0 AND count2006 > 0 AND count2005 > 0
            AND count2004 > 0
            THEN '4yrs consecutive'
        WHEN count2007 > 0 AND count2006 > 0 AND count2005 > 0
            THEN '3yrs consecutive'
        WHEN count2007 > 0 AND count2006 > 0
            THEN '2yrs consecutive'
        WHEN count2007 = 0 AND count1987+count1988+count1989+count1990+count1991
            +count1992+count1993+count1994+count1995+count1996
            +count1997+count1998+count1999+count2000+count2001
            +count2002+count2003+count2004+count2005+count2006 > 0
            THEN 'Lapsed Donor'
        WHEN    count1987+count1988+count1989+count1990+count1991
            +count1992+count1993+count1994+count1995+count1996
            +count1997+count1998+count1999+count2000+count2001
            +count2002+count2003+count2004+count2005+count2006+count2007 = 0
            THEN 'Non donor'
END AS category07
,CASE   WHEN count2008 > 0 AND count1987+count1988+count1989+count1990+count1991
            +count1992+count1993+count1994+count1995+count1996
            +count1997+count1998+count1999+count2000+count2001
            +count2002+count2003+count2004+count2005+count2006+count2007 = 0
            THEN 'New donor'
        WHEN count2008 > 0 AND count2007 = 0 AND
            count1987+count1988+count1989+count1990+count1991
            +count1992+count1993+count1994+count1995+count1996
            +count1997+count1998+count1999+count2000+count2001
            +count2002+count2003+count2004+count2005+count2006 > 0
            THEN 'Reactivated donor'
        WHEN count2008 > 0 AND count2007 > 0 AND count2006 > 0
            AND count2005 > 0 AND count2004 > 0
            THEN '5+yrs consecutive'
        WHEN count2008 > 0 AND count2007 > 0 AND count2006 > 0
            AND count2005 > 0
            THEN '4yrs consecutive'
        WHEN count2008 > 0 AND count2007 > 0 AND count2006 > 0
            THEN '3yrs consecutive'
        WHEN count2008 > 0 AND count2007 > 0
            THEN '2yrs consecutive'
        WHEN count2008 = 0 AND count1987+count1988+count1989+count1990+count1991
            +count1992+count1993+count1994+count1995+count1996
            +count1997+count1998+count1999+count2000+count2001
            +count2002+count2003+count2004+count2005+count2006+count2007 > 0
            THEN 'Lapsed Donor'
        WHEN    count1987+count1988+count1989+count1990+count1991
            +count1992+count1993+count1994+count1995+count1996
            +count1997+count1998+count1999+count2000+count2001
            +count2002+count2003+count2004+count2005+count2006+count2007+count2008 = 0
            THEN 'Non donor'
END AS category08
,CASE   WHEN count2009 > 0 AND count1987+count1988+count1989+count1990+count1991
            +count1992+count1993+count1994+count1995+count1996
            +count1997+count1998+count1999+count2000+count2001
            +count2002+count2003+count2004+count2005+count2006+count2007+count2008 = 0
            THEN 'New donor'
        WHEN count2009 > 0 AND count2008 = 0 AND
            count1987+count1988+count1989+count1990+count1991
            +count1992+count1993+count1994+count1995+count1996
            +count1997+count1998+count1999+count2000+count2001
            +count2002+count2003+count2004+count2005+count2006+count2007 > 0
            THEN 'Reactivated donor'
        WHEN count2009 > 0 AND count2008 > 0 AND count2007 > 0
            AND count2006 > 0 AND count2005 > 0
            THEN '5+yrs consecutive'
        WHEN count2009 > 0 AND count2008 > 0 AND count2007 > 0
            AND count2006 > 0
            THEN '4yrs consecutive'
        WHEN count2009 > 0 AND count2008 > 0 AND count2007 > 0
            THEN '3yrs consecutive'
        WHEN count2009 > 0 AND count2008 > 0
            THEN '2yrs consecutive'
        WHEN count2009 = 0 AND count1987+count1988+count1989+count1990+count1991
            +count1992+count1993+count1994+count1995+count1996
            +count1997+count1998+count1999+count2000+count2001
            +count2002+count2003+count2004+count2005+count2006+count2007+count2008 > 0
            THEN 'Lapsed Donor'
        WHEN    count1987+count1988+count1989+count1990+count1991
            +count1992+count1993+count1994+count1995+count1996
            +count1997+count1998+count1999+count2000+count2001
            +count2002+count2003+count2004+count2005+count2006+count2007+count2008+count2009 = 0
            THEN 'Non donor'
END AS category09
,CASE   WHEN count2010 > 0 AND count1987+count1988+count1989+count1990+count1991
            +count1992+count1993+count1994+count1995+count1996
            +count1997+count1998+count1999+count2000+count2001
            +count2002+count2003+count2004+count2005+count2006+count2007+count2008+count2009 = 0
            THEN 'New donor'
        WHEN count2010 > 0 AND count2009 = 0 AND
            count1987+count1988+count1989+count1990+count1991
            +count1992+count1993+count1994+count1995+count1996
            +count1997+count1998+count1999+count2000+count2001
            +count2002+count2003+count2004+count2005+count2006+count2007+count2008 > 0
            THEN 'Reactivated donor'
        WHEN count2010 > 0 AND count2009 > 0 AND count2008 > 0
            AND count2007 > 0 AND count2006 > 0
            THEN '5+yrs consecutive'
        WHEN count2010 > 0 AND count2009 > 0 AND count2008 > 0
            AND count2007 > 0
            THEN '4yrs consecutive'
        WHEN count2010 > 0 AND count2009 > 0 AND count2008 > 0
            THEN '3yrs consecutive'
        WHEN count2010 > 0 AND count2009 > 0
            THEN '2yrs consecutive'
        WHEN count2010 = 0 AND count1987+count1988+count1989+count1990+count1991
            +count1992+count1993+count1994+count1995+count1996
            +count1997+count1998+count1999+count2000+count2001
            +count2002+count2003+count2004+count2005+count2006+count2007+count2008+count2009 > 0
            THEN 'Lapsed Donor'
        WHEN    count1987+count1988+count1989+count1990+count1991
            +count1992+count1993+count1994+count1995+count1996
            +count1997+count1998+count1999+count2000+count2001
            +count2002+count2003+count2004+count2005+count2006+count2007+count2008+count2009+count2010 = 0
            THEN 'Non donor'
END AS category10
,CASE WHEN count1987 > 0 THEN 1987 END AS [1987]
,CASE WHEN count1988 > 0 THEN 1988 END AS [1988]
,CASE WHEN count1989 > 0 THEN 1989 END AS [1989]
,CASE WHEN count1990 > 0 THEN 1990 END AS [1990]
,CASE WHEN count1991 > 0 THEN 1991 END AS [1991]
,CASE WHEN count1992 > 0 THEN 1992 END AS [1992]
,CASE WHEN count1993 > 0 THEN 1993 END AS [1993]
,CASE WHEN count1994 > 0 THEN 1994 END AS [1994]
,CASE WHEN count1995 > 0 THEN 1995 END AS [1995]
,CASE WHEN count1996 > 0 THEN 1996 END AS [1996]
,CASE WHEN count1997 > 0 THEN 1997 END AS [1997]
,CASE WHEN count1998 > 0 THEN 1998 END AS [1998]
,CASE WHEN count1999 > 0 THEN 1999 END AS [1999]
,CASE WHEN count2000 > 0 THEN 2000 END AS [2000]
,CASE WHEN count2001 > 0 THEN 2001 END AS [2001]
,CASE WHEN count2002 > 0 THEN 2002 END AS [2002]
,CASE WHEN count2003 > 0 THEN 2003 END AS [2003]
,CASE WHEN count2004 > 0 THEN 2004 END AS [2004]
,CASE WHEN count2005 > 0 THEN 2005 END AS [2005]
,CASE WHEN count2006 > 0 THEN 2006 END AS [2006]
,CASE WHEN count2007 > 0 THEN 2007 END AS [2007]
,CASE WHEN count2008 > 0 THEN 2008 END AS [2008]
,CASE WHEN count2009 > 0 THEN 2009 END AS [2009]
,CASE WHEN count2010 > 0 THEN 2010 END AS [2010]
FROM category
WHERE
contact_id IS NOT NULL
AND donor_class IS NOT NULL
ORDER BY contact_id
```

答: One minor suggestion in the mean time, you may find that you are better off creating the index on your temporary table after you have inserted the data into it, as doing so should improve the insertion speed. Creating it before hand means that the index must be modified for each and every record that you insert into the temp table.</p>

Title: Insert into temporary table query

Body:
I'm looking to optimise a query that takes an excessive amount of time to run (around 1.5 hours), and through looking at the execution plan one of the biggest drains seems to be inserting the data from the main query into a temporary table. When I insert the data into a temporary table there is the following process :

Process (cost %)

Compute Scalar (0%) <-- Stream Aggregate (0%) <-- Sort(0%) <-- Table Spool (eager spool) (10%)

Which makes sense, but what doesn't to me is that this appears 9 times?

I can include the query / index information if this would help, but the query itself is quite long and I don't want to make this question into an essay!

Thanks,
Davin.

Edit to include query - sorry for the length!

IF object_id('tempdb..#category') IS NOT NULL DROP TABLE #category
CREATE TABLE #category
(
contact_id NVARCHAR(255)
,donor_class NVARCHAR(255)
,payment_type NVARCHAR(255)
,donation_type NVARCHAR(255)
,approach_channel NVARCHAR(255)
,count1987 INT
,sum1987 DECIMAL(18,3)
,count1988 INT
,sum1988 DECIMAL(18,3)
,count1989 INT
,sum1989 DECIMAL(18,3)
,count1990 INT
,sum1990 DECIMAL(18,3)
,count1991 INT
,sum1991 DECIMAL(18,3)
,count1992 INT
,sum1992 DECIMAL(18,3)
,count1993 INT
,sum1993 DECIMAL(18,3)
,count1994 INT
,sum1994 DECIMAL(18,3)
,count1995 INT
,sum1995 DECIMAL(18,3)
,count1996 INT
,sum1996 DECIMAL(18,3)
,count1997 INT
,sum1997 DECIMAL(18,3)
,count1998 INT
,sum1998 DECIMAL(18,3)
,count1999 INT
,sum1999 DECIMAL(18,3)
,count2000 INT
,sum2000 DECIMAL(18,3)
,count2001 INT
,sum2001 DECIMAL(18,3)
,count2002 INT
,sum2002 DECIMAL(18,3)
,count2003 INT
,sum2003 DECIMAL(18,3)
,count2004 INT
,sum2004 DECIMAL(18,3)
,count2005 INT
,sum2005 DECIMAL(18,3)
,count2006 INT
,sum2006 DECIMAL(18,3)
,count2007 INT
,sum2007 DECIMAL(18,3)
,count2008 INT
,sum2008 DECIMAL(18,3)
,count2009 INT
,sum2009 DECIMAL(18,3)
,count2010 INT
,sum2010 DECIMAL(18,3)
,category05 NVARCHAR(255)
,category06 NVARCHAR(255)
,category07 NVARCHAR(255)
,category08 NVARCHAR(255)
,category09 NVARCHAR(255)
,category10 NVARCHAR(255)
,[1987] NVARCHAR(4)
,[1988] NVARCHAR(4)
,[1989] NVARCHAR(4)
,[1990] NVARCHAR(4)
,[1991] NVARCHAR(4)
,[1992] NVARCHAR(4)
,[1993] NVARCHAR(4)
,[1994] NVARCHAR(4)
,[1995] NVARCHAR(4)
,[1996] NVARCHAR(4)
,[1997] NVARCHAR(4)
,[1998] NVARCHAR(4)
,[1999] NVARCHAR(4)
,[2000] NVARCHAR(4)
,[2001] NVARCHAR(4)
,[2002] NVARCHAR(4)
,[2003] NVARCHAR(4)
,[2004] NVARCHAR(4)
,[2005] NVARCHAR(4)
,[2006] NVARCHAR(4)
,[2007] NVARCHAR(4)
,[2008] NVARCHAR(4)
,[2009] NVARCHAR(4)
,[2010] NVARCHAR(4)
)
CREATE INDEX #idx_category ON #category (contact_id)

;WITH complete_finances as
(
(
SELECT
don.supporter_id AS contact_id
,don.id AS gift_id
,YEAR(don.date_received) AS calendar_year
,YEAR(don.date_received) - CASE WHEN MONTH(don.date_received) < 4 THEN 1 ELSE 0 END AS financial_year
,don.date_received AS date_received
,don.event_id AS event_id
,SUM(CASE   WHEN don.gift_aid_status <> 4 THEN don.value_gross * ((dfa.percentage) / 100)
            WHEN don.gift_aid_status = 4 AND don.value_net > don.value_gross
            AND don.value_net <> 0       THEN don.value_net  * ((dfa.percentage) / 100)
            ELSE don.value_gross  * ((dfa.percentage) / 100)
            END
        )   AS donation_value
,(CASE  WHEN don.payment_method IN (1, 2, 10, 11, 12, 200000, 200001, 200002, 200003, 200004, 200007, 200008, 200009 --single payments
                                            ,7,13,14) -- single payment refunds
                    THEN 'single'
        WHEN don.payment_method IN (3,4,5,9,200006)
                     THEN 'regular'
        ELSE 'other' END) AS type1
,CASE WHEN dfa.fund_id IN --all emergency funds
    (
    select f.id
    FROM _audit a
    INNER JOIN _fund f ON a.article_id = f.id
    WHERE a.entity_name = 'Fund'
    AND a.Changes LIKE
    '%finance_code2%OldValue>3%'
    UNION
    select
    id
    FROM _fund
    WHERE (finance_code2 LIKE '3%'
    OR finance_code2 LIKE '9%')
    AND finance_code2 IS NOT NULL
    ) THEN 'Emergency' else 'Non-emergency' end as type2
FROM donation don WITH (nolock)
INNER JOIN donation_fund_allocation dfa WITH (nolock) ON dfa.donation_id = don.id
WHERE don.supporter_id IS NOT NULL
AND don.status = 4 -- posted donations only
AND don.value_gross <> 0 -- to include refunds as well
GROUP BY don.supporter_id
,don.id
,don.date_received
,don.event_id
,don.payment_method
,dfa.fund_id
)
UNION ALL
(
SELECT
caw.contact_id AS contact_id
,caw.id AS gift_id
,YEAR(caw.bank_date) AS calendar_year
,YEAR(caw.bank_date) - CASE WHEN MONTH(caw.bank_date) < 4 THEN 1 ELSE 0 END AS financial_year
,caw.bank_date AS date_received
,caw.event AS event_id
,(CASE      WHEN  caw.gift_aid_status <> 4 THEN caw.amount_exc_gift_aid
            WHEN  caw.gift_aid_status = 4   THEN amount_inc_gift_aid
            END
        )   AS donation_value
,'CAW gift' AS type1
,'Non-emergency' AS type2
FROM gifts caw WITH (nolock)
WHERE caw.contact_id IS NOT NULL
AND caw.amount_exc_gift_aid <> 0
)
)
,category AS
(
SELECT
cmf.contact_id AS contact_id
,lfu.description AS donor_class
,ISNULL(cmf.type1,'Total') AS payment_type
,ISNULL(lac.description, 'Total') AS approach_channel
--,ISNULL(CASE  WHEN type1 = 'CAW gift' THEN 'CAW Gift'
--      WHEN type1 =  'Single'  THEN 'Non CAW gift'
--      WHEN type1 =  'Regular' THEN 'Non CAW gift'
--      WHEN type1 =  'Other'   THEN 'Non CAW gift'
--      END,'Total') AS payment_type2
,ISNULL(cmf.type2,'Total') AS donation_type
,SUM(CASE WHEN cmf.financial_year = '1987' THEN 1 ELSE 0 END)                   AS count1987
,SUM(CASE WHEN cmf.financial_year = '1988' THEN cmf.donation_value ELSE 0 END)  AS sum1987
,SUM(CASE WHEN cmf.financial_year = '1988' THEN 1 ELSE 0 END)               AS count1988
,SUM(CASE WHEN cmf.financial_year = '1988' THEN cmf.donation_value ELSE 0 END)  AS sum1988
,SUM(CASE WHEN cmf.financial_year = '1989' THEN 1 ELSE 0 END)               AS count1989
,SUM(CASE WHEN cmf.financial_year = '1989' THEN cmf.donation_value ELSE 0 END)  AS sum1989
,SUM(CASE WHEN cmf.financial_year = '1990' THEN 1 ELSE 0 END)               AS count1990
,SUM(CASE WHEN cmf.financial_year = '1990' THEN cmf.donation_value ELSE 0 END)  AS sum1990
,SUM(CASE WHEN cmf.financial_year = '1991' THEN 1 ELSE 0 END)               AS count1991
,SUM(CASE WHEN cmf.financial_year = '1991' THEN cmf.donation_value ELSE 0 END)  AS sum1991
,SUM(CASE WHEN cmf.financial_year = '1992' THEN 1 ELSE 0 END)               AS count1992
,SUM(CASE WHEN cmf.financial_year = '1992' THEN cmf.donation_value ELSE 0 END)  AS sum1992
,SUM(CASE WHEN cmf.financial_year = '1993' THEN 1 ELSE 0 END)               AS count1993
,SUM(CASE WHEN cmf.financial_year = '1993' THEN cmf.donation_value ELSE 0 END)  AS sum1993
,SUM(CASE WHEN cmf.financial_year = '1994' THEN 1 ELSE 0 END)               AS count1994
,SUM(CASE WHEN cmf.financial_year = '1994' THEN cmf.donation_value ELSE 0 END)  AS sum1994
,SUM(CASE WHEN cmf.financial_year = '1995' THEN 1 ELSE 0 END)               AS count1995
,SUM(CASE WHEN cmf.financial_year = '1995' THEN cmf.donation_value ELSE 0 END)  AS sum1995
,SUM(CASE WHEN cmf.financial_year = '1996' THEN 1 ELSE 0 END)               AS count1996
,SUM(CASE WHEN cmf.financial_year = '1996' THEN cmf.donation_value ELSE 0 END)  AS sum1996
,SUM(CASE WHEN cmf.financial_year = '1997' THEN 1 ELSE 0 END)               AS count1997
,SUM(CASE WHEN cmf.financial_year = '1997' THEN cmf.donation_value ELSE 0 END)  AS sum1997
,SUM(CASE WHEN cmf.financial_year = '1998' THEN 1 ELSE 0 END)               AS count1998
,SUM(CASE WHEN cmf.financial_year = '1998' THEN cmf.donation_value ELSE 0 END)  AS sum1998
,SUM(CASE WHEN cmf.financial_year = '1999' THEN 1 ELSE 0 END)               AS count1999
,SUM(CASE WHEN cmf.financial_year = '1999' THEN cmf.donation_value ELSE 0 END)  AS sum1999
,SUM(CASE WHEN cmf.financial_year = '2000' THEN 1 ELSE 0 END)               AS count2000
,SUM(CASE WHEN cmf.financial_year = '2000' THEN cmf.donation_value ELSE 0 END)  AS sum2000
,SUM(CASE WHEN cmf.financial_year = '2001' THEN 1 ELSE 0 END)               AS count2001
,SUM(CASE WHEN cmf.financial_year = '2001' THEN cmf.donation_value ELSE 0 END)  AS sum2001
,SUM(CASE WHEN cmf.financial_year = '2002' THEN 1 ELSE 0 END)               AS count2002
,SUM(CASE WHEN cmf.financial_year = '2002' THEN cmf.donation_value ELSE 0 END)  AS sum2002
,SUM(CASE WHEN cmf.financial_year = '2003' THEN 1 ELSE 0 END)               AS count2003
,SUM(CASE WHEN cmf.financial_year = '2003' THEN cmf.donation_value ELSE 0 END)  AS sum2003
,SUM(CASE WHEN cmf.financial_year = '2004' THEN 1 ELSE 0 END)               AS count2004
,SUM(CASE WHEN cmf.financial_year = '2004' THEN cmf.donation_value ELSE 0 END)  AS sum2004
,SUM(CASE WHEN cmf.financial_year = '2005' THEN 1 ELSE 0 END)               AS count2005
,SUM(CASE WHEN cmf.financial_year = '2005' THEN cmf.donation_value ELSE 0 END)  AS sum2005
,SUM(CASE WHEN cmf.financial_year = '2006' THEN 1 ELSE 0 END)               AS count2006
,SUM(CASE WHEN cmf.financial_year = '2006' THEN cmf.donation_value ELSE 0 END)  AS sum2006
,SUM(CASE WHEN cmf.financial_year = '2007' THEN 1 ELSE 0 END)               AS count2007
,SUM(CASE WHEN cmf.financial_year = '2007' THEN cmf.donation_value ELSE 0 END)  AS sum2007
,SUM(CASE WHEN cmf.financial_year = '2008' THEN 1 ELSE 0 END)               AS count2008
,SUM(CASE WHEN cmf.financial_year = '2008' THEN cmf.donation_value ELSE 0 END)  AS sum2008
,SUM(CASE WHEN cmf.financial_year = '2009' THEN 1 ELSE 0 END)               AS count2009
,SUM(CASE WHEN cmf.financial_year = '2009' THEN cmf.donation_value ELSE 0 END)  AS sum2009
,SUM(CASE WHEN cmf.financial_year = '2010' THEN 1 ELSE 0 END)               AS count2010
,SUM(CASE WHEN cmf.financial_year = '2010' THEN cmf.donation_value ELSE 0 END)  AS sum2010
FROM complete_finances cmf
INNER JOIN contact con WITH (nolock) ON con.id = cmf.contact_id
INNER JOIN [l_function] lfu WITH (nolock) ON lfu.code = con.class
LEFT OUTER JOIN event eve with (nolock) on eve.id = cmf.event_id
INNER JOIN [l_approach channel] lac WITH (nolock) ON lac.code = eve.class
WHERE cmf.contact_id IS NOT NULL
GROUP BY
cmf.contact_id
,cmf.type1
,cmf.type2
,lfu.description
,lac.description
WITH CUBE
)
INSERT INTO #category
SELECT
contact_id
,donor_class
,payment_type
,donation_type
,approach_channel
,count1987
,sum1987
,count1988
,sum1988
,count1989
,sum1989
,count1990
,sum1990
,count1991
,sum1991
,count1992
,sum1992
,count1993
,sum1993
,count1994
,sum1994
,count1995
,sum1995
,count1996
,sum1996
,count1997
,sum1997
,count1998
,sum1998
,count1999
,sum1999
,count2000
,sum2000
,count2001
,sum2001
,count2002
,sum2002
,count2003
,sum2003
,count2004
,sum2004
,count2005
,sum2005
,count2006
,sum2006
,count2007
,sum2007
,count2008
,sum2008
,count2009
,sum2009
,count2010
,sum2010
,CASE   WHEN count2005 > 0 AND count1987+count1988+count1989+count1990+count1991
            +count1992+count1993+count1994+count1995+count1996
            +count1997+count1998+count1999+count2000+count2001
            +count2002+count2003+count2004 = 0
            THEN 'New donor'
        WHEN count2005 > 0 AND count2004 = 0 AND
            count1987+count1988+count1989+count1990+count1991
            +count1992+count1993+count1994+count1995+count1996
            +count1997+count1998+count1999+count2000+count2001
            +count2002+count2003 > 0
            THEN 'Reactivated donor'
        WHEN count2005 > 0 AND count2004 > 0 AND count2003 > 0
            AND count2002 > 0 AND count2001 > 0
            THEN '5+yrs consecutive'
        WHEN count2005 > 0 AND count2004 > 0 AND count2003 > 0
            AND count2002 > 0
            THEN '4yrs consecutive'
        WHEN count2005 > 0 AND count2004 > 0 AND count2003 > 0
            THEN '3yrs consecutive'
        WHEN count2005 > 0 AND count2004 > 0
            THEN '2yrs consecutive'
        WHEN count2005 = 0 AND count1987+count1988+count1989+count1990+count1991
            +count1992+count1993+count1994+count1995+count1996
            +count1997+count1998+count1999+count2000+count2001
            +count2002+count2003+count2004 > 0
            THEN 'Lapsed Donor'
        WHEN    count1987+count1988+count1989+count1990+count1991
            +count1992+count1993+count1994+count1995+count1996
            +count1997+count1998+count1999+count2000+count2001
            +count2002+count2003+count2004+count2005 = 0
            THEN 'Non donor'
END AS category05
,CASE   WHEN count2006 > 0 AND count1987+count1988+count1989+count1990+count1991
            +count1992+count1993+count1994+count1995+count1996
            +count1997+count1998+count1999+count2000+count2001
            +count2002+count2003+count2004+count2005 = 0
            THEN 'New donor'
        WHEN count2006 > 0 AND count2005 = 0 AND
            count1987+count1988+count1989+count1990+count1991
            +count1992+count1993+count1994+count1995+count1996
            +count1997+count1998+count1999+count2000+count2001
            +count2002+count2003+count2004 > 0
            THEN 'Reactivated donor'
        WHEN count2006 > 0 AND count2005 > 0 AND count2004 > 0
            AND count2003 > 0 AND count2002 > 0
            THEN '5+yrs consecutive'
        WHEN count2006 > 0 AND count2005 > 0 AND count2004 > 0
            AND count2003 > 0
            THEN '4yrs consecutive'
        WHEN count2006 > 0 AND count2005 > 0 AND count2004 > 0
            THEN '3yrs consecutive'
        WHEN count2006 > 0 AND count2005 > 0
            THEN '2yrs consecutive'
        WHEN count2006 = 0 AND count1987+count1988+count1989+count1990+count1991
            +count1992+count1993+count1994+count1995+count1996
            +count1997+count1998+count1999+count2000+count2001
            +count2002+count2003+count2004+count2005 > 0
            THEN 'Lapsed Donor'
        WHEN    count1987+count1988+count1989+count1990+count1991
            +count1992+count1993+count1994+count1995+count1996
            +count1997+count1998+count1999+count2000+count2001
            +count2002+count2003+count2004+count2005+count2006 = 0
            THEN 'Non donor'
END AS category06
,CASE   WHEN count2007 > 0 AND count1987+count1988+count1989+count1990+count1991
            +count1992+count1993+count1994+count1995+count1996
            +count1997+count1998+count1999+count2000+count2001
            +count2002+count2003+count2004+count2005+count2006 = 0
            THEN 'New donor'
        WHEN count2007 > 0 AND count2006 = 0 AND
            count1987+count1988+count1989+count1990+count1991
            +count1992+count1993+count1994+count1995+count1996
            +count1997+count1998+count1999+count2000+count2001
            +count2002+count2003+count2004+count2005 > 0
            THEN 'Reactivated donor'
        WHEN count2007 > 0 AND count2006 > 0 AND count2005 > 0
            AND count2004 > 0 AND count2003 > 0
            THEN '5+yrs consecutive'
        WHEN count2007 > 0 AND count2006 > 0 AND count2005 > 0
            AND count2004 > 0
            THEN '4yrs consecutive'
        WHEN count2007 > 0 AND count2006 > 0 AND count2005 > 0
            THEN '3yrs consecutive'
        WHEN count2007 > 0 AND count2006 > 0
            THEN '2yrs consecutive'
        WHEN count2007 = 0 AND count1987+count1988+count1989+count1990+count1991
            +count1992+count1993+count1994+count1995+count1996
            +count1997+count1998+count1999+count2000+count2001
            +count2002+count2003+count2004+count2005+count2006 > 0
            THEN 'Lapsed Donor'
        WHEN    count1987+count1988+count1989+count1990+count1991
            +count1992+count1993+count1994+count1995+count1996
            +count1997+count1998+count1999+count2000+count2001
            +count2002+count2003+count2004+count2005+count2006+count2007 = 0
            THEN 'Non donor'
END AS category07
,CASE   WHEN count2008 > 0 AND count1987+count1988+count1989+count1990+count1991
            +count1992+count1993+count1994+count1995+count1996
            +count1997+count1998+count1999+count2000+count2001
            +count2002+count2003+count2004+count2005+count2006+count2007 = 0
            THEN 'New donor'
        WHEN count2008 > 0 AND count2007 = 0 AND
            count1987+count1988+count1989+count1990+count1991
            +count1992+count1993+count1994+count1995+count1996
            +count1997+count1998+count1999+count2000+count2001
            +count2002+count2003+count2004+count2005+count2006 > 0
            THEN 'Reactivated donor'
        WHEN count2008 > 0 AND count2007 > 0 AND count2006 > 0
            AND count2005 > 0 AND count2004 > 0
            THEN '5+yrs consecutive'
        WHEN count2008 > 0 AND count2007 > 0 AND count2006 > 0
            AND count2005 > 0
            THEN '4yrs consecutive'
        WHEN count2008 > 0 AND count2007 > 0 AND count2006 > 0
            THEN '3yrs consecutive'
        WHEN count2008 > 0 AND count2007 > 0
            THEN '2yrs consecutive'
        WHEN count2008 = 0 AND count1987+count1988+count1989+count1990+count1991
            +count1992+count1993+count1994+count1995+count1996
            +count1997+count1998+count1999+count2000+count2001
            +count2002+count2003+count2004+count2005+count2006+count2007 > 0
            THEN 'Lapsed Donor'
        WHEN    count1987+count1988+count1989+count1990+count1991
            +count1992+count1993+count1994+count1995+count1996
            +count1997+count1998+count1999+count2000+count2001
            +count2002+count2003+count2004+count2005+count2006+count2007+count2008 = 0
            THEN 'Non donor'
END AS category08
,CASE   WHEN count2009 > 0 AND count1987+count1988+count1989+count1990+count1991
            +count1992+count1993+count1994+count1995+count1996
            +count1997+count1998+count1999+count2000+count2001
            +count2002+count2003+count2004+count2005+count2006+count2007+count2008 = 0
            THEN 'New donor'
        WHEN count2009 > 0 AND count2008 = 0 AND
            count1987+count1988+count1989+count1990+count1991
            +count1992+count1993+count1994+count1995+count1996
            +count1997+count1998+count1999+count2000+count2001
            +count2002+count2003+count2004+count2005+count2006+count2007 > 0
            THEN 'Reactivated donor'
        WHEN count2009 > 0 AND count2008 > 0 AND count2007 > 0
            AND count2006 > 0 AND count2005 > 0
            THEN '5+yrs consecutive'
        WHEN count2009 > 0 AND count2008 > 0 AND count2007 > 0
            AND count2006 > 0
            THEN '4yrs consecutive'
        WHEN count2009 > 0 AND count2008 > 0 AND count2007 > 0
            THEN '3yrs consecutive'
        WHEN count2009 > 0 AND count2008 > 0
            THEN '2yrs consecutive'
        WHEN count2009 = 0 AND count1987+count1988+count1989+count1990+count1991
            +count1992+count1993+count1994+count1995+count1996
            +count1997+count1998+count1999+count2000+count2001
            +count2002+count2003+count2004+count2005+count2006+count2007+count2008 > 0
            THEN 'Lapsed Donor'
        WHEN    count1987+count1988+count1989+count1990+count1991
            +count1992+count1993+count1994+count1995+count1996
            +count1997+count1998+count1999+count2000+count2001
            +count2002+count2003+count2004+count2005+count2006+count2007+count2008+count2009 = 0
            THEN 'Non donor'
END AS category09
,CASE   WHEN count2010 > 0 AND count1987+count1988+count1989+count1990+count1991
            +count1992+count1993+count1994+count1995+count1996
            +count1997+count1998+count1999+count2000+count2001
            +count2002+count2003+count2004+count2005+count2006+count2007+count2008+count2009 = 0
            THEN 'New donor'
        WHEN count2010 > 0 AND count2009 = 0 AND
            count1987+count1988+count1989+count1990+count1991
            +count1992+count1993+count1994+count1995+count1996
            +count1997+count1998+count1999+count2000+count2001
            +count2002+count2003+count2004+count2005+count2006+count2007+count2008 > 0
            THEN 'Reactivated donor'
        WHEN count2010 > 0 AND count2009 > 0 AND count2008 > 0
            AND count2007 > 0 AND count2006 > 0
            THEN '5+yrs consecutive'
        WHEN count2010 > 0 AND count2009 > 0 AND count2008 > 0
            AND count2007 > 0
            THEN '4yrs consecutive'
        WHEN count2010 > 0 AND count2009 > 0 AND count2008 > 0
            THEN '3yrs consecutive'
        WHEN count2010 > 0 AND count2009 > 0
            THEN '2yrs consecutive'
        WHEN count2010 = 0 AND count1987+count1988+count1989+count1990+count1991
            +count1992+count1993+count1994+count1995+count1996
            +count1997+count1998+count1999+count2000+count2001
            +count2002+count2003+count2004+count2005+count2006+count2007+count2008+count2009 > 0
            THEN 'Lapsed Donor'
        WHEN    count1987+count1988+count1989+count1990+count1991
            +count1992+count1993+count1994+count1995+count1996
            +count1997+count1998+count1999+count2000+count2001
            +count2002+count2003+count2004+count2005+count2006+count2007+count2008+count2009+count2010 = 0
            THEN 'Non donor'
END AS category10
,CASE WHEN count1987 > 0 THEN 1987 END AS [1987]
,CASE WHEN count1988 > 0 THEN 1988 END AS [1988]
,CASE WHEN count1989 > 0 THEN 1989 END AS [1989]
,CASE WHEN count1990 > 0 THEN 1990 END AS [1990]
,CASE WHEN count1991 > 0 THEN 1991 END AS [1991]
,CASE WHEN count1992 > 0 THEN 1992 END AS [1992]
,CASE WHEN count1993 > 0 THEN 1993 END AS [1993]
,CASE WHEN count1994 > 0 THEN 1994 END AS [1994]
,CASE WHEN count1995 > 0 THEN 1995 END AS [1995]
,CASE WHEN count1996 > 0 THEN 1996 END AS [1996]
,CASE WHEN count1997 > 0 THEN 1997 END AS [1997]
,CASE WHEN count1998 > 0 THEN 1998 END AS [1998]
,CASE WHEN count1999 > 0 THEN 1999 END AS [1999]
,CASE WHEN count2000 > 0 THEN 2000 END AS [2000]
,CASE WHEN count2001 > 0 THEN 2001 END AS [2001]
,CASE WHEN count2002 > 0 THEN 2002 END AS [2002]
,CASE WHEN count2003 > 0 THEN 2003 END AS [2003]
,CASE WHEN count2004 > 0 THEN 2004 END AS [2004]
,CASE WHEN count2005 > 0 THEN 2005 END AS [2005]
,CASE WHEN count2006 > 0 THEN 2006 END AS [2006]
,CASE WHEN count2007 > 0 THEN 2007 END AS [2007]
,CASE WHEN count2008 > 0 THEN 2008 END AS [2008]
,CASE WHEN count2009 > 0 THEN 2009 END AS [2009]
,CASE WHEN count2010 > 0 THEN 2010 END AS [2010]
FROM category
WHERE
contact_id IS NOT NULL
AND donor_class IS NOT NULL
ORDER BY contact_id


问:
MYSQLi and ssl connection to db server
I have some odd problem with establishing ssl connection using php.
I have web and database server. On both I have generated certificates through openssl. They are exactly the same.

So I'm trying to connect from webserver using mysql command:

```<code>mysql -h 10.1.1.1 -uroot -p
Password
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 71
Server version: 5.5.5-10.1.19-MariaDB MariaDB Server

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>
</code>```

So now I want to see if it is really ssl:

```<code>mysql> status;
--------------
mysql  Ver 14.14 Distrib 5.6.33, for Linux (x86_64) using  EditLine wrapper

Connection id:          71
Current database:
Current user:           root@10.1.1.2
SSL:                    Cipher in use is DHE-RSA-AES256-SHA
Current pager:          stdout
Using outfile:          ''
Using delimiter:        ;
Server version:         5.5.5-10.1.19-MariaDB MariaDB Server
Protocol version:       10
Connection:             10.1.1.1 via TCP/IP
Server characterset:    latin1
Db     characterset:    latin1
Client characterset:    utf8
Conn.  characterset:    utf8
TCP port:               3306
Uptime:                 1 hour 6 min 51 sec

Threads: 1  Questions: 153  Slow queries: 0  Opens: 21  Flush tables: 1  Open tables: 15  Queries per second avg: 0.038
--------------

mysql>
</code>```

So I see that connection is established. I wrote some php script to connect to my database:

```<code><?php
ini_set ('error_reporting', E_ALL);
ini_set ('display_errors', '1');
error_reporting (E_ALL|E_STRICT);

$db = mysqli_init();
mysqli_options ($db, MYSQLI_OPT_SSL_VERIFY_SERVER_CERT, true);

$db->ssl_set('/etc/mysql/newcerts/client-key-rsa.pem', '/etc/mysql/newcerts/client-cert.pem', '/etc/mysql/newcerts/ca-cert.pem', NULL, NULL);
$link = mysqli_real_connect ($db, '10.1.1.1', 'root', 'xxxxxx', 'mysql', 3306, NULL, MYSQLI_CLIENT_SSL);
if (!$link)
{
    die ('Connect error (' . mysqli_connect_errno() . '): ' . mysqli_connect_error() . "\n");
} else {
    $res = $db->query('SHOW TABLES;');
    print_r ($res);
    $db->close();
}
?>
</code>```

But now when i'm running this script on my webserver I'm getting this error:

```<code>[root@web-01 config]# php test.php

Warning: mysqli_real_connect(): Unable to locate peer certificate CN in /home/extranet/app/config/test.php on line 10

Warning: mysqli_real_connect(): Cannot connect to MySQL by using SSL in /home/extranet/app/config/test.php on line 10

Warning: mysqli_real_connect(): [2002]  (trying to connect via tcp://10.1.1.1:3306) in /home/extranet/app/config/test.php on line 10

Warning: mysqli_real_connect(): (HY000/2002):  in /home/extranet/app/config/test.php on line 10
Connect error (2002):
</code>```

This is so odd. I've tried mysql_connet() and it works...

Any ideas ??

I'm using PHP 5.6.25

EDIT:
Of course I have as well added lines to my webserver .my.cnf file:

```<code>[client]
port=3306
ssl-ca=/etc/mysql/newcerts/ca-cert.pem
ssl-cert=/etc/mysql/newcerts/client-cert.pem
ssl-key=/etc/mysql/newcerts/client-key-rsa.pem
</code>```

This works fine as well from webserver command line:

```<code>mysql -h 10.1.1.1 -u root --password \
    --ssl \
    --ssl-ca /etc/mysql/newcerts/ca-cert.pem \
    --ssl-cert /etc/mysql/newcerts/client-cert.pem \
    --ssl-key /etc/mysql/newcerts/client-key-rsa.pem \
</code>```

Cert user/group/permissions

```<code>[root@web-01 newcerts]# ls -alZ
drwxr-xr-x root root ?                                .
drwxr-xr-x root root ?                                ..
-rw-r--r-- root root ?                                ca-cert.pem
-rw-r--r-- root root ?                                ca-key.pem
-rw-r--r-- root root ?                                client-cert.pem
-rw-r--r-- root root ?                                client-key.pem
-rw-r--r-- root root ?                                client-key-rsa.pem
-rw-r--r-- root root ?                                client-req.pem
-rw-r--r-- root root ?                                server-cert.pem
-rw-r--r-- root root ?                                server-key.pem
-rw-r--r-- root root ?                                server-req.pem
</code>```

SELinux is disabled:

```<code># This file controls the state of SELinux on the system.
# SELINUX= can take one of these three values:
#     enforcing - SELinux security policy is enforced.
#     permissive - SELinux prints warnings instead of enforcing.
#     disabled - No SELinux policy is loaded.
SELINUX=disabled
# SELINUXTYPE= can take one of these two values:
#     targeted - Targeted processes are protected,
#     minimum - Modification of targeted policy. Only selected processes are protected.
#     mls - Multi Level Security protection.
SELINUXTYPE=targeted
</code>```

答:
I also was facing same error I did below tweets and worked for me.

$link = mysqli_real_connect ($db, '10.1.1.1', 'root', 'xxxxxx', 'mysql', 3306, NULL, MYSQLI_CLIENT_SSL_DONT_VERIFY_SERVER_CERT);
```

Title: MYSQLi and ssl connection to db server

Body:
I have some odd problem with establishing ssl connection using php.
I have web and database server. On both I have generated certificates through openssl. They are exactly the same.

So I'm trying to connect from webserver using mysql command:

mysql -h 10.1.1.1 -uroot -p
Password
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 71
Server version: 5.5.5-10.1.19-MariaDB MariaDB Server

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

So now I want to see if it is really ssl:

mysql> status;
--------------
mysql  Ver 14.14 Distrib 5.6.33, for Linux (x86_64) using  EditLine wrapper

Connection id:          71
Current database:
Current user:           root@10.1.1.2
SSL:                    Cipher in use is DHE-RSA-AES256-SHA
Current pager:          stdout
Using outfile:          ''
Using delimiter:        ;
Server version:         5.5.5-10.1.19-MariaDB MariaDB Server
Protocol version:       10
Connection:             10.1.1.1 via TCP/IP
Server characterset:    latin1
Db     characterset:    latin1
Client characterset:    utf8
Conn.  characterset:    utf8
TCP port:               3306
Uptime:                 1 hour 6 min 51 sec

Threads: 1  Questions: 153  Slow queries: 0  Opens: 21  Flush tables: 1  Open tables: 15  Queries per second avg: 0.038
--------------

mysql>

So I see that connection is established. I wrote some php script to connect to my database:

<?php
ini_set ('error_reporting', E_ALL);
ini_set ('display_errors', '1');
error_reporting (E_ALL|E_STRICT);

$db = mysqli_init();
mysqli_options ($db, MYSQLI_OPT_SSL_VERIFY_SERVER_CERT, true);

$db->ssl_set('/etc/mysql/newcerts/client-key-rsa.pem', '/etc/mysql/newcerts/client-cert.pem', '/etc/mysql/newcerts/ca-cert.pem', NULL, NULL);
$link = mysqli_real_connect ($db, '10.1.1.1', 'root', 'xxxxxx', 'mysql', 3306, NULL, MYSQLI_CLIENT_SSL);
if (!$link)
{
    die ('Connect error (' . mysqli_connect_errno() . '): ' . mysqli_connect_error() . "\n");
} else {
    $res = $db->query('SHOW TABLES;');
    print_r ($res);
    $db->close();
}
?>

But now when i'm running this script on my webserver I'm getting this error:

[root@web-01 config]# php test.php

Warning: mysqli_real_connect(): Unable to locate peer certificate CN in /home/extranet/app/config/test.php on line 10

Warning: mysqli_real_connect(): Cannot connect to MySQL by using SSL in /home/extranet/app/config/test.php on line 10

Warning: mysqli_real_connect(): [2002]  (trying to connect via tcp://10.1.1.1:3306) in /home/extranet/app/config/test.php on line 10

Warning: mysqli_real_connect(): (HY000/2002):  in /home/extranet/app/config/test.php on line 10
Connect error (2002):

This is so odd. I've tried mysql_connet() and it works...

Any ideas ??

I'm using PHP 5.6.25

EDIT:
Of course I have as well added lines to my webserver .my.cnf file:

[client]
port=3306
ssl-ca=/etc/mysql/newcerts/ca-cert.pem
ssl-cert=/etc/mysql/newcerts/client-cert.pem
ssl-key=/etc/mysql/newcerts/client-key-rsa.pem

This works fine as well from webserver command line:

mysql -h 10.1.1.1 -u root --password \
    --ssl \
    --ssl-ca /etc/mysql/newcerts/ca-cert.pem \
    --ssl-cert /etc/mysql/newcerts/client-cert.pem \
    --ssl-key /etc/mysql/newcerts/client-key-rsa.pem \

Cert user/group/permissions

[root@web-01 newcerts]# ls -alZ
drwxr-xr-x root root ?                                .
drwxr-xr-x root root ?                                ..
-rw-r--r-- root root ?                                ca-cert.pem
-rw-r--r-- root root ?                                ca-key.pem
-rw-r--r-- root root ?                                client-cert.pem
-rw-r--r-- root root ?                                client-key.pem
-rw-r--r-- root root ?                                client-key-rsa.pem
-rw-r--r-- root root ?                                client-req.pem
-rw-r--r-- root root ?                                server-cert.pem
-rw-r--r-- root root ?                                server-key.pem
-rw-r--r-- root root ?                                server-req.pem

SELinux is disabled:

# This file controls the state of SELinux on the system.
# SELINUX= can take one of these three values:
#     enforcing - SELinux security policy is enforced.
#     permissive - SELinux prints warnings instead of enforcing.
#     disabled - No SELinux policy is loaded.
SELINUX=disabled
# SELINUXTYPE= can take one of these two values:
#     targeted - Targeted processes are protected,
#     minimum - Modification of targeted policy. Only selected processes are protected.
#     mls - Multi Level Security protection.
SELINUXTYPE=targeted


问:
Node and docker - how to handle babel or typescript build?
I have a node application that I want to host in a Docker container, which should be straight forward, as seen in this article:

https://nodejs.org/en/docs/guides/nodejs-docker-webapp/

In my project, however, the sources can not be run directly, they must be compiled from ES6 and/or Typescript.  I use gulp to build with babel, browserify and tsify - with different setups for browser and server.

What would be the best workflow for building and automating docker images in this case?  Are there any resources on the web that describes such a workflow?  Should the Dockerimage do the building after npm install or should I create a shell script to do all this and simply have the Dockerfile pack it all together?

If the Dockerfile should do the build - the image would need to contain all the dev-dependencies, which are not ideal?

Note: I have been able to set up a docker container, and run it - but this required all files to be installed and built beforehand.

答:
I personally prefer to just remove dev dependencies after running babel during build:</p>

```<code>FROM node:7

# Create app directory
RUN mkdir -p /usr/src/app
WORKDIR /usr/src/app

# Install app dependencies
COPY package.json /usr/src/app/
RUN npm install

# Copy app source
COPY src /usr/src/app/src

# Compile app sources
RUN npm run compile

# Remove dev dependencies
RUN npm prune --production

# Expose port and CMD
EXPOSE 8080
CMD [ "npm", "start" ]
</code>```

Body:
I personally prefer to just remove dev dependencies after running babel during build:

FROM node:7

# Create app directory
RUN mkdir -p /usr/src/app
WORKDIR /usr/src/app

# Install app dependencies
COPY package.json /usr/src/app/
RUN npm install

# Copy app source
COPY src /usr/src/app/src

# Compile app sources
RUN npm run compile

# Remove dev dependencies
RUN npm prune --production

# Expose port and CMD
EXPOSE 8080
CMD [ "npm", "start" ]