texts: SQL Server 2017 Availability Groups - Can a Long-running transactions on a primary replica block a secondary replica from seeing updates?

texts:
The following two links are each very explicit regarding how long running transactions on the primary impact an asynchronous secondary replica.

https://technet.microsoft.com/en-us/library/dn135335(v=sql.110).aspx
https://blogs.msdn.microsoft.com/alwaysonpro/2015/01/06/troubleshooting-redo-queue-build-up-data-latency-issues-on-alwayson-readable-secondary-replicas-using-the-wait_info-extended-event/

  Asynchronous Commit

  A long-running transaction on the primary replica prevents the updates
  from being read on the secondary replica.

  All read workloads on the secondary replica are snapshot isolation
  queries. In snapshot isolation, read-only clients see the availability
  database on the secondary replica at the beginning point of the oldest
  active transaction in the redone log. If a transaction has not
  committed for hours, the open transaction blocks all read-only queries
  from seeing any new updates.

My testing suggests otherwise.

With SQL Server 2017 Enterprise edition, I have created an AlwaysOn Availability Group with two Availability Replicas.
Both are configured with:

Availability Mode = Asynchronous Commit
Failover Mode = Manual
Readable Secondary = Yes

I have single table named "Test" with two columns [Id (IDENTITY+PK), Description]

On the primary, I insert 'beforelongtransaction'
On the primary, I start a transaction that inserts 'longtransaction'. I do not commit the transaction.
On the primary, I confirm with DBCC OPENTRAN that a long running transaction is executing.
On the primary, I insert 'whilelongtransaction'
On the secondary, I execute "SELECT * from Test"

I receive the results:
| Id | Description |
| 1 | 'beforelongtransaction' |
| 3 | 'whilelongtransaction' |

On the primary, I commit the long transaction from Step 2.
On the secondary, I execute "SELECT * from Test"

I receive the results:
| Id | Description |
| 1 | 'beforelongtransaction' |
| 2 | 'longtransaction' |
| 3 | 'whilelongtransaction' |

Has SQL Server 2017 (or 2014/2016) removed this limitation?
Or am I misunderstanding and not properly reproducing the limitation?


texts: Why my mongodb Shell not getting started even after my mongodb server is running?

texts:
I am not getting the right problem what might be making the mongodb shell not getting initiated.
Here is the gist of my log of MongoDB:  https://gist.github.com/JafferWilson/c6f964060bc27014d25e22066ed5f292
I do not know why it is showing the Index Build 2% or 30%.
Kindly, suggest me what might I do to initiate my shell and starte performing. I am running out of time due to this delay. PLease suugest me.


texts: Select rows other than first selected row in a table

texts:
My table looks like

 id     ledgerID    Amount  Debit or credit     TransactionType
 --------------------------------------------------------------
  1         1        500       dr               Sale1
  2         2        300       cr               sale1
  3         3        400       dr               sale1
  4         1        1000      dr               sale2
  5         2        200       cr               sale2

Now I like to select this table like

sno  LedgerID   Transaction     Dr        Cr
-----------------------------------------------
1    1          sale1          500
         2      300 cr
         3      400 dr
2    1          sale2         1000
         2      200 cr

How to do this?


texts:
I have a database that was originally created by a couple of people for a small business unit (about 8 staff and 10 customers) in MS Access.   The original 'developer' was a non-geek who had been sent on a two day ms access course by a previous employer. It was poorly designed but worked well enough, as the unit grew there was the need to have more than one person administrating the data.  At this time I had joined the unit and suggested using SQL server as the back end and keeping the access front end allowing multiple users, three or four at that time.

It now has approx 40 users servicing 50+ business customers, and is essentially a complete mess.  But we need this tool to keep track of our jobs queue, who is doing what, when it is due, who for and every thing.  Without it we could not function.

So I now want to throw it away and rebuild from scratch and do it properly, and ditch the access front end in the process.  While doing this I need to keep the current system running, develop the new one and some how keep the data in sync and run them in parallel for a while until the higher powers are satisfied it's not going to cause any problems.

Does any one have an suggestions or pointer on how to keep the data in sync given that I will be taking several giant tables and normalizing them into sensible tables in the new schema?

It is not a huge database, the main table listing all jobs grows by about 1500 records per month for example, and currently has about 100,000 rows.

My thoughts were to add a row version column to the tables, and then hack something together to monitor it and translate to the new schema.

Is there a better way?


texts: SQL Mirroring synchronize logins and agent jobs

texts:
We have just started using SQL Server mirroring for several clients on SQL Server 2008 R2 Standard.

We've got the SQL Server mirroring working fine however now we run into the issue of replicating logins, and agent jobs. I found this article which shows how to synchronize logins but I wasn't able to get this working. I couldn't specify the source and destination server.

Anyone run into the issue of synchronizing logins and agent jobs after having databases mirrored? If so, what workaround have you come up with?

Thanks in advance.


texts: How to retrieve a large text from inside a table?

texts:
for my own records I have a table in one of my servers where I like to save my activities and scripts

the table definition is:

IF OBJECT_ID('[dbo].[activity]') IS NOT NULL
DROP TABLE [dbo].[activity]
GO
CREATE TABLE [dbo].[activity] (
[dt]        DATE                             NOT NULL,
[i]         SMALLINT                         NOT NULL,
[activity]  NVARCHAR(max)                     NOT NULL,
[script]    NVARCHAR(max)                     NULL
           CONSTRAINT [DF__activity__script__6E01572D] DEFAULT (NULL))

GO
CREATE CLUSTERED INDEX [pk_activity]
   ON [dbo].[activity] ([dt] desc, [i] desc)

as this table is located in the DBA database on my DBA_SERVER server,
from my local machine I created a linked server to the DBA_SERVER so that I can insert scripts into my table.

this is how I insert a script into my table, the script is located on my local machine:

    INSERT INTO [MY_DBA_SERVER].[DBA].[dbo].[activity]
               ([dt]
               ,[i]
               ,[activity]
               ,[script])
    SELECT
               DT=cast ('3 may 2018' as date)
               ,I=1100
               ,ACTIVITY='MOVE APPLICATION ROWS - the Ids work'
               ,[script]=BulkColumn
    FROM Openrowset( Bulk 'C:\Users\MMiorelli\Downloads\applicationID consolidated.sql',
                     Single_Blob) as [the script]

and this seems to be working fine, because when I run the select below:

SELECT [dt]
      ,[i]
      ,[activity]
      ,[script]
  FROM [MY_DBA_SERVER].[DBA].[dbo].[activity]
GO

I can see that the stuff I have just inserted is on my table.

and when I run the following script I find out how much data is in each field:

SELECT [dt]
      ,[i]
      ,[activity]
      ,[script]
      ,len_activity=DATALENGTH( [activity])
      ,len_script=DATALENGTH( [script])
  FROM [MY_DBA_SERVER].[DBA].[dbo].[activity]
GO

and that gives me this:

My problem is that I cannot retrieve the whole content of my script.
I have tried to copy it using the mouse as you can see on the picture below:

But I have some hairy scripts that are very long, and copying like that cuts them short of their whole.

my question is:

How to retrieve this script from my table?

it can be via select or even saving it to a file,
I would prefer a T-SQL way of doing it if possible.

Just a curiosity
would that be the same way for images?


texts:
I have recently started a role as a junior DBA... Yesterday I created a new database for a co-worker and my boss asked me to make sure we get a backup plan on it asap.

I didn't think much about it, went into the agent, created a job that took a full backup every night @ 1AM.  Being proud of myself I went to bed and didn't think anymore about it.  This morning I started thinking that was not "Good enough" - if the database died at 12AM, then they would lose almost 23 hours of data, which would probably get me fired. :)

So to make sure I understand this - I think I need to do Differential Backups in addition to my full backup.. After looking at this tutorial, I had a few questions:

Would I just go into the agent and add a job that happens at every hour (for example) that creates a differential backup?
If my understanding is correct, that would backup the Transactional log every hour until 1AM when it does a Full backup, then it would "Reset" the T-Log and start over again from the next day, so at the most would be 1 hour of data loss - Is that correct?
So in the end, I would have 2 jobs on the agent, one that fires off every day @ 1AM to do a "FULL" backup, and one that fires off every hour that does a differential backup?


texts: Why am I seeing high execution counts for the system stored procedure sys.sp_columns_managed

texts: We are seeing high wait times during business hours on one of our databases. Looking at SolarWinds I am finding that the system stored procedure 'sys.sp_columns_managed' is being executed more than 300,000 times per hour (according to SolarWinds). Has anyone else ever experienced this? Does anyone know what can be done to stop this? Or, am I not seeing things correctly in SolarWinds?


texts: Upgrade to SQL Server 2008 R2 Standard with Mirroring (1 Thread Limit)

texts:
I am upgrading a mirroring pair of SQL2005 Enterprise servers to SQL2008R2 Standard which has 1 thread for mirroring limitation.

As far as I understand this limitation applies to redo queue: http://social.msdn.microsoft.com/Forums/en-US/sqldatabasemirroring/thread/a5b58253-cf4a-4dd6-9472-c484347d39ab/ but I don't fully understand what this means.

My question is: how do I measure the current database system to make sure I won't run into problems with this upgrade (with this 1 thread limitation)?


texts:
It may sound stupid but it's my first experience ever with databases.

It's a simple database for a car accessories store. I was hoping when making a sale on a certain item it effects the quantity of this item, not sure if this is possible with this design. Also as shown in the image an item could be for one car or more so I thought it should be many to many.


texts:
I would like to store some protobuf objects in a MySQL database. The objects are created in a Java application. The object is accompanied with a textual type identifier and a timestamp.

+------------+--------------+------+-----+---------+----------------+
| Field      | Type         | Null | Key | Default | Extra          |
+------------+--------------+------+-----+---------+----------------+
| id         | mediumint(9) | NO   | PRI | NULL    | auto_increment |
| type       | varchar(30)  | NO   | MUL | NULL    |                |
| timestamp  | date         | NO   | MUL | NULL    |                |
| pixel_data | ?            | NO   | MUL | NULL    |                |
+------------+--------------+------+-----+---------+----------------+

What should be the datatype of pixel_data?

My best guesses are either BLOB or TEXT.

What's the difference between TEXT and BLOB? Which one is better for Java serialization of protobuf objects? Since Java serializes using byte streams, I assume that BLOB is better, but I didn't find any reference for that.
Assuming that I can only estimate object sizes, which quantifier should I choose (TINY, MEDIUM or LONG)? How much space is wasted for the larger sizes?


texts: MySQL replication fails due to duplicate keys after master is restarted

texts:
I'm new to MySQL load balancing, I want to try the master-slave concept.

Whenever I setup master-slave for the first time, and both have up-to-date fresh data, that works perfectly fine. However,  whenever the master goes down, or I stop and then restart it, the slave starts generating errors because of duplicate keys.

Why would this be happening and how can I prevent this?

My application has a lot of update queries something like 5 SELECTs then 3 UPDATEs or INSERTs

My data will grow with time and I'm expecting quite a high load in the future.


texts: View all 32 possible Binary combinations for 5 digits

texts:
I have 5 digits

A,B,C,D,E

Is it possible in SQL to view all the possible Binary combinations for them?
(Not the list of 120 possible permutations, just the 32 possible combinations in SQL http://www.mathsisfun.com/binary-combinations.html)

Thus

A   B   C   D   E
1   1   1   1   1
1   1   1   1   0
1   1   1   0   0
1   1   0   0   0
1   1   0   0   0 etc

Thank you


texts: Automating SQL scripts in Toad for Oracle (13.1)

texts:
I'm not a DBA, so bear with me.

I've scheduled tasks through Toad's automation designer, but this uses Windows task scheduler. It's using Oracle 12c. All I'm doing is updating (creating/dropping) tables with the most recent data. Then I use an ODBC to report on that data in another application.

How can I ensure my scripts execute on time without running a local machine with scheduled tasks? Can this be done without additional software? Thanks in advance.


texts:
I have 2 slaves where one is working as expected, but the other slave is extremely slow when executing update statements. The insert and delete statements are fast.

I used the command check table tbl_name to see if any MyISAM table was corrupt. Both the slaves are MyISAM only.

I am using (AWS ec2 instance) ext3 for the fast slave and xfs for the the other. Is that the reason why the updates are taking time?


texts: Using relational algebra to check a condition for all

texts:
I encountered this question while revising for my finals exam on database theory.

The following database contains information about car repair workshops. The following tables are used:

Given two workshops W1 and W2, W1 is more expensive than W2 if for every car C that is repaired by both W1 and W2, the repair price for W1 is higher than the repair price for W2 for C. Write a relational algebra query to find all workshops (W1, W2) where W1 is more expensive than W2. Exclude workshops that do not repair any common cars.

I attempted to use a join naively:

And then simply project out the rname on a join with Workshop. However, I realised that there would exist some Repairs where c1 < c2 and c1 > c2 are both present, and it would still indicate that W1 is more expensive than W2.
I am thinking of using the division operator, but I am not sure how to proceed.


texts: ulimit 'open files' and MySQL max_connection

texts:
When running mysql on SSD, does max_connections, if set more than the kernel's ulimit open files, be limited to the kernel's limit?

My understanding is that if the kernel's ulimit open files is 1024 for example, any value of max_connection for mysql greater (e.g, 3000) than the ulimit open files will be limited to just 1024.

My question is, does this also hold for SSD?


texts: Tools and techniques for untangling monster views (nested views)

texts:
First a bit of context (venting). I'm working with a new vendor product which is really nothing more than a SQL Server database with a .NET interface.

First few weeks with this client and users are complaining about performance constantly with even the most bread and butter reporting functionality. I finally get DB access and discover the worst set of nested views (Is nested view a good database design?) I've seen in my life. We're talking a tree of views that is 5 nodes deep and each node has 3-5 leaves. So a single query has 20+ views behind it. It's mind bogglingly bad. The explain plan looks like a fractal after ten million iterations. I can't even read it.

I need to untangle at least this one monster view. But I have no ideas on how. I found this post (http://www.midnightdba.com/Jen/2010/06/detangling-nested-views/) which has a handy SQL script that lists the referred objects but that information doesn't get me terribly far on getting the actual table references for each field in the parent view. If I were to do it by hand, it would probably take 2 weeks. If I got it down to inline form, are there any tools that could remove redundancies and unused fields and optimize the query?


texts: Best practices for selecting partition boundary ranges to maintain empty partitions at both end?

texts:
I have partitioned the table based on a date datatype partitioning column.

The data for the column starts at "2014-01-01" and ends at "2015-01-01". I know that, the partitions should have empty partitions at left and right ends. So, I selected the following boundary ranges while creating the partition function. The partition function is shown below,

create partition function PfDateRange(date)
as range right for values('2014-04-01','2014-07-01','2014-10-01','2015-01-01')

I would like to hear some tips or good practices for selecting boundary ranges.

Also, at future the data may get inserted which were greater than the last boundary range. So how to maintain empty partition at the right end?

Thanks in advance.


texts:
Which performance counter (System Monitor) should I use to answer the questions:

total amount of memory currently used by SQL Server
total amount of memory used by a process
total amount of memory required by SQL Server for running processers efficiently.

I think that learning about the answers to these questions is the beginning of the evaluation of memory (global).


texts:
I have scheduled oplog incremental dump using mongodump but it fails very often on majority of the servers. I have a shard cluster with around 200GB of oplog size defined and around 30-40 hours of oplog window.
I am thinking it is because of message "WiredTiger record store oplog truncation" just before the dump starts which is common during every failure. Other times, the dump runs perfectly fine. I have analysed the oplog window and size and it is fine. The oplog start data was way older than the timestamp I am using during oplog dump.
Below are some of the logs :

From mongod.log and my backup script :

2020-07-19T16:28:10.472+0000 I STORAGE  [WT RecordStoreThread: local.oplog.rs] WiredTiger record store oplog truncation finished in: 420ms

2020-07-19T16:28:10.502+0000 E QUERY    [conn74791] Plan executor error during find command: DEAD, stats: { stage: "COLLSCAN", filter: { $and: [ { ts: { $lte: Timestamp(1595176086, 2244) } }, { ts: { $gt: Timestamp(1595173520, 25) } } ] }, nReturned: 0, executionTimeMillisEstimate: 2970, works: 356598, advanced: 0, needTime: 356597, needYield: 0, saveState: 2787, restoreState: 2787, isEOF: 0, invalidates: 0, direction: "forward", docsExamined: 356596 }```

oplog-prd-mon-XYZ-shard-hd03.c.XYZ-dr.internal-20200719.log-2020-07-19T16:28:06.794+0000 reading password from standard input
oplog-prd-mon-XYZ-shard-hd03.c.XYZ-dr.internal-20200719.log-Enter password:
oplog-prd-mon-XYZ-shard-hd03.c.XYZ-dr.internal-20200719.log-2020-07-19T16:28:07.153+0000 writing local.oplog.rs to stdout
oplog-prd-mon-XYZ-shard-hd03.c.XYZ-dr.internal-20200719.log-2020-07-19T16:28:09.795+0000 local.oplog.rs  0
oplog-prd-mon-XYZ-shard-hd03.c.XYZ-dr.internal-20200719.log-2020-07-19T16:28:10.517+0000 local.oplog.rs  0
oplog-prd-mon-XYZ-shard-hd03.c.XYZ-dr.internal-20200719.log:2020-07-19T16:28:10.517+0000 Failed: error writing data for collection `local.oplog.rs` to disk: error reading collection: Executor error during find command :: caused by :: errmsg: "CollectionScan died due to position in capped collection being deleted. Last seen record id: RecordId(6850019249918838732)"


texts: Cartesian product of two tables not working as expected in MySQL but fine with SQlitee?

texts:
I was practising some sql joins and decided to create a schema online rather than using my local database on sql fiddle. My schema is pretty simple

table 1 (5 entries): (emp->{id,name,dep})
table 2 (4 entries): (dep->{id,name})

I was supposed to write one inner join on dep column which worked with SQLite, but when I tried the same query with MySQL it failed. So in order to investigate, I just checked the Cartesian product of two tables for both the databases.

I tried this query

select * from dep a,emp b

in both databases.

In SQLite, it correctly return 20 entries with 5 columns in it. But in MySQL, it is returning correctly 20 entries but only 3 columns in it (id,name,did). So this explains why the join was breaking earlier.

I can't understand how can this Cartesian product be incorrect? Can someone please explain to me, what is going wrong in MySQL case?

MySQL fiddle

SQLite fiddle


texts: Inconsistent statistics on jsonb column with btree index

texts:
I noticed that the performance of a query involving a jsonb column varied significantly between VACUUM ANALYZE runs while testing it. I get completely different execution plans seemingly randomly after analyzing the table.

I'm using Postgres 9.6 here. The setup for my tests is the following, I'm inserting a single key "x" into the jsonb column "params" with values between 1 and 6, with 1 being the rarest value and 6 the most common. I also have a regular int column "single_param" that contains the same distribution of values for comparison.:

CREATE TABLE test_data (
    id      serial,
    single_param    int,
    params      jsonb
);

INSERT INTO test_data
SELECT
    generate_series(1, 1000000) AS id,
    floor(log(random() * 9999999 + 1)) AS single_param,
    json_build_object(
        'x', floor(log(random() * 9999999 + 1))
    ) AS params;

CREATE INDEX idx_test_btree ON test_data (cast(test_data.params->>'x' AS int));
CREATE INDEX idx_test_gin ON test_data USING GIN (params);
CREATE INDEX ON test_data(id)
CREATE INDEX ON test_data(single_param)

The query I'm testing is a typical query for paginating results, I'm sorting by id and limiting the output to the first 50 rows.

SELECT * FROM test_data where (params->>'x')::int = 1 ORDER BY id DESC LIMIT 50;

I get one of the two explain analyze outputs randomnly after running VACUUM ANALYZE:

Limit  (cost=0.42..836.59 rows=50 width=33) (actual time=39.679..410.292 rows=10 loops=1)
  ->  Index Scan Backward using test_data_id_idx on test_data  (cost=0.42..44317.43 rows=2650 width=33) (actual time=39.678..410.283 rows=10 loops=1)
        Filter: (((params ->> 'x'::text))::integer = 1)
        Rows Removed by Filter: 999990"
Planning time: 0.106 ms
Execution time: 410.314 ms

or

Limit  (cost=8.45..8.46 rows=1 width=33) (actual time=0.032..0.034 rows=10 loops=1)
  ->  Sort  (cost=8.45..8.46 rows=1 width=33) (actual time=0.032..0.032 rows=10 loops=1)
        Sort Key: id DESC
        Sort Method: quicksort  Memory: 25kB
        ->  Index Scan using idx_test_btree on test_data  (cost=0.42..8.44 rows=1 width=33) (actual time=0.007..0.016 rows=10 loops=1)
              Index Cond: (((params ->> 'x'::text))::integer = 1)
Planning time: 0.320 ms
Execution time: 0.052 ms

The difference is that the estimate for the number of columns matching the where clause is different between the two plans. In the first one the estimate is 2650 rows, in the second one 1 row while the real number is 10 rows.

The following version of the query that can potentially use the GIN index does seem to use a default estimate for the json column of 1%, which also results in the bad query plan like above:

SELECT * FROM test_data where params @> '{"x": 1}' ORDER BY id DESC LIMIT 50;

My original assumption was that Postgres would not have any statistics on the jsonb column and always use an estimate like it does for the query using the @> operator. But for the query that is written to be able to use the btree index I created, it uses different estimates. Sometimes those are good enough, and sometimes they're bad.

Where do those estimates come from? I guess they're some kind of statistic Postgres creates with the index. For column statistics there is the option to collect more accurate statistics, is there anything like that for these statistics here? Or any other way to get Postgres to pick the better plan in my case?


texts: Comparing Query Performance Across DBMSs

texts:
I have a list of queries that I want to compare the execution time of across 3 different DBMSs: SQLite, MariaDB and postgreSQL.
I have been looking into command line tools for each system to get the query execution time.

For SQLite I used the .timer ON command to show the real/user/sys time after each query
For MariaDB I set the profiling and called "show profiling" to get the duration of each query
For PostgreSQL I executed the query with "analyze" to show the full query plan including execution time.

My problem is that I wasn't able to find documentation for these tools that specified what the time actually is: CPU vs actual time etc, and some of the values are very different. For example, a join query that I tried with MariaDB took 33 mins but with postgreSQL it only took a few seconds.
Does anyone have insight into how these metrics are actually measuring query time, and if they can accurately be compared with each other? Alternatively, is there a better tool I should be using for this task? Any help is appreciated.


texts: Are we required to handle Transaction in C# Code as well as in stored procedure

texts:
Do we really require transaction handling in c# as well database store process both side

C#:

Using(transaction with transaction scope)
{
     Execute stored proc;
     Transaction. Complete;
}

SQL stored procedure:

Create process
As
Begin try
    Begin transaction
    Commit
End try
Begin catch
    Rollback
End catch


texts: adding 2nd catalog for linked server to DB2

texts:
I am unable to figure out how to add multiple catalogs for a DB2 linked server.
I have an initial Linked server setup and working properly, but I am struggling to figure out how to add multiple databases from the DB2 instance (version 11.1), using below:

Microsoft SQL Server 2014 (SP2) (KB3171021) - 12.0.5000.0 (X64)   Jun 17 2016 19:14:09   Copyright (c) Microsoft Corporation  Standard Edition (64-bit) on Windows NT 6.3  (Build 9600: ) (Hypervisor) .


texts:
Every night a full backup is made of all the user databases. Every 15 minutes, transaction logs are created. Today i succesfully restored a database with the use of transaction logs. I set the time (08:30) and set it with the option WITH REPLACE, and pressed the Ok button.

It looked like this.

Afterwards (a hour later) i wanted to restore it again. But i saw no full backup and transaction logs. (transaction logs are still created on the server)

Any suggestions for restoring databases with transaction logs for multiple times? Or is this not an option?


texts: Sql Server log: is 'Backup|Log was backed up' the same as 'BackupDatabase: Log files done'?

texts:
One transaction log file is growing and growing for a couple of days and I can't find out why. Now I figured out that either the way the activities are stored in the sql logfile OR the behaviour of sql server/backup software changed.

The log files have been backed up every 15 minutes by a third party software.

Until a specific point some days ago, the log backup event is visible every 15 mins as it always used to in the log with the following entries (newer first):

  BackupLog: SafeCheckpoint done

  Backup: Media open

  BackupLog: Work estimate done

  BackupLog: Log files done

  BackupLog: Writing history records

  Log was backed up. Database: xy_db, creation date(time):
  2014/01/10(03:31:14), first LSN: .............. This is an
  informational message only. No user action is required.

  BackupLog: Writing history records

  BackupLog: Finished

After that specific day, there is no more such entry visible.
But instead I can see the following log entries indicating something like a log backup as well (newer first):

  BackupDatabase: Log files done

  Database differential changes were backed up....

  Completed file yyy.mdf

  Started file xxx.mdf

  BackupDatabase: Log files done

  BackupDatabase: Database xy_db

I wonder if or why the presentation of the log backups may have changed and if the new (bolded) entries indicate the same action? I have checked

 DBCC TRACESTATUS (3226);

that tells me status = 0 but I can't find any hints in the log that this ever has been changed these days what also makes me unsure if those Backup Log" entries I was used to ever depended on this trace flag?

EDIT:
I have checked the trace flags for different servers. the trace flag 3226 is turned off but I can see the Log entries I am used to in the sql server logs. So this seems not to be involved here.

Where are those different / new log entries possibly coming from?


texts: Is mongoreplay included in the toolset with mongodb 4.0 community version for windows?

texts: Where to find mongoreplay tool in mongodb 4.0 community version for windows? It is not in the same place as other tools - mongoexport, mongoimport, etc.


texts:
Hi can anyone help with this little MS SQL Puzzle.

I basically need to get a 1 random answer from each of the submitters.

id,   submitter,   answer

1,    James,        Blue

2,    James,        Red

3,    James,        Orange

4,    Ian,        Yellow

5,    Ian,        Pink

6,    Robert,       Red

I have tried the following but obviously this will always get the min(id) or Max(id)  what I need is a random(id)?

select * from #tmpData
 where id in (
  select min(id ) FROM #tmpData
   group by submitter
            )

Thanks in advance..


texts: Native MariaDb encryption and clustering

texts:
Is native (table/Column/tablespace) database encryption possible in a clustered environment. The success criteria would be:

Encrypted Logs
Encryption in transit for all data protocols
Native DB encryption
High availability for 2 sites with ~30 ms latency between each
1-2 local copies at each site for Backups/reporting etc
Sub 30 minute site failover time
Minimal application re-engineering
Assume AWS or Vault KMS

I just need a yes or a no at this stage.
Thanks


texts: Query for a single unique record takes about 1.5 seconds

texts:
I have a simple table with a 13 byte unique key (indexed unique, non-clustered) and about 6 other small fields of no more than 10 bytes each. The table has about 8 million records. I have rebuilt the index. No fragmentation of the index or the file on disk. I am doing a query to retrieve one of these rows. I am doing a loop say 1000 of these queries for records I know exist in the database and it takes about 25 minutes.  The code is in a c# program. It has the same response time whether I do it across our 1GB network or directly on the machine with the MS SQL server.  I have tried suggestions with (index) and nolock - nothing changes.  It wont let me do a forceseek, I get an error.
If I do the same query within the sql management express (or whatever it is called) it returns immediately, no perceptible delay.

So why would my c# program take soooo long.  Seems like it is the c# program more than the query itself.  Nothing fancy or unusual.

I know someone will want to see the code, and I can get that. For now I wondered if there is something known about the mssql c# libraries that is known to cause such slowness.  I have tried doing it a number of different ways. I do not open and close the connection with each query.  Is there something I missed about how I should build the connection string?

Basically I am at a loss.


texts: Do applications have to specify TCP: in the connection string to availability group listeners?

texts:
In articles that I have read about applications accessing databases through the availability group listener, they all seem to want to use TCP: prefix before the listener name in their connection strings to force a TCP connection.

My question is: is this really necessary? I've tested a few applications without using this prefix and they all successfully established a TCP connection to the primary server in the AG group. I also tested failover to one of the synchronous secondaries and that seems to have gone smoothly as well.


texts:
While I'm working on index tuning I would love the history bar to populate with each run so I can see the differences, I'm probably using the tool incorrectly. My current workflow is:

Create New Plan Explorer Session
Paste Query
Click "Get Actual Plan"
go to Index Analysis
either use the <S> or manually script out an index change
click "Get Actual Plan" again and instead of adding history version 2 it just overwrites history version 1.


texts: MySQL query exponentially slower with parameter in subquery vs hard-coded value

texts:
I have the following MySQL query that is querying naughty ip addresses from the last 24 hours and ban count >= 2. I don't want them if they are in an extra naughty ban list from the last 7 days and ban count >= 5. The query takes 3 minutes to run. If I replace @__cutOff_1 with the actual hard coded date inline in the query instead of a variable, the query takes 2 seconds to run.

Is this a bug in MySQL? The query explainer looks like this:

I am using the latest MySQL x64 8.0 on Windows 10 x64 as of 2019-07-30. Submissions table has 8 million rows, ip addresses table 360K. All where clauses are indexed.

SET @__cutOff_0 = '2019-07-29';
SET @__minBanCount_1 = 2;
SET @__cutOff_1 = '2019-07-23';

SELECT `i`.`IPAddress`, `i`.`BanCount`, MAX(`s`.`Timestamp`) AS `c`
FROM `Submissions` AS `s`
CROSS JOIN `IPAddresses` AS `i`
WHERE (((`s`.`Timestamp` >= @__cutOff_0) AND (`i`.`BanCount` >= @__minBanCount_1)) AND (`s`.`IPAddressId` = `i`.`IPAddress`)) AND `i`.`IPAddress` NOT IN (
    SELECT DISTINCT `i2`.`IPAddress`
    FROM `Submissions` AS `s2`
    INNER JOIN `IPAddresses` AS `i2` ON `s2`.`IPAddressId` = `i2`.`IPAddress`
    WHERE (`s2`.`Timestamp` >= @__cutOff_1) AND (`i2`.`BanCount` >= 5)
)
GROUP BY `i`.`IPAddress`, `i`.`BanCount`
ORDER BY `c` DESC


texts:
I upgrade Oracle from 11.1.0.6.0 (32bit version) to 11.2.0.1.0 (64bit version) using RMAN on the same Windows computer (64bit).

After RMAN restored and recovered, I then try to run

SQL> startup upgrade;
SQL> @?/admin/catupgrd.sql

I got the following errors:

SELECT TO_NUMBER('MUST_BE_SAME_TIMEZONE_FILE_VERSION')
ERROR at line 1: ORA-01722: invalid number

My timezone version is

SELECT version FROM v$timezone_file;
timezlrg_11.dat VERSION: 11

select * from sys.registry$database;
Microsoft Windows IA (32-bit)
TZ_VERSION: 4


texts:
I'm new to MySQL and installed MySQL in Ubuntu 16.04. At the time of installation I have given password for root.

In the terminal I can access only root user and created user only.

bharath@bharath-desktop:~$ mysql -uroot -pbharath

  mysql: [Warning] Using a password on the command line interface can be
  insecure. Welcome to the MySQL monitor.  Commands end with ; or \g.
  Your MySQL connection id is 7 Server version: 5.7.16-0ubuntu0.16.04.1
  (Ubuntu)

  Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights
  reserved.

  Oracle is a registered trademark of Oracle Corporation and/or its
  affiliates. Other names may be trademarks of their respective owners.

  Type 'help;' or '\h' for help. Type '\c' to clear the current input
  statement.

  mysql>

But I am not able to access the default MySQL user in the terminal. It shows like this:

bharath@bharath-desktop:~$ mysql

  ERROR 1045 (28000): Access denied for user 'bharath'@'localhost'
  (using password: NO)**

If I hit enter after MySQL, it's not authenticated. How to access this?


texts: SQL Server 2017 Availability Group Deployment - Some availability replicas are not synchronizing data

texts:
I'm in the process of configuring my first cluster using SQL Server 2017's availability groups.  I have some experience with SQL Server failover clusters, but wanted to migrate to the Availability Group technology.  I have created to Windows 2019 (DB01 and DB02) servers and configured them as a typical Microsoft Cluster.  I have also installed SQL Server 2017 on both servers, using identical configurations.  Data on drive D, logs on drive E, and backups (for now) on drive D:.  So far, so good.  The SQL services are running under a domain account named svcSQL.   I've restarted both servers to verify that the service starts correctly.
I restored my two databases (called Lookup and Demo) on to DB01, and left DB02 empty.  My intent was to let DB01 seed DB02.  Using the Availability Group Wizard, I created a new availability group called Lookup, and added the DB02 server as a replica.  DB02 shows up as a secondary, and I checked the "Automatic Failover" checkboxes for both servers.  I left the "Endpoints" tab with the default values.  All items on the "Backup Preferences" tab were unavailable, so I left them alone.  I added a listener using a DNS name that was already created on the domain controller, and provided its matching IP address.  I selected Automatic Seeding for the Data Synchronization panel.
The validation screen showed all green items, as did the Results panel.  However, the dashboard showed that the availability group was disconnected.  I did a "grant connect on endpoint..." to the SQL service account, which resolved that message.  I have also temporary disabled the Windows firewall on both servers.
At this time, the dashboard shows a critical error and two warnings.  The critical error states that the Availability group is not ready for automatic failover.  That makes sense, as the synchronization process is not complete.
The two warnings are that "Some availability replicas are not synchronizing data" and "Some synchronous replicas are not synchronized."  I gather the first warning is likely a result of the second warning.
I'm wondering if anyone can offer me some pointers on how to resolve this.  The process seemed pretty straightforward, but something is definitely not right.  I've tried creating the group a few times with variations on the steps above, but I seem to wind up with the same results each time.
Thanks in advance for any insights you can provide on this.  I'll gladly provide any additional information that would help resolve this.
Thanks!


texts:
I realize this is a little random, but DB2 (circa V8) had a feature to define tables from the CREATE VIEW syntax.  Basically, the view was saved to disk to save the cost of recreating the dataset each time the view is called.

All I really need to know is what was this feature called?  I was thinking it was Federated Views, but that seems to be about incorporating external data into the database.  Would this work for saving aggregated data from within a database or is there another name for this feature?

Please help an old guy out.


texts: What is the difference between Shrink Database and File?

texts:
DBCC ShrinkDatabase()
DBCC ShrinkFile()

Do I need to run both DBCC commands in order to shrink the database?
What is the difference between these two above?


texts:
I recently learned about the concept of parameter sniffing and I am having a confusion about a specific scenario where I am not able to understand the behavior of SQL Server. Anybody who can help me to understand will be great:

SCENARIO:

For instance we have a below stored procedure:

create procedure GetEmployeeDetails  @a int as
BEGIN
    IF @a = 1
    BEGIN
    select * from HumanResources.employee where BusinessEntityID = @a
    END
    IF @a = 2
    BEGIN
    select * from HumanResources.employee
    END
END

And when I execute a stored procedure like below, will parameter sniffing should happen? I already tried it is not happening but I am amazed that why it is not happening. should the exact code be running for different parameters for parameter sniffing to happen? Is it a pre-requisite?

DBCC FREEPROCCACHE;
exec GetEmployeeDetails @a = 1
exec GetEmployeeDetails @a = 2

Any inputs are appreciated. Thanks.


texts: Is where x = 0 or x is null to be replaced by where isnull(x, 0) = 0 resp. where NVL(x, 0) = 0

texts:
Think of x as a column containing null, 0, 1.
Only less than 1 % of the rows contain 1.
There is no logical difference between 0 and Null.

Is it a good idea to insist, that isnull rather than NVL is used here (performance) ?


texts: Postgresql Database leaking t99_8989898 files in db directory or db size is showing incorrect in postgres

texts:
postgresql version is 10
\l+ showing db size to be 7228 GB
i checked all tables sizes those comes out to be 60 GB only
using
SELECT relname AS "relation",
       pg_size_pretty(pg_relation_size(C.oid)) AS "size"
FROM pg_class C
  LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
WHERE nspname NOT IN ('pg_catalog', 'information_schema')
ORDER BY pg_relation_size(C.oid) DESC

Then i went to see filesystem in which my database base directory is:
/var/lib/pgsql/10/data/base/16384`
I see around 70k
t20_909090 files in this directory which are taking the space.
I have checked in my local postgres db instance these files are created only after i create a new temp table and got deleted after i close the session.
files in base directory from my local db:

    pwd ; ls  | grep  t
/var/lib/pgsql/10/data/base/150689
pg_internal.init
t3_150704
t3_150707
t3_150709
t3_150710
t3_150713
t3_150715
t3_150716
t3_150719
t3_150721
t3_150722
t3_150725
t3_150727

from my local postgres db the temp files and above file nodes:
    tertw=# SELECT c.oid,
n.nspname as SchemaName
,c.relname as RelationName
,CASE c.relkind
WHEN 'r' THEN 'table'
WHEN 'v' THEN 'view'
WHEN 'i' THEN 'index'
WHEN 'S' THEN 'sequence'
WHEN 's' THEN 'special'
END as RelationType
,pg_catalog.pg_get_userbyid(c.relowner) as RelationOwner
,pg_size_pretty(pg_relation_size(n.nspname ||'.'|| c.relname)) as RelationSize
FROM pg_catalog.pg_class c
LEFT JOIN pg_catalog.pg_namespace n
                ON n.oid = c.relnamespace
WHERE  c.relkind IN ('r','s')
AND  (n.nspname !~ '^pg_toast' and nspname like 'pg_temp%')
ORDER BY pg_relation_size(n.nspname ||'.'|| c.relname) DESC
;
  oid   | schemaname | relationname | relationtype | relationowner | relationsize
--------+------------+--------------+--------------+---------------+--------------
 150704 | pg_temp_3  | tesst        | table        | postgres      | 0 bytes
 150710 | pg_temp_3  | tesst1       | table        | postgres      | 0 bytes
 150716 | pg_temp_3  | tesst2       | table        | postgres      | 0 bytes
 150722 | pg_temp_3  | tesst3       | table        | postgres      | 0 bytes
(4 rows)

according to above obersvation i concluded that
these are temp tables but in prodcution system  in pg_class these oid (e.g.: i checked 909090 as oid from t20_909090 ) are not present
how do I check what files they are or how to clean them up.
Also all these files got cleaned up automatically after i restarted the postgres db.
Extension on my DB :
tertw=# select * from pg_extension ;
 extname | extowner | extnamespace | extrelocatable | extversion | extconfig | extcondition
---------+----------+--------------+----------------+------------+-----------+--------------
 plpgsql |       10 |           11 | f              | 1.0        |           |
(1 row)

tertw=# \dx
                 List of installed extensions
  Name   | Version |   Schema   |         Description
---------+---------+------------+------------------------------
 plpgsql | 1.0     | pg_catalog | PL/pgSQL procedural language
(1 row)

tertw=# \c postgres
You are now connected to database "postgres" as user "postgres".
postgres=# \dx
                 List of installed extensions
  Name   | Version |   Schema   |         Description
---------+---------+------------+------------------------------
 plpgsql | 1.0     | pg_catalog | PL/pgSQL procedural language
(1 row)


texts: What is the correct way to define and query a 3NF Associative entity

texts:
A subset of my database contains the tables contacts, networks and networkContacts which is my associative entity. I have defined it using a composite primary key composed of the primary keys of the first two tables. The tables are are defined as follows:

CREATE TABLE contacts (
  cid char(88) NOT NULL,
  alias varchar(255) NOT NULL,
  PRIMARY KEY (cid)
)

CREATE TABLE networks (
  nid int(11) NOT NULL,
  fingerprint char(88) NOT NULL,
  port int(11) NOT NULL,
  network_alias varchar(255) NOT NULL,
  PRIMARY KEY (nid),
  UNIQUE KEY fingerprint (fingerprint),
  UNIQUE KEY port (port),
  UNIQUE KEY network_alias (network_alias)
)

CREATE TABLE networkContacts (
  nid int(11) NOT NULL,
  cid char(88) NOT NULL,
  PRIMARY KEY (nid,cid),
  CONSTRAINT networkContacts_ibfk_1 FOREIGN KEY (nid) REFERENCES networks (nid),
  CONSTRAINT networkContacts_ibfk_2 FOREIGN KEY (cid) REFERENCES contacts (cid)
)

Is this correct and if so how should I correctly query the contacts table by inner joining it with the networks table as every example I have seen uses both fields in the primary key to conduct the join. As you can see I only have one field of the associative entity primary key in each of the other tables respectively so doing a join based on both components can't be done.


texts: How to JOIN each row ON NOT IN ( SELECT ... )?

texts:
Say I have a table of users, songs, and songs played.

users
id: int

songs
id: int

songs_played
user_id: int
song_id: int

If I want to, for each user, find a song that has not yet been played. How do I do that in one query?

It's pretty straightforward for me to just do something like:

user_ids = SELECT id FROM users;
for user_id in user_ids:
    SELECT *
    FROM songs AS s
    WHERE s.id NOT IN (
        SELECT p.song_id
        FROM songs_played
        WHERE id = user_id
    )
    ORDER BY RANDOM()
    LIMIT 1;

But that's pretty inefficient.

Thanks!


texts:
I'm trying to see if it's possible to create a PolyBase connection to a .CSV file.  I'm using the Microsoft Access Text Driver and have set up a system DSN to the folder where the csv file is located.  I'm able to query the file using OPENROWSET and the DSN, but when I try creating the external table, I'm getting the following generic error.

Msg 105082, Level 16, State 1, Line 108
105082;Generic ODBC error: [Microsoft][ODBC Text Driver]Optional feature not implemented

The file is just a simple one I created with three columns and two rows.  Would it be possible to create a PolyBase connection to a .csv file?

CREATE DATABASE SCOPED CREDENTIAL CSVFile
WITH IDENTITY ='ident',SECRET='pass'

CREATE EXTERNAL DATA SOURCE CSV
WITH ( LOCATION = 'odbc://localhost'
       ,CONNECTION_OPTIONS = 'Driver={Microsoft Access Text Driver (*.txt; *.csv)};DSN=CSVs'
        ,CREDENTIAL = CSVFile
        );

CREATE EXTERNAL TABLE TestCSV
(
    Col1 VARCHAR(255) NULL
    ,Col2 VARCHAR(255) NULL
    ,Col3 VARCHAR(255) NULL
)
WITH
(
    LOCATION = N'Test.csv',
    DATA_SOURCE = CSV
)
GO


texts: how to transfer data from table to table

texts:
I have two tables Table1, Table2. From Table1 want to transfer data to Table2.

Suppose I have the following query:

Select *
from Table1
where UserID between 5 and 10

I want to store above result to Table2. Both tables save same type of columns. How to transfer data from table to table?

If have any questions, please ask.


texts: I am currently working on migrating a legacy codebase from VB6 and Access to VB.NET and SQL Server.  I have run the Microsoft SQL Server Migration Assistant successfully and populated a SQL server with all of our existing tables from access, and I am currently looking into completely removing access from the equation, but I still need it currently to run the queries so that they can be forwarded to SQL Server.  Is there a way to send queries directly to SQL Server without needing to translate them all, such as some sort of translation plugin or service I can use?


texts: MySQL: Is it possible to have an IF subquery?

texts:
I'm attempting to run a subquery inside of an if statement used in the SELECT, but I'm getting an error. I've never needed to do something similar before so I've never tried this until now. Here is what I'm running:

SELECT t1.*, IF(t1.tin > 0, (SELECT name FROM special_packaging WHERE id = t1.tin), 0), t2.productname, t2.imagepath FROM cartitems AS t1 INNER JOIN products AS t2 ON t1.productid = t2.id WHERE t1.cartid = 8

I'm getting a syntax error. Is it possible to run a subquery inside of an IF statement?

Any advice on what I'm doing wrong is greatly appreciated.

EDIT:

This is the error message I am getting:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1


texts: Is copy/rename a good way to improve temp table performance?

texts:
An application I am working with contains this sequence of queries at the end of constructing a large temp table; I believe it is an attempt to improve performance by pruning dead tuples -

create temp table tmp_foo_new as select * from tmp_foo;
drop table tmp_foo;
alter table tmp_foo_new rename to tmp_foo;

Some rudimentary benchmarks do indicate this sequence does improve performance substantially, so it is worthwhile.  It feels somewhat hacky to me though.  Is there a more canonical way to do this?


texts: How can a PostgreSQL standby decide whether its primary is still active or not

texts:
I have a setup with asynchronous streaming replication so that PSQL1 is primary and PSQL2 is hot standby.
Since PSQL2 is running in hot standby mode I can do run queries against it.
Is it possible on PSQL2 to tell whether PSQL1 is still active or already shutdown?
I tried select pg_is_in_recovery(); but it returns true regardless whether PSQL1 is running or not.


texts: Find what values are not in multiple columns

texts: I need to find what "Codes" from column "RMS_CODE" and column "RMS_ALT1_Cde" are not being used in the Child # (starting in position 6 for a length of 4) column within the "APC_Spec" number. I tried using the sql in the image provided, but it only looks in the row and doesn't check the whole column within the "APC_Spec" requirement. Can anyone suggest a method to check which codes are not being used per "APC_Spec" in the Child column #?


texts: Is there a set based way to load/read a tree branch with HierarchyId

texts:
I am playing around with HierarchyId, and I have not figured out a set based way to do the following:

insert all subtree at once
retrieve all subtree at once

This question is related to my previous one, and I suspect the only way to accomplish these two tasks with HierarchyId is one node or one level at a time. If I am using materialized path, both actions are easily accomplished by a single (and trivial) set based command.

What am I missing?

Edit: I also missed a way to move a subtree, but I learned it from  Mikael Eriksson's comment


texts: How to diagnose what is causing DB timeouts

texts:
I'm a web developer which currently has the joy of managing a client website that is still using Classic ASP that uses SQL Server 2000.

The code is quite old and in the process of been updated to a completely new system but in the interim we have to keep the websites running as smooth as possible.

There are three sites, a commercial front end, UK backend and Singapore back end. The two backends have a nearly 95% identical code base and each has a database on the same server and are hosted on the same machine.

MY issue is that for the last two or three days there have been errors for users when they try to log in on the Singapore website, the error is:

Microsoft OLE DB Provider for ODBC Drivers error '80040e31'
[Microsoft][ODBC SQL Server Driver]Timeout expired

The code in question is a simple UPDATE statement, updating a session variable for one entry and the code has been running fine for many years.

There have been no recent code changes either that would affect this. How can I diagnose what is causing the bottleneck, EventViewer, SQL Error logs and IIS logs seem to not show errors around the time the timeout occurred.

My guess that the error is occurring when this code tries to run the UPDATE but there was/is an earlier process choking the SQL Server, but not been a DBA I am only guessing at this point.

Many thanks
John


texts:
I am exporting a table to a TSV file using sqlcmd, and I am running into issues. The table has 16+ million rows and about 55 columns.

The problem is that it does not export the full table, but seems to stop randomly at various points (i am guessing a timeout?)  Each time a different number of rows are exported and each time the file is of a slightly different size (indicating that I am not hitting any row or size limit).

I am not using any timeout switch (meaning the default of "as long as it takes" is used).

Here is my command (with most columns removed for simplification and illustration purposes):

sqlcmd -U myUsername -P myPassword -S SERVERNAME -d "DBNAME" -Q "SELECT ROW_KEY, ISNULL(CARRIER_ID, ''),ISNULL(CONVERT(VARCHAR(19),ORDER_DATETIME,120),''),ISNULL(HEIGHT, ''),ISNULL(RESIDENTIAL_CHARGE, '') FROM MYTABLE" -o "OUTPUT_FILE.txt" -h-1 -s"|" -W

I wonder if it could have something to do with timeouts or the use of ISNULL() on all of the columns (although when i run the query in sql server management studio I get the correct number of rows returned e.g. 16 million + )?

Again, I get about 4-8 million rows each time, but never the full amount. I am in a sql server 2k5 db, and running sqlcmd from a remote machine with sql server 2k8.


texts:
I am student and I am writing Master of Science paper about in-memory databases. I want to use MySql Cluster for benchmarks in my work.
Can I use MySql Cluster free of charge for educational purposes or do I need to pay for it?


texts: psql is not prompting me for a password, then denies access

texts:
I'm using Postgres 9.4 on Debian Linux.  I created a database with a user, cindex with access to the database.  Yet when I try and login at the command line, I'm not even prompted for a password:

myuser@myuserserver:~ $ psql -Ucindex cindex
psql: FATAL:  Peer authentication failed for user "cindex"

What else do I need to do to enable the user?  Below you can see the privileges that I have already set up:

postgres@myuserserver:~$ psql
psql (9.4.13)
Type "help" for help.

postgres=# GRANT SELECT ON ALL TABLES IN SCHEMA cindex TO cindex;
GRANT
postgres=# \l
                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
 cindex    | postgres | UTF8     | en_GB.UTF-8 | en_GB.UTF-8 | =T/postgres          +
           |          |          |             |             | postgres=CTc/postgres+
           |          |          |             |             | cindex=c/postgres
 postgres  | postgres | UTF8     | en_GB.UTF-8 | en_GB.UTF-8 |
 template0 | postgres | UTF8     | en_GB.UTF-8 | en_GB.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_GB.UTF-8 | en_GB.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
(4 rows)

postgres=# \du
                             List of roles
 Role name |                   Attributes                   | Member of
-----------+------------------------------------------------+-----------
 cindex    |                                                | {}
 postgres  | Superuser, Create role, Create DB, Replication | {}


texts:
Unlike tables, a table type cannot be altered by a simple command. I need to do the following:

Rename existing table type
Create the new table type
Open each affected objects (i.e. ALTER SP) and execute it again so that the reference to table type will be updated.

But this is a tedious task as there may be many SP to be updated. Is there any easy way/pre-generated script that can automate this process?


texts: Will large inserts crash a 3-node MySQL database cluster?

texts:
We have a 3 node MySQL database cluster than runs on Percona. I have a stored procedure that inserts a lot of rows in one hit, potentially over a million rows.

I've been warned (by our DBA) this might cause the cluster to crash, but I can't find anything online regarding this or that would suggest this might happen.

Can anyone provide some insight?


texts: Getting error when using Oracle imp command to import dmp file

texts:
I am importing a dmp file using the following command:

imp user/pass file=file.dmp log=logfile.log full=y ignore=y destroy=y

I get the following errors:

. . importing table                "MESSAGE_BOARD"
IMP-00058: ORACLE error 22993 encountered
ORA-22993: specified input amount is greater than actual source amount
IMP-00028: partial import of previous table rolled back: 219638 rows rolled back
. . importing table       "MESSAGE_BOARD_ARCHIVES"
IMP-00058: ORACLE error 22993 encountered
ORA-22993: specified input amount is greater than actual source amount
IMP-00028: partial import of previous table rolled back: 2477960 rows rolled back

I have tried increasing the tablespace size and adding a second datafile to no avail. I'm a total newb at Oracle so any help will be appreciated. Googled this for hours and still can't come up with a solution.

Thanks!


texts:
I thought this would be an easy question to google for; but I couldn't not find it.

Given the following query:

SELECT name, MATCH (name) AGAINST ('vision black*' IN BOOLEAN MODE) as rel
FROM (`phppos_items`)
WHERE MATCH (name) AGAINST ('vision black*' IN BOOLEAN MODE) AND `deleted` = 0
ORDER BY `rel` DESC LIMIT 100

Is the HIGHER match score more relevant? Could someone point me to the right mysql manual page that explains this?

Based on simple observation a higher score does seem better.


texts:
I noticed that PostgreSQL >= 10 does not generate WAL if there are no transactions.
In previous versions, PostgreSQL generated WAL at the expiration of archive_timout, because it wrote some empty transactions.

I want PostgreSQL to still generate WAL if no one writes to it. Does anyone know how to do this in versions starting from 10?


texts: How should I set up SQL Server Login permissions for access to website on a different server using Windows Authentication

texts:
I know this question doesn't really belong here, but I posted on the MS IIS Site and am still hoping for a reply. I have an intranet site created with .NET Framework 4.0 in Visual Studio 2012 and I used the VS2012 publish command with "Window Authentication". The SQL Server is on a different server than the Web Server. I have set up and given the correct permissions to the Application pool for both the NTFS and SQL Server (remember my SQL Server is on a different (virtual) Server. I used this document to set up the particulars:

IIS Setup Win2008R2 Server With IIS 7.0

I also added the machine name (Domain\MachineName$) as a user to the SQL Server, as I couldn't find a way to add the app pool as a user to a SQL Server that wasn't on the same box. Bonus Question -- Is there a way to get the app pool from one server recognized on a different server... it actually came up in intellisense in SQL Server Management Studio, but I wasn't able to add it?

Everything works for "My" login (which is a member of the "administrator" group), but I was unable to allow users to get access to the intranet website unless I added their username to the root folder of the website and gave them permissions.

My Questions:  (Bearing in mind that this is an intranet app that will only be accessed by authenticated users)

1)  Is it OK from a security standpoint to grant users access to the root of the website? If not ... what am I missing... If this is a no-go from a security standpoint, what are the better ways to do this? If it is OK, what permissions do they need?

Bonus Question:

2) Is there a way to get the app pool from one server recognized on a different server?


texts:
I was reading this post to understand how things are done in Sql Server:
http://rusanu.com/2013/08/01/understanding-how-sql-server-executes-a-query/

If I understand it correctly, data is being sent to client as is being needed. Before we fetch next batch of data to the client, execution of query is suspended. If we do range scan then metadata about last returned row will be stored somewhere.
What happens if in the meantime there is a page split and last returned row is on the new page?
Is this possible? Does this operator/iterator (clustered index scan for example) holds latch on page whole time?
This would be extremely inefficient, so Sql Server must have some other way to keep it in sync?

My question is - what metadata is stored and how does Sql Server keep it in sync?


texts: Why is our 1000 MB database file not uploading completely via phpMyAdmin?

texts:
We have an e-commerce Magento site and a large database of 1000 MB for that site.

When we try to import the database through phpMyAdmin, it's not uploading the full databse, a lot of tables will be missing after uploading.

We use a GoDadddy cPanel and phpMyAdmin.

How can we upload the full 1000 MB database completely?

Also, is there any way to replace the old domain name to new domain name in the database?

thanks in advance


texts: How do I keep a database physically secure?

texts:
This is more of a conceptual question. I am learning how to integrate Java and databases (using PostgreSQL at the moment) and my initial questions is:

  What are the guarantees that the data I create will remain safe?

For example, if I have a simple website hosted on my computer, what is the guarantee that data will remain safe from physical elements (HD failure or any kind of hardware deficiency)?

  What are the procedures used in small and big databases to prevent this kind of problem? Should the databases be stored in a separated locked up machine?

Thanks.


texts: Mismatch between size computed from pg_buffercahce Vs shared_buffers in postgresql?

texts:
I have a rather naive question regarding the size of shared_buffers computed from pg_buffercache and the shared_buffers size mentioned in postgresql.conf. Based on my understanding pg_buffercache shows the state of all the buffers in shared_buffers. Thus, size of shared buffers = Number of entries in pg_buffercache * Size of one page (8192 bytes). When I run this on my system :

    test=# select count(1) from pg_buffercache;
    count
    -------
    3072
    (1 row)

    test=# show shared_buffers;
    shared_buffers
    ----------------
    24MB
    (1 row)

Calculated size of shared buffers = 3072 * 8192 ~= 25.17 MB.
Actual size of shared buffers = 24 MB.

Why is there a difference between the computed size of shared buffers and the actual size of shared buffers ? Let me know if my understanding is wrong.

Thanks.


texts:
Possible Duplicate:
  Must-read Books for DBA

What books do you recommend for the aspiring DBA?

Update: I'm particularly looking for sql-server and PostgreSQL books (though books geared towards other DB's might be helpful to keep in one place).


texts: Does the length of the fields play a role in index access in MS-SQL?

texts:
Last week I had already started two threads dealing with slow SQL selects that probably did not use the index.
Today I noticed the following: The customer and the material are involved in almost all index accesses.
Customer and material are nvarchar fields everywhere, and the customer and material numbers are the same in all tables.
Only the length of the nvarchar fields is different in different tables.
The customer sometimes consists of an nvarchar (10), sometimes of an nvarchar (20) and sometimes of an nvarchar (30).
This is because these tables were created by external consultants who each used a different length for the customer-fields.
However, the customers are only seven characters everywhere in all tables.
Could that be a reason that the index access is not working?
Does the nvarchar length play a role when linking different tables via fields for which an index exists?


texts: Grant for locking sys user on Oracle Database

texts:
I would like to know what GRANT is required to block the SYS user, as i have a common user in the database.
GRANT ALTER USER enabled me to block only the SYSTEM, but it returns the error ORA-01031: insufficient privileges when trying to block the SYS account.
I have also tried GRANT SYSOPER, but also without success.


texts: CHECK constraint for array column to verify length > 0

texts:
I'm playing with postgres table validation rules and trying to set a CHECK constraint for an array column. An idea is to allow only arrays with length > 0.

Here is how I want to implement it:

create table words_table (
  id serial primary key,
  words varchar(20)[] CHECK (array_length(words, 1) > 0)
);

But looks like it doesn't work. O_o

insert into words_table (words) values ('{}');
//INSERT 0 1

How to implement such a constraint?


texts: AD group cannot connect to SQL from excel

texts:
We have a data warehouse and users are trying to browse the database directly from excel. We put those users in an AD group and give them read only permission to a couple of databases.

The problem is under such circumstance users are not able to establish connection from excel to SQL Server. "login failed for user ***" will be shown as error message.

However, if we add every single user's login to SQL Server security, they are able to access to databases from excel then.

Does anyone have any idea why this happens?


texts: How to embed a sub-prodecure call in a SELECT statement in an Oracle 11g PL/SQL stored procedure

texts:
I need to figure out a way to embed an Oracle PL/SQL sub-procedure call in a SELECT statement, within another procedure in the same package.

I am using SQLDeveloper 3.0 with an Oracle 11g database.

I have already developed a stored procedure 'acctg_detail_proc()' that generates a detailed list of accounting transactions within a specified period. What I am trying to do is create a summary report procedure 'acctg_summary_proc()' using the detailed data returned by acctg_detail_proc().

Since I am new to PL/SQL, all I have learned so far is how to retrieve the detailed data via a ref cursor, then LOOP through it, FETCHing each individual detail row. I want to figure out how acctg_summary_proc() can substitute that call to acctg_detail_proc() for a table name in a SELECT statement with a GROUP-BY clause.  Here is the source code for an anonymous block where I tried to test it:

SET SERVEROUTPUT ON;
DECLARE
    start_date VARCHAR2(50) := '04/01/2012';
    end_date VARCHAR2(50) := '04/30/2012';
    c_acctg_refcur    SYS_REFCURSOR;
BEGIN
  acctg_rpt_pkg.acctg_detail_proc(start_date, end_date, c_acctg_refcur);
  SELECT
    date_posted,
    debit_acct,
    credit_acct,
    SUM(dollar_amt)
  FROM c_acctg_refcur
  GROUP BY
    date_posted,
    debit_acct,
    credit_acct;
  CLOSE c_acctg_refcur;
END;

When I try to execute this code, I get the following error:

PL/SQL: ORA-00942: table or view does not exist

I realize I could use a nested SELECT statement instead of a table name,
but I want to avoid duplication of source code. Is there any way to
'alias' a ref cursor so I can reference its data in a SELECT statement?

Here is some further background info:
The called sub-procedure has ~600 lines of code, and selects 40 columns of data from a de-normalized VIEW.  The corporate DBAs will not let me create any VIEWs that contain WHERE clauses, so that is not an option for me.

Thanks in advance,
Ken L.


texts:
CREATE TEMPORARY TABLE tt;
INSERT INTO tt; many times
SELECT FROM tt ORDER BY id LIMIT ?,1000;

The temp table is created at the beginning of the script having an "id" auto_increment column (and others). Part 1 of the script fills up the table using various SELECTs. Part 2 should process all selected rows in chunks of 1000 but will never ever write the table again.

I assume that these two commands are the same

SELECT FROM tt ORDER BY id LIMIT ?,1000;
SELECT FROM tt LIMIT ?,1000;

but is this true? Is there any chance that mySQL will retrive the rows in a different order on multiple SELECTs even if the table is not written in between?

Sorting the temp table is the longest part while reading it and I'd be happy to get rid of the ORDER BY part but I don't want to miss a record because the order changed between SELECT LIMIT 0,1000 and SELECT LIMIT 1000,1000.


texts: How to join global temp tables in SQL Server?

texts:
I have four global temp tables (##A,##B,##C,##D) on which I want to perform a left join in SQL Server 2008. By global temp tables I mean I created them using a code like this:

SELECT *
INTO ##A

These four tables all have a column with same name 'key' and I want to perform join using this column. I am  using this logic:

SELECT *
INTO ##X
FROM ##A as a
LEFT JOIN ##B as b ON a.key = b.key
LEFT JOIN ##C as c ON a.key = c.key
LEFT JOIN ##D as d on a.key = d.key

When I execute this code I get the following error:

  Cannot resolve the collation conflict between "Latin1_General_100_BIN"
  and "SQL_Latin1_General_CP1_CI_AS" in the equal to operation.

I have been stuck here and cannot seem to resolve it.

Any help/advice/suggestions much appreciated.


texts: Import data from one Database to another script

texts:
As part of a data migration I would like to script a data transfer from the existing to a new database.
I used to do this with DTS on older versions of MS SQL. I am currently using SQL 2012.
Note the tables have different some different fields.
It would be nice if the import/export data tool had a save script option?


texts: Why my Oracle 12c database seems not to be started?

texts:
I have installed an Oracle 12c database on my development environment, which I start only once a while for integration testing. But today, it does not seem to start, or did it start but does not allow connections? Here are some clues:

OS is Debian 8
I use a shell script to start and stop the database
$ORACLE_SID contains

  INFO

The hosts file contains

  127.0.0.1 localhost and 127.0.1.1 Strada.nohoo.biz    Strada

The /u01/app/oracle/diag/rdbms/info/INFO/alert_INFO.log file ends with

  Completed: ALTER DATABASE OPEN Sun Mar 11 20:27:57 2018 Shared IO Pool
  defaulting to 112MB. Trying to get it from Buffer Cache for process
  4294.
  =========================================================== Dumping current patch information
  =========================================================== No patches have been applied
  =========================================================== Sun Mar 11 20:27:57 2018 db_recovery_file_dest_size of 4560 MB is 0.00% used.
  This is a user-specified limit on the amount of space that will be
  used by this database for recovery-related files, and does not reflect
  the amount of space available in the underlying filesystem or ASM
  diskgroup.

tnsping INFO returns

  TNS Ping Utility for Linux: Version 12.1.0.2.0 - Production on
  11-MAR-2018 21:42:22

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

  Used parameter files:
  /u01/app/oracle/product/12.1.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 =
  INFO.nohoo.biz)))

  OK (0 msec)

tnsnames.ora contains

INFO =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = INFO.nohoo.biz)
    )
  )

listner.ora contains

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )

lsnrctl services returns

  LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 11-MAR-2018
  21:54:24

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

  Connecting to
  (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521)))

  The listener supports no services

  The command completed successfully

The ps -A | grep ora command in a shell returns a list of

  6003 ?  00:00:00 ora_pmon_info
   6005 ?  00:00:00 ora_psp0_info
   6007 ?  00:00:00 ora_vktm_info
   6011 ?  00:00:00 ora_gen0_info
   6013 ?  00:00:00 ora_mman_info
   6017 ?  00:00:00 ora_diag_info
   6019 ?  00:00:00 ora_dbrm_info
   6021 ?  00:00:00 ora_vkrm_info
   6023 ?  00:00:00 ora_dia0_info
   6025 ?  00:00:00 ora_dbw0_info
   6027 ?  00:00:00 ora_lgwr_info
   6029 ?  00:00:00 ora_ckpt_info

But all this does not look like a successful situation!

And finally, trying to connect with SQLplus ends up with

  ORA-12547: TNS:lost contact

Thanks for you help!


texts: How to do Incremental statistics on auto_created stats(_WA_) in SQL server

texts:
How to do Incremental statistics on auto_created stats(WA) in SQL server? I know I can do this for the non auto_created stats (indexed column) either by rebuilding index on the table or at DB level.

Is there any way to achieve this on auto_created stats(WA) also?

The underlying issue is-

On a good day query runs in 10 sec and on a bad day it take 5 min. This query uses 15 columns in a join on a large table. This table is partitioned. The only difference I found that on the bad run stats were also updated on those 15 columns at the same time query is running. please advise.

SQL Server 2017:
is_auto_create_stats_on= True;
is_auto_create_stats_incremental_on= False;
is_auto_update_stats_on= True;
is_auto_update_stats_async_on= False;


texts: Generate INSERT statement from SELECT query

texts:
I have two databases, in first database I have a table called zoom_city and in the second database I have a table called courrier_city. The SQL for each is below:

CREATE TABLE IF NOT EXISTS `zoom_city` (
  `id_ciudad` int(11) NOT NULL AUTO_INCREMENT,
  `id_estado` int(11) NOT NULL,
  `ciudad` varchar(200) NOT NULL,
  `capital` tinyint(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id_ciudad`),
  KEY `id_estado` (`id_estado`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=644;

CREATE TABLE IF NOT EXISTS `courrier_city` (
  `country` varchar(4) NOT NULL,
  `state` varchar(10) NOT NULL,
  `id` varchar(100) NOT NULL,
  `name` varchar(255) NOT NULL,
  `capital` varchar(255) NOT NULL,
  `created` timestamp NULL DEFAULT NULL,
  `modified` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `deletedAt` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`country`,`state`,`id`),
  KEY `fk_courrier_city_state1_idx` (`country`,`state`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

So I need to dump values from first table zoom_city and insert them in courrier_city but taking this in account:

country always be VE string
id_estado are stored in zoom_city as for example 1, 2, 3, 10 and right values for courrier_city and taking same example would be VE.01, VE.02, VE.03 and VE.10 so this value needs a transform before insertion.

Is that possible in raw SQL or I need to do this in a programming language like PHP for example?


texts: mysqldump doesn't restore database. why not?

texts:
Why doesn't my restore operation work?

I created a dumpfile for a database using:

mysqldump -u root -p databasename > /home/databasename_bkup.sql

I then opened the dumfile and confirmed that it contains CREATE TABLE statements for each of the tables in the database.  So I dropped the database and re-created an empty database of the same name before running the following restore command:

mysqldump -u root -p databasename < /home/databasename_bkup.sql

This restore command resulted in the following printing in the terminal:

-- MySQL dump 10.13  Distrib 5.6.23, for Linux (x86_64)
--
-- Host: localhost    Database: databasename
-- ------------------------------------------------------
-- Server version   5.6.23

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

-- Dump completed on 2015-03-05 17:56:29

But then when I log into mysql to check the contents of the database, I found out that the database is empty, as follows:

mysql> use databasename;
Database changed
mysql> show tables;
Empty set (0.00 sec)

So why is the database not being restored?  What specific syntax can I use to ensure that the database does get restored properly?


texts: Why is WITH NOLOCK running as Serializable

texts:
I ran the following query:

SELECT session_id,CASE transaction_isolation_level
WHEN 0 THEN 'Unspecified'
WHEN 1 THEN 'ReadUncommitted'
WHEN 2 THEN 'ReadCommitted'
WHEN 3 THEN 'Repeatable'
WHEN 4 THEN 'Serializable'
WHEN 5 THEN 'Snapshot' END AS TRANSACTION_ISOLATION_LEVEL
FROM sys.dm_exec_sessions
Where transaction_isolation_level = 4

and then:

DBCC INPUTBUFFER(157)

where 157 was one of the prior session_ids, to see the statement of one of the results from query no. 1.

It showed the following:

(@ID uniqueidentifier) SELECT * FROM PS WITH (NOLOCK) WHERE ID = @ID

How can the statement using WITH (NOLOCK) run with isolation level Serializable? Is there anything "overriding" the With (NOLOCK)?


texts: Edit Application Name to Log Connection once for Logons on Pooled Connections

texts:
I am wondering if I can change the application name of a connection in SQL Server itself, e.g. in the logon trigger? Is that possible or is the application name "read only"?

Edit to explain the motivation: I would like to mark all audited connections in my logon trigger, so that I write only one record per connection through its many logon events. I thought I could modify some info, e.g. the app name, of a connection so that next time around the logon trigger sees that it can ignore this connection attempt because it already logged the info around this connection (like user, host etc.).


texts: Is it possible to get the last restore date of a MySQL instance?

texts:
Is it possible to get the last restore date of a MySQL database?

I am going to restore the database from a .sql dump file, but I don't know how to get from the database, the last time when a restore happened.

I tried to query the information_schema.tables table in order to check the creation_time of each table, but they seem to be not updated after the restore.


texts: Key column 'r_Course_id' doesn't exist in table Error in my code

texts:
I used the following syntax and keep getting the following error at the end.
" Key column 'r_Course_id' doesn't exist in table"

CREATE TABLE Faculty (
Faculty_id INT NOT NULL,
First_Name VARCHAR (20) NOT NULL,
Last_Name VARCHAR (20) NOT NULL,
EMAIL VARCHAR (30) NOT NULL,
Date_of_birth VARCHAR (30) NOT NULL,
Number_of_courses int,
PRIMARY KEY (Faculty_id)
);

CREATE TABLE Courses (

Course_id  INT NOT NULL,
Discipline VARCHAR (20)NOT NULL,
Course_number INT NOT NULL,
Credit DECIMAL(7,2)NOT NULL,
Year_offerd INT ,
Course_Title VARCHAR (50)NOT NULL,
PRIMARY KEY (Course_id )
);

CREATE TABLE FacultyCourses (
Course_id  INT NOT NULL
FOREIGN KEY (Course_id) REFERENCES Courses (Course_id),
FOREIGN KEY (Faculty_id)REFERENCES Faculty(Faculty_id),
F_C_ID int NOT NULL AUTO_INCREMENT,
PRIMARY KEY (F_C_ID)
);

the first two tables are created without a flaw, however the third is giving me trouble. please help.


texts: Why are these MongoDB queries using indexes differently?

texts:
I've been trying to diagnose some long running queries and whilst profiling them I've noticed some very strange behaviour that I can't explain (pun intended); which I'm hoping someone here can...

Given an abridged example document of:

{
    author: "laura",
    keywords: ["foo", "bar", "hello"],
    created_date: ISODate("2017-01-18T00:00:00Z"),
    type: "content"
    ...
}

And indexes of:

{type: 1, author: 1, created_date: -1}
{type: 1, keywords: 1, created_date: -1}

If I run the following query:

db.items.explain().find({$or: [
    {
       type: "content",
       author: "kate",
       created_date: {
         $lte: ISODate("2017-01-25T00:00:00Z"),
         $gte: ISODate("2017-01-18T00:00:00Z")
       }
    },{
       type: "article",
       keyword: "bar",
       created_date: {
         $lte: ISODate("2017-01-25T00:00:00Z"),
         $gte: ISODate("2017-01-18T00:00:00Z")
       }
    }
]})

In the executionPlans, I notice that for the author component of the query, the entire query scans the index:

    {
            "stage" : "IXSCAN",
            ...
            "keyPattern" : {
                "type" : 1,
                "author" : 1,
                "created_date" : -1
            },
            ...
            "indexBounds" : {
                "type" : [
                    "[\"content\", \"content\"]"
                ],
                "author" : [
                    "[\"kate\", \"kate\"]"
                ],
                "created_date" : [
                    "[new Date(1485388799000), new Date(1484697600000)]"
                ]
            }
        }

Where as the keywords query splits the date out into a fetch stage as well:

{
        "stage" : "FETCH",
        "filter" : {
            "created_date" : {
                "$gte" : ISODate("2017-01-18T00:00:00Z")
            }
        },
        ...
        "inputStage" : {
            "stage" : "IXSCAN",
            ...
            "keyPattern" : {
                "type" : 1,
                "keywords" : 1,
                "created_date" : -1
            },
            ...
            "indexBounds" : {
                "type" : [
                    "[\"article\", \"article\"]"
                ],
                "keywords" : [
                    "[\"bar\", \"bar\"]"
                ],
                "created_date" : [
                    "[new Date(1485388799000), true)"
                ]
            }
        }
    }

I know that when indexing on a list/array field, a document is created for each element in that field so wonder if it's a product of that, but it's strikes me as bizarre that this is the behaviour.

Any thoughts would be gratefully recieved


texts: Should order of column in where clause be same as the index order?

texts:
I was reading this use-the-index-luke.com which explains in detail how indexes work. One of the things this person has re-iterated that the order of indexes matter a lot and to make the query fast the where clause columns should be same as that in index.
Today, I was just corroborating this theory and created a table (id int , name nvarchar(100) ) on SQL Server 2008.
I inserted some 5000 rows in it and created a index
create index abc on test (name, id )

and fired the query

select ID, name
from test
where ID = 10
and name = '10'

I was expecting a full table scan to be followed by a select in the query plan
but to my surprise the output of plan was a index scan folowed by select.

So, my question is does the order of columns in Where clause matters or does SQL Server re-arranges them as per the index definition ?

Thanks !!


texts: Oracle Data Pump Import with running advanced replication

texts:
In our system we have a running advanced replication between two databases(11g release 2) and want to use pump import to import a backup on one site. However the imported data is not replicated to the other site. All following changes get replicated as usual.

Edit for calification: Replication is active and both sites have the same data. I then import something into the primary site. These imported changes are NOT replicated. The second site still has the "pre-import" data.
When something in the primary site changes after the import, these changes get still replicated. So the replication itself is still active.
The pump import seems not to trigger the replication of the imported data.

I could not find anything about this in the oracle documentation. Is it even supported or is there something missing from our import and export commands?

expdp <connection data> dumpfile='xyz.dmp' SCHEMAS=XYZ EXCLUDE=USER NOLOGFILE=Y REUSE_DUMPFILES=Y

impdp <connection data> DUMPFILE='xyz.dmp' NOLOGFILE=y IGNORE=y INCLUDE=table_data TABLE_EXISTS_ACTION=skip


texts: Single-row INSERT...SELECT much slower than separate SELECT

texts:
Given the following heap table with 400 rows numbered from 1 to 400:

DROP TABLE IF EXISTS dbo.N;
GO
SELECT
    SV.number
INTO dbo.N
FROM master.dbo.spt_values AS SV
WHERE
    SV.[type] = N'P'
    AND SV.number BETWEEN 1 AND 400;

and the following settings:

SET NOCOUNT ON;
SET STATISTICS IO, TIME OFF;
SET STATISTICS XML OFF;
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

The following SELECT statement completes in around 6 seconds (demo, plan):

DECLARE @n integer = 400;

SELECT
    c = COUNT_BIG(*)
FROM dbo.N AS N
CROSS JOIN dbo.N AS N2
CROSS JOIN dbo.N AS N3
WHERE
    N.number <= @n
    AND N2.number <= @n
    AND N3.number <= @n
OPTION
    (OPTIMIZE FOR (@n = 1));

Note:@The OPTIMIZE FOR clause is just for the sake of producing a sensibly-sized repro that captures the essential details of the real problem, including a cardinality misestimate that can arise for a variety of reasons.

When the single-row output is written to a table, it takes 19 seconds (demo, plan):

DECLARE @T table (c bigint NOT NULL);

DECLARE @n integer = 400;

INSERT @T
    (c)
SELECT
    c = COUNT_BIG(*)
FROM dbo.N AS N
CROSS JOIN dbo.N AS N2
CROSS JOIN dbo.N AS N3
WHERE
    N.number <= @n
    AND N2.number <= @n
    AND N3.number <= @n
OPTION
    (OPTIMIZE FOR (@n = 1));

The execution plans appear identical aside from the insert of one row.

All the extra time seems to be consumed by CPU usage.

Why is the INSERT statement so much slower?


texts: Is it possible to retrieve the specific databases selected in a maintenance plan step?

texts:
I would like to use a tsql query to retrieve the databases involved in one of the steps in a database backup plan I've created. From a previous question, I understand that somehow, the data is probably stored in the msdb database, but running a trace didn't reveal the query that retrieved the "checked" status of each selected database.

Does anyone know how SQL Server determines which of the server's databases are included in a particular job step?


texts: MySQL replication as both master and slave (replication loop?)

texts:
I've got two MySQL servers, one as a master in the replication process and the other as the slave. I was wondering if anything bad would happen if I made the current master a slave to the current slave, and the current slave a master to the current master. So basically, both servers would be replicating to and from each other.

I saw some questions and posts on other sites about 'replication loops', which is how I would describe my question; but these posts seemed to be explaining something completely different.


texts: InnoDB: Error: log sequence number is in the future!

texts:
Hi I have 2 node cluster where recently went offline not sure why. Node1 started fine and all the traffic is on node 1 but node 2 still down due to below error. I tried set the inodb recovery to 3 and start but i cant start and the same error as like below. I've even tried removing the "aria file, Galera cache,gwstate file" and restart MySQL but still the same error.

How can i get the node 2 started or should i do full resync.

7a0d83860019dca40019dca30019dca500000b8b15ef4dad45bf0000000000000000000000be000d3b21803500000000180615 12:13:14  InnoDB: Error: page 1209437 log sequence number 12729899042363
InnoDB: is in the future! Current system log sequence number 12692016003988.
InnoDB: Your database may be corrupt or you may have copied the InnoDB
InnoDB: tablespace but not the InnoDB log files. See
InnoDB: http://dev.mysql.com/doc/refman/5.5/en/forcing-innodb-recovery.html


texts:
I have log shipped a database from one SQL Server 2008 R2 server to another in standby mode for reporting purposes. Permissions are granted via a Windows AD Group called DOMAIN\DB Access Group which is both a login and a user in the primary database. The login was never created in the secondary database, yet people are reporting that they are able to connect and run queries.

Using the SSMS GUI, if I right click on the user properties on the secondary, the server reports that DOMAIN\DB Access Group is both a user and a login on this server. Checking sys.database_principals shows the user exists but in sys.server_principals the login is nowhere to be found. Running SELECT SUSER_SNAME(SID_OF_AD_GROUP) returns a login name which is nowhere to be found.

Any ideas what might be causing this phantom login behavior?


texts: Cannot read sql statement into mysqlbinlog

texts:
Mysql 5.7 on Windows:

show global variables like 'binlog_format';

using me the STATEMENT

show master status;

gives me log-bin.000053

Now I execute this:

DELETE FROM test.emp WHERE EMPNO=109;

Now I want to read the contents of my mysqlbinlog:

mysqlbinlog log-bin.000053 > c:\backup\log.txt

An excerpt of the log file is this:

# at 1438
#170227  0:18:47 server id 1  end_log_pos 1514 CRC32 0x63bd770e     Query   thread_id=7 exec_time=0 error_code=0
SET TIMESTAMP=1488151127/*!*/;
BEGIN
/*!*/;
# at 1514
#170227  0:18:47 server id 1  end_log_pos 1576 CRC32 0xf178e099     Table_map: `corsosql`.`emp` mapped to number 296
# at 1576
#170227  0:18:47 server id 1  end_log_pos 1644 CRC32 0x3efad847     Delete_rows: table id 296 flags: STMT_END_F

BINLOG '
V2KzWBMBAAAAPgAAACgGAAAAACgBAAAAAAEACGNvcnNvc3FsAANlbXAABwMPDwMK9gMGPAAeAAgC
f5ngePE=
V2KzWCABAAAARAAAAGwGAAAAACgBAAAAAAEAAgAH/4BxAAAABEx1aXMHQUNDT1VOVGwAAACHnw+A
GvQACgAAAEfY+j4=
'/*!*/;
# at 1644
#170227  0:18:47 server id 1  end_log_pos 1675 CRC32 0xfe9e00e6     Xid = 322
COMMIT/*!*/;
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

Where is my delete?

Why can I not see it?

How can I see the delete that I've just made in this file?

In all docs that I've read the command is the same, but when I tried to do it, I cannot see the delete.

Thank you.

** EDIT: **

I created a new myisam test table and inserted/deleted some rows.

After that, now magically the statements is readable in the log.

Why??

Mysql now shows all the statements that i make in both innodb and myisam tables.

I wasted 3 hours trying to read sql statements from the log, and from the moment i created a new myisam table, mysql starts showing me the statements.

Strange.

Seems like the table creation has awakened mysql starting to show the statements.

Someone can explain me why?


texts: I have a question in regards to SQL Standard availability groups.  I currently have an AG setup with one primary and one secondary replica.  I am using synchronous commit but was curious about asynchronous.  How far will/can the secondary replica be behind?  If the secondary replica goes offline for any reason will it catch completely up once it comes online? How long can the secondary be down before it will not catch up?  I understand the difference between the 2 commit types (one verifies the transaction was committed before handing back to the application whereas the other commit type doesn't).  What I am not sure of is how long the secondary can be offline in an asynchronous commit type.


texts: To apply changes done to "/etc/my.cnf", do we need to restart mysqld service?

texts:
I need to change max_connections parameter in /etc/my.cnf. After doing the change, do we need to restart mysqld service to apply the changes?
I'm on CentOS 7


texts: Do I need a composite primary key in a junction table?

texts:
I have a junction table like this:

CREATE TABLE `entitytagjunction` (
     `EntityId` bigint(20) NOT NULL,
     `TagId` bigint(20) NOT NULL,

     PRIMARY KEY (`EntityId`,`TagId`),
     KEY `IX_EntityId` (`EntityId`) USING HASH,
     KEY `IX_TagId` (`TagId`) USING HASH,

     CONSTRAINT `FK_EntityTagJunction_Entities_EntityId`
        FOREIGN KEY (`EntityId`) REFERENCES `entities` (`Id`)
        ON DELETE CASCADE ON UPDATE CASCADE,
     CONSTRAINT `FK_EntityTagJunction_Tags_TagId`
        FOREIGN KEY (`TagId`) REFERENCES `tags` (`Id`)
        ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

I have EntityId and TagId as composite primary key to ensure uniqueness, and SQL Server and MySql will both use the primary key as clusteredId.

As far as I could read from the other posts, Guid is not good to be a primary key because the randomness make fragments for clusteredIds, poor for performance if the table consist of millions of rows. Apparently the combination of EntityId and TagId has some randomness. Will this have impact on performance when using them as a composite primary key and clusteredId by default? or should I add an int column as a surrogate primary key though it will not be used in applications?


texts: How to correctly use group by for getting the record with the closest time for each rec_id

texts:
Background

I have a table records which stores rec_id, rec_time and rec_value. A record is stored for each rec_id only when its rec_value changes. I would like to query the value for a bunch of rec_ids at a given time.

For a single rec_id this query works perfectly fine to query its value at rec_time 590:

SELECT rec_time,rec_value
FROM records
WHERE rec_id = 4 AND rec_time <= 590
ORDER BY time DESC
LIMIT 1;

I use rec_time <= 590 as the latest value can be set at any instance at or before 590.

PROBLEM

I would like to extend this to work for multiple rec_ids in a single query.

SELECT rec_id,rec_time,rec_value
FROM records
WHERE rec_id IN (4,9,21,565,951,93,6,15,64)
AND rec_time <= 590
GROUP BY rec_id
ORDER BY time DESC, rec_id ASC;

The query above seems to work but I have noticed that I get inconsistent results and I figured that I'm not using GROUP BY correctly.

I have tried a few solutions found in slightly different questions here, but I haven't found any solution which gives consistent results yet.

Can this be solved with GROUP BY or should I use nested queries. If so, how? I gave a shot at using JOIN but I couldn't get it to work.


texts:
I have a SQL Server 2012 instance with 96 GB of RAM that has the Buffer Cache Hit Ratio consistently at 100% and the PLE consistently over 500,000, so memory is obviously not a problem.

However, my monitoring tool shows the Procedure Cache Hit Ratio is usually in the low 90% range, dropping as low as 70% between 10pm and 7am when the user load is minimal. There are no significant nightly maintenance jobs that run other than a full backup which only takes about 6 minutes. The Plan Cache Size doesn't vary significantly over the course of 24 hours, staying around 7 MB.

If I run DBCC FREESYSTEMCACHE('SQL Plans'), it drops the Plan Cache Size to less than 1 MB, but seems to have no effect on the Procedure Cache Hit Ratio. We have not tried "optimize for ad hoc workloads", mainly due to risk aversion and a concern that if freeing the cache didn't make a difference, changing that setting may not either.

While I have no user complaints about performance, I'd like to understand this better.

What might be causing the nightly dip in the ratio?

What can I do to improve the Procedure Cache Hit Ratio?


texts: Mongodb multikey index and $all in queries

texts:
I have such structure of documents in my products collection:

{
  title: 'Samsung Galaxy S III',
  category_id: ObjectId("50bcc2f0b910a6c1936a4424"),
  properties: [
    {
      title: 'OS',
      value: 'Android'
    },
    {
      title: 'Display',
      value: '4.8"'
    }
  ]
}

Also I've created multikey index: {category_id: 1, 'properties.value': 1, 'properties.title': 1}

What happens when I do query with $all? How it works? Example:

{
  category_id: ObjectId("50bcc2f0b910a6c1936a4424"),
  properties: {
    $all: [
      {
        $elemMatch: {
          title: 'OS',
          value: 'Android'
        }
      },
      {
        $elemMatch: {
          title: 'Display',
          value: '4.8"'
        }
      }
    ]
  }
}

Does it find elements in index by first $elemMatch and then iterate over all found element to ensure second $elemMatch?

For my test collection with 2 androids and 1 ios explain() returns:

{
        "cursor" : "BtreeCursor category_id_1_properties.title_1_properties.value_1",
        "isMultiKey" : true,
        "n" : 1,
        "nscannedObjects" : 2,
        "nscanned" : 2,
        "nscannedObjectsAllPlans" : 2,
        "nscannedAllPlans" : 2,
        "scanAndOrder" : false,
        "indexOnly" : false,
        "indexBounds" : {
            "category_id" : [
                    [
                            ObjectId("50bcc2f0b910a6c1936a4424"),
                            ObjectId("50bcc2f0b910a6c1936a4424")
                    ]
            ],
            "properties.title" : [
                    [
                            "OS",
                            "OS"
                    ]
            ],
            "properties.value" : [
                    [
                            "Android",
                            "Android"
                    ]
            ]
        }
}