texts:
I am trying to decide on the database design, with as few assumptions (regarding how the web app actually evolves) as possible at this stage.

As a first step, understanding that JOINS are expensive, I am considering a small number of monolithic tables  as opposed to a large number of normalized smaller tables. As a second point, I am confused between using hstore vs. regular tables vs. JSONB (with GiST indexing).

AFAIK (please feel free to correct):

Generally, in Postgres, hstore is known to perform better than other data types. This presentation from FOSDEM PGDAY has some interesting stats (in the second half of the slides).
https://wiki.postgresql.org/images/b/b4/Pg-as-nosql-pgday-fosdem-2013.pdf
An advantage with hstore is the fast indexing (GiN or GiST). However, with JSONB, GiN and GiST indexing can also be applied to JSON data.
This blog from a professional at 2nd Quadrant says "At this point it’s probably worth replacing hstore use with jsonb in all new applications" (scroll to the end):
http://blog.2ndquadrant.com/postgresql-anti-patterns-unnecessary-jsonhstore-dynamic-columns/

So I would like to decide on the following:

For the main (structured) part of the data: should it go in a couple of
relational tables (relatively large with many columns), or should it be a number of key-value stores using hstore?
For the ad hoc (user contributed/unstructured) data, should it be in JSON or ad hoc key value stores in hstore (with the keys stored in one of the main relational tables)?


texts: What is more efficient: text (765) or varchar (765) to store a freeform searchable description?

texts:
EDIT : To paraphrase quesiton - at what point do I switch from varchar to text and back?

A little background on the question. Let's say we have sales_orders table and we want to be able to keep a note field on the order with customer's or sales reps comments. It needs to be searchable LIKE "%goldfinger's gun%" We know we are not going to have "War & Peace" stored in the field, but at same time it should be big enough to handle a sentence or two.

So which option is:

faster for reads?
better at storing data?
better for full text searching?
indexing?

Assume MySQL(InnoDB) or PostgreSQL use case


texts: what does it mean if the last execution time of a stored procedure is empty

texts:
I recently ran some scripts to get the last execution time of my DB stored procedures, and the code is below:

SELECT d.object_id, d.database_id, OBJECT_NAME(object_id, database_id) 'proc name',
d.cached_time, d.last_execution_time, d.total_elapsed_time,
d.total_elapsed_time/d.execution_count AS [avg_elapsed_time],
d.last_elapsed_time, d.execution_count
FROM sys.dm_exec_procedure_stats AS d
where  DB_Name(d.database_id) = 'MyDB'
ORDER BY [proc name] ;

But I noticed that some of my stored procedure are not listed. Then I use the code below to check particularly one of them:

select s.last_execution_time
from sys.dm_exec_procedure_stats s
where OBJECT_NAME(object_id, database_id) = 'my_stored_procedure'

and I get empty result (but nothing shows in the column).

Just want to know if this means the stored procedure has never executed before, or something else?

Thank you


texts:
I have been researching the concept of ROWGUID's recently and came across this question.  This answer gave insight, but has led me down a different rabbit hole with the mention of changing the primary key value.

My understanding has always been that a primary key should be immutable, and my searching since reading this answer has only provided answers which reflect the same as a best practice.

Under what circumstances would a primary key value need to be altered after the record is created?


texts: Why is there a separate describe and explain statement in MySQL

texts:
I usually use DESCRIBE to get a description of a table, or a query. But, I was recently looking up some information on how to interpret the output of a DESCRIBE SELECT ... statement, and found that all the tutorials used EXPLAIN rather than DESCRIBE.
I found in the MySQL docs, this explanation:

The DESCRIBE and EXPLAIN statements are synonyms. In practice, the DESCRIBE keyword is more often used to obtain information about table structure, whereas EXPLAIN is used to obtain a query execution plan (that is, an explanation of how MySQL would execute a query).
The following discussion uses the DESCRIBE and EXPLAIN keywords in accordance with those uses, but the MySQL parser treats them as completely synonymous.

So, I'm curious why there are two separate commands. Is this needed to conform to the ANSI SQL spec, or is there a historic reason for the two separate commands?


texts: Determine last time a mysql table was accessed (i.e. SELECT'ed)

texts:
In my mysql DB schema there are old tables that are no longer used.
I want to come up with a systematic way to show my developer a list of tables and last time they were read (SELECT'ed, not UPDATE'ed: that would be easy).

There's a way of getting the general_log and parsing the gigantic file, but it's not elegant. I'm wondering if there's a tool or technique to learn and record time as each table gets accessed/read. Thanks.

My environment is 5.5 and 5.6


texts: Best Practice on reusing Service Broker Conversations and Conversation Groups

texts:
At the moment, we're using Service Broker on a lot of our critical components in our database, and I wanted to ask about the best practices on Conversation and Conversation Group reuse.
Presently, we're storing into a table our key for our object, which I'll use an SalesOrder as an example, and the Conversation for that order.  We then reuse that Conversation for updates related to that SalesOrder, to preserve processing order.
From reading about Conversation Reuse I understand we may be better off reusing these Conversations, as we're creating new ones per SalesOrder, which may bloat the amount of Conversations if we leave them for a long time (30 days?) without explicitly ending them.
So, I have a few questions, as I have been doing a lot of reading, and some of it has been conflicting (for example, one entry said that Conversation Groups would keep transactions ordered, where I believe that is only Conversations, and Conversation Groups is a locking mechanism, so only one Conversation is retrieved or updated at once.

Would we be better off keeping fewer Conversations, and reusing them - such as a Sequence of 100, and saving to our table the Conversation against the SalesOrder, and minimising the amount of Conversations that way.
Are there any problems with sharing Conversations among many SalesOrders (or similar objects).
If we had 10 MAX_QUEUE_READERS is there a good threshold to how many Conversations we should be creating? I'd assume a minimum to the number of readers and then YMMV?
Are we better off storing all our Conversations in one Conversation Group, or starting separate Conversation Groups for each Conversation - keeping the locking on the Conversations in that group down? A mix? 10 x 10?

I'm assuming, if I use the code below I am not reusing Conversations or Conversation Groups - so am I better just reducing the numbers of Conversations and leaving it at one Conversation per Conversation Group rather than the tens of thousands of Conversations we are producing each day?
;BEGIN DIALOG CONVERSATION @unqConversation
        FROM SERVICE [//SalesOrder/InitiatorService]
        TO SERVICE '//SalesOrder/TargetService'
        ON CONTRACT [//SalesOrder/Contract]
        WITH ENCRYPTION = OFF,
        LIFETIME = @lifetime


texts: (AWS) Mysql memory alert threshold(for monitoring) and innodb_buffer_pool_size

texts:
I recently upgraded my AWS RDS(mysql) from 5.6.23 to 5.7.11.

innodb_buffer_pool_size is automatically set as 75% of RAM by AWS. (I think it's maybe a recommendation)

The problem is the available RAM(FreeableMemory in AWS) slowly but steadily decreasing. It's never going up.. now at 66%..2 days after rebooting.

First I thought it's kind of bug at 5.7.11(it never happened before) and memory leaking but maybe it's not.

What if.. mysql is storing query to cache for the performance.. much more aggressively than 5.6.x..?

So my question is, what is the proper number of memory threshold for the monitoring?

If innodb_buffer_pool_size is set as 75% of RAM, memory threshold is set as 25% of RAM?

Please notice me for any further information needed.


texts: Store wait_stats automatically and query it whenever required

texts: I'm learning wait_stats and came up with thought. Is it possible to store wait stats in a table or create a stored procedure or query which will return wait_stats % when we execute query or stored procedure? if yes I would like to have query and stored procedure.


texts: MySQL: Loop over cursor results ends ahead of schedule

texts:
I'm executing a loop that iterates over a cursor's results. The code is inside a trigger function, and the part that matters looks like that:

EDIT: Sorry, stupid mistake. The trigger is executed after deleting on "one_table". This is not the same table in which I perform the 'delete' or 'update' operations inside the trigger's code (see second listing below)

create trigger my_trigger after delete on one_table
for each row
begin

declare my_value int;
declare num_rows int default 0;
declare done int default false;
declare my_cursor cursor for select value from table where condition;
declare continue handler for sqlstate '02000' set done = 1;

open my_cursor;
select found_rows() into num_rows;

-- This is just for debugging
insert into log_table(key, value) values('foo', num_rows);

if num_rows > 0 then:
  repeat
    fetch my_cursor into my_value;
    -- Do stuff
  until done end repeat;
end if;

close my_cursor;

end

The loop should be executed 11 times, because the query returns 11 values. This is checked by the 'insert' clause. The var 'num_rows' is equal to 11. But the problem is the loop is only executed 3 times.

These numbers (11, 3) are not important. If I change the query to return a different amount of results, the problem remains: the loop ends before scheduled.

Does it make any sense? I may do something inside the loop (the 'do stuff' part) that causes the end of the loop. It's the only thing that sounds logic for me.

EDIT: I include the 'Do stuff' part. The problem is probably caused because, inside this part, some 'select' sentences are executed. When one of those sentences returns an empty result, the defined handler is executed, setting done to 'true' and breaking the loop.

The "Do stuff" part is like this:

select some_value into some_field from other_table where some_conditions;

if (some_field is null) then
  delete from my_table where my_condition;
else
  update my_table set key1 = value1 where condition1;

Thanks in advance, and best regards,


texts: Creating and designing Air Trafic Control System

texts:
I'm having a class about Database management system, and the prof. gave us a project regarding Air traffic controller system, exact question is "Design and create a system which can manage informations of Air Traffic Control System, summarize entities and how system works?"

im not asking you to do this for me, but im desperate, because i dont know how air traffic control system work and what my database should include.

i have limited time, Thanks in advance.


texts: MySQL configuration to connect to multiple local applications

texts:
I've installed FreeRADIUS, MySQL and FreeRADIUS management (a web-based app) on the same machine, with both FreeRADIUS and FreeRADIUS management using same user to connect MySQL.
My problem is when I make a report from FreeRADIUS managment, it makes MySQL busy and FreeRADIUS can not respond to the request (out of service) until my report query has finished.
My question is: How both applications (FreeRADIUS & FreeRADIUS management) run queries on MySQL at same time.

I'm using MyISAM and below is the result of SHOW PROCESSLIST;

These are the queries run during SHOW PROCESSLIST;

SELECT count( DISTINCT (username) ) , `nasipaddress` FROM `radacct`
WHERE (
   acctstarttime < 'First period'
   AND acctstoptime > 'First period'
   AND acctstoptime <'Second period'
   )
OR (
    acctstarttime > 'First period'
    AND acctstoptime < 'Second period'
   )
OR (
  acctstarttime < 'First period'
  AND acctstoptime > 'Second period'
 )
OR (
  acctstarttime > 'First period'
  AND acctstarttime < 'Second period'
  AND acctstoptime > 'Second period'
 )
OR (
    acctstarttime < 'First period'
    AND acctstoptime IS NULL
    )
OR (
  acctstarttime > 'First period'
  AND acctstarttime <'Second period'
  AND acctstoptime IS NULL
 )
GROUP BY `nasipaddress`

-----------------------------------------
SELECT SUM(AcctSessionTime), SUM(AcctOutputOctets), SUM(AcctInputOctets)
FROM radacct
WHERE username='xxx'
-----------------------------------------

UPDATE radacct
      SET
     nasipaddress = '192.168.1.2',
         framedipaddress = '10.10.10.2',
         acctsessiontime     = '200',
         acctinputoctets     = '8263'
         acctoutputoctets    = '310'
      WHERE acctsessionid = 'FD324A12'
      AND username        = 'yyy'
      AND nasipaddress    = '192.168.1.2'"


texts:
I have a view with two UNION ALL between three tables with three or more identical columns, but displaying different information. But I want to increase its performance. Is there an alternative more faster than UNION ALL? Thanks in advance.

Here is the actual execution plan of the view.

And the definition of the view


texts: Does restoring a database in Microsoft SQL Server add objects to Resource Database?

texts: As far as I know the resource database contains information on all objects in its server instance, so I would think it's only natural that when you restore a database from a backup file, part of the process would be to register its objects in the server's resource database but I can't find any concrete information on the subject and I'm unable to test it locally.


texts: Copying MySQL Raw Data Files for Master-Slave Replication and GTIDs

texts:
I have been developing and running a mysql-based web application (intranet-based) for some time now. I wish to establish a step-by-step sane sequence for creating a slave from the running MySQL master. I have decided I should start the slave server by copying MySQL raw data files. A couple dozen times, I've gone through the relevant section of the manual, and I'm still dizzy.

I once got replication going (years ago) using this same approach, but at present, I'm in a bit of a fix. I think the complexity for me has been increased by the following:

I am using version 5.6.23
I have just turned on GTID mode, when I never really mastered the "old" system yet!
Generally, most tutorials and help posts out there are currently either outdated or in "transition", given the recent changes and advances in replication functionality that came on board during the 5.6.xx series.

Currently, I have the following config declarations:

MASTER:

log-bin=mysql-bin
server-id=1
innodb_flush_log_at_trx_commit=1
sync_binlog=1
binlog-format=MIXED
enforce-gtid-consistency
gtid-mode=ON
log-slave-updates

SLAVE:

log-bin=mysql-bin
server-id=2
read-only=ON
gtid-mode=ON
enforce-gtid-consistency
binlog_format=MIXED

In order to copy raw data files from the master, what should be my
step-by-step process?
Exactly what files within the data directory must I NOT copy, and
why?
On the slave, what are the gotchas, especially regarding the GTID
mode?

I'm sorry if aspects of my question sound vague. I'd be more than willing to elucidate as might be necessary.

Thank you in advance.


texts: pgpool2 setup on master-slave replication using postgreql 9.1

texts:
I have already setup 2 ubuntu server 12.04 LTS on my local machine using virtual box and is using the master-slave replication (postgresql 9.1).

192.168.0.109 = master
192.168.0.110 = slave

my question is how to apply the load-balancing based on the two database server? I've heard about pgpool2 and pgbouncer, which one should I use?

Thanks guys and sorry I am new to postgres replication.


texts: How to increase column limit of a table in SQLite?

texts: SQLite: How can we configure the maximum number of columns allowed in a table?


texts: MySQL too many connections on shared server

texts:
We have run a PHP/MySQL blog site on a shared server for years with no problem (my code not Wordpress). I'm not an system or database guy except for what I need. Our host (BlueHost) moved our site to a new server, and our capacity to serve our audience has dropped significantly. We're experiencing a seasonal spike now, so that makes it worse. So much so that we're also using another identical site to bear the load (GoDaddy). Not ideal. I don't have admin privileges obv, but I can see that we get 1000 max connections on GoDaddy and 150 on BlueHost (not sure what it was before).
I've been tracking the Threads_connected and Threads_running for both sites (again identical sites). GoDaddy never gets above ~25 connected and ~2 running. BlueHost will start to stack up Threads_connected until it hits the 150 limit mark. I'm trying not to break our resource allotment, but even with a lot of (and sometimes all) traffic redirected over to GoDaddy, BlueHost will stay above 120 for an extended period of time. Not sure if it's a caching issue or what.
I've tried doing an 'inline' declaration for timeouts when I open my PHP/PDO connection to MySQL, but I'm somewhat skeptical that it's fixing the problem.
$dbh_ip_check -> exec("SET session wait_timeout=10");
$dbh_ip_check -> exec("SET session connect_timeout=10");

These are the current global variables:
mysql> show global variables like '%timeout';
+-----------------------------+----------+
| Variable_name               | Value    |
+-----------------------------+----------+
| connect_timeout             | 20       |
| delayed_insert_timeout      | 40       |
| have_statement_timeout      | YES      |
| innodb_flush_log_at_timeout | 1        |
| innodb_lock_wait_timeout    | 50       |
| innodb_rollback_on_timeout  | OFF      |
| interactive_timeout         | 10       |
| lock_wait_timeout           | 31536000 |
| net_read_timeout            | 30       |
| net_write_timeout           | 60       |
| rpl_stop_slave_timeout      | 31536000 |
| slave_net_timeout           | 60       |
| thread_pool_idle_timeout    | 60       |
| wait_timeout                | 3600     |
+-----------------------------+----------+
We don't get a huge amount of traffic, but our spikes are probably seeing >100 people hit the site at a time, and that would continue for a half hour or more.
Any recommendations for fixes on a shared server? Thanks.
Current server variables/status:
Show global status copy/paste: https://paste.debian.net/1182642/
Show global variables: https://paste.debian.net/1182643/
ETA:

  [~]# iostat -xm 5 3
Linux 4.19.150-76.ELK.el7.x86_64 (boxxxxx.bluehost.com)         01/27/21       _x86_64_ (12 CPU)

avg-cpu:  %user   %nice %system %iowait  %steal   %idle
          51.92    5.08   25.79    2.47    1.35   13.39

Device:         rrqm/s   wrqm/s     r/s     w/s    rMB/s    wMB/s avgrq-sz avgqu-sz   await r_await w_await  svctm  %util

  [~]# ulimit -a
core file size          (blocks, -c) 0
data seg size           (kbytes, -d) 800000
scheduling priority             (-e) 0
file size               (blocks, -f) unlimited
pending signals                 (-i) 178732
max locked memory       (kbytes, -l) 64
max memory size         (kbytes, -m) 800000
open files                      (-n) 100
pipe size            (512 bytes, -p) 8
POSIX message queues     (bytes, -q) 819200
real-time priority              (-r) 0
stack size              (kbytes, -s) 15240
cpu time               (seconds, -t) unlimited
max user processes              (-u) 25
virtual memory          (kbytes, -v) unlimited
file locks                      (-x) unlimited


texts: How is a join performed by a database engine?

texts:
How is a join between two tables actually performed by a database engine?

I am sure that listing one tuple against all tuples of the other table cannot be the way to perform the join; it's just a way of understanding what the output will look like. Otherwise, for two tables containing 1000 tuples each, a join would prepare an intermediate data set of 1000*1000 tuples! That's hard to believe.


texts: What clustering features are required to implement Availability Groups?

texts: My understanding is that if one wants to implement Always On / Availability Groups (without FCI) the Windows Clustering Service would need to be installed, i.e. a Windows Cluster is required but not a SQL Cluster. Is that correct?


texts: Creating a new table from another make it read-only in PGadmin interface

texts:
On PostgreSQL 11, I've created a new table from another, using a subquery :
CREATE TABLE table2 AS (SELECT * FROM table1)
As a result, table2 is created, but each column is in read-only mode within the PGadmin interface.
--EDIT--
To answer to Vérace's comment, i add screenshots of my tables (I use PGadmin). As you can see, Table2's columns have a locker in their name. I can update a value with an SQL Query, but not with PGadmin interface. So, it's a PGadmin problem... !
TABLE 1

TABLE 2


texts: SQL Server 2005 Create a SQL Server failover cluster check button is not enabled

texts:
I try to install SQL Server failover cluster under windows 2003 or windows 2008. In both installing time I found the checkbox enable error. I don't know any changes want to do to windows server before install this server cluster. I used windows 2008 enterprise version and SQL Server 2005 Enterprise edition. If it's can't explain like this way can you pass any step by step install web site or document, that is grate help to me.

Thank you
Suranga


texts: UUID array declared in stored procedure, on access throws explicit type casts needed

texts:
I have a stored procedure where I have declared an array of UUIDs, on using that variable, it throws casting exception.

create or replace function test.test_function(max integer, interval interval) returns SETOF test.test_table
    language plpgsql
as
$$
DECLARE
    min TIMESTAMP;
    max TIMESTAMP;
    array_of_guids uuid[];
BEGIN
    array_of_guids = ARRAY(SELECT guid FROM test.test_table WHERE guid NOT IN (SELECT guid FROM test.ignore_test));
    ...

END
$$;

The guid is of type UUID, but at this line, I get an error which says:

[2019-09-27 12:12:48] Where: PL/pgSQL function test.test_function(integer,interval) line 10 at assignment```

Unable to figure out the syntax to fix the above problem.


texts: Postgres: FATAL: number of requested standby connections exceeds max_wal_senders (currently 0)

texts:
I setup master-replication and in my slave server logs, I am seeing this error over and over (it repeats every second):

FATAL: number of requested standby connections exceeds max_wal_senders (currently 0)

I am able to connect to the slave server, and perform read queries on it and replication seems to be working fine. max_wal_senders is set to 3 in the master server, and isn't set at all in the slave server, so I'm not sure what the issue is. max_connections is set to 1000 in the slave server, which is more than enough.

Why am I getting this error?


texts: How To Fix High DTU Usage on Specific Query

texts: I have a query that runs every few seconds on a Microsoft SQL Azure Database. It is from a web job that checks if there are actions that need to be done based on rows in a specific table (its a queueing table). Based on the image attached, does anyone have any suggestion of how i could reduce the DTU usage for the query in question, it is causing the database to reach 100% CPU.


texts: Mongodb index keeps growing in size eventhough the collection has a TTL index

texts:
I work on a mongodb sharded cluster (3.0.2 i know it's very outdated, not my call), on which i have a sharded db and a sharded collection on which i have multiple indexes.
One of the indexes is a TTL index (24h only, 86400) on a date field named d, used to expire old data, this leads to almost fixing the number of entries in the collection within our use case. Given the fixed structure and model of the data, this means an almost fixed size of the collection and it's indexes, it has always been the case for index and collection sizes throughout the whole cluster.
Our issue is, we have another index on this collection, on a field named k which is a large string.
ex : GigabitEthernet_XXXX-XXXXX-01_ABC_DEFG-HIJKLMNOP-5-QRS_TUV_7_0&#47;0&#47;1
This index keeps growing indefinitely in size, and causes memory outages and we don't know why. Any help ?
Knowing that the TTL index always expires/removes data from the collection, isn't the second index, on the k field supposed to also remove the references to that data, hence be fixed or at least not be evergrowing in size ?
We use this same technique (TTL index + other indexes) on all our collections and it always works in terms of fixing the sizes, not on this collection though. It is also important to note that other collections have a much smaller k field than this one, is that the issue ? Any explanations ? Any mongodb docs about this ?
Happy to provide any needed details to explain the issue.
Thanks in advance


texts: Add value to a column in a while loop in SQL Server

texts:
I have a stored procedure which looks like that :

ALTER PROCEDURE psu_auto_segment_folder_create

@Folder VARCHAR(MAX), /* for example : 'folder1/folder2/folder3' */
@ListId INT

AS BEGIN

SET NOCOUNT ON

DECLARE @FolderName VARCHAR(2048)
DECLARE @SQL VARCHAR(MAX)
DECLARE @ServerIp VARCHAR (15)

SELECT @ServerIp=SQL_IpAddress FROM xxxxxxxxx
(NOLOCK)
INNER JOIN xxxxxxxxx (NOLOCK)  ON xxxxxxx.Serverid = xxxxxx.Serverid
WHERE xxxx = @ListId AND Enable = 1

IF OBJECT_ID ('tempdb..#Folder') IS NOT NULL DROP TABLE #Folder
CREATE TABLE #Folder (ID INT IDENTITY(1,1) PRIMARY KEY, FolderName VARCHAR(MAX), Rank VARCHAR(256) NULL, Status INT NOT NULL DEFAULT(-1))

INSERT INTO #Folder (FolderName)
SELECT VALUE FROM dbo.f_Split(@Folder, '/')

WHILE (SELECT COUNT(*) FROM #Folder WHERE Status = -1) > 0
BEGIN

    SELECT @FolderName = FolderName
    FROM #Folder
    WHERE ID IN(SELECT MIN(ID) FROM #Folder WHERE Status = -1)

    SET @SQL = '
                DECLARE @IdBranch INT
                DECLARE @Rank VARCHAR(256)

                IF (SELECT COUNT (*) FROM [' + @ServerIp + '].[List_' + CONVERT(VARCHAR, @Listid) + '].dbo.xxxxxx WHERE Name = ''' + @FolderName + ''' AND objectid = 0) = 0
                BEGIN

                    IF OBJECT_ID (''tempdb..#IdBranch'') IS NOT NULL DROP TABLE #IdBranch
                    CREATE TABLE #IdBranch (IdBranch INT)

                    IF (SELECT COUNT(*) FROM #Folder
                    INSERT INTO #IdBranch (IdBranch)
                    EXEC [' + @ServerIp + '].[List_' + CONVERT(VARCHAR, @Listid) + '].dbo.xxxxx @Folder_name = ''' + @FolderName + ''', @Rang = '''', @Table = ''xxxx'' /* This stored procedure give me an ID in return */

                    SELECT @IdBranch = IdBranch FROM #IdBranch

                    SELECT @Rank = rank FROM [' + @ServerIp + '].[List_' + CONVERT(VARCHAR, @Listid) + '].dbo.xxxx WHERE idbranch = @IdBranch

                    UPDATE #Folder
                    SET Rank = @Rank
                    WHERE FolderName = ''' + @FolderName + '''

                    SELECT * FROM #Folder

                END
                ELSE
                BEGIN
                    PRINT ''error''
                END
    '

    EXEC(@SQL)

    UPDATE #Folder
    SET Status = 1
    WHERE FolderName = @FolderName

END

SELECT * FROM #Folder

SET NOCOUNT OFF

END
GO

Now if i run the stored procedure i will get something like

Id | Foldername | rank | status
--------------------------------
1  | folder 1   | 1    | 1
2  | folder 2   | 2    | 1
3  | folder 3   | 3    | 1

But i want the result to be

Id | Foldername | rank | status
--------------------------------
1  | folder 1   | 1    | 1
2  | folder 2   | 12   | 1
3  | folder 3   | 123  | 1

Like folder / subfolder / sub-sub-folder

thanks for your help !


texts:
In the /var/lib/pgsql/data/base Postgres that in version 8.4 and it was identified that the error single bank is with 37G which is absurd, is not already realized vacuum full declined nothing and also I put the autovacuum as off in postgresql.conf. the only thing I can do is back up and after that uninstall and reinstall the bank to resolve palliative-mind.

Can someone help me?

I am using Centos 6.7 and I know the Bank that's full ...


texts: how to avoid the "An INSERT EXEC statement cannot be nested" exception on this situation?

texts:
when I run the following script, it runs fine:

declare @temp table
(
    name varchar(255),
    field varchar(255),
    filename varchar(255),
    filegroup varchar(255),
    size varchar(255),
    maxsize varchar(255),
    growth varchar(255),
    usage varchar(255)
);
INSERT @temp
   exec sp_msforeachdb  @command1='use ?; Exec sp_helpfile;'

but when I use my own sp_foreachdb procedure, the source code is on this link below:

A more reliable and more flexible sp_MSforeachdb

declare @temp table
(
    name varchar(255),
    field varchar(255),
    filename varchar(255),
    filegroup varchar(255),
    size varchar(255),
    maxsize varchar(255),
    growth varchar(255),
    usage varchar(255)
);
INSERT @temp
   exec sp_foreachdb  @command='use ?; Exec sp_helpfile;'

I get an exception (please note I have added exception handling on that procedure)

--EXCEPTION WAS CAUGHT--
THE ERROR NUMBER:8164

SEVERITY: 16
STATE: 1

PROCEDURE: sp_foreachdb
LINE NUMBER: 165

ERROR MESSAGE:
An INSERT EXEC statement cannot be nested.
------------------------------------ the sql ------------------------------------

SELECT name FROM sys.databases WHERE 1=1 AND state_desc = N'ONLINE' AND is_read_only = 0
Msg 16916, Level 16, State 1, Procedure sp_foreachdb, Line 239
A cursor with the name 'c' does not exist.
Msg 16916, Level 16, State 1, Procedure sp_foreachdb, Line 240
A cursor with the name 'c' does not exist.

(0 row(s) affected)


texts: Count rows in a table based on a DATE field

texts:
I have a table with 3 columns (order_id, client_id, date_added).
The content would look something like this:

order_id | client_id  | date_added
-----------------------------------
14152    | NA4156     | 2019-03-01
14153    | EA4656     | 2019-03-02
14154    | EA4656     | 2019-03-02
14155    | CA4456     | 2019-03-03
14156    | DA4556     | 2019-03-03
14157    | EA4656     | 2019-03-03
14158    | FA4756     | 2019-03-06
14159    | GA4856     | 2019-03-06

and so on.
As you can see on a certain day there might be no entries.

I am trying to obtain the following result:

date       | no_of_rows
-----------------------------
2019-03-01 | 1
2019-03-02 | 2
2019-03-03 | 4
2019-03-04 | 4
2019-03-05 | 4
2019-03-06 | 6

I understood from here how to generate all dates that I am looking for, but I am not sure now how to count the unique clients based on client_id for each date.

At the moment I am doing this step by step and moving the data into an Excel and processing it from there using the query below:

get unique number of clients registered until and including 2019-03-01

SELECT COUNT(client_id) FROM clients WHERE date_added < '2019-03-02' GROUP BY client_id

get unique number of clients registered until and including 2019-03-02

SELECT COUNT(client_id) FROM clients WHERE date_added < '2019-03-03' GROUP BY client_id

and so on.

But this method seems to be a little bit exhaustive and I am pretty sure there is a way to do it in a single query, but not sure where to start from.


texts: what does SHOW /*!50000 GLOBAL */ STATUS do ?

texts:
What is the difference between

SHOW /*!50000 GLOBAL */ STATUS

and

SHOW GLOBAL STATUS

I saw that in few scripts and tuning presentations the first command is being used. Is there a difference, If there is, what does the 50000 signify ?

Also, this seems to be a good script : https://raw.github.com/rackerhacker/MySQLTuner-perl/master/mysqltuner.pl
Are there any similar scripts that list important stats and display recommendations


texts:
We had an issue in production (2008R2 SP3) yesterday, where a developer was running a piece of code in SSMS that was executing sp_send_dbmail inside a transaction, and including an attachment from a network share on another server.

The developer reported the code "seemed to take longer than expected" and tried to stop the query. When the query failed to stop, he asked me to kill his query.

Using sp_WhoIsActive, I gathered the following info on the session:

PREEMPTIVE_OS_GETPROCADDRESS wait that was continuing to go up
Extremely low CPU, memory, IO usage

almost all time spent on the wait

KILLED/ROLLBACK status
had 1 open transaction
Held a schema modification lock, which was causing significant blocking
Current statement was executing xp_sysmail_attachment_load

Call stack is sp_send_dbmail --> sp_GetAttachmentData --> xp_sysmail_attachment_load

Due to the PREEMPTIVE_OS wait, the session cannot be killed from SQL Server until the OS yields/returns.
Using TCPView, I confirmed there were no active network connections from the SQL Server to the file server with the attachment. I do not know if there was a connection that had since disconnected, or if it never made the connection.

Based on the data, my assumption is that there was some issue with accessing the attachment, which caused the session to hang, and the OS process to never yield/return.

Since this was impacting production by causing blocking, we ultimately failed over the FCI to another node, so that the instance restart would force the session to die.

My questions are:

Is there some way to reliably identify what OS process SQL was waiting on? (and thus try to "help" that process return--killing it etc)
Coming from the other direction: Is there a way to force a session to stop waiting for the OS with a PREEMPTIVE_OS wait so that we can kill it without restarting SQL?
Is there some factor in this scenario that make the session hanging on xp_sysmail_attachment_load more likely? (I'm working on a repro case, but can't reliably reproduce this yet. )
Are there other places I can/should look for additional information if this issue happens again?


texts: Issue: Data synchronization state of availability database is not healthy

texts:
I have a sqlserver 2017 cluster (sql server 14.0.3026.27) with two secondary nodes set with Availability Mode = Synchronous commit. The server has 8 databases that are replicated on the secondary nodes. The network speed between nodes is very high - when I transfer big files I get speeds of over 200MB per second.

I restored the databases from a sql server 2008 on the primary node and I tried to replicate them to the secondary nodes using the process where I remove them from the Availability Database on the primary, I delete them from the secondary nodes and I add them back to the Availability Databases on the primary following that "Add Database..."  dialog (I selected Automatic Seeding) . It's a hit and miss process. Some of the databases were not synchronized, so I had to repeat the process over and over until I got them all synchronized. At least in the past. Now I am stuck with one database that stubbornly refuses to synchronize.

When I go to the Availability Dashboard, it shows two warnings besides the database that has the Synchronization State = Not Synchronizing:

  Data synchronization state of availability database is not healthy.

  The data synchronization state of this availability database is
  unhealthy. On an asynchronous-commit availability replica, every
  availability database should be in the SYNCHRONIZING state. On a
  synchronous-commit replica, every availability database should be in
  the SYNCHRONIZED state.

and

  Secondary database is not joined.

  This secondary database is not joined to the availability group. The
  configuration of this secondary database is incomplete. For
  information about how to join a secondary database to an availability
  group, see SQL Server Books Online.

I looked in the logs on the primary and secondary nodes there is nothing relevant there.

I found these article:

https://social.technet.microsoft.com/wiki/contents/articles/3380.sql-server-data-synchronization-state-of-some-availability-database-is-not-healthy.aspx

But they don't have any useful suggestions.

At this point, the only thing that works is repeating the process multiple times and eventually all the databases are synchronized.

Do you have any suggestions on how to make this process predictable? Upgrade to latest sql server 2017 patch? Upgrade to sql server 2019?

While I was writing this post, I went to this page: https://support.microsoft.com/en-ca/help/4047329/sql-server-2017-build-versions. We are definitely behind with the releases. We are on CU7 and the latest release is CU20. Our cluster has been configured by another group and I don't know if they vetted the latest patches.
Going through the list of patches I found this: https://support.microsoft.com/en-us/help/4538275/fix-database-initialization-may-fail-by-using-automatic-seeding-in-sql.

This looks like the problem I am experiencing. I used Automatic Seeding for replication.

I am going to look into upgrading to the latest CU and I will come back to the thread.


texts:
I am having a bit of a hardtime in converting string to date in MS SQL. Hope you can guide me.

Here is my scenario:

I have a column in my table called ExpDt. It is the expiration date of the product. the format is mm/dd.

Now I want to know what products would expire 5 days before. So I am going to do a datediff. But the problem is the ExpDt column is not a valid date. How can I convert it to a valid date?

So the output would be something like: 2018-01-20 which is a valid date. Not a string.

Here's what I have tried.

-- Outputs 2018-01-31
-- select  @expDate = cast(datepart(year, getdate()) as nvarchar(5))
-- + '-' + substring(ExpDt, 1, 2)
-- + '-' + substring(ExpDt, 4, 5)

-- from BigEMerchandiser.dbo.tbl_Abis_D

It returns a string but I can't convert it to date using the convert function. I will attach the screenshot of the table so you can see the data of the ExpDt column. Hope I explained myself clearly. Any help would be much appreciated. Thankyou.


texts: If a database is detached from an instance permanently, are there any cleanup tasks that should be done?


texts: How much memory will allocated for a mysql view,if not how it works,if allocated how much will be allocated.


texts: Abstracting away filtering behavior from Oracle SQL Function

texts:
I have a Oracle SQL function, which returns a bunch of records through various sub-queries. However, there are countless filters that can be applied to these result records.

In an application programming language, I'd be able to abstract away the filtering behavior by allowing a function parameter to my function i.e. a callback in JS or Functional Interface object in Java.

Another possibility is having optional parameters or function overloading, but it still isn't scalable in regards to different filtering demands. Also, it won't work on stored procedures or functions.

I'm new to writing SQL procedures and functions, so I'm wondering is there any other option besides using an application programming language?


texts: Entity whose possible types differ in the FK

texts:
I have one entity called "Groups".
"Groups" can be of different types and the difference between each type is the FK.
For instance, if a "Group" is of type "Event Group", then it uses eventID; if it's of type "Institution Group", then it uses institutionID.
What's the proper way to model this?


texts: Logical Replication/Foreign Data Wrapper Connectivity between Two Aurora Serverless Databases (PostgreSQL 10.7)

texts:
I have two Aurora PostgreSQL Serverless databases running in the same region and have PostgreSQL version 10.7. I'm wondering if logical replication is available? The official docs say that it's possible for Aurora PostgreSQL, but nothing specifically about Serverless databases.
It times out when I tried to run this command with the right credentials:
CREATE SUBSCRIPTION testsub CONNECTION
   'host=publisher-cluster-writer-endpoint port=5432 dbname=db-name user=user password=password'
   PUBLICATION testpub;

I also struggle to have connectivity set up for Foreign Data Wrapper between two Serverless PostgreSQL databases.


texts: Counting postgres open connections without SQL query or connecting to postgres itself

texts:
For a monitoring script I need to count the number of open connections to a postgres database. This is done easily enough by opening up a client connection and running a count on the pg_stat_activity table.

However, if the maximum number of connections has already been reached, the connection, and check script, will hang waiting for the connection. I'd prefer to not have this as it complicates the check script and could hang for an indeterminate amount of time. Also, although it is only one connection, opening up a client connection adds to the connection pool which is not ideal.

Looking at the pg_stat_activity table there is a column listing the process id for the connection. This gave me the idea of using the linux command ps to list the current running process and grep out those that are related to protgres. Specifically, the command I am using is:

  ps -ef | grep 'postgres' | grep 'root' | grep -v 'grep' | grep -v 'su - postgres'| wc -l

This seems to match the number of entries in pg_stat_activity, but is sometimes, but not always, off by 1 (can be above and below).

Is there anything that might be in the process list that I should include or grep out?
Or is there another way to do this without using a db connection?


texts: SQL Agent Job or SQL Alert for Long Running Queries

texts:
Am trying to create either an Agent Job that runs scheduled every 60 minutes or a SQL Alert that fires off a mail everytime a query is detected that runs longer than 1 hour. The below is the basis of the query which I have:
SELECT
    r.session_id as [SPID]
    ,r.[start_time] as [Start Time]
    , T.[text] as [Query]
FROM sys.dm_exec_requests r
cross apply sys.dm_exec_sql_text(r.[sql_handle]) T
where
 DATEDIFF(second, r.[start_time], getdate()) > 3600
GO

Would it be more efficient to use an IF Statement, i.e if rows are returned then send mail, if so what is the best way of creating this. Or use a SQL Alert condition that would alert for Long Running Transactions > 3600 seconds and send via operator. If I have go via the Alert would I need an agent job with the Above SQL in. I have tried this (via SQL Alert) but the alert does not seem to trigger.
Any help is much apprecaited.
Have tried to include IF Statement but getting Syntax error (Incorrect syntax near the keyword 'into'.), note made it 60 seconds for testing.
IF (SELECT
'SPID='+CONVERT(VARCHAR,a.session_id)+' has been running the following for '+CONVERT(VARCHAR,DATEDIFF(SS, a.start_time, CURRENT_TIMESTAMP))+' seconds: '+convert(varchar,b.text), CONVERT(VARCHAR,DATEDIFF(SS, a.start_time, CURRENT_TIMESTAMP))
INTO #LongRunningQuery
FROM sys.dm_exec_requests a
CROSS APPLY sys.dm_exec_sql_text(a.sql_handle) b
WHERE a.status <> 'background'
AND DATEDIFF(SS, a.start_time, CURRENT_TIMESTAMP) > 60) > 0

begin

exec msdb.dbo.sp_send_dbmail

@profile_name = 'Test Alerts',

@recipients = 'test@test.com',

@subject = 'Warning',

@query = 'select * from #LongRunningQuery'

DROP TABLE #LongRunningQuery
end

Any help is much apprecaited.


texts: Oracle Grid Control Notification for Data Guard failures

texts:
Grid Control 10.2.0.5.

Does anyone know a way to configure a Grid Control notification rule for a failure of the SQL APPLY process on a logical standby database? I'd really like an email notification that the SQL APPLY process has encountered an error and is no longer applying redo to the logical standby.

I can get notifications on job failures, database down, listener stops, agent stops monitoring, but don't see how to get notified for this event.


texts:
I have a query to fetch the available rooms from 2 tables, roomtypes and reservations, as follows:

Roomtypes has the following columns

id,
product_id,
type,
total_no_of_rooms,
created_at,
updated_at

Reservation has the following columns

id,
roomtype_id,
customer_id,
check_in,
check_out,
no_of_rooms,
created_at,
updated_at,

This is my sql query:

SELECT rt.id, rt.type, rt.total_no_of_rooms,
    SUM(r.no_of_rooms) as "reserved rooms",
    rt.total_no_of_rooms - SUM(r.no_of_rooms) as "rooms available"
FROM Roomtypes rt
    LEFT OUTER JOIN Reservations r ON r.roomtype_id = rt.id
        AND '2018-04-11 10:00:00' >= r.check_in
        AND '2018-04-12 09:59:59' <= r.check_out
GROUP BY rt.id, rt.type, rt.total_no_of_rooms

When there is a reserved room in the given date, the column shows values to rooms available and rooms reserved. But if no rooms are reserved, the column shows null as values where as I need to get the rooms available as number of rooms available and reserved rooms as 0. How can I get this?


texts: Does implicit casting negatively affect index usage?

texts:
I have a query similar to the following:

SELECT id FROM MyTable
WHERE eventCount > eventCutoff * eventFactor

eventCount and eventCutoff are defined as a smallint, and eventFactor as a float.

I have two questions: is there an implicit cast of eventCount to a float for the comparison? And if so, will this prevent me from effectively leveraging an index on eventCount?


texts: How do I index two table having a one-to-many relation but only the latter is being frequently updated?

texts:
Take the following two tables:

table A has ~100k rows and 50 columns, and it is being updated only twice per month, how long this update takes is not an issue (as long as it doesn't take more than one day, which I think is unlikely :D). It will grow very slightly, not more than 10k rows per year.
table B has ~25k rows and 25 columns, column F is a foreign key to column C of table A. This table is being updated 100-1000 times a day, most of the time to add new rows, so it will easily grow to hundreds of thousands of rows.

Table A has a one-to-many relation to table B, both column F and column C must be not null, have their own index, and column C is also unique but it is not the primary key of table A. Every row of table B has a unique correspondence with a row in table A.

Both tables are being queried many thousands of times every day, individually or using their relation using an application that allows a few dozen different types of query.

Is it a good idea to index every column of table A that is involved in a query, plus making a composite index for every type of query? If no, what are the possible drawbacks?
For table B is it a good idea to make a composite index only for the most time consuming queries? Or should I make a composite index also for every column in table B that is being queried, since those queries may vary in the parameters but not in the form?


texts: Why MongoDB needs to disable majority read concern for PSA architecture?

texts:
We have MongoDB 3.6.9 replica set, contains 3 data nodes and 2 arbiters.

I noticed warning in mongodb console:

2020-01-20T11:29:04.057+0000 I REPL     [replexec-0] ** WARNING: This replica set uses arbiters, but readConcern:majority is enabled
2020-01-20T11:29:04.057+0000 I REPL     [replexec-0] **          for this node. This is not a recommended configuration. Please see
2020-01-20T11:29:04.057+0000 I REPL     [replexec-0] **          https://dochub.mongodb.org/core/psa-disable-rc-majority-3.6

I studied the material from the link https://dochub.mongodb.org/core/psa-disable-rc-majority-3.6, but I did not understand why it needs to disable majority RC, and what will be better: remove arbiters or disable majority RC.


texts: Why can MySQL handle multiple updates concurrently and PostgreSQL can't?

texts:
Let's imagine you have a table with this definition:

CREATE TABLE public.positions
(
   id serial,
   latitude numeric(18,12),
   longitude numeric(18,12),
   updated_at timestamp without time zone
)

And you have 50,000 rows in this table. Now for testing purposes you will run an update like this:

update positions
set  updated_at = now()
where latitude between 234.12 and 235.00;

That statement will update 1,000 rows from the 50,000 (in this specific dataset).

If you run such a query in 30 different threads*, MySQL innodb will succeed and PostgreSQL will fail with lots of deadlocks.

Why?

* I'm comparing the latest version of MySQL innodb vs Postgres, this is a concurrent update case. Production cases: imagine 5000 stocks being updated with the latest price available constantly.


texts:
I'm using the following query to create a leaderboard of top 10 people in this week.

SELECT
users.id,
concat(users.first_name, ' ', users.last_name) as name,
users.picture_url,
MAX(rounds.speed) as speed,
AVG(rounds.accuracy) as accuracy,
SUM(rounds.score) as score,
users.level
FROM users INNER JOIN rounds ON users.email=rounds.email
WHERE DATE(rounds.dateTime) BETWEEN CURDATE()-INTERVAL 1 WEEK AND CURDATE()
GROUP BY users.id
ORDER BY score DESC, speed DESC
LIMIT 10

Currently, it takes around 6 seconds to run this query. The table 'rounds' contain around 3000 rows. Soon it will be much bigger. So once the user opens the leaderboard it takes more than 6 seconds to load!

Is there any way of caching or improving the query so that it loads faster?

Database: MySQL Backend: PHP5 Framework: Codeigniter


texts: SQLite Design: Better to have many tables with few columns or few tables with many columns?

texts:
I am struggling to figure out the best design for a Sqlite database that will hold data about Facebook pages so that I can calculate benchmarks for how the average Facebook page performs.

I have an option of a single large table, a few largish tables, or many small tables, and I don't know which is the better choice.

Background:

20,000+ Facebook pages. ~50 metrics for each page. I want to store data for every day since July, 2011. A python script runs daily to grab the latest data from the Facebook API and dump it into the database.

I rarely calculate benchmarks on all 20,000 fan pages at once--normally I bucket them by the number of fans they have (1-100K, 100K-1M, 1M+) or the industry they're in (food, movies, celebrities, travel, etc) and then calculate benchmarks on the subgroup.

Generally benchmarks are calculated by averaging all the page results for a given day. The results are normalized on a "per-fan" basis to enable comparing across pages of differing sizes. Here's pseudo-code to calculate a benchmark:
    for each day within the benchmark range:
        for each page meeting the benchmark criteria:
            take the chosen metric and divide it by the number of fans that page had that day
        calculate the average of all the pages for that particular day
        output that day's average to JSON for charting

Criteria:

1) Top priority: Simple SQL SELECT statements.
I poke around a lot looking for correlations in the data, so I'm constantly writing adhoc SELECT statements. I'm comfortable joining and unioning if need be, but simpler is always better.

2) Speed of calculating results.
This only matters for my ad-hoc queries, and I try to keep those queries fast by limiting them to a small date range and a small bucket of pages. If something looks interesting I expand to larger datasets and let it run in the background so it's fine if it takes an hour.

3) Database storage size. Not too worried about this unless I'm choosing between two valid options and one is 2x the size of the other.

4) Speed of inserts. Shouldn't be an issue since I can wrap everything in a single transaction and run it overnight.

Table Design options:

Option A - Single massive table.
It seems ideal, but 20K pages and 700 days results in this table containing 14M rows!

Table: Page-Days
(ID INTEGER PRIMARY KEY,
 PageID INTEGER REFERENCES Page(PageID),
 date DATE,
 metric1 INTEGER,
 metricN INTEGER,
 metric50 INTEGER)

Option B: One Table per metric - 50 tables, each with 700+ DATE Columns and 20K page rows.
This is a lot simpler since most benchmarks will only be joining two tables for 40K total rows. But the column count will get huge.

Table: Metric_name
 (PageID INTEGER PRIMARY KEY REFERENCES Page(PageID),
 2011-07-01 DATE,
 2011-07-02 DATE,
 dayN DATE, etc...)

Option C: One Table per day - 700+ tables, each with 50 Metric columns and 20K page rows.
This seems crazy at first, but it avoids massive numbers of columns and rows. My one concern is that while I'm almost always calculating metrics on a per-day basis, meaning I don't need to join across tables to generate a daily average, I do need 100+ days at a time when I'm graphing the daily average, and this becomes impossible to do within the SQL layer, I am forced to do it using a loop in my script.

Table: 2011-07-01
(PageID INTEGER REFERENCES Page(PageID),
metric1 INTEGER,
metricN INTEGER,
metric50 INTEGER)

Which should I choose?


texts: Separate letters from digits in alphanumeric string

texts:
I have an alphanumeric string as input and I want to get two results out of it:

a string where all the numerals are removed
and

an integer that is the sum of all the digits in the input string.

For instance, for this input:
GR35hc7vdH35

I want the following output:
| Col1.         |        Col2    |
----------------------------------
| GRhcvdH       |        23      |

How can do this?


texts: Does parameter sniffing exist for views?

texts:
I've heard of and encountered parameter sniffing in stored procedures before.  But today I ran into the first instance of what I can only assume is parameter sniffing in a view.

I have a query that selects from a number of tables and a view.  It's basically:

select *
from view v
inner join table t
on v.id = t.id
where v.date = @EndDate;

With the parameter in place, the query will attempt to execute literally for hours.  I've let it sit for over 2 hours and nothing gets returned.  If I replace the parameter with a specific date where v.date = '2017-11-20' or even a calculated value where v.date = cast(getdate() as date) it will return in 2 seconds.  I can keep the parameter in by adding option (recompile) to the end of the query and it will return results in a second or two.

So I know how to fix this, I am really just asking: Is this something that others have run into before?  Is it possible it's not parameter sniffing but rather something else?

Thanks.


texts: How to improve performance for read only innodb MySQL Database

texts:
I have a particular task which is to maximize the concurrent performance. There is only one particular type of query, which is

select * from table where col1 between ? and ? and col2 between ? and ?

I have created a composite index for (col1, col2). The table is about 20G in size and 100 million rows

However, even in peak concurrent requests, the CPU utilization for MySQL is only 30%. I have tried various techniques like increase max_connections, innodb_buffer_pool_instances but none of them are working.

How to maximize the configuration so that it can perform such read-only query to extreme?


texts:
i read the doc about  postgres bloom, but can't reproduce the same results, please help me to understand what i missed.
my server is:

SHOW server_version;
        server_version
-------------------------------
 10.6 (Debian 10.6-1.pgdg90+1)

dev=# show random_page_cost;
 random_page_cost
------------------
 4

first create the table with the same command as in the docs:

dev=# CREATE TABLE tbloom AS
   SELECT
     (random() * 1000000)::int as i1,
     (random() * 1000000)::int as i2,
     (random() * 1000000)::int as i3,
     (random() * 1000000)::int as i4,
     (random() * 1000000)::int as i5,
     (random() * 1000000)::int as i6
   FROM
  generate_series(1,10000000);

next i create the btree index

dev=# CREATE index btreeidx ON tbloom (i1, i2, i3, i4, i5, i6);
CREATE INDEX

and get next plan:

dev=# EXPLAIN ANALYZE SELECT * FROM tbloom WHERE i2 = 898732 AND i5 = 123451;
                                                       QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------
 Gather  (cost=1000.00..127195.10 rows=1 width=24) (actual time=258.963..260.900 rows=0 loops=1)
   Workers Planned: 2
   Workers Launched: 2
   ->  Parallel Seq Scan on tbloom  (cost=0.00..126195.00 rows=1 width=24) (actual time=255.446..255.446 rows=0 loops=3)
         Filter: ((i2 = 898732) AND (i5 = 123451))
         Rows Removed by Filter: 3333333
 Planning time: 0.412 ms
 Execution time: 260.939 ms

Execution time: 260.939 ms

and now drop the btree index and create bloom:

dev=# DROP INDEX btreeidx;
DROP INDEX
dev=# CREATE INDEX bloomidx ON tbloom USING bloom (i1, i2, i3, i4, i5, i6);
CREATE INDEX

get new plan:

dev=# EXPLAIN ANALYZE SELECT * FROM tbloom WHERE i2 = 898732 AND i5 = 123451;
                                                       QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------
 Gather  (cost=1000.00..127195.10 rows=1 width=24) (actual time=260.278..261.989 rows=0 loops=1)
   Workers Planned: 2
   Workers Launched: 2
   ->  Parallel Seq Scan on tbloom  (cost=0.00..126195.00 rows=1 width=24) (actual time=256.224..256.224 rows=0 loops=3)
         Filter: ((i2 = 898732) AND (i5 = 123451))
         Rows Removed by Filter: 3333333
 Planning time: 0.165 ms
 Execution time: 262.053 ms

Execution time: 262.053 ms in the docs

  Bloom is better than btree

but not in my test. I tried several options with Length but not find good result.


texts:
I am sitting with lots of data in multiple xml files, which I would like to convert to relational data, for better being able to analyze it. Are there any tools that can transform the data / import to a database with a minimum of work?

In this specific case, the data will map very nicely to relational data, It is not completely flat, but the structure have a simple parent-child nesting. However there are no IDs, so keys and foreign keys will need to be generated.

I have been looking at Xpath queries, SQLXML Bulk load, SSIS and so on, and they seem to be too much work. Yes I know, I am being lazy, but knowing that the xml schema may change from time to time, I don't want to put too much work in it, I just want an easy way to summarize the data, without having to tinker with building a database schema manually, and then scripting queries to populate the DB.

Any tips or tools?


texts: Mongodb incremental oplog Dump not working

texts:
I am not able to take an incremental oplog dump. I am not getting any error messages the connection happens fine and also there is a operation recorded(shows up in db.currentOp()), but still nothing happens, not sure why?

I have executed the same command on a smaller database where everything works fine no problems at all, but the same command on a larger database(talking about 10-11 billion records which is increasing day by day), does not work.

The command is mentioned below:

mongodump --host $MONGODB_HOST:$MONGODB_PORT --authenticationDatabase admin -u $MONGODB_USERNAME -p $MONGODB_PASSWORD -d local -c oplog.rs -o backup/oplogDump/$currentTime --query '{"ts":{$gt: Timestamp( 1452157469, 37)}}'

After executing this command, the entire secondory mongo machine gets stuck, i mean letrely i need to restart the machine to get mongod start running again.

Another change that i have done recently is, i have increased the nssize to 1 GB as per developers requirement, I am not sure since when this issue started or what is causing this issue, Any Help will be really appreiciated?


texts: Unable to import shapefile into PostGisDatabase - problem of connection

texts:
I have been trying to import/load a shapefile into a PostGis database without any success despite many trials and searching an answer in the documentation.
Configuration
I'm running Windows 64 bits Home (in japanese )
pgAdmin 4
PostgreSQL 12.1
postgis 3
I used 2 differents methods without any results
METHOD 1 - using the  shp2pgsql-gui.exe/postGis shapefile import gui
*Here are the connection details
username postgres
password : i put the same password I usually input in pgadmin4
server host "localhost" then for the port  "5432"
database : gis_analysis  (a postgis database)
*And I get the following error message :

"unable to connect to the database - please check your connection settings"

and the log message

"Connecting:  host=localhost port=5432 user=postgres password='*********' dbname=gis_analysis client_encoding=UTF8
Connection failed."

*I checked TCPIP to check if the port 5432 was opened
I got 3 sessions of pgadmin4 running on remote port 5432  with a state "established" and some sent packets
METHOD 2 - using psql/the windows prompt command
I was planning to use something like this kind of syntax :
shp2pgsql -I -s SRID -W encoding shapefile_name table_name | psql -d database -U
user
But even when I'm running the following command in the prompt command

psql -d gis_analysis -U postgres

*I first get a enter the password : the password does not show up in the terminal  but my understanding is that it's normal.
I typed slowly the
I also copied pasted to make sure I don't make a mistake)
*Eithe way I then  get the following error message (for clarity sake,  I get a japanese message but I put here the standard message in english that seems to be used)

'psql: FATAL: password authentication failed for user "postgress"'

BOTTOMLINE
I can use (import, export, select, alter) a variety of database/tables in pgadmin4 without connection trouble (at worst sometimes it fails because it's too slow but it seems unrelated to me).
I can specifically create a table within my gis_analysis postgis database. I can both import csv file into this table, I can alter/update the table, I can select data from it.
But somehow I cannot import a shapefile
Nor can I use the command prompt, either for the postgis database or any other database.
I am inclined to think it's a username/password problem based on the fact there is no port problem and that I cannot access any kind of database with psql.
But I don't understand why I don't have this connection problem with pgadmin4 and yet when I run either shp2pgsql-gui  or psql  it doesnt work.
I am at my wit's end.
Any insight ?
EDIT : as requested in answer, I m adding a snapshot of connection settings


texts: Oracle tnsping.exe - where to get/download it

texts:
I have recently downloaded latest version of Oracle SQL Developer (4.0.0.13), and my idea was that tnsping.exe should go along with it - but this is not the case. Also I was surprised that SQL Developer is provided as archive of files without any kind of installer.
Can somebody advice where to download tnsping.exe?


texts: Cannot shutdown a shutdown connection MySQL Enterprise Monitor

texts:
We are facing below errors on MySQL Enterprise Monitor.. While deleting a connection from MySQL Instances it is displaying following error messages.
Also it is not deleted event it displays success message.

  Cannot shutdown a shutdown connection
  Connection refused: connect

Any way of doing this? Any table in DB storing information in MySQL databases.

MySQL 5.6.21
Windows Platform


texts: Where is stored the messages results from T-SQL?

texts:
I would like to know if SQL Server store all messages related to T-SQL in a system table or in other object from Database Engine.

Because, I don't know if this is a response of engine or some other stuff.

Now, I did this to capture transactions

And I need to do that for each transaction that I want to "control" (number of register inserted, deleted or updated)


texts:
I am using amazon aurora RDS in my application. I have created cluster endpoint by selecting both read and write instance. Then I used that instance in my application. Whenever I am executing update query I am getting following error:

  The MySQL server is running with the --read-only option so it cannot execute this statement".

I have checked online few fixes.

Make sure you are using cluster end point - Yes i am using cluster endpoint
Read only for mysql is set to 0 - already set.
Cluster is pointing to both reader/writer instance.


texts: How to return the difference (columns) between two databases or tables

texts:
I have a database that has been updated, many columns have been added. Currently the only way of returning the differences between the two databases is by going through the database.cs class that is autogenerated and comparing it with the old database.cs file. I have both database on Sql Server Management Studio (the new structure, and the old structure of the databsae) and want to see all the differences. How can I return the differences between the databases and tables.

E.g.

OLD Db  New Db
TabA    TabA
Col1    Col1
Col2    Col2
Col3    Col3
        Col4

Output
Col4 int not null


texts:
Does activating the TF9567 seeding mode trace flag speed up the synchronization process in AlwaysOn?
I am using SQL Server 2019.


texts: Does an Oracle user have all privileges in its schema?

texts: I'm learning Oracle and did some testing. I used the sys user and created a table t in the scott schema and I found t's owner is scott, and scott has full access to t. I want to confirm, does scott always have full access in the schema scott? No matter who created these objects?


texts: My postgres server is running in a windows server and three tablespace are created. Postgresql server is installed in C: drive and the tablespaces are in D: and E: drive. So how to take a backup and restore. Is it similar to normal backup and while restore, in another machine, do i need to have the same setup or i can restore it in a single drive? From the tablespace folder, is it possible to retreive information for only one table which is huge?


texts: Unable to connect using Azure AD Service Principal on SQL Server

texts:
I'm having trouble testing a connection to Azure SQL Server using SSMS with an active directory service principal.

I have an AD Admin account created, and have successfully added a colleague's AD user account, whom can connect via SSMS.

The service principal is a Web App / Api service principal with a key. And I am attempting to create a database contained user (understanding this has better future compatibly)

Thinking it could be the syntax for creating the user I have tried many variations, however only this syntax has worked:

CREATE USER [username] FROM EXTERNAL PROVIDER

(other variations say the service principle cannot be found or type is not supported).

I have the database name specified in SSMS.

I've tried "Active Directory - Universal with MFA support" and "Active Directory - Password".

The error I receive is "Could not discover user realm" which suggests it's not aware of the active directory tenant to use, however none of these user login formats seem to work (using the password version, I wouldn't expect them to in universal):

user
domain\user
user@domain.com (FQDN)
user_domain.com#EXT#@domain.onmicrosoft.com
object id
application id

However MSDN documentation suggests AD service principals are supported.

I am using azure active directory service principals for other things fine.

Any ideas?

EDIT

The plot thickens, after reading Connect to Azure SQL Database by Using Azure AD Authentication

I wondered if the service principal needed explicit permissions in AD, however modifying the code slightly so it wasn't doing impersonation, I was able to connect fine using c# (I've added the c# tag for stackexchange syntax highlighting)

var sqlConnectionString = "Data Source=tcp:[servername].database.windows.net,1433;Initial Catalog=[databasename];Persist Security Info=False;Connect Timeout=30;Encrypt=True;TrustServerCertificate=False";

string clientId = "[service principal application id]";
string aadTenantId ="[azure active directory id]";

string AadInstance = "https://login.windows.net/{0}";
string ResourceId = "https://database.windows.net/";

var authenticationContext = new AuthenticationContext(string.Format(AadInstance, aadTenantId));
var clientCredential = new ClientCredential(clientId, LINQPad.Util.GetPassword("[the password to get]"));

var authenticationResult = authenticationContext.AcquireTokenAsync(ResourceId, clientCredential).Result;

authenticationResult.Dump();

using (var conn = new SqlConnection(sqlConnectionString))
{
    conn.AccessToken = authenticationResult.AccessToken;
    conn.Open();

    using (var cmd = new SqlCommand("SELECT SUSER_SNAME()", conn))
    {
        var result = cmd.ExecuteScalar();
        result.Dump();
    }
}

Notably, the SUSER_SNAME() bring back the username in the format clientid@tenantid (both guids). However this format doesn't work in SSMS.

EDIT 2

Interestingly if in the C# code I switch to the username / password in the connection string, I receive the same user realm error.


texts: How to encrypt a column using aes-256-gcm encryption in PostgreSQL?

texts:
As a database point person, I am required to encrypt SSN numbers wherever they are in a table. We use PostgreSQL.
My application counterparts require that we use the AES-256-GCM encryption algorithm. However, I am not too sure how to apply that in postgresql.

After googling around, I am able to encrypt using either one of these two AES methods:

1.

UPDATE ssnTable
   SET encrypted_ssn = encrypt(encrypted_ssn::bytea, 'mykey', 'aes')`

2.

UPDATE ssnTable
   SET encrypted_ssn = pgp_sym_encrypt(encrypted_ssn, 'mykey', 'compress-algo=1, cipher-algo=aes256')

None of the above specifically uses the 'aes-256-gcm' algorithm, which is what I'm being asked to use.
Any idea on how to use this algorithm on the column, in PostgreSQL?


texts: Insert data from a source table into a destination table with more columns?

texts:
I want to use INSERT INTO SELECT * to insert data into a dest table from a source table, but I have some extra columns added to destination table. These columns are distributed around the table like 2 are at top and 2 are at bottom.

Also for insertion I can insert some default value like null to these columns.


texts: Recompile not working for DELETE statement

texts:
I want to optimize a deletion from my tables.

I have a table-variable input for my usp of following type:

CREATE TYPE [AddressInfoParts] AS TABLE(
(
    [TcpInfoId]     [BIGINT] NULL INDEX IXTcpInfoId CLUSTERED,
    [EmailInfoId]   [BIGINT] NULL INDEX IXEmailInfoId NONCLUSTERED,
    [PhoneInfoId]   [BIGINT] NULL INDEX IXPhoneInfoId NONCLUSTERED
)

Here I have a deletion:

DELETE FROM [doc].[TcpInfo]
WHERE ID in (SELECT aip.TcpInfoId FROM @AddressInfoParts aip)
OPTION(RECOMPILE)

With following plan:

My question is: why it expects for 1 row (in Clustered index seek), when I specify recompile (and expect, that it will recompile a procedure for last table variable size (2000 in this case)).

I always have a table of the same type, but I don't know how can I tell it to SQL Engine. Because it selects non-optimal plan: it uses loops becuase expects only 1 row when we have N rows there, there is 1-1 relationship between variable and those 3 tables. Each column has X unique values and Y NULLS. But in practice one of them is 0 (we have all nulls or we have all unique non-null values).

When I split table on 3 unique tables, statistics works better, but anyway it expects only 1 row, when receive N:

Please, advice.


texts: Can I enforce one field being dependent on another?

texts:
I would like to know if it's possible to have two fields (in this case bits) in the same table where one is dependent on another. For example Check2 may only be true if Check1 is true.

Note: I'm trying to implement a constraint in an attempt to accomplish this, but so far failing.  I admit I have limited experience with constraints. This is a new database with little data, and there are no entries currently in the table where the conditions aforementioned are not true. That is, there is no record where Check2 is true.


texts: SQL agent jobs hung state (SSIS jobs hung)

texts:
For a couple of weeks I have been experiencing a weird issue on SQL Server 2016 SP1. We migrated a critical server from SQL Server 2008 R2 to SQL Server 2016 with SP1 using side-by-side. We migrated all the databases, logins, db mail, jobs etc.

We have around 150 jobs deployed in SQL Server Agent. At the time of database cut-over (Saturday 5 AM ET) from SQL Server 2008 R2 to SQL Server 2016, few SSIS jobs are in a hung state. Jobs have neither succeeded nor failed; they were showing as in the 'executing' state. We found this kind of behavior only for SSIS jobs (around 39 jobs); the remaining T-SQL jobs are fine.

Interestingly, we could see hung state over transaction log backup as well (since we had created TRN backup job from maintenance plan which run every 1 hour). We had verified the event viewer logs, SQL Server Agent logs, error logs and everything and nothing captured in the logs. We had checked the server health status and everything is up and running fine (CPU 5%, memory 15%) and also verified the locking, blocking status and we couldn't find anything wrong.

Once we restarted the SQL Server Agent, these jobs started working smoothly without any issue and also running successfully on their schedules. After a week, the same issue was repeated at same time (Saturday 5 AM ET), that set of jobs went into the hung state.

Again we verified the logs, blocking...etc. Nothing captured and again restarted the SQL Server Agent service from configuration manager then it got resolved. Here our intention is it's a production server and users reported that if jobs not run then data would become stale. Below log was for all hung jobs after restarting the agent.

Unable to terminate process 1208 launched by step 1 of job
0xB4CF917BAF53234796F42A38EC45B871 (reason: Access is denied)

This issue recurred at Saturday 5 AM EST after the weekly maintenance job (rebuild index--> update stats). Duration of this job is 1 hr 50 mins. After that we found that a few SSIS jobs hung. We don't say it's the exact cause. I was changed the schedule to Sunday 5 AM EST to get the root cause. We had verified the wait types as well and everything was fine.

Finally we came to know that a vendor changed the security key at SFTP side and one batch which caused for remaining jobs hung. So our developers changed the security key manually at appropriate step in SQL job then job running fine. we again facing one issue here in test env.

We didn't accept the key in FTP and it's keep on running (executing) but not affecting to other jobs in SQL Server 2008 R2. Here the scenario is completely different in SQL Server 2016 as this FTP job which is affecting to remaining SQL agent jobs to hung.

Can anyone please guide me if any patch which we required to apply on server for permanent fix or it's a bug in SQL Server 2016?

Jobs working fine over SQL Server 2008 R2 in scheduled manner except batch (FTP) job keep on executing mode. We had migrated all the jobs to SQL Server 2016 and we found the issue when SFTP batch job started executing then after all remaining jobs goes to hung.


texts: Adding Full Text to SQL Server 2014 - do I have to be careful about versions of installation media?

texts:
Hi I'm developer not a DBA, so I'm fairly clueless. I need to add Full Text Indexing to a SQL Server 2014 server. It's a production server on a HyperV VM.

Do I need to be careful about specific versions of the installation media. When I upgraded my dev machine I simply mounted the installation media as a DVD. I just used the most recent 2016 SQL Dev ISO.
It's a licenced sever and I have MSDN subscription so I have access to media. Will generic ISOs be OK?
I'm a little unsure of the environment, I'm unsure how easy it will be to mount the ISO. Is there a way to install from binaries. When playing around installing on my dev machine, I saw a few blog posts saying you could get issues unless the ISO is mounted.


texts: Understand why the query plan was changed

texts:
We had some performance issues with our ERP system. Somewhere around 06:00 the problem started. Long story short; one of my colleagues executed a DBCC FREEPROCCACHE (I know that this is not a preferred action, but that is not the point here). After that, he restarted the whole ERP application, not the database engine. That didn't seem to help. I looked into the Query Store and saw that the query plan was changed somewhere around 06:05. I tried forcing the older plan and the performance issues were gone.

I compared the 2 plan XMLs and I saw that both were based on the same statistics (updated date was exactly the same). The number of changes on the indexes was different, but that did not trigger an update statistics, because else the new query plan would have had a new date for the used statistics. I checked all the statistics of the involved tables, but they were not updated since the last maintenance job. The last maintenance job ran this weekend, which was also the update date of the statistics used in both query plans.

The query is a parameterized query, so why the query plan changed suddenly is a little unclear to me. I would think just the opposite, that a query suddenly runs slow, because the same plan is used for different parameters. This did not seem to be the case here, because I forced the old query plan.

The query comes from some bespoke work that retrieves orders. Now, I suspect that someone issued the parameterized query which would have returned a very large dataset and that slowed down the ERP system. Then, my colleague ran a DBCC FREEPROCCACHE and that triggered the re-compile of the query plan, based on the new statistics from this weekend. The current query just kept running and that is were he decided to restart the ERP application. Now with the new query plan all new queries slowed down until I forced the old query plan.

Could this be the explanation? I am a little skeptical, because SQL Server would trigger a re-compile when statistics have changed, so the plan should already have been re-compiled after the index maintenance job.

I think I am missing something here, but I am not exactly sure what. It is SQL Server 2016 Standard and is a dedicated server for the ERP system.

Edit 15-4-2020 12:17: Now that I write this... I think the issue is just that after the DBCC FREEPROCCACHE the parameters of the first execution of the parameterized query in question used different parameters, which caused SQL Server to recompile the (bad) plan. Or is there still something I am missing?


texts: MySQL database cluster vs. replication setup for large crawler indexes

texts:
What is the most efficient way to set up a MySQL cluster or replication in an application that has a crawler/indexing engine with thousands of read/writes and then a separate front-end for public access.

It is kind of like creating a mini Google search engine. The database that keeps the latest crawl data has to be separate in a way not to impact the performance of the publicly-accessed database. However, eventually the two databases need to be reconciled so that the publicly-accessed database has all the latest data.

So my idea would be the crawler DB would be the master, while the publicly accessed database would be the slave. And then edit/insert actions on the publicly assessed database would really need to be done on the master DB (same as crawler).

Is this the best way to do such a thing? Is there a better setup?

More information:

Currently there is a lot of reading/updating/inserting at a very fast pace while the crawler is running.

For example, the crawler could send 10 businesses with location and items for each business per every 2-3 seconds, at which, the receiving server needs to do the following. Search DB if business already exists (out of few hundred thousand records), if not create it. Then search if that location (out of half a million records) for that business exists, if not, create it. Then loop through each item of the business and check if it exists (out of 40+ million records), update, if not, create it. Old ones are backed up and then deleted.


texts: Getting notification when log truncation is delayed

texts:
Is there any way to notify DBA proactively when transaction log truncation has been queued multiple times however for some reason (it could be an open transaction, replication, currently running full backup) failed to truncate the log.

Considering a scenario in which client application initiate a transaction using BEGIN TRAN and perform a huge update, it failed to acquire necessary locks during the set time (as another transaction was blocking) and it times out (in .net commandtimeout is set to default 30 seconds and no error handling in client application) and leave an open transaction on SQL server which in turn prevent log truncation. Now everytime log backup is run it can't truncate log because you have an open (hosed) transaction. I admit we can subscribe to log growth event in many ways but was wondering if there is something built in to the SQL server that notifies when log truncation has been queued multiple times but it failed to truncate it.


texts:
I joined a company as a fresher DBA and in testing server there is weird problem.

My listener is running very well and developers can connect remotely to database but whenever I tried to start enterprise manager it shows me error like:

  Listener status down. The listener "(Listener name)" is not running
  using the Listener parameter file:
  "D:\app\dbadmin\product\11.2.0\dbhome_1\network\admin/listener.ora" on
  Host: "(Hostname)" at Port: "1521".

When I write emctl status dbconsole it shows me:

  C:\Windows\system32>emctl status dbconsole Oracle Enterprise Manager
  11g Database Control Release 11.2.0.1.0 Copyright (c) 1996, 2010
  Oracle Corporation.  All rights reserved.
  https://(HOSTNAME):5500/em/console/aboutApplication Oracle Enterprise
  Manager 11g is running.

  Logs are generated in directory
  D:\app\dbadmin\product\11.2.0\dbhome_1/(Hostname)_(Listenername)/sysman/log

Here is my listener.ora file:

(Listener Name) =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = (Hostname))(PORT = 1521))
    )
  )

lsnrctl status:

 LSNRCTL for 64-bit Windows: Version 11.2.0.1.0 - Production on 10-JUL-2018 14:33:08

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

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for 64-bit Windows: Version 11.2.0.1.0 - Production
Start Date                02-JUN-2018 19:18:22
Uptime                    37 days 19 hr. 14 min. 57 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   D:\app\dbadmin\product\11.2.0\dbhome_1\network\admin\listener.ora
Listener Log File         d:\app\dbadmin\diag\tnslsnr\ESTDEV03\listener\alert\log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ESTDEV03)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=1521)))
Services Summary...
Service "********" has 1 instance(s).
  Instance "*******", status READY, has 1 handler(s) for this service...
Service "******" has 1 instance(s).
  Instance "******", status READY, has 1 handler(s) for this service...
The command completed successfully.

TNSPING Result:

TNS Ping Utility for 64-bit Windows: Version 11.2.0.1.0 - Production on 13-JUL-2018 10:16:01

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

Used parameter files:
D:\app\dbadmin\product\11.2.0\dbhome_1\network\admin\sqlnet.ora

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = (Service Name))))
OK (20 msec)

TNSNAMES.ORA File:

# tnsnames.ora Network Configuration File: D:\app\dbadmin\product\11.2.0\dbhome_1\network\admin\tnsnames.ora
# Generated by Oracle configuration tools.

(Listener Name)=
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = (service name))
    )
  )

(Listener Name) =
  (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))

I asked old DBA but he was not able to find a solution.
So can you please tell me where is a problem?


texts: SQL Server Linked Server to Azure SQL using Azure AD

texts:
I am trying to set up a linked server from an on-prem SQL Server to an Azure SQL instance.  The Azure SQL instance uses Azure AD for authentication.  When we attempt the connection we get this error

The server it can not open is the piece after the @ symbol in the typical azure ad user name ie:something@company.com
I have searched the internet for answer and I can not find any reference to used Azure AD for a linked server connection.  Is this simply not possible?


texts: Determine whether there is any blocking while deleting large number of records

texts:
I would like to know whether there is any way to determine why it is taking too much time say 3 minutes for deleting 50K records from a database hosted in Sql Azure.
I have clustered index on primary key and a non-clustered index on ID which is being used in where condition in Delete Query

DECLARE @RowsDeleted INTEGER
SET @RowsDeleted = 1

WHILE (@RowsDeleted > 0)
BEGIN
    DELETE top(100) FROM table WHERE ScenarioID= @ID
    SET @RowsDeleted = @@ROWCOUNT
END

Is there any way to know whether there are any locks on this table

indexes:

ALTER TABLE [dbo].[ActCost] ADD  CONSTRAINT [PK_ActCost] PRIMARY KEY CLUSTERED
(
    [ActCostID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON)
GO

 Alter NONCLUSTERED INDEX [IX_ActCost_ScenarioID] ON [dbo].[ActCost] ([ScenarioID] ASC)
INCLUDE ( <list_all_other_columns_returned> );
GO

CREATE TABLE [dbo].[ActCost](
    [ActCostID] [int] IDENTITY(1,1) NOT NULL,
    [ActID] [int] NOT NULL,
    [ActCostTypeID] [int] NOT NULL,
    [Description] [nvarchar](200) NOT NULL,
    [Cost] [float] NOT NULL,
    [CostPerProductUnit] [float] NOT NULL,
    [CostPerEndProductUnit] [float] NOT NULL,
    [OtherValue] [float] NULL,
    [OtherID] [int] NULL,
    [Comment1] [nvarchar](200) NULL,
    [Comment2] [nvarchar](200) NULL,
    [OPerProductUnit] [float] NULL,
    [OPerHour] [float] NULL,
    [OCostPerUnit] [float] NULL,
    [OCostPerHour] [float] NULL,
    [PerfEnh_ProcessID] [int] NOT NULL,
    [PerfEnh_MillID] [int] NOT NULL,
    [ScenarioID] [int] NOT NULL,
 CONSTRAINT [PK_ActCost] PRIMARY KEY CLUSTERED
(
    [ActCostID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON)
)

GO

Azure Space :
Available 2 GB
Used :500MB

I have another database with same data but with more Available Size which works fine i mean it takes just 20-30 seconds to do the same job , Is it going to be a factor ?

Blocking inspection :


texts: Is it possible to re-use a query plan from the plan cache from one database in another database?

texts: For example, if I hit the sys dynamic views to select a specific query plan, am I able to insert that query plan into another database's plan cache, that runs the same exact query? (I know the query is hashed and compared to determine when to generate a new plan, so in my example I would ensure the query truly is exactly the same character by character.)


texts:
I was using Pentaho Data Integration even before Pentaho bought it and call it that.
I have the last free version.

I went on their website recently to see if they had released another version only to find out my favorite open source etl is not much open anymore and not quite free.

Does any of you know of alternatives in affordable, easy to use ETL tools?


texts: Multiple self joins where joining condition not always exists

texts:
I am trying to perform a multiple self join query where one of the conditions it is joining on does not always exist. I have a table that stores different sale events, where a single transaction (sale_id) can have multiple sale events associated with it - for example, transaction with sale_id 123 has four rows with sale_id 123 and sale_types SALE_PROPOSED, SALE_ACCEPTED, SALE_DONE, SALE_EXCEPTION.
I'm trying to write a query that joins all sale types based on sale_id and selects relevant information from them into a single result row. So far I got:
WITH SALES_TODAY AS(
SELECT *
FROM SALES
WHERE ..//date range logic for getting sales for the day)
SELECT SALE_PROPOSED.sale_id, SALE_PROPOSED.customer_id, SALE_ACCEPTED.price,
       SALE_ACCEPTED.accepted_date, SALE_DONE.done_date, SALE_EXCEPTION.exception_description
FROM SALES_TODAY SALE_PROPOSED, SALES_TODAY SALE_ACCEPTED,
     SALES_TODAY SALE_DONE, SALES_TODAY SALE_EXCEPTION
WHERE (SALE_PROPOSED.sale_type='SALE_PROPOSED')
AND (SALE_PROPOSED.sale_id=SALE_ACCEPTED.sale_id AND SALE_ACCEPTED.sale_type='SALE_ACCEPTED')
AND (SALE_PROPOSED.sale_id=SALE_DONE.sale_id AND SALE_DONE.sale_type='SALE_DONE')
AND (SALE_EXCEPTION.SALE_TYPE='SALE_EXCEPTION');

Example table excerpt
sale_id |    sale_type    | ....// other columns
------------------------------------------
123     | SALE_PROPOSED
123     | SALE_ACCEPTED
123     | SALE_DONE
123     | SALE_EXCEPTION
456     | SALE_PROPOSED
456     | SALE_ACCEPTED
456     | SALE_DONE

While it works fine for sales that have all 4 events, it returns 0 rows for sales that don't have a sale_type 'SALE_EXCEPTION' type associated with them.
I also have tried refactoring this SQL with left join for joining with 'SALE_EXCEPTION' but it returned duplicates for sales that had sale_type 'SALE_EXCEPTION' associated with them while working fine for sales without such event.
Could anyone advice what my query is missing so that it would return correct results despite whether a sales_id has sale_type 'SALE_EXCEPTION' or not. Thank you.


texts:
Gave command: "SQL> shutdown immediate" to shutdown DB. But it is hung.
Also I am not able to connect to DB via PL/SQL Developer. Its throwing error: "ORA 12537: TNS Connection closed


texts: insert multi on a table handled by trigger

texts:
I have this table

CREATE TABLE `users` (
  `uid` int(11) NOT NULL AUTO_INCREMENT,
  `user_name` varchar(20) DEFAULT NULL,
  `email` varchar(255) NOT NULL,
  `md5_email` char(32) NOT NULL,
  `entered` date DEFAULT NULL,
  `modified` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `country` varchar(5) NOT NULL DEFAULT 'DE',
  PRIMARY KEY (`uid`),
  KEY `md5_eml` (`md5_email`),
  KEY `entered` (`entered`)
) ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=latin1
/*!50100 PARTITION BY RANGE (uid)
(PARTITION id50k VALUES LESS THAN (50000) ENGINE = MyISAM,
 PARTITION id100k VALUES LESS THAN (100000) ENGINE = MyISAM,
 PARTITION id150k VALUES LESS THAN (150000) ENGINE = MyISAM,
 PARTITION id200k VALUES LESS THAN (200000) ENGINE = MyISAM,
 PARTITION idOthers VALUES LESS THAN MAXVALUE ENGINE = MyISAM) */

& this trigger to check duplicate emails

DELIMITER $$
CREATE TRIGGER checkExistEmail BEFORE INSERT ON users
FOR EACH ROW
BEGIN
IF NOT EXISTS (SELECT 1 FROM users WHERE md5_email = MD5(TRIM(NEW.email))) THEN
 SET NEW.md5_email = MD5(TRIM(NEW.email));
ELSE
 SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Email Already Exists';
END IF;
END $$
DELIMITER ;

the trigger works fine with single insert cmd

but with multi insert it stops when the first duplicate email found

how can i make it ignore duplicates without aborting all operation ?

  mysql  Ver 15.1 Distrib 10.1.21-MariaDB


texts: Database design for tracking referral dollars and referred dollars

texts:
Dropbox has a great referral incentive program that gives a storage bonus based on whether a person is referred to Dropbox or refers others to Dropbox.  What data base design is good for managing information of this nature but with a slight twist.

Say that getting referred to a service gives you a fixed storage bonus of 100 MB.
Further, say that a referrer gets a storage bonus equivalent to the purchased amount by the referral.

So if Tom refers Jerry, Jerry gets 100 MB. If Jerry buys another 300 MB, Tom gets 300 MB of storage free of charge for as long as Jerry is paying for the incremental 300 MB.  Tom might also refer other people and get more free storage according to the same terms and Jerry can do the same.  Given that the people that are paying at any given time is changing, you need to track what each person is entitled to based on their referrals and the amount their referrals are purchasing at any given point in time.

Any suggestions?


texts: Need some help for choosing the replication type

texts:
My target is to create a replication process for the following

Please suggest which type of replication is suitable for this architecture, See the image below


texts: Oracle enterprise manager showing wrong port after change

texts:
I changed the port that Oracle was using from 1523 to the default 1521. The database is running on the correct port now. I can connect on 1521 and run queries.

However the Enterprise Manager is still showing that the port is 1523, and that the database is down.

Why doesn't the OEM know that I'm using the new port? How do I fix this?

Edit: I think I changed the port the wrong way. I changed the listener.ora file, the tnsnames.ora file and ran alter system set LOCAL_LISTENER="(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521))";


texts: How can I set two values at a single command? Here I want to set Country name for Los Vegas and Alaska from a single sql command


texts: Specify the account in domain format Error in SQL Server 2016 installation on Windows 10

texts:
I am trying to install SQL Server 2016 on Windows 10 Pro. In the Server Configuration tab I need to provide the account names, the services will be running as. Independent whether I provide virtual or local accounts an error is generated and I cannot proceed:

Comments?


texts:
Our MySQL database will contain an encyclopedia. The encyclopedia will be shown in pages where every page contains entries starting from a letter.

Which indexes should I use? Should I have two indexes for the field "title" (one index of the length 255 for ordering and one index of the length 1 for indexing by the first letter)?

CREATE TABLE `cyclopedy` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `title` varchar(255) collate utf8_bin NOT NULL,
  `article` mediumtext collate utf8_bin NOT NULL,
  PRIMARY KEY  (`id`),
  KEY `title` (`title`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

Well, now I understand that length 1 is not enough for indexing by the first letter, because an UTF-8 letter may consist of three bytes.


texts: SQL Server Ubuntu server - failure: remote connection from Win 7

texts:
Our analytical lab (chemistry) has an information management system (LIMS) that uses a Microsoft SQL Server database. Technicians interact with the system through a Windows form application that is closed-source and permits only SQLOLEDB.1, MSOLEDBSQL, and SQLNCLI11.1 (Native Client 11) connections (choices are picked from a dropdown box at login). Our production server is SQL Server 2016 running on Windows Server 2012.
The lab has several analytical instruments that are constrained to Win 7 and Win XP for data acquisition. This is an immutable constraint.
On the production setup, all machines make a connection with legacy SQLOLEDB.1 but Win 10 Machines can alternatively connect with MSOLEDBSQL, and SQLNCLI11.1 as well. Win 7 and XP machines cannot.
I am prototyping a SQL Server 2019 upgrade on a Ubuntu server. As configured, no machines can connect to it using the legacy SQLOLEDB.1 connection. The LIMS application is able to connect to it from Win 10 using either MSOLEDBSQL or SQLNCLI11.1. The Win 7 and XP machines cannot connect with any of the protocols.
If this is to work, I have two choices:

Somehow configure MSSQL on the Ubuntu server to accept SQLOLEDB.1 connections.
Somehow configure XP and Win 7 machines to make MSOLEDBSQL or SQLNCLI11.1 connections.

I cannot figure out how to do either one.
Error messages: "could not connect to the database" (when using sqloledb1 indicating that the server does not accept this protocol as configured). "item cannot be found in the collection corresponding to the requested name or ordinal" (when using Native Client 11 indicating that Win 7 does not have the necessary connection capabilities).
As asked, I need to either configure the server to accept SQLOLEDB1 or configure Win 7 to make SQLNCLi11.1 connections.
The Win 7 machine never attempts to make the Native Client 11 connection because it doesn't have the required libraries for this protocol. It does attempt to make the sqloledb1 connection but it cannot because the SQL Server 2019 implementation is not, at least yet, configured to negotiate this legacy oledb connection. There is no error in the server log. The connect simply fails.
I should add that the Win 7 machine is able to make a connection to the production SQL Server 2016 database. But... none of our Win 7 or XP machines has ever been able to make a connection with anything but legacy OLEDB.
The MSOLEDBSQL driver will not install out of the box. The error thrown is KB2999226 Check Failed. I downloaded KB2999226-x86.msu but manually installing it is beyond me because the only mechanism I can see for applying updates on XP is to do it through the web interface (non-existent site). I can extract the .msu but the sprawling directory tree is perplexing. Perhaps there's a way to configure the server to accept legacy SQLOLEDB.1.


texts: Reference a newly created computed column in the same query where we create it

texts:
SELECT
(SUBSTRING(Column1,1,3)) AS ComputedColumn
WHERE ComputedColumn !="dec"
INTO Table2
FROM Table1;

This is an example where I have a computed column (which is essentially created by using an expression). If I run this, I get a "column name invalid" error, because I make a reference to the computed column in the same query where I created it.

In an application called SAS, this is solved by prefixing the computed column name in the WHERE clause with the "(CALCULATED)" keyword. Is there a similar solution in Sql Server.
Other than doing this:

SELECT
(SUBSTRING(Column1,1,3)) AS ComputedColumn
WHERE (SUBSTRING(Column1,1,3)) !="dec"
INTO Table2
FROM Table1;

Which I know it is possible but very confusing when the expression is very huge. There must be another way to REFERENCE A NEWLY CREATED COMPUTED COLUMN in the same query where we create it.

Thanks a billion!
Anna