How to index a query with `WHERE field IS NULL`? I have a table with lots of inserts, setting one of the fields (uploaded_at) to NULL. Then a periodic task selects all the tuples WHERE uploaded_at IS NULL, processes them and updates, setting uploaded_at to current date.

How should I index the table?

I understand that I should use a partial index like:

CREATE INDEX foo ON table (uploaded_at) WHERE uploaded_at IS NULL

Or smth like that. I'm a bit confused though if it is correct to index on a field that is always NULL. Or if it is correct to use a b-tree index. Hash looks like a better idea, but it is obsolete and is not replicated via streaming hot-standby replication. Any advice would be greatly appreciated.

I've experimented a bit with the following indices:

"foo_part" btree (uploaded_at) WHERE uploaded_at IS NULL
"foo_part_id" btree (id) WHERE uploaded_at IS NULL

and the query planer seems to always choose the foo_part index. explain analyse also yields slightly better result for the foo_part index:

Index Scan using foo_part on t1  (cost=0.28..297.25 rows=4433 width=16) (actual time=0.025..3.649 rows=4351 loops=1)
   Index Cond: (uploaded_at IS NULL)
 Total runtime: 4.060 ms

vs

Bitmap Heap Scan on t1  (cost=79.15..6722.83 rows=4433 width=16) (actual time=1.032..4.717 rows=4351 loops=1)
   Recheck Cond: (uploaded_at IS NULL)
   ->  Bitmap Index Scan on foo_part_id  (cost=0.00..78.04 rows=4433 width=0) (actual time=0.649..0.649 rows=4351 loops=1)
 Total runtime: 5.131 ms

In this special case the column actually indexed is irrelevant for the query at hand. You can pick any column. I would pick something else than uploaded_at, which is useless. Some column that may be useful for other queries and is not bigger than 8 bytes, ideally.

CREATE INDEX foo ON table bar (some_col) WHERE uploaded_at IS NULL;

If you have no use case for any other column, it's still best to stick with the useless uploaded_at, so not to introduce additional maintenance cost for the index and restrictions for H.O.T. updates. More:

Is a composite index also good for queries on the first field?

Or use a constant as index expression if you have no use for any other index column. Like:

CREATE INDEX baz ON table bar ((TRUE)) WHERE uploaded_at IS NULL;

Parentheses required. This also keeps the index at minimum size. But while the index column is never bigger than 8 bytes (which is the case for timestamp) it's still at minimum size anyway.
Related:

Get count estimates from pg_class.reltuples for given conditions


mysql statement based replication - unsafe statements I have recently set up statement based replication and my error logs are filling up with this error

  121231 21:10:55 [Warning] Unsafe statement written to the binary log
  using statement format since BINLOG_FORMAT = STATEMENT. INSERT... ON
  DUPLICATE KEY UPDATE  on a table with more than one UNIQUE KEY is
  unsafe Statement: INSERT INTO bunching (route_number, vehicle_name,
  time, status, direction, eta) VALUES (11, '223', -1.0, 1, -1, 99999.0)
  ON DUPLICATE KEY UPDATE route_number = 11, time = -1.0, status =1,
  direction = -1, eta = 99999.0

and

  121231 21:10:55 [Warning] Unsafe statement written to the binary log
  using statement format since BINLOG_FORMAT = STATEMENT. INSERT... ON
  DUPLICATE KEY UPDATE  on a table with more than one UNIQUE KEY is
  unsafe Statement: INSERT INTO vehicleETA (routes_id, vehicleNum,
  relEta, relStopID, lat, lng, bearing, online, msg, lastReport,
  direction, inETA) VALUES (18, 2012, 39.70092857098426, 7, 29.970915,
  -90.09537, 2.2320573776159844, 1, '', '2012-12-31 21:10:55',1, 1 ) ON DUPLICATE KEY UPDATE routes_id = 18, relEta = 39.70092857098426,
  relStopID = 7, lat = 29.970915, lng = -90.09537, bearing =
  2.2320573776159844, online = 1, msg = '', lastReport = '2012-12-31 21:10:55', direction =1, inETA = 1, GMT = '031052', lastPass = 0

Mysql Docs state that this is unsafe because:

  INSERT ... ON DUPLICATE KEY UPDATE statements on tables with multiple
  primary or unique keys. When executed against a table that contains
  more than one primary or unique key, this statement is considered
  unsafe, being sensitive to the order in which the storage engine
  checks the keys, which is not deterministic, and on which the choice
  of rows updated by the MySQL Server depends.

I simply don't understand what this means. Can someone explain this to me? I'm wondering if this is a huge problem that will cause data differences on the slave or a warning I can simply ignore. Unfortunately these errors are filling the logs quite quickly. This data is relatively ephemeral and im

I have the option of

changing the logging mode to "mixed"
turning off warnings (ignoring these warnings)

Or we can alter the queries. Any advice here?

It will probably cause differences on the slave in the long run. I've had the same warnings (but with INSERT DELAYED, so your mileage may vary), and on a couple of write intensive tables the data would be different a couple of times a week (leading to a lenghty dump and import).

The ordering of some things is not necessarily the same on every mysql server, so you might end up with the same data in a different order. This is what "not deterministic" means, you execute the exact same query, but you might end up with different results.

If your mysql version supports the MIXED mode for binlogging, I'd definitely use that.
As you can see on http://dev.mysql.com/doc/refman/5.1/en/binary-log-formats.html, the only reason it's not MIXED by default is backward compatibility:

  Support for row-based logging was added in MySQL 5.1.5. Mixed logging
  is available beginning with MySQL 5.1.8. In MySQL 5.1.12, MIXED become
  the default logging mode; in 5.1.29, the default was changed back to
  STATEMENT for compatibility with MySQL 5.0.

MIXED mode uses STATEMENT mode binlogging (the default), unless it knows the query is non deterministic, then it'll switch to ROW mode for those queries. With ROW mode, it simply sends the changed rows if any, so that's deterministic again.


How to access a shared folder from Windows Cluster IP This is a very strange question, but I am trying to access a Windows file share from a SQL server cluster IP, that resides on that server. In the past and on other servers I have, I get connected to the share on the primary node. I just stood up a 2012R2 cluster and when I try to access the cluster IP I cannot get to any shares. It takes me to an empty share and does not let me navigate out of the share. Has anyone experienced this? Is there any way to resolve this? Any tips are appreciated.

Thanks!

I figured it out. The problem was we mounted and unmounted the drives initially and that share is only created once automatically. You have to create it manually after the fact.


How to determine why a MySQL server has become slow and requests sometimes time out? I'm working on my first project that makes heavy use of a MySQL database and am struggling to understand performance issues I'm having.

Most of the time, responses are returned quickly from the server, but when I have certain scripts that make a lot of queries (both reading and writing data) running in the background, I'm finding that other requests are slow to receive responses, or are timing out entirely.

As an example, yesterday when a PHP script that makes a lot database queries was running, requests to the server for pages that made a number of queries before returning a response were timing out -- I couldn't even connect to the database with Navicat, as it timed out as well.

In the above example, I ran top/htop in an SSH console and found that there was very low CPU usage and memory usage was only at about 50%. Because of this, I think the problem lies with the database, but I'm having trouble understanding what is causing the issues, such as whether they might be due to:

Slow queries
Too many connections
Too many queries per second
Other potential issues I'm not aware of

I know about the slow query log, but none of my queries are very slow.

What methods or tools can I use to determine what is causing a MySQL database to become slow, sometimes with requests even timing out?

You need to tune your database. This is a complicated process with many variables and decision trees. Without specifics we can only really get you started here.
If you have a script that runs lots of SQL and slows down your DB, and your cpu and memory usage are fine while it's running, then you are probably running into I/O problems. i.e. You are hammering the disk. You need to look through the SQL used in the script and tune it. For example, it might be generating lots of full table scans or creating too many transaction logs that are slowing down the DB. To start with, check if any unneccessary full table scans are taking place, and see if you can add indexes to the relevant columns to reduce the I/O. then report back and we can go from there.
The above linked MySQLtuner script is excellent but understanding the results may be challenging for you if you are inexperienced with databases. Do read the disclaimers on the main page before you get started: https://github.com/major/MySQLTuner-perl


SQL schema for movie rating and seenlist I'm making funny app.

User can mark movie as seen.
User can rate movie (1-10).
When user rate movie it is automatically marks as seen.
User can't remove movie from seenlist if movie is rated by him.

User have 2 tabs: seen movies which have movies in seenlist and rated movies. And tab with rated movies for movies that has rating.

I was going to make 2 tables:
ratings (user_id, movie_id, rating, created_at)
seen(user_id, movie_id, created_at).

But I don't like to duplicate data, so I kind of think maybe single ratings table will be fine, if user wants only mark as seen, rating value will be 0 or -1.
One think will be is to make where clause when calculating movie avg rating (where ratiny >0). But everything else is done dryer.

What do you think is better single table or separate tables?

What does created mean?  When the record was created?  If so, of what use is that?  Perhaps it means when_seen (or rated)?

I see no necessity to separate the two tables.

Since seen is not "optional", and rating is, consider

rating TINYINT UNSIGNED NULL  COMMENT '1..10'

Where the column is NULL until the user specifies a rating.

NULL has the advantage that

SELECT movie_id, AVG(rating)
    ...

will ignore the NULL values and get the 'correct' average.


ERROR 1034 (HY000) : Wrong aligned block, while importing in mysql I got a SQLdump from my database and now I want to import it in to an other mysql server with same version(5.5.22-0ubuntu1) in ubuntu 12.04 .but at the middle of importing, it said:

ERROR 1034 (HY000) at line 3502: Key 2 - Found wrong stored record at 232882088

I do it again and I got another error

ERROR 1034 (HY000) at line 3502: Wrong aligned block at 11847827

I use default parameter to create SQL dump
This is my my.conf:
[client]
    port            = 3306
    socket          = /var/run/mysqld/mysqld.sock

[mysqld_safe]
socket          = /var/run/mysqld/mysqld.sock
nice            = 0

[mysqld]
#
# * Basic Settings
#
user            = mysql
pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
port            = 3306
basedir         = /usr
datadir         = /media/HDD1/comparable/mysql
tmpdir          = /tmp
lc-messages-dir = /usr/share/mysql
skip-external-locking
bind-address            = 127.0.0.1

key_buffer              = 16M
max_allowed_packet      = 11600M
thread_stack            = 192K
thread_cache_size       = 8
myisam-recover         = BACKUP
#max_connections        = 100
#table_cache            = 64
#thread_concurrency     = 10

query_cache_limit       = 1M
query_cache_size        = 16M

#general_log_file        = /var/log/mysql/mysql.log
#general_log             = 1
#log_slow_queries       = /var/log/mysql/mysql-slow.log
#long_query_time = 2
#log-queries-not-using-indexes
#server-id              = 1
#log_bin                        = /var/log/mysql/mysql-bin.log
expire_logs_days        = 10
max_binlog_size         = 100M
#binlog_do_db           = include_database_name
#binlog_ignore_db       = include_database_name

# chroot = /var/lib/mysql/
# ssl-ca=/etc/mysql/cacert.pem
# ssl-cert=/etc/mysql/server-cert.pem
# ssl-key=/etc/mysql/server-key.pem

[mysqldump]
quick
quote-names
max_allowed_packet      = 11600M
net_write_timeout       = 10000
[mysql]
#no-auto-rehash # faster start of mysql but no tab completition

[isamchk]
key_buffer              = 16M

!includedir /etc/mysql/conf.d/

start of SQL dumb:

DROP TABLE IF EXISTS `DescIdf`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `DescIdf` (
  `word` varchar(255) NOT NULL,
  `idf` float NOT NULL,
  PRIMARY KEY (`word`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

...

Can anyone tell me how can I fix it?

At first glance, I would say you have some corruption. But, this an import. That scares me. Take your mysqldump and run this:

head -20 SQLDump.sql

Does the dump contain DROP TABLE and CREATE TABLE ? My guess would be no.

Did you change the table structure, i.e.,

added columns to the table
removed columns from the table
changed charset/collation to utf8

after doing that mysqldump ? My guess would be yes.

If neither of these are the case, then your table has some corruption in the .frm.

Try installing MySQL on another DB Server and loading the SQLDump there. If nothing errors out, the mysqldump is fine. This verifies some corruption.

Try dropping the problem table, create it from scratch. Then, load it back. You could run myisamchk against the MyISAM table.

http://bugs.mysql.com/bug.php?id=44571
http://grokbase.com/t/mysql/mysql/123aqcrswa/help-the-dreaded-incorrect-key-file-for-table-xxxxxxx-try-to-repair-it-error


Database_Id for our database in sys.databases does not match that of DB_ID ( '<our database' ) The Database ID in sysdatabases and sys.databases for our database is 4.

The function DB_ID ( '<our database>' ) returns 5 for our database.
Also any missing index data for our database in the table sys.dm_db_missing_index_details is assigned to database_id 5 also.

It seems to me that the sys.databases should have ID 5 and NOT 4. This is driving me bonkers and making it so sp_BlitzIndex does not work.

Does anyone know why my database id doesn't match between sys.databases, sys.dm_db_missing_index_details and DB_ID() on SQL Azure? Is it actually screwed up or does this just work different in SQL Azure?

Azure SQL DB is different !

The database_id column from sys.databases (dont use sysdatabases <-- its deprecated) will remain the same for the database.

The DB_ID() is a function and the value will change if the Azure DB is failed over.

Best is to use name <-- database name column.

People have reported that here and here.

  One shouldn't rely on database id / db_id builtin in azure as it changes whenever database moves to a different sql instance (because of the database failover). Use name instead of the id. Also please note that the db_id can potentially be different than in the sys.databases


Distinct in one column where another column has same values I have a table sample

  id   |  name  | type
-------+--------+------
 PR001 | DC001  |    a
 PR002 | DC001  |    a
 PR003 | DC001  |    b
 PR004 | DC002  |    a
 PR005 | DC003  |    a
 PR006 | DC003  |    a
 PR007 | DC004  |    b
 PR008 | DC005  |    c

Is there a way to display distinct for column name only if where column type should be all the same value ('a')

output I want to achieve

 name   | type
---------+------
 DC002  |    a
 DC003  |    a

Any help would be greatly appreciated

SELECT name, MIN(type) AS type
FROM table_name
GROUP BY name
HAVING COUNT(DISTINCT type) = 1 ;

or the same with:

HAVING MIN(type) = MAX(type)

I assume that type is not nullable.

If you want only those that have type = 'a', use:

HAVING MIN(type) = 'a'
   AND MAX(type) = 'a' ;


How i can create backup script from diagram in SQL Server? I use SQL Server 2012 and SQL Server 2008 R2.

I create a script from all object (tables / trigger / stored procedure / function ...) in my database.

I generated this script from SQL Server Management Studio.

I can recreate my database with this scrips on the other server.

But I miss all diagrams of my database after run my script for create another database.

Therefore, I need create backup script from all diagrams that exist in my database.

I need execute this script on the destination database for recreating all my diagrams.

I found this Link. but i need some thinks that create all script (Insert Command) automatically.

I write this query and this work.

USE DestinationDatabase

DELETE  sysDiagrams
WHERE   name IN ( SELECT    name
              FROM      SourceDatabase.dbo.sysDiagrams )

SET IDENTITY_INSERT sysDiagrams ON

INSERT  sysDiagrams
    ( name ,
      principal_id ,
      diagram_id ,
      version ,
      definition
    )
    SELECT  name ,
            principal_id ,
            diagram_id ,
            version ,
            definition
    FROM    SourceDatabase.dbo.sysDiagrams

SET IDENTITY_INSERT sysDiagrams OFF


Is staggering reindexing jobs a good strategy? I have inherited a server with 35 databases and a job that reindexes all tables on all databases (using sp_MSForEachTable). It's taking over 14 hours to complete and is now blocking other processes.

My question is this:

Would it be an acceptable strategy to stagger the reindexing over the week? Say, 5 DBs per night?

Thanks.

As long as the indexes that need to be rebuilt get rebuilt it should be fine to stagger them.

This will of course vary based on your usage - if you have a ton of updates/inserts/deletes run on Fridays then there may be a good reason that you currently reindex on Saturdays.

Something else you can do, instead of blindly rebuilding every index every week, is using some queries to check fragmentation levels and only reindexing when a specific index is over a certain threshold of fragmentation.  The usual threshold (which was chosen somewhat randomly by Paul Randal many years ago) is 30%.  There are dozens if not hundreds of sample scripts available for this.


New PostgreSQL 9.1 configuration doesn’t work I changed the length of table names in PostgreSQL in the file pg_config_manual.h (in C:\Program Files\PostgreSQL\9.1\include) to 128. Then I reloaded the new configuration by selecting menu "Reload Configuration" under the menu PostgreSQL 9.1 in start menu. Also, I restarted the PostgreSQL. But the change did not take effect. Length of table names in my database still has the limit 64 characters.

How to solve this problem? I am using PostgreSQL 9.1 on Windows 7 Professional.

pg_config_manual.h is not a configuration file, it's part of the source code. After a change in the source code, you have to compile all the code and install the program you just compiled.

Changing the max length for names is not trivial.


Create a secondary read-only copy of DB in a different server We have users that query our prod database (mostly ODBC through MS-Access) to create some customs reports. We want to copy our data to a second server (replicate / log shipping / ???) so the load on the prod DB is less. Many of the tables in the Prod DB do not have primary keys and is a SQL Server 2005. Our target DB is SQL Server 2012 Standard (though we can down-grade the destination server if we have to).

How could we accomplish that? We've tried replication and failed because of the lack of primary keys. We also tried log-shipping but the second DB cannot be brought online because of the difference in SQL versions.

Thanks,
Jim

If you only need the database updated once a day, why not simply have a SQL Server Agent job restore a backup from the production database onto the secondary machine nightly?

Simple to setup and provides a handy way to validate your backups.


Can you Schedule a job inside another job? I have many jobs that need to run but they cant run at the same time. the first step of each checks to see if any others are running if yes it waits a little bit and checks again. This can lead to a locking situation where two jobs are waiting for each other to finish.

The built in retry wont work because they are considered running while waiting to retry. I could just error out and have the job run every 10min checking when it was last run successfully and if anyone else is running. Then run or error again, but that could lead to jobs happening way off scheduled.

I have looked around but can't find anything, so I don't know if it is even possible but can you set up a one time schedule, like run this job at this time from inside another job?

Or is there a better way of doing this that I have overlooked?

Yep, indeed you can! So you are hoping to have one job complete and the last step of that job would be to kick off another job? Here is the command to kick off the next job: sp_start_job

E.g. The first job has a schedule and  final step of the job is to run:

exec msdb.dbo.sp_start_job N'My Second Job';

"My Second Job" doesn't have a schedule, relying on the first job to start it.


Eliminate Key Lookup (Clustered) operator that slows down performance How can I eliminate a Key Lookup (Clustered) operator in my execution plan?

Table tblQuotes already has a clustered index (on QuoteID) and  27 nonclustered indexes, so I am trying not to create any more.

I put the clustered index column QuoteID in my query, hoping it will help - but unfortunately still the same.

Execution plan here.

Or view it:

This is what the Key Lookup operator says:

Query:

declare
        @EffDateFrom datetime ='2017-02-01',
        @EffDateTo   datetime ='2017-08-28'

SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

IF OBJECT_ID('tempdb..#Data') IS NOT NULL
    DROP TABLE #Data
CREATE TABLE #Data
(
    QuoteID int NOT NULL,   --clustered index

    [EffectiveDate] [datetime] NULL, --not indexed
    [Submitted] [int] NULL,
    [Quoted] [int] NULL,
    [Bound] [int] NULL,
    [Exonerated] [int] NULL,
    [ProducerLocationId] [int] NULL,
    [ProducerName] [varchar](300) NULL,
    [BusinessType] [varchar](50) NULL,
    [DisplayStatus] [varchar](50) NULL,
    [Agent] [varchar] (50) NULL,
    [ProducerContactGuid] uniqueidentifier NULL
)
INSERT INTO #Data
    SELECT
        tblQuotes.QuoteID,

          tblQuotes.EffectiveDate,
          CASE WHEN lstQuoteStatus.QuoteStatusID >= 1   THEN 1 ELSE 0 END AS Submitted,
          CASE WHEN lstQuoteStatus.QuoteStatusID = 2 or lstQuoteStatus.QuoteStatusID = 3 or lstQuoteStatus.QuoteStatusID = 202 THEN 1 ELSE 0 END AS Quoted,
          CASE WHEN lstQuoteStatus.Bound = 1 THEN 1 ELSE 0 END AS Bound,
          CASE WHEN lstQuoteStatus.QuoteStatusID = 3 THEN 1 ELSE 0 END AS Exonareted,
          tblQuotes.ProducerLocationID,
          P.Name + ' / '+ P.City as [ProducerName],
        CASE WHEN tblQuotes.PolicyTypeID = 1 THEN 'New Business'
             WHEN tblQuotes.PolicyTypeID = 3 THEN 'Rewrite'
             END AS BusinessType,
        tblQuotes.DisplayStatus,
        tblProducerContacts.FName +' '+ tblProducerContacts.LName as Agent,
        tblProducerContacts.ProducerContactGUID
FROM    tblQuotes
            INNER JOIN lstQuoteStatus
                on tblQuotes.QuoteStatusID=lstQuoteStatus.QuoteStatusID
            INNER JOIN tblProducerLocations P
                On P.ProducerLocationID=tblQuotes.ProducerLocationID
            INNER JOIN tblProducerContacts
                ON dbo.tblQuotes.ProducerContactGuid = tblProducerContacts.ProducerContactGUID

WHERE   DATEDIFF(D,@EffDateFrom,tblQuotes.EffectiveDate)>=0 AND DATEDIFF(D, @EffDateTo, tblQuotes.EffectiveDate) <=0
        AND dbo.tblQuotes.LineGUID = '6E00868B-FFC3-4CA0-876F-CC258F1ED22D'--Surety
        AND tblQuotes.OriginalQuoteGUID is null

select * from #Data

Execution Plan:

Key lookups of various flavors occur when the query processor needs to obtain values from columns that are not stored in the index used to locate the rows required for the query to return results.

Take for example the following code, where we're creating a table with a single index:

USE tempdb;

IF OBJECT_ID(N'dbo.Table1', N'U') IS NOT NULL
DROP TABLE dbo.Table1
GO

CREATE TABLE dbo.Table1
(
    Table1ID int NOT NULL IDENTITY(1,1)
    , Table1Data nvarchar(30) NOT NULL
);

CREATE INDEX IX_Table1
ON dbo.Table1 (Table1ID);
GO

We'll insert 1,000,000 rows into the table so we have some data to work with:

INSERT INTO dbo.Table1 (Table1Data)
SELECT TOP(1000000) LEFT(c.name, 30)
FROM sys.columns c
    CROSS JOIN sys.columns c1
    CROSS JOIN sys.columns c2;
GO

Now, we'll query the data with the option to display the "actual" execution plan:

SELECT *
FROM dbo.Table1
WHERE Table1ID = 500000;

The query plan shows:

The query looks at the IX_Table1 index to find the row with Table1ID = 5000000 since looking at that index is much faster than scanning the entire table looking for that value.  However, to satisfy the query results, the query processor must also find the value for the other columns in the table; this is where the "RID Lookup" comes in.  It looks in the table for the row ID (the RID in RID Lookup) associated with the row containing the Table1ID value of 500000, obtaining the values from the Table1Data column.  If you hover the mouse over the "RID Lookup" node in the plan, you see this:

The "Output List" contains the columns returned by the RID Lookup.

A table with a clustered index and a non-clustered index makes an interesting example.  The table below has three columns; ID which is the clustering key, Dat which is indexed by a non-clustered index IX_Table, and a third column, Oth.

USE tempdb;

IF OBJECT_ID(N'dbo.Table1', N'U') IS NOT NULL
DROP TABLE dbo.Table1
GO

CREATE TABLE dbo.Table1
(
    ID int NOT NULL IDENTITY(1,1)
        PRIMARY KEY CLUSTERED
    , Dat nvarchar(30) NOT NULL
    , Oth nvarchar(3) NOT NULL
);

CREATE INDEX IX_Table1
ON dbo.Table1 (Dat);
GO

INSERT INTO dbo.Table1 (Dat, Oth)
SELECT TOP(1000000) CRYPT_GEN_RANDOM(30), CRYPT_GEN_RANDOM(3)
FROM sys.columns c
    CROSS JOIN sys.columns c1
    CROSS JOIN sys.columns c2;
GO

Take this example query:

SELECT *
FROM dbo.Table1
WHERE Dat = 'Test';

We're asking SQL Server to return every column from the table where the Dat column contains the word Test.  We have a couple of choices here; we can look at the table (i.e. the clustered index) - but that would entail scanning the entire thing since the table is ordered by the ID column, which tells us nothing about which row(s) contain Test in the Dat column.  The other option (and the one chosen by SQL Server) consists of seeking into the IX_Table1 non-clustered index to find the row where Dat = 'Test', however since we need the Oth column as well, SQL Server must perform a lookup into the clustered index using a "Key Lookup" operation.  This is the plan for that:

If we modify the non-clustered index so that it includes the Oth column:

DROP INDEX IX_Table1
ON dbo.Table1;
GO

CREATE INDEX IX_Table1
ON dbo.Table1 (Dat)
INCLUDE (Oth);        <---- This is the only change
GO

Then re-run the query:

SELECT *
FROM dbo.Table1
WHERE Dat = 'Test';

We now see a single non-clustered index seek since SQL Server simply needs to locate the row where Dat = 'Test' in the IX_Table1 index, which includes the value for Oth, and the value for the ID column (the primary key), which is automatically present in every non-clustered index.  The plan:


Corrupt MySQL log file - cannot start the database My VM ran out of disk space and MySQL wasn't able to write to the log file causing it to crash. Here is the log file:

2017-09-05T11:47:00.636352Z 0 [Note] InnoDB: Initializing buffer pool, total size = 128M, instances = 1, chunk size = 128M
2017-09-05T11:47:00.650903Z 0 [Note] InnoDB: Completed initialization of buffer pool
2017-09-05T11:47:00.654252Z 0 [Note] InnoDB: If the mysqld execution user is authorized, page cleaner thread priority can be changed. See the man page of setpriority().
2017-09-05T11:47:00.667475Z 0 [Note] InnoDB: Highest supported file format is Barracuda.
2017-09-05T11:47:00.669522Z 0 [ERROR] InnoDB: Ignoring the redo log due to missing MLOG_CHECKPOINT between the checkpoint 72827846847 and the end 72827846656.
2017-09-05T11:47:00.669553Z 0 [ERROR] InnoDB: Plugin initialization aborted with error Generic error
2017-09-05T11:47:01.270002Z 0 [ERROR] Plugin 'InnoDB' init function returned error.
2017-09-05T11:47:01.270053Z 0 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
2017-09-05T11:47:01.270064Z 0 [ERROR] Failed to initialize plugins.
2017-09-05T11:47:01.270071Z 0 [ERROR] Aborting

I've tried to remove the ib_log* file in /var/lib/mysql and set innodb_force_recovery = 3 in my.cnf file. But received the same error. Any help appreciated.

Was able to do a mysqldump, remove everything. Do a clean reinstall of mysql and import it back. Thanks for the help !


SQL aliasing to redirect traffic between databases on the same server In order to upgrade a database with no client disconnects, I clone an existing database and put it in a readonly mode. Now, I want all user traffic to be redirected from the original database to the cloned one (both are on the same server). I want to do it with SQL aliases. However, I see that aliasing is possible for server instances and protocols. Is there another way to go about database redirects on the same server?

Aliases are only for server/instances of SQL Server, not for databases.

In your case I'd create a database snapshot of the current database (so there's no need for a new read-only copy of your db), redirect all user connections there, do you update, then drop the snapshot. Please take into consideration that a snapshot can take a lot of space if forgotten there, not dropped on time.


SQL Server 2012: Security_error_ring_buffer_recorded: ImpersonateSecurityContext a couple servers I manage are recording a lot of events in the system_health XE session.

name: security_error_ring_buffer_recorded
api_name: ImpersonateSecurityContext
calling_api_name: NLShimImpersonate
error_code: 5023

The error_code 5023 should be (System Error Codes):

ERROR_INVALID_STATE    5023 (0x139F)

The group or resource is not in the correct state to perform the requested operation.

I have no failed login event in the event security log, nor in the SQL Server log.

I am going to assume you are looking for the definition and the root cause of these events.

From How It Works: SQL Server 2005 SP2 Security Ring Buffer – RING_BUFFER_SECURITY_ERROR (archive):

  SQL Server 2005 SP2 added new ring buffer entries (sys.dm_os_ring_buffers) for various security errors. The reason the ring buffer entries were added was to provide the DBA with more details as to why a client is receiving a failed login or other such error.

You state that you have no failed login entries in the event log nor in the error log. Instead you can query this ring buffer directly:

SELECT CONVERT (varchar(30), GETDATE(), 121) as runtime,
dateadd (ms, (a.[Record Time] - sys.ms_ticks), GETDATE()) as [Notification_Time],
a.* , sys.ms_ticks AS [Current Time]
FROM
(SELECT
x.value('(//Record/Error/ErrorCode)[1]', 'varchar(30)') AS [ErrorCode],
x.value('(//Record/Error/CallingAPIName)[1]', 'varchar(255)') AS [CallingAPIName],
x.value('(//Record/Error/APIName)[1]', 'varchar(255)') AS [APIName],
x.value('(//Record/Error/SPID)[1]', 'int') AS [SPID],
x.value('(//Record/@id)[1]', 'bigint') AS [Record Id],
x.value('(//Record/@type)[1]', 'varchar(30)') AS [Type],
x.value('(//Record/@time)[1]', 'bigint') AS [Record Time]
FROM (SELECT CAST (record as xml) FROM sys.dm_os_ring_buffers
WHERE ring_buffer_type = 'RING_BUFFER_SECURITY_ERROR') AS R(x)) a
CROSS JOIN sys.dm_os_sys_info sys
ORDER BY a.[Record Time] ASC

The notification time might shed some light on the root cause.

I think you will find that the date/time of the entries will line up with login failure entries in the errorlog similar to:

  "Login failed for user 'domain\user'. Reason: Token-based server access validation failed with an infrastructure error. Check for previous errors. [CLIENT: ] Error: 18456 Severity: 14 State: 11."

From Troubleshooting specific Login Failed error messages (archive) :

  State 11 corresponds to “Valid login but server access failure” which indicates that the login is valid but is missing certain security privileges which would grant it access to the instance.

  Check if that login is directly mapped to one of the SQL Server logins by looking in the output of sys.server_principals.
  If the login is directly mapped to one of the available logins in the SQL instance, then check if the SID of the login matches the SID of the Windows Login.

If someone dropped the login at the Windows/AD level, and added it back, it will get a new SID which won't match the SID SQL has stored in its system catalog and it will fail.


postgresql select error: could not read block, Invalid argument SQL state: XX000 Postgres 9.6 on Windows 10

I'm creating a table filtered_table1 via a simple select operation on another table table1 (around 1.8 billion rows) and filtering it by some field

create table filtered_table1  as (
 select *
    from table1
    where epoch_time >= 1510227000000 and epoch_time <= 1510230600000
);

I get the error pasted below on running this query after sometime.

ERROR:  could not read block 4585496 in file "base/16393/1949151.34": Invalid argument
SQL state: XX000

I followed some suggestions especially the one given here where it was suggested to try setting zero_damaged_pages = on in postgresql.conf file. I continue to get this error even after doing this. Request help on finding and fixing this issue. Note: I have other tables in the same database and do not see such error while working with those.

I hope my post won't sound too rude, but you have corruptions issues and are trying rather desperate measures to fix it. Did you have a look at the documentation regarding zero_damaged_pages ? Make it a habit of looking at the official documentation for such configuration:

  Setting zero_damaged_pages to on causes the system to instead report a
  warning, zero out the damaged page in memory, and continue processing.
  This behavior will destroy data

Please have a look at https://wiki.postgresql.org/wiki/Corruption first. As stated on the wiki, you need to do a file system backup, and work on a copy. If you do not deeply understand what commands and configurations like zero_damaged_pages, pg_reset_xlog, ... do, save yourself a LOT of troubles and avoid using them.

But all is not grey and horrible... If you want a safe way out, do a restore from a safe backup (because you do have backups), and as Christopher Pettus said on one of his talks on PostgreSQL corruption, receive the praise of a grateful nation.


Oracle DB Security Issue: Anyone can login using SQL*PLUS, with any username, and any wrong passphrase I'm using Oracle DB 11g-R2 on CentOS 6.4, and unfortunately there is a terrible issue freaking me out.

Anyone can login using SQL*PLUS, with any username, and any wrong passphrase.

Consider somebody logging in using SYS AS SYSDBA with a wrong passphrase, and get all database and tables he wants. Isn't it bad?

I know you may say that when somebody gets in physically, they can circumvent the rest, yes indeed. But anyone who knows this problem, doesn't need to get in physically, they will be only in need of the IP address and the name of your DB to login remotely or use a script to do what they need by SQL*PLUS.

Fortunately, anyone logging in with wrong passphrase for three times, will be locked for the next time. But what is the benefit? Personally as an attacker, I only need one time to be in someone's database to get the all things I need.

Does anybody know how can I fix this and why is this happening?

The OSDBA group (typically, dba)
You must create this group the first time you install Oracle Database software on the system. This group identifies operating system user accounts that have database administrative privileges (the SYSDBA privilege).

This is expected behavior as you are logging the Oracle Database from the OS user which belongs to DBA OS group.
[oracle@localhost Desktop]$ sqlplus sys/abc as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Tue Nov 15 11:23:20 2016

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

[oracle@localhost Desktop]$ groups
oinstall dba

[oracle@localhost Desktop]$ su joe

[joe@localhost oracle]$ sqlplus sys/abc as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Tue Nov 15 11:26:56 2016

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

ERROR:
ORA-01031: insufficient privileges

Let's see which OS user group does the Joe belong.
[joe@localhost oracle]$ groups
joe

Here, the OS User Joe doesn't belong to DBA OS user group, he can not login to the database with wrong password.

But anyone who knows this problem, doesn't need to get in physically, they will be only in need of the IP address and the name of your DB to login remotely or use a script to do what they need by sqlplus.

No, this is not correct this can be controlled by remote_os_authent initialization parameter :
SQL> show parameter remote_os_authent

NAME                     TYPE    VALUE
------------------------------------ ----------- ------------------------------
remote_os_authent            boolean     FALSE

Setting this parameter to FALSE can prevent such situation.

How I should unlock a user which got locked after three wrong attempts?

You can use alter user username account unlock command as SYSDBA.


updating a hstore with multiple new columns I am trying to add 2 new hstore key/value pairs in my postgreSQL database.  Is it possible to do this in one statement or do I need to break it up?  Here is what I have so far and it is not working.

with foo as (
   select
          id,
          new_value_1,
          new_value_2
   from table 3
)
update public.table1
    set hstore = hstore('new_key_1',foo.new_value_1),
        hstore = hstore('new_key_2',foo.new_value_2)
    where id = foo.id

I am getting a error where I cannot call the same row in a update twice in a row.  How would can i insert into the hstore two different values I am selecting in the foo statement above as values with a new key into a hstore of columns that already exist.  I just need to tag the rows so even if the values change the original values are stored for future issue resolution.

you can concatenate them:

set hstore_col = hstore('new_key_1',foo.new_value_1)||hstore('new_key_2',foo.new_value_2)

Alternatively use the constructor function that accepts two arrays:

set hstore_col = hstore(array['new_key_1', 'new_key_2'],array[foo.new_value_1, foo.new_value_2])

The second solution will be easier to extend when you have more keys then just two

If you don't want to overwrite the current value, then append that to the original one:

set hstore_col = hstore_col || hstore('new_key_1',foo.new_value_1)||hstore('new_key_2',foo.new_value_2)

or

set hstore_col = hstore_col || hstore(array['new_key_1', 'new_key_2'],array[foo.new_value_1, foo.new_value_2])


RMAN backup of RAC database failing Backup goes through if I don’t have any explicit connection to any instance :

RUN {
ALLOCATE CHANNEL ch1 TYPE DISK FORMAT '\\192.168.2.11\test\DF_CH00_%U';
ALLOCATE CHANNEL ch2 TYPE DISK FORMAT '\\192.168.2.11\test\DF_CH01_%U';
ALLOCATE CHANNEL ch3 TYPE DISK FORMAT '\\192.168.2.11\test\DF_CH02_%U';
ALLOCATE CHANNEL ch4 TYPE DISK FORMAT '\\192.168.2.11\test\DF_CH03_%U';
Backup incremental level 0 database;
RELEASE CHANNEL ch1;
RELEASE CHANNEL ch2;
RELEASE CHANNEL ch3;
RELEASE CHANNEL ch4;
}

But it fails when I specify explicitly the instance name :

RMAN> RUN {
2> ALLOCATE CHANNEL ch00 TYPE DISK connect 'sys/oracle@realdb_1' FORMAT '\\192.168.2.11\test\DF_CH00_%U';
3> ALLOCATE CHANNEL ch01 TYPE DISK connect 'sys/oracle@realdb_2' FORMAT '\\192.168.2.11\test\DF_CH01_%U';
4> ALLOCATE CHANNEL ch02 TYPE DISK connect 'sys/oracle@realdb_3' FORMAT '\\192.168.2.11\test\DF_CH02_%U';
5> ALLOCATE CHANNEL ch03 TYPE DISK connect 'sys/oracle@realdb_4' FORMAT '\\192.168.2.11\test\DF_CH03_%U';
6> Backup incremental level 0 database;
7> RELEASE CHANNEL ch00;
8> RELEASE CHANNEL ch01;
9> RELEASE CHANNEL ch02;
10> RELEASE CHANNEL ch03;
11> }
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-12001: could not open channel ch00
RMAN-10008: could not create channel context
RMAN-10003: unable to connect to target database
ORA-12154: TNS:could not resolve the connect identifier specified

Any ideas ?

Are you able to tnsping realdb_1, realdb_2 individually?

If you're using SCAN, but haven't manually added node specific entries to your tnsnames.ora, this problem is likely to occur upon requesting connection to a specific node, since SCAN may be configured to resolve to the service_name of the RAC, rather than to the "instance_name".

Here's my tnsnames.ora:

USAPROJ =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = rac-scan.localdomain)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = usaproj.localdomain)
    )
  )

USAPROJ1 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = rac-scan.localdomain)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = usaproj.localdomain)
      (INSTANCE_NAME = usaproj1)   <-------
        )
  )

I created another entry, usaproj1, and in addition to service_name parameter, I added "instance_name", to specify the node I wanted to connect.

Hope it helps.


Suddenly Slow Execution Plan for Stored Proc I'm trying to understand an issue we're having with SQL Server 2000. We are a moderately transactional website and we have a stored proc called sp_GetCurrentTransactions which accepts a customerID, and two dates.

Now depending on the dates, and the customer, this query can return anything from zero to 1000s of rows.

The problem: what we've experienced is that suddenly we will get a number of errors (typically Execution Timeout Expired or similar) for a particular client while they try execute that stored proc. So we examine the query, run it in SSMS and and find that it takes 30s. So we recompile the stored proc and -bang- it runs now in 300ms.

I've spoken to our DBA about this. He has told me that the database created a query plan when we created the stored proc. He said that it was a good plan for that set of parameters, but if you throw a certain set of parameters at it, then the plan will not be the best plan for that data, and so you will see it running slow.

The options presented to me are the move that problem query from a stored proc and back into dynamic SQL that has it's execution plan created on every run.

This feels like a step back to me and I feel like there must be a way around this. Is there any other way to deal with this issue?

Any and all responses are appreciated.

This issue is called parameter sniffing.

Later versions of SQL Server give you more options in dealing with it such as OPTION (RECOMPILE) or OPTIMIZE FOR hints.

You might try declaring variables in the stored procedure, assigning the parameter values to the variables and using the variables in place of the parameters as it sounds as though most of the time you are getting a reasonably satisfactory plan.

Normally the most catastrophically bad plans are those compiled for parameters with very high selectivity but ran with parameters with low selectivity.

Assuming the plan generated is more robust with this approach and satisfactory for all parameter values then the advantage of this approach over that suggested by JNK is that it does not incur a compilation cost for every call.

The disadvantage is that for some executions run time might be greater than for a plan tailored specifically for those parameter values so it is a trade off of compile time vs execution time.


Include Columns in Index : SQL Server I have one question regarding include column in the indexes. If I have an include column which is updating its value quite often, does it update the index every time it's updating the value or does the index only update when key columns are updating? Does include column updates add performance issues? According the example below, if the Balance and DiscountedAmount keep updating does it update the index IX_Temp_RegistrationNumber for every updates?

CREATE TABLE #Temp (
    TheID BIGINT
    ,RegistrationNumber BIGINT
    ,ClientName VARCHAR(50)
    ,Balance NUMERIC(10, 2)
    ,DiscountedAmount NUMERIC(10, 2)
    )

CREATE NONCLUSTERED INDEX [IX_Temp_RegistrationNumber] ON [#Temp] ([RegistrationNumber] ASC) INCLUDE (
    [Balance]
    ,[DiscountedAmount]
    )
GO

Actually if you get Actual Plan, you will be able to see, indexes that modified by every update.

When you do update, Cluster Index and all indexes that keys and included column , had updated, will be modified.

Example 1 :
update #Temp set ClientName = 'foo'
Execution plan : The clustered index will be update

Example 2:
update #Temp set Balance = 'foo'
Execution plan : The clustered index and [IX_Temp_RegistrationNumber] will be update.

Tip :If your table haven't so many indexes or update operations isn't high, then there isn't any performance issue.


Executing a script via Powershell possibility Let's say I don't happen to have the ability to create a Stored Procedure, but I have to run the same query on a regular basis. Would I be able to put my query in a .txt document and be able to run it from Powershell by calling it? What syntax would I use for myquery.txt to run? Put another way, what's a way to end-run not being able to use a stored procedure on a query that should be made into one?

I notice you tagged the question netezza but I'm not sure that SQLCMD (suggested in the accepted answer) works with Netezza or just with SQL Server. An alternative is to use Aginity Workbench for Netezza which provides an unattended commandline option:

"C:\Program Files (x86)\Aginity\Aginity Workbench for PureData System for Analytics(x64)\Aginity.NetezzaWorkbench.exe" --unattended --stdout MyOutput.txt --stderr MyErrors.txt --description "New Execute SQL Command" --action exec --connstr "Driver={NetezzaSQL};server=mynzbox;UserName=myuser;Password=mypwd;Database=system;LoginTimeout=120" --dbtype NetezzaODBC --sqlfile MyQuery.sql


PostgreSQL: From vague "DB is slow" to concrete actions I know how to debug a particular slow query with explain analyze (PostgreSQL).

If I received vague "DB is slow" feelings, I worked with this answer: https://stackoverflow.com/a/12818168/633961

In my case there are no sequence scans. I guess a combined index is missing.

Is there a tool which can help me to find missing combined indexes?

Other strategies which help to solve the goal are welcome, too.

Update

According to Julien Rouhaud this is fixed in PostgreSQL 10: https://groups.google.com/d/msg/powa-users/UmIEWvgJ2-k/sS5lfRE4CAAJ

To resolve overall DB slow, these are the things you can look into in Postgres:

1-

 -- Lock monitoring
 SELECT relation::regclass, * FROM pg_locks WHERE NOT GRANTED;

-- To find waiting queries
SELECT pid, datname, usename, now() - query_start AS runtime, wait_event,
wait_event_type, state, query FROM pg_stat_activity WHERE wait_event
is NOT NULL AND state = 'active';

-- To look at what all is running
select * from pg_stat_activity;

-- Once you have identified the session that is blocking you, you can kill it as below, make sure you don't kill someone's else work,
-- check with DBA if unsure (replace blocking_pid with pid that is blocking your session)

   SELECT pg_terminate_backend(pg_stat_activity.pid)
FROM pg_stat_activity
WHERE pg_stat_activity.datname = 'your_db'
  AND pid <> pg_backend_pid() and pid='blocking_pid';

2- Make sure autocommit is enabled in your client (This is especially an issue in DBeaver)

3- If there is no lock, let's generate an explain plan to see what is the cost of the query and places where we can improve. How to generate an explain plan

4- You might have to vacuum the tables if there have been large DML, it is also advisable to collect stats after any large DML, by default vacuum and stats collection happen automatically. (See link above for info n VACUUM, too).

5- Regarding your combined index, that will depend on the slow queries, there is no automated way to check that, you can however make sure that your foreign keys are indexed and that you can check using information_schema views.


MS SQL Server installation fails with error message I am trying to install SQL Server (tried all 2012 Developer edition, Web edition, Standard edition, Express and also tried all 2014 versions). Each time it is failing with the following error message

> (01) 2016-08-05 13:25:12 Slp: PerfCounter calling lodctr: 'C:\Program
> Files\Microsoft SQL
> Server\MSSQL11.SQLEXPRESS12\MSSQL\Binn\perf-MSSQL$SQLEXPRESS12sqlctr.ini'
> (01) 2016-08-05 13:25:12 Slp: Prompting user if they want to retry
> this action due to the following failure: (01) 2016-08-05 13:25:12
> Slp: ----------------------------------------
>(01) 2016-08-05 13:25:12
> Slp: The following is an exception stack listing the exceptions in
> outermost to innermost order (01) 2016-08-05 13:25:12 Slp: Inner
> exceptions are being indented (01) 2016-08-05 13:25:12 Slp: (01)
> 2016-08-05 13:25:12 Slp: Exception type:
> Microsoft.SqlServer.Configuration.Sco.ScoException (01) 2016-08-05
> 13:25:12 Slp: Message: (01) 2016-08-05 13:25:12 Slp: The system cannot
> find the file specified. (01) 2016-08-05 13:25:12 Slp:  Slp:
> WatsonData =
> SQLCTR110.DLL@OpenSQLPerformanceData@CollectSQLPerformanceData@CloseSQLPerformanceData
> (01) 2016-08-05 13:25:12 Slp: DisableRetry = true (01) 2016-08-05
> 13:25:12 Slp: Inner exception type:
> System.ComponentModel.Win32Exception (01) 2016-08-05 13:25:12 Slp:
> Message: (01) 2016-08-05 13:25:12 Slp: The system cannot find the file
> specified. (01) 2016-08-05 13:25:12 Slp: (01) 2016-08-05 13:25:12 Slp:
> HResult : 0x80004005 (01) 2016-08-05 13:25:12 Slp: Error : 2 (01)
> 2016-08-05 13:25:12 Slp: Stack: (01) 2016-08-05 13:25:12 Slp: at
> Microsoft.SqlServer.Configuration.Sco.PerformanceCounter.LoadPerformanceCounter(String
> symbolInstallPath, String counterPrefixServiceName) (01) 2016-08-05
> 13:25:12 Slp: at
> Microsoft.SqlServer.Configuration.Sco.PerformanceCounter.RegisterPerformanceCounterCore(String
> libraryFileName, String openFunction, String collectionFunction,
> String closeFunction, String symbolInstallPath, String
> counterPrefixServiceName) (01) 2016-08-05 13:25:12 Slp: at
> Microsoft.SqlServer.Configuration.Sco.PerformanceCounter.RegisterPerformanceCounter(String
> libraryFileName, String openFunction, String collectionFunction,
> String closeFunction, String symbolInstallPath, String
> counterPrefixServiceName) (01) 2016-08-05 13:25:12 Slp:
> ---------------------------------------- (01) 2016-08-05 13:52:56 Slp: User has chosen to cancel this action (01) 2016-08-05 13:52:56 Slp:
> Configuration action failed for feature SQL_Engine_Core_Inst during
> timing ConfigNonRC and scenario ConfigNonRC. (01) 2016-08-05 13:52:56
> Slp: The system cannot find the file specified.

There is an entire directory of logging done by the installer. If you are getting this from the graphical popup try looking in the \Setup Bootstrap folder under your installation of Sql Server. This is created whether it succeeds or not. The path is something like so...

C:\Program Files\Microsoft SQL Server\110\Setup Bootstrap\Log

In there you will find logs in separate folders by date and time. Like so..

\20160502_124028

Start with the Detail.txt log going all the way to the bottom and work your way up looking for anything with the word 'error' in it. That should give you more information about what is happening.

Also, I do see an access violation in the middle of your stack. That is:
HResult : 0x80004005

Hi Sofia,
     The detail.txt is a good place to start. It does refer to the file that was inaccessable. It is: "C:\Program Files\Microsoft SQL Server\MSSQL11.SQLEXPRESS12\MSSQL\Binn\perf-MSSQL$SQLEXPRESS12sqlctr.ini" at the very beginning of the stack.

Whether or not this is the issue with the other editions of Sql Server you attempted to install I couldn't say. However if you aren't logged in as local Administrator while trying the install you should log in with the local Administrator account and retry the install.


Export as csv or xsl file from SQL Server 2008 express R2 I am basically LAMP developer got some assignment where SQL Server is required. I have installed SQL Server 2008 R2 Express and imported backup file in it.

Now, I want to export user table in excel or csv but not able to do that.

Can anyone tell me how can I export table?

You'll want to look at using SQL Server Integration Services.

If you want to export a small amount of data, you can also SELECT the data in SQL Server Management Studio, and highlight / copy / paste it into Excel, or rt click save as.


Mysql error : Slave SQL Could not execute ...on table Im running a percona cluster of 3 nodes.

Yesterday I needed to throw some more memory in them, so I had to power down all nodes
Now I booted up and then my 3rd Node, Gave me this error :

140701 23:40:51 [ERROR] Slave SQL: Could not execute Update_rows event on table database_prod.cron_schedule; Can't find record in 'cron_schedule', Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND; the event's master log FIRST, end_log_pos 206, Error_code: 1032
140701 23:40:51 [Warning] WSREP: RBR event 2 Update_rows apply warning: 120, 69137050

Whats my options regarding fixing this error. And what does it actually mean.

The 3rd node can not update record in cron_schedule because the record is missing.
That could happen because some binlog events were lost when the servers crashed.

First try to skip those errors and start replication.
Run this on the faulty slave:

pt-slave-restart

The script skips binlog events that return error and start replication. In some cases a slave is heavily inconsistent and replication never recovers. In that case you need to rebuild the slave. But if replication is restarted and running proceed further.

Now you need to find all inconsistencies and sync data on the master and its slaves.

First run pt-table-checksum on the master(add user/password options if needed):

pt-table-checksum -q

It will show you which tables are inconsistent i.e. have different data on the master and slaves.

Then let pt-table-sync fix the inconsistencies. Run this on the master:

pt-table-sync --execute --replicate percona.checksums --noforeign-key-checks localhost

Afterwards you may want to run pt-table-checksum again to make sure the slaves are in-sync


What are the primary architectural differences between the backup formats of some of the more common RDBMS' and what advantage do they serve? Of the systems with which I am familiar, I have noticed that SQL Server's native database backup format is binary and proprietary. PostgreSQL and MySQL allow for a simpler approach, dumping the schemata and data. This leads to an interesting question.

  What are the primary architectural differences between the backup formats of some of the more common RDBMS' and what advantage do they serve?

It seems to me that the PostgreSQL and MySQL dump-style implementations would allow for one to open the backup in a text editor and get a sense for the data being restored.

The native backup of sql server, commonly have extension of .bak is something which is proprietary of microsoft.

Different RDBMS providers have different formats and it also depends on whether they are open source (PostgreSQL and MySQL) or are patented (MS SQL Server, ORACLE or SybaseASE).

For MS SQL Server :

A backup is a page-by-page copy of the database, as it existed the moment you took the backup, a restored copy of that database will be in exactly the same state as it was the moment you took the backup.

A backup contains more than just the data. It contains the entire structure of the database including all the data structures and data along with procedures, views, functions and any other code. It also contains the settings and definitions of the database along with the users of the database. Exception is contained databases have a concept called USER WITH PASSWORD.

If you want to use something that you mentioned, have a look at SqlDump - Microsoft SQL Server database backup program

  SqlDump is a program to backup a Microsoft SQL Server database as a text file. SqlDump generates SQL statements for tables, indexes, user-defined types, views, procedures, etc and also the table data. This data can be used for a variety of purposes, such as database backups, moving databases to another server, or setting up a test database based on the contents of an existing database

Note: I have not tried the SQLDump, as I would personally prefer SSIS or T-SQL along with BCP and BULK INSERT - to move data out and in of sql server.


IBM DB2 9.7, possible to temporarily disable transaction logs? I want to clean the whole database, so I am dropping all tables and DB objects in two FOR loops, within one transaction. This makes the transaction logs become full. I could increase the transaction log size, but I would much rather drop the database without anything going into the transaction logs. Is this possible to do?

Dropping tables will engage the transaction logs. Actually dropping pretty much everything (except a tablespace or a bufferpool) engages the transaction logs.

Couple things that may help you

Using truncate on each table will drop the data from the table without logging. This only works for tables. Dropping tables and other objects will still log, but truncate would help you cut down on logging.
Dropping a tablespace (pending you are using your own and not the default IBM provided ones) will automatically drop ANY and ALL objects associated with that tablespace (usually tables), but drops the tables and the data in a way that is not logged. (There is a way to mark a tablespace for recoverable tables, but for the most part you get a clean database table-wise). NOTE: you still have to drop other objects, which will log some.
Dropping the whole database. This will drop ALL objects immediately without logging.

I guess depending on what you are doing, the second or third points will get you there the quickest. Just remember that anything dropped that does not engage the transaction logs is not recoverable from, without restoring from a backup.

I'd recommend taking a backup before you try any of these as a best practice. You could also use db2look to get the schema changes and db2move to get the schema with data (although I don' think you get any security with either of those). Again, I'd stick to a backup before you try dropping anything.

EDIT: You can indeed get some security with db2look. These are usually the -x and -xdep parameters.


POSTGRES DB Server Blunder This weekend I had to rebuild my laptop which had Postgres running. For some reason, I forgot to dump the data but I did make a complete copy of the original hard drive.

Is it possible to recover data from the original data files to transfer to another instance of Postgres?

Yes, it's fairly simple. You need to copy the database from the old disk image to a directory in the new host then start it using the same major version of PostgreSQL compiled with the same options (integer datetimes on/off, etc).

You can start the DB with a command like PGPORT=5433 pg_ctl -D /path/to/copy/of/old/database start. You must use the same PostgreSQL major version that the DB was created with before. If you're not sure what that was, check the PG_VERSION file in old data directory.

Once you've started a server with the old database you can then pg_dump the databases(s) and pg_dumpall --globals-only the users, etc, and load them into the new database.

Do not try to start the database directly on the old disk image, make a copy and start the copy.

You haven't provided details about versions, operating systems, how you installed Pg on the old and new machines, etc, so it's hard to provide more detail.


How to view security permissions for encryption keys in SQL server Is there a way to view the security permissions assigned for encryption keys in SQL Server? Furthermore, where are all these keys stored?

Without going into a long discussion about certificates and keys, I'm just going to give you what you need to create some keys, grant some access to them and then review the access granted.

I've included certificates because your keys depend upon them if you chose ENCRYPTION BY CERTIFICATE.

USE SOME_DATABASE;
GO

-- assuming you have a login testuser

CREATE USER testuser FOR login testuser
GO

--Create a database master key
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'my_Strong_Password'
GO

CREATE CERTIFICATE MyCertificateName
WITH SUBJECT = 'A label for this certificate'

-- Grant control on certificate to testuser
GRANT CONTROL ON CERTIFICATE::MyCertificateName TO testuser

-- create a SYMMETRIC key
CREATE SYMMETRIC KEY MySymmetricKeyName WITH
IDENTITY_VALUE = 'a fairly secure name',
ALGORITHM = AES_256,
KEY_SOURCE = 'a very secure strong password or phrase'
ENCRYPTION BY CERTIFICATE MyCertificateName;

-- grant control on symmetrick key to testuser
GRANT CONTROL ON SYMMETRIC KEY::MySymmetricKeyName TO testuser;

-- create ASYMMETRIC key
CREATE ASYMMETRIC KEY MyASymmetricKeyName
    WITH ALGORITHM = RSA_2048
    ENCRYPTION BY PASSWORD = 'my_Strong_Password';
GO

-- grant control and alter on asymmetric key to testuer
GRANT CONTROL ON ASYMMETRIC KEY::MyASymmetricKeyName TO testuser;
GRANT ALTER ON ASYMMETRIC KEY::MyASymmetricKeyName TO testuser;

-- display permissions for those certificates and keys we just created

declare @sql as nvarchar(max)
declare @db_name as nvarchar(max) = 'some_database'

SET @sql = '
SELECT ''' + @db_name + ''' as database_name,
CASE dperms.state_desc
WHEN ''GRANT_WITH_GRANT_OPTION'' THEN ''GRANT''
ELSE state_desc
END
+ '' '' + permission_name + '' ON '' +
CASE dperms.class
WHEN 0 THEN ''DATABASE::['' + DB_NAME() + '']''
WHEN 1 THEN
CASE dperms.minor_id
WHEN 0 THEN ''OBJECT::['' + sch.[name] + ''].['' + obj.[name] + '']''
ELSE ''OBJECT::['' + sch.[name] + ''].['' + obj.[name] + ''] (['' + col.[name] + ''])''
END
WHEN 3 THEN ''SCHEMA::['' + SCHEMA_NAME(major_id) + '']''
WHEN 24 THEN ''SYMMETRIC KEY::['' + symm.[name] + '']''
WHEN 25 THEN ''CERTIFICATE::['' + certs.[name] + '']''
WHEN 26 THEN ''ASYMMETRIC KEY::['' + asymm.[name] +'']''
END
+ '' TO ['' + dprins.[name] + '']'' +
CASE dperms.state_desc
WHEN ''GRANT_WITH_GRANT_OPTION'' THEN '' WITH GRANT OPTION;''
ELSE '';''
END COLLATE database_default AS ''Permission''
FROM [' + @db_name + '].sys.database_permissions dperms
INNER JOIN [' + @db_name + '].sys.database_principals dprins
ON dperms.grantee_principal_id = dprins.principal_id
LEFT JOIN [' + @db_name + '].sys.columns col
ON dperms.major_id = col.object_id AND dperms.minor_id = col.column_id
LEFT JOIN [' + @db_name + '].sys.objects obj
ON dperms.major_id = obj.object_id
LEFT JOIN [' + @db_name + '].sys.schemas sch
ON obj.schema_id = sch.schema_id
LEFT JOIN [' + @db_name + '].sys.asymmetric_keys asymm
ON dperms.major_id = asymm.asymmetric_key_id
LEFT JOIN [' + @db_name + '].sys.symmetric_keys symm
ON dperms.major_id = symm.symmetric_key_id
LEFT JOIN [' + @db_name + '].sys.certificates certs
ON dperms.major_id = certs.certificate_id
WHERE dperms.type <> ''CO''
AND dperms.major_id > 0
and dperms.grantee_principal_id > 4
and dperms.class in (24, 25, 26)
;'

print @sql
execute(@sql)

Aside from the service master key which is created automatically and stored in the master database, the master key, certificate and symmetric, asymmetric keys would be stored in the databases you created them in.


How can I group together rankings by number of users? I have a table which includes the following:

ID    evar     event_date   ranking
1    landing   2019-01-01      1
1    content   2019-01-02      2
1    homepage  2019-01-03      3
2    support   2019-02-01      1
2    content   2019-02-02      2
3    content   2019-01-23      1
3    support   2019-01-24      2

etc.

I am trying to map customer journeys, so I've ranked the pages that were visited by date.
I need to find the top journeys on the website - so it needs to be at this level, but aggregated by the number of users.

For example: ID 1 made the journey landing > content > homepage;
I would like to see who else made that same journey (without me manually checking)

How can I write such a query?

Your data is not enough to give the query a good test, but this FIDDLE seems to do it.

code of dbfille added:

SELECT y.journey, RANK() OVER (ORDER BY cnt DESC) as ranking
FROM
(
    SELECT x.journey, count(*) as cnt
    from
    (
        SELECT ID, STRING_AGG(RTRIM(evar),',') as journey
        FROM journey
        GROUP BY ID
    ) x
    GROUP BY journey
) y;

output:

journey,     ranking
content,support,     1
landing,content,homepage,     1
support,content,     1

when I add an extra journey like this:

INSERT INTO journey VALUES
(4    ,'landing'   ,'2019-01-01',      1   ),
(4    ,'content'   ,'2019-01-02',      2   ),
(4    ,'homepage'  ,'2019-01-03',      3   );

output:

**journey,     ranking**
landing,content,homepage,     1
support,content,     2
content,support,     2


Removing Databases in Recovery Pending Status after AG group was removed I have removed a High Availability Group, disabled the option in sql configuration manager and restarted and I have also removed the Failover cluster components from windows and restarted.

Unfortunately the databases are marked as Recovery Pending on both servers.

I have attempted to drop them, but get this:

  Msg 3752, Level 16, State 1, Line 1
  The database 'GEO' is currently joined to an availability group.  Before you can drop the database, you need to remove it from the availatility group.

I also tried to recovery the db with no success and also set it into emergency mode but cannot:

  The operation cannot be performed on database "GEO" because it is involved in a database mirroring session or an availability group. Some operations are not allowed on a database that is participating in a database mirroring session or in an availability group.

I even tried to brute force the server by stopping the engine and renaming the file to try to get it into suspect, but its still in recovery pending...  I cant re-enable the HADR settings as the windows cluster has been destroyed.

All systems catalogs are empty apart from sys.availability_replicas which has a reference to the other server (first time in a few years I wish I was back in the good old days when I could access system tables).

Any ideas how to get rid of these databases?

To resolve this I had to recreate the whole Failover cluster, re-enable HA in the SQL Configuration manager and then I could set one of the db's to recover


Show Primary and Secondary Accounts in a table I have a table that provides the relationships between Primary and Secondary accounts which looks like this:

CREATE TABLE testRelate
(
  AccountRef nvarchar(20) PRIMARY KEY,
  RelationshipType nvarchar(20),
  RelationshipRef nvarchar(10),
  PrimaryRelationshipRef nvarchar(10)
);

I am trying (with no success) to find a way to show each account and its related accounts in a single row. Something like this:

The format is not terribly important (All the Primary/Secondary Account numbers could be concatenated into one field if it makes it easier, though if the first could be Primary that would be preferable). As long as each account shows all its related accounts in one row I would be more than happy! In the data the maximum number of secondary accounts so far is 4, but that is not fixed.

The best I have managed is

SELECT
    testRelate.AccountRef AS ACref,
    testRelate_2.AccountRef
FROM
    testRelate AS testRelate_2
INNER JOIN
    testRelate AS testRelate_1
    ON testRelate_2.[RelationshipRef] = testRelate_1.[RelationshipRef]
RIGHT OUTER JOIN
    testRelate
    ON testRelate_1.[PrimaryRelationshipRef] = testRelate.[PrimaryRelationshipRef];

Which produces the desired matches but not in one line.

If you use sql server 2017 you could use the STRING_AGG function
and your query would look like

SELECT testRelate.AccountRef AS ACref,STRING_AGG(testRelate_2.AccountRef, ', ')
FROM testRelate AS testRelate_2
INNER JOIN  testRelate AS testRelate_1 ON testRelate_2.[Relationship Reference] = testRelate_1.[Relationship Reference]
RIGHT OUTER JOIN testRelate ON testRelate_1.[Primary Relationship Reference] = testRelate.[Primary Relationship Reference]
GROUP BY testRelate.AccountRef

but if not, it would have to be with FOR XML PATH Y STUFF


Reformatting master MySQL server and temporary solution We have MySQL master and replication servers both running on different machines. We have noticed some system files showing integrity issues in the master machine. Temporarily we would like to make the slave as a master and reformat the original master and then bring back everything to the original architecture.

What are the crucial steps now for us to maintain data integrity?

We can down the master and let the inserts and update take place in the replication db, but how do we revert back to the original situation later?

Globally speaking, I would essentially be performing the following steps in the same situation.

Before starting, backup everything on both machines.

Put the website or application into maintenance, preventing users from accessing the database.  Also make sure there are no imports/updates/activity coming in from cron jobs.  You can verify this by running SHOW FULL PROCESSLIST; in MySQL.
Make sure the slave server is fully synced with the master. Read notes below.
Stop replication on the slave server STOP SLAVE; and turn off the master server.
Exchange IP addresses between the old and new master server.  Or point the application's database connection to the slave server.
Bring the application back online.
Perform your maintenance on the old master server.
Once your old master server is back up and running
Put the website or application back into maintenance.
Backup your new master server's database and restore it to the original and now functional master server.
Exchange IP addresses once again between the two machines. Or point the application's database connection to the master server again.
Reconfigure replication between the newly restored master server and the original slave server.  Read notes below.
Bring the application back online.

For step 2, when you are making sure that the master and slave are synced, you would do this. Comparing the current log file and file position for both servers.

On the master server

SHOW MASTER STATUS;

mysql> SHOW MASTER STATUS;
+---------------+----------+--------------+------------------+
| File          | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+---------------+----------+--------------+------------------+
| mysql-bin.003 | 73       | test         |                  |
+---------------+----------+--------------+------------------+

On the slave server

SHOW SLAVE STATUS;

mysql> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
             Slave_IO_State: Waiting for master to send event
                Master_Host: master.server.ip.address
                Master_User: root
                Master_Port: 3306
              Connect_Retry: 3
            Master_Log_File: mysql-bin.003
        Read_Master_Log_Pos: 73

Since you are planning on reverting back to the original infrastructure, there is no need to setup the slave server as a new master.  You can leave the slave server's configuration in place, so that once the master server is back online, you can restart replication more quickly.

Configuring replication

Rolando answered a similar question with an easy to read how to on setting up replication.

Clarification about master slave configuration in mysql


Index for speeding up sorting by boolean, timestamp For a website that displays recent news, I have this query on the home page:

SELECT *
FROM "cms_news"
WHERE
  NOT ("cms_news"."timestamp" >= '2013-08-08 13:32:39.778988+00:00') AND
       "cms_news"."state" = 'published'
ORDER BY "cms_news"."flash" DESC, "cms_news"."timestamp" DESC LIMIT 7;

Here's the table definition:

CREATE TABLE cms_news (
    id integer DEFAULT nextval('cms_news_id_seq'::regclass) NOT NULL,
    created timestamp with time zone NOT NULL,
    modified timestamp with time zone NOT NULL,
    title character varying(510) NOT NULL,
    text text NOT NULL,
    "timestamp" timestamp with time zone NOT NULL,
    state character varying(20) NOT NULL,
    flash boolean NOT NULL,
);

With about 67000 entries in the table, the above query to select the newest 7 published news entries takes about 100ms, which seems a bit much. Here's the output of EXPLAIN:

EXPLAIN SELECT * FROM "cms_news" WHERE (NOT ("cms_news"."timestamp" >= '2013-08-08 13:32:39.778988+00:00' ) AND "cms_news"."state" = 'published' ) ORDER BY "cms_news"."flash" DESC, "cms_news"."timestamp" DESC LIMIT 7;
                                                                QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=13600.34..13600.36 rows=7 width=1274)
   ->  Sort  (cost=13600.34..13768.27 rows=67171 width=1274)
         Sort Key: flash, "timestamp"
         ->  Seq Scan on cms_news  (cost=0.00..12321.62 rows=67171 width=1274)
               Filter: (("timestamp" < '2013-08-08 15:32:39.778988+02'::timestamp with time zone) AND ((state)::text = 'published'::text))

I'm a complete amateur (as you might have guessed already), but it looks like sorting is the real problem. What kind of index would help me here?

Try creating the following index, running ANALYZE cms_news, and posting the EXPLAIN ANALYZE plan again

CREATE INDEX cms_news_flash_ts_idx ON cms_news ( flash desc, timestamp desc );

Tip: You can post your EXPLAIN ANALYZE plans to http://explain.depesz.com/ and post the link back here. I've found it to be a pretty helpful tool over the years.


MySQL exception after upgrading from Java 6 to Java 7 I've been trying, for a long time, to find a solution for the issue.

Recently I've updated my JDK from 6 to 7. Now my grails app doesn't start up, I keep getting the follow exception:

Caused by SQLException: Unable to initialize driver properties due to java.lang.IllegalAccessException: Class org.springsource.loaded.ri.ReflectiveInterceptor can not access a member of class com.mysql.jdbc.ConnectionPropertiesImpl with modifiers "private"
->> 1078 | createSQLException in com.mysql.jdbc.SQLError
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
|    989 | createSQLException in     ''
|    975 | createSQLException in     ''
|    920 | createSQLException in     ''
|   2819 | initializeProperties in com.mysql.jdbc.ConnectionPropertiesImpl
|   3490 | initializeDriverProperties in com.mysql.jdbc.ConnectionImpl
|    823 | <init> .  in     ''
|     47 | <init>    in com.mysql.jdbc.JDBC4Connection
|    411 | handleNewInstance in com.mysql.jdbc.Util

Any tips?

I am using grails 2.1.4

This is a bug in recent versions of the JDK - it fails in jdk1.7.0_25 but I'm using jdk1.7.0_21 and it works fine. Watch this issue for more information


Error while exporting data to flat file from database using sqlplus in UNIX Trying to export data from a table to .dat file in a unix script using the SPOOL command.

However on execution only ~17k data is exported to file out of 91K data with an error in end of the file : ORA-08103 Object do not exist, though the objects exists in the database.

When I tried to export the data in csv, it exported only ~11k data with same error in the file.
Exporting almost ~35 columns from the table.

spool ${DataDirectory}/output.dat;

select * from < Table_name >;

What could be the reason for not exporting complete data set and throwing a error even if object exist?

Attached is the screenshot of the script.

ORA-08103 object no longer exists

means you were querying a segment that has since been (re)moved.  The most common examples of this are:

Someone truncated the table
Someone did 'alter table move'
Someone did 'exchange partition'

all of which move a segments contents to a new place on disk, hence the data you were scanning "no longer exists"


How do I build a PostgreSQL ORDER BY expression based on a concatenation of columns? I'm using Postgres 9.5.  I want to write an order by expression for a series of concatenated strings, but I'm not sure if that's possible or how to do it.  I have cobbled together the below

myproject=> SELECT  "my_objects".*
            FROM "my_objects"
              left join addresses on my_objects.address_id = addresses.id
            WHERE (my_objects.name ILIKE '%my_object%'
              AND EXISTS (SELECT *
                          FROM my_object_times
                          WHERE my_object_times.my_object_id = my_objects.id))
            order by lower(addresses.city) || "," || addresses.state_id;

ERROR:  column "," does not exist
LINE 1: ...id = my_objects.id)) order by lower(addresses.city) || "," || add...

But as you can see there is an error complaining about syntax.  Is what I'm tryihng to do possible and if so how do I write an order by clause based on a concatenation of columns?

There are two things I can say here,

The solution: "," is different from ',' single-quotes is the literal quote mechanism. Double quotes means the column named , which is perfectly valid however horrible it would be in practice CREATE TABLE foo ("," int);

-- Identifier quoting
order by lower(addresses.city) || "," || addresses.state_id;
-- Literal quoting
order by lower(addresses.city) || ',' || addresses.state_id;

The larger problem: why concatenate the strings? It can only ever slow this down or lose accuracy. You can order by more than one column; just do this..

order by lower(addresses.city), addresses.state_id;


mongo 3.6 replication and write concern timed out Not sure how to ask this.
I have a process that is importing thousands of records to the database using a bulkwrite.
I process 1000 documents and then write that and then get the next thousand and write that etc.
This works perfectly in a 1 database scenario and i'm working on setting up replication.
When replicating, I'm getting a write concern error 64 "waiting for replication timed out" frequently.
I do have journaling turned on and have the rs.config set with w:majority.
My understanding is that means to be sure its written to at least the journal before the response is returned.
I'm not sure how best to handle the write concern error though.
Do i just capture it and continue on? or do I need to reprocess some records?
Both primary and secondary have the same document count when executing the count() request on the collection.
Any guidance on what best practices are for this would be helpful.
thanks.

"waiting for replication timed out"  is not a Error , this is a warning msg that your document has not been written to majority of your replica server. but this going to be replicate.

what you can do is along with W:majority configure one more parameter wtimeout:5000 , with this you can overcome of this warning messages.

""Do i just capture it and continue on? or do I need to reprocess some records?""
You can set w:1 for specific process for example a bulk write.


What queries is Oracle executing, how frequently, and time-taken? I have a (Java) web-application, that of course executes many queries (selects, inserts, updates, deletes) to the Oracle Database.

I want to find out the following for last 7 days:

    SQLQuery          Times-Executed   Average-Resp-Time    Average-Rows-Returned
====================================================================================
    SELECT whatever     981            330ms                1201
    UPDATE whatever     45             99ms                 0

Is this something Oracle can give me? If so, how can I get that?
If not Oracle, should I look at JDBC driver?

Note: Using AWR data requires you to have the Diagnostics Pack license option purchased. It may be possible to run the queries below without having the necessary license. Be sure to check with your DBA/licensing manager before using these.

The Automatic Workload Repository (AWR) contains details of the activity on your database. These are stored in various dba_hist* views.

The kind of information you're looking for is primarily available in the dba_hist_sqlstat view. By joining this to dba_hist_snapshot you can get the information available in a given period:

select *
from   dba_hist_sqlstat t, dba_hist_snapshot s
where  t.snap_id = s.snap_id
and    t.dbid = s.dbid
and    t.instance_number = s.instance_number
and    s.begin_interval_time between trunc(sysdate)-1 and trunc(sysdate) -- yesterday's stats

This view holds various *_elapsed columns, which state the increase in the given snap (time period). e.g.:

ELAPSED_TIME_DELTA
CPU_TIME_DELTA
ROWS_PROCESSED_DELTA
BUFFER_GETS_DELTA
DISK_READS_DELTA
PARSE_CALLS_DELTA
EXECUTIONS_DELTA

Times are in microseconds. You can combine these to get average time/execution stats and so on. For example, this gives you the average time/execution for a given sql statement:

select sql_id, sum(t.elapsed_time_delta/1000000)/sum(t.executions_delta)
from   dba_hist_sqlstat t, dba_hist_snapshot s
where  t.snap_id = s.snap_id
and    t.dbid = s.dbid
and    t.instance_number = s.instance_number
and    s.begin_interval_time between trunc(sysdate)-1 and trunc(sysdate) -- yesterday's stats
group  by sql_id

The sql_id is a hash of the text of the SQL itself. You can find the actual statement from:

select * from dba_hist_sqltext where sql_id = :sqlid

Note that only "highest consuming" statements are retained in AWR. So if the query you're interested in isn't the slowest, most executed, most disk access, etc. it may not be in the AWR data. You can get around this by "coloring" sql_ids of interest. This ensures they stay in AWR for as long as your retention period is set. You can do this by running:

exec DBMS_WORKLOAD_REPOSITORY.ADD_COLORED_SQL(:sqlid);

If you just want an overview of your system in a time period (including "heaviest" SQL statements), the AWR report itself gives this. You can find an intro to this on oracle-base.


Is there an easy way to replicate MySQL data? My software is deployed in various cities, each have a database, but they have different authorizations such as:

              central DB
                 /\
                /  \
              DB1  DB2
              /\    /\
             a  b  c  d

Databases a & b need replication to DB1.  Databases DB1 & DB2 need  replication to central DB.

How can I accomplish this?

I have two suggestions
Commercial Product
There is a product called ScaleBase that can handle Geographic replication topologies.
Handcrafted Solution
If you want to set up Geographic Replication using MySQL Replication, you will need to make the MySQL Instance in each Data Center resilient. Here is what thinking:
IDEA #1 : Local Data Redundancy
In each city, setup DRBD with MySQL running on the DRBD Primary
IDEA #2 : Use Distribution Masters
Having a MySQL Replication Master handle the traffic of SQL statement between Data Center could possibly add some intermittent behavior in terms of Replication Lag. If you run another MySQL Instance within the same Data Center whose sole purpose is to ship binary logs to another Data Center, that can be setup in what some call a BLACKHOLE slave. That MySQL Instance contains no actual MySQL. It simply has binary logs and relay logs sued for shipping to multiple slaves. I have discussed using this type of Slave in StackOverflow.
CAVEAT
The two ideas can be combined. The only drawback for using IDEA #2 is doing ALTER TABLE. If any major changes are to be done to table layouts, make sure the Distribution Masters do not change from the BLACKHOLE Storage Engine because this could cascade to the other Data Center and possibly destroy data.
EPILOGUE
I wrote about setting up such topologies before

Aug 20, 2012 : Best solution for cross-datacenter MySQL master-slave replication
Aug 20, 2012 : How to use MySQL Server 5.6 for GeoIP replication?
Jun 25, 2012 : Mysql database replication on different vlan/subnet/another site
Dec 19, 2011 : Best way to setup master to multi master replication
Dec 17, 2011 : Mysql Master-Master Replication Topologies on >2 machines
Aug 18, 2011 : With MySQL Replication, what level of resilience is possible?
Jun 01, 2011 : What can we do in MySQL 5.0 Replication to address bandwidth concerns?
Mar 29, 2011 : MySQL high availability, failover and replication with Latency

Give it a Try !!!


Incorrect datetime value Mysql Good morning.
Mysql 5.7.25:

insert into mytable values('2019-03-31 02:06:29')

return error "#1292 Incorrect datetime value"

but:

insert into mytable values('2019-03-31 03:06:29')

return no errors.
(the type of the field is timestamp)

Where am I wrong ?

Thanks

Chico

If this gives you an error:

insert into mytable values('2019-03-31 02:06:29')

But this doesn't:

insert into mytable values('2019-03-31 03:06:29')

You are most likely using a EU-like timezone, in particular, CET (I strongly suggest not to use tz on databases, and just use UTC, it is just easier). EU (and other countries) changed the clocks from 2 -> 3 am that Sunday for summertime adjustment, so it is not possible, if using such a TZ, to insert a '2019-03-31 02:XX:XX  ' data, as such data is invalid for that timezone.

Do:

SELECT @@time_zone;

To know which timezone you are using. If it says SYSTEM, check it on the os.

Fix: Do not insert it, that clock time didn't exist, either it happened before 2 or after 3. Or fix your timezone of your database or os to match the one you are trying to insert.

Proper fix: Do not handle timezones on the database (never, ever try to implement then on your own), do it only at presentation time, and use UTC for your application backend. Set your servers also to use UTC.


Command line export and import CSV in SQL Server 2008 R2 I have a database xyz and 100+ tables and has to perform the following operations on the command line:

I need to create CSV of all tables in one go.
CSV with column separator ,, fields enclosed by " " and lines terminated by \n.
Restore the all created CSV in one go.

Based on your need, I can suggest couple of options :

Use SSIS Import/Export; or
Use BCP OUT and BCP IN

Each method has its own benefits and it all depends on your requirement which one to choose.


Application unable to connect after doing a failover ,Mirroring After doing a failover to mirror database, the application is throwing below error
Login failed for user 'NT Authority\anonymous logon'. reason: could not find a login matching the name provided

In error log it is logging
Login failed for user 'xxx'.Reason: failed to open the explicitly specified database ' xxx'. Error 18456,severity:14,state:38

I know this question is duplicated and i found many threads, but i don't find the solution in them or may be i'm unable to understand that.

Please help me with the steps to resolve.

How is your application connecting to the SQL Server?

If it is not a direct connection and involve two hops(for example lets say you have an web tier that goes through an App tier and then to SQL Server) it might be related to kerberos authentication.

You can check if if the connection that were coming into the principle server for the application were NTLM or Kerberos.  Please check the below link

Using Kerberos Authentication with SQL Server

If it is, that means that your principle server was configured correctly to facilitate kerberos authentication and you would have to do the same thing for secondary server.

Register a Service Principal Name for Kerberos Connections

Another not so ideal situation could be that someone has added  NT Authority\anonymous as login on the primary server, you could technically make it work by adding the same thing to the secondary server, but it is not recommended.


Where does a type specific field go? Let's say I have table of cars and table of car manufacturers. Each car references one manufacturer.

Furthermore, each car that has manufacturer x requires an extra field. Say, cars from that manufacturer have an extra feature that needs configuring that no other manufacturer provides.

Where does this field go?

I think there should be another table with a one-one mapping between it and the cars table which holds the extra field for cars of that manufacturer.

So given this, how do I make sure that only cars of the correct type have this extra data in the other table? Or is this really bad data design?

The features data should really exist in a separate table.  Normalize your data for maintainability and scalability.  For instance, you could implement something like this:

create table Manf
(
    pkManf int identity(1, 1) not null primary key clustered,
    Name varchar(256) not null
)

create table Car
(
    pkCar int identity(1, 1) not null primary key clustered,
    fkManf int not null foreign key references Manf(pkManf),
    Name varchar(256) not null
)

create table Features
(
    pkFeatures int identity(1, 1) not null primary key clustered,
    fkManf int not null foreign key references Manf(pkManf),
    Name varchar(256) not null
)

Just because you currently only have one manufacturer with a feature that needs to be noted in data, that doesn't mean that's always going to be the case.  It is a very easy SELECT to retrieve all of the cars with a particular feature.

EDIT: To get all Cars with a particular feature, you'd do something like this:

select c.*
from Car c
inner join Manf m
on c.fkManf = m.pkManf
inner join Features f
on f.fkManf = m.pkManf
where f.Name = 'some feature'


Two (almost) identical queries, but only one of them uses the index I have a table with two columns, batch_id and study_id. There are indexes on both columns. I want to get a random row back from MySQL.

I originally wrote:

SELECT * FROM alt_join_batch_study ORDER BY RAND() LIMIT 1

but that proved slow. Here's the explain.

id    select_type    table    type    possible_keys    key    key_len    ref    rows    Extra
1    SIMPLE    alt_join_batch_study    ALL    NULL    NULL    NULL    NULL    172235    Using temporary; Using filesort

I was surprised it wasn't using any indexes. I tried this:

SELECT batch_id, study_id FROM alt_join_batch_study ORDER BY RAND() LIMIT 1

and Lo! it was faster. And the explain shows the use of the index.

id    select_type    table    type    possible_keys    key    key_len    ref    rows    Extra
1    SIMPLE    alt_join_batch_study    index    NULL    study_id    4    NULL    172235    Using index; Using temporary; Using filesort

Why wouldn't MySQL find the index when I do SELECT * ...?

If the table is InnoDB, here is what's happening:

The query optimizer sees SELECT * and does this

Sees all columns are included in the SELECT list
Uses the clustered index since all columns are included

The query optimizer sees SELECT batch_id, study_id and does this

Sees SELECT list has two columns, not all columns
Sees the study_id index (and other indexes)
Chooses the study_id index
Retrieves batch_id using PRIMARY KEY attached to study_id index entry

Why would this happen at all? The Query Optimizer is kinda picky

Aug 13, 2013 : Why does MySQL ignore the index even on force for this order by?
Nov 13, 2012 : Must an index cover all selected columns for it to be used for ORDER BY?

Sometimes, it can go the other way

Apr 23, 2012 : Why is a secondary index chosen over a clustered index for SELECT COUNT(*) ...?

The key to your question is that the Query Optimizer does not view the two queries as almost identical. To the query optimizer, they are completely different in appearance and in its treatment.


Calling a stored procedure in a stored procedure Environment sql server 2005
My question is

Calling a stored proc within a stored proc within another stored proc.
When is this type of development detrimental?

In order to promote code reusablity we have encapsulated various insert/update statements into stored procedures. So if want to insert A Foo record then you would pass the parameters to the CreateFoo stored proc and go about your day.

Well lets assume a Foo object needs to Create A Bar objects which also Creates a Mark object.

Thus your stored proc will call the foo stored proc which will then call the bar stored proc which will then call the mark stored proc.

When is this not a good idea, and what are my other options. Thank you very much.
If you could, could you put the sources that back up your answer. Again thank you very much.

This can be good thing.

If you have to create bar and mark objects for other reasons or in other place then having a reusable stored proc eases maintenance and allows all checks in one place.

If I have to create n bar objects I'd probably loop n times rather than doing it set based if it wasn't critical for performance. Maintainability wins out for me. Of course, maybe my stored proc should deal with 1 to n bar creations... but then again did I know this up front? :-)

My answer here shows you how to nest stored procs with error handling: What is the best method to add error handling in SQL 2005 stored procs?


subquery must return onl one column in postgresql I want to get the XML response after the stored procedure call in PostgreSQL. In the below query I was trying to get the output, but it throws an Error: subquery must return only one column. Can someone help me with this?
SELECT (
        SELECT xmlforest('2021_PPP_Parasiticide_Program' AS Name, CASE
                    WHEN "temptable"."C_2021_PPP_PARA" IS NOT NULL
                        THEN 'True'
                    ELSE 'False'
                    END AS requirementsMe, coalesce("temptable"."YTD_2021_Qualifying_Carton_Purchased", 0) AS qualifyingPurchaseAmount, 'Dollar' AS qualifyingPurchaseAmountType, CASE
                    WHEN "temptable"."C_2021_PPP_PARA" IS NOT NULL
                        THEN 'Active'
                    ELSE 'In-Active'
                    END AS componentStatus)
            ,(
                SELECT (
                        SELECT xmlforest('q1_parasiticide_carton_rebate' AS rewardName, CASE
                                    WHEN q1_parasiticide_carton_rebate IS NULL
                                        THEN CAST(0 AS VARCHAR)
                                    ELSE cast(coalesce(q1_parasiticide_carton_rebate, 0) AS VARCHAR(10))
                                    END AS rewardAmount)
                        FROM temptable
                        )
                    ,(
                        SELECT xmlforest('q2_parasiticide_carton_rebate' AS rewardName, CASE
                                    WHEN q2_parasiticide_carton_rebate IS NULL
                                        THEN CAST(0 AS VARCHAR)
                                    ELSE cast(coalesce(q2_parasiticide_carton_rebate, 0) AS VARCHAR(10))
                                    END AS rewardAmount)
                        FROM temptable
                        WHERE temptable.rus_id = temptable.rus_id
                        )
                FROM temptable
                )
        FROM temptable
        );

I want response in the below format -
<CustomerProgramStatus_response>
   <programComponents>
      <name>2020_PPP_Parasiticide_Program</name>
      <requirementsMet>FALSE</requirementsMet>
      <qualifyingPurchaseAmount>0</qualifyingPurchaseAmount>
      <qualifyingPurchaseAmountType>Dollar</qualifyingPurchaseAmountType>
      <componentStatus>In-Active</componentStatus>
      <componentRewards>
         <rewardName>Q1_Parasiticide_Carton_Rebate</rewardName>
         <rewardAmount>0</rewardAmount>
      </componentRewards>
      <componentRewards>
         <rewardName>Q2_Parasiticide_Carton_Rebate</rewardName>
         <rewardAmount>0</rewardAmount>
      </componentRewards>
   </programComponents>
</CustomerProgramStatus_response>

If temptable has one record, then you probably dont need subqueries at all. It it has more the one, then you need aggregates. Probably the best is not to provide more info, but study some examples like here: https://www.w3resource.com/PostgreSQL/postgresql-xml-functions.php and build your xml from inside moving outward step by step like
1:
SELECT xmlforest('q1_parasiticide_carton_rebate' AS "rewardName",
    CASE WHEN q1_parasiticide_carton_rebate IS NULL
    THEN CAST(0 AS VARCHAR)
    ELSE cast(coalesce(q1_parasiticide_carton_rebate, 0) AS VARCHAR(10))
    END AS rewardAmount)
FROM temptable;

2:
SELECT xmlforest(xmlforest('q1_parasiticide_carton_rebate' AS "rewardName",
    CASE WHEN q1_parasiticide_carton_rebate IS NULL
    THEN CAST(0 AS VARCHAR)
    ELSE cast(coalesce(q1_parasiticide_carton_rebate, 0) AS VARCHAR(10))
    END AS rewardAmount) as "componentRewards",
xmlforest('q2_parasiticide_carton_rebate' AS rewardName,
    CASE WHEN q2_parasiticide_carton_rebate IS NULL
    THEN CAST(0 AS VARCHAR)
    ELSE cast(coalesce(q2_parasiticide_carton_rebate, 0) AS VARCHAR(10))
    END AS rewardAmount) as "componentRewards")
FROM temptable;