texts: How to perform JOIN and only use single record for each instance on 2nd table

texts:
I am trying to perform a join from table 1.  In table 2 there could be multiple matches on the join, but I only want to use each match once and only once.  How do I do this?  Here is my data set:

Products

Product_Plan_ID      Product_Plan_Creation_Date
===============      =========================
123-3241             1/2/2018
123-3242             1/15/2018
123-3242             1/17/2018
123-3243             1/18/2018
123-3244             2/2/2018
123-3245             2/5/2018

AND...

Product_Delivery

Product_Plan_ID      Product_Plan_Delivery_Date
===============      =====================
123-3242             1/20/2018
123-3242             1/22/2018

Here is code to generate tables and data set...

CREATE TABLE Products
(
Product_Plan_ID varchar(25),
Product_Plan_Creation_Date date
)

create table Product_Delivery
(
Product_Plan_ID varchar(25),
Product_Plan_Delivery_Date date
)

INSERT INTO Products
select '123-3241', '1/2/2018'
union all
select '123-3242', '1/15/2018'
union all
select '123-3242', '1/17/2018'
union all
select '123-3243', '1/18/2018'
union all
select '123-3244', '2/2/2018'
union all
select '123-3245', '2/5/2018'

INSERT INTO Product_Delivery
SELECT '123-3242', '1/20/2018'
UNION ALL
SELECT '123-3242', '1/22/2018'

In my query, I want to join the Products table to Product_Delivery and for each row in Products, I want to join it to Product_Delivery matching on the Product_Plan_ID and where the Product_Plan_Delivery_Date is within 30 days of the Product_Plan_Creation_Date.  The kicker is, if I find a match in Product_Delivery, I only want that match to be used a single time.  The way I have it now, I'm ending up with duplicates on Product_Plan_Delivery_Date 1/20/2018 below.  Here's my query so far:

select Products.Product_Plan_ID, Products.Product_Plan_Creation_Date,
t2.Product_Plan_Delivery_Date from Products
CROSS APPLY (
    SELECT TOP 1 t.Product_Plan_ID, t.Product_Plan_Delivery_Date
    FROM Product_Delivery t
    where t.Product_Plan_Delivery_Date between
    Products.Product_Plan_Creation_Date and dateadd(dd, 30, Products.Product_Plan_Creation_Date)
and Products.Product_Plan_ID = t.Product_Plan_ID
) t2

The problem with the above is I end up with a duplicate match for Product_Plan_ID 123-3242 on Product_Plan_Delivery_Date, 1/20/2018.  How would I write this so I have Product_Plan_ID 123-3242 match first on 1/20/2018, then 2nd on 1/22/2018?

My desired result set would be this:

Product_Plan_ID    Product_Plan_Creation_Date    Product_Plan_Delivery_Date
123-3242           1/15/2018                     1/20/2018
123-3242           1/17/2018                     1/22/2018

However...I'm getting this:

Product_Plan_ID    Product_Plan_Creation_Date    Product_Plan_Delivery_Date
123-3242           1/15/2018                     1/20/2018
123-3242           1/17/2018                     1/20/2018

Thanks in advance.


texts: How can I merge data from an Excel file into an access database with multiple tables?

texts:
I have an access database with many internal tables.
Each table is a category of electronic components (resistor, capacitor etc)
In all tables, there is a manufacturer part number and a currently empty field for our internal part number.

I have an excel file containing the internal part numbers and the matching manufacturers part numbers.

How can I fill in the internal part number field for all tables from my excel file, matching on the manufacturer part number?

I'm sure this is simple, but I don't use access much, and I don't speak "accesseze".


texts: Delete more than 50K records in SQL table

texts:
We have 500k records in a SQL table. Below is table structure
UserID varchar
FirstName varchar
LastName varchar
Address varchar

We need to delete 50K records in this table. We tried below approach
Approach 1: This has 50K DELETE statements which takes around 40 minutes
DELETE FROM [dbo].[Table] WHERE UserID = 'tom345'
DELETE FROM [dbo].[Table] WHERE UserID = 'john45'

and so on until 50K DELETE statements
Approach 2: This contains all 50K user id in IN statement but it takes more that 40 minutes
DELETE FROM [dbo].[Table] WHERE UserID IN ( 'tom345', 'john45',  .... )

Is there any other approach which takes less time comparatively?


texts:
I have a MySQL table named tasks that contains four columns:

id (Integer)
name (Varchar)
task_start (DateTime)
task_finish (DateTime)

How can I write an SQL statement that returns tasks that are running at a specific point in time (e.g. task that are running right now)?


texts:
I have a server machine and four client machine for database connectivity.

I installed sqlexpress 2008 in server and one machine and all remaining systems, they have connected the database through the EMS (SQL Manager 2008 for SQL Server ).

The issue is that when I connect the database from that m/c which has sqlexpress 2008 then it gives "Timeout Expired"

But all remaining systems connect to server easily.

Why it gives an error for that particular system.


texts: Can you use dynamic sql in a select statement?

texts:
I have the following code:

select
    [schema].[name]     [schema],
    [table].[name]      [table],
    [column].[name]     [column],
    [type].[name]       [type],
    [column].max_length [maxAllowedLength],
    exec('select max(len(' + [column].[name] + ')) from ' + [table].[name]) maxDataLength
from sys.schemas [schema]
    join sys.tables [table] on [schema].schema_id = [table].schema_id
        join sys.columns [column] on [table].object_id = [column].object_id
            join sys.types [type] on type.system_type_id = [column].system_type_id
where 1=1
and   [schema].[name] = 'dbo'
and   [table].[name]  = 'TableToAnalyze'
order by [schema].[name], [table].[name]

But it doesn't work.  Can I do what I'm trying to do?  I'd like to also add other information, like minimum length and whether there are any non-numbers, non-dates, dates, etc. to it as well, but that all depends on the basic framework.

In case it's not clear, I want one row per column in the table 'TableToAnalyze', and each row should have the schema, table, column, type, maximum allowable size, and (the part that's giving me trouble is) the maximum actual data length, all for each column in the table I'm analyzing.

It was recommended I use a cross apply, so I tried

select
    [schema].[name]     [schema],
    [table].[name]      [table],
    [column].[name]     [column],
    [type].[name]       [type],
    [column].max_length [maxAllowedLength],
    maxDataLength
from sys.schemas [schema]
    join sys.tables [table] on [schema].schema_id = [table].schema_id
        join sys.columns [column] on [table].object_id = [column].object_id
            join sys.types [type] on type.system_type_id = [column].system_type_id
            cross apply (exec('select max(len(' + [column] + ')) from ' + [table].[name])) maxDataLength
where 1=1
and   [schema].[name] = 'dbo'
and   [table].[name]  = 'TableToAnalyze'
order by [schema].[name], [table].[name]

But that doesn't work either.


texts: Should a SQL Server DBA know Windows Server and vice versa? Are these roles soon to become synonymous. Or at least more encapsulated/?

texts:
I have Jr. experience with SQL Server and a desire to dive deep into Windows System Administration.

Are these fields too separate to combine skill sets? Would I be marketable as a Windows Admin who knows SQL Server, or vice versa, a SQL Server admin who knows Windows Server?


texts: Will improper bin-log purging leads to deletion of mysql database

texts:
We had  master to master and master to multiple slave environment in one of our client location. In one of the slave Db host we had disk space issue with bin-logs. At first i deleted the bin-log files manually while the mysql server is running using rm -f binlog.000015, binlog.000016.......binlog.000026. But later i came to know this procedure is wrong by issuing the commmand :

mysql> SHOW BINARY LOGS;
+---------------+-----------+
| Log_name      | File_size |
+---------------+-----------+
| 0             |           |
| 0             |           |
| 0             |           |
| binlog.000027 |    724935 |
| binlog.000028 |    733481 |
+---------------+-----------+

What ever the bin logs deleted using rm -rf was not getting cleared in slave. Later i issued the command : PURGE BINARY LOGS TO 'mysql-bin.000026'; and could see the bin logs got cleared properly.

mysql> SHOW BINARY LOGS;
+---------------+-----------+
| Log_name      | File_size |
+---------------+-----------+
| binlog.000027 |    724935 |
| binlog.000028 |    733481 |
+---------------+-----------+

But this didnt affected the slave replication and replication is fine. But after some time i could noticed that missing of mysql database. What could be root cause. Whether mysql dropped due to mis handling of the bin-logs.

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| client_prod        |
+--------------------+
2 rows in set (0.01 sec)

====

As the other slave is fine.

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| client_prod        |
+--------------------+
3 rows in set (0.00 sec)

We do not see anything indicative of the folder being removed or the database dropped in logs or history. What would be the mystery behind. How could be recover apart from taking dump and restoring, can we take dump of only mysql and restore it in this slave. If doing so will it leads to any replication problem.


texts: Connecting to SQL Server using cached credentials

texts:
While migrating from SQL authentication (user-password) to integrated security I ran into an unexpected combination of requirements.

SQL clients are services running under a LOCALSYSTEM or NETWORKSERVICE account
SQL clients use integrated security to connect to the local SQL Server
The system is in an AD domain.
Database connections must be successfully established even when network connectivity to the domain controller is down for hours or days at a time.

On the luckier side, the SQL clients and SQL Server (2005 SP4) always run on the same box.

The conclusion of reading, experiments done so far and a support call to MS seems to have been that I cannot meet all these requirements at the same time, regardless of whether Kerberos or NTLM is used.

On the other hand I see some references to cached credentials that are giving me hopes that it might be possible to somehow connect to the database using cached credentials, under some conditions, following a previous successful domain login (service startup, or previously established connection).

Is anyone successfully using cached domain credentials to connect to a local SQL Server from a service running under a domain account, during prolonged DC outages?  How is that configured?  What are the security implications?
Are the four requirements above really incompatible?


texts: How to increase the performance of this 3 minute reporting query

texts:
We have a table MyTable with about 10.000.000 records which contains a record of all transactions processed in our application. This table has the user Owner who did the transaction, the date ActionDate on when it took place, the status Status and the order type OrderType of what type of transaction has been performed.

To generate a report where for every user the amount of transactions it did for every order type is displayed we have the following query as part of a stored procedure:

SELECT mt.Owner AS User,

(SELECT COUNT(ID)
FROM `MyTable` mt
WHERE DATEDIFF(day, ActionDate, GETDATE()) = 0
AND mt.Owner = ca.Owner
AND mt.OrderType = 1
AND mt.Status <> 3
AND mt.Status <> 2  ) AS FIRST,

(SELECT COUNT(*)
FROM MyTable mt
WHERE DATEDIFF(day, ActionDate, GETDATE()) = 0
AND mt.Owner = mt.Owner
AND mt.OrderType = 2
AND mt.Status <> 3
AND mt.Status <> 2 ) AS SECOND,

(... Do this for six order types where mt.OrderType and the AS name changes ...)

(SELECT COUNT(*)
FROM MyTable mt
WHERE DATEDIFF(day, ActionDate, GETDATE()) = 0
AND mt.Owner =  mt.Owner
AND mt.Status<> 3
AND mt.Status<> 2 )
AS TOTAL

FROM MyTable mt
WHERE mt.Owner IS NOT NULL
GROUP BY mt.Owner

Some relevant information on indexes:

There are three indexes on MyTable. One on ActionDate, Status, OrderType, one on Id, and one on Owner.

These are created as follows:

CREATE NONCLUSTERED INDEX AnExampleOfAnIndex ON dbo.MyTable (ActionDate ASC,
                                                             Status ASC, OrderType ASC)
WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF,
      IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON,
      ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 90)
ON [PRIMARY]

The query takes three minutes. Does someone see how it can be improved? Thanks.


texts: mysql: select join table with latest record for both table

texts:
This question has been asked at here as well.

i had 2 table, tableA and tableB looking like this:

tableA:

tableB:

i manage to get the latest record by auto increment id by using the following sql for tableA and tableB:

SELECT *
FROM tableA
WHERE id IN (SELECT MAX(id) AS id
             FROM tableA
             GROUP BY social_num)

SELECT *
FROM tableB
WHERE id IN (SELECT MAX(id) AS id
             FROM tableB
             GROUP BY social_num)

how to join them together, so that, i get the latest record on tableA and latest record on tableB link by social_num?
i tried following sql, it work:

SELECT *
FROM tableA
LEFT JOIN tableB ON tableA.social_num = tableB.social_num
WHERE tableA.id IN (SELECT MAX(id) AS id
             FROM tableA
             GROUP BY social_num)
AND
tableB.id IN (SELECT MAX(id) AS id
             FROM tableB
             GROUP BY social_num)

but how do i show all of other user from tableA togather as well, like Morris Q

DDL Information

CREATE TABLE `tableA` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `fullname` varchar(255) DEFAULT NULL,
  `social_num` varchar(255) DEFAULT NULL,
  `email` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8;

/*Data for the table `tableA` */

insert  into `tableA`(`id`,`fullname`,`social_num`,`email`) values (1,'David J','1155','davidj@gmail.com'),(2,'Brian H','2244','brianh@gmail.com'),(3,'Hawkins M','6677','hawkins@gmail.com'),(4,'Marry K','7122','marry@gmail.com'),(5,'Utah O','9123','utah@gmail.com'),(6,'James L','1266','james@gmail.com'),(7,'David J','1155','david@hotmail.com'),(8,'Marry K','7122','marry@gmail.com'),(9,'Johnson E','1180','johnson@gmail.com'),(10,'Hawkins M','6677','hawkins@yahoo.com'),(11,'Morris Q','1461','morris@gmail.com'),(12,'David J','1155','david@yahoo.com');

CREATE TABLE `tableB` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `fullname` varchar(255) DEFAULT NULL,
  `social_num` varchar(255) DEFAULT NULL,
  `phone_num` varchar(255) DEFAULT NULL,
  `fav_color` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8;

/*Data for the table `tableB` */

insert  into `tableB`(`id`,`fullname`,`social_num`,`phone_num`,`fav_color`) values (1,'Brian H','2244','912-112-1231','blue'),(2,'Johnson E','1180','912-221-5512','red'),(3,'David J','1155','812-231-6125','green'),(4,'Utah O','9123','741-661-3125','red'),(5,'Hawkins M','6677','881-331-6612','blue'),(6,'James L','1266','934-513-5132','yellow'),(7,'Brian H','2244','785-513-9821','green'),(8,'David J','1155','960-231-5151','black'),(9,'Hawkins M','6677','135-661-3516','red');

Any help would be great, im using mysql 5.5


texts:
I monitor MySQL databases by querying various things remotely. I recently came across an issue where by a table had become corrupted but I did not know about it until the daily mysqldump failed. Is there a way I can check for corruption (without running a CHECK TABLE on every table)? I thought I had it figured when I noticed information_schema.tables has a table_comment column which says this when it's corrupt:

TABLE_COMMENT: Table './demo/test' is marked as crashed and should be repaired

Turns out, when I try to search on that column, i.e. WHERE TABLE_COMMENT LIKE '%marked as crashed%', nothing is returned!

I need to be able to query this from a stored procedure, any suggestions?


texts:
I have created a new MySQL cluster by this:
mysqld --initialize-insecure --basedir=/var/lib --datadir=/var/lib/mysql2 --defaults-file=/etc/my2.cnf
chown -R mysql:mysql /var/lib/mysql2
chmod 777 /var/lib/mysql2

my config file looks like this:
datadir=/var/lib/mysql2
socket=/var/lib/mysql2/mysql2.sock

log-error=/var/log/mysqld2.log
pid-file=/var/run/mysqld/mysqld2.pid
port=2222

whenever i run: mysqld --defaults-file=/etc/my2.cnf -u root
It creates the process + socket for a second and then aborted, the log I receive is this:
2021-01-04T15:09:15.069657Z 0 [System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.0.22) starting as process 16704
2021-01-04T15:09:15.091392Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2021-01-04T15:09:15.787218Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
mysqld: Table 'mysql.plugin' doesn't exist
2021-01-04T15:09:15.886765Z 0 [ERROR] [MY-010735] [Server] Could not open the mysql.plugin table. Please perform the MySQL upgrade procedure.
2021-01-04T15:09:16.887822Z 0 [ERROR] [MY-011292] [Server] Plugin mysqlx reported: 'Preparation of I/O interfaces failed, X Protocol won't be accessible'
2021-01-04T15:09:16.887985Z 0 [ERROR] [MY-011300] [Server] Plugin mysqlx reported: 'Setup of bind-address: '*' port: 33060 failed, `bind()` failed with error: Address already in use (98). Do you already have another mysqld server running with Mysqlx ?'
2021-01-04T15:09:16.888098Z 0 [ERROR] [MY-013597] [Server] Plugin mysqlx reported: 'Value '*' set to `Mysqlx_bind_address`, X Plugin can't bind to it. Skipping this value.'
2021-01-04T15:09:16.888217Z 0 [ERROR] [MY-011300] [Server] Plugin mysqlx reported: 'Setup of socket: '/var/run/mysqld/mysqlx.sock' failed, another process with PID 7487 is using UNIX socket file'
2021-01-04T15:09:17.039111Z 0 [Warning] [MY-010015] [Repl] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2021-01-04T15:09:17.225731Z 0 [Warning] [MY-010015] [Repl] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2021-01-04T15:09:17.251232Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed.
2021-01-04T15:09:17.251408Z 0 [System] [MY-013602] [Server] Channel mysql_main configured to support TLS. Encrypted connections are now supported for this channel.
2021-01-04T15:09:17.293753Z 0 [Warning] [MY-010441] [Server] Failed to open optimizer cost constant tables
2021-01-04T15:09:17.294070Z 0 [ERROR] [MY-013129] [Server] A message intended for a client cannot be sent there as no client-session is attached. Therefore, we're sending the information to the error-log instead: MY-001146 - Table 'mysql.component' doesn't exist
2021-01-04T15:09:17.294248Z 0 [Warning] [MY-013129] [Server] A message intended for a client cannot be sent there as no client-session is attached. Therefore, we're sending the information to the error-log instead: MY-003543 - The mysql.component table is missing or has an incorrect definition.
2021-01-04T15:09:17.294774Z 0 [ERROR] [MY-010326] [Server] Fatal error: Can't open and lock privilege tables: Table 'mysql.user' doesn't exist
2021-01-04T15:09:17.294978Z 0 [ERROR] [MY-010952] [Server] The privilege system failed to initialize correctly. For complete instructions on how to upgrade MySQL to a new version please see the 'Upgrading MySQL' section from the MySQL manual.
2021-01-04T15:09:17.295351Z 0 [ERROR] [MY-010119] [Server] Aborting
2021-01-04T15:09:18.851807Z 0 [System] [MY-010910] [Server] /usr/sbin/mysqld: Shutdown complete (mysqld 8.0.22)  MySQL Community Server - GPL.

If i try to start the process with the default conf file (the one created when I installed mysql via yum) the process works and also the socket. but for some reason it gets aborted for my new cluster.


texts: Are there security-issues with a read-only-account on Oracle-DBs?

texts:
I am currently working as IT-maintenance-guy for a classic MVC-system(oracle-db/jboss) at public sector. The newest feature should be the shutdown of my oracle-db-readOnly-Account cause of "security reasons" (<-- Yes, it is not any more specific than that). I tried to some investigation on what that security threats might be, but i ended up finding no argument suiting my case.

My question is:
Can a readOnly-Access to an Oracle-Database cause any serious security issues?
I am looking for arguments that i can toss at the decision-maker that there is no threat coming from me having a readOnly-Account!

Additional information:

I am also inside the house-LAN (behind exterior firewall)
I am a learned SQL-user.
I don't store data on my local machine and i have no intention of selling/leaking any data. Beside the fact that the data is not the slightest bit intresting for any outsider ;)

Thanks in advance for any good ideas/arguments/issues/hints!!


texts: PostgreSQL: How can I identify the connections which is blocking ALTER TABLE?

texts:
I would like to add some fields to a table in all testing and production DBs, all of which are stored in PostgreSQL 9.3 on Amazon RDS.

ALTER TABLE users DROP COLUMN IF EXISTS device_language;
ALTER TABLE users DROP COLUMN IF EXISTS device_name;

ALTER TABLE users ADD COLUMN device_language character varying(3);
ALTER TABLE users ADD COLUMN device_name character varying(3);

This snippet works without problems on my local database and on one of the testing databases, but hangs for a very long period on another DB and never runs the command.

I have stopped Apache (with all possible clients reading and writing from the DB), made the change and started Apache again, but this isn't a very elegant solution.

How can I know which connection is blocking the ALTER TABLE command in a PostgreSQL database?


texts:
I have this long query which works one for processing one sale.
What I'm trying to do is loop it until no more B.boughtPending = 0 and S.soldPending = 0 and then Select all the ones that B.boughtPending > 0 and S.soldPending > 0 and also has to match by B.itemId and S.itemId and both S.aborted=0 and B.aborted=0.

Here is the working query below.

DROP TABLE IF EXISTS sale;

CREATE TEMP TABLE sale (
    buyerId,
    sellerId,
    willBuyAmount
);

INSERT INTO sale (
    buyerId,
    sellerId,
    willBuyAmount
)
SELECT B.id AS buyerId,
       S.id AS sellerId,
       CASE
            WHEN( B.amount - B.bought ) >( S.amount - S.sold ) THEN( 1 + ABS( RANDOM(  ) %( S.amount - S.sold )  )  )
            ELSE( 1 + ABS( RANDOM(  ) %( B.amount - B.bought )  )  )
       END AS willBuyAmount
  FROM Buying AS B,
       Selling AS S
ON B.itemId = S.itemId
AND
B.aborted = 0
AND
S.aborted = 0
AND
B.boughtPending = 0
AND
S.soldPending = 0
AND
B.price >= S.price
AND
S.sold < S.amount
AND
B.bought < B.amount
 LIMIT 1;

UPDATE Buying
   SET boughtPending = boughtPending +(
           SELECT willBuyAmount
             FROM sale
       )
 WHERE id =(
           SELECT buyerId
             FROM sale
       );

UPDATE Selling
   SET soldPending = soldPending +(
           SELECT willBuyAmount
             FROM sale
       )
 WHERE id =(
           SELECT sellerId
             FROM sale
       );

DROP TABLE sale;

Here is my failed attempt. (From research I need a JOIN HERE to fix it?)

SELECT *
FROM
Buying AS B,
Selling AS S
ON B.itemId = S.itemId
AND
B.aborted = 0
AND
S.aborted = 0
AND
b.boughtPending > 0
AND
S.boughtPending > 0
(
DROP TABLE IF EXISTS sale;

CREATE TEMP TABLE sale (
    buyerId,
    sellerId,
    willBuyAmount
);

INSERT INTO sale (
    buyerId,
    sellerId,
    willBuyAmount
)
SELECT B.id AS buyerId,
       S.id AS sellerId,
       CASE
            WHEN( B.amount - B.bought ) >( S.amount - S.sold ) THEN( 1 + ABS( RANDOM(  ) %( S.amount - S.sold )  )  )
            ELSE( 1 + ABS( RANDOM(  ) %( B.amount - B.bought )  )  )
       END AS willBuyAmount
  FROM Buying AS B,
       Selling AS S
ON B.itemId = S.itemId
AND
B.aborted = 0
AND
S.aborted = 0
AND
B.boughtPending = 0
AND
S.soldPending = 0
AND
B.price >= S.price
AND
S.sold < S.amount
AND
B.bought < B.amount
 LIMIT 1;

UPDATE Buying
   SET boughtPending = boughtPending +(
           SELECT willBuyAmount
             FROM sale
       )
 WHERE id =(
           SELECT buyerId
             FROM sale
       );

UPDATE Selling
   SET soldPending = soldPending +(
           SELECT willBuyAmount
             FROM sale
       )
 WHERE id =(
           SELECT sellerId
             FROM sale
       );

DROP TABLE sale;
)


texts: SQL Server Self Signed Cert - no private key?

texts:
I recently turn on SSL encryption using SQL server self-signed cert then I start to ponder more about

1) encryption algorithm, authentication method

It seems that the encryption/hashing algorithm used depends on the SChannel negotiation between the client/server's OS. - fine.

2) key length of the self-signed cert.

I tried query this

select  name, pvt_key_encryption_type_desc from sys.certificates

##MS_SQLResourceSigningCertificate##    NO_PRIVATE_KEY
##MS_SQLReplicationSigningCertificate## NO_PRIVATE_KEY
##MS_SQLAuthenticatorCertificate##  NO_PRIVATE_KEY
##MS_AgentSigningCertificate##  NO_PRIVATE_KEY
##MS_PolicySigningCertificate## NO_PRIVATE_KEY
##MS_SmoExtendedSigningCertificate##    NO_PRIVATE_KEY
##MS_SchemaSigningCertificate990F36EF1B3577FE5687C7465F0A5135DE9E6834## NO_PRIVATE_KEY

q1) can I check if the cert use for SSL encryption is the MS_SQLAuthenticatorCertifcate ?

q2) How could a SSL encryption / CERT work with "NO PRIVATE KEY"  ? In all my understanding, a cert/asymmetric encryption will involved a PUBLIC key (provided in the cert) for encryption, and the owner of the cert will have its PRIVATE key for decryption ?

Why is this not the case here ?


texts: Pivot functionwith multiple conditions (concatenated in one header)

texts:
I have this query written in Microsoft Access, but I need to convert it to run on SQL Server.  I read multiple answers on dynamic pivoting in SQL Server, but can not apply it to my particular code, since I don't know how to pivot multiple conditions that are concatenated in one header.

Here is the code in Access:

TRANSFORM SUM(Sales.Tons) AS SumOfTons
SELECT     Sales.ID_SaleDest
FROM       (QualPack
INNER JOIN (Groups
INNER JOIN (Customers
INNER JOIN SaleDest
ON         Customers.ID_Customer = SaleDest.ID_Customer)
ON         Groups.ID_Group = SaleDest.ID_Group)
ON         QualPack.ID_QualPack = SaleDest.ID_QualPack)
INNER JOIN Sales
ON         SaleDest.ID_SaleDest = Sales.ID_SaleDest
WHERE      (((
                                            Year([Date_shipped]))=2017)
           AND        ((
                                            SaleDest.ID_Group)<>10)
           AND        ((
                                            QualPack.Quality_Second)<>'NO')
           AND        ((
                                            Sales.ID_Dataset)="AC"
                      OR         (
                                            Sales.ID_Dataset)="BU"))
OR         (((
                                            Year([Date_shipped]))=2016)
           AND        ((
                                            SaleDest.ID_Group)<>10)
           AND        ((
                                            QualPack.Quality_Second)<>'NO')
           AND        ((
                                            Sales.ID_Dataset)='AC'))
GROUP BY   Sales.ID_SaleDest,
           SaleDest.ID_Group
ORDER BY   Sales.ID_SaleDest,
           [Sales]![ID_Dataset] & '-' & Year([Sales]![Date_shipped]) & '-M' & Format(Month([Sales]![Date_shipped]),'00')
PIVOT [Sales]![ID_Dataset] & '-' & YEAR([Sales]![Date_shipped]) & '-M' & Format(MONTH([Sales]![Date_shipped]),'00')
WITH OWNERACCESS OPTION;

The problem is this:

PIVOT [Sales]![ID_Dataset] & '-' & YEAR([Sales]![Date_shipped]) & '-M'
    & Format(MONTH([Sales]![Date_shipped]),'00')

How can I pivot this in mssql?

Idea is to summarize Tons for a particular SaleDest, by using Dataset, Year of Date_shipped and Month od date_shipped, but in one header (concatenated). For example, output for AC Dataset, Year 2017 and Month 1:

SaleDest    AC-2017-M01
2311        29,4
2313        2,8
2314        49,56
2320        24


texts: Location of #th iteration of a character or a word in a sql text field

texts: How can I get the location for the #th iteration of a character or a word in a sql server text field?


texts: SQL Procedure Cache getting wiped out every 60 seconds

texts:
I have an Azure VM running SQL 2017, deployed around August 2020. Not yet taking a production load. Several databases restored, but no users active yet. Running sp_Blitz I was getting this: "The oldest query in the cache was created at "
So I ran this query every second for several minutes:
select
datediff(second, min(creation_time), getdate() ) AS [Age seconds],
count(*) as [Row Count]
from sys.dm_exec_query_stats

[Age Seconds] would get up to 60, and [Row Count] would get up to
about 30 or 40 max, then it would reset to 0's EVERY MINUTE.

This was going on for several days.
I disabled just about every login except mine, stopped ancillary services (Agent, SSIS, SSRS, etc.) and took user databases off-line. Nothing stopped it.
I see in the log several entries of

"A significant part of sql server process memory has been paged
out..."

Finally a re-start of the service did the trick.
SOMETHING SOMEWHERE was running DBCC FREEPROCCACHE every MINUTE like clockwork.
Has anyone ever encountered this, and if so, what was the resolution?


texts:
I am experiencing the unexplainable - at least to me.

I have some tables in my production database which have some indexes on. We then have a number of views which merge the data across tables to make it easier to select data that we need.

Last weekend, we recreated all the tables from scratch. We made sure to recreate all the indexes. However, now I am finding that my views are taking a huge amount of time to complete (from 2 secs to 43 mins). We have gathered stats on the tables using AUTO sample sizes but the explain plan still shows the joins being completed by FULL TABLE SCAN rather than index join.

Does anybody have any ideas? Do you have to do something to force the views to use the indexes.

Thanks Iain


texts: Specify type on SQL Server Computed Column

texts:
I have just added a computed column to a table, and it is showing up as an int and I'd like to make it a bit is this possible? I'm having a hard time finding any info on this. Below is the computed column:

IsActive  AS case when DateDecommissioned is null then 0 else 1 end PERSISTED


texts: How to extract values from a JSON field with Mongodb?

texts:
When I run mongdob query I got this json results in a column.
Column name is "user"

{"email": "mail1@mail.com"}
{"email": "mail6@mail6.com", "name": "name1"}
{"email": "mail2@mail.com"}
{"email": "mail7@mail7.com", "name": "mail2"}
{"email": "mail3@mail.com"}
{"email": "mail5@mail8.com", "name": "name3"}
{"email": "mail4@mail.com"}
{"email": "mail5@mail9.com", "name": "name4"}

However I need to extract email and name from this JSON into different columns like
"email", "name"

How can I do this with Mongodb?

Output


texts:
Tried to get some clarity but can't find any thing googling or searching  in forums.

We got a couple of files in the Data folder of one of our MS SQL servers

databasename.ndf_MSSQL_DBCC66
databasename.ndf_MSSQL_DBCC63

Can anyone tell me what these files are?


texts:
I run Mariadb 10.3 on a Debian 10.3 server. The database was standalone until now and worked fine. Now I want to set it up as a replication slave. I use this as a guide:
https://www.linuxbabe.com/mariadb/master-slave-replication-ubuntu-18-04-18-10

I added these lines into /etc/mysql/mariadb.conf.d/50-server.cnf:

server-id              = 02
relay-log-index        = /var/log/mysql/slave-relay-bin.index
relay-log              = /var/log/mysql/slave-relay-bin
replicate-do-db        = <my database>

and set up the change master setting. At this point I realized that this did not have any effect, and I discovered that the line to include the entries in mariadb.conf.d was missing in /etc/mysql/my.cnf. I added this line and restarted the mariadb service. Now things started to get weird. The service failed to start, and in the error.log I find these lines:

2020-05-03 13:59:31 7 [Note] Reading Master_info: 'master-master01.info'  Relay_info:'relay-log-master01.info'
2020-05-03 13:59:31 7 [ERROR] Failed to open the relay log './mysqld-relay-bin-master01.000009' (relay_log_pos 4)
2020-05-03 13:59:31 7 [ERROR] Could not find target log during relay log initialization
2020-05-03 13:59:31 7 [ERROR] Initialized Master_info from 'master-master01.info' failed
2020-05-03 13:59:31 7 [Note] Added new Master_info 'master01' to hash table
2020-05-03 13:59:31 0 [Warning] Reading of some Master_info entries failed
2020-05-03 13:59:31 0 [ERROR] Failed to initialize multi master structures
2020-05-03 13:59:31 0 [ERROR] Aborting

To get a fresh start, I removed the added lines from 50-server.cnf and restarted the service. Now it starts, but in the error.log I find this:

2020-05-03 14:04:41 7 [Note] Reading Master_info: 'master-master01.info'  Relay_info:'relay-log-master01.info'
2020-05-03 14:04:41 7 [Warning] Neither --relay-log nor --relay-log-index were used; so replication may break when this MySQL server acts as a slave and has his hostname changed!! Please use '--log-basename=#' or '--relay-log=mysqld-relay-bin' to avoid this problem.
2020-05-03 14:04:41 7 [Note] Initialized Master_info from 'master-master01.info'
2020-05-03 14:04:41 7 [Note] Added new Master_info 'master01' to hash table
2020-05-03 14:04:41 8 [Note] Master 'master01': Slave I/O thread: Start asynchronous replication to master 'replicant@10.8.0.12:3306' in log 'master-bin.000004' at position 343
2020-05-03 14:04:41 7 [Note] Started replication for 'master01'
2020-05-03 14:04:41 0 [Note] Reading of all Master_info entries succeeded
2020-05-03 14:04:41 0 [Note] Added new Master_info '' to hash table
2020-05-03 14:04:41 0 [Note] /usr/sbin/mysqld: ready for connections.
Version: '10.3.22-MariaDB-0+deb10u1'  socket: '/run/mysqld/mysqld.sock'  port: 3306  Debian 10
2020-05-03 14:04:41 9 [Note] Master 'master01': Slave SQL thread initialized, starting replication in log 'master-bin.000004' at position 256, relay log './mysqld-relay-bin-master01.000031' position: 4

  show slave status;
  Empty set (0.001 sec)

So this seems to be an inconsistent state: I cannot start the service with correct relay-bin files, but when I remove them, the replication starts anyway and gives these warnings. What can I do to clean this up?

Replication seems to work, btw.


texts: Make slave has same "general_log" table like master server

texts:
Apologize for my English. I'm very new to database administration. I've been trying to set up MariaDB replication server by following this document.
The only data that I need to replicate is in a table called

mysql.general_log

Basically all I want is that my replica server has the same data that my master server has in

mysql.general_log

here is my setting in a master server.
log-bin = /var/log/mysql-bin.log
server_id=1
log-basename=master1
binlog-format=mixed
bind-address        =  my public IP address
binlog_do_db=mysql
binlog_do_db=test2

and here is my setting slave server
during my #1st attempt, I used this setting.
server-id = 2
replicate_do_table=mysql.general_log

#2nd attempt
server-id = 2
replicate_do_db = mysql

#3rd attempt
server-id = 2
replicate_rewrite_db = mysql->mysql_archived

and here is my slave server status
MariaDB [(none)]> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: my master IP address
                  Master_User: ds_slave
                  Master_Port: 3306
                Connect_Retry: 10
              Master_Log_File: master1-bin.000004
          Read_Master_Log_Pos: 329
               Relay_Log_File: mysqld-relay-bin.000029
                Relay_Log_Pos: 619
        Relay_Master_Log_File: master1-bin.000004
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB:
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 0
                   Last_Error:
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 329
              Relay_Log_Space: 1208
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File:
           Master_SSL_CA_Path:
              Master_SSL_Cert:
            Master_SSL_Cipher:
               Master_SSL_Key:
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 0
               Last_SQL_Error:
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 1
               Master_SSL_Crl:
           Master_SSL_Crlpath:
                   Using_Gtid: No
                  Gtid_IO_Pos:
      Replicate_Do_Domain_Ids:
  Replicate_Ignore_Domain_Ids:
                Parallel_Mode: conservative
1 row in set (0.00 sec)

all of these configurations don't work on mysql.general_log table but works fine on other tables. any ideas for solutions or workaround? I've been frustrating for 3 days :( :(
Thank you for your help.


texts: Risk in evicting node from SQL Server cluster without prior uninstalling of SQL Server binaries

texts:
I have to perform upgrade of a SQL Server failover cluster with several SQL Server 2005 instances running on two Windows Server 2003.

Since the plan is to have servers running Windows Server 2008 and SQL Server 2008, two additional nodes were added to cluster, SQL Server binaries installed and cluster resources moved to temporary nodes.

At the moment I have 4 nodes in cluster and need to evict original two nodes so that it can be re-installed with Windows Server 2008, form cluster and migrate data back from temporary nodes to original nodes.

Question is: do I need to "properly uninstall" the SQL Server binaries from original nodes before evict? SQL Server resources will be running on temporary nodes after that for about two weeks and original cluster will be thus "destroyed". I mean nodes will not be again connected back to cluster, but can you think of any other risk in evicting cluster nodes other than possible issues with connecting server back to cluster?


texts:
I have a 8 core VM machine running SQL Server 2012. The current setting of MAXDOP is set as 5. There is recommendation from my colleagues to change the MAXDOP to 5 for better performance as there has been complaints on the same..

I might be very low on info in this question. But just curious, wont setting MAXDOP to 0 cause to degrade since all processors might not be needed for all the queries right? And is it right to set it as '0' rigth away?

Inputs please


texts: FullTextSearch indexing/searching words with special characters

texts:
I'm trying to query FullTextSearch indexed column for hashtags

SELECT Bio FROM Users
WHERE CONTAINS (Bio,'promoter')

SELECT Bio FROM Users
WHERE CONTAINS (Bio,'#promoter')

both results returns the same record

promoter of #Art

I tried also to put double quotes around the searched term "#promoter" but same result.

The same problem when I'm searching for '@something'

What I need to do in order FullTextSearch to index the special characters?


texts: Mongo Collection Mysteriously unavailable

texts:
We are running a mongo on single EC2 instance, we were not able to connect to it , upon debugging we saw CPU usage was spiking , this was the problem we faced previously we then restarted the instance and all connections were restored.

Now after two days same problem occurred, we tried to restart the instance and after that we found out our database is missing. Others database are as it is, only one got affected.

We saw mongo logs and it shows nothing about it , is there a way to find out what went wrong and to recover the data ?

Edit :

What O/S and version are you running?

Ubuntu 16 LTS

What is your version of MongoDB server and what security options have you enabled?

Mongo 3.4.9 , Security Password Protection

Was your instance restarted cleanly or did you force it to restart?

Clean Restart

Do you know what operations were running when the CPU was spiking?

Top Process Showed Mongod

Can you confirm whether there is a database missing (per the current description) or a collection (as per the current question title)?

A Database I think it had many collections now I can only see one


texts:
I have server database MySQL which is connected to different network(or ip address)
i want to find out which user has updated what and when ?

can log file help me in this if so how ?

please can anyone update me in this?


texts: How to properly filter columns after applying aggregate function

texts:
I find that I need to apply an aggregate a lot, but I don't want to show the aggregated value as well. So the best way to do this was to just nest the query to filter it out (like below) but there has to be a better way, how?

select state
from(
    select state, max(cnt)
    from (
        select state, count(*) as cnt
        from customer
        group by state) as t
) as r;

Here I just want to show state without the max value.


texts:
I have a (SQL Server) column which has a System Type but no Data Type (it is actually more then one) and this causes me problems when using the JDBC drivers on this column - it doesn't show up. I'm trying to understand what this means.

Can someone explain what the difference is between System Type and Data Type?
What does it mean that a column is missing a Data Type?

To create a Table which doesn't display a Data Type. This demands that you create a user which haven't read access to the user defined Data Types. This is how can reproduce it:

CREATE TYPE TEST_TYPE2 FROM [int] NOT NULL
GO
CREATE TABLE Customer
(
    CustomerID INT IDENTITY(1,1) NOT NULL,
    LastName    VARCHAR(100) NOT NULL,
    FirstName   VARCHAR(100) NOT NULL,
    ZIP         TEST_TYPE2 NOT NULL
)
GO
-- Create Table without UDDT
CREATE TABLE Customer_Orig
(
    CustomerID INT IDENTITY(1,1) NOT NULL,
    LastName    VARCHAR(100) NOT NULL,
    FirstName   VARCHAR(100) NOT NULL,
    ZIP         INT NOT NULL
)
GO

-- Create User with db_datareader to database
USE [master]
GO
CREATE LOGIN testUser WITH PASSWORD=N'THE_PASSWORD',
 DEFAULT_DATABASE=[master],
 CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
USE TEST_DATABASE
GO
CREATE USER testUser FOR LOGIN testUser
GO
USE TEST_DATABASE
GO
EXEC sp_addrolemember N'db_datareader', N'THE_PASSWORD'
GO


texts: I need advice on storing application level encrypted data

texts:
I just want to ask, what is your solution on storing encrypted data on database? And also how you do searching on it?
For now the subject database server is mysql.

Disk level encryption is not an option. I also research about database level encryption but, MySql TDE is not for me, and also if you migrate to other database server in my understanding you need to re-encrypt all the data.

So my last bet is application level encryption. And I tried to implement it using AES-GCM, but on this I will lose database searching, because encrypting the same data with the same key and id will produce different results. Now I got an idea but, i don't know what would be the risk implementing it. I was thinking blind indexing.

What I am planning to do is to store 2 version of the encrypted data. first the data will be encrypted with aes-gcm, then store another version of it using one-way encryption on this same data will produce the same hash. So the first encryption is reversible but not searchable, while the second one is not reversible but I can do whole word searching.

The obvious drawback of this is speed. But I want also to ask if in security perspective is it feasible? And also how do you do a search query on encrypted database using the partial text of the data?

Thank you


texts:
I've been working on a software/hardware upgrade from SQL2005 on win 2003 to SQL2008 R2 SP2 on win2008.  The new hardware has 48 cores with 128 gig of ram whereas the older server 16 cores with 32Gig ram.  We did several batch run and the result just didn't add up.  The newer server was slower than the older server?

I've run out of ideas and would like some pointers please.

Thanks


texts:
I have a table (emp) with two columns in ORACLE:

COMPANY   OFFICE    EMP
------------------------
9999      00001     emp1
9999      00001     emp2
9999      00002     emp3
9999      00002     emp4
0001      99999     emp5
0001      00001     emp6
0001      00002     emp7
0002      00001     emp8
0002      99999     emp9
0003      99999     emp10
0003      00001     emp11
0003      00002     emp12

I want to get value in EMP column with following statement:

SELECT
   EMP
FROM
   emp
ORDER BY COMPANY /*(But 9999 is first)*/, OFFICE /*(But 99999 is first).*/

I don't know how to do.


texts: Can't figure out how to update a column in one table based on multiple boolean values in another table in postgresql

texts:
What I want to happen is boolean milestone.milestonecompleted will turn true only if ALL of the linked boolean task.taskcompleted values are True
I've looked around and come up with this.
Any help would be great :)

UPDATE milestone
SET milestonecompleted =
    CASE task.taskcompleted WHEN false then false ELSE true END --CASE
FROM task, milestonedependency
WHERE milestone.milestoneid = milestonedependency.milestoneid
      AND task.taskid = milestonedependency.taskid
      AND milestone.milestoneid = 1


texts: Mysql 8.0.21 long lived prepared statements have a memory leak ? or are we doing something weird

texts:
-- updated the question because it was easy to initially assume I was asking for mysql tuning tips,  although some of the tips did help me narrow down the problem --
-- further update. After doing only a partial upgrade on some servers, we found the problem is occurring on MySQL 5.7 as well, so guessing it was some sort of change in behaviour between 5.6 and 5.7. It's still a bit weird to us how it didn't cause a problem in MySQL 5.6 --
We recently upgraded from MySQL 5.6 to MySQL 8.0 on a few servers,   one of the servers was fine,  and has had no problems, but it
has significantly less load than one of our other servers which has been running out of memory.
Our server launches,  then grabs 300 connections, and keeps them open with a C3P0 pool to the mysql server.
Over 4 days, this particular server accumulated 3500+ opened prepared statements, some of them taking more than 300 Mb in memory/sql/Prepared_statement::main_mem_root
It's normal for our server profile to have the connections / prepared statements open, and hasn't been a problem on mysql 5.6 or 5.7
looking that up at mysql https://dev.mysql.com/doc/dev/mysql-server/latest/classPrepared__statement.html#a999eb56b28a5202c0fb1f5df96db3c74
I can see it's somehow related to prepared statements, but 'allocate parsed tree elements (instances of Item, SELECT_LEX and other classes).' doesn't tell me much,  is it caching results? is that how it's growing ?
we're using connector/j 8.0.18  I've looked at the release notes for 8.0.18 -> 8.0.23 and there isn't any obvious memory leak fixes
our connection parameters include
cachePrepStmts", "true");
useServerPrepStmts", "true");

We were running these servers on AWS on MySQL 5.6 with the same overridden parameters on 8GB of RAM,  when we upgraded to MySQL 8.0.21 we started running out of RAM in about 1 day. We grew the server to 32Gb but didn't change the parameters. It's gone over 15 GB used and still going up.
We're pretty sure it's related to the per connection thread memory,  but not sure why.
Looking at memory_by_thread_by_current_bytes we have connections with over 200Mb
The server is running 8.0.21 on AWS RDS  m4.large
I've audited the code and all resultsets are being closed.  the prepared statements are in code as well, but the cache behavior above is keeping them open.
normally the innodb buffer pool is 4GB, but we dropped it to 3GB when we were on the 8GB machine for a bit more room.
show global status / memory_summary_by_thread_by_event_name / MySQL tuner run / show engine innodb status / show variables / sys.memory_by_thread_by_current_bytes / sys.memory_global_by_current_bytes
https://gist.github.com/DaveB93/138f6bac254fee5bbbbb7ce2af7c2fef
-- update --
we 'fixed' this by connecting to our application via JMX and changing the C3P0 connection pool settings to "maxIdleTimeExcessConnections" to 300 (up from 0)
This cleaned up all of the long lived connections, and freed up 10GB of ram,   This doesn't seem like the long term solution I want though.


texts: Can I downscale/upscale my MySQL server automatically?

texts: Is this doable? Since we know the peak times of our platform we are planning to downscale our DB after peak hours then upgrade the specs before the start of peak hours, how can we do this? Like how is RDS doing this?


texts: MySQL for Administrators course from Oracle: worth it?

texts:
This is a question probably experienced MySQL admins can answer, but is not really a technology question.

I am looking into MySQL Administraton course from Oracle/MySQL AB.

It seems interesting as it will fill the knowldge gap that I have being a MySQL admin. But is this course worth it. Would be interested to know from some one who has taken the course or peeked at the content. I can find the topics which would be discussed, but they are there in almost any MySQL book any way.

Considering that this course is almost an entry level software developers whole month salary, it is not a cheap buy and does require 5 working days so for a small company it's not small affair.

They have the same course in  CD format but I think that the instructor led course is probably the best.

One can get trained by reading books, blogs, manuals and practicing. But this training does seem like a ramp to quick learning.


texts:
How can I write a query to seed a many to many table. Let's say I have an Employee table, a Branch table, and I am introducing an EmployeeBranch table for the many to many relationship.

I need to insert records for every employee and branch combination into the EmployeeBranch table. So if there are 100 employees and 10 branches, I would end up with 1000 records; each employee will have 10 records for each branch.


texts: PostgreSQL dblink password encrypted or not?

texts:
I have two PostgreSQL servers:

server 1: 192.168.0.1
server 2: 192.168.0.2

I want to connect to the database on server 2 from server 1. I do it like this:

SELECT dblink_connect('hostaddr=192.168.0.2 port=5432 dbname=mydb user=postgres password=mypasswd');

My question is that when I do such connect my password is send as plaintext or its somehow encrypted during the transmission to the server 2?

I use PostgreSQL 9.3 on Linuxx Mint 16 (server 1) and Xubuntu 13.10 (server 2).


texts: How to get server cpu, memory, network usage from SQL Server

texts:
For reporting purposes, I want to get SQL Server box CPU, memory, network (not SQL Server process alone) and show it in report. Do you have any idea how to pull this? My initial thoughts are run wmi queries through task scheduler to get the data every 15 minutes and insert into database table and use it in reports

My report format will be like below

Server name:
CPU usage
Memory usage
Network usage
Disk space usage:

My questions:

can this be done in any,other way other than wmi/power shell
I will also need to get web servers data
will you be able to give one sample query for one server.


texts: Filtering subquery results with group by

texts:
I have a join that works but seems hacky because it orders the subquery and uses group by to filter everything but the top subquery result. Without doing so, the cartesian product of s and print_issues.pub_date would be returned.

SELECT
    print_issues.pub_date,
    print_issues.x,
    s.page_size
FROM print_issues,
  ( select pub_date, page_size
    from print_page_size,
          print_issues
    where effective_date < print_issues.pub_date
    order by effective_date desc
  ) as s
WHERE s.pub_date = print_issues.pub_date
group by print_issues.pub_date
order by print_issues.pub_date desc

The query is an artifact of the schema of print_page_size. That table has the columns page_size and effective-date. New entries with a greater effective_date supersede old ones. The goal of the query is to unite the print_issue table with the page_size in effect when each issue was published.

Sample Data:

TABLE: print_page_size
======================
effective_date      page_size
2014-01-01          100
2017-05-01          105

TABLE: print_issues
===================
pub_date      x
2017-04-26    "Random"
2017-05-02    "Data"

OUTPUT
======
print_issues.pub_date          print_issues.x    s.page_size
2017-04-26                     "Random"          100
2017-05-02                     "Data"            105

My questions: Is there a problem with this approach? Is there a more direct way to to achieve the desired result?


texts: Enhanced ER Model Supertype / Subtype modeling issue with inheritance and recursion

texts:
Before posting, I have looked for a good solution on internet and I have coined this post for 3 days in row and trying to think through it prior this post. So if I have missed something or got it wrong, please, have understanding.

I need to model Invoicing Database for creating Invoices (in PDF) with SQL Server 2014 and Visual Studio 2013. My company operates and works only with other businesses, that is business entities.

I don't know how to model situation in which:

Seller (Service Provider) issues Invoice to Customer XOR Representative. (XOR = Exclusive OR)

I need to track info about companies and all of them: Seller, Customer or Representative initially have the same attributes. I can come up with identifiers for them like SellerID, CustomerID, RepresentativeID but I don't know if it is valid thing to do or not, since they inherit ID's from Superclass (Company) and up to this point I can't think of any other unique attribute.

There are some business rules like:

Seller (only one at the moment - my company) issues Invoices to either Customer or Representative.
Customer can have only one or none Representative and one Representative can represent many Customers.
Representative is Customer and is Company
I need to track info about issued Invoices and Companies, of course there are other entities like: Bank Accounts, Invoice Items, Services, Agreements (which  can be Contracts and Orders)

Here are the Entities:

Company (supertype)
Seller (Service Provider) (subtype of Company)
Customer (subtype of Company)
Representative (subtype of Customer or subtype of Company - see pictures for more on this...) (maybe this should be recursive relationship)

Questions:

Is supertype / subtype needed here (since subtype Entities have no unique attributes and since it gives me a headaches)?
How to implement inheritance with one entity in recursive relationship - if for instance Customer / Representative is in recursive relationship (relational table example needed)
Can you give me relational solution (EER to relational) with some examples (like table with data) so I can wrap my mind around it.

I have come up with following EER Model:

And now I am missing one of following two modeling options (some other suggestion that will work better and be less newbie friendly) ...

This one is full_disjoint with more inheritance and partial disjoint:

And this one is full overlapping (missing one "p" on a picture description):


texts:
i was going through the book: High performance MySql 2nd edition. In chapter 4 it says:

  An application that keeps counts in a table can run into concurrency
  problems when updating the counters. Such tables are very common in
  web applications.

  To keep things as simple as possible, suppose you have a counter table
  with a single row that just counts hits on your website:

mysql> CREATE TABLE hit_counter ( cnt int unsigned not null ) ENGINE=InnoDB;

  Each hit on the website updates the counter:

mysql> UPDATE hit_counter SET cnt = cnt + 1;

  The problem is that this single row is effectively a global “mutex”
  for any transaction that updates the counter. It will serialize those
  transactions. You can get higher concurrency by keeping more than one
  row and updating a random row.

  Below is the change:

mysql> CREATE TABLE (slot tinyint unsigned not null primary key, cnt int unsigned not null ) ENGINE=InnoDB;

  Prepopulate the table by adding 100 rows to it. Now the query can just
  choose a random slot and update it:

  mysql> UPDATE hit_counter SET cnt = cnt + 1 WHERE slot = RAND() *
  100;

  To retrieve statistics, just use aggregate queries:

mysql> SELECT SUM(cnt) FROM hit_counter;

Here comes my doubt:

As there are two different queries one which increment the cnt and other which calculate the sum of cnt than how come it will give correct result to two different threads executing simultaneously? For example t1 fires the first query to increase the cnt  , before it fetches the sum t2 also updates the cnt than the 2nd query for t1  will fetch wrong result. Please clarify.

if you dont have the book please refer this.


texts: How to store and work from a Postgresql database on an external hard drive?

texts:
I have installed PostgreSQL 13.1 on two computers, a laptop and desktop.
I created database using a Tablespace on a portable SSD drive under F:\MyProject\DB\PG_13_202007201\20350. So I expect all the database files to be in there somewhere.
I want to work on the database on the SSD drive which is fine on the laptop that created the database, but when I go to my desktop I can't figure out how to attach (SQL Server style) the existing database/tablespace and continue working on it.
Is there a way to work like this with Postgresql?


texts: What is the best way to replace a SQL Server database with an updated one?

texts:
I have a SQL Server instance with 3 databases in it (SQL Server 2016 Web on Win Server 2012 r2). All databases have relations with each other. It is live and is feeding some web services.
I need to update 2 databases (or all 3) with the actual data. It takes around a week. So, I created a new VPS and a new SQL Server on it, I created same databases (with the same names and same filenames) on it and I filled them with the new data with no rush.
Now I want to learn what is the best, safest and fastest way to update the live environment.
I plan to do something like this:

Detach the new DBs on the new SQL Server

Detach the hdd from the new windows server.

Attach the hdd to my current live server.

Detach (or maybe delete ???) the old 2 or 3 databases from the live SQL Server.

Attach the new databases on newly attached hdd to old live server.

I have some questions about the logins, relations between the databases, etc...
I need to interrupt the services no more than some minutes. How can I proceed?
I would appreciate with any help.


texts:
I was having the toughest time today adding some new users to our system.  No matter what I tried (from the root account), it kept failing: CREATE USER, GRANT what have you.  Entries were getting created into the User Table but the users were not able to access the DBMS remotely.  First I had the wildcard host and when that failed I switched a specific IP address, and still no luck.  To reiterate I was doing all this from my root account.
Finally I decided to create the accounts via cPanel.  Voila! Everything works like a charm now.  I was able to grant access for a wildcard host, and it works as expected.  (cPanel imposed a naming constraint on the user names, but that's a small price to pay given all the headache I went through)

Q) Does cPanel act as some kind of super-superuser, enabling operations that even the root user cannot?  Or is the magic in the naming constraint for new users that cPanel imposed?


texts: MySQL cluster 5.6 doesn't convert invalid TIMESTAMP value to '0000-00-00 00:00:00'?


texts:
I cannot work this out, how do I get the subquery to return the lowest course weight for each lecturer, it currently only returns the lowest and is my subquery wrong or outer query?

Question: Show each lecturer’s lowest coursework weighting, displaying the Staff ID, the Module ID and the weighting selected.

SELECT m1.ModuleID, m1.CWWeight, l.StaffID
FROM dbo.module as m1
INNER JOIN dbo.Lecturer as l
    ON m1.ModuleConvenor = l.StaffID
WHERE m1.CWWeight = (SELECT MIN(m2.CWWeight)
                    FROM dbo.Module as m2)


texts:
We have just completed an 11g to 12c migration to a new server. There is a schema that didn't seem to come over that consists fully of external .dat files.

I can't seem to find a way to simple 'attach' these pre-existing (and moved from the old server) .dat files. Is the method to get this data back in to re-create the schema, table by table, pointing to the existing .dat files?

If so, is there a simple way to generate all the DDL statements for an entire schema of tables?

Thanks,
Wes


texts: It is possible to export a MySQL database without BLOB fields?

texts: If I have a MySQL database with some tables where one have a BLOB column, It is possible to export the whole database excluding the BLOB fields? If not, could you suggest a solution to work-around it?


texts:
Here is my SQLFiddle: http://sqlfiddle.com/#!2/affc4/9

A brief description of the issue:

I have the following data:

topics
brands
celebrities

All 3 of those data types can be associated to an article.

The associations exist in article_assets.

As you can see from the fiddle, I easily retrieved all of the articles that are of asset_type topic and topic name of Relationships.

The issue I'm having is:

I cannot assign an article to having an association of "hot" as a topic AND also the celebrity "Denzel Washington".

I know the flaw is in my database structure. Here's my stream of thoughts on my database structure:

The article_assets table contains an asset type and then a foreign key to that data's table primary id (asset_id)
This creates a problem since I can only reference the specific asset type and the corresponding primary key id.

I need to figure out a solution to allowing multiple references to data and I don't really know how to modify my database structure from here.

Thanks for the help.


texts: Design ER with complicated relationships between different tables

texts:
I try to design a database and I am stuck dealing with a part of it. The problem is very scientific, so for simplicity, I will try to give you a simple representation that accurately matches the case.

Imagine we have an oven with many drawer trays (each can only be used once and then get stored), and each tray is split in a specific number of compartments. That is either:

8 compartments
2 compartments
no compartments (or 1 big if you like)

You can call it 3 types of trays if that's better for the database design, in case some other type becomes available in the future.

Now, we have projects (imagine persons) and each project can have 1 or more iterations (imagine bakes). Each iteration can have 1 or more samples (imagine cakes). Each iteration is assigned to 1 tray. Two or more iterations of different projects that run at the same time, can be assigned at the same tray. However, two iterations of the same project CANNOT be assigned at the same tray. Imagine a new iteration will come at a later date, so since the trays are single-use, the next iteration has to be assigned on a new tray.

Each sample can be stored in 1 or more compartments (we can divide a cake in smaller cakes and bake them in several compartments of the same tray). Also, 1 compartment can hold 1 or more samples from the same iteration of the same project (imagine we divide the cake to 4 parts, and we bake 2 parts in compartment_1 and 2 in compartment_2), or from an iteration of a different project.

So in other words, two or more projects can run in parallel, and their iterations can be assigned to one tray. Future iterations of either of the projects will be assigned in a different tray. Each sample and compartment can be related in any order, and we need to keep track of everything.

I have tried several things, but nothing feels good enough to post here as ER. My tables are the ones I have in bold: project, tray, sample, iteration, (not sure about compartment).

Since a project can use many trays, and a tray can host many projects, I use the iteration table as a join table. But now I have no clue where I should connect the sample.

A tray is hosting many samples, while 1 sample only uses 1 tray, so I could connect them 1-to-many. However, I also need to keep track of whether the sample is stored in 1 compartment or spread out on more. Then I also need to know which project each sample belongs to.
Similarly, if I relate sample to project, I will not have information about the tray relation to the sample.
An iteration may have 1 or more samples, while 1 sample can be in 1 iteration, so I could create a relationship sample-iteration. This way I will be able to know both the project and the tray each sample is related to. However, the compartment problem remains.

Sounds like a chasm trap, so a triangle relationship could be the way to go, but I am not experienced enough and I'm not sure how to design something like this.

To tackle the compartment problem, I could use a field in sample to store the compartment information and use the software to make sure the compartment values are as supposed to be. But not sure this is the right choice.

Any help to design an ER for this part of the project is highly appreciated.


texts: Should you create separate tables for fixed options for a column?

texts:
As shown below, in multiple places in my DB Design I am creating tables for "options" if you will. Example, RFP Stages. Which would hold things like "Complete", "Bidding", etc.

I'm doing this because it was recommended to me at one point in a question that was deleted. Is this the proper way to do this? Or should I just store these options as text, given there is only 5 or so possibilities?


texts: Why is creating and dropping a random index fixing this performance problem?

texts:
I have a big heap table with 17,093,139 rows. This table is the most heavily used table in the database. Since this is a heap table, there are only non-clustered indexes in this table. I rebuild/reorganize fragmented indexes on this table regularly.

Now we are facing this issue very often: Lots of queries accessing this table will suddenly start taking longer than usual. When I check, I observe that the execution plans for the queries have changed.

I create and drop a random non-clustered index and this fixes the issue.

What I don't get is, what is causing these sudden slowness randomly anytime and what does creating and dropping the index do in the background to the table to fix it which the index rebuild job doesn't do?

I need to find what exactly is triggering these slowdowns so that a permanent solution can be found as I can't just just keeping creating and dropping the index to fix this issue every time.

Any help here would be greatly appreciated.


texts: SQL: Select all items and add info from left join where condition is met

texts:
Background

I am creating a news site. Table NEWS has all news with unique ID news_uid.

TABLE NEWS
--------------
news_uid | ...

If a user has read a certain news, that info gets stored in table NEWSREAD:

TABLE NEWSREAD
--------------
user_uid | news_uid | news_read
-------------------------------
A        | N1       | 1
A        | N2       | 1
B        | N3       | 1

So here user A has read News 1 and 2 and user B has read N3.

Question

I am trying to create a SQL query that

Selects all news from the news table
And tells me for a given user, which news have been read

Output:

news_uid | news read by user A
------------------------------
N1       | 1
N2       | 1
N3       | 0 (or NULL)

I have gotten so far, but this does not show N3 in the results.

SELECT NEWS.news_uid, NEWSREAD.news_read FROM NEWS
LEFT JOIN NEWSREAD ON NEWS.uid = NEWSREAD.news_uid
WHERE NEWSREAD.user_uid = 'A' or NEWSREAD.user_uid IS NULL


texts:
I have a following query. THIS returns me correct result. Note the criteria.

SELECT
        `review_score_aggregate`.`review_id` AS `id`,
        `review_score_aggregate`.`business_id` AS `business_id`,
        `review_score_aggregate`.`review_channel_id` AS `review_channel_id`,
        `review_score_aggregate`.`review_date` AS `review_date`,
        COUNT(0) AS `review_count`,
        `review_score_aggregate`.`average_score` AS `average_score`,
        TRUNCATE(AVG(`review_score_aggregate`.`rating`),
            2) AS `average_rating`,
        SUM((CASE
            WHEN (`review_score_aggregate`.`average_score` >= 0) THEN 1
            ELSE 0
        END)) AS `positive_count`,
        SUM((CASE
            WHEN (`review_score_aggregate`.`average_score` < 0) THEN 1
            ELSE 0
        END)) AS `negative_count`
    FROM
        `review_score_aggregate`
    WHERE business_id = 10 AND review_channel_id = 3 // CRITERIA
    GROUP BY `review_score_aggregate`.`business_id`
        , MONTH(`review_score_aggregate`.`review_date`)
        , YEAR(`review_score_aggregate`.`review_date`)

Correct result:

Now preferably I want to use view for this same query which is defined exactly like this:

CREATE VIEW `review_score_monthly_view` AS
    SELECT
        `review_score_aggregate`.`review_id` AS `id`,
        `review_score_aggregate`.`business_id` AS `business_id`,
        `review_score_aggregate`.`review_channel_id` AS `review_channel_id`,
        `review_score_aggregate`.`review_date` AS `review_date`,
        COUNT(0) AS `review_count`,
        `review_score_aggregate`.`average_score` AS `average_score`,
        TRUNCATE(AVG(`review_score_aggregate`.`rating`),
            2) AS `average_rating`,
        SUM((CASE
            WHEN (`review_score_aggregate`.`average_score` >= 0) THEN 1
            ELSE 0
        END)) AS `positive_count`,
        SUM((CASE
            WHEN (`review_score_aggregate`.`average_score` < 0) THEN 1
            ELSE 0
        END)) AS `negative_count`
    FROM
        `review_score_aggregate`
    GROUP BY `review_score_aggregate`.`business_id`
        , MONTH(`review_score_aggregate`.`review_date`)
        , YEAR(`review_score_aggregate`.`review_date`)

.. and through the application I would simply query from the view. Now this gives me way different result.

SELECT *
FROM review_score_monthly_view
WHERE business_id = 10
    AND review_channel_id = 3;

Wrong result??!

Can't find what causes this, any ideas please.


texts: SSIS connection used based on passed value

texts:
I have an SSIS package that populates a reporting warehouse from a live and test environment.

Currently the package has each step duplicated as the source connection changes between live and test. This is a pain to maintain if changes are required.

I know I can send a variable value to the package via the agent job.

I was wondering if I could cut the package down so that the steps only appear once. The agent job could then have two steps that execute the package. E.g First step execute the package and variable set is the name of the live DB and second step executes the package again but the variable set is the name of the test DB.

Can you dynamically select a connection based on a variable received?

Is there a better method?

Thanks


texts:
There is a query:

SELECT entry_id FROM table_name
WHERE (path_id = 7 AND value_string =  N'00020003-0004-0005-0006-000700080009' )

The table in this query contains some millions of records. Both path_id and value_string have their own non-clustered indexes.

When I use SQL Server Profiler to trace this query with Lock:Acquired event, I see that there are three IS page locks taken: one on a page that contains this value_string as '00020003-0004-0005-0006-000700080009', one on a page that contains path_id as 6 (which is staggering), and one on a page that contains path_id as 7 (which is expected).

The way I get information about pages: in Lock:Acquired there is a field TextData containing text like 1:87032; I take this and execute

DBCC PAGE ('mc33', 1, 87032, 3)

Then I get output like this:

The question is: why engine puts an IS lock on a page containing only sixes, if the query is only interested in sevens? This "excessive" locking causes us deadlocks when this query is executed in parallel with certain inserts, and I would like to know how I can get rid of such locking.


texts: I need to monitor the local admins on my SQL Server systems for auditing. Is there a way to use a stored procedure with powershell to accomplish that?


texts:
I set up simple bidirectional db replication using Oracle Streams. I used enterprise manager for this. Streams are active and their status is ok. The problem is that the replication is very slow. What happened:

I made insert into on machine A.
I checked but there was no data in machine B.
I opened View Capture Statistics on machine A and my transaction was under Active Streams Transactions List (Running for more than 1 minute) and was Active For over 1000 seconds.
I went to make some food and when I came back transaction had been eventually propagated to machine B.

So it looks like the transaction stayed in some kind of buffer for over 1000 seconds. How can I speed up this process? I am totally new in databases so please forgive me if it is a stupid question :).

Machines are just vbox instances (WinXP + Oracle 11g Enterprise).

Greetings.


texts: How do I search to see if a MySQL user exists on the system?

texts:
I'm not finding a user by show grants for 'username'@'localhost'' I'm thinking that perhaps the username is slightly different, so I want to do a wildcard search of all the database users. How can I do this?

update: for clarification, as I'm thinking maybe I've not been 100% clear. by wildcard I meant I wanted to be able to search for only part of the users name, or for all users who's name matches pattern. On the system I needed this on there were 5k+ users, and although that's not performance loss huge, I don't want to look through them one at a time either.


texts: How can I create a view in a stored procedure on MySQL?

texts:
I am trying to dynamically create views based on an SQL query and have a trigger recreate them when the fields in the view are to change.  When I call the procedure I end up with a permission denied issue.  Here is the SQL:

DROP FUNCTION IF EXISTS EE_FIELD_NAME;
CREATE FUNCTION EE_FIELD_NAME (id INT)
    RETURNS VARCHAR(32) NOT DETERMINISTIC
    RETURN (SELECT field_name FROM exp_channel_fields WHERE field_id = id);

DROP PROCEDURE IF EXISTS CREATE_CHANNEL_VIEW;
DELIMITER $$
CREATE PROCEDURE CREATE_CHANNEL_VIEW (IN channame VARCHAR(40))
BEGIN
    DECLARE done INT DEFAULT 0;
    DECLARE f INT;
    DECLARE fcur CURSOR FOR SELECT field_id FROM exp_channel_fields f INNER JOIN exp_channels c ON c.field_group = f.group_id WHERE channel_name = channame;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

    SET @field_names = '';

    OPEN fcur;

    field_loop: LOOP
        FETCH fcur INTO f;
        IF done = 1 THEN
            LEAVE field_loop;
        END IF;
        SET @field_names = CONCAT(@field_names, '`field_id_', f, '` AS `', EE_FIELD_NAME(f), '`, ');
    END LOOP;

    CLOSE fcur;

    SET @field_names = SUBSTRING(@field_names, 1, CHAR_LENGTH(@field_names) - 2);

    SET @dv = CONCAT('DROP VIEW IF EXISTS cv_', channame);
    PREPARE dstmt FROM @dv;
    EXECUTE dstmt;
    DEALLOCATE PREPARE dtmt;

    SET @cv = CONCAT('CREATE VIEW staging.cv_', channame, ' AS SELECT entry_id, author_id, title, url_title, status, versioning_enabled, view_count_one, view_count_two, view_count_three, view_count_four, allow_comments, sticky, entry_date, year, month, day, expiration_date, comment_expiration_date, edit_date, recent_comment_date, comment_total, ', @field_names, " FROM staging.channel_entries e NATURAL JOIN staging.exp_channels c WHERE c.channel_name='", channame, "'");
    PREPARE cstmt FROM @cv;
    EXECUTE cstmt;
    DEALLOCATE PREPARE stmt;
END$$
DELIMITER ;

The resulting error is Error Code: 1142, ANY command denied to user 'user'@'10.%' for table '/tmp/#sql_671_0'.  My assumption is that this is a result of the select driving the create view does not execute on the table I expect it to.  SHOW GRANTS; gives me GRANT ALL PRIVILEGES ONstaging.* TO 'user'@'10.%' WITH GRANT OPTION

Is it possible to create a view within a stored procedure?


texts: What are some efficient ways in using COUNT(*) with WHERE

texts: Instead of calling COUNT(*) with a WHERE clause, is there anything one can do to make this work more efficiently on the server?  I would like to get this count on complicated joins, just wondering the best way to do this. Using SET NOCOUNT OFF? Because SET NOCOUNT ON is enabled by default in a new stored procedure and is suggested I guess by Microsoft.  Thank you.


texts: Readable join for execution plan in Oracle

texts:
I want to understand what join operations were done on my database by different users for future analysis and improvements. but I have issues with understanding the execution plan.
Example:
Consider this query:
SELECT f.year, f.geoid, a.age_band, sum(f.num_people), sum(f.num_insured)
FROM fact f
  JOIN age_bands a ON f.age_code = a.age_code
WHERE f.gender_code = 1
GROUP BY f.year, f.geoid, a.age_band

When I try to see the execution plan with this command:
SELECT s.sql_id, p.*
FROM v$sql s CROSS JOIN LATERAL (SELECT * FROM TABLE (
  dbms_xplan.display_cursor (s.sql_id, s.child_number, 'ALLSTATS LAST')
)) p
WHERE s.sql_text LIKE '%f.gender_code = 1%';

I get different values for the Predicate Information values (for different child numbers)
for instance - for child number 0:
Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("ITEM_1"="A"."AGE_CODE")
   6 - filter("F"."GENDER_CODE"=1)

for child number 1 (and 2):
Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("F"."AGE_CODE"="A"."AGE_CODE")
   4 - filter("F"."GENDER_CODE"=1)

My questions are

what is "ITEM_1" and why does it replace "F"."AGE_CODE" ?

is there a better way to understand what joins were made in the DB?

is there a simple way to convert the alias in the Predicate Information to the full table name? (for instance convert "A" to "AGE_BANDS" in the above example)


texts: Can you get a filehandle to an Oracle SecureFile LOB?

texts:
Does Oracle's SecureFile LOB allow me to obtain a filehandle to the underlying resource.

Both PostgreSQL through lo_open and SQL Server with FILESTREAM's OpenSqlFilestream allow you to get a native filehandle to a resource tracked by the database. What's the Oracle alternative to this?

With PostgreSQL you get a filehandle by providing a resource id (large object id) that is only valid for the duration of the transaction and the DB provides the filehandle.
With SQL Server you get a transactionToken which is only valid for the duration of the transaction which you can provide to the OpenSqlFilestream to get a filehandle.


texts:
The production table I want to alter is around 700 Gb. I want to remove a FK from that table. Mysql version is 5.1.61-log.

I found following options:

pt-online-schema-change: alters a table’s structure without blocking reads or writes. This looks most efficient in terms alteration time. This also takes care of server load by dividing table copy into chunks and pauses data copy process in case of high load on the server.

The only problem with tool for me is available space on server is 450 GB and there is no room for another drive. This tool works on a copy of the table which is around 700 GB.

Upgrade to Mysql 5.6: With ONLINE DDL feature of 5.6, most of the ALTER types won’t block writes to a table that is being changed. Also it supports Add/Drop a foreign key constraint without the need of full table copy.

But I am not sure how it takes care of server load and how much time it going to take for alteration.

Is there any other option like pt-online-schema-change which doesn't have to copy table? Any experience/comments on ONLINE DDL of Mysql 5.6?

Thanks in advance!


texts:
I have this for simplicity fictional table which explains my problem:

CREATE TABLE IF NOT EXISTS `posts` (
  `pid` int(11) NOT NULL AUTO_INCREMENT,
  `user` int(11) NOT NULL,
  `posts` int(11) NOT NULL,
  `comments` int(11) NOT NULL,
  `notes` varchar(100) NOT NULL,
  PRIMARY KEY (`pid`),
  KEY `posts` (`posts`)
  KEY `comments` (`comments`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1687306;

The table has over 1 million rows, MariaDB version 10.0.20

Now I want to perform an update and set a notice on one post that has more comments than posts

UPDATE posts SET notes = 'more comments' WHERE posts < comments

Now this query takes about 2-4 seconds to complete.

I suppose I'm missing an index or is it necessary to rewrite the query?

When I do queries with a fixed comparison

UPDATE posts SET notes = 'more comments' WHERE posts > 1000

the query runs fast.


texts: Postgresql inheritance based database design

texts:
I'm developing a simple babysitter application that has 2 types of users: a 'Parent' and the 'Babysitter'. I'm using postgresql as my database but I'm having trouble working out my database design.

The 'Parent' and the 'Babysitter' entities have attributes that can be generalized, for example: username, password, email, ... Those attributes could be
placed into a parent entity called 'User'. They both also have their own attributes, for example: Babysitter -> age.

In terms of OOP things are very clear for me, just extend the user class and you are good to go but in DB design things are differently.
Before posting this question I roamed around the internet for a good week looking for insight into this 'issue'. I did find a lot of information but
it seemed to me that there was a lot a disagreement. Here are some of the posts I've read:

https://stackoverflow.com/questions/190296/how-do-you-effectively-model-inheritance-in-a-database: Table-Per-Type (TPT), Table-Per-Hierarchy (TPH) and Table-Per-Concrete (TPC) VS 'Forcing the RDb into a class-based requirements is simply incorrect.'

Multiple User Types - DB Design Advice:

Table: `users`; contains all similar fields as well as a `user_type_id` column (a foreign key on `id` in `user_types`
Table: `user_types`; contains an `id` and a `type` (Student, Instructor, etc.)
Table: `students`; contains fields only related to students as well as a `user_id` column (a foreign key of `id` on `users`)
Table: `instructors`; contains fields only related to instructors as well as a `user_id` column (a foreign key of `id` on `users`)
etc. for all `user_types`

How to model inheritance of two tables mysql

https://stackoverflow.com/questions/3074535/when-to-use-inherited-tables-in-postgresql: Inheritance in postgresql does not work as expected for me and a bunch of other users as the original poster points out.

I am really confused about which approach I should take. Class-table-inheritance (https://stackoverflow.com/tags/class-table-inheritance/info) seems like the most correct in
my OOP mindset but I would very much appreciate and updated DB minded opinion.


texts:
I want to create the trigger which prevents insertion in the reservation table of flights (numflights) which does not exist.

Tables
----------------------------------------------
flights(numflight, origine, destination, dep, arr)
airports(code, city, country)
reservation(people, numflight, date_travel)

The columns origine and destination contain airport codes.


texts: Procedure doesn't have compatible arguments

texts:
Getting an error while invoking my procedure named LOG_MSG_TEST(). Procedure Schema name is ADMINIST.

My procedure has one INTEGER input parameter and one VARCHAR output parameter.

I am calling this procedure in the CLP using mentioned syntax:

call ADMINIST.LOG_MSG_TEST(1);

It is existing in the SYSCAT.PROCEDURES table, but I am still getting the following error:

  No authorized routine named "ADMINIST.LOG_MSG_TEST" of type
  "PROCEDURE" having compatible arguments was found.. SQLCODE=-440,
  SQLSTATE=42884, DRIVER=4.17.30


texts:
I am having an issue with a SQL query that runs and runs but never completes.  The database is an Azure SQL database.

My table has tens of millions of rows and looks like this:

CREATE TABLE [dbo].[MyData](
[CustomerId] [int] NOT NULL,
[TagName] [nvarchar](100) NOT NULL,
[TagValue] [real] NULL,
[TimeStamp] [datetime2](7) NOT NULL,
[status] [int] NULL,
CONSTRAINT [PK_MyData] PRIMARY KEY CLUSTERED
(
  [CustomerId] ASC,
  [TagName] ASC,
  [TimeStamp] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF,
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
)

My select statement looks like this:

DECLARE @StartDate as datetime
DECLARE @EndDate as datetime

Select @StartDate = LastReadDT from MyData_LastDT_Read
Select @EndDate = dateadd(minute,30,@StartDate)

SELECT   CAST([TagName] as varchar(100)) as [tag],
         CAST(isnull(TagValue, 0) as real) as tagvalue,
         CAST([TimeStamp] as datetime) as [DataTimeStamp],
         CAST(192 as int) as [status]
FROM     [dbo].[MyData]
where    CustomerID = 1
and      TimeStamp <= @EndDate
and      TimeStamp > @StartDate
order by TimeStamp asc

The strange thing is if I remove the @StartDate and @EndDate variables and instead just hard-code the actual TimeStamp values in the where clause, the query will return results in less than 1 second.  Why would hard-coding the TimeStamp values in the query make such a dramatic difference compared to the variables?  Any suggestions on how to improve the performance?  Unfortunately I need to use the variables as this query is executed repeatedly with different time ranges.


texts: MySQL: Optimize UNION with "ORDER BY" in inner queries

texts:
I just set up a logging system which consists of multiple tables with the same layout.

There is one table for each data source.

For the log viewer, I want to

UNION all the log tables,
filter them by account,
add a pseudo column for identification of the source,
sort them by time,
and limit them for pagination.

All tables contain a field called zeitpunkt that is an indexed date/time column.

My first attempt was:

(SELECT l.id, l.account_id, l.vnum, l.count, l.preis, l.zeitpunkt AS zeit,
 'hp' AS source FROM is_log AS l WHERE l.account_id = 730)

UNION

(SELECT l.id, l.account_id, l.vnum, l.count, l.preis, l.zeitpunkt,
 'ig' AS source FROM ig_is_log AS l WHERE l.account_id = 730)

ORDER BY zeit DESC LIMIT 10;

The optimizer cannot use the indexes here because all rows from both tables are returned by the subqueries and sorted after the UNION.

My workaround was the following:

(SELECT l.id, l.account_id, l.vnum, l.count, l.preis, l.zeitpunkt AS zeit,
 'hp' AS source FROM is_log AS l WHERE l.account_id = 730
 ORDER BY l.zeitpunkt DESC LIMIT 10)

UNION

(SELECT l.id, l.account_id, l.vnum, l.count, l.preis, l.zeitpunkt,
 'ig' AS source FROM ig_is_log AS l WHERE l.account_id = 730
 ORDER BY l.zeitpunkt DESC LIMIT 10)

ORDER BY zeit DESC LIMIT 10;

I was expecting the query engine would use the indexes here since both subqueries should be sorted and limited already prior to the UNION, which then merges and sorts the rows.

I really thought this would be it, but running EXPLAIN on the query tells me the subqueries still search both tables.

EXPLAINing the subqueries themselves shows me the desired optimization but UNIONing them together it does not.

Did I miss something?

I know that ORDER BY clauses inside UNION subqueries are ignored without a LIMIT, but there is a limit.

Edit:
Actually, there probably will also be queries without the account_id condition.

The tables already exist and are filled with data. There might be changes in the layout depending on the source so I want to keep them divided.  Additionally, the logging clients use different credentials for a reason.

I have to keep a kind of layer between the log readers and the actual tables.

Here are the execution plans for the whole query and the first subquery as well as the table layout in detail:

https://gist.github.com/ca8fc1093cd95b1c6fc0


texts: What does it mean when a table is listed with fragmentation?

texts:
With this query I can list index fragmentation:

SELECT dbschemas.[name] as 'Schema',
dbtables.[name] as 'Table',
dbindexes.[name] as 'Index',
indexstats.avg_fragmentation_in_percent,
indexstats.page_count
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS indexstats
INNER JOIN sys.tables dbtables on dbtables.[object_id] = indexstats.[object_id]
INNER JOIN sys.schemas dbschemas on dbtables.[schema_id] = dbschemas.[schema_id]
INNER JOIN sys.indexes AS dbindexes ON dbindexes.[object_id] = indexstats.[object_id]
AND indexstats.index_id = dbindexes.index_id
WHERE indexstats.database_id = DB_ID()
ORDER BY indexstats.avg_fragmentation_in_percent desc

But sometimes, it's listed like this:

What does this mean? is the table fragmented?

And, while I'm here, I would like to ask another question.

I'm using a job to reorganize indexes. All of them are just being reorganized, because this database is running at full speed 24/7 and I can't use rebuild because of locks.

What is the best way to use rebuild\reorganize in this situation? is Hallengren a better solution in this case? if im not mistaken, if the index can't be locked, it will just reorganize it.

Thanks


texts:
I'm just learning about MYSQL after 6 years focusing only on sql server(dumb me).
I'm studying about how backups works there, but I can't find the mysqldump file to execute the backup. on DOS it says the command is not recognized and i cant find it to use the full patch in the command line.
is it something that should be download apart?
and also as we are here. with SQL Server we create a physical file for the backup. with MYSQL if i'm not mistaken, we create a DUMP FILE that has the "commands" to insert data and create objects again, right?


texts: Cannot create a row of size XXXX which is greater than the allowable maximum row size of 8060

texts:
I have a scenario where some inserts/updates are failing due to maximum row size of 8060 limitation. This is happening for a table having lots of varchar 250 to 500, numerics and datetime columns. I was trying to reproduce this same scenario in my test environment but unable to do so.

Do you guys have any idea how to reproduce it in Test environment? Any examples would be great for explanation.

My environment is SQL server 2005 Ent. Edition. My table has ~470 columns. I know its a bad design but its not in my control.

I tried below but it doesn't work:

create table test (col1 varchar(5000), col2 varchar(5000))
insert into test values (replicate('A', 5000), replicate('B', 5000))

Even though record length is greater than 8060, it inserts successfully.


texts: Can't create an Availability Group with Powershell when instance uses non-standard port

texts:
I am trying to create an Always On Availability Group with PowerShell, something I have done many times before but am hitting an issue now that I am using non-standard ports for the SQL Instance

I generally use a slightly modified version of this script

But I get an error saying that the current instance SERVER\INSTANCE,Port must be added to the availability group before it can be created. Now obviously that is the value I pass in at the top of the script

$SqlServerPrimName = "SERVER\INSTANCE,Port"

Which is then used to create a SMO object

$SqlServerPrim = New-Object Microsoft.SqlServer.Management.Smo.Server($SqlServerPrimName)

And then added to the Availability Group here

$AvailabilityGroup.AvailabilityReplicas.Add($PrimaryReplica)

But it obviously is not working!

I notice also that if you try and call $SQLServerPrim it comes back empty for InstanceName even though the object is correctly loaded with other values

Creating an Availability Group through the GUI works

Using the New-SQLAvailability* Cmdlets have the same error

Any idea what I need to change in the code to make this work?


texts:
We have a very large table in a SQL Server 2008 R2 Standard server which I am archiving rows by copying them into another database on a separate disk and then deleting them from the original table using an SSIS data flow. The table has a bigint Primary Key and rows are being deleted in numerical order of this key. As I am deleting rows, however, the overall Index size of the table is steadily increasing, and I cannot figure out why. The data size is remaining the same throughout this process.

Here are the table details:

Row Count: ~300,000,000
Data Size: ~65 GB
Index Size: ~65 GB

Rows are being deleted out at a rate of ~350,000 per hour.

The column definitions use the following data types: smallint, int, bigint, char, varchar, nvarchar, uniqueidentifier, bit, datetime

The table has one Primary Key which is the Clustered key on the table as well as 4 non-clustered indexes.

The table is also part of a replication maintenance plan. The replicated copy of the table is decreasing in both Data Size and Index Size!

I performed the same process on another table last week and I could see both the Data Size and Index Size of that table decreasing as I deleted rows.

Is there any explanation as to why the Index Size keeps going up in this case?


texts: Is a “‘Junction’ Table” the same as a Weak Entity?

texts: Is a “‘Junction’-Table” basically just the table that sits between two entities that have a many-to-many (M-M) relationship?


texts: I've been given the task of finding which stored procs are being called in our database.  To that end I started querying sys.dm_exec_cached_plans.  All is well and good, until I go over to our instance that has a read only copy of our DB and a read only secondary in our AG.  It looks to me like the DMV does not get updated when procs are executed.  Makes sense I suppose, but it there another way?  I can go the extended event route if I have to, but the DMVs are so much easier.


texts:
If theres something I can't learn, is PIVOT.

People are trying to teach me this, I'm reading blogs about this but I just can't understand the logic.

How can I turn this query , into a pivot?

The query:

select
    case datename(weekday,Vit_DataAberturaReal)
        when 'Monday' then 'Segunda-Feira'
        when 'Tuesday' then 'Terça-Feira'
        when 'wednesday' then 'Quarta-Feira'
        when 'Thursday' then 'Quinta-Feira'
        when 'Friday' then 'Sexta-Feira'
        when 'Saturday' then 'Sábado'
        when 'Sunday' then 'Domingo' end as 'Dia_Da_Semana',
    case datepart(hour,Vit_DataAberturaReal)
        when '0' then '00:00'
        when '1' then '01:00'
        when '2' then '02:00'
        when '3' then '03:00'
        when '4' then '04:00'
        when '5' then '05:00'
        when '6' then '06:00'
        when '7' then '07:00'
        when '8' then '08:00'
        when '9' then '09:00'
        when '10' then '10:00'
        when '11' then '11:00'
        when '12' then '12:00'
        when '13' then '13:00'
        when '14' then '14:00'
        when '15' then '15:00'
        when '16' then '16:00'
        when '17' then '17:00'
        when '18' then '18:00'
        when '19' then '19:00'
        when '20' then '20:00'
        when '21' then '21:00'
        when '22' then '22:00'
        when '23' then '23:00'
        when '24' then '24:00'  end as Hora,
    count(Vit_DataAberturaReal) as Count_Reps_Abertas
from tb_Vitima
where Vit_DataAberturaReal between '2016-01-03 00:00:00.000' and '2016-01-16 23:59:59.000'
group by datename(weekday,Vit_DataAberturaReal),datepart(hour,Vit_DataAberturaReal)
order by 1,2

This is the result:

And this is what I want:


texts: What is contained in the controlfile of oracle database?

texts: As we all know that controlfile contains datafile locations and redo log locations along with their names.It also contains dbname,timestamp,checkpoint info,log sequence number,rman backup info.In addition to this,what more a controlfile contains? Does it contain user backup info?


texts: UNIQUE constraint encompassing all the columns of a table - is it bad practice?

texts:
Is it considered bad practice to have a UNIQUE constraint applying to all columns on a table, essentially enforcing unique rows?

My use case is to store tokens from textual message from various sources, tracking the ID of each message. There can be many tokens per message, and IDs may clash from various sources.

I want to DO NOTHING in the case of reading the same message multiple times, rather than removing duplicates after the fact. I also want to be able to quickly access the tokens for any particular message, hence indexing on (source,id). Is something like the below a good design for this purpose?

source | id | token
---------------------
x      | 1  | token_1
x      | 1  | token_2
y      | 1  | token_3
y      | 2  | token_1

idx (source, id)
pk (source, id, token)


texts: MongoDB mongos router and microservices architecture

texts:
Any mongos process in your infrastructure can perform chunks migration while they hold the lock.

In a microservice architecture, processes are started and stopped more dynamically, i'm thinking for example in Kubernetes where you will restart Pods possibly frequently because there is no disruption.

How does that fit with killing mongos processes? If they are doing a chunk migration, what are the implications of that? In my cluster i see some 'Aborted' migrations (in sh.status()) and i believe they are cause by this.

How bad are those 'Aborted' migrations and how bad is killing mongos?


texts: How to retrieve all data from table if column value providing null value?

texts:
How to write a query:

If country = '    ' I need to display all data in table.
If country='algeria', it will display the particular data.
I need both queries in a single query.

I am getting result, but without providing variable how to write query...

declare @country1 varchar(30)
set @country1 = 'asd'

SELECT country_id, country
FROM   country
WHERE
(
    (@country1  = '')
    OR
    (@country1 != '' AND country.country= @country1)
);

with out  taking any  extra variable i need to  write query.
there@country1


texts: Grant create permission on a specific schema in Oracle 11g

texts: I have two users A and B. I want to grant B the permission to create, drop, etc. all tables in A's schema. As far as I can see, I can grant B full access to all schemas not a specific one. Is this correct?