Do not lock the whole table on select statement I have a table that is constantly updated by some scripts. The scripts are adding new values and changing tuples but never deleting any tuple.

I need to run some queries, that use all rows in the table (about 2 million at the moment) for joining. The Problem is that the scripts lock the table in a way, that my select statements are blocked and are executed very slowly (compare 16minutes vs a few seconds if running without any script in the background).

Is it possible to lock the table in a way, that i read a current snapshot and do not care about changes? For most applications it is sufficient to allow that ghost entries are present (data is changed/added while the select query was running). It is not necassary in the most cases to lock the whole table, but rather use a column lock (which i think will not work on my MS SQL Server 2005)

Sounds like you want to consider READ COMMITTED SNAPSHOT ISOLATION, which uses tempdb to provide a point-in-time version of the data and prevents writers from blocking readers and vice-versa (as long as it's ok that readers do not see the most current version of the row). With a table that large and having this much of an impact by concurrent write operations, the impact to tempdb could be huge, and there's no way for us to predict it, so you will need to test it.

Kendra Little has a great intro post from the administration side here:

Implementing Snapshot or Read Committed Snapshot Isolation in SQL Server: A Guide

And Paul White goes into detail about what happens at the query level here, in his series on ACID and the various transaction isolation levels in SQL Server:

Read Committed Snapshot Isolation

Official documentation which you'll want to review, especially to understand how it works and the limitations involved:

Understanding Row Versioning-based Isolation Levels
Choosing Row Versioning-based Isolation Levels
Enabling Row Versioning-based Isolation Levels
Using Row Versioning-based Isolation Levels


Can I create a SQL Job to run on specified dates? I want to create a new SQL job that runs the beginning of every quarter:
01/01
04/01
07/01
10/01
Is this possible? I know i can set it to run every 90 days but that's not exact. I am using SQL Server 2008.  Thanks in advance!

You can't do this using the point-and-click UI, because it is primitive and inflexible, but you can set the job to run once a month, on the first, and put this in your job step:

IF DATEPART(MONTH, GETDATE()) IN (1,4,7,10)
BEGIN
  -- DO THE THINGS!
END

If you have multiple steps and want the whole job to exit, rather than having to put that logic in each step, you can reverse the condition and raise an error, and set the step properties to quit the job on error. If you want more graceful failure, you could add a step at the end that quits the job with success, and make that the next step for the first step on error.

IF DATEPART(MONTH, GETDATE()) NOT IN (1,4,7,10)
BEGIN
  RAISERROR('This is not the beginning of a quarter, silly.',11,1);
END


Delete all data in Postgres database I have created a fresh db dump from a production server with the --data-only and --column-inserts flags, so I only have a bunch of insert statements to insert data when performing a restore on a staging server.

pg_dump -h localhost -U adminuser --data-only --column-inserts maindb > maindb.sql

How do I delete all data in the staging server database first, before restoring the data from the production dump?

I want to delete all data only so I don't have to drop and create the database and all that stuff. I just want to remove data and insert new data that is all.

I don't have the option to drop and create the database for several reasons. I will have to remove all data and just insert only, so whatever it takes to find how to do this, am willing to go for it but need help obviously to start with.

I also need to automate this process. Will automate "dumping data from production db", then "deleting data on staging db", and then "restoring data to staging db". I just need help on the "deleting data on staging db" part.

I am running on PostgreSQL 9.5.2

You don't have to drop the database, it should be enough to drop all the objects in the database. This can be done using

drop owned by adminuser

If you then create the SQL dump including the create table statements (so without the --data-only option) everything should be fine.

You can also remove the --column-inserts then, which will make the import a lot faster.

However, if you do want to delete everything, you can do that with a little dynamic SQL:

do
$$
declare
  l_stmt text;
begin
  select 'truncate ' || string_agg(format('%I.%I', schemaname, tablename), ',')
    into l_stmt
  from pg_tables
  where schemaname in ('public');

  execute l_stmt;
end;
$$

This will truncate all tables in the schema public with a single statement which will also work even if there are many foreign key constraints connecting all tables. If your tables are spread over multiple schemas, you need to add them in the where condition.


How to remove NULL values in multidimensional array? SELECT dt.name,dt.description,dt.odobj,dt.defval,dt.datatypeid
      ,array_agg(array[dtv.description::text,dtv.odobj::text,dtv.name::text,dtv.datatypevalueid::text,dtv.datatypeid::text]) as arrayofdatatypevalues
FROM datatypevalue dtv
FULL JOIN public.datatype as dt on dt.datatypeid = dtv.datatypeid
WHERE dt.name !='Integer' and dt.name !='String' and dt.name !='Boolean'
GROUP BY dt.name,dt.description,dt.odobj,dt.defval,dt.datatypeid;

Returns output like (example with two result rows):

{{NULL,NULL,NULL,NULL,NULL}}
{{NULL,10,xxs,1,21},{NULL,9,xyz,0,21}}

How do I replace this {{NULL,NULL,NULL,NULL,NULL}} with {}?

You can do it the slow and verbose way:

SELECT name, description, odobj, defval, datatypeid
     , CASE WHEN arrayofdatatypevalues = '{{NULL,NULL,NULL,NULL,NULL}}'::text[]
            THEN '{}' ELSE arrayofdatatypevalues END AS arrayofdatatypevalues
FROM  (
   -- your org. query here
   ) sub;

My educated guess, though: you don't actually want FULL [OUTER] JOIN to begin with. Looks like a LEFT [OUTER] JOIN to me. (Basics in the manual here.)

...
FROM   public.datatype dt
LEFT   JOIN datatypevalue dtv USING (datatypeid)
...

If so, transform it into into a LATERAL subquery or just a correlated subquery:

SELECT dt.name, dt.description, dt.odobj, dt.defval, dt.datatypeid
     , ARRAY(
          SELECT ARRAY[dtv.description::text,dtv.odobj::text,dtv.name::text,dtv.datatypevalueid::text,dtv.datatypeid::text]
          FROM   public.datatypevalue dtv
          WHERE  dtv.datatypeid = dt.datatypeid
          ) AS arrayofdatatypevalues
FROM   public.datatype dt
WHERE  dt.name <> ALL ('{Integer,String,Boolean}'::text[]);

Returns {} instead of {{NULL,NULL,NULL,NULL,NULL}} out of the box if no matching row is found in table datatypevalue. But still returns {{NULL,NULL,NULL,NULL,NULL}} for a single rows with all retrieved columns holding an actual NULL value, which can be a very important distinction to make. (Probably not possible with your setup - another guess in want of actual table definitions.)

The need for GROUP BY goes away.
While being at, use a faster ARRAY constructor instead of array_agg(). See:

Why is array_agg() slower than the non-aggregate ARRAY() constructor?

db<>fiddle here for Postgres 12.


How can I only UPDATE if the user was created? I got A cursor with some ifs like this one:

 IF @ROLE='RW'
            BEGIN
                SET @COMMAND ='
                USE '+QUOTENAME(@DATABASE)+'
                CREATE USER '+QUOTENAME(@LOGIN_ACESSO)+' FOR LOGIN '+QUOTENAME(@LOGIN_ACESSO)+' WITH DEFAULT_SCHEMA=[DBO]'+CHAR(13)+CHAR(10)+'
                USE '+QUOTENAME(@DATABASE)+'
                ALTER ROLE [DB_DATAREADER] ADD MEMBER '+QUOTENAME(@LOGIN_ACESSO)+'
                ALTER ROLE [DB_DATAWRITER] ADD MEMBER '+QUOTENAME(@LOGIN_ACESSO)
            END

PRINT @COMMAND
EXECUTE (@COMMAND)

UPDATE TBL_LOGINPERMISSAO SET CRIADO =1 WHERE [ID]=@ID

But obviously, even if the user was not created, I update TBL_LOGINPERMISSAO. How can I update only if the user was created? even copying this update on all ifs, it does not work, because even not creating the user will reach the UPDATE.

is this a case for try/catch?

Taking into account that you need to check the user for a specific database,
you could do something like this?

SET @COMMAND =
'USE '+@DATABASE+'
 SELECT name FROM sys.sysusers WHERE name = '''+@LOGIN_ACESSO+''''
DECLARE @t TABLE (LOGIN_ACESSO NVARCHAR(255))
INSERT INTO @t
EXECUTE  (@COMMAND)
IF exists (select LOGIN_ACESSO from @t)
BEGIN
    UPDATE TBL_LOGINPERMISSAO SET CRIADO =1 WHERE [ID]=@ID
END


Best way to apply a "friend system"-like table? I am making a website that has multiplayer games like chess, poker, etc.

Users will be able to add friends. I was thinking about how could I make this relationship in the database, and I thought of two methods:

1 - Using a row for every friendship
e.g

id   |   friend1   |   friend2

2 - Using only a row per user, adding all friends in a string separated by comma (in a text field maybe?) e.g

|id   |   user    |   friends                |
----------------------------------------------
|1    |    rellfy |   user1,user2,user3,user4|

What would be the best method to use? Using a string only, and parsing it later through the application, or querying for friends directly?

First approach is the SQL approach. You need to make modification though. The relation that you are describing is called many to many - each player can have each other player as friend. To achieve this you need a separate table which connects player with his friends (you just need 2 IDs):

|Player_ID  | Friend_ID|
------------------------
|  1        |  2       |
|  1        |  5       |

This is the better approach if you need make a lot of queries on Player and list of his friends (this will give speed of queries).

If you just need list of names, the other approach is also possible.

So you have to think and decide which is better approach for your needs.


What is object Server? As I was reading article I came across IBM's site about object Server. What actually is Object Server and what are it's uses? Can this be explained for a beginner?

Actually, you question has less to do with "DB2" than it does with one of IBM's main products - IBM i on Power (also known as System i, iSeries, and AS/400 in the past). For whatever reason, IBM chooses to refer to a lot of things as "Objects" within their system. This gets very confusing to hear, because in and of itself, the operating system i5/OS is not particularly object oriented, nor is the programming language (RPG) that comes with the system. That being said, RPG is starting to gain some object-oriented functionality within the last few releases of the language.

The key point of the link you shared is that the Object Server is the piece of the iSeries that allows end users access to reports, etc. (Strangely, a lot of these reports, end-programs, etc. are referred to as objects as mentioned above). Hence the term Object Server.

Now, all this being said, the iSeries does come with a version of DB2. It is called DB2 for i and it is actually integrated into the operating system and the file system. It differs from DB2 LUW (Linux, Unix, Windows) in that it relies on an EBCDIC representation of text rather than ASCII. It also functions a bit different under the covers. Because it is integrated into the OS, the OS takes care of monitoring DB2, so you can actually get away with having no DBA on staff. We did it for years. I've heard similar of other iSeries shops. (And since IBM created the RPG language for the iSeries it integrates incredibly well with DB2 as well.)

Now if it also helps, a physical iSeries server can be divided into several LPARs (logical partitions). Perhaps a "virtual server" if you will. So an LPAR is an "instance" of the iSeries.

Since DB2 for i is integrated into the OS, there is one instance of DB2 per instance of the OS. In other words, each LPAR is an instance of DB2 (and also like THE one and only database at the same time). And unlike DB2 LUW, you cannot have more than one instance on the same server. And lastly, a "library" for DB2 for i, is the equivalent of a schema, and a "file" for DB2 for i, is the equivalent of a table.

Hope this helps clarify your thoughts.


case inside values statement I want to insert data from mysql into a table in postgresql. I want to perform a WHERE NOT EXISTS statement for a particular id.

   # Source data: MySQL
   curr_msql.execute(''' SELECT code, subjectname
                          FROM test_subj ''')

    # Target data: PostgreSQL
    for row in curr_msql:
            curr_psql.execute(''' INSERT INTO subs (
                                                    created, modified,
                                                    subjcode, subjname,
                                                    is_pe_or_nstp)

                                  VALUES (current_timestamp, current_timestamp,
                                          %s, %s,
                                          %s) ''', (row['code'], row['subjectname'],
False))

I want to know how can I replace the "False" to a case statement. Something like

CASE
    WHEN code like '%PE%' or code like '%NSTP%'
    THEN True
    ELSE False
END

I tried to run this code:

for row in curr_msql:
            curr_psql.execute(''' INSERT INTO subs (
                                                    created, modified,
                                                    subjcode, subjname,
                                                    is_pe_or_nstp)

                                  VALUES (current_timestamp, current_timestamp,
                                          %s, %s,
                                          CASE
        WHEN code like '%PE%' or code like '%NSTP%'
        THEN True
        ELSE False
    END) ''', (row['code'], row['subjectname']))

I'm getting IndexError: tuple out of index error message
Any advice pls?

The destination DB is PostgreSQL, source data is from MySQL.

One part of your problem is that execute() tries to interpret the LIKE expressions ('%PE%') as a placeholder.  You have to double all the percent signs, so that the following query in pure SQL (with a pseudocode placeholder)

SELECT ? WHERE 'abc' LIKE '%bc'

turns into

cur.execute("SELECT %s WHERE 'abc' LIKE '%%bc'", ['%s'])
cur.fetchall()
==> [('%s',)]

Since the column code is also from the source table, it needs to be "fed" from the parameters, too.

for row in curr_msql:
    curr_psql.execute('''
        INSERT INTO subs (
                           created, modified,
                           subjcode, subjname,
                           is_pe_or_nstp )
        VALUES (current_timestamp, current_timestamp,
                %s, %s,
                CASE
                    WHEN %s LIKE %s OR %s LIKE %s
                    THEN TRUE
                    ELSE FALSE
                END)
    ''', (row['code'], row['subjectname'],
          row['code'], '%PE%',
          row['code'], '%NSTP%'))

and much cleaner, using named parameters:

for row in curr_msql:
    param = dict(code = row['code'],
                 subjectname = row['subjectname'],
                 str1 = '%PE%',
                 str2 = '%NSTP%')
    curr_psql.execute('''
        INSERT INTO subs (
                           created, modified,
                           subjcode, subjname,
                           is_pe_or_nstp )
        VALUES (current_timestamp, current_timestamp,
                %(code)s, %(subjectname)s,
                CASE
                    WHEN %(code)s LIKE %(str1)s OR %(code)s LIKE %(str2)s
                    THEN TRUE
                    ELSE FALSE
                END)
    ''', param)

Link to psycopg2: Basic module usage for further reading.


What is the MySQL equivalent of Postgres' C collation? I'm working on reimplementing a Postgres schema in MySQL, and one of the things I can't seem to find a clear equivalent for is Postgres' "C" collation.

id TEXT COLLATE "C" NOT NULL

What would be the equivalent collation for MySQL?

C is PostgreSQL's way of saying do not reinterpret the data, order it according to binary byte comparisons. From the docs

  On all platforms, the collations named default, C, and POSIX are available. Additional collations may be available depending on operating system support. The default collation selects the LC_COLLATE and LC_CTYPE values specified at database creation time. The C and POSIX collations both specify “traditional C” behavior, in which only the ASCII letters “A” through “Z” are treated as letters, and sorting is done strictly by character code byte values.

MySQL seems to call it binary the "Binary pseudo charset". Whether or not they're optimized to take advantage of that is another question.

See also

The binary Collation Compared to _bin Collations


Which is better in a master-slave setup to use local queue or write changes to remote instantly? This is more of a a design question

I have a local db (mysql if relevant) for reads and remote master for writes but
wondering if it's better to..

1) Write changes to a local queue and bulk insert to remote master every 1 min or so

2) Write changes immediately to the remote master

Using 1 I [think] I have more control but there is the delay
and it uses 1 connection/thread per outgoing table

but 2 could be any cpu/memory usage and no control, but changes are updated in realtime.

I currently use #1 but now as my project uses more tables
I bulk insert 10+ connections and I am planning for horizontal
scalability and 10+ connections each instance could eventually add up
so please let me know what real/professional developerts/DBA's handle this
as I am just in development stage with no actual [production] experience
and just using common sense/logic currently.

It's fine to write to the master directly. A database does much more for you than just persist your changes to disk. It manages transaction, enforces integrity, etc. If you introduce a queue you introduce a new cog in the machine that can break and a lot of complexity that you need to manage for yourself and that is actually not required in most cases.

I worked with MySQL master servers having thousands of connections all the time and handling thousands of inserts/updates per second without problem. InnoDB is quite efficient if your tables are well designed and you have your configuration properly optimized. There are many ways how tables and my.cnf can be optimized for writes if that's your main concern. Some general tips:

Keep your innodb_log_file_size big enough to keep about an hour
worth of changes.
If you can afford have a large enough server to
keep the hot dataset in innodb_buffer_pool
In InnoDB don't use complex primary keys. For inserts AUTO_INCREMENT primary keys are the best.
Look into table partitioning if you have tables in the tens of gigabytes range.

(These are only the very basics there are much more your can do here)

You should worry about IO usage first because CPU and Memory are less likely to become the bottleneck. Keep an eye on IO utilization to identify when you're likely to hit the ceiling of what your system can do. If your writes are competing for locks it's possible you will experience high CPU usage but that's usually coming from mutex contention not "actual" CPU work. These can be remedied designing your tables and queries in such a way that they don't lock out each other.


AWS aurora (postgres) proper value for the shared_buffers parameter I think it's fairly well known that a normal PostgreSQL database has a starting value of 25% of system memory for its shared_buffers parameter, but I haven't been able to find anything regarding a proper value for an AWS Aurora instance.

Now I would normally use the community value for this parameter, but the existing system is already using a value of 50% of the available memory, and I'm just not certain that this is a good value.

Of course, for Aurora the shared_buffers parameter is unique for each database in the system, as opposed to normal PostgreSQL where this is cluster-wide.

Hence, my confusion. Can anyone provide guidance in this manner?

Standard PostgreSQL runs on a file system and depends on the file system cache in addition to the PostgreSQL buffer cache. Aurora PostgreSQL runs on top of Aurora Storage, which is not a file system and doesn't have a separate cache on the instance. So, with Aurora PostgreSQL we set the default for shared_buffers to 75% of the total memory on the instance. We recommend you look at your specific workload requirements to determine memory requirements for caching your working set, for managing the connections needed, and for other PostgreSQL memory requirements.

Full Disclosure: I am the Product Manager for Aurora PostgreSQL.

-Kevin Jernigan


Backups script completes, but doesn't back up all dbs I'm running sql server 2005 and I've made a simple backup script that backs up all the databases on one server. The script always succeeds, but when I go to check the log or the files, I see it has only backed up around half of my databases. I've tried it on multiple servers and it does the same thing. I've checked the application log for the backups in the eventvwr, however, it tells me to check the application log for more details so I can never find the detailed information I need.

I saw this similar question and tried the solution that was recommended to try and catch errors. However, there were no errors thrown when I run the script.

DECLARE @db_names NVARCHAR(1000)

DECLARE db_cursor CURSOR
    FOR select name from sys.databases
            where name not in ('tempdb')
    order by name

OPEN db_cursor
FETCH NEXT FROM db_cursor into @db_names
WHILE @@FETCH_STATUS = 0
    BEGIN

    BEGIN TRY

        EXECUTE [Full_Backup] @DB_name = @db_names

    END TRY

    BEGIN CATCH

        EXEC spErrorHandling

    END CATCH

FETCH NEXT FROM db_cursor INTO @db_names
END
CLOSE db_cursor
DEALLOCATE db_cursor

I used this sites example for error handling.

-- Declaration statements
DECLARE @Error_Number int
DECLARE @Error_Message varchar(4000)
DECLARE @Error_Severity int
DECLARE @Error_State int
DECLARE @Error_Procedure varchar(200)
DECLARE @Error_Line int
DECLARE @UserName varchar(200)
DECLARE @HostName varchar(200)
DECLARE @Time_Stamp datetime

-- Initialize variables
SELECT @Error_Number = isnull(error_number(),0),
@Error_Message = isnull(error_message(),'NULL Message'),
@Error_Severity = isnull(error_severity(),0),
@Error_State = isnull(error_state(),1),
@Error_Line = isnull(error_line(), 0),
@Error_Procedure = isnull(error_procedure(),''),
@UserName = SUSER_SNAME(),
@HostName = HOST_NAME(),
@Time_Stamp = GETDATE();

-- Insert into the dbo.ErrorHandling table
INSERT INTO dbo.ErrorHandling (Error_Number, Error_Message, Error_Severity, Error_State, Error_Line,
Error_Procedure, UserName, HostName, Time_Stamp)

SELECT @Error_Number, @Error_Message, @Error_Severity, @Error_State, @Error_Line,
@Error_Procedure, @UserName, @HostName, @Time_Stamp

Is there a reason why backups fail silently? Is there better error handling I can be doing?

[Full_Backup]

declare @path varchar(200)
    set @path = 'P:\Backups\' + CONVERT(char(10), GetDate(),126) + '_' + @DB_name + '.bak'

declare @backupStr nvarchar(max)
set @backupStr = 'BACKUP DATABASE [' + @DB_name + '] TO  DISK = N''' + @path+ ''' WITH NOFORMAT, NOINIT,  NAME = N'''+@DB_name+'-Full Database Backup'', SKIP, NOREWIND, NOUNLOAD,  STATS = 10, CHECKSUM

    declare @backupSetId as int
    select @backupSetId = position from msdb..backupset where database_name=N'''+@DB_name+''' and backup_set_id=(select max(backup_set_id) from msdb..backupset where database_name=N'''+@DB_name+''' )
    if @backupSetId is null begin raiserror(N''Verify failed. Backup information for database '''''+@DB_name+''''' not found.'', 16, 1) end
    RESTORE VERIFYONLY FROM  DISK = N''' + @path + ''' WITH  FILE = @backupSetId,  NOUNLOAD,  NOREWIND'

Edited to add the [Full_Backup] sp

The problem may actually be that in using your cursor, you're not encountering errors, but databases are simply getting skipped.  I have had situations in the past where a default cursor will skip records.  I've never been able to determine the cause, but I have found some ways to handle it better in the code.

One is to put some better definition around your cursor declaration.  Since this kind of cursor will always iterate forward, you should declare it as such:

DECLARE db_cursor CURSOR STATIC FAST_FORWARD FOR
SELECT name
FROM sys.databases
WHERE state_desc = 'ONLINE'
    AND name != 'tempdb'
ORDER name

I have found using the FAST_FORWARD option is much more reliable for iterating each item in a data set.  Also, it can have other benefits.

Another approach is to build a WHILE... loop structure to handle this.  This does not rely on the cursor engine in SQL Server to handle your logic and can give you better reliability for getting all your databases(this is incomplete code, you will need to add backup/error handling logic to make it work):

DECLARE @work table
(dbid int,
 backupdate datetime,
 errormsg varchar(max));

DECLARE @currdb int

INSERT INTO @work (dbid)
SELECT
    database_id
FROM
    sys.databases a
WHERE
    state_desc = 'ONLINE'
    AND name != 'tempdb'

WHILE EXISTS (SELECT 1 FROM @work WHERE backupdate IS NULL AND errormsg IS NULL)
BEGIN
    SELECT top 1 @currdb = dbid
    FROM @work
    WHERE backupdate IS NULL AND errormsg IS NULL;

    BEGIN TRY
        --Execute Backup Code, then update @work table
        UPDATE @work
        SET backupdate = GETDATE()
        WHERE dbid = @currdb
    END TRY
    BEGIN CATCH
        --Catch error, put message in @work table
        update @work
        set errormsg=ERROR_MESSAGE()
        where dbid = @currdb;
    END CATCH

END

These are two approaches if you decide to move forward with writing your own scripts for backups.  Overall, I would strongly recommend you check out Ola Hallengren's backup scripts.  They are highly recommended by members of the community, well documented, and thoroughly tested.


How to get session parameters changed by the user in Oracle? Oracle 11.2.0.3 .

I need to identify all "alter session" changed for a specific session.
There is some v$session_* where I able to get this information?

My objective is part of investigation what I doing about a problem of sessions hanging at compile time... from Forms Builder.

I already got a trace of the session and have some suspicious over a bug documented at Oracle Support... but I want more details about this session.

This one is provided by one of the oracle gurus

There are several Initialization Parameters that influence the Optimizer and that can be modified with ALTER SESSION. We can see whether certain sessions have settings different from the System-wide settings with a query like

QL> set lines 200
col name for a40
col username for a10
col value for a40
select a.sid,c.username,a.name,a.value
from v$ses_optimizer_env a
join v$sys_optimizer_env b on a.id=b.id
join v$session c on a.sid=c.sid
where a.value<>b.value
and c.username is not null
and c.username not in ('SYS','SYSTEM','DBSNMP')
order by a.sid,a.name;


Redundant data or UNION I couldn't come up with better title.

I'm preparing data structure for GDPR and I ran into one problem.
I have two types of legal basis for storing the personal data:

Law
User specific (consent)

So some of the purposes for collecting data apply to all users (law) and some of them apply only to users which gave consent.

I have relation table Consent between User and DataCollectionPurpose.

Should I fill it only with data where user actually gave consent.
Should I fill it with "fake" data because law applies to user whether they gave consent or not.

Solution 1 will make me write two separate queries and union them, solution 2 will fill table with loads of redundant data.

EDIT: Added code for testing

/*
CREATE TABLE [Rule]
(
      ID int
      , RuleName varchar(30)
      , IsGlobal bit
);

CREATE TABLE Person
(
      ID int
      , [Name] varchar(30)
);

CREATE TABLE PersonRule
(
      PersonID int
      , RuleID int
);

INSERT INTO dbo.Person (ID, [Name])
    VALUES
      (1, 'Tom')
     , (2, 'Richard')
     , (3, 'Harry')

INSERT INTO dbo.[Rule] (ID, RuleName, IsGlobal)
    VALUES
      (1, 'LocalRule', 0)
    , (2, 'GlobalRule', 1);

INSERT INTO dbo.PersonRule (PersonID, RuleID)
    VALUES
     (1, 1)
    , (2, 1)

    /*
    drop table PersonRule;
    drop table Person;
    drop table [Rule];
    */
*/

SELECT P.*, r.* FROM dbo.Person p
JOIN dbo.PersonRule pr ON P.ID = pr.PersonID
JOIN dbo.[Rule] r ON pr.RuleID = r.ID
UNION ALL
SELECT P.*, r.* FROM dbo.[Rule] r
OUTER APPLY dbo.Person p
WHERE r.IsGlobal = 1

You can use solution 2, but you should have a stronger INSERT logic validation, to check whether data has already a policy or not.

So it would be something like:

check for law rules first (applies to all your users) check for user
consent rule (user specific, so you have to relate it to userID)

So you can use tables: User, DataCollectionPurpose(a flag for law or user consent) and Consent(so you will join to it where your flag in the other table is for user consent)

Hope this helps!


How to query for something like "last active" per column in Access Let's Say i have two Tables, let's call them

PC-Info
| ID | Hostname  | Location          |
|:---|----------:|:-----------------:|
| 1  | Hostname1 | This Room         |
| 2  | Hostname2 | That Room         |
| 3  | Hostname3 | The other Room    |
| 4  | Hostname4 | Somewhere Offsite |

And

IsUp
| Timestamp  | Hostname1   | Hostname2   |
|:-----------|------------:|:-----------:|
| 19.02.2017 | up          | down        |
| 20.02.2017 | down        | down        |
| 21.02.2017 | down        | up          |
| 22.02.2017 | up          | down        |

What would a query in Access look like to get the last time the host was "up", bundeled with the other Info? Or is this database flawed from the ground up?

Since the amount of hosts ist fixed and the timestamps will increase over time, I thought it was the better idea to have fixed columns and add rows per timestamp.

I would have....

| Hostname | Timestamp | Status |
|----------|-----------|--------|
| Host1    | 19.02.2017| up     |
| Host2    | 19.02.2017| down   |
| Host3    | 19.01.2017| down   |

Then you could have another table...

| ID | Hostname | Location       |
|----|----------|----------------|
| 1  | Host1    | Living Room    |
| 2  | Host2    | Bedroom        |

When you run queries, it's easy to find all of Host1's activities, or you could easily find all the activities for 19.02.2017. Yes you have a million rows, but that doesn't matter.


Postgresql rds: relation .... does not exists This seems to be a question that is asked alot all of the answers that I have read do not seem to pertain to this situation.  I maybe wrong, I am a newbie but here is the question.
I am using  psl 11 in amazon rds.  I have one db on the instance with 2 schemas.  It is running slow in testing so I was going to run the  missing index query
SELECT   relname
    ,seq_scan - idx_scan AS too_much_seq
    ,CASE
        WHEN
          seq_scan - coalesce(idx_scan, 0) > 0
        THEN
          'Missing Index?'
        ELSE
          'OK'
      END
     ,pg_relation_size(relname::regclass) AS rel_size, seq_scan, idx_scan

FROM    pg_stat_all_tables
WHERE   schemaname IN( 'schemaname')
AND pg_relation_size(relname::regclass) > 80000
ORDER BY  too_much_seq DESC;
When I run it in this new environment I am getting a relation does not exists.
I have checked permissions, ownership, lowercase, I can see the table, run queries against the table, however I can't seem to get this query to work.  If I do a SELECT * from pg_stat_all_tables  I can see all of the tables that do not exists in he other query.
I am not sure what is wrong. I am pretty sure it is in the   pg_relation_size(relname::regclass)  section, I think that is where it is not finding the relation,  however I have been unable to find documentation on this function that would appear to shed light on my issue.
Any assistance would be appreciated.

jjanes answered the question in his comment.
Why use the convolution relname::regclass when the relid is directly available in the table?
using the relid  fixed the issue


Optimize query with similar fields I have the below schema, fields owner_id,billing_id,tech_id,admin_id on carts table are pointing to contacts table. As you can see from the query result I have to JOIN for each field .Is there a way to avoid?

SELECT c.domain, c.period, co.name AS owner, co1.name AS admin, co2.name AS tech, co3.name AS billing, h.nameserver
FROM carts c
LEFT JOIN contacts co ON c.owner_id = co.id
LEFT JOIN contacts co1 ON c.admin_id = co1.id
LEFT JOIN contacts co2 ON c.tech_id = co2.id
LEFT JOIN contacts co3 ON c.billing_id = co3.id
LEFT JOIN HOSTS h ON c.host_id = h.id
WHERE c.user_id =1

You can get the same results with just one join and pivoting .

SELECT c.domain, c.period, h.nameserver,
MAX(CASE WHEN co.id=c.owner_id THEN co.name END) AS owner,
MAX(CASE WHEN co.id=c.admin_id THEN co.name END) AS admin,
MAX(CASE WHEN co.id=c.tech_id THEN co.name END) AS tech,
MAX(CASE WHEN co.id=c.billing_id THEN co.name END)  AS billing
FROM carts c
LEFT JOIN contacts co ON co.id IN (c.owner_id , c.admin_id,c.tech_id,c.billing_id)

LEFT JOIN HOSTS h ON c.host_id = h.id
WHERE c.user_id =1
GROUP BY c.domain,c.period,h.nameserver

However, check execution plan for both versions, sometimes multiple joins may be faster than such approach.


Trigger based on linked table OK I have 3 tables:

client:

ClientID (int)auto-inc

clientName (varchar)

tnt_group:

groupID (int)auto-inc

ClientID (int)foreign-key

tnt_item:

itemID (int)auto-inc

itemNo (int)

groupID (int)foreign-key

Now, I need to generate an itemNo for each record in tnt_item, starting from 1 for each ClientID so each client see's their item count starting from 1. I have tried adding a trigger as follows:

BEGIN
    SET NEW.itemNo = (
        SELECT IF(t.itemNo IS NULL, 1, IFNULL(MAX(t.itemNo), 0) + 1)
        FROM (
            SELECT IFNULL(i.itemNo, 0) AS itemNo, g.groupID, g.ClientID, cg.ClientID AS cgClient, cg.groupID  AS cgGroup
            FROM tnt_group cg
            INNER JOIN tnt_group g ON cg.ClientID = g.ClientID
            INNER JOIN tnt_item i ON i.groupID = g.groupID
        ) AS t WHERE t.cgGroup = NEW.groupID
        GROUP BY cgClient
    );
END

This is working, in the case where a client already has at least 1 asset, it gets the max itemNo then adds one. where it is failing, is if a client does not have any items yet, no matter what I do, it sets the first itemNo as NULL

Any tips on what i am doing wrong?

CREATE TRIGGER tr
BEFORE INSERT
ON tnt_item
FOR EACH ROW
SET NEW.itemNo = 1 + COALESCE((SELECT MAX(t1.itemNo)
                               FROM tnt_item t1, tnt_group t2, tnt_group t3
                               WHERE t1.groupID = t2.groupID
                                 AND t2.ClientID = t3.ClientID
                                 AND t3.groupID = NEW.groupID), 0);

fiddle


Breaking up long running query into batches runs fine for a while and then produces fatal error I'm using a variation of Take Care When Scripting Batches by Michael J. Swart, which details using "careful batching" to delete records in batches from a table that contains 38MM records. The script below runs for a while and shows results until it hits about the 30MM record mark (30 minutes in) when I get an error:

  Msg 0, Level 11, State 0, Line 0
  A severe error occurred on the current command.
  The results, if any, should be discarded.

Part of the issue is that each week I get a new feed of records and these records are added to the repository of existing records from previous feeds.  A new record can be received that dictates that an older record be deleted.  The older record can be anywhere in the table all the way back to the oldest feed.

So when this script loops through the table, it might only be looking at 100,000 records per batch on one end of the equation (the "latest" alias), but it still has to search the entire table for the existence of the older record (the "previous" alias) which would need to be deleted, so I'm thinking the performance gains that I might see in a normal delete situation using this approach are eliminated in this scenario.  Should I abandon this approach?

Before I tried this approach I was just using an update statement without the batch, and I was getting a transaction log error.  It would run for ~4 hours and then fail.

DECLARE
@latestFeed INT = 90,
@LargestKeyProcessed INT = -1,
@NextBatchMax INT = 1,
@RC INT = (SELECT MAX(record_id) FROM table_B);

WHILE (@NextBatchMax < @RC)
BEGIN
SELECT TOP (100000) @NextBatchMax = record_id
FROM table_B
WHERE record_id > @LargestKeyProcessed
ORDER BY record_id ASC;

delete previous
output Deleted.*
into table_A
from table_B as previous
inner join table_B as latest on previous.field_1 = latest.field_1
    and previous.field_2 = latest.field_2
    and previous.field_3 = latest.field_3
where previous.feed_id < latest.feed_id
    and previous.condition_1 in ('1', '2')
    and latest.condition_1 = '3'
    and latest.feed_id = 90
    AND latest.record_id > @LargestKeyProcessed
    AND latest.record_id <= @NextBatchMax;

delete previous
output Deleted.*
into table_A
from table_B as previous
inner join table_B as latest on previous.field_1 = latest.field_1
    and previous.field_2 = latest.field_2
    and previous.field_3 = latest.field_3
where previous.feed_id < latest.feed_id
    and previous.condition_1 in ('1', '2')
    and latest.condition_1 = '2'
    and latest.feed_id = 90
    AND latest.record_id > @LargestKeyProcessed
    AND latest.record_id <= @NextBatchMax;
SET @LargestKeyProcessed = @NextBatchMax;
END

Since these aren't my production tables, just mirrors of them, I truncated them and re-inserted all the records again.  I then removed the second DELETE statement shown in my code, so that it was at least more simple.  This time it ran completely without the severe error.

NEXT ATTEMPT

I'm adding a transaction to the while loop so that with each iteration it commits the transaction.  I'm also reducing the batch size to 50,000 to see if that will help at all with the speed of the processing.

It also occurred to me that there's no reason to include the entire table in this.  By that I mean, I know that the only feed that will contain records triggering the delete of a previous record are in the latest feed.  So I'm setting the starting value of @LargestKeyProcessed like so:

@LargestKeyProcessed INT = (SELECT MIN(record_id) - 1
FROM Table_B WHERE [feed_id] = 90),

While the script will still have to look through the rest of the table for the actual records to delete, there's no reason it needs to look through the entire table to find the records triggering a delete.

I ran DBCC CHECKDB. It reported:

  CHECKDB found 0 allocation errors and 0 consistency errors in database

Jon,

One possible option with the statement you posted would be to put an explicit begin transaction before the first delete statement and a commit statement right before the set of the @LargestKeyProcessed variable. This would commit the delete before you move to next loop iteration and should help with the log file as it's breaking the batch up into small transactions instead one one large one that is generated implicitly by the loop.

Additionally if your database is in FULL recovery mode you will need to take frequent backups of the transaction log to allow the transactions to be removed,once the have committed or rolled back, to prevent the log file from needing to grow. If your database is in SIMPLE recovery mode the transactions will get overwritten once the transaction commits or rolls back.


Can't upgrade a SQL Express to SQL Standard I have a 64 bit SQL Server 2008 Express R2 installed and I cannot upgrade it to a fully licensed 2014 Standard. When I try to update to SP 4, I get this issue:

If I try to run the upgrade edition, notice there is no "Instance Select" option for me to select my instance:

However, I am able to connect and run to the database, and get the properties:

So my question is, I'm able to use the existing SQL express instance, but I cannot upgrade the service pack or upgrade the edition. I'm at a loss here. Any thoughts? Below is the summary of the install log:

Overall summary:
  Final result:                  Passed
  Exit code (Decimal):           0
  Start time:                    2015-08-11 12:12:19
  End time:                      2015-08-11 12:19:27
  Requested action:              EditionUpgrade

Machine Properties:
  Machine name:                  xxxxxxxx
  Machine processor count:       8
  OS version:                    Windows 8
  OS service pack:
  OS region:                     United States
  OS language:                   English (United States)
  OS architecture:               x64
  Process architecture:          64 Bit
  OS clustered:                  No

Product features discovered:
  Product              Instance             Instance ID                    Feature                                  Language             Edition              Version         Clustered  Configured
  SQL Server 2008 R2   xxxxDBSERVER         MSSQL10_50.xxxxxBSERVER        Database Engine Services                 1033                 Express Edition      10.50.1600.1    No         Yes
  SQL Server 2008 R2                                                       Management Tools - Basic                 1033                 Express Edition      10.50.1600.1    No         Yes

Package properties:
  Description:                   Microsoft SQL Server 2014
  ProductName:                   SQL Server 2014
  Type:                          RTM
  Version:                       12
  SPLevel:                       0
  Installation location:         E:\x64\setup\
  Installation edition:          Standard

User Input Settings:
  ACTION:                        EditionUpgrade
  CONFIGURATIONFILE:             C:\Program Files\Microsoft SQL Server\120\Setup Bootstrap\Log\20150811_121219\ConfigurationFile.ini
  ENU:                           true
  FEATURES:                      BROWSER
  HELP:                          false
  IACCEPTSQLSERVERLICENSETERMS:  true
  INDICATEPROGRESS:              false
  INSTANCENAME:                  <empty>
  PID:                           *****
  QUIET:                         false
  QUIETSIMPLE:                   false
  UIMODE:                        Normal
  X86:                           false

  Configuration file:            C:\Program Files\Microsoft SQL Server\120\Setup Bootstrap\Log\20150811_121219\ConfigurationFile.ini

Detailed results:
  Feature:                       SQL Browser
  Status:                        Passed

Rules with failures:

Global rules:

There are no scenario-specific rules.

Rules report file:               C:\Program Files\Microsoft SQL Server\120\Setup Bootstrap\Log\20150811_121219\SystemConfigurationCheck_Report.htm

Your problem is you are trying to install Service Pack 4 for SQL Server 2008 (the original release, not R2) on a SQL Server 2008 R2 instance.

Despite the similar names, SQL Server 2008 and SQL Server 2008 R2 are different releases, with different Service Packs.

Apply the correct Service Pack for SQL Server 2008 R2 (the latest is SQL Server 2008 R2 Service Pack 3), then you should be able to upgrade to 2014 Standard with no problem.


pg_repack not organizing table by column I'm using pg_repack to order one table by a timestamp column, however I'm finding that after running pg_repack command it's not ordered at all.

I'm using this command

pg_repack --jobs 4 -t table_with_dates -e -o "start_time asc"

After about 10 minutes it finishes but when I query the first page of the table there is no order over "start_time" column.

I'm using postgresql 9.6.3 and pg_repack 1.4.0

Is there something i'm missing here so I can get the correct data ordering?

Edit 1:
Table definition

CREATE TABLE customer_service (
    customer_service_id character varying(32) NOT NULL,
    store_id character varying(32) NOT NULL,
    start_time timestamp without time zone NOT NULL,
    end_time timestamp without time zone NOT NULL,
    customer_id character varying(32) NOT NULL,
    service_id character varying(32) NOT NULL,
    CONSTRAINT customer_service_key PRIMARY KEY (customer_service_id),
);
CREATE INDEX customer_service_start_idx
  ON public.customer_service
  USING btree
  (start_time);

 CREATE INDEX customer_service_customer_idx
  ON public.customer_service
  USING btree(customer_id);

CREATE INDEX customer_service_service_idx
  ON public.customer_service
  USING btree
  (service_id);

SELECT performed by pg_repack:

LOG: (query) CREATE TABLE repack.table_6527203 WITH (oids = false) TABLESPACE pg_default AS SELECT customer_service_id, store_id, start_time, end_time, customer_id, service_id
FROM ONLY customer_service
ORDER BY start_time WITH NO DATA

LOG: (query) INSERT INTO repack.table_6527203 SELECT customer_service_id, store_id, start_time, end_time, customer_id, service_id
FROM ONLY customer_service

Seems like the insert into is not ordering the data?, as the order by is only present on the create table command

Edit 2:
If I run

WITH dates_per_page AS (
  SELECT (ctid::text::point)[0]::bigint AS page_number, COUNT(DISTINCT date(start_time))
  FROM customer_service
  GROUP BY 1
) SELECT avg(count) FROM dates_per_page;

I get 15.340 as the result, I would expect a number closer to 1 here...

This problem is fixed on the version 1.4.1 of pg_repack.

github issue for reference


Find the number of rows in a table from metadata I want to write a single select statement to determine the number of rows in a set of tables in Sybase IQ.

In SAS, I write this as

CREATE TABLE dsnobs as
SELECT libname,memname,nlobs, datetime() format DATETIME16. as timestamp
FROM dictionary.tables
WHERE (libname=upcase('MY_LIB') & memname IN ('TABLE_1', 'TABLE_2', 'TABLE_3'));

For your information,

a SAS libname is something like a table creator or table space
the dictionary are similar to IQ SYS tables
memname is just the name of the table

Can I do the same thing in IQ?

An approximate solution, or an indication on the space used would suit me too.

For SybaseIQ 16 (SAP IQ 16.0 SP08) use system viewSYSTAB as:

select * from SYSTAB

Here is a link to SyBase IQ v16 documentation. Page 918 (correction to my comment above) says:

  Each row of the SYSTAB system view describes one table or view in the
  database. Additional information for views  can be found in the
  SYSVIEW system view. The underlying system table for this view is
  ISYSTAB.

  Column:      count     Datatype:    UNSIGNED BIGINT     Description:
  The number of rows in the table or mate rialized view. This value is
  updated during each successful checkpoint. This number is used to
  optimize database access. The count is always 0 for a
  non-materialized view or remote table.


How to make SQL query to calculate the range from permutation of 123 ex: select 123 from dual; --123!

Result

123
 132
 213
 231
 321
 312

6 rows return

Maybe this:

WITH Nums(Num) AS (
    SELECT 1 UNION SELECT 2 UNION SELECT 3
)
SELECT *
FROM Nums x
CROSS JOIN Nums y
CROSS JOIN Nums z
WHERE x.Num <> y.Num
AND x.Num <> z.Num
AND y.Num <> z.Num
ORDER BY x.Num, y.Num, z.Num

Assumes you are using mysql 8+ with CTEs. Also I have not tested this on mysql, only SQL Server, but hopefully the syntax is the same. Also note your 2nd to last and last results appear to be out of order, unsure if this was intentional.


Modify Backup Directory Path Ola Maintenance Script I have configured backup using SQL Server Maintenance Solution by Ola (https://ola.hallengren.com).

In the backup job, I specified BackupDirectory as a shared folder (eg: \comp1\path). It worked fine, but now I need to change the path. Assuming that I don't want to modify/recreate the jobs, is there a way to modify the path inside the sql jobs?

You can search the command column of the sysjobsteps table for the path you have in your current jobs with the following statement:

SELECT * FROM msdb.dbo.sysjobsteps AS s WHERE s.command LIKE '%\\comp1\path%'

You should then be able to update the sysjobsteps with the following statement:

USE msdb
GO
UPDATE sysjobsteps SET command = REPLACE(command, '<your_old_path_here>', '<your_new_path_here>')

You have to be careful to run the SELECT first to check if the correct commands are being returned. You could also backup the msdb before you start as a precaution.

To be on the safer side, you can right click a job and "Script Job As | Drop and Create To | New Query Editor Window" and then modify the path and execute.

Be sure to remove the line containing @schedule_uid=N'<GUID>' in the sp_add_jobschedule part before you attempt to recreate the job.


What is the name of this property in (SQL?) Databases? Suppose we query Clients Table by Date of Birth, and one of the returned results being a record having client id = CID1234.

Now if we query the same table for client id = CID1234 we should get the record with client id = CID1234 (maybe client id field is primary key or not, that does not matter). Nobody expects that this query to return no results found.

Edit : Trying to make it clear, lets say the returned record had a fieldX = XYZ, now querying on Table with the fieldX = XYZ should also include a record with client Id= CID1234 , if none of the returned records include such a record then it seems something is wrong.

Although this intuitively makes sense, my question is what is the name of this property and is it specific to SQL databases, or a whole category of Databases e.g. Non-SQL etc.

Is there a name for data systems that have such a property? Can this be found within Lambda calculus?

My original problem is SOA related, but knowing this a property in SQL databases made me ask it here, the original question is posted here : https://stackoverflow.com/questions/41215546/is-there-a-principal-that-requiers-total-of-possible-enumeration-results-to-add

If that value is unique and is used to identify a single row in a table ( in your example to uniquely identify a single client record) it would typically be considered a unique or primary key.

If it has some meaning and you already have a separate primary key it could could also be described as a business key or natural key.

Without a constraint to enforce uniqueness you cant guarantee the data is unique.  You should be careful to avoid developing with that as an assumption.


How to merge records for query set We have a question related to a DB query :

CREATE TABLE `Action` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `emp_Id` int(10) unsigned NOT NULL,
  `name` varchar(60) NOT NULL,
  `updated_action_at` datetime(3) DEFAULT NULL,
  `created_At` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `notes` varchar(400) DEFAULT NULL,
  PRIMARY KEY (`id`),

  KEY `action_empId_fk` (`emp_Id`),

  CONSTRAINT `action_empId_fk` FOREIGN KEY (`emp_Id`) REFERENCES `Employee` (`id`) ON DELETE CASCADE,

) ENGINE=InnoDB AUTO_INCREMENT=502004 DEFAULT CHARSET=latin1

CREATE TABLE `ActionAssignedTo` (
  `action_Id` int(10) unsigned DEFAULT NULL,
  `emp_Id` int(10) unsigned DEFAULT NULL,
  KEY `actionassignedto_emp_id_foreign` (`emp_Id`),
  KEY `actionassignedto_action_id_foreign` (`action_Id`),
  CONSTRAINT `ActionAssignedTo_ibfk_1` FOREIGN KEY (`emp_Id`) REFERENCES `Employee` (`id`) ON DELETE CASCADE,
  CONSTRAINT `ActionAssignedTo_ibfk_2` FOREIGN KEY (`action_Id`) REFERENCES `Action` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1

CREATE TABLE `Employee` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `vendor_Id` int(10) unsigned DEFAULT NULL,
  `name` varchar(40) NOT NULL,
  `mobile_Number` varchar(15) NOT NULL,
  `active` tinyint(1) DEFAULT '1',
  `updated_At` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `created_At` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `employee_vendor_id_foreign` (`vendor_Id`),
  CONSTRAINT `employee_vendor_id_foreign` FOREIGN KEY (`vendor_Id`) REFERENCES `Vendor` (`vendor_Id`)
) ENGINE=InnoDB AUTO_INCREMENT=511 DEFAULT CHARSET=latin1

We want to find the action created by employee with Action.empId = 4 along with the list of Assignee (an action can be assigned to multiple people)

We have the following query :

Select     notes,
           Action.id,
           AssigneeName.name,
           ActionAssignedTo.emp_Id
from       Employee
inner join Action
on         (Action.emp_Id = Employee.id
and        Action.emp_Id in (select Employee.id
                             from Employee
                             where Employee.vendor_Id = 1))
inner      join ActionAssignedTo
on         Action.id =  ActionAssignedTo.action_Id
inner join Employee as AssigneeName
on         ActionAssignedTo.emp_Id =  AssigneeName.id
where      Action.emp_Id = 4

Now, lets say if Action with id = 10 was assigned to 2 people : John (empId : 2) And Jack (empId : 5) --> the above query returns following results :

(columns from the query)

notes                       | Action.id | AssigneeName.name | ActionAssignedTo.emp_Id
1. Finish Designing Queries | 10        | John              | 2
2. Finish Designing Queries | 10        | Jack              | 5

If we observe the above results the first 2 columns get repeated for the Assignees for SAME ACTION ID  --> is there a way we can merge them something like

1. Finish Designing Queries |10         | { John : 2  , Jack  : 5}

Basically records being merged into one.

You can get it by using group_concat()

create table t (notes varchar(200), id int, name varchar(100), emp_Id int);

insert into t values ('1. Finish Designing Queries', 10, 'John', 2);
insert into t values ('2. Finish Designing Queries', 10, 'Jack', 5);
insert into t values ('3. Other note', 20, 'Jack', 5);

select notes, id, group_concat(name, ':', emp_id)
from   t
group by id;

notes                       | id | group_concat(name, ':', emp_id)
:-------------------------- | -: | :------------------------------
1. Finish Designing Queries | 10 | John:2,Jack:5
3. Other note               | 20 | Jack:5

dbfiddle here


Is there a way to DENY CRUD operations on connections from linked server? I have a SQL Server instance, RPT1 (2008 R2), that was set up for "power users" to run their own queries. This server is linked to our production reporting server, PRD1 (SQL Server 2008 instance), with views (on RPT1) that query the linked server (PRD1) directly.

The linked server has been created with an account that requires the ability to CRUD on the server (for report auditing, and such).

As the users gain sophistication, they also request greater permissions (e.g. to create their own views). I'd like to ensure that they are not going to be doing any CRUD operations on the linked server, but the account with which it was linked has those permissions.

The first resolution path that I see is to create a different user, with lower permissions, and re-link the server.

My second path is to see if there's a way to explicitly DENY CRUD on the PRD1 server for connections coming from a linked server.

Is it possible to explicitly deny CRUD only from the link? Or should I simply set about re-linking the server with a different account? Is there another obvious solution that I'm overlooking?

Have you had a look at setting up a Server wide DDL trigger? I have used this successfully on several production servers to block unauthorized changes made by developers. I joined a company where ALL developers had full SA access.

When we tried to change their rights we had a full blown revolution as the developers all of a sudden 'could not do their work'. So I used a server wide DDL trigger to limit what they could do. It was very simple.

I created a table called AuthorizedDDLUser which had a very simple structure something like this:

 CREATE TABLE [dbo].[AuthorizedDDLUser](
    [Username] [nvarchar](256) NOT NULL,
    CONSTRAINT [PK_AuthorizedDDLUser] PRIMARY KEY CLUSTERED
    (
      [Username] ASC
     )
    WITH (
           PAD_INDEX  = OFF
           , STATISTICS_NORECOMPUTE  = OFF
           , IGNORE_DUP_KEY = OFF
           , ALLOW_ROW_LOCKS  = ON
           , ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
    ) ON [PRIMARY]

  GO

I then inserted the Login names of the developers which I would allow to make changes(i.e. the DBA team and Senior SQL developers).

I then created a server wide DDL trigger similar to this:

CREATE TRIGGER [block_ddl]
ON SERVER
FOR CREATE_PROCEDURE,ALTER_PROCEDURE, DROP_PROCEDURE,
    CREATE_TABLE,ALTER_TABLE, DROP_TABLE,
    CREATE_FUNCTION,ALTER_FUNCTION, DROP_FUNCTION,
    CREATE_INDEX,ALTER_INDEX, DROP_INDEX,
    CREATE_VIEW,ALTER_VIEW, DROP_VIEW,
    CREATE_TRIGGER,ALTER_TRIGGER, DROP_TRIGGER
AS
    IF NOT EXISTS(SELECT UserName FROM AuthorizedDDLUser WHERE UserName=CURRENT_USER)
    BEGIN
            //RAISE CUSTOM ERROR MESSAGE HERE AND ROLLBACK THE TRANSACTION
            // ROLLING BACK EFFECTIVELY CANCELS THE DDL STATEMENT
            ROLLBACK TRANSACTION --
    END

My implementation was a little bit more involved as it also did auditing. However I am trying to keep it simple here.

So in a nutshell you can selectively limit what the linked user can do on the system. To really get started with this I would suggest your read the following articles.

Using a DDL Trigger to Block Schema Changes in SQL Server: http://www.mssqltips.com/sqlservertip/2646/using-a-ddl-trigger-to-block-schema-changes-in-sql-server/
Auditing DDL (Create, Alter, Drop) Commands in SQL Server 2005: http://www.mssqltips.com/sqlservertip/1006/auditing-ddl-create-alter-drop-commands-in-sql-server-2005/
DDL Event List: http://msdn.microsoft.com/en-us/ms189540.aspx
DDL Triggers: http://msdn.microsoft.com/en-us/library/ms175941.aspx
Using the EventData() Function with DDL triggers in SQL Server 2005: http://www.mssqltips.com/sqlservertip/1571/using-the-eventdata-function-with-ddl-triggers-in-sql-server-2005/

So in my case the developers could still do pretty much anything except mess up my system. Eventually we got them weaned off SA access and my life became normal again thanks to DDL triggers.

Hope that helps!

UPDATE:

Thanks to Martin Smith for pointing out that these are after triggers. Something I did not realise until now (you learn everyday). The MSDN article is very confusing in that it indicates it is both a before and after trigger. So yes this could open some potential for performance issues if like Martin said you rollback a index build on a rather large table. However if you are just going to block view and tables from being created then it should be ok.

So use wisely.

They really need to fix this as it limits the usability of this feature.


Generate wide table from lookup values I have a wide table that looks like this:

Table Windspeed
Timestamp           A-004   A-005a  A-006a ... (column 161) E-009
09-Apr-19 00:00:00  13.09   13.95   10.16                   11.71
09-Apr-19 00:05:00  12.17   11.98   12.54                   10.66

I need to generate a new table in the same format, but replace the column values from a lookup table that looks like this:

Table Powercurve
WindSpeedBin    Power_WdSpd Turb_Num
...
9.5             1216.9      1
10              1362.9      1
10.5            1476.3      1
11              1597        1
11.5            1701.5      1
12              1798.3      1
12.5            1828.5      1
13              1840        1
13.5            1846.1      1
...
9.5             1211.9      2
10              1342.9      2
10.5            1436.3      2
11              1517        2
11.5            1691.5      2
12              1713.3      2
12.5            1801.5      2
13              1833.1      2
13.5            1846.1      2
...

And finally, a third table that looks like this:

Table NameLookup
TName   Tag     TNum
A-004   A04     1
A-005a  A05     2
A-006a  A06     3
A-008a  A08     4
A-009   A09     5
...
E-009   E09     162

I need to generate a table that takes the values from table 1 (Windspeed), looks up the values in table 2 (Powercurve) (round to the nearest .5 to match WindSpeedBin), using the column names to lookup the Turb_Num value. Any ideas how to go about this? the end result would look something like this:

Table Expected_Production
Timestamp           A-004   A-005a  A-006a ... (column 161) E-009
09-Apr-19 00:00:00  1840    1846.1  1528.5                  1780.6
09-Apr-19 00:05:00  1828.5  1713.3  1843.9                  1614.5
...

This is for Microsoft SQL Server 2017

This should work for you.  At least for the entries in the PowerCurve table that I copied over, it works correctly.  This should get you close.  See comments in the code for more details.

Assistance from https://stackoverflow.com/questions/9873990/round-to-5-or-1-0-in-sql on how to round to nearest .5/0.

/** FIRST - we setup the data and tables we need.

    Please note that I didn't copy the entire table of dbo.PowerCurve
    across, so my output is not as complete as yours, but this should
    get you close.
    */

DROP TABLE IF EXISTS dbo.Windspeed
DROP TABLE IF EXISTS dbo.PowerCurve
DROP TABLE IF EXISTS dbo.NameLookup

CREATE TABLE dbo.Windspeed
    (
    [TimeStamp] DATETIME NOT NULL
    , [A-004] DECIMAL(5,2) NOT NULL
    , [A-005a] DECIMAL(5,2) NOT NULL
    , [A-006a] DECIMAL(5,2) NOT NULL
    , [E-009] DECIMAL(5,2) NOT NULL
    )

INSERT INTO dbo.Windspeed
([TimeStamp], [A-004], [A-005a], [A-006a], [E-009])
VALUES ('4/9/2019 00:00:00', 13.09, 13.95, 10.16, 11.71)
    , ('4/9/2019 00:05:00', 12.17, 11.98, 12.54,10.66)

CREATE TABLE dbo.PowerCurve
    (
    WindSpeedBIN DECIMAL(5,2) NOT NULL
    , Power_Wdspd DECIMAL(10,2) NOT NULL
    , Turb_Num INT NOT NULL
    )

INSERT INTO dbo.PowerCurve
(WindSpeedBIN, Power_Wdspd, Turb_Num)
VALUES (13.0, 1840, 1)
    , (13.5, 1846.1, 1)
    , (13.0, 1833.1, 2)
    , (13.5, 1846.1, 2)
    , (12.0, 1798.3, 1)
    , (12.5, 1828.5, 1)
    , (12.0, 1713.3, 2)
    , (12.5, 1801.5, 2)

CREATE TABLE dbo.NameLookup
    (
    TName VARCHAR(10) NOT NULL
    , Tag VARCHAR(10) NOT NULL
    , TNum INT NOT NULL
    )

INSERT INTO dbo.NameLookup
    (TName, Tag, Tnum)
VALUES ('A-004', 'A04', 1)
    , ('A-005a', 'A05', 2)
    , ('A-006a', 'A06', 3)
    , ('A-008a', 'A08', 4)
    , ('E-009', 'E09', 5)

Get the data

/** GET THE DATA
    - CTE_Source - unpivots so we can get the TNum and PowerCurve
    - CTE_Join - looks up the TNum in dbo.NameLocation and dbo.PowerCurve
    - CTE_Pivot - pivots the data back to make the output what we want
    **/

;WITH CTE_Source AS
    (
    SELECT [TimeStamp]
        , [TName]
        , [Value]
    FROM dbo.Windspeed AS W
        UNPIVOT ([Value] FOR TName IN ([A-004], [A-005a], [A-006a], [E-009])) AS P
    )
, CTE_Join AS
    (
    --NOTE rounding answer from https://stackoverflow.com/questions/9873990/round-to-5-or-1-0-in-sql
    SELECT S.[TimeStamp]
        , S.[TName]
        , S.[Value]
        , CONVERT(DECIMAL(5,2), ROUND(S.[Value] * 2, 0)/2)  AS RoundedValue
        , NL.TNum
        , PC.Power_Wdspd
    FROM CTE_Source AS S
        LEFT OUTER JOIN dbo.NameLookup AS NL ON NL.TName = S.TName
        LEFT OUTER JOIN dbo.PowerCurve AS PC ON PC.WindSpeedBIN = CONVERT(DECIMAL(5,2), ROUND(S.[Value] * 2, 0)/2) AND PC.Turb_Num = NL.TNum
    )
, CTE_Pivot AS
    (
    SELECT [TimeStamp]
        , MAX([A-004]) AS [A-004]
        , MAX([A-005a]) AS [A-005a]
        , MAX([A-006a]) AS [A-006a]
        , MAX([E-009]) AS [E-009]
    FROM CTE_Join AS J
        PIVOT (MAX(Power_Wdspd) FOR TName IN ([A-004], [A-005a], [A-006a], [E-009])) AS P
    GROUP BY [TimeStamp]
    )
SELECT * FROM CTE_Pivot


SQLAZURE Resource limitations-Max concurrent sessions I was trying to select a tier for our internal consumption and going through this limitations in detail..Azure SQL Database resource limits

One particular instance of trouble was below chart and particularly highlighted values

as you see ,from the highlighted ,there is a max concurrent limitation of 60 concurrent logins and 600 concurrent sessions

ASK:
a single query can go in parallel and can have multiple sessions,will those sessions count under this limitation  ?

i tried to test this using Paul White's  article Forcing a Parallel Query Execution Plan like below

DBCC FREEPROCCACHE
DBCC SETCPUWEIGHT(1000)

GO
-- Query to test
some expensive query
GO

DBCC SETCPUWEIGHT(1)
DBCC FREEPROCCACHE

or using

OPTION (RECOMPILE, QUERYTRACEON 8649)

but i  can only go upto 9 sessions,when i checked using below DMV

select * from sys.dm_os_tasks
where session_id=61

and these trace flags  and DBCC options wont work in azure .let me know if i am unclear

When a task goes parallel it is still considered a single session with multiple request.

Hopefully the following will clear you confusion about the 2 metrics. I

Ref: https://social.msdn.microsoft.com/Forums/en-US/c073c554-03a3-43b5-9052-4de63d1f44d7/logins-versus-sessions?forum=ssdsgetstarted

Lets say you have 10 developers working on your app and they all connect to Azure SQL using SSMS. They will create 10 concurrent logins and 10 sessions in Azure SQL.

Now let's say that your app is using one login for connecting to Azure SQL and you have 100 concurrent users on your app. This will create one concurrent login and 100 concurrent sessions in Azure SQL.

If you want to baseline your concurrent session this is a great article. You might have to tweak it for Azure SQL Database.


MySQL Galera cluster can't initiate first node I'm using Ubuntu 16.04 and installed MySQL version 5.7 via apt-get in AWS.

After that, I've managed to upgrade the MySQL to Galera Cluster by following the Documentation from the official website using apt-get install galera-3 galera-arbitrator-3 mysql-wsrep-5.6

After that I've configured the file /etc/mysql/conf.d/galera.cnf to be:

[mysqld]
binlog_format=ROW
default-storage-engine=innodb
innodb_autoinc_lock_mode=2
bind-address=0.0.0.0

# Galera Provider Configuration
wsrep_on=ON
wsrep_provider=/usr/lib/galera/libgalera_smm.so

# Galera Cluster Configuration
wsrep_cluster_name="test_cluster"
wsrep_cluster_address="gcomm://this_node_ip,second_ip,third_ip"

# Galera Synchronization Configuration
wsrep_sst_method=rsync

# Galera Node Configuration
wsrep_node_address="this_node_ip"
wsrep_node_name="this_node_name"

The step above is repeated for the other 2 nodes (with it's own IP address). And stop the mysql with the following command sudo service mysql stop for each of the node.

And in the first node, I'm trying to start with the following command:

sudo service mysql start --wsrep-new-cluster

But it returns: Job for mysql.service failed because the control process exited with error code. See "systemctl status mysql.service" and "journalctl -xe" for details.

In the systemctl status mysql.service, it says:

● mysql.service - LSB: start and stop MySQL
   Loaded: loaded (/etc/init.d/mysql; bad; vendor preset: enabled)
   Active: failed (Result: exit-code) since Fri 2018-02-09 15:53:16 +08; 33s ago
     Docs: man:systemd-sysv-generator(8)
  Process: 6264 ExecStart=/etc/init.d/mysql start (code=exited, status=1/FAILURE)

Feb 09 15:53:15 systemd[1]: Starting LSB: start and stop MySQL...
Feb 09 15:53:15 mysql[6264]: Starting MySQL
Feb 09 15:53:15 mysql[6264]: .180209 15:53:15 mysqld_safe Can't log to error log and syslog at the same time.  Remove all --log-error configuration options for --syslog to take effect.
Feb 09 15:53:15 mysql[6264]: 180209 15:53:15 mysqld_safe Directory '/var/run/mysqld' for UNIX socket file don't exists.
Feb 09 15:53:16 mysql[6264]:  * The server quit without updating PID file (/var/run/mysqld/mysqld.pid).
Feb 09 15:53:16 systemd[1]: mysql.service: Control process exited, code=exited status=1
Feb 09 15:53:16 systemd[1]: Failed to start LSB: start and stop MySQL.
Feb 09 15:53:16 systemd[1]: mysql.service: Unit entered failed state.
Feb 09 15:53:16 systemd[1]: mysql.service: Failed with result 'exit-code'.

Hope these information helps.

Have you tried creating the /var/run/mysqld directory, with rwx permissions for the mysql user? Judging by the error messages, that seems to be a good place to start.

The other error message should probably also be addressed, regarding not writing to syslog and the mysql error log at the same time. I'd suggest inspecting your MySQL config files under /etc/ to see if you can disable the syslog option.

Also, it looks a bit strange that you're using mysql-wsrep-5.6 with MySQL 5.7. Is there a mysql-wsrep-5.7? If not, perhaps you need to use MySQL 5.6.


DBLINK match a value from a local table Am trying to run the following dblink start transaction;

select dblink_connect('host=remote port=5432 dbname=remote_db user=user password=pass');
insert into local_table (column1, column2 )
select * from dblink(`
    select remote_column1 ltrim(remote_column2, ''TEST'') from remote_table
       where remote_column2 ilike ''TEST%''
) as t(column1 varchar, column2 varchar) ;

now the above works fine, but am encountering a challenge:- I want instead of hardcoding TEST, I want it to be queried from a local table so I would wish to substitute TEST to something like SELECT uniq_id from some_local_table The logic behind it is I want to get records which has prefix matching the result of SELECT uniq_id from some_local_table but trim the prefix when inserting to local_table hope someone understands me

This should actually work with a correlated subquery:

SELECT dblink_connect(
                'host=remote port=5432 dbname=remote_db user=user password=pass');

INSERT INTO local_table (column1, column2)
SELECT *
FROM   dblink((
          SELECT '
             SELECT remote_column1 ltrim(remote_column2, ''' || uniq_id || ''')
             FROM   remote_table
             WHERE  remote_column2 ILIKE ''' || uniq_id || '%''
             AND    row_counter > ' || coalesce((
                SELECT max(row_counter)
                FROM   yet_another_local_table), -1)
          FROM   some_local_table
          --WHERE <some_condition>
          LIMIT  1  -- make sure it's one row
          )
       ) AS t(column1 varchar, column2 varchar);

SELECT dblink_disconnect();

The meta-levels and layers of single-quotes may be a bit confusing.

This form is vulnerable to SQLi. Make sure nothing bad can come from some_local_table or use quote_literal(uniq_id) in place of plain uniq_id.

In reply to the comment I added a nested correlated subquery to the example. It returns a single row, so it should work fine. Could also be done by left-joining to it in the FROM clause.

Note that I rewrote the expression to make COALESCE() the outer wrapper, so the query won't fail if there are no rows yet_another_local_table. The way you had it would only catch the case where max(row_counter) IS NULL.

Probably easier to read with format() (requires Postgres 9.1+):

INSERT INTO local_table (column1, column2)
SELECT *
FROM   dblink(
         (SELECT format(
            'SELECT remote_column1 ltrim(remote_column2, %1$L)
             FROM   remote_table
             WHERE  remote_column2 ILIKE %2$L
             AND    row_counter > %3s'
            ,uniq_id,  uniq_id || '%'
            ,COALESCE((
               SELECT max(row_counter)
               FROM   yet_another_local_table), -1)
            )
          FROM   some_local_table
          --WHERE <some_condition>
          LIMIT  1)
   ) AS t(column1 varchar, column2 varchar);


Garbage collection in database? its kind weird idea which I would need suggestions. A classical example:

users
-----
ID | USERNAME
1    test1
2    test2
3    test3

emails
------
USER_ID | EMAIL
1         test1@email.com
2         test2@email.com
2         test3@email.com
1         test4@email.com
3         test5@email.com
3         test6@email.com
2         test7@email.com
3         test8@email.com
1         test9@email.com

and a select:

SELECT * FROM users LEFT JOIN emails ON users.ID = emails.USER_ID

would resuls all emails of users. So far its so good, but lets say its a very big system and we are on MyISAM, so no real foreignkey connectionts. I only delete a user, so all of their emails remain. Normally we would say its bad, because lots of unconnected garbages remain. But what if we wipe out them later? We can SELECT those unnecessary records later and a cron-task would erase them. Is it bad, crazy idea?

MyISAM is especially bad about garbage collection.  That is one of several reasons you really should move to InnoDB.  MyISAM will be removed in the next release.

MyISAM, more so than InnoDB, can sometimes benefit from OPTIMIZE TABLE.  That is the command for freeing up wasted space and otherwise defragmenting a table.  It is tedious and takes a long time (depending on size of table).

As already pointed out, InnoDB, with FOREIGN KEYs, could take care of the deleting.

I would prefer to have an API that handles the delete -- plus any cleanup that might be needed.  Built into that API could be

Cascading delete, or
Application code to do the delete(s), or
A stored procedure to do the delete(s).
A cron job "because it could take a long time to do all the deleted".

A cron job?  -- Suggest you have the app copy the deleted userid into a ToBeDeleted table.  This could be done with a simple TRIGGER.  Then the cron job would select one row from that table, proceed to do deletes from various other tables (COMMITting as it goes), and finally delete from ToBeDeleted.  Rinse and Repeat.

Better yet, don't make it a cron job, make it a continually running job.  (OK, add a "keep-alive" cron job to make sure it does not vanish, especially after a reboot.)  When this continual job finds nothing to do, it sleeps for, say, a minute.  Otherwise, it runs full-tilt.


Free up disk space on sql server Assuming I have a sql database server with disk space as follows:

D Drive: 10GB (For the MDF file)

E Drive: 10GB (For the LDF file)

And I have only one database on the server with the following details:

Disk space occupied:
9GB MDf file and
9GB LDF file.

10 tables, Only one Schema (dbo), No indexes at all on any of the tables.

What I do:
I drop all the tables on the database, but the drive space remains the same(9gb) and I am left with just 1GB on both the drives.

How would I reclaim the space on D drive and what is the best way to do this without shrinking the mdf and without dropping the database?
Obviously without adding any disk space.

Shrinking log file would free up space but I am more concerned about getting the space back on the D drive which holds the MDF file.

If you've dropped all the tables, you could just drop the database and re-create it. It will be created with the default sizes and growth rates as defined by the model database. These may not be ideal (I don't find the defaults particularly useful in most scenarios), but you can change them.

Unless you have a lot of things set up on this database (like Service Broker, a ton of users, etc.), I think that is less cumbersome than getting into the habit of growing/shrinking growing/shrinking growing/shrinking. You should put the database on a drive that has enough space to accommodate the data you're going to generate, without having to shrink all the time. Shrinking and growing are both expensive events, especially on slow I/O.

Also, to prevent the log from growing to 9 GB (that seems pretty ridiculous for the data you describe), you should either be full recovery and you need to perform more frequent backups and log backups, or you should switch to simple recovery. More details here - it's long but please read it all.


managing a growing database in the long run (10 years from now) I'm close to finishing-up a WEB app (PHP + mysql website portal), which will start slowly, as in uploading few pictures and inserting few rows in main database table, but soon, and over few years, I expect it to grow steadily and even logaritmically at some point.

By my Excel calculations, at some point, the database table data + indexes (innoDB) will overgrow the size of hosted pictures by a factor of 10, but in the beginning it will be the other way round.

So basically my portal hosts pictures (medium size jpegs let's say) and uses MySql InnoDB database tables.

My question to you all admins and professionals is about my MySql Database.

I'm thinking in advance so I don't get caught unprepared :

When my database will grow more and even get 1.2TB or 2TB or more....how can I deal with that ? Can I say split it on 2 or more dedicated machines so searches will still be fast and getting data from this big database ? Or shall I do 1 server and pump it up with a lot of cheap 7200 rpm sata ? or what can be the solution to a growing database.

My first configuration for this web-app is a VPS - SSD hosting with a 4ghz processor, 3GB of ram and 2x60 GB SSD raid 1.

thanks for your answers,

Here's some generic suggestions...

Don't store the images themselves inside the database (it's unclear if that's your plan). This will cause you huge scaling problems. Store them in a filesystem, or a non-relational database if you must.
The database should be a separate server from the app.
Ideally you should have enough RAM to hold your working data set. Make sure you tune the database in advance (innodb_buffer_pool_size, innodb_log_file_size, etc).
Put your mysql data on the fastest disks you can get. SSD or 10k+ SATA/SAS. Make sure the RAID controller has a battery backup and write caching.
RAID-10 for your mysql data volume.
Put mysql's binlogs (and possibly relay logs, and innodb transaction logs) on a separate RAID array, RAID-1 should be fine.
Have a slave server for read-only queries, to take some load from the master. Your app will have to differentiate RW and RO queries. Set 'read_only=1' on that slave to prevent unfortunate accidents.
As your dataset grows, you may need to consider horizontal sharding across multiple servers. MySQL doesn't provide a mechanism for this, so your app will need to.
There's no reason you can't have a single 2TB database, but you will probably find performance unacceptable for data that doesn't fit in the buffer pool.
Don't go overboard on indexing. Additional indexes beyond the primary introduce additional overhead (not to say you should have none). There may be good opportunities to use a composite index (multiple columns) instead of 2 separate ones.

Hope this helps,


Apply limit and conditions per grouped data I am building an api endpoint to send statistics data that then is going to be plotted on a graph. The hardest part is being the query I need to execute. To sum up I want the first n most sold products per day in a period. The period, for instance, could be any date range.

Suppose I have a sales table with the following data (this is not the real schema I have defined):

---------------------------------
date       | product_name | count
---------------------------------
2015-01-08 | A            | 10
2015-01-08 | B            | 5
2015-01-08 | C            | 1
2015-02-08 | A            | 5
2015-02-08 | B            | 3
2015-02-08 | C            | 100

And suppose I want the 2 most sold products per day. I want a query that
returns the following:

---------------------------------
date       | product_name | count
---------------------------------
2015-01-08 | A            | 10
2015-01-08 | B            | 5
2015-02-08 | C            | 100
2015-02-08 | A            | 5

Note that besides that C is the most sold product in the whole period, it does not appear in the top 2 of 2015-01-08

Is there a way to achieve what I want?

Thanks :)

One way to solve such problems, is using window functions:

WITH cte AS
    ( SELECT date, product_name, count,
             ROW_NUMBER() OVER (PARTITION BY date
                                ORDER BY count DESC) AS rn
      FROM sales
      WHERE date >= '2015-07-01'     -- example range
        AND date < '2015-08-01'      -- 2015, July
    )
SELECT date, product_name, count
FROM cte
WHERE rn <= 2           -- max of 2 products per date
ORDER BY                     -- this does not affect the result
    date, count DESC ;       -- only its order

If there are ties, they will be resolved arbitrarily and only 2 rows per date will be returned. If you want to resolve ties with a specific preference, you can change the order inside the OVER clause. Say you want the products' names to be sorted alphabetically when they tie in top sales, use (... ORDER BY count DESC, product_name).

If you want all the (tied) products per date, just change ROW_NUMBER() to RANK().


Is it possible to do this math in a view? I have been tasked with creating a view for a client. Specifically it must be in a view. However, there is some math that I am not sure how to do within a view. I do not know if it is even possible. But then again, my mind is feeble.

I am using SQL Server 2008R2, so advanced OVER() functionality doesn't work.

Let's say a person is given $400 to spend. They can spend more, but the first $400 is free. One column of the report will have the amount the person spent on something, and another will have the total amount the person needs to pay out of their own pocket.

So, for the first record in the report for this person, one column will have an amount they have spent, say $50, and then a second column will have a $0. Behind the scenes they still have $350 to spend.

The next record has the person spending $300. The second column will still show a $0, and behind the scenes the initial $400 is now $50.

The third record for the person shows they spent $75 dollars, but they only have $50 left over from the initial $400. The second column should now have a $25 value in it. They have exhausted the initial $400 and are now spending their own money.

The fourth record shows they spent $40, so now the second column will show a $65. etc...

I have briefly read about CTEs and table valued functions and such, but is it possible to use them in any combination to give the desired behavior above?

Below is some sample code for structure and desired results

CREATE TABLE Payroll (
    PersonID int,
    PlanCode varchar(10),
    Deduction int NULL
)
GO

INSERT INTO Payroll (PersonID, PlanCode, Deduction)
VALUES (1, 'Medical', 200)
  ,(1, 'Dental', 250)
  ,(1, 'Vision', 300)
  ,(2, 'Medical', 100)
  ,(2, 'Dental', 150)
  ,(2, 'Vision', 100)
  ,(2, 'Disability', 100)
  ,(2, 'Life', 140)

Desired results:

It may make sense to think of the OutOfPocket as TotalOutOfPocket.

There is nothing like a timestamp in the source data for ordering of the entries. The ordering is not too important. If any ordering is done, it will be on the PlanCode.

Based upon our constraints and a 3rd column that was not necessary to include, there will not be any duplicate entries possible.

Something along these lines maybe? You can do running totals with the OVER clause for SUM.

CREATE TABLE Expenses (
    expense_id int NOT NULL IDENTITY(1,1) PRIMARY KEY CLUSTERED,
    amount decimal(19,5)
)

INSERT INTO Expenses (amount) VALUES (50), (300), (75), (40)
GO

WITH running_total AS (
    SELECT
        expense_id,
        amount,
        SUM(amount) OVER (ORDER BY expense_id ROWS UNBOUNDED PRECEDING) AS total
    FROM Expenses
)
SELECT
    expense_id,
    amount,
    total,
    CASE WHEN total > 400 THEN total - 400 ELSE 0 END AS out_of_pocket_total
FROM running_total
ORDER BY expense_id


query to search a particular word/string in toad/oracle I trying to find out a word/string named 'audapro_ind' in oracle database in toad. This word can be present anywhere in column of table, function, procedure etc. in oracle database

I tried few queries like

select name , line,text from dba_source where upper(text) like
upper ('audapro_ind') escape '\'

also

select DISTINCT(name) from user_source where type = 'PROCEDURE'
 AND text_like 'audapro_ind'

But  could not able to find the solution

You can use this:

select name , line,text from dba_source where regexp_like(text,'audapro_ind','i');

'i' specifies case-insensitive matching.


Find constraint disabled in SQL Server I'm doing an audit of constraints in SQL Server.

How to find all constraints that are not enabled?

This, after using the next declaration:

alter table mytable nocheck constraint all

Use the system views for this:

select * from sys.check_constraints
where is_disabled = 1;


ORA-30009: Not enough memory for CONNECT BY operation I have a Linux VM (with CentOS 64 bit) where I installed Oracle Database 11g Express Edition. The database is running, I can use sqlplus and I can create tables and stuff. However, when I run a certain SQL script which inserts a huge amount of random data (~2 million rows) I get this error:

ORA-30009: Not enough memory for CONNECT BY operation

I already tried to increase PGA_AGGREGATE_TARGET with the command below. As far as I read this should solve the problem (but it doesn't) since it increases the memory.

ALTER SYSTEM SET PGA_AGGREGATE_TARGET = 40M scope = both;

However, the problem is that I can not set PGA_AGGREGATE_TARGET higher than ~40M (which seems to be not sufficient). If I try to set it up to 100M or more I get another error:

ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-47500: XE edition memory parameter invalid or not specified

Any idea how I can solve this problem? It is perfectly fine for me to re-install the whole database or whatever.

PS.: I asked the same question on https://stackoverflow.com/ since I wasn't sure whether it is programming or database administration.

Here are a few tips that might help you inserting huge amounts of data:

use APPEND hint, this should solve your problem, as the data will go directly into the datafiles per-say.
try to break the data into smaller chunk, and insert them sequentally
use NOLOGGING mode to avoid frequent log-switchees (don't forget to enable it back afterwards!) - or at least increase the size of redo-log files
avoid using cursors
drop any indexes that you might have
try to COMMIT less frequently (use a transaction for each chunk of data, and commit at the end)

Hope this helps!


Server response time high when InnoDB tables active but not being accessed I am experiencing high server response time (up to 5 seconds) when loading every Webpage on my server, ONLY when MySQL InnoDB tables are running.

The 5 second delay occurs even on static HTML pages with no call to any MySQL data table (and no other users on the site accessing other InnoDB linked webpages).

As soon as the InnoDB is shutdown (MyISAM tables remain active), all pages (those that call DB or not) load very fast (including MyISAM tables) in about a second.

How could InnoDB be causing server wide slow response, even when not being accessed?

[SOLVED]

I enabled query_cache_type=1 and query_cache_size=256M.

The problem went away and the server response reduced from 6 seconds to 1 second. Very strange considering most recommend that Query cache should be disabled!


Stored Proc Logical Reads Spiking Daily At The Same Time I have a procedure that I optimized in a dev environment.  It is a heavy lifter and started at 14 millions logical reads with a run time of 30-40 seconds.  I got it down to 96k reads and 8 seconds.  It was pushed to our production database.  Then we noticed that for the last 3 days from 11:30AM-2PM the performance for this proc was tanking with the run time going anywhere from 40 seconds to 2 minutes and the logical read count jumping to 12 million.

This same query on this same server and DB goes back to 70-95k reads and a couple of seconds around 3PM.

This issue CANNOT be reproduced in the dev or QA environments and the schema structure is the same between them all and prod.

At first I thought sniffing or a bad execution plan but that doesnt explain the consistent timing aspect of it all.

I cant tell if I am over looking something that is staring me in the face or if there is more to the story.  What could cause this read spike?  Any feedback is welcome.

The root was tracked to some old SSIS packages running longer than anticipated. Thanks for the feedback folks!


Without rewriting this query, is there a way to force the query optimizer to better estimate the amount of resources required, e.g. with query hints? I know there's a couple of alternate ways to write this query, but I'm more curious if there's another approach that I could use to improve the performance by helping guide the optimizer in the right direction? For example, can I use any kind of query hints / options to improve performance here?

Currently it runs in about 40 minutes and returns about 30,000 records.

Note: I'm also interested if there's new features in 2019 that apply here that don't apply in 2008 R2 (I know one big difference between the two is the Cardinality Estimator).

This query, as written, must compare every pair of rows, and the cost estimate is reasonably accurate.

On a supported version of SQL Server you might get a parallel plan, or there is an undocumented query hint that will force a parallel plan.

select t1.itemid, t2.itemid
from #temp T1
inner join #temp as t2
  on t1.itemid like '%' + t2.itemid + '%'
  and t2.itemid <> t1.itemid
OPTION(USE HINT('ENABLE_PARALLEL_PLAN_PREFERENCE'))


Postgresql cannot peer authenticate using usermap (provided user name (^dbuser^) and authenticated user name (^sysuser^) do not match) I am trying to peer authenticate some users using usermap. What I am trying to do is to map user mailreader as you can see:

postgres=# \du
                              List of roles
 Role name  |                   Attributes                   | Member of
------------+------------------------------------------------+-----------
 mailreader |                                                | {}
 postgres   | Superuser, Create role, Create DB, Replication | {}

Into system's user root,dovecot,postfix. Therefore I edited my pg_ident.conf and put the following content:

mailmap         dovecot                 mailreader
mailmap         postfix                 mailreader
mailmap         root                    mailreader

I also edited the pg_hba.conf and appended with the following content:

local    mail        all                     peer map=mailmap

When I try to connect via sudo psql -U mailreader -d mail command I get:

  psql: FATAL:  Peer authentication failed for user "mailreader"

error.

I also tried the following configuration:

local    mail            all                                     ident map=mailmap

Without any progress.

May I have some help?

Edit 1

The logs regarding the error is:

2017-06-27 19:10:10 UTC [1188-1] mailreader@mail LOG:  provided user name (mailreader) and authenticated user name (root) do not match
2017-06-27 19:10:10 UTC [1188-2] mailreader@mail FATAL:  Peer authentication failed for user "mailreader"
2017-06-27 19:10:10 UTC [1188-3] mailreader@mail DETAIL:  Connection matched pg_hba.conf line 90: "local   all             all                                     peer"

In the end I had to comment the line:

local   all             all                                     peer

Or to put it below the following line:

local    mail            all                             ident map=mailmap


Performance: MySQL id = vs id IN (multiple values) I've found posts of = vs IN for single value (link and link), but I'm asking actually for multiple values. Which is likely to perform better:

# Query 1:
SELECT * FROM tbl WHERE id = 50 OR id = 51 OR id = 52

# Query 2:
SELECT * FROM tbl WHERE id IN (50, 51, 52)

# Query 3:
SELECT * FROM tbl WHERE id >= 50 AND id <= 52

Note: id is primary and indexed, obviously.

I found a similar post here.

I've done some benchmark on all 3 queries on a table with 500,000 records with index on id. I fetched 10,000 rows. I did the same tests on MyISAM and Innodb, all same results. I made sure that for each query I used different set of numbers, so the query is not cached by MySQL. I did the tests multiple times, and I got similar numbers every time.

Benchmark results (average):

Query 1, fetch by = OR =: 3.700 seconds
Query 2, fetch by IN: 0.037 seconds
Query 3, fetch by >= && <=: 0.005 seconds
Query 4, fetch by between: same as >= && <=


Check 1 Table If No Data Check Second Table I am passing into a stored procedure two variables @begindate & @enddate.  I need to check firsttabletocheck and get a count of where csdate is between @begindate and @enddate.

I then need to check and see if any of the dates between @begindate & @enddate Not Exists  in firsttabletocheck I need to query secondtabletocheck to get a count from that.

I can write my code to do 1 table, but not quiet sure how to write it to do the other.  For example this is my current syntax that only checks 1 table, how can I add in the check for the second table?

Declare @d1 datetime, @d2 datetime, @numreturned int
Set @d1 = '01/01/2015'
Set @d2 = '01/12/2015'
Set @numreturned = 0

Set @numreturned = (Select COUNT(*)
                    FROM firsttabletocheck
                    where csdate BETWEEN @begindate and @enddate)

How can I add my not exist here to check if the date does not exist in the above table then check this table?

For example let's say that all dates in the date range exist in firsttabletocheck EXCEPT 01/11/2015 then I need to query the second table to check if 01/11/2015 exists in that table.

You could try something like this query:

Declare @d1 date, @d2 date, @numreturned int
Set @begindate = '01/01/2015'
Set @enddate = '01/12/2015'
Set @numreturned = 0

SELECT @numreturned = COUNT(DISTINCT CAST(csdate as date))
FROM firsttabletocheck
WHERE csdate >= @begindate and csdate < @enddate;

IF DATEDIFF(day, @begindate, @enddate) > @numreturned
BEGIN
    SELECT DISTINCT CAST(csdate as date))
    FROM secondtabletocheck
    WHERE csdate > @begindate <= @enddate
        AND CAST(csdate as date) NOT IN (
            SELECT DISTINCT csdate = CAST(csdate as date)
            FROM firsttabletocheck
            WHERE csdate >= @begindate and csdate < @enddate
        )
END

I am using CAST(csdate as date) everywhere because you datatype seems to be datetime. If you are not using the time part, you should consider changing the type to date. This will very likely leads to table scan with the second query.

I added a distinct because I don't know whether you have duplicates or several rows on the same day. If there is only 1 row per day, this is not needed.

I replaced the between by >= and <. (see @ypercubeᵀᴹ comments)

I am not sure what sort of output is needed precisely. You will have to adapt this query to your needs or add sample data and sample output. Right now it only show csdate missing from firsttabletocheck but present in secondtabletocheck.


A beautiful trigger issue. Flawed logic? Updating Orderheader when orderlines are complete THe bussiness rule is as such: The orderlines must be updated with a date stamp when all the related orderlines are marked as 'Y' denoting a completed order line.

THe trigger thus far is:

CREATE OR REPLACE TRIGGER orderComplete
after update ON orderline
DECLARE
orderId NUMBER;
ordersNotDone NUMBER;
BEGIN

    SELECT COUNT(Orderline_fulfilled), order_id INTO ordersNotDone, orderId
    FROM orderHeader
    JOIN orderline ON
    orderHeader.Order_id = orderLine.Orderline_order
    WHERE Order_id = orderline_order
    AND orderline_fulfilled = 'N'
    GROUP BY order_id;

    if ordersNotDone = 0
    then
        UPDATE orderHeader
        set ORDER_COMPLETED = sysdate
        WHERE order_id = orderId;
    end if;

END;

Upon an update of the order line, an 'no data found' is returned- is this sugesting the orderId variable is not returning data? The queries do work, however, when taken out of the trigger and ran independently.

Your trigger checks all orderlines for completeness even when only one is updated. You should use the :NEW table prefix to filter the currently updated record.
There is no need to include the table orderHeader while counting
There is no need for a group-by

Try the following:

CREATE OR REPLACE TRIGGER orderComplete
   AFTER UPDATE ON orderline
 FOR EACH ROW -- Added after comment by a1ex07
 DECLARE ordersNotDone NUMBER;
 BEGIN
    SELECT COUNT(Orderline_fulfilled)
      INTO ordersNotDone
      FROM orderline
     WHERE orderline_order = :NEW.orderline_order
       AND orderline_fulfilled = 'N';

    IF ordersNotDone = 0
    THEN
        UPDATE orderHeader
        SET order_completed = sysdate
        WHERE order_id = NEW:orderline_order;
    END IF;
END;


Join on different types In a database I have two tables:

The first has a field named taxonomy_id that is an integer
The latter has a field named ID that is a character varying

The two tables are related: if it exists a row in the first one with taxonomy_id = N it will exist a row in the second one with ID = N.toString.

Now, I would like to do the join between this two tables; naturally the "normal" join doesn't work because of the type mismatch.

Can you help me solving this?

I'm using PostgreSQL.

Not valid integer types

If the values in the field are not actually valid integer types (digits and leading - only), a JOIN like @mustaccio suggested would fail with an exception: you couldn't cast to integer at all.

It would have to be the other (even more expensive) way round:

SELECT *
FROM   tbl1 t1
JOIN   tbl2 t2 ON t1.taxonomy_id::varchar = t2.id;

Also, since @mustaccio misleadingly suggested to use int8: Don't. integer equals int4. int8 would be bigint.

You can support that with a functional index:

CREATE INDEX tbl1_taxonomy_id_idx ON tbl1 (cast(taxonomy_id AS varchar));

Valid integer types

If we are dealing with valid integer types, you could just convert your column id to integer - if your setup allows that.

ALTER TABLE tbl2 ALTER COLUMN id TYPE integer USING id::int;

Then your problem is gone for good:

SELECT *
FROM   tbl1 t1
JOIN   tbl2 t2 ON t1.taxonomy_id = t2.id;

Barring that (if you cannot convert the column for some reason), a functional index would help:

CREATE INDEX tbl2_id_idx ON tbl2 (cast(id AS int));

SELECT *
FROM   tbl1 t1
JOIN   tbl2 t2 ON t1.taxonomy_id = t2.id::int;


how to change wsrep_cluster_size in Percona XtraDB Cluste? I am trying to setup Percona XtraDB Cluster according to their doc
When I run show status like 'wsrep%'; it's show wsrep_cluster_size is 1 in every node(2 node for me). Actually it should be 1 for node1 and 2 for node2. How can change it to work?

You have a couple issues here, I believe it is a misunderstanding of how to deploy PXC. Firstly, wsrep_cluster_size is telling you how many nodes you have in your cluster. If both nodes are saying 1, you have 2 clusters of 1 node, not 1 with 2 nodes.

Secondly, follow the rule of 3's. You should always have an odd number to avoid split brain.

Recommended step here is to shut down node 2. Bootstrap node 1

http://severalnines.com/blog/how-bootstrap-mysqlmariadb-galera-cluster

https://www.percona.com/doc/percona-xtradb-cluster/5.6/manual/bootstrap.html

Then bring node 2 up with the correct cluster ips set in your my.cnf.

Follow configuration options here: https://www.percona.com/doc/percona-xtradb-cluster/5.6/installation.html