texts: Best practice for storing large files (+1GB each)

texts:
I am looking to build a big data platform for weather forecasting. I will be using a numerical model known as WRF which takes in GFS files and produces files which can be over 1GB each.

I will be using either AWS or Azure as my cloud provider.

These 1GB files will be processed and a lot of the  variables will be removed, reducing file size, but exactly how much - I do not know.

Now, the 1GB files may be needed later (it's expensive to run the model), so I do not want to throw them away.

Do I store them as files on a disk somewhere  (ie the cloud) or do I put them in a DB? Of course, the files will be compressed, but they are still going to be massive...

As for the type of data, the files are binary and the data is like a big spreadsheet but not every row will have the same data... what I mean is that row 1 might have 6 columns whereas row 2 might have 5 columns. To make it worse, the column names are not always identical and some may have a value where others may not. I can however write a script which will ensure the records all have the same column names. I realise some of this information may not be relevant, so I apologise if it is not.


texts: RedGate SQL Prompt - Cursor Snippet mistake

texts:
Using RedGate's SQL Prompt & loving it.  But I used the "curff" snippet for the first time today ("Fast-forward read-only cursor"), and it produced the following TSQL (which I've filled in to make it a concrete example):

DECLARE @variable INT
DECLARE Curse CURSOR FAST_FORWARD READ_ONLY FOR
    SELECT ID FROM #TempTable

OPEN Curse
FETCH NEXT FROM Curse INTO @variable

WHILE @@FETCH_STATUS = 0
BEGIN
    FETCH NEXT FROM Curse INTO @variable  --This seems wrong...

    PRINT ('Hello, I am # ' + CAST(@variable AS VARCHAR(10)))
END

CLOSE Curse
DEALLOCATE Curse

Isn't the location of the inner FETCH NEXT (inside the WHILE loop) incorrect?  It should be at the end of the loop, or at least after the query (queries) that need to use the variable, no?


texts: MySQL hang during "show collation" on Amazon RDS

texts:
MySQL is hanging when I try to do SHOW COLLATION on my Amazon RDS instance.  I can use SHOW COLLATION fine with my local database, and other SQL commands work fine on my RDS instance. But Amazon SHOW COLLATION hangs every time.  Maybe I have too many collations?  I'd be perfectly happy just setting it to utf8 or Latin1 collation.

I'm using mysql(5.6.12) on a micro instance.

Any ideas on what I can do? Or how can I get more information on what's happening?


texts: Even when user has 'bulkadmin' role, query says user does not have role - SQL Server 2012

texts:
I am facing a weird issue with SQL Server 2012.

I have a user (say RS\sqluser) which has bulkadmin role.

Now when i run this query

Select IS_SRVROLEMEMBER('bulkadmin', 'RS\sqluser')

output is 0. But when i execute this query

sp_helpsrvrolemember 'bulkadmin'

I can see RS\sqluser present in the list.

Can someone please help me understand how is the output differing or is it BUG in SQL Server 2012? (Can't be a BUG as same query with different user works fine)


texts: Will Partitions and Indexes on the same table help in performace of Inserts and Selects?

texts:
I have a table containing the list of visitors and this table has the following information.

Visitor Browser Information
Visitor Location Information
Visitor Time Information
No of Visits

I have a second table that maintains the history of each visits, which means I if the same visitor visits the site, I insert into the second table and update the no. of visits on the first table.

The kind of reports that I have to generate for this table are

Count of Visitors/day or days (Search Between days)
Count of Visitors/month
Count of Visitors/year
Count of Visitors/browser or grouped by browsers

On an average there are about 20000 inserts to the second table and about 15000 inserts to the first table, meaning 5000 were updates to the first table (5000 repeat visits).

I need to decide between partitioning the tables by month and sub-partitioning by days for the reports 1,2,3 and index the browser related columns for report 4.

There will be more reports in the future not sure on what clauses.

Does partitioning/sub-partitioning along with indexing help in the performance of inserts and selects?

Should I perform partitioning on both the tables?

I am currently using MySQL 5.5 + InnoDB


texts:
Does oracle database suffer from parameter sniffing issue in stored procedures?

Considering there are millions of pages written on parameter sniffing issue, I think that this is more of a bug than a feature.  Also considering with Sql Server 2017 features like automatic query tuning, I was wondering is SQL Server late in fixing these estimates/plan caching issues compared to other Database management system?


texts: Does sp_addtabletocontents Use Precomputed Partitions

texts:
I am using merge replication in SQL 2012.

When I bulk insert rows into a table, and run this stored procedure to add references into the merge tracking table,

sp_addtabletocontents

Is that missing out on precomputing the partitions using the precomputed partition optimisations?


texts: Join and Count based on Specific User ID in SQL

texts:
I have the following tables, I inserted some dummy data in it for demonstration purposes. I would like to join these two tables in a way that based on the user ID, I can get the count of similar questions other users answered. For Example : Let's say I want to see how many people in my Answer table answered age as 30 like Alex. So how can I join these two tables based on User.USERID="1" ? My expected result should be 1 since There is only one user "Tom" who is 30 years old and is "M" Male.

CREATE TABLE Question (
  QID INT,
  QTxt VARCHAR(150),
  QOrder INT);

CREATE TABLE User (
  UID INT,
  UserName VARCHAR(150));

CREATE TABLE ANSWER (
  AID INT,
  ATxt VARCHAR(150),
  QID INT,
  USERID INT);

INSERT INTO User VALUES
 (1, 'Alex'),
 (2, 'Sarah'),
 (3, 'Tom');

INSERT INTO Question VALUES
 (1, 'Age', 1),
 (2, 'Gender', 2),
 (3, 'Weight',3),
 (4, 'Height',4);

INSERT INTO Answer VALUES
 (1, '30', 1 , 1),
 (2, '27', 1,2),
 (3, '30', 1,3),
 (4, 'M', 2,1),
 (5, 'F', 2,2),
 (6, 'M', 2,3),
 (7, '61', 4,1),
 (8, '165', 3,1);

This is how I did it and it doesn't work :(

SELECT *
FROM Answer
JOIN Question
ON Answer.QID IN (SELECT Answer.QID FROM Answer WHERE Answer.USERID=3)
   AND Answer.ATxt IN (SELECT Answer.ATxt FROM Answer WHERE Answer.USERID=3)


texts: Error while running script to backup redo logs

texts:
I am very new to DB administration and i was hired to manage an enterprise database.

We have a standby Oracle DB server that runs on Linux.

There is an FRA on that standby that gets filled up quickly, my predecessor wrote a script to back up the redo logs on that FRA to another drive and then delete the redo logs from FRA.

The problem.

whenever I run that script i get this error from /var/spool/mail/oracle

RMAN-00554: initialization of internal recovery manager package failed
RMAN-04004: error from recovery catalog database:
            ORA-01017: invalid username/password; logon denied

Please help I can't afford that standby db not getting it's redo logs.


texts: Postgresql size of Slave is bigger than Master size

texts:
I have a Postgresql in master slave streaming replication. Master is located in four partitions (tablespaces).
df -h on Master shows me

Filesystem      Size  Used Avail Use% Mounted on
***
/dev/md121      880G  490G  346G  59% /mydb/1
/dev/md122      880G  613G  223G  74% /mydb/2
/dev/md123      880G  322G  514G  39% /mydb/3
/dev/md124      880G  506G  330G  61% /mydb/4

but on Slave it takes more disk space on /mydb/4 partition

Filesystem      Size  Used Avail Use% Mounted on
***
/dev/sdb        880G  613G  223G  74% /mydb/2
/dev/sda        880G  448G  388G  54% /mydb/1
/dev/sdc        880G  322G  513G  39% /mydb/3
/dev/sdd        880G  773G   63G  93% /mydb/4

And it grows. WAL files are located in /mydb/1. Where I was mistaken?

Config of Slave

wal_compression = on
autovacuum_naptime = 2s
autovacuum_analyze_scale_factor = 0
autovacuum_vacuum_scale_factor = 0
max_wal_senders = 5
autovacuum_analyze_threshold = 1000
checkpoint_timeout = 40min
temp_buffers = 3000MB
autovacuum_vacuum_threshold = 1000
autovacuum_vacuum_cost_delay = 100ms
wal_keep_segments = 1000
wal_level = hot_standby
autovacuum_vacuum_cost_limit = 5000
autovacuum_max_workers = 6
listen_addresses = '192.168.1.4'
max_wal_size = 100GB
hot_standby = on

Recovery.conf on Slave

standby_mode = 'on'
primary_conninfo = 'user=replication password=mysecretpassword host=master.mydomain.local port=5432 sslmode=prefer sslcompression=1 krbsrvname=postgres'

Config on Master

wal_compression = on
autovacuum_naptime = 2s
autovacuum_analyze_scale_factor = 0
autovacuum_vacuum_scale_factor = 0
max_wal_senders = 5
autovacuum_analyze_threshold = 1000
checkpoint_timeout = 40min
temp_buffers = 1GB
autovacuum_vacuum_threshold = 1000
autovacuum_vacuum_cost_delay = 100ms
wal_keep_segments = 6000
wal_level = hot_standby
autovacuum_vacuum_cost_limit = 5000
autovacuum_max_workers = 6
listen_addresses = '192.168.1.5'
cpu_index_tuple_cost = '0.0005'
wal_buffers = 16MB
checkpoint_completion_target = '0.9'
random_page_cost = 2
maintenance_work_mem = 32GB
max_wal_size = 60GB
synchronous_commit = false
work_mem = 2GB
cpu_tuple_cost = '0.001'
default_statistics_target = 500
effective_cache_size = 96GB


texts: SQL query too slow to fetch single row of data

texts:
we have this SP, running extremely slow to fetch a single row of data from  tables within 100K rows, which seems not that big. It takes onn avg 15-20 mins to complete.

We have tried to remove all possible Key lookups, table scans and there does not seem to be any missing index info as well.

Actual plan can be found here

SELECT @totalAssetCount = count(tblassetvalidationdataforemployee.assetId),
       @totalEmployeeCount = COUNT(DISTINCT tblassetvalidationdataforemployee.employeeid)
FROM   tblassetvalidationdataforemployee WITH(nolock)
       INNER JOIN tblbillingcscequipments WITH(nolock)
         ON tblbillingcscequipments.assetid = tblassetvalidationdataforemployee.Assetid
            AND tblbillingcscequipments.cycleid = tblassetvalidationdataforemployee.cycleid
            AND tblbillingcscequipments.cycleid IN (SELECT item
                                                    FROM   [SplitList](@CycleId, '~'))
       LEFT OUTER JOIN tblemployees WITH (nolock)
         ON tblassetvalidationdataforemployee.employeeid = tblemployees.employeeid
       INNER JOIN tblusagedescriptionmapping WITH(nolock)
         ON tblbillingcscequipments.UsageDescription = tblusagedescriptionmapping.UsageDescription
            AND CustomType IN ( 'Desktop', 'Laptop', 'Tabelet', 'Tablet',
                                'Workstation', 'Laptop/Tablet/Handheld' )
WHERE  tblassetvalidationdataforemployee.cycleid IN (SELECT item
                                                     FROM   [SplitList](@CycleId, '~'))
       AND ( emailid NOT IN (SELECT emailId
                             FROM   tblWaliDoNotSendEmailList) )
       AND assettypedescription IN (SELECT AssetType
                                    FROM   [tblWaliAssetTypes] WITH(nolock))
       AND ( Cdstatus != 'DISABLED'
              OR Cdstatus IS NULL )
       AND tblbillingcscequipments.operatingunit IN (SELECT item
                                                     FROM   [SplitString](@OperatingUnit, '~'))
       AND tblbillingcscequipments.Businessunit = @BU
       AND tblbillingcscequipments.[Org-tier1] IN (SELECT item
                                                   FROM   [SplitString](@Region, '~'))
       AND ( tblbillingcscequipments.[Org-tier2] = @Company
              OR @Company = (SELECT TOP 1 goby
                             FROM   tblwalioperatingunit WITH(nolock)
                             WHERE  company = tblbillingcscequipments.[Org-tier2]
                                    AND operatingunitdescription = tblbillingcscequipments.operatingunit)
              OR @company = 'All' );

Please suggest for any performance improvements.

Thanks


texts: Need suggestion for placing foreign keys in the databases

texts:
I have the database for customers. The customers table is something like this

+------------------+
|     Customers    |
+------------------+
| id  (PK)         |
| business_email   |
| business_name    |
| customer_name    |
| payment_terms    |
| currency         |
| business_address |
| city             |
| state            |
| postal_code      |
| country          |
| phone            |
| created_at       |
| updated_at       |
+------------------+

Now I want to put the shipping address in my application where the customer can optionally mention his shipping address.S o I have made an extra table for shipping address like this

+------------------+
| Shipping Address |
+------------------+
| id  (PK)         |
| contact_name     |
| contact_address  |
| delivery_address |
| created_at       |
| updated_at       |
+------------------+

Now my problem is I am little bit confused in whose key should be foreign key in which table.I am strucked in this.So any help and suggestion will be highly appriciable.


texts: Why do 59% of my Execution Plans have CONVERT_IMPLICIT warnings?

texts:
Yes, you read correctly, 59%.  Some of these are undoubtedly programmer error where they are comparing an INTEGER with a VARCHAR value, but of the ones I've surveyed (out of the 5139 execution plans with the problem) the only conversion has been a varchar to varchar comparison, like this ...

ScalarString="CONVERT_IMPLICIT(varchar(250),[CAMMS].[Entity].[t_PersonName].[LastName] as [pn].[LastName]+', '+[CAMMS].[Entity].[t_PersonName].[FirstName] as [pn].[FirstName]+' '+[CAMMS].[Entity].[t_PersonName].[MiddleName] as [pn].[MiddleName],0)"

This part of the stored proc is concatenating name parts.  The varchar(250) reference above appears to come from the fact that the Full-Name value is being inserted into a table with a column of type varchar(250)

CREATE TABLE #TmpMatter (
    ObjectType      varchar(50),
    ObjectID        int,
    MatterEntityID int,
    CaseNumber      varchar(100) null,
    Title           varchar(50),
    DefendantName   varchar(250) null ...

I checked COLLATION on both my database and on TempDB ... they are the same.  Checked COLLATION on all text columns and that is correct.  I have 12 columns whose ANSI PADDING is OFF but these CONVERT_IMPLICIT errors are showing up only on columns with it ON, so I'm not seeing any mismatch there.

One post said they fixed this problem by addressing the ANSI PADDING issue, but that can't be it ... unless my temp tables are being created with PADDING = OFF.  But TempDB (and all system dbs) have PADDING = ON.

I'm stuck on this one.  Anyone have any ideas?


texts:
Using IBM Informix database.
If I specify order_no manually, the statement works but if I try to increment it within the query, it fails.

Table structure

patkey serial
notetype char(4)
order_no int

The constraint is patkey, notetype and order_no

I can manually insert running the following and increment the order_no

insert into patnotes (patkey, notetype, order_no) values (5000, 'GEN', 3)

I wanted to increment it within the insert statement but I get a duplicate violation and don't understand how.

Here is the insert statement that I'm trying to use to increment the order_no.

INSERT INTO patnotes (patkey, notetype, order_no)
SELECT FIRST 1
    p.personkey,
    'GEN' as notetype,
    CASE WHEN n.order_no is null then 1 else (n.order_no + 1) end as order_no
FROM
    person p
LEFT JOIN
    patnotes n
    ON p.personkey = n.patkey
    AND n.notetype = 'GEN'
WHERE
    p.patid = '5000'
ORDER BY
    n.order_no DESC

Checking the table the following entries exist

5000, GEN, 1
5000, GEN, 2

The next order should be 3.

Running the select portion of the above insert statement gives:

5000, GEN, 3

This is correct but fails the constraint. If I enter these manually in the first insert statement, there is no issue.

Now the next odd issue is if I clear out these entries so the first entry is order_no 1.

This will add entries for order_no 1 and 2 but then fails for 3+ but again if I specify this manually in just an insert statement, no issues.

I'm not seeing what I'm missing


texts:
I'm trying to list all databases in existing Maintenance Plans. (or, more importantly those NOT in a plan...)

I'm using the followed code, based on this blog:

Select DISTINCT p.name as 'Maintenance_Plan'
, RIGHT(smpld.line4,LEN(smpld.line4)-6) as 'Backup_Type'
, sp.subplan_name as 'Subplan_Name'
, smpld.line3 as 'Database_Names'
, job.name As 'Job_Name'
From msdb.dbo.sysjobs As job
join  msdb.dbo.sysmaintplan_subplans sp
on sp.job_id = job.job_id
inner join msdb.dbo.sysmaintplan_plans p
on p.id = sp.plan_id
join msdb.dbo.sysmaintplan_log smpl
on p.id = smpl.plan_id
and sp.subplan_id =smpl.subplan_id
join msdb.dbo.sysmaintplan_logdetail smpld
on smpl.task_detail_id=smpld.task_detail_id
where job.[enabled] = 1
and smpld.line3<>''
and smpld.line4 like '%Full%'

However, sysmaintplan_logdetail.line3 is only a nvarchar(256) so doesn't store the complete list and I'm not sure what use this column is!  It's also comma separated and not easy to interrogate.
There is a Command column in sysmaintplan_logdetail.  This is nvarchar(max) and does indeed contain all the detail of the maintenance plan.  However it would take a lot of text manipulation to extract just a list of databases. Typical contents:

BACKUP DATABASE [SP2013_Enterprise_Search_LinksStore] TO  DISK = N''\\uk-sba-sql02\backups\SP2013_Enterprise_Search_LinksStore_backup_2015_11_16_113747_3942470.bak'' WITH  COPY_ONLY, NOFORMAT, NOINIT,  NAME = N''SP2013_Enterprise_Search_LinksStore_backup_2015_11_16_113747_3942470'', SKIP, REWIND, NOUNLOAD,  STATS = 10

Can anyone suggest a simple robust way of extracting database details from maintenance plans?  I'm on SQL Server 2012.  I normally use the Ola Hallengren Maintenance Solution, but I've inherited these.

Thanks

Ian


texts: Better performance to have nullable columns or an extra table?

texts:
There are many ways to get rid of nulls in tables, and I have heard of various reasons to get rid of them, but can anyone show me some real numbers as to how your database might perform differently using different models?

One Table:

A    B    C
1    La   NULL
2    Moo  7
3    Ba   9
4    La   NULL

OR These two tables:

A    B
1    La
2    Moo
3    Ba
4    La

X    C
2    7
3    9

Which is better?

(X is Foreign key to A)

The second set of tables would remove the nulls from the first design. When would be better to use each design?

This second pair of tables would achieve the same result, however it would be more flexible by naming the column as a cell:

First model (A, Arms Legs):

A    Arms    Legs
1    NULL    NULL
2    NULL    2
3    4       2
4    NULL    NULL
5    1       NULL

Second Model (XYZ):

X     Y     Z
2     Legs  2
3     Arms  4
3     Legs  2
5     Arms  1

The second one would remove nulls, but at what compromise?


texts: In SSMS "SQL Server Agent" sub folders are appearing twice

texts:
I have SSMS 17 installed on my system, after I connected to database instance, I found sub folders of SQL Server Agent are repeating.

Thanks!


texts: Query Multiple SQL Server Instances using Powershell referencing the list of instances in a text file

texts:
Rather than issuing a query per instance via SQL Server Management Studio, we are trying to create a powershell script to query multiple instances by going through a loop referencing a text file where it has let's say 100 SQL Server instances.  Go through each SQL Server instance, issue a query, and export to CSV.

The below is the powershell script we currently have:

$ServerInstance = "C:\Users\<NAME>\Documents\InstanceList.txt"

foreach ($i in $ServerInstance)
{

$sql = "SELECT
    DB_NAME(dbid) as DBName,
    COUNT(dbid) as NumberOfConnections,
    RTRIM(loginame) as LoginName,
       RTRIM(Hostname) As HostName, Login_Time,Program_name
FROM
    sys.sysprocesses
WHERE --DB_NAME(dbid) = 'genesys_wfm' and
    dbid > 5
       --and HostName = 'xxxx'
       and loginame not in ('NT AUTHORITY\SYSTEM','ACE','domain\xxxx')
GROUP BY
    dbid, loginame,Hostname, Login_Time,Program_name
       order by Login_Time desc;"

Invoke-Sqlcmd -ServerInstance $i -Query $sql -ConnectionTimeout 60 -QueryTimeout 99999

The below is the InstanceList.txt:

servername\instance name1
servername2\instance name2

and so forth.


texts: Managing multiple DBs and servers with central or local processes?

texts:
In my previous job, I had to manage some DBs (~500) installed on some servers (~20). Everything on SQLServer.

For basic tasks such as backups, archives or monitoring, I used to have a process installed on a central server which knows where is everything and what to do on each DB or server. So, a generic SSIS package, some configuration tables, a couple of SQLServer jobs, and we loop, apply scripts, and pull results and logs to this central DB server.

Everything was going well and I get used to that kind of design. And also learnt the good and the bad (ex: pro = only one process easy to configure ; con = it's a kind of spof).

Now, in my new company, I am facing the opposite: each server has its own local jobs and scripts, and pushes the results to a central DB server (using linked servers).
First, I thought it was not a very reliable way of doing things (if one task is completely failing, nobody knows / harder versioning / etc...). But actually, it seems to work well for years.

So I would like to have a better point of view than "I'm not used to that so it's bad".

What could be the pros and cons of each approach ? (in a SQLServer environment, version 2008 and more).


texts: How can I configure MySQL Innodb to handle 1000s of inserts per hour?

texts:
I have a very high traffic website where it is possible that 1000s of new records are inserted every hour.

This one error is crippling the site:

PDOException: SQLSTATE[40001]: Serialization failure: 1213
Deadlock found when trying to get lock;
try restarting transaction: INSERT INTO {location_instance}
(nid, vid, uid, genid, lid) VALUES (:db_insert_placeholder_0,
:db_insert_placeholder_1, :db_insert_placeholder_2,
:db_insert_placeholder_3, :db_insert_placeholder_4);
Array ( [:db_insert_placeholder_0] => 1059 [:db_insert_placeholder_1] =>
1059 [:db_insert_placeholder_2] => 0 [:db_insert_placeholder_3] =>
cck:field_item_location:1059 [:db_insert_placeholder_4] => 1000 )

I would be very surprised if MySQL could not handle this type of load. So, my questions are then, is this a database issue and how can I configure MySQL to be able to handle this much traffic?

I have a copy of my website set up on a development server with scripts that simulate the load of content being added to the website. I am running Ubuntu, LAMP stack, with 16GB of RAM.

Admittedly, I am not very knowledgeable about databases. In fact, I am starting with the default my.cnf that comes with it after 'apt-get install' finishes.  Tables are all Innodb. What starting configuration settings and approach would you recommend to begin solving this problem?

Let me know what more information you may need.

Thanks


texts: Can I provide a default for a left outer join?

texts:
Suppose I have tables a (with column a1) and b (with columns b1 and b2) and I perform a left outer join

SELECT *
FROM a LEFT OUTER JOIN b
ON a.a1 = b.b1

Then b1 and b2 will be NULL where a value of a1 has no matching value of b1.

Can I provide a default value for b2, instead of NULL?  Note that COALESCE won't work here, because I don't want the default value to override potential NULLs in b2 where there is a value of b1 matching a1.

That is, with a and b as

CREATE TABLE a (a1)
  AS VALUES (1),
            (2),
            (3) ;

CREATE TABLE b (b1,b2)
  AS VALUES (1, 10),
            (3, null) ;

a1     b1 | b2
---    --------
 1      1 | 10
 2      3 | NULL
 3

and a default for b2 of, say, 100, I want to get the result

a1 | b1   | b2
---------------
1  |  1   | 10
2  | NULL | 100
3  |  3   | NULL

In this simple case I could do it "by hand" by looking at whether b1 is NULL in the output.  Is that the best option in general, or is there a more standard and neater way?


texts:
I am new to postgresql and I would grateful if
you could please advise on how-to resolve the following error..

I have issued the followed commands:

ip_spotlight-# REVOKE EXECUTE ON ALL FUNCTIONS IN SCHEMA doc FROM PUBLIC ;
ip_spotlight-# REVOKE EXECUTE ON ALL FUNCTIONS IN SCHEMA net FROM PUBLIC ;

ip_spotlight# GRANT USAGE ON SCHEMA doc TO netopsapp ;

ip_spotlight-# ALTER DEFAULT PRIVILEGES IN SCHEMA doc GRANT ALL ON TABLES TO netopsapp ;
ip_spotlight-# ALTER DEFAULT PRIVILEGES IN SCHEMA net GRANT ALL ON TABLES TO netopsapp ;

Below is a list of the privileges:

ip_spotlight# \dn+
                          List of schemas
  Name  |  Owner   |  Access privileges   |      Description
--------+----------+----------------------+------------------------
 doc    | postgres |                      |
 net    | postgres |                      |
 public | postgres | postgres=UC/postgres+| standard public schema
        |          | =UC/postgres         |
(3 rows)

And the error is:

python3 -m pwiz --engine=postgresql --host=x.x.x.x --port=5432 --user=netopsapp --password  --schema=doc --tables=bgp_communities ip_spotlight
Password:
Traceback (most recent call last):
  File "/usr/lib/python3.4/site-packages/peewee.py", line 3768, in execute_sql
    cursor.execute(sql, params or ())
psycopg2.ProgrammingError: permission denied for schema doc
LINE 1: SELECT * FROM "doc"."bgp_communities" LIMIT 1

Could you please advise on how to setup the privileges so that netopsapp user to have access to the tables defined in schema doc

PS: the first 2 commands were mentioned as best practice in the postgresql book


texts: How can I recover my tables in sql server database

texts:
I deleted 3 tables from my database with data and I have no backup. How can I restore tables with data in my database ?

How can I restore tables and data with database log file?(Database recovery model is full)


texts:
I'm making an inventory management system using invoices.

 creating invoices (sale/transfer between warehouses or purchase)
if purchasing new product => we will define new products in database
we have currencies, units, account agent (supplier or customer) ...
system will be able to add/modify delete all data(products,currencies,units

if someone of you have a business ideas please help me
 this is an example of invoice teacher gave me

my db diagram is as follows

can u help me if there is a lack? because my teacher is waiting for something bigger than my entity and he is not telling me what to do more or helping to add more attributes.

where should i put tva?
should i put tva final of all items in invoice table?
should i put the net amount in invoice table?
are the relationship true?


texts:
Today I was making some work with site and I get error what MySQL can't rename file (to make changes in BD). Next I tried to restart MySQL, and it failed to stop MySQL. Then I did go to /var/lib/mysql and all I see is: ib_logfile0+1, ibdata1, and site folder, with tables that I was changing (not tables themselves, just #sql-b61_25a8e.MYI).

Can I recover my MySQL data? I don't need all tables, because I have old backup of them. I just need that one table with all users (yea I was changing table with users) And yes, I did shutdown MySQL for some stupid reason.


texts: How to fetch last two year annual data in oracle


texts:
We have exported Microsoft SQL 2014 server and rebuilt master DB. Users are missing now, so we need to detect orphaned users.
We have found article:
https://docs.microsoft.com/en-us/sql/sql-server/failover-clusters/troubleshoot-orphaned-users-sql-server?view=sql-server-ver15#:%7E:text=%20Identify%20orphaned%20users%20in%20those%20environments%20with,are%20user%20SID%27s%20in%20the%20user...%20More%20
It says to use code in user database:
SELECT dp.type_desc, dp.SID, dp.name AS user_name
FROM sys.database_principals AS dp
LEFT JOIN sys.server_principals AS sp
    ON dp.SID = sp.SID
WHERE sp.SID IS NULL
    AND authentication_type_desc = 'INSTANCE';

What is this user database? Where the code should be run to detect orphaned users? Now the server logins are missing. How to get same users as in original database?


texts: What is the difference between `MAX(column + 1)` and `MAX(column) + 1`

texts:
I know I should be writing MAX(column) + 1 in my queries rather than MAX(column +1). However, by mistake I wrote the wrong one and it seems to prevent a deadlock issue that I was having.

Couldn't find an explanation on this after digging for sometimes. Do point me to the write direction if this is already answered or looking forward to learn some new interesting things.


texts: MySQL: How index speed up query execution

texts:
I have read multiple articles about MySQL indexes. Some of the important pages are below:
http://dev.mysql.com/doc/refman/5.6/en/mysql-indexes.html
https://stackoverflow.com/questions/3567981/how-do-mysql-indexes-work

Its correct that query using indexes executes much faster because there is no need to traverse every records. But as a novice RDBMS guy, all other remaining columns are dependent on indeed column. In a table with thousands of records, system need to traverse the index first (one by one, in any defined order I don't know).

To avoid a big description, I am limiting the scope of this question as

How MySQL traverse indexes?
How indexes arrangement (B-trees,
hash,etc.) are determined?
What is the difference between traversing
rows one by one and traversing index column value?


texts: What is faster, one big query or many small queries?

texts:
I have been working for different companies, and I have noticed that some of them prefer to have views that will join a table with all its "relatives". But then in the application sometimes, we only need to use only 1 column.
So would it be faster to just make simple selects, and then "join" them in the system code?
The system could be in php, java, asp, or any language that connect to the database.
So the question is, what is faster going from a server side (php, java, asp, ruby, python...) to the database and running one query that gets everything we need or going from the server side to the database and running a query that only gets the columns from one table at a time?


texts: MySQL index size doesn't include all values


texts: Another question about finding the last time a value changed

texts:
I know there is a simple answer to this question but just can't get my head round it. I have a table with datetime and int columns.
| TimeStartTz             | RowNumber | RateID      |
|-------------------------|-----------|-------------|
| 2018-05-07 07:00:00.000 | 45        | 1           |
| 2018-04-09 07:00:00.000 | 43        | 1           |
| 2018-03-26 07:00:00.000 | 42        | 1           |
| 2018-03-19 07:00:00.000 | 41        | 1           | <-- want this row
| 2018-03-05 07:00:00.000 | 2         | 2           |
| 2018-02-26 07:00:00.000 | 1         | 2           |
| 2018-02-19 07:00:00.000 | 40        | 1           |
| 2018-02-12 07:00:00.000 | 39        | 1           |
| 2018-01-29 06:45:00.000 | 38        | 1           |
| 2018-01-22 06:45:00.000 | 37        | 1           |

I want to get the row of the most recent change in RateID or the first row if there is no change in the set, i.e. in the example the row with RowNumber 41. I tried partitioning by RateID but it obviously doesn't account for contiguous sections:
    ROW_NUMBER() OVER (PARTITION BY pq.[RateID] ORDER BY pq.[TimeStart]) RowNumber


texts:
I'm running into an issue linking SQL servers. The servers I have are:

Server A - SQL Server 2008 R2

Server B - SQL Server 2014

I can set B as a linked server on A with no problem, but setting up A as a linked server on B gets me the error:

  Cannot write property UseRemoteCollation.This property is not
  available on SQL Server 7.0. (SqlManagerUI)

Is this just an issue with the two servers being different versions? Is there anything I can do to get around this issue?


texts: Can the 'returning' clause return source columns that are not inserted?

texts:
Here's a minimal example of my real-world problem:

create table t(id serial primary key, rnd double precision);

of course you can return the inserted columns with a returning clause:

with w as (insert into t(rnd) values(random()) returning *)
insert into t(rnd) select random() from w returning *;
/*
| ID |            RND |
|----|----------------|
|  9 | 0.203221440315 |
*/

you can also return a literal:

with w as (insert into t(rnd) values(random()) returning *)
insert into t(rnd) select random() from w returning *, 1.0 dummy;
/*
| ID |            RND | DUMMY |
|----|----------------|-------|
| 11 | 0.594980469905 |     1 |
*/

but you can't return the source columns:

with w as (insert into t(rnd) values(random()) returning *)
insert into t(rnd) select random() from w returning *, w.rnd;
/*
ERROR: missing FROM-clause entry for table "w": with w as (insert into t(rnd) values(random()) returning *) insert into t(rnd) select random() from w returning *, w.rnd
*/

Is there any way I can get w.rnd out of the final returning clause?

db<>fiddle here


texts:
Any one knows what is "query containment" and where do we use it please?
As far as I can search on Google, I only found one here.

Although, I've got some idea on the subject but I'm finding it difficult to understand the use case of it.

Many thanks,


texts: Wrong query results on column ORA_ROWSCN with bind variables and NVL (Oracle 12c)

texts:
In the process of migrating our DB from Oracle 11 to Oracle 12c (12.2.0.1.0), we discovered that the same query gives different results on the two instances.

The query is:

VARIABLE myId NUMBER;
BEGIN :myId := 1325; END;
/

SELECT
  rid, ora_rowscn
FROM
  my_table
WHERE
  rid = NVL(:myId, rid);

On Oracle 11, we have:

      RID ORA_ROWSCN
--------- ----------
     1325 1.3439E+13
     1325 1.3439E+13

while on Oracle 12c we have:

      RID ORA_ROWSCN
--------- ----------
     1325
     1325

and the following query

SELECT
  rid, ora_rowscn
FROM
  my_table
WHERE
  rid = NVL(1325, rid);

gives:

      RID ORA_ROWSCN
--------- ----------
     1325    2549788
     1325    2549788

(Clearly the different values of the ORA_ROWSCN column are expected.)

MY_TABLE.RID is NUMBER(38) NOT NULL and there is a non-unique index on it.

I strongly suspect that the wrong results are the unintended consequences of the optimizer, but, as I have a very limited experience with DBs, I have no clues on how to fix this strange behavior.

The execution plans of the two queries are:

Plan hash value: 709374914

---------------------------------------------------------------------------------------
| Id  | Operation           | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |                 |    52 |   676 |     2   (0)| 00:00:01 |
|   1 |  VIEW               | VW_ORE_55EECC8D |    52 |   676 |     2   (0)| 00:00:01 |
|   2 |   UNION-ALL         |                 |       |       |            |          |
|*  3 |    FILTER           |                 |       |       |            |          |
|*  4 |     INDEX RANGE SCAN| MY_TABLE_INDEX1 |     1 |    13 |     1   (0)| 00:00:01 |
|*  5 |    FILTER           |                 |       |       |            |          |
|   6 |     INDEX FULL SCAN | MY_TABLE_INDEX1 |    51 |   663 |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - filter(:MYID IS NOT NULL)
   4 - access("RID"=:MYID)
   5 - filter(:MYID IS NULL)

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)

and

Plan hash value: 460232730

-------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |                 |     2 |    26 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| MY_TABLE        |     2 |    26 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN                  | MY_TABLE_INDEX1 |     2 |       |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("RID"=1325)

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)

My question is: how can I obtain on Oracle 12c the same results that I obtained so far on Oracle 11?


texts: Best database and table design for billions of rows of data

texts:
I am writing an application that needs to store and analyze  large amounts of electrical and temperature data.

Basically I need to store large amounts of hourly electricity usage measurements for the past several years and for many years to come for tens of thousands of locations and then analyze the data in a not very complex manner.

The information that I need to store (for now) is Location ID, Timestamp (Date and Time), Temperature and Electricity Usage.

About the amount of the data that needs to be stored, this is an approximation, but something along those lines:
20 000+ locations, 720 records per month (hourly measurements, approximately 720 hours per month), 120 months (for 10 years back) and many years into the future. Simple calculations yield the following results:

  20 000 locations x 720 records x 120 months (10 years back) = 1 728 000 000 records.

These are the past records, new records will be imported monthly, so that's approximately 20 000 x 720 = 14 400 000 new records per month.

The total locations will steadily grow as well.

On all of that data, the following operations will need to be executed:

Retrieve the data for a certain date AND time period: all records for a certain Location ID between the dates 01.01.2013 and 01.01.2017 and between 07:00 and 13:00.
Simple mathematical operations for a certain date AND time range, e.g. MIN, MAX and AVG temperature and electricity usage for a certain Location ID for 5 years between 07:00 and 13:00.

The data will be written monthly, but will be read by hundreds of users (at least) constantly, so the read speed is of significantly more importance.

I have no experience with NoSQL databases but from what I've gathered, they are the best solution to use here.
I've read on the most popular NoSQL databases, but since they are quite different and also allow for very different table architecture, I have not been able to decide what is the best database to use.

My main choices were Cassandra and MongoDB, but I since I have very limited knowledge and no real experience when it comes to large data and NoSQL I am not very certain. I also read that PostreSQL also handles such amounts of data well.

My questions are the following:

Should I use a NoSQL database for such large amounts of data. If not can I stick to MySQL?
What database should I use?
Should I keep the date and time in separate, indexed (if possible) columns to retrieve and process the data quickly for certain time and date periods, or can this be done by keeping the timestamp in a single column?
Is a time series data modeling approach appropriate here, and if not could you give me pointers for a good table design?

Thank you.


texts: How to specify authentication database and target database separately on mongodb connection uri?

texts:
I am using this connection uri to connect to mongodb: mongodb://user:password@localhost/admin. It will use admin as bot authentication and target database. How can I make the uri to use admin as authentication but allow me to connect to a different database? Please see below command as an example:

mongo --host localhost -u user -p password --authentication admin test

the above command will use admin as the authentcation database but connect to test database. how can I do the same thing on uri?


texts:
Goal:
Reuse the SQL server agent jobs list.

Problem:
The list contains a lot of jobs and I need to reuse the jobs list in a another time without recreating it.

Based on the example of the picture, can I get the sourcecode of every job and enable reusing it when I establish a new database from scratch?

I would prefer not to recreate the job list from scratch.

Information:
*The list contains a lot of jobs.


texts: Insert Into Select multiple columns mysql

texts:
This query doesn't work, but individual queries inserting and selecting only one column do work:

INSERT INTO subdata (reggeduser,completed)
SELECT COUNT(u.email) FROM user AS u,COUNT(a.email) FROM application AS a

My end goal is this:

CREATE EVENT
ON SCHEDULE EVERY 1 DAY
STARTS '2016-12-12 00:00:00'
DO
INSERT INTO subdata (reggeduser,completed)
SELECT COUNT(u.email) FROM user AS u,COUNT(a.email) FROM application AS a

MySQL version used is 5.6.19-67.0-log.

The error thrown is a simple #1064 Invalid Syntax.


texts: Updating rows in a large table in sql server

texts:
I want to update one large table that has 83,423,460 rows and growing.

The below query takes 8 minutes to execute successfully:

 UPDATE FPP_Invoice_Revenue
 SET    Till_Prev_Inv_Amt = Till_Prev_Inv_Amt_In_USD / 0.0285714286,
        Cur_Inv_Amt = Cur_Inv_Amt_In_USD / 0.0285714286,
        YTD_Inv_Amt = YTD_Inv_Amt_In_USD / 0.0285714286
 WHERE  SOW_Number = '20014378'

There exists one clustered index. I thought of disabling that index before updating and again will rebuild after update but that also did not work since rebuilding is taking a lot of time.

I have read somewhere this can be achieved by dividing into small parts,  but how can I divide the above query?

DDL:

CREATE TABLE [dbo].[FPP_Invoice_Revenue](
[Project_Code] [varchar](10) NOT NULL,
[Project_Desc] [varchar](50) NULL,
[SOW_Number] [varchar](10) NOT NULL,
[SOW_Desc] [varchar](50) NULL,
[Invoice_No] [varchar](50) NOT NULL,
[Inv_Month] [int] NOT NULL,
[Inv_Year] [int] NOT NULL,
[Billing_Date] [smalldatetime] NULL,
[Doc_Currency] [varchar](10) NULL,
[Vertical] [varchar](255) NULL,
[Till_Prev_Inv_Amt] [numeric](24, 10) NULL,
[Cur_Inv_Amt] [numeric](24, 10) NULL,
[YTD_Inv_Amt] [numeric](24, 10) NULL,
[Till_Prev_Inv_Amt_In_USD] [numeric](24, 10) NULL,
[Cur_Inv_Amt_In_USD] [numeric](24, 10) NULL,
[YTD_Inv_Amt_In_USD] [numeric](24, 10) NULL,
CONSTRAINT [PK_FPP_Invoice_Revenue] PRIMARY KEY CLUSTERED
(
[Project_Code] ASC,
[SOW_Number] ASC,
[Invoice_No] ASC,
[Inv_Month] ASC,
[Inv_Year] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF,
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]
GO

SET ANSI_PADDING OFF
GO

Execution Plan:


texts:
i have a big issue, every day i need to update or insert like 3.3 milion rows, i have trying to do what ever i can but it still only run like 605 query/s i use VirtualBox so my setup are testing on 1, 2 and 4 cpu widt 2 and 4gb of ram still the same result.

so now are my question how can i use more threads on same connection is it possible or what can i do here?

if i don't do anything its will take like 1,5 hour to run this script, we load a CSV file into PHP5 and run the script on a NGINX webserver, but its still pretty slow and can not handle more then 605 querys/s

hope sombardy can tell me whats happen here and what i do wrong.


texts: What order does Oracle's EXIST condition compare the sub-query results?

texts:
I know EXIST works better than IN for larger sub-queries because it checks each sub-query record individually and stops once a comparison is found.

Let's say I can order the sub-query in a way to make it more likely for the match to be at the top. Would EXIST actually stick to the order in which the sub-query is returned?

I'm aware this approach is a bit of a double-edged sword, with specifying an order for a large sub-query having a negative impact on performance. I suppose I'm mostly just curious.


texts: Oracle startup error (could not open parameter file)

texts:
I installed the Oracle 11g server on Ubuntu 12.10. But I can't start the server. When I start up the server, the following error occurs:

SQL> startup
ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file '/u01/app/oracle/product/11.2.0/xe/dbs/initXE.ora'

How can I solve this issue? I think the connect failed because target host or object does not exist. How can a target host be specified?

Running:

$ ls -latr /u01/app/oracle/product/11.2.0/xe/dbs/initXE.ora

returns:

-rw-r--r-- 1 oracle dba 2637 Mar 13 17:07 /u01/app/oracle/product/11.2.0/xe/dbs/initxe.ora

Running echo $ORACLE_SID displays nothing - an empty line.


texts: to_tsquery - Does it always accept quert text?

texts:
I am very new to FTS features in Postgres, in fact, have started exploring them since yesterday only.
I wanted to pass list of values stored in columns of a DB tables instead of query text in to_tsquery.
Something like this:
select  *
from t1
 where to_tsvector("col1") @@ to_tsquery((select col2 from t2 where id = 123))

Not sure if this is a possibility? Is there any alternative by which such kind of implementation can be achieved using to_tsquery?


texts:
We use MSSQL 2016 database, I recently see a file call DatabaseName.mdf_MSSQL_DBCC15 in the same location where my database .mdf file located.  It has identical file size as my .mdf database file.

Do you know what is this file and what caused it to be created?
I googled around and the term snapshot comes up here and there but I can't confirm 100%.


texts: How do I get multiple rows output in one line?

texts:
The data is like Jane has items pen pencil eraser.

If I write the statement 'select * from item'
the output is:

Jane pen
Jane pencil
Jane eraser
Jack box
Jack wiper

I want the output to be like:

Jane pen pencil eraser
Jack box wiper

How to do this?


texts:
I'm thinking to use UUID as PK for all tables into my new PostgreSQL database, for such a goal I will be using uuid_generate_v5 and uuid_ns_dns or uuid_ns_url functions, but I have some doubts:

is there any problems using UUIDs only in a database (i.e. performance)?
is there any pros and cons respect to use uuid_generate_v4?
When to use uuid_ns_dns or uuid_ns_url? (I haven't found info about it)
can name parameter for uuid_generate_v5 function be any string (even an empty string) or it depends on namespace parameter? Examples, please.


texts: Why 2 allocation units during online index rebuild to different file group?

texts:
I'm in the process of rebuilding the clustered index of a big table to a different file group. I'm monitoring the rebuild process using sys.system_internals_allocation_units. I'm 3h in but not done yet.

Strangely, the table appears to have too many allocation units!

Old file group, IN_ROW_DATA (normal)
Old file group, ROW_OVERFLOW_DATA (normal)
New file group, IN_ROW_DATA (normal)
New file group, ROW_OVERFLOW_DATA (normal)
New file group, IN_ROW_DATA (again!)

So two IN_ROW_DATA in the destination file group! One of them has data compression as NONE, one of them has PAGE. I'm doing the index rebuild WITH (DATA_COMPRESSION = PAGE, ONLINE = ON). The table is not partitioned.

You can see for yourself:

SELECT idx.name
, au.allocation_unit_id, au.type_desc, au.container_id, au.filegroup_id, au.used_pages, au.root_page
, par.index_id, par.rows, par.data_compression_desc
FROM sys.system_internals_allocation_units au
LEFT JOIN sys.partitions par ON (au.container_id = par.hobt_id AND au.type IN (1, 3)) OR (au.container_id = par.partition_id AND au.type IN (2))
LEFT JOIN sys.indexes idx ON par.object_id = idx.object_id AND par.index_id = idx.index_id
LEFT JOIN sys.objects obj ON par.object_id = obj.object_id
WHERE obj.name = 'X'
ORDER BY obj.name, idx.name, par.partition_number, au.filegroup_id, au.type_desc

File group 2 is the destination. Both IN_ROW_DATA allocation units have lots of pages in them so it is not like one of them is some kind of dummy. Also, the table is taking more than 2x the space that it should at this point. Clearly, the DATA_COMPRESSION setting didn't take!

Question: Why are there two allocation units in the same partition? Why doesn't my DATA_COMPRESSION setting cause the data to be compressed?

Edit: Lines 1 and 4 seem to correspond to the same data. They are both in the destination and they have the same row count. It seems like all rows are written twice - once compressed and once uncompressed. I can confirm the space is being used (or at least written and marked as allocated).

Edit 2: The INSERT DML plan for my table under rebuild shows an insert into 3 HOBT's. Not just 2 which would be expected during an online index rebuild. There are no non-clustered indexes defined. Here is the plan:

Edit 3: The rebuild has completed. The uncompressed (huge) partition is gone. The only thing left is the compressed one. Unfortunately, the allocated extents of both have been intermixed during writing. Using a visualizer tool that I have written, it looks like this:

(This is a crop of <1% of the whole allocation bitmap). Black = allocated, White = free (formerly the temporary partition).

That is a terrible outcome for sequential IO! So I switched out the table to an empty copy of it to be able to perform an ONLINE = OFF rebuild on it to a fresh file group (it is an insert-only table so I can switch it out whenever I want). That fixed things: The table is now contiguous and no temporary partition was seen.

Still, the question remains: Why does the ONLINE = ON CI rebuild cause all these nasty effects? How to fix it?


texts: What is the default my.cnf on debian's mariadb server?

texts:
I'm trying to set default-time-zone on mariadb server
Server version: 10.3.25-MariaDB-0+deb10u1 Debian 10
To do such things, in mysql, I used to simply modify /etc/mysql/my.cnf but here I have a jungle of config files:
/etc/mysql
├── conf.d
│   ├── mysql.cnf
│   └── mysqldump.cnf
├── debian.cnf
├── debian-start
├── mariadb.cnf
├── mariadb.conf.d
│   ├── 50-client.cnf
│   ├── 50-mysql-clients.cnf
│   ├── 50-mysqld_safe.cnf
│   └── 50-server.cnf
├── my.cnf -> /etc/alternatives/my.cnf
└── my.cnf.fallback

And my.cnf only contains this cryptic instructions:
# The MariaDB configuration file
#
# The MariaDB/MySQL tools read configuration files in the following order:
# 1. "/etc/mysql/mariadb.cnf" (this file) to set global defaults,
# 2. "/etc/mysql/conf.d/*.cnf" to set global options.
# 3. "/etc/mysql/mariadb.conf.d/*.cnf" to set MariaDB-only options.
# 4. "~/.my.cnf" to set user-specific options.
#
# If the same option is defined multiple times, the last one will apply.
#
# One can use all long options that the program supports.
# Run program with --help to get a list of available options and with
# --print-defaults to see which it would actually understand and use.

#
# This group is read both both by the client and the server
# use it for options that affect everything
#
[client-server]

# Import all .cnf files from configuration directory
!includedir /etc/mysql/conf.d/
!includedir /etc/mysql/mariadb.conf.d/

So I'm wondering what is the relevant config file to make the changes?


texts: SQL Always On COPY ONLY backups - what's the point if I cant restore the AG from these backups?

texts:
Think I already know the answer to this but I'll ask anyway.

Situation is:

SQL 2012 Always On - 2 synchronous replicas.
7 TB worth of Sharepoint DBs in 1 AG.
Before patching - I want a Full DB backup.
Backup share is on Secondary server B.
Backup from server A to backup share on server B = 12 hours.
Backup from server B to backup share on server B = 6 hours, BUT the backups need to be COPY ONLY.

If I try and restore these and create an AG - it fails.
I need to take ANOTHER Full Db backup = another 6 hours, before I can recreate the AG.

So COPY ONLY backups are useless. Unless I'm missing something?
What's the point of these?


texts: 2 B-tree indices OR 1 GiST index on tsrange -- which will perform better?

texts:
I have a table which is storing reservation data using the columns starts_at & ends_at Whenever I'm querying the table to find overlapping reservations, I have an option of using one of the following queries:

SELECT * FROM reservations
WHERE starts_at < '2014-01-03 00:00:00'
AND   ends_at   >='2014-01-01 00:00:00';

Or

SELECT * FROM reservations
WHERE tsrange(starts_at, ends_at) && ('2014-01-01 00:00:00', '2014-01-03 00:00:00')

I have regular B-Tree indices on the starts_at and ends_at columns, therefore the first query is always using them. However, unless I define a functional GiST index on the tsrange, the second query does a complete scan.

create index tsrange_idx on reservations using gist(tsrange(starts_at, ends_at));

My question is, as the table grows, which index is going to be faster? Probably the answer is obvious looking at the query execution plan, but I'm not well-versed with reading EXPLAIN ANALYZE output.


texts:
Can someone please highlight the advantages of Database Snapshot in SQL2008.

I read some where that

"When a Database Snapshot is created, SQL Server doesn’t allocate space on disk equivalent
to the current size of the data files in the source database. Instead, SQL Server takes advantage of an operating system feature called sparse files. A sparse file is essentially an entry in the file allocation table and consumes almost no space on disk."

Can someone please also tell me how can a file created with no space allocated.

Thanks


texts:
I am trying to delete some old dbs in my sharded set up.
Step i am taking are :

Stop Balancer.
Drop Db
Start balancer

But in some of the DBs i am getting these errors.

"errmsg" : "exception: collection's metadata is undergoing changes. Please try again."

"errmsg" : "exception: Error locking distributed lock for chunk drop. :: caused by :: 13661 distributed lock ibeat20140904.dgrpCount/iBeat55:27017:1420803001:1804289383 had errors communicating with individual server x.x.x.x:27017 :: caused by :: field not found, expected type 7"

When i checked the db using show collections some of those collections were removed but not all.
 x.x.x.x:27017 is ip of my config server.
I check hash of all config servers and they seems to be in sync.


texts: Why does the system generated TNSNAMES.ORA have two entires, one with a full connect descriptor and another with just a network address?

texts:
My TNSNAMES.ORA file was auto-generated when I installed my database. It generates with two entries for my database however. One entry looks "complete" with a full connect descriptor, while the 2nd entry is just the network address of the listener.  What's the difference between the two and when would you use one versus the other?

DB2 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1522))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = db2)
    )
  )

LISTENER_DB2 =
  (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1522))


texts: Can I choose which CPUs / Cores to be used by MySQL?

texts:
I have a Windows VPS (Windows 2008 R2 x64) that runs a couple WordPress blogs and other few ASP.NET things. The VPS runs 2 CPUs (for example).

With ASP.NET, in such situation, I usually set the app pool to use one CPU / Core, and SQL Server to use the other one (just change it in the server properties).

In MySQL, can I tell it which CPU(s) / Core(s) to use?


texts: Is not having 'named' timezone support common in MySQL?

texts:
I'm trying to set the timezone the database uses using:

"SET time_zone = 'UTC';

Whilst this works on my development server it doesn't work on my staging server; I get this error:

General error: 1298 Unknown or incorrect time zone: 'UTC'

Now I am assuming this is because I cannot see a mysql table on the staging server with the respective timezones, whilst it does exist on my development server.

Edit: Apparently it does exist on the staging server but the user I am logged in with for that database can't see it, so I guess MySQL can't find the data.

I'm building some software that others will be using on their servers, so I will have no idea if they will have the aforementioned mysql table.

With that said, should I avoid using "SET time_zone = 'UTC'; in software that others will use?

If so, what is the correct way to handle this situation?

Is doing something like:

"SET time_zone = '+0:00';

...the better way to do it?


texts: Storing whether a user is a student or teacher? Is a lookup table necessary?

texts:
Users can be either student or teachers, and are able to do different things depending on which role they are. Can I just use role with a 0 or 1? I usually see lookup tables which map a user to a role to permissions and whatnot, but would that be overkill for this scenario?

user
---
id      1
name    John
role    0

id      2
name    Jane
role    1

or

user
---
id      1
name    John
role    1

id      2
name    Jane
role    2

role
---
id      1
type    Student

id      2
type    Teacher

id      3
type    Principal


texts: Conditionally track CDC on a table in SQL Server 2016

texts: I have CDC working on many tables in SQL Server 2016 without any issues. I now have a requirement to capture data changes (CDC) on a table when a specific criteria is met. Is it possible to capture data using a filter criteria? In other words, track data changes into (_CT) table using a specific "where clause" condition (for example, Where Status = 'Active' and Flag = 1 etc.). How do I go about it? Any help would be appreciated.


texts: Permission denied on function after schema copy

texts:
I had one schema 'public' and simple function in the schema named 'isvalidoption'.

What was done:

backup schema using pg_dump;
rename schema to 'development';
create new schema 'public';
restore it from backup using pg_restore.

The problem:

when I select from any table omitting schema prefix like 'SELECT *
FROM mytable;' it's OK
when I run function with schema prefix like 'SELECT public.isvalidoption();' or 'SELECT development.isvalidoption()' it's OK;
when I run function omitting schema prefix like 'SELECT isvalidoption();' I get ERROR: permission denied for function isvalidoption

User has USAGE on both schemas and EXECUTE on function in both schemas also he is owner of the function in both schemas.

PostgreSQL version is 9.6.1.

'SELECT current_user;' shows correct user.

'SHOW search_path;' shows '"$user", public'.

Any ideas?


texts: How to enable use of SSL in MySQL server?

texts:
I have 2 devices on my local network: a remote MySQL server (using MariaDB 10.3) and a laptop (acting as a client with Python). I need to connect to a remote MySQL server via Python using SSL encryption.
When I try to connect to a remote MySQL server without using SSL everything works good:
import mysql.connector as mysql
HOST = "192.168.1.8"
DATABASE = "some_database_name"
USER = "ssluser"
PASSWORD = "some_password"
db_connection = mysql.connect(host=HOST, database=DATABASE, user=USER, password=PASSWORD)
print("Connected to:", db_connection.get_server_info())

Got the message:
Connected to: 5.5.5-10.3.27-MariaDB-0+deb10u1

But when I try to connect using SSL, I get the error:
import mysql.connector as mysql
HOST = "192.168.1.8"
DATABASE = "some_database_name"
USER = "ssluser"
PASSWORD = "some_password"

SSL_ca='/etc/mysql/ssl/ca.pem'
SSL_cert='/etc/mysql/ssl/client-cert.pem'
SSL_key='/etc/mysql/ssl/client-key.pem'

db_connection = mysql.connect(host=HOST, database=DATABASE, user=USER, password=PASSWORD, ssl_ca=SSL_ca, ssl_cert=SSL_cert, ssl_key=SSL_key, port=3306, ssl_verify_cert=True)
print("Connected to:", db_connection.get_server_info())

Got the error:
InterfaceError: 2026 (HY000): SSL connection error: SSL is required but the server doesn't support it

SSL is required but the server doesn't support it

On a remote MySQL server, I entered the following commands and received the following messages:

SHOW VARIABLES LIKE '%ssl%';

+---------------------+--------------------------------+
| Variable_name       | Value                          |
+---------------------+--------------------------------+
| have_openssl        | NO                             |
| have_ssl            | DISABLED                       |
| ssl_ca              | /etc/mysql/ssl/cacert.pem      |
| ssl_capath          |                                |
| ssl_cert            | /etc/mysql/ssl/server-cert.pem |
| ssl_cipher          | TLSv1.2,TLSv1.3                |
| ssl_crl             |                                |
| ssl_crlpath         |                                |
| ssl_key             | /etc/mysql/ssl/server-key.pem  |
| version_ssl_library | YaSSL 2.4.4                    |
+---------------------+--------------------------------+

SHOW SESSION STATUS LIKE 'Ssl_cipher';

+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Ssl_cipher    |       |
+---------------+-------+

STATUS

--------------
mysql  Ver 15.1 Distrib 10.3.27-MariaDB, for debian-linux-gnueabihf (armv8l) using readline 5.2

Connection id:        15
Current database:
Current user:     root@localhost
SSL:          Not in use
Current pager:        stdout
Using outfile:        ''
Using delimiter:  ;
Server:           MariaDB
Server version:       10.3.27-MariaDB-0+deb10u1 Raspbian 10
Protocol version: 10
Connection:       127.0.0.1 via TCP/IP
Server characterset:  utf8mb4
Db     characterset:  utf8mb4
Client characterset:  utf8mb4
Conn.  characterset:  utf8mb4
TCP port:     3306
Uptime:           13 hours 21 min 3 sec

Threads: 8  Questions: 17  Slow queries: 0  Opens: 17  Flush tables: 1  Open tables: 11  Queries per second avg: 0.000
--------------

In the config file /etc/mysql/mariadb.conf.d/50-server.cnf on the remote server I have set the following settings, below the line [mysqld]:
bind-address            = 0.0.0.0

ssl-ca = /etc/mysql/ssl/cacert.pem
ssl-cert = /etc/mysql/ssl/server-cert.pem
ssl-key = /etc/mysql/ssl/server-key.pem

ssl-cipher = TLSv1.2,TLSv1.3

ssl = on


texts: SQL Server - Required Permissions For Executing Stored Procedures With SHOWPLAN ON

texts:
In order to get a stored procedure plan I'm running the following code:

set showplan_all on
exec sp_name param
set showplan_all off

Now I'm wondering if there's any way of doing that without granting execute permissions to the user running this code. After all, this code does not really execute the procedure but rather display it's plan.


texts: How to create multiple tables based on existing tables in postgreSQL?

texts:
I have several tables, and I need to create a matching _hist table for each one. For example if I have:

CREATE TABLE public.test (
    first       text,
    middle      text,
    "last"      text,
    "timestamp" timestamp without time zone NOT NULL default now()
);

I would like to create:

CREATE TABLE public.test_hist (
    "action"    text,
    "user"      text,
    first       text,
    middle      text,
    "last"      text,
    "timestamp" timestamp without time zone NOT NULL default now()
);

I'm in the initial phase of the project I'm working on, and requirements keep changing.

As I run the changed script that drops and re-creates tables, I would like for _hist tables to somehow get automatically generated, so I don't need to make the same change in two places.

Is it possible to do that with SQL at the end of my table creation script?

I have postgreSQL 10.

Or should I do it in python?


texts: How to Create Insert Statement from Table Data using sp_export_data without Id?

texts:
How to Create Insert Statement from Table Data using sp_export_data without the Id?
I have the following statements and want to omit the Identity. How would I accomplish this?

This is how I'm scripting the one row:

exec sp_export_data tblTableName
 , @from ="FROM tblTableName WHERE tblTableNameId = 123"

This is how I am script the whole table:

exec sp_export_data tblTableName
 , @from ="FROM tblTableName"

Any & all feedback is appreciated.


texts: Gaining visibility into long running INSERT INTO ... SELECT

texts:
I have a large SQL insert statement that updates a summary table. The process normally runs hourly, and takes about 5 to 10 minutes to calculate uniques over that period. I "foolishly" chose to run it over a period of 15 days, and I'm wondering if there's anything I can do to gain visibility into where it's at. The process has been running for 9 hours already. I would simply like to know if it's still the SELECT query that's running or if it's inserting data: I need to know if I should stop it and do smaller batches, or if I should just let it finish.

I know the server's been at it for 9 hours because of that statement:

select
    now() - xact_start
  , procpid
  , client_addr
  , client_port
  , current_query
from pg_stat_activity
where xact_start is not null
order by 1 desc

The actual query is:

INSERT INTO summary_show_unique_personas(period, show_id, persona_id, interactions_count)
  SELECT
      date_trunc('hour', created_at) AS period
    , show_id
    , persona_id
    , COUNT(*)
  FROM
          twitter_interactions
    JOIN  show_bindings        USING(interaction_id)
    JOIN  twitter_personas     USING(screen_name)
  WHERE
        created_at >= '__PERIOD_START_AT__' AND created_at < '__PERIOD_END_AT__'
    AND interaction_created_at >= '__PERIOD_START_AT__' AND interaction_created_at < '__PERIOD_END_AT__'
  GROUP BY
    1, 2, 3;


texts: Best practices for schema changes and data migrations to a live database without downtime?

texts:
How do you make schema changes to a live database without downtime?

For instance, lets say I have a PostgreSQL database with a table including various user data like email addresses etc, all associated with specific users. If I wanted to move the email addresses to a new dedicated table, I'd have to change the schema and then migrate the email data across to the new table. How could this be done without stopping writes to the original table? Surely while data is written over from the old table to the new one, new data would continue to be written to the old table and be missed, right?

I guess this problem comes up pretty frequently but I can't find any standard solution for dealing with it.

This article deals with the problem but I didn't really understand step 3. He says to write to both tables, then migrate old data from the first table to the new one. How do you make sure you're only migrating old data?

(I use PostgreSQL on Heroku.)


texts: how to identify the default mail configurations

texts:
In SQL Server, we can setup multiple email configurations out of which only some were set to default.

I have tried using the following query to get the list of accounts:

select *
from msdb.dbo.sysmail_profile p
    join msdb.dbo.sysmail_profileaccount pa on p.profile_id = pa.profile_id
    join msdb.dbo.sysmail_account a on pa.account_id = a.account_id
    join msdb.dbo.sysmail_server s on a.account_id = s.account_id

Is there any way to identify the default mail configuration profile?


texts: Can't connect to a Oracle db instance via SQLDeveloper

texts:
When I set the connecting information on the GUI in SQLDeveloper I get the following error (everytime!)

Status : Failure -Test failed: IO Error: The Network Adapter could not establish the connection

Connection information:

user: hr
pass: hr
Conn. type: basic / Role: default
hostname: localhost
port: 1521
SID: xe

I couldn't connect with the sys user either.
Previously I successfully unlocked the HR user through SQLPLUS command line
(as described here:
and set the port=localhost in the tnsnames.ora and listener.ora, the OracleXETNSListener service is up and running as well as the OracleServiceXE)
Sadly I'm still getting this error message...
I'm on a laptop (with wireless wifi connection only), under Windows 7.
What might be the problem?
Thank you for your help.


texts: I want to create custom Full Text Search dictionary with special stop words from some_words.stop. How to upload this file to my RDS instance by FTP?


texts: How can I always get Timestamp in certain format without having to explicitly define it everytime

texts:
When I run a select on a timestamp I get something like:

2015-01-02T23:22:36.000Z, 1/7/2015 11:03:31 AM

In order to format properly I have to do:

SELECT to_char(entered, 'MM/DD/YYYY HH12:MI:SS AM')
FROM analytics WHERE business_id = 371746;

which is kind of a pain, since I often just do SELECT * and don't specify the fields.

Is there a way to tell Postgres to always return this kind of format 'MM/DD/YYYY HH12:MI:SS AM' from a SELECT without having to specify it every time?


texts: Create index on a Postgres table as fast as possible

texts:
I have a large database import (100GB) on a Postgres table without indexes.

After the import, I want to create an index for queries as fast as possible. No data is accessed in the table as long as the index is not ready.

What is the quickest way of building the index? I have to build the index on 3 columns (two varchar, one date). The index creation takes about 2 hours, which is really not useful.

Is there any way I could speed up the index creation? Probably it's not better to set the index before data import, as this will slow down the import?

I am aware that Postgres has an option to create an index "without locking", but wouldn't that decrease performance, since it enables Postgres to access the data while index is being created?

This is a virtual machine. The memory of the server can be increased to my needs. Currently I have 32GB. work_mem and maintenance_work_mem are still at the default configuration values. I don't have SSD for the database. Would this certainly speed things up?

I don't have any boolean flags or similar that could be used for partial indexes. In fact, I want each row in my table to be indexed.


texts: Linked Server Setup Error - SSL Provider: The client and server cannot communicate, because

texts:
I am trying to setup a linked server and ending up with the following error.
Error:
SSL Provider: The client and server cannot communicate, because they do not possess a common algorithm.

Environment:
Linked server is being created on
Microsoft SQL Server 2012 (SP3-CU8) (KB4013104) - 11.0.6594.0 (X64)  Developer Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: ) (Hypervisor)
Windows Server 2012 Standard

IISCrypto shows these values

Source Server:
Microsoft SQL Server 2008 R2 (SP3) - 10.50.6000.34 (X64)
Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)
Windows Server 2008 R2 Enterprise

IISCrypto shows these values

I gave up resolving this issue for a while now.
Could someone please assist me on this?
I need to create this Linked server!! What am I doing wrong all these days?


texts: Oracle 11gR2 Installation on Oracle linux 7 - Prerequisite condition failed for OS kernel parameter "semmni"

texts:
while Installing oracle 11gr2 on oracle Linux 7. The prerequisite condition to test whether the OS kernel parameter "semmni" is failed with below error.

Please find the screenshot of it.

Below is the kernel parameters which are configured in the etc/sysctl.conf file.

fs.aio-max-nr = 1048576
fs.file-max = 6815744
kernel.shmall = 2097152
kernel.shmmax = 2147483648
kernel.shmmni = 4096
#semaphores: semmsl, semmns, semopm, semmni
kernel.sem = 250 256000 100 1024
net.ipv4.ip_local_port_range = 9000 65500
net.core.rmem_default=262144
net.core.rmem_max=4194304
net.core.wmem_default=262144
net.core.wmem_max=1048586

Any idea why it is failing?


texts:
I have a SQL Table that we are selling off some of the data, so I need a quick way of splitting the main table into sub tables every 50 rows.  Table nomenclature does not matter, I am just stuck on a way of selecting 50 different rows for each table with the table not holding a primary key or numeric field.

How would one go about this in SQL Server 2008?


texts: How can I correct SQL Server Error 8928?

texts:
I received the following error message in an application running against one of my databases:

SQL Server detected a logical consistency-based I/O error: incorrect checksum
(expected: 0xfdff74c9; actual: 0xfdff74cb). It occurred during a read of page (1:69965) in
database ID 5 at offset 0x0000002229a000 in file 'e:\SQL Server\MSSQL.1\MSSQL
\[DB_Name].mdf'. Additional messages in the SQL Server error log or system event log may
provide more detail.
    This is a severe error condition that threatens database integrity and must be
corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This
error can be caused by many factors; for more information, see SQL Server Books Online.

That sounded bad so I ran the following:

DBCC CHECKDB ([DB_Name]) WITH NO_INFOMSGS, ALL_ERRORMSGS

And received the following report:

Msg 8928, Level 16, State 1, Line 1
Object ID 1326627769, index ID 1, partition ID 72057594048872448, alloc unit ID
    72057594055557120 (type LOB data): Page (1:69965) could not be processed. See other
    errors for details.
Msg 8939, Level 16, State 98, Line 1
Table error: Object ID 1326627769, index ID 1, partition ID 72057594048872448, alloc
    unit ID 72057594055557120 (type LOB data), page (1:69965). Test (IS_OFF (BUF_IOERR,
    pBUF->bstat)) failed. Values are 12716041 and -4.
Msg 8965, Level 16, State 1, Line 1
Table error: Object ID 1326627769, index ID 1, partition ID 72057594048872448, alloc
    unit ID 72057594055557120 (type LOB data). The off-row data node at page (1:69965),
    slot 0, text ID 89622642688 is referenced by page (1:69968), slot 1, but was not
    seen in the scan.
Msg 8929, Level 16, State 1, Line 1
Object ID 1326627769, index ID 1, partition ID 72057594048872448, alloc unit ID
    72057594055622656 (type In-row data): Errors found in off-row data with ID
    89622642688 owned by data record identified by RID = (1:77754:1)
CHECKDB found 0 allocation errors and 4 consistency errors in table '[Table_Name]'
    (object ID 1326627769).
CHECKDB found 0 allocation errors and 4 consistency errors in database '[DB_Name]'.
repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB
    ([DB_Name]).

I looked through my old images, and it appears the error has existed for quite a while. I don't have a clean image. I'm not much of a DBA so I don't know the best way to proceed; I don't even understand how the error messages can point me to the data corruption.

Can anyone tell me what these messages indicate and how to proceed?


texts: PostgreSQL trigger function that selects from %current% table

texts:
I have multiple tables that have the same column names, they only vary in their column values, like:

tbl_log_a
tbl_log_b
tbl_log_c
...

26 tables from a to z. Each table has a trigger that calls a trigger function which does the exact same thing:

SELECT columnname FROM tbl_log_a

Other than that, all my trigger functions do the exact same thing. They differ in that they:

select columnname FROM tbl_log_a
select columnname FROM tbl_log_b
select columnname FROM tbl_log_c
...

So I have to create 26 trigger functions, one for each tbl_log_%letter%. Is there a way to tell the trigger function to:

SELECT columnname FROM %currenttable%

By %currenttable% I mean the table where the trigger is placed. Or:

SELECT columnname FROM tbl_log_%letter%

Is it possible in Postgres 9.1? I'm reading about dynamically determined tables. Any clue? I would like to store the table name itself inside a variable, not the columns inside that table, because the trigger function works on multiple columns inside that table.

TG_TABLE_NAME
TG_TABLE_SCHEMA


texts: How to design databse for storing object states in mysql?

texts:
I have multiple objects with multiple states. I want to build a timeline for each object. For example: an object might have a created, submitted, processing and success state. I would like to store when the object's state changes. I have 2 different solution in mind.

For each object model add columns with [status]_at name. In the above case the model will have created_at, submitted_at, processing_at, success_at columns.
Store the current object state in the object model. Create a separate table which stores the object state with time. In this case even if I want to add more states to objects it will not change my db schema.

What is the best way of storing the data? What are the pros and cons of the above solutions? Open for other solutions also.


texts:
I want to create a rating weight and I need do calculate AVG, then find COUNT and multiply both to get weight. I have something like that:

SELECT post_id, COUNT(CASE WHEN vote > 0 THEN vote ELSE NULL END) AS voters,
AVG(CASE WHEN vote > 0 THEN vote ELSE NULL END) AS average FROM wp_imdb_rating
GROUP BY post_id
ORDER BY voters * average DESC
LIMIT 0, 100

But it says:

  #1247 - Reference 'voters' not supported (reference to group function)

I can order by average and voters separately, but I can't combine them with any formula. How can I use a formula in order clause that includes values from AVG and COUNT?

Thanks.


texts: Script component source won't load when click "Edit Script..."

texts:
I'm working on an SSIS package in Visual Studio 2013. For any script component throughout the package, if I double-click on it and then click the "Edit Script..." button, Visual Studio 2012 opens but no editor window loads. It's just an empty window like you closed all documents. The Solution Explorer shows items, but notably "ScriptComponent" is an empty folder:

If I try to open any of the files (BufferWrapper.cs, ComponentWrapper.cs, main.cs) in the Solution Explorer, I get a popup error box saying:

Anyone have any clue what's going on here? FWIW, I have tried closing all instances of Visual Studio and reopening my solution with the SSIS package. The same behavior persists.


texts: SQL Server Management Studio generates error, can't start

texts:
On my development machine, SQL server 2008 R2 was running fine previously. But after formatting OS and re-installing the OS and SQL server, it generates the following error when I want to start SQL Server Management Studio.

Error Message:

  A problem was encountered initializing commands and menus. The merged file was loaded, but it contained no valid commands. Please run setup and select Repair.

Please note that I've executed setup and repaired successfully after getting the above error.


texts:
I am trying to set up query notification however when I try to create a queue and service I get the error 'There is no Service Broker active in the database. Change to a database context that contains a Service Broker.'  When I run the query

select is_broker_enabled from sys.databases where name='db01'

It returns 1 which means that a broker has been enabled on the database

Here are the sql commands I am running to create my queue and service...

CREATE QUEUE DeviceChangeMessages
CREATE SERVICE DeviceChangeNotifications ON QUEUE DeviceChangeMessages ([http://schemas.microsoft.com/SQL/Notifications/PostQueryNotification]);


texts: Has my repair fixed incomplete patching?

texts:
I am patching a SQL2008R2 Express Edition instance. On the first failed attempt, all seemed to be fine - SQL loaded, dbs online, @@VERSION returned the expected results, but errors were found during installation.

I had the issue below

The registry key System\CurrentControlSet\Services\MSSQLSERVER\Performance is missing.

For which I followed this post to fix

https://blog.sqlauthority.com/2018/04/28/sql-server-service-pack-failure-missingregistrykeyexception-the-registry-key-is-missing/

It seems to have worked. I ran Repair from the setup image. But I am not able to run the patch - the initial screen tells me that the Upgrade Status is 'Installed' - whereas before this Repair fix it told me 'Installation incomplete'.

Can anyone confirm that the patching has been successful? I would think so, as it says there SP3, Installed. @@VERSION returns the correct results, but it already did when the patch was 'incomplete'.
Has SQL 'caught up' during the repair, or were the incorrectly installed aspects of SP3 repaired?

Thanks


texts: ER Diagram - View Intergration/Centralised approach

texts:
Could anyone enlighten me on the difference between a view integration and centralised approach when designing a database, and how the ER diagram can be used to illustrate upon this; can't seem to find any simple explanations on the topic

i understand ER diagrams


texts:
I know how to create a new column based on an old one , encrypt it after creating a master key/certificate and etc.
I would like to know what would be the best approach to encrypt ceartin columns without the need to create a new one.
I mean, is there a way to encrypt a column, and during a select, we see encrypt data or something like J&Y@HS@H&HS and only some users can see it?
I'm not talking about TDE in the entire database or backup encryption.
Thanks.


texts:
I purged about 100 GB on 200 GB SQL Server v11 DB. The MDF file still show about 200 GB in the file explorer.

Everything looks fine, first tests shows 50% time treatments increase and the backup's size are reduced.

Do you think shrinking is still interesting ? If yes what could be the best way ?

Release unused space,
Reorganize pages before releasing unused space (shrink file to : X MB),
Empty file by migrating the data to other files in the same filegroup.

Index Reorganize/Rebuild, then Update Stats is planned anyway after this operation.

Thanks for help


texts: Cost of indexed view versus filtered index?

texts:
I have met the following table design for recording references in non-normalized way:

RecordGUID              uniqueidentifier
DestinationEntity       uniqueidentifier
DestinationRecordGUID   uniqueidentifier

For example for Users table to describe references to Countries, we will have:

 RecordGUID                             DestinationEntity                       DestinationRecordGUID
 79282C80-4474-4617-9C40-C21E97F5AC52   3A462EF8-DAB6-4B98-BA1B-FBD957A1737C    5842E524-8C5D-4DEE-A553-0857FB9C12F4
 B25DA064-8DAF-4EEB-8894-D540B3A929EA   3A462EF8-DAB6-4B98-BA1B-FBD957A1737C    C2B0AA1A-E688-4E2C-BD5F-16309C496774

So, the 3A462EF8-DAB6-4B98-BA1B-FBD957A1737C means Countries, while 5842E524-8C5D-4DEE-A553-0857FB9C12F4 and C2B0AA1A-E688-4E2C-BD5F-16309C496774 are referring records in Counries table.

We are having many relations here - for example, users to languages, companies, etc. And because working with GUID in order to join the tables is not user friendly, we have indexed view for each relation like this:

CREATE VIEW [dbo].[users_countries]
WITH SCHEMABINDING
AS
SELECT [RecordGUID]
      ,[DestinationEntity]
      ,[DestinationRecordGUID]
FROM (the relationship table)
WHERE [DestinationEntity] = '3A462EF8-DAB6-4B98-BA1B-FBD957A1737C';

as a result one can easily make a join with this view to the counties table and query it - also, because of using the view (with no expand) and having a index on it, not all records from the original table are read (it's faster).

The system may have hundreds of indexed views and I am worried about the cost of the engine to manage them, compare to filtered indexes on the original table. I mean instead having a view just to create index like this:

CREATE INDEX (index_Name) ON (the relationship table)
(
    [RecordGUID]
   ,[DestinationRecordGUID]
)
WHERE [DestinationEntity] = '3A462EF8-DAB6-4B98-BA1B-FBD957A1737C'

We are not able to query in a friendly way the table anymore, but will this affect the performance?

I was not able to find any topics about indexed view hurting the performance - but facing such design makes me doubting.

I am wondering can we say that the maintenance of the index of a view and the same index on a table (like the above) is the same?


texts: Query performance become slower after upgrade to MySQL 8.0.20

texts:
I have found bug/issue after update the MySQL Server from 5.7 to 8.0.20.
When I run a query with the user-define function in 5.7(same session), the duration for each query is constant and stable.
But while I run the same query in the 8.0.20(same session), the duration for each query is kept increasing.
I have tried running the query without the user-define function in 8.0.20, the duration for each query is back to normal. In the second test with the user-define function but without the query in the function, the result is also normal.
So I suspect the query in the user-define function is the root cause to this bug/issue.
But I cannot think of why this is only happening in 8.0.20, but its work great in 5.7.
Below are the sample result, table structure and the user-define function. Please let me know If there is any additional information is needed.
Result in 5.7:    https://pastebin.com/Wexs69UP
Result in 8.0.20: https://pastebin.com/kBq6DLGy
Table A
CREATE TABLE `item_2` (
  `id` int NOT NULL AUTO_INCREMENT,
  `receive_dt` datetime NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

Table B
CREATE TABLE `holiday` (
  `holiday` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `date` date NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

user-define Function
DELIMITER $$
CREATE DEFINER=`admin`@`%` FUNCTION `min_diff`(start_datetime datetime, end_datetime datetime) RETURNS int
BEGIN

/*declare*/
DECLARE min_diff int;
DECLARE off_day int;
DECLARE temp_start_datetime datetime;
DECLARE temp_end_datetime datetime;
DECLARE temp_start_date date;
DECLARE temp_end_date date;
DECLARE holiday boolean;

SET temp_start_date = date_format(start_datetime, '%Y-%m-%d');
SET temp_end_date = date_format(end_datetime, '%Y-%m-%d');
SET temp_start_datetime = start_datetime;
SET temp_end_datetime = end_datetime;
SET off_day = 0;

/*If Star date greater than End Date*/
if temp_start_datetime > end_datetime
then return 0;
end if;

/*Skip 1st day*/
set temp_start_date = temp_start_date + INTERVAL 1 DAY;

/*loop from start days to end day*/
while temp_start_date < temp_end_date do

/*exclude holiday*/
SET holiday = (SELECT EXISTS(SELECT 1 FROM `holiday_list` WHERE `date`=temp_start_date));

/*if weekend or holiday */
/*  5-saturday
    6-sunday*/
if (weekday(temp_start_date)=5 OR weekday(temp_start_date)=6) OR holiday
then set off_day = off_day+1;
end if;

/*plus one days*/
set temp_start_date = temp_start_date + INTERVAL 1 DAY;

END WHILE;

SET min_diff = (SELECT TIMESTAMPDIFF(MINUTE, temp_start_datetime, temp_end_datetime));
SET min_diff = min_diff - (off_day*24*60);

/*return days diff*/
RETURN min_diff;
END$$
DELIMITER ;

Thanks in advance, and sorry for the bad grammar.


texts: How do I compare two tables with the same column definitions in different schemas?

texts:
Any advice would be appreciated. I'm leading my projects initiative to upgrading our ETL software. This can result in data integrity differences. My testing plan is as follows:

create an identical schema, schema B, with the same table definitions as schema A
run all the ETL jobs to populate schema B using the upgraded ETL version
**compare the two schemas and record differences
determine why those differences occured

**So my question is regarding step 3. What technically do I need to do (commands, queries, etc) to compare every field in every row between the two schemas to confirm that they are identical?

Thank you for your time!


texts: Idempotent user and database creation script with MySQL

texts:
I have written a shell script which creates a database and user on a MySQL server. The user and database are to be used by a web application and generally this task only be performed once per website installation, but sometimes, (e.g. testing the script) the script needs to be run many times.
Currently the script assumes the user always exists and just pre-emptively deletes, i.e.

DELETE FROM mysql.user WHERE User = '${web_app_sql_user}';

Ive also tried drop user ${web_app_sql_user}@localhost; but then that fails if the user doesn't exist if you can believe that! lol

Script

#!/bin/bash
echo -e "Creating a SQL user and database for the webapp"

read -s -p "enter SQL root user password: " password

cat <<- EOF | mysql -v -uroot -p$password
    DROP DATABASE IF EXISTS \`${web_app_database}\`;
    CREATE DATABASE \`${web_app_database}\` CHARACTER SET utf8 COLLATE utf8_general_ci;

    -- Deal with possibility user already exists!
    DELETE FROM mysql.user WHERE User = '${web_app_sql_user}';
    -- drop user ${web_app_sql_user}@localhost;
    FLUSH PRIVILEGES;

    CREATE USER '${web_app_sql_user}'@localhost IDENTIFIED BY '${web_app_sql_user_password}';
    -- CREATE USER '${web_app_sql_user}'@localhost IDENTIFIED BY '${web_app_sql_user_password}';
    GRANT ALL ON ${web_app_database}.* TO '${web_app_sql_user}'@'localhost';

    GRANT USAGE ON * . * TO  '${web_app_sql_user}'@'localhost' IDENTIFIED BY  '${web_app_sql_user_password}' WITH MAX_QUERIES_PER_HOUR 0 MAX_CONNECTIONS_PER_HOUR 0 MAX_UPDATES_PER_HOUR 0 MAX_USER_CONNECTIONS 0 ;
    GRANT ALL PRIVILEGES ON  \`${web_app_database}\` . * TO  '${web_app_sql_user}'@'localhost';

    FLUSH PRIVILEGES;
EOF

Result when running the script

--------------
DROP DATABASE IF EXISTS `web_app_database`
--------------

--------------
CREATE DATABASE `web_app_database` CHARACTER SET utf8 COLLATE utf8_general_ci
--------------

--------------
DELETE FROM mysql.user WHERE User = 'web_app_user'
--------------

--------------
FLUSH PRIVILEGES
--------------

--------------
CREATE USER 'web_app_user'@localhost IDENTIFIED BY ''
--------------

ERROR 1396 (HY000) at line 17: Operation CREATE USER failed for 'web_app_user'@'localhost'

Is it possible for MySQL to be able to handle these situations?

MySQL Server version: 5.5.46-0ubuntu0.14.04.2 (Ubuntu)

Updated script based on michael-sqlbot's answer

read -s -p "enter SQL root user password: " password
cat <<- EOF | mysql -v -uroot -p$password
    DROP DATABASE IF EXISTS \`${web_app_database}\`;
    CREATE DATABASE \`${web_app_database}\` CHARACTER SET utf8 COLLATE utf8_general_ci;
    GRANT ALL PRIVILEGES ON  \`${web_app_database}\` . * TO  '${web_app_sql_user}'@'localhost';
    FLUSH PRIVILEGES;
EOF


texts:
I'm very new to SQL and I'm trying and failing to get the right syntax on what I presumed would be a pretty easy query.

I have a table (CustomerSales) with four columns:

Period: a combination of year and month, e.g. 201501 is January 2015;
Customer_no;
Sales;
Class, i.e. customer segment.

I currently have the below query which extracts all sales in 2015 for class 'R', by customer number:

select Customer_no, class,
SUM (Sales) as '2015'
from CustomerSales
where left(Period,4) = '2015' and class = 'R'
Group by Customer_no, class

This returns a simple 3-column output of Customer_no, class and 2015 Sales.

I would like to add columns for each year in the table, i.e. add five more columns for each year 2010-2014.

To do this, I tried to move the left(Period,4) = '2015' expression into a case when condition and then my intention was to repeat this for 2014, 2013, etc. But the query fails before I even try to include the other years. The current query (with just 2015) is:

select Customer_no, class,
SUM (case when(left(Period,4) = '2015' then Sales else 0 end)) as '2015'
from CustomerSales
where class = 'R'
Group by Customer_no, class

The error I get is

  Incorrect syntax near the keyword 'then'.

I'd be grateful for any help as to how to fix the above query and add the 2010-2014 sales as extra columns.


texts: START WITH and CONNECT BY condition in Oracle SQL

texts:
I have a question on the START WITH and CONNECT BY condition (in Oracle 12c):

This is my Oracle SQL:

SELECT
    employee_id,
    lpad(' ', level*2-1,' ') || last_name,
    manager_id,
    level,
    SYS_CONNECT_BY_PATH(last_name, '/') "Path" --returns the full path of the child
FROM
    hr.employees
start with employee_id = 100
CONNECT BY PRIOR employee_id = manager_id
AND NOT (last_name = 'Cambrault' and first_name = 'Gerald')
order siblings by last_name

which is giving the following output:

EMPLOYEE_ID     LAST_NAME           MANAGER_ID  LEVEL   PATH
100             King                    null    1   /King
102                 De Haan             100     2   /King/De Haan
103                     Hunold          102     3   /King/De Haan/Hunold
105                         Austin      103     4   /King/De Haan/Hunold/Austin
104                         Ernst       103     4   /King/De Haan/Hunold/Ernst
107                         Lorentz     103     4   /King/De Haan/Hunold/Lorentz
106                         Pataballa   103     4   /King/De Haan/Hunold/Pataballa

But when I remove the START WITH in the above query:

SELECT
    employee_id,
    lpad(' ', level*2-1,' ') || last_name,
    manager_id,
    level,
    SYS_CONNECT_BY_PATH(last_name, '/') "Path" --returns the full path of the child
FROM
    hr.employees
CONNECT BY PRIOR employee_id = manager_id
AND NOT (last_name = 'Cambrault' and first_name = 'Gerald')
order siblings by last_name

it gives the following result:

EMPLOYEE_ID     LAST_NAME   MANAGER_ID  LEVEL   PATH
148             Cambrault   100         1       /Cambrault
172                 Bates   148         2       /Cambrault/Bates
169                 Bloom   148         2       /Cambrault/Bloom
170                 Fox     148         2       /Cambrault/Fox
173                 Kumar   148         2       /Cambrault/Kumar
168                 Ozer    148         2       /Cambrault/Ozer
171                 Smith   148         2       /Cambrault/Smith

Can you please explain why despite giving using the condition NOT (last_name = 'Cambrault' and first_name = 'Gerald') it has no effect, whereas it works only when used with START WITH (starting the hierarchy from the employee_id = 100). I understand the condition in CONNECT BY has no effect on the root row(s) but employee_id (148) is not root; it is just a parent row so why it is not working?


texts: MYSQL optimization questions about values on server

texts:
I have an etc/my.cnf file with these values:
innodb_force_recovery=6
sql-mode="NO_ENGINE_SUBSTITUTION"
innodb_strict_mode=0
bind-address=0.0.0.0
performance-schema=0
innodb_file_per_table=1
thread_concurrency=4
max_connections=250
max_user_connections=30
key_buffer_size=256M
myisam_sort_buffer_size=64M
join_buffer_size=1M
read_buffer_size=1M
sort_buffer_size=1M
interactive_timeout=45
wait_timeout=360
connect_timeout=120
max_allowed_packet=1053741824
max_connect_errors=10
query_cache_limit=20M
query_cache_type=1
flush
flush_time=60
open_files_limit=50000
default-storage-engine=MyISAM

What would you recommend when having this kind of warnings in Mysql please:
created tmp disk tables    345.7K
handler read rnd 165M
handler read rnd next 45G
innodb buffer pool reads 140k
opened tables 2k
Qcache lowmenprunes 50k
sort merge passes : 55
select full join 450k

All the above showing as "High" in PHPMYADMIN
The site is relatively slow and I wonder if increasing the memory in some cases might help a bit but I would like to have an opinion on this please first.
Thank you.


texts: Adding columns from joined table slows down query incredibly

texts:
I have a fairly complex query that joins several tables and views. It executes pretty quickly (~9 seconds for 2200 rows), except when I add a column from a view that is already joined in the query the query seems like it will never finish - I left it running for 33 minutes once and it had not completed.

SELECT
    table1.col1,
    table2.col2,
    view1.col3,
    view2.col4

FROM
    table1
    INNER JOIN table2 ON table1.id = table2.id
    LEFT OUTER JOIN view1 ON view1.id = table1.id
    LEFT OUTER JOIN view2 ON view2.id = view1.id
    LEFT OUTER JOIN view3 ON CONVERT(CHAR(8),view1.DateRequired,112) + LEFT(view2.ItemNumber, 4) = view3.UID

The above will run fine, but when I add any column from view3 into the SELECT list the query slows down to a crawl. I'm unable to get the full result set but if I limit the result to 50 rows it takes around 1 minute to run to completion compared to roughly 1 second with the original query.

Now, I know that the join for view3 is pretty complicated; it converts one value from a date to a string and then concatenates this to a value from another table and uses this resulting concatenated blob of info to join against the same style code in view3. However, I would expect that if this were a problem with the join itself then it would slow down the query when the view itself is joined, not just when columns from that view are added into the SELECT list.

I managed to run an execution plan when the extra column is added and it is absolutely colossal. I did manage to find one task that has a cost of 51%: RID Lookup (Heap). I'm not au fait with execution plans so not sure what this means or how to resolve it.

UPDATE

Using Scott's answer I created a non-clustered index on the row that was causing the expensive RID lookup. It improved performance quite a lot - down from over a minute to around 30 seconds to return the 50 row limit.

I think Nic is right in his suggestion that the datatype conversion from date to char(8) in the join condition is causing the bulk of the cost when querying. Going to work on another way to join view3.


texts:
We have a backup process set to copy files over to a different network. Occasionally we lose internet connection and then one of the backups files will fail to copy over. This becomes a bigger issue for larger files as they take much longer to copy over and if it needs to be copied again we need to wait a few hours. Our proposed solution is to split the backups across multiple files so that when it fails we only lose a smaller file.
Is there any other/better solutions to handle this type of issue?


texts: Issues (Other Than Disk Space) For Using Operating System Partition

texts:
Other than accidentally filling your entire C:\ drive with data, are there any other considerations for using the OS drive for data files?  Most answers are simply "don't do it", but I'd like to know any technical reasons.
I'm on a dev box and will gladly trade risk for extra IO bandwidth.  I'm refactoring a very large dataset, which generates 10-20 GB (simple) log files and a ton of tempdb activity.  I'm moving the source, read-only tables to sata SSDs.  I'd like to give the refactored data, tempdb data, and log files their own NVMe, but that means one of them would share C: with the OS.