What is the best way to store SSH fingerprints and public keys? I'm building a virtual Keyring in our DB to allow users to store PGP/GPG public keys with relation to email addresses so that they can optionally encrypt all communications our system sends them.  I'm just building the table now and wasn't sure about the column type to store the fingerprint.

From what I understand fingerprints will be 128 or 160 bits long, generally in the form of:

43:51:43:a1:b5:fc:8b:b7:0a:3a:a9:b1:0f:66:73:a8

... which I believe is just a 32bit MD5 hash / UUID.  I'm guessing I can store this as a 16bit Binary value, or a 32 char string...

Is there a best practice, and if so what is it, or lacking that, what's your preference?

As a related follow up, is there any reason to Encrypt the public key string itself, or is that redundant since it is public after all?

EDIT: BTW, this is MySQL currently 5.5, but will be 5.7 in the near future.**

EDIT 2: I can't find any examples out there of a table schema for storing this type of record and I admittedly have only minimal understanding beyond the basics of using public/private keys in my own role, so while I could discern that the fingerprint is a hash, I'm unaware of the different formats for public keys and what range their lengths can come in (i.e. what about the -----BEGIN CERTIFICATE----- components, what size field will these need?).

If possible can someone provide a basic CREATE TABLE statement with what you see as an optimal fieldset? Below is what I've created thus far, is it sufficient, would you change anything?

CREATE TABLE `public_keys` (
    `ai_col`  int UNSIGNED NOT NULL AUTO_INCREMENT ,
    /* ... other foreign keys ... */
    `label`  varchar(100) NULL ,
    `fingerprint`  binary(16) NOT NULL ,
    `key_type`  varchar(10) NOT NULL ,
    `public_key`  varchar(1024) NULL ,
    PRIMARY KEY (`ai_col`)
);

If I'm not mistaken, the fingerprint is a hexadecimal number.  That means you could store it as a binary number, since two hexadecimal characters can be represented by a single byte, making a 32-character hexadecimal string = 16 bytes.

Here's the documentation on binary and varbinary data types for MySQL 5.7 (I don't have a test environment handy).

I do something similar for IP addresses based on this question and answer from StackExchange.  Store the data as binary, convert it back to text when it needs to be displayed or compared against another string.  Create a view that calls the functions as a part of it so you can easily get and store the full string when you want it.

Also, public keys are indeed public, and are often stored in searchable databases like this one.


How can MySQL be optimized for fast reads? From this Flickr article, they state:

  MySQL is optimized for fast random reads.

Also, one of the answers to this Stack Overflow question states:

  MySQL is typically built for 80/20 read/write ratio

I used those two sentences as keywords to search a lot but cannot find any useful resouces to actually prove/explain that opinion.

Can you explain to me in which way MySQL is optimized for fast random reads with some materials as references, please (or just offer some keyword to help to find out)?

90% of Production servers are humming along nicely because they are not "pushing the envelope".

9% of Production servers are humming along nicely because they pushed the envelope, but then found a way around the problem(s) they encountered.

(I made up the numbers, based loosely on years of experience.)

My point is that you probably fall into the 90% and don't need to worry.  But when you get close to hundreds of writes/second or thousands of reads/second, you may migrate to the 9%.  Then bring your question to us, together with SHOW CREATE TABLE and some info about the queries.

As for Flickr, that article was written before their "Ticket server" melted down.  It was a server with exactly one purpose:  Produce the next photo_id.  This experience has little to do with 80/20 or random reads.  Most of the rest of Flickr is sharded because of high read and write load.  Are you anywhere near needing sharding?  And it is possible to essentially eliminate that "ticket server" by a minor architectural change.

The other article starts with "several hundred update and inserts on them per minute".  Well, that is not a problem unless you are sloppy with indexes, etc.  Several hundred per second is likely to be a problem.

UUIDs on huge tables are a known source of trouble due to the randomness of access for updating.

So, what is MySQL -- InnoDB in particular -- optimized for?  I would say "some of everything".  But then I would be quick to point out that this forum (and stackoverflow) are littered with inefficient SQL that can easily be worked around.

Read: Many novices don't really understand indexes, especially 'composite' indexes.
Read: Certain design patterns are prone to inefficiency:  EAV, UUID.
Write:  Inserting one row at a time is literally 10 times as slow as at least two alternatives.

Most of the optimizations (read or write) are focused around the 16KB blocks that comprise the BTrees that both data and indexes are stored in.  Discussing the details there would take hours.

Another anecdote:  Later, Flickr sped up most reads by 50-100% by making essentially one index change.


Single user mode while restoring database Is it necessary to put the database into single user mode before restoring a database? If so which one is more preferable putting the sql server into single user mode or putting the database only into single user mode before restoring ! Thank you.

You would only need to put the SQL Server instances in single user mode if you were restoring the master database.  For user databases, you have to make sure there are no active connections to the database you're restoring.  You'd either have to determine and kill any active SPID's (which would not require the database to be in single user mode) or actually put the database in single user mode using one of the following (referencing a post by Greg Robidoux Getting exclusive access to restore SQL Server databases:

ALTER DATABASE [Test4] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
OR
ALTER DATABASE [Test4] SET SINGLE_USER WITH ROLLBACK AFTER 30
OR
ALTER DATABASE [Test4] SET SINGLE_USER WITH NO_WAIT

WITH ROLLBACK IMMEDIATE - this option doesn't wait for transactions
to complete it just begins rolling back all open transactions
WITH ROLLBACK AFTER nnn - this option will rollback all open
transactions after waiting nnn seconds for the open transactions to
complete. In our example we are specifying that the process should
wait 30 seconds before rolling back any open transactions.
WITH NO_WAIT - this option will only set the database to single user
mode if all transactions have been completed. It waits for a
specified period of time and if the transactions are not complete the
process will fail. This is the cleanest approach, because it doesn't
rollback any transactions, but it will not always work if there are
open transactions.

  Once the database has been put in single user mode, you have exclusive
  access to the database and can then do the restore without a problem.

  Note: when using the ROLLBACK option you are rolling back any open
  transactions that still exist for the database. The rollback process
  should work without issue, but if you have very long running
  transactions the rollback process could take a long time, so be aware
  of what is running on your systems. For test and development systems
  since you are doing a restore you don't care about the transactions
  anyway, so rolling things back should not be an issue, but you still
  need to be aware that long running transactions may take some time to
  rollback.


cannot connect to sql server in single user mode I have started my SQL Server 2008 sqlcst02\inst01 in Single User Mode by appending the ;-m to the end of the startup parameters for this instance in SQL Server Configuration Manager. I have stopped SQL Server Agent for the inst01 instance. My account is a member of the Domain Admin group and I have attempted to login using sqlcmd -S sqlcst02\inst01 and keep getting an error that the service is in Single User Mode and only one administrator can be logged in. I tried adding ;-m"sqlcmd" to the startup parameters to limit the connection to just sqlcmd but I get the same results. I have tried this logged in as my own account and logged in as the local administrator account. I have started the command prompt as myself and as administrator but I get the same results. I have tried connecting using ADMIN:sqlcst02\inst01 in SSMS. No luck.

I'm attempting to reset the sa password as none of this information was left for me by the previous admin. This is maddening. I've done this before on other machines with no issues.

Another alternative to recovering access to a SQL Server is outlined in this article, and doesn't require restarting in single user mode:

http://www.mssqltips.com/sqlservertip/2682/recover-access-to-a-sql-server-instance/

Essentially, download PSExec, then run it with the following command line (fill in your location of SSMS.exe):

PsExec -s -i "C:\Program Files (x86)\...\ssms.exe"

You will be prompted to connect - the authentication mode will be Windows auth and hard-coded to NT AUTHORITY\SYSTEM. Once connected, you will be able to go into security and fix the sa password, add yourself as admin, add other admins, etc.

The article above has several caveats you should read before trying this, and you may also want to review the comments at the bottom.


Query to get all schemas in a database in PostgreSQL? I'm not looking for the command \dn of psql, but instead of a query in SQL language that selects all schemas in a given database.

select * from information_schema.schemata;

Have a look at Postgres Docs:

  The view schemata contains all schemas in the current database that the current user has access to (by way of being the owner or having some privilege).

|                          Name | Data Type      | Description                                                                        |
|------------------------------:|----------------|------------------------------------------------------------------------------------|
| catalog_name                  | sql_identifier | Name of the database that the schema is contained in (always the current database) |
| schema_name                   | sql_identifier | Name of the schema                                                                 |
| schema_owner                  | sql_identifier | Name of the owner of the schema                                                    |
| default_character_set_catalog | sql_identifier | Applies to a feature not available in PostgreSQL                                   |
| default_character_set_schema  | sql_identifier | Applies to a feature not available in PostgreSQL                                   |
| default_character_set_name    | sql_identifier | Applies to a feature not available in PostgreSQL                                   |
| sql_path                      | character_data | Applies to a feature not available in PostgreSQL                                   |


Reviewing all code on a SQL Server Instance Shortly our development team will be moving an existing SQL Server 2008R2 instance into our Production environment. Ahead of this I have been asked to review all the code on it for anything malacious (e.g. wiping their own order history from the sales database).

This has come out of the blue and I'm not sure it's particularly physically feasible but I'm willing to give it a good go, at least from the SQL side of things. The question is, where can code exist? My draft list is

Stored Procedures
Functions (all)
Table Triggers
Assemblies (shouldn't be any by our developers)
Rules (shouldn't be any by our developers)
System Database versions of the above, modified by users
SSIS Packages
Server Triggers
Unexpected LinkedServers
SQL Agent Jobs
SQL Agent Alerts

Startup stored procedures run from master each time the instance starts.

Replication with this database as the subscriber.  Not code per se, but it will change data.


Setup mail notifications on SQL Server (severe) errors I want to be notified by email whenever an error occurs on our production database (e.g. failed database backups, engine crashes due to corruption etc). I could not find built-in support for this, so I wrote my own stored procedure that runs every 5 minutes.

My stored procedure takes the following steps:

Store the results from sp_readerrorlog (last x minutes) into a temporary table
Remove all records in that table that are on my ignore list (such as database back succeeded messages).
If there are any entries left, send an email to me (containing the last x minutes of the error log).

While this appears to work fine there is a major flaw: If the SQL Agent Service is not running I'm not getting notified about errors.

Therefore I'm wondering, there must be a better way to do what I want?

If the SQL Agent Service is not running I'm not getting notified about errors.Therefore I'm wondering, there must be a better way to do what I want?

Few options

1.Monitor from different server

IF EXISTS(
SELECT 1
FROM MASTER.dbo.sysprocesses
WHERE program_name = N'SQLAgent - Generic Refresher')

2.Use a Third Party Monitoring service like SQLSentry

Also i don't see a reason why you need to Parse SQL error log .SQLServer alerts can be used to catch most of the errors/Performance conditions

below article has more details on how to set up alerting for different scenarios

https://www.simple-talk.com/sql/database-administration/sql-server-alerts-soup-to-nuts/

And this article also shows  ,few more ways to monitor  using audits


Same query way faster with distinct than without distinct I have a query that runs very fast with distinct than without distinct. when running both queries, the one with distinct takes 13% execution time, the one without distinct takes 87% execution time.

These are TSQL query in SQL Server 2014 on a database with 110 compatibility mode.

Strange thing is that the query with distinct is using seeks while the query without distinct is using 1 scan. Both query are identical and have the same where clause.

Can you help me understand why the query with the distinct is faster and why the query without distinct is not using a seek ?

Query plans : www.brentozar.com/pastetheplan/?id=ryr18jJsb

Queries:

select
    SMS_R_SYSTEM.ItemKey
   ,SMS_R_SYSTEM.Name0,SMS_R_SYSTEM.SMS_Unique_Identifier0
   ,SMS_R_SYSTEM.Resource_Domain_OR_Workgr0
   ,SMS_R_SYSTEM.Client0
from vSMS_R_System as SMS_R_SYSTEM
   inner join Add_Remove_Programs_DATA
      on Add_Remove_Programs_DATA.MachineID = SMS_R_System.ItemKey
   inner join Add_Remove_Programs_64_DATA
      on Add_Remove_Programs_64_DATA.MachineID = SMS_R_System.ItemKey
where Add_Remove_Programs_DATA.DisplayName00 = 'aze'
   or Add_Remove_Programs_64_DATA.DisplayName00 = 'aze'
;

select distinct
    SMS_R_SYSTEM.ItemKey
   ,SMS_R_SYSTEM.Name0,SMS_R_SYSTEM.SMS_Unique_Identifier0
   ,SMS_R_SYSTEM.Resource_Domain_OR_Workgr0
   ,SMS_R_SYSTEM.Client0
from vSMS_R_System as SMS_R_SYSTEM
   inner join Add_Remove_Programs_DATA
      on Add_Remove_Programs_DATA.MachineID = SMS_R_System.ItemKey
   inner join Add_Remove_Programs_64_DATA
      on Add_Remove_Programs_64_DATA.MachineID = SMS_R_System.ItemKey
where Add_Remove_Programs_DATA.DisplayName00 = 'aze'
   or Add_Remove_Programs_64_DATA.DisplayName00 = 'aze'
;

From the way I read your execution plans, your DISTINCT query is selecting columns only from vSMS_R_System, so the optimizer chooses to read that data first (retrieving the DISTINCT values used in your SELECT) and then uses a NESTED LOOP to join up against the other tables.

The non-DISTINCT query forces the optimizer to assume that many more rows might need to be read from vSMS_R_System, so it chooses a MERGE JOIN to scan the other tables and then a NESTED LOOP to match up against vSMS_R_System.


Dump PostgreSQL schema with foreign keys and referenced data The official PostgreSQL documentation states that pg_dump can use the --schema=SCHEMA option to selectively dump a given schema.

However according to the same documentation (read Note on --schema=SCHEMA) foreign-keys to objects outside the selected schema will be included in the dump, but not the tables which they refer to.

With that said, I would like to know how to dump a given schema in a way that it includes all the needed tables regardless of the schema they are in and that this is done in a way that is safe/easy to restore the dump.

Additionally, are there any tools available that do it already?

Thanks

I am not aware of any tools to do exactly what you are asking right now.  I want to suggest a few options you might have overlooked however that may meet your needs.

Database Cloning

If your database is not being used 24/7 you can clone it for test cases.  This would be done using during times when the db is not in use:

createdb -U postgres new_dbname -T old_dbname

This does a file-level copy of the database, and so it preserves all data and is much faster than a dump and reload.

I very frequently use this technique for cases where I want to run test cases on one db where I can't guarantee that everything will roll back.

Base Backup and Restore

A second thing you can do if you want to bring all data onto another server is to set it up for replication as a master with at least one wal_sender, and use pg_base_backup to clone your db cluster over the network.  Note this is a file-based clone, and so it will be a very network-intensive process (more than a dump) but restore will be a lot faster.

Writing your own Tool

One of the big difficulties in having a generally available tool is that cross-schema dependencies could be quite complex and handling all possible complexity is a bit of a daunting task.  However for specific purposes this isn't too hard.  I would recommend three steps, using pg_dump to get you most of the way there, and then having a script written in one language or another to add what we can hope are modest missing pieces.  I think you could even do it with an sql script run through psql.

Dump schemas only of tables you need.  Create new file.  Something like: pg_dump -t mytable1 -t mytable2 -t mytable3 > my_partial_dump.sql
Export selected data to csv, and wrap using COPY statements.  This could be done using psql along with echo and COPY TO STDOUT.  This is appended to the partial dump.
Dump the schema as you are, and append this to the partial dump.

The advantage of a purpose-built tool if this is what you need is that you can manually manage the complex part of it and thus have a smaller, simpler tool than you might otherwise require.


Linked Server Timeout - Not Queried I have a job that runs for multiple regions across the globe. Depending on what region is running and what time zone it's in, the job might not run. It uses GOTO to get to the end and reports that it was skipped due to time and completes.

This runs as expected with no issues.

The Issue: there are times when a linked server farther down the proc is under really heavy load. We know about it and cannot avoid it - so we don't query the linked server at this time.

But, we're still getting an error as if it's queried:

  Cannot obtain the schema rowset "DBSCHEMA_TABLES_INFO" for OLE DB
  provider "SQLNCLI11" for linked server   returned message "Unspecified
  error". [SQLSTATE 01000] (Error 7412)  OLE DB provider "SQLNCLI11" for
  linked server   returned message "Query timeout expired". [SQLSTATE
  01000] (Error 7412).  The step failed.

Are linked servers validated when a proc starts causing this schema check that times out?

This is a  guess, but I would try enabling lazy schema validation for the linked server under heavy load:

EXEC master.dbo.sp_serveroption @server=N'Linked_Server_name', @optname=N'lazy schema validation', @optvalue=N'true'

  If this option is set to false, the default value, SQL Server checks
  for schema changes that have occurred since compilation in remote
  tables. This check occurs before query execution. If there is a change
  in the schema, SQL Server recompiles the query with the new schema.

  If this option is set to true, schema checking of remote tables is
  delayed until execution. This can cause a distributed query to fail
  with an error if the schema of a remote table has changed between
  query compilation and execution.

Source

As to only check the linked server metadata when querying the linked server itself.

Another workaround could be using dynamic sql statements to query the linked server

DECLARE @SQL nvarchar(max)
IF (Linked Server not under heavy load = true)
BEGIN
SET @SQL =
N'SELECT * FROM OPENQUERY(LinkedServer,''SELECT * FROM master.sys.databases'')'
EXEC SP_EXECUTESQL @SQL
END


Transactional replication using articles filters and delete operations I have a problem with my transactional replication when I use articles, filters, and delete operations. When I insert the new data into the publisher all the data that meets the specified filter is inserted to the subscriber - everything works fine. When I delete some data from the publisher, not all data that meets the specified filter is deleted from the subscriber and I don't know why.

I have three servers:

A – publisher (Microsoft SQL Server 2012)
B – distributor (Microsoft SQL Server 2012),
C – subscriber (Microsoft SQL Server 2012).

There are six tables in my publisher database. Each table has a primary key and some of them have foreign key as shown below:

I want to replicate only data for the first manufacturer (manufacturer ID=1) from the publisher to the subscriber, so I use these filters:

SELECT <published_columns> FROM [dbo].[Manufacturer]
WHERE [dbo].[Manufacturer].ID IN (Select M.ID from [dbo].[Manufacturer] AS M where M.ID = 1)

SELECT <published_columns> FROM [dbo].[Catalog]
WHERE [dbo].[Catalog].ID IN (Select C.ID from [dbo].[Catalog] AS C
INNER JOIN [dbo].[Manufacturer] AS M ON M.ID = C.MID where M.ID = 1)

SELECT <published_columns> FROM [dbo].[Cars]
WHERE ID IN (Select CA.ID from [dbo].[Cars] AS CA
INNER JOIN [dbo].[Catalog] AS C ON C.ID = CA.CID
INNER JOIN [dbo].[Manufacturer] AS M ON M.ID = C.MID where M.ID = 1)

SELECT <published_columns> FROM [dbo].[CarParts]
WHERE ID IN (Select CP.ID from [dbo].[CarParts] AS CP
INNER JOIN [dbo].[Cars] AS CA ON CA.ID = CP.CarID
INNER JOIN [dbo].[Catalog] AS C ON C.ID = CA.CID
INNER JOIN [dbo].[Manufacturer] AS M ON M.ID = C.MID where M.ID = 1)

SELECT <published_columns> FROM [dbo].[Parts]
WHERE ID IN (Select P.ID from [dbo].[Parts] AS P
INNER JOIN [dbo].[CarParts] AS CP ON CP.PID = P.ID
INNER JOIN [dbo].[Cars] AS CA ON CA.ID = CP.CarID
INNER JOIN [dbo].[Catalog] AS C ON C.ID = CA.CID
INNER JOIN [dbo].[Manufacturer] AS M ON M.ID = C.MID where M.ID = 1)

SELECT <published_columns> FROM [dbo].[PartDetails]
WHERE ID IN (Select PD.ID from [dbo].[PartDetails] AS PD
INNER JOIN [dbo].[Parts] AS P ON P.ID = PD.PartID
INNER JOIN [dbo].[CarParts] AS CP ON CP.PID = P.ID
INNER JOIN [dbo].[Cars] AS CA ON CA.ID = CP.CarID
INNER JOIN [dbo].[Catalog] AS C ON C.ID = CA.CID
INNER JOIN [dbo].[Manufacturer] AS M ON M.ID = C.MID where M.ID = 1)

When I insert the new data into the publisher's CarParts, Parts, or PartDetails tables, all the data that meets the specified filter (manufacturer ID=1) is inserted at the subscriber.

When I delete data from the publisher CarParts, Parts, or PartDetails tables, not all data that meets the specified filter (manufacturer ID=1) is deleted from the subscriber - it only deletes data from the CarParts table, not from Parts or PartDetails.

The same filter is used for insert and delete operations. What could be the problem? Maybe it is a Microsoft SQL Server 2012 bug?

The problem was with delete operations sequence.

Problem solved.


how to force postgresql to filll work_mem? i'm currently investigating a situation where my PostgreSQL 9.3 instance was killed by the Linux OOM Killer and went to recovery mode. The machine is EC2 Server with 16 GB of RAM and has rather few connections and is used for Business Analsys purposes only.

My assumption is that the reason for the out of memory situation is on one hand a very huge statement but also the fact that work_mem variable is set to 2GB while at the same time 100 connections are allowed.

And here are my questions:

is my assumption correct
if so, how can i force postgres to fill the session memory in a controlled manner to make the situation repeatable

I hope you guys can help me soon, thanks in advance and have a good week.

You definitely have too high memory settings. What you can do in your situation:
1) Lower work_mem
2) Lower max_connections

If you want to completely get rid of OOM-killer coming to your postgres server, then you should set kernel parameter vm.overcommit = 2 is sysctl.conf. This will forbid kernel to allocate more memory than it really have.

Important notes about this parameter:

1) Tune our memory-related settings. If something will try to allocate memory when it is full, you'll get Can't allocate memory problem. This can end even in no possibility of launch any bash command from command line because of no memory enough to fork().

2) Create relatively small swap area and set vm.swappiness to some low value (like 5 maybe, default is 60). This will save some nerves in case of trouble.


efficient primary key that does not expose business internals I am using auto_increment primary key to store each task user performs. Users will be given the key to perform queries about their tasks.
Keys are short, efficient, but are auto incremented in a predictable way.

If a user creates 2 tasks and is given a key that auto increments for both of them, than he can calculate how many tasks happened in that time interval, giving him unwanted insight.

To combat that, I thought about using UUIDs in place of auto_incremented key. But this has it's drawbacks too. Its 32 chars long when dashes are stripped, and as far as I understand has a significant performance hit. The table in question won't have more than a million rows so I am not sure how this applies.

The last part of UUID is MAC address of a node. I will be using only 1 machine, so that part can be stripped of, making it 20 chars.

My currently favourite option is to use something like SELECT FLOOR(rand() * 10000000000);  and than check if that number already exists.

Should I consider any other method of storing non predicatable primary keys? Which option do you recommend?

First make sure that it is important that the user doesn't know this information. Is it really a problem if they know that 12 other tasks were recorded between they last two? What could they do with this information that would cause problems?

If you are concerned that they could potentially access data they should by tweaking client-side requests (asking for getdata.php?recordid=1233, someone else's record, instead of getdata.php?recordid=1234, one of their records) then the problem doesn't lie in the knowledge that other records exist but in that the application isn't properly validating requests and/or performing checking security. If that is the case then you can solve the issue in the application layer by request forgery checking and/or privilege checks on every request (I'll not cover these in detail here as it is stepping away from the core topic of the question).

If there is a legitimate concern with using incrementing numbers, is the extra storage required for UUIDs really that expensive compared to the problem you are trying to combat?

You don't state which database engine you are using, but parts of the question strongly suggest mySQL. Other databases can handle UUIDs better by dealing with them as a native binary type, taking 16 bytes instead of 32 characters. It is a while since I've used mySQL, do modern versions support this or similar? That is still four times the size of an integer though.

You could translate the keys in the application layer by bit swapping: every time you send an ID client-side switch the bits around (swap bit 1 with bit 3, bit 4 with bit 0, ...) and translate back when receiving IDs before using them to query the database. This means you are obfuscating the IDs without altering the storage efficiency of the data but you are adding extra complication and potential bugs and inefficiency into your application(s). A seasoned hacker might see through this obfuscation, especially given a sizeable amount of data to analyse, but someone less determined will just see a random/arbitrary set of numbers. There are other such translations you could use: shift the number of 8 bits and fill in the bottom 8 with a simple hash of the original number perhaps.

Pushing the "scale and add some random bits to the bottom" idea into the database you could have a combined key that is the auto-increment value plus another integer or smallint whose value defaults to something arbitrary/random. In SQL Server you could do this in one column by using a user defined sequence to populate a BIGINT column, other DBs might offer something similar, though for many inserts there would be a performance consideration here. In fact even without the bigger type and randomness a sequence shared by all the increments in your data model may have the obfuscating effect you are looking for.

There are many imaginative ways you could make your keys more arbitrary, but they all add complexity somewhere in either the DB or the application so I'd go back to my initial response: do you really need to? If the need is real then I'd be inclined to bite the bullet and use UUIDs, type 4 UUIDs that are fully arbitrary rather the type 1s that include the MAC and so have some order, and if not I'd stick with increment integers for efficiency.


spliting a row based on max values I have a table with 3 fields and one is a Qty. If the Qty is > than a set amount I would like to split the row into a second row and/or third row

Row   Order      Product     qty
1     123        AAA         25

Based on a max Qty of 10 I would like it to become

Row   Order      Product     qty
1     123        AAA         10
2     123        AAA         10
3     123        AAA         5

-- set chunk size
WITH cte0 AS ( SELECT 10 chunk
             ),
-- calculate max chunks count
     cte1 AS ( SELECT 1 + MAX(qty / chunk) chunks
               FROM test, cte0
             ),
-- generate numbers
     cte2 AS ( SELECT 1 num
               UNION ALL
               SELECT num+1
               FROM cte1, cte2
               WHERE num < chunks
             ),
-- prepare data
     cte3 AS ( SELECT num, Product, CASE WHEN qty > num * chunk
                                         THEN chunk
                                         ELSE qty - (num - 1) * chunk
                                         END qty
               FROM test, cte0, cte2
               WHERE CASE WHEN qty > num * chunk
                          THEN chunk
                          ELSE qty - (num - 1) * chunk
                          END > 0
             )
-- perform action
MERGE test
USING cte3
ON (test.Product = cte3.Product AND cte3.num =1)
WHEN MATCHED THEN
    UPDATE SET Qty = cte3.qty
WHEN NOT MATCHED THEN
    INSERT (Product, Qty)
    VALUES (cte3.Product, cte3.qty);

fiddle


Get Json field value from Json I want to get "OrderNumber" field value from below mentioned json string. Pleas suggest best way to get the same

DECLARE @JSON NVARCHAR(MAX)=

  '{"ErpOrderResponses":[{"OrderSubmitStatus":"Ok","TransactionId":"{5EAB3ADC-EB9B-47F7-98B8-1449E2CC0A6B}","OrderNumber":7000027934,"CustomerNumber":80038707,"Description":"Request Successful","ResponseCode":"Success","ResponseType":"None","Errors":[]}],"IsErpUnavailable":false,"IsSuccessful":true,"IsPartiallySuccessful":false,"IsValidationFailed":false,"IsFailed":false}'

    DECLARE @OrderNumber BIGINT =
    (SELECT REPLACE(REPLACE(SUBSTRING(@JSON,CHARINDEX('OrderNumber',@JSON)+1,
                                                        CHARINDEX(',',@JSON,
                                                                        CHARINDEX('OrderNumber',@JSON)+1
                                                                )-CHARINDEX('OrderNumber',@JSON)
                                                        )
                                                        ,'RDERNUMBER":',''),',','') )
    --SELECT CAST( @OrderNumber AS BIGINT)

    SELECT  @OrderNumber

Assuming you are on SQL Server 2016 or greater, you can use the JSON_VALUE function to parse out an individual value:

SELECT  JSON_VALUE(@JSON,'$.ErpOrderResponses[0].OrderNumber');

If you are going to be processing more than one row, I recommend you use OPENJSON for better performance:

SELECT
    *
FROM
    OPENJSON(@JSON,'$.ErpOrderResponses')
    WITH (OrderSubmitStatus varchar(20),OrderNumber bigint);

Documentation: https://docs.microsoft.com/en-us/sql/t-sql/functions/openjson-transact-sql?view=sql-server-2017


Is this table in 1NF? Here are the columns of an SQL Table:

Course_Code
Course_Name
Tutor_ID
Tutor_Name
Student_No
Student_Name
DateOfBirth
Gender
LastAttDate

By checking all the NF1 requirements below, I believe this table is NF1.

All data is atmoic
There is no grouping of columns.
Each column can be identified by a primary key. (Course_Code)

But in this website where I got that question, they make some modifications on the table to make it NF1. I believe that these modification was unnecessary and table was already in NF1.

am I missing something here?

In the site that you have cited, note the parentheses at the right of the attributes in the second part of the initial column:

There is no context in the picture, but the obvious interpretation of such parentheses is that they denote repeated attributes: in other words, for each course (for which there is only one tutor), there are multiple students. So the notation (actually not very clear) should denote a non-flat situation, in which the 5 attributes are repeated.

This is clearly not a relation (or a "table" in 1NF), and the first step in the normalization is to divide these data in two different tables, one for courses and one for students, and of course add to each row of the second table,  which should represent a student, a foreign key towards the course to relate that particular student to a particular course.

Related Q & A on Computer Science SE: Does 2NF require 1NF?


Should I capture all the relationships in a Database design? I am a newbie when it comes to database design. I was trying to create a blogging application and I ran into a problem. My database consists of the following tables:

User
HasInt (Records which user has which interest)
Interest (category of blogs: Sports, Literature etc)
Follow (Records which user follows which user)
Blog
Comment
From what I realise, I should not have anything to do with knowing how many comments have been made for any particular Interest. But I can see that there is a many to one relationship between Comment and Interest. Should I still record this information ?
If so, why ?

Second what @bbaird says. I usually start with the objects I'll be dealing with. (user, category, blog, comment).
Then I'll start thinking about how they are connected.

A user is interested in zero to many categories
A user can follow another user
A user can create a blog
A user can create a comment
A comment is applied to a blog
A blog belongs to a category

To cover the links that are to one to many or many to many, I'll use joining tables that look like this;
UserToCategory (PK int, UserID int, CategoryID int)
UserToBlog  (PK int, UserID int, BlogID int)
Obviously there's a bit more to fully flesh out the plan, but this gets things started. If your foreign keys are built right, you can run a query to get all comments where the blog category is sports, joining across a couple of tables at once.


Replication - Binary log parsing(Tableid generation, ON DELETE CASCADE handling, Changed columns for update event) I am using MySQL 5.5 with InnoDB engine. I have set binlog format as row-based replication.

I have some doubts regarding replication:

1) How is the tableID generated? Is it stored somewhere?

2) Say I have Table T1(parent), T2(child), T3(child). T1 has a FK(ON DELETE CASCADE) relationship with its children: T2 & T3. If a row is deleted from T1, then will a DELETE event be generated for all T1, T2 & T3?

3) Is there any way to get info about the changed columns in case of an UPDATE event?

These are interesting questions with answers that are not too easy to come by.  The internal structures are sometimes not very well documented or the documentation is difficult to find and more than just a little bit frustrating.

Part 1: table_id

The table_id in a row-based replication TABLE_MAP_EVENT is not a value ordinarily exposed to the user, though you'll see it in the output of mysqlbinlog with --verbose and --base64-output=decode-rows.

#120820 13:42:23 server id 10  end_log_pos 681420677    Table_map: `xifc`.`action_log` mapped to number 4025219

This value comes from an incrementing global counter in sql/sql_base.cc.  It increments every time a table is added  to the cache of open tables that is limited in size by the global variable open_table_cache... so if that value is smaller than the number of tables being periodically accessed, you should see it increment more frequently.  All of the tables will have a new id on the next generated binlog event after a FLUSH TABLES.

Since a table appears to be closed and opened every time it is altered, the table_id apparently has no choice but to increment every time a table is altered, but this does not mean than an increment in the table_id for a given table necessarily means that the structure has changed.  My testing reveals that the table_id changes even when a column is renamed, even though all of the data types for the table are identical.

In summary: the table_id for a table always changes when the table is altered, and sometimes changes even when it has not been altered.  The counter resets if the server is restarted.  The TABLE_MAP event contains the table_schema and table_name.

Part 2: ON DELETE CASCADE ...not in the binlog?

I was surprised to find that, unlike row changes caused by triggers, cascaded deletes in child tables caused by foreign key constraints don't seem to show up in the binary log.  Then, I found this explanation and a moment's reflection makes it apparent why this might be true.

I'll give you a hint: it's the same reason MyISAM doesn't do foreign key constraints... namely, that's because MySQL itself doesn't do foreign key constraints... InnoDB does.  They're handled internal to InnoDB, apparently outside of the visibility of that part of MySQL server that handles binary logging, and rely on the slave to have the same constraints defined.  When the rows are deleted on the slave, InnoDB on the slave handles it, so from a replication perspective, they aren't "needed" in the binary log.

Part 3: Row Changes in UPDATE events and identifying columns

When a row is updated, an UPDATE_ROWS_EVENT is written, containing before and after images of the row in question.  (Inserts only contain an after image, deletes only contain a before image).  Prior to MySQL 5.6.2, each event contains the full before and after row image, which is what I like.  Arguably, the space savings for only logging what is needed for replication into the binlog doesn't justify the loss of potential recovery data, but a new variable binlog_row_image was introduced in 5.6.2 to allow you to change how many columns' worth of information are stored in the log.

Assuming this is set full, which would be the same as the old behavior, you get column values in the row events from every column that isn't null, in the row image (and null flags row columns that are null), and you get column type definitions in the table_map events that is sent at the beginning of every transaction for each of the tables that will be involved.  From this, you can extract the column values, ordered by their ordinal position within the table, but you don't get column names.  They are not in the binary log.

I have been working on a project that generates live events for background processes by emulating a slave server, connecting to the master (or a downstream slave with log_slave_updates enabled), requesting a binlog stream, decoding it, and generating a JSON representation of the event to be sent into a message queue (SQS or Stomp) or topic (SNS or Stomp).  I had to make an architectural decision: provide an array of the column values by ordinal position in the table and require the processes consuming the events to sort them out... or try to figure out the column names for inclusion in the event as an associative array (hash).

I decided to go with the column names, and I accomplished this by asking the server -- on a second connection that I keep open for the purpose -- to DESCRIBE table_schema.table_name every time I see a table_id that I had not seen before, and I cache it in memory until I see the same table mapped to a different id.  I also clear my cache if I see a ROTATE_EVENT in the binlog, since MySQL rotates the binlog if the server crashes or shuts down, and the table_id values restart.

The answer here ties back in with the answer on item #1.  Every time a row event occurs after a table is altered, the table shows up with a new ID, so if you fetch the table definition each time you see a new table_id, but cache the definition against the old id until it changes, you're pretty safe, though not precisely so: if a table is altered more than once in rapid succession and rows are changed between the alter events, it's possible for you to see a table definition that is newer than the one that was active when the binlog event was generated.  This becomes "more true" if your parser can't keep up with the rate at which the server is generating events.  Of course, in among the binlog events, you'll also see the ALTER TABLE event get replicated as a query event, but it is replicated exactly as it was entered by the client, so you'd have to parse, tokenize, and understand it, in order to make use of it... but this condition is very difficult to actually produce and unlikely to be a major concern.  I my implementation, I handle it by using the lesser of the number of columns in the binlog or the number of columns in the table definition, with the assumption that the primary keys are the most interesting columns, and usually at the left, and unlikely to be encoded incorrectly even if this condition is encountered.  A single alter on an active table works perfectly because I've already cached the definition under the table_id and am still using it to decode/encode row events until I see the new table_map event, which prompts me to fetch a new definition.

While working on my project -- which I am using on the fringes of production and plan to release under an open source license if it's ever truly "finished" -- something I found very helpful in figuring out some of the internals (even though I don't know Ruby) was the source code to Jeremy Cole's mysql_binlog.  Especially decoding of packed decimals. :(


Getting collation confict in IF statment and need help resolving it please A SQL script I am writing is generating the error:

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

I am not familiar with this error and don't know how to deal with it. I have been able to determine it is caused by the last IF check in the script as once I remove it the script runs fine. Can anyone help me figure out how to fix this issue? Here is my current code:

DECLARE @Source varchar(25);

SET @Source = 'DOD';
/*
Source options are:
    DOD
    SPRAGUEENERGY
    SRAPROD
*/

IF (@Source = 'DOD')
BEGIN
    USE DOD;

END

IF(@Source = 'SPRAGUEENERGY')
BEGIN
    USE SPRAGUEENERGY;

END

IF (@Source = 'SRAPROD')
BEGIN
    USE SRAPROD;
END

Well, that is interesting. I created your 3 databases using different collations for each database.  I tried starting from different databases to see the results.  Naturally I do not know which collations you used on each database, but apparently the SQL Server is (in fact) aware of this.

FIRST - SQL_Latin1_General_CP1_CI_AI
DOD - SQL_Latin1_General_CP850_CI_AS
SPRAGUEENERGY - SQL_Latin1_General_CP850_CI_AS
SRAPROD - SQL_Latin1_General_CP1_CI_AI

Depending on the database I start with (I tried all 4) some give collation errors and some do not. So...  The answer is:

IF (@Source COLLATE DATABASE_DEFAULT = 'DBNAME' COLLATE DATABASE_DEFAULT)

This raises no errors no matter which database I start with.


Debug stored procedure that times out from year to year We have a stored procedure that joins a couple of tables and returns json. We've added indexes and it returned the expected results without any issues for months. Btw, here's a snapshop of the live query statistics for an execution:

Today, and after being in production for more than a couple of years, it suddenly stopped working. Whenever it gets executed, it times out and doesn't return anything. Restarting the instance solved the issue and it's fast again. When it was timing out (ie, before we restarted the instance), I've tried running the query to see if there was any index suggestions, but it kept timing out.My colleagues say that CPU and memory weren't issues (CPU was pretty low and more than 50% of memory was available).
As expected, logs (sql and machine) show nothing out of the ordinary.
Interestigly, the reamining queries work as expected.
Any clues on how to debug this issue? I'd really like to understand what happened, but not sure on what to check...

If you are using parameters, then it sounds like a classic case of parameter sniffing. This article gives a better explanation that I could do right now: https://www.brentozar.com/archive/2013/06/the-elephant-and-the-mouse-or-parameter-sniffing-in-sql-server/
Another thing to check is, in the execution plan, the estimated number of rows vs. the actual number of rows. If you find they are mismatching then you can do an update statistics on that table (or index) which will do 2 things: update the statistics (obviously) + clear out the bad plan so that a new and hopefully good one will get created. This is the official documentation for the update statistics command: https://docs.microsoft.com/en-us/sql/t-sql/statements/update-statistics-transact-sql?view=sql-server-ver15
If you find this is what is causing the problem then for a long term fix take a look at what and when is causing your statistics to be out of date and why SQL Server is not updating them automatically. In my experience it's either somebody messing around with the settings and turning off the auto-update stats, or that table has grown very big and the threshold for the auto-update of stats is not reached anymore (This is a nice article about that: https://www.sqlskills.com/blogs/erin/understanding-when-statistics-will-automatically-update/ )


SSAS 2016 tabular : Discrepancy on countrows between measures visual studio vs Excel Is anyone aware of a SSAS (tabular) 2016 in which the number of records in a table are not fully shown in Excel?

Say that when working in Visual Studio, a table has a rowcount of 740,000 records.
We process and deploy that table, but in Excel it shows only 12,000 records.
also funny enough, when we deploy the model in VS it says that it only processed 12,000 records (yet the rowcount in the model is clearly 740,000 records).

(we ensure that Excel is connected to the deployed Tabular Model and not the user's workspace copy in Visual Studio)

We then change the table properties in VS to apply a filter, then we remove the filter, to see if this somehow "resets" this strange behaviour. It seems to do it.
However the problem seems to arise again during the next data load on the underlying database.

This only seems to happen with ONE table (all other tables are fine). So we tried recreating the table, but the same problem persists. Any idea of what might be causing this issue?

The solution was to change the processing in Visual Studio (VS) from Default to Full.

The issue is described in detail here: https://connect.microsoft.com/SQLServer/feedback/details/685325/processing-ssas-in-tabular-mode-not-refreshing-data (see Cathy Dumas's reply under "Posted by Microsoft on 8/25/2011 at 2:02 PM")


Multiple SELECT INTO I have a ~300 million row table with a column that has 9 different values. I need to be able to SELECT INTO 9 different new tables based on this column.

At the moment it has to do 9 separate table scans, as I just run 9 separate queries. Is there a way I could do this in one table scan with a CASE statement that defines the table to go into, or something similar?

I'm using SQL Server 2012 x64 Standard Edition SP1. There is no covering index as the column list is almost all of them. Example of what I'm trying to achieve:

SELECT [col1]
    ,[col2]
    ,[col3]
    ,[etc]
INTO CASE
        WHEN col1=N'tbl1' THEN [dbo].[tbl1]
        WHEN col1=N'tbl2' THEN [dbo].[tbl2]
    END
FROM [dbo][tblBase]

There is no clustered index on the base table. There is an index on col1 but it chooses not to use it. Forcing it increases the cost dramatically as it does an RID Lookup.

The base table is 68GB, with SQL Server max server memory being 30GB (although that's increasing to ~50GB soon).

SELECT INTO <table-decided-by-code>

You can't dynamically set the destination table like that.
If the target tables are part of a partition set partitioned on that column then SQL Server would do this for you, but you are using 2012 standard edition so that won't be possible (partitioned tables were an enterprise only feature until 2016sp1).

  Forcing it increases the cost dramatically as it does an RID Lookup.

Have you verified that this produces a slower result? Sometimes the query cost derived for plans by the planner doesn't not match actual performance.

If you are only copying a few columns from the target table, perhaps you could include those in the index with something like CREATE INDEX [ix_tblBase_col1_plus] ON ([col1]) INCLUDE ([col2] ,[col3]). This will increase the size of the index but remove the need for the extra page lookups by RID, but as you say you are taking most of the rows this is unlikely to be practical.

  There is no clustered index on the base table.

Adding any old clustered index would not help, you'll just replace the extra lookups by RID with extra lookups by clustering key.

You could try defining [col1] as your clustering key. When clustering on a non-unique key SQL Server adds a 4-byte "uniquifier" column which is appended to the indexed values. If your column is 4 bytes long or less this will result in a clustering key no larger than RID values. If the column contains wider data then space efficiency may be an issue as that extra data will be added to every row in every non-clustered index. You would need to experiment to see what difference this makes to the size of the table+indexes and any impact on other parts of your application (we could offer educated guesses if you provide a full table definition, and if your "col1" is variable length some information on the pattern of its values, but trying it in a dev copy of your DB would be more definitive).


AlwaysOn Availability Groups: Confusion with some concepts In Sql Server 2012, I am trying to install Failover Clustering to configure AlwaysOn Availability Groups. When I run Cluster Validation test wizard, it comes back with a lot of errors. One of them points to not having privileges to create 'Computer Object' in our Active Directory. So, I tried to gather any documentation that would help me understand what this means and I found this article on MSDN

Now, I have a question:

In the MSDN article, the paragraph right above this diagram talks about some 'Computer Account' and 'Computer Account for Clustered Service'. I re-read the paragraphs several times, but I am not sure if I can distinguish between these two. Could someone with experience on AoAG, shed some light on what this means.

What it means is that the computer object of the cluster (within Active Directory) will have the same set of the permissions as the user that created the cluster (and in turn, the cluster computer object).

This cluster computer object will then be the security context when a cluster service or application needs to have a computer object created.

Think of it like this: Joe creates a cluster and the cluster computer object is Cluster1.  When a computer object of the cluster needs to be created, it will be under the context of Cluster1. And Cluster1 will have the permission set of Joe.

Take a look at a blog post I wrote on this very topic (SQL Salt).  Maybe that will shed a little more light on this. When you create a listener, for instance, auditing AD will show that it is the cluster computer object that is actually creating the listener computer object.

In many environments this elevated security for computer objects may be undesirable. Therefore you can pre-stage these objects within AD. The end result should be the same.


Oracle automatically unmount I have a weird issue in my Oracle database, after 10 - 15 minutes of not using it (Not performing any query, insert on SQL Developer tool) then it will say ORA-27101: shared memory realm does not exist so I have to login through sqlplus and do startup. After that I can now use my database again then after 10 - 15 minutes same thing happens.

Below is the alert log but I can't understand it

Sun Jan 24 19:03:29 2016
Errors in file c:\oracle\diag\rdbms\orcl\orcl\trace\orcl_smon_1076.trc  (incident=43416):
ORA-00600: internal error code, arguments: [6856], [0], [102], [], [], [], [], [], [], [], [], []
Incident details in: c:\oracle\diag\rdbms\orcl\orcl\incident\incdir_43416\orcl_smon_1076_i43416.trc
Process debug not enabled via parameter _debug_enable
Sun Jan 24 19:03:34 2016
PMON (ospid: 6464): terminating the instance due to error 474
Sun Jan 24 19:03:54 2016
Instance terminated by PMON, pid = 6464

--
................................
--- End of Call Stack Trace -----
----- Process Map Dump -----
------------------- Memory Map of process ----------------
Start Addr-End Addr   Type     Size        ModuleName
Unab
0x00400000-0x06a4afff Image    0107261952  C:\ORACLE\PRODUCT\11.2.0\DBHOME_1\BIN\ORACLE.EXE
0x575c3a43-0xc6c0a8ab Image    1868852841  C:\WINDOWS\SYSTEM32\USER32.DLL
0x74bf0000-0x74c3afff Image    0000307200  C:\WINDOWS\SYSTEM32\MSWSOCK.DLL
------------------- End of memory map --------------------

----- Guard Frame Metadata Dump -----

internal error ORA-600 seen on the error stack
process appears to be having problems repeatedly, committing suicide
Background_Core_Dump = partial

Ok, that error (ORA-00600 [6856]) is related to corruption.

Normally a DBA would go to My Oracle Support, follow the notes (for example    ORA-600 [6006] ORA-600 [6856] During Startup Instance, Followed by Termination by SMON (Doc ID 549000.1)), identify the affected objects then try to repair them, recover corruption, or restore a valid backup.

If you can not do that, and this is a sandbox/development database with no important data, in my opinion the easiest and fastest solution for you would be recreating your database with DBCA (Database Configuration Assistant).


Concatenation of setof type or setof record I use Postgresql 9.1 with Ubuntu 12.04.

In a plpgsql function I try to concatenate setof type returned from another function.

the type pair_id_value in question is created with create type pair_id_value as (id bigint, value integer);

the function that returns elementary setof pair_id_value (those that will be concatenated later) is this one:

create or replace function compute_pair_id_value(id bigint, value integer)
    returns setof pair_id_value
as $$
    listResults = []
    for x in range(0,value+1):
        listResults.append({ "id": id, "value": x})
    return listResults
$$
language plpython3u;

this straigth-forward plpython code should be good, for exemple the query: select * from compute_pair_id_value(1712437,2); returns nicely:

  id            | value
 ---------------+-----------
        1712437 |         0
        1712437 |         1
        1712437 |         2
 (3 rows)

this python function is fairly simple for now, for this example, but above all for my proof of concept. It will be more complex in the near future.

The problem arises when I try to concatenate all the result tables from multiples id.

create or replace function compute_all_pair_id_value(id_obj bigint)
    returns setof pair_id_value as $$
declare
    pair pair_id_value;
begin
    for pair in (select compute_pair_id_value(t.id, t.obj_value) from my_obj as t where t.id = id_obj)
    loop
            return next pair;
    end loop;
    return;
end; $$ language plpgsql;

I receive the error: invalid input syntax for integer "(1712437,0)" as if it is no longer seen as a pair_id_value with two columns but as a tuple (1712437,0).

So I changed the output type of the function from setof pair_id_value to setof record... and if I execute this similar concatenation function:

create or replace function compute_all_pair_id_value(id_obj bigint)
    returns setof record as $$
declare
    pair record;
begin
    for pair in (select compute_pair_id_value(t.id, t.obj_value)  from my_obj as t where t.id = id_obj)
    loop
            return next pair;
    end loop;
    return;
end; $$ language plpgsql;

I get the error: a column definition list is required for functions returning "record"

Trying to follow the answer to this SO question:  I have tried defining the column definition in the select this way select compute_pair_id_value(t.id, t.obj_value) as f(id bigint, value integer), the complete code is here:

create or replace function compute_all_pair_id_value(id_obj bigint)
    returns setof record as $$
declare
    pair record;
begin
    for pair in (select compute_pair_id_value(t.id, t.obj_value) as f(id bigint, value integer) from my_obj as t where t.id = id_obj)
    loop
            return next pair;
    end loop;
    return;
end; $$ language plpgsql;

But when launching the sql script, psql doesn't accept to create the function:
syntax error at or near "("  select compute_pair_id_value(t.id, t.obj_value) as f(id bigint, value integer) ... pointing the finger at the f(

Any idea how to do it properly ?

Should I consider to create temporary table to do the job ?

The approach you're using is unnecessarily complex - and very inefficient. Instead of the first function use:

create or replace function compute_pair_id_value(id bigint, value integer)
    returns setof pair_id_value
as $$
SELECT $1, generate_series(0,$2);
$$
language sql;

or better, get rid of it entirely and write the whole operation like this:

-- Sample data creation:
CREATE TABLE my_obj(id bigint, obj_value integer);
insert into my_obj(id,obj_value) VALUES (1712437,2),(17000,5);

-- and the query:
SELECT id, generate_series(0,obj_value) FROM my_obj;

Resulting in:

regress=> SELECT id, generate_series(0,obj_value) FROM my_obj;
   id    | generate_series
---------+-----------------
 1712437 |               0
 1712437 |               1
 1712437 |               2
   17000 |               0
   17000 |               1
   17000 |               2
   17000 |               3
   17000 |               4
   17000 |               5
(9 rows)

This exploits PostgreSQL's behaviour with set-returning functions called in the SELECT list. Once PostgreSQL 9.3 comes out it can be replaced with a  standards-compliant LATERAL query.

Since it turns out your question was a simplified version of the real problem, let's tackle that. I'll work with the simplified compute_pair_id_value above to avoid the hassle of plpython3. Here's how to do what you want:

SELECT (compute_pair_id_value(id,obj_value)).* FROM my_obj;

Result:

regress=> SELECT (compute_pair_id_value(id,obj_value)).* FROM my_obj;
   id    | value
---------+-------
 1712437 |     0
 1712437 |     1
 1712437 |     2
   17000 |     0
   17000 |     1
   17000 |     2
   17000 |     3
   17000 |     4
   17000 |     5
(9 rows)

but again, be warned that compute_pair_id_value will be called more than once. This is a limitation of PostgreSQL's query executor that can be avoided in 9.3 with LATERAL support, but as far as I know you're stuck with it in 9.2 and below. Observe:

create or replace function compute_pair_id_value(id bigint, value integer)
    returns setof pair_id_value
as $$
BEGIN
  RAISE NOTICE 'compute_pair_id_value(%,%)',id,value;
  RETURN QUERY SELECT $1, generate_series(0,$2);
END;
$$
language plpgsql;

output:

regress=> SELECT (compute_pair_id_value(id,obj_value)).* FROM my_obj;
NOTICE:  compute_pair_id_value(1712437,2)
NOTICE:  compute_pair_id_value(1712437,2)
NOTICE:  compute_pair_id_value(17000,5)
NOTICE:  compute_pair_id_value(17000,5)
   id    | value
---------+-------
 1712437 |     0
 1712437 |     1
 1712437 |     2
   17000 |     0
   17000 |     1
   17000 |     2
   17000 |     3
   17000 |     4
   17000 |     5
(9 rows)

See how compute_pair_id_value is called once per output column?

There is a workaround: Another layer of subquery to unpack the composite type result. See:

regress=> SELECT (val).* FROM (SELECT compute_pair_id_value(id,obj_value) FROM my_obj) x(val);
NOTICE:  compute_pair_id_value(1712437,2)
NOTICE:  compute_pair_id_value(17000,5)
   id    | value
---------+-------
 1712437 |     0
 1712437 |     1
 1712437 |     2
   17000 |     0
   17000 |     1
   17000 |     2
   17000 |     3
   17000 |     4
   17000 |     5
(9 rows)

You can use the same technique in your code if you really must LOOP over the results (it's slow to do that, so avoid it if you can).


ORACLE: Changing the grouping of a data to be based on month breakdowns To keep this short and to the point, I am looking at modifying the grouping for this SQL query to go from NAME to Month/yy

Here is a example of the script, and screenshots of the output file

This is what I am trying to accomplish.

Any ideas on how to approach this?

SQL SCRIPT:

SELECT A.NAME
     COUNT(DISTINCT(CASE WHEN TO_CHAR(A.INVDATE,'MM') = '10' THEN A.ORDER_NO ELSE NULL END)) AS OCT11_ORDERS
     , COUNT(CASE WHEN TO_CHAR(A.INVDATE,'MM') = '10' THEN A.ORDER_NO ELSE NULL END) AS OCT11_LINES
     , COUNT(DISTINCT(CASE WHEN TO_CHAR(A.INVDATE,'MM') = '10' THEN A.INVOICE_NO ELSE NULL END)) AS OCT11_INV_COUNT
     , COUNT(CASE WHEN TO_CHAR(A.INVDATE,'MM') = '10' THEN A.INVOICE_NO ELSE NULL END) AS OCT11_INV_LINES
-- NOV 11
     , COUNT(DISTINCT(CASE WHEN TO_CHAR(A.INVDATE,'MM') = '11' THEN A.ORDER_NO ELSE NULL END)) AS NOV11_ORDERS
     , COUNT(CASE WHEN TO_CHAR(A.INVDATE,'MM') = '11' THEN A.ORDER_NO ELSE NULL END) AS NOV11_LINES
     , COUNT(DISTINCT(CASE WHEN TO_CHAR(A.INVDATE,'MM') = '11' THEN A.INVOICE_NO ELSE NULL END)) AS NOV11_INV_COUNT
     , COUNT(CASE WHEN TO_CHAR(A.INVDATE,'MM') = '11' THEN A.INVOICE_NO ELSE NULL END) AS NOV11_INV_LINES
-- DEC 11
     , COUNT(DISTINCT(CASE WHEN TO_CHAR(A.INVDATE,'MM') = '12' THEN A.ORDER_NO ELSE NULL END)) AS DEC11_ORDERS
     , COUNT(CASE WHEN TO_CHAR(A.INVDATE,'MM') = '12' THEN A.ORDER_NO ELSE NULL END) AS DEC11_LINES
     , COUNT(DISTINCT(CASE WHEN TO_CHAR(A.INVDATE,'MM') = '12' THEN A.INVOICE_NO ELSE NULL END)) AS DEC11_INV_COUNT
     , COUNT(CASE WHEN TO_CHAR(A.INVDATE,'MM') = '12' THEN A.INVOICE_NO ELSE NULL END) AS DEC11_INV_LINES

FROM test.test1 A
WHERE TO_DATE(A.INVDATE) BETWEEN TO_DATE('01-OCT-11') AND TO_DATE('30-SEP-12')
AND COMPANY = '30'
AND CUST_GRP IN ('3','4')
GROUP BY A.NAME

OUTPUT FILE:

DESIRED OUTPUT

For the desired output, all of the columns should be counts

Thanks!

I figured it out,
had to group by trucn(invdate,'mm') and count the separate columns

SELECT TO_CHAR(TRUNC(inv.invdate,'MM'),'MON-RR') "Month",
         COUNT(DISTINCT(inv.order_no)) "Orders",
         COUNT(inv.order_no) "Order Lines",
         COUNT(DISTINCT(inv.invoice_no)) "Invoices",
         COUNT(inv.invoice_no) "Invoice Lines",
         COUNT(DISTINCT(inv.name)) "Companies"
FROM test.test1 inv
GROUP BY TRUNC(inv.invdate,'MM')


Moved database to new server, not sure about NT AUTHORITY\* users I just moved database to new instance of SQL Server (SQL Server 2012 Web from Amazon EC2) and am now cleaning up database users.

I am not sure if/why these 2 users are needed (they exist in original database):

NT AUTHORITY\IUSR
NT AUTHORITY\NETWORK SERVICE

Note that there are also 2 database schemas with exactly the same names.

I am more clear on NT AUTHORITY\IUSR - this seems to be a user for IIS. IIS is not even installed on the new instance, so this one probably can be deleted.

I am not sure about NT AUTHORITY\NETWORK SERVICE (or how it got created to begin with - new databases created on new server don't have it, or NT AUTHORITY\IUSR)

Neither of these 2 users exist at server level on a new instance or on an old instance.

These users are built in accounts to Windows and they're usually hidden from view. If you know that your application isn't using those users then you can delete them.

However, it sounds like you're moving a database that was used in ASP.Net applications. ASP.Net does use both those accounts under the hood. NT AUTHORITY\IUSR for anonymous access and
NT AUTHORITY\NETWORK SERVICE is used in ASP.Net application pools. If that's the case, make sure that you no longer need to access the database you moved with ASP.Net and you'll be golden.

http://www.iis.net/learn/get-started/planning-for-security/understanding-built-in-user-and-group-accounts-in-iis

https://msdn.microsoft.com/en-us/library/ff647402.aspx


MySQL used all drive space running an ALTER and now won't start I tried to alter a table without paying attention to the size, and MySQL ate up all the free space on the drive. I deleted a million or so old records but the space was still being used.

Googling suggested that restarting the MySQL server would help, but the server won't start:
[FAIL] /etc/init.d/mysql: ERROR: The partition with /var/lib/mysql is too full! ... failed!

So now I'm a bit stuck. My database only has one (very big) table:

# ls -lh
total 18G
-rw-r--r-- 1 mysql mysql    0 Sep 25  2014 debian-5.5.flag
-rw-rw---- 1 mysql mysql  18G Jul 13 15:17 ibdata1
-rw-rw---- 1 mysql mysql 5.0M Jul 13 15:17 ib_logfile0
-rw-rw---- 1 mysql mysql 5.0M Jul 13 15:15 ib_logfile1
drwx------ 2 mysql mysql 4.0K Jul 13 11:44 log
drwx------ 2 mysql mysql 4.0K Sep 25  2014 mysql
-rw------- 1 mysql mysql    6 Sep 25  2014 mysql_upgrade_info
drwx------ 2 mysql mysql 4.0K Sep 25  2014 performance_schema
drwx------ 2 mysql mysql 4.0K Dec  4  2013 test

So, specifically, my question is: how do I free up the drive space that MySQL used when I tried to alter a large table?

[edit]I should mention that I've got no space on any other drives, and I can't change the size of the drive either :-(

[edit2]In response to comments:

mysql> OPTIMIZE TABLE `emails`;
+------------+----------+----------+-------------------------------------------------------------------+
| Table      | Op       | Msg_type | Msg_text                                                          |
+------------+----------+----------+-------------------------------------------------------------------+
| log.emails | optimize | note     | Table does not support optimize, doing recreate + analyze instead |
| log.emails | optimize | error    | The table '#sql-8aa_25' is full                                   |
| log.emails | optimize | status   | Operation failed                                                  |
+------------+----------+----------+-------------------------------------------------------------------+
3 rows in set, 1 warning (1 hour 12 min 10.62 sec)

# df -h
Filesystem      Size  Used Avail Use% Mounted on
rootfs           20G   19G     0 100% /
udev             10M     0   10M   0% /dev
tmpfs           100M  128K  100M   1% /run
/dev/xvda1       20G   19G     0 100% /
tmpfs           5.0M     0  5.0M   0% /run/lock
tmpfs           200M     0  200M   0% /run/shm

Find the file #sql-8aa_25 and remove it.  It is probably in the same directory where the database is.

If you did a kill -9, mysqld did not get a chance to remove the tmp file.


Configure postgresql recovery again to be slave I completed a replication setup using PostgreSQL. Everything was working fine, so I proceeded doing a test by bringing down the main database and promoting the slave to be new master (via touch trigger file).

Again,it worked as expected and the slave became a full read/write database. Now, I want to return everything to normal state which means starting up master database, copying all the transactions that were done on slave during the transition, and finally start again the replication on slave. How can I do this... there seems to be no docs about restoring everything to normal state. Should I just rename the recovery.done to recovery.conf?

Thanks a lot

Assuming you are using streaming replication, "no latency".
If you can stop writes or stay on the new master B, some steps below can be reduced

If you stop writes, before you failover back to old master A, it could be a master without any issues, most of the times.

But as you have mentioned that you have writes on the new master B after failing over to it, you will have to

resynchronize the old master A as a standby, using pg_start_backup-rsync-pg_stop_backup (and of course recovery.conf)
promote A using the trigger file, etc
restart B as a standby of A
if it doesn't start (due to continuous writes), you'll have to rsync it again as listed above

I would love to hear if there are other options :)


Query for hierarchical RBAC scenario I have a SQLite database with a hierarchical role-based access control (RBAC) data model.

In this model, users are granted permissions to perform certain actions against various resources either directly or by being a member of one or more roles to which the permissions are assigned. Additionally roles can be assigned to other roles such that a hierarchy of roles can be defined.

The schema is roughly like the following:

(from How to design a hierarchical role based access control system - StackOverflow)

With the exception that there is a Role_Role join table instead of a "parent" column on the Roles table, similar to the below:

(from same post as above image)

Also there is no "grant" column on the join tables; existence of a record in a join table means it is granted.

What would a query to get all of the permissions for a given user look like? I'm assuming using a recursive CTE would be involved, but I'm not sure.

Edit: I've got the following recursive CTE working for getting the roles inherited by other roles:

WITH RECURSIVE
  deep_roles(role_id, parent_id, depth) AS (
    SELECT role_id, parent_id, 1 FROM role_roles WHERE role_id = ?
    UNION ALL
    SELECT rr.role_id, rr.parent_id, dr.depth+1
      FROM deep_roles dr, role_roles AS rr
     WHERE rr.role_id=dr.parent_id
       AND dr.depth < 10
  )
SELECT DISTINCT role_id, parent_id FROM deep_roles;

Not sure if this is the only CTE I'll need but it's a start.

Here's what I came up with -- thanks to @CL. for the tips. I made it into a view so I can just query against that for a particular user.

CREATE VIEW user_permissions_deep AS
SELECT DISTINCT user_id, permission_id
FROM (
    SELECT urd.user_id user_id, pr.permission_id permission_id, urd.role_id role_id, urd.parent_id parent_role
    FROM (
        WITH RECURSIVE
          deep_roles(user_id, role_id, parent_id, depth) AS (
            SELECT ru.user_id, rr.role_id, rr.parent_id, 0 depth
            FROM role_roles rr
            INNER JOIN role_users ru ON rr.role_id = ru.role_id
            UNION
            SELECT dr.user_id, rr.role_id, rr.parent_id, dr.depth+1
            FROM deep_roles dr, role_roles rr
            WHERE rr.role_id=dr.parent_id
            AND dr.depth < 10
          )
        SELECT user_id, role_id, parent_id FROM deep_roles
        WHERE depth > 0
        UNION
        SELECT user_id, role_id, null parent_id
        FROM role_users
    ) AS urd
    INNER JOIN permission_roles pr
        ON pr.role_id = urd.role_id
        OR pr.role_id = urd.parent_id
    UNION
    SELECT user_id, permission_id, null role_id, null parent_id
    FROM permission_users
) AS upd
INNER JOIN permission p ON upd.permission_id = p.id;

I found I had to keep the depth check otherwise the loop would never exit if there was a cycle in the role inheritance chain.

If anyone has any suggestions on how this could be improved, please let me know!


Copy Data To Different Server using SELECT * INTO? I'm not a DBA by any means.  I'm just knowledgeable enough with the MySQL command line to be capable half the time and dangerous the rest.

Today I was working on exporting data from a one DB into another DB.  The exercise was to take production data back into staging.  Since the table name exists in both places, a new table needed to be created in staging with a name like "_prod_copy"

First I did an export on the production server and scp'd the file over to the staging server.  On staging, I created the table in staging using:

CREATE TABLE table_name_prod_copy AS (SELECT * FROM table_name);
TRUNCATE table_name_prod_copy;

Then I used the command line to import.

Everything worked great.

I did research afterward and found this command in an example:

SELECT * INTO newtable [IN externaldb] FROM table1;

I'm going to RTFM, but also wanted to ask here:

Is there a way to tell mysql that the other DB, 'externaldb' in this case is on another server?  I know there are command line ways to export from one DB server into another DB server using SSH.  Just wondering if this command could do something similarly internal to the tool.

Thanks,

Michael J

You might be interested in FEDERATED table engine. It allows a local server to see tables in different server. I don't recommend it in normal usage, but to move table data around, it could be an option.

http://dev.mysql.com/doc/refman/5.5/en/federated-storage-engine.html


Prefix index length impact for VARCHAR? I have a VARCHAR(255) primary field field in an InnoDB database table. I only store values of length 32 bytes in it (32 ASCII characters). My primary key has a prefix length of 767 bytes. Clearly, this longer prefix length than I need. Does this have any performance impact?

If it is always 32 ascii bytes, then say CHAR(32) CHARACTER SET ascii.

There are subtle reasons for not making the field bigger than necessary.  They mostly involve how temporary tables are generated in complex SELECTs.  Most importantly, the tmp table is MEMORY and turns VARCHAR(255) into CHAR(255) of the given charset.  If that is utf8, then you are unnecessarily using 755-32 extra bytes for each row in the tmp table.  This can lead to slowdowns.

"32" smells like hex for something?  UNHEX(...) and put it into BINARY(16) to make the PK even smaller.

Every secondary key (in InnoDB) implicitly includes a copy of the PK.  Making the PK bigger than necessary can impact disk space.


Is there any correct way to convert file system from ext4 to xfs in centos? I have deployed MongoDB Sharding Cluster(os-centos) on Azure with 2 disks each VM. But default file system is ext4 and I want xfs file system because of performance. I want to convert that file system.

Is there any way of converting file system without any configuration changes in mongo?

I tried below steps:

detach disk
unmount dir
attach disk
create partition with xfs file system
changes on fstab file
mount dir

create partition where enough space, format it to xfs
mount that partition to some directory
stop mongod process
rsync -aP src dst files from original mongod dbpath to this xfs mount
unmount original dbpath partition mount AND this xfs partition
mount xfs partition to path where mongod dbpath points
start mongod process

So, basically when mongod process is stopped, copy all dbpath files to new location/partition and then point mongod process to use that location...


PostgreSQL: Valid variable names for functions I cannot find any definitive information on what constitutes a valid variable name in a PostgreSQL function.

I am assuming the function is written using plpgsql. I know I can use letters, numerals (apart from the first character) and underscores. Is there anything else?

Where is this documented?

This is implicitly stated in the introduction to PL/pgSQL.

  Identifiers are implicitly converted to lower case unless double-quoted, just as they are in ordinary SQL commands.

So a PL/pgSQL variable has to comply with the rules for SQL identifiers


sp_Blitz Findings: SQL Server Last Restart is an hour before Server Last Restart Result of sp_Blitz:
Finding = “Server Last Restart” ; Details = “Feb 11 2017 5:48AM”
Finding = “SQL Server Last Restart” ; Details = “Feb 11 2017 4:48AM”

SQL Server Last Restart is the tempdb create_date
Server Last Restart is the ms_ticks

I find the results odd because, SQL Server Started an hour before Server Restart?
Am I missing something here?

I would 'suggest' that when the server was restarted (and Sql Server was also restarted) back on February 11, 2017, the current Windows time, which was Standard time and not Daylight Saving time, was captured as the Creation Date of TEMPDB (and that is what SP_Blitz uses to determine Sql Server restart time).

Now, you are running SP_Blitz AFTER the change to Daylight Saving time and sys.dm_os_sys_info (which is used by SP_Blitz to report Server Last Restart and appears to be Daylight Saving time aware) is affected by the time change, thus making it 'look' like the server started AFTER Sql Server.


IIS takes longer to run queries than SMSS I'm completely stumped on this one.
We're running a pretty usual intranet app here, classic ASP frontend and SQL Server 2005 backend.
Occasionally, once/twice a week, a certain stored proc takes ~50x longer to run when I run it in the Management Studio. This slowness lasts the entire day and it inexcplicably goes away the next day.

So essentially what's happening is this: once/twice a week, when I fire up the intranet page that runs the stored procedure and displays the results, it takes roughly 115secs to run.
I then fire up SQL Server Management Studio and run the same query, which this time around takes only 3-4secs to run.

The next day, the intranet page is taking as long as SSMS to run the very same stored procedure, with nothing having been changed in the interim (not to my knowledge anyway).

Other details:

the IIS connects to SQL Server 2005 using Provider=sqloledb as the driver
the slowdown lasts the entire day, regardless of the number of active users
I can replicate the slowness by running the same query from Excel, using the same driver/connection string
I've ran a trace on the SQL server and it turns out that on those particularly slow days, the duration of the stored proc is actually longer when executed via IIS than direct query

As such, I was wondering whether any of you has encountered any similar behaviour before, if you've fond a solution to it and what would your advice be in tackling/solving it?

Thanks for your help,

Please find Trace results in .csv format below:

EventClass,TextData,ApplicationName,CPU,Reads,Writes,Duration (s),ClientProcessID,SPID,StartTime,EndTime
12,exec spStubbornProc,Internet Information Services,"106,016","7,059,999",1,115.80,5476,85,18/04/2013 08:17:15,18/04/2013 08:19:11
12,exec dbo.spStubbornProc,Microsoft SQL Server Management Studio - Query,"3,141","146,051",0,3.40,5876,84,18/04/2013 08:20:45,18/04/2013 08:20:48
12,exec dbo.spStubbornProc,Microsoft SQL Server Management Studio - Query,"2,563","147,387",0,2.93,5876,84,18/04/2013 08:21:04,18/04/2013 08:21:07
12,exec spStubbornProc,Internet Information Services,"103,156","7,083,365",7,118.73,5476,80,18/04/2013 09:39:41,18/04/2013 09:41:40
12,exec dbo.spStubbornProc,Microsoft SQL Server Management Studio - Query,"2,406","175,029",1,2.57,5876,84,18/04/2013 10:08:58,18/04/2013 10:09:01
12,exec spStubbornProc,Internet Information Services,"112,218","7,103,267",7,114.72,5476,75,18/04/2013 14:06:26,18/04/2013 14:08:21
12,exec spStubbornProc,Internet Information Services,"93,515","7,101,229",3,93.60,6092,60,18/04/2013 19:37:02,18/04/2013 19:38:35
12,exec spStubbornProc,Internet Information Services,"2,500","148,775",1,3.42,5160,75,19/04/2013 09:16:01,19/04/2013 09:16:04

Usually this means that SQL is using cashed execution plan that just does not work any more. From SMSS it is recompiled and uses fresh execution plan. Try to force some index in queries, or rebuild indexes ? Definitely check execution plan for both queries.


PhpMyAdmin Missing parameter Everything was working fine until this moment I want to create a new database and I'm receiving this error

db_create.php: Missing parameter: new_db (FAQ 2.8)

Also when I was trying to export my database I also receive the following error

export.php: Missing parameter: what (FAQ 2.8)
export.php: Missing parameter: export_type (FAQ 2.8)

When I looked it FAQ 2.8 from the suggested link in PHPMYADMIN

  2.8 I get "Missing parameters" errors, what can I do?

  Here are a few points to check:

  In config.inc.php, try to leave the $cfg['PmaAbsoluteUri'] directive    empty. See also FAQ 4.7.
  Maybe you have a broken PHP installation or you need to upgrade your    Zend Optimizer. See http://bugs.php.net/bug.php?id=31134.
  If you are using Hardened PHP with the ini directive    varfilter.max_request_variables set to the default (200) or another
  low value, you could get this error if your table has a high number
  of columns. Adjust this setting accordingly. (Thanks to Klaus
  Dorninger for the hint).
  In the php.ini directive arg_separator.input, a value of ";" will    cause this error. Replace it with "&;".
  If you are using Hardened-PHP, you might want to increase request    limits.
  The directory specified in the php.ini directive session.save_path    does not exist or is read-only.

I did tried with php.ini to make sure that I've session.save_path = "/tmp"

I'm using Mac Xserver and running with MAMP I did tried everything to restart my server and nothing help.

Can someone please make a suggestion on the same?

My apologies if I have posted this in the wrong place!

This might also happen if you disable the cookies in your browser.
If this is the case, you may see this message:"Cookies must be enabled past this point" on the top of Phpmyadmin page.
All you need to do is to enable cookies on your browser, or use different browser to log to phpmyadmin.


Retrieving users by their age efficiently I have a PostgreSQL database of users and their birthdays and want to get all users in a specific age range:

SELECT * FROM users
WHERE age(birthday) >= 21 AND age(birthday) <= 30;

How can I achieve that this query stays efficient, even when there are millions of users in the database?
Of course there are more filters, I just don't want to calculate the age for every row.

I do not want to use a materialized view.

You want to move the computation from the column, to the other side of the inequality operators where you have literals rather than a column:

WHERE birthday >= current_date - interval '30 years' AND birthday <= current_date - interval '21 years';

Then a simple index on "birthday" will make it efficient.  It might need some fiddling depending on what datatype "birthday" is.  I tested it as a timestamp without time zone.


Trying to better understand how SELECT rights are managed in a/my SQL Server db Sorry, I'm a security noob:

I've been asked to provide write a script that grants our users that belong to some yet unidentified domain group SELECT access to a table called "NewTable1." To identify the global group, I was told to check out "ReportingTable1" and see who (which domain group) has SELECT access to that table and then grant the same group access to NewTable1.

So, in SSMS (2008 R2), I script ReportingTable1 making sure that the option to script permissions was first enabled. When I inspect the generated scripts for ReportingTable1, I only see VIEW DEFINITION rights granted to various domain groups.

Understanding that one might have rights to see the defintiion of an object but not necesarily the object itself, I realize that having VIEW access does not necessarily mean that one will have SELECT access.

Q: Why don't I see SELECT accesss granted when I clearly have select access?

I realize that for a different db, my local db when I am the admin, that I must have implied SELECT rights on all of the tables simply because I belong to the admin group. Therefore, belonging to a role must also give me access rights associated with that role, yet I am unable to determine what those rights are. Are they implied and well known for all of the built-in roles?

Obviously, I have a lot to learn. I expect flames and suggestions to buy a book and read.

You need to look at the Effective Permissions http://msdn.microsoft.com/en-us/library/ms178358.aspx

I've never understood why there isn't a version of fn_my_permissions http://msdn.microsoft.com/en-us/library/ms176097.aspx which simply takes a user.

To do that you have to EXECUTE AS the other user: http://sqlarea.blogspot.com/2008/04/fnmypermissions.html


Database mirroring - link failure between principal and witness I am using synchronous database mirroring with a witness and am testing the various failure scenarios and the impact on the mirroring session, such as whether failover occurs.

Starting with the principal, mirror and witness all being connected I break the link between the principal and witness (using some hackery with the hosts file and tcpview)

           W
           |
 P---------M

The DMVs correctly report this configuration - the principal says the mirror is connected but witness disconnected, mirror says both principal and witness are connected. The witness reports the session as being out of sync.

Next I kill the sqlservr.exe process on the principal, so the connection between P and M terminates:

           W
           |
 P         M

I would now expect failover to the mirror server as both M and W agree that P is unavailable and there is witness-to-partner quorum. However what happens instead is the database on the mirror goes into the Mirror/Disconnected mode and there is no failover.

Can anyone explain this behaviour? I'm using SQL 2008 SP3 on WS 2008 R2 SP1.

The problem is that the principal became disconnected from the witness server before it went offline. In this case, the witness cannot agree that the principal is offline because it was disconnected while the principal was still connected. As far as the witness knows, the principal may still be up.

In this scenario, it would require that you force service on the mirror to bring it online using ALTER DATABASE ... SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS.


PostgreSQL getting the latest value I have started working on a postgreSQL database. There is a table keeping the record of stocks and every time a record is updated, a copy of the original row needs to be kept, with a flag added to say that the record is no longer current.
I am planning to implement this using a Trigger (which I am assuming is the best option).

In the above case, with a flag to say that a record is not the current one, what's the best way to ensure that reads from the table always used the latest records? Only checking the flag value with where condition?

If it were me, rather than flagging current, I would just time stamp each entry. Then, you would not need to update anything. You would only need to insert a new row. This way, you can query sets of data to plot against time, as a side benefit.


@TimeLimit setting in Ola-Hallengren's IndexOptimize Running Much Longer Than The Value Set We start our indexing at 10:00 PM every evening and we want to allow it run until 4:30 AM, the next day.  Therefore, we set the @timelimit variable / parameter to 23400 seconds.  The issue is that the system continues to run well past the allotted time, by over 3607 seconds, or until I shut it down.
Thoughts on why it would not stop processing all commands, which is what the code is to promote via the variable?

As explained in the documentation (and evident from the procedure source code), TimeLimit is used to

Set the time, in seconds, after which no commands are executed.

(emphasis above is mine).
A command that started before the threshold will keep running until it completes.


SSIS packages fail with Pending Execution Status My packages have been running on our production environment for a couple of weeks until they started failing with very little information apart from packages having a pending execution status.

All packages fail regardless of the user executing them e.g. myself or the SQL Agent service account.

There is a cryptic error message in the windows log about activation permissions for the SQL Integration Services. Setting permissions for the accounts calling the package makes no difference,

I managed to get them working by repairing the SQL Server installation I couldn't see what repairs it had made.

Now following a reboot of the server all packages refuse to run. I have managed to get them to work by repairing the SQL Server installation again. This is obviously not a scalable solution.

I suspect that some group policy setting is overriding a local security setting and this is why it happens after some time or after a reboot.

Has anybody come across this issue before or have any ideas where to look.

If the packages are running as part of a SQLAgent job, check the job for what user it's running as.  Check those permissions, and that could be your problem.  I just had to repair a situation like that.  Permissions got yanked from the wrong users, and it broke everything...


Priority search on columns Description:

We are using PostgreSQL 9.3 - Centos 6 x64 . We have a dtsc_search_data table as below:

dtsc_search_data
id ---- c1 ---- c2 ---- c3
1  ----  1 ----  1 ----  1
2  ----  1 ----  2 ----  2
3  ----  1 ----  1 ----  3

We want to search on "c1, c2, c3" coloumns on dtsc_search_data table with conditions: if seach_value is found on "c1" then return ;  if search_value is not found on "c1" then finding on "c2", if search_value is found on "c2" then return ; else return (c3).

Example:

search_value = 1 => "c1" = 1 => just search on "c1"
search value = 2 => "c1" != 2, "c2" = 2 => just search on "c2".
search_value = 3 => "c3"

CODE (update)

create table dtsc_search_data (id int, c1 int, c2 int , c3 int) ;
 insert into dtsc_search_data values(1,1,1,1);
 insert into dtsc_search_data values(2,1,2,2);
 insert into dtsc_search_data values(3,1,1,3);

 -- search_value = 2
 -- find on c1 column first
     select *
     from dtsc_search_data
     where c1 = 2
 -- if c1 is not found then c2 -- get value from here
     select *
     from dtsc_search_data
     where c2 = 2

Question:

Our solution: write 3 SQL queries search on "c1", "c2", "c3" are followed by conditions above. How can we do that with lowest performance ?

A simple OR condition will do this:

select *
from dtsc_search_data
where c1 = 2
  or (c1 <> 2 and c2 = 2)
  or (c1 <> 2 and c2 <> 2 and c3 = 2);

SQLFiddle example: http://sqlfiddle.com/#!15/d1ca1/1


Queries logged in the 'Slow query log', and 'start transaction' vs 'set autocommit = 0' Currently long_query_time is set to 5, and that works.

However, many queries are part of a transaction, and the slow query log only keeps the final commit;.

# Time: 2018-08-15T15:18:18.205309Z
# User@Host: bijin[bijin] @ localhost [127.0.0.1]  Id: 3274529
# Query_time: 7.510110  Lock_time: 0.000000 Rows_sent: 0  Rows_examined: 0
use interferences_db;
SET timestamp=1534329958;
commit;                      # <=== only 'commit' is logged

Which is not very informative ( while it's correct, as seen from the db server ).

Is there a way to see the queries up to the commit, meaning all queries from the transaction?(or at least some part of it allowing to identify the culprit query)

~~~ edit ~~~

The answer from @AmitPatel had me perform some tests using

set autocommit = 0

instead of

start transaction

to initiate a transaction and, indeed, with autocommit = 0 the slow query log has the details of every slow query, even if they're eventually rollbacked.

So, in order to log slow queries that are part of a transaction, can I safely replace, in the code,

start transaction .... commit / rollback

with

set autocommit = 0 .... commit or rollback and set autocommit = 1 ?

( The final autocommit = 1 is to keep the same behavior in the code, after a transaction is rollbacked or committed )

Mysql run with autocommit feature , it look like your application code forcefully commit the each DML.


Data distribution in mongos with shards or replica sets I am a newbie to mongodb.I have set an mongodb architecture with a single mongo shard(mongos) connected to three shards(replica sets) with three config servers. The architecture looks as below:

Now the mongos are connected with these shards.The data in the mongos will be splitted among these three shards.What is my doubt is whether the shard 1 contains the same exact data as that of shard 2 and shard 3 or all the 3 shards have different data.

So let's look at the "roles" here represented in the referenced architecture. Keep in mind, this is the "2 cent tour" of what the architecture is all about.

Mongos

This process is the "router" in that it acts as the interface for a "client" to connect to a sharded cluster. You can have many mongos instances, so you are not limited to "one" only. As pointed to in the diagram, these communicate with the "config servers" in order to determine which "shard" to send requests to based on the query or operation submitted.

Config Server

These are the "brains" of a sharded cluster. Essentially they contain the "meta data" which says which "chunks" or "parts of the data" appear on which "shard" as it were. These stay consistent with each other in a "reasonably redundant way" in order to provide that information to the mongos "router".

Shard

Mostly a "meta" concept here. Though it is possible have a single server as a "shard" it is generally recommended not to and to use a "replica set" as a shard node otherwise. The purpose of a "shard" is to hold data "specific" to a "defined range". This works by a "shard key" that is "split" between "nodes" of the sharded cluster. The full nature of the "splitting" is determined by the "shard configuration" which lives on the "config servers" already described. The data on a "shard" only lives on that shard and "no-where else".

Replica Set

The purpose of a replica set is rather self explanatory. It essentially "copies" data from a given "Primary" node which is the only node that accepts "write" operations. This is done to what is one or more "Secondary" nodes. You only need one more "Secondary
 node for a replica set, but if you do that then you really need another "Arbiter" node in order to break the deadlock on elections.

What "elections" means is that when one member of the replica set goes down or is otherwise un-available, there is an "election" held to determine which one then becomes the new "Primary". There needs to be a "majority" vote here, hence the requirement for an "odd number" of members that can vote (not all member types can vote).

Summary

There is no "duplication" over shards. There may be a "brief" time in which the same data appears on one shard and another at the same time, but this is never reflected in the "config servers" ( if it is then this is corruption ). There is a process called "chunk migration" which is what all of this is about, where data allocated to one shard is "balanced" to exist on another. But the "meta data" mentioned earlier is "atomically updated" so there should be a consistent view of where the true source of data actually resides.

The "replica set" members are where there is intentional duplication. This is for data redundancy, where if one member goes down then there are other members to carry on the work. They "should" have a consistent view, but this generally is largely determined by "write concern" in your application as to the "guarantee" of consistency. Still, there may be problems if only rare. (But network partitions in the cloud happen more often than not).

The role of "shards" is opposite to the "redunancy" of replica sets. Their role is to "distribute" data. That means by definition that data is not "duplicated" over shards (moment case already given) so that general CRUD operations are either "distributed" or "targeted", across or directed to as appropriate the relevant "shard" that is going to deal with the data for your operation.

In real short. Shards are for "data distribution" in order to make operations scalable, by either "targeting" workload to a specific "shard node" or "splitting" the workload across many nodes. Whatever is appropriate to your application purpose.

Always different data across shard nodes, and that is what they exist for.