SQL Server error importing an Excel sheet I am facing a problem while loading & importing an Excel sheet into a SQL Server database.

The error is

  The OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server
  "(null)" reported an error. The provider did not give any information
  about the error.

  Msg 7303, Level 16, State 1, Server PREM4, Line 3
  Cannot initialize the data source object of OLE DB provider
  "Microsoft.ACE.OLE .12.0" for linked server "(null)"

I could not find out what the issue is.

After some careful thought and some experimentation, the AccessDatabaseEngine.exe installed on the server is probably part of the problem. There are actually two versions 32 bit and 64 bit. On my computer 64 bit, I have the 32 bit version of Office installed. The SQL Server on my computer is 64 bit. When I attempt to use openrowset with the same OLEDB provider on a spreadsheet, I get the same error as you. I can't install the 64bit version, without removing the 32 bit version of Office.

Here are some links to two different versions. The second one has both the 32bit and 64bit.

Access Database Engine 2007 32 bit

Access Database Engine 2010 32 bit and 64 bit

The lesson here is; be sure to install the version 32 bit or 64 bit that matches your SQL Server install. If you have a 64 bit server and 32 bit MS Office components installed, you won't be able to install the 64 bit version until you remove 32 bit MS Office.

Once you have the Access Database Engine that matches your SQL Server (64 bit or 32 bit), you can test the provider directly using a query to see if you can import from the spreadsheet.

OLE 12.0

SELECT *
FROM OPENROWSET
('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;Database=D:\somefile.xls;
HDR=YES', 'select * from [name_of_spreadsheet_in_file$]');

OLE 4.0

SELECT *
FROM OPENROWSET
('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=D:\somefile.xls;HDR=YES',
'select * from [name_of_spreadsheet_in_file$]');


Do roles have an owner in oracle? Suppose that I have logged in to oracle database using user X, then user X creates some roles using create role command. I want to know that is user X the owner of the roles? Can I select all roles created by user X?

The roles in Oracle Database have no owner as well as directories have no owner.

When the user with CREATE ROLE or SYSDBAprivilege creates the role, he or she is automatically assigned that role WITH ADMIN OPTION which means that the user can subsequently grant that role to other users even if the privilege CREATE ROLE is revoked from the user.

You can see what roles are assigned to which users querying DBA_ROLE_PRIVS view, or USER_ROLE_PRIVS if you want to know what roles are assigned to the current user.

Also, a user cannot create a role  if the role with the same name already exists. However you can audit role creation and granting. You can do that with AUDIT ROLE statement (if it was for some reason disabled with NOAUDIT ROLE), and then querying DBA_AUDIT_TRAIL view.


Is high index fragmentation an issue on performance Below is a small sample (top 20ish) from over 8000 indexes in avg_fragmentation_in_percent descending order. Will this cause any performance issues?

Will this cause any performance issues?

I would start by quoting from Old Books Online for SQL Server 2000 version. Microsoft Books Online (The SQL Server 2000 BOL is now removed by MS so the link is not present. Although its for SQL Server 2000 but gives very accurate definition )

  Fragmentation affects disk I/O. Therefore, focus on the larger indexes
  because their pages are less likely to be cached by SQL Server. Use
  the page count reported by DBCC SHOWCONTIG to get an idea of the size
  of the indexes (each page is 8 KB in size). Generally, you should not
  be concerned with fragmentation levels of indexes with less than 1,000
  pages. In the tests, indexes containing more than 10,000 pages
  realized performance gains, with the biggest gains on indexes with
  significantly more pages (greater than 50,000 pages).

Plus and fact being that since index is small pages are most likely to be in memory so no physical I/O would be required to get page and hence  fragmentation would not come in picture.

So the small indexes which have page_count <1500 or for that sake < 3000 is hardly going to cause any performance issue, it is quite likely that pages would be in memory and physical I/O would not be required hence fragmentation would actually not matter. If I look at your output only two indexes with page_count 66K+ should really cause some performance issue if SQL Server has to scan index pages. People can argue that page_count of 5000 could also be deciding factor and to which I would say "not really", considering the definition from old books online quoted above. You must also note that the fragmentation we are talking is logical fragmentation where leaf level page order does not matches that of clustered index key.

So performance issue, if any, could only come from large fragmented index not the smaller ones.


How do i disable or limit Postgres Replication creating causing too many WAL files generated? On AWS RDS PostgreSQL 10.7,
I turned on logical replication for a one specific table
.

on publisher:

CREATE PUBLICATION mypublication FOR TABLE <mytable>;

on subscriber:

CREATE SUBSCRIPTION mysub
         CONNECTION 'host=... port=... user=foo dbname=foodb'
        PUBLICATION mypublication;

However, this led to some interesting errors where the WAL files generated ate up > 100 times the size of the table I replicated and I had to shut it down.
I started following the AWS guiding for troubleshooting this: https://aws.amazon.com/premiumsupport/knowledge-center/diskfull-error-rds-postgresql/

on AWS RDS, this was indicated by TransactionLogsDiskUsage metric.

It seems almost like it's trying to generate WAL files for any changes to the DB instead of just that table.
This was indicated by OldestReplicationSlotLag increasing when there are write to any tables instead of the specified table.

I tried looking at postgres error logs on RDS but still can't really understand the cause. Is there way to limit the WAL files created by logical replication?

The database generates WAL for the entire database instance.  It is the logical sender's job to sort them out to restrict and construct what gets sent.  I don't think there is a way to get the logical sender to work ahead.  You should look at why it is falling behind, not why there is a lot of WAL.


Memory issue in Transactional Replication I want to replicate a data of 500 GB through transactional replication once in a day, so my query is this, will it create a new file in the snapshot folder on daily basis of 200-300 GB or will update it only.

What can I do if i don't have too much memory for this file, can we not transfer our data without generating this file.

The SQL Server Snapshot Agent will use BCP to create an initial snapshot of the data and objects to be replicated.

These are stored on disk in the default installation folder. You can specify a different location if you are concerned about space issues in the default location.

Once the snapshot has been distributed to the subscribers, subsequent changes will be stored in the distribution database and sent to the subscriber at the frequency you choose.

If you don't need to create and send a snapshot every day you might save time and bandwidth only sending the differences for a while before refreshing the snapshot.


How to restore a postgres database created via an older version of postgres? I have a file db.backup, that I would like to restore to PostgreSQL version 11.

When I tried to install via Azure Data Studio I got an error:

  Restore: pg_restore: [archiver] unsupported version (1.13) in file header

Then I found that running pg_restore I can actually read the file:

pg_restore /..db.backup -U postgres > db-restore.sql

And I can see that the file is human-readable. However I would much rather just restore the database from the backup.

How can I do this? I tried installing an older version of PostgreSQL server, but I don't know how to install an older version of PSQL (yet). Would this help?

Is there some flag I can add to the restore command so that the restore works?

-- EDIT

The output of pg_restore --version, i.e. the version of pg_restore that gave me the error message mentioned above:

  pg_restore (PostgreSQL) 11.4 (Ubuntu 11.4-1.pgdg18.04+1)

When I tried to install via Azure Data Studio I got an error:

Restore: pg_restore: [archiver] unsupported version (1.13) in file header

Version 1.13 of the pg_dump archive format was introduced in February 2018 as part of the changes for CVE-2018-1058. The version bump was mentioned in the commit message with a specific warning about the problem:

  Since this change requires a change in the behavior of pg_restore when
  processing an archive file made according to this new convention, bump
  the archive file version number; old versions of pg_restore will
  therefore refuse to process files made with new versions of pg_dump.

That was six months before PostgreSQL 11 was released, so an installation that supports PostgreSQL 11 is not supposed to use an older pg_restore. I'm not sure what exactly is "Azure Data Studio" in terms of the software stack it provides, but it looks like it has an outdated version of pg_restore that is not suitable for your needs.


Group by alternative I have a table , that has a generic set of actions for EVERY user.

However if there is a user that needs to have a unique action, that replaces an existing action,  then in my query, I want to exclude the generic action.

So for example every logged in user gets two actions OPEN or CLOSE based on the table below.

SELECT ActionID, ActionCode FROM Actions

ActionID,  ActionCode,      LoginID.
OPEN       Open contract    NULL – Generic Open Operation
CLOSE      Close Contrct    NULL  -- Generic Close Operation

A new user has a very specific OPEN case so if I add a row to the Action table

ActionID, ActionCode,    LoginID.
OPEN      Open contract   NULL  – Generic Open Operation
CLOSE     Close Contrct   NULL  - Generic Close Operation
OPEN1     Open1 contract  123   – Specific Open Operation only to user logged in as 123

So then I perform the following query

SELECT ActionID, ActionCode FROM Actions WHERE (LoginID = 123 OR LoginID is null)

I get

OPEN    Open contract   NULL
CLOSE   Close Contrct   NULL
OPEN1   Open1 contract  123

But I want

CLOSE   Close Contrct   NULL
OPEN1   Open1 contract  123

If I understand you correctly, try this:

with ActionX as (
   select
        ActionID, coalesce(LoginID,'_')) as LoginID, ActionCode
    from ActionTable
)
select
    a.ActionID,
    a.LoginID
    b.ActionCode
from (
   select
        ActionID, max(LoginID) as LoginID
    from ActionX t
    group by ActionID
) a
join ActionX  b
   on b.ActionID  = a.ActionID
  and b.LoginID   = a.LoginID


How to update a table with a stored procedure with XML parameter in SQL 2008? I have the following Stored Procedure query. This query is incomplete. I need help completing this query.

Problem:
In this SP, I am receiving a well-formed XML string into the SP. My goal is to update those rows in the Notes table that have the matching NoteId for the Notes passed in XML.

For example: If the XML has two notes with NoteId = 1, 2 and the table has three notes with NoteId = 1, 2, 3, I would like to update the NoteText of the table for rows with NoteId = 1, 2. Please help me complete my stored procedure.

Input XML:

<Notes>
  <Note>
    <NoteId>1</NoteId>
    <NoteText>Hello</NoteText>
  </Note>
  <Note>
    <NoteId>2</NoteId>
    <NoteText>World</NoteText>
  </Note>
</Notes>

Table before update:

NoteId | NoteText | IsDeleted
1        Hell       0
2        Worl       0
3        Test       0

Table after update needs to look like:

NoteId | NoteText | IsDeleted
1        Hello      0
2        World      0
3        Test       0

This is the Stored Procedure that I currently have:

ALTER PROCEDURE USP_Notes_UpsertNotes
    (@notesToUpsert XML)
AS
BEGIN

    SET NOCOUNT ON;

        UPDATE Notes
        SET NoteText = n.note_text
        FROM (  select  T.C.value('(NoteId/text())[1]','varchar(500)') as note_id,
                        T.C.value('(NoteText/text())[1]','varchar(500)') as note_text **PROBLEM**
                from @notesToUpsert.nodes('/Notes/Note') as T(C)
             )   as n

        WHERE n.note_id = Notes.NoteId AND Notes.IsDeleted = 0

END
GO

Error screenshot:

The .nodes() method must start at the root of your xml.  It will then return one "row" per matching node for the SELECT clause to work on. It should look like this:

from @notesToUpsert.nodes('/Notes/Note') as note(col)

(BTW using "note" as an alias is confusing.  Perhaps T(c) would be easier to debug?)

Then the XQuery in the SELECT clause operates in the xml context of each "row" i.e. a <Note> element.  The query path will be:

 col.value('(./Text)[1]','varchar(500)')

,col.value('(/Notes/Note/Text)[1]','varchar(500)')

A handy debugging aid is to just dump the xml for each "row":

select
    col.query('.') as Debug
...

Edit: re-tested in light of Mikael's comments.  I couldn't reproduce my earlier output and have removed the offending line.


Simulate effective LEAD(1) or LEAD(2) in MySQL Assume i have a table with the dutch or english alfabet.

Alfabet

digit
--------
a
b
c
d
e
f
g
h
i
j
k
l
m
n
o
p
q
r
s
t
x
y
z

Now i want to display a list like this.

digit   next_digit  next_two_digit
------  ----------  ----------------
a       b           c
b       c           d
c       d           e
d       e           f
e       f           g
f       g           h
g       h           i
h       i           j
i       j           k
j       k           l
k       l           m
l       m           n
m       n           o
n       o           p
o       p           q
p       q           r
q       r           s
r       s           t
s       t           x
t       x           y
x       y           z
y       z           (NULL)
z       (NULL)      (NULL)

In Oracle (and more databases that support window functions) you can get this list with

SELECT
   "digit"
 , LEAD("digit", 1) OVER (ORDER BY "digit" ASC) as next_digit
 , LEAD("digit", 2) OVER (ORDER BY "digit" ASC) as next_two_digits
FROM
 alfabet
ORDER BY
 "digit" asc

demo http://www.sqlfiddle.com/#!4/a11f1/4

Now i want to simulate the window function lead(1) and lead(2) in MySQL.

Now in MySQL i know i can get the same with co-related subqueries like so.

SELECT
 *
 , (SELECT
     *
    FROM
     alfabet alfabetInner
    WHERE
      alfabetInner.digit > alfabet.digit
    ORDER BY
      digit ASC
    LIMIT 0, 1
   ) AS next_digit

 , (SELECT
     *
    FROM
     alfabet alfabetInner
    WHERE
      alfabetInner.digit > alfabet.digit
    ORDER BY
      digit ASC
    LIMIT 1, 1
   ) AS next_two_digit
FROM
 alfabet
ORDER BY
 alfabet.digit ASC

Editted because off comment from Evan Carroll

  why doesn't the table Alfabet just store the id/row number on it?

Because i wanted to simulate a pure LEAD(1) or LEAD(2) solution without having to need a auto increment sequence without gaps as PRIMARY ID... the window function off Oracle doesnt do annything with a auto increment PRIMARY key also.

demo http://www.sqlfiddle.com/#!9/6a8701/2

  Now the question. Do you know even more (effective) LEAD simulation
  methodes?

Use MySQL 8, which supports Window Functions
Hack around it..

Generate a sequence of IDS. This whole project makes no sense without them anyway because the result set of a SQL query isn't guaranteed without an ORDER BY.
LEFT-OUTER JOIN the table with itself self join on id+1 = id

MySQL specific, emulate lag with user-defined variables

That looks something like this in MySQL,

SELECT t1.digit, t2.digit
FROM (
    SELECT digit, @rownum1 := @rownum1 + 1 AS id
    FROM
     alfabet  , (SELECT @rownum1 := 0) r
    ORDER BY
     alfabet.digit ASC
) AS t1
LEFT OUTER JOIN (
    SELECT digit, @rownum2 := @rownum2 + 1 AS id
    FROM
     alfabet  , (SELECT @rownum2 := 0) r
    ORDER BY
     alfabet.digit ASC
) AS t2
  ON t1.id +1 = t2.id;

You can even make that simpler with

SELECT digit, previous
FROM (
    SELECT digit, @prev previous, @prev := digit AS prev
    FROM
     alfabet  , (SELECT @prev := '') r
    ORDER BY
     alfabet.digit ASC
) AS t1;


SQL Server 2016 AlwaysOn VDI_CLIENT_WORKER command I've setup an AlwaysOn AG on SQL Server 2016 SP1 Standard. Then I created an AG and added a database with autoseeding (synchronous mode). I used SSMS 2017 to create my AG and to add the database. Everything works fine.

But when I check the wait stats I get waits of type VDI_CLIENT_OTHER (80%) on the primary with an average resource time of 42 seconds. After some research I found out that the waits are generated by 4 sessions that execute the command VDI_CLIENT_WORKER. As I understand the wait means that a thread is waiting for work when seeding a new AG. But what I do not understand is why I have those waits because my AG is ready and why do I have 4 sessions that execute the VDI_CLIENT_WORKER command?
I found out that each scheduler has one VDI_CLIENT_WORKER

Can somebody try to explain what VDI_CLIENT_WORKER-command does and how can I solve the problem of the many VDI_CLIENT_OTHER waits?

After some more research I discovered that when I create an AG with autoseeding SQL Server creates a new worker (that executes the VDI_CLIENT_WORKER command) for each scheduler.
To remove those workers I need remove every 'autoseeded' AG and restart the SQL Server service.

If I then create the AG with full backup/restore none of these workers are created.


Why can I drop table when logged in as not owner? I read that only owner can drop table and no other user may be granted to do so.

I created a table with owner postgres and then drop it as another user. What privileges should I revoke to disallow it?

The user has all the privileges granted by default in db properties but I don't see any "create table" or "drop table" privilege.

Superusers are always exempt from lowly permission checks. The documentation:

superuser status
A database superuser bypasses all permission checks, except the right
to log in. This is a dangerous privilege and should not be used carelessly; ...

To see whether your current user is a superuser:
SHOW IS_SUPERUSER;

To list all superuser roles:
SELECT rolname FROM pg_roles WHERE rolsuper;

And the schema owner can do it, too. The documentation for DROP TABLE:

Only the table owner, the schema owner, and superuser can drop a table.

To list schema-owners:
SELECT n.nspname AS schema_name, pg_get_userbyid(n.nspowner) AS owner
FROM   pg_namespace n
WHERE  n.nspname NOT LIKE 'pg_%'
AND    n.nspname <> 'information_schema'
ORDER  BY 1;

About the same as \dn in psql.
And who are you?
SELECT current_user, session_user;


MySQL Indexing VarChar I am trying to index my blogentries database for better performance but found an issue.

Here is the structure:

CREATE TABLE IF NOT EXISTS `blogentries` (
  `id_id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `title_id` varchar(100) COLLATE latin1_german2_ci NOT NULL,
  `entry_id` varchar(5000) COLLATE latin1_german2_ci NOT NULL,
  `date_id` int(11) NOT NULL,
  PRIMARY KEY (`id_id`)
)
ENGINE=MyISAM
DEFAULT CHARSET=latin1
COLLATE=latin1_german2_ci
AUTO_INCREMENT=271;

A query like the following uses the index properly:

EXPLAIN SELECT id_id,title_id FROM blogentries ORDER by id_id DESC

+----+-------------+-------------+-------+---------------+---------+---------+------+------+-------------+
| id | select_type |    table    | type  | possible_keys |   key   | key_len | ref  | rows |  Extra      |
+----+-------------+-------------+-------+---------------+---------+---------+------+------+-------------+
|  1 | SIMPLE      | blogentries | index | NULL          | PRIMARY |     114 | NULL |  126 | Using index |
+----+-------------+-------------+-------+---------------+---------+---------+------+------+-------------+

However, when I add the entry_id into the SELECT query it uses the filesort

EXPLAIN SELECT id_id,title_id,entry_id FROM blogentries ORDER by id_id DESC

+----+-------------+-------------+------+---------------+------+---------+------+------+----------------+
| id | select_type |    table    | type | possible_keys | key  | key_len | ref  | rows |   Extra        |
+----+-------------+-------------+------+---------------+------+---------+------+------+----------------+
|  1 | SIMPLE      | blogentries | ALL  | NULL          | NULL | NULL    | NULL |  126 | Using filesort |
+----+-------------+-------------+------+---------------+------+---------+------+------+----------------+

I was wondering why this is happening and how I can avoid it? Is it due to the VarChar, and that should be changed to something else?

I am trying to have all my queries use the index as I'm running into high Handler_read_rnd and Handler_read_rnd_next values.

If you need any other info I can post it too.

Since you don't have a WHERE clause in either query, you're returning all rows in both cases, so I'd think the use or non-use of the index would have very little impact on performance in these examples.


Migrate from SQL Server 2014 Failover Cluster to 2019 Availability Group, rollback plan we have a 2 node SQL Server 2014 failover cluster with 2 SQL Server instances and about 100 databases to be migrated to an availability Group 2019. What is the faster way to do that considering that we have one weekend for any instance? What's the best rollback strategy considering that restore from a recent version to a older one is not possible?
Any idea?
thanks

2 Massive questions
You haven't said anything about what is connecting to your databases.
For swapping over the databases log shipping will give you a much shorter cutover time than backup & restore.
If you can add a AOAG Listener with the name of the old server you will simplify updating all of the client connection strings (means the old server has to be powered down or renamed).
For rollback one generally has as thorough acceptance testing as possible - if that fails just power down the new server and power up the old one. Once clients are connecting and adding new data you have gone past the point of no return & know you have to fix any problems that occur on the new server.


Multiple SELECT With Multiple WHERE I am stumped and need your help.

I am trying to select three columns from my table where one of them is a VARCHAR and the other two are either "Y" or "N".

SELECT  CurrencyPair, Count(Accepted) As Accepted, Count(Successful) As Successful
    FROM  Trades
    WHERE  Accepted = "Y"
    GROUP BY  CurrencyPair
    ORDER BY  Successful/Accepted DESC, Successful DESC
    LIMIT  5

This returns a table just as I like in regards to format, but the data seems to only show where the three columns coincide. For example id three were accepted and two were successful I would expect the count for accepted to be 3 and the count for successful to be 2. My table always returns the same number of accepts and successes despite knowing the data in the table having more accepted than successes.

Furthermore, please have a look at the division operator to make sure it is correct syntax. It's purpose is to place higher success ratios at top.

Thanks in advance.

Try to use case inside count:

Count(Case WHEN Successful = 'Y' THEN 1 ELSE 0 END)

Regarding division operator, looks ok, if you want to order by count.


Count results of table by date I am trying to query the number of transactions that occur on a given day with the following query
select COUNT(seq_no) from "table"
where start_time >= '2020-04-01 00:00:00'
and start_time < '2020-04-01 23:59:59'

So I would like a count of the total sequence numbers for April 1st to display. But the results return

What am I doing wrong with this query? SQL Server2000, and I am very new and green to SQL. Thanks for any input.

Are there any transactions on that particular day?
Check the data "by eye" and see if it's the query that's "missing" them or if they really aren't there at all!
Your query looks OK to me.
Your table I'm less convinced about.
Having a table called "table" is a Bad Idea.
Using any reserved word as an identifier (table, column, anything else) is likely to come back and bite you at some point.  Save yourself the trouble; you should never need to "protect" the identifiers in your SQL.
What Data Type are you using for the start_time column?
I'm hoping it's a Date-related one but, given the problem you're having, I'm guessing it's not and that the values stored in it are in a different "format".
For example:
select top 5 *
from table1 ;

+--------+-----------------------+
| seq_no | start_time_as_varchar |
+--------+-----------------------+
|    111 | 25-Apr-2021 00:00:01  |
|    222 | 26-Apr-2021 00:00:01  |
|    333 | 27-Apr-2021 00:00:01  |
|    444 | 28/04/2021 00:00:01   |
|    555 | 2021-04-29 00:00:01   |
+--------+-----------------------+

The values are perfectly valid Character Representations of date values, but they're not held in [a column with] a Date Data Type, so SQL Server will do a character-by-character comparison, not a date-wise comparison.
Always store Date Values in Date[-related] Data Types.


counter table in mysql i was going through the book: High performance MySql 2nd edition. In chapter 4 it says:

  An application that keeps counts in a table can run into concurrency
  problems when updating the counters. Such tables are very common in
  web applications.

  To keep things as simple as possible, suppose you have a counter table
  with a single row that just counts hits on your website:

mysql> CREATE TABLE hit_counter ( cnt int unsigned not null ) ENGINE=InnoDB;

  Each hit on the website updates the counter:

mysql> UPDATE hit_counter SET cnt = cnt + 1;

  The problem is that this single row is effectively a global “mutex”
  for any transaction that updates the counter. It will serialize those
  transactions. You can get higher concurrency by keeping more than one
  row and updating a random row.

  Below is the change:

mysql> CREATE TABLE (slot tinyint unsigned not null primary key, cnt int unsigned not null ) ENGINE=InnoDB;

  Prepopulate the table by adding 100 rows to it. Now the query can just
  choose a random slot and update it:

  mysql> UPDATE hit_counter SET cnt = cnt + 1 WHERE slot = RAND() *
  100;

  To retrieve statistics, just use aggregate queries:

mysql> SELECT SUM(cnt) FROM hit_counter;

Here comes my doubt:

As there are two different queries one which increment the cnt and other which calculate the sum of cnt than how come it will give correct result to two different threads executing simultaneously? For example t1 fires the first query to increase the cnt  , before it fetches the sum t2 also updates the cnt than the 2nd query for t1  will fetch wrong result. Please clarify.

if you dont have the book please refer this.

It's the transaction isolation that guarantees consistent results.

In your example, if you are concerned about possible changes from other threads between the first and the second query, you should enclose your queries in a single transaction: this is implicit if you have autocommit disabled, otherwise can be explicitly declared:

START TRANSACTION;
UPDATE hit_counter SET cnt = cnt + 1 WHERE slot = RAND() * 100;
SELECT SUM(cnt) FROM hit_counter;
COMMIT;

But it must also be said that, in many real use cases (like a counter for visits on a web page), having a result which is consistent with the previous increment is not so important.

For a deep explanation about possible transaction isolation levels please refer to: http://dev.mysql.com/doc/refman/5.7/en/set-transaction.html


Can I Improve Performance on Bloated System Tables? Background:
I have numerous databases with a large number of VIEW's, and an extremely large number of SYNONYM's.  For example, one db has more than 10k VIEW's and 2+ million SYNONYM's.

General Problem:
Queries involving sys.objects (and system tables in general) tend to be slow.  Queries involving sys.synonyms are glacial.  I am wondering what I can do to improve performance.

Specific Example
This command is run by a third party tool.  It is slow in both the app, and in SSMS:

exec sp_tables_rowset;2 NULL,NULL

My Question:
How can I make this run faster?

What I've Tried:
If I SET STATISTICS IO ON I get this output:

  (2201538 row(s) affected)Table 'sysobjrdb'. Scan count 1, logical
  reads 28, physical reads 0, read-ahead reads 0, lob logical reads 0,
  lob physical reads 0, lob read-ahead reads 0. Table 'sysschobjs'. Scan
  count 1, logical reads 53926, physical reads 0, read-ahead reads 0,
  lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

I have been able to update statistics on the underlying system tables.  This has worked in my SQL 2008 R2 or newer environments:

UPDATE STATISTICS sys.sysobjrdb WITH FULLSCAN
UPDATE STATISTICS sys.sysschobjs WITH FULLSCAN

I have also been able to perform index maintenance.  This works in my SQL 2012 or newer environments.  For instance running sp_help 'sys.sysschobjs' identifies the indexes on the table, and from there I create and run these commands:

ALTER INDEX clst ON sys.sysschobjs REORGANIZE
ALTER INDEX nc1 ON sys.sysschobjs REORGANIZE
ALTER INDEX nc2 ON sys.sysschobjs REORGANIZE
ALTER INDEX nc3 ON sys.sysschobjs REORGANIZE

Updating stats and reorganizing indexes helps, but not by much.

If you have not already done so, you could gain performance by moving the primary data file to a separate set of spindles from the rest of the data (see Files and Filegroups Architecture and SQL Server: filegroup for system tables only?).


How can I give a group of developers the ability to view the query plan, without the ability to run a trace or to view the server state? I have a group of 5 developers that need to be able to view the plans of queries they have written for a database, and that database only.  I refuse to make them a member of sysadmin.  I do not want them to be able to run a trace, ever, on this server, nor do I want them to be able to run dmv's.

These 5 developers are members of a windows group.  I have made them a member of db_owner.

This is what I have found:

A server principal with DENY for server permission ALTER TRACE will encounter Error 262 when attempting to view SHOWPLAN output
This happens even if the database principal has a GRANT for SHOWPLAN database permission
This happens because SHOWPLAN permission is implied by ALTER TRACE permission
This does not happen if the database principal is the database owner
A server principal with GRANT for server permission ALTER TRACE will be able to get SHOWPLAN output without needing to explicitly granting SHOWPLAN for individual databases

Only sysadmin and if you are mapped to the dbo user name within the db, does it not perform the check.

Is there a way to give these developers the ability to view plans without the role of sysadmin or granting alter trace or granting view server state?

DENY trumps GRANT, so yeah, you can’t let people view plans if you’ve explicitly denied them the ability to alter trace. But just because you haven’t denied a right doesn’t mean it has been granted.

This example shows that your database users can run queries and collect the execution plans within a database where they have been granted SHOWPLAN (and without being added to the db_owner role), as long as the server-level login has not been explicitly denied the ability to ALTER TRACE.

It also shows that, unless you explicitly grant any trace-related permissions, they won't be able to read trace data, or even be able to tell there are traces running at all, unless they have inherited those rights in some other way (Windows group, server role, etc). So you don't need to explicitly deny ALTER TRACE in order to protect trace data from these users - just be aware that it is possible for them to can inherit it without an explicit grant.

USE master;
GO
CREATE LOGIN blob WITH PASSWORD = 'x', CHECK_POLICY = OFF;
GO
CREATE DATABASE floob;
GO
USE floob;
GO
CREATE USER blob FROM LOGIN blob;
GO
GRANT SHOWPLAN TO blob;
GO
CREATE TABLE dbo.splunge(plonk INT);
GO
GRANT SELECT ON dbo.splunge TO blob;
GO
EXECUTE AS USER = 'blob';
GO
SET SHOWPLAN_XML ON;
GO
-- plan is shown:
SELECT plonk FROM dbo.splunge;
GO
SET SHOWPLAN_XML OFF;
GO
-- error:
EXEC sp_trace_getdata @traceid = 1;
GO
-- empty result set:
SELECT * FROM sys.traces;
GO
REVERT;
GO
USE master;
GO
DROP DATABASE floob;
DROP LOGIN blob;


The Target Principal Name Is Incorrect. Cannot Generate SSPI Context (SQL or AD Issue)? We had a power outage last night and when the server came back up our domain users could not access our SQL Server 2000 instance name: MASSQL.

We can access using SQL Authentication.

We tried making sure the user that runs the service has SSPI read and Write
We tried creating a new user
We tried running the service using NETWORK SERVICE account
We tried running as Local System Account
We tried creating a new Domain Admin account and run service as the new Domain Admin account

I also ran the following command against the problem SQL Server instance:

setspn -L xsql2

Results

Registered ServicePrincipalNames for CN=MASSQL,CN=Computers,DC=ABC,DC=com:
        HOST/MASSQL
        HOST/MASSQL.ABC.COM

I ran the following command against a non-problem SQL instance:

setspn -L xensql1

Results

Registered ServicePrincipalNames for CN=XENSQL1,CN=Computers,DC=ABC,DC=com:
        WSMAN/XENSQL1
        WSMAN/XENSQL1.ABC.com
        RestrictedKrbHost/XENSQL1
        HOST/XENSQL1
        RestrictedKrbHost/XENSQL1.ABC.COM
        HOST/XENSQL1.ABC.COM

It seems the results for the non-problem instance have more data.  I do not know if this has to do with the fact one is 2000 and the other is 2012.

What likely happened?  How do I allow domain users login again?

Use setspn -X to look for duplicate SPNs for the SQL Server in question.

Remove any duplicate SPNs that don't line up the SQL Server Service account in question.

Service Principal Names for SQL Server take the form of:

MSSQLSvc/server.domain:port
MSSQLSvc/server:port

Assuming your SQL Server is using the default TCP port, 1433, I would expect you need the following servers:

MSSQLSvc/MASSQL.abc.com:1433
MSSQLSvc/MASSQL:1433

You can create those SPNs using the following command:

SETSPN -A MSSQLSvc/MASSQL.abc.com:1433 DOMAIN\Account
SETSPN -A MSSQLSvc/MASSQL:1433 DOMAIN\Account

Where DOMAIN\Account is the name of the service account used by SQL Server.

If your SQL Server uses a named instance, the SETSPN commands look like:

SETSPN -A MSSQLSvc/MASSQL.abc.com:1433 DOMAIN\Account
SETSPN -A MSSQLSvc/MASSQL:1433 DOMAIN\Account
SETSPN -A MSSQLSvc/MASSQL.abc.com:INSTANCENAME DOMAIN\Account
SETSPN -A MSSQLSvc/MASSQL:INSTANCENAME DOMAIN\Account


MySQL 'copying to tmp table' while enlarging VARCHAR One user called today because he has tried to enlarge a VARCHAR column (from 20 to 100) in a slightly large table resulting in a write-locked table.

I watched PROCESSLIST and saw that ALTER TABLE sentence was being locked for 5 minutos in 'copy to tmp table' state.

I'm wondering why MySQL needs to copy table to a tmp table to enlarge a VARCHAR.
I mean, isn't supposed that one of the benefits of VARCHAR is that every row only uses needed bytes for this column as opposed to CHAR that uses all available bytes?.

From http://dev.mysql.com/doc/refman/5.1/en/char.html:

VARCHAR values are stored as a 1-byte or 2-byte length prefix plus data. The length prefix indicates the number of bytes in the value

I understood that, while enlarging a CHAR will need to modify all table data to add new bytes in each row, enlarging a VARCHAR will only need modifications in table schema leaving data as is.

Why does MySQL need to copy the table to a tmp table?

This is the paradigm of ALTER TABLE. It is a simpler approach to making changes. Keep in mind that MySQL supports an open storage engine plugin architecture. The same calls to perform every operation fits InnoDB, MyISAM, and other storage engines.
Under the hood, doing
ALTER TABLE tblname MODIFY COLUMN mycolumn VARCHAR(255);

would mechanically work like this
CREATE TABLE tblname_new LIKE tblname;
ALTER TABLE tblname_new MODIFY COLUMN mycolumn VARCHAR(255);
INSERT INTO tblname_new SELECT * FROM tblname;
DROP TABLE tblname;
ALTER TABLE tblname_new RENAME tblname;

This approach is totally storage engine agnostic.
The alternative to this would be for the storage engine to actively manage a list of blocks that can hold new rows.
What happens when you deal with...
CHAR columns
Even if such management was in place, it would never get summoned because none of the fragments would be large enough to hold changed rows since the column had a smaller size before. This would result in appending all the rows to the physical file for the table. Now, the space occupied by the rows before the change would appear before the first of the table after the ALTER TABLE was done. This would be true for expanding CHAR variables.
VARCHAR columns
In the case of expanding a VARCHAR, altering lengths to be larger may require changing from 1 to 2 bytes for every row should you expand a VARCHAR(255) (255 is a 1-byte unsigned number) to VARCHAR(256) (256 is a 2-byte unsigned number). The introduction of that one extra byte would still require vigorous fragmentation block list management just for the sake of accommodating one extra byte. Therefore, actual space management via maintaining a fragmentation block list would be highly impractical for the open storage engine plugin architecture of MySQL. Such management would probably be in Oracle, SQL Server and PostgreSQL.


What is default MySQL binlog retention in AWS RDS? I tried to  retrieve my MySQL RDS binlogs in order to run manual "rollback" for some SQLs.
Unfortunately, I noticed that only 2 binlogs for the last 30 minutes are available.

"call mysql.rds_show_configuration" provides "binlog retention hours | NULL".

I guess (because RDS provides ability to point-in-time restore between daily backups) that AWS RDS keeps its binlogs for 24 hours. But it seems that it's unavailable for the end user...

TIA,
Vitay

you need to give it a value if you wish to preserve the binlogs:

call mysql.rds_set_configuration('binlog retention hours', 24);

will cause the binlogs to be stored for 24hrs, for example.


T-SQL to Group time interval change by date range in sql server The original table has the column Timestamp with Interval

Interval: difference in minute between the current and previous Timestamp when sorted by Timestamp itself

Timestamp               Interval(InMinute)
2016-12-31 00:28:00     NULL
2016-12-31 00:29:00     1
2016-12-31 00:30:00     1
2016-12-31 00:45:00     15
2016-12-31 01:00:00     15
2016-12-31 01:15:00     15
2016-12-31 01:16:00     1
2016-12-31 01:17:00     1
2016-12-31 01:18:00     1
2016-12-31 01:19:00     1

I want to detect a time interval change using T-SQL and produce the output

StartDate                EndDate                Interval
2016-12-31 00:28:00      2016-12-31 00:30:00    1
2016-12-31 00:30:00      2016-12-31 01:15:00    15
2016-12-31 01:15:00      2016-12-31 01:19:00    1

I wanted to tell for how long an Interval stayed the same. The second row says that from 2016-12-31 00:30:00 to 2016-12-31 01:15:00, the Interval stayed the same i.e. 15. But then it changed back to 1 after 2016-12-31 01:15:00

there is an example , from Itzik Ben Gan (Gaps and Islands in Sequences)
This is base on this article: Gaps

DECLARE @vt_Source AS TABLE
( ts datetime NOT NULL PRIMARY KEY,
 interval tinyint NULL
)

INSERT INTO @vt_Source(ts, interval)
VALUES('2016-12-31 00:28:00',     NULL)
,('2016-12-31 00:29:00'  ,   1)
,('2016-12-31 00:30:00'  ,   1)
,('2016-12-31 00:45:00'  ,   15)
,('2016-12-31 01:00:00'  ,   15)
,('2016-12-31 01:15:00'  ,   15)
,('2016-12-31 01:16:00'  ,   1)
,('2016-12-31 01:17:00'  ,   1)
,('2016-12-31 01:18:00'  ,   1)
,('2016-12-31 01:19:00'  ,   1)

SELECT
   min(ts_prev) AS startDate
  ,max(ts) AS endDate
  ,interval
FROM
   (SELECT
         ts
         ,interval
         ,ROW_NUMBER() OVER(ORDER BY ts ASC) AS  rn_all
         ,ROW_NUMBER() OVER(PARTITION BY interval ORDER BY ts ASC) AS rn_group
         ,LAG(ts,1,ts) OVER(ORDER BY ts ASC) AS ts_prev
    FROM
        @vt_Source
   )A
WHERE
   A.interval IS NOT NULL
GROUP BY
   rn_all - rn_group
   ,interval
ORDER BY
   startDate ASC

output for this:

startDate           endDate          interval
31/12/2016 00:28:00 31/12/2016 00:30:00 1
31/12/2016 00:30:00 31/12/2016 01:15:00 15
31/12/2016 01:15:00 31/12/2016 01:19:00 1

I added a WHERE clause to eliminate the first row , the one that have NULL on interval column

http://dbfiddle.uk/?rdbms=sqlserver_2016&fiddle=fe1888d0a934d73de0ed9887aaf4d482


Record scan speed, column orientated databases and SSDs I'm learning about column orientated databases. I have just watched a YouTube video on column orientated databases by Arnaldo from MIT, who suggests an interesting motivation for column orientated databases. About half way through he talks about how long it takes to do a table scan for a query, and discusses the actual physical disk head reading from a spinning magnetic disk. He says that even though we may only want to query, say, 3 columns from a table with 100 columns, the disk head still needs to physically pass over the entire records in a row-orientated database, hence one reason why a column orientated storage system may be faster in some scenarios for querying.

I have a couple of questions about this:

How true is this, given that data in records may not actually be physically next to each other on disk (because of B+Tree index structures)? Is this an oversimplification, or is it true in some situations and not in others (e.g. in tables with no indexes)?
How does this relate to SSDs - as there is no spinning disk and head scanning over it, does the same principle hold true when data is stored in an SSD?

Thanks

Don't know enough about SSDs to comment, but I have studied columnar stores and how traditional disk technology affect/are affected by this model.

It's perfectly true that records in "OldSQL" (Google Michael Stonebraker and OldSQL) are not adjacent - this is entirely by design - the RDBMS hands placement of files over to the OS - hence fragmentation. (I'm guessing here, but I don't see why SSDs wouldn't suffer (maybe to a lesser degree) from the same effects as traditional HDDs).

What with DELETEs and subsequent INSERTs, your data for a given table could be scattered all over a disk. Columnar stores are meant to mitigate this problem - BUT by imposing restrictions on updating &c. There are complex ways in which they can retain bits of space for reorganisations, but it gets tricky fairly quickly.

The argument is that if you are summing over, say, sales, it would easier to trip along the disk and pick up the million (or whatever) sales in one pass rather than going to each record and picking out the sale value and adding. (Again, I can't see why a straight scan wouldn't be more efficient on SSDs - no idea of benchmarks in this area).

This also makes compression easier, since if datatypes are the same, more efficient algorithms can be used as opposed to compressing records with varying datatypes - which in turn makes the data smaller and quicker to read.

Take a look at Infinidb (now defunct) and Infobright (Community Edition). Vertica is a columnar store - kicked off by Stonebraker and sold subsequently to HP.

It's an interesting concept for the OLAP world and one which IMHO has validity in that space. I'm interested for the performance of statistical analysis which often requires an OLAP approach.


How to transform "dot" to "slash"? In a context of using SQL DB2:

The sql instruction
CHAR(MyDate, EUR) transforms 25/12/16 into 25.12.2016
(and CHAR(MyDate, ISO) transforms 25/12/16 into 2016-12-25)

Is it possible to get directly 25/12/2016
(or 2016/12/25)
without using the greedy scalar function
TRANSLATE(CHAR(MyDate, EUR), '.', /')
(or TRANSLATE(CHAR(MyDate, EUR), '-', /')

Maybe General Settings somewhere ...

to_char(MyDate, 'DD/MM/YYYY') is the correct answer
(or to_char(MyDate, 'DD;MM;YYYY') if you want ; instead of /)

Beware : MyDate must be a timestamp zone.

If MyDate is a Date Zone, you should code

to_char(timestamp_iso(MyDate), 'DD/MM/YYYY')

(or to_char(timestamp_iso(MyDate), 'DD,MM,YYYY') if you want , instead of /)


Why does SQL Server use a better execution plan when I inline the variable? I have a SQL query that I am trying to optimize:

DECLARE @Id UNIQUEIDENTIFIER = 'cec094e5-b312-4b13-997a-c91a8c662962'

SELECT
  Id,
  MIN(SomeTimestamp),
  MAX(SomeInt)
FROM dbo.MyTable
WHERE Id = @Id
  AND SomeBit = 1
GROUP BY Id

MyTable has two indexes:

CREATE NONCLUSTERED INDEX IX_MyTable_SomeTimestamp_Includes
ON dbo.MyTable (SomeTimestamp ASC)
INCLUDE(Id, SomeInt)

CREATE NONCLUSTERED INDEX IX_MyTable_Id_SomeBit_Includes
ON dbo.MyTable (Id, SomeBit)
INCLUDE (TotallyUnrelatedTimestamp)

When I execute the query exactly as written above, SQL Server scans the first index, resulting in 189,703 logical reads and a 2-3 second duration.

When I inline the @Id variable and execute the query again, SQL Server seeks the second index, resulting in only 104 logical reads and a 0.001 second duration (basically instant).

I need the variable, but I want SQL to use the good plan. As a temporary solution I put an index hint on the query, and the query is basically instant. However, I try to stay away from index hints when possible. I usually assume that if the query optimizer is unable to do its job, then there is something I can do (or stop doing) to help it without explicitly telling it what to do.

So, why does SQL Server come up with a better plan when I inline the variable?

In SQL Server, there are three common forms of non-join predicate:
With a literal value:
SELECT COUNT(*) AS records
FROM   dbo.Users AS u
WHERE  u.Reputation = 1;

With a parameter:
CREATE PROCEDURE dbo.SomeProc(@Reputation INT)
AS
BEGIN
    SELECT COUNT(*) AS records
    FROM   dbo.Users AS u
    WHERE  u.Reputation = @Reputation;
END;

With a local variable:
DECLARE @Reputation INT = 1

SELECT COUNT(*) AS records
FROM   dbo.Users AS u
WHERE  u.Reputation = @Reputation;

Outcomes
When you use a literal value, and your plan isn't a) Trivial and b) Simple Parameterized or c) you don't have Forced Parameterization turned on, the optimizer creates a very special plan just for that value.
When you use a parameter, the optimizer will create a plan for that parameter (this is called parameter sniffing), and then reuse that plan, absent recompile hints, plan cache eviction, etc.
When you use a local variable, the optimizer makes a plan for... Something.
If you were to run this query:
DECLARE @Reputation INT = 1

SELECT COUNT(*) AS records
FROM   dbo.Users AS u
WHERE  u.Reputation = @Reputation;

The plan would look like this:

And the estimated number of rows for that local variable would look like this:

Even though the query returns a count of 4,744,427.
Local variables, being unknown, don't use the 'good' part of the histogram for cardinality estimation. They use a guess based on the density vector.

SELECT  5.280389E-05 * 7250739 AS [poo]
That'll give you 382.86722457471, which is the guess the optimizer makes.
These unknown guesses are usually very bad guesses, and can often lead to bad plans and bad index choices.
Fixing It?
Your options generally are:

Brittle index hints
Potentially expensive recompile hints
Parameterized dynamic SQL
A stored procedure
Improve the current index

Your options specifically are:
Improving the current index means extending it to cover all the columns needed by the query:
CREATE NONCLUSTERED INDEX IX_MyTable_Id_SomeBit_Includes
ON dbo.MyTable (Id, SomeBit)
INCLUDE (TotallyUnrelatedTimestamp, SomeTimestamp, SomeInt)
WITH (DROP_EXISTING = ON);

Assuming that Id values are reasonably selective, this will give you a good plan, and help the optimizer by giving it an 'obvious' data access method.
More Reading
You can read more about parameter embedding here:

Parameter Sniffing, Embedding, and the RECOMPILE Options, by Paul White
Why You’re Tuning Stored Procedures Wrong (the Problem with Local Variables), Kendra Little


Query to check if all replication articles are created at Subscriber SQL Server 2012 I am creating an automated build and test tool for our databases. In general there are two stages:
- install databases in version A, install transactional replication
- drop replication, update databases to version B, install replication

The problem is that replication is dropped before previous stage finish to create articles (tables) at Subscriber. In this case Subscriber is missing some tables and update script fails.

Can you give me a query or tables to check if all articles are created?

Subscriber is on different instance than Publisher.
Articles are created with sp_addarticle and after that we execute this: sp_startpublication_snapshot

UPDATE:
I managed to create a workaround but it is not 100% correct.
This query is supposed to check if tables created from replication on Subscriber are the same as articles on Publisher.
I found out that our actual situation is as follow:
- tables on Subscriber exists, because they were created from initial scripts
- replication is installed
- below query is executed
- in the meantime articles on Publisher are created
- my query compares them with original tables from initial scripts and finishes with success
- Replication is started and Subscriber tables are being dropped and created
- update to version B is started
- sql agent is stopped, replication is dropped
and we have missing tables on Subscriber.

The query is executed on Subsciber with linked connection to Publisher.

WHILE (
  SELECT COUNT(1)
  FROM (
   SELECT a.dest_table
   FROM [10.149.000.000].PubDB.dbo.sysarticles a (NOLOCK),
    [10.149.000.000].PubDB.dbo.syspublications p (NOLOCK)
   WHERE a.pubid = p.pubid
    AND p.NAME = 'my publication name'

   EXCEPT

   SELECT t.NAME
   FROM sys.tables t (NOLOCK)
   WHERE t.is_ms_shipped = 0
   ) AS Z
  ) > 0
BEGIN
 PRINT 'Waiting for replication to finish.'

 WAITFOR DELAY '00:00:02'
END

I am not sure if there is a better way but something simple like this should do it. If you run this query on the Publisher, it will compare the tables and will return you the difference in tables. The Publisher needs to be linked to the Subscriber.

-- Publisher
select t.name
from sys.tables t
where is_published = 1
except
-- Subscriber
select t.name
from [SERVERNAME].[REPLICATED_DATABASE].sys.tables t
where is_ms_shipped = 0;


postgres database user grants to query database owned by other user The postgres learning curve is very steep for me I need help. (I come from a mysql background and have the bad habit of trying to translate my mysql knowledge to pg)

here is the premise:

I want to import a sql dump of a pg database into a new (clean) instance.
I want to have 2 users:

dba which will own the database and be able to do DDL.
app will be only able to SELECT, INSERT, DELETE and that sort of things.

With the research I did this is what I tought should work:

I psql -U postgres and do the following

CREATE ROLE "app" WITH ENCRYPTED PASSWORD '${APP_PASSWORD}' LOGIN;
CREATE ROLE "dba" WITH ENCRYPTED PASSWORD '${DBA_PASSWORD}' LOGIN;

REVOKE ALL ON SCHEMA public FROM PUBLIC;

GRANT ALL ON SCHEMA public TO "dba";

GRANT SELECT, INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES ON ALL TABLES IN SCHEMA public TO "app";
GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA public TO "app";
GRANT SELECT, USAGE ON ALL SEQUENCES IN SCHEMA public TO "app";

ALTER DEFAULT PRIVILEGES FOR USER "dba" IN SCHEMA public GRANT SELECT, INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES ON TABLES TO "app";
ALTER DEFAULT PRIVILEGES FOR USER "dba" IN SCHEMA public GRANT EXECUTE ON FUNCTIONS TO "app";
ALTER DEFAULT PRIVILEGES FOR USER "dba" IN SCHEMA public GRANT SELECT, USAGE ON SEQUENCES TO "app";

Then I login with dba and do

CREATE DATABASE "mydb";

Then I import my sql dump with psql

psql -U dba -c mydb < dump.sql

Then I see my data and tables with the user dba.

With the user app tho, I can connect with psql to mydb but when I do \dt I don't see my tables and a select returns:

mydb=> select * from mytable limit 10;
ERROR:  relation "mytable" does not exist

Your app user should be created like this.
Create the user:
CREATE ROLE "app" WITH ENCRYPTED PASSWORD '${APP_PASSWORD}' LOGIN;

Run this on mydb database.
Grant Connect to DB:
GRANT CONNECT ON DATABASE mydb to app;

Grant Usage:
GRANT USAGE ON ALL SEQUENCES IN SCHEMA public TO "app";
GRANT USAGE ON ALL TABLES IN SCHEMA public TO "app";

Grant Other privileges:
GRANT SELECT, INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES ON ALL TABLES IN SCHEMA public TO "app";
GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA public TO "app";


How to define macros in SQL server? I want to define a macro in my SQL script and later based on my conditional query I want to create a new variable name which will be 'X'+macro name.

For example, say I define a macro 'month' which has the current month of the system.

DECLARE @month AS VARCHAR(20);
SET @month = DATENAME(MONTH, GETDATE()), 3)

Upon execution, say it returns 'Nov'.
Now further in the script, I want to execute a query which based on CASE/WHEN will create a new variable which will be called something like 'Product_Nov'. When I use the below mentioned query my output is a new column named 'Product_@month' whereas I wanted 'Product_Nov'.

CASE
WHEN x ='Y' then 1
ELSE 0
END AS Product_@month

Any help/advice/suggestions much appreciated.

I sort of simulate macros using dynamic SQL, but it's pretty clunky since this isn't what T-SQL was designed for.

DECLARE @sql nvarchar(max) = N'SELECT foo_$alias$ = 1;';

DECLARE @alias sysname = N'bar';

SET @sql = REPLACE(@sql, N'$alias$', @alias);

EXEC sys.sp_executesql @sql;

Output:

foo_bar
-------
      1

My concern would be that the consuming application is able to handle all of the potential column names coming back from the database (I assume your real example is more complex, because the application certainly has the ability to know the current month).


MongoDB UserNotFound when starting mongod I have a MongoDB version 2.4 that i wish to upgrade to 4.0. The official solution is to upgrade from 2.4 to 2.6, then to 3.0, and so on.
I am attempting to upgrade from 2.6 to 3.0, and also to change the storage engine to WiredTiger .
To upgrade from 2.6 to 3.0 i changed the bin folder's name of the 2.6executables and then changed the 3.0 bin folder's name to bin (so i will have a seamless transition to 3.0). Then as This official MongoDB upgrade manual says, i am trying to change the storage engine to WiredTiger by using This manual.
However, when i run this command:
mongod --storageEngine wiredTiger --dbpath ../dataDb_WiredTiger

as suggested by the manual, i get an error:

SCRAM-SHA-1 authentication failed for MyAdminUser on admin ;
UserNotFound Could not find user MyAdminUser@admin
Failed to authenticate MyAdminUser@admin with mechanism MONGODB-CR: AuthenticationFailed UserNotFound Could not find user MyAdminUser@admin

But using MongoVUE or shell, i am able to authenticate using the same user.
What am i missing here?
Thank you.

Ended up working by using a configuration file when running mongod. Don't know why.


Where condition execution sequence in MySQL I am using MySQL 5.0.88. I have a query like:

select DISTINCT party.id
FROM party
  LEFT join party_identifier AS pi
  ON pi.party_id = party.id
WHERE (pi.source_note=?
       AND (first_name LIKE ? OR last_name LIKE ? OR pi.identifier LIKE ?))
  AND party.type = ?
  AND party.record_status_id = ?

If the Pi.source_note = ? evaluates to false, will the rest of the conditions will evaluated or not?

What is the execution sequence for the where conditions? (In terms specifically of SQL, not programming languages.)

There is no order, subject to standard operator precedence.

SQL is a declarative language. You tell the engine "what to get", "how to do it". So the engine does not evaluate left to right in code order, nor does it generally short circuit.

For example:

the engine attempts to match the predicates against a suitable index. The index key columns order may not match the WHERE clause order so it will ignore the WHERE order.
it may decide that the 2nd condition is cheaper to evaluate in order to limit rows before applying the 1st condition


The database owner account has been deleted. What impact? When I create a database, I have always changed the database owner from me (usually my domain account) to another, either a standard service account used for ownership, or to the usual sa

However, I've just inherited a series of instances where a lot of the databases have been created by another member of staff, who's now left, so naturally his domain account has been deleted.

Didn't run into any issues with this until yesterday when I tried to create a stored procedure on a database that would execute as owner (blindly believing it to be 'sa'). The create failed because the owner didn't exist, so I had no choice but to make an ad-hoc change as the client needed the stored procedure creating.

There are tons of production databases under his name so I want to be both pro-active and change-control savvy.

First question is, what are the potential issues this could cause now or in the future?

Second question, can I safely just change ownership to that of a standard account or 'sa'? Could it, will it, have any weird impact?

Just change ownership for starters.

Database ownership (the owner_sid in sys.databases) not have really have much impact on day to day operations


whats best practice for Log Truncation in SQL Server? I have a production DB in SQL server and wanted to put the final touches after the functionality is completed. prior to shipping it out i want to make sure i have some clean up in the SQL server DB and truncate and shrink log files?

can i have a nightly job run to truncate logs and shrink files?

this is what i have so far:
MY RECOVERY MODEL IS SIMPLE

ALTER proc [dbo].[UTIL_ShrinkDB_TruncateLog]
as

-- exec sp_helpfile
BACKUP LOG PMIS WITH TRUNCATE_ONLY

DBCC SHRINKFILE (PMIS, 1)

DBCC SHRINKFILE (PMIS, 1)

Don't truncate or shrink your log files.
See point 1

Why do you think this is a good idea?

With simple recovery, the internal log entries are removed periodically (ready to be reused) but the file requires no more processing.

The log file is the size it is because of your day to day activities. If you shrink the log file you'll end up with poor performance.


Postgresql tables seem duplicated (with suffixes such us pkey, hashes, etc) due to some problems I came to check the state of my current database in production and I found that some tables are "duplicated". By duplicated I mean that there appear to be some clones of the actual table, with the same name and a suffix. An example:

Running the following command once connected to my database:

SELECT relname, reltuples, relpages * 8 / 1024 AS "MB" FROM pg_class ORDER BY relpages DESC;

Returns this:

                            relname                       |  reltuples  |  MB
----------------------------------------------------------+-------------+-------
 ...                                                      |             |
 reversion_revision                                       |      806547 |    58
 ...                                                      |             |
 app_session                                              |       96803 |    40
 ...                                                      |             |
 reversion_revision_pkey                                  |      806547 |    17
 reversion_revision_manager_slug_7521cb9583783434_like    |      806547 |    17
 reversion_revision_c69e55a4                              |      806547 |    17
 reversion_revision_e8701ad4                              |      806547 |    17
 reversion_revision_b16b0f06                              |      806547 |    17
 ...                                                      |             |
 app_session_dadd2494                                     |       96803 |     2
 app_session_f8a3193a                                     |       96803 |     2
 app_session_3ed54a03                                     |       96803 |     2
 app_session_4437cfac                                     |       96803 |     2
 app_session_pkey                                         |       96803 |     2
 app_session_e160a0b9                                     |       96803 |     2
 app_session_975a0aa5                                     |       96803 |     2
 app_session_b5ac7ae1                                     |       96803 |     2
 app_session_c585221c                                     |       96803 |     2
 ...                                                      |             |

I think they are duplicated since the number of reltuples is the exact same and the names share the original name (plus the weird suffix)

I am not a DB expert, but this seems wrong to me. Is there a problem with this? The only one I see (since everything works fine) is the space increase. Is there a clear solution? Did I perform something wrong? Is it dangerous?

I imagine it should be enough with deleting this "duplicated" tables. Would there be a programmatic way of doing this? Any hints? I am completely lost.

I am running psql (PostgreSQL) 9.3.13 under Ubuntu 14.04

No, those are not "duplicated", those are additional relations/objects created for the tables.

reversion_revision_pkey is most probably the unique index supporting the primary key for the table reversion_revision.

And the others are most probably indexes as well.

You can add this expression to your query to see the actual type of the relation:

case relkind
   when 'r' then 'table'
   when 'i' then 'index'
   when 'S' then 'sequence'
   when 'v' then 'view'
   when 'm' then 'materialized view'
   when 't' then 'TOAST table'
   else relkind
end as rel_kind

  I imagine it should be enough with deleting this "duplicated" tables

No, do not delete (drop) those. They are essential for your database to work.

For a description on all columns in pg_class see here


how to display running count of records in query I am working on SQL SERVER 2012, and I have been trying to figure out how to include a running count of records in a group by statement. For example, the query would look something like:
SELECT CUSTOMER_ID, TRANSACTION_ID, COUNT
FROM TRANSACTIONS
GROUP BY CUSTOMER_ID, TRANSACTION_ID
ORDER BY CUSTOMER_ID, TRANSACTION_ID
And the output would look something like:
10   100   1
10   108   2
10   125   3
10   140   4
12   102   1
12   103   2
12   138   3
15   135   1
I appreciate any advice on how to query this information.

You'll want to use a window function such as ROW_NUMBER() for this one like so:
SELECT CUSTOMER_ID, TRANSACTION_ID, ROW_NUMBER() OVER (PARTITION BY CUSTOMER_ID ORDER BY TRANSACTION_ID) AS [Count] -- Running incremental count of rows / TRANSACTION_ID per CUSTOMER_ID
FROM TRANSACTIONS
GROUP BY CUSTOMER_ID, TRANSACTION_ID
ORDER BY CUSTOMER_ID, TRANSACTION_ID


Why does mysqldump stop the slave SQL thread for the entire backup? I realized recently that I was using --master-data to dump a backup from a replication slave when what I really wanted was --dump-slave. After switching to --dump-slave, I see that mysqldump will stop the slave SQL thread during the backup (which was clear from the documentation -- it wasn't a surprise).

I was wondering if anyone could explain why the slave's SQL thread needs to be stopped for the entire backup? I'm using --single-transaction and I'm expecting to get a consistent snapshot as of the time the backup is started. That should be possible even with transactions being processed by the SQL thread. Even if the slave SQL thread is completely held-up due to some lock that mysqldump has acquired, the slave should be allowed to continue to (try to) execute SQL DML statements during the backup.

I can also understand wanting to at least pause the slave SQL thread so that the get-stave-status the start-transaction operations could happen consistently, but as soon as the dump operation begins, the slave SQL thread could be re-started, right?

When using --master-data with a replication master, global locks are obtained a single time and then released, allowing the regular application-load to continue during the dump operation. Why can't this be done on the slave in the same way? Any information anyone could provide would be greatly helpful.

--single-transaction obviously only preserves a transaction for the transactional tables.

The STOP SLAVE is needed to preserve the consistency of the dump of the MyISAM tables. If there was logic in mysqldump to dump the MyISAM tables first, it could START SLAVE at that point (in a different connection as START SLAVE probably an implicit commit`), and continue dumping the InnodDB tables.

The STOP SLAVE is also required as DLL isn't transactional. What could happen is while tables are being dumped, a CREATE TABLE / DROP TABLE could happen in the replication stream, and as those aren't transactional, the dump would then be inconsistent.

Why --master-data assumes it isn't a slave and doesn't need the same level of protection is anyone's guess. Why --master-data issues a FLUSH TABLES WITH READ LOCK around dumping position information and dump slave doesn't? Why the UNLOCK TABLES happens before the data dump starts? Also a mystery.


Logging Stored Procedure Parameter Values I've worked in an SQL Server environment before where logging was made part of our stored procedures to capture execution start/end, parameter values and error messages which I found very useful and is something I'm looking to introduce in a new environment.

The tables used for this logging looked like something below, parameters were captured using INSERT statements into a table with the with values being implicitly cast to NVARCHAR.

CREATE TABLE dbo.Execution
(
    Id                  INT IDENTITY(1,1)           NOT NULL
,   SchemaName          NVARCHAR(128)               NOT NULL
,   ProcedureName       NVARCHAR(128)               NOT NULL
,   ExecutionStart      DATETIME                    NOT NULL
,   ExecutionEnd        DATETIME                    NULL
,   ExecutionFailed     BIT                         NOT NULL
)

CREATE TABLE dbo.ExecutionError
(
    Id                  INT IDENTITY(1,1)           NOT NULL
,   ExecutionId         INT                         NOT NULL
,   CustomErrorMessage  NVARCHAR(8000)              NULL
,   SqlErrorMessage     NVARCHAR(8000)              NULL
)

CREATE TABLE dbo.ExecutionParameter
(
    Id                  INT IDENTITY(1,1)           NOT NULL
,   ExecutionId         INT                         NOT NULL
,   ParameterName       NVARCHAR(128)               NOT NULL
,   ParameterValue      NVARCHAR(MAX)               NULL
)

I've been reconsidering the ExecutionParameter table with the possibility of using an SQL_VARIANT data type so I could get the base data type if ever needed for analysis and reporting purposes without having to guess based on the name/value.

However this wouldn't work for parameters that have a data type of NVARCHAR(MAX) or VARCHAR(MAX) and would therefore need to still have the NVARCHAR(MAX) column there which would be NULL the majority of the time.

The use of SQL_VARIANT is tempting but I feel the original table structure works fine and can't really be made any better without making the process more complicated.

Is this something you have done previously, if so how did you implement it? Can you see room for improvement in the above schema without making it overly complicated?

Perhaps an additional table with details of the stored procedure and their parameters which can be referenced? Though I feel this would be difficult to maintain and become confusing as stored procedures are modified over time.

Yes, we did this type of thing at a company I worked at a few years ago. Even though we did it only for error logging, it was the same general concept. You just need to write the SELECT statement that includes all of the input parameters, use FOR XML, and store that in a variable that can be inserted into your Execution table in a new field: Parameters XML NULL. And then you can scrap the ExecutionParameter table.

Try the following as it shows three ways of generating the XML: Attribute-based, Element-based, and Element-based including NULL elements. The default behavior of FOR XML is to indicate NULL values by not including the attribute or element. But if you are doing Element-based XML and want the parameters to show even if they are NULL, then you need to specify XSINIL in the FOR XML clause. The ability to include an indicator for NULL is not available in Attribute-based XML.

Please note that there is not a huge difference between Attributed-based XML and the default Element-based XML (default = does not include NULL items) in terms of storage size (as shown in the example below). When viewing the XML it does appear that there is a lot more "bloat" with Element-based, and that would be the case if storing the data in an NVARCHAR field or text file. But the XML datatype uses an optimized method of storing the data internally, in a way that cannot be seen, but is clearly indicated in the resulting DATALENGTH output.

There is, however, a slight increase in size when using the XSINIL option for Element-based XML and it needs to keep track of NULL elements which are excluded from the other two types. The increase in size is due to a one-time hit for declaring the xsi namespace in the root element, and then a per-NULL element hit.

DECLARE @Param1 INT = 5,
        @Param2 DATETIME = NULL,
        @Param3 NVARCHAR(50) = N'Test < some & XML chars "',
        @Param4 DATETIME = '2016-04-09';

DECLARE @Parameters XML;

SELECT @Parameters = (
   SELECT @Param1 AS [Param1],
          @Param2 AS [Param2],
          @Param3 AS [Param3],
          @Param4 AS [Param4]
   FOR XML RAW(N'Params') -- Attribute-based XML (NULL attributes are missing)
);

SELECT @Parameters, DATALENGTH(@Parameters); -- 183

SELECT @Parameters = (
   SELECT @Param1 AS [Param1],
          @Param2 AS [Param2],
          @Param3 AS [Param3],
          @Param4 AS [Param4]
   FOR XML RAW(N'Params'), ELEMENTS -- Element-based XML (NULL elements are missing)
);

SELECT @Parameters, DATALENGTH(@Parameters); -- 185

SELECT @Parameters = (
   SELECT @Param1 AS [Param1],
          @Param2 AS [Param2],
          @Param3 AS [Param3],
          @Param4 AS [Param4]
   FOR XML PATH(N'Params'), ELEMENTS XSINIL -- Element-based XML (NULL elements included)
);

SELECT @Parameters, DATALENGTH(@Parameters); -- 434

Returned XML values:

<!-- Attribute-based -->
<Params Param1="5" Param3="Test &lt; some &amp; XML chars &quot;"
 Param4="2016-04-09T00:00:00" />

<!-- Element-based -->
<Params>
  <Param1>5</Param1>
  <Param3>Test &lt; some &amp; XML chars "</Param3>
  <Param4>2016-04-09T00:00:00</Param4>
</Params>

<!-- Element-based including NULL elements -->
<Params xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <Param1>5</Param1>
  <Param2 xsi:nil="true" />
  <Param3>Test &lt; some &amp; XML chars "</Param3>
  <Param4>2016-04-09T00:00:00</Param4>
</Params>

Additional notes:

For SchemaName and ProcedureName columns, I would use sysname as the datatype since that is how it is defined in the system tables. And be sure to use lower-case for sysname as it is an alias that lives in master and servers with a default collation that is binary or case-sensitive won't be able to find that alias if it is not all lower-case. Your current server might be case-insensitive, but it is still a good habit to get into when using sysname.
For the ExecutionError table, be sure to include INT columns to capture: ERROR_LINE(), ERROR_NUMBER(), ERROR_STATE(), and ERROR_SEVERITY().
No, you don't want to use SQL_VARIANT as that prevents you from storing the LOB types: VARCHAR(MAX), NVARCHAR(MAX), VARBINARY(MAX), and XML.
No, you can't use NVARCHAR(8000) because NVARCHAR has a top size of 4000, unless you use MAX.


Left Outer Join doesn't give expected result. What am I missing? My objective is to find records that exist in Table 1 but do not exist in Table2. No record has a NULL value.
I am using the following code, but it is not giving me the expected results.

Select
    distinct a.fld
from
    tbl1 a
left outer join
    tbl2 b
on
    a.fld= b.fld
where
    a.fld<> b.fld

Most likely the issue is with my where clause, but I can't seem to figure out what the issue is. Pls Help.

Assuming "Table 1" is tbl1a, this should give you what you want:

SELECT DISTINCT a.fld
FROM tbl1 a
LEFT JOIN tbl2 b ON a.fld = b.fld
WHERE b.fld IS NULL


PMA export with all attributes in CREATE TABLE This is the default PMA output of an export:
CREATE TABLE `foo` (
  `id` int(11) UNSIGNED NOT NULL,
  `fk_id` int(11) UNSIGNED NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

ALTER TABLE `foo`
  ADD PRIMARY KEY (`id`);

ALTER TABLE `foo`
  MODIFY `id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT;

ALTER TABLE `foo`
  ADD CONSTRAINT `foo_ibfk_1` FOREIGN KEY (`fk_id`) REFERENCES `bar` (`id`),

...

Is there a way to get an export where all of the following ALTER TABLE are merged into the CREATE TABLE statement?

I shows all changes you have made, as you can see in the example
Schema (MySQL v8.0)
CREATE TABLE `bar` (`id` int(11) UNSIGNED,PRIMARY KEY (`id`));
CREATE TABLE `foo` (
  `id` int(11) UNSIGNED NOT NULL,
  `fk_id` int(11) UNSIGNED NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

ALTER TABLE `foo`
  ADD PRIMARY KEY (`id`);

ALTER TABLE `foo`
  MODIFY `id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT;

ALTER TABLE `foo`
  ADD CONSTRAINT `foo_ibfk_1` FOREIGN KEY (`fk_id`) REFERENCES `bar` (`id`);

Query #1
SHOW CREATE  TABLE foo;

| Table | Create Table                                                                                |
| ----- | --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| foo   | CREATE TABLE `foo` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `fk_id` int(11) unsigned NOT NULL,
  PRIMARY KEY (`id`),
  KEY `foo_ibfk_1` (`fk_id`),
  CONSTRAINT `foo_ibfk_1` FOREIGN KEY (`fk_id`) REFERENCES `bar` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |

View on DB Fiddle


Shutting down mongod while mongodump is running Can we shutdown mongod instance while the mongodump is running? Does mongodump operate by itself or it is a client for a mongod server?

No we cannot . other wise it will give error of server not reachable. As it  connects to DB via mongod service like normal connections. follow below link https://docs.mongodb.org/v3.0/tutorial/backup-and-restore-tools/  clearly mentioned that Mongos or mongod service should be running on machine.


INSERT INTO statement with parameters for tableName, columns and values? Is it possible to have an SP that looks something like this:

DECLARE @tableName = null;
DECLARE @cols = null;
DECLARE @values = null;

INSERT INTO @tableName @cols @values;

The resultant query looks like this:

INSERT INTO <table name> (<set of column names>) VALUES (<set of corresponding values>);

My use case is that I'm parsing Excel data into a database, i.e. a single workbook of 'type X' equates to a single row in this database. These workbooks are filled in from a template, so the data format is pretty constant. To parse the data I create a sheet in the workbook called 'DATA' or something, that looks like this:

tableName | attributeName | attributeValue | .. plus some config stuff
People    | Firstname     | Johnny         | etc
People    | Lastname      | Heinz          | etc
People    | Firstname     | Sam            | etc
People    | Lastname      | Smith          | etc

This would equate to two rows in the People table of the database (multiple rows are recognized by repeating attributeNames for a specific table).

The reasons this needs to be dynamic SQL is:

I'd like this to be applicable to any table in the database
I won't know how many cols a particular table has before parsing

You can't parameterize table names or column names - you'll have to use dynamic SQL.
You should also be passing the table's schema.
You should be validating table names and column names. Table names are easy to check, but for column names we'll use this function:

CREATE FUNCTION dbo.ParseColumnList
(
  @List NVARCHAR(MAX)
)
RETURNS TABLE
AS
  RETURN
  (
    SELECT i,
      c = LTRIM(CONVERT(SYSNAME, SUBSTRING(@List, i,
          CHARINDEX(',', @List + ',', i) - i)))
    FROM (SELECT ROW_NUMBER() OVER (ORDER BY [object_id])
          FROM sys.all_columns) AS n(i)
    WHERE i <= LEN(@List) AND SUBSTRING(',' + @List, i, 1) = ','
  );

You should be using strongly-typed parameters for parameter values, to protect yourself from SQL injection vulnerabilities. Understanding where these parameter values come from will help to further refine this solution, but for now you'll just have to blindly append your values list and hope for the best:

CREATE PROCEDURE dbo.DealersChoice
  @TableSchema   sysname,
  @TableName     sysname,
  @ColumnList    nvarchar(max),
  @ParamValues   nvarchar(max)
AS
BEGIN
  SET NOCOUNT ON;

  IF NOT EXISTS ( -- make sure table exists:
    SELECT 1 FROM sys.tables AS t
      INNER JOIN sys.schemas AS s
      ON t.[schema_id] = s.[schema_id]
      WHERE t.name = QUOTENAME(@TableName)
      AND s.name = QUOTENAME(@TableSchema)
  )
  BEGIN -- raise an appropriate error here
    RETURN;
  END

  IF EXISTS ( -- make sure columns exist:
    SELECT 1 FROM dbo.ParseColumnList(@ColumnList) AS f
      LEFT OUTER JOIN sys.columns AS c ON f.c = c.name
      WHERE c.[object_id] = OBJECT_ID(QUOTENAME(@TableSchema)
        + N'.' + QUOTENAME(@TableName))
    WHERE c.name IS NULL
  )
  BEGIN -- raise appropriate error
    RETURN;
  END

  DECLARE @sql nvarchar(max) = N'INSERT '
    + QUOTENAME(@TableSchema) + N'.' + QUOTENAME(@TableName)
    + N' (' + @ColumnList + N') VALUES(' + @ParamValues + N');';

  EXEC sys.sp_executesql @sql;
END

I am not sure how you are building the @ParamValues parameter but if you have string delimiters embedded in it (like 5, 'foo', '20160206'), you'll need to double those up somewhere so that it becomes 5, ''foo'', ''20160206'' - this becomes a challenge when you have strings like O'Brien - one of just many reasons why you should be using proper parameters instead of concatenating one big string with all your values.

See the following:

Protecting Yourself from SQL Injection in SQL Server - Part 1
Protecting Yourself from SQL Injection in SQL Server - Part 2


Upgrading Instances with Mirroring If you want to upgrade or install a patch on two separate instances that house both the principal and mirrored database, how can you go about that?  If the database that is being mirrored needs to be available 24/7 and you don't have a window to go offline, what is the best means to do this?

EDIT: this is with SQL Server.

First hit from Google gives an MS KB article

"How to install service packs and hotfixes on an instance of SQL Server 2005 that is configured to use database mirroring"

Another viewpoint "How To Apply Service Packs To SQL Servers Involved In Mirroring"


Obtain column names of referenced objects I used a script from here: https://stackoverflow.com/questions/13757387/getting-sql-server-cross-database-dependencies that Milica Medic posted and it works GREAT thank you :) my question is how do I get the column name? so it finds table name from stored proc but can it also find the column name that is being used? thank you in advance

The query Referenced above is.

CREATE PROCEDURE [dbo].[get_crossdatabase_dependencies] AS

SET NOCOUNT ON;

CREATE TABLE #databases(
    database_id int,
    database_name sysname
);

-- ignore systems databases
INSERT INTO #databases(database_id, database_name)
SELECT database_id, name FROM sys.databases
WHERE database_id > 4;

DECLARE
    @database_id int,
    @database_name sysname,
    @sql varchar(max);

CREATE TABLE #dependencies(
    referencing_database varchar(max),
    referencing_schema varchar(max),
    referencing_object_name varchar(max),
    referenced_server varchar(max),
    referenced_database varchar(max),
    referenced_schema varchar(max),
    referenced_object_name varchar(max)
);

WHILE (SELECT COUNT(*) FROM #databases) > 0 BEGIN
    SELECT TOP 1 @database_id = database_id,
                 @database_name = database_name
    FROM #databases;

    SET @sql = 'INSERT INTO #dependencies select
        DB_NAME(' + convert(varchar,@database_id) + '),
        OBJECT_SCHEMA_NAME(referencing_id,'
            + convert(varchar,@database_id) +'),
        OBJECT_NAME(referencing_id,' + convert(varchar,@database_id) + '),
        referenced_server_name,
        ISNULL(referenced_database_name, db_name('
             + convert(varchar,@database_id) + ')),
        referenced_schema_name,
        referenced_entity_name
    FROM ' + quotename(@database_name) + '.sys.sql_expression_dependencies';

    EXEC(@sql);

    DELETE FROM #databases WHERE database_id = @database_id;
END;

SET NOCOUNT OFF;

SELECT * FROM #dependencies;

As much as I've tried, I cannot get sys.sql_expression_dependencies to display any column details.

However, there is a Table-Valued-Function, sys.dm_sql_referenced_entities(), that can be employed to help us in our quest for referenced column details.

The following displays a single table denoting all referenced entities, with column details where appropriate, for the given SQL Server instance:

DECLARE @AllCmds nvarchar(max);
DECLARE @cmd nvarchar(max);
DECLARE cur CURSOR LOCAL FORWARD_ONLY
FOR
SELECT
'SELECT ReferencingDatabaseName = ''' + d.name + '''
    , ReferencingSchemaName = s.name
    , ReferencingObjectName = o.name
    , ReferencedClass = sed.referenced_class_desc
    , ReferencedServerName = sed.referenced_server_name
    , ReferencedDatabaseName = ISNULL(sed.referenced_database_name, ''' + d.name + ''')
    , ReferencedSchemaName = sed.referenced_schema_name
    , ReferencedEntityName = sed.referenced_entity_name
    , ReferencedColumnNames = (STUFF((
        SELECT '', '' + COALESCE(dsre.referenced_schema_name, '''') +
                ''.'' + COALESCE(dsre.referenced_entity_name, '''') +
                ''.'' + COALESCE(dsre.referenced_minor_name, '''')
        FROM sys.dm_sql_referenced_entities(s.name + ''.'' + o.name, N''OBJECT'') dsre
        WHERE dsre.referenced_minor_id > 0
        FOR XML PATH ('''')
    ), 1, 2, ''''))
FROM ' + QUOTENAME(d.name) + '.sys.sql_expression_dependencies sed
    LEFT JOIN ' + QUOTENAME(d.name) + '.sys.objects o ON sed.referencing_id = o.object_id
    LEFT JOIN ' + QUOTENAME(d.name) + '.sys.schemas s ON o.schema_id = s.schema_id
'
FROM sys.databases d
ORDER BY d.name;
OPEN cur;
FETCH NEXT FROM cur INTO @cmd;
WHILE @@FETCH_STATUS = 0
BEGIN
    SET @AllCmds = CASE WHEN @AllCmds IS NOT NULL THEN @AllCmds + 'UNION ALL
' ELSE '' END + @cmd;
    FETCH NEXT FROM cur INTO @cmd;
END
CLOSE cur;
DEALLOCATE cur;
PRINT @AllCmds;
EXEC sys.sp_executesql @AllCmds;

The output contains a single row per referencing object.  If that referencing object includes column references, those column names will be listed in comma-delimited form under the ReferencedColumnNames column.


PgBouncer on separate server than PostgreSQL Right now I am hosting PostgreSQLand PgBouncer on the same Linode with 48GB of RAM and 12 Cores.

The server gets many short lived connections per second. The server load will increase with time and I want to reduce the server load as much as possible.

When I look at the 'top' command on the server, I see that PgBouncer is taking about 30% CPU usage. Would I benefit by creating another Linode and running PgBouncer on that server by itself?

The top command gives the CPU usage per core. Since you have 12 I would not create another Linode for that. Check the line with Cpu(s). There you will find the %id which is a better indication weather or not your server is using it's full CPU capacity.


Synchronize two MySQL databases between two servers Is there any way to connect a database on two servers inside a trigger?

I have 2 databases on 2 servers.  The first server is the primary, and the second server is the secondary.  I want to sync both databases with each other.

I've tried using a trigger but it looks like that can only update data in the same server, however I want to sync the data from server one to server two.

Is there any way to do that?

MySQL version :- 5.0.12 (10.1.36-MariaDB)

Purpose of replication: read-write and read data spritely (for performance)

Use replication. Triggers aren't needed.

There are replication filters and replicate_do_db will limit the replication to a single database. This operates differently depending on binlog_format=row or statement in the same way mysql does. For simplicity binlog_format=row gives the most expected behaviour.


Connecting to a new RDS instance from an EC2 instance I'm trying to use MediaWiki and WP to connect to two MySQL databases.
The databases are working properly and so are the forms but
when I try to establish a connection to the database with MediaWiki or WP,
I just get a DB connection error: Permission denied.

Some facts:

I'm able to...

Connect to RDS by MySQL Workbrench to add/remove a users/table/Schema
Connect to RDS by ec2 Command-line ( mysql -u MYUSER -p -h ENDPOINT )

I'm NOT able to...

Check my server logs in MySQL Workbrench or Command-line Could not acquire management access for administration. Exception: Unsupported administration target type: ('windows', 'linux', 'wmi')
Remove rdsadmin or Revert the asingeded roles and privileges Error changing account rdsadmin@localhost: Access denied on rdsadmin@localhost
Grant DBA/MaintenenaceAdmin/ProcessAdmin or ReplicationAdmin to WPuser Error changing account WPuser@%: Access denied for user 'WPuser'@'%' (using password: YES)

What i have done so far:

I added the Elastic/Private and even Public IP address for the EC2 instance to the VPC security group. as follows: xx.xx.xxx.xxx/32 and xxx.xx.xx.xxx/32. I'm Not useing EC2-Classic Platform, so there is no RDS security group as i'm seeing.

GRANT ALL ON%.* TO WPuser@%`; and GRANT index, create, select, insert, update, delete, drop, alter, lock tables on WIKIdb.* to
     'WIKIuser'@'ENDPOINT';
See the following picture to have a over view of the rules

Again, my questions are:

What i missed and why would be the solution to establish those applications to databases?

Also do you have anything in mind, how can i solve the "I'm NOT able to..." section to "I'm able to..." ?

I'd be grateful for any help on these

Check my server logs in MySQL Workbrench or Command-line Could not acquire management access for administration.

Right, you can't do that with workbench and RDS because that requires shell access, which RDS does not allow.  The logs are accessible through the console and API.

  Remove rdsadmin or Revert the asingeded roles and privileges

Right, you can't do that, because rdsadmin is the account that the RDS infrastructure uses to manage your instance.  You don't control it.

  Grant DBA/MaintenenaceAdmin/ProcessAdmin or Replication Admin to WPuser Error changing account WPuser@%: Access denied for user 'WPuser'@'%' (using password: YES)

The problem here is threefold.

The "roles" in workbench were a ridiculous idea on the part of Oracle because they do not correspond to real structures on MySQL... They're just fancy packaging for permission presets and you'd be better served to ignore that functionality and understand how the permissions actually work.

The DBA role doesn't work on RDS because that includes the SUPER privilege, which RDS does not provide, so that one wouldn't work anyway, even if you hadn't made the final error:

You appear to be trying to give "WPUser" permissions... while you're logged in as "WPUser."  You can't grant permissions to yourself.  You also can't grant permissions you don't hold.  If WPUser is the master user for your RDS instance, it already has all the permissions it can have... though you shouldn't use your master user for your application's access to the database.

  I added the Elastic/Private and even Public IP address for the EC2 instance to the VPC security group

That isn't likely the problem, here.  If the security group isn't right, you'll fail to connect, after a lengthy timeout, and the error code will not be "access denied."

Also potentially incorrect, depending on what you are using as 'ENDPOINT':

GRANT ... ON WIKIdb.* to 'WIKIuser'@'ENDPOINT';

If 'ENDPOINT' is the RDS endpoint, this is incorrect.  You can never connect to RDS from the RDS endpoint, or even from 'localhost'.

The final argument there needs to be the IP address of the server where the wiki code is running, or a wildcard for the netblock, like '172.31.%'.


Print Column Names for Synonym for Table in Another Database I am utilizing one database A. How do I print out the columns names for synonym for table in another database B?

create table DatabaseB.dbo.Customer
(
     CustomerId int primary key,
     CustomerName varchar(100),
     ZipCode varchar(9)
)

Answer if table was in Current Database A:

declare @ColumnList varchar(max) =
(select STUFF((
SELECT ',
    '
+ QUOTENAME(c.name)
FROM sys.columns c
where c.object_id = object_id('Customer')
FOR XML PATH(''), TYPE).value('.','nvarchar(max)'),1,2,''))

print @ColumnList

How to Print columns for table if in Database B?

use Database A;
go;

create synonym dbo.Customer for DatabaseB.dbo.Customer

declare @ColumnList varchar(max) =
(select STUFF((
SELECT ',
    '
+ QUOTENAME(c.name)
FROM sys.columns c
where c.object_id = object_id('dbo.Customer')
FOR XML PATH(''), TYPE).value('.','nvarchar(max)'),1,2,''))

print @ColumnList

I want have it use synonym in current database, (which already points to other database B). Purpose: To find columns for Customer, without specifying Database B

I provided a similar answer here which utilizes sys.dm_exec_describe_first_result_set.  Here is an example of how you can achieve your goal.

--demo setup
SET NOCOUNT ON
USE [master]
GO
--Create two sample databases
DROP DATABASE IF EXISTS [DB1]
DROP DATABASE IF EXISTS [DB2]
CREATE DATABASE [DB1]
CREATE DATABASE [DB2]
GO

--Connect to [DB2]
USE [DB2]
GO
--Create and load a table
CREATE TABLE [dbo].[CUSTOMER](
    [CustomerID] [int] NOT NULL,
    [FirstName] [varchar](20) NOT NULL,
    [LastName] [varchar](20) NOT NULL,
    [Email] [varchar](30) NOT NULL,
    [PhoneNo] [int] NOT NULL,
    [StreetAddress] [varchar](40) NULL,
    [City] [varchar](20) NULL,
    [OrderID] [int] NOT NULL
)
GO

--Connect to [DB1] and create a synonyn that points to a table in [DB2]
USE [DB1]
GO
CREATE SYNONYM DB2_CUSTOMER FOR [DB2].dbo.CUSTOMER
GO

--The solution
--Use sys.dm_exec_describe_first_result_set to retrieve the metadata for the synonym
DECLARE @cols NVARCHAR(MAX) = N'';

SELECT @cols += N',' + NAME + ' ' + system_type_name
FROM sys.dm_exec_describe_first_result_set(N'SELECT * FROM DB2_CUSTOMER', NULL, 1);

SET @cols = STUFF(@cols, 1, 1, N'');
SET @cols = replace(@cols,',',',' + char(10))
print @cols

Results:

CustomerID int,
FirstName varchar(20),
LastName varchar(20),
Email varchar(30),
PhoneNo int,
StreetAddress varchar(40),
City varchar(20),
OrderID int


Mongodb: Couldn't find mech GSSAPI I am trying to understand how mongodb works with kerberos. To do that I have configured a kerberos server, just only for local. This is the configuration of my realm:
this is the content of krb5.conf file:
[libdefaults]
    default_realm = LOCALHOST

# The following krb5.conf variables are only for MIT Kerberos.
krb4_config = /etc/krb.conf
krb4_realms = /etc/krb.realms
kdc_timesync = 1
ccache_type = 4
forwardable = true
proxiable = true

v4_instance_resolve = false
v4_name_convert = {
        host = {
                rcmd = host
                ftp = ftp
        }
        plain = {
                something = something-else
        }
}
fcc-mit-ticketflags = true

[realms]
    LOCALHOST = {
        kdc = localhost
        admin_server = localhost
}
[login]
    krb4_convert = true
    krb4_get_tickets = false

And this is the content of kdc.conf file:
[kdcdefaults]
    kdc_ports = 750,88

[realms]
    LOCALHOST = {
        database_name = /var/lib/krb5kdc/principal
        admin_keytab = FILE:/etc/krb5kdc/kadm5.keytab
        acl_file = /etc/krb5kdc/kadm5.acl
        key_stash_file = /etc/krb5kdc/stash
        kdc_ports = 750,88
        max_life = 10h 0m 0s
        max_renewable_life = 7d 0h 0m 0s
        master_key_type = des3-hmac-sha1
        supported_enctypes = aes256-cts:normal arcfour-hmac:normal des3-hmac-sha1:normal des-cbc-crc:normal des:normal des:v4 des:norealm des:onlyrealm des:afs3
        default_principal_flags = +preauth
    }

The kerberos seems to work fine. I created some principals (two users for test, one user for admin and one for the mongo server) and I tested that I can create tickets for each of them with kinit. This is the list of the principals:
ardi/admin@LOCALHOST
mongodb/localhost@LOCALHOST
testUser@LOCALHOST
testUser2@LOCALHOST

The problem comes when I trying to start the mongodb service with the GSSAPI mechanism. The service never starts and I get this message:

2015-03-10T12:52:12.285+0100 ERROR: Couldn't find mech GSSAPI
2015-03-10T12:52:12.286+0100 SEVERE: Failed global initialization:
BadValue SASL(-4): no mechanism available: Couldn't find mech GSSAPI

Of course, if I remove the authenticationMechanisms parameter, mongodb is started without no problems
Other information that could be useful:

The value of the krb5_ktname is /home/ardi/test/mongodb.keytab
The permissions of the keytab file are 777 (I know this is wrong but it is only for test)
My SO is ubuntu 14.04
I have this problem with the mongo enterprise versions 2.6.8 and 3.0.0

Does anyone of you have any idea where is the problem?
Thank you in advance for your time

I solved. The problem was that I did not have the library libsasl2-modules-gssapi-mit. Once I installed everything works fine


Controlled shutdown/startup of a cluster My three node SQL Server 2008 R2 physical server cluster needs to be shutdown, moved along with its storage to a new location, and restarted once in the new location. My desire is to have a clean, controlled shutdown/startup sequence and, if possible, for all SQL Server instances to remain on their original node during this process. So, if SQLInstA is on NodeA and SQLInstB is on NodeB and NodeC is empty, there is no failover during server shutdown and I’ll bring up the services individually upon startup.

I’ve read various recommendations regarding setting SQL Server offline, pausing nodes, stopping cluster services, setup startup types to manual.

My first thought was to set the SQLInstA and SQLInstB services offline in Failover Cluster Manager. Offline does not trigger a failover so they’ll stay put. But, rebooting will cause failover, regardless of my setting them to have only one preferred owner. The service doesn’t restart, even though I didn’t “disable auto start”, so even if both instances ended up on the same node, the services would be down on startup, which isn’t horrible.  Just manual failover work afterward.

I could stop the cluster services, but that triggers failover as well.  Is it important to stop cluster services in this case?

Any thoughts on how to accomplish this?

Shutting down the cluster for a move is pretty straight forward.

Open the failover cluster administrator.
Using the failover cluster administrator stop the resource groups for the SQL instances
Shut down two of the nodes.
At this point the cluster will fail and the cluster service will stop.
Shutdown the final node

To bring things back online.

Startup all the servers.
Start the cluster service if it didn't start automatically
Verify that the servers can see the storage correctly.
Startup the resource groups that host the SQL instances from within the Failover cluster administrator assigning them to the correct node of the cluster as you start them.

A word of advice, make sure that EVERY cable is properly labeled at both ends before you unplug anything. I've done this kind of move a few times.  A couple of hours of labeling the cables will save you hours of pain when getting things back up and running.


Delete SQL Server 2008 R2 server logins mapped to offline databases I have circumstances that a server can host many databases and a lot of them get offline and transfered elsewhere from time to time.
This causes lots of server logins to be created and stay there after the transfer.
I need a script to find all the server logins that are mapped to offline or non existing databases so I can clean them.

Thank you!

Here is a script to identify orphaned logins.

SELECT SL.name, SL.dbname
, 'USE [master];
ALTER LOGIN [' + SL.name + '] WITH DEFAULT_DATABASE=[master];
CREATE USER [' + SL.name + '] FOR LOGIN [' + SL.name + ']
WITH DEFAULT_SCHEMA = [dbo];' AS SQL_command
FROM sys.[syslogins] SL
LEFT JOIN sys.[databases] SD
ON SL.[dbname] = SD.[name]
WHERE SD.name IS NULL
ORDER BY SL.[name], SL.[dbname];

The full article can be found on http://sqlmag.com/database-administration/identifying-orphaned-logins


MySQL FullText search on string shorter than 3 chars returns no rows I have a MySQL table with some addresses in it. Let's say I have the following 2 rows:

"10 Fake Street"
"101 Fake Street"

I'm trying to use fulltext search with MATCH() AGAINST(). I have set ft_min_word_len to 1, rebooted the server and dropped and then rebuilt the index by running

ALTER TABLE addresses DROP INDEX address_index
CREATE FULLTEXT INDEX address_index ON addresses(street)

I have verified that my ft_min_word_len is indeed set to 1 by running

show global variables like 'ft_min_word_len'

If I include any word in my search that's shorter than 3 characters, I get no results back, unless I append a wildstar to it. For example

SELECT * FROM addresses WHERE MATCH(street) AGAINST('+101' IN BOOLEAN MODE)

or

SELECT * FROM addresses WHERE MATCH(street) AGAINST('+10*' IN BOOLEAN MODE)

both return 1 row "101 Fake Street". Running

SELECT * FROM addresses WHERE MATCH(street) AGAINST('+10' IN BOOLEAN MODE)

returns 0 rows. Why? The only suggestions I can find online all talk about setting min length, but I already verified that mine is set to 1 and rebuilt the index.

Setting ft_min_word_len only affects MyISAM.

You need to set innodb_ft_min_token_size to 1 since the default is 3.

Once you set innodb_ft_min_token_size to 1, go back and do

ALTER TABLE addresses DROP INDEX address_index
CREATE FULLTEXT INDEX address_index ON addresses(street);

Give it a Try !!!