texts: Chunks not splitting on MongoDB sharded clusters

texts:
I have created 2 shard servers named rs1 and rs2.
Now in the database collection there are multiple records those should split on these 2 servers in chunks. The sharding key is TxnMonth. It is not unique.

Now I have given the chunk size as 1 MB. When I inserted records corresponding to "TxnMonth" : 1, "TxnMonth" : 2 and "TxnMonth" : 3, the data was splitted onto 2 shards where on rs1 there were 2 chunks and on rs2 there was 1 chunk.

On checking, data for TxnMonth : 1 and 2 was on rs1 and data for TxnMonth : 3 was on rs2.

On running, sh.status(), it gave this output-

{  "_id" : "shardPOC",  "primary" : "rs1",  "partitioned" : true }
                shardPOC.machines
                        shard key: { "TxnMonth" : 1 }
                        unique: false
                        balancing: true
                        chunks:
                                rs1     2
                                rs2     1
                        { "TxnMonth" : { "$minKey" : 1 } } -->> { "TxnMonth" : 1 } on : rs1 Timestamp(2, 1)
                        { "TxnMonth" : 1 } -->> { "TxnMonth" : 3 } on : rs1 Timestamp(1, 2)
                        { "TxnMonth" : 3 } -->> { "TxnMonth" : { "$maxKey" : 1 } } on : rs2 Timestamp(2, 0)
        {  "_id" : "NewTestDB",  "primary" : "rs1",  "partitioned" : false }

After this, I started inserting records for TxnMonth : 4, 5, 6, 7 and 8, to check when the chunk on rs2 will split but even if the chunk size exceeded 1 MB size, it didn't split. Below is the output of db.collection.getShardDistribution() -

Shard rs1 at rs1/JCB-DB1:27012
   data : 57KiB docs : 80 chunks : 2
   estimated data per chunk : 28KiB
   estimated docs per chunk : 40
Shard rs2 at rs2/JCB-DB1:27013
   data : 1.1MiB docs : 1560 chunks : 1
   estimated data per chunk : 1.1MiB
   estimated docs per chunk : 1560
Totals
   data : 1.15MiB docs : 1640 chunks : 3
   Shard rs1 contains 4.87% data, 4.87% docs in cluster, avg obj size on shard : 740B
   Shard rs2 contains 95.12% data, 95.12% docs in cluster, avg obj size on shard : 740B

What I expected was that the chunk will split after it exceeds 1 MB but it did not.
Can someone explain the reason?
Thanks in advance.


texts: postgres notifications from inside stored procedures/triggers not immediately received by node.js listener client

texts:
We are using postgres 11's stored procedures to do looped update of rows in batches. After a batch is updated, a 'progress' table is updated with progress percent and status, THEN a notification (using pg_notify) is sent. A nodejs client listens for the notification and handles it.

For testing, we added PERFORM pg_sleep(5) right after pg_notify call so we can see the progress percent slowly increment.

The problem is the notifications aren't sent immediately to listeners when PERFORM pg_notify is called but instead ALL notifications are sent AFTER the stored procedure has completed running.

Has anyone encountered this problem and found any solution (aside from dblink)?

We've tried the following inside the Stored Procedure:
- moved pg_sleep at top of loop
- adding commit; after pg_notify
- use notify channel, 'payload instead of pg_notify

We then tried using triggers + pg_notify. When that didn't work we also tried using execute format('notify channel, ''%s''', payload). The same result.

Some sample codes:

Node.js

const pg = require('pg');
let client = new pg.Client({user: '', database: '', host: '', password: '', port: 5432});
client.connect();
client.query('LISTEN progress_updates');
client.on('notification', function(data) {
   console.log('notification: ', data);
});

Backend:

-- table
drop table if exists operations;
create table progress ( id serial primary key, percent int default 0 );

-- stored procedure
CREATE OR REPLACE PROCEDURE my_stored_proc() language plpgsql
AS $$
DECLARE
progress_id int;
BEGIN
insert into progress (percent) values (0) returning id into progress_id;
commit;

update progress set percent= 10 where id = progress_id ;
commit;
perform pg_notify('progress_updates', '{"percent": 10}');
-- notify progress_updates, '{"percent": 10}'
perform pg_sleep(5);

update progress set percent= 30 where id = progress_id ;
commit;
perform pg_notify('progress_updates', '{"percent": 30}');
perform pg_sleep(5);

update progress set percent= 70 where id = progress_id ;
commit;
perform pg_notify('progress_updates', '{"percent": 70}');
perform pg_sleep(5);

update progress set percent= 100 where id = progress_id ;
commit;
perform pg_notify('progress_updates', '{"percent": 100}');
END;
$$;

-- trigger
create or replace function notify_progress_trigger() returns trigger
  language plpgsql
as
$$
DECLARE
  payload TEXT;
BEGIN
  raise notice 'TRIGGERED ON %', NOW();
  payload := '{"percent": ' || NEW.percent || '}';
  perform pg_notify('progress_updates', payload);
  -- execute format('notify progress_updates, ''%s''', payload);
  return NEW;
END;
$$;
alter function notify_progress_trigger() owner to postgres;

create trigger watched_progress_trigger
  after insert or update
  on operations
execute procedure notify_progress_trigger();

-- Call stored proc manually
call my_stored_proc();

We expected the notifications will be received one by one by the listener after each batch gets updated while the stored procedure is still running, but instead we receive all of the notifications in quick succession after the stored procedure completes.


texts:
Is there any way to skip some results on sp_BlitzIndex? Like has done on sp_Blitz, using the @SkipChecksTable.
For example, i have some index thats are not in use, but, those indexes are UNIQUE and i cannot drop then. I just want to ignore these indexes in the result


texts: How to save the design of an Access database -- not the content -- as text?

texts:
How can I save the entire design or specifications of an Access 2007 database -- not the data content -- as text?

I have fallen foul of the "Enter Parameter Value" bug whereby a query pop-up shows an out-of-date query name -- despite having the Name AutoCorrect Options turned on and having run Compact and Repair Database.

I have read that Name AutoCorrection is buggy.
I've also read that it was possible to save an object as text (in an earlier version of Access) and think this would be a valuable means of making name changes everywhere -- queries and forms as well as tables.

Please do not try to convince me not to change names; that may only make me more determined to do so ;-)


texts: PostgreSQL text search email parser does not handle + correctly


texts: Changing collection path and index path in mongod.conf file

texts:
I want to have collection path and index path in different folders of my mongodb.
What are the required changes? My current configuration file is this:

storage:
  dbPath: /db/mongodb
  journal:
    enabled: true

How can I give index path and collection path? Can anyone give me an example?


texts:
I have spent a few days trying to debug a deadlock that occurs in production. This is one I didn't understand however.
Below is pseudo code for the stored procedure. The stored procedure generates a deadlock with another instance of itself.

I know that the code is not brilliant, this old code on the system. But I want to understand why this deadlocks. The graph always shows the second selects deadlocking.

The first select performs a table scan, so I believe that places click on all rows in table.

I'd have thought this would queue one stored procedure behind the other. I cannot recreate this after days if trying.

Begin tran

Begin tran

Set isolation level repeatable read

//This select does a table scan, so I believe does X lock on all rows

Select a,b,c
From queuetable with(xlock, rowlock)
Where a = x

Delete from queuetable
Where a = X

Commit tran

Begin tran

//Get oldest row - this is the deadlocking query

Select top 1 a,b,c
From queuetable
Order by b

//Mark row to be processed
Update queuetable
Set c=processing
Where a = y

Commit tran

Commit tran


texts: Recover PostgreSQL password with admin access to host

texts:
Good day.

I have a PostgreSQL server.

Once i had admin login and password, then i created a new login for web application (with low permissions like select form some tables etc). And i just lost my admin login and password.

I have access (administator) to host where the server is running. Server is windows 2008 server.
Is there any way to recover admin permissions? May be i somehow could create another superuser?


texts: Restored DB has data, DBCC CHECKDB is clean, but SELECT TOP 1 returns zero rows

texts:
Having restored a client's SQL 2014 EE DB onto our Developer Edition instance, without issue, then running a DBCC CHECKDB that found zero errors, and a clustered columnstore index alleging over 1Bn rows, a simple SELECT TOP 1 FROM TheBaseTable returns no data - having taken 6m 38s to run, using 3845 reads, 155 physical reads, and over 400K ms CPU, so it gives the appearance of doing something.

Oddly, compatibility mode is at 2008 upon restore.  Changed it to 120 and page verification to CHECKSUM.

Fixed the SQL Login SID (only 1 orphan).  The login has db_owner, db_accessadmin, and db_securityadmin role membership, so it doesn't appear to lack privileges, but belts-and-braces-style it now also has db_datareader and db_datawriter memberships.

Ran sp_updatestats because the DMVs show rows but SELECT statements return zero.

Is it possible that the client's having set the compatibility down to 2008 with there being a CCSI in place has messed up the metadata?

Has anyone heard of anything like this, please?

Any ideas on how to see the data?

Here's the table - I see nothing fishy...

CREATE TABLE dbo.tbl_HistoryByType(
    InventoryID int NOT NULL,
    SalesTypeID int NOT NULL,
    ReturnFlag bit NOT NULL,
    SalesDate datetime NOT NULL,
    SeasonID int NULL,
    SalesQuantity float NOT NULL,
    TotalRevenue float NOT NULL,
    TotalCost float NOT NULL,
    TotalOriginalRetail int NULL
) ON [PRIMARY]
GO
CREATE CLUSTERED COLUMNSTORE INDEX CCSIX_tbl_HistoryByType ON dbo.tbl_HistoryByType WITH (DROP_EXISTING = OFF, DATA_COMPRESSION = COLUMNSTORE) ON [PRIMARY]
GO

Hope someone can lend a hand as I've never seen anything like this in over 10 years with SQL Server...!

I have absolutely no idea why compatibility would be set to 2008, especially when we are to evaluate the DB code and performance.

  Microsoft SQL Server 2014 - 12.0.4416.0 (X64)
  Jun 11 2015 19:18:41
  Copyright (c) Microsoft Corporation
  Developer Edition (64-bit) on Windows NT 6.1  (Build 7601: Service Pack 1) (Hypervisor)

Sadly, the original system is behind iron bars, so to speak. I'll inquire about any kind of select-only access. Strangely enough, I ran an ALTER TABLE...REBUILD and after DBCC CHECKTABLE scored zero rows.  1Bn+ "disappeared"  Will look at applying the latest CU, too - this server is heavily shared, so will have to schedule it.


texts:
I'm not a DBA  but a JIRA admin. I run a cron backup of my mysql InnoDB JIRA database which runs a script that executes the command below:

mysqldump -v --single-transaction --user=DATABASEUSER --password=PASSWORD? --max_allowed_packet=1024M DATABASE > /backup/mysql/DATABASE_$(date +%d-%m-%Y-%H.%M).sql 2> /backup/log/DATABASE_backup_$(date +%d-%m-%Y-%H.%M).log

The application is running during the backup. If I try to restore this backup file, I find that not all the tables are backed up and a load of them is missing. Only 130 out of 430 tables are being restored.

The restore was done by first creating the database and then specifying the source of the SQL dump:

CREATE DATABASE databasename CHARACTER SET utf8 COLLATE utf8_bin;
Use databasename;
Source /pathtofile

Any ideas why tables will be missing from such a backup command?

We have 5.6.36 version of MySQL

Cheers,


texts: When using underscores, should they be reserved to junction tables?

texts:
Say you have the following tables

company (contains companies)
industry (contains industries)
company_type (contains company types, e.g., Public, Private)
company_status (contains company statuses, e.g., Acquired, Operating)
company_industry (junction table between company and industry)

Using an underscore in the company_type and company_status tables makes it a bit difficult to easily glance at the database and tell which tables are junction tables.

Should underscores be reserved for junction tables? If so, how do you deal with a table like company_type? Simply naming it 'type' is non-descriptive, and is a SQL reserved keyword. As well, what if you have school types and you want to keep them separate from company types? I.e., school_type table and company_type table.

As an aside: I'm looking for a good resource on naming in database design. It's the one thing troubling me more than anything else.


texts: Rename the constraint of a possibly referenced primary key in PostgreSQL

texts:
Is it safe to use this command just like that, even though the table might be referenced from other tables as foreign key?

ALTER INDEX foo_pkey RENAME TO pk__foo__id;

I have just tried it and it seems OK, but I have heard it is necessary to drop foreign keys, rename pkey (drop/recreate) and re-reference the table again.

In case it is not that easy, is there a way how to find all referencing tables, drop their fkeys, alter the index and atach them again in one SQL?

Here is what I am using to find tables and their fkeys referencing my table:

SELECT
    tc.table_name, tc.constraint_name, kcu.column_name,
    ccu.table_name AS foreign_table_name,
    ccu.column_name AS foreign_column_name
FROM
    information_schema.table_constraints AS tc
    JOIN information_schema.key_column_usage AS kcu
      ON tc.constraint_name = kcu.constraint_name
    JOIN information_schema.constraint_column_usage AS ccu
      ON ccu.constraint_name = tc.constraint_name
WHERE constraint_type = 'FOREIGN KEY' AND ccu.table_name ='foo';


texts: Should I use one-to-many or many-to-many to log previous records?

texts:
I have 2 tables, company and interns. the initial relationships are,

An intern belongs to only one company.
A company has many interns

I have a slight problem with these relationships.

If the student changes to another company halfway through but needs the record of the previous company to be kept, is it correct if I change the relationship to become 'An intern belongs to many company' and create a third table to handle the 'many-to-many' relationship?

Or should I just leave it at that and instead create a 'log' table that logs the students' previous company?

Or is there a better way?

Note : Imagine that this system requires the most efficient way of storing data and that student cannot belong to two or more different companies at the same time.


texts: Why SQL Server uses Non-Clustered but not Clustered Index?

texts:
I have a table with 145 million rows

CREATE TABLE [dbo].[RFTest](
    [SnapshotKey] [int] NOT NULL,
    [SnapshotDt] [datetime] NOT NULL,
    [LoanNum] [int] NOT NULL,
    [GLSourceSystem] [varchar](10) NOT NULL,
    [FlowDescription] [varchar](30) NULL,
    [Account] [varchar](30) NULL,

    --- plus 20 more column
)

The table is partitioned on SnapshotDt.

I added following indexes on my table:

create clustered index ci on RFTest (SnapshotDt, SnapshotKey, LoanNum)

create nonclustered index nci on RFTest (SnapshotDt, SnapshotKey, LoanNum)
    include ([GLSourceSystem],[Account],[FlowDescription])

I ran below query: (I use top 100 for test as the whole table will take long time if I wanted to run it)

select top 100 *
from RFTest with (index(ci)) -- force index
where LoanNum = 2712

select top 100 *
from RFTest
where LoanNum = 2712

LoanNum column exists in both indexes, part of key in clustered and included in non-clustered.

Execution plan shows the engine choose non-clustered "nci" index, NOT the clustered one.

I like to know why.

Clarification:

To me in both case SQL read the same amount of data. and LoanNum is in both index and BTW, the LoanNum is part of key so It seems to me that it makes more sense if it use clustered index.

The indexes are exactly as I posted. There were some comments in queries, when I captured plan. the query you see in the post is correct. I don't want to keep both index, I was trying to see which one performs better and the question came to me.


texts: How to get the size of an index created in a plpgsql block

texts:
I'm trying to benchmark something. I want to create an index and get it's size in a plpgsql block.

This works:

haki=# create index ix on t(n);
CREATE INDEX

haki=# select pg_relation_size('ix');
 pg_relation_size
------------------
         22487040

When I try the same in a plpgsql block, I'm getting ERROR: relation "ix" does not exist:

haki=# DO $$DECLARE
haki$#     s int;
haki$# BEGIN
haki$#     PERFORM 'create index ix on t(n)';
haki$#     SELECT pg_relation_size('ix') INTO s;
haki$#     RAISE NOTICE 'size is %', s;
haki$# END$$;
ERROR:  relation "ix" does not exist
LINE 1: SELECT pg_relation_size('ix')

I'm guessing it has something to do with the fact that DDL is transactional and the block is not committed (I might also be wrong!). However, I am unable to find a way around that.

Is it possible to get the size of an index created in a plpsql block?


texts: On long-running queries, which exact time is current_timestamp?

texts:
I've got a query that checks for new patients in the ER, and if they have been there before in the last 72 hours, it notifies the killbo... uhhh... care managers.

I could set this up as a trigger, but I don't think this is a good idea.

What I want to do instead is set it up as a job that runs every 5 minutes, with this in the where clause:

    datediff(minute, TriageDateTime, current_timestamp) <= 5

But the query itself takes 03:31.

Question: is current_timestamp the timestamp of the beginning of the query?

here's the entire big, bad query:

    with visits as(
    select LocationID, ab.RoomID, ab.UnitNumber as MRN, datediff(year,ab.BirthDateTime, CURRENT_TIMESTAMP) as age,
    ed.TriageDateTime, ab.AdmitDateTime,
    ab.ReasonForVisit as reason, ab.DischargeDateTime,  ab.PtStatus,
    lag(ed.TriageDateTime,1) over (partition by ab.UnitNumber order by ed.TriageDateTime) as last_TriageDateTime,
    lag(ab.ReasonForVisit,1) over (partition by ab.UnitNumber order by ed.TriageDateTime) as last_reason,
    lag(ab.DischargeDateTime,1) over (partition by ab.UnitNumber order by ab.AdmitDateTime) as Last_DischargeDateTime,
    lag(ab.PtStatus,1) over (partition by ab.UnitNumber order by ab.AdmitDateTime) as Last_PtStatus

    from
    [eih-dr01].livefdb.dbo.EdmAcct_Main ed
    left join
    [eih-dr01].livendb.dbo.AbstractData ab
    on ed.VisitID = ab.VisitID
    where
    TriageDateTime >= dateadd(hour, -100, current_timestamp)
    and
    ab.UnitNumber is not null)

     select *, datediff(hour, last_TriageDateTime, TriageDateTime) as time_out
     into #temp
     from visits
     where datediff(hour, last_TriageDateTime, TriageDateTime) between 0 and 72
     and datediff(minute, TriageDateTime, current_timestamp) <= 5

     declare @mrn varchar(55),
     @reasonforvisit varchar(255),
     @lasttriage varchar(255),
     @lastreasonforvisit varchar(255),
     @text nvarchar (max),
     @sql nvarchar (max),
     @subject varchar(255)

     declare c cursor local for
     select MRN, reason, cast(cast(last_TriageDateTime as date) as varchar(55)) + ' ' + convert(varchar(5), last_TriageDateTime, 108), last_reason
     from #temp

     open c
     fetch from c into @mrn, @reasonforvisit, @lasttriage, @lastreasonforvisit

     while @@FETCH_STATUS = 0
     begin

    set @text = 'Patient ' + @mrn + ' is in the ED for ' + isnull( @reasonforvisit, 'unkown reasons') + '. Their last visit was '
    + @lasttriage + ' for ' +  @lastreasonforvisit
    print @text

    set @subject = 'ED Return visit'

    EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'NoReply',
    @recipients =
    '**********@vtext.com;',

    @subject = @subject,
    @execute_query_database = 'Master',
    @body = @text,
    @body_format = 'html'

    fetch next from c into @mrn, @reasonforvisit, @lasttriage, @lastreasonforvisit
     end
     close c
     deallocate c
     select * from #temp
     drop table #temp


texts:
I have a question about how to manually set SPN for using Kerberos authentication on a SQL cluster.

Do I set one SPN on the clustername or one on each node?

I also have a default named instance, do i specify the name of the instance?


texts: How do I find out when a node was added to an availability group?

texts: Ideally, I am looking for T-SQL that returns two columns: the node name, and the date/time that node was added to the availability group, for all nodes in a given availability group.


texts: Avoid foreign keys on an 800-GB database?

texts:
This question is for the experienced developers out there.

My friend recently told me that his employer has a legacy code base whose MySQL database has now grown to around 800 GB in size. What's interesting is that there are very few foreign key constraints, resulting in some duplicated bad data, but since that doesn't harm anything (apparently), nobody is overly concerned.

Now I can't imagine life without foreign keys, so when I asked him why there were no such checks, he replied by saying that a foreign key is another index that the database has to maintain, and takes more space.

Yes, space. So here's my question: Just how much more space can an index take? 10% of the DB? 20%? Even if his reasoning was wrong, there's no denying that foreign keys entail a performance penalty. Would you, as a developer, let go of foreign keys and instead rely on code to do proper housekeeping once the DB approached 1 TB in size?


texts: MySQL: Loop on each column value & split column by delimiter & replace numbers with values from another column

texts:
I would like to loop on each value of path column & split content by '/' delimiter & replace numbers with values from another column using MySQL & store result into another new column path_long.
Example
I have a column named path with content like '/426/427/428'. I would like to replace category id numbers with category names. Result would be like '/Computers/Other accessories/Laser printers'.
I have this partially working query:
CREATE TEMPORARY TABLE IF NOT EXISTS
t1
ENGINE=MyISAM
AS (
    SELECT
         n AS nr
         , SUBSTRING_INDEX(SUBSTRING_INDEX((SELECT TRIM(LEADING '/' FROM '/426/427/428')), '/', tmp.n), '/', -1) AS catid
         , (
                SELECT name FROM category
                WHERE category.id = catid
            ) AS name
         , (
                SELECT path FROM category
                WHERE category.id = catid
            ) AS path
    FROM
      (SELECT @rownum := @rownum + 1 AS n, category.id, category.name, category.path
            FROM category
            CROSS JOIN (SELECT @rownum := 0) r
        ) AS tmp
    GROUP BY catid
    ORDER BY
       n

);

/* SELECT * FROM t1; */
SELECT group_concat(name SEPARATOR '/') as path_long FROM t1;
DROP TEMPORARY TABLE t1;

Result is:
path_long
/Computers/Other accessories/Laser printers

Here is the test DDL:
CREATE TABLE `category` (
    `id` INT(11) NOT NULL AUTO_INCREMENT,
    `name` VARCHAR(100) NOT NULL,
    `path` VARCHAR(100) NOT NULL DEFAULT '0',
    PRIMARY KEY (`id`)
)
COLLATE='latin1_swedish_ci'
ENGINE=InnoDB
AUTO_INCREMENT=429
;

Also test data:
INSERT INTO `category` (`id`, `name`, `path`) VALUES (1, 'A', '/1');
INSERT INTO `category` (`id`, `name`, `path`) VALUES (2, 'B', '/1/2');
INSERT INTO `category` (`id`, `name`, `path`) VALUES (3, 'C', '/1/2/3');
INSERT INTO `category` (`id`, `name`, `path`) VALUES (4, 'D', '/4');
INSERT INTO `category` (`id`, `name`, `path`) VALUES (5, 'E', '/4/5');
INSERT INTO `category` (`id`, `name`, `path`) VALUES (6, 'F', '/4/5/6');
INSERT INTO `category` (`id`, `name`, `path`) VALUES (7, 'G', '/7');
INSERT INTO `category` (`id`, `name`, `path`) VALUES (8, 'H', '/7/8');
INSERT INTO `category` (`id`, `name`, `path`) VALUES (9, 'I', '/7/8/9');
INSERT INTO `category` (`id`, `name`, `path`) VALUES (426, 'Computers', '/426');
INSERT INTO `category` (`id`, `name`, `path`) VALUES (427, 'Other accessories', '/426/427');
INSERT INTO `category` (`id`, `name`, `path`) VALUES (428, 'Laser printers', '/426/427/428');

Unfortunately I can't change the design. It is given in the software. Horrible schema or not, horrible database or not, this is what I have. The framework uses this schema, and the database is MySQL. I have to do the query on this system and I have to get the desired result.
Problem
How to execute this query in a loop for each value of column named path and store the result to column named path_long?
Source column looks like (showing undecoded paths):
path
/426/427/428
/1/2/3
/4/5/6
/7/8/9

Desired result column should be like (showing decoded paths):
path_long
/Computers/Other accessories/Laser printers
/A/B/C
/D/E/F
/G/H/I

Basically it should decode a path having category ids to readable path format using category names.
I showed examples how the result should look like.
I added DDL to create test environment.
How can be better clarified?
How can I help more?
Now I added test case, why the question is still closed?


texts: Do MongoDB's "read uncommit" and "write lock" conflict with each other?

texts:
I was reading the MongoDB documentation, where it says about read uncommit: MongoDB allows clients to read documents inserted or modified before it commits these modifications to disk, regardless of write concern level or journaling configuration[1] and about locks: Locks help guarantee that all writes to a single document occur either in full or not at all[2].

My question is, if the write operation has a lock, then why user can read the uncommitted data?


texts:
I'm copying data from a Postgres DB on AWS RDS to a Postgres-like DB on AWS Redshift.

Varchar arrays are being exported as {foo,bar} but Redshift wants them as ["foo","bar"].

Redshift doesn't actually have an array type, instead you store it as varchar and read it as a JSON array with their JSON functions.

         json_arrays          | is_valid_json_array
------------------------------+---------------------
 []                           | t
 ["a","b"]                    | t
 ["a",["b",1,["c",2,3,null]]] | t
 {"a":1}                      | f
 a                            | f
 {foo, bar}                   | f
 {"one", "two"}               | f
 [x,y,z]                      | f
 [1,2,]                       | f

Is there a way to use COPY TO and transform the array style, or to configure Postgres to use another array format?


texts: Mysql daemon failed to start: unknown variable password=xxx

texts:
Mysqld don't restart anymore...In the log, the apparently obvious reason:

mysqld started
[Warning] Ignoring user change to 'root' because the user was set to 'mysql' earlier on the command line
[ERROR] /usr/libexec/mysqld: unknown variable 'password=mypassword'
mysqld ended

/etc/my.cnf there is nothing relevant and there is no such variable, which by the way has the correct mysql root password value in the message (!!!?):

[mysqld]
user=mysql
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

Mysql 5.0.95 on Centos 5.9. It worked ok before restarting!!

Tried also

 /usr/bin/mysqld_safe --datadir=/var/lib/mysql --pid-file=/var/run/mysqld/mysqld.pid --no-defaults
Starting mysqld daemon with databases from /var/lib/mysql
STOPPING server from pid file /var/run/mysqld/mysqld.pid
mysqld ended


texts: How to model parent-child relationship correctly in multi-tenant, multi-schema Postgres database

texts:
I have a multi-tenant SaaS app where each tenant gets their own schema. When a user creates an account, I create a new schema and run a bunch of migrations to create tables under that schema. Then, when the user wants to authenticate, they provide their tenant info (this can be their subdomain, e.g. mycompany.coolapp.com, or it can be a unique account ID), and the app routes subsequent queries to that tenant's schema.

However, certain pieces of tenants' data will be public. For instance, the user will need to create invoices and email their links (containing a UUID) to their customers. When the customer clicks the link, the app needs to be able to figure out which tenant the invoice belongs to, based on the UUID.

To do this, I created a "public.invoices" table, with two columns: uuid and tenant. The latter contains the tenant's schema name, which I can then use to perform the subsequent queries (i.e. the invoice's actual data) to the appropriate schema.

Question 1: Is this design sound? Is there some other, more standard way of routing unauthenticated/pre-authenticated requests to specific schemas in situations where each tenant gets their own schema?

Question 2: How do I tell Postgres that, when the tenant deletes the invoice (the one in their schema), the public record (under "public.invoices") also should be deleted? I'm trying to avoid using two queries, if some database-provided option or feature exists. I looked at CASCADE, but it seems that only goes from parent to child, and as far as I can tell there isn't a way to define a foreign key relationship in this scenario where the tenant invoice is the parent record.


texts: Setup MongoDB replica servers with different power?

texts:
Is it a good practice to setup replicas with the same machine size (wrt. CPUs, RAM, Bandwidth, etc.) or can replicas be based on smaller machines? One usecase might be to have a small non-voting backup replica machine. Another is to save money on the replica "backend".

The only problem I see with voting replicas is that in case the primary (big machine) goes down the new primary would be weaker. Are there other problems with clusters using different machine sizes?


texts: Convert SQL Server 2012 SSIS package to 2008

texts:
I've created a few SSIS packages in SQL Server 2012 that I need to run on SQL Server 2008. I've been told that it is impossible in their current state.

Is there a way to convert the 2012 packages to a 2008 version?

If not, what are my alternatives? Start over?


texts: Access Sharing database over Network vs Mysql multiple users

texts:
Something no one mentions and I find important to stress is that Access has a file locking mechanism for sharing the database on a network share which is orders of magnitude more efficient than any network database out there and therefore MySQL is no different.

The reason is the following, a file locking mechanism permits all clients of an Access database to cache somehow the database file and therefore to execute queries on it as if it were a local file. This of course means very little data is sent over the network in the case for instance of a SELECT which returns 5000 records, because the select is actually returned from the cached file.

A traditional client server model such as Mysql instead means that a query for 5000 records will involve a lot of TCP packets between server and clients to return the whole 5000 records from server to client, that will involve a high latency between the query and the completion of the return of results.

A file locking mechanism of course has its limits which usually incur when more than 5 users share a database heavily with a lot of modifications to it, in that case the locking mechanism (which in Access can be very selective up to row locking) will cause more delay than say a client server database which could theorically allow a lot more of concurrency.

I came therefore to the conclusion that if you need to share a database on a LAN with a couple of users therefore Access has few competitors, if instead you need to host a database on the internet or with tens/hundreds of users of course Mysql is better.

I also notice many other databases tried implementing the file locking mechanism but most failed in stability or weren't able to make it work as efficiently as Microsoft with Access which must have spent a good load of time and money on it. SQLite and H2 database locking mechanisms for instance are either very low performance or are highly not recommended for risks of corruption of the database:

  SQLite uses POSIX advisory locks to implement locking on Unix. On
  Windows it uses the LockFile(), LockFileEx(), and UnlockFile() system
  calls. SQLite assumes that these system calls all work as advertised.
  If that is not the case, then database corruption can result.

http://www.sqlite.org/lockingv3.html

  This database file locking mechanism uses native file system lock on
  the database file. No *.lock.db file is created in this case, and no
  background thread is started. This mechanism may not work on all
  systems as expected. [...] This feature is relatively new. When using
  it for production, please ensure your system does in fact lock files
  as expected.

http://h2database.com/html/advanced.html#file_locking_serialized

Can anyone shed some light on how or why is Access so efficient and whether there are any serious competing products which follow the same file locking shared on a network approach and which implement it in an efficient way?


texts: What's the point of the relationship diamond in Entity-Relationship diagrams?

texts:
I'm drawing an ER diagram for an ecommerce site that I'm building, and noticed something: the diamond that I am drawing between two models (or entities), which I understand is meant to symbolize the relationship between the entities, seems irrelevant.

The diagram could be drawn without the diamonds, and it would still show everything that I am conveying if I just connected entities with -|---|0- and similar connectors, without a diamond in between.

So what are the diamonds for?


texts: Postgres and Windows AD using windows authentication

texts:
I am working with SQL SERVER 2012 and postgres 9.4.x version on windows OS. The service-x on this machine is running under user-1 and user-1 is member of AD group ADG1.

during the installation of our product one of the step is to create the required login ADG1 on sql server and postgres server. this is happening fine.

one of the operation this service does is create new database in both SQL SERVER and corresponding in Postgres with same name upon user's single request.

During database creation, service is making connection to both data bases with windows authentication. So the AD Group login ADG1 is present in SQL SERVER and user-1 under which account this service is running is member of this AD group. So it has appropriate permission. Database creation is happening fine on SQL SERVER. though I do not know how the SQL SERVER resolves AD Group authentication.

My main issue is, service is not able to connect to postgres using windows authentication. I have set the SSPI authentication method in pg_hba.config file and hence the service is trying find login "user-1" which doe snot exist on postgres server.

So my question is how Do I tell postgres in pg_hba config file to search this user name in AD groups and use that GroupName instead the username.

please let me know if any specific information you may need to understand the situation.

Thanks

Update:1

I find out this post which is very old and not sure if since then postgre has not changed this behavior but basically under this post point number (4) is indicating that even thought postgres support windows authentication using SSPI authentication method, it does not support AD Groups Directly. We still have to create those individual user in that AD Group in postgres as login.

BIGGEST OBSTACLE TO POSTGRESQL ADOPTION


texts: What is the easiest way of finding out the number of blocks a table (oracle) occupies and the number of extents?


texts: Would it be sensible to create a MySQL user for each user account of a web application?

texts:
The thought occurred to me recently, but I have a suspicion the idea might be insane. A couple of benefits I'm perceiving are:

facilitate automated logging via triggers
control mysql connections on a per-(web-application-)user level

Obviously this would yield me a ridiculously long table of users, but any penalties of that aren't clear to me yet: the User table is properly indexed for O(Log(n)) single-user lookups.


texts: Transfer data from RO PostgreSQL (readonly) server to other MySQL server

texts:
I've got a PostgreSQL (v10) DB with a 5 tables (3GB+ per table) which get new records on a rate of 15-20/second. Every day records older than 90 days are deleted.
I need to run analytics on the data every minute, however the tables are missing 2 indexes, so running the analytics on the data is a no-go (takes longer than 1 minute).
Are there any methods to transfer data (semi-)live to another server (pref MySQL, as its an already available production DB server), or any suggestions I can try?


texts: Number of rows between two hours regardless of date

texts:
I want to get all records between two consecutive hours. But I want to show all rows regardless of day, month or year.

I have an approximation but it doesn't works when the range of hours cross to new day (23:00 to 01:00).

SELECT *
from
 dba_hist_sqlstat stat,
 dba_hist_snapshot snap
where snap.instance_number = stat.instance_number and stat.snap_id=snap.snap_id and SQL_ID='f2kmv0hafq9c3'
 AND TO_CHAR(snap.BEGIN_INTERVAL_TIME,'HH24MISS') >= 000000 AND TO_CHAR(snap.BEGIN_INTERVAL_TIME,'HH24MISS') <= 040000
ORDER BY 1

Regards


texts: postgresql Insert into table B based on foreign key in table A, if exists in A else

texts:
the scenario is as follows:

I have an invoice and supplier table. The invoice references supplier on supplier_id. I want to insert into invoice. If supplier_company does not exist in the supplier table, first insert into supplier then use the new supplier_id in invoice, else use the supplier_id of the matching supplier_name.

I've managed to get here:

insert into supplier (supplier_company) select ('Company Name') where not exists (select 1 from supplier where supplier_company='Company Name');
insert into invoice (invoice_number, supplier_id) values (xxx, (select supplier_id from supplier where supplier_company = 'Company Name')

but I was wondering if there's a cleaner, clearer way to express this in one expression.

Thank you for your time.


texts: Can we assign mysql Key_buffer_size value for particular table?

texts:
In some documents I found that we can assign key_buffer_size to myisam table. This also includes the following steps:

SET GLOBAL myisam_table.key_buffer_size = 4194304;

CACHE INDEX test.myisam_table IN myisam_table;

LOAD INDEX INTO CACHE test.myisam_table;

This command worked fine.

But how can I see the MyISAM table that is taking the key_buffer_size not the global one?

Is there any other MySQL engine that supports something similar?


texts: How to determine which tables use a sequence in SQL Server

texts: I have a sequence in a SQL Server 2012 database that appears in sys.sequences. How do I determine which tables use this sequence? I do not have sufficient permissions to view the definition of this system view so can't dig around for clues.


texts: Does software exist for transforming data from one DB schema to another?

texts:
I need to transform some data between two different database schemas. For example, I have one table in old schema like this:

and I need to transform it to new schema like this:

So I have few questions to ask:

Does there exist software that can migrate data between these two schemas (Without or with little usage of SQL)?
If it exists, can this software migrate even between two platforms (i.e. MS SQL Server to Oracle)?

Thanks!


texts:
I can not find a simple way to get a copy of the nightly backup I take of my Amazon RDS postgres instance so I can restore it on my local machine to play with.

Right now I'm doing a pg_dump when I need a copy, but it would be nicer (and faster, presumably) to be able to download those snapshots. Is this not possible?


texts:
How to implement constraints that go beyond a single row in PostgreSQL?

Examples 1:
With 3 tables: Person, Food which has a field called type, and Purchase which links Persons to Food. How can I enforce that a person can only purchase food of the same type from previous purchases if any?

Examples 2:
With 5 tables: Session, Auth which has a sessionId and accountId fields, Account, Link which links Accounts to Users, and User. How can I enforce that a Session can only link to Accounts (via Auth) that are linked to the same User (via Link)? In other words, a Session cannot link to Accounts that are linked to different Users.

Thank you!


texts: how to find which user performed DML operations in a table in oracle? Need a query to find out from DB?


texts: How to see urls of incoming SQL Server traffic?

texts:
We are migrating the dns entry for our SQL Server database from an machine name to a C-Name, in preparation for moving it from a bare metal box to a virtual machine.  We have switched all the connection strings we know about and also issued instructions across our department, asking everyone to switch their connections in case there are one-off apps or db connected spreadsheets, etc.

We would like to monitor the traffic coming into the box to verify that there is no incoming traffic that got to the server via the old machine name.  Is there any way to do this?  What I need specifically is the url that all of the clients used to reach the server.  We tried monitoring the port with Wireshark but that didn't seem to provide the info we needed.

Thanks!


texts: For states (col) receiving exactly X number of items (row), sort the items by weight (col)

texts:
I have been trying to figure this out for a few hours now but haven't gotten it. Each row in the table is a package.

I am able to get the States with the exact number of packages, but don't know how to then query only the packages with one of those states.

SELECT State FROM testing
 GROUP BY State
 HAVING COUNT(State) = 7;

Can anyone point me in the right direction?

So from the data below, it would return the 10 packages from GA and MI, sorted by weight.

-------------------
| State | Weight  |
-------------------
| AL    | 29.745  |
| AZ    | 54.4081 |
| CA    | 3.79151 |
| CA    | 94.0579 |
| CT    | 68.6374 |
| CT    | 80.6644 |
| CT    | 55.8415 |
| FL    | 5.49145 |
| FL    | 76.6451 |
| FL    | 7.02167 |
| GA    | 84.3802 |
| GA    | 5.51498 |
| GA    | 12.9253 |
| GA    | 88.4529 |
| GA    | 38.8692 |
| MN    | 82.009  |
| MN    | 33.893  |
| MO    | 10.9912 |
| MS    | 41.8613 |
| MT    | 21.3695 |
| MI    | 18.9998 |
| MI    | 71.6003 |
| MI    | 72.0666 |
| MI    | 67.8444 |
| MI    | 72.2397 |
| ND    | 5.66484 |
| ND    | 80.8316 |
| NJ    | 81.8038 |
| NJ    | 14.91   |
| NJ    | 55.238  |


texts:
I have got two queries, but I just want one result:
1 Query, give me all the data of the column
 SELECT * FROM dbo.Notificacion
 FOR JSON PATH, ROOT('notification')

2 Query give me all the column names
 SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'notificacion' ORDER BY ORDINAL_POSITION
 FOR JSON PATH, ROOT('COLUMN_NAME')

This way, I can make the view of the application more dynamic.
I want JSON something like this:
{
  "COLUMN_NAME": [
    {
      "COLUMN_NAME": "ID"
    },
    {
      "COLUMN_NAME": "Active"
    },
        {
      "COLUMN_NAME": "date"
    },
        {
      "COLUMN_NAME": "dateUpdate"
    }
  ],
    "notification": [
    {
      "ID": 1103,
      "Active": 1016,
      "date": 0,
      "dateUpdate": 0
    },
    {
      "ID": 1103,
      "Active": 1016,
      "date": 0,
      "dateUpdate": 0
    },
        {
      "ID": 1103,
      "Active": 1016,
      "date": 0,
      "dateUpdate": 0
    }
  ]

}


texts: Different Date format in predicate when comparing against a date or datetime

texts:
I have the following query:

SELECT  CAST(MyDateTimeCol AS DATE) AS MyDate,
        Col1,
        Col2
FROM    MyTable
WHERE   MyDateTimeCol >= '2019-11-25' AND MyDateTimeCol < '2019-11-26'

Which gives me the error

  The conversion of a varchar data type to a datetime data type resulted
  in an out-of-range value.

If I change the WHERE clause to

WHERE   MyDateTimeCol >= '2019-25-11' AND MyDateTimeCol < '2019-26-11'

This works.

However, I change my original query to the equivalent (performance issues aside)

SELECT  CAST(MyDateTimeCol AS DATE) AS MyDate,
        Col1,
        Col2
FROM    MyTable
WHERE   CAST(MyDateTimeCol AS DATE) = '2019-11-25'

The query works without any errors

Why does casting the datetime as a date mean I have to use a different Datetime format in the predicate?


texts: Example of loading into RDS from S3 using SQL*Loader

texts:
Does anyone have a working sample of loading data into an Amazon AWS RDS Oracle database using SQL*Loader where the source file is on S3?

In principle it's clearly possible. The Amazon RDS Data Import Guide for Oracle documents using sqlldr. But the only sample there loads data from a local EC2 instance.

I have worked with RDS and with S3 and with SQL*Loader before, but I haven't worked with all 3 together. One sample would be really useful. (Related advice would be welcome too... but a sample is all I'm really looking for.)


texts:
I have 2 tables "a" and "b".

Table "a"

| property | element | ply | ..

Table "b"

| property | ply | material | ..

What I want to do is to add a column to the first table called "material" and provide values based on the following rule:

if a.element < 300000 write 80000 else write the value you would get from joining tables "a" and "b" on (property and ply)

What I wrote for the above is this:

ALTER TABLE a ADD COLUMN Material INTEGER
UPDATE a SET Material = CASE
    WHEN a.Element <= 300000
        THEN 80000
    ELSE b.Material FROM
        a JOIN b ON (a.PCOMP = b.PCOMP AND a.Ply= b.Ply)
    END

  I am using SQLite 3.14.1

But this does not go through. Any ideas?


texts:
create view ENTRP_SERVS.V_IRS_BULK as
Select
  Atr.Ssn_Tx
,Names.First_Na
,Names.Middle_Na
,Names.Last_Na
,Reff.Ref_Cd_Desc_Tx Suffix_Na
,MBR.PRSN_MBRSH_ID,
T_PRSN_MBRSH_TAX.FILTER_TYPE_CD

 From Hix.T_Enrt En Inner Join Hix.T_Prsn_Enrt Pe On En.Enrt_Id = Pe.Enrt_Id And En.Enrt_Status_Cd In (428,439, 430 ) And Pe.Active_In = 'Y' And (Pe.Retro_In Is Null Or Pe.Retro_In = 'N')
Left Outer Join Hix.T_Aptc Ap On En.Enrt_Id = Ap.Enrt_Id
Left Outer Join Hix.T_Prsn_Addl_Attr Atr On Pe.Prsn_Mbrsh_Id = Atr.Prsn_Mbrsh_Id
Left Outer Join
    (
                    Select nAc.Prsn_mbrsh_Id,  First_Na , Middle_Na , Last_Na, Suffix_Cd From Hix.T_Prsn_Name Na, Hix.t_prsn_mbrsh_prsn_name_ac nAc
                                                   where na.prsn_name_id = nAc.prsn_Name_Id
    ) Names On Pe.prsn_mbrsh_id = Names.prsn_mbrsh_id
Left Outer Join Hix.T_Ref_Data Reff On Names.Suffix_Cd = Reff.Ref_Data_Id
Left Outer Join Hix.T_Ref_Data Re On Atr.GENDER_CD = Reff.Ref_Data_Id
Left Outer Join Hix.T_PRSN_MBRSH MBR on MBR.PRSN_MBRSH_ID =  Pe.PRSN_MBRSH_ID

Left Outer Join

(SELECT (CASE WHEN FILLING_STATUS_CD IN (2061,2062,2063) OR  (FILLING_STATUS_CD=2063 AND PRIM_TX_FILER_IN ='Y') THEN 'PRIMARY'
             WHEN FILLING_STATUS_CD=2063 AND PRIM_TX_FILER_IN ='N' THEN 'SPOUSE'
             WHEN FILLING_STATUS_CD IN (2066, 2068) THEN 'DEPENDENT'
             ELSE NULL
             END) AS FILTER_TYPE_CD,PRSN_MBRSH_ID FROM HIX.T_PRSN_MBRSH_TAX  WHERE TAX_FL_YR_NB=YEAR(CURRENT DATE)-2)T_PRSN_MBRSH_TAX
    ON PE.PRSN_MBRSH_ID=T_PRSN_MBRSH_TAX.PRSN_MBRSH_ID WHERE T_PRSN_MBRSH_TAX.FILTER_TYPE_CD IS NOT NULL

I have this view and I want to remove the apostrophe (') from the FIRST_NA, MIDDLE_NA and LAST_NA columns. How can I do that?


texts:
I have this table which is used to generate second table with the result:

INSERT INTO payment_transactions_daily_facts (count, volume, date, year, month, week, day, transaction_type, contract_id, merchant_id, terminal_id, status, card_brand, currency)
SELECT
        COUNT(*) count,
        SUM(amount) volume,
        DATE(created_at) date,
        YEAR(created_at) year,
        MONTH(created_at) month,
        WEEK(created_at) week,
        DAY(created_at) day,
        type transaction_type,
        contract_id, merchant_id, terminal_id,
        status, card_brand, currency
        FROM payment_transactions
        WHERE created_at BETWEEN '2018-11-11' AND '2018-11-14'
        GROUP by date, contract_id, merchant_id, terminal_id, transaction_type, status, card_brand, currency

When I execute only the select statement I get:

count   volume  date    year    month   week    day transaction_type    contract_id merchant_id terminal_id status  card_brand  currency
1   3000    2018-11-11  2018    11  45  11  Sale    1   2   1   approved    NULL    USD

But I can't execute the INSERT. I get error:

Field 'id' doesn't have a default value

I use for I sequence:

CREATE TABLE `payment_transactions_daily_facts` (
  `id` int(11) NOT NULL,
  `card_brand` varchar(255) DEFAULT NULL,
  `contract_id` int(11) DEFAULT NULL,
  `count` int(11) DEFAULT NULL,
  `currency` varchar(255) DEFAULT NULL,
  `date` datetime DEFAULT NULL,
  `day` int(11) DEFAULT NULL,
  `merchant_id` int(11) DEFAULT NULL,
  `month` int(11) DEFAULT NULL,
  `status` varchar(255) DEFAULT NULL,
  `terminal_id` int(11) DEFAULT NULL,
  `transaction_type` varchar(255) DEFAULT NULL,
  `volume` int(11) DEFAULT NULL,
  `week` int(11) DEFAULT NULL,
  `year` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

How I can use sequence for id?


texts: Is a mySQL or mongoDB relational database scalable for holding 80 GBs of additional logs per day?

texts:
I am currently deciding on a long term architecture solution for storing DNS logs. The amount of data we are talking about numbers some 80 GBs of logs per day at the peak. Currently I am looking at noSQL databases such as mongoDB, as well as relational - mySQL. I want to structure a solution that has three requirements:

Storage: This is a long term project, so I want the necessary capability to store 80 GBs of logs per day (~30 TB a year!). I realize this is pretty ridiculous, so I'm willing to have a retention period (keep 6 months' worth of logs = 15 TB constant).
Scalability: As it is a long term solution, this is a big issue. I've heard that mongoDB is horizontally scalable, while mySQL is not? Any elaboration on this would be very well received.
Query speed: As close to instantaneous querying as possible.

It should be noted that our logs are stored on an intermediary server, so we do not need to forward logs from our dns servers.


texts: getting the total of amount of money of each user between two dates even if no record is found

texts:
I am trying to get the total amount of money which the user spent between a certain period. The code that I have come up with can successfully get the result but I also want to show the users who didn't spend any money on that certain period.

The code I have come up with:

select guest_id,
    sum(spent)
from shopping_t
left join
(
    SELECT ADDDATE( CURDATE() - INTERVAL 3 MONTH, INTERVAL @i:=@i+1 DAY) AS days
    FROM (
        SELECT a.a
        FROM (SELECT 0 AS a UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS a
        CROSS JOIN (SELECT 0 AS a UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS b
        CROSS JOIN (SELECT 0 AS a UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS c
    ) a
    JOIN (SELECT @i := -1) r1
    WHERE
    @i < DATEDIFF(NOW(), NOW() - INTERVAL 3 MONTH)
) as target
on shopping_date = target.days
where shopping_date = target.days
group by guest_id

The code above gives me this result:

id  spent
0   220000
2   10000

My desired output is:

id  spent
0   220000
1   0 <----- I also want to display users who didn't suspend anything on that period
2   10000

guest_t table:

id  name    address     telphone
0   George  Summerwind  042968084
1   Lisa    Laplace     042968084
2   Anne    Windhill    042968084
3   Mike    Fairview    042168084

shopping_t table:

id  spent   date
0   150000  2016-03-02
0   50000   2016-01-03
0   20000   2016-02-16
1   1500    2014-01-02
1   3400    2014-01-03
2   9000    2016-01-02
2   1000    2016-01-03

I have tried crossing date list query and guest_t and left join to shopping_t but I can't figure out what is wrong: http://sqlfiddle.com/#!9/57f9d/3


texts: What does exchangevent indicate in SQL deadlock graph?

texts:
Have a deadlock XDL file with several exchange events listed.  Not sure if these are good or bad or what causes them.  What causes exchange events in SQL deadlock graph?
 <exchangeEvent id="Pipe12dfb4bc900" WaitType="e_waitPipeGetRow" nodeId="28">
   <owner-list>
    <owner id="process4a9ee4eca8" />
   </owner-list>
   <waiter-list>
    <waiter id="process2612ca8" />
 </waiter-list>
  </exchangeEvent>


texts: Can you recover to a point in time using the LDF?

texts:
Possible Duplicate:
  How do I get back some deleted records?

A coworker accidentally ran an update statement that needs to be reverted. The last backup is about 12 hours old.

Is there any way to rollback to a point in time just using LDF file?

Every example I was able to find mentions *.trn files to be used...


texts: How do I copy a materialized view to a table?

texts:
I have a materialized view. I want to create a copy of it so I can modify some of the data to test a few theories about an issue and its possible solutions.

How do I make a copy of this MV as a table? The copy will reside in the same schema but will have a different name. the copy should be an identical copy (structure and data) of the MV, it's just going to be a table so I edit the data.


texts: SSRS 2016 report manager using m/d/y date parameter format in Chrome

texts:
I've got a 2016 SSRS server, set up with UK regional settings.  When using Chrome, date parameter fields are using the US mm/dd/yyyy format instead of the expected UK dd/mm/yyyy:

Edge and IE are fine:

The date picker itself works fine, and the reports run normally in all browsers.  All reports are set to use en-GB language, and this happens on all client systems I've tried it on.  As far as I can tell all the server region and local settings are correct.

Any thoughts on how I can get SSRS to use the correct format in Chrome?


texts: Is it possible for unique key to break master-master replication in mysql?

texts:
I have a master-master mysql setup with 2 servers running the exact same application making writes to such a table:

 CREATE TABLE `metric` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `host` varchar(50) NOT NULL,
  `userid` int(10) unsigned DEFAULT NULL,
  `name` varchar(255) NOT NULL,
  `sampleid` tinyint(3) unsigned NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique-metric` (`userid`,`host`,`name`,`sampleid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

auto_increment_increment is 2 and offsets are 0 and 1, so PK ids don't clash, but is it possible that with bad timing, 2 applications will create a row with an equal unique-metric index breaking replication on both mysql servers, since replication thread won't be able to insert replicated row into table due to another row already having the exact same index?


texts: SQL: How do you create a table inside of a function

texts:
For some reason, whenever I attempt to create tables and drop them within an SQL function, I'm met with a syntax error. When I do them outside it's always fine but inside the function it becomes a problem.
I've truncated my code significantly but this is similar to what I have:
DECLARE

CREATE TABLE inputCategories(
      Category varchar(255)
   );

DROP TABLE inputCategories;

return 0
END ;
$$ LANGUAGE plpgsql ;

I am required to do all table operations within this function that will be run several times, any help is greatly appreciated, thanks in advance!


texts: Query slow on subscriber but fast on publisher (Merge Replication)

texts:
As the title suggests, I am having issues with performance with my subscriber. I run the exact same query (it's a recursive query with a few joins but nothing too serious). I can run this query on my publisher in under 1s but it takes over 30s on the subscriber.

Things I've taken into account so far:

This is the only subscriber
The two servers are practically identical in terms of computing power (in fact the sub has more firepower).
The indexes are the same on both but the publisher has an MSmerge_index (which I think should only exist on the publisher).
It's a partial merge but this subscriber doesn't have filtered articles (in other words it has the full data set) so the traffic should be identical besides users running select queries (only 30 users online right now with infrequent requests)

I have duplicated my environment onto development servers and see the same results (slow subscriber, fast publisher).

Is there anything I can do to improve this performance? Some index or modification? Or is this something that I have to live with?

Thanks in advance!


texts:
I have 3 tables A,B,C.
A is parent of B and C.
I want to get the all A rows with sum of B and sum of c.

Ex:
table A:

id
1
2
3

table B:

id, parentid, data
1,  1,        12
2,  1,        23
3,  2,        24

table C:

id, parentid, data
1,  2,        44
2,  3,        23
3,  3,        24

what I want to get is:

A.id, B.sum, c.sum
1,    35,     0
2,    24,    44
3,     0,    47

Is it possible to get the output in single query.


texts: Configuration of TCP/IP with SSL and TLS for Oracle

texts: I am trying to configure the connections to my Oracle database with tcp / ip using ssl I have been guided by the procedure of the site https://oracle-base.com/articles/misc/configure-tcpip-with-ssl-and-tls-for-database-connections site in which I have learned a lot, but nevertheless this procedure after configuring everything without apparent problems shows me the error ORA-29106: Can not import PKCS # 12 wallet. Please, I need help, thank you very much.


texts:
Is there any way in Postgres to apply a function over a results of an array (or any sort of collection)?

For example if I have a function create function add1... that increments a number by one, is there any way to do something like:

select map add1 array[1,5,8...];

and getting back

array [2,6,9...]

?

I am not sure if it has any sense over a select as it can map a function on the single result directly:

select add1(x) from X;

Also, is there any concept of laziness here? All the functions will be 'strictly' applied?


texts: How to retrieve disk spaces by removing tables from tablespaces in Oracle 10g?

texts:
I'm using a Oracle 10g database.
I have a big tablespace MYTBS made of a dozen data files (MYTBS_01, MYTBS_02, ...) of different sizes which have been added through the years.

Now i have removed very big tables from this tablespace and i would like to retrieve disk space on the server.

How can i be able to free up space/blocks from the tablespace ?


texts: Trying to upgrade PostgreSQL 9.1 to 9.4 on Debian

texts:
After upgrading my Debian development box I have both PostgreSQL 9.1 and 9.4 on my system.  I want to have 9.4 serve my data and get rid of the outdated version.

AFAICS, it is important to use a working directory where the postgres user can write, and configure the old and new services in a way which allows them to run simultaneously (using different ports).  Thus, this is what I tried:

# cd /tmp/
# sudo -u postgres /usr/lib/postgresql/9.4/bin/pg_upgrade \
--check \
--retain \
--old-bindir=/usr/lib/postgresql/9.1/bin/ \
--new-bindir=/usr/lib/postgresql/9.4/bin/ \
--old-datadir=/var/lib/postgresql/9.1/main \
--new-datadir=/var/lib/postgresql/9.4/main \
--old-options /etc/postgresql/9.1/main/postgresql.conf \
--new-options=/etc/postgresql/9.4/main/postgresql.conf \
--old-port=5433 \
--new-port=5432

This failed (because of postmasters serving the running services).

The --old-port and --new-port options mirror the settings in the configuration files; thus, both services are able to run at the same time.  I stopped them both and tried again:

Performing Consistency Checks
-----------------------------
Checking cluster versions                                   ok

*failure*
Consult the last few lines of "pg_upgrade_server.log" for
the probable cause of the failure.

connection to database failed: could not connect to server: No such file or directory
        Is the server running locally and accepting
        connections on Unix domain socket "/tmp/.s.PGSQL.5433"?

could not connect to old postmaster started with the command:
"/usr/lib/postgresql/9.1/bin/pg_ctl" -w -l "pg_upgrade_server.log" -D "/var/lib/postgresql/9.1/main" -o "-p 5433 -b /etc/postgresql/9.1/main/postgresql.conf -c listen_addresses='' -c unix_socket_permissions=0700 -c unix_socket_directory='/tmp'" start
Failure, exiting

The log file pg_upgrade_server.log contains:

-----------------------------------------------------------------
  pg_upgrade run on Mon Jul 18 11:46:33 2016
-----------------------------------------------------------------

command: "/usr/lib/postgresql/9.1/bin/pg_ctl" -w -l "pg_upgrade_server.log" -D "/var/lib/postgresql/9.1/main" -o "-p 5433 -b /etc/postgresql/9.1/main/postgresql.conf -c listen_addresses='' -c unix_socket_permissions=0700 -c unix_socket_directory='/tmp'" start >> "pg_upgrade_server.log" 2>&1
waiting for server to start....postgres: invalid argument: "/etc/postgresql/9.1/main/postgresql.conf"
Try "postgres --help" for more information.
 stopped waiting
pg_ctl: could not start server
Examine the log output.

As far as pg_ctl --help tells me, there is no -b option; the file /etc/postgresql/9.1/main/postgresql.conf does exist.

What is going on here, and how can I get the upgrade done?


texts: Access will be the front end but I don't want to use ODBC on everyone's PC. Do you have any suggestions on how Access can communicate to SQL without using ODBC?


texts: MySQL Replication without stopping master

texts: I am currently setting up a replication for my database one of the ways i am thinking of synchronizing the master and slave without write locking the master, is by first taking a log position then perform a backup, then restore the backup on the slave and start the slave with the log position(which will be before the backup was taken), one of the issues i see with this approach is the slave will be reapplying changes to it self which is not a problem in the type of system we have, does any one else see any other pitfalls with this approach? Or are there any other better options to achieve this?


texts: Merging Continous Date Ranges with upto 63 days in between


texts: Select rows, where 3 columns have the same values

texts:
I have a structure like this:

 col0 col1 col2 col3
 1    1    1    1
 1    1    1    2
 1    1    1    3
 1    2    1    1
 1    2    3    1
 2    3    4    5
 2    3    4    9

I now want to select all rows, that match other rows on the first three columns, so the result of this query using the sample data would be:

 col0 col1 col2 col3
 1    1    1    1
 1    1    1    2
 1    1    1    3
 2    3    4    5
 2    3    4    9

How can I achieve this, I'm kinda stuck at the early beginning...

Thanks in advance!
Alex

Update: Clearified the question. Thanks Erwin.


texts:
I have a table with product descriptions, and each product description has a product_id and a language_id. What I want to do is update all of the fields with a language_id of 2 to be equal to the same product_id where the language_id is 1.

So far I've tried the following query, but I'm getting errors indicating that MySQL doesn't want to update a table where the table's also being used in the subquery.

UPDATE
  products_description AS pd
SET
  pd.products_seo = (
    SELECT
      pd2.products_seo
    FROM
      products_description AS pd2
    WHERE
        pd2.language_id = 1
    AND pd2.products_id = pd.products_id
  )
WHERE
  pd.language_id <> 1

Is there a "simple" way around this limitation in MySQL? Or any "tricks"? I'm a little surprised that my query doesn't work, as it seems logical.


texts: What should I do with this large database filling up the disk?

texts:
I have a SQL Server 2008 R2 Standard Edition warehouse database of around 400GB and face daily hard disk full issues. I've checked table sizes and row counts. Below is an image of what I've got:

What steps should I take in order to maintain the database size on a daily basis without any data loss?


texts: Linking Two database to execute join query

texts:
I have a remote odbc connexion and an MySQL database in my localhost
i ask if there is a way to execute a join query ( linking the two database ) ?

thanks.


texts: Is it possible to have two copies of a table clustered in different arrangements seamlessly?

texts:
I have a table with around 100M rows.  It only gets data inserted once/day but we need to do selects a lot.  The selects are usually simple but need to return 100s of thousands of rows sometimes.

It's unique based on three columns node_id, pricedate, hour which are integer, timestamp, integer respectively.  It was slow for most queries but I clustered it to node_id, pricedate and that fixed the slowness for most queries.  Those queries were of the type:

select * from mytable where node_id in (1,2,3,4)

We still occasionally need to do queries like:

select * from mytable where pricedate>='2016-05-01'

These are still slow because it's clustered by node_id first. We have an index on pricedate already. The issue is that the users often need enough data that the query engine throws out the index and uses a seq scan. Once it's using a seq scan, it benefits greatly from having the data clustered in the way that it is being queried.  This leads to the problem that I have where some queries benefit from one clustering and other queries from the other:

It would be nice if there was a way to have two physical copies of the table where one copy is clustered one way and the other is clustered another but user access to it appears as though there is only 1 table and the DB engine would ensure they're in sync. Obviously there'd be write penalties in doing this but that's inconsequential for our usage.

Would something like this be possible?

I'm guessing there isn't a built in way to do what I describe. To do it anyway I guess I'd make a table called mytable_dup with the same unique key constraint but with the alternate clustering and then setup triggers to insert to it whenever the master is inserted/updated/deleted.  That seems doable but from here, would there be a reasonable way to select from the duplicated table that will be efficient?

I'm running PostgreSQL 9.4 at home and 9.5 on Google.


texts: Why do PostgreSQL examples say `+ integer '7'`?

texts:
The PostgreSQL date functions documentation contains the following example:

date '2001-09-28' + integer '7'

Why does it consist of converting a string to an integer? Why not phrase it like this:

date '2001-09-28' + 7

Is there some tangible reason why integer '7' is preferable to plain old 7?


texts:
I have two databases - one is 11g and another 12c. 12c db has the exact data of 11g (expdp/impdp). Now I'm facing an error in one of the tables with unique constraint error. I have checked the structure of the table and constraints enabled for the table in both the databases, that is same as well. Now how can I sort this out? I'm facing this error on the application which is running on it, when I click one of the tabs.
Caused by: java.sql.SQLIntegrityConstraintViolationException: ORA-00001:unique constraint (USER1.HIGHT_PK) violated is the error i'm getting


texts: Is there a way to get SQL Server capcity constraints dynamically

texts:
I have some code which creates database tables dynamically, and I want to ensure that people don't try to do things that result in me trying to create a table with more columns than what SQL Server allows.

According to this article, the limit for the max number of columns per (nonwide) table is 1024.

However, this can obviously change in the future, so I don't want to hardcode it in my app.

Is there a way to get this value dynamically using a SQL query (e.g. by reading from some system table or proc)?


texts: Dynamically change schema to accommodate custom fields!

texts:
I am working on building a sort of a cms for a particular project. Nothing that will have a large volume of records in any table. Within 6 digits max.

I was thinking to create the default schema for all tables but then allow the editors to add custom fields to certain tables by adding fields to the schema. Obviously there would be the option to also drop fields.

I'm not an expert but neither a noob and I am sure I can write the proper codes to not get things mixed up and make a mess of it.

But still would this have any impact on the data integrity if changes are made on populated tables?

What are the hazards I could come across?


texts: Snapshot_isolation_state_desc on a Read only/Stand by Database

texts:
I have configured Log Shipping where the primary server database has snapshot_isolation_state_desc reported as off.  I tried to turn it off on the secondary which is a standby read only by using the GUI and by using the T-SQL statement to disable:

ALTER DATABASE ABC SET ALLOW_SNAPSHOT_ISOLATION OFF;

However, it turns back on every time the SQL Server restarts.

Is this expected behaviour? If so, why?


texts: Casesensitive vs. Caseinsensitive Collatesequences?

texts:
I just started working with the SQLite database places.sqlite.

Fact is that this database uses case sensitive collate sequences making writing queries as funny as in Oracle.

I want to know, are there really any applications where the user wants case sensitive sorting.

Or is the default use of case sensitive sorting a heritage of the fact that binary sort order is cheaper than the other forms.

EDIT:

Just in case you are curious. SQLite can handle caseinsenitive compares. see this SO question

BTW what is the state of art with Oracle. We migrated our SQL databases to ORACLE at the time of 8i and never checked, if we can do it caseinsensitive today.


texts: MySQL: querying for latest messages in flat reply system

texts:
I've created a message system schema in MySQL as follows. It consists of two tables (messages, recipients).

`Message_ID` int(11) NOT NULL AUTO_INCREMENT,
`Message_Sender_ID` int(11) NOT NULL,
`Message_Sent_Time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`Message_Subject` varchar(255) NOT NULL,
`Message_Text` text NOT NULL,
`Message_Sender_Keep` boolean DEFAULT TRUE,
`Message_Root_ID` int(11),
PRIMARY KEY (`Message_ID`)

`Recipient_Message_ID` int(11) NOT NULL,
`Recipient_User_ID` int(11) NOT NULL,
`Recipient_Keep` boolean DEFAULT TRUE,
`Recipient_Read_Date` timestamp DEFAULT NULL,
PRIMARY KEY (`Recipient_Message_ID`, `Recipient_User_ID`)

Here's how it works. When a user creates a new message, the message's root_id is set to it's own id. When someone replies, the root id is set to the root message of that thread. The recipient information is also stored in the recipients table.

What I'm stuck on is querying the latest message of each unique thread. Instead, it's returning all of the unique threads, but only the root messages (I want the newest ones).

SELECT
  M.*,
  R.*
FROM messages M INNER JOIN recipients R
  ON M.Message_ID = R.Recipient_Message_ID
WHERE (R.Recipient_User_ID = $user_id OR M.Message_Sender_ID = $user_id)
GROUP BY M.Message_Root_ID HAVING MIN(M.Message_Sent_Time)
ORDER BY M.Message_Sent_Time DESC

I've been trying to figure this out for myself, but I've just looked at it for way too long. Anyone able to help me on this?


texts: How to speed up ORDER BY sorting when using GIN index in PostgreSQL?

texts:
I have a table like this:

CREATE TABLE products (
  id serial PRIMARY KEY,
  category_ids integer[],
  published boolean NOT NULL,
  score integer NOT NULL,
  title varchar NOT NULL);

A product can belong to multiple categories. category_ids column holds a list of ids of all product's categories.

Typical query looks like this (always searching for single category):

SELECT * FROM products WHERE published
  AND category_ids @> ARRAY[23465]
ORDER BY score DESC, title
LIMIT 20 OFFSET 8000;

To speed it up I use the following index:

CREATE INDEX idx_test1 ON products
  USING GIN (category_ids gin__int_ops) WHERE published;

This one helps a lot unless there are too many products in one category. It quickly filters out products that belong to that category but then there is a sort operation that has to be done the hard way (without index).

A have installed btree_gin extension allowing me to build multi-column GIN index like this:

CREATE INDEX idx_test2 ON products USING GIN (
  category_ids gin__int_ops, score, title) WHERE published;

But Postgres does not want to use that for sorting. Even when I remove DESC specifier in the query.

Any alternative approaches to optimize the task are very welcome.

Additional information:

PostgreSQL 9.4, with intarray extension
total number of products currently is 260k but expected to grow significantly (upto 10M, this is multi-tenant e-commerce platform)
products per category 1..10000 (can grow up to 100k), average is below 100 but those categories with large number of products tend to attract many more requests

The following query plan was obtained from smaller test system (4680 products in selected category, 200k products total in the table):

Limit  (cost=948.99..948.99 rows=1 width=72) (actual time=82.330..82.341 rows=20 loops=1)
  ->  Sort  (cost=948.37..948.99 rows=245 width=72) (actual time=80.231..81.337 rows=4020 loops=1)
        Sort Key: score, title
        Sort Method: quicksort  Memory: 928kB
        ->  Bitmap Heap Scan on products  (cost=13.90..938.65 rows=245 width=72) (actual time=1.919..16.044 rows=4680 loops=1)
              Recheck Cond: ((category_ids @> '{292844}'::integer[]) AND published)
              Heap Blocks: exact=3441
              ->  Bitmap Index Scan on idx_test2  (cost=0.00..13.84 rows=245 width=0) (actual time=1.185..1.185 rows=4680 loops=1)
                    Index Cond: (category_ids @> '{292844}'::integer[])
Planning time: 0.202 ms
Execution time: 82.404 ms

Note #1: 82 ms might not look that scary but that is because sort buffer fits into memory. Once I select all columns from products table (SELECT * FROM ... and in real life there are about 60 columns) it goes to Sort Method: external merge  Disk: 5696kB doubling execution time. And that is only for 4680 products.

Action point #1 (comes from Note #1): In order to reduce memory footprint of sort operation and therefore speed it up a little it would be wise to fetch, sort and limit product ids first, then fetch full records:

SELECT * FROM products WHERE id IN (
  SELECT id FROM products WHERE published AND category_ids @> ARRAY[23465]
  ORDER BY score DESC, title LIMIT 20 OFFSET 8000
) ORDER BY score DESC, title;

This brings us back to Sort Method: quicksort  Memory: 903kB and ~80 ms for 4680 products. Still can be slow when number of products grows to 100k.


texts: Setting user accounts for Form Authentication SSRS

texts: Is there a separate procedure to set up user/log in accounts for Form Authentication in Reporting Service? Can you refresh me on this? I am having difficulties with Log in to my report server. All I can find is Window Authentication Log in Configurations and was unable to find out about Form Authentication properly. It seems really difficult for me to do especially when I am not familiar with those. Hope someone could shed some light.


texts: What is the consensus on using NOT NULL constraints on all text-based fields in a table?

texts:
Some of the tables I have come across don't have any NOT NULL constraints, which has yielded to some gotchas when querying.

I'm curious what the feelings are with using something like NOT NULL DEFAULT '', which would replace the need of the coalesce() function when retrieving/filtering records.  On the other hand instead of coalesce, you'd have to use CASE statements or, if you still wanted to use coalesce, you'd do something like coalesce(nullif(field_foo,''), field_bar)


texts:
Currently, I am transitioning a server from a company to another.

They want to keep SQL Server installed on the server so we are wiping clean all the databases, maintenance plans and jobs.

I'm trying to delete a bunch of SQL Server Agent jobs but even though they are disabled they are still on "IDLE" mode so it raises me the following error when trying to delete them:

  Drop failed for Job job_name.Subplan_1.  (Microsoft.SqlServer.Smo)

  An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

  The DELETE statement conflicted with the REFERENCE constraint FK_subplan_job_id. The conflict occurred in database "msdb", table dbo.sysmaintplan_subplans, column job_id.

  The statement has been terminated. (Microsoft SQL Server, Error: 547)

How can I remove the IDLE status from the job so the delete gets through?


texts: pg_restoring into a table with a not null column

texts:
I'm merging two databases. The newer one has a table with an integer column failed_port which is NOT NULL. The older database does not have this column.

So after using pg_dump to dump both databases and then pg_insert to merge them there is an error:

pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 2336; 0 58976 TABLE DATA c_a postgres
pg_restore: [archiver (db)] could not execute query: ERROR:  null value in column "faild_port" violates not-null constraint
DETAIL:  Failing row contains (1, A, B, 2000, null, 2019-01-19 16:27:15.335126, Tool).
    Command was: INSERT INTO c_a (c_a_pk, c_b_fk, name, port, row_created, row_creator) VALUES (1...

The table in older database looks like this:

c_a_pk | c_b_fk | name | port  |        row_created         | row_creator
-------+--------+------+-------+----------------------------+-------------
     1 | A      | B    |  2000 | 2019-01-19 16:27:15.335126 | Tool

And in the newer like this:

c_a_pk | c_b_fk | name | port | failed_port |        row_created         | row_creator
-------+--------+------+------+-------------+----------------------------+-------------
     1 | C      | D    | 2000 |           0 |2019-02-19 16:27:15.335126  | Tool

How to merge the older table with the newer? Is there for example a command for pg_restore to automatically set default value to 0 if there is a NOT NULL column that doesn't exist in first table? I could do this manually, but I'm planning to merge multiple databases so it would be burdensome to do this by hand.


texts: I can't create a transactional replication

texts:
I have two databases identically in two SQL Server 2014 instances.

The problem is as follows. I can access from server A to B, I can create the publication in server A and access to that publication from server B. But when I finish to create the subscription in server B, I can't see it in server A.

The servers are in two different LANs and the connection is through two proxy servers.


texts:
I want to know if concurrently UPDATE .... LIMIT N will not overlap with more than one client querying the db.

After UPDATE ... LIMIT N the client will make a SELECT with some client_id assigned.
I don't want clients to overlap results, so every client will have different records each time the query the db with the SELECT after the update.

This depends on table engine?

The update locks the table/records? (I am sure this one depends on engine).

From my opinion UPDATE LIMIT should not overlap results, dbs have ACID property...I want to confirm this.


texts: Is it acceptable to have a default value on a foreign key

texts:
Say we have a tabled called Account that has a foreign key FK_CurencyID, is it acceptable to hard set the default value of the FK_CurrencyID?

For example, if we wanted the default currency of all accounts to be GBP. In the currency table, GBP has a primary key of 50. The primary key data type is an int identity(1,1) field.

The problem I see see with setting the default of Account.FK_CurrencyID to 50 is that:

Since the PK of currency is an identity, the PK of GBP may be different across instances (e.g. It may be different on Live, UAT and on any of the developers local machines). There are ways to force the PK to be 50 for GBP initially so we can work around this as long as nothing changes.
Since the currency table may change, we could end up in a situation where we have to delete GBP as an option, which would cause the system to fall down until we update the default value.

I think the problem ultimately comes down to mixing / coupling code & system design with data (is there a word for this?)

Is it acceptable to set the default foreign key value to a hard coded ID or should we remove the default and have it handled in the logic layer of the application?


texts: CTE column caused an overflow - Order By only!

texts:
;WITH
cte_Date ( DateCode_FK ) AS (
    SELECT  DATEADD( DAY,
                1 - ROW_NUMBER() OVER (
                        ORDER BY so1.object_id ),
                GETDATE() )
    FROM    sys.objects so1
    CROSS APPLY sys.objects so2 )
SELECT  TOP 10 d.DateCode_FK
FROM    cte_Date d
ORDER BY d.DateCode_FK DESC;

Nothing an overly interesting query, but i'm receiving an error message if I run it with the ORDER BY clause:

Msg 517, Level 16, State 1, Line 4
Adding a value to a 'datetime' column caused an overflow.

However, without the ORDER BY clause, it runs just fine.  In addition, if I run the query on other catalogs contained in the same instance on the same server, the query runs fine with or without the ORDER BY clause.
I've taken a look at the configuration options and compatibility levels between the affected catalog and a catalog on which the query runs as expected, but have not found anything that might warrant the difference.  Has anybody else run into a similar issue?  I can work around it for now, but would ideally need to be able to fix the problem, whatever it is.
Potential hint - if you have a relatively large number of objects in a catalog ( > 5000 ), you -may- be able to reproduce the error... This is occurring on my largest catalog and it appears that if I include a TOP in the CTE, the ORDER BY issue goes away.


texts: Improving indexing of jsonb data when the json structure is well defined

texts:
I have a simple table records that contains a primary key (id) and a jsonb field (data). The structure of the jsonb is consistent across all rows. I have created a view (metadata) for this table which extracts a particular value category, which I can then run queries against.
CREATE VIEW metadata AS
SELECT id as id,
       data -> 'some_key' -> 'some_array' ->> 0 as category
FROM records;

(the data structure is not controlled by me)
I can use this VIEW to perform queries like:
SELECT category
FROM metadata
WHERE category = 'category_0';

and it behaves as expected.
I was experiencing slow performance out of the above query (and similar equality queries), so I added an index as follows:
CREATE INDEX metadata_category_idx ON records ((data -> 'some_key' -> 'some_array ->> 0));

This has not improved my performance as I would have expected when I query for a particular category using string equality. It should be noted that for "categories" with a small number of rows, EXPLAIN ANALYZE tells me its using the index, however for "categories" with a large number of rows, it falls back to sequential searching. There are approximately 350k rows in my table. I've tried each type of index (i.e. GIN, GIST) with no measured benefits.
I intend to strictly use string equality when querying this view, and I have little control over the structure of the json object in data.
What would be the best way to construct my index such that the above query can run more efficiently?


texts:
I have a SQL Server 2008 instance which I want to use to import data from an Oracle server.

I have set up a linked server that works correctly when running simple queries like SELECT * FROM table. However, if I declare a variable or loop through the rows in a table or anything else inside the OPENQUERY, I get errors.

Is it possible to do this using OPENQUERY? Do I need additional permissions?


texts: What's better for large changes to a table:  DELETE and INSERT every time or UPDATE existing?

texts:
I am making a project where I need to change around 36K records in one table daily. I'm wondering what will perform better:

delete rows and insert new ones, or
update already existing rows

For me it is easier to just delete all the rows and insert new ones, but if this is going to fragment the table and indexes and impact performance  then I would prefer to make updates where possible and delete/insert only when necessary.

This is going to be a nightly service and I am not looking to improve the speed of the process itself. I am more concerned about the performance of queries against this table in general where I already have 89 million records and how this nightly process will affect it.

Should I delete/insert records or should I update existing ones (where possible) for this nightly process?


texts: Query data via username - pair - best performance

texts:
RDBMS: MariaDB 10.3
Let's say you have a table which stores specific information (info_1, info_2, etc.) according to a given pair of usernames; like so:
+----------+----------+----------+----------+----------+------+--..
|  user_1  |  user_2  |  info_1  |  info_2  |  info_3  |  ..  |  ..
+----------+----------+----------+----------+----------+------+--..

where the values of user_1 and user_2 each equal a specific & distinct username (VARCHAR(50)), and are defined as multi-column index (user_1,user_2) of the table upon creation.
The queries my app does search in function of:
A) a given user pair:
WHERE user_1 = name_1 AND user_2 = name_2 OR user_1 = name_2 AND user_2 = name_1
B) a given user / return all the records where a specific user is one of the members:
WHERE user_1 = name_1 OR user_1 = '%' AND user_2 = name_1
Problem: I must make sure that searches are always indexed. When using an OR clause, a full table scan is however executed each time.
SOLUTION I)
Thanks to this answer, possible queries resulting in indexed searches for these cases is:
A)
SELECT * FROM my_table WHERE user_1 = name_1 AND user_2 = name_2
UNION ALL
SELECT * FROM my_table WHERE user_1 = name_2 AND user_2 = name_1;

B)
SELECT * FROM my_table WHERE user_1 = name_1
UNION ALL
SELECT * FROM my_table WHERE user_1 = '%' AND user_2 = name_1;

However with the drawback of always using two SELECT statements and one WHERE clause which will always be in vain (as there will always be one record per user-pair). That's why I attempted to further optimize the query by using a fulltext index, which resulted in the following table:
SOLUTION II)
+---------+----------+----------+----------+------+--..
|  users  |  info_1  |  info_2  |  info_3  |  ..  |  ..
+---------+----------+----------+----------+------+--..

where users has been defined as FULLTEXT VARCHAR(150), and its value always corresponds to the format name_1-name_2, whereas I used a hyphen because a comma is used in the MATCH...AGAINST syntax to execute fulltext index queries.
With this structure, I now get the following new query possibilities for A) and B):
A)
SELECT * FROM my_table WHERE MATCH(users) AGAINST ('"name_1-name_2","name_2-name_1"');

B)
SELECT * FROM my_table WHERE MATCH(users) AGAINST ('name_1');

Which in my opinion drastically facilitate the query structure, and use neither two SELECT statements, nor an additional WHERE clause which will be in vain. Still, as I don't know a lot yet about query performance optimization and the comparison of fulltext index searches with regular index searches, I wondered which solution of I and II may perform better, and why ? Or is there even a better approach?


texts:
Searching for DB2 PureScale supported hardware, I can see that for Linux distributions only, some System X and IBM BladeCenter BladeCenter HS22 are listed.

If I try to install PuseScale on a Fujitsu hardware, will it work. will IBM support it?


texts:
When MySQL can't create a table due to a foreign key constraint problem, it returns the following error

  Can't create table 'dbname.table_name' (errno: 150)

Is there anyway to have MySQL tell you (or to automatically lookup/analyze) why it couldn't create the table.  For example, if you attempt to create a table with 10 foreign keys, and one key is invalid, is there a way to have MySQL tell you which one is the problematic key?  Or do you need to rely on human intelligence and debugging to figure this out for yourself?


texts: Query planner slow to use newly created index on database under heavy load

texts:
We have some heavily loaded databases with more than 1k requests / sec and have started to have problem with index maintenance.

Sometimes we need to update/recreate one of the indexes we have, for example to remove bloat or tweak it to include/exclude a column. We do not want to have any downtime, and at the same time avoid doing these things between 4 and 6 am when there is less load.

What we usually do is:

Create new index concurrently (CREATE INDEX CONCURRENTLY ...)
Wait some time
Restart applications using the db
Wait some more
Drop the old index, hoping that the query planner will use the new index properly.

Is there some better way to handle index creation/rebuild?

As you can guess waiting an unspecified time and restarting the services that connect to the database is nonoptimal, and if you are unlucky the query planner still wont use the new index for a while which might be very bad for our application.

Our servers run PostgreSQL 9.3 on CentOS 6, we use pgbouncer to handle connections and our application using the db is coded in go.

On example just recently where I got bitten by this was when adding a column to an index on a table storing pictures:

-- table (some irrelevant columns not included)
CREATE TABLE pictures (
     id serial primary key,
     user_id integer not null,
     type type not null, -- enum with 2 different values
     picture_url varchar not null
);
-- old index
CREATE INDEX pictures_user_id_idx ON pictures (user_id);
-- new index
CREATE INDEX CONCURRENTLY pictures_user_id_type_idx ON pictures (user_id, type);
-- drop old index
DROP INDEX pictures_user_id_idx;

Right after dropping the old index the queries went very slow. I restarted the services that connect to the database and after a little while it was back to normal.


texts:
--skip-lock-tables

when i am taking mysqldump with this option or without this,I am not able to identify any difference. Tables are not getting locked in any of these cases. I thought mysql will lock tables without this option and after this option i am able to query table, however in both of this cases i am able to query to all the tables. Please suggest.


texts:
We have recently migrated our database mirroring secondary to new hardware. The log drive (and other SQL Server drives) on the secondary has been formatted to 64KB block size, whereas the primary server is on the default 4KB.

As a result of this we are getting the following entries in the log of the secondary almost constantly -

There have been N misaligned log IOs which required falling back to synchronous IO.  The current IO is on file L:\SQLSERVER\Logs\MyDatabase_log.ldf.

this article suggests a fix is to turn on trace flag 1800 on the server

  with 512-byte sector size as per Microsoft's recommendation

I have run the following command on both servers:

fsutil fsinfo ntfsinfo L:\

and can see for the primary:

Bytes Per Sector  :               512
Bytes Per Physical Sector :       512
Bytes Per Cluster :               4096
Bytes Per FileRecord Segment :    1024

and the secondary:

Bytes Per Sector  :                512
Bytes Per Physical Sector :        4096
Bytes Per Cluster :                65536
Bytes Per FileRecord Segment :     1024

Therefore it seems traceflag 1800 should be enabled on the primary.

With this in mind, I ran DBCC TRACEON (1800, -1);  on the primary and verfied it was on with DBCC TRACESTATUS

I then checked the error log on the secondary and can see the messages regarding Async IO still persist.

Does this traceflag need setting as a startup parameter (with subsequent reboot) to take effect? Obviously I would do this anyway so it persists reboots but just ran the DBCC TRACEON first so it is active

The documentation doesn't reference that a reboot is required.

Obviously the way to find out is to test but due to this being a production server, I would need a maintenance window to that which isn't available to me at the moment

Eventually, the primary server will be migrated to new hardware and I will ensure the disks are formatted in the same manner but for the tie being I was hoping this traceflag would provide a quick fix.

If I cannot get this to work, I suppose the intermediate option would be to reformat the log drive on the existing primary to 64KB block size


texts: What is the proper way to index a column in MySQL that has a lot of json data in it?

texts:
We have a long row that includes a json column that holds variable columns specific to each row.

We currently do not index that column as we don't query against it - rather it is supporting data for the result.

That table is now over 100,000 rows and pulling results from it is painfully slow.

We pull the data using its primary key - which is indexed, with a result set no larger than 200, but even at 20... it is very slow.

Slow in the reference is 5-20+ seconds.

The json data is not too large (we have blobs in other tables that are much larger and don't see a performance hit).

Is it a mistake to mark it as a json field? Should we just make it a long text field instead (since we do not actually query the data within it?) and avoid the overhead on the server? Or is there some index setting we are missing to ensure json fields are optimized for data pulls whether or not they are being queried...

Here is the structure of that table:

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

--  Table structure for caches

CREATE TABLE caches (
  id int(11) unsigned NOT NULL AUTO_INCREMENT,
  audit_id int(11) unsigned NOT NULL,
  audit_key int(11) unsigned DEFAULT NULL,
  building_key int(11) unsigned DEFAULT NULL,
  building_name varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  building_id int(11) unsigned DEFAULT NULL,
  project_id int(11) unsigned DEFAULT NULL,
  project_key int(11) unsigned DEFAULT NULL,
  amenity_id int(11) unsigned DEFAULT '0',
  lead_id int(11) unsigned DEFAULT NULL,
  lead_key int(11) unsigned DEFAULT NULL,
  status varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
  type varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
  type_total int(11) DEFAULT NULL,
  type_text varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
  type_text_plural varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
  program_total int(11) DEFAULT NULL,
  finding_total int(11) DEFAULT NULL,
  finding_file_status varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
  finding_nlt_status varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
  finding_lt_status varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
  finding_sd_status varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
  finding_file_total int(11) DEFAULT NULL,
  finding_file_completed int(11) DEFAULT NULL,
  finding_nlt_total int(11) DEFAULT NULL,
  finding_nlt_completed int(11) DEFAULT NULL,
  finding_lt_total int(11) DEFAULT NULL,
  finding_lt_completed int(11) DEFAULT NULL,
  finding_sd_total int(11) DEFAULT NULL,
  finding_sd_completed int(11) DEFAULT NULL,
  address varchar(250) COLLATE utf8_unicode_ci DEFAULT NULL,
  city varchar(250) COLLATE utf8_unicode_ci DEFAULT NULL,
  state varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
  zip varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
  followup_date date DEFAULT NULL,
  followup_description text COLLATE utf8_unicode_ci,
  auditors_json json DEFAULT NULL,
  amenities_json json DEFAULT NULL,
  findings_json json DEFAULT NULL,
  created_at timestamp NULL DEFAULT NULL,
  updated_at timestamp NULL DEFAULT NULL,
  amenity_inspection_id int(10) unsigned DEFAULT NULL,
  PRIMARY KEY (id),
  KEY audit_id (audit_id),
  KEY audit_key (audit_key),
  KEY building_key (building_key),
  KEY building_id (building_id),
  KEY project_id (project_id),
  KEY project_key (project_key),
  KEY amenity_id (amenity_id),
  KEY lead_id (lead_id),
  KEY lead_key (lead_key),
  KEY status (status),
  KEY type (type),
  KEY finding_file_status (finding_file_status),
  KEY finding_nlt_status (finding_nlt_status),
  KEY finding_lt_status (finding_lt_status),
  KEY followup_date (followup_date)
) ENGINE=InnoDB AUTO_INCREMENT=1019212 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;


texts: Pgpool executes queries on standby nodes instead of master when replication is behind in standby

texts:
I have a postgresql 10 master db with 2 hot standby servers with streaming replication, and the replication is working correctly. The synchronous_commit is setted to remote_write
Also I have a pgpool 3.7.5 configured with the params:
delay_threshold = 1
sr_check_period = 1

And the the following weights:

master: 1
node1: 3
node2: 3

In the log I can see the node1 and node2 are lagging:
Replication of node:1 is behind 75016 bytes from the primary server (node:0)

The pgpool docs says:

delay_threshold (integer)
Specifies the maximum tolerance level of replication delay in WAL bytes on the standby server against the primary server. If the delay exceeds this configured level, Pgpool-II stops sending the SELECT queries to the standby server and starts routing everything to the primary server even if load_balance_mode is enabled, until the standby catches-up with the primary. Setting this parameter to 0 disables the delay checking. This delay threshold check is performed every sr_check_period. Default is 0.

The problem it's that pgpool sends queries to the hot standbys before they obtained the new data from master through streaming replication.
I enabled the log_per_node_statement = on temporally to be able to see which node the query executes and I can see that queries are sent to the nodes even if there aren't sync when delay_threshold should avoid that.
Am I missing something? When the nodes are behind master the queries are not supposed to go the master?
Thanks in advance.
Other config values of pgpool are:
num_init_children = 120
max_pool = 3
connection_cache = off
load_balance_mode = on
master_slave_sub_mode = 'stream'
replication_mode = off
sr_check_period = 1