texts: Can I restore a Replication Subscriber backup without needing to re-configer/re-initialize

texts:
I've been trying to read up on this and there are quite a few of posts about restoring a subscribers' backup on a different machine but my question is about -- in my opinion -- a much more simple situation.

We have a subscriber database that presents a set of large tables for reporting purposes. Suppose 'somehow' something gets messed up and the (transactional) replication process gets broken. (e.g. someone drops a column from one of the replicated tables (hypothetical example, but I suppose this would be fatal ;-) ).

Would I be able to fetch a backup of the subscriber database from the day before, restore it on top of the existing subscriber database and assume that the replication will resume? And even if that would 'work'; would it catch up from the point in time where the backup was made or would I lose all changes between the moment of the backup and the moment the replication got broken?

My guess is this wouldn't work, but I'll gladly be proven wrong. Assuming this wouldn't work; what would be the easiest way to 'restart' from yesterdays backup or should I simply live with a total removal & recreation of the replication? (it's scripted, so not really a disaster)

I know I could 'simply' try it out and see what happens, but I'd rather hear from you guys first before drawing conclusions on a sample-size of 1. (besides, I could get lucky now and end up being screwed in the future)

Thanks,
Roby

PS: I noticed that there is a KEEP_REPLICATION option in the RESTORE command, but it's not overwhelmingly documented.


texts: Unable to Create COMMIT inside PostgreSQL 11.5 Procedure

texts:
I'm trying to learn PostgreSQL stored procedures. Specifically creating the procedure below in PSQL.

CREATE OR REPLACE PROCEDURE BUILD_AND_POPULATE(INOUT cresults refcursor)
    LANGUAGE PLPGSQL
    AS $$
    BEGIN
        BEGIN; -- I've tried removing this but the behaviour is the same
        cresults:= 'cur';
        DROP TABLE IF EXISTS procsampledata;
        CREATE TABLE procsampledata as select x,1 as c2,2 as c3, md5(random()::text) from generate_series(1,10) x;
        COMMIT;
        OPEN cresults FOR SELECT * FROM procsampledata;
    END;
$$;

Then I execute it like so, but receive an error:

postgres=# call build_and_populate(null);
ERROR:  invalid transaction termination
CONTEXT:  PL/pgSQL function build_and_populate(refcursor) line 6 at COMMIT

I've tried with AUTOCOMMIT set to both on and off.

This is my Postgres version

 PostgreSQL 11.5 on x86_64-pc-linux-musl, compiled by gcc (Alpine 8.3.0) 8.3.0, 64-bit

Can anyone see what I'm doing wrong? Thank you!


texts: SQL Server 2014 SP1 installation iso file question

texts:
I read a lot regarding the issues with the original SP1 slipstream file and if
the file names to download can be clarified it will be very helpful.

What files do I need to install SQL 2014 SP1 CU6? I have the developer edition license and will not be using this in production. Is there a slipstream SQL 2014 SP1 ISO file available for download or do I install SQL 2014 RTM then SQL 2014 SP1 and then SP1 CU6.Which RTM CU6 file should be used?

I expect the file size to be around 4GB so the file size indicated does not seem correct for slipstream SP1 ISO

Is there a separate download required for SSMS 2014 etc.

I apologize as this is a very basic question. Thank you

SQLServer2014SP1-KB3058865-x64-ENU.exe
Date Published:5/14/2015
File Size: 639.3 MB

SQL14SP1CU6\x64\SQLServer2014-KB3144524-x64.exe
Date Published:4/18/2016
File Size: 579.0 MB


texts:
I'm having trouble with my MongoDB Replica Set. I'm connecting to the server and using the database fine from two servers but on another server I'm getting the following error:

bson.errors.InvalidDocument:
    BSON document too large (2119 bytes) -
    the connected server supports BSON document sizes up to 0 bytes.

I have no idea where to begin on this, my MongoDB Server version is 2.4.10, the code on each server is the same with some configuration file differences. The DB Server is on AWS as are the two working servers. The server that is not working is on a Digital Ocean server.

I have allowed the Digital Ocean server access to the DB server with AWS Security Groups and it seems to be connecting fine.

Also, I am able to access and update the database from my local machine using the command line and RoboMongo.


texts: SSRS CROSS Domain Reports Server Authentication

texts:
I have a pretty complicated situation at hand, let me try to explain.

I have a SQL Server and a Web Server in a separate domain (lets call it domain A).

SQL Server has the databases for Reporting services, The web server has reporting services installed.

The SSRS also have SSL certificates installed and we are using https protocol to connect to the reports manager.

Now I need to give access to users from domain B to connect to the Reports Manager (report server on Domain A).

The users from Domain B cannot have any logins in the Domain A where the reporting services are installed, I know to access the reports manager I need to add domain logins/groups to the reports manager and assign them appropriate roles to access the reports.

What options do I have (if any) to give access to users from Domain B to connect to a Reports Manager on Domain A?

Important Note:

The access to reports server is via NLB with external facing IP, the reports server (web server) or the SQL server does not have any external facing IP


texts: Why are some aggregates treated differently in the RHS of a rule in the model clause?

texts:
With my limited understanding of the model clause, I'd expected the following three queries to return the same results because there are no nulls in the data, and the functions are equivalent:

select *
from (select level k, 100 v from dual connect by level<=2)
model return updated rows
dimension by (k)
measures (v, 0 shr)
rules ( shr[any] = v[cv()]/sum(v)[any] );  --plain sum
/*
         K          V        SHR
---------- ---------- ----------
         1        100        0.5
         2        100        0.5
*/
select *
from (select level k, 100 v from dual connect by level<=2)
model return updated rows
dimension by (k)
measures (v, 0 shr)
rules ( shr[any] = v[cv()]/nullif(sum(v)[any],0) ); --with nullif
/*
         K          V        SHR
---------- ---------- ----------
         1        100       0.25   <------\___ why?
         2        100       0.25   <------/
*/
select *
from (select level k, 100 v from dual connect by level<=2)
model return updated rows
dimension by (k)
measures (v, 0 shr)
rules ( shr[any] = v[cv()]/decode(sum(v)[any],0,null,sum(v)[any]) );  -- with decode
/*
         K          V        SHR
---------- ---------- ----------
         1        100        0.5
         2        100        0.5
*/

What am I missing about the processing of rules that explains this behaviour?

SQLFiddle here


texts: Connecting to local database on Azure VM

texts:
Using DBforge to try and connect to a local MSSQL database on an Azure Ubuntu 18 VM, I am getting this error:

A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify
that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open
a connection to SQL Server)

I have:

Enabled SSH on the inbound rules.
I can SSH into the Azure VM using PuTTY
I have tried both the host name (xx.eastus.cloudapp.azure.com) and the public IP address (13.82.xx.xx) to try and connect, both give this error.
I can connect to the website running on the VM by using the host name, and going to the public IP address shows the Apache landing page.
The website is running 100%, and I can log in, so the database instance is running.
I followed the advice here, and I confirm my server is listening on port 1433. (Log entry: Server is listening on [ 'any' <ipv4> 1433].)
Port 1433 is not blocked by a firewall. I have disabled the Azure firewall for testing purposes.
Various other answers suggest I must go into services.msc etc., but this is a Linux VM, so those won't work either.

I am getting the same error using SSMS, and none of the answers here seem to work for my specific case. All of them speak about connecting to an Azure SQL database, which this is not.

Any idea what else I can try?


texts: Postgres Problems with Phusion Deployment (Rails)

texts:
FATAL:  Ident authentication failed for user "test-postgres"

So I have a postgres server hosting a side of my database access for my Rails application and I have a database for production set up and a user for production set up but I keep getting the previous error whenever I try to view my Rails application from a browser. Can somebody help me out? I have the postgres user created and the database created already...


texts: Write output from dynamic queries in PL/pgSQL to CSV file

texts:
PG_CMD="psql -d portal -U portal -c "
PG_CMD_TP="psql -d portal -U portal -t -P format=unaligned -c "
abc()
{
 $PG_CMD "
        DO \$$
        DECLARE
           srowdata record;
           customer_list varchar[];
           customer_schema varchar(100);
           portal_row a.portal%ROWTYPE;
           var1 varchar(100);
           temp varchar(100);
        BEGIN
           customer_list=ARRAY(select cname from customer);

            FOREACH customer_schema IN ARRAY customer_list LOOP

               EXECUTE format('select %s.portal.*,%s.p_fb_config.*,%s.p_gplus_config.*,%s.p_linkd_config.*,%s.p_localum_config.*,
                   %s.p_sms_config.*,%s.p_twt_config.*,%s.p_webform_config.*,%s.p_wechat_config.*,%s.p_clickthrough_config.*,%s.splash.*
               from %s.portal left outer join %s.p_fb_config on %s.portal.pid = %s.p_fb_config.pid left outer join %s.p_gplus_config
               on %s.portal.pid = %s.p_gplus_config.pid left outer join %s.p_linkd_config on %s.portal.pid = %s.p_linkd_config.pid left
               outer join %s.p_localum_config on %s.portal.pid = %s.p_localum_config.pid left outer join %s.p_sms_config on
               %s.portal.pid = %s.p_sms_config.pid  left outer join %s.p_twt_config on %s.portal.pid = %s.p_twt_config.pid left outer join
               %s.p_webform_config on %s.portal.pid = %s.p_webform_config.pid left outer join %s.p_wechat_config on
               %s.portal.pid = %s.p_wechat_config.pid  left outer join %s.p_clickthrough_config on
               %s.portal.pid = %s.p_clickthrough_config.pid left outer join %s.splash on %s.portal.pid=%s.splash.pid;', customer_schema,
                   customer_schema,customer_schema,customer_schema,customer_schema,customer_schema,customer_schema,customer_schema,
                   customer_schema,customer_schema,customer_schema,customer_schema,customer_schema,customer_schema,customer_schema,
                   customer_schema,customer_schema,customer_schema,customer_schema,customer_schema,customer_schema,customer_schema,
                   customer_schema,customer_schema,customer_schema,customer_schema,customer_schema,customer_schema,customer_schema,
                   customer_schema,customer_schema,customer_schema,customer_schema,customer_schema,customer_schema,customer_schema,
                   customer_schema,customer_schema,customer_schema,customer_schema,customer_schema,customer_schema) INTO srowdata;

                   raise notice ' %: %', customer_schema,srowdata;
            END LOOP;
        END;
        \$$";
}

abc

I have written anonymous block in plpgsql. Now, instead of using raise notice I want to write this output in CSV format. I tried COPY but it accepts argument as a query.


texts: How to optimize clustered index rebuild?

texts:
I need to rebuild the clustered index of the table that has more then 1.6 millions rows, more then 20 non-clustered indexes and 80 columns in order to reclaim space after two NVARCHAR(MAX) columns were deleted.

I have try using the following command:

ALTER INDEX [PK_A] ON [dbo].[B] REBUILD PARTITION = ALL
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80)
GO

and using DBCC CLEANTABLE command.

In both cases it takes more then 19 hours. Is there a way to optimize the clustered index rebuild operation?


texts: How to maintain database ownership restoring across domains?

texts:
I am working with a vendor who is developing a SQL Server database outside of our domain. Occasionally, they need to deploy a new copy of that database into our domain by backing up their database, and restoring over the top of the of the database within our domain.

The vendor uses a domain login on our side to access this database. This domain login is also the owner of this local copy. This vendor is trusted enough that we have granted the login the "dbcreator" server role. A combination of database ownership and the dbcreator role allows this domain login to restore a copy of the database over the top of the existing database. The restore completes, but as soon as it's done, the domain login can no longer access the database because the database ownership is lost. (I believe because it had a different owner on the vendor's system). After the restore is complete, I have to re-create the user within the database that matches up to the domain login, and make that user the owner again. The domain login can then access the database again.

Is there any way I can configure this so the vendor is able to perform the restores when they need to without losing the existing ownership? This way I would not have to be involved every time they deploy a new version of the database. I cannot give them sysadmin because there are other databases on the server.


texts: Splitting file groups of large SQL Server database using FileStream

texts:
Background:

I have a SQL Server database which is storing documents in the file stream. These documents are usually accessed via Web API by Mobile Apps and Modern JS Apps.

The size of the database is around 2 TB with a growth rate of 30% per year.

Situation:

Full backup of the server happens every night to the disaster environment. This is taking a substantial time.

Task:

To reduce the backup load, we only want to back up the last year's documents in the disaster recovery environment because there are very fewer chances that documents older than one year have been changed.

Action:

To do so, I've created two filegroups, one for the current documents (storing document added no longer than one year ago) and one for the archive document (storing document older than one year). My plan was to store these two filegroups in a different drive and ignore the drive having old documents in the backup process.

Result:

This hasn't worked because if a filegroup is missing, SQL Server marked your database as unavailable or under maintenance.

Question:

Is there any other way of splitting the database without changing the existing schema or stored procedure etc.


texts: If database is compromised and downloaded, does it matter if user passwords are hashed?

texts:
I'm currently implementing as much security into a web app as possible, but I can't stop thinking about the database itself. For all the security I implement, such as salting and hashing passwords in the users table, if hackers manage to download the database entirely (by getting access to the server for example), they'll be able to see all the data in the entire database?

I've been reading about all the database breaches of well-known brands within the past couple of years, and that these databases are for sale on the dark web.

I understand hashing passwords will slow down / stop hackers having access to plain text login credentials that may be the same for other websites the user has signed up to, but my question is, what about the personal information stored in the database that isn't hashed. Like which products you've bought over the past couple of years, or your employment history, or your contacts, or something really embarrassing such as which adult websites you've visited and/or bookmarked.

Considering you can't hash a user's email address if it's being used as part of the login credentials, some hacker could query the database using the userID for example, get personal information from other tables, and then send a blackmail email to that user.

The two potential solutions I can think of are:

Encrypt as much of the information in the database as possible - but can't encrypt it all otherwise we wouldn't be able to perform quick insight queries on our user base for example.
Encrypt the actual database file itself on the server, so if it were downloaded, they wouldn't be able to have access without knowing the key.

So, 1 isn't much of a solution because I can't encrypt everything. And would 2 actually work i.e. would a web app using django for example be able to write to the database if its root file on the server was an encrypted file? I know MS SQL Server has an always-encrypted feature, but I'm using MySQL.

Sorry for the essay-length question - my head is spinning with all sorts of possibilities, and it's driving me nuts!

Thank you!


texts: Install SQL Server 2008 Express on same machine as normal SQL Server 2008 install?

texts:
When setting up a server, we installed SQL Server 2008. Now it turns out we don't need it, and we want to use the free Express version instead. Ideally without much interruption to things using the database. Is it possible to install Express side by side with the full version without disrupting things?

The tables are small and the load is relatively light. As in, there are maybe 30 rows in each table, and a few aspx pages that use them to keep track of state. We're not doing anything other than simple queries on a few tables in our current setup. I could get by using SQLite, if I wanted to rewrite some ASP.NET pages to use it.


texts: MySQL Load XML Infile with dynamic file name

texts:
I have a shell script which loops through a folder and picks up a set of files in turn.

I then pass the filename to a .sql script so that the file can be imported into my database.

BUT, I can't for the life of me get it to work.

My Basic script is as follows:

#shell script includes:
mysql -hxx -Pxx -uxx -pxx -e "set @filename=${filename}; source ${sqlscript};"

#sql script includes:
LOAD XML LOCAL INFILE @filename
INTO TABLE `temp_test_log`
ROWS IDENTIFIED BY '<test_record>';

But this keeps telling me error in your sql sytax. . .near @filename. . .

If I type the filename in manually it works fine. But I have 50 such files, and this will be a frequent process which I need to automate.

If I do select @filename; it works and I get the 'filename'.

I tried concatenating it with CONCAT('\'',@filename,'\'') but that didn't work.

I tried creating a stored procedure, but that 'isn't supported'.

I tried using a prepared statement, but that 'isn't supported'.

Is there a way of doing this, am I missing something?


texts: During a log backup is the data backed up to the start or end of the operation?

texts:
Say I am running a log backup, and that log backup takes 10 minutes to complete. During that 10 minute window, further transactions are run. Given the below example, which transactions does the log backup actually contain?

  Transaction A commits
  Transaction B opens
  Log backup begins
  Transaction C opens
  Transaction B commits
  Log backup completes
  Transaction C commits


texts:
Here is my requirement for a SAP HANA 2.0 system. 1) Identify users with not having SAML flag enabled from "SYS."USERS" table. Fields that I need from this table are USER_NAME,EXTERNAL_IDENTITY,IS_SAML_ENABLED. Sample data looks like this.

USER_NAME    EXTERNAL_IDENTITY    IS_SAML_ENABLED.
JDOE          JDOE@aol.com        TRUE
JDOE1         JDOE1@mail.com      FALSE
JDOE2         JDOE@aol.com        FALSE

2) If SAML is not enabled, I have to run these two SQL statements on each user ID in a loop until all those users identified in step 1 are updated and then close the loop. alter user JDOE enable SAML; alter user JDOE add identity 'JDOE' for SAML provider ;

SAML_PROVIDER variable comes from a table SAML_PROVIDERS that has multiple entries and and so on.

3) Another constraint is to map SAML to user based on their EXTERNAL_IDENTITY that has multiple values. This field is used for Kerberos authentication. So if first user has @aol.com then assign 'SAML_PROVIDER1' and if @mail.com is there then assign

4) I am looking to have this in a stored procedure because I would like to schedule this as a background job in HANA XS.

Here is the code that I tried to come up with after spending considerable amount of time and looking at information available online. Please note that I still not added the second SQL statement to the code. I am open to other suggestions that does not involve loop as long as I can do these tasks through a background job.

PROCEDURE "<SCHEMA>"."<add_saml>" ( )
LANGUAGE SQLSCRIPT
SQL SECURITY INVOKER
AS
i INTEGER;
row_count INTEGER;
loop_current_SQL NVARCHAR(200);
valid_SAML NVARCHAR(5);
BEGIN
it_no_saml_users = SELECT DISTINCT
A."USER_NAME",A."IS_SAML_ENABLED",A."CREATOR"
FROM "SYS"."USERS" A
LEFT OUTER JOIN "SYS"."SAML_USER_MAPPINGS" B
ON (A."USER_NAME" = B."USER_NAME")
WHERE B."USER_NAME" IS NULL and A."CREATOR"='ADM' and
A."IS_SAML_ENABLED"='FALSE';
SELECT COUNT("USER_NAME") into row_count FROM :it_no_saml_users;
FOR i IN 0 .. :row_count -1 DO
SELECT "IS_SAML_ENABLED"
into valid_SAML FROM :it_no_saml_users
LIMIT 1 OFFSET :i;
IF :valid_SAML IS NULL THEN
SELECT 'ALTER USER' || "USER_NAME" || 'ENABLE SAML'
INTO loop_current_SQL
FROM :it_no_saml_users;
EXEC(:loop_current_SQL);
END IF;
END FOR;
END

When this procedure is ran, I do not get any error but number of rows affected is shown as 0.
I am new to SQL and please ignore if my procedure does not make sense.

Thank you.


texts: Why is a hash match operator in this very basic query

texts:
I'm beginning to learn some about looking at execution plans and making queries more efficient

Consider these two basic queries

select distinct pat_id, drug_class, drug_name from rx

select pat_id, drug_class, drug_name from rx

and their execution plans

index being used:

CREATE CLUSTERED INDEX [ix_overlap] ON [dbo].[rx]
(
    [pat_id] ASC,
    [fill_date] ASC,
    [script_end_date] ASC,
    [drug_name] ASC
)

Even though the first query supposedly has the higher cost by a 4:1 margin it runs faster than the second one.  Why is it that a simple distinct added to the query will add the (what I assume to always be bad, corrections are welcome) hash match operator?  And why does it have the higher query cost relative to the second query if it runs faster.


texts: bridge table joined table have both the keys equal to both the keys

texts:
SELECT DISTINCT i0.id,
            i0.name
FROM main_table AS i0
   RIGHT OUTER JOIN "bridge_table" AS i3 ON i3."main_table_id" = i0."id"
   RIGHT OUTER JOIN "other_table" AS r4 ON i3."other_table_id" = r4."id"

I need to get records for which other_table has name equal to both "name_1" and "name_2" or name is equal to "name_3". I have tried WHERE (r4."name" = ANY (name_1, name_2)) OR (r4."name" = name_3). it is many to many relation

I know its wrong query...
Any help will be really appreciated, thanks

Update:

CREATE TABLE main_table
(
      id   serial PRIMARY KEY,
      name VARCHAR(50) UNIQUE NOT NULL
);

CREATE TABLE other_table
(
      id   serial PRIMARY KEY,
      name VARCHAR(50) UNIQUE NOT NULL
);

CREATE TABLE bridge_table
(
      other_table_id INT NOT NULL
              REFERENCES other_table,

      main_table_id  INT NOT NULL
              REFERENCES main_table,

      PRIMARY KEY (other_table_id, main_table_id)
);

INSERT INTO main_table(id, name) VALUES (1, 'Main 1');

INSERT INTO main_table(id, name) VALUES (2, 'Main 2');

INSERT INTO main_table(id, name) VALUES (3, 'Main 3');

INSERT INTO other_table(id, name) VALUES (1, 'Other 1');

INSERT INTO other_table(id, name) VALUES (2, 'Other 2');

INSERT INTO other_table(id, name) VALUES (3, 'Other 3');

INSERT INTO bridge_table(other_table_id, main_table_id) VALUES (1, 1);

INSERT INTO bridge_table(other_table_id, main_table_id) VALUES (1, 2);

INSERT INTO bridge_table(other_table_id, main_table_id) VALUES (2, 2);

INSERT INTO bridge_table(other_table_id, main_table_id) VALUES (3, 2);

SELECT mt.id     as mt_id,
      mt.name   as mt_name,
      ot_1.name as ot_1_name,
      ot_2.name as ot_2_name,
      ot_3.name as ot_3_name
FROM main_table AS mt
            INNER JOIN bridge_table AS bt_1 ON bt_1.main_table_id = mt.id
            INNER JOIN other_table AS ot_1 ON bt_1.other_table_id = ot_1.id

            INNER JOIN bridge_table AS bt_2 ON bt_2.main_table_id = mt.id
            INNER JOIN other_table AS ot_2 ON bt_2.other_table_id = ot_2.id

            INNER JOIN bridge_table AS bt_3 ON bt_3.main_table_id = mt.id
            INNER JOIN other_table AS ot_3 ON bt_3.other_table_id = ot_3.id
WHERE (ot_1.name = 'Other 1' AND ot_2.name = 'Other 2')
  OR (ot_3.name = 'Other 3')

I have created above sql to help generate required structure...


texts: How to collapse contiguous single day start/end date ranges in MySQL

texts:
(edited - I oversimplified the original problem)

I'm trying to figure out a MySQL query that will collapse one-day long contiguous start/end date ranges.  Here is my sample input data:

INSERT INTO my_table(tag, start_date, end_date) VALUES ('AA', '2020-07-01', '2020-07-02');
INSERT INTO my_table(tag, start_date, end_date) VALUES ('XB', '2020-07-02', '2020-07-03');
INSERT INTO my_table(tag, start_date, end_date) VALUES ('YC', '2020-07-03', '2020-07-04');
INSERT INTO my_table(tag, start_date, end_date) VALUES ('ZD', '2020-07-04', '2020-07-05');
INSERT INTO my_table(tag, start_date, end_date) VALUES ('0E', '2020-07-05', '2020-07-06');

INSERT INTO my_table(tag, start_date, end_date) VALUES ('CA', '2020-07-06', '2020-07-15');

INSERT INTO my_table(tag, start_date, end_date) VALUES ('CA', '2020-07-20', '2020-07-21');
INSERT INTO my_table(tag, start_date, end_date) VALUES ('CA', '2020-07-21', '2020-07-22');
INSERT INTO my_table(tag, start_date, end_date) VALUES ('CA', '2020-07-22', '2020-07-23');

INSERT INTO my_table(tag, start_date, end_date) VALUES ('EA', '2020-07-23', '2020-07-31');

Here is the output I want:

AA,2020-07-01,2020-07-06
CA,2020-07-06,2020-07-15
CA,2020-07-20,2020-07-23
EA,2020-07-23,2020-07-31

It seems like it should be simple, but I can't figure out how to do it in pure MySQL.  I've tried joining the table to itself and using various combinations of MIN() and MAX(), but the solution eludes me.  I can do it in PHP code, but would prefer to let MySQL do the work.

I can't rely on the tag field for grouping (it can be anything). The date collapse should be done only on the date ranges that are 1-day long and contiguous.  Date ranges longer than one day should not be collapsed. The selected tag should be the value associated with the first date in the range


texts:
I was executing following command

mysqladmin ext -i1 -c3600

It produces valuable output but I want to use the output to plot graphs so that I can visualize inserts, updates, buffer and other stuff.

Anyone has any idea on how to use the output to plot graphs ?


texts: Azure SQL Server outdated version - how can I upgrade it to current version?

texts:
I've connected to couple of Azure SQL Servers (those are hosting few Azure SQL Databases on them) in SSMS Object Explorer and see that version is 12.0.2000.8
select @@Version shows below:

Microsoft SQL Azure (RTM) - 12.0.2000.8   Feb 20 2021 17:51:58   Copyright (C) 2019 Microsoft Corporation

For comparison, my other box is SQL Server 2017 and version it shows is 14.0.3370.1
I expected to see Azure SQL Servers as 15.0.something  (correspond to SQL Server 2019), but they seem to correspond to SQL Server 2014 (version 12)
Questions:
Weren't Azure SQL Servers supposed to upgrade automatically to latest version ?
How can I upgrade them to version 15 manually ?


texts:
I am having some troubles configuring pgmodeler, particularly the Connections.

I put

  Connection DB: postgres

  Host/Port      localhost           5432

  User           postgres

  Passwort

Could not connect to the database. Message returned: could not connect to server: No route to host Is the server running on host "5432" (0.0.21.56) and accepting TCP/IP connections on port 5432?

I think there is something wrong with my idea. However, I just looked for the required information on the first page of pgadmin after logging into. Can someone explain me how to get the connection from pgmodeler to my postgres DB?


texts:
Our production server runs on average 4,000 transactions per second.  In the past few days the average has jumped to 175,000 transactions per second.  That is not a typo, it's 175K per sec.
Looking at the DMVs for transactions, we can't link it to user sessions directly, but we do see this:

SELECT NAME,
       COUNT(*)
FROM   sys.dm_tran_active_transactions
GROUP  BY NAME
ORDER  BY 2 DESC

--

+------------------------------+-------+
|             Name             | Count |
+------------------------------+-------+
| WorkFileGroup_fake_worktable |   627 |
| LobStorageProviderSession    |   217 |
| workfile                     |   171 |
+------------------------------+-------+

Can anyone shed light on these types of transactions?  Or am I chasing ghosts here?


texts: SQL Server Application paths to include in server backup?

texts:
I have an instance of SQL Server 2017 Standard installed on a physical server running Windows Server 2019. I'm currently working with our server administrator to configure the server backup, which he is using Symantec Backup Exec to complete. He is using the SQL Server agent to perform the SQL Server Database backups, but for the file backup I was wondering if there are any specific directories that it is best practice to include.
I'm under the impression that database and log files should be excluded from the file backup, but was wondering if there are any specific folders where SQL Server was installed to that should be included? I've tried looking this up in Microsoft's documentation and Googling it, but can only seem to find documentation regarding performing database backups.
Any advise would be appreciated
Thanks


texts: Set particular database connection temporarily to read-only

texts:
Set particular database temporarily to read-only through the command line while connecting db ?

Note : Want to prevent UPDATE/DELETE operations

Like  - < --i-am-a-dummy >, it prevents to update/delete a table without a WHERE clause, But I dont want update/delete with/without WHERE.

Yes, I can create user, however, is it possible at connection time ?

This would set it permanently, and I don't want it permanently.

/bin/mysql -uroot -ppass --execute="GRANT SELECT ON 'maindb'@.* TO 'root'@'localhost';FLUSH PRIVILEGES;"


texts:
Effectively I want to be able to group data based on a condition.

I have:

I want the data grouped using value effectively as a breakpoint.

I know how to do this with a cursor and some relatively simple logic.  However I'd rather use a set based solution.


texts: bulk-insert import fails, can't handle some forward slashes in data

texts:
Table like so:

[Hotelid] [int] NULL,
[CountryCode] [nvarchar](10) NULL,
[CountryName] [nvarchar](100) NULL,
[State] [nvarchar](100) NULL,
[CityName] [nvarchar](100) NULL,
[DisplayName] [nvarchar](100) NULL,
[Category] [int] NULL,
[StarRating] [int] NULL,
[AccomodationType] [nvarchar](250) NULL,
[Address] [nvarchar](250) NULL,
[ZipCode] [nvarchar](100) NULL,
[Area] [nvarchar](100) NULL,
[District] [nvarchar](100) NULL,
[Chain] [nvarchar](100) NULL,
[Lng] [nvarchar](100) NULL,
[Lat] [nvarchar](100) NULL,
[RoomCount] [int] NULL,
[Phone] [nvarchar](50) NULL,
[Fax] [nvarchar](50) NULL,
[Email] [nchar](200) NULL,
[Website] [nchar](200) NULL,
[IsEUCountry] [nvarchar](10) NULL

Have file to import like so:

HotelId|CountryCode|CountryName|State|CityName|DisplayName|Category|StarRating|AccomodationType|Address|ZipCode|Area|District|Chain|Lng|Lat|RoomCount|Phone|Fax|Email|WebSite|IsEUCountry

4001530|AR|Argentina||San Bernando|Hostal Del Sol||4||Chiozza 1566,7111|||||-56.6772|-36.7016||54-02257-46-1164\/3372Rva 0810-4444-678 02944-527575|54-02257-46-1164/3372|||False

BCP import command:

BULK INSERT Table FROM  'File.csv' WITH ( FIRSTROW=2, FIELDTERMINATOR = '|', ERRORFILE = 'error.txt', MAXERRORS = 1000, ROWTERMINATOR = '\n')

Error like so:

  Msg 4864, Level 16, State 1, Line 1 Bulk load data conversion error
  (type mismatch or invalid character for the specified codepage) for
  row 2, column 18 (Phone).

If I remove the forward slashes from the phone column, the row imports- it never complains about the forward slash in the Fax column, just the phone column.

How do I escape the forward slashes in the Phone column or specify the command line, and why does it not care about the forward slash in the FAX column. This is immensely frustrating...


texts: Will DB session terminate when client close DB connection

texts:
I have several questions about database connection and database sessions. I assume questions below are apply to all databases (RDBMS).

In most of the cases, one database session is on one database connection. If client close the DB connection, will the DB session terminate straight away?
If the DB session contain a row level lock created via select for update query, will closing the DB connection without commit or rollback release the lock?
What is the impact if database statements are not close?


texts: What to name a field that includes Store and Version ID

texts:
I am building a database that manages the definitions for a POS system (menu items, cashiers etc). It will include many stores and many versions.

So, typical table will look like this (MenuItems table)...

StoreID PK
VersionID PK
MenuItemID PK
MenuItemName

But I would like to merge the StoreID and VersionID into a single ID that represents the store and version (so, version 5 for store 101). So, I simplify the primary key on each table (because every table is going to need this)

And then have my tables looks like this...

SomethingID PK
MenuItemID PK
MenuItemName

Please help me name that StoreIDAndVersionID field. I have not idea what to call it.


texts: MSSQL Insert takes over 5 hours, when inserting more than specific number of rows

texts:
I have a question connected with MS SQL. When inserting less then about 1350000 rows to the table it all takes about 2 minutes, but when number of inserted rows is bigger, then a time needed to insert data grows to about 5 hours.

The problem is not connected with query or indexes, because everything has been working fine for a long time and nothing has changed in the structure of query, tables or indexes. Problem appeared for the first time about 2 weeks ago and it appears repeatedly on days, when number of inserted rows is bigger than +-1350000. For example on one day number of ins rows is 1200000 and the process takes 2 minutes, on the other day number of rows is 1450000 and it takes 5-6 hours to insert data.

I tried to rebuild indexes, but it has not helped.

:)


texts: What are some known hazards with cyclical replication between two MySQL master servers?

texts:
If I were to set up cyclical replication between a MySQL 5.6 master server and a MySQL 5.1 master server, what are some things that could potentially go wrong?

Also, the 5.6 MySQL does not have GTID enabled.

Thanks IS


texts: SQL Server, connecting without windows authentication?

texts:
I am confused about how this works:
I was reading the SQL Server documentation on authentication here:
Choose an Authentication Mode
It says:

Windows Authentication is always available and cannot be disabled

This makes me think that ALL connections to SQL server NEED windows authentication (as it cannot be disabled), but it goes on to talk about the advantages of having only SQL Server authentication, for example:

Advantages of SQL Server Authentication

Allows SQL Server to support environments with mixed operating systems, where all users are not authenticated by a Windows domain.

So, can you connect to SQL server without Windows Authentication or not?


texts: I've got a database with 3 Filegroups and each filegroups own a single file, Filegroup names are Primary, SecondFG and ThirdFG. I want to backup ThirdFG separately each time without backing up the others. I've tried it in many ways but when I want to restore database filegroup backup I get the error for other Filegroups or files are not included in the backup file! but When I backup all FileGroups together there would be no problem, please guide me how to backup and restore single filegroup, by the way I've read all BOL and I couldn't solve my problem!


texts: Track all changes to table with Extended Event Session

texts:
I am using the sql_statement_completed Event in SQL Server 2016 to track all SQL executed on a certain table.  I have a Filter on sqlserver.sql_text like %tablename%.

This captures any SQL I execute on the table EXCEPT if a Stored Procedure affects the table.

Is there a way to enhance this Extended Event Session to also track Stored Procedure INSERTs and UPDATEs to my table?

(I have also looked at Change Data Capture, but that doesn't capture the user name that executed the statement)


texts: Logshipping alert emails are being sent out while there is no error

texts:
I have logshipping setup with a Primary and a Secondary, and this has been working fine for a long time. Yesterday I started reveiving these emails:

  The log shipping primary database SERVER.DATABSE has backup threshold
  of 300 minutes and has not performed a backup log operation for 2670
  minutes. Check agent log and logshipping monitor information.

Checking the server I see backups being made, copied and restored. All in good order.

Executing execute sp_help_log_shipping_primary_database @database = 'DATABASE'
shows me the last backup date is '2012-10-16 07:00:00.673' as expected.

Things I have tried:

execute sp_refresh_log_shipping_monitor
    @agent_id = '43C13441-6892-4C1E-8435-60F4900E5994',
        @agent_type = 0,
    @database = 'DATABASE',
    @mode = 1

EXEC msdb.dbo.sp_processlogshippingmonitorprimary @mode = 1
,@primary_id = '43C13441-6892-4C1E-8435-60F4900E5994'
, @primary_server = 'SERVER'
, @monitor_server = NULL
, @monitor_server_security_mode = 1
, @primary_database = 'DATABASE'
, @backup_threshold = 30000
, @threshold_alert = 14420
, @threshold_alert_enabled = 0
, @history_retention_period = 5760

I have tried to disable the alert:

exec sp_change_log_shipping_primary_database
    @database = 'DATABASE'
    , @threshold_alert_enabled = '0'
    ,@backup_threshold = 30000

And I have disabled the SQL server agent job 'LSAlert_SERVER' which sends out these emails. Even so, emails get sent put every 10 minutes.

Is there an option I missed?


texts: How does PostgreSQL decide which query to cancel during deadlock cleanup

texts: I know that PostgreSQL will detect and clear deadlock once it happen by cancelling one of the queries. How does PostgreSQL decide which one to cancel? Is this deterministic or there is some randomness in this? Will it cancel the same query always when situation is the same?


texts:
I recently started working with SQL Server 2008 as a DBA trainee. I need calculate the size of the database but also estimate its growth over recent months and the predicted growth for the next 12 months.

I can use the sp_spaceused statement to calculate the actual size but how do I calculate everything else?


texts: Changing Auto-Growth Setting in AOAG for log file

texts:
We are on AOAG SQL Server 2014.

I am planning to change Auto-Growth setting from % --> MBs for log file.

Do I need to change the setting in all Replicas or only Primary Replica?


texts: Dynamic Pivot Table Sort by Month and year

texts:
I have used dynamic pivot query using the script below.

    select @cols = STUFF((SELECT ',' +
                        QUOTENAME(DATENAME(MONTH, CONVERT(DATETIME, createDate)))
                      FROM OSCL
    GROUP BY QUOTENAME(DATENAME(MONTH, CONVERT(DATETIME, createDate)))
     , DATEPART(MONTH, CONVERT(DATETIME, createDate))
    ORDER BY DATEPART(MONTH, CONVERT(DATETIME, createDate))
                      FOR XML PATH(''), TYPE
                     ).value('.', 'NVARCHAR(MAX)')
                        , 1, 1, '');

I am getting the following result.

Description   January   February  March April
xxxxxx           44       20        0    12

I want the following

Description   January 2017   February2017   March 2017  April 2017
xxxxxx           44             20              0          12

Could you please suggest the script for this.

Thanking you,

Hem


texts:
I come from the Relational DB world (SQL), and I'm getting started with NoSQL. So please consider that when answering.

I have two Buckets: Apps and Users.

A user may be subscribed to N applications, and every app may have M subscribers. To model that I just thought about adding a subscribed apps collection to Users like this:

{
    "id": "user:1",
    "apps": [
        "app:138",
        "app:16"
    ]
}

The issue is that I need to be able to efficiently create a list of AppsByUser (which is just user.apps) and a list of UsersByApp (which could be a view with MapReduce). If I do so, I will have a list of references to the other bucket, but no info at all, and I'll need to make one get for each "row" in the list displayed... losing all the performance gain.

How can I solve it?


texts: Are there any way to execute a query inside a string value in PostgreSQL?

texts:
I wanted to pivot a table using cross tab function which have dynamic number of category names. By digging out the web I have found a solution.

Solution -  http://www.rummandba.com/2013/03/postgresql-dynamic-crosstab-function.html

Function Used

   DROP FUNCTION crosstab_dynamic_sql_gen_fn (source_sql text, category_sql
   text, v_matrix_col_type varchar(100), v_matrix_rows_name_and_type
   varchar(100),debug bool);
   CREATE OR REPLACE FUNCTION crosstab_dynamic_sql_gen_fn (source_sql text,
   category_sql text, v_matrix_col_type varchar(100),
   v_matrix_rows_name_and_type varchar(100),debug bool default false)
   RETURNS text AS $$
   DECLARE
   v_sql text;
   curs1 refcursor;
   v_val text;
   BEGIN
   v_sql = v_matrix_rows_name_and_type;
   OPEN curs1 FOR execute category_sql;
   Loop
   FETCH curs1 INTO v_val;
   exit when v_val IS NULL;
   v_sql = v_sql ||' , "'||v_val||'" '||v_matrix_col_type;
   IF debug THEN
   RAISE NOTICE 'v_val = %',v_val;
   END IF;
   END LOOP;
   CLOSE curs1;
   v_sql := 'SELECT * from crosstab(' || chr(10) || E' \''||source_sql ||
   E'\','||chr(10) || E' \''||category_sql || E'\'' || chr(10)|| ' ) AS (' || v_sql ||')';
   IF debug THEN
   RAISE NOTICE 'v_sql = %',v_sql;
   END IF;
   RETURN v_sql;
   END;
   $$ language 'plpgsql';

Query used to get the result of the function

 select crosstab_dynamic_sql_gen_fn('select emp_id, month, qty from sales
 order by 1','select distinct month from sales','int','year text');

The result of the query is string. Can somebody tell me how to execute the query inside the string. Below is the result of query


texts: How to minimize the writes needed to update a counter field multiple times

texts:
I need to minimize the number of queries that are executed just for updating a counter field.

One of my example counter fields is raised by 1 everytime a page is visited, I was wondering what could be the best method to update it with just one occasionally query, maybe a can raise it by 100 with 1 query instead of raising it 100 times by 1.

How could I do that?

I think I need a place where to store by how much I need to raise that specific counter field, and this place should be the fastest possible, I thought about memcached but I think there will be problems with concurrent accesses to the same resource.


texts: Cannot connect to 19c with SQL Developer though database is mounted and opened, error ORA-01034

texts:
I have recently had a server crash that is hosting both an 11g and 19c databases.  Upon restart by IT staff, I discovered that one of the two 19c instances was idle. The 11g instances are all mounted, open and accepting SQL Developer connections.I was able to mount and open it using the init.ora file as an argument in the startup. I checked and the database is reporting, through SQL Plus, that it is mounted and open.  The listener has been restarted successfully.  However, trying to connect to it through SQL Developer I get ORA-01034. Any advice on what the problem is would be appreciated.
Error: ORA-01034: Oracle not available
ORA-27101: shared memory realm does not exist
01034, 0000 "Oracle not available"
SQL Developer is local and I have not tried remote sql plus as I am remoting into the Win 2012 R2 host server to run the commands. There is one listener for the database. Stopped and restarted listener and reports STATUS UNKNOWN for both instances.
Have not done anything with the environment variables. I do not have admin rights to the server.


texts: Find efficient way of migration 2TB GBK table in SybaseIQ to UTF-8 files

texts:
My project need to migrate more than 2TB raw data from SybaseIQ to another Database. SybaseIQ using GBK, the target Database using UTF-8, the target database can only load from UTF-8 files.

We use The Extract Options to extract data to GBK files first, then convert data into UTF-8, the converting is very slow because it's single processor only.

Can anyone tell me the efficient way of extract data from SybaseIQ and convert charset and encode to UTF-8?


texts: Calculation Not Displaying Proper Answer

texts:
I am sure this results from an incorrect parenthesis on my part or something silly like such, but for the life of me I can not get this syntax accurate.  Here is sample DDL

Declare @Hold Table
(
val1 float
,val2 float
,val3 float
,val4 float
,val5 float
,val6 float
,val7 float
)

 Insert Into @Hold values
 (145, 10, 20, 30, 40, 50, 60)

 Select
 (val1-(val2+val3+val4+val5+val6+(val7*.22)))/val1
 FROM @Hold

So for a breakdown of what I am after with the equation:

val1 = 145
val2+val3+val4+val5+val6 = 150
val7 = 60
val7 * .22 = 13

Which takes us to this equation:

145-(150)+13/145
8/145

My desired output is
.055172 or 6%

However my code above produces this as the answer:

-0.12551724137931

What do I need to alter in order to get my above %?


texts:
TL;DR What is the reason that in 2013 we still can't define a VIEW that displays some pivoted data with a dynamic column set.

TL;DR/2 Is pivoting data a denormalization technique?

First: Not going to assert anything. Maybe someone does dynamic pivot, but atm. it seems neither MSSQL nor Oracle nor anyone else does it. (Well, Oracle does support dynamic pivot columns with the XML option.)

Second: Obviously vendors haven't thought it important enough / not enough customer pressure. This question is exactly about the why.

Third: Dynamically building a query string that contains the right columns and then EXECUTEing it doesn't strike me as very nice.

This question more or less exactly describes the problem I think I'm facing.

  ... a main table and a child table ... I want to show the user a
  single table in a grid view which includes rows from the main table
  and rows from the child table, with the latter one being shown as
  additional columns.

There are two approaches to solving this, the classical approach with CASE WHEN and the newer approach with the PIVOT database feature.

The strange thing I have found (understood) so far is that the PIVOT approach seems to be exactly as limiting as the CASE WHEN approach, that is, if I define a VIEW that displays some pivoted data, all columns need to be hardcoded in advance.

As someone put it:

  ... by saying that I agree that pivoting data is a display function and is
  best done at the application layer or in reporting software like ...

But as far as I can grasp this, the "need" to pivot data comes from the "need" to first normalize the data in a RDBMS, that is, the "best" way to store data in an RDBMS is in a normalized form, but that isn't the "best" way to consume the data - viewing or processing the data would normally done on some "joined" / denormalized view of the data, and as far as I can tell pivoting the data is a good way of doing so for some datasets.

This leads me to the question why pivoting data isn't better supported by the "DB layer", for instance by being able to (what the Oracle PIVOT XML supports)  specify a dynamic set of columns to pivot.


texts: Does composite cluster index affect performance of seek/scan non-cluster index which is part of the composite?

texts:
I have bank transaction table with columns

Division
Brcode
TrDate
ProjectCode
Accountnumber
EffectiveDate
DrCr
Amount
EndBalance

In the other table the Brcode itself is unique and is assigned under only 1 division.

I am planing to set Division,Brcode,TrDate as cluster index(composite) and set each of them as non-cluster index individually.

What I curious is

since Brcode is unique for all division so clustering Division,Brcode,TrDate is implicitly clustering Brcode,does Brcode index get benefit from this? Does it need to search all division?
If I search by TrDate it won't get benefit from cluster index,is that right? this column shouldn't include in composite?
If I search by TrDate will cause full table scan or index scan?

ps.this table have 7 millions record so even minor factor will cause a huge different :(


texts:
I had to move our TempDB files around, since both the data and log files were originally on the C:\ drive.

I moved the data file to our E:\ drive and the log file to our F:\ drive.

This worked and I was able to get SQL Server back online, but I noticed that the F:\ drive, which has the ldf file now, also has another data file.

This made a total of two mdf files, one on the E:\ drive and the other on the F:\ drive.

I intended to have just one mdf file and log file seperated on different drives.

Why/how did SQL make the extra file?


texts: SQL Server 2012, triggers missing after restore database

texts:
I am using MS SQL Management Studio 2012 to restore the database from a .bak file.  (That file is from the backup of the database on server)

In my new database, the triggers are missing. Why did I lose the triggers?


texts: pg_dump/pg_restore: different PostgreSQL version, another database name, another tablespace

texts:
I am going to move a database from the old server to a new one.

the old one runs PostgreSQL 9.1, the new one has 9.3.
I want to change the database name.
I want the new database to be in a dedicated tablespace (the old one is in default) - including indices, etc.

Unfortunately, I have no chance to experiment (and HDDs of the old server start to fail) so I ask for the options for pg_dump/pg_restore that would work in my case.


texts: Possible Sign That A Database Is Corrupt?

texts:
I have a database that's size is 100GB.  When I run CHECKDB on it, it fails to due a lack of space for tempdb, which has over 1TB of space.  I can restart my SQL Server, flush out tempdb, the drive has 1TB of available space, and run it again, and it will use everything and fail due to a lack of space.

I suspect the database is corrupt because this seems like odd behavior, but is there a scenario where a 100GB database would use more than 1TB for running CHECKDB?

UPDATE

The table that didn't pass DBCC CHECKTABLE, I recreated its indexes and will see if it verifies faster.


texts: SQL Server randomly slow to respond to complete query

texts:
We have a SQL Server 2012 server.

Randomly a query which normally takes 0 seconds will take 30-50 to return results.

SELECT TransactionId, ItemNumber
 FROM Transactions
 WHERE CompanyId = 'MyCompany'
 AND VoucherNumber = '111111'
 AND ItemNumber = 0
 AND AccountNumber = '123-123-2105'
 AND Source = 'WEB'

Explain plan shows 0% cost, uses indexes etc. I haven't caught any locks on the table which it is running slow, server has 120G memory, 10 cores, Load is on the low side.

I am new to SQL Server (formerly Oracle) but I can't find any valid reason this randomly happens. It does happen on at least 1 other table I have gotten a complaint about.

What else can I check to see why seemingly back to back runs of this take such drastic differences?


texts:
How to revoke access for particular table from an user who have 'select any table' privilege?

I want to block access to system tables that have password details such as user$ etc..

DB : Oracle 11.2.0.4


texts: How to merge multiple SQL Server DBs to one central DB

texts: I am working on 25 identical DBs (SQL Server 2014) installed in 25 Branches. I need to merge the data of these DBs into one central DB, daily.I tested Replication (merge) method. As my servers are located in a LAN network which is not domain, I encountered a problem to setup the related agent needed for replication. How I can solve the problem?


texts: Can I fix this insertion anomaly without using a composite key?

texts:
Suppose I have a schema with the following basic elements:

Client : id (PK)

Pet : id (PK)
      owner_id (FK to Client)

Contract : id (PK)
           client_id (FK to Client)

PetInContract : pet_id (FK to Pet)
                contract_id (FK to Contract)
                PK(pet_id, contract_id)

So a client may have multiple pets and multiple contracts, and each contract may involve any number of the client's pets.

This schema has the problem that it's possible to insert pets into a contract that are not in fact the pets of the client associated with the contract.  I believe adding client_id to the primary key in the Contract table fixes this problem:

Contract : client_id (FK to Client)
           id
           PK(client_id, id)

PetInContract : pet_id (FK to Pet)
                client_id
                contract_id
                PK(pet_id, client_id, contract_id)
                FK(client_id, contract_id) references Contract

My question: is the problem fixable in a way that doesn't involve using a composite key?  (ie, what if each table in the database is required to have an identity column?)  Are there advantages/disadvantages to going one route vs the other?


texts:
I'm trying to create a backup plan which creates a full backup twice a day.
A differential backup every hour and a log backup every 5 minutes.

It works fine but I've noticed the log backup (.trn file) grows and grows in size each time.

If I use backup log with init it overwrites and I'm getting the too recent error.

I thought that after another full or differential backup the next log backup would only include the log back-ups "since the last full / diff backup"?

BACKUP DATABASE
    lagerdb
TO DISK = N'C:\SQLBACKUP\lagerdb-rechts.bak'
WITH INIT, NOUNLOAD, NAME = N'lagerdb full', NOSKIP, STATS = 20, NOFORMAT

BACKUP DATABASE
    lagerdb TO DISK = N'c:\SQLBACKUP\lagerdbd-rechts.bak'
WITH INIT, NOUNLOAD, NAME = N'lagerdb differential', NOSKIP, STATS = 20, NOFORMAT, DIFFERENTIAL

BACKUP LOG
    lagerdb
TO DISK = N'C:\SQLBACKUP\lagerdb-rechts.trn'
WITH NAME = N'lagerdb logs'

I did something with the suggestion below to backup the log to separate files with a datetime stamp:

 -- Declares a string
 declare @sql nvarchar(255)
 declare @ParmDefinition NVARCHAR(500)

 -- declares the path name. Seperated by disk drive and directory.
 declare @bkName varchar(255)

 -- Declares the filename.
 declare @bkdbName varchar(50)
 declare @bkdbDateTime varchar(20)

 --Get Timestring
 set @sql = N'select @parm1OUT = CONVERT(varchar(20),CONVERT(DATE,GETDATE(),104),104)     + ''_'' + Substring(CONVERT(varchar(8),getdate(),114),1,2) + Substring(CONVERT(varchar(8),getdate(),114),4,2)'
 SET @ParmDefinition=N'@parm1OUT varchar(20) OUTPUT'

 exec sp_executesql
 @sql,
 @ParmDefinition,
 @parm1OUT=@bkdbDateTime OUTPUT

 set @bkdbName = 'LagerDB_' + @bkdbDateTime

 -- Enter the Drive letter below. Please remove C: as current value.
 set @bkName = 'C:'

 -- Enter the folder name below (with lead and end '\' back slashes.
 -- Ensure that you change the folder from Temp below.
 set @bkName = @bkName + '\SQLBackup\'
 set @bkName = @bkName + @bkdbName

 -- Sets the backup running by executing the @sql string.
 set @sql = 'BACKUP DATABASE lagerdb TO DISK = '''+@bkName+'.bak'''

 print @sql
 exec sp_executesql @sql

(e.g.: LagerDB_06.12.2011_1418.bak)

But the answer I was really looking for is that I thought it would reset after a full backup. Maybe I should schedule a backup with init after a full or differential backup?


texts: Detecting Logical Replication Error on Postgres v10 on AWS RDS CloudWatch metrics

texts:
https://www.postgresql.org/docs/10/logical-replication-restrictions.html
States:
"When the schema is changed on the publisher and replicated data starts arriving at the subscriber but does not fit into the table schema, replication will error until the schema is updated"

How do we detect/query for these errors on Postgres?

I tried

SELECT * FROM pg_stat_subscription;

but it shows that it received the publisher's message. It errored out when I changed publisher's schema and subcriber stopped getting updates.

on AWS,  the RDS cloudwatch metrics related to it is:

OldestReplicationSlotLag (The lagging size of the replica lagging the most in terms of WAL data received. Applies to PostgreSQL)

but I don't think this is the right metric if I want to know if my updates have corrected applied to the subscriber replica database.


texts: Partition SQL Server Table by Year, 35 days rolling period

texts:
We are trying to partition SQL Server tables by year (2015, 2016, 2017, 2018) and we want to create one more partition which hold current 35 days period data always.

Partitions: Part_2015, Part_2016, Part_2017, Part_2018, Part_35Day

Create Partition Function RangePartFunction (datetime2)
as Range Right For Values ('20150101', '20160101', '20170101')

I'm struggling how do we create for 2018 and 35 day rolling period

Can anyone please advise...


texts: Separate login for user / administrators using mongodb

texts:
I have 2 separate groups of users on my website. One is user and the other one is admin.  Currently I put them in 2 different collections user and admin and login respectively.  My questions is:

Mongodb generates _id automatically for both user and the admin collection.  Since they're in 2 different collections chances are one day you created one user and one admin with the same _id right?  So it seems the only way is to check all documents in both dbs and make sure your newly created user (or admin) does not have repeated _id right?  Is there something I can do natively in mongodb to make this happen without checking?
I can also put all users and admins in one collection.  This way I don't have a problem but I am not sure if I should do this.

p.s: because I have a table keeping track of user / admin balances so I certainly don't want the _id to repeat
p.s2: the db is taking longer and longer to query.  Also my ORM creates user instances and generated _id automatically for me.  I am not sure if I should just generate an _id and create it this way...

Any comments are greatly appreciated.  Thanks!!


texts: What could cause a record to appear in the MySQL binary log but not the original table?

texts:
I have a web application (CiviCRM) that has the option to enable binary logging, which I've done.  We use this software to record donations, including automatic recurring donations.  Our payment processor sends a POST request to a particular URL, which SHOULD create a new donation record in the db.  Instead, it's creating a record in the log table with log_action of Insert, but there's no corresponding record in the original table - and no Delete record in the log.

What circumstances might cause this?  Where should I begin troubleshooting?  There's no clustering/relaying - there are a handful of triggers that act on this record, which I'm eyeing suspiciously, but would appreciate input.

MySQL version is 5.5.44-0+deb7u1.


texts:
I am new to linux and mongodb.
I've already installed mongodb on ubuntu but I want to run one more instance on different port.
As I understood from other questions and forums , there must be upstart script.
https://stackoverflow.com/questions/7300109/ubuntu-start-upstart-second-instance-of-mongodb

But I can not find or locate an upstart script on my server.If I create a upstart script , where should I locate it to start as a service.

or is there any other proper and easy way to start another instance of mongodb on the same server but different port.


texts: PostgreSQL for user management and another database for file management (Blob storage)

texts:
I am working on a project with video hosting and online editing platform with a microservice architecture in mind.
One of my teammates uses PostgreSQL SQL for user management, but I need to handle large files, and we are going to use Azure blob storage.
I would like to know, should I create another database for file management, and if yes, what should it be? (I have MongoDB in mind)


texts: How to search records based on date range for other matching data

texts:
I have below records in table

APP_No Stage Start_Date End_Date
A123   L1    01-01-19 02-01-19
A123   D1    01-03-19 05-03-19
A345   L1    01-01-19 02-01-19
A345   D1    01-03-19 05-03-19

I want to search records for stage D1 for the month of Mar19 but this should include data for L1 stage also, since related app ID fall between same Mar19 date range.
I have used condition in where clause as : trunc(Col_Name) BETWEEN trunc (sysdate, 'mm')/current month/ AND SYSDATE

Please help with solution


texts: Datetime Difference in MySQL when format is Varchar

texts:
I have two columns in a table: STOP and START, of type varchar.

I want to have the difference between both columns, and I'm using :

SELECT DATEDIFF(stop, start) AS difference from T1;

It is giving me null, because of different data types.

To test, I also tried:

SELECT STR_TO_DATE('5/16/2011 20:14 PM', '%c %e %Y %H:%i:%s');

It is also giving null.

What should I do for the difference? I have n number of data and time in different formats.


texts: Convert a numeric column with number eg.(-1222222) to two float column with two decimal places eg (-12222.22)

texts:
I know there are different variations of this question asked.
But what I am trying to do is that, there is a table with a numeric column (12,0)
that has data looking like

Units
-----
-1222222
0
-19
21123021

What they really mean is this

Units
-----
-12222.22
0.00
-0.19
211230.21

With two decimal places in the last digits of each number.

I am struggling to convert these numbers to a two decimal place float column(Units)


texts: SQLServer add a default value for a column

texts:
I am using MS SQL Server. I have an "action" table with an "action_type" column that I want to set a default value of "to do" for. My DDL in MS SQL Studio errors! I have read many many articles trying to work out what I am doing wrong but nothing seems to work. The DDL is as follows, the table CREATE works, and the ALTER fails.

CREATE TABLE "action"
( "action" INT IDENTITY(1,1)
, "name" VARCHAR(250) NOT NULL
, "owner" VARCHAR(50)
, "action_type" VARCHAR(50) NOT NULL
, "due_date" DATETIME
, "done_date" DATETIME
, "successful" BIT NOT NULL
, "job" INT
, "contract" INT
, "person" INT
, "description" VARCHAR(MAX)
, "deliverable" VARCHAR(MAX)
, "outcome" VARCHAR(MAX)
, "source" VARCHAR(50)
, "notes" VARCHAR(MAX)
);
ALTER TABLE "action"
ADD CONSTRAINT "df_action_0" DEFAULT "To Do" FOR "action_type";

The ALTER statement returns error

  Msg 128, Level 15, State 1, Line 4 The name "To Do" is not permitted
  in this context. Valid expressions are constants, constant
  expressions, and (in some contexts) variables. Column names are not
  permitted.

Based on various articles I have tried replacing "To Do" with 'To Do', N'To Do', ("To Do") and others, nothing works.


texts: Improving sql query execution time for 3 billion rows table

texts:
I have a 2+ billion row mysql table. I am also writing 1 million rows each day. The table contains some large columns like varchar(255) (to hold long urls)
In order to perform analytics on this table I created 5 specific indices which really speed up the execution time. (From 25+ minutes to 2 minutes for some queries).
The problem still persists that 2 minutes is a lot of time for just one query. I would like to run multiple queries for analytics and reporting.
Also, this table is increasing rapidly on a daily basis and I am pretty sure the indexes are as optimised as they can be.
Is this the point where clustering would solve my issues? i.e. is it unusual that a table this size still runs on a single sql node?
or is it still possible to run queries in such large table in milliseconds?
An example query of mine is:
SELECT name, url, SUM(visits), AVG(price), AVG(loc) FROM mytable
    WHERE sname IN ('white') AND usage IN ('three') AND date BETWEEN '2001-01-01' AND '2003-03-10'
    GROUP BY name, url ORDER BY SUM(visits);

I am new to clustering and HPC in general any advice on what I should do here is appreciated.


texts:
I need to remove the last character of my subquery but I cannot figure out how to reference my subquery in the subselect as I need the length of it.
The subselect collects all rows and converts them into a single string by appending them with a separator ";".
This is what I need:
(SUBSTRING(
    (SELECT
                    d1.name +'; '
    FROM
                    data1 d1
                    inner join data2 d2
                    on d1.id = d2.id
    WHERE
                    d1.id = dOfOutterSelect.id
    ORDER BY
                    d1.CreatedOn FOR XML PATH('')

    ),
LENGTH(),1))

As "Row name",


texts: Need to update a column in table from Form , if there is no value in the column before ,

texts:
I am new comer to Access Database. I have a table named Employees with the columns named:

Badge No
Name
Position
Salary
Address
Status

I create a form , in which i am using DLookup for to get the required data from table, but the problem is the Status Box.

I want that the status box should be like that, if there is any data in status column in table for the concerned Badge No, then return to me that otherwise I want to update that from Form.

For example, if an Employee is on Vacation it show me the "Vacation", otherwise  I want to Put here "On vacation" and then I want to save it in "Status" Column in "Employees" Table

Can anybody help me please,

Thanks in Advance.


texts: psql, getting a list of databases to be parsed by a script

texts:
I want to use psql to list all of the databases on a Postgres server, to be parsed by a script. This command lists them:

psql -l -A -t

but the output shows an obvious issue: the records are separated by newlines, but also contain newlines.

$ psql -l -A -t
postgres|postgres|UTF8|en_CA.UTF-8|en_CA.UTF-8|
template0|postgres|UTF8|en_CA.UTF-8|en_CA.UTF-8|=c/postgres
postgres=CTc/postgres
template1|postgres|UTF8|en_CA.UTF-8|en_CA.UTF-8|=c/postgres
postgres=CTc/postgres

Using the -R option I can change the record separator, but it seems like no matter what I change it to, there's the risk of that string appearing in the data. Is it possible to instead tell psql to replace the newlines in the data with something else? (and then what if that string also appears in the data?)

I'd also tried to set the record separator to a null character with such sequences as -R '\000' and -R "\0", but it doesn't seem to interpret escape sequences in the parameter at all, and just uses the literal string \000 instead.

The other option I know of to list all databases is:

psql --quiet --no-align --tuples-only --dbname=postgres --username=postgres --host=127.0.0.1 --port=5432 --command="SELECT datname FROM pg_database"

but that requires me to give the password for the postgres user, so it's not desirable. Perhaps there's another way to get a list of the names of all databases?


texts: Redshift: Table columns can be found in information_schema.columns but not in pg_catalog.pg_table_def

texts:
I create a table in Redshift. When I tried to search for the table definition, I get back results from information_schema.columns by running the following query:

select * from information_schema.columns
where table_name = 'table' and table_schema='schema'

However, when I run a query against pg_catalog.pg_table_def, I don't get back any result.

select * from pg_catalog.pg_table_def
where tablename = 'table' and  schemaname = 'schema'

Could anyone help me understand why this is happening? The table is created and owned by the account I'm using.


texts: Checking IFI enabled on SQL server below 2016

texts:
I am using in one of my reports to check for status of IFI being enabled on SQL server.

It works well when using dmv sys.dm_server_services for SQL2016

However, in SQL2014 and SQL2012 i see difficulties in rendering this check via SQL query.

For example if i use below

EXEC sys.xp_readerrorlog 0, 1, N'Database Instant File Initialization';

There is no guarantee it will show status on file '0' and sometimes i keep querying the error log manually to find that out. How can i achieve the IFI check using a better way so that it can show up on my SSRS report


texts: Database corrupt after restore and upgrade on another instance

texts:
We have SQL Server 2008 R2 (10.50.2789) and SQL Server 2014 (12.0.2000) instances. There is a process of backup on SQL Server 2008 R2 and restore to SQL Server 2014.

Everything was going fine, until today. The database gets correctly backed up (without errors or warnings), restored, and upgraded. DBCC CHECKDB on the source is ok, but DBCC CHECKDB fails on the target just after the restore.

I would raise a ticket to Microsoft, if it wasn't the most confidential db in the house ;)

Details:

Both machines are virtual boxes; source is on a decent traditional SAN, target resides on our full flash array beast. Both are serving other servers with no consistency errors. The backup is saved locally as with checksum xxxxxx.tmp, then transferred over network and when the transfer ends without error, file is renamed to \xxxxxx.bak. This file gets restored on the target server.
Yes, a fresh backup helped, it was our first try. But why the corrupted one can restore on SQL Server 2008 R2 without corruption, but not on 2014?
'Exact' errors were couple of following in two tables"

  Object ID .., index ID 1, partition ID .., alloc unit ID ..(type In-row data). Page (1:..) is missing a reference from previous page (1:..). Possible chain linkage problem.

  Object ID .., index ID 8, partition ID .., alloc unit ID ..(type In-row data), page (1:1116376). Test (IS_OFF (BUF_IOERR, pBUF->bstat)) failed. Values are 2057 and -4.`

Bad sectors are probably not the cause. The data resides on a reliable SAN with lots of other systems which aren't complaining about data inconsistency. If 2014 was on sick storage, why would the fresh one restore smoothly?

It is a regular daily backup/restore process, this happens just once in a 'long period'.


texts: Postgres, trigger function, is it bad for performance?

texts:
I have added the following trigger function to one of my tables in my Postgres database.
CREATE OR REPLACE FUNCTION trigger_set_timestamp()
RETURNS TRIGGER AS $$
BEGIN
  NEW.updated_at = NOW();
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

I am new to this, so, I am wondering, is this going to slow down my database? Should I avoid trigger functions for performance reasons and if so, what's the alternative?
(In this case this is the only way I knew how to make the updated_at date column always set the date when any of the columns in the table would change....)


texts: Clarify unique constraint and index, cannot grasp the concepts to 100%

texts:
When reading about UNIQUE, I understand the concept but when it comes to the practical part of it, I get confused and I'm unsure of what it means.

What I do understand, is that there are two ways to make an attribute UNIQUE, through an index or a unqiue constraint (except Primary key of course).
The only difference seems to be that the constraint has the additional "unique key".
So here are my questions;

A unique key is created for a constraint but not for a unique index, what’s the point with the key and without?

Can’t drop constraint before dropping index? Why, what’s the point? Is it like this for “additional security”?

What’s the relationship between and UQ key and UQ index? How they are related?

Is it more ”secure” to choose a constraint, if you really want an attribute to stay Unique because you can’t disable a constraint? (Because there is less room for mistakes like disabling the uniqueness of an attribute because you can’t disable a constraint)

I already know that you can use a constraint as an index.


texts:
I'm trying to get my head around postgres roles.

I want to create a database and have the owner role have all privileges on the database.

I then want another role which cannot create tables but able to read/write from any tables that may be added to the db in the future.

I have done REVOKE ALL ON schema public FROM public and then GRANT ALL ON schema public TO admin_role and the admin role works.

But I can't figure out how to create a limited role with the create table permission revoked and it still able to see and read to tables within the database. If I do the GRANT ALL ON schema public it has all privileges which I don't want.

Any help much appreciated.


texts: SSIS ASCII requirement: "Code page is 1252 and is required to be 20127"

texts:
I have a requirement to export a database to a tab-delimited file in the ASCII format. I am using derived columns to convert any Unicode strings to non-Unicode strings. For example, a former Unicode text stream is now casted as this:

    (DT_TEXT,20127)incomingMessage

But SSIS is still looking for ANSI. I am still seeing an error at the Flat File Destination:

    The code page on input column <column_name> is 1252 and is required to be 20127.

This is what I have been doing to ensure ASCII is used:

In the Flat File Connection Manager, used Code page "20127 (US-ASCII)"
Used a Derived Column to cast data types
In the OLE DB source, set the default code page to 20127.

Any thoughts?


texts: Postgres pg_dump restore performance. Is using pg_dump to back up large databases an acceptable solution

texts:
Folks,
I am considering moving from SQL Server to Postgres. The per core licensing of SQL Server is too expensive for my needs.

My one concern is restoring Postgres from a dump. I believe that the pg_dump creates a file of SQL commands to build back up the DB and that this can be quite slow.

I am used to restoring large SQL Server databases in quite a short period of time.

Can anyone advise me here on what folks do for performant disaster recover solutions?

I am installing postgres on windows, so barman etc are probably not an option and without some backup software I would be concerned about doing WAL point in time restores manually. I believe you need to shut down the server and restore everything and that seems severe.


texts: How to query a table efficiently without affecting live transactions

texts:
I have a high transaction table with millions of records with the structure below:

transaction
--------------
id             int
txn_status     varchar
amount         bigint
name           varchar
txn_time       datetime --Date and time of the transaction

The txn_status field value can be completed or Pending. I need to run a query that will retrieve the number of records with the following characteristics:

Transactions with Pending txn_status since the last 15 minutes i.e (current_time - txn_time) <= 15 minutes
Transactions with Pending txn_status between the last 16 and 25 minutes i.e (current_time - txn_time) >= 16 minutes and (current_time - txn_time) <= 25 minutes
Transactions with Pending txn_status between the last 26 and 30 minutes i.e (current_time - txn_time) >= 26 minutes and (current_time - txn_time) <= 30 minutes
Transactions with Pending txn_status more than 30 minutes i.e (current_time - txn_time) > 30 minutes

There are two solutions currently in my head currently:

Query the production database every minute using a case statement (the case statement will filter and group the records based on the time difference above) in the where clause of the query.
To avoid putting much load on the production database, have a separate database with a similar table and have an update and insert triggers update the table and execute the query in No. 1 above afterwards on the database.

Please if there is any optimal or better solution in achieving this without putting much load on the database, please share.


texts:
We have a 2012 SQL Server and I am looking into how it is being backed up currently. We do have a shadow copy that runs hourly. We also have a nightly full backup that is taken. DB runs in FULL mode.

My issue is my log files seem to be growing very large quickly. I have read that log backups as often as every 5 minutes. I know that shrinking the log files often is not a good solution. I want to make sure that we have full recovery options.

If I take a nightly backup, then take a log backup right after will that leave a break in consistency if there happens to be transactions going on at that time?

Taking a log backup should free up space in the log to prevent file growth, leaving a file large enough for a days activities is ok. Or should I be taking log backups more often?

New to the SQL Server world and want to learn all the best practices I can. I am also just trying to ensure we are doing everything we can to safeguard our backups. Thanks.


texts: When should you split a database into 2-3 databases for performance reasons?

texts:
I'm not an expert on database performance. A colleague has suggested splitting a BI data warehouse database into several databases based on functional business unit - I believe still on one machine though I'm not sure - for performance reasons - there are many processes, procedures, logging, and monitoring happening. This has nothing to do with security or permissions - in fact those will be made more complicated potentially.

Again I can't say I'm the expert but for some reason, it doesn't seem logical to me. DB in total is about 500 GB and we have about 200 tables.
Naturally it seems like most solutions from my cursory research recommend improving the disc drive speed (like SSD) or RAM (currently an anemic 4 GB) for performance reasons. Or possibly keeping one logical database, but fragmenting the schemas into 2-3 different machines.

But I don't know. Is there a significant use case or performance reason to split one database into 2 logical databases - other than user permissions/ security which aren't a factor here. There will be many views that span across these 3-4 databases.

Do read operations, normalization, tuning options, or database/table locks come into play here? My instincts tell me no - those are entirely separate issues, but I can certainly be wrong. I'm just concerned that we are going to trade minimal, if not zero, performance improvements for a whole lot of added complexity and maintenance.

I obviously am coming in here already leaning/ biased at one side, but wondering your thoughts.


texts: How do I calculate the average weight of riders by competition?

texts:
I have an assignment where I need to calculate the average Weight of Riders by Competition. The assignment has a lot of different parts but I got stuck on this one so please help my out if you can.

I have the following tables:

CREATE TABLE Rider(
    name VARCHAR(30),
    weight REAL,
    PRIMARY KEY(name)
) ENGINE=innodb;

CREATE TABLE Participants(
    competition VARCHAR(30) NOT NULL,
    rider VARCHAR(30) NOT NULL,
    PRIMARY KEY(competition, rider),
    FOREIGN KEY(competition) REFERENCES Competition(name),
    FOREIGN KEY(rider) REFERENCES Rider(name)
) ENGINE=innodb;

CREATE TABLE Competition(
    name VARCHAR(30),
    PRIMARY KEY(name)
) ENGINE=innodb;

With the following data:

--Riders
Name : Weight
----   ------
Alan : 55
David : 60
Peter : 65
Stan : 60

--Competition
Name
-----
Derby
York

--Participants
Competition : Rider
-----------   -----
Derby : Alan
Derby : David
York : Alan
York : Peter
York : Stan

So the results I should get from my SQL query for the different competitions should be:

Derby: 57,5 (average of Alan and David)
York: 60 (average of Alan, Peter and Stan)

I've tried multiple queries. At the moment my best is:

SELECT Competition.name, AVG(Rider.weight)
FROM Rider, Compeition, Participants
GROUP BY Competition.name;

How should I tackle this SQL query?


texts: virtual MySql server with mounted RAID array

texts:
I have a Ubuntu 16.04 server with an 18TB RAID array.  I am running virtual servers on the server using Wok/Kimchi and am wondering if it is reasonable to run a MySQL virtual server and set the data directory to run on the RAID that is mounted by sshfs or samba?  Alternatively, should I just make a large virtual machine instance and keep the data in the default /var/lib/mysql location and then do backups of the database to the RAID array?  I would think it is best to keep the data in a "real" location on the RAID but perhaps if that RAID is unmounted for some reason my database will be corrupted.

For testing I tried setting the data directory to the mounted RAID and ran into all sorts of ownership issues despite several different ways of doing this that I found online.  The one that sort of works is to mount the RAID using sshfs using solution 2 from here, but that is maybe not best from a security perspective:  https://unix.stackexchange.com/questions/326883/owner-of-sshfs-mounted-directory-with-777-permission-cant-open-it-no-acl-no-s

I then followed this guide to mv /var/lib/mysql to the mounted RAID array:  http://www.vacilando.org/article/moving-mysql-data-files-virtualbox-shared-folder.  It threw file permission/ownership errors but appears to have moved the folder and mysql is running and I can create databases.  I could never get anything to work with a samba mount because of ownership issues.  Also, trying to rsync the /var/lib/mysql directory to the RAID never worked and I had to use mv despite it also giving ownership errors with the sshfs mount.

I am quite new to this and maybe overthinking something here, which is why maybe I should make the large (a few TB should be plenty) storage allocation for a virtual machine and let mysql store its data in the default /var/lib/mysql directory.


texts: Database cannot be opened. It is in the middle of a restore

texts:
I have executed these two commands:

USE master
GO

RESTORE DATABASE test
FROM Kingston
WITH NORECOVERY
GO

USE master
GO
RESTORE LOG test
FROM Kingston
WITH STOPAT = '2020-05-13 14:05:25', RECOVERY;
GO

But now I get this error while accessing the database with USE test GO:

  Database 'test' cannot be opened. It is in the middle of a restore.

When it clearly isn't. Any suggestions how to fix it? So far found out that RECOVERY helps, but as you can see it doesn't.

Edit

Changing NORECOVERY to RECOVERY doesn't help, because then I'm unable to execute 2nd command (throws error), and I need that log at specific moment.

Edit

I have found the solution. You need additional command:

RESTORE DATABASE test
WITH RECOVERY
GO

It appears that RECOVERY is ignored in RESTORE LOG command, and it needs additional command to confirm that restoring has been finished. I appreciate the help though.


texts: How do I get data from multiple tables if the agency_code doesn't match

texts:
I have a table with these fields

Employment_Form (table name)
employee_number
full_name
agency_code

The second table is

HumanResources(table name)
employee_number
full_name
agency_code

The assignment is to find the records that have a typo for agency_code on the Employment_Form table.
The data in the HumanResources table is said to be 100% correct.

I am not sure how to best approach this. I am open to suggestions.


texts: How to split a row based on a delimiter into multiple rows and retain the ID and ordering by using a single SQL statement for MySQL

texts:
Scenario:

A MySQL table
A Delimiter (e.g. comma) is used to separate entities inside a string store in a column of the MySQL table
MySQL table contain 2 columns - ID and Countries_Involved

=======================================
| ID |   Countries_Involved           |
=======================================
| 1  | Japan, India, France           |
| 2  | Benin, Chad, Chile, Iraq, Iran |
=======================================

How do I use a single SQL statement (very good to have, if possible) to generate a table below:

====================================
| SN | ID | Country    |  Ordering |
====================================
| 1  | 1  | Japan      | 1         |
| 2  | 1  | India      | 2         |
| 3  | 1  | France     | 3         |
| 4  | 2  | Benin      | 1         |
| 5  | 2  | Chad       | 2         |
| 6  | 2  | Chile      | 3         |
| 7  | 2  | Iraq       | 4         |
| 8  | 2  | Iran       | 5         |
====================================


texts:
I was studying about how and how much memory allocated to each connection thread and found below MySQL tutorial:

http://dev.mysql.com/doc/refman/5.1/en/memory-use.html

There is a point mentioned as below which I am unable to understand.

When a thread is no longer needed, the memory allocated to it is
released and returned to the system unless the thread goes back into
the thread cache. In that case, the memory remains allocated.

My concerns are the following:

Does it mean thread occupied the memory even system is using threads from cache?
A cache in thread still use memory?


texts: Script fails to run as SQL Server Agent Job, completes successfully when run from SSMS

texts:
I have two T-SQL scripts that are scheduled to run once every day. Both do cleanup of some old data from different tables. Both scripts are run as the same user, on the same database.

Script A will execute successfully both in SQL Server Management Studio and when launched as a job, but Script B will only execute successfully in SQL Server Management Studio, even when logged in as the same user as is set up to run the script under SQL Server Agent.

Script B fails with the following error:

  Executed as user [username].
  DELETE failed because the following SET options have incorrect settings:   'QUOTED_IDENTIFIER'.

  Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or filtered indexes and/or query notifications and/or XML data type methods and/or spatial index operations.

  [SQLSTATE 42000] (Error 1934). The step failed.

I do have a filtered index on one of the tables I'm trying to clean, but why does it then work through SSMS?


texts: Want to order on two columns but not the usual way

texts:
I have a table as below

ID  userID  Date
1   2273    22/08/2011
2   2274    24/08/2011
3   2275    26/08/2011
4   2273    26/08/2011
5   2273    26/08/2011
6   2271    26/08/2011

And want result as below.  Need mysql query help to acheive this. Basically i want to order by date but same user must all be together. And i am trying to achieve this in single query.

ID  userID  Date
1   2273    22/08/2011
4   2273    26/08/2011
5   2273    26/08/2011
2   2274    24/08/2011
3   2275    26/08/2011
6   2271    26/08/2011

I tries select * from mytable order by userId,Date and will result in following

ID  userID  Date
6   2271    26/08/2011
1   2273    22/08/2011
4   2273    26/08/2011
5   2273    26/08/2011
2   2274    24/08/2011
3   2275    26/08/2011

Which is not i want i want user with date ascending should come first and same user records in table should follow his first record...


texts:
I'm trying to uninstall ms sql server 2012. But when I click Microsoft SQL Server 2012(64 bit) and choose remove option it shows like this.

Then how can I remove the MS SQL Server 2012? Please help.


texts: I'm investigating intermittent performance issues on a database running MariaDB 10.4 and I notice extraordinarily high network traffic.  Bytes_sent is about 20TB after running for 58 hours (379GB per hour).  There are only 8 clients, and typical traffic is expected to be about 2GB per hour.  Is there some way to monitor bytes_sent per client to help track down the issue?


texts: trigger or check constraint insert values based upon existence of others

texts:
For simplicity (and since it captures the essence of my problem), suppose I have a table with data.

CREATE TABLE my_practice_table
(

my_id       NUMBER(12),

field_2     VARCHAR2(20),

field_3     VARCHAR2(20)

);

I want to define a new constraint on the table that has the following logic:

For any new record x inserted into my_practice_table,

if (x.field_2 is null)

   if [there exists a record y in the table such that

   i.) y.my_id == x.my_id

   AND

   ii.) y.field_2 is not null
    ]

       --do not insert x into the table

   else
      --there exists no such y in the table, so we insert x

else (x.field_2 is not null)

 if [there exists a record y in the table such that
    i.) y.my_id == x.my_id
    AND
    ii.) y.field_2 is null
   ]
     --do not insert x into the table
else
     --there exists no such y in the table, so we insert x

Example: Suppose my_practice_table had the following data
    table1: [1    'hi'     'bye' ]
            [2    'hello'   'adios']
            [1    'hi'      'seeya']

The statement:
INSERT INTO table1
(my_id, field_2, field_3)
VALUES
(1, null, 'ciao');

won't work because there is already a record in table1 with my_id equal to 1 and field_2 not equal to null.

Example: Suppose my_practice_table had the following data:
    table2: [1    'hi'     'bye' ]
            [2    null   'adios']
            [1    'hi'      'seeya']

The statement:
INSERT INTO table2
(my_id, field_2, field_3)
VALUES
(2, 'hola', 'this won't work');

won't work because there is already a record in table2 with my_id equal to 2 and field_2 equal to null.

My question is what is the way to go about this in Oracle syntax. The algorithm is simple enough, but I'm just not comfortable enough with Oracle syntax to get the job done.
Do I use a BEFORE INSERT Trigger? How do I scan for values in other fields of a particular row once I found a match on the field my_id? Is there a 'break' statement in Oracle that I can do to speed this up?
Any help or guidance to the right place to look for a possible solution would be appreciated. Thanks


texts: Multiple databases, in multiple geographic locations while preserving PostgreSQL triggers and data consistency

texts:
I've read a ton over 300 pages of db setups in the last day but I feel a little overwhelmed with all of the potential options and lack of documentation on how PostgreSQL triggers function across replicated databases.

Thus I'm reaching out to the world experience pool before I set db presence. If I've completely screwed this up I'm totally open to new ideas.  Thanks!

Refer to http://i.stack.imgur.com/HiewA.png while I try to explain what I'm trying to accomplish.
All of my on-site client locations and offsite db's are running PostgreSQL 9.0.5.
Arrows are all internet connections.

I have "location terminals" (red boxes) connecting to each database for their location.   Our database programming is pretty intense as we have lots of triggers that push data across sockets to all the connected terminals (red boxes).

I would like to be able to replicate (as close to real time as possible) all this data from our "client locations" to our "offsite DB".  Each "client location" would have it's own database on our main server.

There are times when "remote users" (orange boxes) need to connect to my "offsite db" to access/change data.  Changes that are taking place on "offsite db" can start database triggers that need to be reflected back to the "client location DB".  Triggers occurring n the "client DB" need to be pushed to these remote users as well.

Keeping all the data between the remote db and the site db consistent is important - keeping calculated fields like unique id's and primary keys the same on each database is extremely important for optimization/performance.

I would also like to have some kind of streaming replication running on the offsite database to have a backup in-case anything goes down.  Having HA isn't critical but if it's easy to implement I'd like to do that as well.

I'm trying to do this with the least amount of 'moving parts' to keep maintenance support costs as low as possible.

Thanks!


texts: Identify co-related record between two columns with in the group


texts: Find the first gap in an aggregation of integers in SQL Server

texts:
Let's say I have a table called dbo.GroupAssignment.

GroupID | Rank
------------------
1    1
1    2
1    3
2    1
2    3
2    4
3    2
3    4
3    5

The PK is GroupID, Rank. Normally, the ranks within a group are a contiguous sequence of integers starting from 1. But it's possible for a GroupAssignment to get removed, leaving a gap. When a new assignment is made for the group, I want to fill the first gap available.

So, how could I calculate this in SQL Server?


texts: Optimise a LATERAL JOIN query on a big table

texts:
I'm using Postgres 9.5. I have a table that records page hits from several web sites. This table contains about 32 million rows spanning from Jan 1, 2016 to June 30, 2016.

CREATE TABLE event_pg (
   timestamp_        timestamp without time zone NOT NULL,
   person_id         character(24),
   location_host     varchar(256),
   location_path     varchar(256),
   location_query    varchar(256),
   location_fragment varchar(256)
);

I'm trying to tune a query that counts the number of people that performed a given sequence of page hits. The query is meant to answer questions like "how many people viewed the home-page, and then went to the help site and then viewed the thank-you page"? The result looks like this

╔════════════╦════════════╦═════════════╗
║  home-page ║ help site  ║ thankyou    ║
╠════════════╬════════════╬═════════════╣
║ 10000      ║ 9800       ║1500         ║
╚════════════╩════════════╩═════════════╝

Notice the numbers are decreasing which makes sense, because of the 10000 who viewed the home-page 9800 went on to the help site and of those 1500 went on to hit the thank you page.

The SQL for a 3 step sequence uses lateral joins as follows:

SELECT
  sum(view_homepage) AS view_homepage,
  sum(use_help) AS use_help,
  sum(thank_you) AS thank_you
FROM (
  -- Get the first time each user viewed the homepage.
  SELECT X.person_id,
    1 AS view_homepage,
    min(timestamp_) AS view_homepage_time
  FROM event_pg X
  WHERE X.timestamp_ between '2016-04-23 00:00:00.0' and timestamp '2016-04-30 23:59:59.999'
  AND X.location_host like '2015.testonline.ca'
  GROUP BY X.person_id
) e1
LEFT JOIN LATERAL (
  SELECT
    Y.person_id,
    1 AS use_help,
    timestamp_ AS use_help_time
  FROM event_pg Y
  WHERE
    Y.person_id = e1.person_id AND
    location_host = 'helpcentre.testonline.ca' AND
    timestamp_ BETWEEN view_homepage_time AND timestamp '2016-04-30 23:59:59.999'
  ORDER BY timestamp_
  LIMIT 1
) e2 ON true
LEFT JOIN LATERAL (
  SELECT
    1 AS thank_you,
    timestamp_ AS thank_you_time
  FROM event_pg Z
  WHERE Z.person_id = e2.person_id AND
    location_fragment =  '/file/thank-you' AND
    timestamp_ BETWEEN use_help_time AND timestamp '2016-04-30 23:59:59.999'
  ORDER BY timestamp_
  LIMIT 1
) e3 ON true;

I have an index on timestamp_, person_id and the location columns.
Queries on date ranges of a few days or weeks are very fast (1s to 10s). Where it gets slow is when I try to run the query for everything between Jan 1st & July 30. It takes over a minute. If you compare the two explains below you can see it no longer uses the timestamp_ index and instead does a Seq Scan because the index wouldn't buy us anything since we're querying "all time" hence pretty much all the records in the table.

Explain for a few days date range (uses index):
https://explain.depesz.com/s/2tOi
Explain for a 6 months (seq scan):
https://explain.depesz.com/s/c0yq

Now I realise the nested loop nature of the lateral join is going to slow down the more records it has to loop through but is there any way I can speed up this query for huge date ranges so that it scales better?


texts: Multi-Instance Failover Cluster SQL Cluster

texts:
Currently planning on building a new SQL Server Cluster enviroment, we currently have 2 node 2 instance Active/Active cluster.

One of the clusters has had heavy hitting security/monitoring apps and also they've put user facing toolsets on there too.

Would it be possible have a Active/Active/Active 3 node 3 Instance Cluster? So each Instance was on its own node?

We had suggested Active/Active/Passive but one of the requirements from the client was each instance is active on its own node


texts: can I set a MAX Limit to a BIGINT cassandra's data type?

texts:
can I set a MAX Limit to a BIGINT cassandra data type?
this is my first step in cassandra db.
for example: bigint(10) just like in mysql?


texts: Is it possible to modify an existing trigger definition in MySQL?

texts:
I am wondering if it is possible to update a trigger definition in MySQL. For example, I have a trigger T and I want to add some new functionality to it.

My assumption is that I need to drop and recreate it.

What are the best practices in the database administration for such scenarios?


texts: Optimal way to store leaderboards by month

texts:
I have a leaderboard table of users for current month. I need to save all leaderboards by months and display current/old leaderboard on site. Should i create a new table when every month stars? Is this a good approach? In two years i will have 24 additional tables in my database which is really confusing me.
What is optimal way to do this?


texts: STRING_SPLIT with a multiple-character separator?

texts:
SQL Server 2016 introduced STRING_SPLIT which is very fast and an excellent replacement for any homemade implementation people would roll before 2016.

Unfortunately, STRING_SPLIT only supports a single-character separator, which isn't always enough. Does anyone know of a good implementation that allows for using multiple characters in the separator?