Can i view per Database memory usage on SQL Server? I have a test server with a number of databases on, which is filling with memory
i would like to analyse which databases are claiming the memory, is this possible?

Just be aware that the suggested queries focuses on the Buffer Pool (cached data).
A database can have other types of memory usage as well, like exec plans (when database bound), working memory for queries, locks, etc. I don't know of a way to summarize those since many are, sort of indirect consequences of doing things inside a SQL Server, and some are not necessarily bound to a certain database.


Amazon SimpleDB or Amazon RDS? I'm at a deciding point in my project of whether to implement the database using SimpleDB or Amazon RDS. The database needs to be accessed through an iPhone app and as well through a website. Here are some factors I've been considering in the decision:

Amazon has an iOS SDK (Beta) for iPhone that can be used with their SimpleDB --
Getting Started with the AWS SDK for iOS(Beta)
Cost. SimpleDB is practically free while running a small RDS instance is $80/month or around $240/ year. If the price wasn't such a difference I would just choose the RDS and be done with it.
If SimpleDB is all that I require for my app then I am fine using that, but I do not know enough about databases yet (I just began my Database I course today) to decide if it has the functionality I need.

The other thing is that I've already built the website using PHP/MySQL and I really like how easy it is to use this combination so having to re-build it using SimpleDB is kind of annoying.

I think that I should probably just pay the cost for a year's worth of an RDS instance and just be done with it, but I just wanted to see what other people have to say about this.

Well it's really a matter of you deciding on the architecture you want to go with.

SimpleDB is a non-relational "no-sql" data storage system built around the ideas of collections of items accessible via key/value pairing.

Where as RDS is really just a custom version of MySQL which a fully relational SQL database.

both systems will work for tracking profiles but the implementation patterns are very different. Neither is right or wrong, so it's your call on what you want to do.


Myloader command: Table doesn't exist I took the backup of mysql database using mydumper command:

  mydumper -B mydb -o /backup/oct3 -s 500000 -c -m -S /tmp/mysql.sock -u
  root -p password@123 -v 3

Now, I am restoring mysql database backup with the myloader command:

  myloader -d /backup/oct3 -S /tmp/mysql.sock -u root -p password@123 -v
  3

ERROR:

  CRITICAL **: Error restoring mydb.tabe1 from file mydb.table1.sql.gz:
  Table 'mydb.table1' doesn't exist
  ** Message: Thread 2 shutting down

I believe the table should be created while restoring the database.

But I could not figure out what I missed. The mydumper version is 0.6.2.

My.cnf file:

innodb_buffer_pool_size = 2308M
innodb_data_home_dir = /database
innodb_data_file_path = ibdata1:1G;ibdata2:1G;
innodb_log_file_size=1073741824
innodb_log_files_in_group = 3
innodb_flush_method=O_DSYNC
innodb_file_per_table = 0

The dumped .gz files are from MySQL 5.5 and I am restoring to MySQL 5.6.

Does it matter of version incompatibility?

Any suggestions will be appreciated. Thanks.

problem is you backed up your databases using mydumper with -m or --no-schemas option, so the database create commands were not backed up.

So on restore you are not able to re-create the database tables prior to myloader reimporting data.


Can an Oracle 19c database dblink to an Oracle 10.2.3 database? I have an Oracle 12.1.0.2 database that dblinks to an Oracle 10.2.0.3.0 database.
I am upgrading the Oracle 12.1.0.2 database to 19c (12.2.0.3).
Will my dblinks to the Oracle 10.2.0.3.0 database continue to work?

Based on this document from Oracle Support, the answer appears to be "No". A 19c client (the owner of your DB Link) is only supported/compatible with a server version of 11.2 or greater.
Conversely, a 10.2 database can only support a client as new as 12.1, so any upgrade of your 12.1 database to any newer version will break your db link.


MySQL PDO Cannot assign requested address Can someone help me with this error?

[08-Apr-2013 17:44:08 Europe/Berlin] PHP Warning:  PDO::__construct(): [2002]
    Cannot assign requested address (trying to connect via tcp://****:3306) in
    /var/www/***
[08-Apr-2013 17:44:08 Europe/Berlin] PHP Fatal error:  Uncaught exception
    'PDOException' with message 'SQLSTATE[HY000] [2002] Cannot assign requested
    address' in /var/www/***

I have a Server with a lot connections per second; out of about 100 Connections, a single one got this error.

I've tried this recommendation from stackoverflow however it does not solve my problem.

You could also be hitting max open files (sockets) under Linux. You can change this in /etc/security/limits.conf.


How to enforce lowercase for all data in specific column? I have this table.

CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  name TEXT
);

I'm looking for a way to enforce lowercase for the name values.

For example, when I save a record using this command:

INSERT INTO users (name) VALUES ('JOHN');

or:

INSERT INTO users (name) VALUES ('John');

I want it to be saved as john without converting the case beforehand using my programming language i.e. JavaScript, Ruby and etc.

Is it possible to make use of LOWER() function inside this CREATE TABLE command?

You need a trigger for that:

create function make_lower_name()
  returns trigger
as
$$
begin
  new.name := lower(new.name);
  return new;
end;
$$
language plpgsql;

create trigger ensure_lower_name_trg
   before update or insert on users
   for each row
   execute procedure make_lower_name();


How to properly add IPv6 data of GeoLite2 into existing table? I'm building an application which will use MaxMind's GeoLite2 City database.

By following a 2016 tutorial, I imported the data of GeoLite2-City-Blocks-IPv4.csv and GeoLite2-City-Locations-en.csv and also added the helper procedure. Now I can successfully query the location information of IP address which are IPv4.

But I still need to be able to query the location information of IP address which are IPv6. Can I use the same code from the tutorial (shown below) to add the data of IPv6 into the existing table without messing things up? (I will change the file path to GeoLite2-City-Blocks-IPv6.csv)

LOAD DATA LOCAL INFILE '/root/geoip/GeoLite2-City-CSV_20160503/GeoLite2-City-Blocks-IPv4.csv' INTO TABLE ip_blocks COLUMNS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' IGNORE 1 LINES (
@network,
geoname_id,
registered_country_geoname_id,
represented_country_geoname_id,
is_anonymous_proxy,
is_satellite_provider,
postal_code,
latitude,
longitude,
accuracy_radius) SET
ip_from = INET_ATON(SUBSTRING(@network, 1, LOCATE('/', @network) - 1)),
ip_to = (INET_ATON(SUBSTRING(@network, 1, LOCATE('/', @network) - 1)) + (pow(2, (32-CONVERT(SUBSTRING(@network, LOCATE('/', @network) + 1), UNSIGNED INTEGER)))-1));

To directly answer your question, no, you can't use the exact same code from that tutorial with the IPv6 version.

Here are a few reasons:

The LOAD DATA LOCAL INFILE query that inserts the data uses MySQL's INET_ATON() function, which won't work with IPv6. Thankfully MySQL has another function INET6_ATON() which supports both IPv4 and IPv6, although it does return a different datatype, VARBINARY(16) instead of integer. Which brings me to point #2...
In the tutorial, the ip_blocks table is created with the ip_from and ip_to columns as int, but as mentioned above, you need VARBINARY(16) to be compatible with IPv6.
The LOAD DATA LOCAL INFILE query also does some integer math to calculate a min  and max IP address based on the CIDR range in the CSV. This won't work with the binary format returned from the INET6_ATON() function.

Most of these obstacles stem from the fact that the IPv6 address space is much larger than the IPv4 space (128 bits versus 32 bits), which means IPv6 can't be stored as a normal integer. So you will need to modify the code and underlying storage quite a bit to make it work.

You can probably use MaxMind's converter tool to get a min and max IP address from the IPv6 range in the CSV, then use the BETWEEN keyword to find IP addresses in that range. See this question for background info: https://stackoverflow.com/questions/42414946/check-if-ipv6-is-inside-range


Rollback Committed transaction in SQL Server Is it possible to rollback a committed transaction?

I have deleted all the data in a table. The delete statement is inside Begin Transaction and I have committed the transaction with a commit transaction command. Now is it possible to rollback and get the data back?

No. Committed transactions modify the contents of the database tables first in the transaction log, then in the data files. Unless triggers or other techniques are explicitly set up upfront, there is no way to recover the values stored in the database before the transaction was committed.

In order to do that, you have to restore a copy of the database before the transaction ended.

Other techniques rely on reconstructing the data reading the transaction log, but I don't recommend them: restoring a copy of the database is much easier (and supported).


Should I put users in admin DB or in the DB that they should have access to What would the pros / cons be of putting users that should have access to one database in the admin database instead of the database that they should have access to?

Background:

I'm a C# developer. This is the first live project using mongodb I'm working on.

I would like to know from people used to managing mongodb instances how they go about setting up users.

In this case I want three users, one for administration, one "webuser" that the intranet web application which I develop will use to read and write documents in one database and a reader user used when read access is enough. Both the readwrite and the reader user just need acces to one database. The admin user should be admin of the mongoserver. The database will be hosted on the server that hosts the web

in general not just Mongo talking in general given root/sysadmin privileges or adding all users to the admin groups is not recommended due to obvious reasons. And in mongo like advanced databases there are enriched security groups and roles can be created and assign each user as per the requirement. please refer the mongo documentation on creating user roles and assigning privileges.
https://docs.mongodb.com/manual/tutorial/manage-users-and-roles/


Can db_datareader user block ALTER TABLE statement? db_datareader user tries to update and gets an error:

BEGIN TRAN

UPDATE [TestDB].[dbo].[Table_1]
  SET Col1 = 'Test';

   Msg 229, Level 14, State 5, Line 3
The UPDATE permission was denied on the object 'Table_1', database 'TestDB', schema 'dbo'.

User doesn’t close session.

User with enough rights tries to alter the table afterwards and gets blocked:

BEGIN TRAN

ALTER TABLE [TestDB].[dbo].[Table_1]
ADD Col2 INT;

Executing query ..

Is this normal behavior?

Yes. The first query has left an open transaction. SQL Server doesn't check if the current user has permissions at compile time. It will find out at run time. If you were writing a stored procedure you wouldn't want to check permissions at compile time as a different user may execute the procedure.

Wrapping the UPDATEs, in both queries, in a TRY CATCH would avoid leaving an open transaction.

BEGIN TRY
    BEGIN TRAN

    UPDATE [TestDB].[dbo].[Table_1]
      SET Col1 = 'Test';

    COMMIT TRAN
END TRY
BEGIN CATCH
    IF @@TRANCOUNT > 0
        ROLLBACK;

    ;THROW;
END CATCH


drop big table freeze server I am using amazon rds mysql 5.6 master instance and 4 slaves.
there is big table with 800 GIB size that i want to drop it for releasing  space. But when i execute drop command, the server entering to "freeze mode" and cant execute any kind of commands.

similar issue : https://bugs.mysql.com/bug.php?id=44757

I need to remove it from production rds.

there is no transactions on this table
innodb_file_per_table  = ON

I though about Deleting rows in chunks for not create big lag for slaves and then (optional optimize) drop the table.

What exact version?

DELETE on a huge table will take a huge amount of time because it is building an "undo" log in case of a crash.  Don't use that technique.

What was the value of innodb_file_per_table when the table was built?

If OFF (or 0), the table is in ibdata1, which will not shrink and return space to the OS.  It will only free up blocks for subsequent use.
If ON (or 1), then DROP TABLE will be much faster, but still not instantaneous.
If you have FOREIGN KEYs connecting the table (in either direction), well, let's see them.  (SHOW CREATE TABLE)

If you need to delete a big part of a table, here are several techniques.  This discusses how to do the delete in chunks, and takes Replication issues into account.


Get current time, on commit, with oracle database When inserting a row I want to make a column, equal to the current time on commit.

If I use default value sysdatetime on the column, it uses the current time at insert (which could be significantly different to commit time). If I use an on insert trigger, it again uses the time at the insert - not the actual commit.

Are there any possible solutions to get current time on commit with oracle ?

To get an approximate value (+/- 3sec or so) you may use ORA_ROWSCN

You must define the table with ROWDEPENDENCIES to have the SCN stored for each row.

Here an simple example:

create table t1
(c1 number,
c2 date)
ROWDEPENDENCIES;

insert into t1 values(1,sysdate);
-- wait 5 sec
commit;

insert into t1 values(2,sysdate);
-- wait 10 sec
commit;

SELECT ORA_ROWSCN, SCN_TO_TIMESTAMP(ORA_ROWSCN), c1,c2 FROM t1;

ORA_ROWSCN SCN_TO_TIMESTAMP(ORA_ROWSCN)         C1 C2
---------- ---------------------------- ---------- -------------------
   1.4E+13 19.05.16 19:14:15,000000000           1 19.05.2016 19:14:09
   1.4E+13 19.05.16 19:14:30,000000000           2 19.05.2016 19:14:21


List all database containing table and value in one of our SQL Servers we have several databases attached and most of them contain a little table, let's say dbo.DBType and that table will always contain 1 record with two columns, first column tells me if the DB is of type A or B (only A and B are allowed).

is there a way I can query the master db of SQL Server and retrieve a list of all databases of type A or type B ?

I know I could connect inside a loop to each of them and check the containewd DBType table but was thinking

I'm guessing on the first column - replace ColumnName below with the actual column name. Also it's always useful to specify which version of SQL Server you are using - I've assumed 2005.

DECLARE @DBType CHAR(1);
SET @DBType = 'A'; -- or 'B';

DECLARE @sql NVARCHAR(MAX);
SET @sql = N'';

CREATE TABLE #x(DB SYSNAME);

SELECT @sql = @sql + 'IF EXISTS (SELECT 1 FROM '
  + QUOTENAME(name) + '.sys.tables WHERE name = ''DBType'')
  INSERT #x SELECT ''' + name + '''
    FROM ' + QUOTENAME(name) + '.dbo.DBType
    WHERE ColumnName = ''' + @DBType + ''';'
FROM sys.databases;

EXEC sp_executesql @sql;

SELECT DB FROM #x;

DROP TABLE #x;


Insert to particular location in Oracle DB table? Suppose I have a table containing the following data:

Name | Things
-------------
Foo  |   5
Bar  |   3
Baz  |   8

If I want to insert a row, so that the final state of the table is:

Name | Things
-------------
Foo  |   5
Qux  |   6
Bar  |   3
Baz  |   8

Is this possible?

I understand we don't typically rely on the order of rows in a table, but I've inherited some code that does precisely that. If I can insert to a location, I can avoid a significant refactor.

The code you have inherited is broken - and it always has been broken. That re-factoring you'd like to avoid needs to be done. There is no alternative to an explicit order by to guarantee the sort order of a result set and there never has been.

Who knows if the code you inherited always returned rows in the order the original developer 'expected' or not.


CATCH block is triggered when it should not On my neverending quest for shooting myself in the foot with save transaction I seem to have found even more ways to fulfill the quest purpose. The save transaction clause itself this time is out of the question, but it is because of it I wrote the code below.

Consider the following complete example with nested error handlers:

begin try

  begin try
    select 'Step 1';
  end try
  begin catch
    select 'Step 1 handler - handling ''' + error_message() + '''';
    goto commit_and_exit;
  end catch;

  begin try
    select 'Step 2';
    raiserror('Step 2 error', 16, 1);
  end try
  begin catch
    select 'Step 2 handler - handling ''' + error_message() + '''';
    goto commit_and_exit;
  end catch;

end try
begin catch
  select 'Outer handler - handling ''' + error_message() + '''';
  goto commit_and_exit;
end catch

commit_and_exit:

raiserror('Error raised for the caller to see', 16, 1);

It is documented that

  GOTO statements can be used to jump to a label inside the same TRY or CATCH block or to leave a TRY or CATCH block.

Or can it?

Given the code above, a sane programmer would figure the output will be

  Step 1
  Step 2
  Step 2 handler - handling 'Step 2 error'
  <Error raised for the caller to see>

In fact what is happening is:

  Step 1
  Step 2
  Step 2 handler - handling 'Step 2 error'
  Outer handler - handling 'Error raised for the caller to see'
  <Error raised for the caller to see>

When debugging step by step, I can see that control leaves the try/catch blocks completely, then an error is raised, the control is returned to the outermost catch block, that block executes, the control goes to commit_and_exit: again, and that final block is executed again.

If you have some commit trans or rollback transs under commit_and_exit:, you are going to attempt to commit the tran twice. With imaginable results, given the fact there can be outer transactions started by the caller.

I also tried create another label, end_of_outer:, right before the outer end try, so that the control leaves the outer try block "normally". Interesting enough, that did not make any difference.

What the hell is going on, and what is the correct way of doing it?

It looks like the pre SQL Server 2012 behaviour is a bug.

I infer this from this Connect article:

https://connect.microsoft.com/SQLServer/feedback/details/712003/sql-server-2012-error-in-exception-handling-mechanism

I could be wrong of course...


How to remove earliest/latest row by two columns (userId, StatusCheck)? I have crosschex database that is used to store logged in/out data. Sometimes it happens that user clock in/out few times. I would like to remove that duplicates leaving the earliest when log in and latest when log out.
Here is what I have:

USE crosschex;
SELECT UI.Name, INOUT.UserId, INOUT.CheckTime, S.StatusText
FROM dbo.Checkinout AS INOUT
INNER JOIN dbo.Userinfo AS UI ON
INOUT.Userid = UI.Userid
INNER JOIN dbo.Status AS S ON INOUT.CheckType = S.Statusid
WHERE
    UI.Groupid = 1 AND INOUT.CheckTime >= DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE())-3, 0) AND INOUT.CheckTime < DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE())-3, 1)

Here is the result:

"Zeab Simon"    "6" "2018-05-04 07:58:06.000"   "In"
"Zeab Simon"    "6" "2018-05-04 07:58:15.000"   "In"
"Zeab Simon"    "6" "2018-05-04 07:58:38.000"   "In"
"Kate B"    "4" "2018-05-04 14:15:45.000"   "In"
"Kate B"    "4" "2018-05-04 14:15:46.000"   "In"
"Zeab Simon"    "6" "2018-05-04 16:34:46.000"   "Out"
"Zeab Simon"    "6" "2018-05-04 16:34:48.000"   "Out"
"Zeab Simon"    "6" "2018-05-04 16:34:49.000"   "Out"
"Zeab Simon"    "6" "2018-05-04 16:34:50.000"   "Out"
"Zeab Simon"    "6" "2018-05-04 16:34:51.000"   "Out"
"Patty" "5" "2018-05-04 20:31:36.000"   "Out"

What I would like is to remove the earliest and lattest for each userId and StatusText In/Oou to get that:

"Zeab Simon"    "6" "2018-05-04 07:58:06.000"   "In"
"Kate B"    "4" "2018-05-04 14:15:45.000"   "In"
"Zeab Simon"    "6" "2018-05-04 16:34:51.000"   "Out"
"Patty" "5" "2018-05-04 20:31:36.000"   "Out"

How can I do it?

To answer this question, I did the following.

CREATE TABLE shift (worker_name VARCHAR(25), worker_id INTEGER, shift_time TIMESTAMP, in_out VARCHAR(3));

INSERT INTO shift VALUES ('Zeab Simon', 6, '2018-05-04 07:58:06.000', 'In');
INSERT INTO shift VALUES ('Zeab Simon', 6, '2018-05-04 07:58:15.000', 'In');
INSERT INTO shift VALUES ('Zeab Simon', 6, '2018-05-04 07:58:38.000', 'In');
INSERT INTO shift VALUES ('Kate B',     4, '2018-05-04 14:15:45.000', 'In');
INSERT INTO shift VALUES ('Kate B',     4, '2018-05-04 14:15:46.000', 'In');
INSERT INTO shift VALUES ('Zeab Simon', 6, '2018-05-04 16:34:46.000', 'Out');
INSERT INTO shift VALUES ('Zeab Simon', 6, '2018-05-04 16:34:48.000', 'Out');
INSERT INTO shift VALUES ('Zeab Simon', 6, '2018-05-04 16:34:49.000', 'Out');
INSERT INTO shift VALUES ('Zeab Simon', 6, '2018-05-04 16:34:50.000', 'Out');
INSERT INTO shift VALUES ('Zeab Simon', 6, '2018-05-04 16:34:51.000', 'Out');
INSERT INTO shift VALUES ('Patty',      5, '2018-05-04 20:31:36.000', 'Out');

I then ran the following query:

SELECT worker_name, MIN(shift_time), in_out
FROM shift
WHERE in_out = 'In'
GROUP BY worker_name, in_out
UNION
SELECT worker_name, MAX(shift_time), in_out
FROM shift
WHERE in_out = 'Out'
GROUP BY worker_name, in_out
ORDER BY worker_name

Which gives:

worker_name,         shift_time,   in_out
Kate B       2018-05-04 14:15:45       In
Patty        2018-05-04 20:31:36      Out
Zeab Simon   2018-05-04 07:58:06       In
Zeab Simon   2018-05-04 16:34:51      Out

A cursory inspection reveals that these are the correct times - earliest clock_in and latest clock_out. A db-fiddle is available here. To get the best answers to your questions, you might consider giving a fiddle in the question itself - help  us to help you!

Following the OP's clarifications in comments the need to delete extraneous records from the same table, I used the result above in a CTE (Common Table Expression - AKA the WITH clause) to delete the other records from the main shift table as follows.

WITH cte AS
(
  SELECT worker_name, worker_id,
  MIN(shift_time) as shift_time, in_out
  FROM shift
  WHERE in_out = 'In'
  GROUP BY worker_name, worker_id, in_out
  UNION
  SELECT worker_name, worker_id,
  MAX(shift_time) as shift_time, in_out
  FROM shift WHERE in_out = 'Out' GROUP
  BY worker_name, worker_id, in_out
)
-- select * from cte  -- testing
DELETE FROM shift
WHERE shift_time NOT IN
(
  SELECT shift_time FROM cte
);

And then a simple SELECT in the fiddle shows that the correct result has been achieved.

Another solution was kindly provided by @ypercube here - worth looking at for learning purposes. It is actually (no surprise here) more correct. My SQL will not work properly if there are exactly matching times for a MAX(or MIN) and a non significant value!


Blocking SPID has Zero Disk IO and Zero CPU Time Running sp_who2 and following the BlkBy trail up to the root cause of blocking returns a SPID which has CPUTime and DiskIO values of 0; yet it's blocking 4 other SPIDs.

I'm confused by how this is possible; the CPUTime in particular seems odd as in order to obtain a lock you'd have had to have spent some time requesting resources / requesting the lock itself.
CPUTime is in milliseconds, so whilst it's possible that the requesting and locking of resources occurs fast enough to have a rounded down value here, that is a little surprising.

Also, these SPIDS are sometimes a few minutes old; yet appear to have done nothing beyond cause blocking.

Question

How is it possible for a SPID to cause blocking whilst having zero CPU Time?

I'm asking as I suspect something's lacking in my understanding of the CPU Time stat.  If anyone can advise on sensible steps to aid in investigating such issues though that would also be useful.

This can sometimes happen when applications are using implicit transactions

It doesn't help that sp_who2 is really confusing -- Aaron is right that you're better off with sp_WhoIsActive or sp_BlitzWho to find running queries.

One dead giveaway of implicit transactions is seeing the query text like begin tran or IF @@TRANCOUNT > 0.

This can also happen when queries are using explicit transactions, of course. It's just far easier to catch those in code.

As to why CPU time is very low -- well it doesn't take much CPU to do an index seek to one row and lock it, then cause problems for any other queries that also want that row.


SQL Server 2012 Aggressive Over-Indexing when i run sp_BlitzIndex I observed blocking and time outs in one of our production server and there is prod outage to one of the application continuously,

When i run sp_BlitzIndex i got Aggressive Over-Indexing, ran script in more info script it didn't find any missing indexes,

  Aggressive Over-Indexing: Total lock wait time > 5 minutes (row + page) with long average waits

The Create Table script contains below ALTER TABLE script..

  ALTER TABLE [schema].[table] ADD CONSTRAINT [Primary_Key_Index] PRIMARY KEY ( [ID] ) WITH (FILLFACTOR=90, ONLINE=?, SORT_IN_TEMPDB=?, DATA_COMPRESSION=?);

In this table Primary Key is non clustered Index and Non_Clustered_Index is primary key

Can anyone suggest how do i clear this over indexing?

Thanks a ton in Advance

In my post How to Fix sp_BlitzIndex Aggressive Indexes Warnings, I note that:

  Aggressive Over-Indexing: when there’s a lot of blocking on a table with 10+ indexes. Your DUI queries are probably getting held up by acquiring locks across many of these indexes to update ’em.

Over-indexing means your table likely has too many indexes. You probably don't want to add more - you probably want to dig more deeply into the indexes that already exist on the table, and start getting rid of the unused and duplicate indexes.

If you'd like, you can post the output of sp_BlitzIndex for your particular table. Scroll across to the right of your sp_BlitzIndex output, and you can see a More Info query. Run that, and sp_BlitzIndex will list out your existing nonclustered indexes. That's what we'd need to help you reduce the indexing overhead.

Update 2018-10-09: thanks for adding the sp_BlitzIndex output. Based on that, I can see that your clustered index is on 3 separate fields, and it's not a unique clustered index either. Generally speaking, this isn't a great clustered index design because the 3 clustering keys (TREE_ID, LEFT_VISIT, and RIGHT_something) are included in every single nonclustered index as well. (That's what the Secret Columns show.) That makes your nonclustered indexes larger than they need to be.

In terms of the rest of the indexes, it's time to start making some tough decisions: do you really need 4 copied subsets of the table (indexes) that all start with PRJ_ID? Do you really need a 6-key-field index that starts with TREE_ID, LEFT_VISIT (the same two fields that your clustered index starts on)? Those are the questions I'd start asking about the indexes to get down to a lower number.

If you can't reduce the number of indexes, then your options are:

Tune the locking/blocking queries so they run faster (like shorten their transactions), or
Throw hardware at it - if the blocking queries are held up by slow server power, then you can get them to finish faster this way, but it's not a great solution

Hope that helps!


Logins mapped to dropped database sql server I was told to delete the logins that are mapped to a database ABC but unfortunately the database ABC has been dropped and no backups exist.

Is it possible to find the logins that WERE mapped to ABC?

Try the following query:

SELECT * FROM syslogins WHERE dbname = 'ABC'

If there are logins still using that as their default database, this should help you find them.  However, if the users had a default database of master and the application performed a USE ABC command, then you'll have no way of knowing they were tied to the application.  It's only if the application was kind enough to create your SQL logins with a default database of 'ABC' will you be able to track things down.


Converting huge ASCII file to flat and convert I am not a db administrator but I thought to ask this question here.

I have several very large ASCII files (3-5GB each) that I need to extract the data and then sort it.  The instructions say to "extract the zip file into a flat file" and then to "create a program to convert that flat file to whatever application the client chooses to use".

I am not sure if this is something that is simple to do, or if I should hire a database pro to do this.  I am familiar with the basic workings of SQL but maybe enough to get into trouble.

So far I have used something called gVim to look at the files, but they are not delimited in any way except for spaces.

Could someone give me advice on this?
Thanks!

When using a databse to sort the data one needs to know the structure of the data. In other words, there must be a field delimiter and a row delimiter.
Then you can import the file into a SQL-table and index it and sort it the way you want. Importing flat files is easy done with tools provided by any Database Engine, for example SQL Server Integration Services (SSIS) or MySQL LOAD.


You can use a subquery and a GROUP_CONCAT for that
Schema (MySQL v8.0)
CREATE TABLE Table1 (
  `FieldID` INTEGER,
  `FieldName` VARCHAR(3),
  `FieldAccount` INTEGER
);

INSERT INTO Table1
  (`FieldID`, `FieldName`, `FieldAccount`)
VALUES
  ('01', 'Jon', '12345');

CREATE TABLE Table2 (
  `FieldID` INTEGER,
  `FieldName` VARCHAR(7),
  `FieldAccount` INTEGER
);

INSERT INTO Table2
  (`FieldID`, `FieldName`, `FieldAccount`)
VALUES
  ('01', 'Jon', '12345'),
  ('02', 'Charlie', '12345');

Query #1
SELECT
t1.*
,(SELECT GROUP_CONCAT(`FieldID`) FROM Table2 WHERE `FieldAccount` = t1.`FieldAccount`
) 'LookAt '
FROM Table1 t1;

| FieldID | FieldName | FieldAccount | LookAt  |
| ------- | --------- | ------------ | ------- |
| 1       | Jon       | 12345        | 1,2     |

View on DB Fiddle


Does my design adhere to 3NF? I created this schema with OpenOffice.

Does this design adhere to 3NF ?

(A relationship link between Equipment and Supplier_Equipment , Customer and Membership should be created but OpenOffice isn't allowing me to do so.)

Yes, this conforms to 3NF as far as I can see.

Basically 3NF is met when all non-key values (assuming natural keys here) in relations are non-transitively functionally dependent on the key.  Surrogate keys don't count for this analysis since category.name is not functionally dependent on category.category_id.  In fact, category.name is probably your natural key and category.category_id is functionally dependent on it for purposes of this analysis (because it is in essence a join dependency stand-in or surrogate for your natural key).  Going through your tables here I do not spot any cases where this is violated.

The one obvious change that this suggests is what was mentioned in the comments by others, to move customer_id into hire and out of hire_equipment (if third party loans of equipment are supported you probably want customer_id in both places though).


Changing MySQL auto_increment_offset Sorry for the wall of text, but I don't know what's relevant and what's not.

I have MySQL a MySQL database that it is being replicated from our production environment to our development/test environment so that they get real-time updates for the DEV side of the web application. The problem is that DEV wouldn't really be DEV if they couldn't change stuff and submit it. It's one-way so and changes to DEV don't replicate back to prod, but this creates an issue where if they create new entries in the DEV database, they stay local to DEV but have to potential to have their AUTO_INCREMENT id numbers clash should the numbers be re-used in PROD (which won't see the same value for the next available index number since it's blind to what's going on in DEV).

I thought I could resolve this clashing by setting auto_increment_offset at the GLOBAL level so that new database entries get ID's in the 30,000+ range whereas PROD stays below 30,000.

I set the variable on the slave, created the table on the master, filling it with values 1-4 on the master, which get replicated to the slave, but subsequent attempts to INSERT data on the slave, have it pick up where the master DB left off.

mysql> describe test;
+-------+------------------+------+-----+---------+----------------+
| Field | Type             | Null | Key | Default | Extra          |
+-------+------------------+------+-----+---------+----------------+
| id    | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
+-------+------------------+------+-----+---------+----------------+
1 row in set (0.00 sec)
mysql> select * from test;
+----+
| id |
+----+
|  1 |
|  2 |
|  3 |
|  4 |
+----+
4 rows in set (0.00 sec)
mysql> SHOW VARIABLES LIKE 'auto_inc%';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| auto_increment_increment | 1     |
| auto_increment_offset    | 30000 |
+--------------------------+-------+
2 rows in set (0.00 sec)

mysql> insert into test () VALUES ();
Query OK, 1 row affected (0.00 sec)

mysql> select * from test;
+----+
| id |
+----+
|  1 |
|  2 |
|  3 |
|  4 |
|  6 |
+----+
5 rows in set (0.00 sec)

mysql> delete from test where id=6;
Query OK, 1 row affected (0.00 sec)

I've searched online and can't really find anything. It seems auto_increment_offset was probably created for this sort of situation but it doesn't seem to be having the effect I would anticipate (that the slave generates ID's that are a lot higher than what's in the Master). Is this saved table-level somewhere?

Any level of help would be appreciated.

You find the explanation of your question in the docs: "If the value of auto_increment_offset is greater than that of auto_increment_increment, the value of auto_increment_offset is ignored."

For your purpose it would be sufficient to leave auto_increment_increment and auto_increment_offset on their default values and simply run:
ALTER TABLE test AUTO_INCREMENT=30000 on the slave.

This will result in values like 30001,30002,...,N on your slave.


MySQL 5.6 Bug Amazon RDS with replica. AFTER INSERT TRIGGER is not working. Solutions? I have MasterDB and ReplicaDB in Amazon RDS environment. I need to move 1 table (MYTable) from replica to other external DB (ExternalDB).

My solution (do not work) was to add a AFTER TRIGGER listening for UPDATES and INSERTS to MYTable (and only add that TRIGGER in ReplicaDB) and copy everything on MyTableLog. Them pooling MyTableLog (and remove already processed records).

Problem: It looks like the RDS replica it is not firing the AFTER INSERT event (is only firing AFTER UPDATES). However I tested the solution in 5.7 and it worked.

Any ideas? It is a bug in MySQL? Any solution?

UPDATES:

1- I'm adding a new trigger on SlaveDB (it is not a trigger replicated from MasterDB)

2- It is working in 5.7 -> 5.7 ... the issue is in 5.6 -> 5.6 MySQL DB

3- Im adding TWO triggers (both works in 5.7 but only 1 works in 5.6)

4- Update Trigger (works in 5.6 and 5.7)

CREATE TRIGGER after_mytable_update
  AFTER UPDATE ON mytable
FOR EACH ROW
BEGIN
  INSERT INTO mytable_log
  SET is_new = 0, is_processed = 0;
END

5- Insert Trigger (DO NOT WORK IN MYSQL 5.6)

CREATE TRIGGER after_mytable_insert
  AFTER INSERT ON mytable
FOR EACH ROW
BEGIN
  INSERT INTO mytable_log
  SET is_new = 1, is_processed = 0;
END

As simple as it is. The INSERT trigger it **IS NOT being executed in MySQL 5.6**

(From Comment)

CREATE TABLE mytable_log_replica (
    replica_id int(11) unsigned NOT NULL AUTO_INCREMENT,
    is_new int(11) DEFAULT '0',
    is_processed int(11) DEFAULT '0',
    id int(11) unsigned NOT NULL,
    stamp datetime DEFAULT NULL,
    user_id int(11) DEFAULT NULL,
    name varchar(64) DEFAULT NULL,
    address varchar(64) DEFAULT NULL,
    transaction_status varchar(64) DEFAULT NULL,
    ip varchar(64) DEFAULT NULL,
    cb_code varchar(16) DEFAULT NULL PRIMARY KEY (replica_id)
) ENGINE=InnoDB AUTO_INCREMENT=240878 DEFAULT CHARSET=latin1

According Paragraphs 1-3 of MySQL Documentation Replication and Triggers

  With statement-based replication, triggers executed on the master also execute on the slave. With row-based replication, triggers executed on the master do not execute on the slave. Instead, the row changes on the master resulting from trigger execution are replicated and applied on the slave.

  This behavior is by design. If under row-based replication the slave applied the triggers as well as the row changes caused by them, the changes would in effect be applied twice on the slave, leading to different data on the master and the slave.

  If you want triggers to execute on both the master and the slave—perhaps because you have different triggers on the master and slave—you must use statement-based replication. However, to enable slave-side triggers, it is not necessary to use statement-based replication exclusively. It is sufficient to switch to statement-based replication only for those statements where you want this effect, and to use row-based replication the rest of the time.

MySQL default for binlog_format in 5.6 is STATEMENT and ROW in 5.7. You will have to set that in the DB Parameter Group. You may need to convert the binlog_format to STATEMENT on RDS Master, RDS Slave, and the External Slave. Only the can you configure a trigger on the External Slave.

Interestingly, MariaDB has in its Documentation Running Triggers on the Slave for Row-based Events. You may have to switch to MariaDB.


Does view take advantage of table indexes I have a table with a clustered index and 2 non clustered index. Now, in my view I am selecting everything from my table

create view dbo.MyView
as
Select * from MyTable

Now, when I am using this view in my queries may I assume that this view will take advantage of the indexes created on the table. (i.e. work in the same way as Select * from MyTable should work)?
or should I have to use Indexed views

Addition to the question

If view can take advantage of the table index, then using SELECT * FROM MyView Where SomeColumn = @someValue may impact the performance badly (if view has large amount of data , so it will behave like a heap when not indexed).
How to overcome this issue ? other than using NOEXPAND (because I am using Enterprise edition)

A view is just a "saved query". The indexes on the base table are still used whenever you access the view.

You don't need to use an indexed view, unless the view contains an expensive logic (aggregations or joins) that you don't want to perform each time you query the view. Please note that even when the view is "materialized", the optimizer is free to access the base tables and their indexes directly and ignore the index on the view altogether.

Also note that keeping the index on the view aligned with the data in the base tables is expensive: every time you write to one of the base tables you have to update the index on the view as well.


Comparing DISTINCT, GROUP BY and ROW_NUMBER() in SQL Server 2008 with data warehouse workloads I recently heard the advice: "If you're using DISTINCT, I challenge you to fix your code."  However, I'm wondering what considerations are behind this advice, and whether it means that I should approach the following problem differently.

Problem context

I have a denormalised date dimension table (Kimball star schema).  In this table, I have collapsed several 3rd normal form tables: date, absolute week, absolute month and year.  For each of these collapsed tables, I have a column that represents a unique identity, a textual name column and a numeric manual sort ordering index.  Here's an example row to show the structure, the numbers are illustrative only (in vertical form for ease of reading):

DateId: 20110507
DateName: 7th May 2011
DateOrder: 17813
WeekId: 201118
WeekName: Week 18, 2011
WeekOrder: 617
MonthId: 201105
MonthName: May 2011
MonthOrder: 74
YearId: 2011
YearName: 2011
YearOrder: 41

I've chosen to use a date dimension table for ease of understanding, but the problem can be transposed to any other dimension table involving hierarchies.

Problem synopsis

I want to retrieve the distinct set of week and month combinations with the related textual fields.

Possible solutions

1. DISTINCT

SELECT DISTINCT MonthId, WeekId, YearName, MonthName, WeekName
  FROM DimDate

Note that I cannot exclude MonthId or WeekId because I cannot assume that free text fields are unique.

2. GROUP BY

SELECT MIN(YearName), MIN(MonthName), MIN(WeekName)
  FROM DimDate
 GROUP BY MonthId, WeekId

3. ROWNUMBER

WITH grp AS (
  SELECT YearName, MonthName, WeekName
       , ROW_NUMBER() OVER (PARTITION BY MonthId, WeekId) AS r
    FROM DimDate
)
SELECT YearName, MonthName, WeekName
  FROM grp
 WHERE grp.r = 1

4. ETL

In this approach, indexed views of every combination of non-leaf attributes are precalculated and queried directly.  Obviously this is fastest at query time, but could require many duplications of the dimension table, depending on the complexity of the hierarchies in the dimension.

Comments

I would expect that the ROW_NUMBER solution is the fastest (excluding the ETL approach), but it also requires a subquery or CTE in the query specification.  Although this is not a serious problem, it does make the query harder to read.  I would expect that the GROUP BY and DISTINCT methods to be almost equivalent, both suffering from the fact that every column has to be used in some form of sort operation, whereas the ROW_NUMBER solution only requires the partitioning columns to be sorted.

Am I missing something?  Can SQL Server make more effective use of non-unique indexing of denormalised column identities?

--edit - additional notes:

The week and month identifiers are absolute, so they imply the year in this scheme. I enforce that weeks are only in a single year. In SSAS, would be two hierarchies: date, week, year and date, month, year.
I believe DISTINCT gives the same output because the columns requested only vary by week, month or year and year is implied by both week and month.
Indexing is currently on the identifier columns only (single column non-unique)

In general, I'm performing an aggregate query on a fact table but reporting by a set of non-leaf dimension attributes which are chosen by an end-user and not known in advance. I was trying to reduce the problem space for this question.

In my experience, an aggregate (DISTINCT or GROUP BY) can be quicker then a ROW_NUMBER() approach. Saying that, ROW_NUMBER is better with SQL Server 2008 than SQL Server 2005.

However, you'll have to try for your situation.
Compare query plans, and use Profiler and SET to capture IO, CPU, Duration etc

For a lot of background, see these SO questions:

Why are logical reads for windowed aggregate functions so high? (follow the links from Martin Smith)
can I get count() and rows from one sql query in sql server? (note the comments in the answers from me and Chris Bednarski)

Finally, do you need the ROW_NUMBER approach? It looks like you're fixing a problem caused by de-normalisation.

And some notes:

shouldn't YearID be in the GROUP BY or PARTITION BY?
Won't DISTINCT give different output?
Are these columns indexed?


Heap having high fragmentation Does high fragmentation on a heap impact query performance?

Can I rebuild the heap with ALTER TABLE REBUILD to get rid of the fragmentation?

This is a staging table used for reporting. It does have non-clustered indexes but there is no clustered index.

I've seen this question pop up enough times over the years that I figured it finally warranted a real answer.  Yes, this answer is years after the fact, but facts remain the same now as they did in 2015.

Heap Fragmentation is a red-herring, and frankly I wouldn't pay too much attention to it.  Honestly I say that about fragmentation in general and, at best, it is an indicator of issues to come, with the real detriment to performance being excessive white space per page.  Since that's not your question, I won't digress any further and just point you towards a post from Brent Ozar that will further wet your appetite if you wish to go down that rabbit hole.

What you need to be concerned with for heaps is forwarded records/forward pointers counts.  To quote one of the better posts on the topic by Kalen Delaney:

  when a row in a heap has to move, SQL Server leaves behind a forwarding pointer in place of the row that has moved

and

  SQL Server jumps out of sequence and follows the forwarding pointer for every forwarded record.

This is actual fragmentation that does matter as it's introducing additional reads, so depending on how many forwarded records you have in your heap, this could represent a significant chunk of I/O that you need to perform whenever you read the heap into memory.

How do you find out how many forwarded record pointers exist in your heap?  You need to simply run the following query for the heap in question:

SELECT    index_type_desc
        , page_count
        , avg_page_space_used_in_percent
        , avg_fragmentation_in_percent
        , avg_record_size_in_bytes
        , forwarded_record_count, 1.0*(forwarded_record_count)/(1.0*(page_count)) AS forwarded_record_pct
FROM    sys.dm_db_index_physical_stats(db_id(), object_id('schema.tablename'),null, null, 'detailed')
WHERE   index_type_desc = 'HEAP'
GO

Below are results from some actual tables in a vendor supplied application database, I currently support:

Results Table 1:

Results Table 2:

As you can see, fragmentation in both tables in actually pretty high, but I'm not going to waste my maintenance time rebuilding either table as there are not enough forwarded records to be concerned about it yet.  My reasoning here is that a heap is only ever brought into memory by a table scan operation or a RID lookup (in the event that a nonclustered index defined on the heap is non-covering).  When the table scan is performed, the entire table is loaded into memory, at which point fragmentation doesn't matter nearly as much.  I want to avoid table scans in general if possible, but when they occur, I want them as efficient as possible, so reducing forwarded pointers directly helps with that.  RID lookups are also affected by forward pointers, so even seek operations against a NCI with a RID lookup is subject to extra I/O if it runs into a forwarded record.  When I hit a certain threshold of forwarded pointers within my heap, I will then rebuild the table.  That threshold changes based on the table in question, but my general rule is once the heap has 10% or more forwarded record pages in comparison to total pages, I'm looking to rebuild the heap.  Most of the heaps in this database don't suffer from forwarded records, so I don't have to worry about it too much, but that's not necessarily true for your situation.

Also, there are situations where heaps may not properly deallocate empty pages. Basically, if you find your workloads against a heap table is not primarily insert-driven or you find forwarded records are occurring often, you will want to look into clustering your table with a clustered index.


DR/Reporting solution for a large SQL Server database We have a large (3TB) database that is a part of AOG. We now have a need for a third copy of the data. Here are some of things we need:

The third copy needs to be in sync with the primary in real time (or near real time); worst case we can live with a 24 hour lag.
The third copy cannot be PRIMARY NODE any time. (This is just a reporting server)

What are my options?

I don’t want to configure replication on top of AOG. That seems like a complex option. I can add a third node for the AOG. This could add some dependency on the primary; I’d prefer the new architecture to minimally interfere with the other 2 nodes.

If you don't need to query the server, the classic answer here is transaction log shipping: simply restoring your backups on a different SQL Server.

It's a great fit here because:

It's independent of your Always On Availability Group: even if the AG fails or the cluster fails, you can go live with this DR server quickly
It can't become the primary node until you manually run RESTORE WITH RECOVERY and point your clients over to it (can be tuned by using a DNS CNAME that normally points to your primary, but during disaster, you change it to point to the log shipped secondary)
It can stay reasonably in sync - you said within 24 hours, and that's fairly easy to do with log shipping

That meets all  of the requirements in your question.

Just be aware that there is a licensing gotcha: if you have Software Assurance, then for every one primary you buy, you get one free secondary as long as it's not queried. If you're using multiple secondaries (like one in your primary data center, plus adding this new one,) you may need to purchase additional licensing.

If you DO need to query the server while it continues to update the data underneath you, your only two answers are Always On Availability Groups or transactional replication. All other answers (SAN replication, log shipping, VM replication, etc) will require queries to stop each time the data is updated.


Sorting Result state taking very long time I am using a MySQL 5.1.69-log DB for analysis purpose.

I've designed my database in a star schema. All tables are using InnoDB.
 My innodb_buffer_pool_size=2Gb

This is the query I am running :

select
     `dw_dimension_criteria`.`id` as `c0`,
     count(distinct `dw_fact_program_attendance`.`client_id`) as `m0`
from
     `dw_dimension_criteria` as `dw_dimension_criteria`,
     `dw_fact_program_attendance` as `dw_fact_program_attendance`
where
     `dw_fact_program_attendance`.`criteria_id` = `dw_dimension_criteria`.`id`
group by `dw_dimension_criteria`.`id`;

The EXPLAIN gives the following :

+----+-------------+----------------------------+-------+---------------+--------------+---------+-----------------------------------------+--------+-------------+
| id | select_type | table                      | type  | possible_keys | key          | key_len | ref                                     | rows   | Extra       |
+----+-------------+----------------------------+-------+---------------+--------------+---------+-----------------------------------------+--------+-------------+
|  1 | SIMPLE      | dw_dimension_criteria      | index | PRIMARY       | PRIMARY      | 2       | NULL                                    |      1 | Using index |
|  1 | SIMPLE      | dw_fact_program_attendance | ref   | criteria_id   | criteria _id | 2       | clarity_nevada.dw_dimension_criteria.id | 417669 |             |
+----+-------------+----------------------------+-------+---------------+--------------+---------+-----------------------------------------+--------+-------------+

and the SHOW PROFILE command the following :

+----------------------+-----------+
| Status               | Duration  |
+----------------------+-----------+
| starting             |  0.000117 |
| checking permissions |  0.000035 |
| checking permissions |  0.000009 |
| Opening tables       |  0.000022 |
| System lock          |  0.000004 |
| Table lock           |  0.000011 |
| init                 |  0.000032 |
| optimizing           |  0.000012 |
| statistics           |  0.000045 |
| preparing            |  0.000017 |
| executing            |  0.000043 |
| Sorting result       |  0.000005 |
| Sending data         | 12.847928 |
| end                  |  0.000103 |
| removing tmp table   |  0.000016 |
| end                  |  0.000016 |
| query end            |  0.000005 |
| freeing items        |  0.000114 |
| logging slow query   |  0.000003 |
| logging slow query   |  0.000097 |
| cleaning up          |  0.000012 |
+----------------------+-----------+

I do not understand those 12 seconds. I do not understand if it belongs to Sorting Result or Sending Data. I tried adjusting sort_buffer_size but with no flagrant success. The fact table count about 7 millions rows.

Where should I look to drop those 12 seconds to a more reasonable time ?

If there is a foreign key from dw_fact_program_attendance(criteria_id) that REFERENCES dw_dimension_criteria(id), there is no need to join the two tables (but the optimizer is not yet smart enough to understand that.) You can have only one table and GROUP BY criteria_id:

SELECT
     a.criteria_id AS c0,
     COUNT(DISTINCT a.client_id) AS m0
FROM
     dw_fact_program_attendance AS a
GROUP BY
     a.criteria_id ;

An index on (criteria_id, client_id) would help efficiency.


Some view to find out which jobs are in the queue to be executed? A environment start several refreshes along the day.

If I change the job_queue_processes to 20 for example, it start several new jobs. Is there some view that I can see which jobs is waiting and will be executed if I change the job_queue_processes?
Observation: the jobs have been created with dbms_job.

For Oracle versions prior to 19c you can check the DBA_JOBS view for job created with the DBMS_JOBS package. For jobs created using DBMS_JOBS in Oracle 19c and later, or for jobs created with the DBMS_SCHEDULER in any version, check the DBA_SCHEDULER_JOBS view.


Oracle database creation - recording response file option not available I try to create database with dbca assistant (v 12c) and I would like to firstly use the graphical guide in order to save the response file for later.

I checked the advanced mode during the step 2, I passed all the steps and then I went to the Summary step : I don't have the option "save response file" as you can see on my screenshot

I'm wondering if it's because this option is simply not available if we use advanced mode only ?

If you have any suggestions...

What version of the installer are you running?  Saving response files in DBCA is available starting with version 12.2.  You can maybe launch the installer using the -record option to create a response file:

https://docs.oracle.com/cd/B19306_01/em.102/b16227/oui4_response_files.htm#i1005622


Receiving "The SELECT permission was denied on the object" even though it's been granted I'm a programmer, not a dba... I know just enough to be dangerous.

I've inherited a database with a legacy user that is a db_owner for the database. We can't adjust this user's permission for existing tables, schemas, etc., for business reasons, but some new tables are being created, and I only want this user to have SELECT access on them.

Permissions have been set for this user for these tables so that everything is DENIED, except SELECT, which is set to GRANT.

Yet when this user (dbadmin) attempts to perform a SELECT on one of these tables (AccountingAudit), this error happens:

The SELECT permission was denied on the object 'AccountingAudit', database 'billing', schema 'dbo'.

I've run this SQL to try and see what permissions are set for this table/user:

select object_name(major_id) as object,
 user_name(grantee_principal_id) as grantee,
 user_name(grantor_principal_id) as grantor,
 permission_name,
 state_desc
from sys.database_permissions

And this is what I get back:

AccountingAudit dbadmin dbo ALTER   DENY
AccountingAudit dbadmin dbo CONTROL DENY
AccountingAudit dbadmin dbo DELETE  DENY
AccountingAudit dbadmin dbo INSERT  DENY
AccountingAudit dbadmin dbo REFERENCES  DENY
AccountingAudit dbadmin dbo SELECT  GRANT
AccountingAudit dbadmin dbo TAKE OWNERSHIP  DENY
AccountingAudit dbadmin dbo UPDATE  DENY
AccountingAudit dbadmin dbo VIEW DEFINITION DENY
AccountingAudit dbadmin dbo VIEW CHANGE TRACKING    DENY

Seems like it should be working right?

The SELECT call I'm making is a very basic SELECT * FROM AccountingAudit, from within SSMS. I'm not doing any special sp_executesql or anything like that.

I've tried explicitly granting permission:

GRANT SELECT ON [dbo].AccountingAudit TO dbadmin

This has no effect (why would it, the query above already shows it's granted! ;-)

I've searched through stackoverflow.com and elsewhere, and cannot find anything I haven't tried yet. I'm wondering if it has something to do with how the schemas are setup. (At this point I know very little about schemas.)

Any ideas? Thanks!

I'm not sure here, but I'm going to go out on a limb.  I think your issue might be with your DENY CONTROL record.  See here about half way down the page:

Denying CONTROL permission on a database implicitly denies CONNECT permission on the database. A principal that is denied CONTROL permission on a database will not be able to connect to that database.

I realize that example is for a database, but take it one more granual level.  A DENY CONTROL on a table will deny all privileges on it, I'm guessing.  Do a REVOKE CONTROL to get rid of that and see if that fixes your issue.

If so, you'll have to place the user in a database role or deny them the explicit privileges against the table.


Understanding the recreation of index in Table partitioning I need your help. I have[AdventureWorks2014] database and I wanted to perform partitioning on [Production].[TransactionHistoryArchive] table based on [TransactionDate] column. [Production].[TransactionHistoryArchive] table has primary key clustered PK_TransactionHistoryArchive_TransactionID. I preferred using  a wizard and at the end of wizard I a script below:

USE [AdventureWorks2014]
GO

BEGIN TRANSACTION

CREATE PARTITION FUNCTION [myFUNCTION](datetime) AS RANGE LEFT FOR VALUES (N'2011-12-31T23:59:59.997', N'2012-12-31T23:59:59.997', N'2013-12-31T23:59:59.997')
CREATE PARTITION SCHEME [mySCHEMA] AS PARTITION [myFUNCTION] TO ([DRIVE E], [DRIVE F], [DRIVE G], [DRIVE H])

ALTER TABLE [Production].[TransactionHistoryArchive] DROP CONSTRAINT [PK_TransactionHistoryArchive_TransactionID]
ALTER TABLE [Production].[TransactionHistoryArchive] ADD  CONSTRAINT [PK_TransactionHistoryArchive_TransactionID] PRIMARY KEY NONCLUSTERED ([TransactionID] ASC)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)

CREATE CLUSTERED INDEX [ClusteredIndex_on_mySCHEMA_636976799895259162] ON [Production].[TransactionHistoryArchive] ([TransactionDate])
WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [mySCHEMA]([TransactionDate])

DROP INDEX [ClusteredIndex_on_mySCHEMA_636976799895259162] ON [Production].[TransactionHistoryArchive]

COMMIT TRANSACTION

Please help me to clearly understand the index recreation and correct me if I am mistaken.

MY THEORY: For performing table partitioning, we need to assign clustered index to the [TransactionDate] column. In order to do it we need to alter primary key constraint [PK_TransactionHistoryArchive_TransactionID] from PRIMARY KEY CLUSTERED to PRIMARY KEY NONCLUSTERED. SQL does it with the help of the following query:

ALTER TABLE [Production].[TransactionHistoryArchive] DROP CONSTRAINT [PK_TransactionHistoryArchive_TransactionID]
ALTER TABLE [Production].[TransactionHistoryArchive] ADD  CONSTRAINT [PK_TransactionHistoryArchive_TransactionID] PRIMARY KEY NONCLUSTERED ([TransactionID] ASC)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)

Now SQL can create clustered index for [TransactionDate] column:

CREATE CLUSTERED INDEX [ClusteredIndex_on_mySCHEMA_636976799895259162] ON [Production].[TransactionHistoryArchive] ([TransactionDate])
WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [mySCHEMA]([TransactionDate])

In the last part SQL drops the created index (did not understand this part. How come SQL creates and then immediately drops that clustered index?):

DROP INDEX [ClusteredIndex_on_mySCHEMA_636976799895259162] ON [Production].[TransactionHistoryArchive]

Dropping the index is not required, and is likely a coding oversight in the wizard output.  It appears you've not just blindly ran the generated code; this is a Good Thing™, and is why I much prefer to write my own T-SQL statements by hand.

The procedure for modifying a clustered primary key to a non-clustered primary key, then adding a clustered index to a separate column is exactly as you've described:

DROP the primary key constraint.
ADD a new primary key constraint with the NONCLUSTERED keyword.
CREATE a clustered index on the desired column.

The DROP INDEX statement is certainly not required.


Finding the Table and Column Based on a Known Value I've inherited a rather large SQL Server 2012 database -- about 2TB, tens of millions of rows in some tables, and about 350 different tables. I have basically no references nor documentation.

There's a type of file I need to generate which contains information stored somewhere in this database. I have one example of such file, so I have concrete values, but no idea as to where each of its fields came from.

For instance, I have an entry in the output file labeled UUID which has a value of a03dc6109c6f53ce93203f1b85c7d31d. After much uncomfortable digging, I found a column in one of the tables called "custom_value_1" with the value a03dc610-9c6f-53c-e932-03f1b85c7d31d, which I'm pretty sure semantically corresponds to what I wanted. But I have many such values to find, and it seems pretty unlikely that I'll be able to stumble across them all.

So how can I search all the columns across all the tables in a database for a particular value? I'd love to be able to say something like:

SELECT * FROM * WHERE * = 'HB194';

and have it search every column (skipping those of inapplicable types; no timestamps if I'm searching for a string) of every table for the value 'HB194'. Obviously something more complex will need to be employed, but I don't think I have the time (nor the fortitude) to manually craft a specific select query for each of the 350 tables for each of the dozen or so values I'm looking for.

From my post about this issue here (see #1):

DECLARE
  @Search1 NVARCHAR(4000) = N'%a03dc6109c6f53ce93203f1b85c7d31d%',
  @Search2 NVARCHAR(4000) = N'%a03dc610-9c6f-53c-e932-03f1b85c7d31d%',
  @s NVARCHAR(MAX) = N'';

;WITH t AS (
    SELECT t.[object_id], [table] = t.name, [schema] = s.name
    FROM sys.tables AS t
    INNER JOIN sys.schemas AS s ON t.[schema_id] = s.[schema_id]
    WHERE EXISTS (
        SELECT 1 FROM sys.columns
          WHERE [object_id] = t.[object_id]
          AND (system_type_id IN (35,99) -- text, ntext
          OR (system_type_id IN (167,175,231,239) -- (n)(var)(char)
            AND (max_length = -1 OR max_length >=32) -- max or >= LEN(@Search1)
             )))
)
SELECT @s = @s + N'SELECT N'''
    + REPLACE([schema],'''','''''') + '.'
    + REPLACE([table], '''','''''') + ''',*
 FROM ' + QUOTENAME([schema]) + '.' + QUOTENAME([table]) + '
 WHERE ' + STUFF((SELECT '
 OR ' + QUOTENAME(name) + ' LIKE ' + CASE
      WHEN system_type_id IN (99,231,239)
      THEN 'N' ELSE '' END
      + '''' + @Search1 + '''' -- run again with @Search2
    FROM sys.columns
    WHERE [object_id] = t.[object_id]
    AND system_type_id IN (35,99,167,175,231,239)
    ORDER BY name
    FOR XML PATH(''), TYPE
).value(N'.[1]', N'nvarchar(max)'),1,6,'') + ';

'
FROM t;

PRINT @s;
-- EXEC sp_executesql @s;


How to build dimensions from SCD tables Having the foresight to begin collecting data from our production environment several years ago, I have a table with a following structure-

Part
Status
Startdate
Enddate

I can write a relational join, but how do I then go about including this in my project? What should I do in the DSV and in the Dimension relationships?

in the BIDS, open your project and double click on your DSV file, you can right click any where and add a table and then do the rest of steps like adding a relation

look at images

Step 1
Step 2

Is that what you meant


You need outer joins. Specifically to LEFT JOIN the parent table with each child:

  parent
LEFT JOIN child1 AS c1 ON p.parent_id = c1.child_id
LEFT JOIN child2 AS c2 ON p.parent_id = c2.child_id

and then SELECT from the appropriate tables. Columns that are in both children tables can be combined with COALESCE(). The order of the expressions inside the function matter only if there are cases where an id appears in both childen.

The query will be something like:

SELECT
    p.parent_id, p.name,
    COALESCE(c1.child_id, c2.child_id) AS child_id,
    COALESCE(c1.date, c2.date) AS date,
    c1.color,
    c2.lenght
FROM
    parent AS p
  LEFT JOIN child1 AS c1 ON p.parent_id = c1.child_id
  LEFT JOIN child2 AS c2 ON p.parent_id = c2.child_id ;


How to Determine Chunk Distribution (data and number of docs) in a Sharded MongoDB Cluster? Having done all the right things - picked an appropriate shard key, scaled horizontally, distributed my data across several shards, I find I now have no real way to determine how well balanced the data is in terms of document counts, and data size.  The sh.status() command will tell me how chunks are distributed in terms of counts, but not what makes up those chunks.

There are plenty of ways to infer these things - but they all have a downside.  The vagaries of how database size are calculated means that if there have been significant deletes in a database, the stats may not be an accurate reflection of data distribution.  If I look at traffic hitting each shard, that might give me some clue, but only if my traffic is nice and predictable.

So, how do I determine the distribution of documents in each chunk, the relative sizes of each chunk and (of course) where those chunks currently reside?

There is currently no built-in way to do this, so a small function is needed.  For the purposes of this answer I have created a 2 shard cluster with ~1 million documents as per these instructions.  Next up I used this function to examine those documents:

AllChunkInfo = function(ns, est){
    var chunks = db.getSiblingDB("config").chunks.find({"ns" : ns}).sort({min:1}); //this will return all chunks for the ns ordered by min
    //some counters for overall stats at the end
    var totalChunks = 0;
    var totalSize = 0;
    var totalEmpty = 0;
    print("ChunkID,Shard,ChunkSize,ObjectsInChunk"); // header row
    // iterate over all the chunks, print out info for each
    chunks.forEach(
        function printChunkInfo(chunk) {

        var db1 = db.getSiblingDB(chunk.ns.split(".")[0]); // get the database we will be running the command against later
        var key = db.getSiblingDB("config").collections.findOne({_id:chunk.ns}).key; // will need this for the dataSize call
        // dataSize returns the info we need on the data, but using the estimate option to use counts is less intensive
        var dataSizeResult = db1.runCommand({datasize:chunk.ns, keyPattern:key, min:chunk.min, max:chunk.max, estimate:est});
        // printjson(dataSizeResult); // uncomment to see how long it takes to run and status
        print(chunk._id+","+chunk.shard+","+dataSizeResult.size+","+dataSizeResult.numObjects);
        totalSize += dataSizeResult.size;
        totalChunks++;
        if (dataSizeResult.size == 0) { totalEmpty++ }; //count empty chunks for summary
        }
    )
    print("***********Summary Chunk Information***********");
    print("Total Chunks: "+totalChunks);
    print("Average Chunk Size (bytes): "+(totalSize/totalChunks));
    print("Empty Chunks: "+totalEmpty);
    print("Average Chunk Size (non-empty): "+(totalSize/(totalChunks-totalEmpty)));
}

It's pretty basic at the moment, but it does the job.  I have also added it on github and may expand it further there.  For now though, it will do what is needed.  On the test data set described at the start, the output looks like this (some data removed for brevity):

mongos> AllChunkInfo("chunkTest.foo", true);
ChunkID,Shard,ChunkSize,ObjectsInChunk
chunkTest.foo-_id_MinKey,shard0000,0,0
chunkTest.foo-_id_0.0,shard0000,599592,10707
chunkTest.foo-_id_10707.0,shard0000,1147832,20497
chunkTest.foo-_id_31204.0,shard0000,771568,13778
chunkTest.foo-_id_44982.0,shard0000,771624,13779
// omitted some data for brevity
chunkTest.foo-_id_940816.0,shard0000,1134224,20254
chunkTest.foo-_id_961070.0,shard0000,1145032,20447
chunkTest.foo-_id_981517.0,shard0000,1035104,18484
***********Summary Chunk Information***********
Total Chunks: 41
Average Chunk Size (bytes): 1365855.024390244
Empty Chunks: 1
Average Chunk Size (non-empty): 1400001.4

To explain the arguments passed to the function:

The first argument is the namespace to examine (a string), and the second (a boolean) is whether or not to use the estimate option or not.  For any production environment it is recommended to use estimate:true - if it is not used, the all the data will need to be examined, and that means pulling it into memory, which will be expensive.

While the estimate:true version is not free (it uses counts and average object sizes), it is at least reasonable to run even on a large data set.  The estimate version can also be a little off if object size is skewed on some shards and hence the average size is not representative (this is generally pretty rare).


Should I turn off GTID to fix this error? Recently, a query has started failing. It is a query in the Magento software core (not a module) that Reindexes Catalog Search. I have receive the following error.

  Catalog Search Index index process unknown error: exception
  'PDOException' with message 'SQLSTATE[HY000]: General error: 1785 When
  @@GLOBAL.ENFORCE_GTID_CONSISTENCY = 1, updates to non-transactional
  tables can only be done in either autocommitted statements or
  single-statement transactions, and never in the same statement as
  updates to transactional tables.' in
  /var/www/magento-20171012/mage-webroot/lib/Zend/Db/Statement/Pdo.php:228

I am running MySQL 5.6.35 according to SELECT VERSION();.

This documentation claims _ was introduced in MySQL 5.6.9.

This MySQL documentation claims "All servers in your topology must use MySQL 5.7.6 or later. You cannot disable GTID transactions online on any single server unless all servers which are in the topology are using this version."

I don't see gtid_mode in the my.cnf and SELECT @@GLOBAL.GTID_MODE; gives me ON.

I am not sure based on the documentation what GTID exactly is. It sounds like it's used for replication or Master/Slave setups. I don't have a Master/Slave set up and I don't replicate except a backup to a sql file which I don't think is what they mean by replicate.

Running SELECT @@GLOBAL.GTID_MODE; gives me ON. Is running SET @@GLOBAL.GTID_MODE = OFF; all that I need to do to disable GTID and fix this issue? I've done this in my dev (which is not exhibiting the issue) and don't have any issues, but I want to get advice before I apply this to production without understanding the consequences.

What would it mean to disable GTID? Would it resolve this error I'm receiving from the database?

Short answer: Yes, you can disable GTID without risk if you're not replicating from that Server.

Long answer:

The GTID is "a unique identifier created and associated with each transaction committed on the server of origin (master)." This helps when you have some slaves; so you can always track how is your slave (which statements have replicated, from which Server, and some further information; but if you're using the server as a Standalone, shouldn't have problems, you won't use your binlogs.

You should lookup for all the cnf files, (maybe in /usr...), there should be one configuring gtid, since it's disabled by default (https://dev.mysql.com/doc/refman/5.6/en/replication-options-gtids.html#sysvar_enforce_gtid_consistency); it's important to find it so you don't have problems if someday your DB restars; otherwise it'll be ON again.

For disabling it, you'll need to restart; the link you found for disabling it without restart applies for MySQL 5.7 and further.

Finally, what it'll mean for you to disable it, you'll have your binlogs without a clear "identifier", but you'll still have your binlogs in case you need them.


Ensuring a GROUP has specific values, no more no less with an aggregate I have a table (a :: text, b :: uuid).

I also have a set (not a list, i.e. order doesn’t matter) of UUIDs, bs = {b1, b2, b3}.

How can I get such an a that has exactly those three bs in the table, no more and no less?

Something like:

SELECT tt.a
  FROM (SELECT a, sort(array_agg(b)) AS bs FROM t GROUP BY a) AS tt
  WHERE tt.bs = sort('{b1, b2, b3}')

But that seems like quite a bad idea w.r.t. efficiency…

I think you want something like this..

SELECT a
FROM tt
GROUP BY a
HAVING array_agg(b ORDER BY b) = ARRAY[
  -- whatever
]::uuid[];

sample data,

CREATE TABLE tt ( a text, b uuid DEFAULT gen_random_uuid() );
INSERT INTO tt(a) VALUES ('foo'), ('bar'), ('baz'), ('foo'), ('foo');

SELECT a, b FROM tt ORDER BY a,b;
  a  |                  b
-----+--------------------------------------
 bar | 6a7d1d36-d16f-459c-9fb0-b5a54f1fc5b3
 baz | 2fca5dee-4bfe-4862-9b8a-79890fbfda28
 foo | 32f9d608-f87f-4830-b0d8-6215f60a71ee
 foo | 3fe0cffe-446e-4e94-8785-2f893ee91a0c
 foo | f9d07c5a-e69b-4501-a441-5bbd86ed7f4b
(5 rows)

This will return foo,

SELECT a
FROM tt
GROUP BY a
HAVING array_agg(b ORDER BY b) = ARRAY[
  '32f9d608-f87f-4830-b0d8-6215f60a71ee',
  '3fe0cffe-446e-4e94-8785-2f893ee91a0c',
  'f9d07c5a-e69b-4501-a441-5bbd86ed7f4b'
]::uuid[];

However, without exactly those three things and nothing more in the array_agg and in that order and you won't get a match.

If you don't care about excess matches, that is to say if foo having addition UUIds should return a match you can optimize this expression by copying them into an additional where clause which will use an index.

Shy of that, if you need faster you could move to use Materialized Views, or write a count clause too...

HAVING count(*) = ...
  AND array_agg(b ORDER BY b) = ...


Does a trigger in a transaction only fire when the transaction is committed? I'm a developer, but I turn to the dba community for the following question.

I'm writing an integration test in C#. In this test, I start a transaction first, and always rollback so nothing is committed to the database, regardless of wether the test succeeds or fails.

After starting the transaction, I insert something in a table. There is a trigger for insert on this table, but I get the impression this trigger is only triggered when I commit my transaction.

Is this true?

Is there a way for me to make the trigger fire inside the transaction?

So what happens at runtime is:

Insert in table A

Trigger is fired and inserts in table B

What happens in my test is:

Open transaction
Insert in table A
Check table B --> insert hasn't been executed

I don't know anything about Entity Framework, but in the following basic test using Sql Server Management Studio against a local instance, it appears that the trigger fires upon the completion of the insert to table dbo.Test1 and I can immediately see rows in dbo.Test2 BEFORE issuing the COMMIT

USE [Test]
GO
IF OBJECT_ID('dbo.Test1', 'U') IS NOT NULL
    DROP TABLE dbo.Test1
GO
IF OBJECT_ID('dbo.Test2', 'U') IS NOT NULL
    DROP TABLE dbo.Test2
GO
CREATE TABLE [dbo].[test1](
    [col1] [char](3) NULL
) ON [PRIMARY]

GO
CREATE TABLE [dbo].[test2](
    [col1] [char](3) NULL
) ON [PRIMARY]

GO
CREATE TRIGGER dbo.Test1Trigger
   ON  dbo.Test1
   AFTER INSERT,DELETE,UPDATE
AS
BEGIN
    SET NOCOUNT ON;

    INSERT INTO dbo.Test2 select * from inserted

END
GO
begin transaction
insert into dbo.test1 (col1) values('abc')
select * from dbo.test2     --The row comes out of the select without issuing a commit
--insert into dbo.test2 (col1) values('xyz')
--commit
--select * from dbo.test2
--rollback


Azure automatic fail-over vs always on availability groups clarification I am trying to understand automatic failover vs always on availability groups in Azure hybrid and geo-region situations:

Is it possible to have on-premises SQL databases, VM SQL databases and Azure databases in the same availability group? Or does one need to separate on-premises databases from cloud databases in separate always-on availability groups?
Automatic failover is not possible in hybrid situations, it has to be forced manually. But, can automatic failover be implemented across regions in the cloud or does it have to happen in the same region?

On-Premise SQL Server and SQL Server on a VM hosted in Azure are roughly equivalent and can be configured to be part of the same availability group, although as you mentioned the failover is manual rather than automatic.

This link https://docs.microsoft.com/en-us/azure/virtual-machines/windows/sqlclassic/virtual-machines-windows-classic-sql-onprem-availability provides more details.

Azure Database (As A Service) is a different thing.  It is definitely based on SQL Server and it's High Availability is provided by something that is very similar to Always On Availability Groups.  But it's completely managed by Microsoft so you won't be able to add it as a member to your own Availability Group.

This link https://docs.microsoft.com/en-us/azure/sql-database/sql-database-high-availability has more details.

To answer your second question you should view resources in different regions as if they are in separate data centers (which they are).  You can make automatic failover work in these situations by using VPN tunnels and judicious use of cloud witnesses.  However, I would advise against failing between data centers automatically, that should be a deliberate decision.

But mostly this depends on your HA/DR requirements.  I love Azure and we use it heavily in my current organization, but stretching across datacenters or on-prem/cloud is not as pain-free as Microsoft would have you believe.


Insert into indexed view? I give an example to show my problem.
I create 2 table as the following:

CREATE TABLE a
(
    id INT
)

CREATE TABLE b
(
    name NVARCHAR(10),
    id INT
)

Then insert data into these tables

INSERT INTO a VALUES(1),(2)
INSERT INTO b VALUES('Kan',1),('Michael',2)

Next,I create indexed view that join these tables via id

CREATE VIEW a_b
WITH SCHEMABINDING
AS
(
SELECT a.id,b.name FROM a INNER JOIN b ON a.id=b.id
)

CREATE UNIQUE CLUSTERED INDEX ix_a_b
ON a_b(id)

INSERT INTO a_b VALUES (3,'Joe') will be wrong

As I know about view:

views do not store data,just saved queries
but indexed view that stored data physically like table in the database.So why I don't insert,delete from a_b?

And what I know about VIEW is right or wrong?Help me improve?

From Doc Online:

The view referenced by table_or_view_name must be updatable and reference exactly one base table in the FROM clause of the view.
For example, an INSERT into a multi-table view must use a column_list that references only columns from one base table.

https://docs.microsoft.com/en-us/sql/t-sql/statements/insert-transact-sql?view=sql-server-ver15


What could cause a record to appear in the MySQL binary log but not the original table? I have a web application (CiviCRM) that has the option to enable binary logging, which I've done.  We use this software to record donations, including automatic recurring donations.  Our payment processor sends a POST request to a particular URL, which SHOULD create a new donation record in the db.  Instead, it's creating a record in the log table with log_action of Insert, but there's no corresponding record in the original table - and no Delete record in the log.

What circumstances might cause this?  Where should I begin troubleshooting?  There's no clustering/relaying - there are a handful of triggers that act on this record, which I'm eyeing suspiciously, but would appreciate input.

MySQL version is 5.5.44-0+deb7u1.

It sounds like you lack the SUPER privilege (global permission).

When you connect to MySQL, run SHOW GRANTS;

If you see more than one line, you do not have SUPER.

What does having SUPER give you ?

  The SUPER privilege enables an account to use CHANGE MASTER TO, KILL or mysqladmin kill to kill threads belonging to other accounts (you can always kill your own threads), PURGE BINARY LOGS, configuration changes using SET GLOBAL to modify global system variables, the mysqladmin debug command, enabling or disabling logging, performing updates even if the read_only system variable is enabled, starting and stopping replication on slave servers, specification of any account in the DEFINER attribute of stored programs and views, and enables you to connect (once) even if the connection limit controlled by the max_connections system variable is reached.

  To create or alter stored functions if binary logging is enabled, you may also need the SUPER privilege, as described in Section 20.7, “Binary Logging of Stored Programs”.

This also means you may only have database level grants for all users.

How can you tell ?

Login as root@localhost and run this

SELECT
    A.column_name GlobalPrivilege,
    IFNULL(B.column_name,'Not at DB Level') DBPrivilege
FROM
(SELECT column_name FROM information_schema.columns
WHERE table_schema='mysql' AND table_name='user'
AND column_name LIKE '%priv') A
LEFT JOIN
(SELECT column_name FROM information_schema.columns
WHERE table_schema='mysql' AND table_name='db'
AND column_name LIKE '%priv') B
USING (column_name);

You should see something like this

+------------------------+-----------------------+
| GlobalPrivilege        | DBPrivilege           |
+------------------------+-----------------------+
| Select_priv            | Select_priv           |
| Insert_priv            | Insert_priv           |
| Update_priv            | Update_priv           |
| Delete_priv            | Delete_priv           |
| Create_priv            | Create_priv           |
| Drop_priv              | Drop_priv             |
| Reload_priv            | Not at DB Level       |
| Shutdown_priv          | Not at DB Level       |
| Process_priv           | Not at DB Level       |
| File_priv              | Not at DB Level       |
| Grant_priv             | Grant_priv            |
| References_priv        | References_priv       |
| Index_priv             | Index_priv            |
| Alter_priv             | Alter_priv            |
| Show_db_priv           | Not at DB Level       |
| Super_priv             | Not at DB Level       |
| Create_tmp_table_priv  | Create_tmp_table_priv |
| Lock_tables_priv       | Lock_tables_priv      |
| Execute_priv           | Execute_priv          |
| Repl_slave_priv        | Not at DB Level       |
| Repl_client_priv       | Not at DB Level       |
| Create_view_priv       | Create_view_priv      |
| Show_view_priv         | Show_view_priv        |
| Create_routine_priv    | Create_routine_priv   |
| Alter_routine_priv     | Alter_routine_priv    |
| Create_user_priv       | Not at DB Level       |
| Event_priv             | Event_priv            |
| Trigger_priv           | Trigger_priv          |
| Create_tablespace_priv | Not at DB Level       |
+------------------------+-----------------------+

As shown, SUPER does not exist for a DB Level user.

You can run this

SELECT user,host FROM mysql.db;

to see your DB Level users.

Please also look into your DB level user having the TRIGGER privilege, which can allow a database level user to create, drop, or execute triggers.

Your real problem comes from Drupal's required settings for CiviCRM

Drupal.org : CiviCRM 4.2 and higher require more SQL privileges than past versions or binary logging disabled
CiviCRM Community Forums : MySQL Triggers and binary logging suggests giving out the TRIGGER privilege.

You can look into doing on of the following:

make all users global users and give them SUPER (not secure)
give away the TRIGGER privilege to all DB users

UPDATE mysql.db SET trigger_priv = 'Y';
FLUSH PRIVILEGES;

GIVE IT A TRY !!!


Which metrics should a product owner look at? I am the product owner for an on premise, off-the-shelf, application, which relies on a SQL Server DB.

My company outsources DB administration to a bunch of very capable folks, and tickets are issued when something is wrong. (So that I can, for example, escalate with the editor's technical teams).

However, improvement and evolution projects are my responsability, but I have a hard time figuring out if one is in order or not.

As far as performance goes, I do not follow any indicators. Without micromanaging, is there any high-level KPIs that should be monitored by a product owner?

I cannot list everything you need to monitor for a database. The biggest factor is 'It Depends'. I am going to list a few articles that might give you a starting point and from there you can adjust.

SQL Server Monitoring Tips--I know you are not looking for how to monitor but what to monitor. If you go thru these categories and cover all you will cover most of the KPI.
Key metrics for SQL Server monitoring by Paul Gottschling
Server Performance and Activity Monitoring


Oracle allows inserting into table even after revoking quota I am studying creating users and granting roles and I have come across this scenario where I grant unlimited quota to a user, insert into the table and then revoke the quota. I expected that after setting quota to 0, the user would not be able to insert into the table. However, the user is still able to insert. I wonder if this is expected, if there is another command to really prevent the user from inserting again. I am using Oracle 19c and user's current priviledges are create session and create table.
sqlplus system\oracle

SQL> CREATE TABLESPACE libgeneral datafile '/u01/app/oracle/oradata/ORCLCDB/orcl/libgeneral.dbf' SIZE 20M AUTOEXTEND ON NEXT 40M
EXTENT MANAGEMENT LOCAL
SEGMENT SPACE MANAGEMENT AUTO;

Tablespace created.

sqlplus library\libuser

SQL> create table t1 ( idx number ) tablespace libgeneral;

Table created.

SQL> insert into t1 values (1);
insert into t1 values (1)
            *
ERROR at line 1:
ORA-01536: space quota exceeded for tablespace 'LIBGENERAL'

sqlplus system\oracle

SQL> alter user library quota unlimited on libgeneral;

User altered.

sqlplus library\libuser

SQL> insert into t1 values (1);

1 row created.

sqlplus system\oracle

SQL> alter user library quota 0 on libgeneral;

User altered.

sqlplus library\libuser

SQL> insert into t1 values (1);

1 row created.

When you inserted the first row in the empty table, Oracle allocated an extent, which typically contains 8 blocks with default settings for the first extent of a table. 8 blocks (typical size of single block is 8 KB) can store a lot more than 1 row, so even though you revoked the quota, that space is already allocated, and you can write into it, which is expected behavior.
Try inserting a lot more rows and eventually you will get the same error message about the quota.


mysql slave restore database ERROR 3079 Master A mysql 8
Master B mysql 5.7
Master C mysql 5.7
Slave mysql 8

i create 4 channels on my slave and i can not restore backup from master.

[root@CentOS-76-64-minimal hex]# gunzip < db.gz | mysql -u root -p
Enter password:
ERROR 3079 (HY000) at line 22: Multiple channels exist on the slave. Please provide channel name as an argument.

Try this

mysql> create database mydb;
mysql> use mydb;
mysql> source db_backup.dump;


Can normalisation increase speed? We normally think about normalisation as a way of avoiding data duplication, and for speed we tend to denormalise. However I have a performance problem with a database I maintain and I wonder if I could normalise some of the data to increase the speed of select queries.

We have a legacy MS SQL Server 2005 database that runs our helpdesk. There is a Tasks table that contains a task that some engineer has to do. Over the years this table has had more and more columns added to it. It now has 29 columns of various types including text (BLOB) columns.

The problem is that if I do a simple query (using ADO from VBScript) like select created_date, title from Tasks where blah blah then this is getting rather slow. If I look in detail I find that the query returns in a second or two but stepping through the result set and reading back the (few hundred) results takes 10-20 seconds. When I look at the server performance I see a big spike in the disk i/o and disk queue length.

This query doesn't have any joins, so it should be about as fast as it could be. The columns in the where clause are indexed, and reindexing doesn't affect the speed so I assume the indicies are in good shape.

I wonder if the speed problems are due to SQL Server having to pick out just the few columns I'm querying from the 29 columns currently in each row. So my question is whether I can improve performance by splitting the table into a table with just the most commonly queried columns and a second table with everything else?

I guess the obvious thing is to try the experiment, but setting up a test system with SQL 2005 is not an insignificant effort. It would be very helpful if someone has run into this sort of problem and can tell me whether it's likely to be worth trying or not.

No, is unlikely normalization will help. What you have there looks more like missing indexes. 29 columns is not much. And BLOB columns are already stored by the system on separate allocation units (out of row), see Table and Index Organization.

  The columns in the where clause are indexed, and reindexing doesn't affect the speed so I assume the indicies are in good shape.

I recommend you read How to analyse SQL Server performance. Your indexes are not used, or not useful. For more useful answer post the exact table definition, all indexes, the exact query, and the execution plan XML.


Mysql Dump Taking longer time Mysql Dump Taking longer time

I have 188 databases, which I am individually dumping in a loop using a shell script as below

 echo "$(date  +%b-%d-%y) Dumping Started at $(date +%r)" | tee -a $DEST/general.logs $DEST/details.logs
for db in $databases; do
        echo "START : Dumping $db start at $(date +%r)" >> $DEST/details.logs
        if [[ "$db" != "information_schema" && "$db" != "performance_schema" ]]; then
                if ( mysqldump -u $USER -p$PASSWORD --databases $db > $DEST/$DATE.$db.sql) then
                        gzip $DEST/$DATE.$db.sql
                        echo "SUCCESS : Dumping $db" >> $DEST/details.logs
                        echo "FINISH : Dumping $db finished at $(date +%r)" >> $DEST/details.logs
                else
                        echo "FAILED : Dumping $db" | tee -a $DEST/general.logs $DEST/details.logs
                fi
        fi
done
echo "$(date  +%b-%d-%y) Dumping Finished at $(date +%r)" | tee -a $DEST/general.logs $DEST/details.logs

if [ -d "$oldBackup" ]; then
        rm -rf $oldBackup
        echo "Backup for $fiveDaysAgo removed successfully at $(date +%r)" | tee -a $DEST/general.logs $DEST/details.logs
else
        echo "Backup for $fiveDaysAgo not found." | tee -a $DEST/general.logs $DEST/details.logs
fi
echo "Backup Size" $(du -ch $DEST | grep total) >> $DEST/general.logs

The total size of all the databases is nearly 150GB When I restart mysql it takes nearly 2-3 hours to complete all the backups for a couple of days and then it keeps on increasing. Over a period of a week it reaches 7-8 hours and then 15 -20 Hours.

I have made sure that there is no process running while the backup is going for the first 4 hours.

Below is my sys config : OS : Centos 5.6 Mysql : 5.6 RAM : 64GB(8 cores)

Below my SQL config(my.cnf)

[mysqld]
#innodb_force_recovery=6
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
tmpdir=/var/lib/mysql/tmp
user=mysql

interactive_timeout=15
wait_timeout=40

symbolic-links=0
local-infile=0
port=33060
skip-name-resolve

sql_mode=NO_ENGINE_SUBSTITUTION
explicit_defaults_for_timestamp=1

innodb_buffer_pool_size=32G
innodb_log_file_size=10G
innodb_log_buffer_size=16M
innodb_flush_log_at_trx_commit=2
innodb_flush_method=O_DIRECT
innodb_checksum_algorithm=crc32

innodb_buffer_pool_instances=8
innodb_thread_concurrency=16
innodb_lock_wait_timeout=180

innodb_buffer_pool_dump_at_shutdown=1
innodb_buffer_pool_load_at_startup=1
innodb_io_capacity=2000
innodb_io_capacity_max=3000
innodb_lru_scan_depth=2000

#Binary log/replication
#log-bin
#sync_binlog=1
#sync_relay_log=1
#relay-log-info-repository=TABLE
#master-info-repository=TABLE
#expire_logs_days=6
#binlog_format=ROW
#transaction-isolation=READ-COMMITTED
#innodb_autoinc_lock_mode = 2
join_buffer_size=6M
sort_buffer_size=6M
read_buffer_size=6M
read_rnd_buffer_size=1M

key_buffer_size=512M
query_cache_type=0
query_cache_size=0
#query_cache_limit=1M

tmp_table_size=256M
max_heap_table_size=256M
table_open_cache_instances=16
table_open_cache=200000
table_definition_cache=100000
open_files_limit=1024000

thread_cache_size=200
max_connections=400
max_user_connections=350
max_connect_errors=99

connect_timeout=20
max_allowed_packet=128M

slow_query_log=1
long_query_time=5
slow_query_log_file=/var/log/mysql-slow.log

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

[mysqldump]
quick
max_allowed_packet=1G

Thank you in advance!!

Not a solution, but an improvement:

mysqldump ... | gzip >$DEST/$DATE.$db.sql.gz

This would avoid the I/O due to the write and reread of the .sql file.

You could also consider having N connections -- N-1 doing the biggest databases, 1 doing all the rest.  N=4 might be reasonable; there is no use making it too high -- once the I/O is saturated, no further speedup can be had.

A minor note:  Since you are invoking date more than once, there is a chance of getting a different value somewhere in the middle of the script.  Recommend capturing it in a variable.

Does the du show a significant difference from one time to the next?


SQLAlchemy connection error with Postgres I am trying to write a basic python script that uses SQLAlchemy to connect to my Postgres database. In the connection details, if I use "LOCALHOST", it connects fine. If I reference the server by name, it throws the following error:

  OperationalError: (psycopg2.OperationalError)
  FATAL:  no pg_hba.conf entry for host "fe80::19e5:a9a0:bcc2:1c5b%11", user "postgres", database "postgres", SSL off

I checked pg_hba.conf, and here's what I have:

# IPv4 local connections:
host    all             all             127.0.0.1/32            trust

host    all             all             0.0.0.0/0               md5
# IPv6 local connections:
host    all             all             ::1/128                 trust

host    all             all             0.0.0.0/0               md5

I am able to connect to this Postgres database using other tools, like PgAdmin from a different machine, Tableau, Talend, etc.

UPDATE: I tried instead of using the name of the machine, using it's static ipv4 IP address, which worked. The problem has something to do with the ipv6 connection.

Disclaimer - I'm not a huge sys admin buff, so my information may not be correct..  I also realize that the error says "host" - but I don't think that means it has to be a host entry in pg_hba.conf

When you connect to localhost, it uses the loopback address 127.0.0.1 (unless that configuration on your computer has been tampered with) to connect to your system, which counts as a "HOST" connection..

When you connect to "myComputer" (which happens to be your computer name), I'm pretty sure your OS is smart enough to say "oh hey! that's me!" and doesn't even bother resolving an ip address .. it just does a direct connection. This would be a "LOCAL" connection for postgres..

Try adding local all all ::1/128 trust to your pg_hba.conf file..

Then either restart the server, or connect as a superuser and issue select pg_reload_conf() (preferred method).

  UPDATE: I tried instead of using the name of the machine, using it's static ipv4 IP address, which worked. The problem has something to do with the ipv6 connection.

That still works because it's still using a HOST connection (instead of a LOCAL one).  Try what I suggest and see if you still get an error when connecting to the computer hostname.


Postgresql: invalid record length at 29/E4624520 but replication looks fine What is this error? I see that replication is working fine, but I am not so sure if the data is being corrupted or not.

Can I safely ignore this error?

I cannot do anything else than quoting an entry from the pgsql-admin mailing list:

  Just after startup, the standby tries to read and replay the WAL files
  which exist in the standby, and then if it finds invalid WAL record,
  IOW, it can no longer read valid WAL record locally, it starts
  replication and tries to read the WAL files from the master. So the
  above log message "invalid  record ..." means that there is no valid
  WAL record in the standby, which triggers the replication. Since valid
  WAL records are streamed from the master, you don't need to worry
  about that log message.

It's worth noting that that line is only a LOG level message.


myISAM data loss at DELETE / INSERT I have a linked table id_group | id_user.  Every time I add a one or more users to a group I DELETE all the links and INSERT the users again including the new one(s). This is how the framework of the application works.

From time to time some groups lose all the users they had, except the new ones, which are inserted.

The primary index of the table is the pair id_group - id_user.

I cannot reproduce this through a query, but the client has complained multiple times and there's an issue no doubt.

I could explain this if the table was innoDB with the DELETE locking the index and the INSERT not being able to add that pair again, however this is a myISAM table and myISAM locks the entire table during DELETE statements.

Is it possible you've got two processes attempting to do this at almost the same time?  If this is a web application, could you have a site user who is double-click happy?

Process #1 "examine existing users"
Process #1 "remove all users"
Process #2 "examine existing users" (no users would found)
Process #1 "insert new users and put existing users back"
Process #2 "remove all users"
Process #2 "insert new users and put existing users back (but no existing users were found)"