texts: same table on different commands (inner join)

texts:
Testing some commands of MySQL, I've come across these two comands resulting on the same table.

Command 1:

select sales.relatory_code, sales.date, destiny.exportation_name from sales, destiny where sales.destiny_exportation_code = destiny.destiny_exportation_code;

Command 2:

select sales.relatory_code, sales.date, destiny.exportation_name from sales inner join destiny on sales.destiny_exportation_code = destiny.destiny_exportation_code;

Basically, instead of using two tables on "from" clause and using the "where" clause, i'm just using "inner join". I would like to know what's the main difference between the two commands and if one is better than the other.


texts: Two ways to define may-have-a (1..0-1) relationship

texts:
Today I have discovered for myself that may-have-a, one-to-zero-or-one, (1-0..1) relationship can be implemented in two different ways:

Let A be the "main" table (left side of ..) and B be the "linked" table (right side of ..).

The table A has a column which can be either a number OR NULL. The number links to the primary key of B.
The table B has a UNIQUE column which links to a primary key in table A.

Mathematically these two ways to link tables are equivalent (they are both 1-0..1 that is may-have-a), but the structure of the tables is different for these two variants.

I am writing a lightweight ORM for our DB. (I know I should not, but so things work in our company.)

The bad thing is that we have may-have-a relationships in our database implemented sometimes in one way, sometimes in the other.

Should we restructure our DB to retain only one of these two relationships? Which of the two variants to retain?

As a temporary measurement, I propose to create in my ORM two relationships:

may-have-a1
may-have-a2

Is there any way to avoid this trickery?


texts: How do I join a parent table to two child tables without duplication?

texts:
parent table

id
city_name
zip_code

1
new york city
10001

2
los angeles
90001

3
chicago
60007

Customer, child table

id
customer_name
city_id
address

1
alice
1
428 Hartford Lane

2
betty
1
249 George Street

3
cath
3
12112 S Front Ave

4
daph
3
8619 S Wolcott Ave

Restaurant, child table

id
address
city_id

1
256 Roehampton St.
1

2
920  Cherry Camp Road
3

3
7609 Mckinley Ave
2

4
5723 Morgan Ave
2

When using the following query,
    select c.id, c.city_name, c.zip_code, r.id, r.address, r.city_id, cus.id, cus.customer_name, cus.city_id, cus.address
    from restaurant r
    left join city c
        on r.city_id = c.id
     right join customer cus
        on c.id = cus.city_id where c.id = 1;

There will be a duplicate restaurant address entry. How do I ensure that the restaurants and customers are distinct?

id
city_name
zip_code
id
address
city_id
id
customer_name
city_id
address

1
new york city
10001
1
256 Roehampton St.
1
1
alice
1
428 Hartford Lane

1
new york city
10001
1
256 Roehampton St.
1
2
betty
1
249 George Street

***Edit: To add some context, I would like to return a json object of a city which contains its fields as well as a list of customers and a list of restaurants belonging to that particular city.
Is it more efficient to make three separate queries to populate that object, i.e.
select * from city where id = 1;
select * from customer where city_id = 1;
select * from restaurant where city_id = 1;

or
perform one query with two joins, with the expected result as the following, where there are no duplicate restaurants or customers

id
city_name
zip_code
id
address
city_id
id
customer_name
city_id
address

1
new york city
10001
1
256 Roehampton St.
1
1
alice
1
428 Hartford Lane

1
new york city
10001

2
betty
1
249 George Street

Or can I just use a distinct keyword to remove duplicates?


texts:
I need to run

ALTER SYSTEM SET track_activity_query_size = 16384;

So I can see the contents of some long running queries (I can only see the beginning at the default size), but when I try to do that in pgAdmin on my RDS instance I get the error

ERROR:  must be superuser to execute ALTER SYSTEM command

How do I run that alter system query?


texts: Can Sql Server Backups be replaced by a netapp SnapManager?

texts:
In the process of getting some new magic and high speed SAN devices I was told by those crazy magic-SAN-things-administrating IT people that it will be obsolete to perform ANY Sql-Server Backups in future, as soon we start using this netapp snapmanager snapshot magic. Unfortunately I was not involved in product selection process or any meetings or demos.

So the one person that still is very sceptic about this and not yet able to be a part of all those excited and euphoric celebrations is - me...   :-)

I do not really know much about database snapshots and (because) did not really use it so far, and I do not know if this netapp snapshot is the same snapshot/technology that sql supports... But if so, I fear that any of those snapshots are pretty useless as soon we have lost our source database, right?

So, is it really true, that we can replace database backups, transaction log backups and any other backups using those products and technology and still stay able to respond to point in time recovery requests for our databases?


texts:
I have some C# CLR to parse a directory looking for files on the filesystem.  I want this to run in an endless loop (preferably managed via SQL jobs).  Hoping someone can suggest the best and most efficient way to do this?

Thanks in advance,

Ben.


texts: Create index clustered X NonClustered with include

texts:
I have a table like :

create table x (a varchar(20),
b varchar(30),
c varchar(30),
primary key clustered(a asc))

so I'm wondering if I create an index like:

Create nonclustered index test on x(a) include (b,c)

that index will make my querys that use columns a,b,c  faster?


texts: MAX(id) function in query with indexed condition

texts:
I have a table

create table test
(
  id int auto_increment,
  createdAt TIMESTAMP not null,
  primary key (id)
);
create index createdAt
  on test (createdAt);

And here is my query

SELECT MAX(id) FROM test WHERE DATE(createdAt) <= '2020-02-07';

Explain says Using where; Using index
But slow query log says 1 - 10+ seconds.

Important notice: Old rows with createdAt < NOW() - 1 MONTH periodically removed by separate cron job. Average rows count 100k-10m.

As I understand, MAX() function compares each row, so results are so volatile.

Question: Is there any possible optimizations for such aggregation function?


texts: Mysql: select from information_schema.STATISTICS hanging or slow? never saw a result

texts:
I'm trying to check statistics with
    select from information_schema.STATISTICS
The query never ends. I have to stop it.

I checked innodb_stats_on_metadata=0 like in this post Solving INFORMATION_SCHEMA slowness, it was already set to 0.

Any idea?
I have more or less 20K tables and mysql 5.6.
Could this also give performance problem?


texts: Trying to understand SHOW SLAVE STATUS in MySQL

texts:
I have a Master-Slave replication setup and it looks like that it is running fine. Below is a result of SHOW SLAVE STATUS command:

show slave STATUS\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: *.*.*.*
                  Master_User: repliV1
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 10726644
               Relay_Log_File: mysqld-relay-bin.000056
                Relay_Log_Pos: 231871
        Relay_Master_Log_File: mysql-bin.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: data1
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 0
                   Last_Error:
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 10726644
              Relay_Log_Space: 232172
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File:
           Master_SSL_CA_Path:
              Master_SSL_Cert:
            Master_SSL_Cipher:
               Master_SSL_Key:
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 0
               Last_SQL_Error:

I would like to understand further about the Relay_Log_File, Relay_Log_Pos and Relay_Master_Log_File.

My questions are:

Is it true that the relay log file is one which is being read and stored locally for the replication to run ?
What about the relay_master_log_file then; how is it different from the Master_Log_File?
What are both of these values, viz., Read_Master_Log_Pos and Relay_Log_Pos ? Why are they showing up even though the replication is complete and in sync ?
Is it true that these files are in binary format, and hence I cannot view them ?


texts: Is it normal for the SQL Server Profiler SP:StmtCompleted event to give 0 duration?

texts:
I'm trying to diagnose a slow running stored procedure in SQL Server.

The procedure is quite long and complicated. I have been trying to figure out which statement is causing the issue.

I have run a SQL Server Profiler trace with the SP:StmtCompleted and RPC:Completed events set to capture. This looks like it should give me the information I am looking for, the Duration column is showing 0 for all the events. Is this normal? I don't believe that the events are taking less than a microsecond to execute.

On the PRC:Completed event both Start and End time are both set to the same value.

I have made sure that the Duration column is selected in the Trace settings, I have also set the Tools->Options->Show values in Duration column to microseconds.

Note: I don't want people to fix my procedure, this question is about the technique for capturing diagnostic information.


texts: What is the point of a consistent and partition tolerant multi-master DB Architecture as opposed to Master-Slave

texts:
I should emphasize that my question is in regard to CP (Consistent and Partition Tolerant) systems.

A lot of widely used NoSQL databases such as Mongo and Redis seem to fall in the CP category. And in addition to that, RDBMS's such as SQL Server and MySQL can also be configured to operate in a CP multi-master mode.

Two arguments I could find on the Internet are:

Load balancing write queries
Decreasing downtime in case of failure of a Master

I have doubts about the first reason because as far as I understand, in order to maintain data consistency between these Master replicas, the write operation should take place in every Master. If so, that will create the same burden for all the Masters plus the overhead associated for synchronization.

What are the solid reasons for such systems? Or am I missing something?


texts: Build a single JSON value from two jsonb columns in two different tables

texts:
I have two tables. Both have a jsonb column with an array of objects, each containing the key student_id.

I am trying to build one aggregated JSON value from these jsonb columns.

Lets say table A has four rows and each has jsonb data like:

[
  {
    "student_id: 1,
    "others element from table A" ...
    ...
  },
  {
    "student_id: 2,
    "others element from table A" ...
    ...
  }
...
]

And table B has holds jsonb values of the form:

[
  {
    "student_id": 1,
    "others element from table B" ...
  },
  {
    "student_id": 2,
    "others element from table B" ...
  }
...
]

There is only one row from table B at a time that is to be mapped to multiple rows from table A, all containing different "other_elements" but with the same "student_id" value for each object.

My expect result is of the form:

[
  {
    "student_id": 1,
    "others elements from table A" ...
    "others elements from table B" ...
  },
  {
    "student_id": 2,
    "others elements from table A" ...
    "others elements from table B" ...
  }
]

Here is a fiddle with sample data demonstrating my case.


texts: Scalable way to track who's viewed a post?

texts:
I'm building an app that needs to check who, out of millions of users, has not received a post. If a post hasn't been seen by many people, this task will be easy. But if a post has been seen by nearly everyone, it will be difficult to find the needle (the user who hasn't seen it) in the haystack (users who have seen it)

Are there any known strategies to resolving this problem?


texts:
Each table in my database (see schema) has a primary key called id.

I would like to find all records in all tables where id is in the range [1,1000]. How can I write such a query?

I've tried to write a for loop following the PostgreSQL docs

FOR T IN (
  SELECT TABLE_NAME FROM information_schema. TABLES
  WHERE table_schema = 'public'
    )
LOOP
    SELECT id, T FROM T WHERE ID BETWEEN 1 AND 1000;
  RETURN NEXT T;
END LOOP ;

but get "Syntax error at FOR".


texts: What happens when an auto grow event is triggered to the database?

texts:
I want to know what happens when an autogrow event is triggered...
Does it hold up all the database processing including reads and writes during that time?

For instance, our database encounters for up to 10 autogrowth avg per day.

Also, what are the cons for not using autogrow and rather pre sizing the database to use the maximum disk space?


texts: merging many jsonb objects using aggregate functions in PostgreSQL?

texts:
Is there a standard function in PostgreSQL (as of 12.x) to concatenate or merge many jsonb objects in a database column into a single jsonb object?

I know there is a the || operator since PostgreSQL 9.5 to merge two jsonb objects. But I need to merge many jsonb objects from a column. The linked documentation does not seem to have one unless I am missing something.


texts:
I'm looking for any example where using a cursor is unavoidable.
I'm pretty sure there are some rare cases like that, but I'm unable to come up with any scenario. Any help would be wonderful. Thanks!


texts: sql-server optimize query with lots of null checks

texts:
I have a query such as:

select something
from somewhere
join stuff on condition
join more_stuff on condition
where somewhere.sometime is null
and somewhere.someone is null
and stuff.whatever is null

and so on.

I try nonclustered group indexing somewhere.sometime and somewhere.someone, the query slows down. I read somewhere that MSSQL keeps a bitmap of nulls for each row.

The question is, how to speed up the query so that access to rows with the specified null values is faster and indexed? (so you only scan rows with the specified nulls, not full table scan) Can I somehow take advantage of this null bitmap ?


texts: Entering command 'sqlplus' results in 'sqlplus: command not found'

texts:
I've recently installed Fedora 19 on an older PC, to serve as a test server of sorts. After a lot of hiccups, I was able to install Oracle 10g XE.

Because I'm using Linux - which I, admittedly, have little experience of - I used the command line (rpm) to install the file.

Everything proceeded without a hitch, though I didn't understand much of it.

When I try to log in using sqlplus, though, the command line returns:

sqlplus: command not found

Now, I did some digging around. First thing I did was to check if the server is up and running. So I entered the following:

sudo etc/init.d/oracle-xe start

Take note that being a Linux noob, I actually have no idea if this command holds true to every Linux installation. All I know is that it works on my friend's PC. My PC, on the other hand, produces some interesting results:

Starting oracle-xe (via systemctl): Job for oracle-xe.service failed

Tried the following:

sudo etc/init.d/oracle-xe start

With the following results:

  /usr/lib/oracle/xe/app/oracle/product/10.2.0/server/bin/lsnrctl:
  /lib/ld-linux.so.2: bad ELF interpreter: no such file or directory

I don't know who this ELF guy is, but at this point, I assumed I'm screwed. After more research on the internet, I found out something about Oracle variables and paths not setting themselves correctly, but none of them were of any help.

sqlplus: command not found

Go To Oracle Home

Any ideas?

Additional Info:

Oracle Database Installed: Oracle XE 10.2.0.1.1 i386 (rpm file)
Operating System: Fedora x86-84, Release 19


texts:
I have a table with 4 columns:

ID     ORDER_ID     QTY     VALUE
1       153          1        0
2       476          1        0
3       453          9        1
4       719          2        2

With this table, I have to concat " " to the values of QTY and VALUE and then store it back in the table, so it can look like this (Both, qty and value are TEXT):

ID     ORDER_ID     QTY     VALUE
1       153         "1"      "0"
2       476         "1"      "0"
3       453         "9"      "1"
4       719         "2"      "2"

To concat the values, I use the following:

SELECT CONCAT('"',qty,'"') qty,
CONCAT('"',`VALUE`,'"') VALUE
FROM `myTable`;

Now, when I try to use:

INSERT INTO `myTable` (`order_id`, `qty`, `value`)
SELECT `order_id`, CONCAT('"',qty,'"') , CONCAT('"',value,'"')
FROM `1.1`;

This basically blanks my entire table.

However, if I use a single concat, I can get the desired output stored but the rest of the fields are blank.

 INSERT INTO `myTable` (`qty`)
    SELECT CONCAT('"',qty,'"')
 FROM `1.1`;

Sorry if this is a newb question but I did try to search no to avail.

Thank You.


texts:
I have a stored procedure which runs pretty slow (6 sec) using the following JOIN:

JOIN tariffs t ON
(LEFT(`cdrs`.`cnumber`,7) = t.numberrange
OR LEFT(`cdrs`.`cnumber`,8) = t.numberrange)

Without the above JOIN the query runs at 1.5 sec.
Any way how to improve the performance? Full stored procedure below:

CREATE DEFINER=`xxx`@`%` PROCEDURE `GetHourStats`(IN _ID INT, IN _YEAR INT, IN _MONTH INT, IN _DAY INT)
BEGIN

set @_START = UNIX_TIMESTAMP(date(_YEAR * 10000 + _MONTH * 100 + _DAY * 1));
set @_END = UNIX_TIMESTAMP(date_add(date(_YEAR * 10000 + _MONTH * 100 + _DAY * 1), interval 1 day));

SELECT h.idhour, h.`hour` as 'hour', innumber, count(*) as `count`, sum(talktime) as `duration` FROM (
     SELECT
        `cdrs`.`dcustomer` AS `dcustomer`,
        (CASE
            WHEN (LEFT(`cdrs`.`cnumber`, 2) = "01" OR LEFT(`cdrs`.`cnumber`, 2) = "02") THEN '01-02'
            WHEN (LEFT(`cdrs`.`cnumber`, 2) = "03") THEN '03'
            WHEN (LEFT(`cdrs`.`cnumber`, 2) = "05") THEN '05'
            WHEN (LEFT(`cdrs`.`cnumber`, 2) = "06") THEN '06'
            WHEN (LEFT(`cdrs`.`cnumber`, 2) = "07") THEN '07'
            WHEN (LEFT(`cdrs`.`cnumber`, 3) = "080") THEN '080'
            WHEN (LEFT(`cdrs`.`cnumber`, 3) = "084") THEN '084'
            WHEN (LEFT(`cdrs`.`cnumber`, 3) = "087") THEN '087'
            WHEN (LEFT(`cdrs`.`cnumber`, 2) = "09") THEN '09'
        END) AS 'innumber',
        FROM_UNIXTIME(`cdrs`.`start`) AS `start`,
         (`cdrs`.`end` - `cdrs`.`start`) AS `duration`,
         `cdrs`.`cnumber` AS `calling`,
         `cdrs`.`talktime` AS `talktime`
    FROM `cdrs`
    JOIN tariffs t ON (LEFT(`cdrs`.`cnumber`,7) = t.numberrange OR LEFT(`cdrs`.`cnumber`,8) = t.numberrange)
    WHERE `cdrs`.`start` >= @_START and `cdrs`.`start` < @_END
    AND `cdrs`.`stype` = _LATIN1'external'
    AND `cdrs`.`talktime` >= 5
    AND `cdrs`.`status` = 'answer'
    AND CHAR_LENGTH(`cdrs`.`cnumber`) = 11
    GROUP BY callid
   ) cdr

   JOIN customers c ON c.id = cdr.dcustomer
   LEFT JOIN hub.hours h ON HOUR(cdr.`start`) = h.idhour

    WHERE (c.parent = _ID or cdr.dcustomer = _ID or c.parent IN
        (SELECT id FROM customers WHERE parent = _ID))

   GROUP BY h.idhour, cdr.innumber
   ORDER BY h.idhour;

END

Q: How can I make the above stored procedure run faster?


texts: How to persist messages in Postgresql's LISTEN/NOTIFY?

texts:
If I send notifications before starting to listen i.e.

notify test, 'test message';
listen test;

I loose that notifications. How can I persist them for later fetching?
Actually, I need this notifications for tables updates, so it is preferably not to start spezialized messaging system like RMQ etc.


texts: Difference between logical and physical reads/writes

texts:
In short, I would like to understand the difference between physical and logical reads/writes in a database. When (which threshold) should I be worried about them? Is this post an accurate description? (Even in that case, I am still a bit unclear as to what can be considered high reads or writes).

I am doing level 2 support at this moment and the drive where our production database resides is experiencing severe performance problems:

I ran 2 of Glenn Berry's diagnostic information queries to view the top 10 stored procedures in terms of total logical writes and average I/O and got the following back:

My task is to identify the stored procedures that may be causing the low %idle time so our product team can review the code.


texts: Delete unassigned shards in Elasticsearch

texts:
I had an elasticsearch server, which ran in single node mode.
When dataset reached 1TB, I added second node and relocated couple of shards with reroute api.
Now second node has 2 of 5 shards, but first node still holds all 5 shards and space is not reclaimed.
_cat/shards?v command shows:
new_messages                    3     p      STARTED    974698739 256.6gb 5.188.130.61  el01
new_messages                    3     r      UNASSIGNED

I've found some "solutions" like stop ES and delete files by hand, but I don't like them.


texts: Schema for storing historical data on shared identifiers

texts:
When user account is assigned an IP address, I create a table entry for the account/IP mapping. This data in the table is updated every 5 minutes. Right now I'm only keeping real-time data but I need to figure out how to store this historically.

So let's say at 09:00:00 I have the following:

10.10.100.20   dom\jsmith
10.20.5.30     dom\psanders
10.90.220.5    dom\kwest

This all stays the same for the next 45 minutes and then at 09:50:00

10.10.100.30   dom\jsmith
10.10.100.20   dom\jsmith
10.20.5.30     dom\bpeters
10.90.220.5    dom\sparker

In this case jsmith logged into a term server so his account maps to a second IP. The more difficult problem for me is bpeters shares a desk with psanders and the same for kwest and sparker.

Goals:

Recall who was logged in on 8/10 at 9:00.
Recall what IPs did jsmith have between two dates/time periods
Recall who was assigned a given IP on a given date

I thought about creating a table for each time period but over a long period of time that's a lot of tables and probably really inefficient. The number of IPs to track is probably 20k and individual accounts 15k.

As a novice to DB engineering I don't know if I'm missing a simple solution here or if I should invest a good amount of time into DB schema design.


texts: What are the disadadvantages of a datawarehouse with no datawarehouse tables, but only datamarts?

texts:
The ETL design pattern prescribes transformation from staging tables to a central data warehouse data model, followed by extracting data from the data warehouse data model to data marts.
Are there any limitations, and what could go wrong, with transforming data from the staging tables directly into a data mart?  (e.g., skipping the data warehouse data model)
Doing that would save a lot of development time and effort in my view.  It would also save time when the data warehouse model changes.  Data history could be accomplished in the staging layer.
The data warehouse my organisation is currently developing (based on the ETL pattern) seems to take ages.  We do need a data warehouse/business intelligence solution, but it doesn't need to be extremely advanced; we are a midsize organisation (1000+ employees).
Would using ELT instead of ETL save development time? (e.g., writing SQL queries for the transformation instead)


texts: How are other computers able to connect to my database

texts:
I have installed the software on my clients system where sql server was already installed. I have installed another named instance.

I am able to login to the SQL server from another computers, when I check the settings if found that.

TCP IP of my named instance is Disabled
SQL Browser is running
There is no UDP port open for sql browser i.e 1434
SQLSever.exe is not added as exception in Firewall
Firewall settings are off in the Quickheal antivirus
I am using connection string as "computername\instancename" to connect

I wish to know how the are other computers able to connect when Port 1434 is off


texts:
I have been getting the below error.

    System.Data.Entity.Core.EntityException: The underlying provider failed on Open. ---> System.InvalidOperationException: Timeout expired.  The timeout period elapsed prior to obtaining a connection from the pool.  This may have occurred because all pooled connections were in use and max pool size was reached.
   at System.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, DbConnectionInternal& connection)

I know that this is a question already asked by some users and i have gone through most of those and i learned the usual root causes and the fixes.
I learning are below:

systems is unable to open new connections because connection pool max
is reached.(default is 100 and it is set in the connection string)
Opened connections are not closed, called connection leaking
More than 100 concurrent connections are being used. Increase the max
pool size in the connection string if we have more than 100
simultaneous users.
Slow queries or open transactions blocks new connections.
When the connections are being held open by slow query execution/open transactions and instead of being reusing connections the new connection are open and eventually reaches the connection pool maximum.

When i did my research i learned that it connection pool is something which the application maintains.I talked to the application team and they say that it has never occurred earlier(for the last 4 or 5 years) and so it must be something with database server.
Our SQL server version earlier was SQL 2008 R2 and we migrated to SQL Server 2016 as the data became huge like 1.5 TB and the performance was bad.
After migrating to SQL Server 2016,and fixing some performance issues we were stable,but getting this connection pool error now and then.

How do i go about this connection pool error from the database side.

Thanks


texts: Join two tables and create a new one in SQL

texts:
I am new to SQL and trying to apply a concept of Joins in MS-access.

I have two tables Table 1 and Table 2:

I want a query which will create a Table 3:

Ideally i want all the data from both tables but wherever the ID's are matching in both tables, the category should become '3'

Can someone please help me with this query. Thanks in advance.

I have attached the samples for your reference


texts: User cannot select from table when having SELECT permissions if CONTROL is denied

texts:
I'm setting up my database on an Azure SQL Server instance.
(I know the principal names are terrible, it's just a dry run test)
I created a login ("Test"), a user ("Test"), a schema ("Test"), added a table to the schema ("TestTable", created a role ("TestWriter"), set role permissions for the schema and added the user to the role.
I granted the role the following permissions:
GRANT
    INSERT,
    SELECT,
    UPDATE
ON SCHEMA::Test
    TO TestWriter
GO

and denied all other permissions, following the practice of "least permissions" (i.e. giving a role/user only the minimum permissions it needs):
DENY
    DELETE,
    ALTER,
    CONTROL,
    EXECUTE,
    REFERENCES,
    TAKE OWNERSHIP,
    VIEW DEFINITION
ON SCHEMA::Test
    TO TestWriter
GO

however then there was an error selecting and/or inserting into the table in the schema, the error being
The INSERT permission was denied on the object 'TestTable', database 'TestDb', schema 'Test'.
through trial and error I found out the problem is fixed if I GRANT CONTROL permission to the role.
However, from what I read the CONTROL permission isn't something I'd want to grant willy-nilly to anyone who just needs to SELECT/INSERT into a table. Is there something I am missing? Is it possible to SELECT/INSERT/UPDATE a table WITHOUT having CONTROL permission?


texts:
I need to share a SQL Server 2008 R2 DB with the development team.
The data is classified but the schema and the volumes are not.
The DB includes all primitive types (long, decimal, nvarchar etc.) but also:
XML- some of the XMLs have schemas outside of the DB (application is writing to it). I would expect a masking process to only go on the value and the attribute values, leaving the elements and the attributes as is.
Geometry and Geography- the expected behaviour is for the data to maintain the following characteristics: shape and complexity, number of rings (polygon), number of vertices, coordinate system, area. This does not have to be one to one, but if we have a very complex polygon the developers should have something similar in their environment.

How can I scramble the data to declassify it?


texts:
Table Animal
has following columns:

SR_ID
First_Name
Last_Name
PET

The PET column has a check constraint; hence it only accepts three values DOG, CAT, FISH.
Sample Data
+-------+-------------+-----------+----------+
| SR_ID |  First_Name | Last_Name |   PET    |
+-------+-------------+-----------+----------+
|     1 |  JOHN       |      D.   |   DOG    |
|     2 |  MIKE       |      S.   |   CAT    |
|     3 |  MARIO      |      M.   |   CAT    |
|     4 |  MICHEAL    |      T.   |   CAT    |
+-------+-------------+-----------+----------+

How would the admin check (via a query) which values are accepted for the column PET?
Some Background
By running the following query:
Select PET, count(PET) from Animal group by PET

I get the following result:

+-----+------------+
| PET | count(PET) |
+-----+------------+
| CAT |          3 |
| DOG |          1 |
+-----+------------+

The admin only gets DOG and CAT values. It is obvious that FISH won't be found since it is not in the database. How can we know that PET column accepts the value FISH?
DESCRIBE Animal would just list column names and its datatype - not the values accepted.


texts: Installing SQL Server Integration Services on Server Core

texts:
I have Windows Server 2008 R2 Core on virtual machine and want to install SQL Server Integration Services. On the D:/ drive SQL Server 2014 Enterprise .iso is mount.

I am reading a book where it is said that:

  You can use the Setup.exe command-line utility to add SSIS to an
  existing installation on both traditional and Server Core deployments
  of SQL Server 2012. For example, to add the SSIS feature to the
  default MSSQLSERVER instance, run the following command:

Setup.exe /qs /Action=Install /Features=IS /InstanceName=MSSQLServer
/IAcceptSQLServerLicenseTerms

but I am getting the following errors:

Could anyone tell what I am doing wrong?


texts: Reporting on a normalized Key-Value pivot

texts:
ERD & SQL Fiddle provided. We're trying to design a reporting tool for our stakeholders around a normalized Key-Value Pair model. We've tried to find a solution using Pivot Tables but can't seem to find a way to accomplish a query in SQL Server.

http://sqlfiddle.com/#!2/6ed2f (SQL Fiddle's server is having some issues.)

Metaphor for the business problem: we have a collection of surveys that collect responses from users. 1 survey has many questions, 1 question has many responses, each of the responses is from a user, and the user has many responses.

A survey can also generate an output of the surveying experience. Each survey has different output keys. 1 survey has many output keys, 1 output key has many values, and each output value is associated with a user.

The input/output values and other data is tied together to the user through a "Survey Submission".

(Random fact: output values and response/input values are both stored as JSON strings to preserve a dynamic data type.)

The stakeholder would like to see the aggregated values for a given survey.

A solution for just the Inputs would be fine, although a solution that works for Inputs and/or Outputs would be great.


texts: Should I add relation id on parent or child table for one to one relation

texts:
Lets say I have 2 tables product and product_detail they both share one 2 one relation and product_detail can be null so should I add relation id in parent table like this?

CREATE TABLE `product` (
   product_id INT(11) PRIMARY KEY auto_increment,
   ...
   product_detail_id INT(11) DEFAULT NULL,

   FOREIGN KEY (product_detail_id) REFERENCES product_detail(product_detail_id)
);

CREATE TABLE `product_detail` (
   product_detail_id INT(11) PRIMARY KEY auto_increment,
   ...
);

Or should I add it in child table like this

CREATE TABLE `product` (
   product_id INT(11) PRIMARY KEY auto_increment,
   ...
);

CREATE TABLE `product_detail` (
   product_detail_id INT(11) PRIMARY KEY auto_increment,
   ...
   product_id INT(11) NOT NULL,

   FOREIGN KEY (product_id ) REFERENCES product(product_id)
);


texts: Availability Groups using Multi-Subnet Clustering: Preferred Owners for Roles and Possible Owners for AG Listener IPs

texts:
We use a SQL Server 2016 Availability Group (AG) on a Windows Server 2012 multi-subnet failover cluster for HA/DR. There are two nodes in our New York datacenter (10.7.x.x subnet) and one node in our Colorado datacenter (10.8.x.x subnet). The Colorado server is primarily for Disaster Recovery or extended maintenance where NY is offline, so we currently set the Quorum NodeWeight/Votes for the CO-SQL01 node to zero to prevent it from automatically taking ownership of the Cluster or AG if there are any issues.

My question is this: should we change the default settings in the Failover Cluster Manager, specifically the Preferred Owners of the AG role and the Possible Owners of the AG listener IP resources? The defaults that are used seem to conflict with our goals of High Availability using the two nodes in NY and only using the CO node for Disaster Recovery.

Here are the default settings for the Preferred Owners of our Multi-Subnet AG:

  Should we add a check next to NY-SQL02 and move it above CO-SQL01 so
  that it is preferred? What about other roles, can we set the preferred owners of the core cluster resources (like the cluster name)?

And here are the default settings for the SQL-StackOverflow_AG IP resources:

  Should we remove the check marks next to the servers that are in a different subnet than that IP address?

This question came up on a recent office hours, but it is something we though might help prevent downtime when our cluster has issues. We recently had a 5 minute outage when we replaced our CO-SQL01 server hardware and it was added back to the failover cluster (but not the AG) without removing it's vote. The CO-SQL01 server then experienced a hard crash (we think it was an NVMe/PCIe driver bug under heavy load) and managed to take down the AG with it (we think CO-SQL01 took ownership of the core cluster resources when it came back online).

To be honest we have had a number of unexpected issues with using Multi-Subnet Failover Cluster Availability Groups, and it seems like the default preferred role owners and possible resource owners may be incorrect or at least not optimal for our scenario. We currently are looking at using the new Distributed Availability Groups feature in SQL Server 2016 to split our Multi-Subnet AG into to two single subnet AGs (one for each datacenter) as a way to prevent these issues in the future. We also think that will allow us to upgrade the cluster OS with minimal downtime.


texts:
I am currently having a problem with the following
Creating a merge replication is simple if the servers are on the same network, i would like to know how can i do a merge replication if the servers are on seperate networks
I am using SQL 2008 R2


texts: TLS1.0 disabled and 3rd party OLE DB use

texts:
I'm provisioning a new server as part of which we have disabled TLS 1.0 and installed SQL Server Express 2016. I've then attempted to install a 3rd party application that is going to install a website and supporting DB etc. The first thing it does is test the connection to SQL and it returns the following error:

  Connection Problem: [DBNETLIB][ConnectionOpen
  (SECDoClientHandshake()).]SSL Security error

To attempt to work out the issue I created a UDL file on my desktop and reproduced this error when using the Provider Microsoft OLE DB Provider for SQL Server.  The test worked fine for provider SQL Server Native Client 11.0 and I can also connect with SSMS.

Is there anyway I can force the 3rd party installer to use a different provider?
Is there anyway I can log what type of connection it is attempting?


texts: Postgresql Backup all databases seperate files

texts:
I need to create an automatic backup of all databases from PostgreSQL ( running on a Windows machine ).
I need to know if is possible to get backup of all databases but on separate files?
Using pg_dumpall creates a script that has all databases on it. But i need each database on a separate file


texts: MySQL: Show Unique key definition by name

texts:
I get the following error:

ERROR 1062 (23000) at line 1901: Duplicate entry '3-3820-50-0' for key
 'UNQ_CAT_CTGR_ENTT_DTIME_ENTT_TYPE_ID_ENTT_ID_ATTR_ID_STORE_ID'

How can I find out which table and columns this is referring to?


texts: Optimising a sql correlated subquery statement

texts:
I am updating a column on tableA with data from tableB like this:

update tableA
  set bKey = select bKey from tableB B where B.colB = tableA.colB

bKey has a unique index on tableB.

Which is the better optimisation for this query, indexing tableB on ColB or (ColB,bKey) and why?

I am interested in answers for any database.


texts: database and table design for billions of rows of data

texts:
basically i got 2 tables: header, details tables.

CREATE TABLE `header` (
 `ID` int(11) NOT NULL AUTO_INCREMENT,
 `RECORD_DATE` datetime DEFAULT NULL,
 `TICKER_ID` int(11) DEFAULT NULL,
 `CURR_TIMESTAMP` datetime DEFAULT NULL,
 PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

CREATE TABLE `detail` (
 `ID` int(11) NOT NULL AUTO_INCREMENT,
 `HEADER_ID` int(11) DEFAULT NULL,
 `BROKER_ID` int(11) DEFAULT NULL,
 `AMOUNT` decimal(26,0) DEFAULT NULL,
 PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

There're about 4800 ticker need to be update everyday, ticker_id (A00001, B00032...) and each ticker_id contain numbers of record everyday and i store in detail table

it works fine at the beginning, after years, header become 2.4 million row and detail table got 250 millions row, its take an hour to with simple select,

SELECT h.ticker_id, h.record_date , d.broker_id, d.broker_id, d.amount
FROM DETAIL
INNER JOIN herder h
    ON h.id = d.header_id
where h.ticker = 'A00001'

so i create a 'link' table to join reference them together.

CREATE TABLE `linkA00001` (
 `ID` int(11) NOT NULL AUTO_INCREMENT,
 `HEADER_ID` int(11) DEFAULT NULL,
 `DETAIL_ID` int(11) DEFAULT NULL,
 PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

and the result become

SELECT h.ticker_id, h.record_date , d.broker_id, d.broker_id, d.amount
FROM DETAIL
INNER JOIN linkA00001 l
INNER JOIN herder h
    ON h.id = l.header_id and d.id = l.detail_id

its stunning fast, from hour to few seconds, but this approach solve 1 ticker selection only, so i create 4800 link table for better performance, its do solve the selection problem, but i using java spring for my application, building over 4800 entity require 10 minutes to start the application and very hard to maintain the link table.

so my question is:

this approach works?
if not, how can i improve or just redesign this?
according to this link, its require timestamp but i never insert at detail table, is it good practices add back?

Thank you very much!


texts:
When I query a table in my database, I am getting the error message as below.

  Msg 596, Level 21, State 1, Line 0 Cannot continue the execution
  because the session is in the kill state. Msg 0, Level 20, State 0,
  Line 0 A severe error occurred on the current command.  The results,
  if any, should be discarded.

During a scheduled task on the server, server got restarted because of an Windows Update and database status was set to 'Suspect'. We have repaired the database with data loss option using DBCC and the repair terminated with an error. All other tables in the database I can access.

SSMS Version

Microsoft SQL Server Management Studio                      12.0.5000.0
Microsoft Analysis Services Client Tools                        12.0.5000.0
Microsoft Data Access Components (MDAC)                     6.1.7601.17514
Microsoft MSXML                     3.0 4.0 6.0
Microsoft Internet Explorer                     9.11.9600.18449
Microsoft .NET Framework                        4.0.30319.34209
Operating System                        6.1.7601

With SP2

Please help me to resolve the issue. I have transaction level backup. The data in those tables are static and I can load fresh data anytime. I am not worried about data in this table. I cannot even query this table. All the queries which refer this table has this issue. I have tried to drop this table but same error, tried to truncate same.

Below are the contents from dump file:

SqlDumpExceptionHandler: Process 80 generated fatal exceptionc0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.*
* BEGIN STACK DUMP:
*   09/18/16 05:55:06 spid 80*
Exception Address = 000007FEF57BC6A4 Module(sqlmin+00000000000FC6A4)
*   Exception Code    = c0000005 EXCEPTION_ACCESS_VIOLATION *   Access Violation occurred reading address 0000000000000000


texts: Incorrect syntax near ')' when executing multiple select statement

texts:
Incorrect syntax near ')'

How do I execute only one select statement based on user input i.e @id. Here is my syntax.

Create Procedure employee
@id int,
@year int
AS

SELECT CASE WHEN @id = 1 THEN

(select a.name, b.roles from employee a
INNER JOIN department b ON a.id = b.id
where b.years IN (@year)
OR b.roles IS NOT NULL  )

ELSE CASE WHEN @id = 2 THEN

(select a.name, b.dayoff from employee a
INNER JOIN department b ON a.id = b.id
where b.years IN (@year)
OR b.dayoff IS NOT NULL  )


texts:
I am a SQL Server DBA.  I maintain a large number of SSIS packages created by developers.

Over the weekend, many SSIS jobs failed since the jobs were attempting to insert/update values that resulted in duplicate data / key violations.

Normally, I would go through the execution report, and send a screen shot to the concerned developer. How can I create an automated process that will send an email to them about the error on packages as they occur?


texts: DELETE ... IN hangs after deleting half of a large table (even for deleting 10 rows)

texts:
I'm having trouble with a DELETE hanging, even for a small number of rows (10) in a large MariaDB InnoDB table.

Background

I had a large table (~400,000,000 rows) that needed to have rows archived and removed.  After removing about half of the rows, my archive process started to hang on the DELETE statement.  I took the time to run the (painfully slow) 11-hour OPTIMIZE (ALTER for InnoDB) query to reclaim disk space and hopefully unblock the DELETE process.  This worked until I deleted about half of the remaining rows (down to about 106m out of 200m rows).  I figure I could keep running OPTIMIZE on my giant table to let me remove another 1/2 of the rows, but there must be a better way...

Note that the table does get a lot of INSERT/UPDATE traffic, 1000s of changes per minute.

Archive Process

To simplify the archive script, I put the whole process into a stored procedure.  The steps in that procedure are:

Flatten and copy up to N rows from the live DB to the archive DB, where the rows are older than 1 month
Make a list of archived IDs that are probably in the live DB.
Store these in a temporary table A
Filter temp table A to only include row IDs that are actually in the live DB.
Store these in a temporary table B
DELETE FROM live_db.test_table WHERE test_id IN (SELECT * FROM temp_table_B) ORDER BY test_id ASC LIMIT N;

Now that the table is half empty, step 4 is hanging, even for N = 10
Before the table was half-empty, it worked fine and N = 1000 took ~ 1 sec

Diagnostics

Adding an EXPLAIN in the stored procedure lets me know MySQL is planning on using the PRIMARY index for the DELETE, as I hoped it would:

id select_type         table          type   possible_keys  key      key_len  ref   rows     Extra
1  PRIMARY             test_runs      index  NULL           PRIMARY  4        NULL  10       Using where
8  DEPENDENT SUBQUERY  tests_to_drop  ALL    NULL           NULL     NULL     NULL  1        Using where

Output selected from SHOW ENGINE INNODB STATUS (some things redacted):

---TRANSACTION 6457899980, ACTIVE 14 sec starting index read, thread declared inside InnoDB 3008
mysql tables in use 2, locked 2
242717 lock struct(s), heap size 25507368, 5532268 row lock(s)
MySQL thread id 1834147, OS thread handle 0x7f0f68c4a700, query id 204328569 1.2.3.4 user123 Sending data
DELETE FROM live_db.test_table
WHERE test_id IN (SELECT * FROM temp_table_B)
ORDER BY test_id ASC
LIMIT 10
Trx #rec lock waits 0 #table lock waits 0
Trx total rec lock wait time 0 SEC
Trx total table lock wait time 0 SEC

(From Comment)

CREATE  TEMPORARY TABLE temp_table_B (
        test_id int(10) unsigned NOT NULL DEFAULT '0'
    ) ENGINE=InnoDB DEFAULT CHARSET=latin1


texts: DAC connection to sql instance not working

texts:
I have configured one remote sql server with 2 instances A,B. I have enabled TCP/IP,Port 1434, remote connections, sql browser running and firewall inbound rule. I've enable DAC using sp_configure 'remote admin connections', 1; and reconfigure. However when I try to connect via SSMS or SQLCMD using admin:hostmachine\sqlinstance or sqlcmd -S hostmachine\sqlinstance -U sa  -P pass -d master -A I'm getting the following error :
A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 43 - An error occurred while obtaining the dedicated administrator connection (DAC) port. Make sure that SQL Browser is running, or check the error log for the port number) (Microsoft SQL Server, Error: -1)
I can connect using DAC but only as admin:hostmachine or sqlcmd -S hostmachine -U sa  -P pass -d master -A


texts: What will happen to SQL Service Account when SQL Server is uninstalled?

texts:
We are planning to downgrade from Enterprise/Standard version of SQL Server to Developer Edition. We will move the existing system and user database to the Dev Edition.

Are we going to need to request for a new Service Account to be created for the SQL Services of the Developer Edition? The existing account was created by the previous DBA. The account details were not shared with us.

We are looking into two options that reuse the old account, but we will ask the security team to reset the password, or if it will be deleted when we downgrade we will request new accounts.


texts: Database Mirror - Host can not be reached or does not exist

texts:
I tried mirroring database with GUI and T-SQL, but I had problems in both cases.

Here is some information about infrastructure and SSMS:

INFRASTRUCTURE

Principal server with SQL Server 2017 Enterprise Edition
Mirror server with SQL Server 2017 Enterprise Edition
Witness server with SQL Server 2017 Express Edition
All servers S.O.: Windows Server 2016 Standard
All servers with firewall disabled
All servers in common domain
All servers has same users and privileges
Telnet works perfectly with port 5022 in all servers
I created user to manage service in all servers with GRANT CONNECT ON ENDPOINT::Mirroring

SQL Server Management Studio Info - SSMS:

Microsoft SQL Server Management Studio                      14.0.17289.0
Microsoft Analysis Services Client Tools                    14.0.1016.283
Microsoft Data Access Components (MDAC)                     10.0.14393.0
Microsoft MSXML                                             3.0 6.0
Microsoft Internet Explorer                                 9.11.14393.0
Microsoft .NET Framework                                    4.0.30319.42000
Operating System                                            6.3.14393

I'll explain both error cases, with GUI and T-SQL.

With GUI:

Success configuration endpoints in all servers

But I had this error when I tried start mirroring

It's so confusing because the database must be in recovery status in mirror server. But I read some posts about this and is recommended configure with T-SQL, so

WITH T-SQL:

With T-SQL I had this problem:

  Msg 1418, Level 16, State 1, Line 54 The server network address
  "TCP://HML-SQLS-01-TST.domain.br:5022" can not be reached or does not exist. Check
  the network address name and that the ports for the local and remote
  endpoints are operational.

Let me explain step-by-step I had followed:

PRINCIPAL SERVER

CREATE DATABASE mirror
GO

ALTER DATABASE mirror
SET RECOVERY FULL
GO

USE mirror
GO

CREATE TABLE TESTE(
    id INT IDENTITY(1,1),
    descricao VARCHAR(255)
);

BACKUP DATABASE mirror
TO DISK = 'F:\Backup\mirror-full.bak'
GO

BACKUP LOG mirror
TO DISK = 'F:\Backup\mirror-log.bak'
GO

CREATE ENDPOINT Mirroring
    STATE = started
    AS TCP (listener_port=5022)
    FOR database_mirroring (role=partner)
GO

USE MASTER
GO
CREATE LOGIN [DOMAIN\svc.witness] FROM WINDOWS
GO
GRANT CONNECT ON ENDPOINT::Mirroring TO [DOMAIN\svc.witness];
GO

MIRROR SERVER

CREATE ENDPOINT Mirroring
    STATE = started
    AS TCP (listener_port=5022)
    FOR database_mirroring (role=partner)
GO

RESTORE DATABASE mirror
FROM DISK = 'F:\Backup\mirror-full.bak'
WITH NORECOVERY
GO

RESTORE DATABASE mirror
FROM DISK = 'F:\Backup\mirror-log.bak'
WITH NORECOVERY
GO

USE MASTER
GO
CREATE LOGIN [DOMAIN\svc.witness] FROM WINDOWS
GO
GRANT CONNECT ON ENDPOINT::Mirroring TO [DOMAIN\svc.witness];
GO

WITNESS SERVER

create endpoint Mirroring
state = started
    as tcp (listener_port=5022)
    for database_mirroring (role=WITNESS)
go

USE MASTER
GO
CREATE LOGIN [DOMAIN\svc.witness] FROM WINDOWS
GO
GRANT CONNECT ON ENDPOINT::Mirroring TO [DOMAIN\svc.witness];
GO

MIRROR SERVER

alter database mirror
set partner = 'TCP://HML-SQLS-01.domain.br:5022'
GO

PRINCIPAL SERVER (The error pops in this execution)

--The error pops in this execution:
alter database mirror
set partner = 'TCP://HML-SQLS-01-TST.domain.br:5022'
GO

  Msg 1418, Level 16, State 1, Line 54 The server network address
  "TCP://HML-SQLS-01-TST.domain.br:5022" can not be reached or does not exist. Check
  the network address name and that the ports for the local and remote
  endpoints are operational.

But if I try this command in CMD, its work perfectly:

telnet hml-sqls-01-tst.domain.br 5022

ENDPOINTS IN SERVERS:

Principal server endpoint

Mirror server endpoint

Witness server endpoint

Has anyone been through this or could suggest a way forward?


texts: EMC Legato NSR_DATA_VOLUME_POOL in Rman script

texts:
How to put the NSR_DATA_VOLUME_POOL parameter in rman in case this parameter has spaces, I mean that I need to put NSR_DATA_VOLUME_POOL = Pool Oracle but it throws an error however with PoolOra it works, the case is that I need to know how to put the parameter separated with space. I tried 'Pool Oracle' and "Pool Oracle" but neither works.
Here is an example script from  Legato NetWorker, Module for Oracle, Administrator’s Guide, Releases 3.0 and 3.1, p.79
run {
    allocate channel t1 type 'SBT_TAPE'
    parms 'ENV=(NSR_SERVER=mars.legato.com,
    NSR_DATA_VOLUME_POOL=MondayFulls)';
    allocate channel t2 type 'SBT_TAPE’
    parms 'ENV=(NSR_SERVER=mars.legato.com,
    NSR_DATA_VOLUME_POOL=MondayFulls)';
    backup full filesperset 4
    format 'FULL_%d_%u'
    (database);
    release channel t1;
    release channel t2;
}


texts: Is this an excessively large 'Lock' time and is it indicative of a problem?

texts:
Given:

Production environment
Application Servers using Hibernate
SQL Server Studio Manager v17.5
SQL Server 2016 in a clustered HAG setup
SQL Servers do NOT have the Query Store feature enabled
The author of this question is a software engineer with enough SQL
Server knowledge to be categorized as mostly harmless

update 1

Database growth settings: Unlimited, 1024000 KB, data only
instant_file_initialization_enabled - Yes
is_auto_update_stats_async_on - No

update 2

The server has 4 CPU cores
There are spikes of waiting tasks of over 3,000,000.  I have no idea yet what they are. This must be the reason for the large 'lock' times.
These spikes occur every 10 or 15 seconds.  I have the following graph updating once a second:

The problem:
The root problem is that at seemingly random times of a busy day a couple of SQL queries timeout, however, for the purposes of this question I am interested in whether the screen grab is indicative of a problem in itself. Perhaps this is to subjective, but I have no experience with this value.
Action:
The failures themselves do not point directly to a concrete issue and therefore I am currently gathering evidence and attempting a process of elimination where possible.
Currently I am investigating whether excessive wait times and a 'perfect storm' of queries could cause a cascade of locks and thus a query timeout.
Evidence Gathered:

Several queries are resulting in either full index scans or full
table scans.
Several screen grabs with execution plans showing table scans.
Cursory inspection shows that indexes do exist - yet not used. I might be able to sanitize the screen grabs if they will prove useful.
The screen grab below showing a large wait time.

Question:
What other information would help determine if locking and wait times might be the cause of the query timeouts? For example, I have the following screen grab from sql server studio manager activity monitor. The value looked surprising to me.


texts:
I recently got my first taste of the error "Heterogeneous queries require the ANSI_NULLS and ANSI_WARNINGS options to be set for the connection." which I hadn't come across. Nearly every resource I found on this site and elsewhere dealt with this occurring in a stored procedure.

My specific scenario is a Vendor supplied app and DB, I can change the DB but not the app. I created a view on the DB to another server (via a linked server) but when I attempted to use this in the app I got the aforementioned error. I used SQL Profiler to prove the app opens its connections with ANSI NULLs and WARNINGS OFF. I used SSMS to prove the link works with the default values there. But unlike an SP I can not set ANSI WARNINGS on a view.

I set ANSI WARNINGS on the DB as a test, the app now worked, but I couldn't afford a full regression test of the app so this solution does not work. I also tried many different ways of implementing the linked server including created an ODBC connection on the server and then using that. But it's like the app is explicitly setting ANSI WARNINGS to off.

Not sure if matters but the source server is SQL2000 and the remote server was SQL2008R2.


texts: postgresql - how and why indexes are bigger than their tables

texts:
I'm using postgresql 9.3 and trying to understand how and why indexes are bigger than their tables.

Sample output:

 database_name | database_size |                          table_name                          | table_size | indexes_size | total_size
---------------+---------------+--------------------------------------------------------------+------------+--------------+------------
 foo_12345 | 412 MB        | "foobar_dev_12345"."fact_mobile_sends"                       | 57 MB      | 131 MB       | 189 MB
 foo_12345 | 412 MB        | "foobar_dev_12345"."fact_mobile_started"                      | 17 MB      | 39 MB        | 56 MB
 foo_12345 | 412 MB        | "foobar_dev_12345"."fact_mobile_stopped"                      | 16 MB      | 35 MB        | 51 MB

I'm running the following query to get the table and index sizes.

SELECT
    table_catalog AS database_name,
    pg_size_pretty(pg_database_size(current_database())) As database_size,
    table_name,
    pg_size_pretty(table_size) AS table_size,
    pg_size_pretty(indexes_size) AS indexes_size,
    pg_size_pretty(total_size) AS total_size
FROM (
    SELECT
        table_catalog,
        pg_database_size(current_database()) AS database_size,
        table_name,
        pg_table_size(table_name) AS table_size,
        pg_indexes_size(table_name) AS indexes_size,
        pg_total_relation_size(table_name) AS total_size
    FROM (
        SELECT ('"' || table_schema || '"."' || table_name || '"') AS table_name, table_catalog
        FROM information_schema.tables
    ) AS all_tables
    ORDER BY total_size DESC
) AS pretty_sizes;

Is my query correct? What would cause indexes to be bigger?


texts: Is it possible to store historical configuration settings for each row of data without cramming all the configuration settings into each row of data?

texts:
For background: I was recently hired as a database engineer for a water treatment company. We deploy water treatment machines to sites across the country, and the machines treat water and send continuous data back to us regarding the state of incoming water (flow rate, temperature, concentration of X in incoming water, etc.), and regarding the treatments the machine applied to that water at that point in time. Over time, sites (and their various components) change a lot: a machine might break down and need to be replaced, a different concentration of chemical may be used to fill the machine's tanks, its flow meters and other sensors might be recalibrated or set to scale differently, its chemical pumps might be replaced, and on and on. These affect the interpretation of the data: for example, if 5 mL of chlorine was added to the incoming water at 01/01/2021 12:00:05, that means two completely different things if the chlorine was 5% concentrated or 40% concentrated.
Water treatment datapoints are identified by a composite key consisting of the ID of the site, and a timestamp. It would be easy to store configurations if the only data that mattered was current data, as I could keep configuration settings in the Site table and pull them up for datapoints as needed. But we need to be able to correctly interpret older data. So, I thought about storing configurations in its own table table, which would track all the settings for each site over each time period, but it's not possible to create a foreign key between the continuous timestamps of the datapoints and the start/end dates of the configurations - the closest thing would be some kind of range check, like "Datapoint.TimeStamp BETWEEN Configuration.Start AND Configuration.End". So the only other option I see is to store every configuration setting for every datapoint alongside each datapoint, but that seems like a terrible solution given how many configuration settings there are and how many datapoints are generated, especially since most of the settings don't even change often.
So, is there a way to store historical configurations for each row of data in a way that is at all normalized, or is the only possible solution to cram all the settings into each datapoint?


texts:
I backed up a 2008 R2 database and copied to a server with a 2016 SQL Server. I tried using the restore database dialog but it does not seem to want to work. When I use the dialog I have the following:

The drop down for Database is empty. Also when I grabbed that sibk file the open dialog was looking for *.bak, *.tm and *.log and the backup created in 2008 is none of those. So I chose All Files. Not sure that's a clue to the problem.


texts: IF else condition on DATE to determine String in other column

texts:
I have 2 columns : a DATE column and a STRING column. String column contains 'NEW' and 'OLD' distinct data.

How do I right a conditional query:

if DATE is three days old from today
 Then NEW should be 'NO CHANGE'
else
 OLD should be 'CHANGE'

meaning if it is not older than 3 days then OLD.

NEW = 'CHANGE', OLD = 'NO CHANGE' like you will use in a CASE statement but how can I account for the date condition to drive the choice?


texts:
I want to create VIEW using WITH clauses, but really can't find any references on correct syntax.

I want smth like this

WITH TempTbl AS (SELECT ...)
CREATE VIEW SomeView
SELECT *
FROM TempTbl

And what is the correct syntax for using several WITH clauses?

Nothing useful on MSDN :(


texts: How to assign GUID to batches of data based on row counts and tunable batch size

texts:
I need to assign batches of rows a common GUID id (for consumption by an external process).
Below is a simplified setup describing what I'm looking for:
BASE_TABLE represents one of pre-existing tables in this scenario, much simplified obviously.  Values in the "real" table should be considered random; they're not consecutive in real life (even the ID is not really an int)
CREATE TABLE BASE_TABLE (
    ID int
    ,VALUE1 varchar(10)
    ,VALUE2 varchar(255)
)

TARGET_TABLE represents the "output" of the process I'm working on, which is just the input to something else that requires batches of data to have a uniqueIdentifier BATCHID for each batch.  Batches in real life are ~1000 rows each.
CREATE TABLE TARGET_TABLE
(
  ID int
  ,VALUE1 varchar(10)
  ,VALUE2 varchar(255)
  ,BATCHID uniqueIdentifier
)

(Add some dummy data)
DECLARE @DATA1 int
set @DATA1=0
WHILE @DATA1<100
BEGIN
  INSERT INTO BASE_TABLE (ID,VALUE1,VALUE2) VALUES (@DATA1,'v1'+CONVERT(varchar,@DATA1), 'v2'+CONVERT(varchar,@DATA1))
  SET @DATA1=@DATA1+1
END

/**
example DESIRED RESULT, with batch size of 5

ID
VALUE1
VALUE2
BATCHID

0
v10
v20
38B1B4FB-7F1E-44FD-9336-19095C01C629

1
v11
v21
38B1B4FB-7F1E-44FD-9336-19095C01C629

2
v12
v22
38B1B4FB-7F1E-44FD-9336-19095C01C629

3
v13
v23
38B1B4FB-7F1E-44FD-9336-19095C01C629

4
v14
v24
38B1B4FB-7F1E-44FD-9336-19095C01C629

5
v15
v25
41122454-A743-4545-8F0C-D7B461E072AE

6
v16
v26
41122454-A743-4545-8F0C-D7B461E072AE

7
v17
v27
41122454-A743-4545-8F0C-D7B461E072AE

8
v18
v28
41122454-A743-4545-8F0C-D7B461E072AE

9
v19
v29
41122454-A743-4545-8F0C-D7B461E072AE

10
v110
v210
41122454-A743-4545-8F0C-D7B461E072AE

11
v111
v211
FBDE5513-C869-4F2D-AC4D-40CBEF4A2D48

etc
Important result is that for each batch of size N there are N rows with the same BatchID, which must be a GUID.
I'm pretty sure I could do what I want with a cursor, but I want to do something like this if possible:
select
ID,VALUE1,VALUE2,
NEWID() OVER(PARTITION BY ROW_NUMBER() OVER (ORDER BY ID)) AS BatchID
from BASE_TABLE

but it's not valid because NEWID() isn't an aggregate function.


texts: Custom index maintenance percentages for a very large table

texts:
I'm using Microsoft SQL Server 2016 (SP2-CU12) (KB4536648) - 13.0.5698.0 (X64)   Feb 15 2020 01:47:30   Copyright (c) Microsoft Corporation  Standard Edition (64-bit) on Windows Server 2012 R2 Standard 6.3  (Build 9600: )
I have some very large tables (the tables by themselves can be more than 700gb) on a database that's 2.5 terabytes.
I've been using some scripts for index maintenance.
Right now I'm doing :

Reorg if fragmentation is > 10%
Rebuild if fragmentation is > 50%.

It works fine most of the time but for my tables that are 700gb or larger, I think that 10% will be a hard percentage to hit. I was wondering if I should lower that percentage for those big tables or if it's fine to leave it at that threshold.
I have to add that my servers have SSD drivers, but only 128 gb of RAM because it's Standard edition. So there's no way that the tables can fit in RAM, in case of large queries (like reporting), it has to scan/seek the indexes.
I don't want to hit the 665 error file system limitation due to fragmentation.
https://docs.microsoft.com/en-ie/troubleshoot/sql/admin/1450-and-665-errors-running-dbcc
(I'm starting to add filegroups to the database to avoid that error, as I'm having it once in a while on those large tables that are part of the primary file group.)
So should I do a custom percentage for index maintenance on very large tables?
Or it should be fine?


texts:
I will start by mentioning that I am very new to databases. I am working in MySQL.
Can anyone give me a layman's view of when you would use which settings for the foreign key options so when you would use On Delete: Cascade, On Delete: Set Null, On Delete: No Action. Also, how would these work in the sense of foreign keys in a bridging table, does it depend on the context of the database as in what data it contains or is there a general rule of thumb as to which setting is best in this type of relationship?
If you can, please state it simply as I am not totally au fait with the jargon given I am a relative newbie. Thanks so much


texts: Seemingly simple query...excruciating response time

texts:
I am in the process of running what seems to be a simple query and have yet to receive a result set. The purpose of the query is to obtain a list of all records within a table that share the same PostalCode. The table has roughly 75K rows. The PostalCode column is an indexed varchar (I had considered making this table an integer type but wanted to keep our options open for countries that use letters within their postalcodes). The query has been running about 10 minutes now and has yet to return a result. Have I done something horribly wrong???

SELECT * FROM t502locations AS t502
JOIN(

    SELECT PostalCode FROM t502locations
    GROUP BY PostalCode
    HAVING(COUNT(PostalCode) > 1)

) AS vt502

ON t502.PostalCode = vt502.PostalCode


texts: How to see where the default database is configured for a particular username or login name?

texts:
Once I connect to a database server without specifying a particular database, e.g. with:

sqsh -Usomeuser -Ssomeserver

… I can do a:

select db_name();

… to see the default database for this user. But where is this information kept and / or how can I see the default databases configured for every user name or login name?


texts: How can I append a new datafile in an existing InnoDB tablespace?

texts: I want to add a new data file in an existing InnoDB tablespace so that entry of data is smooth in case it hits its 4GB limit.


texts: Ms-access ODBC calls for linked table behaviour

texts:
I'm curious if anyone knows how ms-access goes about generating ODBC calls for linked tables.

The reason I ask is that I'm trying to make the front-end of a system super efficient and too minimise the number of ODBC calls. I'm using MySql and have been performing some tests and looking at the general log from the MySql server. I was surprised at how many calls were made by the front-end.

a very simple example

Create the following test data in MySQL:

CREATE TABLE table1 (table1_id int(11) NOT NULL,
    column1 int(11) DEFAULT NULL,
    column2 int(11) DEFAULT NULL,
    column3 int(11) DEFAULT NULL,
    PRIMARY KEY (table1_id)
) ENGINE=InnoDB;

INSERT INTO sales.table1 (table1_id) VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11);

Now create a linked table in ms-access to table1 and open it. This is what appears in the general log file:

2011-05-29 16:58:00, SELECT `table1`.`table1_id` FROM `table1`
2011-05-29 16:58:00, SELECT `table1_id`,`column1`,`column2`,`column3`  FROM `table1`  WHERE `table1_id` = 1
2011-05-29 16:58:00, SELECT `table1_id`,`column1`,`column2`,`column3`  FROM `table1`  WHERE `table1_id` = 1 OR `table1_id` = 2 OR `table1_id` = 3 OR `table1_id` = 4 OR `table1_id` = 5 OR `table1_id` = 6 OR `table1_id` = 7 OR `table1_id` = 8 OR `table1_id` = 9 OR `table1_id` = 10
2011-05-29 16:58:00, SELECT `table1_id`,`column1`,`column2`,`column3`  FROM `table1`  WHERE `table1_id` = 11 OR `table1_id` = 11 OR `table1_id` = 11 OR `table1_id` = 11 OR `table1_id` = 11 OR `table1_id` = 11 OR `table1_id` = 11 OR `table1_id` = 11 OR `table1_id` = 11 OR `table1_id` = 11

Rather than retrieve all the data in one hit which I could do with a stored procedure it seems to require 4 steps:

1) get a list of the primary keys from table1
2) Request the 1st 10 records by explicitly selecting them in the where clause.
3) Retrieve the next 10 records by explicitly selecting them in the where clause.
4) Because the 1st record has the focus upon opening the table, requery the 1st record again

Notice the odd behavior in step 3 where the criteria table1_id=11 is repeated 10 times.


texts: Would it be good practice to represent MongoDB ObjectId as a String or a Blob type in SQL Lite (RDMS world)?

texts:
All:

We have a software application with

1) Web-based component
2) Mobile-based component

Our 1) Web-based component uses

a) MongoDB as the Database

b) ASP.NET C#

Our 2) Mobile-based component uses

a) Java

b) SQL Lite

However, both the databases have the same tables, but we have Not created any sort of bridge table between the collections in MongoDB and the tables in the SQL Lite.

The MongoDB Database and the SQL Lite will have the following collections/tables

-Daily Schedule ( corresponds to entire Day ) (MongoDB will have a unique ObjectId for daily schedule)

-Daily Schedule ( corresponds to entire Day ) ( SQLLite will use TEXT as ISO8601 strings ("YYYY-MM-DD HH:MM:SS.SSS") )

-Task ( many tasks to One Daily Schedule ) (MongoDB will have a unique ObjectId for every Task )

-Task ( many tasks to One Daily Schedule ) ( GUID type has affinity NONE, SQL Liste will store it store it as BLOB fields.)

In any sort of Mobile/Web combo application, how would they synchronize the data between the 2 databases? I'm looking for typical solutions that the software industry uses.

Some C# programmers on Stackoverflow[dot]com mentioned that it might be good idea to specify the ObjectId as a String or as a BLOB type.

It just gave me an thought, would it be a a good idea Mobile-based component which uses SQL Lite to have some bridge table that contains an ObjectId as String or a Blob type so that they can synchronize the data between the Mobile-based SQL Lite database, and the Web-based MongoDB Database?

Would the aforementioned approach have any disadvantages? If yes, please tell me the disadvantages.


texts: Communication between two database servers

texts:
I have 2 databases running on different virtual servers. I have some database processes running on one node. These processes connect to Database Gateway (PG4MQ) and process the messages.

I need to start the process manually. I need to write a script that will monitor the node on which the processes are running and if the node is down or not running then these process will automatically start on the other node.

Can someone tell me is this feasible or not, if feasible then how can this be achieved?

Databases installed on RHEL 5.8. OEM, Grid, DBCA and Oracle Net services are not available.


texts:
I am trying to track down the root cause of this issue I keep seeing.

We have SSRS running with a custom URL like https://reporting.companyname.com/reports.

When I connect to that address I can login and see reports.  If I progress to a report and click manage I get the report detail and the list of properties on the side screen.

Now if I click on data source it gives me a failure to load page and I notice that the URL in the address bar is http://reporting.companyname.com/reports .

If I put an "s" in that URL the page loads.  I am trying to figure out how I can fix this.

Any ideas would be helpful.

SSRS 2008 R2 enterprise edition


texts:
We have a database on a SQL Server 2014 instance. We have another on a 2008R2 instance. Both on the same server.

I have created a linked server on the 2008R2 instance to the 2014 one.
However, I am only able to see tables & views.

Is it possible to call stored procedures and functions in the 2014 instance using the linked server?

If not what is my best/simplest solution?


texts:
We faced Oracle deadlock (ORA-00060) problem on live for our Java/J2EE based web application. The trace file is pasted below:

The following deadlock is not an ORACLE error. It is a
deadlock due to user error in the design of an application
or from issuing incorrect ad-hoc SQL. The following
information may aid in determining the deadlock:

Deadlock graph:
                                          ---------Blocker(s)--------  ---------Waiter(s)---------
Resource Name                             process session holds waits  process session holds waits
TX-00030013-0000CC59-00000000-00000000        106     408     X             76     171           X
TX-000F001E-0001681C-00000000-00000000         76     171     X            106     408           X

session 408: DID 0001-006A-007D5293 session 171: DID 0001-004C-001C2F48
session 171: DID 0001-004C-001C2F48 session 408: DID 0001-006A-007D5293

Rows waited on:
  Session 408: obj - rowid = 0002A2AC - AAAqKsAAUAABE3oAAB
  (dictionary objn - 172716, file - 20, block - 282088, slot - 1)
  Session 171: obj - rowid = 0002A2AC - AAAqKsAAUAABE2pAAA
  (dictionary objn - 172716, file - 20, block - 282025, slot - 0)

----- Information for the OTHER waiting sessions -----
Session 171:
  sid: 171 ser: 62179 audsid: 1760178 user: *******
    flags: (0x8000045) USR/- flags_idl: (0x1) BSY/-/-/-/-/-
    flags2: (0x40009) -/-/INC
  pid: 76 O/S info: user: ****, term: UNKNOWN, ospid: 48169
    image: **************
  client details:
    O/S info: user: ****, term: unknown, ospid: 1234
    machine: ********* program: JDBC Thin Client
    client info: *********************
    application name: JDBC, hash value=3519240545
    action name: ProcessDummySearchData-UpdateSearchData, hash value=560080304
  current SQL:
  UPDATE TABLE1 SET COL3=:1 ,LASTMODIFIED=SYSDATE WHERE UUID=:2  AND COL2=:3

----- End of information for the OTHER waiting sessions -----

Information for THIS session:

----- Current SQL Statement for this session (sql_id=635v1mk350vst) -----
update TABLE1 set COL3= :1
where
COL4= :2
and COL5 =  :3

If I try to apply "proposed fix" from below URL: I fail to understand how creating composite indexes (UUID, COL2) & (COL4, COL5) in TABLE1 can solve this issue? Can anyone throw some light on how to solve this issue? I understand that this particular issue is perhaps due to simultaneous update on same table/row.

https://stackoverflow.com/questions/20474959/how-to-find-out-the-cause-of-an-oracle-deadlock


texts: cannot find old connection string connecting to old database and not new one that has been defined

texts:
Cannot find old connection string connecting to old database and not new one that has been defined. We have changed web servers on Azure and cannot find the reference to the old database because our IIS sites are still hitting the old database as well as the newly defined one.

I am hoping this is not edge case and someone has had this problem. There are a lot of variables to this problem and know anyone reading this will have questions. Our new sites are working on new SQL but when we shut down the old one our sites break and error out. I am thinking something to do with session state and checked all the conn strings in IIS and found no reference whatsoever to the old SQL Server.


texts: SQLite - Get all other rows with any duplicate column value

texts:
I have a table of users, with information about them and the combination of the fields and UserName is unique (A row as a whole, not only on one field)

Users

+----------+--------+--------+--------+
| UserName | Field1 | Field2 | Field3 |
+----------+--------+--------+--------+
| UserNum1 | AAABBB | CCCDDD | EEEFFF |
| UserNum2 | AAABBB | IIIJJJ | KKKFFF |
| UserNum3 | LLLMMM | CCCDDD | PPPQQQ |
| UserNum4 | RRRSSS | TTTUUU | VVVWWW |
+----------+--------+--------+--------+

Given one column value to find, I have to find other users with ANY same column values for all columns dynamically.

For example, I only provided the username "UserNum1" as UserName, It should return User 2 and 3 because of the values of their Field1 and Field2 respectively.

Desired output:

+----------+--------+--------+--------+
| UserName | Field1 | Field2 | Field3 |
+----------+--------+--------+--------+
| UserNum2 | AAABBB | IIIJJJ | KKKFFF | # Field1 has same value on User1 & User2
| UserNum3 | LLLMMM | CCCDDD | PPPQQQ | # Field2 has same value on User1 & User3
+----------+--------+--------+--------+

Is there ways to do this in one query? Or do I really have to query each column and do the other processing in my backend

FYI: The initial problem is, finding possible user duplicate accounts or "alts" based on similar fields


texts: Check end time (last transaction time) of PostgreSQL WAL archive (backed up with pgBackRest)

texts:
I have pgBackRest 2.27 receiving WAL archives from PostgreSQL 12.3. Is there a way to find out the timestamp of the latest transaction included in the WAL archives, i.e. what time I could restore my database to, for monitoring purposes?

pgbackrest info gives me the WAL filenames, like this:

    db (current)
        wal archive min/max (12-1): 000000010000000000000001/00000001000000000000000D

Is there a way I could find out the time that WAL file 00000001000000000000000D goes to? An approximate time would be fine. (I know I can check the file timestamp, but there may be some delay between PG archiving the WAL file and the pgBackRest repository host receiving it.)


texts: Performance difference for COALESCE versus ISNULL?

texts:
I've seen a lot of people use the COALESCE function in place of ISNULL.  From internet searches, I've found that COALESCE is ANSI standard, so there is an advantage that we know what to expect when using it.  However, ISNULL seems easier to read since it seems more clear what it's doing.

I also realize that ISNULL is kind of tricky since it acts differently on different database servers and in different languages.

All of that, in my mind, boils down to style and standards.  Given that style is subjective, is there any reason to use COALESCE over ISNULL (or vice versa)?  Specifically, is there a performance advantage of one over the other?


texts: How to change the current database in an SQL Query window in SSMS?

texts:
I'm trying to create a SQL script that creates a new server login in the master database, and then creates a corresponding user in an existing database.
If the 'USE [database_name]' appears more than once in the query editor, it results in two errors:

Msg 40508, Level 16, State 1, Line 4 USE statement is not supported to
switch between databases. Use a new connection to connect to a
different database.
Msg 40508, Level 16, State 1, Line 11 USE
statement is not supported to switch between databases. Use a new
connection to connect to a different database.

I've considered turning on SQL CMD mode and using the :CONNECT command, but I don't want to connect to a different database server, just change the database. Connecting to a different database (or even the same one), would require me to embed a username and password in the script as well, which is not desirable.  I just want to open the SQL script connected to the right server, and be able to switch between databases.


texts: How to create Oracle RAC database using dbca silent mode with different db_name and db_unique_name

texts:
I have a requirement to use dbca (version 11.2.0.3) silent mode to create a RAC database on a 2-node RAC and the db_name and db_unique_name must be different, I tried following dbca command

$ORACLE_HOME/bin/dbca -silent -createDatabase -templateName 'General_Purpose.dbc' -adminManaged -emConfiguration NONE -gdbName 'oiddb' -sid 'oiddb' -sysPassword oracle -systemPassword oracle -asmSYSPassword oracle -asmsnmpPassword oracle -storageType ASM -diskGroupName DATA -datafileJarLocation /u01/app/oracle/product/11.2.0/dbhome_1/assistants/dbca/templates -nodelist 'node1,node2' -characterset AL32UTF8 -obfuscatedPasswords false -sampleSchema false -oratabLocation /etc/oratab -initParams db_unique_name=oiddbp,processes=7000,db_name=oiddb -listeners LISTENER_OID

But it failed at this error

DBCA_PROGRESS : 85%
ORA-17502: ksfdcre:4 Failed to create file +DATA/oiddb/spfileoiddb.ora
ORA-15173: entry 'oiddb' does not exist in directory '/'

PRCR-1079 : Failed to start resource ora.oiddbp.db
CRS-5017: The resource action "ora.oiddbp.db start" encountered the following error:
ORA-01078: failure in processing system parameters
ORA-01565: error in identifying file '+DATA/oiddb/spfileoiddb.ora'
ORA-17503: ksfdopn:2 Failed to open file +DATA/oiddb/spfileoiddb.ora
ORA-15056: additional error message
ORA-17503: ksfdopn:2 Failed to open file +DATA/oiddb/spfileoiddb.ora
ORA-15173: entry 'oiddb' does not exist in directory '/'
ORA-06512: at line 4
. For details refer to "(:CLSN00107:)" in "/u01/app/11.2.0/grid/log/node1/agent/crsd/oraagent_oracle/oraagent_oracle.log".

I was monitoring my ASM during the run and I can see +DATA/oiddbp got created, but it seems that dbca was trying to create the spfile under +DATA/oiddb.

I then tried to create the database with the same parameters using dbca UI mode and it did create +DATA/oiddbp and spfile under it.

I am wondering if this could be an Oracle bug? Any workaround or how to resolve this? Please help, thanks.


texts: MySQL - Do not allow two rows to have the same specific columns

texts:
id | source | target | desc
--------------------------------------------
0  | hello  | hola   | this is a description   //Valid insert
1  | hello  | no     | this is a description   //Valid insert
2  | what   | what   | desc                    //Valid insert
3  | hello  | hola   | testing                 //INVALID insert

I want the row with ID 3 to error when inserted because it's source matches another row's source AND its target matches that same other row's target.

I know that I could do this in PHP, but I would prefer it done within the MySQL RDBMS.


texts: How to determine if an Index is uselss in Percona MySQL?

texts:
Using Percona Server 5.5

I've a table which stores user activty (lets call it user_activity). It was created quite a few years ago when I had only a little bit of knowledge about database optimisation. Thusly, I created a few indexes to cope with the different select queries I was likely to run into. Unfortunatly, the indexes are massivly out growning the size of the data and now the table is the largest table I have in my database. Because of this, the more intensive update queries are taking extremly long to run and often cause table locks.

So, I want to see what I can do to improve the table's efficiency in every way and thought I would start with analyzing the indexes we have. Unforutnatly, I can't find a way to determine if an index is needed and with a table this large, i'm not in a position to just remove and re-add as that could take hours to do.

What is the best method to check if an index is needed or can be removed or modified?

Here are the table and index stats:

TABLE:

Engine: InnoDB
Row format: Compact
Column count: 12
Table Rows: 764,640,132
AVG row length: 66
Data length: 47.3 GB
Index length: 168.7GB
max data length: 0 bytes
data free: 6mb

INDEXES:

PRIMARY
BTREE (clustered)
Unique: Yes
Cardinality: 764634204

---------------

actions
BTREE
Unique: no
Cardinality: 18
columns: action_type, action_id

---------------

parent
BTREE
Unique: no
Cardinality: 18
columns: section_parent_type, section_parent_id, status, added_date

----------------

Index_parent
btree
unique: no
cardinality: 18
columns: section_parent_type,added_date,user_activity_id

----------------

Index_parent2
btree
unique:no
cardinality: 18
columns: section_parent_type, section_parent_id,parent_id,parent_type,status,added_date

----------------

Index_actions
btree
unique:no
cardinality:18
columns: section_parent_type, parent_type,parent_id,action_type,added_date

COLUMNS

user_activity_id - int(10), notnull, autoinc (primary)
user_id - int(10), notnull
added_date - datetime
action_type - int(10), notnull
action_id - int(10), notnull
section_parent_type - int(10), notnull
section_parent_id - int(10), notnull
status - tinyint(3), notnull
parent_type - int(10), notnull
parent_id - int(10), notnull
notification - tinyint(3), notnull
friend_activity - tinyint(3), notnull


texts: Code required to make column not nullable

texts:
I have a table where I mistakenly allowed NULL values in a column:

CREATE TABLE EXAMPLE (
    EXAMPLE_ID INT IDENTITY(1, 1) NOT NULL,
    FOO_ID INT,
    CREATED_ON DATETIME DEFAULT CURRENT_TIMESTAMP, -- Oops!
    CODE VARCHAR(50),
    QUOTE DECIMAL(18,2),
    BAR_ID TINYINT NOT NULL DEFAULT 1,

    CONSTRAINT EXAMPLE_PK PRIMARY KEY (EXAMPLE_ID),

    CONSTRAINT EXAMPLE_FK1 FOREIGN KEY (FOO_ID)
    REFERENCES FOO (FOO_ID)
    ON DELETE CASCADE,

    CONSTRAINT EXAMPLE_FK2 FOREIGN KEY (BAR_ID)
    REFERENCES BAR (BAR_ID)
);

CREATE INDEX EXAMPLE_IDX1 ON EXAMPLE (FOO_ID);
CREATE INDEX EXAMPLE_IDX2 ON EXAMPLE (BAR_ID);

It's a fairly unimportant column (not indexed, not foreign key, not used in triggers...) and this is how I'd normally fix it:

ALTER TABLE EXAMPLE
ALTER COLUMN CREATED_ON DATETIME NOT NULL;
-- Runs in 1 second

However, I decided to use a GUI tool (SQL Server Management Studio, but all other tools I've tried do a similar thing) and found the whole process took like 20 minutes. The reason? The automatically generated change code is a 3351-line script that apparently tries to edit every single table on database that has a foreign key pointing to EXAMPLE.EXAMPLE_ID!

ALTER TABLE dbo.SOME_OTHER_TABLE
DROP CONSTRAINT SOME_OTHER_TABLE_FK
GO

BEGIN TRANSACTION
GO
ALTER TABLE dbo.SOME_OTHER_TABLE ADD CONSTRAINT
    SOME_OTHER_TABLE_FK FOREIGN KEY
    (
        EXAMPLE_ID
    ) REFERENCES dbo.EXAMPLE
    (
        EXAMPLE_ID
    ) ON UPDATE  NO ACTION
    ON DELETE  CASCADE

GO
ALTER TABLE dbo.SOME_OTHER_TABLE SET (LOCK_ESCALATION = TABLE)
GO
COMMIT

Further digging reveals that the script creates a new Tmp_EXAMPLE table with the final definition and copies all data from EXAMPLE.

Is this complex process necessary? What's the exact problem with plain ALTER TABLE?


texts: Duplicate Oracle Database from .BKP files - getting "multitenant container database not set up properly"

texts:
I have a regular Oracle 12c database already created, named ECLS. I've been given a pile of .BKP files, not from this database, and have been told to replace everything in ECLS with the contents of these files.

With the files, I was also told to use this command to do it:

RMAN> duplicate database to ECLS backup location '/u01/dumpfilesgohere';

No other directions were provided.

I found that my database needs to be an auxiliary with nothing mounted. So I did the following:

$ sqlplus / as sysdba
SQL> shutdown immediate;
SQL> startup nomount;
SQL> exit
$ rman auxiliary /
RMAN> duplicate database to ECLS backup location '/u01/dumpfilesgohere';

This looks like it's working properly for the first few seconds... it says that it's able to restore the control files from the BKP files. But then it ends up failing. Here's the last few lines before the failure:

contents of Memory Script:
{
   sql clone "alter system set  db_name =
 ''ECLS'' comment=
 ''Reset to original value by RMAN'' scope=spfile";
   sql clone "alter system reset  db_unique_name scope=spfile";
   shutdown clone immediate;
}
executing Memory Script

sql statement: alter system set  db_name =  ''ECLS'' comment= ''Reset to original value by RMAN'' scope=spfile

sql statement: alter system reset  db_unique_name scope=spfile

Oracle instance shut down
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 01/30/2017 14:59:06
RMAN-05501: aborting duplication of target database
RMAN-03015: error occurred in stored script Memory Script
RMAN-06136: ORACLE error from auxiliary database: ORA-65093: multitenant container database not set up properly

What am I doing wrong? What files do I need to change to get this error message to stop occurring?

Alternatively... if my whole database install isn't going to work, can someone tell me what I need to do differently when reinstalling the database?


texts: Is it possible to set up Azure as a publisher for Pull Replication to an on-premise database?

texts:
I am trying to set up Pull Replication from an Azure database to an on-premise database.  I have set up the on-premise database as the distributor but when I try to establish a subscription to the Azure database I get an error, see below.

TITLE: New Subscription Wizard
------------------------------

SQL Server could not retrieve the list of databases for server 'nyrstar-prod-01.database.windows.net'.

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&ProdVer=14.0.17254.0&EvtSrc=Microsoft.SqlServer.Management.UI.CreateSubWizardErrorSR&EvtID=CantGetDatabaseList&LinkId=20476

------------------------------
ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

------------------------------

Could not find stored procedure 'sp_get_distributor'. (Microsoft SQL Server, Error: 2812)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&ProdVer=12.00.0600&EvtSrc=MSSQLServer&EvtID=2812&LinkId=20476

-------------------------------
-------------------------------
ALL ADVANCED INFORMATION
-------------------------------
-------------------------------

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

SQL Server could not retrieve the list of databases for server 'nyrstar-prod-01.database.windows.net'. (New Subscription Wizard)

------------------------------
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&ProdVer=14.0.17254.0&EvtSrc=Microsoft.SqlServer.Management.UI.CreateSubWizardErrorSR&EvtID=CantGetDatabaseList&LinkId=20476

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

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

------------------------------
Program Location:

   at Microsoft.SqlServer.Management.Common.ServerConnection.GetExecuteReader(SqlCommand command)
   at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteReader(String sqlCommand, SqlCommand& command)
   at Microsoft.SqlServer.Replication.ReplicationServer.RefreshGetDistributor()
   at Microsoft.SqlServer.Management.UI.PageChoosePublication.EnumDatabases(ReplicationSqlConnection sqlConn)
   at Microsoft.SqlServer.Management.UI.PageChoosePublication.LoadDatabases(ReplicationSqlConnection publisherConn, String selectedDBName, String selectedPubName, Boolean isHeterogeneous)

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

Could not find stored procedure 'sp_get_distributor'. (.Net SqlClient Data Provider)

------------------------------
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&ProdVer=12.00.0600&EvtSrc=MSSQLServer&EvtID=2812&LinkId=20476

------------------------------
Server Name: nyrstar-prod-01.database.windows.net
Error Number: 2812
Severity: 16
State: 62
Line Number: 1

------------------------------
Program Location:

   at Microsoft.SqlServer.Management.Common.ConnectionManager.ExecuteTSql(ExecuteTSqlAction action, Object execObject, DataSet fillDataSet, Boolean catchException)
   at Microsoft.SqlServer.Management.Common.ServerConnection.GetExecuteReader(SqlCommand command)


texts: How to compare the data type of a query value in Postgresql

texts:
How to compare the data type of a query result value? For example, to check if something is an 'interval'
select pg_typeof(current_timestamp - '2020-01-01 00:00') = 'interval';

gives the error
ERROR: invalid input syntax for type oid: "interval"


texts: sqlnet.ora or tnsnames.ora changes require reboot?

texts: Anytime you change Oracle's sqlnet.ora or tnsnames.ora files, does the system require a reboot?  In my instance, I only have the Oracle client installed on the machine I'm referring to, but out of curiosity what would it mean for an Oracle server installation?


texts: Trying to install SQL Server 2008 Management Studio Express

texts:
On a Wndows Server 2008 R2 64 bits, I installed Visual Studio 2010 Express, which installed a SQL Server 2008 Express:

Trying to install a SQL Server Management Studio, first I'm required to install Service Pack 1. But when trying to do so, I got the following error:

  There are validation errors on this page. Click OK to close this dialog box. Review errors at the bottom Setup page, then provide valid parameters or click Help for more information.

As shown in this image:

Then I uninstalled all SQL Server 2008 components from my server and tried to install Full SQL Server from this Microsoft download page.

(when installing, I would select Express Edition)

No luck, when trying to run setup.exe I get the following errors:

  This program has known compatability issues.

Then I click n "Check for solutions online" button, but then I get other errors:

  Your version of SQL Server 2008 isn't compatible with this version of Windows.


texts:
I have VM with ubuntu 18.04 server installed. I have installed postgres 10 from the repo.

Default installation. I made some connection count related changes in postgres.conf and connection security related changes in pg_hba.conf.

The install directories are all the default, including the data directory.

Server is running. I was able to create databases and access the same from java client.

But when I start psql from the same VM, it throws the following error.

Error: Invalid data directory

Just psql without any parameters also gives this error.

People have faced this error when they have tried to change the data directory which is not the case for me. Data directory is the default and it is owned by the postgres user.

ubuntu@ubuntu:~$ ls -l /var/lib/postgresql/10/
total 4
drwx------ 20 postgres postgres 4096 Jun 12 02:54 main

Any idea what could be the cause or where should I look for detailed error for psql client?


texts: How can I solve java lang unsatisfiedlink error when runing jdeveloper?

texts:
I cannot run Jdeveloper. My java is in /usr/java
How can I fix this? I run rpm --query --whatprovides 'libX....' but it says no package provides this


texts: Replication between SQL Server 2005 and Azure database

texts:
Is it possible to set up transitional replication between a SQL Server 2005 and a SQL Azure database?

My server is nearing end of life and I would rather migrate to a cloud infrastructure instead of buying new hardware.

I’ve been unable to find any documentation stating whether or not SQL Azure Data Sync can co-exist with SQL Server Replication.


texts: What is an "Obfuscation key" in SQL Server?

texts:
Jokes aside, what is an "Obfuscation key"? I'm just going off the docs.

sys.sysclsobjs Exists in every database. Contains a row for each classified entity that shares the same common properties that include the following:
...
Obfuscation key.

This seems to involve

Content/binn/sqllang.dll
Content/binn/sqlmin.dll

I would like to know what the key does and what is being obfuscated, and if that's available for the user.


texts:
I'm doing an audit of constraints in SQL Server.

How to find all constraints that are not enabled?

This, after using the next declaration:

alter table mytable nocheck constraint all


texts: How can I dump a specific table or set of tables without including the rest of the db tables?


texts:
When importing some data (10000) rows each time, I need to perform an INSERT or SELECT (insert if doesn't exist or select if exist and return table_id) 7 times for each row.

At the moment I use the rails function, find_or_create_by, this is usually working, but not for the value of rows I have, also it makes everything slower.

How can I create a function in postgres which will perform the insert or select and return the ids? Is it a good idea?


texts:
Based on the query below it was suggested that I create an index on the column in the order by clause. My understanding is the sorting is done on the result set and not the table.

The person suggesting this insists it helps with performance. I don't think it does but I have been wrong once or twice before.

Thanks.

SELECT TOP 1 [Process_Activity_Id]
,[Process_Id]
,[Transaction_Type]
,[Company_Code]
,[Entity_Identifier]
,[Item_Request]
,[Item_Response]
,[Item_Description]
,[Status]
,[Status_Detail]
,[Start_Date]
,[End_Date]
,[Last_Update_Date]
FROM [dbo].[Process_Activity] (NOLOCK)
WHERE [Entity_Identifier] = @EntityIdentifier
  AND [Status] = 'In_Progress' AND [Process_Id] = @ProcessId
ORDER BY [Message_Sequence] ASC


texts: Is there a way to de-orphan users without setting context to a restored database?

texts:
I have an installation script that restores a database from a template.bak file and then makes a few alterations. This question flows on from another question: How do I create databases / logins / users with minimal privileges when installing an application?.

Prior to running the installation, a script is executed that creates two SQL Login accounts MyAdmin and MyUser with what I believe is the necessary privileges (MyAdmin is used by the installer and a server management application, MyUser is used by the client application):

create login MyAdmin with password = 'TemporaryPassword'
exec sp_addsrvrolemember 'MyAdmin', 'diskadmin'
grant create any database to MyAdmin
grant alter any database to MyAdmin
create login MyUser with password = 'TemporaryPassword'

In the installation script I connect with the MyAdmin account and restore the template.bak file. I can see that the database is created and MyAdmin is the owner of the database. However:
- the instruction use MyNewDatabase fails with The server principal "MyAdmin" is not able to access the database "MyNewDatabase" under the current security context.
- if I login as sa in my test environment, I can see that the dbo user in the database has a user type 'SQL user without login'.

This leaves me very surprised. How is that the MyAdmin user can create a database, but cannot use it? Why is the dbo user not assigned to the owner / creator of the database?

The advice I have read so far is to use alter user MyAdmin ..., however I need to switch context to the database use MyNewDatabase to accomplish this - which I cannot do while the login is not linked to the user - a very frustrating catch-22.

I have found that I can resolve the issue by performing all of these tasks with the SA account, however I will not have access to the SA account with the installer as this is a commercial application.

Is there a way to de-orphan the accounts without setting context to MyNewDatabase?