texts: Very slow queries selecting @@ session variables.

texts:
We're running locally managed databases for our Atlassian suite (Jira/Confluence/Fisheye).   I noticed something strange in the slow query logs.  There are a bunch of selects of session variables showing up like

# Schema: fisheyedb  Last_errno: 0  Killed: 0
# Query_time: 2.487396  Lock_time: 0.000000  Rows_sent: 1  Rows_examined: 0  Rows_affected: 0
# Bytes_sent: 90  Tmp_tables: 0  Tmp_disk_tables: 0  Tmp_table_sizes: 0
# QC_Hit: No  Full_scan: No  Full_join: No  Tmp_table: No  Tmp_table_on_disk: No
# Filesort: No  Filesort_on_disk: No  Merge_passes: 0
# No InnoDB statistics available for this query
use fisheyedb;
SET timestamp=1511794875;
SELECT @@session.tx_isolation;

We have the slow query time threshold set to 2 seconds.

They're so prevelant that they're the number 1 entry in our daily pt-query -digest archives

# Query 1: 0.08 QPS, 0.22x concurrency, ID 0x30AAEC10BC3D0C37 at byte 7953939
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.33
# Time range: 2017-11-27 09:01:15 to 2017-11-28 00:11:48
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count         25    4120
# Exec time     19  11926s      2s     14s      3s      5s   972ms      3s
# Lock time      0       0       0       0       0       0       0       0
# Rows sent      0   4.02k       1       1       1       1       0       1
# Rows examine   0       0       0       0       0       0       0       0
# Rows affecte   0       0       0       0       0       0       0       0
# Bytes sent     0 362.12k      90      91   90.00   88.31       0   88.31
# Merge passes   0       0       0       0       0       0       0       0
# Tmp tables     0       0       0       0       0       0       0       0
# Tmp disk tbl   0       0       0       0       0       0       0       0
# Tmp tbl size   0       0       0       0       0       0       0       0
# Query size     2 116.68k      29      29      29      29       0      29
# String:
# Databases    fisheyedb (4105/99%), confluence... (15/0%)
# Hosts        x.x.x.x
# Last errno   0
# Users        fisheyeuse... (4105/99%), confluence... (15/0%)
# Query_time distribution
#   1us
#  10us
# 100us
#   1ms
#  10ms
# 100ms
#    1s  ################################################################
#  10s+  #
# EXPLAIN /*!50100 PARTITIONS*/
SELECT @@session.tx_isolation\G

What can cause reading a simple session variable like tx_isolation to be anything less than instantaneous?


texts: Determine the name of the column with a null value nearest the last column

texts:
I have five columns in a mysql table

-id
-field_1
-field_2
-field_3
-field_4
-field_5

Field_5 is the last column of the table i have named transactions. If i have a select statement like
select * from transactions where id = 4,how would i know the name of the column nearest the column field_5 that is null.


texts: join and select multi values for each row

texts:
i have 2 tables, users and skills
in skills i save skill ids and each user can have multiple skills

now i want to select all users with each user's all skill ids

but with join i cant, and with sub query i get

  Cardinality violation: 1242 Sub query returns more than 1 row

how should query be written ?


texts: Autoincrement vs composite primary key for a big table on innodb

texts:
I have a had a quite lengthy discussion with Rick James on this this we came out with idea of having composite key to replace the autoincrement pk where the int is limited close 2 billion. My table will reach this limit in few months easily as monthly we are capturing close to few hundred million data. Below is how my table looks like. The key table is the gdata so I composite the primary using 3 fields PRIMARY KEY (alarmTypeID,vehicleID,gDateTime). Then I have another table called alarm table. The link between both is one to many. Meaning one data in gdata can have zero or more alarms related to it. The link between them is vehicleID and gDateTime.

CREATE TABLE `gdata` (
    `alarmTypeID` tinyint(4) NOT NULL DEFAULT '0',
    `fleetID` smallint(11) NOT NULL,
    `fleetGroupID` smallint(11) DEFAULT NULL,
    `fleetSubGroupID` smallint(11) DEFAULT NULL,
    `deviceID` mediumint(11) NOT NULL,
    `vehicleID` mediumint(11) NOT NULL,
    `gDateTime` datetime NOT NULL,
    `insertDateTime` datetime NOT NULL,
    `latitude` float NOT NULL,
    `longitude` float NOT NULL,
    `speed` smallint(11) NOT NULL
     -- (see full text)
) ;
ALTER TABLE `gdata`
    ADD PRIMARY KEY (`alarmTypeID`,`vehicleID`,`gDateTime`),
    ADD KEY `gDateTime` (`gDateTime`),
    ADD KEY `fleetID` (`fleetID`,`vehicleID`,`gDateTime`);
COMMIT;

Here is the alarm table

CREATE TABLE `alarm` (
    `alarmTypeID` tinyint(4) NOT NULL,
    `vehicleID` mediumint(9) NOT NULL,
    `gDateTime` datetime NOT NULL,
    `insertDateTime` datetime NOT NULL,
    `alarmValue` varchar(5) NOT NULL,
    `readWeb` enum('n','y') NOT NULL DEFAULT 'n',
    `readWebDateTime` datetime NOT NULL,
    `readMobile` enum('n','y') NOT NULL DEFAULT 'n',
    `readMobileDateTim` datetime NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

ALTER TABLE `alarm`
    ADD PRIMARY KEY (`alarmTypeID`,`vehicleID`,`gDateTime`);
COMMIT;

All looks good but recently I was doing some googling on related topic and found that some discussion https://www.quora.com/Is-it-a-bad-idea-to-have-a-primary-key-on-3-or-more-columns are against composite primary key and would prefer to go with autoincrement mainly for insert purpose. Can some one shed more light on this to maintain composite key of primary or move back to autoincrement?


texts: Postgres Notify via trigger function that SELECTs the result of window function using new row?

texts:
I have a query that runs a select that operates on a range of rows to find and return the max, min, etc., e.g.:

SELECT
    time,
    device_id,
    MAX(high) OVER(rows between 4 preceding and current row) as window_max
    FROM device_measurements
    WHERE device_id=1234;

I want to set this query up in a trigger function that returns the MAX over the newly inserted row, NEW, and the 4 previous rows.

So far, I can create a trigger that calls a function which calls pg_notify and uses row_to_json(NEW)::text to simply return the new row.

Can I have a listen/notify setup that effectively pushes the most recent result of that query / what's usually done in these cases? Just get the notification and re-run the query separately?


texts:
I was using MySQL query Browser in my system and type the IP address of the remote Server and log in. I tried to create a store procedure and scheduled an event to call the procedure every two minutes. I got the error "Access denied for user 'root'@'%'". To solve this error I tried the below queries.

GRANT ALL PRIVILEGES ON database.* TO 'root'@'%' IDENTIFIED BY 'PWD';
FLUSH PRIVILEGES;
SHOW GRANTS FOR 'root'@'%
select * from mysql.user where User='root';

Now after executing these queries the error was solved and the event scheduler worked.

But now I am unable to login using the query browser in my system. I am getting another error when I try to login using the query browser from my system.

The error I am getting when I try to login using the query browser from my system is this --> "Could not connect to the specific instance. MySQL error number 1045. Access denied for user 'root'@'my public ip' (using password:YES)". How can I solve this error?


texts: How to handle Schedules when moving Jobs to another server?

texts:
We are migrating our SQL Server 2008 R2 to another physical server. For this, I will script out all jobs and run it on the target server. My question is about handling the schedules:
When a job is scripted out, there is a parameter called @schedule_uid of type uniqueidentifier for the step sp_add_jobschedule. Should I include or remove this parameter from the script? Is this value specific to the original server and therefore should not be included? If so, how can I create the matching one on the target server?
Thank you.


texts: Sorry folks if this sounds obvious. But does index seek always work on index pages not data pages? If so, which I think it is, it can only be used on its own if the output list are the same as index fields/covering . As a result, if a non-index field is needed for the output , it is neither not used by a query engine at all or used with Key/RId lookup which does not have a good performance. So I can conclude that index seek is only beneficial if the data is already covered by the index. is that correct?


texts: What's the recommended way to handle naming collisions with the PK column?

texts:
I have a data field "Shipment ID" which is a VARCHAR(18) from a number of different vendors. This is going into a table shipment with a surrogate PK shipment_id.

I'm trying to figure out the best way to name this column. Our conventions say:

Only PKs or FKs can end in _id.
PKs are tablename_id. So a table hello_world would have a PK of hello_world_id.
table and columns are lowercase_with_underscores
nothing about name collisions (if I get a good answer here, I'll make sure it's added to the style guide.)

I know it doesn't follow this awesome answer, but that's what we've got

I can't use the "Shipment ID" field as a PK because it's not guaranteed to be unique or even exist. Plus it is a string and I am not a fan of string PKs.

I don't really like shipment_name or vendor_shipment_id. What do you guys use in cases like this?


texts: MongoDB replica set - Error in heartbeat request

texts:
I have created mongo db replica set in my local as below:

    mongod --port 27021 --dbpath /cm1/mongodb/rs0-1 --replSet rs0 --smallfiles --oplogSize 128.

I have started all 3 (1 Primary, 2 secondary) instances with mongo --port 27022.

Replica set is working and data is flowing from Primary to secondaries.

If I shutdown one of the mongodb instances the remaining 2 instances are throwing errors.
Logs as below:

2017-07-25T13:01:24.904+0530 I REPL     [ReplicationExecutor] Error in heartbeat request to localhost:27021; HostUnreachable: Connection refused
2017-07-25T13:01:24.904+0530 I ASIO     [NetworkInterfaceASIO-Replication-0] Connecting to localhost:27021
2017-07-25T13:01:24.906+0530 I ASIO     [NetworkInterfaceASIO-Replication-0] Failed to connect to localhost:27021 - HostUnreachable: Connection refused
2017-07-25T13:01:24.906+0530 I ASIO     [NetworkInterfaceASIO-Replication-0] Dropping all pooled connections to localhost:27021 due to failed operation on a connection
2017-07-25T13:01:24.906+0530 I REPL     [ReplicationExecutor] Error in heartbeat request to localhost:27021; HostUnreachable: Connection refused
2017-07-25T13:01:24.906+0530 I ASIO     [NetworkInterfaceASIO-Replication-0] Connecting to localhost:27021
2017-07-25T13:01:24.907+0530 I ASIO     [NetworkInterfaceASIO-Replication-0] Failed to connect to localhost:27021 - HostUnreachable: Connection refused
2017-07-25T13:01:24.907+0530 I ASIO     [NetworkInterfaceASIO-Replication-0] Dropping all pooled connections to localhost:27021 due to failed operation on a connection
2017-07-25T13:01:24.907+0530 I REPL     [ReplicationExecutor] Error in heartbeat request to localhost:27021; HostUnreachable: Connection refused
2017-07-25T13:01:26.908+0530 I ASIO     [NetworkInterfaceASIO-Replication-0] Connecting to localhost:27021
2017-07-25T13:01:26.910+0530 I ASIO     [NetworkInterfaceASIO-Replication-0] Failed to connect to localhost:27021 - HostUnreachable: Connection refused
2017-07-25T13:01:26.910+0530 I ASIO     [NetworkInterfaceASIO-Replication-0] Dropping all pooled connections to localhost:27021 due to failed operation on a connection
2017-07-25T13:01:26.910+0530 I REPL     [ReplicationExecutor] Error in heartbeat request to localhost:27021; HostUnreachable: Connection refused
2017-07-25T13:01:26.910+0530 I ASIO     [NetworkInterfaceASIO-Replication-0] Connecting to localhost:27021
2017-07-25T13:01:26.911+0530 I ASIO     [NetworkInterfaceASIO-Replication-0] Failed to connect to localhost:27021 - HostUnreachable: Connection refused

Is there something missing  for replica while shutdown the instance?


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

texts:
I have a multi-subnet AlwaysON AG with one primary and one secondary replica in synchronous commit mode.  Due to hardware maintenance the IP address of the secondary node will change.

Is there any actions I should take or anything I should be aware of from a SQL Server, AlwaysON Availability Groups and WSFC perspective?


texts: Identical(?) SQL Server 2005 machines; query takes 2sec on one, 15min on the other

texts:
The Environment:

We have two 32-bit Windows Server 2003 R2 machines running SQL Server 2005.
The hardware configurations are identical servers with Xeon 5160 CPU, 4GB RAM, and 13GB RAID0. AWE and /3GB flags are not enabled.

The servers were set up side-by-side using a predefined installation checklist, and ALL installed software is the same on both machines.

Every SQL server installation setting and patch level we know to check are identical.
One difference is that TEMPDB is 400MB on the fast machine, and 1.2GB on the slow machine. However, in both cases, we don't see any TEMPDB allocation taking place.

The Problem:

There is a stored procedure which runs in 2 seconds on one, but 15 minutes on the other.
During the additional 15 minutes, there is little to no disk activity, no memory usage changes, but one CPU core is pinned at 100% the entire time.

This behaviour persists even when the databases are backed up from one and restored to the other.

Since it is a stored procedure, the activity monitor and profiler don't show us any detail about where in the stored procedure this high CPU activity is taking place.

The Question:

What else should we be looking at?

Followup:

The slowness occurs in the FETCH NEXT statements for the following cursor definition:

DECLARE C CURSOR FOR
    SELECT X, Y
    FROM dbo.A
    WHERE X NOT IN (SELECT X FROM dbo.B)
    AND Z <=0
...
<snip>
...
FETCH NEXT FROM C INTO @X, @Y
FETCH NEXT FROM C INTO @X, @Y
...

Each of the FETCH statements--on a table containing only about 1000 rows--requires about 7.25 minutes. (No, I don't know why it does two in a row, need to ask the developers, but it does run correctly on both servers).

I'm a little suspicious of that "NOT IN (SELECT ...)", since it looks like Virtual Reads is really high.


texts:
I have a vehicle sales store. In my store I sale many kind of vehicles for example  cars and boats. And I would like to keep track of the sales.

My vehicles has some attributes as:

Arrive on store at (a date)
Brand
Model
Color
Type (is a car or is a boat)

A Boat has some other special attributes as:

Registry
Capacity
Footspace

A car has other attributes as:

Registry
Seats

My orders has the following attributes

Vehicle (should be related with a some vehicle that is related
Date of contract
Value
Buyer ID number (yes could add a data structure for buyer data).

I never have two of the same vehicle, since each vehicle is completely        different of another one (even if it was of same brand/color/model), I'm not selling cookies.

How can I represent that in a SQL relational database?

How can a order be expressed?

In special Im trying to undertand how this work inside SQLAlchemy.


texts: What exactly mean refused in Oracle listener?

texts:
What are exactly the reasons of the listener refuse some attempt of connection in database?

Other question is, the amount of refused connection is lsnrctl services command is all refused connection since when the listener has been started?


texts: Split two delimited strings in same order without function

texts:
I am trying to split two columns with delimited strings into rows. The positions of the values in each string are related so I am trying to split it out so that the related values are in a row. I am unable to use function as I cannot create objects in the database

Here is sample table and data

CREATE TABLE #temp
(id   INT,
 keys VARCHAR(50),
 vals VARCHAR(50)
);

INSERT INTO #temp
VALUES
(1, '1,2,3', 'one,two,three'),
(2, '4,5,6', 'four,five,six'),
(3, '7,8,9', 'seven,eight,nine');

and my desired output would be

ID  key  val
1   1    one
1   2    two
1   3    three
2   4    four
2   5    five
2   6    six
3   7    seven
3   8    eight
3   9    nine

I got the query to work if I only split one column, so I define two CTEs with row_number and join on ID and row_number. This does give desired output but my live table is very large and I was hoping for a way to pass through the table only once, instead of twice.

with keys as(
SELECT id,keys,vals,
       keys.keyid.value('.', 'VARCHAR(8000)') AS keyid,
      row_number() over(order by (select null)) as rn
FROM
(SELECT id,keys,vals,
           CAST('<Keys><key>'+REPLACE(keys, ',', '</key><key>')+'</key></Keys>' AS XML) AS tempkeys
    FROM #temp
) AS temp
CROSS APPLY tempkeys.nodes('/Keys/key') AS keys(keyid)),
vals as(
SELECT id,keys,vals,
       vals.val.value('.', 'VARCHAR(8000)') AS valid,
      row_number() over(order by (select null)) as rn
FROM
(SELECT id,keys,vals,
           CAST('<vals><val>'+REPLACE(vals, ',', '</val><val>')+'</val></vals>' AS XML) AS tempvals
    FROM #temp
) AS temp
CROSS APPLY tempvals.nodes('/vals/val') AS vals(val))

SELECT k.id, k.keyid, v.valid
FROM keys AS k
     INNER JOIN vals AS v
     ON k.id = v.id
        AND k.rn = v.rn;


texts: MySQL, wait_timeout, Mac OS X Mountain Lion - how do i set it?

texts:
I need to change the wait_timeout for my MySQL database, but when I set this parameter in the my.cnf class (either in /etc or in /private/etc, because one of them is a link to the other), I still get the same default value when I type show variables in MySQL.

How do I change that value on Mac OS X then?

UPDATE
I tried setting the value using mysql console, but it didn't help:

mysql> set global wait_timeout = 333;
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like '%timeout%';
+----------------------------+----------+
| Variable_name              | Value    |
+----------------------------+----------+
| connect_timeout            | 10       |
| delayed_insert_timeout     | 300      |
| innodb_lock_wait_timeout   | 50       |
| innodb_rollback_on_timeout | OFF      |
| interactive_timeout        | 28800    |
| lock_wait_timeout          | 31536000 |
| net_read_timeout           | 30       |
| net_write_timeout          | 60       |
| slave_net_timeout          | 3600     |
| wait_timeout               | 28800    |
+----------------------------+----------+
10 rows in set (0.00 sec)

It did work without the word "global", though, but that affects only the current connection.


texts:
I'm using MATLAB to feed the database but after a time I'm getting a too many connections error. I always using opendb and closedb in pair. Is it to fast the feeding? Do I have to wait after I inserted a row?

Connecting to  host = localhost  user = root  password = ***
Uptime: 1591  Threads: 149  Questions: 2956294  Slow queries: 0  Opens: 196391  Flush tables: 1  Open tables: 81  Queries per second avg: 1858.135
Current database is "forwind"
Connection closed, current status:
No connections open
add row
Connecting to  host = localhost  user = root  password = ****
Uptime: 1591  Threads: 150  Questions: 2956402  Slow queries: 0  Opens: 196491  Flush tables: 1  Open tables: 75  Queries per second avg: 1858.203
Current database is "forwind"
Connection closed, current status:
No connections open
add row


texts: Question regarding flow control of SQL based jobs in SSIS

texts:
Question: I have pertaining to SQL and SSIS :

How do I make an SSIS package work according to a logical flow that I mapped out for it?

Say there are six steps, I have the logic for how they should all run.

So for example, if A fails, do b, regardless of failure of b, do C, etc. But it is becoming a huge pain in the back trying to implement this in SSIS.

The conventional precedence constraints are too simple and 'linear' for this sort of thing. So, I thought maybe I could create a separate table that stores the values of 'flags' that I will set to 1 or 0 and these flags will correspond to the outcome of each step.

I will keep two sets of 6 values in a table, one for last run of my package and one for current run, then make comparisons to ensure consistency of data across multiple tables in multiple runs (accounting for any failure of any step this way) but I have no clue how to implement this right now.


texts: How should I be using Users and Access Roles in MongoDB?

texts:
The documents are confusing me a little bit and I'm not finding any answers on here or google. I'm using my own auth via passport-local-mongoose, so I don't need to use mongo's auth at all.

The docs say that I should create a new user for each user/application. Does this mean I create a user for each entry point, ie: one for mobile app, one for web service api, and then an admin for management?

Or does this mean I need to make a new user for every single person who signs up on my site/app?

Every interaction with entry points (other than user registration) requires auth already, so creating a separate mongo user for every registered user seems redundant, however this is my first database so I'm not sure how things work as far as logging or security best practices.

It is worth noting that I am currently storing users as collections with embedded documents for their data for easy lookup per user.


texts:
My system needs to store an append only log of events.  Currently I have a database table that stores all the relevant data in one table:

CREATE TABLE `events` (
        `event_id` VARCHAR(255) NOT NULL PRIMARY KEY,
        `event_type` VARCHAR(255) NOT NULL,
        `event_timestamp` DATETIME,
        `group_id` VARCHAR(255),
        `person_id` VARCHAR(255),
        `client_id` VARCHAR(255),
        `name` VARCHAR(768),
        `result` VARCHAR(255),
        `status` VARCHAR(255),
        `logged_at` DATETIME,
        `severity` VARCHAR(255),
        `message` LONGTEXT,
        INDEX `event_type_index` (`event_type`),
        INDEX `event_timestamp_index` (`event_timestamp`),
        INDEX `group_id_index` (`group_id`),
        INDEX `person_id_index` (`person_id`),
        INDEX `client_id_index` (`client_id`),
        INDEX `name_index` (`name`),
        INDEX `result_index` (`result`),
        INDEX `status_index` (`status`),
        INDEX `logged_at_index` (`logged_at`),
      ) ENGINE=InnoDB DEFAULT CHARACTER SET=utf8mb4 COLLATE=utf8_general_ci

But I've noticed that queries with multiple attributes in the WHERE clause are still slow.  For example:

SELECT
  count(e.event_id) as total
FROM events e
WHERE
  e.result='Success' AND
  e.event_type='some_silly_event' AND
  e.event_timestamp > '2019-01-01 00:00:00'

One solution would be to create an index like the following:

CREATE INDEX successful_silly_events
ON events (result,event_type,event_timestamp);

The downsides of this approach seem to be that creating the index would take a long time, and would only speed up this query.  If I create a different query on this table with different columns, I'm back to square one.

Would I have been better served by splitting the events table into multiple tables from the start?  For example:

CREATE TABLE `events` (
        `event_id` VARCHAR(255) NOT NULL,
        `logged_at` DATETIME,
        `severity` VARCHAR(255),
        `message` LONGTEXT,
        PRIMARY KEY (event_id),
        INDEX `logged_at_index` (`logged_at`),
      ) ENGINE=InnoDB DEFAULT CHARACTER SET=utf8mb4 COLLATE=utf8_general_ci

CREATE TABLE `event_types` (
        `event_id` VARCHAR(255) NOT NULL,
        `event_type` VARCHAR(255) NOT NULL,
        PRIMARY KEY event_id REFERENCES events(event_id)
        INDEX `event_type_index` (`event_type`),
      ) ENGINE=InnoDB DEFAULT CHARACTER SET=utf8mb4 COLLATE=utf8_general_ci

CREATE TABLE `event_timestamps` (
        `event_id` VARCHAR(255) NOT NULL,
        `event_timestamp` VARCHAR(255) NOT NULL,
        PRIMARY KEY event_id REFERENCES events(event_id)
        INDEX `event_timestamp_index` (`event_timestamp`),
      ) ENGINE=InnoDB DEFAULT CHARACTER SET=utf8mb4 COLLATE=utf8_general_ci

CREATE TABLE `event_groups` (
        `event_id` VARCHAR(255) NOT NULL,
        `group_id` VARCHAR(255) NOT NULL,
        PRIMARY KEY event_id REFERENCES events(event_id)
        INDEX `group_id_index` (`group_id`),
      ) ENGINE=InnoDB DEFAULT CHARACTER SET=utf8mb4 COLLATE=utf8_general_ci

And so on for all the other event attributes which I would have normally indexed on the events table.  This way, I could construct a similar query:

SELECT
  count(e.event_id) as total
FROM events e
  LEFT JOIN event_results er ON e.event_id=er.event_id
  LEFT JOIN event_types ety ON e.event_id=et.event_id
  LEFT JOIN event_timestamps eti ON e.event_id=et.event_id
WHERE
  er.result='Success' AND
  ety.event_type='some_silly_event' AND
  eti.event_timestamp > '2019-01-01 00:00:00'

Would the resulting query be fast and not require full table scan?  If so, this seems like a better setup.


texts: Best Azure Service for Large no of SQLServer Databases

texts:
We have a dedicated SQL Server Machine for a SAAS application with about 200+ databases, the server has E5-2620 with 64 GB RAM, Now we want to move all the databases to the Azure, so for looking into the options we can either go with a Virtual Machine like (DS13 v2) which can support all these DBs or can use the Azure SQL Elastic Pools like Standard with 400 eDTUs.

My Question is that in the Long Run and from management and scalability point of view which option is better, and also will the Standard Elastic Pool with 400 eDTU's can provide the same performance as the Virtual Machine?

One of the major concerns for us is the performance of the SQL DBs in Azure, as this is one of the reason to move the whole application to the Azure so that in the future as the no of users grow we can easily upgrade our instances to cater the increased load.


texts:
Table_1
Name   |   Feature
A      |    F1
A      |    F1
A      |    F1
A      |    F2
A      |    F3
B      |    F1
B      |    F1
C      |    F3

Desired Result
Name |   F1  |  F2  |   F3
A    |   3   |  1   |    1
B    |   2   |  0   |    0
B    |   0   |  0   |    1

I tried below query

SELECT Name,Feature,COUNT(Feature) FeatureCount FROM Table_1 GROUP BY Feature,Name

But it is not giving as Desired
Instead it is giving

Name | Feature | FeatureCount
A    |   F1    |     3
A    |   F2    |     1
A    |   F3    |     1
B    |   F1    |     2
C    |   F3    |     1

Confuse on How to get Feature Wise Count Per User


texts: SQL Server 2014 Latest CU / Service Pack

texts:
Where can you find the latest SQL Server 2014 Cumulative Update (CU) and/or Service Pack?

Note: I added this question and answered my own question to make this easier to find for others in the future.

At the time of this question, the first several pages of Google results (when searching for "SQL Server 2014 CU") only mention CU 1-4, with no mention of the latest CU 5.  http://lmgtfy.com/?q=sql+server+2014+cu


texts: Distribute rows into groups while minimizing difference


texts:
I know the premise of a PIVOT() this syntax produces it perfectly for me:

Select *
FROM
(
  Select
  field1
  ,annual
  ,dv As Amt
  FROM Testing
) x
pivot
(
  SUM(Amt)
  ,for annual IN ([2014],[2015],[2016])
) p

However, what if I am using a stored procedure and passing a year1 and year2 to the pivot as variables?  This will not work as incorrect syntax, but could this be achieved?

Create Procedure dbo.PivotParams
As
Declare @year1 varchar(100), @year2 varchar(100)

Set @year1 = '2014'
Set @year2 = '2016'

Select *
FROM
(
  Select
  field1
  ,annual
  ,dv As Amt
  FROM Testing
) x
pivot
(
  SUM(Amt) for annual BETWEEN @year1 and @year2
) p

Also the output should use COALESCE to prevent any NULL values in any of the pivot columns.


texts:
I'm trying to use ASMCMD but it doesn't return any result in the command line

ASMCMD> ls
ASMCMD>
ASMCMD>
ASMCMD>
ASMCMD> lsdg
ASMCMD>
ASMCMD> pwd
ASMCMD>

The version of database is 19c, the only environment about oracle grid is the GRID_HOME, but I also have the ORACLE_HOME and ORACLE_BASE, currently my $ORACLE_SID is orcl and my $ORACLE_HOME is /u01/app/oracle/product/19.3.0/dbhome_1.

My output about srvctl config asm is:

ASM home: <CRS home>
Password file: +DATA/orapwasm
Backup of Password file:
ASM listener: LISTENER,LISTENER2
Spfile: +DATA/ASM/ASMPARAMETERFILE/registry.253.1011650339
ASM diskgroup discovery string: /dev/oracleasm/disks/*


texts: Bizarre density results in sampled statistics

texts:
A NC-index gets a totally different statistical distribution when estimated with sampling vs fullscan; the sampled one having a bizarre density vector. This results in poor execution plans.

I have a table of ~27M rows, with a non null FK-column supported by a nonclustered index. The table is clustered on its primary key. Both columns are varchar.

A fullscan statistics update for our FK-column gives a normal looking density vector:

All density Average Length  Columns
6,181983E-08    45,99747    INSTANCEELEMENTID
3,615442E-08    95,26874    INSTANCEELEMENTID, ID

That is, we're expected to read about 1.7 rows for each distinct INSTANCELEMENTID we are joining with.

A typical bin from the histogram looks like this:

RANGE_HI_KEY    RANGE_ROWS  EQ_ROWS DISTINCT_RANGE_ROWS AVG_RANGE_ROWS
FOOBAR          133053      10      71366               1,679318

However, if we do a sampled update (using the default sample number which is 230k rows for this table) things take a turn to the bizarre:

4,773657E-06    45,99596    INSTANCEELEMENTID
3,702179E-08    95,30183    INSTANCEELEMENTID, ID

The density on INSTANCEELEMENTID is now two orders of magnitude larger. (The density for both columns however has been estimated to a quite acceptable value).

A typical bin from the histogram now looks like this;

RANGE_HI_KEY    RANGE_ROWS  EQ_ROWS     DISTINCT_RANGE_ROWS AVG_RANGE_ROWS
FOOBAR          143870,4    766,2573    1247                115,3596
ZOTZOT          131560,7    1           969                 135,7092

which is a completely different distribution. Note that the INSTANCEELEMENTID with the highest number of associated IDs has 12, the most common number is 1. It's also very strange that some bins get EQ_ROWS = 1, this happens to about 10% of the bins.

There is no "unlucky" draw of strange rows that could contribute to this.

Am I reading the histogram correctly? Doesn't it look like the sampling has somehow scaled EQ_ROWS, DISTINCT_RANGE_ROWS and AVG_RANGE_ROWS incorrectly?

The table is, as far as I can tell, unskewed. I've tried to emulate the sampler by estimating values myself with tablesample. Counting these results in a normal way gives results that agree with the fullscan version, not the sampler.

Furthermore, I've not been able to reproduce this behaviour on clustered indices.

I've narrowed this down to this to reproduce:

CREATE TABLE F_VAL (
    id varchar(100) primary key,
    num_l_val int not null
)

set nocount on

declare @rowlimit integer = 20000000;

The table must be sufficiently large for this to be observed. I've seen this with uniqueidentifer and varchar(100) but not int.

declare @i integer = 1;

declare @r float = rand()

while @i < @rowlimit
begin
set @r = rand()
insert f_val (id,num_l_val)
values (
   cast(@i as varchar(100)) + REPLICATE('f', 40 - len(@i)),
   case when @r > 0.8 then 4 when @r > 0.5 then 3 when @r > 0.4 then 2 else 1 end
)
  set @i = @i + 1

end

create table k_val (
 id int identity primary key,
 f_val varchar(100) not null,
)

insert into k_val(f_val)
select id from F_VAL
union all select id from f_val where num_l_val - 1 = 1
union all select id from f_val where num_l_val - 2 = 1
union all select id from f_val where num_l_val - 3 = 1
order by id

create nonclustered index IX_K_VAL_F_VAL  ON K_VAL (F_VAL)

update statistics K_VAL(IX_K_VAL_F_VAL)
dbcc show_statistics (k_val,IX_k_VAL_F_VAL)

update statistics K_VAL(IX_K_VAL_F_VAL) WITH FULLSCAN
dbcc show_statistics (k_val,IX_k_VAL_F_VAL)

Compare the two statistics; the one with sampling is now representing a total different density vector and the histogram bins are off. Note that the table is not skewed.

Using int as datatype does not cause this, does SQL Server not examine the entire datapoint when using varchar?

It's worthwhile to mention that the problem seems to scale, increasing sample rate helps.


texts:
I have a table which gives the output when queried normally. I want to transform rows to columns and columns to rows to show it in a report.

I want the tranformation to be applied as shown in the below image.

Other than BRk, CLR, BIN and SDC, they need to be categorized under Others in the above desired table.

Below is the table's DDL and DML.

  SET ANSI_NULLS ON
  GO

  SET QUOTED_IDENTIFIER ON
  GO

  CREATE TABLE [dbo].[DtlChrg](
  [ID] [uniqueidentifier] NOT NULL,
  [ChrgCode] [nvarchar](10) NULL,
  [PromotionInd] [nvarchar](1) NULL,
  [ChrgAmt] [decimal](15, 6) NULL

  CONSTRAINT [PK_DtlChrg] PRIMARY KEY CLUSTERED
  (
  [ID] ASC
  )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,
  IGNORE_DUP_KEY =OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
  ON [PRIMARY]) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

  GO

   INSERT INTO [dbo].DtlChrg([ID],[ChrgCode],
   [PromotionInd],[ChrgAmt])VALUES(NEWID(),'BRK','Y','100')
   INSERT INTO [dbo].DtlChrg([ID],[ChrgCode],
   [PromotionInd],[ChrgAmt])VALUES(NEWID(),'BRK','N','200')
   INSERT INTO [dbo].DtlChrg([ID],[ChrgCode],
   [PromotionInd],[ChrgAmt])VALUES(NEWID(),'BRK','N','55')
   INSERT INTO [dbo].DtlChrg([ID],[ChrgCode],
   [PromotionInd],[ChrgAmt])VALUES(NEWID(),'CLR','Y','140')
   INSERT INTO [dbo].DtlChrg([ID],[ChrgCode],
   [PromotionInd],[ChrgAmt])VALUES(NEWID(),'CLR','N','250')
   INSERT INTO [dbo].DtlChrg([ID],[ChrgCode],
   [PromotionInd],[ChrgAmt])VALUES(NEWID(),'CLR','N','5')
   INSERT INTO [dbo].DtlChrg([ID],[ChrgCode],
   [PromotionInd],[ChrgAmt])VALUES(NEWID(),'SDC','Y','100')
   INSERT INTO [dbo].DtlChrg([ID],[ChrgCode],
   [PromotionInd],[ChrgAmt])VALUES(NEWID(),'SDC','N','300')
   INSERT INTO [dbo].DtlChrg([ID],[ChrgCode],
   [PromotionInd],[ChrgAmt])VALUES(NEWID(),'SDC','N','552')
   INSERT INTO [dbo].DtlChrg([ID],[ChrgCode],
   [PromotionInd],[ChrgAmt])VALUES(NEWID(),'BIN','Y','500')
   INSERT INTO [dbo].DtlChrg([ID],[ChrgCode],
   [PromotionInd],[ChrgAmt])VALUES(NEWID(),'BIN','N','20')
   INSERT INTO [dbo].DtlChrg([ID],[ChrgCode],
   [PromotionInd],[ChrgAmt])VALUES(NEWID(),'BIN','N','25')
   INSERT INTO [dbo].DtlChrg([ID],[ChrgCode],
   [PromotionInd],[ChrgAmt])VALUES(NEWID(),'SLC','Y','540')
   INSERT INTO [dbo].DtlChrg([ID],[ChrgCode],
   [PromotionInd],[ChrgAmt])VALUES(NEWID(),'SLC','N','220')
   INSERT INTO [dbo].DtlChrg([ID],[ChrgCode],
   [PromotionInd],[ChrgAmt])VALUES(NEWID(),'SLC','N','245')

Thanks..


texts:
We have an AD group as a Login in several servers, usually as sysadmin,
but I've found one server that this login has no access to any DB and has a server public role only.
I've talked to a member of this group about this issue,
and he told me that he has an access to this Server's DBs.
And endeed by opening SSMS as different user(using his AD credentials) at my local station,
he could select, update tables in this server, even shrinking a log file(but cannot see logins except sa and this group).
I cannot find a source for those permissions:
There is no Login\User with his credentials,
there are other AD groups, but if I revoke connect from this group then
he cannot longer connect to this server.
Public server role has only view any database permission
Public DB role has no permissions on dbo schema
Database is not in partial containment....
Where else can I search for a source of this Login's permissions?
*SQL Server 2012 on my and server's computers.

I used this query to fing this login

   USE [master]
GO

DECLARE @username sysname
DECLARE @objname sysname
DECLARE @found integer
DECLARE @sql nvarchar(4000)
DECLARE @results TABLE (Login sysname)

SET @username = ' '
WHILE @username IS NOT NULL
BEGIN
SELECT @username = MIN(name)
FROM master.dbo.syslogins WITH (NOLOCK)
WHERE sysadmin = 0
AND securityadmin = 0
AND serveradmin = 0
AND setupadmin = 0
AND processadmin = 0
AND diskadmin = 0
AND dbcreator = 0
AND bulkadmin = 0
AND name > @username
AND Name NOT LIKE N'NT %'
AND Name NOT LIKE N'##%'
-- this is the list of non system logins
-- ids in server roles may not have corresponding users
-- any database but they should not be removed
SET  @found = 0

IF @username IS NOT NULL
    BEGIN
    --  now we search through each non system database
    --  to see if this login has database access
    SET @objname = ''
    WHILE @objname IS NOT NULL
    BEGIN
        SELECT @objname = MIN( name )
        FROM master.dbo.sysdatabases WITH (NOLOCK)
        WHERE
        name > @objname
        AND DATABASEPROPERTYEX(name, 'status') = 'ONLINE'

        IF @objname IS NOT NULL
        BEGIN
            SET @sql = N'SELECT @found = COUNT(*) FROM [' + @objname
            + N'].dbo.sysusers s WITH (NOLOCK) JOIN master.dbo.syslogins x WITH (NOLOCK)
            ON s.sid = x.sid WHERE hasdbaccess = 1 AND x.name = '''+ @username + ''''
            EXEC sp_executesql @sql,N'@found Int OUTPUT',@found OUTPUT
            --SELECT @found, @objname, @username
            IF @found IS NOT NULL AND @found > 0
                SET @objname = 'zzzzz'  -- terminate as a corresponding user has been found
        END
    END

    IF @found = 0
    BEGIN
    INSERT INTO @results
    SELECT @username
    END
END
END

SELECT SERVERPROPERTY('ServerName') ServerName ,Login
FROM @results r
ORDER BY Login
GO


texts:
I have this trigger (audit trigger, in every table I want to audit).
CREATE FUNCTION changelog_trigger() RETURNS trigger AS $$
    BEGIN
        IF TG_OP = 'INSERT'
            THEN INSERT INTO "changeLog" (
                "tableName", operation, after, "userId"
                ) VALUES (
                TG_TABLE_NAME, TG_OP, row_to_json(NEW), cast(row_to_json(NEW)::json ->> 'userId' as numeric)
                );
            RETURN NEW;
        ELSIF TG_OP = 'UPDATE'
            THEN INSERT INTO "changeLog" (
                "tableName", operation, before, after, "userId"
                ) VALUES (
                TG_TABLE_NAME,  TG_OP, row_to_json(OLD), row_to_json(NEW), cast(row_to_json(NEW)::json ->> 'userId' as numeric)
                    );
            RETURN NEW;
        ELSIF TG_OP = 'DELETE'
            THEN INSERT INTO "changeLog" (
                "tableName", operation, before, "userId"
                ) VALUES (
                TG_TABLE_NAME, TG_OP, row_to_json(OLD), cast(row_to_json(NEW)::json ->> 'userId' as numeric)
                );
            RETURN OLD;
        END IF;
    END;
$$ LANGUAGE 'plpgsql' SECURITY DEFINER;

CREATE TRIGGER t BEFORE INSERT OR UPDATE OR DELETE ON "Cities" FOR EACH ROW EXECUTE PROCEDURE changelog_trigger();
CREATE TRIGGER t BEFORE INSERT OR UPDATE OR DELETE ON "Buildings" FOR EACH ROW EXECUTE PROCEDURE changelog_trigger();
CREATE TRIGGER t BEFORE INSERT OR UPDATE OR DELETE ON "Areas" FOR EACH ROW EXECUTE PROCEDURE changelog_trigger();
CREATE TRIGGER t BEFORE INSERT OR UPDATE OR DELETE ON "Rooms" FOR EACH ROW EXECUTE PROCEDURE changelog_trigger();
CREATE TRIGGER t BEFORE INSERT OR UPDATE OR DELETE ON "Flags" FOR EACH ROW EXECUTE PROCEDURE changelog_trigger();
CREATE TRIGGER t BEFORE INSERT OR UPDATE OR DELETE ON "Details" FOR EACH ROW EXECUTE PROCEDURE changelog_trigger();
CREATE TRIGGER t BEFORE INSERT OR UPDATE OR DELETE ON "Files" FOR EACH ROW EXECUTE PROCEDURE changelog_trigger();
CREATE TRIGGER t BEFORE INSERT OR UPDATE OR DELETE ON "DetailFiles" FOR EACH ROW EXECUTE PROCEDURE changelog_trigger();
CREATE TRIGGER t BEFORE INSERT OR UPDATE OR DELETE ON "Users" FOR EACH ROW EXECUTE PROCEDURE changelog_trigger();
CREATE TRIGGER t BEFORE INSERT OR UPDATE OR DELETE ON "Infrastructures" FOR EACH ROW EXECUTE PROCEDURE changelog_trigger();
CREATE TRIGGER t BEFORE INSERT OR UPDATE OR DELETE ON "Builds" FOR EACH ROW EXECUTE PROCEDURE changelog_trigger();
CREATE TRIGGER t BEFORE INSERT OR UPDATE OR DELETE ON "DB_Backups" FOR EACH ROW EXECUTE PROCEDURE changelog_trigger();

This works perfectly, at least until now. Client wants to also update a record which states the last time anything was updated. I thought about adding a second insert after THEN. Is this the best approach? I need to update, in table Cities, column updatedAt, with the timestamp of the operation.
I thought about doing this but I want to be sure because this is a production server and I'm not a DBA by any stretch.
        IF TG_OP = 'INSERT'
            THEN INSERT INTO "changeLog" (
                "tableName", operation, after, "userId"
                ) VALUES (
                TG_TABLE_NAME, TG_OP, row_to_json(NEW), cast(row_to_json(NEW)::json ->> 'userId' as numeric)
                );
            UPDATE "Cities" SET "updatedAt" = current_timestamp() where id = 1;
            RETURN NEW;

Is this correct?
Thanks in advance.


texts:
Is there any way to force PostgreSQL to use an index that I created on a materialized view table? I tried every combination possible of moving the column attributes around to make Postgres to use my index...

I have this table:

=> explain select * from flight3mv1;
                         QUERY PLAN
-------------------------------------------------------------
 Seq Scan on flight3mv1  (cost=0.00..3.50 rows=150 width=26)
(1 row)

When I create my index on the materialized view, nothing changed on my query plan.

=> create index flight3mv1index on flight3mv1(c_nation,s_nation);
=> analyze flight3mv1;
=> explain select * from flight3mv1 where c_nation='CHINA' and s_nation='CHINA';

                                      QUERY PLAN
---------------------------------------------------------------------------------------
 Seq Scan on flight3mv1  (cost=0.00..4.25 rows=6 width=26)
   Filter: (((c_nation)::text = 'CHINA'::text) AND ((s_nation)::text = 'CHINA'::text))
(2 rows)

Here is my flight3mv1 table:

=> \d flight3mv1
    Materialized view "public.flight3mv1"
  Column  |         Type          | Modifiers
----------+-----------------------+-----------
 c_nation | character varying(15) |
 s_nation | character varying(15) |
 d_year   | integer               |
 revenue  | bigint                |
Indexes:
    "flight3mv1index" btree (c_nation, s_nation) CLUSTER


texts: Showing all values when at least match a given one

texts:
Given three tables items, genres and itemGenres; how can I filter out the items that have at least the given value while also outputting the rest of its genres.

With this data

INSERT INTO items (title) VALUES ('Mad Max: Fury Road'), ('The Big Lebowski');
INSERT INTO genres (genre) VALUES ('action'), ('comedy'), ('post-apocalyptic');
INSERT INTO itemGenres (itemId, genreId) VALUES (1,1), (1,3), (2,2);

and this query

SELECT
  items.*,
  GROUP_CONCAT(genres.id) as genreIds,
  GROUP_CONCAT(genres.genre) as genres FROM items
LEFT JOIN itemGenres ON
  items.id = itemGenres.itemId
LEFT JOIN genres ON
  genres.id = itemGenres.genreId
WHERE
  genres.id IN (1)
GROUP BY
  items.id

I get the following result

| id |              title | genreIds | genres |
|----|--------------------|----------|--------|
|  1 | Mad Max: Fury Road |        1 | action |

How do I get it to be this?

| id |              title | genreIds |                   genres |
|----|--------------------|----------|------------------------- |
|  1 | Mad Max: Fury Road |      1,3 | action, post-apocalyptic |

The tables are created like this

CREATE TABLE `items` (
  `id` int(1) unsigned NOT NULL AUTO_INCREMENT,
  `title` varchar(120) COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`id`)
);

CREATE TABLE `itemGenres` (
  `id` int(1) unsigned NOT NULL AUTO_INCREMENT,
  `itemId` int(1) unsigned DEFAULT NULL,
  `genreId` int(1) unsigned DEFAULT NULL,
  PRIMARY KEY (`id`)
);

CREATE TABLE `genres` (
  `id` int(1) unsigned NOT NULL AUTO_INCREMENT,
  `genre` varchar(120) COLLATE utf8_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`)
);


texts:
I am designing a content-based website capable of managing huge amounts of pages (mostly web pages; images, videos, etc would be stored on the file system). Which would be faster/more efficient/most stable:

A traditional database (eg. MySQL) with each web page stored as a row with indexes strategically located or

A key/value based NoSQL database (eg. MongoDB) where the key is like PAGE-ID_TITLE or PAGE-ID_BODY etc.

Either option could allow caching (either built-in or roll-your-own).

I do not want to directly compare MySQL and MongoDB but rather SQL vs key/value

Thanks


texts: What is the purpose of column store index on database tables, and in what situations should they be used (with OLTP)?


texts: ODBC Microsoft Access Driver: Not enough space on temporary disk

texts:
I am running spatial modelling software that uses an Access database to store data needed for the computation. Each model run, data is loaded into the model from a Microsoft Access 2010 database using an SQL query and the Jet Database Engine 4.0. The connection between the software and the database is made with ODBC.

I need to run the model for thousands of locations, but after tens of model runs I get the error: “[ODBC Microsoft Access Driver] Not enough space on temporary disk”. After restarting the model, the computation runs ok for the same query and data set, but I quickly get the error again. The amount of data that is loaded differs per model run, with a maximum of ~30.000 records. When loading in only small datasets of max. 1000 records, the error does not occur.

The following did not work:

increasing the MaxLocksPerFile in the windows registry
increasing the MaxBufferSize
Compacting and repairing the .mdb file
there is enough space on my temporary disk

Interestingly, I did not have any problems with the same model on my old system (32-bit Windows XP). Only on this computer (64-bit Windows 7, 16 Gb RAM) the error occurs. The modelling software is 32-bit, and the ODBC connection is set with odbcad32.exe in “c:\Windows\SysWOW64\” directory. It seems there is a memory leak in the Windows7 version, as the same model and data works fine on an older Windows XP computer with less memory.
Any ideas are greatly appreciated!


texts: Can see other user's databases in shared hosting

texts:
This looks like a security concern but I do not know enough about SQL Server to know.

Using a shared hosting service, if I go to restore a database I'm able to see other user's databases.

I'm not able to restore their database to mine so I cannot see any of their data, just their database names.

Is this a security concern?


texts:
I am merging two different versions of a database. I want to add some specific fields from source database to destination, so I used Visual Studio to do that. It does the job quite well but only table level, which means it checks for the table if the table is not available in the destination, it creates.

But my problem is:

In the old database in a specific table I have a Name column with NVARCHAR(50) mean while in the new database table I have same Name column with NVARCHAR(100) datatype.

Now I want to ignore this column level comparison only for this specific table field.

Visual Studio allows me to do this at the table level only, not at the column level. Can anyone tell me how it would be possible?


texts: How can I calculate future interest charges and loan balance

texts:
I have a table like below:

Need to calculate interest for next month 100*(4.29/100).
Row+1:Capital_Balance = Row:Capital_Balance_Calculated - Calculated interest from above step.

Resultant table is like below:
[![enter image description here][2]][2]

For sample data:

CREATE TABLE #Test (
     Account_Number int
    ,Interest_Rate float
    ,LastDateOfMonth Date
    ,Capital_Balance_Calculated Float
    ,Interest float
    ,Payment float

);

INSERT INTO #Test VALUES (12345,4.2,'2019-03-31',100,5,7);
INSERT INTO #Test VALUES (12345,4.2,'2019-04-30',0,NULL,7);
INSERT INTO #Test VALUES (12345,4.2,'2019-05-31',0,NULL,5);
INSERT INTO #Test VALUES (12345,4.2,'2019-06-30',0,NULL,7);
INSERT INTO #Test VALUES (12345,4.2,'2019-07-31',0,NULL,7);
INSERT INTO #Test VALUES (54321,5.1,'2019-03-31',200,6,8);
INSERT INTO #Test VALUES (54321,5.1,'2019-04-30',0,NULL,6);
INSERT INTO #Test VALUES (54321,5.1,'2019-05-31',0,NULL,8);
INSERT INTO #Test VALUES (54321,5.1,'2019-06-30',0,NULL,10);
INSERT INTO #Test VALUES (54321,5.1,'2019-07-31',0,NULL,8);

Please take a look at the calculation:


texts: INSERT into three tables with many-to-many from one table

texts:
With Postgres 9.5 using a trivial example.
Given I have this data:

Table everything:

| fruit  | country |
|--------|---------|
| Banana | USA     |
| Banana | Panama  |

How would I create these tables?:

Table fruit:

| fruit_id | name   |
|----------|--------|
| 1        | Banana |

Table country:

| country_id | name   |
|------------|--------|
| 1          | USA    |
| 2          | Panama |

Table fruit_country:

| fruit_id | country_id |
|----------|------------|
| 1        | 1          |
| 1        | 2          |

As an initial pass I hoped I could do something like this:

WITH fruit_ids AS (
  INSERT INTO fruit(name) (
    SELECT (fruit) FROM everything
  )
  ON CONFLICT DO NOTHING
  RETURNING fruit_id
),
country_ids AS (
  INSERT INTO country(name) (
    SELECT(country) FROM everything
  )
    RETURNING country_id
)
INSERT INTO country_fruit(fruit_id, country_id) (
  SELECT
  fruit_id,
  (SELECT country_id FROM country_ids)
  FROM fruit_ids
);

But no dice:

ERROR:  more than one row returned by a subquery used as an expression


texts: Filling not applicable fields with N/A rather than leaving them empty or Null

texts:
I have a database of few million records, many of the fields (call them Ec1 to EcN) have either Null or empty string in them. When I looked at them more closely, I found that for many records, several of this Ec fields are not applicable. So, there should not be any value there and hence nothing is wrong as instance issue.

However, I found several records that should have had value in these Ec fields which don't. So, here are some instance issues in data entry. So, my question is what would be the best approach to distinguish N/A fields from those user failed to enter and avoid further confusion?

One idea is to fill those Ec fields with N/A value and leave the rest with value. In this setting we won't have any NULL or '' field in the entire database. But, it might use more space (in MB scale).

What is cons and pros of using N/A?

Thanks


texts:
I have a query that is executed frequently with a few of the same conditions

EXPLAIN ANALYZE SELECT COUNT(*) FROM "comments" WHERE "comments"."published" = 't' AND "comments"."user_id" = 15 AND (comments.created_at >= '1970-01-01 00:00:00.000000') AND (comments.created_at <= '2015-12-01 02:19:42.000000');
                                                                                QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=76843.52..76843.52 rows=1 width=0) (actual time=1207.290..1207.291 rows=1 loops=1)
   ->  Seq Scan on comments  (cost=0.00..75622.68 rows=2441669 width=0) (actual time=0.039..1050.204 rows=2500169 loops=1)
         Filter: (published AND (created_at >= '1970-01-01 00:00:00'::timestamp without time zone) AND (created_at <= '2015-12-01 02:19:42'::timestamp without time zone) AND (user_id = 15))
         Rows Removed by Filter: 391534
 Total runtime: 1207.329 ms

I've added indices for the values that are included in these conditions

                                          Table "public.comments"
        Column        |            Type             |                        Modifiers
----------------------+-----------------------------+---------------------------------------------------------
 id                   | integer                     | not null default nextval('comments_id_seq'::regclass)
 published            | boolean                     | default false
 deleted              | boolean                     | default false
 user_id              | integer                     |
 created_at           | timestamp without time zone |
 updated_at           | timestamp without time zone |
Indexes:
    "post_responses_pkey" PRIMARY KEY, btree (id)
    "index_comments_on_user_id" btree (user_id)
    "index_comments_on_created_at" btree (created_at)
    "index_comments_on_published" btree (published)
    "index_comments_on_published_and_user_id" btree (published, user_id)
    "index_comments_on_published_and_user_id_and_created_at" btree (published, user_id, created_at)

But the query still seems quite slow (there are only roughly 3 million comments total). Is there something obvious I'm missing here?


texts: How do I duplicate constraints from one existing table to another existing table with an identical structure?

texts:
I'm using PostgreSQL. I have two tables, table_a and table_b. table_b is a structural copy of table_a but it was created without the constraints of table_a. At a later date, I want to add the constraints to table_b.

All the constraints are regular foreign keys to the primary key of table_a and for the purposes of this question, we can assume that the data are the same in both tables, but I still don't want to drop and recreate table_b; I want to add the constraints independent of table creation.

Is there a generic way of doing this without having to be provided details of the tables' structure or the specific constraints of table_a?


texts: nested if expressions for string in excel source file in derived column expression

texts:
I have a column named Product which contains strings.

Everything starting with "ZYT" should be shown: "ZYTOL"
and "AZ" --> "AZAB"
    "UXO" --> "UXOL"
    "ADX" --> "ADXIZN"

How could I write the expression in SSIS transforming process?


texts: Simple SELECT-query on big table gets stuck in "Sending data" state

texts:
I have a table with about ~200 million rows looking like this:

id, name, timestamp, class

I have an index on each column and I am trying to run a query like this:

SELECT name
FROM t
WHERE timestamp >= date_x AND
      timestamp < date_y AND
      class='class_a'

This query only uses the index on class and when date_x and date_y is about a month a part EXPLAIN says that it looks at about 12.5 M rows which is less than for the index on timestamp.

When the date range is small this query obviously returns fast but as the range increases this query soon gets stuck and never even starts sending any data (i.e stuck on Sending data), compared to a simple SELECT * FROM t which in SHOW PROCESS list alternates between Sending data and Writing to net.

My current workaround is to fetch rows within a daily range and then concatenate the results. For a month it should return about 150 K rows which I think should be doable for a straight-forward query such as this. That is why I think that there should be a way to do this without the workaround.

I have tried looking at the settings but have found no apparent mistakes there either. I can update this question with the relevant MySQL settings.

Do I need a multicolumn index on both class and timestamp?
Why cannot MySQL use both indexes to limit the search?
Have I missed something?

EXPLAIN output:

1   SIMPLE  table   ref index_class,index_timestamp index_class 302 const   11898928    100.00  Using where


texts: Single year column and multiple weeks columns into single year/week column with PIVOT

texts:
I have a table that looks roughly like this.

(Pk int, Year int, Week1, Week2, .., Week53)

The values in each "Week" column are sales data.  What I want to do is create a new table from the first table in the form of -

(Pk int, WeekYear, SalesData)

Where "WeekYear" is a concatentation of the year and the week number and SalesData is the same salesdata for that respective field in the former table. (just trying to get the select down for now)

SELECT pk,
   [1],
   [2],
   ...
   [1000]
 FROM (SELECT * FROM SalesTable) st
PIVOT
(
AGGREGATE(pk) -- no idea what to use here
FOR pk IN [1],
   [2],
   ...
   [1000]
 );

My current attempts make no sense as I'm not sure which aggregate function to apply on my dataset. I'm also having a bit of trouble discerning the best way to create the new column YearWeek.

This seemed like an obvious use for PIVOT (I need to transpose the table). However, I'm having a lot of trouble wrapping my head around how to properly accomplish it.  What would be the best way to go about doing this?


texts: on slave repication filter --replicate-do-db= store; give syntax error 1064 on mysql 5.6

texts:
--replicate-do-db=store;

CHANGE REPLICATION FILTER replicate_do_db = (store);      //also by using this query

in mysql 5.6 that option on server side give syntax error 1064 how to fix it


texts: Does Sql Server 2008 support Database Sharding

texts:
Does Microsoft support implementing Database Sharding using Sql Server 2008 R2 (any edition). From my limited understanding of it, I think, we could probably implement it using Sql Server Express. But I was not sure if Microsoft supports it or not.

Database Sharding Resource Link

Other than Distributed Partitioned Views, Data-Dependent Routing, what other options are available for Scale-out? (Peer-to-Peer Transactional Replication, Federated DB?)

Please share any URL that describe any implementation architectures in detail.

Thanks,
_UB


texts: Get partial results in Postgres in case of timeout?

texts:
Is it possible to send a query that does a sequential scan of the table - something like
SELECT content
  FROM some_big_table
 WHERE <some criteria(content) are met>;
 LIMIT <...>;

with a time limit (e. g. 1000 milliseconds) - so that the query terminates after the specified time period and returns any rows it could find by that time as a result of the scan?
I'm not worried about the result being predictable - I just need to give the user the first matches the server can find, if any.
SET statement_timeout does not help here as it cancels the query altogether if it doesn't execute within the time limit, while I need the partial results.


texts: Standard Meaning of "Bounce the Database"

texts:
I have been hearing the term "bounce the database" lately, and researching it, it seems that people take it to mean different things.

Most recently is was an Oracle DBA saying:

  "not even the DBAs could access the instance, so we had to bounce the database"

Is there a standard definition?

In the context of Oracle databases, I have seen people use it to refer to a shutdown normal, shutdown immediate, or shutdown abort followed by a restart.

Would it mean the same thing in the context of SQL Server? e.g. stop the service, restart, or kill the service abruptly, restart, etc.

Perhaps in all cases it just means to shutdown and restart a database (or instance?, or server?) and depending on the context, can be achieved in a variety of ways.


texts: PostgreSQL 11: How to restrict privileges to stored functions?

texts:
How can I restrict the privileges of a PostgreSQL role to access only functions
already present in a schema, so no individual SELECT or INSERT queries are allowed?

I've created a new user:

$ createuser --no-superuser --createdb --no-createrole --pwprompt myuser
$ createdb --encoding UTF8 --owner myuser mydatabase

Then, I added my schema with all tables and functions for table access:

$ psql -h localhost -U myuser -d mydatabase -a -f myschema.sql

Is it sufficient do drop all privileges for role myuser and them just:

myuser=> GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA myschema TO myuser;

Or should I create a distinct role? Can this role access a schema whom it is not the owner of?


texts: how to set bulk_insert_buffer_size in mysql?

texts:
I want to set bulk_insert_buffer_size variable for insert. I searched from other pages too, all they are saying about my.cnf. But there is no my.cnf in my system. I checked my.ini and there is no such variable there.

I also tried through command line -

SET GLOBAL bulk_insert_buffer_size= 268435456;

It shows -

1 queries executed, 1 success, 0 errors, 0 warnings

Query: SET GLOBAL bulk_insert_buffer_size =1024*1024*256

0 row(s) affected

Execution Time : 0 sec
Transfer Time  : 0.001 sec
Total Time     : 0.002 sec

but on running -

SHOW VARIABLES LIKE '%bulk%';

I am getting the same(old one) -

Variable_name            Value
bulk_insert_buffer_size  8388608

Please let me know if I am doing something wrong.
I am using Mysql 5.5 version.

Thank you.


texts: Batching a query from a linked Oracle database in MS SQL

texts:
Working to compare and older Oracle database with a newer updated version to check that all documents have migrated correctly. 1=1 and 2=2.
The database is very large ~25,000,000 rows. So I want to be able to run this in batches of ~1,000,000 in order to make it more manageable.

The code looks at a list of all documents in the Oracle server. Finds the full path for all of these documents and stores it in CTE S
The code also finds the full path for the Microsoft database and stores it in CTE P.

These two CTE's are then joined and compared to check that they have the same count.

I can easily limit the newer code by limiting the dataID's but this doesn't seem to work for the oracle code.

with P (id, path) as (
select DTreeCore.ParentID, cast (Name as varchar(max))
  from CS16PDDB.LIVELINKPD.DTreeCore with (NOLOCK)
union all
select ParentID, cast (Name + ':' + P.path as varchar(max))
   from CS16PDDB.LIVELINKPD.DTreeCore join P
   on DataID = P.id or DataID = - P.id
),
S (id, path) as (
SELECT ID, PATH from OPENQUERY(CS,'
select
concat(''Enterprise'',SYS_CONNECT_BY_PATH(TRIM(REPLACE(Name,'':'',''-
'')),'':'')) as path,
dtc.DataID as id,dtc.Name,dtc.ParentID
from DTreeCore dtc
where Subtype = 144 AND dataID < 5000 --Here should work
connect by prior DataID=ParentID start with DataID = 2000
')
)
select count (P.path), count(S.path) from P
left join S ON P.path = S.path
where P.id = -1

I've tried a few different things. Rownum's seem to be generated after the full test has been done so i cant filter on those. I tried FETCH FIRST but also struggled there.

Any ideas would be much appreciated.


texts: I want to change oracle "homedir" how to do?

texts:
A good link as answer is ok.
I want to change "homedir" or "basedir" of oracle db from /oracle to
/var/oracle.
How to do?
I have set the oracle unix variables($ORACLE_HOME,$ORACLE_BASE)
with the new path,but db doesn't start.
So i reset them to old dir,and make a symlink for now,it work
but how to tell to oracle the new path?


texts: Give particular privileges for a table to existing role

texts:
My database have some tables such as:

Table A
Table B
Table C

I'm using user monitoring with read-only permission.

SELECT grantee, privilege_type
FROM information_schema.role_table_grants
WHERE table_name= 'Table A'

I want to set monitoring user with insert, update, delete, truncate, alter table, trigger permission for only table Table A. The another table don't affect. How to implement this?


texts: 'SSL is required but the server doesn't support it (2026)' error while making an SSL MySQL connection

texts:
I'm trying to make an SSL supported connection using MySQL Workbench 6.3.9.
My OS is Windows 10 x64 and I have installed MySQL Server 5.7 x64 with OpenSSL-1.0.2L (Win64 version). I made a non-SSL connection without any problem but when I am trying to make an SSL connection I always get this error: 'SSL is required but the server doesn't support it (2026)'
my.ini file contains these settings:
[client]
ssl-ca = "C:/vcerts/ca-cert.pem"
ssl-cert = "C:/vcerts/client-cert.pem"
ssl-key = "C:/vcerts/client-key.pem"
ssl
ssl-capath = "C:/vcerts"

[mysqld]
ssl-ca = "C:/vcerts/ca-cert.pem"
ssl-cert = "C:/vcerts/server-cert.pem"
ssl-key = "C:/vcerts/server-key.pem"
ssl
ssl-capath = "C:/vcerts"

At MySQL Workbench, after SSL Wizard's certificate generation, the SSL certificate settings are:

Any suggestions?


texts: Is it possible to backup and restore InnoDB tables on Oracle's MySQL 5.7.19 Server with Percona Xtrabackup?

texts:
I have the next configuration:

OS: Ubuntu 16.04.2 x86_64
MySQL Server: Ver 5.7.19-0ubuntu0.16.04.1 for Linux on x86_64 ((Ubuntu)) installed from Ubuntu's repos

Percona Tools: innobackupex version 2.4.8 Linux (x86_64) (revision id: 97330f7) installed from the Percona's repos.

The problem is the next: When I do the backup with innobackupex and then restore - my InnoDB tables got corrupted.

2017-07-28T15:49:50.078080Z 4 [Warning] InnoDB: Cannot open table forum/domain from the internal data dictionary of InnoDB though the .frm file for the table exists. Please refer to http://dev.mysql.com/doc/refman/5.7/en/innodb-troubleshooting.html for how to resolve the issue.

The backup/restore happens on the same server. MySQL server has the defaults settings, innodb_file_per_table option is not set directly, but Oracle's MySQL has that enabled by the default.

mysql> show variables like '%innodb_file%';
+--------------------------+-----------+
| Variable_name            | Value     |
+--------------------------+-----------+
| innodb_file_format       | Barracuda |
| innodb_file_format_check | ON        |
| innodb_file_format_max   | Barracuda |
| innodb_file_per_table    | ON        |
+--------------------------+-----------+
4 rows in set (0.00 sec)

In order to do the backup, I do:

innobackupex --defaults-extra-file=/root/.my.cnf \
--include=forum.* --parallel=9 \
--extra-lsndir=/home/forum \
--stream=xbstream \
--no-timestamp /home/forum 2>/home/backup.log | lzop -c > /home/forum.xbs.lzo

the xtrabackup_info file from the result:

uuid = 850f3d9e-73ab-11e7-8b45-448a5b2c32e7
name =
tool_name = innobackupex
tool_command = --defaults-extra-file=/root/.my.cnf --include=forum.* --parallel=9 --extra-lsndir=/home/forum --stream=xbstream --no-timestamp /home/forum
tool_version = 2.4.8
ibbackup_version = 2.4.8
server_version = 5.7.19-0ubuntu0.16.04.1
start_time = 2017-07-28 17:43:27
end_time = 2017-07-28 17:43:36
lock_time = 0
binlog_pos =
innodb_from_lsn = 0
innodb_to_lsn = 14341896999
partial = Y
incremental = N
format = xbstream
compact = N
compressed = N
encrypted = N

As the restore, I do the next:
unarchive the file:

lzop -dcfU /home/forum.xbs.lzo | xbstream -x --directory=/home/forum.restore

Apply Log:

innobackupex --apply-log --redo-only /home/forum.restore --use-memory=1G 2>/home/restore.log

Prepare the backup for usage:

innobackupex --apply-log /home/forum.restore --use-memory=1G 2>>/home/restore.log

After that my directory has:

root@restore-test /home/forum.restore # tree
.
├── backup-my.cnf
├── forum
│   ├── db.opt
│   ├── domain.frm
│   └── domain.ibd
├── ib_buffer_pool
├── ibdata1
├── ib_logfile0
├── ib_logfile1
├── ibtmp1
├── xtrabackup_checkpoints
├── xtrabackup_info
└── xtrabackup_logfile

The final is:

stop MySQL server
chown -R mysql:mysql /home/forum.restore/forum
mv /home/forum.restore/forum /var/lib/mysql/forum
start MySQL

The database is there, but when i try to do any operation with the table (SHOW/DESCRIBE/etc) - I'm having the error listed above.


texts: If "large value types out of row" table option is enabled, the table size is increased

texts:
I have recently learned about sp_tableoption which gives you the ability to store "large value types out of row" for particular table.

A colleague of me told me if I enabled it, each field is stored on 8 KB page at least, no matter how large it is. I was not able to find a confirmation of this, so I run the following test:

DROP TABLE Size

CREATE TABLE Size
(
    [ID] TINYINT
   ,[Text] NVARCHAR(MAX)
)

EXEC sp_tableoption 'Size' ,'large value types out of row' ,0

TRUNCATE TABLE Size

INSERT INTO Size
SELECT 1, N'in row text'
UNION ALL
SELECT 2, N'' -- add here very large string

SELECT [ID], DATALENGTH([Text]) Size, LEN([Text]) [Len]
FROM [dbo].[Size]

EXEC sp_spaceused Size;

DROP TABLE Size

CREATE TABLE Size
(
    [ID] TINYINT
   ,[Text] NVARCHAR(MAX)
)

EXEC sp_tableoption 'Size' ,'large value types out of row' ,1

TRUNCATE TABLE Size

INSERT INTO Size
SELECT 1, N'in row text'
UNION ALL
SELECT 2,  N'' -- add here very large string

SELECT [ID], DATALENGTH([Text]) Size, LEN([Text]) [Len]
FROM [dbo].[Size]

EXEC sp_spaceused Size;

and get following results:

So, it seems that really the first row is now occupying more space (8 KB exactly). So, I add one more row and do the test again:

INSERT INTO Size
SELECT 1, N'in row text'
UNION ALL
SELECT 1, N'in row text'
UNION ALL
SELECT 2,  N'' -- add here very large string

The result was the same. So, it is not true that each row is occupying at least 8 KB, but the size is actually increased. I know that:

  varchar(max), nvarchar(max), varbinary(max), xml and large
  user-defined type (UDT) columns in the table are stored out of row,
  with a 16-byte pointer to the root.

But this 16 byte pointer can be ignored in this case.

Can anyone tell is there a way to determined how much space overhead small values are going to cost if the option is true?

I am planning to apply this option to a large table and want to calculated how the table size will be affected.

Note, I know that the value is moved out of the row when it is inserted or updated only.


texts: Rebuilding index has impact on mdf/ldf files SQL Server 2008

texts:
While rebuilding index have used option 'SORT_IN_TEMPDB = ON' in order to avoid unnecessary growing your user database files.

What does it exactly means?

Is complete process done on TempDB and does not grow mdf/ldf file size?

For example before reindex (database is in simple recovery mode), the largest index size in the database is 25GB

.mdf size (100GB allocated and used --> no freespace)
.ldf size (20GB)
TempDB has 200GB freespace

What will be the approximate size for .mdf & .ldf files. Are they grow or not?


texts: SHOW FULL PROCESSLIST for some host does not show `Client port`

texts:
I am executing Show Full Processlist; and output does not show client port for some hosts in Host column

Documentation says that each and every connection has host:client_port format.

My question is for some of the host column it just shows localhost without client port

What could be the reason behind this?


texts:
I am trying to understand what could be the possible reason which would have caused tempdb ldf file grow full and went out of space on the drive it resides.

Compared the pattern for last month and today was the unusual behaviour of tempdb.

From third party tools i have all the SQl that were running in that 8 hour window but what exactly is the correct way to find the culprit process?


texts: SDO_GEOM.SDO_INTERSECTION between a line and a polygon returns what?

texts:
I'm confused as to what exactly the SDO_GEOM.SDO_INTERSECTION returns in Oracle. Documentation is here, but only gives examples of two intersecting polygons.

What if I have many lines, some of them are contained fully within the polygon and some intersect the polygon. Assume that I need the geometry of any line fully contained within the polygon (i.e. same as SDO_CONTAINS) AND the geometry of those parts of intersecting lines, which are inside the polygon.

Example image below. Assume I only need the geometries of the red lines, but not the green ones. Will SDO_INTERSECTION return all of the red ones or only those red ones, which also go outside of the polygon and have some green bits?


texts: Hex and Binary Literals in MySQL / MariaDB?

texts:
Another question I have pertains to just the & over string types. This question is over the binary-string literals as constructed with B'' or X''.

MariaDB supports binary and hex literals,

SELECT X'01', B'00000001', X'01' = B'00000001';
| X'01' | B'00000001' | X'01' = B'00000001' |
+-------+-------------+---------------------+
|    X  |       X     |                   1 |

* Using `X` because StackExchange strips the non-printable character.

However, interestingly, they're not the same when I pass them through the Binary-operators

SELECT X'01' & B'00000001', X'01' & X'01', B'00000001' & B'00000001';
| X'01' & B'00000001' | X'01' & X'01' | B'00000001' & B'00000001' |
+---------------------+---------------+---------------------------+
|                   0 |             0 |                         1 |

So if they're treated as bit-strings it doesn't make sense (because the result should be 1. And if they're treated as strings it doesn't make sense because the actual string is in fact (BIT-ANDed) & to 1. This shows they don't "behave as a string in all contexts".

SELECT X'01' & B'00000001', '01' & '00000001';
+---------------------+-------------------+
| X'01' & B'00000001' | '01' & '00000001' |
+---------------------+-------------------+
|                   0 |                 1 |
+---------------------+-------------------+

I see this, in the doc on hex literals,

  For hexadecimal literals, bit operations are considered numeric context, but bit operations permit numeric or binary string arguments in MySQL 8.0 and higher. To explicitly specify binary string context for hexadecimal literals, use a _binary introducer for at least one of the arguments.

MariaDB says,

  Normally, hexadecimal literals are interpreted as binary string, where each pair of digits represents a character. When used in a numeric context, they are interpreted as integers. (See the example below). In no case can a hexadecimal literal be a decimal number.

But that still doesn't seem to cut it, if anything it makes it worse (now none of the results of & return non-0.

SELECT _binary X'01' & _binary B'00000001', _binary X'01' & _binary X'01', _binary B'00000001' & _binary B'00000001';
| _binary X'01' & _binary B'00000001' | _binary X'01' & _binary X'01' | _binary B'00000001' & _binary B'00000001' |
+-------------------------------------+-------------------------------+-------------------------------------------+
|                                   0 |                             0 |                                         0 |

Even an explicit cast is wrong,

SELECT CAST(_binary X'01' AS BINARY(8)) & CAST(_binary B'0000001' AS BINARY(8));
| CAST(_binary X'01' AS BINARY(8)) & CAST(_binary B'0000001' AS BINARY(8)) |
+--------------------------------------------------------------------------+
|                                                                        0 |

Another bizarre thing is that they act different when stored on a table, for example,

CREATE TABLE g
AS
  SELECT b'00000001' AS value, b'00000001' = 0 AS isequal;

SELECT value, value=0, isequal AS wasequal
FROM g;
+-------+---------+----------+
| value | value=0 | wasequal |
+-------+---------+----------+
|      |       1 |        0 |
+-------+---------+----------+

Why do binary literals and hex-literals behave different and what are their behaviors?


texts: Troubleshooting / traceroute within Oracle Instant Client?

texts:
I am troubleshooting a freshly reimaged backup server Win2000 / Oracle 10.1.0.5.0.

Here's what I did:

Installed Oracle 10g by copying Basic Instant Client package and ODBC package into same directory, then running odbc_install.exe.
Copied tnsnames.ora from the original Win2000 server (where the DB connection works just fine)
Updated Environment variables : PATH, TNS_ADMIN and rebooted Windows

When I run the end-user application, I get an error:

  ORA-12545 : Connect failed, because target host or object does not exist.

Looking more into this, many sources (e.g. http://www.orafaq.com/wiki/ORA-12545 ) recommend using the traceroute command. But I can't seem to find the \bin directory with that command anywhere (and obviously, command is not recognized), which leads me to believe that the instant client does not have this command? I do not have the installation package for the Administrator, so how can I do more troubleshooting under Instant Client?

At an OS-level e.g. from command-line, both ping and tracert work just fine with the DB server name). Oracle's traceroute should (supposedly, as per Oracle docs) give me the full error stack (instead of a single-line error) - hence it would give me additional hint why the DB connection on a recently reimaged server does not work.

As far as I can say, both Primary and Backup server are identical (I didn't work here 10 yrs ago though). Is there perhaps a package of admin tools I could download? Or just somehow unlock it within Instant Client? Are there any Oracle logs of the DB transactions / connection attempts?

From the command line I can ping the destination DB (defined in tnsnames.ora) just fine, so I believe I can completely rule out the networking (e.g. DNS/firewall/accessibility). Of course, before the server reimage, the DB connection worked just fine, so there is clearly something else missing in the first-time configuration.

I cannot seem to find any info on command line troubleshooting under instant client. Does one really need Administrator installation to do basic command line troubleshooting?


texts: How to determine the size of my tables in the SQL Server database

texts: Is there any built-in function/stored procedure/query which is helpful to retrieve information about the size of MyTable in the SQL Server database?


texts: UTL_FILE directory name changes oracle 19c

texts:
I've scripts where UTL_FILE.FOPEN is used and the parameter passing for directory is an absolute path i.e., /asr/file/path and the corresponding oracle directory name as ASR_ABC but after up-gradation to oracle 19c the parameter is expected to be direcotry name ASR_ABC instead of absolute path /asr/file/path.
If I pass an absolute path I get error as Invalid path.
Do I have to change all the files from absolute path to directory name? or is there any work around which can be done from database to avoid changes in all files?


texts: Design database table for property listing

texts:
I want to design a database for a listing website, this is the first time I design the database, so I am not sure if I in the correct path.
Here is the fields that needed in a listing,

title - Character
pub_date -Timestamp
modi_date -Timestamp
ex_date - Timestamp
desc - Character
thumb - Character
office_no - Integer
mobile_no - Integer
email - Character
web - Character
status - Character
state - Character
city - Character
address - Character
postcode - Character
lat - float
lng - float

The lat (latitude) and lng (longitude) are used for searching, eg, searching nearby listing by comparing the lat and lng. So these fields will be queried often.

My question is should I separate the  lat and lng into another table (normalization) since these fields will be query a lot. Or I just leave it in one table.


texts: How is this called in a snowflake schema?

texts:
I'm learning the basics of DWH modeling and I didn't find a name to refer to what I colored in red in the image below, i.e. a group of tables related to each other, with one of them being related to the fact table?

Does this have a name? (e.g. relationship branch?)


texts: sp_blitzindex randomly fails when run through invoke-sqlcmd

texts:
I'm using sp_blitzindex v6.7 on Sql Server 2016(13.0.4451.0), I am intermittently having issues with running it through powershell using invoke-sqlcmd. It happens consistently with the same databases on the same servers.

When it fails it gives me this error:

  database failed to process. The ORDER BY clause is invalid in views,
  inline functions, derived tables, subqueries, and common table
  expressions, unless TOP, OFFSET or FOR XML is also specified.

It runs just fine on those databases in SSMS. I've checked the db properties to make sure it isn't compatibility level or something, as well as tried the powershell script using other databases on the same server and they work.

There's no consistency as to the results being returned - when run in SSMS for the databases that fail in powershell, some will return results and some will not.

It's not really blocking me from doing what I am trying to, but just wondered if anyone had seen this before.


texts: Need help in optimising the MySql stored procedure

texts:
I have a stored procedure which is used to delete table entries. I have the input to the procedure as an array so that I can pass them as is for the delete queries [For ex: Use the IN clause to submit the array]. Even though the array items are integers, I had read that I need to pass them as string arrays. What is happening with this is, I'm not able to pass the array entries entirely to the Delete query and need to loop through them and submit individual entry to the Delete query. Could I get any suggestions/help to achieve this? Below mentioned is the procedure:

CREATE DEFINER=`root`@`localhost` PROCEDURE `Delete_Proc`(IN entity_id_array varchar(100), IN mgmt_module varchar(10))
BEGIN

    -- Declare variables to hold diagnostics area information
    DECLARE errCode CHAR(5) DEFAULT '00000';
    DECLARE msgTxt TEXT;
    DECLARE rowCount INT;
    DECLARE resVal TEXT;

    DECLARE _sRemainder TEXT;
    DECLARE _Delimiter CHAR(1);
    DECLARE _nPos INT DEFAULT 1;
    DECLARE _sStr VARCHAR(100);
    DECLARE _entitId INT;

    DECLARE EXIT HANDLER FOR SQLEXCEPTION

    GET DIAGNOSTICS CONDITION 1
        errCode = RETURNED_SQLSTATE, msgTxt = MESSAGE_TEXT;

    SET _sRemainder = entity_id_array;

        WHILE CHAR_LENGTH(_sRemainder) > 0 AND _nPos > 0 DO

            SET _nPos = INSTR(_sRemainder, ',');

            IF _nPos = 0 THEN
                SET _sStr = _sRemainder;
            ELSE
                SET _sStr = LEFT(_sRemainder, _nPos - 1);
            END IF;

            IF TRIM(_sStr) != '' THEN

                Set _entitId = CAST(_sStr AS UNSIGNED);

                        DELETE user_management, user_endpoint_association, user_group_association
                                FROM user_management
                                    INNER JOIN
                                        user_endpoint_association ON user_endpoint_association.user_id = user_management.user_id
                                    INNER JOIN
                                        user_group_association ON user_group_association.user_id = user_management.user_id
                            WHERE
                                user_management.user_id = _entitId;

            END IF;

            SET _sRemainder = SUBSTRING(_sRemainder, _nPos + 1);
        END WHILE;

  -- Check whether the delete was successful
  IF errCode = '00000' THEN
    GET DIAGNOSTICS rowCount = ROW_COUNT;
    SET resVal = CONCAT('Delete succeeded, row count = ', rowCount);
  ELSE
    SET resVal = CONCAT('Delete failed, error = ', errCode,', message = ', msgTxt);
  END IF;
  -- Say what happened
  SELECT resVal;

END

=======================================================================

Here is the output of Explain Delete:

=========================================================================

Here is the output after adding the indexes:


texts: What parameters should I use to measure storage I/O capabilities

texts:
I want to measure the throughput of my disk subsystem using: https://gallery.technet.microsoft.com/DiskSpd-a-robust-storage-6cd2f223

what parameters should I use to closely emulate SQL I/O?

Is there a better tool/approach.

Problem I've got is that my SQL server keeps bogging down (few hundred users). I have no access to host. IT dept tell me it's not the host. They say they are SAS drives, but don't know if they are 10K or 15K. In Redgate monitor, I see disk I/O of 110MB sec. (AFAIK 15K SAS is only 126MB), so it seems to me that we're approaching max disk i/o and figured easiest thing to do is to run an I/O test on disk subsystem next time server is planned to be down

I also want to test our dev and staging because they run like molasses (restoring @ 5MB/sec) and agin IT dept say it's not the hosts.

TIA

Mark


texts:
I have a table of levels and years that looks roughly like this:

INSERT INTO demo(first_name, rank, year) AS VALUES
  ('Peter',2,2017), ('Robert',3,2016), ('Evelyn',2,2017),
  ('Rand',3,2017), ('Kofi',3,2017), ('Daniel',3,2016), ('Leonard',1,2017),
  ('Mary',1,2017), ('Frank',1,2017), ('John',3,2017), ('Mary',1,2016),
  ('Gilbert',3,2016), ('Quince',2,2017), ('David',3,2017), ('Louis',2,2017),
  ('Felipe',2,2016), ('Alfonso',1,2017), ('Elise',3,2016), ('Jorge',3,2017),
  ('Mary',3,2016), ('Jasvant',2,2017), ('Paul',3,2017), ('Richard',2,2017),
  ('Christopher',2,2017), ('Johannes',2,2016), ('Nasser',3,2016), ('Bruce',3,2017),
  ('John',3,2016), ('Frederic',3,2016);

I can capture totals for each level and year with a pretty basic COUNT():

SELECT  rank, COUNT(*), year
FROM demo
GROUP BY rank, year
ORDER BY year, rank;

That gives me a fairly tidy summary of the demo data:

Rank  Count  Year
1      1    2016
2      2    2016
3      8    2016
1      4    2017
2      7    2017
3      7    2017

My actual data is a lot bigger, and I'd like to be able to see it in a summary table that looks something more like this:

Rank    2016    2017
1         1     4
2         2     7
3         8     7

Is there a straightforward way to query a pivot-like view like this? For a much wider range of ranks and years?


texts:
I had an issue with a Clustered Index Scan. So I got all columns in the Output list of the Index Scan in SSMS and I created a NonClustered Index for these columns.

Now however, I do not see any performance difference even though it is using the new NonClustered Index. Instead it is now doing a NonClustered Index Scan.

How do I get it to do a seek?

This is the Predicate shown in the plan

CASE WHEN (1) THEN 'SGD' ELSE [UserTbl].[Name] END=[UserTbl].[Name]


texts: missing WAL file on standby server following a backup

texts:
I seem to have an intermittent problem with my postgres backup/restore to standby

Postgres, is version 9.2 running on red hat 6 enterprise

i have a production server (PROD1) and a hot standby (STANDBY1), on sunday evening i have a script which puts
PROD1 into backup mode, performs a tar on the $PGDATA directory, copies the file to STANDBY1, a script on STANDBY1
is then called to stop postgres on STANDBY1, unpack the tar, and start postgres again.

In the meantime the archive_command on PROD1 is continuing to copy WAL files over the STANDBY1, so in theory STANDBY1
will be brought up to date. Then for the remainder of the week the WAL files are applied until the next Sunday when the
process starts again.

code extract for backup script on PROD1

psql -X -U postgres --set ON_ERROR_STOP=on <<PSQL
select pg_start_backup('${WEEK_START}_DataBackup','true');
PSQL

tar -zcf $WEEKLYBACKUP_DIRECTORY/full_backup_${WEEK_START}.tar.gz --exclude=$PGDATA/pg_xlog/* --exclude=$PGDATA/pg_xlogArchive/*
--exclude=$PGDATA/pg_xlogRestored --exclude=$PGADTA/postmaster.pid --exclude=$PGDATA/postmaster.opts $PGDATA/

psql -X -U postgres --set ON_ERROR_STOP=on <<PSQL
select pg_stop_backup();
PSQL

scp -p $WEEKLYBACKUP_DIRECTORY/full_backup_${WEEK_START}.tar.gz $DR_SERVER:$WEEKLYBACKUP_DIRECTORY/.

ssh -T $DR_SERVER <<EOF >> $MAILFILE
  cd /var/lib/pgsql/Scripts
  ./postgres-hotStandby $WEEK_START Y
EOF

code extract for hotstandby script on STANDBY1

pg_ctl stop -D $PGDATA -m i >> $LOGFILE
tar -zxf $WEEKLYBACKUP_DIRECTORY/full_backup_${RESTORE_WEEK}.tar.gz

# create a recovery file
echo "standby_mode = 'on'" >$PGDATA/recovery.conf
echo "primary_conninfo = 'host=${LIVE_SERVER}'" >>$PGDATA/recovery.conf
echo "restore_command = 'cp -f $PGDATA/pg_xlog/%f $PGDATA/pg_xlogRestored'" >>$PGDATA/recovery.conf
echo "trigger_file = '${PGDATA}/failover'" >>$PGDATA/recovery.conf
sed -i 's/#hot_standby = off/hot_standby = on/g' $PGDATA/postgresql.conf
pg_ctl -w -D $PGDATA start

the above process works every second time it runs and fails the other times, giving the message

  "psql: FATAL:  the database system is starting up"

when i try and
get into psql.

looking in the log file on STANDBY1, postgres is awaiting a WAL file which it says...

  cp: cannot stat
  `/vol2/postgres92/pgsql/data/pg_xlog/000000010000005200000003': No
  such file or directory   2015-04-24 00:10:40 EDT -LOG:  streaming
  replication successfully connected to primary   2015-04-24 00:10:40
  EDT -FATAL:  could not receive data from WAL stream: FATAL:  requested
  WAL segment 000000010000005200000003 has already been removed

when pg_start_backup is run, this creates a WAL file which is shipped to STANDBY1 (eg. 000000010000005200000003), and when pg_stop_backup is run this
creates a end of backup WAL (eg. 000000010000005200000003.00000020.backup), which is also correctly shipped to STANDBY1. But when the failure occurs
the pg_start_backup WAL seems to go missing on the STANDBY1 server.

hopefully this all makes some sort of sense to someone, any ideas what is going wrong? I seem unable to reproduce this on my test systems, and i'm thinking it
may be a timing issue as due to the size and location of PROD1 and STANDBY1, the scp between the servers takes 15mins.

thanks for any help you can give


texts:
I created a table in AWS Redshift (4-node dc1 cluster) in the following manner.

CREATE TABLE events
(
    event_id int,
    event_date int ,
    event_month_year int ,
    event_year int ,
    event_fraction_date numeric(10,4) ,
    event_actor1_code varchar(100) ,
    event_actor1_name varchar(100) ,
    event_actor1_country_code varchar(100) ,
    event_actor1_known_group_code varchar(100) ,
    event_actor1_ethnic_code varchar(100) ,
    event_actor1_religion1_code varchar(100) ,
    event_actor1_religion2_code varchar(100) ,
    event_actor1_type1_code varchar(100) ,
    event_actor1_type2_code varchar(100) ,
    event_actor1_type3_code varchar(100) ,
    event_actor2_code varchar(100) ,
    event_actor2_name varchar(100) ,
    event_actor2_country_code varchar(100) ,
    event_actor2_known_group_code varchar(100) ,
    event_actor2_ethnic_code varchar(100) ,
    event_actor2_religion1_code varchar(100) ,
    event_actor2_religion2_code varchar(100) ,
    event_actor2_type1_code varchar(100) ,
    event_actor2_type2_code varchar(100) ,
    event_actor2_type3_code varchar(100),
    is_root_event varchar(100) ,
    event_code varchar(100) ,
    event_base_code varchar(100) ,
    event_root_code varchar(100) ,
    event_quad_class int ,
    event_goldstein_scale float ,
    event_num_mentions int ,
    event_num_sources int ,
    event_num_articles int ,
    event_avg_tone float ,
    event_actor1_geo_type int ,
    event_Actor1_Geo_Full_Name varchar(500) encode lzo,
    event_Actor1_Geo_Country_Code varchar(100) ,
    event_Actor1_Geo_ADM1_Code varchar(100) ,
    event_Actor1_Geo_Lat float ,
    event_Actor1_Geo_Long float ,
    event_Actor1_Geo_FeatureID varchar(100) ,
    event_Actor2_Geo_Type int ,
    event_Actor2_Geo_Full_Name varchar(500) encode lzo,
    event_Actor2_Geo_Country_Code varchar(100) ,
    event_Actor2_Geo_ADM1_Code varchar(100) ,
    event_Actor2_Geo_Lat float ,
    event_Actor2_Geo_Long float ,
    event_Actor2_Geo_FeatureID varchar(100) ,
    event_Action_Geo_Type int ,
    event_Action_Geo_Full_Name varchar(500) encode lzo,
    event_Action_Geo_Country_Code varchar(100) ,
    event_Action_Geo_ADM1_Code varchar(100) ,
    event_Action_Geo_Lat float ,
    event_Action_Geo_Long float ,
    event_Action_Geo_FeatureID varchar(100) ,
    event_date_added int ,
    event_source_url varchar(500) encode lzo
)
diststyle key
distkey(event_id);

I performed a COPY of around 60k records.

Post that, I performed a VACUUM and ANALYZE. It fails at the ANALYZE command with the following error:

[Amazon](500310) Invalid operation: index "pg_toast_16408_index" is not a btree;

I tried dropping and re-creating. But I get the same error. But once I change the diststyle to ALL, then the error disappears.

Any help on this would be very much appreciated.


texts: SQL redundant databases both sets being modified independently yet staying concurrent

texts: I have a server in location 1. This location has slow external internet but all the employees. Location 2 has fast external internet but no people are there. I have a mobile app that I would like to connect to location 2 and have an instantaneous sync. between the two databases. What is the proper way to do this? If a field is updated at site 1 the server sends it to 2. If 2 is modified it sends it to site 1. The last change is always used.


texts: Error Code: 1205 Lock wait timeout exceeded in MySQL

texts:
I have set InnoDB lock wait time-out to 120 seconds.

When I run the query below for first time, it works fine.  However, the second time, the same query with a different value gives me:

  Error Code: 1205 Lock wait timeout exceeded; try restarting transaction

INSERT INTO PM_SPEC_DOC_APPROVE (
    SDA_SYS_NUM,
    SDA_SPEC_SYS_NUM,
    SDA_SYS_REV,
    SDA_APPR_UID,
    SDA_APPR_DATE,
    SDA_CR_UID,
    SDA_CR_DT,
    SDA_SRNO,
    SDA_NOTE)
VALUES (
    '7414',
    '24510',
    NULL,
    'E13462',
    '2012-07-25 16:35:49',
    'E13462',
    '2012-07-25 16:35:49',
    '3',
    NULL)

We are using MySQL v5.1


texts: Always On/SQL Server Replication for Dummies

texts:
I'm a database developer, not a DBA, but the task has fallen to me to write a brief white paper on our options for configuring a database mirror for web service APIs to use as its source, in order to take load off our primary, transactional database.

I listen to the Brent Ozar podcasts, and I hear how "always on availability groups" are pitched as being the "right" answer for this. I also know that the older SQL Server "database mirroring" technology is officially deprecated.

So I suggested AGs, and our DBA seemed to be on-board, but then he came back and said that implementing this would require the creation of failover clusters and would require lots of extra $$$s and licenses.

I have tried to read the literature about how availability group licensing works, and I get lost very quickly.

Can anyone point me to a "SQL Server Replication for Dummies" type post that compares options and costs for building a second database replica/mirror that can be actively queried for reporting purposes, specifically:

Nightly automatic backup/restore
Log shipping (will that work here?)
Database mirroring
Always On Availability Groups

If so, I'd be forever grateful.

If it matters, our production DB version is SQL Server 2014 Enterprise Edition, but we will be updating to SQL Server 2017 Enterprise Edition this fall.

Thanks!


texts: Greenplum ERROR: Canceling query because of high VMEM usage

texts:
We have a GreenPlum Cluster which we have set up recently and getting this error on a single query run:
current group id is 140611, group memory usage 40720 MB, group shared memory quota is 31320 MB, slot memory quota is 0 MB, global freechunks memory is 1044 MB, global safe memory threshold is 1048 MB (runaway_cleaner.c:197) SQL state: XX0
I'll be happy to post the settings we currently have in place that may help able to troubleshoot further.
Here are some basic ones I can share:
Cluster has got 128GB RAM on each node host
SWAP on each Node is 32GB
 groupid  |   groupname   | concurrency | cpu_rate_limit | memory_limit | memory_shared_quota | memory_spill_ratio | memory_auditor | cpuset
 24964400 | my_user_group    | 10          | 60             | 60           | 80                  | 0                  | vmtracker      | -1


texts:
I am using Ola's scripts for stats updates and for one particular index I want to do full scans when updating stats (to solve for the Ascending Key problem in conjunction with trace flag 2389). We have thousands of identical databases (this is how our vendor software works) and I'd like to make sure that all stats for this index are in fact doing full scans, not sampling. How do I go about running a query to check all DBs?

I can use DBCC SHOW_STATISTICS(), but that returns three result sets -- is it even possible to put just the first result set into a table and ignore the other two? Is there a better option?


texts: What are the advantages of using a Materialized View directly on a table?

texts:
Recently, in enterprise's Oracle database, I have seen some materialized views that filter directly columns of table without adding aggregate fields and without calling custom functions.

Example:

create view VW_IMP_INVENTORY
  as SELECT <some-columns> FROM <other-schema>.TB_INVENTORY;

create materialized view MV_IMP_INVENTORY
  as SELECT * from VW_IMP_INVENTORY;

In this situation, what are the advantages of using a MATERIALIZED VIEW instead of using directly the VIEW used in MATERIALIZED VIEW ?


texts: SQL Server Audit not tracking changes at the database level

texts:
I'm currently trying to create a test implementation of SQL Server Audit in SQL Server Management Studio to see how it works. I created an test audit at the server level, and another one at the database level.  In SQL Server, audits are disabled by default when they are first created, so I enabled both of them and tested to see if it worked.  I updated several rows and deleted a role in a table that was in the tracked database.  When I went to check the log, it didn't show any changes at the database level.  I made a few changes (described below), and it did reflect those changes in the SQL Server Audit log.

First, I thought the log might need to be refreshed, so I refreshed the logs at both the database and server level. I restarted SQL Server Management Studio.  Still wasn't working.  I tried to specify which database to track at the server level by using the filter tool in the Properties menu by adding "([database_name]='test_database_name')".

Then, I thought maybe the problem was occurring at the database level, so I rechecked the configuration settings in the database audit properties menu. When selecting the Audit Action Types you want to track, you also have to set the Object Class, the Object Name, and the Principle.  At first, I had set all of the Audit Action Types to track at the database level because I want to track all of the tables in the database, but I changed the Database to Object and selected all of the table names in the Object Name field. I'm not exactly sure what the Principal field does, but you have to select a particular user for the Principal.  I started by selecting the Administrator role because I'm working on a test database, but I changed it to [sys]. None of these changes have had an effect.

Can anyone think of any reasons why the Server Audit log wouldn't be registering changes?


texts:
Is there a way to separate memory allocation for databases in mysql on the same server?
Say for database_1, we would like to allocate 20GB RAM,
and for database_2, 10GB RAM.
Is there any way this is possible.