Return inserted entity in mysql stored procedure I want to have inserted data in stored procedure, because of of sql injection I use prepare and execute statement.
what should I've done to have the inserted data

following code is only a part of user table

DELIMITER ;;
CREATE PROCEDURE `UserCreate`
(
    IN `@Name`      VARCHAR(32)
)
BEGIN
    SET @Query = CONCAT
    (
        'INSERT INTO
            User
            (
                Name
            VALUES
            ('
                , '\'' , `@Name` , '\','
            ')'
    );

    PREPARE Statement FROM @Query;
    EXECUTE Statement;
    DEALLOCATE PREPARE Statement;

END ;;
DELIMITER ;

I use Binary uuid for having more security

That's not using "prepare" to do the escaping.  Put the IN parameter in the EXECUTE.

Please don't name parameters with @ -- it is too confusing with @variables.  I like to prefix in args with _.

A UUID is 36 characters; The binary equivalent is BINARY(16).  Your VARCHAR(32) does not agree with "I use Binary uuid for having more security".


SQL Server 2008 having issues with HAVING clause I have a question regarding a report I am currently writing.

I need this query to only show me line items that have < -5 as mins_elapse

Here is the code. I have left out the FROM clause to prevent forms of business identity.

select
    H.ORDER_NUM
    ,H.CUST_NUM
    ,H.CREATION_DATE
    ,H.DUE_DATE
    ,H.ORDER_PRIORITY
    ,H.ORDER_STATUS
    ,H.ORDER_TYPE
    ,H.TRAILER_ROUTE
    ,S.SHIP_ADDRESS_1
    ,S.SHIP_CITY
    ,S.SHIP_STATE
    ,S.SHIP_COUNTRY
    ,S.SHIP_POSTAL_CODE
    ,C.SERVICE_CODE
    ,C.CODE_TEXT
    ,SUBSTRING(pm.ship_time, 1, 2) + ':' + SUBSTRING(pm.ship_time, 3, 2) + ':' + RIGHT(pm.ship_time, 2) as manifest_time
    ,CONVERT(varchar(8), CURRENT_TIMESTAMP, 114) as curr_time
    ,--determine the difference between when the order was manifested and the current time and use it in the WHERE clause below
    DATEDIFF(MINUTE, SUBSTRING(pm.ship_time, 1, 2) + ':' + SUBSTRING(pm.ship_time, 3, 2) + ':' + RIGHT(pm.ship_time, 2), convert(varchar(8), current_timestamp, 114)) as mins_elapsed
where
    cust_num = @customer `
    and @service = s.service_code
    and @add1 = S.SHIP_ADDRESS_1
    and @zip = S.SHIP_POSTAL_CODE
    and H.ORDER_STATUS between 20
        and 90
    and DATEDIFF(MINUTE, SUBSTRING(pm.ship_time, 1, 2) + ':' + SUBSTRING(pm.ship_time, 3, 2) + ':' + RIGHT(pm.ship_time, 2), convert(varchar(8), current_timestamp, 114)) <= 5
    and h.large_order = 'Y' `
    and creation_date > 20120701 `
group by
    H.ORDER_NUM
    ,H.CUST_NUM
    ,H.CREATION_DATE
    ,H.DUE_DATE
    ,H.ORDER_PRIORITY
    ,H.ORDER_STATUS
    ,H.ORDER_TYPE
    ,H.TRAILER_ROUTE
    ,PM.SHIP_TIME
    ,S.SHIP_ADDRESS_1
    ,S.SHIP_CITY
    ,S.SHIP_STATE
    ,S.SHIP_COUNTRY
    ,S.SHIP_POSTAL_CODE
    ,C.SERVICE_CODE
    ,C.CODE_TEXT
    ,H.LARGE_ORDER
    ,H.ASSOC_DATE
    ,H.ASSOC_TIME`
having
    DATEDIFF(MINUTE, SUBSTRING(pm.ship_time, 1, 2) + ':' + SUBSTRING(pm.ship_time, 3, 2) + ':' + RIGHT(pm.ship_time, 2), convert(varchar(8), current_timestamp, 114)) <= 5
order by
    H.LARGE_ORDER desc
    ,H.ORDER_STATUS asc
    ,H.ASSOC_DATE asc
    ,H.ASSOC_TIME asc

If you are struggling with "Group By" queries than @Kenneth Fisher's answer might help or you need to provide more details same as asked by @dnoeth

I wanted to point out that all values in the image table are already less than -5 or 5. Because values are negative so -42,-71,-100 are less than -5 or 5. (In your question you said -5 but in query you have 5)

Second, about DateDiff returned result.
Syntax = DATEDIFF ( datepart , startdate , enddate )
so if enddate is today and if DateDiff result is positive, it indicates past duration,
if DateDiff result is negative, it indicates future duration.

If you are interested in order shipped in past 5 minutes you should look for

DATEDIFF(MINUTE,ShipDate,GETDATE()) > 0 AND DATEDIFF(MINUTE,ShipDate,GETDATE()) <= 5

(I am guesing this based on your column name, mins_elapsed indicates that you are interested in past duration.)

Third, In DateDiff you are considering only time values,
examine below queries,

DECLARE @ShippedYesterday DateTime, @ShippedToday DateTime;
SELECT @ShippedYesterday = DATEADD(DAY,-1,GETDATE()), @ShippedToday = GETDATE();
SELECT @ShippedYesterday, @ShippedToday;
SELECT convert(varchar(8), @ShippedYesterday, 114) AS YesterdayShippedTime, convert(varchar(8), @ShippedToday, 114) As TodayShippedTime;
SELECT DATEDIFF(MINUTE, convert(varchar(8), @ShippedYesterday, 114), convert(varchar(8), @ShippedToday, 114));
SELECT DATEDIFF(MINUTE, @ShippedYesterday, @ShippedToday);

second last query returns 0 while the last query returns > 1000.

If you store shipping date in your database than you should restrict it to today's date before using DateDiff with Current date.

Right now you are probably comparing today's 4pm to yesterday's or day before yesterday's 5pm and you get result -60 minutes.


Counterexample to demonstrate there are no functional dependencies For instance in the following example

Let be F={AB→C, B→D, CD→E, CE→GH, G→A}
Do we have AB→G?

We don't have any functional dependencies.
I am able to show a functional dependency when it works but how to show a counterexample when it doesn't?

To find if the functional dependency AB→G is implied by F you should find the closure of the attributes AB under F, i.e. AB+.

These are the steps:

AB+ = AB
      ABC     (using AB→C)
      ABCD    (using B→D)
      ABCDE   (using CD→E)
      ABCDEGH (using CE→GH)

and since G belongs to AB+, then AB→G can be derived from F.


Help me to map Column types betwenn SQL and TopSpeed Clarion Tables I am tasked to convert an old Clarion App to new one under SQL Server 2012.

All I have is 200 tables with TPS extension which I can open only by program called TopScan.
(No odbc, no technical documentation, no support).

I am making an C# program which is going to extract data to SQL.

Structure of data I can see in texts like this

                   FILE,DRIVER('TOPSPEED'),NAME('C:\bIRA2013\BIRA_ARHIMED\PART.TPS')
PART:K_SIFPAR        KEY(+PART:SIFPAR),NOCASE,PRIMARY
PART:K_ZIRO          KEY(+PART:ZIRO),DUP,NOCASE
PART:K_NAZIV1        KEY(+PART:NAZIV1),DUP,NOCASE
PART:K_GRUPA         KEY(+PART:GRUPA),DUP,NOCASE
PART:K_PORBR         KEY(+PART:PORBR),DUP,NOCASE
PART:K_KOMER_SIFPAR  KEY(+PART:KOMER,+PART:SIFPAR),DUP,NOCASE
PART:K_BANKA_SIFPAR  KEY(+PART:BANKA,+PART:SIFPAR),DUP,NOCASE
PART:K_GRAD_SIFPAR   KEY(+PART:GRAD,+PART:SIFPAR),DUP,NOCASE
PART:K_NACPL_SIFPAR  KEY(+PART:NACPL,+PART:SIFPAR),DUP,NOCASE
PART:K_NACPLD_SIFPAR KEY(+PART:NACPLD,+PART:SIFPAR),DUP,NOCASE
PART:OPIS            MEMO(2000)
RECORD               RECORD
PART:SIFPAR            STRING(10)
PART:ZIRO              STRING(20)
PART:PORBR             STRING(13)
PART:PORBRBIH          STRING(10)
PART:NAZIV1            STRING(40)
PART:NAZIV2            STRING(40)
PART:GRUPA             STRING(10)
PART:KUP               STRING(2)
PART:DOB               STRING(2)
PART:ADRESA            STRING(30)
PART:GRAD              STRING(25)
PART:PBROJ             STRING(5)
PART:TEL1              STRING(15)
PART:TEL2              STRING(15)
PART:FAX               STRING(15)
PART:OSOBA             STRING(30)
PART:RABAT             DECIMAL(5,2)
PART:RABATD            DECIMAL(5,2)
PART:CASSA             DECIMAL(5,2)
PART:CASSAD            DECIMAL(5,2)
PART:NACPL             STRING(2)
PART:NACPLD            STRING(2)
PART:KOEF              DECIMAL(5,4)
PART:DVO               LONG
PART:BANKA             STRING(6)
PART:VEZDOK            STRING(10)
PART:IZJAVA            STRING(10)
PART:KOMER             STRING(3)
PART:EMAIL             STRING(40)
PART:WWW               STRING(60)
PART:ID                STRING(2)
PART:D                 LONG
                     END
                   END

I think that ROWS between RECORD and END representing structure of table.
When I run through all tables I find following types of data as in list

STRING(10) --many sizes (10,20,100,200)
CSTRING(256) --many sizes (2000,3000)
LONG
DECIMAL(7,4)
BYTE
REAL
GROUP

How should I map these types to SQL Server types?

For me DECIMAL is only direct hit, but I am not so shure even for that.

Why I am so puzzled is that I can't find corresponding type for DATE.

Sub question is , How Clarion holds dates in tables ?

Personally, I would buy the odbc driver and import all the files with the SQL wizard
http://www.softvelocity.com/drivers/tsodbc.htm


RegisterAllProvidersIP 1 or 0 For The Cluster Resource I have set up clustering across two sites, at each site there is a single instance of SQL 2014 enterprise and I am using Availability Groups.

I know I need to set RegisterAllProvidersIP for the AG Network name in order for the listener to check both IP Addresses.

One thing I'm not sure about is if I need to set RegisterAllProvidersIP for the actual cluster name??

Object Name Value Type

??????AG01_??????01 RegisterAllProvidersIP 1 UInt32

Object Name Value Type

Cluster Name RegisterAllProvidersIP 1 UInt32 <-----?????????????

Thanks in advance

I believe the RegisterAllProvidersIP is set to 1 automatically when you create the listener. With this setting, all subnets' IP addresses will be attached to the listener name. This is great, unless you're using older clients that do not support the MultiSubnetFailover option. If you do have such clients, you'll end up getting client connection timeouts (in our testing, we have 2 subnets and we'd get 50% connection timeouts).

To work around this (because we have fun legacy code), we set RegisterAllProvidersIP to 0 which only allows the primary node's IP address to register as the listener in DNS. The downfall of this is that we then had to understand DNS replication such that, in the event of a failover, the domain controller may not be in DNS cache (which relies on TTL for when to refresh) so the instance may not be available for a bit after failover, until the cache refreshes. We decided upon a 5 minute HostRecordTTL value (instead of the default 20 minutes) since our second subnet would only be used in a DR exercise or situation and that 5 minutes would be absorbed in the other DR activities.

At any rate, here is how you can get and set those values on the local machine:

Import-Module FailoverClusters
Get-ClusterResource {{name}} | Get-ClusterParameter


Why has my database connection failed? Recently I have developed software using SQL Server 2012. Now when I try to connect, I get this error message:

  Cannot connect to AAFI\SQLEXPRESSA network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 26 – Error Locating Server/Instance Specified)

This is an extremely common error, with literally millions of search results online. The typical troubleshooting steps include:

Make sure the SQL Server service is actually running (SQL Server Configuration Manager > SQL Server Services > check the State of the specific instance and make sure it says "Running", otherwise right-click > Start).
Make sure the SQL Server Browser service is running, you can check this in Configuration Manager as above, or using sc query sqlbrowser from the command line (if it is not running, right-click > Start or run net start sqlbrowser from the command line).
If you are trying to connect to a different machine (or using a name that forces you to route outside of your machine for some reason), make sure the server is configured to allow remote connections. You can fix this by using remote desktop to connect to that server and running this locally:

EXEC sys.sp_configure N'remote access', 1;
GO
RECONFIGURE WITH OVERRIDE;

Of course this might be hard to do if it's the local instance you can't connect to, so you'll have to get connected locally using other steps in this list first before you'll be able to talk to the server to change this setting.
Make sure the port SQL Server is running on is open, and that TCP/IP is enabled (SQL Server Express Edition, for example, expects Named Pipes for remote connections by default). You can see this in Configuration Manager > SQL Server Network Configuration > Protocols for <instance name> > TCP/IP, right-click, properties, IP Addresses tab, scroll all the way down to see (a) that TCP/IP is enabled (and if not, right-click > Enable, may as well try enabling Named Pipes too), and check what port is listed under IPAll > TCP Dynamic Ports or TCP Port. Note that if it's using dynamic ports, that port could change, and if SQL Browser is not configured to start automatically, you'll be right back here potentially on your next reboot.
Make sure the server name is correct, and be sure to try IP address, simple server name, and fully-qualified domain name. If the server is local, try localhost and 127.0.0.1 in addition to the server name. Make sure you can traceroute/tracert to the server and telnet to the running port. If you can connect to one of these hosts but not others, either change your connection string, change your hosts file and/or DNS, or consult your network administrator.
Make sure the instance name is correct. Often people type their machine name and don't realize that they are trying to connect to a named instance, which requires syntax like server_name_or_ip\SQLEXPRESS - a common one as SQLEXPRESS is typically the instance name in use when running SQL Server Express Edition.
If you can't connect using any of those methods, it is likely a firewall issue. You'll need to determine which ports to open and for whom (of course your SQL Server should be secured from external traffic whenever possible), but you can quickly test if this is the problem by momentarily disabling the firewall altogether.

A lot of this is documented much more elaborately here (found in Paul White's wiki post here):

How to Troubleshoot Connecting to the SQL Server Database Engine


MySQL: Return JSON from a standard SQL Query I have read about JSON objects and the JSON object type.  I only want to do a select and it return JSON.  I do not necessarily want to store a JSON object.  Serialization per se it not my question.  The columns are regular Varchar, Int, etc. columns, no JSON Objects, "normal" database rows.

Can I do a regular old SELECT and return JSON for MySQL?

Isn't this what FOR JSON in SQL SERVER and rows_for_json do in PostgreSQL?

They seemed ahead in this are but I didn't want to fool myself.

I found this question from 2016:
https://stackoverflow.com/questions/35324795/mysql-5-7-return-row-as-json-using-new-json-features

Converting a row to json

It doesn't sound to me like you want to aggregate JSON. You state you want the equivalent of row_to_json, if so then I suggest checking out the much simpler JSON_OBJECT

SELECT JSON_OBJECT(
  'name_field', name_field,
  'address_field', address_field,
  'contact_age', contact_age
)
FROM contact;

Aggregating JSON

As a side note, if you do need to aggregate a resultset to json. then the upcoming MySQL 8 will do that for you.

JSON_ARRAYAGG()    Return result set as a single JSON array
JSON_OBJECTAGG()`   Return result set as a single JSON object


Issue migrating from SQL Server 2008 R2 to SQL Server 2017 Standard (on Linux) I am currently trying to carry out a database migration from SQL Server 2008 to SQL Server 2017 (Linux) using the DMA tool.

I get the all green after running the assessment, however I get hit with an "out of memory" exception during the migration process.

Any advice on how I can resolve this issue? Both source and destination servers have 16GBs of RAM. This should be sufficient, no?

There is a similar, but not exactly the same, question here:

Azure SQL Data Migration Assistant is Stuck on "Migrate Data"

However, it does explain the limitation you're hitting:

  The Extract service connects to a database, reads all of its objects and their properties, and then creates an in-memory model of the database.

If the database you're trying to migrate has an exceptional number of schema objects, this in-memory DAC model potentially overwhelms the client application / system (Data Migration Assistant running on your PC in this case), resulting in the OutOfMemoryException.

Your next best bet would be to use tried and true approaches like backup / restore in order to migrate to the new machine.


Hiding or masking sensitive information in the SQL Server audit log I'm looking into auditing of who are accessing the data in all tables in a database. We do have the additional requirement that we in the log can't have any information that can be used to identify a person.

The first part can easily done using SQL Server audit to log all select, insert and delete statements for a specified database. It is the second part that I have problem with. The whole SQL statement including any personal information in e.g the where clause will be stored in the 'statement' column. This meaning that whoever that have access to the audit log will be able to see this information and identify the person in question. We do not need the information that are stored in the 'statement' column. The information stored in 'database_name', 'schema_name' and 'object_name' columns are enough for us.

My question is are there any way to prevent SQL Server audit to store the SQL statement at all or at least in clear text in the 'statement'column?

I got the following answer from Emily Fei at msdn Forums

  No, SQL Server Audit can't be able to choose the event field name. The
  Audit log file will record the full event field name.

Seems the only way to achieve something like this is to restrict the access to the raw audit log and then publish a subset of the log for more general use.


timeout on updating nvarchar(max) after converting datatype I recently Upgraded to 2008 Enterprise and took the opportunity to change the biggest table in my db with the maintenance window.

ran the following to change my ntext field

ALTER TABLE acomEstabLocale_tbl ALTER COLUMN [description] nvarchar(MAX)
GO
update acomEstabLocale_tbl set [description] = [description]

when I ran the second statement i got the following

length of LOB data (77404) to be replicated exceeds the exceeds configured maximum

ok, so after a poke around I found the sp_configure and ran :-

EXEC sp_configure ‘max text repl size’, 100000

but now I receiving the following

nvarchar max Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.

I'm looking to temporarily extend this query timeout period, can the just be done using the remote query timeout setting on the server properties ? or is there a T-SQL statement for just this query

I'm not sure exactly what the purpose is of your entire-table-update, but perhaps the timeout is because your query is causing:

stats to be created
triggers to be fired
massive writes to the log (and potentially you are waiting on log file auto-grow - instant file initialization doesn't help you here)

If this table is replicated, the database is mirrored, or you've added CDC/Change Tracking then I think it is going to be much worse. If the table is participating in replication, you should read this topic.

What is the update supposed to do? It's too late now for this case but in the future an alternative way to do this for other tables might be:

add a new column with the new data type
populate it in batches with values from the old column, and use transactions or checkpoints to minimize impact to the log (and subsequent features that use the log)
once done, rename or drop the old column, then rename the new column to the old name

Of course this still has to be done in a maintenance window. You don't want users contending for writes during 2.


Effect of an index on update statements where update column is not in an index I constantly see people say that indexes slow down update, delete and insert. This is used as a blanket statement, as if it is an absolute.

While tuning my database to improve performance, I keep coming across this situation that seems to contradict that rule logically for me, and nowhere can I find anyone say or explain in any way otherwise.

In SQL Server, and I believe/presume most other DBMS, your indexes are created based on specific columns you specify. Inserts and deletes will always affect an entire row, so there is no way they will not affect the index, but updates seem a bit more unique, they can specifically affect only certain columns.

If I have columns that are not included on any index and I update them, are they slowed down just because I have an index on other columns in that table?

As an example, say in my User table I have one or two indexes, the primary key which is an Identity/Auto Increment column, and possibly another on some foreign key column.
If I update a column without the index directly on it, like say their phone number or address, is this update slowed down because I have indexes on this table on other columns in either situation? The columns I am updating are not in indexes, so logically, the indexes should not be updated, shouldn't they? If anything, I would think they are sped up if i use the indexes in the WHERE clause.

For a relatively fast modern system the addition of a single index to an OLTP table will probably be virtually undetectable from a performance standpoint for the vast majority of systems.  That said, you should not create needless indexes, and you probably should not create single-column indexes for every column in a table.

You are correct in the assumption that for many queries the presence of useful indexes will result in a very noticeable speed improvement.

Although your question appears to be around performance, there are several other potential issues around adding indexes, including but not limited to:

Time required to create the index may result in blocking while the index is added to the table.  The lock is very short lived, and most likely won't create a big problem.
Index changes result in execution plans being invalidated for any plans that reference the underlying table.  When those execution plans are recompiled, performance may change negatively for some queries.
Index modifications may result in queries returning errors where none were previously returned.  Take the case of a filtered index that was used to return dates contained in a varchar field; if the filter eliminated any rows that weren't dates, and that filter is subsequently changed, queries that relied upon that index may now fail when attempting to convert non-date data.
A new index may cause the order of execution to change resulting in possible deadlocks occurring where non occurred before.


How is TINYINT(1) converted to BOOL/BOOLEAN? I can understand that if you UPDATE a TINYINT(1) column and pass a value of 1 or 0 it will convert it to TRUE or FALSE, respectively.

I'm just confused on what happens when you UPDATE the column with a different value, say 2. In other words, if you UPDATE a TINYINT(1) column with the value 2, will this be TRUE or FALSE? Or, is this even doable?

A tinyint column can hold values from 0 to 255 (if it is defined as unsigned) or -128 to +127 (if it is signed). The (1) in tinyint(1) is only for some formatting options and generally ignored. You could create it as tinyint(100) and it wouldn't make a difference.
Regarding the TRUE or FALSE, any int (int, tinyint, smallint, bigint) value can be used as (or converted to) a boolean value. It is considered FALSE if it is 0 and TRUE otherwise. So, 2 would count as TRUE.
To be entirely clear, MySQL does not have a true BOOLEAN type. BOOLEAN is a synonym of TINYINT(1), as the docs explain in Numeric Type Overview:

BOOL, BOOLEAN
These types are synonyms for TINYINT(1). A value of zero is considered false. Nonzero values are considered true:
...
However, the values TRUE and FALSE are merely aliases for 1 and 0, respectively, as shown here:
...


After a full backup, how does system know which transactions to restore from the transaction log backup? Full backup does not truncate the transaction log file.

Assuming the Following Scenario

Full backup at 6AM
TLog backup at 10AM
Full backup at 1PM
TLog backup at 6PM
Crash immediately at the next second (so no tail log backup needed)

Steps to Restore:

Restore full backup from point 3 (contains data as at 1PM)
Then, restore the tlog backup from step 4 (contains log from 10AM to 6PM)

Questions

During recovery, how does the system know that only certain portion of the log file (post 1PM [exclude 10AM to 1PM]) needs to be replayed on the database?
Does it check the timestamps with in the transaction log backup to compare it with the full backup? Or does it check the LSN?

Additional Scenario

Similarly, in another scenario, let's say we do a Full backup at 10AM, then take a transaction log backup at 12PM. The transaction log will have all transactions up-till 12PM (and even prior to 10AM assuming db existed prior to 10AM and there was no previous transaction log backup).

Now, when we restore the full backup and then apply the transaction log backup, how does the system know that only transactions in the log post 10AM need to be replayed? Since all those prior to 10AM will be already there as part of the full backup restore. Is it checked via timestamps or LSN's?

Each and every restore operation relies (in some part) on the LSN that is stored together with the backup and internally in the msdb database.

Covering The Basics

From some Microsoft documentation:

(emphasis mine)

  LSNs are used internally during a RESTORE sequence to track the point in time to which data has been restored. When a backup is restored, the data is restored to the LSN corresponding to the point in time at which the backup was taken. Differential and log backups advance the restored database to a later time, which corresponds to a higher LSN.

  Every record in the transaction log is uniquely identified by a log sequence number (LSN). LSNs are ordered such that if LSN2 is greater than LSN1, the change described by the log record referred to by LSN2 occurred after the change described by the log record LSN.

  The LSN of a log record at which a significant event occurred can be useful for constructing correct restore sequences. Because LSNs are ordered, they can be compared for equality and inequality (that is, <, >, =, <=, >=). Such comparisons are useful when constructing restore sequences.

Reference: Recover to a Log Sequence Number (Microsoft | Docs | SQL Server)

After reading through some loops you will eventually land on the Microsoft Documentation page for SQL Server Transaction Log Architecture and Management Guide

(emphasis mine)

  The SQL Server transaction log operates logically as if the transaction log is a string of log records. Each log record is identified by a log sequence number (LSN). Each new log record is written to the logical end of the log with an LSN that is higher than the LSN of the record before it. Log records are stored in a serial sequence as they are created. Each log record contains the ID of the transaction that it belongs to. For each transaction, all log records associated with the transaction are individually linked in a chain using backward pointers that speed the rollback of the transaction.

Reference: SQL Server Transaction Log Architecture and Management Guide (Microsoft | Docs | SQL Server)

Getting Into The System

When you query the msdb database with a script I created for my own purpose, you will receive an overview of the relevant LSN numbers that are stored together with the FULL, DIFF and TLOG backups.

SELECT
/* Columns for retrieving information */
   msdb.dbo.backupset.database_name,
   msdb.dbo.backupset.backup_start_date,
   msdb.dbo.backupset.backup_finish_date,
   -- msdb.dbo.backupset.expiration_date,
   CASE msdb..backupset.type
       WHEN 'D' THEN 'Full'
       WHEN 'I' THEN 'Diff'
       WHEN 'L' THEN 'Log'
   END AS backup_type,
   -- msdb.dbo.backupset.backup_size / 1024 / 1024 as [backup_size MB],
   -- msdb.dbo.backupmediafamily.device_type,
   msdb.dbo.backupmediafamily.physical_device_name,
   -- msdb.dbo.backupmediafamily.logical_device_name,
   -- msdb.dbo.backupset.name AS backupset_name,
   -- msdb.dbo.backupset.description,
   msdb.dbo.backupset.is_copy_only,
   msdb.dbo.backupset.is_snapshot,
   msdb.dbo.backupset.first_lsn,
   msdb.dbo.backupset.last_lsn,
   msdb.dbo.backupset.database_backup_lsn,
   msdb.dbo.backupset.checkpoint_lsn,
   msdb.dbo.backupset.differential_base_lsn,
   msdb.dbo.backupset.fork_point_lsn,
   msdb.dbo.backupmediaset.name,
   msdb.dbo.backupmediaset.software_name,
   msdb.dbo.backupset.user_name,
   'EOR'

FROM   msdb.dbo.backupmediafamily
   INNER JOIN msdb.dbo.backupset
   ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id
   INNER JOIN msdb.dbo.backupmediaset
   on msdb.dbo.backupmediaset.media_set_id = backupmediafamily.media_set_id

/* ----------------------------------------------------------------------------
        Generic WHERE statement to simplify selection of more WHEREs
-------------------------------------------------------------------------------*/
WHERE 1 = 1
AND     database_name IN ('<name_of_your_database>')

This will give you an idea of the LSNs that are stored in the backup files. The msdb doesn't contain a full list of all LSNs in the backups, but just the main ones considered vital for a manual restore of a database:

msdb.dbo.backupset.first_lsn
msdb.dbo.backupset.last_lsn
msdb.dbo.backupset.database_backup_lsn
msdb.dbo.backupset.checkpoint_lsn
msdb.dbo.backupset.differential_base_lsn
msdb.dbo.backupset.fork_point_lsn

A description of these columns can be found in the official Microsoft Documentation for the backupset table.

Example Output of My StackExchange Database

database_name  backup_start_date  backup_finish_date                           backup_type physical_device_name                                                    is_copy_only   is_snapshot  first_lsn            last_lsn            database_backup_lsn  checkpoint_lsn     differential_base_lsn  fork_point_lsn  name  software_name       user_name                  (No column name)
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
StackExchange  2018-09-15         16:15:01.000        2018-09-15 16:15:01.000  Log         C:\SQL\Backup\StackExchange\LOG\StackExchange_LOG_20180915_161501.trn   0              0             286000001068100001  286000001068400001  286000001016900037  286000001063000002  NULL                  NULL            NULL  Microsoft SQL Server  NT SERVICE\SQLSERVERAGENT  EOR
StackExchange  2018-09-15         17:15:01.000        2018-09-15 17:15:01.000  Log         C:\SQL\Backup\StackExchange\LOG\StackExchange_LOG_20180915_171501.trn   0              0             286000001068400001  286000001068700001  286000001016900037  286000001063000002  NULL                  NULL            NULL  Microsoft SQL Server  NT SERVICE\SQLSERVERAGENT  EOR
StackExchange  2018-09-15         18:15:01.000        2018-09-15 18:15:01.000  Log         C:\SQL\Backup\StackExchange\LOG\StackExchange_LOG_20180915_181501.trn   0              0             286000001068700001  286000001069000001  286000001016900037  286000001063000002  NULL                  NULL            NULL  Microsoft SQL Server  NT SERVICE\SQLSERVERAGENT  EOR
StackExchange  2018-09-15         19:15:01.000        2018-09-15 19:15:01.000  Log         C:\SQL\Backup\StackExchange\LOG\StackExchange_LOG_20180915_191501.trn   0              0             286000001069000001  286000001069300001  286000001016900037  286000001063000002  NULL                  NULL            NULL  Microsoft SQL Server  NT SERVICE\SQLSERVERAGENT  EOR
StackExchange  2018-09-15         20:15:01.000        2018-09-15 20:15:01.000  Log         C:\SQL\Backup\StackExchange\LOG\StackExchange_LOG_20180915_201501.trn   0              0             286000001069300001  286000001069600001  286000001016900037  286000001063000002  NULL                  NULL            NULL  Microsoft SQL Server  NT SERVICE\SQLSERVERAGENT  EOR
StackExchange  2018-09-15         21:15:01.000        2018-09-15 21:15:01.000  Log         C:\SQL\Backup\StackExchange\LOG\StackExchange_LOG_20180915_211501.trn   0              0             286000001069600001  286000001070800001  286000001016900037  286000001070100001  NULL                  NULL            NULL  Microsoft SQL Server  NT SERVICE\SQLSERVERAGENT  EOR
StackExchange  2018-09-15         22:15:01.000        2018-09-15 22:15:01.000  Log         C:\SQL\Backup\StackExchange\LOG\StackExchange_LOG_20180915_221501.trn   0              0             286000001070800001  286000001071100001  286000001016900037  286000001070100001  NULL                  NULL            NULL  Microsoft SQL Server  NT SERVICE\SQLSERVERAGENT  EOR
StackExchange  2018-09-15         22:30:06.000        2018-09-15 22:30:06.000  Full        C:\SQL\Backup\StackExchange\FULL\StackExchange_FULL_20180915_223006.bak 0              0             286000001071600037  286000001073300001  286000001016900037  286000001071600037  NULL                  NULL            NULL  Microsoft SQL Server  NT SERVICE\SQLSERVERAGENT  EOR

The exact LSNs are stored in the backup files themselves and can be retrieved using 3-rd party tools.

Answering Your Questions

...For SMSS

When you start a restore process a couple of statements are execute in the background, which result in the GUI presenting you with an overview of FULL, DIFF and TLOG backups that would be required to bring your database back into a consistent state. The actual RESTORE DATABASE ... and RESTORE LOG ... will then determine which portions of the corresponding backup files are required to bring the database back into a consistent state, according to the documentation previously mentioned.

...For Transact-SQL

When you manually have to restore a database, then you will have to look into the msdb database (possibly with the aid of my script) to determind which files you require to bring your database back into a consistent state. The actual RESTORE DATABASE ... and RESTORE LOG ... will then determine which portions of the corresponding backup files are required to bring the database back into a consistent state, according to the documentation previously mentioned.

Summary

The magic is in the LSN stored in the backup files and restore process coded by Microsoft.

You can obtain a license to allow you to plug in to various APIs that would allow you to read more information from the backup files. For that you would have to contact a Microsoft representative.


Does listener gets affected when we replace secondary replica with another server in Availability Group? I have created Multi Subnet Always On Availability Group.
Now, I need to replace secondary replica with another server in Availability Group. In this case, Do I need to re-create listener? OR The previous listener works fine?
Thank You

The listener is a cluster ressource. If you new server is part of the cluster and is added to your availability group, it should work just fine.


Upgrade MySQL Master Slave Replication Cluster from 5.7.22 to 5.7.24 I have a two node MySQL cluster with Master Slave replication.
The MySQL version is 5.7.22 and the OS is CentOS.

I need to upgrade the MySQL version to 5.7.24 due to some security reasons.

However, I need to

keep the existing data
keep the existing user privileges
do with a zero downtime
keep the master slave replication after upgrading

What would be the correct approach to do this with the above requirements.

Upgrade the slave  -- This will have the Slave offline for minute(s).  This step may include mysql_upgrade.
Change the configuration to be Master-Master -- binlog, log_slave_updates, etc, on both servers.
Failover  -- This will have the Master offline for minute(s).  This also involves getting writing clients to point at the new Master.  (Presumably, you already have failover procedures in place and tested.)
Upgrade the former Master (now the Slave)

Optionally failback.

With only 2 nodes there will be short downtimes.  With some more advanced clustering techniques, plus a proxy, the downtime is less.  But never absolute zero.  See Group Replication or Galera.

You may as well leave it Master-Master instead of Master-Slave.  This avoids needing to do step 2 again.  However, you should set the Master that is not being written to to read_only.


SQL 2012: Import DB changes original data scheme I'm migrating data from SQL 2008 R2 Servers to SQL 2012. Therefore I'm using SSMS Import Data Task on the SQL 2012 machines.

As a test I copied the data files of one of the DBs, did an "Attach Database" and a schema compare with the latest SQL Server Data Tools.

The diff from the attached vs. the imported-one seems just wrong.
As an example:

Attached:

CREATE TABLE [dbo].[DbEventMessages] (
[EventID]         INT            IDENTITY (1, 1) NOT NULL,
[EntityType]      NVARCHAR (MAX) NOT NULL,
[ContactId]       INT            NOT NULL,
[Action]          NVARCHAR (MAX) NOT NULL,
[DateTime]        DATETIME2 (7)  NOT NULL,
[InnoVersionType] ROWVERSION     NULL,
CONSTRAINT [PK_DbDbEventMessages] PRIMARY KEY CLUSTERED ([EventID] ASC)
);
GO

Imported:

CREATE TABLE [dbo].[DbEventMessages] (
[EventID]         INT            NOT NULL,
[EntityType]      NVARCHAR (MAX) NOT NULL,
[ContactId]       INT            NOT NULL,
[Action]          NVARCHAR (MAX) NOT NULL,
[DateTime]        DATETIME2 (7)  NOT NULL,
[InnoVersionType] ROWVERSION     NOT NULL
);
GO

The imported scheme differs in

the IDENTITY property on the key column
the ROWVERSION was changed from NOT NULL to NULL
the primary key constraint is missing

What is wrong here? Or is it working correctly?
Thanks for any comments..

Take a backup of the SQL Server 2008 databases and restore it on SQL Server 2012. It is a much faster and reliable approach to migrating a database. Do not attach files between the instances, the SQL Server 2012 will upgrade them to a new format and you will loose the ability to use them again on SQL Server 2008.

As a general advice try to follow the procedures and guidelines, including the pre-migration validation steps from Upgrade to SQL Server 2012

Make sure you migrate all login information and SQL Agent jobs relevant for your database(s).


SELECT COALESCE with subselects is throwing error 'Operand should contain 1 column' I made this query:

    SELECT
        COALESCE(
                 (SELECT *
                  FROM JournalEntryTitleAndContent jetc
                  WHERE jetc.language_id = 2
                        AND jetc.journalentry_id = 1 LIMIT 1),
                 (SELECT *
                  FROM JournalEntryTitleAndContent jetc
                  WHERE jetc.language_id = 1
                        AND jetc.journalentry_id = 1 LIMIT 1),
                 (SELECT *
                  FROM JournalEntryTitleAndContent jetc
                  WHERE jetc.journalentry_id = 1 LIMIT 1)
                 )

Its purpose is to check if there is a row present with language_id 2 (thats the preferred language). If that is not present then the coalesce should try to find the row with the fallbacklanguage with id 1, if that returns null as well then it should return the first row it can find where journalentry_id = 1.

When I execute this statement in workbench i get Error Code 1241: Operand should contain 1 column(s)

Can i not return a row as a single value from coalesce? If not what should i use instead then? Should i use CASE or IFNULL() instead?

Thank you

SELECT *
FROM JournalEntryTitleAndContent jetc
WHERE jetc.journalentry_id = 1
ORDER BY FIND_IN_SET(jetc.language_id, '1,2') DESC
LIMIT 1


Aria engine - changing block size It's possible to change aria_block_size from default 8192 without recreating tables? I want try 1024 or 2048 but after change in my.cnf and restart mariadb I got this errors on aria tables:

#138 - Wrong block size 8192; Expected 1024

My db have about 120Gb and many tables, so export/delete/import will cause many hours of outage and this is not possible.

I found solution:

  change engine from aria to myisam
  stop mysql, add aria_block_size = 1024 to my.cnf
  delete aria_log_control file in mysql directory
  start mysql
  convert myisam tables back to aria

This process is safe for data, can be done without outage (only one fast mysql restart) and much faster then re-create tables and import data.


[INS-30131]Initial setup required for the execution of installer validations failed. Encountering this error in Fedora 20 While I was installing Oracle 12c database on Fedora 20 I encountered this error which wasted my 2 days.

"[INS-30131] Initial setup required for the execution of installer validations failed."

In the details it says this:

Cause - Failed to access the temporary location.
Action - Ensure that the current user has required permissions to access the temporary location.
Additional Information:
 - Framework setup check failed on all the nodes
     - Cause: Cause Of Problem Not Available
     - Action: User Action Not Available Summary of the failed nodes fedora
 - Version of exectask could not be retrieved from node "fedora"
     - Cause: Cause Of Problem Not Available
     - Action: User Action Not Available

I have tried a lot of things like restarting the computer and changing the permissions of the tmp folder etc. But none of these methods worked.

Plz help me out from this. Any help will be appreciated.

To see the real reason of this error in console output, use:

./runInstaller -debug -logLevel finest

In my case the reason was no execute permission for the file:
    '/tmp/CVU_12.1.0.2.0_oracle/exectask.sh'

So you simply need to add execute permission to all scripts and executable files in this directory, without exiting the installer.
Open the second console and use:

cd /tmp/CVU_12.1.0.2.0_oracle
chmod +x *.sh
chmod +x exectask
chmod +x cvuhelper

Then resume installation. It should install without further problems!

P.S.

Important remark about starting the database. It is not sufficient to use:

dbstart $ORACLE_HOME

and / or

lsnrctl start

You need to log in to the idle instance:

sqlplus SYS AS SYSDBA

and then use sqlplus command:

startup;

Then you can connect SYS@ORCL AS SYSDBA without error ORA-12514, assuming that you have set environment variable ORACLE_SID=orcl

Read also Oracle 12c Post Installation Mandatory Steps.


PostgreSQL seems to create inefficient plans in simple conditional joins Consider these two queries:
SELECT
    t1.id, *
FROM
    t1
INNER JOIN
    t2 ON t1.id = t2.id
    where t1.id > -9223372036513411363;

And:
SELECT
    t1.id, *
FROM
    t1
INNER JOIN
    t2 ON t1.id = t2.id
    where t1.id > -9223372036513411363 and t2.id > -9223372036513411363;

Note: The -9223372036513411363 is not the minimum value in the tables and the condition reduces the result (from the total number of rows, 350 million) to 17 million.
Personally, I expect PostgreSQL to come up with the same plan for both queries, because having t1.id = t2.id automatically implies the second condition. But unfortunately, PostgreSQL is creating two different plans with the plan for the second one being much better:

First query: http://explain.depesz.com/s/uauk
Second query: link: http://explain.depesz.com/s/uQd
EXPLAIN ANALYZE for the second query: http://explain.depesz.com/s/Snkx
(Second query finishes in 215 seconds, while the first one didn't finish after 1000 seconds until I terminated it).

I would highly prefer the first query, since I want to create a view from the join and put the where condition on queries on the view, where I see a single id column (I join using USING so a single id column is visible in view). Also, I will join more than two tables and I would prefer not to add such condition for each join.
Is there any reason for this behavior? Or is it a bug? Are there any workarounds?

Replacing ON t1.id = t2.id with USING (id) makes no difference in both queries.
This is PostgreSQL 9.3
The actual number of returned rows is 17,658,189
Analyze has been run on the tables. However, statistics related settings of PostgreSQL are its default values.
Observation: Explain for query 1 has a good estimate for the final result, but uses a poor plan for querying t2. For 2nd query, estimates of the number of rows from t1 & t2 are good, but estimate for the final merge is about half the number of actual rows.
The id column is primary key in both tables. Tables have around 350,000,000 rows. t1 is around 20GiB & t2 is 14GiB.
Replacing INNER JOIN with LEFT OUTER JOIN produces similar results
Selecting less rows (by increasing the minimum ID value in where condition) doesn't make any differences, until the number of rows become too low in which case it uses a totally different plan.

What I'm trying to achieve
I have a DB with a lot of rows, and new data is being inserted to it continuously. We want to generate different reports for this data, which includes different kinds of queries like: searching for different data, sorting by each column, aggregation queries and so on.
In current design, we have no UPDATE operations. Currently, I'm experimenting with a highly normalized design (based on ideas promoted by Anchor modeling and/or 6NF). Such design would use JOINs and VIEWs extensively to make working with DB pleasant, and so needs a database to be able to do these efficiently.
As far as I can tell (based on problems like this), PostgreSQL doesn't seem to be a good fit to this design (with around 11 tables and a number of views) and seems to almost always perform worse than a less normalized design with one or two tables and no views. I was hoping that this problem in planning JOIN queries is my fault, but it doesn't seem so yet. With this problem, it seems that I should forget using VIEWS and use verbose queries with lots of repeated conditions, or forget using either PostgreSQL or this design.
Tables
The actual number of columns is a bit more, but they are not in any relation with other tables, and so should be irrelevant to this discussion:
CREATE TABLE t1
(
  id bigint NOT NULL DEFAULT nextval('ids_seq'::regclass),
  total integer NOT NULL,
  price integer NOT NULL,
  CONSTRAINT pk_t1 PRIMARY KEY (id)
)

CREATE TABLE t2
(
  id bigint NOT NULL,
  category smallint NOT NULL,
  CONSTRAINT pk_t2 PRIMARY KEY (id),
  CONSTRAINT fk_id FOREIGN KEY (id)
      REFERENCES t1 (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION
)

Then it looks like an optimizer's blind spot and you should use the second query.

When there is a condition joining two tables a and b: a.id = b.id and an additional condition a.id > @some_constant, seems like the optimizer uses the "index condition" for where to start the index scan on a (id) index but it doesn't use it for the second index b (id).

So, adding the (redundant) b.id > @some_constant allows it to produce a slightly more efficient plan, skipping a part of the b (id) index as well.

This could be posted as a suggestion for improvement (if it hasn't been already) to the Postgres hackers group.

After the edit, we know there is a FOREIGN KEY constraint from t2 that REFERENCES t1. So the "natural" (equivalent) way to write the query would be:

SELECT
    -- whatever
FROM
    t2
  LEFT JOIN
    t1 ON t1.id = t2.id
WHERE t2.id > -9223372036513411363 ;

Can you try this and tell us the execution plan it produces? There are some transformations that apply only to LEFT (outer) joins and not to inner joins.
Unfortunately this doesn't produce any different plan either.

The OP has posted a question at the Postgres performance list, we can see the whole thread here: PostgreSQL seems to create inefficient plans in simple conditional joins and the reply by David Rowley, which confirms that this is a feature that although it has been considered, hasn't yet been implemented in the optimizer:

  Yes, unfortunately you've done about the only thing that you can do,
  and that's just include both conditions in the query. Is there some
  special reason why you can't just write the t2.id > ... condition in
  the query too? or is the query generated dynamically by some software
  that you have no control over?

  I'd personally quite like to see improvements in this area, and even
  wrote a patch 1 which fixes this problem too. The problem I had when
  proposing the fix for this was that I was unable to report details
  about how many people are hit by this planner limitation. The patch I
  proposed caused a very small impact on planning time for many queries,
  and was thought by many not to apply in enough cases for it to be
  worth slowing down queries which cannot possibly benefit. Of course I
  agree with this, I've no interest in slowing down planning on queries,
  but at the same time understand the annoying poor optimisation in this
  area.

  Although please remember the patch I proposed was merely a first draft
  proposal. Not for production use.


syntax error for change replication On a mysql 5.7 master, the following command generates syntax error.

CHANGE REPLICATION FILTER REPLICATE_DO_DB = ('mydb')
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'REPLICATION FILTER REPLICATE_DO_DB = ('mydb')' at line 1

I have tried it with or without single quote around database name. official guide

CHANGE REPLICATION FILTER REPLICATE-DO-DB is a SLAVE filter to read only (mydb) from the relay log.

If you want to filter the Master to write only (mydb) statements on the binary logs (and replicate only (mydb) to all slaves), you need to use the --binlog-do-db=name variable (Link) on master server.


Oracle counterpart to `pg_cancel_backend` Considering the Oracle statement ALTER SYSTEM KILL SESSION ... as... to some degree comparable to the PostgreSQL statement SELECT pg_terminate_backend():

Do you know of any Oracle analogy (better than ALTER SYSTEM KILL SESSION) for the PostgreSQL SELECT pg_cancel_backend()?

Reference: 9.26. System Administration Functions (PostgreSQL Documentation)

pg_terminate_backend

  Terminate a backend. You can execute this against another backend that has exactly the same role as the user calling the function. In all other cases, you must be a superuser.

pg_cancel_backend

  Cancel a backend's current query. You can execute this against another backend that has exactly the same role as the user calling the function. In all other cases, you must be a superuser.

Versions below 18c:

DBMS_RESOURCE_MANAGER

SWITCH_CONSUMER_GROUP_FOR_SESS Procedure

exec DBMS_RESOURCE_MANAGER.SWITCH_CONSUMER_GROUP_FOR_SESS (sid, serial, 'CANCEL_SQL');

  If the group name is 'CANCEL_SQL', the current call will be canceled
  when other switch criteria are met.

From 18c:

ALTER SYSTEM

  cancel_sql_clause

  Use this clause to terminate an SQL operation that is consuming
  excessive resources, including parallel servers. You must provide the
  session id and the session serial number to identify the SQL statement
  that you want to cancel. If you do not provide these required
  arguments, the SQL statement will not run.

  session_id is required and stands for the session identifier.

  serial_number is required and stands for the serial number of the
  session.

  @instance_id is optional. If this argument is omitted, the instance id
  of the current session is used.

  sql_id is optional. If this argument is omitted, the SQL statement
  currently running in the specified session is terminated.

Example:

ALTER SYSTEM CANCEL SQL 'SID, SERIAL, @INST_ID, SQL_ID';


What settings control the maximum number of connections per user in Oracle 12c? We have some software that is throwing an exception "Pooled connection request timed out"

I know the reason for this and i know the fix. I just need to buy some time on the production server until i can release the software update.

Currently the software establishes about 640 simultaneous connections before it starts throwing this exception.

What database settings can i change which will increase the amount of simultaneous connections?

You might want to check the SESSIONS parameter, which controls the maximum number of sessions for the system:

SELECT name, value
FROM v$parameter
WHERE name = 'sessions';

https://docs.oracle.com/database/121/REFRN/GUID-52804B5A-164F-44F3-8980-F2593B58D807.htm#REFRN10197

You can also verify that there isn't a limit configured via user profile. Query the DBA_PROFILES view to check if a value is assigned for the SESSIONS_PER_USER parameter associated with your user:

select profile, resource_name, limit
from dba_profiles
where resource_name = 'SESSIONS_PER_USER';

Info regarding profiles:
https://docs.oracle.com/database/121/SQLRF/statements_6012.htm#SQLRF01310


How do I reindex a MYSQL table using a different disk? I have my mysql data and index files on disk1

I need to reindex the table and do NOT have enough disk space on disk1

How do I reindex a table on disk1 and use disk2 for the reindex disk space?

For example, table foo is on disk1.  I need to add an index on acctnum but do not have enough disk space on disk1. Therefore, I want to reindex on disk2 but keep the data/index files on disk1

alter table foo add index (acctnum);

edits: (by jcolebrand from an answer)

MyISAM is the engine

MySQL version is 5.0.95-log

I need the database operational during the reindex process, unless there is a quick method to load 4.5 million records.

There is the Percona Tool called pt-online-schema-change
If you can tolerate downtime, you can try the following:
Suppose you have the following

datadir is /var/lib/mysql
MyISAM table named mydb.mytable
You have large disk volume mounted on /backup

You could do the following
mysql -u... -p... -e"CREATE TABLE mydb.mynewtable LIKE mydb.mytable"
mysql -u... -p... -e"ALTER TABLE mydb.mynewtable ADD INDEX (acctnum)"
#
# Create Symlinks Pointing to Another Disk
#
cp /var/lib/mysql/mydb/mynewtable.MY[ID] /backup/.
rm -f /var/lib/mysql/mydb/mynewtable.MY[ID]
chown mysql:mysql /backup/mynewtable.*
ln -s /backup/mynewtable.MYD /var/lib/mysql/mydb/mynewtable.MYD
ln -s /backup/mynewtable.MYI /var/lib/mysql/mydb/mynewtable.MYI
mysql -u... -p... -e"INSERT INTO mydb.mynewtable SELECT * FROM mydb.mytable"
mysql -u... -p... -e"ALTER TABLE mydb.mytable RENAME mydb.myoldtable"
#
# Erase Symlinks
#
rm -f /var/lib/mysql/mydb/mynewtable.MY[ID]
#
# Move Newly Made Table Back
#
mv /backups/*.MY[ID] /var/lib/mysql/mydb/.
mysql -u... -p... -e"FLUSH TABLES"

Seems like a lot of work, eh ??? Try the pt-online-schema-change first.
UPDATE 2012-08-22 11:56 EDT
I am not sure what would happen, but please hear me out on this suggestion: Try executing a repair on an empty .MYI file.
How do you do that? Take my idea from my first suggestion and augment to swap the new and old .MYI.
mysql -u... -p... -e"CREATE TABLE mydb.mynewtable LIKE mydb.mytable"
mysql -u... -p... -e"ALTER TABLE mydb.mynewtable ADD INDEX (acctnum)"
cp /var/lib/mysql/mydb/mytable.MYI /backup/.
rm -f /var/lib/mysql/mydb/mytable.MYI
cp /var/lib/mysql/mydb/mynewtable.MYI /var/lib/mysql/mydb/mytable.MYI
mysql -u... -p... -e"REPAIR TABLE mydb.mytable"

This should perform an in-place linear index rebuild.
Give it a Try !!!


Store output of sp_who2 in a table I am looking for something like this, where I could store the output of sp_who2 in a table, without having to first create the table:

create table test as exec sp_who2

USE yourDb;

CREATE TABLE temp_sp_who2
    (
      SPID INT,
      Status VARCHAR(1000) NULL,
      Login SYSNAME NULL,
      HostName SYSNAME NULL,
      BlkBy SYSNAME NULL,
      DBName SYSNAME NULL,
      Command VARCHAR(1000) NULL,
      CPUTime INT NULL,
      DiskIO BIGINT NULL, -- int
      LastBatch VARCHAR(1000) NULL,
      ProgramName VARCHAR(1000) NULL,
      SPID2 INT
      , RequestId INT NULL --comment out for SQL 2000 databases

    )

INSERT  INTO temp_sp_who2
EXEC sp_who2

SELECT  *
FROM    temp_sp_who2
WHERE   DBName = 'yourDb'

Link
Just tested it on SQL server 2012


I want Powershell's invoke-sqlcmd to ignore SQL execution errors I've written a Powershell script that scripts out the appropriate SQL scripts for all databases on a server as well as creating BCP files for the contents of each. This is to allow the restoration of a database from a higher level of SQL to a lower level where backup and restore will be impossible (but there are no features or functions used that are incompatible otherwise). This is great, to automatically run them against the destination server is problematic.

Each script created contains small problems. For example the table scripts all start with dropping the table by name if it already exists. This is fine in SSMS because you can just remove that line if the table doesn't already exist to avoid the "doesn't exist or you don't have permission" errors. There's other things you'd remove too like creating indexes that already exist from having run other scripts that make other tables from the same database.

Invoke-sqlcmd -inputfile dbo.SomeTable.sql stops and fails on these errors. How can I make either invoke-sqlcmd ignore them and just do what it can (in the knowledge that's fine), or temporarily make SQL tolerant of those and not raise stopping errors, or what else can I use to programatically apply the scripts SQL server generates to an SQL server without manual intervention?

How can I [...] temporarily make SQL tolerant of those and not raise stopping errors

Test to see if the object exists before trying to drop it or create it.  That way you'll still throw errors in the event that the DROP or CREATE itself fails rather than for collisions.

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[YourTableName]') AND type in (N'U'))
DROP TABLE [dbo].[YourTableName]
GO

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[YourTableName]') AND type in (N'U'))
CREATE TABLE [dbo].[YourTableName] ( ... )

[Note: This syntax comes from the scripts that Microsoft.SqlServer.Management.Smo.Scripter generates.]

I'm assuming that your PowerShell scripting script is using Microsoft.SqlServer.Management.SMO.Scripter?  Why not specify Scripter.Options.IncludeIfNotExists = $true?  You get all the same options that you get with SSMS's Generate Scripts tool.  Do something like this:

Add-Type -AssemblyName 'Microsoft.SqlServer.Smo, Version=14.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91'

$ServerName = 'servername'
$DatabaseName = 'databasename'
$ScriptFileName = 'C:\scriptfilename.sql'
if (Test-Path $ScriptFileName) {
    Remove-Item $ScriptFileName
}

$SMOServer = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Server -ArgumentList $ServerName
$SMODatabase = $SMOServer.Databases.Item($DatabaseName)

$Scripter = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Scripter -ArgumentList $SMOServer
$Scripter.Options.AllowSystemObjects = $false
$Scripter.Options.IncludeHeaders = $true
$Scripter.Options.IncludeIfNotExists = $true
$Scripter.Options.Indexes = $true
$Scripter.Options.Permissions = $true
$Scripter.Options.ScriptBatchTerminator = $true
$Scripter.Options.ScriptSchema = $true
$Scripter.Options.TargetServerVersion = [Microsoft.SqlServer.Management.Smo.SqlServerVersion]::Version100
$Scripter.Options.WithDependencies = $true
$Scripter.Options.FileName = $ScriptFileName
$Scripter.Options.AppendToFile = $true
$Scripter.Options.ToFileOnly = $true

# Drop all tables
$Scripter.Options.ScriptData = $false # Don't script data
$Scripter.Options.ScriptDrops = $true # Script drop statements
foreach ($SMOTable in $SMODatabase.Tables) {
    $Scripter.EnumScript($SMOTable.Urn)
}

# Create all tables with data, indexes, and permissions
$Scripter.Options.ScriptData = $true # Script table data
$Scripter.Options.ScriptDrops = $false # Script create statements
foreach ($SMOTable in $SMODatabase.Tables) {
    $CreateScripter.EnumScript($SMOTable.Urn)
}

This scripts the tables using SQL Server 2008 syntax (Version100).  I'm using a version of the libraries that ships with Sql Server Management Studio 17.


Rebuild an index while it is being referenced in Oracle I have come across many articles on this site and on Tom Kyte's blog on the how, whys and whens of Re-building indexes. Note that this question is about whether I can rebuild when there are concurrent sessions referencing those indexes; and are there any recommendations for the same?

I have a couple of SELECT queries running in an OLAP DB on a bunch of tables. Should I stop these sessions before the rebuild? I am assuming there might be problems with locks.

UPDATE:
Note that, our DBAs had advised against having active sessions during the rebuild. Also the size of the tables are in the range of 15-20GB and rebuilds can typically take 2-3hrs.

Index rebuild operation is DDL and thus may lock the underlying table preventing you from updating it. However queries by nature do not lock (unless FOR UPDATE clause is specified) and can read from objects even if they're locked, thus, generally, no problems will arise during the index rebuild while queries are running (maybe except for some performance degradation during rebuild).

If you want to be able to update the table having the index which is being rebuilt, you can specify ONLINE clause:

SQL> alter index table1_pk_idx rebuild online;

Otherwise the session performing DML will wait until the index is rebuilt.

For more info on index rebuild, ONLINE clause, and index rebuild restrictions, read the section "Rebuilding an Existing Index" in Administrator's Guide, and the section "ALTER INDEX" in SQL Language Reference.


In mssql 2019 where sqlcmd utility is? I am working on the tool that will silently deploy SQL server instance and I need to run a few scripts after installation, but I struggle to find where SQLCMD utility is.
For example, I use this:
FEATURES=SQLENGINE,SDK
But after installation is finished there is no SQLCMD on the system.
Where is it?
UPDATE:
I've managed to create a silent installation that includes sqlcmd tool with this:
FEATURES=SQLENGINE,Tools

I have MSSQL 2019 installation at my PC and could figure out the path. You can find this tool in the below directory:
C:\Program Files\Microsoft SQL Server\Client SDK\ODBC\170\Tools\Binn

You may also download it fresh from below links:
https://go.microsoft.com/fwlink/?linkid=2142258
https://go.microsoft.com/fwlink/?linkid=2142257
Same is documented here on MS site.
Hope this helps.


How does compound index help speed up query? Say we have a bunch of column in mysql table.

Latitude Longitude

Say we make an index (Latitude and Longitude)

Well, the index will sort by Latitude first and IF the Latitude is the same it'll sort by Longitude.

Something fishy should show up. Latitude is rarely, if ever, exactly the same.

Say we want to find all points whose Lat,Long is "in a box" How in the earth those indexes can be useful at all.

Note: I am trying to understand how indexing work. I am not trying to snobbishly say that compound index is stupid. Doesn't have to be full answer. Any pointing is fine.

More importantly, I am suspicious that a query will only use 1 index and the only way for a query to use multilpe index is to make compound index.

Indexes are in most cases a B-Tree structure (or some sort of). There are DBMS that supported different indexing types.

As you are talking about longitude/latitude PostgreSQL with it's GIST indexes come to mind. Oracle has Bitmap indexes in addition to the B-Tree indexes. I'm sure SQL Server and DB2 also have some special index types. And then there are full text indexes which make searching text very efficient.

A B-Tree index is very efficient in finding a specific value - think of a primary key index where all values are different. If the index only contains the PK column(s) (i.e. it is not a clustered index) then typically looking up a row by a specific PK values takes not more than (roughly) 3-4 IO operations (at least with Oracle). 2-3 to find the index block and an additional one to read the whole row. This gets more efficient if the index contains additional columns so that the lookup of the actual table row is not needed. The term for that is "covering index" or "index only retrieval".

Now for doing "range lookups" (e.g. where foo > 42) an index is very helpful as well as in most DBMS the index can also be scanned according to a predicate. Usually (again this highly depends on the DBMS) this is slightly less efficient than a direct lookup (again this also depends on the ability to do an "index only retrieval").

I don't know of any BMS which can not use more than one index in a query. Think a join on a PK and a FK column - depending on the data distribution the DBMS might use the index to find the parent rows (PK lookup) and the child rows (FK lookup).

But not all DBMS can use more than one index for the same table in a single query.

After all whether or not an index is being used or not depends on a lot of things.

I can highly recommend http://use-the-index-luke.com/ which is a very good introduction on indexing across all major DBMS.

DBMS specific information:

Oracle: http://docs.oracle.com/cd/E11882_01/server.112/e25789/indexiot.htm
PostgreSQL: http://www.postgresql.org/docs/current/static/indexes.html


Do indexes get transferred with pg_restore I see other questions that have been answered in the negative (i.e. indexes do not get transferred over with the standard pg_restore). However, it looks like it did in my recent dump/restore and I am not sure if it really did transfer over or not.

I migrated my database over from PostgreSQL 9.3 to 9.4.5 and used dump/restore in order to do so.

Below are the commands used:

sudo -u postgres pg_dump -h localhost -p 5432 -d nominatim -F d -f dump/postgres/backup -j 20
sudo -u postgres pg_restore --create --dbname=nominatim --exit-on-error -h localhost -p 5432 -F d -j 7 dump/postgres/backup

The dump and restore was successful (no errors).

I set autovacuum to off while doing the restore and so I ran analyze (no parameters) from within psql and while connected to the database that was restored.

The first thing I noticed is that under 9.3, the data directory took up roughly 860GB, whereas under 9.4, it's taking up about 680GB.  Is 9.4 THAT much more efficient?

Second thing I noticed is that I see the indices in the database:

nominatim=# \d country_name
                   Table "public.country_name"
            Column             |         Type         | Modifiers
-------------------------------+---------------------------------
country_code                  | character varying(2) |
name                          | hstore               |
country_default_language_code | character varying(2) |
partition                     | integer              |

Indexes:
        "idx_country_name_country_code" btree (country_code)

nominatim=# \d idx_country_name_country_code
    Index "public.idx_country_name_country_code"
    Column    |         Type         |  Definition
--------------+----------------------+--------------
 country_code | character varying(2) | country_code

btree, for table "public.country_name"

nominatim=# select * from pg_indexes where tablename = 'country_name';
 schemaname |  tablename   |           indexname           | tablespace |                                       indexdef
------------+--------------+-------------------------------+------------+---------------------------------------------------------------------------------------
 public     | country_name | idx_country_name_country_code |            | CREATE INDEX idx_country_name_country_code ON country_name USING btree (country_code)
(1 row)

Do I still need to reindex or did my indexes somehow or other make it over or is what I am seeing simply the index definition?  Any further tips on better managing the dump/restore, checking my indexes, etc. would definitely be appreciated.

Do indexes get transferred with pg_restore?
  I see other questions that have been answered in the negative
  (i.e. indexes do not get transferred over with the standard pg_restore)

That seems to be a misunderstanding. The index itself (containing all the data) is not in the dump. Just the commands to recreate it. So, indexes get "transferred", but really, they are recreated in pristine condition, without any bloat or dead tuples

  Do I still need to reindex?

No. After the restore you have all indexes in perfect condition. No REINDEX needed. ANALYZE would make sense, though, as advised in the manual in the chapter "Restoring the Dump":

  After restoring a backup, it is wise to run ANALYZE on each database
  so the query optimizer has useful statistics;

And finally:

  Is 9.4 THAT much more efficient?

No, not generally. Well, the size of GIN indexes has been reduced substantially. But what you see is most probably the effect of removing bloat from all tables and indexes (including system tables).


How can I select just the 'Create Table' column of SHOW CREATE TABLE? MySQL SHOW CREATE TABLE tablename has two columns of output, 'Table' and 'Create Table'.  How can I select just the 'Create Table' data?
I've tried
mysql> SELECT 'Create Table' FROM (SHOW CREATE TABLE tablename);

and
mysql> SELECT `Create Table` FROM (SHOW CREATE TABLE tablename);

Both result in syntax errors.  I'm not sure what else to try.

You can't use the result-set in MySQL directly, most connectors can give back a "normal" Result Set for SHOW, so you can use it in that language like a result from a select.
you must check out INFORMATION_SCHEMA Tables, there you find almost every information you need.


Trying to get DB2 to enumerate databases I am trying to use DB2DataSourceEnumerator.GetDataSources("db2.windward.net", null, "username", "password") to return all databases on that server. When I make the call it takes about 10 seconds to return and then throws the following exception:

  A connection attempt failed because the connected party did not
  properly respond after a period of time, or established connection
  failed because connected host has failed to respond 104.42.24.172:523

The documentation says:

  To be visible, the remote database server instances must have a
  running database administration server (DAS), which is configured to
  respond to Search and Known Discovery requests.

I'm assuming that is the problem. How do I set this for the DB2 server? We have version 11 running on Windows.

You will have to have admin rights on your computer and the use these instructions to create DAS:

  Enter db2admin create. If a specific user account is desired, you must use /USER: and /PASSWORD: when issuing db2admin create.

You can then see current config with command:

db2 get admin cfg

and update it with

update admin cfg

command.

Sources:

[1] http://www.ibm.com/support/knowledgecenter/SSEPGG_9.7.0/com.ibm.db2.luw.admin.gui.doc/doc/t0004960.html

[2] https://www.ibm.com/support/knowledgecenter/SSEPGG_9.7.0/com.ibm.db2.luw.admin.gui.doc/doc/t0005173.html

[3]
https://www.ibm.com/support/knowledgecenter/SSEPGG_9.7.0/com.ibm.db2.luw.admin.cmd.doc/doc/r0002023.html


Backup errors after TDE certificate expired and new cert was created with AlwaysOn I have 2 databases in AlwaysOn with TDE enabled.
My TDE cert expired and i created a new one but Ola's Backup script is still failing:
EXECUTE [dbo].[DatabaseBackup]
@Databases = 'DB1, DB2',
@Directory = 'D:\SQL-Backup',
@CopyOnly='Y',
@CleanupTime = 120,
@Compress='Y',
@BackupType = 'FULL',
@Verify = 'N',
@CheckSum = 'Y',
@Encrypt = 'Y',
@EncryptionAlgorithm = 'AES_256',
@ServerCertificate = 'DB_DEK',
@LogToTable = 'Y'

With this error (which is an old Cert):

Msg 33111, Level 16, State 3, Line 1
Cannot find server certificate with thumbprint '0xFAF8C09F62ED7D523AE634133331690FC2FD8367'.

Using native sql backup works:
BACKUP Database [DB1]
TO  DISK = N'D:\SQL-Backup\DB1.bak'
WITH FORMAT, INIT,  NAME = N'DB1-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,
ENCRYPTION(ALGORITHM = AES_256, SERVER CERTIFICATE = [DB_DEK]),  STATS = 10
GO

For this to work I had to take the DBs from AlwaysOn.
Has anyone else experienced this type of scenario? And, if so, how did you resolve it?

So after upgrading to SP1 CU7 the backups started working again with TDE.


Tools for SQL Deadlock Statistics One of my company's SQL database generates a lot of deadlocks per day recently (someday >100)

I'm trying to troubleshoot the causes of these deadlocks, i noticed that many deadlocks occur at the same tables, I'm a newbie in Database Administrator so i don't know what is the best way to do some statistics on the deadlock reports, like i want to calculate and visualize things like what tables involed, how manytimes, what time the deadlocks usually happen.... so i can report to my boss, is there an easy way to import the all the deadlock xml columns to something like microsoft excel?

I attached here the deadlock reports (.xel file)

Deadlocks_reports

Sorry for english is not my first language.

Thank you for your time,

You should use the open source stored procedure sp_BlitzLock to analyze these deadlocks.  It will give you what you're looking for in terms of timing, which tables / objects are involved in deadlocks most often, etc.

I downloaded your .xel file and ran the proc like this:

EXEC master.dbo.sp_BlitzLock
    @EventSessionPath = 'C:\Program Files\Microsoft SQL Server\MSSQL14.SQL2017\MSSQL\Log\DL_0_132091861398080000.xel';

As an example, the output tells me that B7R2_CHKVN.dbo.B331GeneralLedger was involved in 217 deadlocks captured in this event session (out of the total 383 deadlocks for that database).

Adding the @ObjectName = 'B7R2_CHKVN.dbo.B331GeneralLedger' parameter to the procedure call filters things down to just deadlocks that involved that object.

Note that the database names are a little wonky because it's pulling the names by id, and my instance has different databases than yours

This will let you start going through each deadlock and seeing if you can find patterns of the queries involved.  Consider loading the deadlock XML (the last column of the first resultset) in SentryOne Plan Explorer in order to better visualize the locks being taken.


I would try an index for:

location, abc_key, a_time

If you want to stretch that a bit and create a covering index

location, abc_key, a_time, val

Hovever, a table without a primary key is a bit suspicious. In fact, nothing is mandatory. You may want to investiga why this is so, and if possible fix


EXPORT specific columns to INSERT statements Is there a way to export specific columns and a fixed number of rows from a table to INSERT statements?
I am trying to generate these for unit testing based on a large production table so using something like "Generate Scripts" in SQL Server Management Studio is out of the question.
There are a few tools that might be able to do this, but something so trivial in MySQL should not be that complex in SQL Server (at least I would hope not).

Use Import/Export wizard and instead of exporting the entire table, choose sql to export specific rows and columns.

You can write sql like : select column1, column2 from Table1 where some_condition or if you don't care about specific data then you can use top command to extract any number of rows that you require.

Edit : Below are screenshots for Import export Wizard (Launch it from SSMS):

EDIT:

@Robin IN your situation, you can do 2 things -

Go for something like Redgate's sql data generator that will generate test data -- OR --
You can do a 1 time import of data as I suggested and then backup database and for your rest of testing you can just keep restoring the database from the backup (keeping in mind the data that you imported will not change). I cant think another way of doing what you want to achieve, so will keep this for others to answer ...


CHECKDB with default settings and parallelism involved This would be my follow-up question because I am trying to understand the CHECKDB behaviors
While running CHECKDB without any MAXDOP specified on below 2 servers:-

Server with 10 Vcores, MAXDOP at instance level 0, DB size 40 GB --> almost hitting 90% CPU spike

Server with 10 Vcores, MAXDOP at instance level 0, same like above DB in terms of schema, but data size over 150 GB --> almost hitting 30% CPU spikes

Can someone help me understand with server with same settings of instance level MAXDOP why one server will get all cores used and while other very few, probably why lower CPU usage.
Just curious how is SQL server making the conclusion of how many schedulers to allocate based on server level MAXDOP setting when running DBCC CHECKDB without specifying MAXDOP.
Thank you

Also, CHECKDB in Standard Edition is single threaded, whereas on Enterprise edition it's multi-threaded - could this be the reason why the 2 servers have different performance?
More info : https://www.brentozar.com/archive/2020/08/3-ways-to-run-dbcc-checkdb-faster/


INSERT DATA The database disk image is malformed We use sqllite to store our data.

Today,when I wanted to insert data in sqllite db,the log showed

  The database disk image is malformed

Im about to try the process as detailed on the link here.
https://www.databasefilerecovery.com/blog/sqlite-error-database-disk-image-is-malformed.html
will let you know how it goes.


Replicaset with MongoDB: Two datacenters, one primary and the other secondary The tutorial I found on MongoDB's website suggest installing an odd number of servers. For what it's worth, we're not sharding so it should be a simple setup. For even numbers I would need an arbiter. In our environment, DC1 is always the primary with DC2 being the secondary where we would fail over services in the event DC1 fails. With that said ...

If I have six nodes (3 node in each DC), would I need an arbiter?
If I have five nodes (3 in DC1 and 2 in DC2), if DC1 fails, wouldn't I need an arbiter in DC2?

I guess my confusion is how to handle failover in the event DC1 fails.

If I have six nodes (3 node in each DC), would I need an arbiter?

Yes - as Mongo docs mentioned an odd number is ideal.

If you have 6 Nodes and 1 goes down, the remaining 5 will elect a new primary. When 2 go down, 4 will elect a primary. When 3 do go down, you only have 3 left which are no longer a majority and all 3 will become read-only until a 4th node comes back online.

If you have 6 + an arbiter, then the scenario plays out slightly more beneficially. 1 node goes down, 5+1 elect a new primary. 2 go down, 4+1 elect a new primary. 3 go down, 3+1 still has a majority (3+1 / 7) so they can elect a new primary. Finally, when 4 nodes are down you are left with 2 nodes up and 1 arbiter, which is less than the majority (2+1 / 7) so you'll enter into read-only until a new node comes back online.

An odd number gives you one more level of outage before you lose the majority of your voting nodes and an arbiter is a cheap way to gain that benefit.

  If I have five nodes (3 in DC1 and 2 in DC2), if DC1 fails, wouldn't I
  need an arbiter in DC2?

In this config, adding an arbiter would give you an even number, so that's not ideal (see above). However, if DC1 goes down you're also losing your majority automatically and thus down to read-only by default. Although it might seem strange, this is the recommended setup from Mongo.

But why?

You could have 3 in DC1 and 3 in DC2 with an arbiter in DC2, which would allow you to maintain a voting majority in DC2 if DC1 were to completely fail. This works if you want automatic failover into DC2. But if DC2 goes down and a node in DC1 goes down, you also lose DC1 majority and end up with read-only nodes - probably not ideal.

That's why Mongo recommends that you set the priority for nodes in DC2 to 0. In this case you should have 3 nodes + 1 arbiter in DC1 and 3 nodes in DC2. You will need to manually fail over to DC2 if DC1 completely fails, but it gives you the best case scenario as far as keeping DC1 active and primary.


Postgres Row Level Security policy optimizes poorly compared to inline version I have a query which looks like this:

  SELECT post.id, post.author_id, post.published_at, post.content
    FROM post
   WHERE post.group_id = 1
ORDER BY post.published_at DESC, post.id
   LIMIT 5;

This query has an index on (group_id, published_at DESC, id) which gives it this query plan when no Row Level Security (RLS) policies are used.

 Limit  (cost=0.14..1.12 rows=5 width=143)
   ->  Index Scan using post_published_at on post  (cost=0.14..15.86 rows=80 width=143)
         Index Cond: (group_id = 1)

Then I add this policy:

CREATE POLICY select_member_of ON post FOR SELECT USING
  (EXISTS (SELECT 1
             FROM group_member
            WHERE group_member.account_id = current_setting('current_account_id', false)::INT AND
                  group_member.group_id = post.group_id));

There is a compound primary key on group_member.account_id and group_member.group_id on the group_member table.

I expect Postgres to plan this query as an index-only scan of group_member since both group_member.account_id and group_member.group_id will be set to constant values. group_member.group_id should be constant because of the WHERE post.group_id = 1 condition in the SELECT query above.

Indeed it looks like this is happening when I inline my RLS policy into the query like this:

  SELECT id, author_id, published_at, content
    FROM post
   WHERE group_id = 1 AND
         (EXISTS (SELECT 1
                    FROM group_member
                   WHERE group_member.account_id = current_setting('current_account_id', false)::INT AND
                         group_member.group_id = post.group_id))
ORDER BY published_at DESC, id
   LIMIT 5;

I get the query plan:

 Limit  (cost=0.30..1.85 rows=5 width=143)
   ->  Nested Loop Semi Join  (cost=0.30..25.04 rows=80 width=143)
         ->  Index Scan using post_published_at on post  (cost=0.14..15.86 rows=80 width=147)
               Index Cond: (group_id = 1)
         ->  Materialize  (cost=0.16..8.19 rows=1 width=4)
               ->  Index Only Scan using group_member_pkey on group_member  (cost=0.16..8.18 rows=1 width=4)
                     Index Cond: ((account_id = (current_setting('current_account_id'::text, false))::integer) AND (group_id = 1))

Which is what I was looking for. However, when I run my query with the real RLS policy the query plan becomes:

 Limit  (cost=23.08..23.10 rows=5 width=143)
   ->  Sort  (cost=23.08..23.28 rows=80 width=143)
         Sort Key: post.published_at DESC, post.id
         ->  Subquery Scan on post  (cost=8.92..21.75 rows=80 width=143)
               ->  Nested Loop Semi Join  (cost=8.92..20.95 rows=80 width=147)
                     ->  Bitmap Heap Scan on post post_1  (cost=8.76..11.76 rows=80 width=147)
                           Recheck Cond: (group_id = 1)
                           ->  Bitmap Index Scan on post_published_at  (cost=0.00..8.74 rows=80 width=0)
                                 Index Cond: (group_id = 1)
                     ->  Materialize  (cost=0.16..8.20 rows=1 width=4)
                           ->  Subquery Scan on group_member  (cost=0.16..8.19 rows=1 width=4)
                                 ->  Index Only Scan using group_member_pkey on group_member group_member_1  (cost=0.16..8.18 rows=1 width=8)
                                       Index Cond: ((account_id = (current_setting('current_account_id'::text, false))::integer) AND (group_id = 1))

Which is significantly worse.

Is this the expected behavior? Is there any way to get the same query plan for the version where I inlined my RLS policy?

Without sample data it’s difficult to reproduce your exact scenario.

It seems to me you should make the expression in the policy as simple as possible. In your case that would be:

CREATE POLICY select_member_of ON post FOR SELECT USING (group_id IN (SELECT group_id
            FROM group_member
            WHERE account_id = current_setting('current_account_id', false)::INT));


How to best store pixels in a database? When loading data into a table, I get the following error:

ERROR:  row is too big: size 8680, maximum size 8160

The table has 1000+ columns in it, which appears to be the problem.  The general internet advice is "refactor!" or "normalize!".  For instance, this post.  Unfortunately, I don't believe such advice applies to my situation.

The table is to store data collected from a device.  The device produces a PNG image as part of an analysis.  The PNG consists of 1024 pixels.  Each pixel has an associated numeric value.  Along with the pixel data are various other fields related to the analysis.  Breaking the table into parts doesn't really make sense.  The fields are all logically associated with the particular object being analyzed.

Postgres doesn't seem to like that each pixel has its own field.  The table has fields of the form: pixel_1, pixel_2, ..., pixel_1024.  Note that this is fundamentally different from the usual example of phone_number_1, phone_number_2, etc.  Each pixel is a unique object by virtue of its location.  pixel_1 has a different position than pixel_123 and each pixel has an associated value.  The common aspect between them is that they both are used to describe the same analysis object.  They are the quantitative analog to the visual representation given in the PNG.

Is there a way to increase the row size?
If the table simply cannot have 1000+ columns, how could I refactor this?
Assuming the first two answers are "No.", should I just stick the 1024 columns into an XML and throw that in a text field?

I hope I have made the context clear. I have tried to boil the problem down to its essence, but I suspect some clarification may be needed.  Please let me know if clarification is needed.

EDIT: As an experiment, I tried breaking the pixels into a separate table.  That seems to be the only possible way to refactor.  But the 1024 columns produces the same error.

Raster Data

  The table is to store data collected from a device. The device produces a PNG image as part of an analysis. The PNG consists of 1024 pixels. Each pixel has an associated numeric value. Along with the pixel data are various other fields related to the analysis. Breaking the table into parts doesn't really make sense. The fields are all logically associated with the particular object being analyzed.

That's called a "raster", not a PNG image. A PNG is in fact an RGB[A] raster stored with DEFLATE compression, but what you want to store has nothing to do with it being a PNG. If these terms don't make sense, it's like telling someone you want to store a PDF. That's possible, but likely what they want is to store the underlying text.

Here your underlying text from the PNG is the raster.

PostGIS

PostgreSQL supports storing actual raster types with PostGIS. I suggest checking out the PostGIS Raster Reference. If your rasters are currently encoded in the PNG channels, you can use GDAL to put them in the database

I would suggest checking that out for information on how to query, process, modify, and export raster data.

Native

Even if you decide not use PostGIS, I don't believe the other answers are adequate. As mentioned above, a RGB pixel is a 3-channel compound data type. So you'll need at least three dimensions for the pixel data.

In addition, you'll likely want information on the XY pixel data, or a method to calculate with that meta data later.

You could use a cube to represent the pixel data

-- white and black pixels (RGB)
SELECT ARRAY['(255,255,255)','(0,0,0)']::cube[]

You can use an array to store each channel (likely not useful for processing), and importing and exporting will be difficult.
You could use a type for the pixel., though a record type even of "char" is substantially larger than our solution below.
You could store each pixel as an int and ignore the channels (assuming you have less than four 1-byte channels). If you need to process this will get super ugly very fast, make analysis near impossible, and exporting and querying very difficult.
Or you could create your own DOMAIN, but at this point your basically recreating PostGIS's raster. With this approach you get to store all four bands as a single int. Access those bands with minimal cost, and construct the pixel from the bands. You can easily expand this to int8 if you need 8 bands.

CREATE DOMAIN pixel AS int4;

CREATE OR REPLACE FUNCTION get_band(p pixel, b int)
RETURNS int AS $$
  SELECT ((p::bit(32)<< ((4-b)*8)) >> (3*8))::int
$$ LANGUAGE sql
IMMUTABLE;

CREATE OR REPLACE FUNCTION create_pixel( int, int, int, int )
RETURNS pixel AS $$
  SELECT (
    $1::bit(32) |
    ($2::bit(32)<<8) |
    ($3::bit(32)<<16) |
    ($4::bit(32)<<24)
  )::pixel;
$$ LANGUAGE sql
IMMUTABLE;

-- Returns 128.
SELECT get_band(create_pixel(255,0,128,32),3)::int;

-- You can use this type in an array too
CREATE TABLE f ( ps pixel[] );

As a side note, if using LIDAR you should check out PG Point Cloud


Sequences Vs Count for auto-increment Can some one please tell me the exact difference between the following statements ,

INSERT INTO table1 VALUES ((select count(column-name) from table-name2)+1);

here i am using the count function to auto-increment the value..

The same functionality can be done with sequences..

what will happen when both of this queries are executed and also please tell me the drawbacks of using the count over sequence

Count(column_name) will give numbers of not-null records from the table "Table1".

Sequence would return a number irrespective of number of records in the table "Table1" .
Sequence can be used globally ,not only specific to a table.

if you require a digit on basic of records in table use Count function.
if you  require just a running serial number use sequence.


SQL Server Linked Server error and Query Problem I have two servers running SQL Server 2000 and SQL Server 2005.
There is a database A,B in SQL Server 2000. Similarly I have the same in A,B in SQL Server 2005. A,B are name of the database.

I have a linked server pointing from SQL Server 2005 database A to SQL Server 2000 database B. Here the scenario is I can able to access database SQL Server 2000 database B from SQL Server 2005.

Two issues I face I am randomly getting the error:

  OLE DB provider "SQLNCLI" for linked server "SQL2000" returned
  message "Communication link failure". Msg 10054, Level 16, State 1,
  Line 0 TCP Provider: An existing connection was forcibly closed by the
  remote host. Msg 18456, Level 14, State 1, Line 0 Login failed for
  user 'NT AUTHORITY\ANONYMOUS LOGON'.

How to resolve this error?

Also, when the user want to access the database located in the same server what must be done? I mean accessing the database SQL Server 2005 database A and SQL Server 2005 database A i.e ignoring the linked server.

Need some more information for the Linked Server error - asked a question in a comment for more info there and will clean this up based on your thoughts there. Though it appears as though you are experiencing issues with kerberos delegation on the face of it. (More info here)

For the second question - it sounds like you are just asking how can that user connect directly to the SQL2005 instance without using a linked server. If I've understood correctly - you just grant their login access to the SQL Server 2005 server (either a SQL authenticated login or a domain login) and grant them the database and server access they need. Then they can use SQL Server Management Studio or whatever tool you use to connect and connect directly.


SQL Server Restore from one database to another One of our devs backed up a dev database, and then restored it in production. It's a new database for a new app that was deployed last night intentionally to prod.

Now in the backupset table (msdb.dbo.backupset) on prod, I can see a record for the dev database, with a backup start date time of when the restore was done.

Record from prod backupset table:

name:         DatabaseName_UAT-Full Database Backup
server_name:  COMPNAME-SQLDEV02
machine_name: COMPNAME-SQLDEV02

I would not expect to see this record. Can anyone explain why restore would insert into the backupset table on prod?

Can I delete this record from the msdb.dbo.backupset table? Or not such a good idea?

"It just does".  Its just meta data about the backup.

The Books Online article http://msdn.microsoft.com/en-us/library/ms186299.aspx hints at it, albeit not very well in the "Remarks" section.  From that article you'll see a reference to sp_delete_backuphistory.  So yes you can safely remove it if you need to.

The info that goes into that backupset table, from a restore, is pulled out of the backup itself.  Run:

RESTORE HEADERONLY FROM DISK = 'Path to backup file';

and you'll find the information.


Security Concerns of SQL Server Express for Web e-Voting web application I am building a database driven web application. The web application is basically an E-Voting website, where users can register and vote on things that concern their community ... etc.

The database engine in use is SQL Server Express 2008. I know that my Domain Model and Application Code does not allow any silly security breaches, e.g. a User viewing the vote of other users ... etc.

But that aside, are there things (free) I can do to make my database secure? So even if a hacker gets access to my Db, he can't do anything with it? or make it very hard for him.

I realise this question can be a little bit open ended, but some simple tips would be greatly appreciated.

Security is such a vast topic that it totally depends on your company's policies and the threat level you are trying to avoid.

You can use native asymmetric encryption in sql 2005 express as described in SQL Server 2005 Express Edition - Part 6 - Post Installation Configuration Tasks (Encryption) and SQL Server 2005 Security - Part 3 Encryption.

You can use master key encryption / cretificate / symmetric key. Read up : SQL SERVER – Introduction to SQL Server Encryption and Symmetric Key Encryption Tutorial with Script

Also, you can secure sql server backups at file level or the entire disk as well if you are trying to stop a hacker that already has access to the database server by some third party softwares like BitLocker or open source T r u e C r y p t.

Brian Kelley has an interesting article on Implementing Encrypting File System (EFS) with SQL Server.

There is a white paper on SQL Server security best practices as well.


Delete all the data from all tables I have a little problem. I have to delete all the data from our database. I don't want to drop tables, only delete the data sets.

These are 47 tables in total.

I tried the following:

Delete FROM(select TABLE_NAME from USER_TABLES);
Delete FROM(select * from ALL_ALL_TABLES Where OWNER = 'DB_NAME')

But that is not valid. How can I efficiently remove all the data from all my tables?

Generate the statements you need

select 'DELETE FROM '||table_name||' ;' from user_tables;

Even better would be using TRUNCATE instead of DELETE.  That would prevent generating extra redo vectors in the redo log - in the end all you want to do is just get rid of the data. It would also avoid all actions by any DELETE triggers that the tables have.

select 'TRUNCATE TABLE '||table_name||' ;' from user_tables;


Logical problem with a database model Beforehand the table names and columns are in Spanish, my native language is Spanish.
(table names have been now edited to English)

I've got a database to manage the tickets sales of a trip that is made by a bus, the problem is that when I related the tables I didn't reason that when setting a trip to a bus the seats of this will be the amount of tickets of the trip, and for each trip this tickets will be different. Also the seat state has to change from available to unavailable when someone buys the ticket of that seat in each trip.

Here's the db model - only the tables involved in this problem :

EDIT: I renamed the table names to English language for be easy to read.

The only way that I imagine to do this is to create another table where I will insert the number tickets of a trip depending on the number of seats that a bus has, and when showing the available stalls in the app I'll set unavailable the seats of the bus where the number of seat is equal to the number of ticket that is already bought. But I don't feel this way leads to a normalized database.

You could reverse the relationship between passenger and ticket so that the ticket has a nullable FK to the passenger. When you create a new trip, create ticket entries for the number of passengers in the trip / seats on the bus. Then relate a passenger to each ticket as the ticket is sold. When a trip has no more tickets with an empty passenger id that trip is sold out.


Create table reference foreign key I'm trying to create a table referencing a primary key from the table person;

CREATE TABLE Membership (foreign key (USERNAME) references Person(USERNAME),MembershipNo INT Primary Key,DatePaid date NOT NULL,DateTo date,AnnualFee INT NOT NULL)
                                      *
ERROR at line 1:
ORA-00904: "USERNAME": invalid identifier

I'm ether blind as a bat(possible) and there is a syntax error or I'm missing something.

SELECT * FROM ALL_CONSTRAINTS WHERE TABLE_NAME='PERSON';

USERNAME     ROLE    DATEJOINED  ADDRESS     EMAILADDRESS    PHONENUMBER

`

SELECT * FROM ALL_CONSTRAINTS WHERE TABLE_NAME='PERSON';

STUBT001     SYS_C00145052   P   PERSON                  ENABLED     NOT DEFERRABLE  IMMEDIATE   VALIDATED   GENERATED NAME          17-OCT-2013         SYS_C00145052

You don't define a column USERNAME in your MemberShip table

CREATE TABLE Membership
(
  MembershipNo INT Primary Key,
  DatePaid date NOT NULL,
  DateTo date,
  AnnualFee INT NOT NULL,
  username varchar(20) not null, -- << you are missing this
  foreign key (USERNAME) references Person(USERNAME)
);

alternatively directly behind the column:

CREATE TABLE Membership
(
  MembershipNo INT Primary Key,
  DatePaid date NOT NULL,
  DateTo date,
  AnnualFee INT NOT NULL,
  username varchar(20) not null references Person(USERNAME)
);