texts: Limits of table partitioning (in PostgreSQL)

texts:
I have the following use case (PostgreSQL, currently v9 but I'm planning to upgrade to the latest version).

I have a data warehouse application with a central table that has a sizeable number of columns, two of these being JSONB columns.
As common for data warehouse applications, I have a number of indices on the central table, roughly one for each query type that is running.
The central table has a case column that contains an integer value and identifies one subset of data.
After the import, the records for a particular case are immutable (but I might need to purge all records for a case and re-import it once in a while).
For each case, I have ~100k to 3M records (a pretty much bimodal distribution with 70% making up ~100k, and 30% making up ~3M records).
I currently have one thousand cases and would expect this to grow by one thousand cases a year.
95% of all queries to go a single case. 5% of all queries go to a dozen or maybe 200 cases.

Now my questions:

Would it make sense to partition by case ID for performance improvements? I'm currently not so much concerned about query performance (Postgres is really awesome) but more about import times.
If I'm partitioning by case, would I hash the case ID integer or would I start a new one-element range for each case?
If I'm getting 10k or 100k cases, would Postgres scale to this number of partitions?
I'm considering using something like Citrus for sharding over multiple physical servers. Is there anything to keep in mind here?

I did quite some online search regarding this topic but did not find much beyond table partitioning for log files.
I'd be more than happy if you could direct me to good resources for research.


texts: Do I need ORDER BY in a window, if I already have a properly ordered query?

texts:
In this query..

SELECT *
FROM foo
ORDER BY x;

Does a window function over foo need a specific ORDER BY clause or does the window share the ordering of the query?

Question inspired by @Erwin, and @Ypercube's comments on my own answer


texts:
I am running into an issues with locating index

In SSMS, if I expand Users>Indexes, I do not see an index named "_dta_index_Users_5_1466188719_88410000_K1_K6_K2_7_10_11"

But I can see a statistics created for it.

Also when I search for indexes on "Users" by running this query

SELECT index_id, name FROM sys.indexes WHERE object_id = OBJECT_ID('Users')

It appears in the result "_dta_index_Users_5_1466188719_88410000_K1_K6_K2_7_10_11"

So the question is why I cannot see this index in SSMS? The index is not disabled and not user created since all our indexes start with IX_USERS_XXX

Any suggestions.

Thanks!


texts: Oracleasm deletedisk failed Unable to clear disk

texts:
I have dropped the disk from ASM diskgroup:

> ALTER DISKGROUP DATA01 DROP DISK DISK1;

Oracle did a rebalance operation and then dropped the disk.

Now, I want to delete the disk from ASM library and return the disk to the system:

# oracleasm deletedisk -v DISK1
  Clearing disk header: oracleasm-write-label: Unable to open device "/dev/oracleasm/disks/DISK1":
  Device or resource busy failed
  Unable to clear disk "DISK1"

The system details:
11.2.0.1 2-node RAC

Why does ASM not clearing the disk header? Where to see the detailed log?

------------------------------------Edit1--------------------------------------------

# fuser /dev/oracleasm/disks/DISK1

returns nothing on both nodes


texts: sp_flush_commit_table_on_demand procedure running even on AG

texts:
I am not sure if this is a bug or not but i am seeing calls on sp_flush_commit_table_on_demand when running async AG.

I wouldnt have a problem with that unless i wanted to capture last commit time value which is changing when this procedure executed (every half n hour).
I dont have any replication or cdc enabled, so it is a clear instance with one AG setup only. No jobs or any other calls.
I couldnt find documentation about this correlation between this SP and AG.

If anyone can clarify this behavior, it would be much appreciated.


texts: Shard key selection for both write scaling and query isolation?

texts:
From the mongod documentation, I know that when choosing shard keys the key should always have high cardinality, so write operations are distributed to different shards (a kind of write scaling). But for query isolation, better query performance will be gained if a query is routed to a single shard server (one single mongos instance).

For write scaling, choosing a shard key which has high cardinality seems easy. But for query isolation, how can I choose a shard key which will route the query to one single shard to the maximum extent when I don't know the detailed sharding algorithm?


texts:
I am attempting to write my first trigger statement. I am trying to create a trigger that performs a SELECT to get my MAX_TOW_WEIGHT for a LOCO_CLASS, then compare that weight to the TRAIN_WEIGHT. If the comparison fails (that is, TRAIN_WEIGHT > MAX_TOW_WEIGHT) the code would need to throw a user-defined exception, which the code which executed the INSERT or UPDATE would need to be written to handle. I have done the following which is most probably wrong though it is an attempt:

CREATE TRIGGER LOCOWEIGHT
BEFORE INSERT
ON Locomotive_Class
REFERENCING NEW AS New
FOR EACH ROW
   BEGIN
      SELECT Train_Weight FROM locomotive
   IF (MAX_TARE_WEIGHT > MAX_TOW_WEIGHT) THEN
       RAISE cError;
EXCEPTION
WHEN cError THEN
      RAISE_APPLICATION_EXCEPTION('Train weight has exceeded max tow weight');
END;

Tables for reference:

CREATE TABLE Locomotive
 (Loco_No integer PRIMARY KEY,
  Serial_No integer UNIQUE,
  Loco_Class_No integer
    REFERENCES Locomotive_Class(Loco_Class_No),
  Loco_name varchar2(20));

CREATE TABLE Locomotive_Class
 (Loco_Class_No integer PRIMARY KEY,
  Max_Tow_Weight float NOT NULL,
  Loco_Class_Len float NOT NULL);


texts: Sqlite: Finding the next or previous element in a table consisting of integer tuples

texts:
I have a sqlite table called tuples defined like
create table tuples
(
    a INTEGER not null,
    b INTEGER not null,
    c INTEGER not null,
    d INTEGER not null,
    primary key (a, b, c, d)
) without rowid;

filled with millions of unique tuples (>1TB). New tuples are inserted quite often, with "random" values. Rows are removed only in very rare occasions.
For an external process accessing the database, I need to find the "next" or "previous" existing 4-tuple in the table.
For example: Given tuples (1-1-1-1), (1-1-1-4) and (1-2-3-4), for the tuple (1-1-1-3) (which does not need to exist in the table) the "next" element is (1-1-1-4) and the previous is (1-1-1-1) (both which need to exist). For (1-1-1-4) (1-2-3-4) is the "next" element. Corner-case: If there there actually is no "next" or "previous" element the result is allowed to be empty. (1-2-3-4) has no "next" element.
At the moment I try to find the next tuple with ("center" is (1-1-1-3))
select a,b,c,d from tuple
where (a == 1 AND b == 1 AND c == 1 AND d > 3) OR
      (a == 1 AND b == 1 AND c > 1) OR
      (a == 1 AND b > 1) OR
      (a > 1)
order by a, b, c, d
limit 1;

which is really slow.
The short question here is: Is there a way to speed up the the process? Ideally the response should only take milliseconds, like searching for the exact value of a tuple (which is basically instantaneous). Using other/more indices, multiple and/or other queries, even changing the database structure is a valid solution.

Edit: Each element of a tuple may cover the whole allowed integer range.


texts:
I am trying to report partition metric to influxdb so that it can be visualised in grafana. I have partitioned my table using pg_partman. I want to report the size of the default table that is created by partman. This is the table where rows that don't fall into any of the existing partition tables are inserted into.
I had 2 approaches in mind

select pg_size_pretty(pg_total_relation_size('<default table name>'))
select count(*) from <default table name>

If I am not wrong 'count(*)' does a full table scan? So it might slow down the query if there are lot of rows being inserted into the default table.
I am not sure of pg_size_pretty(pg_total_relation_size ..) Is that constant time?
Also if there are other ways of doing this I would glad to know about them:)


texts: updating the entire column with datetime values based on other values in the row

texts:
I have a very large table [server].[dbo].[MyTable] where I need the SQL statement to fill the column 'ToDateTime' (currently full of NULLs) according to the following logic:

Only add a value to 'ToDateTime' on rows where AID equals 123 and PID equals 2. (rows 2, 4, 5, and 7 below) - otherwise leave 'ToDateTime' as NULL.
If the above condition is met, set/leave 'ToDateTime' as NULL if the data (varchar) cannot be converted to datetime (rows 5 and 7 below have data that will fail datetime conversion)
But if it does contain a value that can be converted to datetime (rows 2 and 4), convert it and update the value in ToDateTime with that converted value.

BEFORE:

ID | AID | PID | ContainedData        | ToDateTime
_________________________________________________
1  | 123 |  1  | xxxx                 | NULL
2  | 123 |  2  | 7/21/2015 8:15:06 AM | NULL
3  | 234 |  2  | xyxy                 | NULL
4  | 123 |  2  | 19/07/2015 12:29:42  | NULL
5  | 123 |  2  | NULL                 | NULL
6  | 345 |  3  | zzzz                 | NULL
7  | 123 |  2  | badTimeString        | NULL

AFTER:

ID | AID | PID | ContainedData        | ToDateTime
_________________________________________________
1  | 123 |  1  | xxxx                 | NULL
2  | 123 |  2  | 7/21/2015 8:15:06 AM | 2015-07-21 08:15:06.000
3  | 234 |  2  | xyxy                 | NULL
4  | 123 |  2  | 19/07/2015 12:29:42  | 2015-07-19 12:29:42.000
5  | 123 |  2  | NULL                 | NULL
6  | 345 |  3  | zzzz                 | NULL
7  | 123 |  2  | badTimeString        | NULL

I have been trying a query similar to what is below but it is filling ToDateTime with the same values for every row (in the case of my actual data, NULL.) I'm not sure if something similar to this query is the right approach, or if I'm way off.

UPDATE MyTable SET ToDateTime =
CASE
WHEN ISDATE(myT.cd)= 1
THEN CONVERT(DATETIME, myT.cd)
ELSE NULL END
FROM (
 SELECT ContainedData cd
 FROM MyTable
 WHERE [AID] = 123 AND [PID] = 2
 ) myT


texts: Slony not replicating changes from a dump file

texts:
I’m working on a project where we are using pg_dump to get the data out of one database and then using psql to load it into another.  The second database is the master db in a Slony replication cluster.

No matter what options I specify in pg_dump the changes are not being replicated.
I’ve use a standard dump which uses COPY, a --inserts dump which uses INSERT INTO and a --insert-columns dump which adds column names to the INSERT INTO statements.

If I edit the data in pgAdmin III using the view option for the table, that change is replicated just fine.

What is the difference between editing the data that way in pgAdmin III and using psql to load a dump file?


texts: How can I create a fresh copy of a large database on SQL Server?

texts:
I have a very large database (400GB) that is now corrupted. I tried repairing it with the repair_allow_data_loss option, but that did not fix the problem so my next option is to restore to a previous version. What I want to do first it to create a new fresh copy of the database. So a database with all the tables/views/procedures/permissions like the existing one and then restore to it an older backup.

How can I create this fresh copy?


texts: SQL Staging Tables: Primary Key Clustered or Heap

texts:
We are taking legacy flat txt files and inserting them into stage tables with SSIS.The question arose whether table should have primary clustered key index or not. This is direct flat file import with no transformation.

create table dbo.CustomerTransaction
(
     CustomerName varchar(255),
     PurchaseLocation varchar(255),
     Productid  int,
     AmountSold float,
     CustomerAddress varchar(50)
)

create table dbo.CustomerTransaction
(
     -- discussion for adding this column
     CustomerTransactionId int primary key clustered identity(1,1)

     CustomerName varchar(255),
     PurchaseLocation varchar(255),
     Productid  int,
     AmountSold float,
     CustomerAddress varchar(50)
)

-- both tables have nonclustered indexes
create nonclustered index idx_ProductId on dbo.CustomerTransaction(ProductId)
create nonclustered index idx_CustomerAddress on dbo.CustomerTransaction(CustomerAddress)

-- Actually have more indexes, tables above are just for sample

1) Before ETL, the staging tables are truncated. There are No Deletes and No Updates. Only Inserts.

truncate table dbo.[CustomerTransaction]

2) Then disable all indexes before ETL.

alter index all on dbo.[CustomerTransaction] DISABLE

3) We conduct SSIS data flow with default fast load, which I read is equivalent to bulk insert. No transformations occur here.

4) Then reenable all indexes after import is done.

alter index all on dbo.[CustomerTransaction] REBUILD

5) The staging tables are then selected on join and where clauses, and placed into datawarehouse. This we why we have nonclustered indexes. After data warehouse is loaded, we truncate the staging tables.

We are hearing information that ETL Stage tables are good as heaps. However, also learning of fragmentation and performance issues with heaps. Reading all the articles below

I am reading conflicting opinions. One says Binary tree clustered are maintenance headaches for import ETL. Other says Heaps have performance issues with fragmentation. Our performance testing does not show much difference, but our data may change later. So we need to make a good design decision.

https://sqlsunday.com/2016/09/01/compelling-case-for-heaps/

https://www.mssqltips.com/sqlservertip/4961/sql-server-insert-performance-for-clustered-indexes-vs-heap-tables/

http://kejser.org/clustered-indexes-vs-heaps/

https://www.red-gate.com/simple-talk/sql/database-administration/sql-server-heaps-and-their-fragmentation/

We know a good reason to have identity is for row labelling, however question is mostly about internals and performance.


texts: Generally speaking, what pecking order should be used when tuning SQL Server?

texts:
When tuning SQL Server, what generally is the pecking order to use when trying to decide what to tune first? I'm asking in regards to an existing database that would I would have little ability to redesign.

For example, my first thought is to tune clustered indexes first, then nonclustered indexes, and maybe check constraints. Partitioning of tables and indexes would be in there somewhere. I expect that I am missing many items that could go on this list.


texts: Performance issue with Top, Order By and Where condition with more than one million rows involved

texts:
I have a query which looks something like below:
Declare @CompanyID as int = 10;

Select Top 50  UserID, AuditTypeID , [Action], ActionDate , [Description] ,
RecordID, S.ID as SiteUserID from AuditTrial A

inner Join SiteUser S on S.ID = A.UserID

where A.CompanyID = @CompanyID And A.AuditTypeID <> 1
And

UserID in
(
    1,2,3 -- will be a subquery - will have upto 100+ rows.
)

Order by ID desc -- Latest rows

This is a part of a little larger query with some extra and conditions which I have removed temporarily and even this stripped query runs slower.
Key things are Top 50, Order By ID Latest and a where condition with User ID.
The table contains more than 1 million rows.
UserID - can range from 100 to 20000.

Had Primary key with auto generate

Indexes on UserID, CompanyID etc

What I have tried so far
When the UserID where condition has less rows it runs good enough < 1 seconds but when the number of users grow it starts taking more time, i.e. greater than 10 seconds.
Thousands of rows are added in this table sometimes with SqlBulkCopy and regular normal inserts are common.

I had replaced the Subquery to Join - Not much impact
Put userIDs in temporary table first - again not much of a impact
Tried some view with DataSchema (googled) - still slow

Not being a DBA - I am not much aware about clustered/Non clustered index.
Any help is appreciated.
Additional details
Apart from Primary key which already creates an Index others are as follows:
For all in columns that will be used in where columns like UserID, CompanyID, PermissionID and so on. I didn't have any non clustered index with multiple columns
CREATE NONCLUSTERED INDEX [IX_AuditTrial_1] ON [dbo].[AuditTrial]
(
[UserID] ASC
)

If I hard code the UserID with 50 values separated by comma in ,In' Clause than the subquery runs instantly.
Full Live Query

declare  @SiteUserID as int = 6484,
 @CompanyID as int = 34;

DECLARE @ColleageUsers TABLE(
ID int
)

Insert into @ColleageUsers
Select SUB.SiteUserID from SiteUserBroker SUB (nolock)
inner join (Select Count(*) as TotalBrokers , SiteUserID from SiteUserBroker group by SiteUserID)Nested on Sub.SiteUserID = Nested.SiteUserID
where BrokerID in (
Select BrokerID from SiteUserBroker (nolock) where SiteUserID = @SiteUserID)
group by SUB.SiteUserID
Having Count(*) - MIN(TotalBrokers) >= 0

Select Top 50  UserID, AuditTypeID , [Action], ActionDate , [Description] , RecordID, ActionedBy =  S.FirstName + ' ' + S.LastName , S.ID as SiteUserID from AuditTrial (nolock) A
inner Join SiteUser (nolock) S on S.ID = A.UserID
where A.CompanyID = @CompanyID And A.AuditTypeID  1 -- 1 Means Audit Type Login
And
(
184 = (Select Distinct Top 1 PermissionID from PermissionGroup (nolock) where GroupID in
(Select GroupID from SiteUserGroup (nolock) where SiteUserID = @SiteUserID) and PermissionID = 184)
OR
A.AuditTypeID  20   - Not equal to
)
And (A.PermissionID is null Or A.PermissionID in ( Select PermissionID from PermissionGroup (nolock) where GroupID in
(Select GroupID from SiteUserGroup (nolock) where SiteUserID = @SiteUserID)))
And
(
(
UserID in
(
  Select Id from @ColleageUsers

)
)
)

Order by A.id desc

Query Plan
https://www.brentozar.com/pastetheplan/?id=SkQt9mGvr
Plan Link


texts: What is an effective way at handling the issue of Postgres allocating significant memory when it forks a new process?

texts:
I've seen many articles and videos that say things like the following about Postgres over MySQL.

Postgres allocates a significant amount of memory (about 10MB) when it
forks a new process for each connection. This causes bloated memory
usage and effectively eats away at speed. Thus, it sacrifices speed
for data integrity and standards compliance. For a simple
implementation, then, Postgres would be a poor choice! - Sumo Logic

Every time I read that or hear that somewhere, there's no context about what it really means or if there is a way to handle it. What is are specific way to deal with that type of problem in PostgreSQL? Is this overcome by using connection pools?


texts:
I'm working with a database where colors are stored as integers. For some unknown reason, they are negative integers actually.

Now, I need to use them in the CSS background-color property, which accepts color names, RGB colors or hexadecimal colors. The problem is I don't know how to convert them correctly to get an acceptable string value.

I tried converting -65280 with CONVERT(VARBINARY(8), abs(S.Color)) and I'm obtaining 0x0000FF00. What I need as output, however, is either #00ff00 or rgb(0,255,0). How can I achieve that?


texts: Our client is asking some document regarding MySQL database filesystem best practices, could you please share information about that.


texts: Storing Tlogs and data files on the same drive

texts:
I'm looking for some clarification of various things I've read.  I understand it to be best practice to store your transaction logs on a separate drive (spindle) than where you MDFs and NDFs are housed.

Does this same principal hold true in a virtualized environment on a SAN?


texts: Improve the poor performance of the sys.dm_db_stats_properties DMV

texts:
We have some databases with wide tables on COLUMNSTORE compression (21 or 30 COLUMNS) and 2500 partitions (by date). There are about 4000 stats objects in this database, of which most are INCREMENTAL column statistics on the partitioned tables.

When running sys.dm_db_stats_properties on these databases, the performance of this table function is extremely poor. We are looking at approximately 1 second per ROW - i.e per 'run' of the this table function.

Here is an example of the query plan generated by a simple query with the CROSS APPLYsyntax used to execute this table function against 1605 stats-table combinations.

There is nothing very helpful here - the performance is clearly poor from the DMV.

My current theory is that due to the nature of the statistics objects in the database, the query against the OPENROWSET internal table is poorly optimized (possibly the TOP 1, and this is what is causing the slowdown.

CREATE FUNCTION sys.dm_db_stats_properties (@object_id int, @stats_id int)
RETURNS TABLE
AS
RETURN SELECT TOP 1 -- The first row in the TVF will be the root; avoid scanning entire TVF to find any additional rows.
    object_id, -- Columns now explicit since underlying tvf has additional columns we don't want to expose for backwards compat
    stats_id,
    last_updated,
    rows,
    rows_sampled,
    steps,
    unfiltered_rows,
    modification_counter,
    persisted_sample_percent
FROM OPENROWSET(TABLE DM_DB_STATS_PROPERTIES, @object_id, @stats_id)

However, given that sys.dm_db_stats_properties is a DMV and therefore immutable, we can't change the way it queries the internal tables or anything like that, as far as I know.

The objective here is to obtain the values for the columns rows, rows_sampled, unfiltered_rows, modification_counter, last_updated as obtained from sys.dm_db_stats_properties in a way that doesn't take 3 hours per database! It does not matter if we use a different DMV, so long as the source of the information is not less accurate.

We have tried reorganizing all the system tables to see if that has any effect with statements such as ALTER INDEX ALL ON [Database].sys.[table to reorganize] REORGANIZE. However, no performance increase was observed.

The we are running the following version of SQL server:
Microsoft SQL Server 2017 (RTM-CU18) (KB4527377) - 14.0.3257.3 (X64) on Windows Server 2016.The compatibility level is 140


texts: I am about to setup Ola Hallengrens database maintenance plan. We have our database mirrored and I was just wanting to know if I need to run the script on both my Sql Server instances or only the primary?


texts:
I'm trying to create an xml with customer specific prices for a webshop I am working on atm.

I have to join data from 6 tables in order to acomplish that.

I ran into a problem I am unable to solve for a couple of days now.

The thing is that I join customer ID with discount group (works fine),

then I join it with data that are stored in another table that connects item groups with customer discount groups (this table is not complete - it has data if someone entered discount for specific customer for specific group of items).
The end result lacks all items from item groups that have no data in this table.

then I join it with item discount group table where most items have item discount group written in it.

then i tried to full outer join it with a table of items that have webshop tag enabled (so I would get all items in the result that are included in the webshop but have no discounts for a customer entered) - this thing does not work

lastly I cross apply latest prices with items. (works fine)

Any help would be gravely appreciated.

Code:

select stranka.SifraStranke,
--  stranka.DomacaStran,
--  strFaktura.SifraGrucePopusti,
--  popust.OdstotekPopusta,
--  popust.SifraGruceArtikla,
--  artProdaja.SifraArtikla,
    osnArtikel.SifraArtikla,
    cene.ProdajnaCena * ((100-popust.OdstotekPopusta)/100)

from STRStrankeSplosno stranka      -- customer basic data table
left join
    STRStrankeFaktura strFaktura    -- customer financial data table
    on stranka.SifraStranke=strFaktura.SifraStranke
left join
    ARTPopustiStranke popust        -- customer discount data table for each discount group
    on strFaktura.SifraGrucePopusti=popust.SifraGruceStranke
left join
    ARTArtikliProdaja artProdaja    -- item discount group data table
    on popust.SifraGruceArtikla=artProdaja.SifraGrucePopust
full outer join
    ARTArtikliOsnovniP osnArtikel   -- item basic data table
    on osnArtikel.SifraArtikla=artProdaja.SifraArtikla

cross apply (
    select top 1 cene.ProdajnaCena
    from ARTArtikliCeneCP cene      -- item prices data table
    where cene.SifraArtikla=osnArtikel.SifraArtikla
    and cene.SifraCenovnegaPodrocja='MC'
    order by cene.DatumZacetkaVeljavnosti desc
    ) cene
where stranka.DomacaStran<>''
and osnArtikel.SpletObjava='D'
and stranka.StevilkaSifranta=2
and strFaktura.StevilkaSifranta=2
and popust.StevilkaSifranta=2
and artProdaja.StevilkaSifranta=2
and strFaktura.SifraGrucePopusti<>''
order by stranka.SifraStranke, osnArtikel.SifraArtikla


texts: Removing Archive Logs after PostgreSQL PITR online-recovery

texts:
I'm using pgpool-II + PostgreSQL 8.4 on three servers: primary + standby1 + standby2.
Replication mode is "on"
Load balance mode is "on" (between primary and standby1)

I followed the official tutorial to configure online recovery with PITR:
http://pgpool.projects.postgresql.org/pgpool-II/doc/pgpool-en.html#online-recovery

There is a tar command in script "copy-base-backup":

  tar -C /data -zcf pgsql.tar.gz pgsql

So all my PG cluster directory being copied while script is running:

ls -1 /srv/pg/data/
PG_VERSION
archive # directory with postgres archive files
backup_label.old
base
global
pg_clog
pg_hba.conf
pg_ident.conf
pg_log
pg_multixact
pg_stat_tmp
pg_subtrans
pg_tblspc
pg_twophase
pg_xlog
pgpool_recovery
pgpool_recovery_pitr
pgpool_remote_start
postgresql.conf
postmaster.opts
postmaster.pid
recovery.conf
recovery.done

How can I safely remove old archive logs from primary and from standby nodes? There are about 30Gb of archives per day on my nodes.


texts: sqlserver - Insert from xml generated with `select for xml`

texts:
Is there a simple way to create an XML (file, variable, value in column, whatever) with select * from table for xml ... and later automatically insert this data into identical table in different database? Something like insert into table from xml .... All solutions I can find require providing column names explicitly.
What I want in the end is a simple thing - I have Java service connecting to the database. It is able to download new dictionary data and insert (merge) it into specific tables. Currently there are quite a few such tables and we have separate procedures for them, all specifying columns names and so on. This is getting out of hand and I'm looking for some way of simplifying it.


texts: What the "Reads" Column in Profiler exactly mean?

texts:
Can any one explain, whats the "Reads" column in SQL Server Profiler exactly mean ?

Why sum of logical reads get by SET STATISTICS IO ON are different from number of reads in SQL Profiler?

Which other types of reads are added in SQL Profiler to show?


texts: SQL Server Cannot Create Transactional replication Error 14013

texts:
I am trying to create transactional replication but encountered the following error:

Please help,

[


texts: Tokudb storage engine as mysql-cluster storage engine

texts: Can we use tokudb storage engine as mysql-cluster storage engine? If can, how? I search over google and found nothing


texts:
While creating a PostgreSQL database of customer subscribed services with DNS domains, I want to:

enable customers to subscribe to multiple services
each service can have multiple domain names per customer
a domain name must be unique per customer per timestamp

Example customers:

1 | Sun Microsystems (USA) Inc.
2 | Sun Microsystems (UK) Ltd.

Example domains:

1 | sun.com
2 | sun.co.nz
3 | sun.co.uk

If Sun USA subscribes with the .com & .nz domains in January, then decides in April to have the offshore UK office manage these services instead, along with the .uk domain. Therefore the customer ID changes at a timestamp for billing in the different currency.

A domain name must be live (have no deleted_at timestamp) for only 1 customer per time. A customer may delete a domain & re-add it later, or another customer may add it later. For billing, I need to know which customer had what domains active on what dates.

How do I manage the link between domains & subscriptions, and maintain a history?

CREATE TABLE subscription
(
  id serial NOT NULL,
  customer integer NOT NULL,
  service integer NOT NULL,
  created timestamp with time zone NOT NULL DEFAULT now(),
  suspended timestamp with time zone,
  ceased timestamp with time zone,
  CONSTRAINT subscription_pk PRIMARY KEY (id),
  CONSTRAINT subscription_customer_fk FOREIGN KEY (customer)
      REFERENCES customer (id) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE RESTRICT,
  CONSTRAINT subscription_service_fk FOREIGN KEY (service)
      REFERENCES service (id) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE RESTRICT
)

CREATE TABLE dns_domain
(
  id serial NOT NULL,
  name character varying(256) NOT NULL,
  added_at timestamp with time zone NOT NULL DEFAULT now(),
  CONSTRAINT dns_domain_pk PRIMARY KEY (id),
  CONSTRAINT dns_domain_uk UNIQUE (name)
)

This simple link table works, but without maintaining any history:

CREATE TABLE subscribed_dns_domain
(
  subscription integer NOT NULL,
  dns_domain integer NOT NULL,
  CONSTRAINT subscribed_dns_domain_pk PRIMARY KEY (subscription, dns_domain),
  CONSTRAINT subscribed_dns_domain_dns_domain_fk FOREIGN KEY (dns_domain)
      REFERENCES dns_domain (id) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE RESTRICT,
  CONSTRAINT subscribed_dns_domain_subscription_fk FOREIGN KEY (subscription)
      REFERENCES subscription (id) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE RESTRICT
)

If I want to allow multiple instances of the same subscribed domain on different dates (only 1 having a NULL deleted_at timestamp), how do I do that? This doesn't work:

DROP TABLE subscribed_dns_domain;
CREATE TABLE subscribed_dns_domain
(
  subscription integer NOT NULL,
  dns_domain integer NOT NULL,
  added_at timestamp with time zone NOT NULL DEFAULT now(),
  deleted_at timestamp with time zone,
  customer_add_reference character varying(40),
  customer_delete_reference character varying(40),
  CONSTRAINT subscribed_dns_domain_pk PRIMARY KEY (subscription, dns_domain, added_at),
  CONSTRAINT subscribed_dns_domain_dns_domain_fk FOREIGN KEY (dns_domain)
      REFERENCES dns_domain (id) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE RESTRICT,
  CONSTRAINT subscribed_dns_domain_subscription_fk FOREIGN KEY (subscription)
      REFERENCES subscription (id) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE RESTRICT
)

Should I revert to the first subscribed_dns_domain table, and also have a subscribed_dns_domain_history table, with timestamps populated by triggers on the simple subscribed_dns_domain table? Not sure how to populate customer reference/ticket numbers ...

How about a partial unique index or EXCLUDE USING gist () on the above table? A quote from the manual:

  Note: The preferred way to add a unique constraint to a table is ALTER
  TABLE ... ADD CONSTRAINT. The use of indexes to enforce unique
  constraints could be considered an implementation detail that should
  not be accessed directly.


texts: Index that is not used, yet influences query

texts:
I've got a PostgreSQL 9.3 table with some numbers and some additional data:

CREATE TABLE mytable (
    myid BIGINT,
    somedata BYTEA
)

This table currently has about 10M records and takes 1GB of disk space. myid are not consecutive.

I want to compute how many rows are in every block of 100000 consecutive numbers:

SELECT myid/100000 AS block, count(*) AS total FROM mytable GROUP BY myid/100000;

This returns about 3500 rows.

I noticed that existence of a certain index significantly speeds up this query even though the query plan does not mention it at all. The query plan without the index:

db=> EXPLAIN (ANALYZE TRUE, VERBOSE TRUE) SELECT myid/100000 AS block, count(*) AS total FROM mytable GROUP BY myid/100000;
                                                               QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------
 GroupAggregate  (cost=1636639.92..1709958.65 rows=496942 width=8) (actual time=6783.763..8888.841 rows=3460 loops=1)
   Output: ((myid / 100000)), count(*)
   ->  Sort  (cost=1636639.92..1659008.91 rows=8947594 width=8) (actual time=6783.752..8005.831 rows=8947557 loops=1)
         Output: ((myid / 100000))
         Sort Key: ((mytable.myid / 100000))
         Sort Method: external merge  Disk: 157440kB
         ->  Seq Scan on public.mytable  (cost=0.00..236506.92 rows=8947594 width=8) (actual time=0.020..1674.838 rows=8947557 loops=1)
               Output: (myid / 100000)
 Total runtime: 8914.780 ms
(9 rows)

The index:

db=> CREATE INDEX myindex ON mytable ((myid/100000));
db=> VACUUM ANALYZE;

The new query plan:

db=> EXPLAIN (ANALYZE TRUE, VERBOSE TRUE) SELECT myid/100000 AS block, count(*) AS total FROM mytable GROUP BY myid/100000;
                                                            QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------
 HashAggregate  (cost=281242.99..281285.97 rows=3439 width=8) (actual time=3190.189..3190.800 rows=3460 loops=1)
   Output: ((myid / 100000)), count(*)
   ->  Seq Scan on public.mytable  (cost=0.00..236505.56 rows=8947485 width=8) (actual time=0.026..1659.571 rows=8947557 loops=1)
         Output: (myid / 100000)
 Total runtime: 3190.975 ms
(5 rows)

So, the query plans and the runtimes differ significantly (almost three times) but neither mention the index. This behavior is perfectly reproducible on my dev machine: I went through several cycles of dropping the index, testing the query several times, recreating the index, again testing the query several times. What's happening here?


texts: How to create a row for every day in a date range using a stored procedure?

texts:
I would like to create a stored procedure that will create a row in a table for every day in a given date range.  The Stored Procedure accepts two inputs - A start date and end date of the date range desired by the user.

So, let's say I have a table like so:

SELECT Day, Currency
FROM ConversionTable

Day is a DateTime, and Currency is just an integer.

To keep things simple, let's just say I always want the Currency column to be 1 for each of these inserted rows.  So, if someone inputs 'March 5, 2017' as the start date and 'April 11, 2017' as the end date, I would like the following rows created:

2017-03-05 00:00:00, 1
2017-03-06 00:00:00, 1
...
2017-04-11 00:00:00, 1

What's the best way to code the stored procedure to do this?  I am using SQL Server 2008 R2 in my test environment, but our real environment uses SQL Server 2012, so I can upgrade my test machine if there is new functionality introduced in 2012 that makes this task easier.


texts: Speed problem with a simple select statement

texts:
I'm having a problem with a select statement taking way too long to run.  I would like to know if I'm doing something wrong or if it taking this long makes sense. I am using Microsoft SQL 2005 standard.  The select I am running takes about 30 seconds to run and return about 60000 records.  Here's the sql:

SELECT l.ID, l.Number, l.Date, l.FirstName + ' ' + l.LastName Name,
    isnull(l.Amount,0) Amount1, isnull(l.Interest,0) Interest1,
    isnull(l.Interest2,0) Interest2, l.LastPayment,
    l.NextDueDate, isnull(l.Aging,0) Aging, isnull(l.InterestRate,0) InterestRate,
    ls.DisplayValue Status, lt.DisplayValue Type, l.LocationID
    FROM Table1 l
    JOIN Table2 ls ON (l.Table2ID=ls.ID)
    JOIN Table3 lt ON (l.Table3ID=lt.ID)
    WHERE
    l.Table3ID IN (SELECT Value FROM fn_Split('5,4,3', ','))
    AND l.Table2ID IN (SELECT Value FROM fn_Split('8,20,9,14,18,21,22,26,13,10,27,25,23,24,16,3,11,6,4,12,19,17,5,28,7,15', ','))
    AND l.LocationID IN (SELECT Value FROM fn_Split('4,6,8,9,10,14,11,12,13', ','))

I've added indexes but nothing seems to speed it up.  If I remove everything in the select except for l.ID and l.Number I can get it to complete in about 8 seconds, if that helps at all.  This DBA stuff is not my area of expertise so any help at all would be greatly appreciated. Thank you.


texts: Replace a sequential set of numbers with special character

texts:
I have a varchar(200) column that contains entries such as,

ABC123124_A12312
ABC123_A1212
ABC123124_B12312
AC123124_AD12312
A12312_123
etc..

I want to replace a sequence of numbers with a single * so that I can group the distinct non-numeric patterns in the table.

The result for this set would be
ABC*_A*
ABC*_B*
AC*_AD*
A*_*

I have written the following primitive query below, it works correctly, but takes a long time to run on a huge table.

I need help with rewriting or editing it to improve it's performance. SQL Server 2014

-- 1. replace all numeric characters with '*'
-- 2. replace multiple consecutive '*' with just a single '*'
SELECT REPLACE
        (REPLACE
             (REPLACE
                  (REPLACE
                       (REPLACE
                            (REPLACE
                                 (REPLACE
                                      (REPLACE
                                           (REPLACE
                                                (REPLACE
                                                     (REPLACE
                                                          (REPLACE
                                                               (REPLACE(SampleID, '0', '*'),
                                                                '1', '*'),
                                                           '2', '*'),
                                                      '3', '*'),
                                                 '4', '*'),
                                            '5', '*'),
                                       '6', '*'),
                                  '7', '*'),
                             '8', '*'),
                        '9', '*')
                  , '*', '~*') -- replace each occurrence of '*' with '~*' (token plus asterisk)
             , '*~', '') -- replace in the result of the previous step each occurrence of '*~' (asterisk plus token) with '' (an empty string)
        , '~*', '*') -- replace in the result of the previous step each occurrence of '~*' (token plus asterisk) with '*' (asterisk)
        AS Pattern
FROM TABLE_X

Data

The column includes letters and numbers [A-Za-z0-9] and may also include the special characters / and _. I want to replace any sequence of numbers with *, but I do not know if the entry has special characters, and if so how many special characters.

I also do not know how many sequences of numbers are in the entry. All I know is that an entry must have a minimum of 1 number sequence.


texts: Performance issues from RDS Aurora 5.6 to Aurora 5.7

texts:
Recently upgraded one of my RDS instances to aurora mysql 5.7 from 5.6 and I've had to triple the instance class while I try to resolve this (cpu eventually pegs and never declines).
Most reads are slow now (some about 30% slower, others are up to 400% slower).  Looking at explain output, the queries are still using proper indexes. innodb_buffer_pool_size us set to default RDS value (instance class*3/24 I think) and most other innodb_* settings are not modifiable.
Here's an example Mysql5.6:
| count(*) |
+----------+
| 20646739 |
+----------+
1 row in set (0.06 sec)

Mysql5.7:
mysql> select count(*) from nope;                                                                                +----------+
| count(*) |
+----------+
| 20646739 |
+----------+
1 row in set (3.77 sec)

Anyone else encounter this that can provide some insight into some setting I can tweak?
Both instances are db.r3.2xlarge.


texts:
According to the documentation,
"Use the constant DBMS_STATS.AUTO_CASCADE to have Oracle determine whether index statistics are to be collected or not."

But it seems like AUTO_CASCADE will always gather index statistics, even when it obviously does not need to.

This test case demonstrates the problem:

SQL> --Create table
SQL> create table statistics_test(a number, b number, c number, d number, e number);

Table created.

SQL> --Add 10 million rows (about 320 MB)
SQL> begin
  2     for i in 1 .. 100 loop
  3             insert into statistics_test
  4             select level, level, level, level, level from dual
  5             connect by level <= 100000;
  6     end loop;
  7     commit;
  8  end;
  9  /

PL/SQL procedure successfully completed.

SQL> --Create large index (AFTER the data load)
SQL> create index statistics_test_index on statistics_test(a,b,c,d,e);

Index created.

SQL> --Statistics are automatically gathered on index creation or rebuild.
SQL> select to_char(last_analyzed, 'HH24:MI:SS') last_analyzed
  2  from user_indexes
  3  where index_name = 'STATISTICS_TEST_INDEX';

LAST_ANA
--------
16:11:43

SQL> --Gather stats, use AUTO_CASCADE.
SQL> exec dbms_stats.gather_table_stats(user, 'STATISTICS_TEST', cascade => DBMS_STATS.AUTO_CASCADE);

PL/SQL procedure successfully completed.

SQL> --FAIL: Time changed - index was re-analyzed even though no data changed.
SQL> select to_char(last_analyzed, 'HH24:MI:SS') last_analyzed
  2  from user_indexes
  3  where index_name = 'STATISTICS_TEST_INDEX';

LAST_ANA
--------
16:12:12

SQL> --Let's assume that LAST_ANALYZED is lying.
SQL> --What I'm really worried about is the extra time spent analyzing indexes.
SQL> --Compare performance of AUTO_CASCADE, TRUE, and FALSE.
SQL> --FAIL: AUTO_CASCADE is as slow as TRUE.  It should be as fast as FALSE.
SQL> set timing on
SQL> exec dbms_stats.gather_table_stats(user, 'STATISTICS_TEST', cascade => DBMS_STATS.AUTO_CASCADE);

PL/SQL procedure successfully completed.

Elapsed: 00:00:20.80
SQL> exec dbms_stats.gather_table_stats(user, 'STATISTICS_TEST', cascade => true);

PL/SQL procedure successfully completed.

Elapsed: 00:00:19.58
SQL> exec dbms_stats.gather_table_stats(user, 'STATISTICS_TEST', cascade => false);

PL/SQL procedure successfully completed.

Elapsed: 00:00:08.27

I do not think it matters, but my system is using the default for ESTIMATE_PERCENT, METHOD_OPT, DEGREE, GRANULARITY, CASCADE, and NO_INVALIDATE.  I can reproduce this issue on 10.2.0.4, 11.2.0.1, 11.2.0.3, and 12.2.0.1.

This looks like a bug, but I can't find anything on My Oracle Support.  And it's strange that a default setting, with significant performance impact, would be completely broken for so long.

Am I missing something here?


texts:
My question consists of three parts:

When can I be sure that my database design is perfect?

Is returning to the database design to change some issues (i.e, adding new column, deleting a column, changing data type, add new table, etc.) considered a bad practice or is it normal?

Are there any websites or books just for training on ERD and normalization? I want a lot of samples, practices, and case studies with recommended answers, to strengthen my skill in database design and avoid the poor database designs I've made.

Note: I don't need books to explain the concepts, what I need is practices, examples, and case studies with recommended answers.


texts: PostgreSQL database for GITLAB - Grant privileges only to user PUBLIC?

texts:
I manage a Postgresql database for a GITLAB application.
The developer provided me with a backup file for migrating his database to a new server that contains the following statements:

ALTER DEFAULT PRIVILEGES FOR ROLE <dbowner> IN SCHEMA public REVOKE ALL ON SEQUENCES  FROM <dbowner>;
ALTER DEFAULT PRIVILEGES FOR ROLE <dbowner> IN SCHEMA public GRANT SELECT,USAGE ON SEQUENCES  TO PUBLIC;

ALTER DEFAULT PRIVILEGES FOR ROLE <dbowner> IN SCHEMA public REVOKE ALL ON TABLES  FROM <dbowner>;
ALTER DEFAULT PRIVILEGES FOR ROLE <dbowner> IN SCHEMA public GRANT SELECT,INSERT,DELETE,UPDATE ON TABLES  TO PUBLIC;

Why would you revoke all privileges from the database owner & grant them instead to PUBLIC?  I thought that granting privileges to user PUBLIC is a big security risk?


texts: link multiple MS project to Access/Excel

texts:
sir
I have a large project tracking database used to track PMs, status, help needed, action items, reports etc. I keep the project schedule in a large MS-Project file. I've exhausted all efforts to have my organization invest in an MS-Project server. How can I use the data found in my project file: Task Name, Resource, Start Date, End Date... etc as source data into my access database. Can these files be linked somehow if I put the project ID# into my project file as a foreign key? I just need to make the data available to people other than the PM. Ideas?

how can multiple MS project file can be view in one ms access or excel or any other application


texts: Why can I not DENY permissions through module signing?

texts:
In the documentation for ADD SIGNATURE, they have the following caveat:

  Module signing should only be used to grant permissions, never to deny or revoke permissions.

My question is: why? Conceptually, I think of signature possibly adding to the user token. If the user linked to the certificate or key that signed the module has a DENY permission associated with it, I'd think that it would come along for the ride. My testing shows that it doesn't, but I'd like to understand why it doesn't.


texts:
I have been trying to create a clustered index on a table which is 1.4 TB in size.

This is my query:

CREATE CLUSTERED INDEX [C_IDX_Impressions]
ON [dbo].[Impressions] ( [User_ID] ASC )
WITH (PAD_INDEX = OFF,
      STATISTICS_NORECOMPUTE = OFF,
      SORT_IN_TEMPDB = OFF,
      DROP_EXISTING = OFF,
      ONLINE = OFF,
      ALLOW_ROW_LOCKS = ON,
      ALLOW_PAGE_LOCKS = ON)
ON [Impressions]
GO

The [Impressions] filegroup has 1TB of free space. But the query is failing with the following message:

  Could not allocate space for object 'dbo.SORT temporary run storage: 140740546723840' in database 'HULU' because the 'Impressions' filegroup is full. Create disk space by deleting unneeded files, dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup.

Am I missing something here?

The Tempdb should not come in the picture. Is it correct?

Any input will be appreciated.


texts:
We have a system running SQL Server 2008 R2 on a Hyper-V VM (64GB RAM and 16 virtual cores).

Another VM (the application server) within the system accesses the db server quite intensively for both querying and data updates.

We have been experiencing two main exceptions in the application server at the time were a particular maintenance sub plan stored procedure is executed.

The two exceptions are:

  System.Data.SqlClient.SqlException (0x80131904): Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding (.......)

  System.Data.SqlClient.SqlException (0x80131904): The app domain with specified version id (1859) was unloaded due to memory pressure and could not be found. (.....)

The maintenance plan stored procedure executed at this time has the following statements:

DBCC FREESYSTEMCACHE ('ALL')
DBCC FREESESSIONCACHE
DBCC FREEPROCCACHE
CHECKPOINT
DBCC DROPCLEANBUFFERS

This is obviously causing the issues after some testing. Also I have found out that:

Timeout exception is caused by this:

CHECKPOINT
DBCC DROPCLEANBUFFERS

Memory pressure exception is caused by this:

DBCC FREESYSTEMCACHE ('ALL')
DBCC FREESESSIONCACHE
DBCC FREEPROCCACHE

Unfortunately we don't know the reason for having this maintenance task happening. My first thought is remove this task, but I don't know if there would be more problems by not having these running.

Are these DBCC commands really necessary?

Thanks


texts: Kafka Internal Data structure vs LSM tree

texts: I was going through Database storage engines and found out about LSM trees. I had also read about Kafka architecture and know that Kafka internally is a commit log. I want to know if Kafka internally uses LSM data structure for append-only store or uses some other data structure for storing data?


texts:
For the last few days the service broker command which run from master database is consuming much more RESOURCES than before. The Impact on the Database is high (from the bad side), It causes for internal waits.
I didn't make any changes in the database before and after the 10.07.2014 (you can see this start date in the graph).
I checked if service broker is enabled in any database and found that it is, in Tempdb & Msdb. What can cause these changes ?

I attached a graph, you can see the behavior change.

i attached another screen shot as you can see from sp_who2, what i don't understand is, you can see that the "LastBatch" is in the very old , from the 10/12 07:55 (this results are from today 10/20/2014 10:23 Local time). why the session not closed . ?


texts: uniqueness in array column type throughout all records

texts:
There is a table

id serial primary key,
names varchar(20)[]

it contains such record

1, {"one", "two", "three"}

Then, next record is inserting (notice one array element already exists in existing record)

2, {"four", "one"}

or existing record is updated by adding one more element to the array, but such string element is already used in some other record.

Is it possible to create a unique constraint which wouldn't allow repeating elements inside arrays globaly throughout all rows and not just inside the array value itself?


texts:
I'm using web2py to create an application

I've got 2 different roles: client and referents. Every client can have multiple referents.
Both client and referent can insert row in a table, called object.

The user table, where client and referent are registered, has a column called "client_id".
Every referent in the column_id has the id of the client to whom he reference to.

The problem is: which value should a client have in client_id?
A possible answer could be NULL or the id of the client itself.

Which one is better? Or there's something better?


texts:
I've spent a lot of time searching for, researching and implementing various complex MySQL queries over the last several years. Many of them use lots of aliases for tables, even when none are necessary.

Why is this? I've seen a little discussion about using aliases to improve readability, but other than for aggregate/calculated columns, I find that it's majorly detrimental to readability. Your brain has to translate between aliases and actual tables.

For example:

SELECT orders.id, COUNT(*)
FROM orders, order_items
WHERE order_items.order_id = orders.id
GROUP BY orders.id
HAVING COUNT(*) > 1

vs

SELECT o.id, COUNT(*)
FROM orders o, order_items i
WHERE i.order_id = o.id
GROUP BY o.id
HAVING COUNT(*) > 1

To me, the first version is so much easier to read, and this is just a simple example. But I see it used in simple examples a lot. Is it just to type less? Because that's a horrible reason.

I understand it makes sense if you're joining the same table one or more times, but totally not the case much of the time.

Or is there just something I'm missing?


texts: How do I see what update statistics is doing while running?

texts:
I have a very large table with 500m rows and 150 columns (I did not design the table, im simply trying to figure out what is going on here). I try to update a single statistic for this table.

I run like so

update statistics A (stat1) with sample 5000000 rows

It takes me 35 minutes for this to complete. Subsequent runs of the same task finish in less than 1 minute. I don't know what is happening here. I tried to monitor sys.dm_exec_requests and there is no other load, I did notice on the initial run that cpu_time to elapsed_time ratio was pretty low (like 1:5 vs 1:1 on subsequent runs).


texts: Foreign keys - link using surrogate or natural key?

texts:
Is there a best practice for whether a foreign key between tables should link to a natural key or a surrogate key? The only discussion I've really found (unless my google-fu is lacking) is Jack Douglas' answer in this question, and his reasoning seems sound to me. I'm aware of the discussion beyond that that rules change, but this would be something that would need to be considered in any situation.

The main reason for asking is that I have a legacy application that makes uses of FKs with natural keys, but there is a strong push from devlopers to move to an OR/M (NHibernate in our case), and a fork has already produced some breaking changes, so I'm looking to either push them back on track using the natural key, or move the legacy app to use surrogate keys for the FK. My gut says to restore the original FK, but I'm honestly not sure if this is really the right path to follow.

The majority of our tables already have both a surrogate and natural key already defined (though unique constraint and PK) so having to add extra columns is a non-issue for us in this insance. We're using SQL Server 2008, but I'd hope this is generic enough for any DB.


texts: Error 3201 Operating system error 5(Access is denied.) Creating backup on network drive

texts:
Msg 3201, Level 16, State 1, Line 1
Cannot open backup device
     '\\nst-shantanug\c$\Dummy Db Backup\dummydb.bak'.
Operating system error 5(Access is denied.).
Msg 3013, Level 16, State 1, Line 1
BACKUP DATABASE is terminating abnormally.

What all permissions do I need to assign to drive or pc where I want to take backup of my database.


texts:
I am implementing an application where users can save alerts for different services. Each service can have multiple types of alerts and each alert has X number of attributes to form a predicate. So here's an example:

Services:
- Weather
- Reddit

Weather Alerts:
- Temperature

id
user_id
type
city
condition (above, below)
temperature

- Forecast

id
user_id
type
city
forecast (rainy, sunny)

Reddit Alerts:
- Frontpage

id
user_id
type
number_of_votes

- User comment

id
user_id
type
reddit_user
number_of_votes

I think this gives a good idea of the model. All the alerts are similar but have a different number of attributes. Now my question is: How should I store these alerts in my database? Here are the options I'm considering so far:

1 table with all the possible fields. The first three (id, user_id, and type) would always have some data but most of the others would be empty as the number of alerts type grow. I don't think that's a good option.
1 table per type of alert. This seems like an easy solution but might be tricky for real life scenarios. I'll have to do plenty of joins if I want to get all the alerts for a user since they will be in different tables. The id, user_id and type fields are also always needed so this feels wrong.
1 to many relationship where I have an alert table and an alert has many attributes or conditions. That second table is basically just alert_id, attr_name, value. When I want to use an alert I just fetch all the attributes for that alert_id.
EAV model.

What are your suggestions? Am I missing something here or making all of this too complicated? Database modelling was never one of my strong areas so I'm a bit struggling to make a decision.

If it matters this is for a ruby on rails application.


texts: Delete all duplicated entries where multiple columns duplicates match

texts:
I need to add this contrain to my table called 'kraken'
ALTER TABLE kraken ADD UNIQUE unique_index(exchange_pair,timestamp);

this shows me that i have duplicated entries on those 2 columns (exchange_pair and timestamp)
so first i have to delete all the duplicated entries based on those 2 columns
this is what i found but im not sure if my query would work since i need to delete only duplicated entris with column 'exchange_pair' and 'timestamp', i dont want to mess things up with my table
https://www.mysqltutorial.org/mysql-delete-duplicate-rows/


texts: Restoration in MySQL 8 is slower than MySQL 5.6

texts:
Recently I have installed mysql 8.0.12 version and started observing that its taking too long to restore database. So I verified the same with using a backup file which I restored to MySQL 8.0.12 as well as MySQL 5.6.38.

I didn't add any variables to configuration files except datadir, socket, pid files and in MySQL 8 I added  skip-log-bin to stop bin logs.

Please see the timings which I got. The first one is mysql 8 and the second results is from 5.6.

$ time mysql < backup_file.sql
  real  5m7.485s
  user  0m1.059s
  sys   0m0.255s

$ time mysql2 < backup_file.sql
 real   1m54.475s
 user   0m1.188s
 sys    0m0.136s

Any insight what can be the reason behind this and how I can overcome.


texts: Dumping a database into a file from terminal freezes

texts:
I've had this a couple of times and I can't find a way around it. I'm dumping a mysql database from the terminal using the following command:

mysql -uusername -p my_database > myfile.sql

When I run that and enter my password I wait and wait but nothing happens, the file myfile.sql is created but has the size of 0k. Am I missing something? Is there a better way to dump a database?


texts: Is MongoDB a good fit to track a stock portfolio?

texts:
Is mongoDB or any document database in general a good fit to track a stock portfolio?

I have identified 5 main data domains:

Portfolios
Transactions
Holdings
Stocks information
Stock Prices

The holdings are just the combined view of all the transactions and are therefore derivable from the transaction history. Every time a transaction is updated, created, deleted, the holdings change.

It seems to be the most challenging part.

In a RDBMS, we could use triggers or views to work around this but I am not sure what we could do on MongoDB and if there is a good solution?


texts:
I have few FOREACH loops in my functions. The loops seem to perfectly agree with the official postgresql documentation. This error message is returned:

ERROR: loop variable of FOREACH must be a known variable or list of variables
SQL state: 42601

Google found nothing for postgresql "loop variable of FOREACH". I solved this somehow for my older foreach loops, but I don't remember how, and I can't find any difference that could be significant for the solution of this error.

I have PostgreSQL 9.3.4 and pgAdmin III 1.18.1 on Windows XP 32 bit. Here are interresting parts of the functions:

CREATE OR REPLACE FUNCTION vloz_kont() RETURNS trigger AS $$
DECLARE
  _idmat integer;
  _nazmat text;
  _zast text;
  sj_nad text;
  ...
BEGIN

...

--bugged loop
FOREACH _mat IN ARRAY NEW._material::text[] LOOP
  SELECT split_part( _mat, ' ', 1 ) INTO _nazmat;
  SELECT rtrim( ltrim(split_part( _mat, ' ', 2 ), '(' ), ')' ) INTO _zast;
  SELECT id INTO _idmat FROM pro_material WHERE popis = _nazmat;
  IF _idmat IS NOT NULL THEN
    INSERT INTO material_konst ( id_mat, kod_sj, kod_akce, zastoupeni )
    VALUES ( _idmat, NEW._kod_sj, NEW._kod_akce, _zast );
  ELSE
    INSERT INTO material_konst ( id_mat, kod_sj, kod_akce, jiny_nazev, zastoupeni )
    VALUES ( 8, NEW._kod_sj, NEW._kod_akce, _nazmat, _zast );
  END IF;
END LOOP;

...

--OK LOOP
FOREACH sj_nad IN ARRAY NEW._nad::text[] LOOP
  INSERT INTO s_vztah (nad, pod, kod_akce, typ_vztahu)
  VALUES(
    sj_nad,
    NEW._kod_sj,
    NEW._kod_akce,
    1
  );
END LOOP;

...

RETURN NEW;
END;
$$ LANGUAGE plpgsql;

Both _material and _nad are text array columns of a view, as well as all other variables. The whole INSTEAD OF trigger function is too long to reproduce in entirety (almost 200 code rows) - ... in the code sample represent skipped unrelated code.

So why is there the error, and how to solve it?


texts: How to match all words of one column against words in another column?

texts:
I have two tables.

Table A:

Company_name
-------------------------------
Tata consultancy
Infosys tech
Atm service
Air force firm

Table B:

Company1_name                    |  Id
--------------------------------   -----
Atm and service.                 |   1
Honey tech.                      |   2
Tata & consultancy.              |   3
Graft soft.                      |   4
Atm, service.                    |   5

I want this result:

Id
----
1
3
5

Description:

  For each Company_name in table A, find a row in table B whose company1_name value has all the words from table A's value, and return the matching row's Id.

How to design a query for this in MySQL?


texts: How to order some rows one way, and the rest another way?

texts:
I am trying to sort rows from a table in PostgreSQL 10.12:
CREATE TABLE example (
 id  serial PRIMARY KEY,
 val text NOT NULL,
 x   integer NOT NULL,
 y   integer NOT NULL
);

INSERT INTO example (val,x,y)
VALUES
  ('First Value (This should be first order)',7,2)
, ('Second Value (This should be second order)',6,3)
, ('Third Value (This should be third order)',5,4)
, ('Seventh Value (And This should be last order)',4,1)
, ('Sixth Value (This should be sixth order)',3,5)
, ('Fifth Value (This should be fifth order)',2,6)
, ('Fourth Value (This should be fourth order)',1,7)
;

First three result rows should be ordered by x desc, and the rest should be ordered by y desc.
I tried this query but it is ordering only by y:
SELECT * from (SELECT * from example order by x desc fetch first 3 rows only) foo
UNION
SELECT * from example order by y desc;

But it sorts by only y. And I need to get the result without UNION.
Here is an SqlFiddle.


texts: ODBC Data Source SQL Server Connection - Login Failed For User

texts:
I have moved my database from an SQL 2005 to a server with SQL 2008.

I am now trying to create an ODBC Data Source.

I am using "With SQL Server authentication using a login ID and password entered by the user" and have entered my Login and password.  The Login is visible in SQL Server Management Studio under Security-> Logins.

The login also contains the exact properties of the same login in my old server.  When I hit next after entering the username and password I get:

Connection failed:
SQLState: '28000'
SQL Server Error: 18456
[Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user 'myUser'.

After reading this: http://support.microsoft.com/kb/555332 I realized that the server properties were set to "Windows Authentication Mode" only, but even after changing to "SQL Server and Windows Authentication Mode" I am still having problems connecting.  I can connect with my Windows account though.


texts: Is there a way to purge a DTC transaction from command line?

texts:
as you can see on my screenshot, I have a DTC transaction in error on my server.
I know, I can Right -> Resolve -> Forget/Abort
However, I want to do it with a command line (batch or Powershell). Is there any way to do that?
Thank you


texts: why is this left join faster than an inner join?

texts:
So I'm tuning this query, and I am pretty sure that in this instance, I can replace an inner join with a left join without affecting the data. However, I'm not entirely sure why this is faster. Here is the query:

SELECT DISTINCT  cl.NAME                            AS company_name,
                     cl.id                              AS company_id,
                     ep.Plan__c                           AS plan_id,
                     ep.Employee__c,
                     ep.id,
                     do.Subcategory__c,
                     ep.Plan_Type__c,
                     pt.SubType,
                     Sum((pt.[shares] * fvh.[ValuePerShare])) AS TotalValue,
                     ppe.Deferral_Option__c,
                     dt.Defer_type_Code,
                     do.Short_Code__c,
                     pt.ContributionYear
  FROM   dbo.ParticipantTrades pt WITH (NOLOCK)
        INNER JOIN dbo.PayoutPathElection ppe WITH (NOLOCK)
                ON pt.payoutPathElectionID = ppe.Id
        INNER JOIN dbo.DeferralOption do WITH (NOLOCK)
                ON ppe.Deferral_Option__c = do.id
        INNER JOIN dbo.EmployeePlan ep WITH (NOLOCK)
                ON pt.employeePlan = ep.Id
        LEFT JOIN dbo.DeferralType dt WITH (NOLOCK)
                ON pt.deferralType = dt.defID
        INNER JOIN dbo.Fnc_lastfundvalue('2019-01-30') AS fvh
                ON pt.fund = fvh.Fund
        INNER JOIN dbo.Clients cl with (NOLOCK)
                ON ep.Company__c = cl.Id
  WHERE ep.Company__c = '0017000001WL1HfAAL' AND ep.Plan_Type__c  LIKE '%' AND pt.tradeDate <= '2019-01-30'
  Group by   cl.NAME,
             cl.id,
             ep.Plan__c,
             ep.Employee__c,
             ep.id,
             do.Subcategory__c,
             ep.Plan_Type__c,
             pt.SubType,
             ppe.Deferral_Option__c,
             dt.Defer_type_Code,
             do.Short_Code__c,
             pt.ContributionYear

The bottleneck is at the table-value function join (Fnc_lastfundvalue). My hunch to why changing it to a left join is faster is that it can then reorder the joins and it causes less spillage into tempdb? Here is the query plan before and after changing INNER JOIN dbo.Fnc_lastfundvalue.. to LEFT JOIN dbo.Fnc_lastfundvalue..

Before (29 seconds): https://www.brentozar.com/pastetheplan/?id=Bk1Y-WqEE

After(3 seconds): https://www.brentozar.com/pastetheplan/?id=B1hoW-544

NB: Execution plans above are created on a dev box. The production server is still on SQL Server 2008.


texts: MariaDB defragmenting with new option innodb-defragment=1

texts:
I try to defragment table using innodb-defragment=1
https://mariadb.com/kb/en/library/defragmenting-innodb-tablespaces/

But it was a bad experience :
- locks on table
- no a good defragmentation result (always data free on table)

So I use percona-tools
https://www.percona.com/doc/percona-toolkit/LATEST/pt-online-schema-change.html

And get a good result no lock et no more data free on table. (But need space on disk to duplicate table)

But what's wrong with the first method ?

UPDATE :


texts: Redis remove slave from replication along with sentinel

texts:
I have a 3-nodes Redis replication and a 3-nodes Sentinel. I want to remove one slave.

I tried stop slave and remove slaveof 10.128.130.139 6379 from configuration. But it became slave again after I started it. And slaveof was back again in config.

After reading SLAVEOF, I tried invoking SLAVEOF NO ONE. But it became slave again after 1 min.

I guess Sentinel reconfig it to slave. How can I remove one slave in my case where Sentinel exists?


texts: Query to test and return timed lockout in MySQL

texts:
I have some functionality in my webapp that locks out users from certain behavior for a set amount of time (e.g., they have to wait 10 minutes before they can update a level).

My query looks like this:

SELECT date_last_updated FROM levels
WHERE user_id = 123 AND date_last_updated > date_sub(now(), INTERVAL 10 MINUTE);`

Please note that date_last_updated is DATETIME.

Currently, if a row is returned I know they're still in the lockout, so that's fine for a boolean result. But just a single column row of the remaining time, in minutes as an integer would be better. That is where anything > 0 indicates both lockout is in effect, and how much time is left.

How would I do this?


texts: SQL Server hitting System.OutOfMemoryException but server has 10GB free

texts:
SQL Server 2012 11.0.5058.0

I'm running a very large batch of queries (about 2700) to simulate a user doing X, Y and Z in our app. I have SQL Server's max memory set to 2147483647 MB. However when I run the query I get several messages like so:

An error occurred while executing batch. Error message is: Exception of type 'System.OutOfMemoryException' was thrown.

When I look at the server it has 20GB of memory free and SQL Server is only using 4.8GB (and that number is stable).

I've tried putting some GO statements in to no avail. I don't know where it is hitting the memory issues. None of the queries return more than a few rows.

How can I get around this memory issue?


texts:
I need to set up a SQL Server Audit to capture all database activity for all logins except one - the login our application runs under. In this case the login I want to exclude is CPSDOM\cpointedb.

In SQL Server Management Studio under 'Security / Logins' I see the login CPSDOM\cpointedb. But, when I look under 'Databases / dbName / Security / Users', I do not see the user CPSDOM\cpointedb.

When I set up my Database Audit Specification I also do not see a CPSDOM\cpointedb user. I was hoping to see this user listed in the Database Audit Specification so I could check all the users except that one.

Is there a different (better) way to do this? All I want to do is set up an audit that excludes one user.

I like the idea of just auditing all users (public) in the audit and excluding CPSDOM\cpointedb from the analysis, but I am concerned this may cause performance problems. Is that a valid concern?


texts: Query for a table with paging and filtering vs. CTE (common table expression)

texts:
Intro

In PostgreSQL 9.3: I am building a query that fetches data for a table that supports sorting, filtering and paging. Think Customers for example, you want to show name, surname, some detailed information, some information from associated tables (eg. past purchases) etc. A lot of data is being fetched, and in the simplest form the query looks like this:

SELECT
  customer.name,
  customer.surname,
  (SELECT aggregate_whatever(foo) FROM bar) past_purchases,
  baz.aaa bbb,
  gaz.ccc ddd,
  ...
FROM
  customers customer
LEFT JOIN
  baz ON ...
LEFT JOIN
  gaz ON ...

There are several thousand of customers and the additional data being fetched comes from most of the system.

Filtering, paging and sorting

In the end we will have to put past_purchases into the WHERE clause when building the query according to the filter. For this reason, the whole query is encapsulated as a CTE (common table expression), just like in this SO question. It looks like this:

WITH encapsulated AS (
  SELECT
    customer.name,
    customer.surname,
    (SELECT aggregate_whatever(foo) FROM bar) past_purchases,
    baz.aaa bbb,
    gaz.ccc ddd,
    ...
  FROM
    customers customer
  LEFT JOIN
    baz ON ...
  LEFT JOIN
    gaz ON ...
)
SELECT * FROM encapsulated
WHERE
  past_purchases = 5
  AND <other conditions>

That ultimate SELECT will also have an ORDER BY clause for the necessary columns. For paging purposes, a LIMIT is added at the end, and the number of total rows is calculated as follows:

...
SELECT *, COUNT(*) OVER () as total_row_count
FROM encapsulated
WHERE
  past_purchases = 5
  AND <other conditions>
ORDER BY
  surname, name
LIMIT 0, 10

Problem

This solution works but quickly runs into performance problems. All the tables have proper indices and EXPLAIN (ANALYZE) shows a nice yet costly plan with indexed accesses, yet the whole query takes almost 20 seconds to finish.

My suspicion is that the CTE is basically built in full at first, meaning the server fetches all the data for all the thousands of customers, and only then it applies the WHERE filtering, ordering and LIMITing.

Question 1

Are the WHERE conditions outside of the CTE propagated into the CTE?

Question 2

Am I using the CTE correctly? Basically I am simplifying my life: my final WHERE clause is trivial, because it does not need to repeat the expressions that form my columns, but did I break the performance in that act?

Things I have tried

Made sure all the table accesses are on an index (this helped a bit, but still almost 20 seconds in total to fetch).
Tried replacing all the views referenced from the query by their materialized versions (no improvement).
Tried removing JOINs and sub-SELECTs from the query to see if the performance improves (only a very little).
Set shared_buffers to 1/4 of the available RAM (1 GB of 4 GB, no effect).
VACUUMed the whole database (FULL FREEZE ANALYZE, no effect).

Question 3

Am I correct in thinking that my approach requires to scan basically the whole DB, build a specialized view of it (that is that CTE) and only then apply some filtering? Is there any better way to do this?


texts: Audit Admin rights use on SQL Server 2019

texts: Being security conscious, I want to audit the use of admin rights. I would like to assign specific permissions to a user (following the concept of least privilege) that current has the SysAdmin role and I want to know what rights they are using above DBO to the database. Is there a good way to audit these actions so that I can grant those specific rights?


texts:
I have 3 servers all running Windows Server 2016 Datacenter:

Server1 is a domain controller and has ssms installed for accessing SQL Server Instances on Server2 and Server3

Server2 & Server3 are running SQL Server Microsoft SQL Server 2016 (SP1-CU3)

Server2 has a linked server link to Server3

Server3 has a linked server link to Server2

If I log onto server2 and run a select query which uses the link to server3

Select top 5 * from [Server3].[DB3].[dbo].[TableName]

It runs fine and results are returned.

If I log onto server3 and run a select query which uses the link to server2

Select top 5 * from [Server2].[DB2].[dbo].[TableName]

It runs fine and results are returned.

But If I log onto Server1 and open ssms, connect to Server2 and run query

Select top 5 * from [Server3].[DB3].[dbo].[TableName]

I receive error 'Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.'

And the same happens if I log onto Server1, open ssms, connect to server3 and run query

Select top 5 * from [Server2].[DB2].[dbo].[TableName]

I am logging onto all 3 servers using the same domain credentials, but it feels to me like if im running the query from Server1, then its not passing my credentials through causing the query to be ran against the target sql instance as NT AUTHORITY\ANONYMOUS LOGON


texts:
I have three SQL database instances on a cloud server: A, B and C and the Server Installed Physical memory (RAM) is 32GB. The size of A (data+log file) is 44GB and B (data+log file) is 41GB. We ignore C since it has no databases in it and max memory set to 500MB.

Both instances A & B has it's memory set to the default size: 2147483647 MB. Although we have had no issues this is definitely not practical.

After some research, based on database activity, i decided that instance A memory allocation should be set to 20GB and B set to 10GB. In total 30/32 of the max physical memory in the system. Below shows the actual memory consumption. I am afraid if i set the max memory it will slow down response time. Is this configuration optimal in setting the SQL Max Server Memory for such databases and how can i simulate before applying?


texts:
Assume a mysql table with columns A, B, C, ... .

There are one composite index based on columns A+B+C. Combination of values A, B and C is unique, so this index forms the primary key.

Now, a second index is need. There are two possibilities for this one: a) A+B+D+E; b) D+E.

I wonder which one of the previous options for the second index is better. Any hint is welcome.

Thanks.


texts:
This question has been asked several times and even answered as well but it's not helpful to me, that's why I am posting it again. I have this query:

select u.*,l.*,
(
    select count(cr.id)
    from cloud_recordings cr
    left join cameras c
        on c.owner_id=u.id
    where c.id=cr.camera_id
) valid_licence
from users u
left join licences l
    on l.user_id=u.id;

It works fine, but when I put a where clause at the end:

select u.*,l.*,
(
    select count(cr.id)
    from cloud_recordings cr
    left join cameras c
        on c.owner_id=u.id
    where c.id=cr.camera_id
) valid_licence
from users u
left join licences l
    on l.user_id=u.id
where valid_licence > 0;

It simply gave me an error:

  ERROR:  column "valid_licence" does not exist
  LINE 4: left join licences l on l.user_id=u.id where valid_licence >..


texts:
I have created a table where I have around 100 partitions (per month), all of them are configured to use page compression. After that I'm inserting data to this table month by month.

I now would expect SQL Server to apply the page compression. However using sp_estimate_data_compression_savings it doesn't look like it. (I would have savings of around 40%-50%.)

Did I miss something in configuring page compression?

The problem with this is that I have now large files, 70-80 GB per partition. When I compress them, they are half empty. Regaining this disk space is impossible because if I try to shrink the data file that is behind the partition I end up with a fragmented clustered index.

How can I avoid this disk space waste party?


texts: Combining previously different rows after changing value during query

texts:
I'm currently running a query that is supposed to return a salesperson's aggregated revenue, grouped by individual clients.

Because of how user input was handled, there are a lot of near-duplicate rows with minor mispellings/additions to client names. As you can see in the code below, I'm trying to trim off one of the problem strings in order to prevent duplicate rows for the same clients.

However, though the CASE statement successfully removes "@iraPrefix", the rows with now-identical client names are still not grouped together because their revenue fields are not being summed.

How should I approach this?

DECLARE @iraPrefix VARCHAR(7)
DECLARE @iraLen INT
SET @iraPrefix = 'FOO BAR '
SET @iraLen = LEN(@iraPrefix)

SELECT [client] = (
           CASE SUBSTRING([name], 1, @iraLen)
               WHEN @iraPrefix THEN SUBSTRING([name], @iraLen+2, (LEN([name]) - @iraLen))
               ELSE [name]
           END
       )
       , CONCAT( sal.firstname
               , ' '
               , sal.lastname) AS [salesman]
       , [revenue] = SUM(net)

Here's an example of the query results without prefix removal:

An example of the current query results:

And an example of the results I want:


texts: 3NF Normalization Problem Employing an Algorithm

texts:
I'm getting stucked in figuring out how to obtain the third normal form (3NF) using an algorithm ( removing attributes and then functional dependencies ).

The relation R( A,B,C,D,E,F,G,H,I,J,K ) has the following dependencies:

A → BEFG,
ACDI → H,
CD → J,
CDF → K,
B → G,
CF → ABE,
AC → D,
ABD → C,
EG → B

And the result is:

R1(B#, G)
R2(E#, G#, B)
R3(A#, B, E, F)
R4(C#, D#, J)
R5(A#, C#, I#, H)
R6(A, C, D, F, K) with primary key one of: AD or AC or CF.

When I tried to resolve, I came up with:

R1(B#, G)
R2(E#, G#, B)
R3(A#, B, E, F)
R4(C#, D#, J)
R5(A#, C#, I#, H)

and

R6(A#, C#, D )
R7(A#, D#, C )
R8(C#, F#, A, K )

With is similar but separates the last one.

Why is that the answer?
( I took it from the book ).


texts: Accessing a row directly at a table in PostgreSQL by using the PostgreSQL's internal library

texts:
I am writing a PostgreSQL extension using the C language. I would like to do the following tasks:

Store a set of rowids (or other row identifier, like ctid). Each rowid uniquely represents a row in its table.
Process this set and return other set of rowids
As a result, I want to access the tuples pointed by the returned rowids

For instance, I have a table with the rowids => {1, 2, ..., 10000}. Then, I have processed my algorithm, and as result I have the following rowids => {3, 100, 8394, 12}

However, I have some doubts:

What tuple identifier would be the best to be stored in the task 1? According to the documentation, the ctid value is not recommended to be used since it can change after a VACCUM. But, which value could I store? Any recommendation?
How to directly access the set of tuples that are pointed by the returned rowids of task 2? One solution would be to use SQL query. However, is it efficient? I mean, I am writing a extension of PostgreSQL, therefore, I have access to its internal library. Is there a direct way to do it instead of use sql queries? Is it a good and efficient solution use SQL queries with SPI?


texts: table exists on 'show tables' - can't query

texts:
Anybody know why this his happening?

 mysql> show tables;

 +-------------------+
 | Tables_in_photos  |
 +-------------------+
 | captions_images   |
 | image_meta        |
 | mentions_images   |
 | tags_images       |
 | words             |
 +-------------------+
 5 rows in set (0.00 sec)

 mysql> select * from captions_images;
 ERROR 1146 (42S02): Table 'ig_call.captions_images' doesn't exist
 mysql>

I tried repairing the table to no avail. What could help fix it?


texts: Does an index improve the querying performance in just two ways?

texts:
As far as I know, an index improves the querying performance in the following two ways:

The index only contains the values of the column that it was created for and pointers to the rows in the original table that each value points to (so this means there are fewer data to read from the disk when reading from the index compared to reading directly from the original table which could contain a lot of columns).
The values in the index are sorted.

But does an index improve the querying performance in other ways also? For example if we have an index for a name column, does the index contain pointers to the rows in the original table of each name, for example does an index contains something like this:

John (row4, row10, row12)
Robert (row9, row20)
Steve (row2, row6)


texts: Why would anyone ever disable `index_condition_pushdown` (ICP)?

texts:
I'm just wondering if there is every a valid reason to disable index_condition_pushdown?

  Index Condition Pushdown (ICP) is an optimization for the case where MySQL retrieves rows from a table using an index. Without ICP, the storage engine traverses the index to locate rows in the base table and returns them to the MySQL server which evaluates the WHERE condition for the rows. With ICP enabled, and if parts of the WHERE condition can be evaluated by using only columns from the index, the MySQL server pushes this part of the WHERE condition down to the storage engine. The storage engine then evaluates the pushed index condition by using the index entry and only if this is satisfied is the row read from the table. ICP can reduce the number of times the storage engine must access the base table and the number of times the MySQL server must access the storage engine.

So essentially if I have a table, people(zipcode,lastname,address) with all columns on the index this permits what other databases call Index-only scans, a query

SELECT * FROM people
  WHERE zipcode='95054'
  AND lastname LIKE '%etrunia%'
  AND address LIKE '%Main Street%';

can be satisfied without visiting the table.

How is it advantageous to visit the table in addition, or to not use the index at all -- the only two options I see that can result from disabling  index_condition_pushdown?


texts:
Imagine the situation. There are three tables,

Student table[PK Student ID]
Enrollment Table [PK EnrollID, FK Student ID]

Fees Paid [PK Fee ID, FK EnrollID, FK StudentID]

Edit: Fee_Paid Table references the PK of both ENROLL TABLE and STUDENT Table.
ENROLL Table References the PK of STUDENT Table.

Given this situation which I have, I was informed that student ID should not be a FK in the Fees table and it causes "Integrity Issues".

While It can be done that way (linking fees paid to student ID and not needing student id in Fees_Paid Table).

Are there any negative ramifications to be using the foreign keys the way that I am cause I honestly seen none. The integrity rule I'm aware of is not letting students be deleted when their ID exist in Fees table but any further clarification will be appreciated.


texts: Why SQL server does not have column-level locking?

texts:
As far as i know, the lowest level locking is row-level

However i don't see the reason why it can not have column level locking

Assume that 2 queries simultaneously updates 2 different columns.

So why it can not happen? Why each update has to lock at least row level?

This behavior tells me that i should split table into smaller tables so less row level locking would happen

Am i incorrect?


texts:
When I create a new user in MariaDB, I cannot login as that user.

First I login to the database as root doing mariadb in a bash terminal.

I then run the following commands to create a database and a user:

CREATE DATABASE mydb;
grant all privileges on mydb.* TO 'myuser'@'localhost' identified by 'password';
FLUSH PRIVILEGES;

When I then run SELECT user,host,plugin FROM mysql.user;, it prints the following:

+-----------+-----------+-------------+
| user      | host      | plugin      |
+-----------+-----------+-------------+
| root      | localhost | unix_socket |
| myuser    | localhost |             |
+-----------+-----------+-------------+

However, when I then try to login as that user doing mariadb -u myuser mydb -p, I have no luck.

ERROR 1045 (28000): Access denied for user 'myuser'@'localhost' (using password: YES)

I have no idea what is happening.

I am running the following MariaDB version:

mysqld  Ver 10.1.44-MariaDB-0ubuntu0.18.04.1 for debian-linux-gnu on x86_64 (Ubuntu 18.04)


texts: it’s safe to run mysqltuner on production server

texts: Can i run mysqltuner directly on a production server ? It’s will slowdown my server or install some packages ? My users will be affected ?


texts:
This is kind of the part 2 of Replicating a SQL Server database & Optimization that I started earlier this week, but I believe it owes to be a different questions now that I have a clear idea of what we are trying to achieve.

Currently we have one box running our application and database server. We about to increase our capacity to two servers. One on DC1 and the other on DC2. Both will have our application server and database running.

What we need:

Not have boxes sitting around doing nothing waiting for a failure.
Automatic fail over. We don't want to be running like headless chickens flipping switches and configuration settings when a database / box fails
Sensible fail-over times (e.g. a five minute fail over is not sustainable)

We can sustain some (sensible) permanent data loss in certain areas of our application. Anything up to a few seconds is fine. If its temporary loss e.g. pointing to an db image valid ten minutes ago is ok as long as the data recorded within those ten minutes make their way to their database at some point.

OPTION 1

Point app server to local db. If local db is down then point to remote db. e.g. App Server A points to DB A. App Server B points to DB B. If DB A goes offline then App Server A points to DB B. If Box A goes offline then Box B continues as it is.

Reasons we like this: Does really well on criteria A, B and C

OPTION 2

Application servers are pointed to one combined DB. This is the combination of DB in box A and DB in box B. No sweat if one database fails.

Reasons we like this: Does well on criteria A, B and C

But:

Disadvantage is that we get Application Server A pointing to box B so increased latency

OPTION 3

Application servers are pointed to one DB, say on box A. So application server A points to DB A and application server B is pointing to DB A. We use database B for read only reporting. On DB A failure applications are automatically switching and point to DB B which is now used for everything, not just reporting.

Reasons we like this: Still does well on criteria A, B and C

What I would like to know is how are the options above implemented with a SQL Server 2012 Enterprise. Mind that the two boxes are on different data centers but IF needed we can change that. Also, if there is a better option that we haven't thought about.

Databases image size is currently in excess of 150GB. There are some very frequently updated tables (the analytics tables are inserted sometimes 50-100 times a second).

Let me know what you think

Thanks,
Yannis


texts: How to get the SQL_ID that is blocking another SQL?

texts:
Well, I'm personalizing a useful script that I found and I need put in this script what is the sql_id of the SQL that is blocking another SQL.

Here is my script, if you try this, You never will get the sql_id of the blocker session, just the sql_id of the bloqued session.

col BLOCKER for a20;
col OBJ_BLOCKED for a20;
set linesize 3000;
col OWNER_OBJCT for a25;
set pagesize 0 embedded on;
select TO_CHAR(TRUNC(max(l2.ctime)/3600),'FM9900') || ':' ||
    TO_CHAR(TRUNC(MOD(max(l2.ctime),3600)/60),'FM00') || ':' ||
    TO_CHAR(MOD(max(l2.ctime),60),'FM00') AS "Tempo LOCK",
c.username "BLOCKER",c.sid "SID_BLOCKER",c.serial# "SERIAL#_BLOCKER",c.inst_id "ID_inst_BLOCKER",c.sql_id,
b.object_name "OBJ_BLOCKED",b.owner "OWNER_OBJCT",
d.username "SES_BLOCKED" ,d.sid "SID_SES_BLOCKED",d.serial# "SERIAL_SES_BLOCKED",d.inst_id "IDinst_SES_BLOCKED",d.sql_id from gv$locked_object a, dba_objects b, gv$session c, gv$session d,gv$lock l2 where a.object_id=b.object_id and
c.sid=a.session_id and l2.sid=c.sid and c.username is not null and c.sid in (select blocking_session from gv$session) and d.blocking_session is not null
group by c.username,c.sid,c.serial#,c.inst_id,b.object_name,b.object_type,b.owner,d.username,d.sid,d.serial#,d.inst_id,d.sql_id,c.sql_id
order by 1,3,4 asc
/

Can someone help me?


texts: What significance does an index_id < 256000 have?

texts: In a certain tutorial I read the author is filtering sys.indexes based on the predicate index_id < 256000. What does this accomplish?


texts: Would a set of non-clustered index work better than a clustered index for my large table?

texts:
I'm trying to create an index over a table with potentially 100 million rows. Right now I have the following columns:

name="date", type ="DATE"
name="account_id", type ="INT64"
name="group_id", type ="INT64"
name="run_id", type ="INT64"
name="page_id", type ="INT64"
name="page_name", type ="LONGVARCHAR"
name="countable", type ="INT64"
name="viewing_object_id", type ="INT64"
name="viewing_object_title", type ="LONGVARCHAR"

Where each unique group_id belongs to a single account_id (each account has multiple groups) and each unique run_id belongs to a single group_id (each group has multiple runs). In comparison, viewing_object_id can be associated with multiple page_id.

My most common query is going to be of the type:

SELECT
  MAX_BY(viewing_object_title, date) AS viewing_object_title,
  MAX_BY(page_name, date) AS page_name,
  SUM(countable) AS countable
FROM my_table
WHERE account_id = %d
  AND date >= DATE(%s)
  AND date <= DATE(%s)
GROUP BY
  viewing_object_id

But sometimes I also include AND page_id = %d. And other times I also filter on group_id and run_id.

WHERE account_id = %d
  AND group_id = %d
  AND run_id = %d

I'm thinking that I have a few options:

1) Use the following non-clustered indices without a clustered index (maintain heap structure).

[
  [account_id, viewing_object_id, date],
  [account_id, page_id, viewing_object_id, date],
  [account_id, group_id, run_id, viewing_object_id, date]
  [account_id, group_id, page_id, viewing_object_id, date]
]

I'm thinking that this would take up a ton of space. But surely it wouldn't be the same amount of space as having 500 million rows right? These indices match to all of the query types that I would want to run. And because data is added each day in a pipeline, I'm not that concerned about write performance.

2) Use the following clustered index: [account_id, date, page_id].

This lets me have really fast searches when I'm specifically loading everything associated with an account over a date range, including when I filter by the page_id. But it seems like if I were to use group_id or run_id I'd end up having to do a full table scan.

Not really sure how to make this trade-off or if I'm thinking of this the right way. Are one of these options clearly wrong for how I'd want to index my data?


texts: Filtering One column into Different Columns in one statement

texts:
I need to

SELECT amount FROM table WHERE type='1' AND type='2' AND type'3'

Depending on the types, I need them to be pulled into different columns.

For example,

IF type=1 THEN amount1
IF type=2 THEN amount2
IF type=3 THEN amount3.

Is it possible to this in one statement?


texts: How to get list of dates between From Date and To Date in MySQL

texts:
I have a table Emp_UnAvailablity_ID`, `PersonID`, `Is_UnAvailable`, `From_Date`, `To_Date` FROM `emp_unavailablity. I want to get list of all dates between From_Date and To_Date. I have tried this query SELECT date(From_Date) AS F, DATEDIFF(date(To_Date), date(From_Date)) AS D FROM emp_unavailablity its output is in the attached image

Here is my table data

I have tried some php code to get list of dates but not worked for me.

$connection = Yii::app()->db;
    $sql = "
        SELECT date(From_Date) AS F, DATEDIFF(date(To_Date), date(From_Date)) AS D FROM emp_unavailablity ";
    $command = $connection->createCommand($sql);
    $dataReader = $command->queryAll();

    $newValues = array();
    foreach($dataReader as $key => $value)
    {
        $newValues[$key] = $value;
        if($key == 'F')
        {
            for ($i=0; $i < $key['D']; $i++) {
                $xx = $key['F'] + $i;     //f + i
                $newValues = $xx;
            }
        }
    }
    print_r($newValues);

Please help me to solve this problem. Thanks


texts: In Google App Engine, what is the most effective many-to-many join model?

texts:
The BigTable design rejects many of the philosophies of standard relational models, explicitly preferring denormalization to a big host of tiny tables.

One of the larger areas where this is a problem is in the modelling of many to many joins.

One way to model these joins is to violate first normal form, and put all interesting data in a db.ListProperty(). While this has the ability to be searchable from a query, I have not yet explored the performance implications of searching a list versus pulling another table.

As joins are not possible, it is possible to link tables through RelationshipProperties. Therefore, with enough effort, the standard intersection table (a table with a joint primary key which references both parent tables) can be created. Has anyone explored the performance hits of the various implementations?

-Edit-

While the List of Keys suggested in the documentation is indeed one way to do it, I'm interested in the performance and anomaly rates of that and other implementations. Is there utility in creating mutual lists of keys? Is the effort involved in the repeated gets worth the price? Is there a better way to do it?


texts: Oracle 11g hunting down the deadlock, maybe foreign key?

texts:
I am trying to understand why a deadlock occurs in my database. The trace tells me that two updates on the same table are locking. In table is the first table to be written to in an transaction, only some read occur before. The updates/inserts are done in a batch manner. The key, which is also used to identify the row which is to be updated, is indexed with a b+-tree. This key also acts in other tables as a foreign key. In these other tables the foreign key is allowed to be null.

The application disallows parallel transactions containing possible conflicts.

Here is the head of the trace file:

Deadlock graph:
    ---------Blocker(s)--------  ---------Waiter(s)---------
Resource Name          process session holds waits  process session holds waits
TX-134b0012-3201376f        65     185     X             33     392           S
TX-13495007-50f092d1        33     392     X             65     185           S

session 185: DID 0001-0012-04533045 session 392: DID 0001-0023-03401G5A
session 392: DID 0001-0023-03401G5A session 185: DID 0001-0012-04533045

Rows waited on:
Session 588: no row
Session 497: no row

Can the insert of a null value in a table referencing the key as a foreign key cause some kind of table scan (don't think so)? Can a batch insert/update maybe cause bigger branches of an index to be locked? Maybe T1 gets some left branch of the tree and T2 a right branch, and now T1 wants to to insert/update a value in the right branch, but the right branch is still locked by T2 and T" then requests something from the left side? I have got no idea if and how multi granularity locking is implemented in Oracle, but it would be nice if someone could rule this out.

Is there any possibility that Oracle allows deadlocks even if the transactions are 100% logically disjunct?

What could I be looking for? Is it some problem accessing the index?

P.S.: read committed is set.


texts: ALTER DATABASE SET ARITHABORT not taking effect

texts:
I've executed the following query as described here:

ALTER DATABASE [{database_name}] SET ARITHABORT ON WITH NO_WAIT;

SELECT DATABASEPROPERTYEX('{database_name}', 'IsArithmeticAbortEnabled'); returns 1, but sys.dm_exec_sessions shows arithabort set to 0 for all relevant connections. Tracing in Profiler during connection establishment - audit login rows show set arithabort off with no subsequent sets and SELECT @@OPTIONS returns 5432 (ARITHABORT off).

Why isn't this setting taking effect?

Compatibility level is 80. Seeing this across both ODBC and SqlClient connections.


texts: Sudden increase in Page Writes/s after patching / rebooting

texts:
I was given a SQL Server 2008R2 to run, but my DBA skills are rusty after some years away from the day-to-day ops, so please bear with my (somewhat) silly questions.

This SQL Server is running in Azure with 14GB RAM, 8 cores and 3 sets of striped disks (two stripes per set).

Whenever I have to apply an Update and reboot the machine, Users experience a MAJOR system slowdown during the first 20/30 minutes of the next business day.

In ALL occasions I was online inspecting the behaviour, the only significant change I could detect was a sudden increase in Page Writes/s.  Further investigation pointed to a sudden increase in TEMPDB i/o ops.  I have then tried to increase TEMPDB's base size, but this still happens.  Event Viewer logs report nothing.  It's like SQL Server needs to "re-tune" itself under load after reboot.

As I said, the slowdown lasts for 30 minutes tops, but it is obviously annoying and I can't figure out a way to prevent it.

Any ideas?

Thanks in advance,


texts: Is there any view or table to show how long a session have been in Idle state in Oracle?

texts:
As my question title, Is there any view or table to show how long a session have been in IDLE state in Oracle ?

So that I can monitor how long this session have been in IDLE state.

It doesn't mean how to set session expired-time.