texts: Simple query with date type requires explicit conversion to_date of date

texts:
My question is, I have a table which contains a column called myDateColumn (for example) which is of the type date:

I can confirm desc myTable contains this line:

myDateColumn  NOT NULL     DATE

However, when I try to select all the data that is between certain dates, for example:

select * from myTable
where myDateColumn
between to_date('13-FEB-11', 'DD-MON-YY') AND TO_DATE('15-FEB-11', 'DD-MON-YY');

I get null results no matter what, even though I am sure some data exists within this specific date range.

The fix for it I discovered was to explicitly convert myDateColumn to a date:

select myDateColumn from myTable
where to_date(myDateColumn)
between to_date('13-FEB-11', 'DD-MON-YY') AND TO_DATE('15-FEB-11', 'DD-MON-YY');

which returns the correct results, but it is already a date type!  Why do I need to explicitly convert a date column to date before doing the between logical comparison?

The reason why I ask is because this select/ where will be part of a stored procedure selecting data within a specific date range to be run on a big table partitioned by date, with more than ten million rows, and if I can avoid explictly converting each myDateColumn of each row, then I might be able to save some query time, in theory.

Or is there a more correct way to run this comparison to select data within a date range?

Thank you.

Update:  Leigh suggested in the answer to diagnose the table for something inconsistent with his query.  The result of his query was:

myDateColumn    TO_DATE(myDateColumn)   TO_CHAR(myDateColumn,'DD-MON-YYHH.MI.SSPM') TO_CHAR(myDateColumn,'YYYY')
14-Feb-11   14-Feb-11   14-FEB-11 12.00.00 AM   0011
14-Feb-11   14-Feb-11   14-FEB-11 12.00.00 AM   0011
14-Feb-11   14-Feb-11   14-FEB-11 12.00.00 AM   0011
14-Feb-11   14-Feb-11   14-FEB-11 12.00.00 AM   0011
14-Feb-11   14-Feb-11   14-FEB-11 12.00.00 AM   0011

Indicating that the reason the between clause was not working, is because none of the data would fall in the query interval, since the year was 0011, during the Roman Empire :)

Thanks guys.


texts: Identifying the normal form that a certain relation complies with

texts:
I have a little problem with a normalization exercise. I have the following relation:

R = {L,K,D,S,O,W}

which presents the functional dependencies (FD) shown below:

F = { {D,S,K} -> {O},
      {O} -> {K},
      {O,L} -> {W}
      {W,L,K} -> {O},
      {D,S,W} -> {L}}

I have to determine what normal form (NF) is satisfies. So I started from determining the candidate keys; they are:

{D,S,O,L}

{D,S,L,K}

{D,S,K,W}

{D,S,O,W}

Then, I worked from-top-to-bottom (not sure if it is good strategy).

This relation isn't in BCNF because the left side of every FD does not contain key(s) e.g. {O} -> {K}
My definition for 3NF was that, for every nontrivial, simple FD, the left side contains a key or the right side is part of a key (or it is a key). Everything matches and I thought that the relation R is in 3NF.

Then I look on the conditions to be in 2NF and saw that:

This relation contains partial dependency e.g. {O} -> {K}
This relation contains transitive dependency.

Transitive dependency is for every FD X -> A, X is a subset or superset of the key (is that right, but if X is a subset of the key then this is a partial dependecy)?

This dependency doesn't work for me {W,L,K} -> {O} - this is transitive. So, is relation R in 1NF? This is something weird, either I have a bad definition of 3NF or this relation is in 3NF... but it isn't in 2NF, which is impossible. What's wrong?


texts: How do I determine if membership in a DB fixed role is by direct user memership or through membership in a custom DB role?

texts:
I'm cataloging memberships of DB users in the various fixed DB roles so that I can create custom roles by business function and grant permissions through these custom roles only.

When using SSMS to review the membership of a fixed role (i.e., db_datareader), I'm seeing both the custom role I made a member of db_datareader as well as the users that I member of the custom role only.

Is SSMS is performing nested lookups that are not performed by the sp_helprolemember stored procedure? In Remarks from this MSDN article, the stored procedure doesn't perform the nested lookups but SSMS appears to do so without a visual cue to differentiate direct vs. indirect grants.


texts: How query execution time and concurrent users affect DTU

texts:
I am using Azure SQL database. In Azure, pricing is calculated on the basics of DTU (Database Throughput Unit). There are lots of articles and QA regarding this but I failed to find the Steps for calculation of DTU or what are the factors affecting DTU. If I know the calculation steps, I can make changes in my codes or queries so that I can utilize the resource in a good manner.

Whether the following factors are affecting DTU?

Volume of data in the DB
Volume of data that we are fetching;
Type of joins?
Concurrent users/Hits

Azure Pricing Table:


texts: How to log error details when using using try/catch for dynamic SQL backup commands

texts:
When issuing a backup command within a stored procedure that uses a try catch and dynamic sql, the error messages are very general when compared to running the backup command directly.

Try/Catch within SP:

    begin try
        execute sp_executesql @sql;  -- a backup command
    end try
    begin catch
        print ERROR_MESSAGE();  -- save to log, etc.
    end catch

Results in

  50000:usp_Backup:117:  BACKUP DATABASE is terminating abnormally.

wheareas issuing the raw command:

    backup DATABASE someDb to disk...

Results in better details:

  Lookup Error - SQL Server Database Error: A nonrecoverable I/O error
    occurred on file "H:\FolderName\Filename.bak:" 112(There is not enough
    space on the disk.).

Is there a way to catch these details into variables within the stored procedure (to log, pass back to caller, for retry logic)?  It seems the details are coming through on the message channel but I would like them available within the SP.


texts:
Does the version of MySql Library have the same impact as the version of MySql server when it comes to other software requirements?

Example: I run Magento 1.9.3, which specifies MySQL 5.6 as a minimum requirement.
If my web server installation is running MySql Library of an older version (5.1.73), what problems will this cause, if any?

I can't seem to find any definitive answer out there regarding this detail?


texts: moving database from old disk to new disk

texts: I need to move a database from an old disk which is no longer bootable to a new bootable disk. I have been able to mount the old disk. Now I need to move the database from there to a new disk. Can this be done? Where do I start and what do I need to move?


texts: How to calculate the final value of a row based on another table with costs in PostgreSQL


texts:
How can I force SSMS to forget the recently used logins?

Of course I use "SQL Server Authentication" with Login/Password fields.

(It get's annoying, as everytime I open SSMS it shows the "Connect to Server"-Dialog, with a login preselected that is not valid anymore.)

Didn't find any option, and no info here.

Thank you.


texts: How to get the table change script for SQL Server using SSMS 2017?

texts:
I'm having frequent table changes in my database, I'm using SSMS 2017, is there any easy mechanism for generating scripts for the changes made to the table. Or Are there any tools for managing this kind of stuff.

I have tried generating the scripts for table changes in the SSMS itself in the designer section, but it will create separate files for each save click after the structure change. I think there is a better mechanism for this. Writing scripts for each structure change took lots of time.


texts:
I am really confused as to how to digest the fact that a transaction is atomic. If a transaction is set of "queries" then how will it be atomic. I am relating the word "queries" to a general SQL query. Therefore a transaction then becomes nothing but a set of SQL queries executed at the same time. But the fact that each query multiple operations, I am really not getting as to how the previously executed queries(in the same set) roll back if an error occur in a later query. What am I missing here?
Thank you!


texts: "Lost connection to MySQL server during query" error

texts:
I have MySQL 5.5.20 and this table:

mysql> desc country;
+----------------+-------------+------+-----+---------+-------+
| Field          | Type        | Null | Key | Default | Extra |
+----------------+-------------+------+-----+---------+-------+
| id             | int(255)    | NO   | PRI | NULL    |       |
| iso            | varchar(2)  | NO   |     | NULL    |       |
| name           | varchar(80) | NO   |     | NULL    |       |
| printable_name | varchar(80) | NO   |     | NULL    |       |
| iso3           | varchar(3)  | YES  |     | NULL    |       |
| numcode        | smallint(6) | YES  |     | NULL    |       |
+----------------+-------------+------+-----+---------+-------+

If I run a query like this

SELECT country.ID, country.ISO, country.NAME,
       country.PRINTABLE_NAME, country.ISO3, country.NUMCODE
FROM country;

It returns:

ERROR 2013 (HY000): Lost connection to MySQL server during query

If I change the order of the columns (ISO3 before PRINTABLE_NAME for example) like this:

SELECT country.ID, country.ISO, country.NAME,
       country.PRINTABLE_NAME,  country.NUMCODE, country.ISO3
FROM country;

It works fine!

Or if I rewrite the query using lower-case letters for columns, it works as well.

This issue appears from time to time (about once a month) and the only solution to fix it is to restart MySQL!
i have checked error log and nothing added to error log upon this error!!
I am really confused and don't know why this happens! Any ideas on how this could be fixed?


texts: Significance of ".WORLD" on end of Oracle connection

texts: What is the significance of having ".WORLD" on the end of an Oracle connection name?  I've found things to still connect without that suffix.  I've also noticed that both the client tnsnames.ora file and the server tnsnames.ora file must both have this suffix (or none at all) in order to communicate.


texts: Finding Exclusive Pairings in Relational Algebra

texts:
classId | studentId
  0          0
  0          1
  0          2
  1          0
  1          1
  1          2
  2          2

How do you find the pairs of students (s1, s2) that ONLY have classes with each other? So if student1 has a class, student2 HAS to be in that class to and vice versa.

I know you have to pair them together by classId but how do you eliminate false positives e.g. studentId =2 in this case?


texts: MySQL tables/databases into different disks

texts:
I am aware that there is a possibility to have a single database server with many tables/databases, and you can separate some of those tables/databases into separate disks.
Lets say you have 5 tables/databases, and 5 drives. So you attach each table/database for a different drive. Thus increasing IO and everything else.

What is that technique called ?

I think if you try separating databases, you just mount different drive to different folder for different database, but what if you want to separate tables into separate drives (each table in different disk)


texts: Is it ever harmful to set a database owner in SQL Server?

texts:
There are a bunch of databases on one of our SQL servers that have no owner.  Generally speaking, is it harmful to give them one?  For example:

USE dbName;
GO

EXEC sp_changedbowner 'sa';
GO

I know sa may not be the best choice, but it is just an example.  My primary concern is whether or not creating an owner when there is none may cause issues with software that can currently connect OK.


texts: How to temporarily disable foreign keys in Amazon RDS PostgreSQL?

texts:
I'm migrating existing test environment to Amazon RDS PostgreSQL. The test framework has a feature of reloading data in certain tables to an earlier state. For this it disables foreign keys, deletes existing data, loads save state and enables foreign keys again.

Currently, the test framework disables foreign keys by disabling all triggers (of course, this requires superuser):

alter table tablename disable trigger all;

On RDS, this fails with:

  ERROR:  permission denied: "RI_ConstraintTrigger_a_20164" is a system trigger

How can I temporarily disable foreign keys in Amazon RDS PostgreSQL?

Note: similar question has already been asked (PostgreSQL on RDS: How to bulk import data with FK constraints?) but it was specifically about offline import and the solution is specific to offline import too.


texts:
I'm looking through the queries being executed by a third party application. The application was flagged as being slow by some of our end-users.

It seems to INSERT records into a HEAP, then DELETE them again after the session ends (without any selects happening on them?)
But at the end of each INSERT statement there is a SELECT 0.

Am I seeing some kind of artifact from the query plan cache? Is it possible there is some parameter that's missing? All other parameters are clearly indicated as for example @P1.

There are 350 INSERTactions per minute and a DELETE of everything in the table ever hour. Which seems to be an OK case to use a HEAP, however if this SELECT 0 is actually selecting something from this heap, I'm wondering if I should ad a clustered index.

Update: Added a screenshot to show the select:


texts: Forcing correct execution plan when passing a table UDT to a table function

texts:
I have a scalar function that returns a big XML, created over a bunch of invoices.

The exact list of invoices to feed to the function can be calculated using several different ways, but the function is same every time. For this reason, I declared a user-defined table type to contain primary keys from the eInvoice.Header table and pass it to the function. This way I can have several different functions to decide which invoices to process, and just one function that actually generates XML:

create function eInvoice.GetRelevantLinesInOneWay()
returns table ...

create function eInvoice.GetRelevantLinesInAnotherWay()
returns table ...

create function eInvoice.GetXML(@lines eInvoice.InvoicePrimaryKeys readonly)
returns xml
as
begin
    declare @x xml;

    with xmlnamespaces(N'important namespace' as pro)
    select @x = (
        select
            ...
        from
            eInvoice.Header h
            inner join @lines l on h.ST_PRIMARY = l.invoice_row_id
        for xml path(N'pro:Import'), type
    );

    return @x;
end;

Unfortunately this setup has proven to be very fragile.

Typically @lines contains about 150 rows (out of about 1m in eInvoce.Header). The correct execution plan is to use index seek on ST_PRIMARY, and that is what always happens when I execute the body of eInvoice.GetXML as an ad-hoc query.

However when I store it as a function, it works as expected for some little time, then something happens (too many rows in @lines, like about 300?), and it decides to change the execution plan to a full scan on eInvoice.Header and to keep it that way.

With the seek plan the function executes instantly, with the scan plan I don't know how much time it takes, I waited for 30 minutes and cancelled it.

I've tried various ways of forcing the seek plan on the function.

Adding with (forceseek) after eInvoce.Header seems to work when I click "Display estimated execution plan," but when I actually execute it, the hint is ignored and a scan is performed.
Adding option (recompile) inside the function or outside, in the calling code, does not seem to have an effect.
Capturing the execution plan and hardcoding it inside the function with option (use plan N'<plan>') does not work because @lines contains a primary key on its only column, and the name of that index is different for each instance of @lines, but the execution plan has to refer to the index by fixed name.
I tried deleting cached execution plans and recompiling the function several times. It sometimes helps for a very limited time and sometimes has no effect.

Is there a way to force the index seek?


texts: Insert image with relative path to the postgres table

texts:
I have created following table in postgres:

CREATE TABLE public.test
(
    id bigint NOT NULL DEFAULT nextval('test_id_seq'::regclass),
    file_content oid,
    name bigint NOT NULL,
    CONSTRAINT test_pkey PRIMARY KEY (id)
)
WITH (
    OIDS = FALSE
)
TABLESPACE pg_default;

ALTER TABLE public.test
    OWNER to postgres;

To put some example data via sql script:

INSERT INTO public.test(id, file_content, name) VALUES (1, lo_import('test.jpg'), 14);

It works fine when I am putting full path to binary file, like:
C:\temp\myTest\test.jpg
However if I am putting just filename (sql script exists in the same directory) then I am getting error that file does not exists.

Do you know if it is possible to execute lo_import with relative path?


texts: PostgreSQL logical replication using pglogical

texts:
I am testing logical replication using Pglogical with Postgresql v9.5.

Simple configuration seems OK.
It means:

create pglogical extension in the two nodes
create a set and add tables from public schema into this set,
create a node and create a subscription in the other server.

But, subscription to a new replication_set containing tables from a specific schema does not work.

This command passed (in the provider node):

select pglogical.replication_set_add_all_tables(
set_name := 'new_replication_set',
schema_names := '{myschema}',
synchronize_data := 'true'
);

This command passed (in the subscriber node):

select pglogical.create_subscription(
subscription_name := 'subscription',
replication_sets := array['new_replication_set'],
provider_dsn := 'host=10.20.30.40 port=6432 dbname=production user=pglogical_prod',
synchronize_data := 'true'
);

But tables in the subscriber node are empty.

I missed something?
Thanks and regards.

Additional information:

select pglogical.alter_subscription_synchronize(...) and select pglogical.alter_subscription_resynchronize_table(...) commands were sent,
Tables in the subscriber node are in a specific schema too,
select * from pglogical.queue; request is not empty.

Hereunder the log messages taken from the subscriber node :

I set log_min_message = debug5 in the subscriber node.

And hereunder the error message :

792 < 2018-03-14 11:38:56.449 CET >LOG:  starting apply for subscription subscription
793 < 2018-03-14 11:38:56.449 CET >DEBUG:  StartTransaction
794 < 2018-03-14 11:38:56.449 CET >DEBUG:  name: unnamed; blockState:       DEFAULT; state: INPROGR, xid/subid/cid: 0/1    /0, nestlvl: 1, children:
795 < 2018-03-14 11:38:56.449 CET >DEBUG:  CommitTransaction
796 < 2018-03-14 11:38:56.449 CET >DEBUG:  name: unnamed; blockState:       STARTED; state: INPROGR, xid/subid/cid: 0/1    /0, nestlvl: 1, children:
797 < 2018-03-14 11:38:56.449 CET >ERROR:  subscriber subscription initialization failed during nonrecoverable step (d)    , please try the setup again
798 < 2018-03-14 11:38:56.449 CET >DEBUG:  shmem_exit(1): 2 before_shmem_exit callbacks to make
799 < 2018-03-14 11:38:56.449 CET >LOG:  apply worker [16870] at slot 1 generation 4 exiting with error
800 < 2018-03-14 11:38:56.449 CET >DEBUG:  shmem_exit(1): 6 on_shmem_exit callbacks to make
801 < 2018-03-14 11:38:56.449 CET >DEBUG:  proc_exit(1): 2 callbacks to make
802 < 2018-03-14 11:38:56.449 CET >DEBUG:  exit(1)
803 < 2018-03-14 11:38:56.449 CET >DEBUG:  shmem_exit(-1): 0 before_shmem_exit callbacks to make
804 < 2018-03-14 11:38:56.449 CET >DEBUG:  shmem_exit(-1): 0 on_shmem_exit callbacks to make
805 < 2018-03-14 11:38:56.449 CET >DEBUG:  proc_exit(-1): 0 callbacks to make
806 < 2018-03-14 11:38:56.450 CET >DEBUG:  reaping dead processes
807 < 2018-03-14 11:38:56.450 CET >LOG:  worker process: pglogical apply 16385:2875150205 (PID 16870) exited with exit     code 1

Thanks and Regards.


texts: Create an incremental number with year and month without updating the entire table using a trigger

texts:
My situation and code

I'll add a number to my MS SQL Server table. It must be an unique number beginning with the year and month of the time when it's created, followed by an incremental index number. Something like this 2019.08.0001, 2019.08.0002, 2019.08.0003 etc. The ID I'll keep as internal PK for references from other tables.

For this I use this code.

create table Things (
    ID int primary key identity not null,
    Number nvarchar(20),
    Text nvarchar(max),
    CreationDate datetime not null
);

create trigger UpdateThingsNumberTrigger on Things
after insert
as
begin

    declare @month nvarchar(2);
    select @month = cast(month(getdate()) as nvarchar(2));

    declare @code nvarchar(15);
    select @code = cast(year(getdate()) as nvarchar(4)) +
        '.' +
        replicate('0', 2 - len(@month)) +
        @month +
        '.';

    declare @max nvarchar(20);
    select @max = t.ID
    from Things t
    where ID like @code + '%';

    with CTE_UPD as
    (
        select
            replicate('0',
                4 -
                len(cast(coalesce(cast(right(@max, 4) as int), 0) + row_number() over (order by ins.ID) as nvarchar(4)))) +
                cast(coalesce(cast(right(@max, 4) as int), 0) + row_number() over (order by ins.ID) as nvarchar(4)) as NextNo,
            ID
        from Things ins
    )
    update Things
    set Number = @code + NextNo
    from Things t inner join CTE_UPD ins on ins.ID = t.ID;

end;

insert into Things (Text, CreationDate)
values ('A', current_timestamp), ('B', current_timestamp), ('C', current_timestamp);

select *
from Things;

-- Output:
-- ID | Number       | Text | CreationDate
-- -- | ------------ | ---- | ----------------------
-- 1  | 2019.08.0001 | A    | 2019-08-23 08:54:15.157
-- 2  | 2019.08.0002 | B    | 2019-08-23 08:54:15.157
-- 3  | 2019.08.0003 | C    | 2019-08-23 08:54:15.157

But my trigger has a major logical flaw that has multiple effects… It never references the inserted virtual table. That means I'm updating the entire table every time when I insert a new row.

Effects of the logical flaw

When deleting a thing and inserting a new one

When I delete the row with ID 2, then the ID's after it, will change too.

delete Things
where ID = 2;

insert into Things (Text, CreationDate)
values ('D', current_timestamp);

select *
from Things;

-- Output:
-- ID | Number       | Text | CreationDate
-- -- | ------------ | ---- | -----------------------
-- 1  | 2019.08.0001 | A    | 2019-08-23 08:54:15.157
-- 3  | 2019.08.0002 | C    | 2019-08-23 08:54:15.157
-- 4  | 2019.08.0003 | D    | 2019-08-23 09:31:27.568

This isn't what I want to have. When ID 2 is removed, and a 4th is added, the numbers of things after ID 2 may not be changed. So the 4th thing must have number 2019.08.0004. Number 2019.08.0002 must be skipped. Like on table below.

ID | Number       | Text | CreationDate
-- | ------------ | ---- | -----------------------
1  | 2019.08.0001 | A    | 2019-08-23 08:54:15.157
3  | 2019.08.0003 | C    | 2019-08-23 08:54:15.157
4  | 2019.08.0004 | D    | 2019-08-23 09:31:27.568

When it's another month

Also when it's another month, all the things will get an other number when inserting a new thing. Then, the output will be this, the expected results on the last column:

ID | Number       | Text | CreationDate            | ExpectedNumber
-- | ------------ | ---- | ----------------------- | --------------
1  | 2019.09.0001 | A    | 2019-08-23 08:54:15.157 | 2019.08.0001
3  | 2019.09.0002 | C    | 2019-08-23 08:54:15.157 | 2019.08.0003
4  | 2019.09.0003 | D    | 2019-08-23 09:31:27.568 | 2019.08.0004
5  | 2019.09.0004 | E    | 2019-09-01 10:34:54.059 | 2019.09.0001

Question

How could I solve the logical flaw inside my trigger so that it has no effect to the existing things in my table?

  Note: The flaw was shown on this question for Stack Overflow.


texts: What is the purpose of this Uniq1002 column in this index scan?

texts:
Take the following repro:

USE tempdb;

IF OBJECT_ID(N'dbo.t', N'U') IS NOT NULL
DROP TABLE dbo.t
GO
CREATE TABLE dbo.t
(
    id int NOT NULL
        PRIMARY KEY
        NONCLUSTERED
        IDENTITY(1,1)
    , col1 datetime NOT NULL
    , col2 varchar(800) NOT NULL
    , col3 tinyint NULL
    , col4 sysname NULL
);

INSERT INTO dbo.t (
      col1
    , col2
    , col3
    , col4
    )
SELECT TOP(100000)
      CONVERT(datetime,
         DATEADD(DAY, CONVERT(int, CRYPT_GEN_RANDOM(1)), '2000-01-01 00:00:00'))
    , replicate('A', 800)
    , sc2.bitpos
    , CONVERT(sysname, CHAR(65 + CRYPT_GEN_RANDOM(1) % 26)
        + CHAR(65 + CRYPT_GEN_RANDOM(1) % 26)
        + CHAR(65 + CRYPT_GEN_RANDOM(1) % 26))
FROM sys.syscolumns sc
    CROSS JOIN sys.syscolumns sc2;

Here I'm adding a clustered index onto a set of columns that are not unique, and typical single-column non-clustered index:

CREATE CLUSTERED INDEX t_cx
ON dbo.t (col1, col2, col3);

CREATE INDEX t_c1 ON dbo.t(col4);

This query forces SQL Server to do a lookup into the clustered index.  Please forgive the use of the index hint, it was the quickest way to get the repro:

SELECT id
    , col1
    , col2
    , col3
FROM dbo.t aad WITH (INDEX = t_c1)
WHERE col4 = N'JSB'
    AND col1 > N'2019-05-30 00:00:00';

The actual query plan shows a non-existent column in the Output List for the nonclustered index scan:

Ostensibly, this represents the uniqifier used in the non-unique clustered index.  Is that the case?  Is a column named like that always the clustered index uniqifier?


texts: Why number of matches in fulltext search get less when we put less strings?

texts:
SELECT * FROM tableauxiliary WHERE MATCH (FullTextSearch)
AGAINST ('resta*' IN NATURAL LANGUAGE MODE)

show 5 results

SELECT * FROM tableauxiliary WHERE MATCH (FullTextSearch)
AGAINST ('restaurant*' IN NATURAL LANGUAGE MODE)

shows 56k

What am I missing? How can something contains restaurant without containing resta

I am using mysql and MyISAM


texts: Slow connection to SQL Server both locally and by client

texts:
Yesterday I was experiencing extremely slow connectivity to the SQL Server with connections via management studio taking 5+ seconds where normally connecting to the server occurs within seconds. This issue both happened when connecting to the SQL Server on the actual server VM and then also from client pcs. Executing requests on their own seemed to be fine once connected, we just appeared to have issues connecting to the SQL Server it self.

CPU Utilisation had spikes but averaged around 75% with memory also around the 85 % mark suggesting resources were available for clients to connect.

Given that we are not experiencing the issues right now, is there any way I can troubleshoot what was causing it and if not and should we experience the issues again, is there anything I should look for in particular with Extended Events when troubleshooting?

Thanks


texts: Index usage and update statistics do not correspond?

texts:
In order to discover unused and wrong indexes in sql server I went through the following article:
https://www.mssqltips.com/sqlservertutorial/256/discovering-unused-indexes/
It gives two queries that deliver index information:
SELECT OBJECT_NAME(A.[OBJECT_ID]) AS [OBJECT NAME],
   I.[NAME] AS [INDEX NAME],
   A.LEAF_INSERT_COUNT,
   A.LEAF_UPDATE_COUNT,
   A.LEAF_DELETE_COUNT
FROM   SYS.DM_DB_INDEX_OPERATIONAL_STATS (db_id(),NULL,NULL,NULL ) A
   INNER JOIN SYS.INDEXES AS I
     ON I.[OBJECT_ID] = A.[OBJECT_ID]
        AND I.INDEX_ID = A.INDEX_ID
WHERE  OBJECTPROPERTY(A.[OBJECT_ID],'IsUserTable') = 1

SELECT OBJECT_NAME(S.[OBJECT_ID]) AS [OBJECT NAME],
   I.[NAME] AS [INDEX NAME],
   USER_SEEKS,
   USER_SCANS,
   USER_LOOKUPS,
   USER_UPDATES
FROM   SYS.DM_DB_INDEX_USAGE_STATS AS S
   INNER JOIN SYS.INDEXES AS I ON I.[OBJECT_ID] = S.[OBJECT_ID]
AND I.INDEX_ID = S.INDEX_ID
WHERE  OBJECTPROPERTY(S.[OBJECT_ID],'IsUserTable') = 1
   AND S.database_id = DB_ID()

Trying the queries my suspect was confirmed that several existing indexes are simply useless.
The article mentions that the results of query #1 shound correspond with results of query #2:
"The updates refers to how many times the index was updated due to data changes which should correspond to the first query above."
So LEAVE(insert/update/delete) Counts sould be = USER_Updates.
In my case they do not correspond at all. e.g:

A.LEAF_INSERT_COUNT=26219
A.LEAF_UPDATE_COUNT=0
A.LEAF_DELETE_COUNT=0

vs.

USER_UPDATES =1.550.063
So my question is, what could possibly cause my data to dis-correspond that much?


texts: Can I disable the redo log for a DELETE query or a whole transaction?

texts:
I'm working on an existing database, whose structure I can't change. I need to clear (empty) several InnoDB tables (that I can't TRUNCATE due to references in other tables' FK constraints), but I have no interest in any recovery - i.e. if a crash occurs during those operations, I'll clear those tables again on the first occasion.

Can I tell MySQL to not create the redo log about those queries?

I am concerned about speed, as the first thing that can be noted is the time the operation takes. But, space too, I guess. Tables can store 20k-1M rows depending on the specific installation (same schema structure, different usages). I am operating on a bunch of table whose data are related (i.e. products, products_images, products_votes, ...), so FK are not an issue because all data will be deleted consistently without the need of any additional check.


texts:
First of all sorry about my english, and most importantly, my database ignorance, I'll do my best ;)

Until now, the scenario that I have is as follows:

In a server under our LAN, we have a database in a SQL Server 2008 R2 instance that is replicating as a publisher in a transactional replication with a DB (SQL Server 2008 R2 too) subscriber under a client LAN, both of them communicating through internet without using any VPN

Today the world turned black... at least for me, I'm on the developer side and this kind of requirements makes me spend a lot of time to get little progress: The client requested that we set up bidirectional transactional replication.

I know the client doesn't want a bidi-trans-replication exactly; what they really want is to interact with data that we have to see like they see ours via replication, and here it's where my doubts appears:

Is there other solution to this scenario than bidi-trans-replication?, I read something about mixed replications, but I don't know if this is better or worst :S
In case of bidi-trans-replication, does the client need to keep open ports in order to maintain the bidi-trans-replication? Currently we distribute the snapshot via FTP. I understand that we have to keep ports 21 and 1433 opened, but, does the client needs the 1433 port opened too?


texts: SQL Loop through Inserts and Update table

texts:
I have a table (tblStagingHeader) containing the below two columns and example data.  It is a record of SQL table names that have been created as part of an SSIS\ETL load.

StageTable       Inserted
-------------------------
tblStage1          0
tblStage2          0
tblStage3          0

Using the below SQL task, I dynamically insert data from the staging tables (e.g. tblStage1 etc) into real tables.

My question is - after each successful insert, how do I loop through the tblStagingHeader and change the 'Inserted' field from a '0' to a '1' for each corresponding StageTable?

--NOTE: the actual EXECUTE sp_executesql @cmd is commented out
Declare @Cmd nvarchar(max)
;with DistinctTables as
(
select distinct [RealTable], [StageTble] from MappingTable
),
InsertCommands as
(
select *,
'insert into ' + [RealTable] + '(' +
STUFF((
        SELECT ',' + [RealCol]
        FROM MappingTable t1
        where t1.RealTable = drt.RealTable
        ORDER BY OrdinalPosition
        FOR XML PATH(''), TYPE).value('.', 'VARCHAR(MAX)'),1,1,'')
+ ')' +
' select ' +
STUFF((
        SELECT ',' + [StageColumn]
        FROM MappingTable t1
        where t1.RealTable = drt.RealTable
        ORDER BY OrdinalPosition
        FOR XML PATH(''), TYPE).value('.', 'VARCHAR(MAX)'),1,1,'')
+ ' from ' + [StageTble]  as InsertCommand
 from DistinctTables drt
)
select @cmd =
STUFF((
        SELECT ';' + char(10) + [InsertCommand]
        FROM InsertCommands
        FOR XML PATH(''), TYPE).value('.', 'VARCHAR(MAX)'),1,1,'')
+';' from InsertCommands ic

print @cmd
--EXECUTE sp_executesql @cmd

I cannot take all credit - this is also linked to post:
SQL Insert via mappings table


texts: I have an Oracle dump file which I need to import but do not know what is its tablespace name. How can I get it from the dump file? I know that it is possible to get many information using impdp MASTER_ONLY flag but can't see there any information about dump's tablespace.


texts: I am trying to create a script which creates a postgres-fdw connection between two postgres 9.4 databases. The script (which is checked in under version control), has been relying on pgpass to do other things. Is there any option I can use to request that the password be looked up in pgpass? ... in general, where is the documentation on what options are available for CREATE USER MAPPING? the reference just says that options depend on the server.


texts: How to create Parent record on Child record creation and assign Parent ID to Child record in MySQL

texts:
I have 2 tables - [stakeholders] and [organizations].

FK from [organizations.stakeholder_id] to [stakeholders.id].

I want to create [stakeholder] record automatically when new [organization] is created AND to assign newly created [stakeholder.id] back to [organizations.stakeholder_id] in the same transaction.

Is it possible and how?


texts: Poor cardinality estimate disqualifies INSERT from minimal logging?

texts:
Why is the second INSERT statement ~5x slower than the first?

From the amount of log data generated, I think that the second is not qualifying for minimal logging. However, the documentation in the Data Loading Performance Guide indicates that both inserts should be able to be minimally logged. So if minimal logging is the key performance difference, why is it that the second query does not qualify for minimal logging? What can be done to improve the situation?

Query #1: Inserting 5MM rows using INSERT...WITH (TABLOCK)

Consider the following query, which inserts 5MM rows into a heap. This query executes in 1 second and generates 64MB of transaction log data as reported by sys.dm_tran_database_transactions.

CREATE TABLE dbo.minimalLoggingTest (n INT NOT NULL)
GO
INSERT INTO dbo.minimalLoggingTest WITH (TABLOCK) (n)
SELECT n
-- Any table/view/sub-query that correctly estimates that it will generate 5MM rows
FROM dbo.fiveMillionNumbers
-- Provides greater consistency on my laptop, where other processes are running
OPTION (MAXDOP 1)
GO

Query #2: Inserting the same data, but SQL underestimates the # of rows

Now consider this very similar query, which operates on exactly the same data but happens to draw from a table (or complex SELECT statement with many joins in my actual production case) where the cardinality estimate is too low. This query executes in 5.5 seconds and generates 461MB of transaction log data.

CREATE TABLE dbo.minimalLoggingTest (n INT NOT NULL)
GO
INSERT INTO dbo.minimalLoggingTest WITH (TABLOCK) (n)
SELECT n
-- Any table/view/sub-query that produces 5MM rows but SQL estimates just 1000 rows
FROM dbo.fiveMillionNumbersBadEstimate
-- Provides greater consistency on my laptop, where other processes are running
OPTION (MAXDOP 1)
GO

Full script

See this Pastebin for a full set of scripts to generate the test data and execute either of these scenarios. Note that you must use a database that is in the SIMPLE recovery model.

Business context

We are semi-frequently moving around millions of rows of data, and it's important to have these operations be as efficient as possible, both in terms of the execution time and the disk I/O load. We had initially been under the impression that creating a heap table and using INSERT...WITH (TABLOCK) was a good way to do this, but have now become less confident given that we observed the situation demonstrated above in an actual production scenario (albeit with more complex queries, not the simplified version here).


texts: Determining Whether Query Load is Too High for PostgreSQL Server

texts:
I just started experiencing some write-heavy load on a PostgreSQL 11.2 system running inside a Docker container on an Ubuntu 18.04 server. PostgreSQL is receiving about 1-4k INSERT queries/second.

How can one determine whether this amount of load is too high for the system? Are there some metrics to monitor, like one that shows the number of queries being queued up at any one time?

System Specs

Intel® Atom™ C2750 (8-Core, 2.4GHz)
8GB DDR3
240 GB SSD

Output of htop

2 node.js app concurrently sending queries to PostgreSQL. Both app and database are running on the same server.

Output of iotop

Output of iostat -c 5 999

Output of vmstat 1

pgAdmin


texts: How to check if Oracle DB process is running in Unix and where Oracle DB is installed

texts:
I am new to Unix and trying to find out a command that checks if Oracle DB is installed in my Unix box. I came across commands ps -ef|grep smon and ps -ef|grep pmon in net. But which is the correct command that I can use.

Also how to find out the installation directory where the Oracle DB is installed if ORACLE_HOME variable is not set?

I am using bash shell.


texts: MySQL least privileges to get database/tables size

texts:
I'm using centreon_plugins to monitor some mysql databases. As a best practice I create a dedicated user for monitoring and I give it the least possible privileges.

For simple monitoring I usually create a monitor user with only 2 global privileges:

mysql> create user 'monitor'@'%' identified by 'monitorpassword';
mysql> grant SHOW DATABASES, REPLICATION CLIENT on *.* to 'monitor'@'%';
mysql> flush privileges;

Now I'm required to check database size. To access DB size I need that the user monitor can access information_schema.tables, but I cannot give it direct select privileges to that table.

Obviously the root user I'm using has the GRANT privilege.

mysql> show grants;
+---------------------------------------------------------------------+
| Grants for root@localhost                                           |
+---------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION |
| GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION        |
+---------------------------------------------------------------------+
2 rows in set (0.00 sec)

mysql> grant SELECT on information_schema.tables to 'monitor'@'%';
ERROR 1044 (42000): Access denied for user 'root'@'localhost' to database 'information_schema'

The access denied is due to the particular use that mysql reserves to the information_schema DB; the access to that DB is limited by the granted access to all other mysql objects.
I could achive the result giving the SELECT privilege on *.* to monitor user, but I don't want to do that because the monitor user is not protected enough (i.e. it's password is stored in cleartext in the monitoring software) and so it must not be able to see application data.

I tried various combination of privileges on different DBs and tables and none of them works.
(ref. https://dev.mysql.com/doc/refman/5.7/en/grant.html)

Did someone had the same need? How did he/she resolved it?

For completeness, the queries executed by the monitoring plugin are:

show variables like 'innodb_file_per_table'
SELECT table_schema, table_name, engine, data_free, data_length+index_length as data_used, (DATA_FREE / (DATA_LENGTH+INDEX_LENGTH)) as TAUX_FRAG FROM information_schema.tables WHERE table_type = 'BASE TABLE' AND engine IN ('InnoDB', 'MyISAM')

Thanks in advance.
Flavio


texts: Restore backup of SQL Server database results in an error

texts:
I want to restore my backup that I get it before has no extension.
It's filename is:
mssdp-dd-dump-75b5264f8e294595b462ec13a212f225

that has no extension; I don't know how to change extension to .bak or ...
When I right click on database and click restore database and select source this file causes an error when I used:
RESTORE DATABASE test
FROM DISK = 'c:\users\user\desktop\pmm-mssdp-dd-dump-75b5264f8e294595b462ec13a212f225'

I get this error:

Msg 3241, Level 16, State 13, Line 1
The media family on device 'c:\users\user\desktop\pmm-mssdp-dd-dump-75b5264f8e294595b462ec13a212f225' is incorrectly formed. SQL Server cannot process this media family.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.


texts:
I have the structure like this (json pretty):

[{
        "uid": "comment_1",
        "message": "level 1 - comment 1",
        "comments": [{
            "uid": "subcomment_1",
            "message": "level 2 - comment 1",
            "comments": []
        }, {
            "uid": "subcomment_2",
            "message": "level 1 - comment 2",
            "comments": []
        }]
    },
    {
        "uid": "P7D1hbRq4",
        "message": "level 1 - comment 2",
        "comments": []
    }
]

which is part of a table named 't1' with the structure:

id: integer,
... other stuff...,
comments: array of jsonb

I need to update a particular field: comments[1](with uid = comment_1) -> comments[2] (with uid = subcomment_2) -> message = 'comment edited'.

I'm brand new to postgresql so I can't figure it out how to do this, not even close.
I manage to merge objects and change message for level 1 with

UPDATE tasks
    set comments[1] = comments[1]::jsonb || $$
      {
        "message": "something",
      }$$::jsonb
where id = 20;

but that's as far as I could go.

Any hints towards the right direction?


texts: Same record shows up multiple times in ordered paginated query

texts:
Some records show up on multiple result "pages" with the following query:

SELECT
    `description`,
    `wallpaper`
FROM
    `bbr_bar`
WHERE
    `country_id` = '2510769'
ORDER BY
    `online` DESC

For instance, record with id 99 shows up on the first "page" (LIMIT 10 OFFSET 0) but also on the last one LIMIT 10 OFFSET 50. Obviously this should not happen.

The problem disappears under the the following circumstances:

if I don't filter the query, or don't order it (both country_id and online are int(11), not foreign keys)
if I remove one of the two fields from the SELECT clause (both varchar(255), not foreign keys)
if I delete some tables or fields in the schema. However, afters hours of effort, I am still unable to say for sure which tables/fields have an impact and which ones don't. Looks like random (even if I know it is not). Hence I don't paste here any CREATE TABLE or INSERT statement as I don't think they are relevant.

Only thing I can say for sure is that if I mysqldump all tables and restore them in a new schema, the problem is still there.

(There are other fields in the table but adding/removing them has no effect on the result).

Questions:

Am I right if I conclude that something is corrupted in my schema?
How could I fix this?

(MySQL version: 5.6.24 Win32 x86)


texts:
I know I can use "show processlist" to see what is running when I'm in the office. How can I see what is running when I'm not there.

Something is running on my server at 3am every morning that is generating a IO Rate Alert and a CPU Usage Alert, but I don't know what it is. Is there a logging system that I should be using or a script I can write that will report what is currently running against the database at that time.

The alerts come in within the same rough time-frame every night, about 0300 - 0330.

I'm not even sure if this is a DBA question or more a Sys Admin question?

I appreciate any help.


texts:
I have an update query that selects data from a table (HST) and updates it into another table (PCL). The PCL table contains ~30000 rows while the HST table contains about 1.1 million rows. I have multiple (composite) indexes on HST and despite being a large table all queries on it are fairly quick (~2-3 sec). However, when I attempt to select rows from this table and update PCL, it takes 3-4 hours, and in most cases I get "Lock wait timeout exceeded; try restarting transaction" errors and I have to keep trying until it works.

UPDATE PCL
SET `T2A` = (SELECT HST.`Date` from `HST` WHERE HST.`SYM`=PCL.INST AND
HST.`DATE` >=  PCL.Date AND HST.`HP` >= PCL.T2 order by HST.`Date` limit 1)
WHERE `BS` = 'B' AND `T1A` IS NOT NULL;

Is there a way I can rewrite the above query so that it runs faster?


texts:
I have 3 virtual Cassandra nodes cluster.
Node1, Node2 & Node3.

I have written a small python code to bulk load data into it.

1st time only 2 nodes out of three are available online, Node1 & Node2 when I execute the python code with

session.default_consistency_level = 2

I got and error:

cassandra.Unavailable: Error from server: code=1000 [Unavailable exception] message="Cannot achieve consistency level TWO" info={'consistency': 'TWO', 'required_replicas': 2, 'alive_replicas': 1}

After that I brought my third node (Node3) online and executed the python program, it executed successfully.

As I understand Consistency Level = 2 means at least 2 nodes out of 3 should be available every time.

Thanks!


texts: Can somebody please help me understand what is ESCAPED BY clause means in LOAD DATA LOCAL INFILE statement MySQL 5.5.  I am a newbie to MySQL so please bear with me.


texts: How to express functional dependency in relational calculus and SQL?

texts:
I want to express in relational calculus and SQL the following functional dependency associated with Vote. This is the first time I try to implement it in actual SQL and relational calculus.
Vote: #PollID, #ElectorID → VoteID

From the following relational scheme:

Candidate(CandidateID, Name, PoliticalGroup)

Poll(PollID, Date, Round)

Elector(ElectorID, Name, #PollingStationID)

Vote(VoteID,#PollID, #CandidateID, #ElectorID)

My attempt
1. In relational calculus

2. In SQL
CREATE ASSERTION Vote
    BEFORE COMMIT t.voteID FROM Vote t
        CHECK u.CandidateID FROM Candidate u
             WHERE EXISTS v.PollID FROM Poll v
                 WHERE u.ElectorID=t.ElectorID AND t.PollID=v.PollID


texts: Examples of a database with no consistency measures that ensures consistency through schema alone

texts:
To clarify, the database can be any type RDBMS, NoSQL/BigTable. However, it must not employ any usual consistency measures such as locks, or synchronization, but the inconsistency-invincibility must come only from the schema, somehow.

Is this "known" to be possible or impossible? Could there be a particular, perhaps very atomic, and isolated, and somehow clever, organization of data in the schema, such that all inconsistency and race conditions are impossible under any transactions? Even trivial, or non-implementable examples are welcome!

Or is there some kind of database uncertainty principle that says you can't have "consistency" and "performance" in equal share?


texts: Storing changes to stored procedure and triggers in PostgreSQL

texts:
I want a mechanism where I can store/track changes in stored procedure and triggers.

I want to track these logs in some table. What is the best possible way ?


texts:
I'm moving up to the next level of my mystery query. It looks like there's a subselect inside of an exists, but on the same table. I think this could probably be simplified with an INNER JOIN higher up.

Using PostgreSQL 9.4.2.
Table definitions (/d+): https://gist.github.com/neezer/879f5d3649ca1903c6f3
Cardinalities:

billing_pricequote: 1,462,625 rows
billing_pricequotestatus: 3,331,657 rows
billing_lineitem: 43,687,855 rows

Here's the original query, without modifications suggested for the subquery inside EXISTS:

SELECT i.quote_id, i.acct_id AS account_id, SUM(i.delta_amount) AS amt
FROM billing_lineitem i
INNER JOIN billing_pricequote pq ON i.quote_id = pq.id
WHERE pq.date_applied AT TIME ZONE 'PST' BETWEEN '2016-02-02T00:00:00'::timestamp
                                AND '2016-03-03T22:27:41.734102-08:00'::timestamptz
AND EXISTS(
  SELECT s1.quote_id
  FROM billing_pricequotestatus s1
  INNER JOIN (
    SELECT DISTINCT ON (quote_id) quote_id, MAX(created_at) AS max_created_at
    FROM billing_pricequotestatus
    WHERE quote_id=i.quote_id
    GROUP BY quote_id, created_at
    ORDER BY quote_id, created_at DESC
  ) AS s2
  ON s1.quote_id = s2.quote_id
  AND s1.created_at = s2.max_created_at
  WHERE s1.name IN ('adjustment','payment','billable')
)
GROUP BY i.quote_id, i.acct_id
;

The part I noticed looking weird is the SELECT on billing_pricequotestatus and then another subselect on the same table inside the INNER JOIN.

I tried changing this with the modification from my other SO post:

SELECT i.quote_id, i.acct_id AS account_id, SUM(i.delta_amount) AS amt
FROM billing_lineitem i
INNER JOIN billing_pricequote pq ON i.quote_id = pq.id
WHERE pq.date_applied AT TIME ZONE 'PST' BETWEEN '2016-02-02T00:00:00'::timestamp
                                AND '2016-03-03T22:27:41.734102-08:00'::timestamptz
AND EXISTS(
  SELECT quote_id, MAX(created_at) AS max_created_at
  FROM billing_pricequotestatus
  WHERE quote_id=i.quote_id
  AND name IN ('adjustment','payment','billable')
  GROUP BY quote_id
)
GROUP BY i.quote_id, i.acct_id
;

That cut my execution time in half (~40 seconds to ~20 seconds), but yielded slightly different results (the original query returned 28,895 rows, but my new query returns 28,917 rows). It's not immediately clear to me why my modification didn't produce equivalent output (which it needs to).

EXPLAIN ANALYZE for both queries
Explain analysis for original query depesz.com.

Would really appreciate any help/guidance on this!

I tried updating @ypercubeᵀᴹ's answer with a LATERAL JOIN, and the performance seems about the same (each has an equal number of wins by less than a second):

SELECT i.quote_id, i.acct_id AS account_id, SUM(i.delta_amount) AS amt
FROM billing_lineitem i
INNER JOIN billing_pricequote pq ON i.quote_id = pq.id
LEFT JOIN LATERAL
( SELECT name
  FROM billing_pricequotestatus
  WHERE quote_id = i.quote_id
  ORDER BY created_at DESC
  LIMIT 1
) pqs ON true
WHERE pq.date_applied AT TIME ZONE 'PST' BETWEEN '2016-02-02T00:00:00'::timestamp
                                AND '2016-03-03T22:27:41.734102-08:00'::timestamptz
AND pqs.name IN ('adjustment', 'payment', 'billable')
GROUP BY i.quote_id, i.acct_id
;

Explain analysis.

Any other suggestions to get this below ≈10 seconds?


texts:
We have a table with 10 billion records, where daily there are hundreds of thousands of updates/inserts/selects happening. As the auto-update stats was ON there were a few performance issues; hence, we turned it OFF and there were positive results.

Now, we want to turn the auto-update stats on in async mode, then perform another round of testing on it. How can the auto-update stats in async mode be enabled for one particular table?


texts: My SQL Server table identity is incrementing way too fast

texts:
I have a table with pretty basic design: 1 primary key (id), 1 unique column (filename) (the filename of the file I am tracking)

The primary key is an identity column (bigint) (I had to make it bigint recently because it's growing so fast)

There are only 82318 rows in the table but the identity for the last row I inserted is 2147891928.

Identity spec is on, starts at 1 increment by 1

If I go through the table ordered by id DESC I can see it jumped from time to time, sometimes by 40, sometimes by 100, 1000, 10000 or more. Is this related to the SQL Server restart seed "FEATURE" in SQL Server 2012? Because it sounds slightly different

I have a script that runs and tries to insert filenames into the SQL from a folder, if the filename already exists nothing should happen because its setup as unique right? or does it use an auto increment digit?


texts: Loading Custom Rules into unaccent postgresql hosted into an amazon aws instance

texts:
The default unaccent module has no or little support for Greek Letters using the unaccent module as seen in following links:

https://stackoverflow.com/q/49845063
https://www.postgresql-archive.org/BUG-15347-Unaccent-for-greek-characters-does-not-work-td6035023.html

Also this is my problem in my case as well. So I wanted to load custom unaccent rules as seen in https://www.postgresql.org/docs/current/unaccent.html

So I wanted to know:

In the example CREATE TEXT SEARCH CONFIGURATION fr ( COPY = french ); where the ( COPY = french ) is located?
Can be the copy statement ( COPY = french ) use data from the STDIN with my own rules?
Also can I use a dedicated database table in order to load my own unaccent rules as well?

The database is hosted into an Amazon RDS instance.


texts: What is the best method to add error handling in SQL 2005 stored procs?

texts:
What’s a good way to make stored procs robust enough that they can scale very well and also contain error handling?

Additionally, whats the best way to handle multiple error scenarios in a stored proc and have an intelligent feedback system that will return meaningful error information to the calling apps?


texts: Adhoc queries memory consumption in SQL Server

texts:
I have a SQL Server 2012 and it has just one (1) database in it. When I generate a server dashboard report it shows that 50% of CPU usage is related to my database and the rest is associated to Adhoc queries.

Where are these adhoc queries? Are they related to my database? Why are these two parameters separated? How can I find and optimize them?

Any comments would be appreciated.


texts: How to migrate the SQL Server instance and system databases to a different drive (mapped to a different letter) on the same server?

texts:
I'm tasked with moving our SQL Server instance and system databases from the existing drive it's on to a new drive on the same server. I know I can stop the SQL Server service, literally move the instance and database files to the new drive and start the service again, but the drive is going to be mapped to a different letter.

I'm guessing I'll need to update the database file paths for the system databases to point to the new drive letter.

What else do I need to do to reconfigure the instance so that's pointing to the correct drive, to ensure it works properly when I start the service again?

Edit: It sounds like this Microsoft doc is pretty encompassing of what to do, especially from the system database perspective, but are there any other steps or gotchas I should be aware of from the instance perspective: Move System Databases

Note: I am unable to re-use the same drive letter for business reasons.


texts: Select data with the latest table partition that actually holds data

texts:
Version: SQL Server 2016

So, I need to be able to select the data within the most recent table partition. I can do this by using the below script but the latest partition doesn't contain any data. What I need is a way to select the latest partition within the table that actually contains data. Does anyone have any ideas.

There's a new partition created daily, but data doesn't always get inserted within the day. There's thousands of partitions that span many years so simply scrolling through each partition isn't ideal at all.

This is how I'm currently selecting the most recent partition (or should I say the max partition):

USE DBName
DECLARE @PartitionValue int
set @PartitionValue = (select max(boundary_id) from sys.partition_range_values)
SELECT * FROM schema.tablename
WHERE $PARTITION.partition_function_name(expression) = @PartitionValue


texts:
I am trying to install oracle grid, One of the Oracle installation steps says,

  set the variable ORA_SHIPS to the path location where you are going to
  extract the 11.2.0.4 shiphomes

Please explain what is shiphomes ?


texts: DataBase Performance Issues with Indexes

texts:
I'm reviewing a production database with over 400 tables and 658 indexes. I have determined that only 440 of these indexes are being used. Of these, many have fragmentation approaching 100% so I will be rebuilding and reorganizing them as needs require. Many reports are crawling, etc.

One thing I noticed on querying the system views is that all indexes have a fill factor of 0. So my question is twofold: should I aim for a fill factor of 90% and monitor it, and should I adjust the fill factor BEFORE rebuilding indexes?


texts: PostgreSQL - Index Not Used on Large Table

texts:
I've the following query

SELECT *
FROM "items"
WHERE (("field1" = 'somevalue' OR "field2" = 'somevalue' OR "field3" = 'somevalue'))
ORDER BY "createdAt" DESC
LIMIT 20 OFFSET 0;

Here is the result of EXPLAIN ANALYZE in staging

Limit  (cost=118.72..118.77 rows=20 width=2212) (actual time=0.407..0.416 rows=20 loops=1)
  ->  Sort  (cost=118.72..118.80 rows=30 width=2212) (actual time=0.405..0.412 rows=20 loops=1)
        Sort Key: "createdAt" DESC
        Sort Method: quicksort  Memory: 75kB
        ->  Bitmap Heap Scan on items item  (cost=13.10..117.98 rows=30 width=2212) (actual time=0.078..0.231 rows=25 loops=1)
              Recheck Cond: (("col1" = 'something'::text) OR ("col2" = 'something'::text) OR (col3 = 'something'::text))
              Heap Blocks: exact=21
              ->  BitmapOr  (cost=13.10..13.10 rows=30 width=0) (actual time=0.056..0.056 rows=0 loops=1)
                    ->  Bitmap Index Scan on "item_col1_col2_col3_index"  (cost=0.00..4.47 rows=25 width=0) (actual time=0.038..0.038 rows=25 loops=1)
                          Index Cond: ("col1" = 'something'::text)
                    ->  Bitmap Index Scan on "col2_index"  (cost=0.00..4.29 rows=1 width=0) (actual time=0.008..0.008 rows=0 loops=1)
                          Index Cond: ("col2" = 'something'::text)
                    ->  Bitmap Index Scan on col3_index  (cost=0.00..4.31 rows=4 width=0) (actual time=0.008..0.008 rows=0 loops=1)
                          Index Cond: (col3 = '5a9793161131a39df46fe851'::text)
Planning time: 0.867 ms
Execution time: 0.631 ms

The item_col1_col2_col3_index, as the name suggests, is a compound index of col1, col2 and col3.

And here is the result of EXPLAIN ANALYZE in production

Limit  (cost=0.43..13020.16 rows=20 width=2378) (actual time=1060451.457..1060451.457 rows=0 loops=1)
  ->  Index Scan Backward using "createdAt_index" on items item  (cost=0.43..7711586.19 rows=11846 width=2378) (actual time=1060451.453..1060451.453 rows=0 loops=1)
        Filter: (("col1" = 'something'::text) OR ("col2" = 'something'::text) OR (col3 = 'something'::text))
        Rows Removed by Filter: 2987649
Planning time: 0.539 ms
Execution time: 1060459.032 ms

As you can see, in production it was executed with createdAt_index. The process of filtered out unmatched records were done without index though the item_col1_col2_col3_index exists. Therefore it took very long.

The main difference is in staging, the table only has about 5000 records. In production, it contains about 3.4 million records

I've been running VACUUM ANALYZE items since 2 hours ago (it's still running at the time I posted this question)

Is there any idea how can I improve the speed of the query?


texts: Is it possible to get the information of which user changed what in a database?

texts:
I'm trying to create a log of changes in a given table in a database.
To do this, I'm going to get the info about all the fields in the original table and create a new one using a function.

In addition to the fields in the original table, the new table should contain the time of the change, the type of the change (insert, delete, update) and an identification of the user that made the change.

I've figured out where I can get the information about the columns of the original table in MySQL (table INFORMATION_SCHEMA.Columns), but I don't know where I can find the information related to the user.

Can anyone please point me in the right direction?


texts:
I am inserting into the following table using LuaSQL with PtokaX API.

CREATE TABLE `requests` (
    `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
    `ctg` VARCHAR(15) NOT NULL,
    `msg` VARCHAR(250) NOT NULL,
    `nick` VARCHAR(32) NOT NULL,
    `filled` ENUM('Y','N') NOT NULL DEFAULT 'N',
    `dated` DATETIME NOT NULL,
    `filldate` DATETIME NULL DEFAULT NULL,
    PRIMARY KEY (`id`),
    UNIQUE INDEX `nick_msg` (`nick`, `msg`),
    UNIQUE INDEX `ctg_msg` (`ctg`, `msg`)
)
COMMENT='Requests from users in any of the categories.'
COLLATE='utf8_general_ci'
ENGINE=MyISAM;

Now, my problem is, when a user(represented by nick) tries to insert same request again, the UNIQUE index is checked and the script returns a false. This causes my script to fail and I have to restart the script.

  Is there something I can do in the INSERT ... ON DUPLICATE KEY command so that it does nothing or at-least does NOT return an error in case of DUPLICATE KEY?

Otherwise I'd have to go for updating my dated field with the new DATETIME value.


texts: Database design, many rows or many columns?

texts:
I am aware that this has been discussed before, but I've read conflicting information about this particular topic, so I'm hoping for a bit more decisive opinions from you.

I have multiple tables, which stand in a 1:1 relationship to a main table linked by a foreign key.

The largest of these tables, the parameter table, has about 1000 columns by now, each of the columns holding a single numerical parameter.

The table in question has a size of about 16 MB with 7000 rows right now.

After creating a normalized structure (ID, Key, Value) and inserting all the data into this new table, it turned into 3 million rows (after filtering out the columns that hadn't been used in the table with the many columns) and a size of 107 MB.

Now I'm wondering, if the effort is really worth the gain. Sure, it's a lot more scalable (I've had to add columns manually way too often lately to make sure it continued to work), but like this I have up to 1000 rows to sort through and I'm just not sure any more.

Some input would be appreciated. Thank you very much.

Edit: The majority of the operations will be 'SELECT', followed by UPDATE/INSERT ... ON DUPLICATE KEY UPDATE respectively (the later is for the relational approach)


texts: Registering a SSDT database project as a data tier application fails

texts:
I am having problems publishing a SSDT database project and registering it as a data tier application. Let me explain.

I have a database (A) which references two other databases (B & C) through linked servers. I have created projects based on B and C and snapshoted the projects to create dacpac's for databases B and C. I have created a database project for database A which has database references to B and C through dacpac's. I have set SQLCMD variables and modified the db project ddl scripts to use the SQLCMD variables in place of the un-resolved linked server names. The project builds!

I am trying to publish the project as a data tier application but keep receiving the following error "Databases registered as a DAC database must be hosted by an instance of SQL 2005 SP4, SQL 2008 SP2, SQL 2008 R2, SQL 2012 or SQL Azure". Incidentally I am running SQL server 2012.

I thought I would test whether I could register as a data tier application through SSMS. Within SSMS the option to "Register as data Tier Application" is grayed out. I therefore tried to "Export Data Tier Application" and received a number of error in reference to the linked server objects.

My question is; is it possible to deploy a SSDT database project and register it as a Data Tier Application where the project is using linked servers, or am I doing something wrong? If it is possible could some one provide some advice.

I have broken Google looking for the answer, so any help would be greatly appreciated. I have previously asked this question on StackOverflow but got no response so I am posting here in a more targeted forum.


texts: Postgres security for functions with "security definer"

texts: I was reading about possible security issues when creating functions in Postgres with "security definer". Official documentation suggests that search_path is set to some trusted schema followed by pg_temp. All examples I could find had pg_temp at the end. Do I have to put pg_temp at the end, what would happen if set search_path only to the trusted schema? Would it open any security holes? And why would you put pg_temp in search_path anyway?


texts: user permission cache in session or check from database?

texts:
On each page of my websites 2 or 3 permissions have to be checked and I use role-permission model. Which one is better? to check permissions of each user always through database directly or once calculate it and store in session of user(cache permissions in sessions)?

which one is more common?


texts: Generate test data distrubution in Oracle DB

texts:
What I want to do is to generate test data as follows

table
t1 (id number, colA date, colB number).
Where
"id" is unique,
"colA" is a date incremented every 15 minutes,
"colB" is a number

Condition: value from colA should be repeated random n times.
This is what I've managed to come up with so far:

    SELECT rownum id, TO_DATE('01-MAY-2015') + ROWNUM/24/4 - 1 AS colA, trunc(100*exp(dbms_random.normal)) AS colB
    FROM (
        SELECT ROWNUM FROM (
            SELECT 1 FROM DUAL
            CONNECT BY LEVEL <= (TO_DATE('01-SEP-2015') - TO_DATE('01-MAY-2015'))*24*4
        )
    );

And how it should look like

Example:

ID      colA                    colB
=====   ====================    =====
11089   23-AUG-2015 12:15:00    75667
11090   23-AUG-2015 12:15:00    64552
11091   23-AUG-2015 12:15:00    26633
11092   23-AUG-2015 12:15:00    86463
11093   23-AUG-2015 12:30:00    77364
11094   23-AUG-2015 12:30:00    2233
11095   23-AUG-2015 12:30:00    32213
11096   23-AUG-2015 12:45:00    73112
11097   23-AUG-2015 13:00:00    1238
11098   23-AUG-2015 13:00:00    8222


texts:
I'm trying to do a performance comparison of a table with partitioning and the same table without partitioning.

So we have Subcriber and SubscriberPartitioned tables.

Tables structure is:

SubscriberId | Name | Email | Telephone | UserId (partition column)

Data: I'm using SQL Data Generator:

to fill the tables with 10 million rows each
the UserId range goes from 1-200

Partitioning: I have partitioned the table SubcriberPartioned in 2 according to the UserId, which goes from 1 to 200. So we have approx 5 million rows in each partition.

Performance measure: I'm using SQL Server Profiler to measure the query times.

Why does SQL Server differ so much in query times?

For example the query

select * from subcriber where SubscriberId = 1000

... will the first time take like 40 seconds, and if I re-execute it will take less than a second. If I try with another Id it also will take less than a second. But the first time took a lot of time.

So, is there any optimization or caching being done automatically in the background by SQL Server?

I was expecting the same query to take the same time if repeated over time...
Which do you think would be the best queries to run and how to measure them in order to test the partitioning of the table and to see the optimization gained with the partition strategy instead of the normal table.

EDIT:

After answers on this post I made some cold start tests, and found that the non partitioned table was performing faster than the partitioned table.

Specially this query caught my attention:

select * from [table] where IdUser = 100

Why doesn't the partitioned table improve the performance on it? The above query includes a filter by the partition column, wouldn't that focus the engine to scan only half of the records on the partitioned approach?

EDIT 2:

I have performed the tests again. Assigning the second partition a different file group, and I have reached a performance improvement of 50 % in the mentioned query.


texts: Is it Okay for mysql to hit 59% without no query running in my site?

texts:
Please I need some little help from you guys.

I have a VPS I bought since 2018 and since then one of the most difficult thing i have been facing is high load on my SQL, but i guess that was due to my big database of 1GB size.
my vps spec is 16GB RAM & 6 cores.

Recently I have sold the site and deleted the 1GB database which was causing me issues on mysql server. Ever since then I have little wp sites on the server with No much traffic, but I still have mysql hitting up to 59% sometimes. I have check to see if its long query that is taking it but nothing at all.

One day I even switch off my apeche just to make sure I see the sql server uses low cpu process but to my greatest surprise I am getting at least 5-15%

I thought since no apache is connected the sql server has to fall to 0.2 or i don't even want to see it among my list of processes when i run top command.

Please Guru in the house is it okay to get this limit while no website is running on the server. and what are the optimization needed to stop it from reaching such percentage even with no long query running on the websites. or is there an evil spirit from the old 1gb deleted database that is still disturbing the mysql server. help me out please.


texts: How to list all functions with arguments that a role is allowed to execute in Postgres?

texts:
I found how to list tables a role has permissions on. I found how to list all functions with arguments.

My question is: How to list all functions with arguments a role is allowed to execute?


texts: PostgreSQL Role Inheritance behavior in AWS RDS

texts:
I was familiarizing myself with how PostgreSQL (9.6) role management works and ran into a case that seemed to go against my understanding of the documentation.

I have an RDS instance and a master user with the rds_superuser role.  I used this role to create a "group" role called 'readwritegroup'. This role has NOLOGIN and CREATEDB granted to it so that anyone who is added to the group may inherit those permissions but cannot actually be used to do things on.

Using my master user I created a database and a new schema inside of it.  I granted CREATE and USAGE on the schema to the 'readwritegroup" role.  I then created and added 2 separate users to the 'readwritegroup' with only INHERIT as their grants.

I then used the master user to run the following on the schema:

ALTER DEFAULT PRIVILEGES IN SCHEMA test
GRANT ALL PRIVILEGES ON TABLES TO "readwritegroup";

Using these two users I successfully created 1 table for each user and then attempted to SELECT from each user's table using the other user. So they are the owner of their respective tables. They are unable to see each other's tables however. (permission denied)

In fact, even my master user is unable to GRANT any permissions on the tables that they own (though I can change the ownership to my master user).

Is there any way to circumvent the authority of object ownership?  I don't really understand why the ALTER DEFAULT PRIVILEGES bit does not correctly modify the behavior to allow other users in the group to SELECT (or whatever grants are used).


texts: ORACLE SSL using Microsoft Certificate Store for PKI

texts:
I am trying to set up PKI SSL authentication between client and server using a Microsoft Certificate Store.

Client: Windows 7 64 bit with Oracle client 11.2.0.3.0

Server: Windows 2008 R2 64 bit with Oracle server 11.2.0.1.0

The Advanced Security Administrators guide doesn't have a whole lot of information on the interaction with Microsoft Certificate Services other than specifying METHOD=MCS for your wallet location. Does anyone have a more detailed explanation?

On the client in my user certificate store, I have a personal certificate (public/private 'smart-card') in the personal container and the server's public cert in the Trusted Root Certificate Authorities container. Both are signed by the same CA.

On the server in the Local Computer certificate store, I have the server's certificate (public/private) in the Personal container and the clients public cert in the Trusted Root Certificate Authorities container. Again signed by the same CA.

I would sure like to get some confirmation from anyone that has done this with any version of Oracle before I recommend this to a customer.

When I connect from client I get ORA-28864: SSL connection closed gracefully.

Here is what I think is the relevant part of the Servers Listener trace file.  This error occurs when I attempt a client connect or when I perform a lsnrctl status.  I'm making an educated guess that it can not find the Server Certificate.

2012-04-23 07:13:52.503508 : ntzGetStringParameter:found value for "wallet_location" configuration parameter: "SOURCE = (METHOD = MCS)"
2012-04-23 07:13:52.503523 : ntzGetStringParameter:exit
2012-04-23 07:13:52.503536 : ntzAllocate:entry
2012-04-23 07:13:52.503551 : ntzAllocate:allocating 26 bytes of memory.
2012-04-23 07:13:52.503565 : ntzAllocate:exit
2012-04-23 07:13:52.503585 : ntzAllocate:entry
2012-04-23 07:13:52.503600 : ntzAllocate:allocating 8 bytes of memory.
2012-04-23 07:13:52.503614 : ntzAllocate:exit
2012-04-23 07:13:52.503628 : ntzgwrlFromFile:exit
2012-04-23 07:13:52.503642 : ntzgwrl:exit
2012-04-23 07:13:52.503655 : ntzscr:entry
2012-04-23 07:13:52.503670 : ntzGetStringParameter:entry
2012-04-23 07:13:52.503684 : ntzGetStringParameter:exit
2012-04-23 07:13:52.503698 : ntzGetStringParameter:entry
2012-04-23 07:13:52.503712 : ntzGetStringParameter:exit
2012-04-23 07:13:52.503725 : ntzGetStringParameter:entry
2012-04-23 07:13:52.503740 : ntzGetStringParameter:exit
2012-04-23 07:13:52.503754 : ntzscr:exit
2012-04-23 07:13:52.503767 : ntzlogin:entry
2012-04-23 07:13:52.503784 : ntzlogin:Persona retrieval failed with error 28788
2012-04-23 07:13:52.503803 : ntzlogin:returning NZ error 28788 in result structure
2012-04-23 07:13:52.503819 : ntzlogin:failed with error 540
2012-04-23 07:13:52.503833 : ntzlogin:exit
2012-04-23 07:13:52.503847 : ntzConfigure:failed with error 540
2012-04-23 07:13:52.503862 : ntzConfigure:exit
2012-04-23 07:13:52.503876 : ntzCreateConnection:failed with error 540
2012-04-23 07:13:52.503891 : ntzCreateConnection:exit
2012-04-23 07:13:52.503906 : ntzdisconnect:entry
2012-04-23 07:13:52.503920 : ntzFreeNTZData:entry
2012-04-23 07:13:52.503936 : ntzFreeNTZData:exit
2012-04-23 07:13:52.503950 : nttdisc:entry
2012-04-23 07:13:52.557834 : nttdisc:Closed socket 816
2012-04-23 07:13:52.557864 : nttdisc:exit
2012-04-23 07:13:52.557883 : ntzdisconnect:exit
2012-04-23 07:13:52.557899 : ntzconnect:failed with error 540
2012-04-23 07:13:52.557915 : ntzconnect:exit
2012-04-23 07:13:52.557931 : nserror:entry
2012-04-23 07:13:52.557949 : nserror:nsres: id=0, op=65, ns=12560, ns2=0;
    nt[0]=540, nt[1]=0, nt[2]=0; ora[0]=0, ora[1]=0, ora[2]=0
2012-04-23 07:13:52.557966 : nsopen:unable to open transport
2012-04-23 07:13:52.557981 : nstoClearTimeout:entry


texts: SQL tracking number of Selects of a databases table row

texts:
Hello I'm trying to create a way in which I could track the number of times a certain column in a database table is selected. Is there something I could implement like a trigger that could increment a count column on another table that is associated with the selected row? Trying to figure out which rows in my database tables are used the most.

Thanks, any help is appreciated.


texts: Can a large number of schemas be an issue?

texts:
Can a large number of schemas present a problem in some ways (performance, maintenance)?
By large I mean a few thousands, and schemas will mostly contain views, fetching data from shared tables.


texts:
I'm trying to improve the performance of a postgresql databases that I migrated from oracle. I`m bringing alot of data from oracle tables with oracle_fdw extension into local postgresql tables and after that I'll create indexes on them.

In total, I'm bringing 56 GB of data from 10 tables. When I say "bringing" I mean that I run insert into local_postgresql_table select * from remote_oracle_table. After that I'm building many multiple indexes on the local postgresql tables.

Lets look on specific table ps_rf. The size of the table is 23 GB and it took 1.5 hours only to bring the data and 14 min. for creation of every index (5 indexes for this table).

Another example is a table with a size of 3.3 GB, it took me 10 min. to bring its data.

The parameters that I assigned :

min_parallel_relation_size = 200MB
max_parallel_workers_per_gather = 5
max_worker_processes = 8
effective_cache_size = 2500MB
work_mem = 16MB
maintenance_work_mem = 1500MB
shared_buffers = 1500MB
RAM : 5G

I would like to improve both operations - bringing data from oracle and creation on indexes. Please advice how can I continue.


texts: Create an Azure Synapse temp table from a SELECT with ORDER BY

texts:
I have the following statement that works when I omit the ORDER BY clause, or if I run it as a select and omit the create table part, but I need both to ensure my generated key is sequenced correctly
Any ideas?

Msg 104381, Level 16, State 1, Line 18
The ORDER BY clause is invalid in views, CREATE TABLE AS SELECT, INSERT SELECT, SELECT INTO, inline functions, derived tables, subqueries, and common table expressions, unless TOP or FOR XML is also specified.

CREATE TABLE #demo
WITH (DISTRIBUTION = ROUND_ROBIN)
AS
SELECT
       ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS ID,
       schemas.name as [schema],
       tables.name as [table],
       columns.column_id as [ordinal],
       columns.name as [column],
       types.name as [type]
FROM SYS.COLUMNS
  inner join sys.types
          on  types.system_type_id = columns.system_type_id
  inner join sys.tables
          on  tables.object_id = columns.object_id
  inner join sys.schemas
          on  schemas.schema_id = tables.schema_id
order by schemas.name,
       tables.name,
       columns.column_id


texts: Specify Connection in Management Studio T-SQL Query

texts:
When adding users are roles to DB servers I often use the "Script this action" function from the GUI. I then just go to "Connection :: Change Connection" to do the same on my other servers.

Is there a way I can specify the connection in the scripted action so I don't have to do that second Change Connection step?


texts: pg_restore to create database before loading it?

texts:
I understand that pg_restore needs to connect to a database before it can start the restore process, is there anyway pg_restore can use the "postgres" database to create another database?

I have the following command

pg_restore -h "192.168.1.212" -p 5432 -U mysuperusername -C -d bp bp.custom

when I run the command I get the following message

  pg_restore: [archiver (db)] connection to database "bp" failed: FATAL:
  database "bp" does not exist

Is there anyway for pg_restore to create the "bp" database without me having to create it manually?


texts: Database does not send any data to application when ran for a larger data set

texts:
I have a query that runs fine when returning a smaller dataset (~1 million rows), but when I increase the number of rows it returns (~15-20 million) it will send nothing to the application.

My query has two inner joins, both that use an index. There is also an index on the date range that is used for smaller data sets, but not for larger data sets. I have tried forcing this key to no avail.

In show processlist the query seems to be stuck on "Writing to net".

I am using Mariadb 10.

My application requires that I get my data as soon as possible with as little reduction to throughput as possible, but some is acceptable.

The query runs fine using the mysql cli.

Explain for small dataset:

+------+-------------+--------------+--------+-----------------------------------+------------------+---------+------------------------------------+----------+----------+-----------------------+
| id   | select_type | table        | type   | possible_keys                     | key              | key_len | ref                                | rows     | filtered | Extra                 |
+------+-------------+--------------+--------+-----------------------------------+------------------+---------+------------------------------------+----------+----------+-----------------------+
|    1 | SIMPLE      | event_list   | range  | event_type_index,time_event_index | time_event_index | 12      | NULL                               | 10091044 |   100.00 | Using index condition |
|    1 | SIMPLE      | misc_details | eq_ref | PRIMARY                           | PRIMARY          | 4       | usage.event_list.event_id   |        1 |   100.00 |                       |
|    1 | SIMPLE      | profiles     | eq_ref | PRIMARY                           | PRIMARY          | 4       | usage.event_list.profile_id |        1 |   100.00 | Using where           |
+------+-------------+--------------+--------+-----------------------------------+------------------+---------+------------------------------------+----------+----------+-----------------------+

Explain for large dataset:

+------+-------------+--------------+--------+-----------------------------------+---------+---------+------------------------------------+----------+----------+-------------+
| id   | select_type | table        | type   | possible_keys                     | key     | key_len | ref                                | rows     | filtered | Extra       |
+------+-------------+--------------+--------+-----------------------------------+---------+---------+------------------------------------+----------+----------+-------------+
|    1 | SIMPLE      | event_list   | ALL    | event_type_index,time_event_index | NULL    | NULL    | NULL                               | 65610043 |    38.03 | Using where |
|    1 | SIMPLE      | misc_details | eq_ref | PRIMARY                           | PRIMARY | 4       | usage.event_list.event_id   |        1 |   100.00 |             |
|    1 | SIMPLE      | profiles     | eq_ref | PRIMARY                           | PRIMARY | 4       | usage.event_list.profile_id |        1 |   100.00 | Using where |
+------+-------------+--------------+--------+-----------------------------------+---------+---------+------------------------------------+----------+----------+-------------+

Using FORCE INDEX lets it run for a large dataset with the same explain, but does not work.

CREATE TABLE (key information for relative fields):

  PRIMARY KEY (`event_id`),
  KEY `event_type_index` (`event_type`),
  KEY `profile_index` (`profile_id`),
  KEY `time_event_index` (`timeOf`,`event_type`)

SELECT [various fields]
FROM event_list
INNER JOIN misc_details USING (event_id)
INNER JOIN profiles USING (profile_id)
WHERE event_type IN ([various ids]) and timeOf BETWEEN [date range]


texts: How to select from pdbs with a cdb user?

texts:
I have several store procedures, which analyze oracle logs using log miner. These procedures run on CDB user C##ADMIN which created for this manner.
Now I want to compare the logged values to the live values, so I need to be able to access each pdb from the CDB.
After several searches, I came across database link which looks like a possible answer. but when I'm trying to create a database link it says ORA-02011: duplicate database link name. and when I'm trying to drop it I'm getting ORA-65230: internal database link cannot be altered or dropped.
I'm tried to run the following queries, but it failed: (even when connected as: / as sysdba)
SELECT * FROM ALL_TABLES@testpdb;

ORA-12541: TNS:no listener
*Cause: The connection request could not be completed because the listener
      is not running.
*Action: Ensure that the supplied destination address matches one of
      the addresses used by the listener - compare the TNSNAMES.ORA entry with
      the appropriate LISTENER.ORA file (or TNSNAV.ORA if the connection is to
      go by way of an Interchange). Start the listener on the remote machine.

SELECT * FROM test.TABLE1@testpdb;

ORA-12541: TNS:no listener
*Cause: The connection request could not be completed because the listener
      is not running.
*Action: Ensure that the supplied destination address matches one of
      the addresses used by the listener - compare the TNSNAMES.ORA entry with
      the appropriate LISTENER.ORA file (or TNSNAV.ORA if the connection is to
      go by way of an Interchange). Start the listener on the remote machine.

So my questions are:

Is using database links is it the correct way?
If it is, how to configure it?
How can I use it?
Do I have any alternative to achieve my goal?


texts: How calculate per tenant disk usage in a multitenant database with tenant_id columns?

texts:
I would like to provide forum hosting as a service, for forum software I'm developing. The data is stored in a PostgreSQL database, in tables with a forum_id column. That is, all forums share the same tables in one single database, and I know to which forum a row belongs, by looking at the forum_id column.

How do I go about estimating how much disk storage space each forum is using?

I cannot simply use the total size of any particular table, since each table contains data for many forums. What I'm thinking about, is to populate a test database with lots of test data, then add 1000 "typical" rows, and measure how much the relevant tables and and indexes grow because of these new rows, and in that way estimate row & index sizes per row. Then, to estimate how much storage a certain forum uses, I could multiply the number of rows for that forum with the average row & index sizes for the database table.

But this feels like an inexact method and I imagine that the actual storage space used could easily be 5x more or less than what my calculations would indicate. — Something I could do to make the above approach more exact, could be to try to take into account e.g. how long each comment that is posted is, and not just the number of comments. I could add 1000 forum comments with 100 characters, and measure the size growth of the comments table. Then add 1000 comments with 10 000 characters, and again measure size growth. And, given this data, try to estimate row size as a function of number of characters per comment, and use that when estimating total size. But this seems like lots of work.

Yet another approach is to ignore the actual on disk database size, and instead count number of characters written, when comments are posted. The total length of all combined comments for a certain forum, would then be considered the database size for that forum. This seems a lot simpler, but I would still have to estimate table sizes as a function of forum sizes, in order to know how much disk would be needed.

How do service providers that give you e.g. 500 MB storage calculate how much storage a tenant has actually used? Assuming they use tables/collections with a tenant ID column (but not dedicated one-per-tenant databases).


texts: why has Lock_time in slow query log when execute select sql?

texts:
Why mysql has Lock_time in slow query log when execute select sql?

  Time: 2018-11-22T15:06:32.654503Z
   User@Host: root[root] @ localhost
   []  Id:     3
   Query_time: 0.058138  Lock_time: 0.000287 Rows_sent: 0  Rows_examined: 3115 SET timestamp=1542899192;
  select * from COLUMNS where COLUMN_TYPE ='int';


texts: Changes access method for non-correlated subquery

texts:
Oracle 11g R2

Unfortunately our application has per row security "features".
We have a query that looks about like this:

Bad, slow:

SELECT someRow, someOtherRow
FROM bigTableA a
WHERE EXISTS (
  SELECT 0 FROM bigTableA_securitymapping b
  WHERE b.PrimaryKeyTableA = a.PrimaryKeyTableA AND
       b.accesscode in (SELECT accesscode
                              FROM accesscodeView
                              WHERE user = :someUserID)
)

There a unique index on bigTableA_securitymapping(PrimaryKeyTableA,accesscode).

The accesscodeView could potentially return more than one accesscode for a given user, so it must be IN() and not =.

The issue is that this query ignores the unique index for bigTableA_securitymapping and chooses to do a full table scan.

If I change the IN() to an = then it does a UNIQUE SCAN on the unique index on bigTableA_securitymapping and is about 50 times faster.

Good, fast but not possible:

SELECT someRow, someOtherRow
    FROM bigTableA a
    WHERE EXISTS (
      SELECT 0 FROM bigTableA_securitymapping b
      WHERE b.PrimaryKeyTableA = a.PrimaryKeyTableA AND
           b.accesscode =(SELECT distinct accesscode
                                  FROM accesscodeView
                                  WHERE user = :someUserID)
    )

But, I cannot do that because the accesscodeView may return more than one row.

(There's a distinct in there because the accesscodeView needs it given the =, putting the DISTINCT on the original query makes no difference.)

If I hardcode the accesscodes, it also does a UNIQUE SCAN on the unique index for bigTableA_securitymapping.

Good, fast but requires large application change:

SELECT someRow, someOtherRow
      FROM bigTableA a
      WHERE EXISTS (
        SELECT 0 FROM bigTableA_securitymapping b
        WHERE b.PrimaryKeyTableA = a.PrimaryKeyTableA AND
             b.accesscode in (1,2,3,4)
      )

Changing to a join inside doesn't really help either. It still does a full table scan.

Bad, slow:

SELECT someRow, someOtherRow
FROM bigTableA a
WHERE EXISTS (
  SELECT 0 FROM accesscode ac INNER JOIN
                bigTableA_securitymapping b ON
                  ac.accesscode = b.accesscode
  WHERE b.PrimaryKeyTableA = a.PrimaryKeyTableA
        AND user = :someUserID
)

So why the difference between = and IN() in.  And why does a non-correlated subquery (the accesscodeview subquery) cause such a plan difference?  Is there any way to rewrite it to do what I want?  The difference in 'good plan' costs vs 'bad plan' costs here are 87 vs 37,000 and a large amount of time in real runtime for the same results.


texts: Are db_recovery_file_dest and db_recovery_file_dest_size parameter dynamic or static?

texts:
Please have a look at the output below:

As I know we can check if the parameter is static or dynamic in Oracle using this query. Please correct me if I am wrong.

But I have seen something strange that "Even if db_recovery_file_dest and db_recovery_file_dest_size parameter are dynamic parameter, the data dictionary is showing FALSE value". As I understand from this SQL query, the mentioned parameters are static parameters.

I am not able to understand which one is correct or I am getting something wrong.


texts: Configuring Postgres 9.2 streaming replication with Redhat Clustering Suite for High Availability

texts:
I'm in the process of configuring PostgreSQL 9.2 streaming replication with Redhat Clustering Suite for high availability.

I have 1 master and 1 hot standby configured to replicate the data from master to standby using streaming replication.

Now I will have to configure automatic fail over to standby using RedHat Clustering Suite (RHCS).
It looks like PostgreSQL 9.2 is not a supported resource type for RHCS and I will have to add a resource type manually.
But I couldn't find any resources which tells how to go about it.

Following is the approach I'm planning to follow in the status and start command in the resources script.

Status command:
Check the database cluster state is "in production"
using the command: /usr/pgsql-9.2/bin/pg_controldata /var/lib/pgsql/9.2/data | grep "Database cluster state:" | cut -d':' -f2 | sed 's/^ *//g'
If yes return success. If no, return failure.
Start command:
Check the database cluster state of the master and follow the steps below:

If it is "in production" state , do nothing
If it is "in archive recovery" state, touch the trigger_file configured in the recovery.conf file and return true.
If it is "in shutdown" state, rename the recovery.conf and restart the standby.

I would like to get the expert opinion on whether the approach is correct or is there any better/well defined approach for this.


texts: migration by cloning a Production SQL Server host into a Dev environment

texts:
We are doing some SQL Server migrations, various versions, by cloning (snapshot backups) the host servers to the new DC in a different domain. Work is still in planning stage, not yet implemented. For the DEV environment in the destination domain, we need to have Production data in the databases from the source instances.
I am therefore thinking that we could clone the Production server from the source domain and rename the host in the destination Dev environment. The SQL server host name part of the instance name will also be renamed i.e. host_server_name\instance_name.
Are there other considerations that I need to have in going down this route? Things I need to be aware of about moving from Prod to Dev? Logins/users from Prod will be dropped and test logins/users will be scripted in.
Any thoughts will be appreciated please. Thank you.


texts: Special function slow in where but not in select

texts:
I have kind of a special problem using a levenshtein algorithm in MySql.
But I don't think that it's special for levenshtein.
This query:

SELECT *,levenshtein(word,'Facbook') FROM Words WHERE length(word) between 6 and 8 and levenshtein(word,'Facbook') <=1 is pretty slow with around 5000 rows it takes about 1s.

But on the other hand this one:

SELECT *,levenshtein(word,'Facbook') FROM Words WHERE length(word) between 6 and 8 is kind of fast (0.02s).

The explain statments looks nearly identical only the number of rows differs a single bit.

The explain table for the first query:

 | id select_type table type possible_keys key key_len ref rows Extra                    |
 ----------------------------------------------------------------------------------------
 | 1  SIMPLE      Words index NULL         word   302  NULL 4711 Using where; Using index|

The second explain:

 | id  select_type table type   possible_keys key key_len ref rows Extra                  |
 ------------------------------------------------------------------------------------------
 | 1   SIMPLE      Words index  NULL          word 302    NULL 4621 Using where; Using index

Btw I have an index on the word column.
Now the question is why is the first one so much slower? It only has to use the second query and "print out" only the words that matching the second where clause: levenshtein(word,'Facbook') <=1 Right?

Strange:
I used PHPMyAdmin to check the time and now I used a simple php file to send the query and now both queries took 1s.
So maybe this question is totally bullshit...


texts: SSIS Data Source: Embedded Queries, Stored Procedures, or Functions

texts:
We are moving data from OLTP server into Data Warehouse server, utilizing SSIS to conduct this.

When writing queries from the OLTP Data Source, it it better to utilize?

1) Inline Embedded queries

2) Functions

3) Or Stored Procedures

In researching and testing - Embedded queries are great, however we want code placed in SSDT Database projects to build/compile. So if column name changes/deleted, we would know in Devops portion. If writing in SSIS Datasource 'Test abcd etc', SSIS will still build/compile. If writing in SQL Database project, 'select testabcd from dbo.exampletable', SSDT DB project will fail to build/compile.

I hear Functions are great, since it will display the metadata. However, Multi Statement table value functions are Slow, which team will require sometime.

Stored procedures I am reading have issues with creating metadata. Is this true ? Is there a workaround, heard its possible to create metadata in stored procedures.

Can someone let me know good option between three, or any other options?

4) * Did not want to incorporate views, since team wants to parameterize queries by source Create/Update Dates, if ever intending to reprocess data.


texts: What causes "lock request time out period exceeded" when droping a column?

texts:
I created some columns in our production database but now I need to drop one.  However this seems impossible and I do not know why.  I dropped the column in our development database just fine.  I created a table in production and dropped that.  But when I try to remove this one column I get the error.  I used management studio (which fails quickly) and T-SQL (which takes longer but still fails).

ALTER TABLE VEN DROP COLUMN RI_VEN_Approved

Any idea whats going on?

I checked for locks using a query found here: https://stackoverflow.com/questions/1511675/how-to-check-if-a-table-is-locked-in-sql-server

Using this query specifically:

SELECT resource_type, resource_associated_entity_id,
request_status, request_mode,request_session_id,
resource_description, o.object_id, o.name, o.type_desc
FROM sys.dm_tran_locks l, sys.objects o
WHERE l.resource_associated_entity_id = o.object_id
and resource_database_id = DB_ID()

The table did not appear in the results.

I ran sp_who2 and did not see anything in the BlkBy column.


texts:
I want to create a job with two steps:
 1. Success - go to step two, failure - exit(f).
 2. Success - exit(s), failure - go to step one, but if this step fails more than once - exit(f).
Initial thought was to query some kind of job run id in step one, and if there are four (or more) rows for this id exit with failure, but there is no such thing as run id.
What is the best way to implement this logic?


texts: System-level way to enforce that reserved words may not be used for Oracle column names?

texts:
Is there a way in Oracle 12c to enforce that reserved words / keywords may not be used as column names? I am aware that it is not good practice to use keywords in this way. However from what we have observed, tables are able to be created with a column name of the reserved word "password" in our local Oracle, but the customer found that the table was was not being created on their Oracle environment.

A complication here is that the tables are being created by a Java application which uses Hibernate to create the tables and these are not simply DDL statements. I have seen the related question: Oracle: Add Reserved Word as Column Name and I realise that the use of reserved words is permitted inside quotes, but I was wondering is there an system level "strict" mode that prevents the creation of tables with reserved words?


texts: Trouble converting MySql Database to SQL Server

texts:
I have an mysql database that contains 38 tables. I used "SQL Server Migration Assistant for Mysql" tool to convert this database to SQL server Database. I successfully converted 37 tables. Only one table is making error. Can anyone write the exact code of this (mysql table) in SQL server. I am using SQL Server 2008. The code is;

CREATE TABLE `oc_t_item_description` (
  `fk_i_item_id` int(10) unsigned NOT NULL,
  `fk_c_locale_code` char(5) NOT NULL,
  `s_title` varchar(100) NOT NULL,
  `s_description` mediumtext NOT NULL,
  PRIMARY KEY (`fk_i_item_id`,`fk_c_locale_code`),
  FULLTEXT KEY `s_description` (`s_description`,`s_title`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8

I want all keys to remain unchanged. In short the above code should be exactly changed to SQL server. so that i may create it manually.