texts:
I have installed DB2 10.1 in a Red hat box. During the installation I was asked to create several users (db2inst1, db2fenc1 etc). I have been using db2inst1 to perform all operations with DB2 and it works perfectly fine. The sqllib files are located in

/home/db2inst1/sqllib

The installation files are located in

/opt/ibm/db2/V10.1/

All users have access to both these folders. I now want to perform the DB2 operations with another user, namely "john". Given that db2 uses the underlying user/password of the host machine, I added the groups dasadm1 and ds2iadm1 to the "john" user (which are the ones listed in db2inst1).

uid=500(john) gid=500(john) groups=500(john),101(dasadm1),102(db2iadm1)

I can properly enter the db2 cmd with the user "john"

[john@rhel6 java]$ /home/db2inst1/sqllib/bin/db2

but when I run my java app as user "john" I get the following error:

SQLException: com.ibm.db2.jcc.am.SqlException: [jcc][10389][12245][4.13.127] Failure in loading native library db2jcct2, java.lang.UnsatisfiedLinkError: db2jcct2 (Not found in java.library.path):  ERRORCODE=-4472, SQLSTATE=null

this error does not appear if I run the same java app as user db2inst1. The library paths are the exactly the same for both users. Any idea what is going wrong?


texts: mysql replication deadlock slave stopped

texts:
I've a master and slave and got the slave stopped with the following error log in slave. I want to fix this and get the two servers on synch and get going again.

111128  8:42:24 [Note] Slave I/O thread: connected to master 'repl@masterIP:3306',  replication started in log 'mysql-bin.000006' at position 169
111128 15:46:50 [ERROR] Slave: Query caused different errors on master and slave. Error on master: 'Deadlock found when trying to get lock; try restarting transaction' (1213), Error on slave: 'no error' (0). Default database: 'Dbase'. Query: 'insert into archieved(eid,at,rtime,scode) select id,at,avg(rtime),scode from tstatus where date(at)="2011-10-29" group by id', Error_code: 0
111128 15:46:50 [ERROR] Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL thread with "SLAVE START". We stopped at log 'mysql-bin.000006' position 1277768

I'm not sure how to do the following:

try restarting transaction' (1213)

Thanks.


texts: What does duration measurement in sql server actually measure?

texts:
What precisely does the query duration measure when conducting a profiler trace?

I have a stored procedure which performs data access (SELECT), and it is taking an average of around 30 seconds to return results to the application.  But when I run a trace on it I get an average duration of around 8 seconds, max duration 12 seconds, average CPU 5.5 seconds.

What could cause it to take so much longer to return the result set?  I am not accumulating large NETWORKIO waits.  The result set is only around 270 rows of simple text data, around 50 columns total.


texts: database design storing historical information

texts:
I have this database table Templates and I need to keep historical information. So in code what I am doing is inserting a new row with the updated data and then setting the  IsCurrentVersion flag to false. This is working fine but I am not sure how this will grow. Will that have performance problems? Is there a better way to do this? would a archive table be better?
Table Templates {
  id int [pk]
  Title varchar
  Added datetime
  Moddified datatime
  Deleted datetime
  IsCurrentVersion bit
}


texts: Should I create an index for non key columns?

texts:
I have a table in a PostGreSql database defined as following:

CREATE TABLE public."MATCH"(
    "ITEM_A_ID" bigint DEFAULT 0,
    "ITEM_B_ID" bigint DEFAULT 0,
    "OWNER_A_ID" bigint DEFAULT 0,
    "OWNER_B_ID" bigint DEFAULT 0,
    "OTHER_DATA" varchar(100) NOT NULL DEFAULT ''
    CONSTRAINT "MATCH_PK" PRIMARY KEY ("ITEM_A_ID","ITEM_B_ID")
);

It will contain a lot of rows. There will be a lot of queries like the following performed on this table:

SELECT * FROM "MATCH" WHERE "OWNER_A_ID" = owner_a_id;
SELECT * FROM "MATCH" WHERE "OWNER_B_ID" = owner_b_id;

I was thinking about creating indexes on OWNER_A_ID and OWNER_B_ID, since these columns are not keys. Is this a good idea, and if yes, how should I create these? Should I create one index with both columns? Should I create two indexes? Should I include other columns?


texts:
Short of reinstalling the software, is there a simple way to 'start from scratch' with a SQL instance? By that I mean:

Remove all created logons,
Remove all added databases,
Reset system databases,
Remove any new configuration,
Reset any SQL Server Agent configuration

I can't find anything online on how to do this.


texts: How do you block ad-hoc distributed queries from specific sql server instances?

texts:
For one reason or another, a particular development SQL Server is unable to openrowset or opendatasource to production servers, but can hit other dev servers. I've tried sqloledb and sqlncli providers.

I'm not sure if this is a database or network configuration.

If it is a database configuration, is it set on the dev server, or ALL of the prod servers?

Is it a firewall setting?

It's weird. I have full access from the Sql Server instance running from my PC, but not the one on the dev server. I'm using SSMS 2008 in both cases. The dev server is 2005, and the remote access is to other 2005 and 2000 servers.

I ask the question as "how to block queries" because I'm looking to know what settings/configuration I need to disable to allow it again. Thanks!

The error message returned is as follows:

OLE DB provider "SQLNCLI" for linked server "(null)" returned message
"Login timeout expired".
OLE DB provider "SQLNCLI" for linked server "(null)" returned message
"An error has occurred while establishing a connection to the server.
When connecting to SQL Server 2005, this failure may be caused by the
fact that under the default settings SQL Server does not allow remote connections.".
Msg 5, Level 16, State 1, Line 0
Named Pipes Provider: Could not open a connection to SQL Server [5].

Edit: Not sure if folks who answered are clear on what I am looking for. Ad hoc Queries are enabled for all boxes, but from this particular development box, querying production boxes results in the error message above. How did they block things so specifically?


texts: SQL - Return most recent batch of sequential rows

texts:
Version:
Microsoft SQL Server 2014 (SP3-CU4) (KB4500181)

What i'm trying to accomplish here is pull in all the rows where there is a break of less than lets say 40 days between the next row.

So in the first example there is an uninterrupted break every month (roughly). So I would like the query to pull in all records here.

Example 1

CREATE table #Temp1
(
id Varchar(50),
DT8 int,
series int
)

INSERT INTO #Temp1
VALUES
('147501',20181022,1),
('147502',20181115,1),
('147503',20181211,1),
('147504',20190110,1),
('147506',20190306,1),
('147505',20190205,1),
('147507',20190402,1),
('147508',20190429,1),
('147509',20190529,1),
('147510',20190603,1),
('147511',20190703,1),
('147512',20190802,1),
('177901',20190905,1),
('177902',20191001,1),
('177903',20191108,1),
('177904',20191205,1),
('190401',20200103,1),
('190402',20200205,1)

In example two there is a gap greater than 40 days between Sep 25 2019 and January 29 2020.  So I would like the query to just pull in the most recent subsequent block.  In this case it would just be the top record.

Example 2

INSERT INTO #Temp1
VALUES
('164701',20190605,2),
('164702',20190703,2),
('164703',20190802,2),
('164704',20190904,2),
('164705',20190925,2),
('189101',20200129,2)

I have started down this road, and was looking at using LEAD to calculate the number of days between the current and previous rows.  I realize I probably need to break the years out to account for the case when moving to a new year or convert it to a real date so that I can use some sql functions to calculate the difference in days for me.

After that I wasn't sure how to go about only returning the most recent consecutive block.  Thought I would ask here to see if anyone had any insight on how to accomplish this.


texts: How to grant a group to another group in Redshift

texts:
I am trying to build a matrix of roles to dynamically change priorities for our users. In other database I would be doing something like:

create role role1;
create role role2;
grant role role1 to role2;

In Redshift I found the concept of groups, but it looks like it is not possible to assign groups to other groups, is there any solution to handle this?


texts: SQLAgent executing command on Mirror database

texts:
I have a mirrored database DB1, ServerA as primary and ServerB is mirror.

I have a job running on ServerB  (mirror), configured to run against DB1 database.

I was expecting the job to fail but the job connects to ServerA, completes successfully.
I am not able to reproduce the scenario on a different mirrored instance.

I am curious to know when this happens (SQL Agent connects to a different Server if the database is in mirror/restoring mode).

Note: If I re-configure the job to run against master database and access DB1 database using db1.schema.table it fails!


texts:
I need to grant a permission to a newly created user. I have access to root. But, i can't grant a permission to a new user. When I run:

grant all privileges on 'newuser'.* to 'newuser'@localhost;

I got this:

ERROR 1044 (42000): Access denied for user 'root'@'%' to database 'newuser'

The part that I don't understand is why do I logged in as 'root'@'%'? Since I logged in with this command:

mysql --protocol TCP -h localhost -u root -p

But this:

select user(), current_user();

gives me this:

+----------+----------------+
| user()   | current_user() |
+----------+----------------+
| root@::1 | root@%         |
+----------+----------------+

I know only 'root'@'localhost' has grant privilege. Then I check mysql.user table which gives me output (only different columns shown here):

+-----------+---------+------------+-----------------------+
| Host      | User    | Grant_priv | authentication_string |
+-----------+---------+------------+-----------------------+
| localhost | root    | Y          |                       |
| %         | root    | N          | NULL                  |
+-----------+---------+------------+-----------------------+

Other columns that is not included above for 'root'@'localhost' and 'root'@'%' are identical.

So, back to my question: How to login into mysql shell from localhost instead of '%'?

NOTE: I run mysql version 5.5.21 on windows 10 machine.


texts: Postgres memory settings (RAM, work_mem, etc.) for complex text searches on indexed tsvectors

texts:
I know very little about Postgres memory settings.
I have developed a paid-access database, which mostly stores text (and some metadata)
2 tables have 10 millions rows with little text (1 paragraph for each row)
2 other tables have 100,000 rows with the full texts (40 pages for each row)
Overall, the database size is about 10 GB.

The purpose of the project is to enable users to make complex text searches (sometimes combined with metadata queries such as title or data, which are unproblematic).
Text searches are made on indexed tsvectors in the tables described above, which are automatically filled a trigger.
They are rather fast on my personal computer (MacBook Pro 2019 - 2,6 GHz Intel Core i7 6 cores - RAM 16 GB 2400 MHz DDR4).
Unfortunately, as I am going into production, they are rather slow on the remote ODS server (RAM 2 GB),

Hence my question:
What are the ideal memory settings for complex text searches on indexed tsvectors?

Does it help when the server RAM size is higher than the database size (10 GB in my case), so that the database can be entirely stored in RAM?

Should I increase work_mem, or other settings?

As a side question, does it help to reduce the number of tables interrogated, or is that irrelevant?
(say, if users were only allowed to interrogate the tables with paragraphs, and not the tables with full texts)

Thank you very much for your help !


texts: Is it possible to remove useless GRANTS in MySQL

texts:
I'm using pt-show-grants to create all my GRANTS on a new server but there's lots of old GRANTS that refer to tables that doesn't exist anymore so I get errors when I try to apply the SQL file on the new server.

Is there a way to remove GRANTS that refer to tables that doesn't exist anymore?


texts: sys.dm_sql_referencing_entities not returning results

texts:
No doubt this is syntax or typo...but when I query sys.dm_sql_referencing_entities
for a parent table, I get zero results.  No error, just no results.

USE [source];
GO
SELECT *
FROM sys.dm_sql_referencing_entities ('[Test].[USERS]', 'OBJECT');
GO

When I view the dependencies from SSMS, I get

I've never used this before, so maybe I am misunderstanding how this works or what it should be returning?  I tried against the SUBSCRIPTIONS table as well, same result...nothing.


texts: SQL Server 2008 R2 backup fails to restore on SQL Server 2008 R2 SP2

texts:
My DR SQL Server got automatically updated by Windows update from 2008 R2 to 2008 R2 SP2 and since then I can't restore backups from my production machine which is still on 2008 R2.

10 percent processed.
20 percent processed.
Msg 3183, Level 16, State 2, Line 1
RESTORE detected an error on page (0:0) in database "si7500_1" as read from the backup set.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.

I can restore backups that were made prior to the DR update but not ones made since even though the production machine that made the backup hasn't changed. This makes me think that this is something other than the actual DB engine version that is wrong as I'd have expected all old backups to fail in that case.
I've tried removing the SP2 update but this makes no difference?
Anyone has any ideas on what to do as I'm reluctant to update my live server without a working DR backup.


texts:
Okay first things first, this is probably a common issue which gets resolved going through all those questions and googling it but still I face this issue:

LSNRCTL> service
Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
TNS-12541: TNS:no listener
 TNS-12560: TNS:protocol adapter error
   TNS-00511: No listener
   Linux Error: 111: Connection refused

When I tnsping my service name it works:

$ tnsping TESTDB

TNS Ping Utility for Linux: Version 11.2.0.4.0

Copyright (c) 1997, 2013, Oracle.  All rights reserved.

Used parameter files:

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = FIHOST)(PORT = 1523))) (CONNECT_DATA = (SERVICE_NAME = TESTDB)))
OK (0 msec)

But not with:

$ tnsping FIHOST

Used parameter files:

Used HOSTNAME adapter to resolve the alias
Attempting to contact (DESCRIPTION=(CONNECT_DATA=(SERVICE_NAME=))(ADDRESS=(PROTOCOL=TCP)(HOST=172.18.23.199)(PORT=1521)))
TNS-12541: TNS:no listener

AND

$ tnsping FIHOST.level.com

Used parameter files:

Used HOSTNAME adapter to resolve the alias
Attempting to contact (DESCRIPTION=(CONNECT_DATA=(SERVICE_NAME=))(ADDRESS=(PROTOCOL=TCP)(HOST=172.18.23.199)(PORT=1521)))
TNS-12541: TNS:no listener

PS: FIHOST.level.com is registered in DNS

Listener.ora:

LISTENER_TESTDB =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = FIHOST.level.com)(PORT = 1653)

SID_LIST_LISTENER_TESTDB =
  (SID_LIST =
    (SID_DESC =
      (ORACLE_HOME = /u01/11.2.0.4/product)
      (SID_NAME = TESTDB)

Tnsnames.ora:

TESTDB =
  (DESCRIPTION =
   (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = FIHOST)(PORT = 1523))
    (CONNECT_DATA =
      (SERVICE_NAME = TESTDB)

Also, local_listener                       string      (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=FIHOST.filevel.com)(PORT=1653)))

There isn't sqlnet.ora configured.

Ideally, it should show which parameter file it uses and also it should be able to tnsping with hosts as well right? What could be the issue?

Thanks!


texts: MS SQL Server 2008R2, "Login failed for user" from time to time, IIS 7 , NET 4.0

texts:
I'm fighting with strange problem that that most probably is connected with Windows / MS SQL Server security/privileges setup. When external app (that runs with administrator privileges) polls the IIS server(it does login to database with already opened connection, checks for data) and when there is data to process(with the same connection when it then tries to get data) fails from time to time with this error:        Cannot open database "TheDatabase" requested by the login. The login failed. Login failed for user 'DatabaseUser'.
        Even worse, when this happens - every activity that involves database fails with same error message. So for a few minutes the database and thus whole website is down. But I'm not able to recreate this no matter what I do and I'm only able to see this in exception logs. Database user,that public login is mapped to, is 'dbowner' and nothing points to why it happens. This issue seem to appear after update of website code - ie this happened due to some particular changes in either Windows security or SQL Server config or code, but review of changes in sources gives no clues as everything seems 100% unrelated to this. Thanks in advance for helping with this.


texts:
I have two tables, e.g. "Locations" and "Connections"

"Locations" has values

Id | Dimension
---------------
 1 |    4
 2 |    8
 3 |    2

"Connections" maintains attributes

Origin | Destination | Value | Distance_KM
-------------------------------------------
   1   |      2      |  500  |     30
   1   |      3      |  100  |     20
   2   |      1      |  100  |     10
   2   |      3      |  300  |     10
   3   |      1      |  100  |     40

I want to create an output with the following Attribute Table. Where "In" correspond to "Destination" from "Connections" and "Out" to "Origin" accordingly.

Id | Dimension | In_Value | In_Count | In_Dist | Out_Value | Out_Count | Out_Dist
----------------------------------------------------------------------------------
 1 |     4     |    200   |     2    |    50   |    600    |    2      |     50
 2 |     8     |    500   |     1    |    30   |    400    |    2      |     20
 3 |     2     |    400   |     2    |    30   |    100    |    1      |     40

I can achieve the result that I strive for separately with two queries.

Query 1

SELECT C.Destination, SUM(C.Value) AS In_Value, COUNT(C.Destination) AS In_Count, SUM(C.Distance_KM) AS In_Dist
FROM Connections AS C
GROUP BY C.Destination

Query 2

SELECT C.Origin, SUM(C.Value) AS Out_Value, COUNT(C.Origin) AS Out_Count, SUM(C.Origine_KM) AS Out_Dist
FROM Connections AS C
GROUP BY C.Origin

Nevertheless, there should be only one query that solves my issue, is not it? I tried this but no success.

SELECT L.Id AS Id, L.Dimension AS Dimension, C.In_Value, C.In_Count, C.In_Dist, C.Out_Value, C.Out_Count, C.Out_Dist
FROM   Locations AS L
LEFT   JOIN (
             SELECT C.Destination,
                    SUM(C.Value) AS In_Value,
                    COUNT(C.Destination) AS In_Count,
                    SUM(C.Distance_KM) AS In_Dist
             FROM Connections AS C
             GROUP BY C.Destination
        ) ON L.Id = C.Destination
LEFT JOIN (
           SELECT C.Origin,
                  SUM(C.Value) AS Out_Value,
                  COUNT(C.Origin) AS Out_Count,
                  SUM(C.Origine_KM) AS Out_Dist
           FROM Connections AS C
           GROUP BY C.Origin
        ) ON L.Id = C.Origin

Basically, I do not know if I eligible to add a second LEFT JOIN ON to the query with already existing LEFT JOIN ON, am I?

References:

Two SQL LEFT JOINS produce incorrect result
Multiple left joins on multiple tables in one query


texts:
I call the table is [vDemo]
[]1

i want count MaLC by TenNV, result is

if record 13 MaLC is L then Count(x) = 16, Count(L) = 1


texts: Update all the elements of JSON array nested in a JSON column (Postgres)

texts:
I have json column with data that looks like this:

{
    a: [
        { q: 5 },
        { q: 8 },
        { q: 10 }
    ]
}

I'd like to map over the elements in the array and add an extra property to get something like this:

{
    a: [
        { q: 5, r: [] },
        { q: 8, r: [] },
        { q: 10, r: [] }
    ]
}

Any and all would be greatly appreciated.

PostgreSQL 9.6.6


texts: SQL database mail configuration to use a domain account that is not the SQL Service account

texts:
We have been asked to use a domain service account for everything releated to email (smtp). I looked at the SQL Database Mail and the only option that looked like Windows Auth. is the first one:

The problem is that the account we will use will not be the SQL service account.
Is there a way to make it work with a domain service account that is not the SQL Service account ?


texts: Repair GTID from Restore with sql_log_bin=0 and then drop table

texts:
I have been experimenting with mysql innodb cluster. One of the errors i've come across I don't know how to remedy or why exactly it causes a permanent death to any slave that wishes to catch up to the master - let me explain.

Create 3 instances of mysql. Use the innodb cluster utility to combine them into a cluster. You can use the AdminAPI to make sure the cluster is good. (use Single Primary mode so only 1 writer)

In the master mysql use mysql.exe to restore a dump of any table that has
sql_log_bin = 0;

You'll notice that this causes that table not to replicate to the other database instances. Now if you drop the table the other slaves die and can never be caught up to the master.

You can even create mysql instance from scratch and point it to the master and it will never catch up. It will say recovering and then it will fail.

How do you repair in the case of this scenario? And, what exactly is going on that a slave even from scratch can't be synced after you do this?


texts: How to add a day/night indicator to a timestamp column?

texts:
There is a timestamp column I use to indicate whether a row was created during day or night time. My code is the following, but for some reason I  only get 'DAY' as outcome. Am I not formatting the values right?

select record_id, rec_date,
       case when date_part('hour', rec_date) between 20 and 07 then 'Night'
            else 'Day' end as Indicator
from records;

The rec_date column is a timestamp where I can see values such as 2019-11-20 21:34:02.000000 - which should get a 'Night' indicator.


texts:
I am wanting to perform some index maintenance, in particular we have a poor performing query against a particular table.  Is it a sensible strategy to profile on RPC:Completed, SP:StmtCompleted and SQL:BatchCompleted but filtering only on that table?

The idea being I can focus my indexing maintenance on that table and work out what the best indexes will be?


texts: ORDER BY with IF-ELSE statement (DESC doesn't work)

texts:
Descending sort type does not work with this syntax, please help.

<?php

require_once("../connection/conn.php");

$compare = $_GET["sortfield"];

if(isset($_GET["sortfield"])){
    if(isset($_GET["sorttype"]) == "asc"){
        if($compare == "gender"){

            $holdData = $conn->prepare("SELECT * FROM informations ORDER BY gender ASC, first_name ASC");
            $holdData->execute();
            $getData = $holdData->fetchAll(PDO::FETCH_ASSOC);
            $newData = json_encode($getData);;
            print_r($newData);

        }elseif ($compare == "last_name") {

            $holdData = $conn->prepare("SELECT * FROM informations ORDER BY last_name ASC");
            $holdData->execute();
            $getData = $holdData->fetchAll(PDO::FETCH_ASSOC);
            $newData = json_encode($getData);;
            print_r($newData);

        }elseif ($compare == "first_name") {

            $holdData = $conn->prepare("SELECT * FROM informations ORDER BY first_name ASC");
            $holdData->execute();
            $getData = $holdData->fetchAll(PDO::FETCH_ASSOC);
            $newData = json_encode($getData);;
            print_r($newData);

        }elseif ($compare == "timestamp") {

            $holdData = $conn->prepare("SELECT * FROM informations ORDER BY timestamp ASC");
            $holdData->execute();
            $getData = $holdData->fetchAll(PDO::FETCH_ASSOC);
            $newData = json_encode($getData);;
            print_r($newData);
        }else{

        }

    }elseif(isset($_GET["sorttype"]) == "desc"){

        if($compare == "gender"){

            $holdData = $conn->prepare("SELECT * FROM informations ORDER BY gender DESC, first_name DESC");
            $holdData->execute();
            $getData = $holdData->fetchAll(PDO::FETCH_ASSOC);
            $newData = json_encode($getData);;
            print_r($newData);

        }elseif ($compare == "last_name") {

            $holdData = $conn->prepare("SELECT * FROM informations ORDER BY last_name DESC");
            $holdData->execute();
            $getData = $holdData->fetchAll(PDO::FETCH_ASSOC);
            $newData = json_encode($getData);;
            print_r($newData);

        }elseif ($compare == "first_name") {

            $holdData = $conn->prepare("SELECT * FROM informations ORDER BY first_name DESC");
            $holdData->execute();
            $getData = $holdData->fetchAll(PDO::FETCH_ASSOC);
            $newData = json_encode($getData);;
            print_r($newData);

        }elseif ($compare == "timestamp") {

            $holdData = $conn->prepare("SELECT * FROM informations ORDER BY timestamp DESC");
            $holdData->execute();
            $getData = $holdData->fetchAll(PDO::FETCH_ASSOC);
            $newData = json_encode($getData);;
            print_r($newData);
        }else{

        }

    }else{

    }
}else{
    echo json_encode(["Message"=>"No Sortfield and Sort type found!"]);
    echo "<br>";
    echo json_encode(["Message"=>"Please select the sortfield and sort type!"]);
}


texts: MySQL Error when importing CSV with empty fields

texts:
I have CSV file with exported table, that has rows with fields that do not contain values. Columns are separated using "," and if fields have no values they look like that: ,,,,,, I tried to load data using following code:

LOAD DATA INFILE "C:/table.csv"
INTO TABLE target_table
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 ROWS

Columns in target table (to be loaded with data from this CSV file) are nullable, but server returns error:

"Incorrect integer value"

When I put NULL between commas in CSV file, server do not return any error. Is it possible to change some setting to have table loaded without changing the CSV file?


texts: Oracle: finding rows without children in a table with a foreign key?

texts:
Suppose I have two tables with a foreign key relationship.  How can I find the rows in the "parent" table for which there is no corresponding "child" row?

For example:

create table a(x number primary key);
create table b(x number);
alter table b add constraint b_fk foreign key(x) references a(x) enable;

insert into a values(1);
insert into b values(1);
insert into a values(2);
commit;

delete from a where x = 2; -- will succeed
delete from a where x = 1; -- will fail, because there is a child row.

select ??? as "rows with no children" from a;

rows with no children
---------------------
                    2


texts:
I have been reading on Oracle hierarchical queries and all of the documentation/examples seem to be focused on SELECTing.

I have a table with parent-child relations and would like to change parents of children. Everything is done on the same table.

I can select current parents
I can select new parents
Old/new parent pairs have a common (otherwise unique) identifier

The data looks something like this:

ID | DISABLED | NAME | PARAM | PARENT
1  | 1        | qwer | 11    | NULL
2  | NULL     | qwer | 12    | NULL
3  | NULL     | tyui | 3     | 1
4  | NULL     | wert | 4     | 1
5  | NULL     | erty | 5     | 1
6  | 1        | asdf | 21    | NULL
7  | NULL     | asdf | 22    | NULL
8  | NULL     | ghjk | 6     | 6
9  | NULL     | hjkl | 7     | 6
10 | NULL     | jklp | 8     | 6

And expected output (changed IDs: 3,4,5 and 8,9,10):

ID | DISABLED | NAME | PARAM | PARENT
1  | 1        | qwer | 11    | NULL
2  | NULL     | qwer | 12    | NULL
3  | NULL     | tyui | 3     | 2
4  | NULL     | wert | 4     | 2
5  | NULL     | erty | 5     | 2
6  | 1        | asdf | 21    | NULL
7  | NULL     | asdf | 22    | NULL
8  | NULL     | ghjk | 6     | 7
9  | NULL     | hjkl | 7     | 7
10 | NULL     | jklp | 8     | 7

EXTRA INFO:
The data consists of multiple roots, each having multiple children (I'm testing on a table with ~20k records). The data has only 2 levels. Although I am interested in the PARAM value, I cannot simply update that, because part of application references "correct" roots and another part references children pointing to broken roots, so reparenting is the only option.

I can distinguish roots from children (PARENT IS NULL)
I can find roots to be changed (DISABLED IS NOT NULL)
I can find replacement root IDs (NAME columns are identical)

Parents to be changed are not static (have to be found in database).

To better illustrate this is how replacement pairs can be constructed (not good at this):

SELECT
  NEW.ID REPLACEMENT,
  OLD.ID TO_REPLACE
FROM
  HIERARCHICAL OLD
LEFT JOIN
  HIERARCHICAL NEW
  ON
    OLD.NAME = NEW.NAME
    AND NEW.DISABLED IS NULL
WHERE
  OLD.PARENT IS NULL
  AND OLD.DISABLED IS NOT NULL
  AND NEW.ID is NOT NULL

I would like to update the table so that entry 3 is parented to 2 instead of 1. Of course there are multiple disabled roots so handwriting updates would be a pain.

I cannot find examples to at least get on track with this, so anything would be appreciated.

I can do this with LOOPs, something like:

DECLARE CURSOR c1 IS
  SELECT OLDPARENT, NEWPARENT FROM
  <...>;
BEGIN
  FOR item IN c1 LOOP
    EXECUTE IMMEDIATE 'UPDATE <...>'
  END LOOP;
END;

But performance of such query is dreadful.


texts: Error 13542 enabling system-versioning for existing tables

texts:
I'm trying to enable system-versioning for some existing tables in a SQL Server 2016 database, which contain data. I'm following these instructions from Microsoft.

One of the tables looks like this:

CREATE TABLE [dbo].[ClientBeacon](
    [ClientId] [int] NOT NULL,
    [BeaconId] [int] NOT NULL,
    [FromDate] [datetime] NOT NULL,
    [ToDate] [datetime] NULL,
    [Deleted] [bit] NOT NULL,
    [ModifiedByUserId] [nvarchar](128) NOT NULL,
    [ModifiedOn] [datetime] NOT NULL,
    [Timestamp] [timestamp] NOT NULL,
    CONSTRAINT [PK_ClientBeacon] PRIMARY KEY CLUSTERED
    (
        [ClientId] ASC,
        [BeaconId] ASC
    )
)

And the script I'm trying to run looks like this:

CREATE SCHEMA History;
GO

ALTER TABLE dbo.ClientBeacon
   ADD
      SysStartTime datetime2(0) GENERATED ALWAYS AS ROW START HIDDEN CONSTRAINT DF_ClientBeacon_SysStartTime DEFAULT SYSUTCDATETIME(),
      SysEndTime datetime2(0) GENERATED ALWAYS AS ROW END HIDDEN CONSTRAINT DF_ClientBeacon_SysEndTime DEFAULT CONVERT(datetime2 (0), '9999-12-31 23:59:59'),
      PERIOD FOR SYSTEM_TIME (SysStartTime, SysEndTime);
GO

ALTER TABLE dbo.ClientBeacon SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = History.ClientBeacon));
GO

The result of running the script is the following error:

Msg 13542, Level 16, State 0, Line 4
ADD PERIOD FOR SYSTEM_TIME on table 'test.dbo.ClientBeacon' failed because there are open records with start of period set to a value in the future.
Msg 13510, Level 16, State 1, Line 11
Cannot set SYSTEM_VERSIONING to ON when SYSTEM_TIME period is not defined.

There isn't much I can find about this error. There's a suggestion in a comment on this post that using UTC in the default might be an issue if UTC is ahead of the server time, but I'm in the UK on daylight savings time, an hour ahead of UTC. I've tried using GETDATE just in case, but the same error occurs.


texts:
Here are my config options:

options: { config: "/etc/mongod.conf"
, net: { bindIp: "127.0.0.1, 10.0.2.57" }
, processManagement: { fork: true, pidFilePath: "/var/run/mongodb/mongod.pid" }
, storage: { dbPath: "/data" }
, systemLog: { destination: "file", logAppend: true, path: "/log/mongod.log" }
}

First thing I do is check to see if any mongod is running using

ps aux | grep mongod

I see no results as expected (I've run sudo chkconfig mongod off to make sure the service doesn't start at boot).

When I run

sudo mongod service start

And check /log/mongod.log I see this

getaddrinfo(" 10.0.2.57") failed: Name or service not known
ERROR: listen(): bind() failed errno:98 Address already in use for socket: 0.0.0.0:27017

I then run this

netstat -an | grep :27017

And get no results. So then I check to see if a mongod is running using

ps aux | grep mongod

I get get no results.

When I remove my 10.0.2.57 from the config file the service starts and I can connect to it.

Do I need to explicitly have that 10.0.2.57 in my conf file to allow external connections to mongo? If so, what am I doing wrong?

Disclaimer: I'm very weak with Linux.


texts: How can we check for data integrity when we upgrade from MySQL 4.1 to MySQL 5.5?

texts:
By data integrity, I mean:

Schema integrity
Data (record level) integrity

Checksum was an option, but since the storage format for some data types in MySQL 5.5 has been changed, it won't be reliable.

Another option is to parse the XML file generated by mysqldump. Since the file is too large (in GBs), is it worth it?

Any other way you have confirmed it?


texts: PGAdmin3 connection with non standard SSH port and SSH tunnel

texts: Hi I am trying to connect to Postgres from pgAdmin3 using SSH tunneling. However I am not using standard port 22. How can I do SSH tunneling using a non standard port?


texts: Stop SQL job if it runs past a certain time

texts:
I have a SQL job which executes a stored procedure. The proc loops through a number of databases, then executes a series of dynamic SQL queries to archive/delete a bunch of tables in each database.  Each of the dynamic queries itself loops because the deletes are happening in smaller batches. (As below)
This job runs overnight but I'd like it to stop if it runs to 7:00am, since that will run into production hours.
I know that I could create a separate additional job that runs "dbo.sp_stop_job" to stop my archive job, but I'd prefer to do it within the archive stored procedure itself.
I'd like for the proc to exit at 7:00am regardless of whether the execution is between tables or if it's between delete batches within each table, but don't really want to have to code the same condition over and over all through it.
I know I might be asking a lot here, but any thoughts on this?
DECLARE CURSOR_DB CURSOR FAST_FORWARD FOR
        SELECT DatabaseName
        FROM #Databases
OPEN CURSOR_DB
FETCH NEXT FROM CURSOR_DB INTO @sourceDB
WHILE @@FETCH_STATUS = 0
BEGIN

    SET @SQL = 'DELETE tbl1 in batches'
    EXEC (@SQL)
    SET @SQL = 'DELETE tbl2 in batches'
    EXEC (@SQL)
    SET @SQL = 'DELETE tbl3 in batches'
    EXEC (@SQL)

    FETCH NEXT FROM CURSOR_DB INTO @sourceDB
END
CLOSE CURSOR_DB
DEALLOCATE CURSOR_DB


texts: Using SSIS to extract query data and present it through a cube

texts:
The problem: I have a client who is running their application on SQL Server. They use SQL Server Analysis Services for additional reporting based on the cube that's been provided. It turns out, there is one query they are looking to run that is written in SQL and is fairly complex. The cube doesn't give them the data that they are looking for.

The workaround: As the client, they don't have access to directly query the database. Hence, they send me this query, and every week, I send them back the results on a spreadsheet.

Proposed solution: I believe I would be able to use SQL Server Integration Services to work around this whereby, I can automate the creation of a table with the results of said query which they would be able to query using their existing access to Analysis Services. Am I correct in saying this?

If so, I believe I need to create an SSIS package with certain control and data flows. What is the high level structure of what needs to be built?

P.S. In looking so far, I see a control flow task called "Execute SQL Task". I imagine I would have to build on this.


texts: I am looking at mysql installation (Version 5.7.19). The mysqld is running and has innodb_file_per_table set to on. Looking at the tables using a mysql client shows up that tables all have engine "InnoDB" set. There are files ibdata1 and ibdata2 in innodb_data_file_path and also the .ibd files. I wondering now since it was my newbee understanding that innodb_files_per_table obsoltes the ibdata* files? Am I wrong or is the presence of the .ibd files and ibdata* files at the same time an indication for a mis-configured database?


texts: SQL Server Management Studio creates 18456 login error using registered server group

texts:
I have been using "Local Server Groups" registration for quite a while now with SQL Server 2008 Management Studio.
I have created a group with 5 registered servers. They are registered using SQL authentication with the same login. That login has the same default database set on each of those servers. I can successfully connect to that group and query.
Today I changed the last server in the list of the registered servers group and changed it's connection details from "default database" to a specific database name that is different (*1) than the default database used for the other registered servers. It is assured that the login / password is correct, the login has access to the default and explicitly configured databases and all the databases exist.
Edit 1: I meanwhile figured out that it does not matter at all which database is configured for the connection, as long as there is something else configured other than "default db" the error comes - even if the configured db is the same as the default database, just typed explicitly.)
Since that moment, a SQL Alert for error 18456(=invalid login), that is configured to raise on each of the servers, showed up when connecting to the group for the FIRST server in the list. However, this alert definitely is WRONG because the connection was successfully established for all registered servers and I can query the databases as expected.
After deleting this first server from the list of registered servers, the same wrong login error alert 18456 showed up again only for the (new) first registered server in the group.
When I changed all the registered servers in the group back to use the default database for connect, this behavior disappeared.
I looked for a similar bug description but found nothing. Does anyone have any experience or reasons for this?
Details about SSMS:
Microsoft SQL Server Management Studio                      10.50.6220.0
Microsoft Analysis Services Client Tools                        10.50.6000.34
Microsoft Data Access Components (MDAC)                     6.1.7601.17514
Microsoft MSXML                     3.0 4.0 6.0
Microsoft Internet Explorer                     9.11.9600.18282
Microsoft .NET Framework                        2.0.50727.5485
Operating System                        6.1.7601

Error log details:

Date      09.05.2016 16:38:52 Log     SQL Server (Current - 08.05.2016
23:59:00)
Source        Logon
Message Error: 18456, Severity: 14, State: 38.
Date      09.05.2016 16:38:52 Log     SQL Server (Current - 08.05.2016
23:59:00)
Source        Logon
Message Login failed for user 'xxx'. Reason: Failed to open the
explicitly specified database. [CLIENT: 1.2.3.4]

EDIT 2:
Even if all registered servers have "default database" selected but the login's configured default database on one (not the first) of the registered servers is different, then the same behavior shows up - the error is raised for the first server in the list.


texts: Clarification on Clustered index scan and Clustered Index Seek

texts:
I have created one table with 80000 records with Cluster Index on CustomerID column.  When I queried the following statements the execution plan behaves in differently.  Can anyone clarify the same.

select * from customer

  Showing Cluster Index Scan,  So this is correct.

select * from customer where CustomerID=80000

  Execution plan shows ClusterIndex Seek,  So this is correct.

select * from customer where CustomerID between 1 and 70000

  Execution plan still shows ClusterIndex Seek why?

As per the statistics the row count is 90% then the optimizer have to use 'ClusterIndex Scan' but it's using 'Cluster Index Seek' Why?  All statistics are up to date and I have checked the Estimated row and actual rows are same then why the optimizer choose 'Cluster Index Seek' For getting 90% of rows among total records.


texts:
I was at an interview for a new job today and got asked a question regarding a SELECT query:

SELECT lastname FROM users WHERE name='John';

This SELECT query is to run over a million rows.

I was asked: How would you make that faster/more efficient?

I didn't have the answer, and some googling also didn't reveal anything to me.

I know the possibility of this question being closed is quite high, but I'd really like to know how you would make that query run faster. Any ideas?


texts: Is it possible to implement automation for Oracle and DB2 with Jenkins and or Ansible?

texts: I'm curious if anyone has successfully used Jenkins and/or Ansible for automating routine tasks for Oracle and DB2 databases. From what I've looked up online options seem very limited. So far automation for these seem to amount to using a scheduler to run scripts or batch jobs. I'm trying to implement something more robust.


texts: POWA analyzer displays error 500 after logging in

texts:
I tried to install POWA web analyzer. I can see the login page but after login there is a 500 error message.
This is the error at the server:
ProgrammingError: (psycopg2.errors.InsufficientPrivilege) permission denied for table powa_servers

    [SQL: SELECT
           CASE WHEN id = 0 THEN
              '<local>'
           ELSE
              COALESCE(alias, hostname || ':' || port)
           END AS alias,
            error
            FROM powa_servers s
            JOIN (SELECT srvid, unnest(errors) error
                FROM powa_snapshot_metas
                WHERE errors IS NOT NULL
            ) m ON m.srvid = s.id]
    (Background on this error at: http://sqlalche.me/e/13/f405)

I granted all permissions to powa user in postgres and loggin in using this user.


texts: Why does the Tuple Mover in C-Store consider only rows older than LWM?

texts:
In the Tuple Mover section of the C-Store paper by Michael Stonebraker (link: http://db.csail.mit.edu/projects/cstore/vldb.pdf) the following is described:

  MOP (merge out process) will find all records in the chosen WS segment with an insertion time at or before the LWM (low water mark; a timestamp order/epoch value) [...] The most recent insertion time of a record in RS’ becomes the segment’s new t_lastmove and is always less than or equal to the LWM. [...] Hence, LWM “chases” HWM (high water mark), and the delta between them is chosen to mediate between the needs of users who want historical access and the WS space constraints.

I could not understand, when moving records from WS (write optimized storage) to RS (read optimized storage), why the tuple mover considers only records older than LWM? Doesn't this mean that all the rows that were inserted in the system after LWM would only be in WS? In a system, with small LWM, i.e, in a system where old historical queries are supported, this may mean that much of the records would be in WS only and we would miss out all the optimizations provided by the read optimized storage.

Am I missing something?


texts: Reference the database from a stored proc?

texts:
I'm using this T-SQL to get index stats (edited for brevity):

SET @db_id = DB_ID(N'TestDB');
SET @object_id = OBJECT_ID(N'TestDB.dbo.Users');
SELECT * FROM sys.dm_db_index_physical_stats(@db_id, @object_id, NULL, NULL , 'LIMITED');

I want to be able to encapsulate this into a stored proc and call it for whichever database the stored proc is in, but the DB name is hard-coded, how can I get the database name dynamically?

Does T-SQL have anything like:

  SET @db_id = DB_ID(this);


texts: Effect on concurrency of breaking queries into multiple statements

texts:
Let's say I have a paginated search query which takes a long time (when the offset is large) due to a large number of rows in the joined tables. The purpose of the query is to search through accounts, while also fetching whether the current user (account ID 42) has "followed" that account by doing a left join:

SELECT a.id, a.nickname, IF(c.id IS NOT NULL, 1, NULL) AS `is_followed`
FROM Account AS a
LEFT JOIN Contacts AS c
ON
    c.contact_account_id = a.id
    AND c.account_id = 42
WHERE
    (a.id != 42)
    AND (a.nickname LIKE 'the%')
LIMIT 32 OFFSET 50000;

Is there any concurrency benefit in breaking this into 2 statements which store intermediate results in a temporary table before doing the join?

CREATE TEMPORARY TABLE temp (
  SELECT a.id, a.nickname
  FROM Account AS a
  WHERE
    (a.id != 42)
    AND (a.nickname LIKE 'the%')
  LIMIT 32 OFFSET 50000;
);

SELECT temp.*, IF(c.id IS NOT NULL, 1, NULL) AS `is_followed`
FROM temp
LEFT JOIN Contacts AS c
ON
    c.contact_account_id = a.id
    AND c.account_id = 42

(The original single statement takes ~200ms, while the latter temporary table creation takes ~150ms and the subsequent join takes ~50ms. Total time remains roughly the same.)

Does this allow mysqld to more easily interleave work from other connections' threads in between the CREATE TEMPORARY TABLE and the SELECT, or is this just as bad (or worse) for concurrency than a single statement that takes longer?

Update 1

Extended EXPLAIN of single-statement query:

[
    {
        "id" : 1,
        "select_type" : "SIMPLE",
        "table" : "a",
        "type" : "range",
        "possible_keys" : "PRIMARY,nickname",
        "key" : "nickname",
        "key_len" : "303",
        "ref" : null,
        "rows" : 106880,
        "filtered" : 100.00,
        "Extra" : "Using where; Using index"
    },
    {
        "id" : 1,
        "select_type" : "SIMPLE",
        "table" : "c",
        "type" : "eq_ref",
        "possible_keys" : "Contact_uk_account_id,Contact_uk_contact_account_id,Contact_fk_contact_account_id",
        "key" : "Contact_uk_account_id",
        "key_len" : "8",
        "ref" : "const,a.id",
        "rows" : 1,
        "filtered" : 100.00,
        "Extra" : "Using index"
    }
]

Extended EXPLAIN of statements in decomposed query:

SELECT inside CREATE TEMPORARY TABLE:

[
    {
        "id" : 1,
        "select_type" : "SIMPLE",
        "table" : "a",
        "type" : "range",
        "possible_keys" : "PRIMARY,nickname",
        "key" : "nickname",
        "key_len" : "303",
        "ref" : null,
        "rows" : 106880,
        "filtered" : 100.00,
        "Extra" : "Using where; Using index"
    }
]

SELECT from temp with LEFT JOIN

[
    {
        "id" : 1,
        "select_type" : "SIMPLE",
        "table" : "temp",
        "type" : "ALL",
        "possible_keys" : null,
        "key" : null,
        "key_len" : null,
        "ref" : null,
        "rows" : 32,
        "filtered" : 100.00,
        "Extra" : ""
    },
    {
        "id" : 1,
        "select_type" : "SIMPLE",
        "table" : "c",
        "type" : "eq_ref",
        "possible_keys" : "Contact_uk_account_id,Contact_uk_contact_account_id,Contact_fk_contact_account_id",
        "key" : "Contact_uk_account_id",
        "key_len" : "8",
        "ref" : "const,temp.id",
        "rows" : 1,
        "filtered" : 100.00,
        "Extra" : "Using index"
    }
]


texts: Sequential GUID or bigint for 'huge' database table PK

texts:
I know this type of question comes up a lot, but I've yet to read any compelling arguments to help me make this decision. Please bear with me!

I have a huge database - it grows by about 10,000,000 records per day. The data is relational, and for performance reasons I load the table with BULK COPY. For this reason, I need to generate keys for the rows, and cannot rely on an IDENTITY column.

A 64-bit integer - a bigint - is wide enough for me to use, but in order to guarantee uniqueness, I need a centralised generator to make my IDs for me. I currently have such a generator service which allows a service to reserve X sequence numbers and guarantees no collisions. However, a consequence of this is that all the services I have are reliant on this one centralised generator, and so I'm limited in how I can distribute my system and am not happy about the other dependencies (such as requiring network access) imposed by this design. This has been a problem on occasion.

I'm now considering using sequential GUIDs as my primary keys (generated externally to SQL). As far as I've been able to ascertain from my own testing, the only drawback to these is the disk space overhead of a wider data type (which is exacerbated by their use in indexes). I've not witnessed any discernible slowdown in query performance, compared to the bigint alternative. Loading the table with BULK COPY is slightly slower, but not by much. My GUID-based indexes are not becoming fragmented thanks to my sequential GUID implementation.

Basically, what I want to know is if there are any other considerations I may have overlooked. At the moment, I'm inclined to take the leap and start using GUIDs. I'm by no means a database expert, so I'd really appreciate any guidance.


texts:
I'm trying to sift through the remains of an event that was causing us a lot troubles. Aside from report to management about what happened, I'm trying to gain a much better understanding of how this situation arose and how to avoid it in the future. Also, sorry for the long story, I'm just trying to provide detail.

We have two databases on our Mongo servers.
Our server is:

Virtualised
initially had ~75GB free space on the mongo data drive (that increased)
has 4x Xeon X5650@2.67GHz (virtualised)
has 3.7GB of RAM in total
and is a dedicated Mongo instance.

Database A contains data which is more or less a LIFO queue, data is inserted, and processed in batches over the day with the oldest being processed first. After it's processed it's deleted. This is the bigger of the two, and the one with the higher Read/Write activity, being about 1.3Gb. In the past it has reached up to 10-13Gb. It's essential a user activity tracking log on a website. For all intent and purposes it's a very large number of 1 dimensional array and comparatively small document sizes.

Database B contains a transaction log for users. Data is read and written randomly, but in contiguous times blocks. While a user is logged in, that particular user's document is more likely to be accessed. It's about 0.3Gb, and has grown, but only minimally.

We'd been experiencing high Disk Io for a while, but in the last couple of days, it took a jump from about 28Mbps to 35Mbps, which is at our limit (registering at 100%). When this happened, our CPU rose steadily, but the RAM stayed where it was, at about 750Mb.

Looking at MMS, during this period, Database A seemed to be chugging along happily, but Database B suddenly started getting a lot of page_faults, accessesNotInMemory, and pageFaultExceptionsThrown. We were not getting any b-tree misses. Our totalOpen and clientCursor_size rose steadily, but never hit their peak.

The storageSize for the database was about 13Gb, so about an order of magnitude larger than the actual data size.

We restarted the mongo server, and this caused our global RAM use to jump from 705Mb to about 1530Mb, and stayed there, but the CPU rose up again, and diskio jumped almost straight up.

MMS showed the same kind of behaviour as above for hours after the restart.

We attempted to re-create the indexes one by one on Database B (rather than reIndex() to ease load as it was during our peak period). We found one index, which after we dropped it, we would try to ensureIndex, it would run for a few seconds, and then send us back to the console, but db.collection.stats() would not show the index. We managed to recreate it in a sense by creating the reverse ordered index.

During a low period we ran db.repairDatabase() on both the database and this has fixed all our problems. DiskIO has dropped to nearly nothing; RAM is sitting marginally higher now about 1.7GB; load and CPU are barely registering; the DB storageSize has dropped to about 1.2Gb; no more page faults; and our cursor count sits around 0.

I am completely at a loss as to explain what happened. The indexes were in memory. MongoDB  looked like it had both the database and indexes entirely stored in RAM (after the restart). But we were still getting what looked to be lots of disk access on Database B.

My question: why was MongoDB not storing Database B in RAM, like it should, and kept going to disk to records?

EDIT: Attached NewRelic and MMS Graphs.


texts: SQL Server Express to SQL Server Standard upgrade?

texts:
The database size on our SQL Server Express database is around 10GB. I checked our database size and it said 38GB.

Do I need to upgrade to SQL Server Standard edition?
Is it expensive to upgrade?
Would the web hosting company be able to provide us with the license?

Our website has been very slow and unreliable lately


texts: AlwaysON Availability Groups - IP address & subnet change of secondary replica node

texts:
This is a follow up from question:
AlwaysON Availability Groups - IP address change of secondary replica node

Same scenario, Availability Group in synchronous commit mode, one primary and one secondary replica in a multi-subnet configuration.  The cluster nodes are physical machines.  Due to hardware maintenance, the server used by the secondary replica cluster node is being moved, so will be offline and come back online with a different IP address in a different subnet.  How would one approach this?

My initial thoughts are:  If possible, add the new network interface to the secondary node.  Configure the new network interface with the new IP address which is in a different subnet.  The cluster should automatically build the internal routes and register the new network interface.

In FCM (Failover Cluster Manager) a new cluster network will appear under the 'Networks' tab.  In cluster core resources, add the new network and create a new static IP address for the cluster VNN.  Apply the changes then go back in and add an OR dependency on the new IP address.

Prior to the server move outage where the old IP address/network interface will be removed, in FCM, go back to cluster core resources, go into the properties of the cluster VNN and remove the old network/static IP address and remove any dependencies on it.  When the server is taken offline and comes back online, the old network interface won't be visible to the server and won't appear in the networks tab in FCM.  There shouldn't be any issues with any clustered resources as any dependencies on the old IP/subnet/network were removed.

Is there anything else that should be taken into consideration?  Since the cluster nodes are physical, does this complicate matters with regards to the network interfaces?  With testing in a virtual environment it's obviously simplified as removing and attaching virtual network switches is easy.


texts: commit statement missing for a dml statement in binary log

texts:
I have noticed 'commit' statement missing for a DML statement in binary log. Does it mean that this statement is rolled back in server? I am using mysql 5.5.40.

sample binlog

at 159527792

160229 23:25:22 server id 104  end_log_pos 159528127   Query   thread_id=45195 exec_time=0     error_code=0(in comments)

SET TIMESTAMP=1456809922/!/;

update table_name set col1=4 where col2=4
/!/;

at 159528275

160229 23:25:22 server id 104  end_log_pos 159528484   Query   thread_id=45195 exec_time=0     error_code=0(in comments)

SET TIMESTAMP=1456809922/!/;

insert into table_name values (1)
/!/;

at 159528632

160229 23:25:22 server id 104  end_log_pos 159528511   Xid = 22993533(in comments)

COMMIT/!/;

As per mysql documentation

Binary logging is done immediately after a statement or transaction completes but before any locks are released or any commit is done. This ensures that the log is logged in commit order.

http://dev.mysql.com/doc/refman/5.5/en/binary-log.html

please let me know whether my undersatnding is correct or not. if any statement getting rolled back, server write rollback to binlog?


texts: Problem trying to remove sub-query and use WHERE only to return certain rows

texts:
I am extremely new to SQL queries. I am studying for Microsoft 70-461 and came to an example that I thought could be simplified, but my version is returning the wrong results, and I wanted to understand why.

This study guide show this query:

USE TSQL2012;
SELECT productid, productname, unitprice, P.supplierid
FROM Production.Products AS P
WHERE supplierid IN
    (SELECT supplier
     FROM Production.Suppliers
     WHERE country = N'Japan');

Which correctly returns 6 rows,
My query:

USE TSQL2012;
SELECT productid, productname, unitprice, P.supplierid
FROM Production.Products AS P, Production.Suppliers AS S
WHERE S.country = N'Japan':

Returns 231 rows, the production.products table only contains like 70 rows to begin with so I don't get it. Can somebody please tell me what I am overlooking. Thank you in advance.

The sample database, "TSQL2012", can be downloaded here.


texts: Hint to force locks between 2 Select clauses

texts:
I have a process that run in parallel on several threads and also on several machines.

In summary, the process has to 'lock' a row in a table named 'Queue' and then retrieves some columns from the locked row and do some other logic operations.

My objective is to do this with the best performance: reliable info and fastest way.

This is how I design the query of the process:

UPDATE  MQ.dbo.Queue
            SET     queue_lock = 1,
                    Queue_Key = 'H10_P19_T0_19032017T193926148',
                    Queue_LockDate = getdate()
            where   Queue_ID = (select top 1 Queue_ID
                                    from MQ.dbo.Queue with (nolock)
                                    where queue_lock = 0
                                    and QueueName_ID = 16
                                    order by queue_id)
            And     isnull(queue_lock,0) = 0

GetQueue (Datasource=mydatabase, Time=1.12 ms, Records=0) in F:\Web\Process\Component\MQ.cfc

Select  top 1 Queue_ID,Queue_Message
            From    MQ.dbo.Queue with (nolock)
            Where   QueueName_ID = 16
                    and queue_lock = 1
                    and Queue_Key = 'H10_P19_T0_19032017T193926148'

The Queue_key consist of:

Host_ID
Process_ID
Thread_ID
Datestamp

This choice (Queue_key) was taken to tolerate parallelism on the process with reliable results.

My problem is that in some cases the second query (Select top 1....) returns 0 records even so it should not, since there are a lot of waiting row in the table 'Queue'.

And the percentage of this case rise when I rise the parallelism.

I know where is the gap in this 2 queries but I search for a way to overcome it and I need your help doing this.

In fact, the gap is in the select clause of the first query (the sub-query): Since select clauses hold only shared lock and 2 shared locks (2 select) don't block each other (are compatible), it is possible that 2 threads running in parallel retrieve the same row and only one update take place (because update hold exclusive lock on the row). So the second query (select top 1 ...) returns result for only one thread.

So I need through a lock hint (maybe updlock or rowlock), to don't allow 2 select clauses to retrieve the same row, without degrading a lot the response time.

Rq: the with (nolock) hint was added to increase the response time since shared lock is compatible with another shared lock (either with this hint or without it).

Can anyone have a suggestion for this type of scenarios?


texts:
I'm designing a database for a new project and I'm looking for some feedback on the design before I start to implement. It basically consists of secure messaging system with those abilities :

One user can send a message to one or many users
Messages can be sent both to registered and unregistred users
User can be a person or a company
A company can have multiple user but only one administrator

1°) I am having difficulties with choosing how to approach the relashionship between Messages and User and particularly how to manage unregistred users.

2°) Is the relationship between User and userGroup a good approach for multi-users ?

I will appreciate any feedback given :)
Many thanks ! and sorry for my english...


texts: How to store search keyword or browsing history in database

texts:
I'm making my final year project. Which is a website.
Things I can't do:

Store the browsing history of users between their login and logout session on the site.
Or
Store the search keywords.

But I dont know how to do it. Is there any query or functions that I can use???
I'm using xampp server.

I'm really stuck in this phase and don't know how to do it.
One of my friend told me it's IMPOSSIBLE.
Does anyone have a possible solution rather than just IMPOSSIBLE.
Please help.


texts: The process could not execute 'sp_replcmds' on 'S15415940'. Cannot execute as the database principal because the principal "dbo" does not exist

texts:
Can anyone show me the way to fix these error messages:

  The process could not execute 'sp_replcmds' on 'S15415940'. (Source:
  MSSQL_REPL, Error number: MSSQL_REPL20011)

  Cannot execute as the database principal because the principal "dbo"
  does not exist, this type of principal cannot be impersonated, or you
  do not have permission. (Source: MSSQLServer, Error number: 15517)

  The process could not execute 'sp_replcmds' on 'S15415940'. (Source:
  MSSQL_REPL, Error number: MSSQL_REPL22037)

Thanks you!


texts: Transactional Replication Update process

texts:
I need to create report using ssrs. for that i create the main table contains the values from nine other tables which is the Source tables. Now the new source tables which are used for main table are in another server.So using transactional replication I got all the source tables from another server to my datawarehouse.Now I need the main table values are get automatically updated whenever the transactions(updates) made in the nine source tables.

I am new to replication So please help me.

Thanks in advance


texts:
I have a database with 25 M record in 42 tables(all tables is Myisam). When the number of users increases, the site is slowed down . pleas help me .

Server Config: Linux , Centos
Ram : 30 GB
CPU : 20 Core

my.cnf parameters:

[client] port = 3306 socket = /var/lib/mysql/mysql.sock

[mysqld] port = 3306
socket = /var/lib/mysql/mysql.sock local-infile=0
 skip-locking skip-innodb
 wait_timeout=50
 max_connections=550
query_cache_type=1
 query_cache_limit=1M
interactive_timeout=50
connect_timeout=10
max_allowed_packet = 128M
join_buffer=1M
table_open_cache = 1024
sort_buffer_size = 30M
read_buffer_size = 30M
read_rnd_buffer_size = 8M
key_buffer_size = 6G
bulk_insert_buffer_size = 256M
myisam_sort_buffer_size = 246M
myisam_max_sort_file_size = 4G
myisam_repair_threads = 1
myisam_recover thread_cache_size = 16
query_cache_size = 960M
 max_connect_errors=10
record_buffer=1M
table_cache=2048
thread_concurrency = 40
log-bin=mysql-bin
 server-id = 1

[safe_mysqld] err-log=/var/log/mysqld.log open_files_limit=65535 [mysqldump] quick max_allowed_packet = 128M

[mysql] no-auto-rehash

[myisamchk] key_buffer_size = 512M sort_buffer_size = 256M read_buffer = 16M write_buffer = 16M

[mysqlhotcopy] interactive-timeout


texts: Difference Between Database Mail Profile and SQL Server Agent Operator in MS SQL Server

texts:
I have configured Database Mail profile with several accounts. In the SQL Server Agent Properties I've enabled my profile.

If I specify whom to send email with Database Mail profile, why do I need to use Operators which are provided by the Agent? What is their difference?

Thanks for your time.


texts: Setting password for 9.3 PostgreSQL server

texts:
I currently have a 9.3 server running (on Ubuntu 14.04) with "trust" set as the authentification method in pg_hba.conf. How can I set the password again. In pgadmin the server currently doesn't allow me to access the console (The PSQL console closes immediately after I open it).

I have the feeling that setting the password using

     $ sudo -u postgres psql

changes the password of a parallel running 9.1 server.

Using

     psql -U postgres

gives an error saying

   psql: FATAL:  Peer-Authentifizierung für Benutzer »postgres« fehlgeschlagen

(peer authentification for user postgres failed)

pg_hba.conf

    # Database administrative login by Unix domain socket
    local   all             postgres                                trust

    # TYPE  DATABASE        USER            ADDRESS                 METHOD

    # "local" is for Unix domain socket connections only
    local   all             all                                     trust
    # IPv4 local connections:
    host    all             all             127.0.0.1/32            md5
    # IPv6 local connections:
    host    all             all             ::1/128                 md5
    # Allow replication connections from localhost, by a user with the
    # replication privilege.
    #local   replication     postgres                                peer
    #host    replication     postgres        127.0.0.1/32            md5
    #host    replication     postgres        ::1/128                 md5

Setting password in pgadmin

After setting the password for the postgres user (1), the ENCRYPTED PASSWORD appears in pgadmin (2), but psql -U postgres still fails.

Previous question:

Authentification failure for PostgreSQL server (9.3)


texts: Generally unsolvable problem? How to ORDER BY aggregated columns GROUPED BY many possible columns very fast?

texts:
Is there general solution or some best practises or at least some vague hint, how to solve following problem (general, but concretised in following example)? I think this must have been solved many times. Let's say I want to make analytics of posts being shared on many twitter profiles.

I've got big table (let's assume 1M rows, but potentially it can be 100 or 1000x larger), with posts and their statistics and table with monitored profiles:

CREATE TABLE post
(
id int,
id_profile int,
created_date date,
text varchar(255),
shared_link varchar(255),
comments int,
stars int
);

CREATE TABLE profile
(
id int,
name varchar(255),
link varchar(255)
);

Because each post is shared on many profiles (possibly many times), I want to see aggregated statistics for each post, profile, days or mixed (ex.: show how much stars had each post on all profiles each day). So something like this:

SELECT * FROM (
    SELECT po.created_date, po.shared_link, sum(comments) as comments, sum(stars) as stars
    FROM post po
    INNER JOIN profile pr ON (po.profile_id = pr.id)
    WHERE po.created_date >= '2015-06-01' AND po.created_date <= '2015-06-30'
    GROUP BY XXXGROUP
) as a
ORDER BY XXXORDER DESC
LIMIT 20
OFFSET 0

While:

XXXGROUP can be: {po.shared_link} or {po.id_profile} or {po.created_date} or {po.shared_link, po.created_date} or {po.id_profile, po.created_date} or {po.shared_link, po.id_profile} (number of possible group by options with n groupable columns grows by 2^n-2)

XXXORDER can be every aggregated column or created date.

This is generally known problem - for working pagination I need to group in subselect and then sort this subselect, which is of course pain in the ass because of missing indexes. But I need results of this query to have in real time, to be shown at frontend in graphs and grids.

I already read some articles about map reduce, so I have some idea how that work theoretically and I know I will have to do it somehow like this, but I'm not sure if and how exactly is that possible in postgreSQL? Almost everything I found was mongoDB related. I'd like to see some best practises with pgSQL.

So my main question is, what is the best solution and best practises to get same result as this query, but fast and ideally in pgSQL?

And btw I still wonder: As I said, number of possible group by options with n groupable columns grows by 2^n-2, is it needed to write nearly 2^n map functions and for every map-reduce function there is need for special table for reduced results, am I wrong? Google has tens or maybe hundereds groupable criteria in analytics, even if it is possible to use only 3 or whatever of them, it is still C(3, n) possibilities... am I totally missing something?

Thank you for any answer!


texts: Can I run a Report Server and a Mirror server from a working Log Shipping secondary instance?

texts:
Our setup:

City A - Primary server for logshipping
City B - Secondary server for logshipping

Now there is a lot of data being updated from A - B.

And the server in City B is underutilized, and it is a very powerful machine.

Can I install a Reporting server and a mirroring server on the server which hosts the secondary logshipping server, and run the Reporting and mirroring server to get the data from the sSecondary logshipping server?

We cannot have the data come across 3 times (one for Logs, Report, Mirror) - and the secondary server is updated regularly enough to allow for our requirements.

But the secondary logshipping server is obviously always in Standby/Readonly mode - is this all possible?


texts: Is SQL Server Certificate using asymmetric encryption internally?

texts:
In Books Online I've read that both asymmetric key and certificates use the idea of asymmetric encryption. For the asymmetric key it's clear, but do the certificates created in SQL Server or let's say via Always Encrypted, use the idea of asymmetric encryption? So internally they have public and private keys stored in the same place? Does it mean that when we create a symmetric key and encrypt it by a certificate it actually means encrypting a key with a asymmetric encryption?

Thanks


texts: Log shipped database server user SID is created automatically

texts:
We have a production server "prod"  which has a user say 'test' that lets the users connect to the application and  a logon trigger which stops them from connecting to the server through SSMS.

I  logship 'prod' to the 'rep' database and the user 'test' is obviously created in the logshipped database 'rep' during the logshipping. Now the login 'test' does not have any login from server 'rep', But people can still login to the 'rep' server and query the 'rep' db .

I checked with SUSER_SNAME and found the SID of the user in rep server which I never created and which is not even present in the login names.

Even If I create a new username in the 'prod' db , after logshipping the new user is replicated in the 'rep' server. This is the weirdest thing I have ever seen.

Can some one help please ?


texts: Unable to start Oracle Listener - "The user name or password is incorrect"

texts:
I am trying to install Oracle Database 12c on a Windows Server 2012 machine. The installation goes well for the most part, except that I get the error "Oracle Net Services configuration failed" (that may be actually the root cause of the problem, which I'm also looking at, but I'd like to understand the error that I get after that).

After installation, I navigate to <dbhome>\BIN\ and I execute lsnrctl start. I get the following prompt:

LSNRCTL for 64-bit Windows: Version 12.1.0.2.0 - Production on 22-MAY-2018 18:13:21

Copyright (c) 1991, 2014, Oracle.  All rights reserved.

Starting tnslsnr: please wait...

Enter oracleadmin's password :

And then I input the password for the user oracleadmin (which has been automatically created during installation). The result is:

Invalid password: WSE-000018: Credential verification failed.

O/S-Error: (OS 1326) The user name or password is incorrect.

TNS-12560: TNS:protocol adapter error
 TNS-00530: Protocol adapter error

I am confident that I am entering the correct password. I have faced the same issue twice, installing with different users.

How can I find where the problem is?

In case it's relevant, Oracle 11g is installed in the same machine too. I didn't face this issue when installing 11g.


texts:
MYSQL version 5.7.31
How do I make it show the distinct rows of the value/column mainthreadid with the highest value of date?
Note that the data is filtered using
WHERE approved = 1 AND section != 150

A fiddle is available here.
Fiddle DDL and DML
Table
CREATE TABLE forum (
    id int,
    mainthreadid int,
    section int,
    approved int,
    date int(20),
    title varchar(255),
    text varchar(255)

);

Sample Data
INSERT INTO forum (id, mainthreadid, section, approved, date, title, text)
VALUES (1, 1, 5, 1, 1000, "title1", "text1");
INSERT INTO forum (id, mainthreadid, section, approved, date, title, text)
VALUES (2, 2, 5, 1, 1000, "title2", "text2");
INSERT INTO forum (id, mainthreadid, section, approved, date, title, text)
VALUES (3, 2, 5, 1, 1001, "title3", "text3");
INSERT INTO forum (id, mainthreadid, section, approved, date, title, text)
VALUES (4, 2, 5, 1, 1002, "title4", "text4");
INSERT INTO forum (id, mainthreadid, section, approved, date, title, text)
VALUES (5, 2, 5, 1, 1003, "title5", "text5");
INSERT INTO forum (id, mainthreadid, section, approved, date, title, text)
VALUES (6, 2, 5, 1, 1004, "title6", "text6");
INSERT INTO forum (id, mainthreadid, section, approved, date, title, text)
VALUES (7, 2, 5, 0, 1005, "title7", "text7");
INSERT INTO forum (id, mainthreadid, section, approved, date, title, text)
VALUES (8, 8, 150, 1, 1004, "title8", "text8");
INSERT INTO forum (id, mainthreadid, section, approved, date, title, text)
VALUES (9, 1, 5, 1, 1006, "title9", "text9");
INSERT INTO forum (id, mainthreadid, section, approved, date, title, text)
VALUES (10, 1, 5, 1, 1005, "title10", "text10");

SQL Statement
SELECT date, id, mainthreadid
FROM `forum`
WHERE approved = 1 AND section != 150 ORDER BY date DESC

Resultset
My desired result set would be:
date    id  mainthreadid
1006    9   1
1004    6   2


texts: SQL Server in memory temp table disk usage

texts:
I've activated in-memory tables on my databases and use it only for table variables. However I notice that the disk space used by the in memory folder keeps growing and growing. I was wondering if there was a way to delete those files as there should be no persisted data in them.

Thanks in advance.


texts: MongoDB service not working after upgrade to Ubuntu 15.04

texts:
I have recently upgraded my Ubuntu from 14.10 to 15.04. All features seems to be working fine except MongoDB. It is not controllable from service as we do normally in Ubuntu.

sudo service mongodb start

or

sudo service mongod start

I get this error message

  Failed to restart mongod.service: Unit mongod.service failed to load:
  No such file or directory.

But I have to start it manually each time I have to connect my application to MongoDB server using

sudo mongod -f /etc/mongod.conf

So MongoDB is connected as long as that terminal is opened. I am using MongoDB in Ruby on Rails application using Mongoid adapater/ORM. Any help in this regard will be appreciated.


texts: SSPI handshake failed with error code 0x8009030c - only stops when SSMS is closed

texts:
I was getting the following error every 4 minutes on the same sql server:

  SSPI handshake failed with error code 0x8009030c, state 14 while establishing a connection with integrated security; the connection has been closed. Reason: AcceptSecurityContext failed. The Windows error code indicates the cause of failure. The logon attempt failed   [CLIENT: ipAddress here]

All the googling I do points towards being unable to connect when running something, but that's not quite my problem...

The errors were coming from the same ip address every time, which I tracked down to one of our developers. They have permission to log onto that server, and had been successfully running queries on it earlier, through sql server management studio.

We looked at what they had running, and decided to shut down one application at a time until the errors stopped. We did Visual Studio first, and got another error soon after. Then we closed all instances of sql server management studio, and the errors stopped.

Why was SSMS repeatedly (every 3-4 minutes) trying to connect to this server, and why would it have been failing, when all queries were run with a valid login? Is there a way to prevent this happening in future?


texts: Can SSRS XML data sources contain spatial data?

texts:
I've been given the task of producing a report which will extract data from a web service.

The web service provides data from a list of sites including aggregates. Lets say total sales by month.

Id like to plot these values on a map and locate them using geometry points.  The developer is investigating how he can include the spatial reference in the xml reply.

But before i get him change the Web service is there any limitation or restriction in ssrs that would stop this working?

If so, what are my alternatives? If i can retrieve the easting and northing in the xml can i convert to a spatial datatype with an expression on the fly?


texts:
I came across this description of this feature, AuditTrail Tables: https://docs.oracle.com/cd/A60725_05/html/comnls/us/fnd/auddat02.htm

Our project needs to track changes on a per table basis, so this feature seems perfect.
We use the 12c version of the database.

Is audit trail tables still the way to go or is this built into unified audit?
Can we enable it through some sql
statements, and if so, is there a guide for that? Or do we need to
enable it through a tool?


texts:
Basically I need an scalable efficient way to select all scheduled items so it made sense that the main table is the schedule. The problem is that all the operation tables have different properties

I created a diagram which is obviously wrong but demonstrates what I am trying to achieve.

I was thinking about having the join on the other side so that all three operations pointed to the schedule, but if I do that then to get a complete schedule I have to aggregate all the operation tables. What if I have loads more in the future?

Another option was to have the delivery, maintenance, loading columns in the schedule table but again there could be loads more operations. I'm just wondering of I am missing something or is this the best option?


texts:
EDIT
It seems that the problem is with my SSH connection, so I created a different question on serverfault. From what I understand so far, it that this problem is caused from the fact that the port forwarding is not established properly.

Original question
I am trying to create a master-slave sync between two Mysql instances. After a lot of work, I at least have two running servers (since I had so many problems that most of the times I was breaking something).
Now, on my slave my.cnf file I have

  server-id               = 155
  log_bin                 =
  /var/log/mysql/mysql-bin.log
  master-host = 127.0.0.1
  master-user =
  masterUser
  master-password = masterpassword
  master-port = 3336
  (I am using port forwarding, so that why I use 127.0.0.1 as host)

The problem is that when I ask for the slave's id I get 0

mysqld --help --verbose |grep server-id

  replicate-same-server-id                          FALSE
  server-id                                         0

and

mysql> SHOW VARIABLES LIKE 'server_id';

  +---------------+-------+
  | Variable_name | Value |
  +---------------+-------+
  | server_id     | 0     |
  +---------------+-------+

The file I am using is the proper file, since when I added this

  [mysqld]
  collation-server = utf8_unicode_ci
  init-connect='SET NAMES utf8'
  character-set-server = utf8

the result of  mysqld --help --verbose |grep character-set-server
changed from

  character-set-server                              latin1

to

  character-set-server                              utf8

Thanks in advance


texts: Cosmos DB ODBC driver installation problem

texts:
We are trying to install Cosmos DB ODBC Driver following the instructions provided in this Microsoft Azure resource and are running into the following error:

  The setup routines for Microsoft DocumentDB ODBC Driver ODBC driver could not be loaded due to a system error code 126: The specified module could not be found. (C:\Program Files\Microsoft DocumentDB ODBC Driver\lib\DocumentDBODBC_sb64.dll).

We've tried unsuccessfully on Windows Server 2008R2 and Windows Server 2012R2.  We have it working on several Windows 10 desktops, just not server OS's unfortunately.  We tried several things like installing using run as administrator, from command line, with both 32 and 64 bit installed, and then one or the other.  We opened the location of the DLL to 'Everyone/full control' We checked that the Visual C++ redistributables are installed although not sure which specific ones are needed.

Any advice would be greatly appreciated.


texts: How to get max value of a column from one table with its matching record in second table in PostgreSQL?

texts:
I have two tables as follows:

create table two(grade_id int, edu varchar(20),sortby int);
alter table two add constraint pk_one primary key(grade_id);
create table one(id int, name varchar(20),grade int);
alter table one add constraint fk_two_one foreign key(grade) references two(grade_id);

Now I insert some records in both tables:

insert into two values(1,'High School',1);
insert into two values(2,'Bachelor',3);
insert into two values(3,'College',2);
insert into two values(4,'Masters',4);
insert into two values(10,'PHD',5);

insert into one values (1,'Ahmad',10);
insert into one values (1,'Ahmad',3);
insert into one values (1,'Ahmad',4);
insert into one values (2,'Ghani',1);
insert into one values (2,'Ghani',3);
insert into one values (2,'Ghani',2);
insert into one values (3,'Fahim',4);
insert into one values (3,'Fahim',1);
insert into one values (3,'Fahim',3);
insert into one values (3,'Fahim',2);

Now I want to select id,name from table one with its edu and sortby columns from table two that has max value in sortby column.
So my desired output would be:

id   +    name   +   education  +   sortby
1    |    Ahmad  |      PHD     |     5
2    |    Ghani  |      Bachelor|     3
3    |    Fahim  |      Masters |     4

Anybody please help.


texts: SSIS OData Package Fails on SQL Server 2017

texts:
Awhile back, I created several SSIS packages, in SSDT 2015, that were deployed to a SQL Server 2016 SSIS instance. They were scheduled with agent jobs and run using basic auth.

Currently, in both SSDT directly and in the SQL 2016 instance, everything is working as expected. When I deployed them to a new SQL Server 2017 server, after changing the destination data sources to the new server, I get the following:

I have tried:

Running they package using windows auth using a proxy account
Running in 32 bit
Installing the Client Components SDK (not installed in previously working server)
Modified the IE Proxy settings to ensure access (connecting to INTERNAL SharePoint)
Ensured target version was set to SQL Server 2017

I have tried pretty much everything that the first few pages of Google has to offer under several different searches. I am truly at a loss for what to try next. Please let me know if you have any suggestions of where to look or what to try.

Thank you.


texts: Empty Strings: Why or when is '' equal to ' '?

texts:
Who can explain why

select case when '' = ' ' then 1 else 0 end, LEN(''), LEN(' '), DATALENGTH(''), DATALENGTH(' ');

yields

----------- ----------- ----------- ----------- -----------
1           0           0           0           1

The funny consequence of this is that in

create table test ( val varchar(10) );
insert into test values( '' );
update test set val = ' ' where val = '';

the update will indeed replace the empty string by a blank, but the where clause keeps to be true and repeated executions of the update statement tell

(1 row(s) affected)


texts: Best practice for when the ideal number of columns is unknown

texts:
I'm currently making a MySQL database to list products for an e-commerce site. Each product will have several sizes available which will each have their own dimensions and measurements (e.g. length, width, thickness) and pricing. However, different products, even within the same category, have different numbers of dimensions (some will have 2 widths at different sections, etc.) and there isn't necessarily a maximum number of measurements required to uniquely ID a product size. The options I can see are:

Create a table every time a product is created to account for how many size measurements it needs. Obviously a poor way to handle this.
Create a table with an arbitrarily large number of "measurement" columns. Leaves a lot of nulls if it's too large, and requires that I manually edit the table structure later if it's too small.
Create a table with two CSV varchars: one to identify pseudo-column names, the other for values. Sorting and searching by any value but the first would be largely impossible to my knowledge, but the information is all there.
Create two tables: one for the individual product size code (linking to the product of course), and another with some kind of "key-value" setup: one column for the product code, another for the name of the measurement (e.g. length), and a third for the value of that measurement. Necessitates more joins to get the same information as the other solutions, plus an extra column for an "index" if I want to keep sizing attributes in a particular order (or the size key could be an enum, I suppose).

The last solution seems most reasonable, but is there a better way to go about this?


texts:
I have a Drupal site with a fair amount of content, about 12G in size, with 500+ distinct fields making for almost 1400 tables. We're migrating to a new server environment so I need to be able to export the DB and then import it on the new servers. I've successfully used mysqldump on this system in the past, but the last couple days each export is incomplete, with the data for some tables (in particular, the critical node and users tables) not getting dumped.

The process I follow is to put the site into maintenance mode and then halt any external process that hits the database. I then export the structure first

$ mysqldump -u XXXXXX -p DBNAME --no-data | gzip > structure.sql.gz

and then the table data

$ mysqldump -u XXXXXX -p DBNAME --no-create-info | gzip > data.sql.gz

(The second query is a little more complicated as I tell mysqldump to skip the cache files, the search index etc. but you get the point.)

Both times mysqldump runs without complaint, and the resulting SQL files load without error messages, but looking through the DB afterwards it's clear the data dump process got partway through the list of tables and then stopped after completing a table. Note that it does not stop at the same place each time.

Has anyone encountered this problem? Are there any tricks to get mysqldump to better handle large DBs?


texts: Postgresql index size: penalty for composite type?

texts:
My question refers to Postgresql 12.2.
I have two almost identical tables, structure- and contentwise, the main difference being that the primary key of the second table is a composite type, while the first table includes the components of the type directly.
CREATE TABLE first (
   a INTEGER,
   b SMALLINT,
   c SMALLINT,
   PRIMARY KEY (a,b,c),
   -- further fields: 3 SMALLINT, 6 INTEGER, 2 NUMERIC
);
CREATE TYPE abc_type AS (a INTEGER, b SMALLINT, c SMALLINT);
CREATE TABLE second (
   abc abc_type PRIMARY KEY,
   -- same further fields as above,
   extra BYTEA -- only 4 bytes long
);

Both tables contain the same number of records, roughly 2 billion.
The size of table second is 1.25 times the size of table first, which may be partially attributed to the extra field.
But what's really surprising (to me) is the 2.5 times increase of index second_pkey compared to index first_pkey.
SELECT
  t.tablename,
  indexname,
  c.reltuples AS num_rows,
  pg_size_pretty(pg_relation_size(quote_ident(t.tablename)::text)) AS table_size,
  pg_size_pretty(pg_relation_size(quote_ident(indexrelname)::text)) AS index_size
FROM pg_tables t
  LEFT OUTER JOIN pg_class c ON t.tablename=c.relname
  LEFT OUTER JOIN
    ( SELECT c.relname AS ctablename, ipg.relname AS indexname, indexrelname FROM pg_index x
      JOIN pg_class c ON c.oid = x.indrelid
      JOIN pg_class ipg ON ipg.oid = x.indexrelid
      JOIN pg_stat_all_indexes psai ON x.indexreliddd = psai.indexrelid )
    AS foo
  ON t.tablename = foo.ctablename
WHERE indexname in ('first_pkey','second_pkey')
ORDER BY 1,2;

 tablename |  indexname  |   num_rows    | table_size | index_size
-----------+-------------+---------------+------------+------------
 first     | first_pkey  | 1.9596214e+09 | 165 GB     | 62 GB
 second    | second_pkey | 1.9596214e+09 | 205 GB     | 160 GB

Is this difference in size a penalty for the composite type? Can it be avoided?
The size might make a difference, not just regarding storage: 62 GB still fit into memory, while 160 GB is definitely beyond in my case.


texts: Fast copy/rename database, not mysqldump

texts:
I would like to keep a "default" setup for a database on a testing server, which can be used to "restore" a "dirty" testing database back to the default state.

mysqldump is very fast, done in a few seconds. But restoring from the dump takes 5-10 minutes. If I could drop the test database, copy the "default" over to the test database directly somehow, perhaps by copying the database's system files, that would be awesome.


texts: Auditing - sp_send_dbmail from server-level triggers failed

texts:
I have a server-level trigger to send a mail when a database is created. I have a  few users in the db_creator server role. The trigger fails for these users because they don't have permissions to execute sp_send_dbmail. Is there a better option here than adding the logins to the DatabaseMailUserRole in MSDB or adding "WITH EXECUTE AS 'sa'"  (or similar with a lower-privileged account) to the trigger?

CREATE TRIGGER [ddl_trg_Create_Database]
ON ALL SERVER
FOR CREATE_DATABASE
AS
declare @results varchar(max)
declare @subjectText varchar(max)
declare @databaseName VARCHAR(255)
SET @subjectText = 'New database ceated - ' + @@SERVERNAME + ' by ' + SUSER_SNAME()
SET @results =
  (SELECT EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','nvarchar(max)'))
SET @databaseName = (SELECT EVENTDATA().value('(/EVENT_INSTANCE/DatabaseName)[1]', 'VARCHAR(255)'))

EXEC msdb.dbo.sp_send_dbmail
 @recipients = 'DBA_GROUP@SomeCompanyName.com',
 @body = @results,
 @subject = @subjectText,
 @exclude_query_output = 1
GO

ENABLE TRIGGER [ddl_trg_Create_Database] ON ALL SERVER
GO


texts: Looking to consolidate several different older SQL serves into one AG with an ent lic when Sql Server 2016 is released. Plan is to have a primary, a sync passive secondary, and an async third instance used for reporting. That third instance I want to configure using a Std lic w/CALs to save on costs. Any issue with mixing Ent and Std in the same Availability group?


texts: Mongodb ubuntu - config server and shard together

texts:
I have the following services on a single shard server:

sudo mongod --dbpath /data/db/ --port 31001
sudo mongod --configsvr --dbpath /data/configdb/ --port 21001

The same server is using for shard and config.
for now I am testing the environment so I am running these rows manually.
I want to run it as a service. I did not event tried it, adding dbpath twice to the config file will not do the work, can you suggest please a config in order to make these lines run as a service?
thanks


texts:
Is it possible to encrypt 2008 R2 backups like TDE for instance?
What I've read is encrypting the whole database and that way the backups will be encrypted. But can you have TDE encrypted backups alone?


texts:
I created a sql database and filled it with some good data. Now I'm trying to move all the stored procedure over to another database instance.
I exported the entire source database, including beloved stored procedures to a .sql file.
Now I'm looking for a way to read that data into the other computer's sql server installation.

So far, I've tried looking through the Visual Studio UI for hints, and using sqlcmd with an input file. After using sqlcmd with the .sql file, some rows were affected, but they didn't show up in the server explorer.


texts: Single query for searching for records without knowing what the search column is for

texts:
I have a single line search where i should be able to search for address or city or zip or state. I should be able to show data of what ever that matches for the typed in word. Suppose user had typed in new york then I have to write an sql query  which should give  the output like

  SearchResult new York(coming from city)

  123 main street New York (coming from address)

  .

  . .

If i type in 58986

  SearchResult

  123 main street houston 58986  (coming from address data)

  58986  (coming from zip column data)

  .

  .

I have tried writing like this

select sd_name,zip from mls_data where sd_name like '%new%' or zip like 'new%'

but its not working

table mls_data

mls   address                   city            zip
1   56 Main Street, New York    New York        123456
2   10178 Berlin, Germany       Berlin          566996
3   5th Street, USA             Washington      878788
4   3rd Street, Washington      Washington      589345

input parameter for query

washington

result

mls     alisasName
4   3rd Street, Washington
4   Washington
3   Washington


texts: can't install citext extension: could not access file "$libdir/citext": No such file or directory

texts:
I'm trying to get citext available as a type in a specific database. The Postgres database server is 9.4. I see it on the server in the pg_catalog's pg_available_extensions view.

From the psql console I try to install it and get this error:

create extension citext;

ERROR:  could not access file "$libdir/citext": No such file or directory

Is there a configuration problem? I'm not sure why it would not have the $libdir variable populated.


texts: How do you approach refactoring an ugly procedure/query?

texts:
I've inherited a database which contains several procedures which are 1000-1500 lines long, with complex nested sub-selects going up to 7 or 8 levels deep in places. I desperately need to refactor them for my own sanity, but how can I begin to do this with any level of confidence that they still work the same?

I would write unit tests if this were .Net - do you recommend a similar approach?


texts:
At the present we have to go to each database and manually generate database diagrams, so I want a way to automate this.

Question: Is it possible to generate DB diagram for all SQL databases opened in SSMS ?


texts: Replication topology advice - central subscriber

texts:
I'm seeking guidance and advice about how to set up replication in MSSQL to deal with the following criteria.

We have a central server hosted at a data center - this is where our web app is located.

We have various clients who have VPN connections (restricted) to our central server, so our web app can access their databases which our clients manage.

We are looking at providing a Documents database which will hold various small files and pictures - e.g. word docs, excel files, pdfs etc.

I want to design the system in such a way, that if they so wish, they can set up replication for this documents database so a 'local' copy is available in our data center, which means we don't have to go down the VPN to get the file.

I've read various articles such as these :

http://www.sqlrepl.com/sql-server/central-subscriber-model-explained/

https://www.0xsql.com/2011/05/31/transactional-replication-and-wan-links/

Which seem to cover most of what I need. However, from time to time, our VPN connections die and the publishing SQL servers will be unavailable - is this handled by the replication agents at all - i.e. by retrying a little later - or would the replication just error?

Also, can the subscriber be configured to PULL from multiple sources say every hour or two - I don't need the central server to be in sync instantly as its effectively just a cache for our web app.


texts: Modify slow query log rotation on Amazon RDS instance

texts:
Our Amazon RDS instance rotates slow logs every hour, and keeps only the last 20 of them.

This is not optimal for monitoring as it prevents us from seeing any slow query that might have been running more than 24 hours ago.

Is there a way to have the slow query log rotate once a day - instead of once an hour - or any other way to keep the slow logs for a longer time?

Edit: Database is MySQL 5.6.


texts: Get the id of multiple inserted/updated rows

texts:
I insert my _as_ci accent sensitive, case-insensitive rows. Tail is unique, id is auto_increment.

INSERT INTO tails (tail) VALUES ('Hello'), ('cafe'), ('Café') ON DUPLICATE KEY UPDATE tail = VALUES(tail);

I could try to get the ids out like this:

SELECT id, tail FROM tails WHERE tail IN ('Hello', 'cafe', 'Café')

But I've had experience with it matching rows in MySQL but I can't do the exact matching process in PHP. If only PHP could collation compare.

Is there any way to get MySQL to also return the original string from the IN so I can do an exact comparison in PHP or is there a better method I'm missing?

This code is run extremely often, so I can't afford to run each insert individually.