texts: SQL Server 2016: cannot restore database from .bak file because of huge transaction log?

texts:
From our production server, I have downloaded a .bak file of SQL Server 2014 which I want to restore in my SQL Server 2016 Developer in my pc to test newer versions of the application with the most recent data.

I restore the DB from SSMS and while the progress bar gets at 100% in few seconds (the .bak file is about 145 MB), then the process remains stuck like that and nothing more happens, even if the restoring process seems to continue.

I have noticed that a very huge transaction log is created at once as soon as the restore process starts and it has a size of more than 300 GB.

How can I solve this? I have read maybe I should shrink the transaction log before create the .bak file but I am afraid of causing damages to the production DB. Which am I supposed to do in the correct way?


texts:
I have some tables with millions of rows (up to 6MLN) and I need to execute a delete to reduce the amount of rows. I created a procedure with a loop that delete 10k record each execution.
What I did see is that after some executions the table starts to respond very slow and the delition no longer respond, for example:

First itaration, 30k records to delete, time 35 seconds
Second iteration, 126k records to delete, time 3.4 mins
Third iteration, 350k records to delete, time 24 mins

Every iteration i place a commit every 10k record. So first iteration need to delete 30k records and commit every 10k records.
I see that every time i execute the procedure the table grow (and does not decrease, what i was expecting...)
I'm trying to find a good solution before this post, with no luck.
I cannot delete and re-create the table with less records, so i need to find a way to lower down the records on the table.
How i can do to delete all this records maintaining the performance? Free space, "well built indexes" etc
I can give you more details if needed


texts: Logon failed for login > due to trigger execution

texts:
I have the following issue for a SQL Server 2016 instance.

Usually everything works fine but form time to time we are getting Error 17892 followed by error 18056. This is a selection form the error log of the instance:

2019-03-20 16:38:19.90 Logon       Login succeeded for user <<User Name 1>>. Connection made using Windows authentication. [CLIENT: 10.xxx.xxx.xxx]
2019-03-20 16:38:40.96 Logon       Error: 17892, Severity: 20, State: 1.
2019-03-20 16:38:40.96 Logon       Logon failed for login <<User Name 1>> due to trigger execution. [CLIENT: 10.xxx.xxx.xxx]
2019-03-20 16:38:40.96 spid76      Error: 18056, Severity: 20, State: 1.
2019-03-20 16:38:40.96 spid76      The client was unable to reuse a session with SPID 76, which had been reset for connection pooling. The failure ID is 1. This error may have been caused by an earlier operation failing. Check the error logs for failed operations immediately before this error message.
2019-03-20 16:38:45.36 Logon       Error: 17892, Severity: 20, State: 1.
......

2019-03-20 16:38:49.19 Logon       Login succeeded for user <<User Name 1>>. Connection made using Windows authentication. [CLIENT: 10.144.227.204]

As you can see the same user is first able to login without any problem and then the logon fails for some time and then start to work again! This goes on and off for some time and then start to work again.

We do have a logon trigger that uses APP_NAME() to check the application name and write to a log table if a certain condition are met. The user rights should be alright as it is able to insert records into the logging table. For this user that the error is occurring for there should not be any logging. The trigger is created with 'WITH EXECUTE AS SELF'.

On request here is the trigger code

CREATE   TRIGGER [tr_sasLogon]
ON ALL SERVER
WITH EXECUTE AS SELF
FOR LOGON
AS
BEGIN
    IF
    (APP_NAME() like 'SOMETHING%')
    BEGIN
      INSERT INTO tkadmin.dbo.audit_login
                (date, login, program_name)
                    VALUES
                (GETDATE(), ORIGINAL_LOGIN(),APP_NAME())
    END
END
GO

Anyone got some ideas what can be the issue causing this behavior?


texts: How can I select all of the parents of rows that exist in another table?

texts:
I have two tables that share a many to many relationship. The relationships are stored in a third table.

CREATE TABLE Person
(
    Id int NOT NULL,
    Name varchar(255) NOT NULL,
    PRIMARY KEY (Id)
)

CREATE TABLE Filenodes
(
    Id hierarchyid NOT NULL,
    Name varchar(255) NOT NULL,
    PRIMARY KEY (Id)
)

CREATE TABLE PersonFilenodes
(
    FileId hierarchyid NOT NULL,
    PersonId int NOT NULL,
    PRIMARY KEY (FileId, PersonId)
)

I need to select all of the filenodes that belong to a person, as well as all of the files that are ancestors of those files.

I know how to select all of the Filenodes that belong to a given person:

SELECT Id, Name
FROM PersonFilenodes
JOIN Filenodes on Filenodes.Id = PersonFilenodes.FileId
WHERE Id = @personId

Thanks to marc_s, I also know how to select all ancestors of a given filenode:

SELECT Id, Name
FROM Filenodes
WHERE @filenodeId.IsDescendantOf(Id) = 1

I just can't figure out how to combine the two queries to achieve the result set that I need. How can I select all of the filenodes that belong to a person, as well as all of the files that are ancestors of those files?


texts: How can prevent writes to a Postgres schema for a period of time?

texts:
I am moving a schema from one server to another using pg_dump + pg_restore + drop schema cascade. I need to know that no applications are writing to it (or at least that the writes will fail) during this process to avoid losing data.

At the moment I am renaming the schema before starting the dump. Is that sufficient to prevent writes? What if a large batch insert is busy when I rename the schema? Will that still complete?


texts: Error when upgrading SQL Server from 2012 (sp3) to 2014

texts:
I am attempting to upgrade MS SQL Server 2012 (sp3) to SQL Server 2014.

At the end of the upgrade several components fail with an error that says,

  "Error details: Error installing SQL Server Database Engine Services
  Instance Features Failed to create a new folder
  'H:\MSSQL11_MSSQLSERVER_Backup_Dir'. The specified path is invalid
  (for example, it is on an unmapped drive). Error code: 0x84CF000E"

This server does not have an H:\ drive. As far as I know, it never did have an H drive. How can I correct this?


texts: Is it possible to restore SQL Server DB from transaction log (no MDF)?

texts:
We had a SQL Server 2005 server that housed roughly 200 low/no traffic archive databases, and that server was not backed up to any other device.  The database files (MDF and LDF) were stored on a RAID array, and when the RAID controller failed we were concerned we were going to lose our data.  We sent the RAID controller and array to a data recovery company, and they were able to recover most of the files, but in some cases we have the transaction log but are missing the main data files.

Is it possible to restore the database to the last transaction (which should be just after the database was created) using just the transaction log, or is this something that can't be done?


texts: Is "deadlock detected" really an error? Should I be suppressing them after handling them?

texts:
A while ago, I spent a nightmarish number of weeks dealing with "deadlock detected" and trying to figure out how to handle it. I ended up finally handling it in such a manner that my code is able to detect when it happens and then retry the same query indefinitely with 50000 microseconds in between each retry, until it works.
Maybe that's bad practice, but so far (months), it has not caused any problems except for logging the "deadlock detected" so-called "errors".
Is it "OK" for me to now suppress the "deadlock detected" errors by marking them as "unimportant" and thus not showing up to me, even if they are still logged into my error log table?
Please don't tell me to "avoid them in the first place". This is simply not possible. They will apparently happen if you do concurrent (multiple processes/script instances) working on the same table/thing. I spent forever trying to "code them away", but it just doesn't seem possible.
Obviously, since I'm asking this instead of just adding the ignore rule and being done with it, I do care about the answers/responses. Still, I don't think I can be convinced at this point that they can be avoided completely. I'm not saying that I get thousands of them logged every hour or anything, but a few each day, seemingly always in the beginning when I do have lots of concurrent processes working on the same table/query.


texts: The best way to raise error from mass insert query

texts:
I have a following stored procedure:

CREATE TABLE Table1 (val INT)
GO
CREATE TABLE Table2 (val INT)
GO
ALTER TABLE Table1 ADD CONSTRAINT chk1 CHECK (val > 0 )
GO
INSERT INTO Table2
SELECT 1 UNION ALL
SELECT 2 UNION ALL
SELECT -1 UNION ALL -- wrong data
SELECT 3
GO
CREATE PROCEDURE sproc1
AS
BEGIN
    BEGIN TRY
        INSERT INTO Table1
        SELECT val FROM Table2
        COMMIT TRAN
    END TRY
    BEGIN CATCH
        IF @@TRANCOUNT > 0
        BEGIN
            ROLLBACK TRANSACTION;
        END
    END CATCH
END

Is it possible without using cursor to insert of 1,2 and 3 values and correctly raise error that -1 is wrong data?

Current version roll back a whole transaction what is not good case for me. And I won't use cursors :)

Thank you in advance!

P.S. I'm using SQL Server 2008 R2


texts:
So we have this VARCHAR(255) column called code, which is a string of varying size we use in a WHERE clause.

Doing this search slows the query down by about 300ms and since the query is run a few hundred times a day, this amounts to quite some time lost.

The statement looks something like this:

SELECT *
FROM table1 t1
WHERE t1.code LIKE '%so%'

Where 'so' is the start of the string 'something'.

I've optimized the rest of the query, so this is really the only part that is left unoptimized.

I've tried adding an index to the column, however, since the input string doesn't always match the start of the string, it results in a search that could be anywhere within the string, meaning that a B-tree index doesn't work.

I've also tried adding a 'FULLTEXT' index, but it didn't speed up my query, it only used more disk space.

Is there any way to speed up a full text search with variable length strings , where the searched string could be at any position within each row of the column?

The problem with the FULLTEXT index is that when using match against it has a 50% threshold, which I don't really understand. The effect it has is that it returns an empty result set, because it matches too much.


texts:
I have two MongoDB instances running on two servers (instance1 and instance2) and they are configured as a replica set. Instance1 is the primary and instance2 is the secondary.

When instance1 was shut down, instance2 became the primary. However, they both had priority value set to 1. Therefore when instance1 was up again, instance2 kept being the primary node.

I changed the priority value of instance1=2 and instance2=1. If instance1 is shut down again, instance2 will become the primary. When instance1 is up again, will this new priority setting make sure that instance1 becomes the primary again?


texts:
I have 2 server using merge replication. One acts as a Publisher and one acts as a Subscriber.
One day I want to switch the side, publisher become subscriber and subscriber become publisher. In my current way, I need to stop the replication, delete all publishing & subscription settings I was made and recreate it again. Is there any easier/faster way that I don't know ?


texts: PostgreSQL GRANT ALL on mutliple tables using LIKE

texts:
I have more than 100 tables named public.test_*

How can I easily GRANT ALL access to the user test to all that tables at once?

I tried with:

GRANT ALL ON TABLE public.test_* TO test;

But it is not working ...


texts:
I have a table like this (with more columns though):
CREATE TABLE [dbo].[MyTable](
    [SnapKey] [int] NOT NULL,
    [SnapDt] [smalldatetime] NOT NULL,
    [Company] [varchar](4) NOT NULL,
    [ProfitCenter] [varchar](10) NOT NULL,
    [CostCenter] [varchar](10) NOT NULL,
) ON [MyPartition]([SnapKey])

CREATE CLUSTERED INDEX [IDX1] ON [dbo].[MyTable]
(
    [SnapKey] ASC
)

Table is partitioned on SnapKey. SnapKey is the date part of SnapDt, stored as an integer, for example 20160131 and 20160229.
Each partition contains only 1 SnapKey. For each SnapKey partition, I have about 5 million rows. Currently I'm keeping only the last day of each month in my table.
I always use SnapKey for querying. No updates happen to the data. For each day, data is populated into the table and then we run some reports on it during the month.
Question: If I keep data for 6 days per month, instead of 1 day per month, will my queries perform slower?
I couldn't find any clear answer, so I tried to populate the table with data but I ran out of storage, so I decided to ask you, to see if there is any theoretical explanation.
Clarification
By storing 5 more days, we will store 6 times the data (for historical reporting). We will keep the last 6 days of the month instead of only the last day.
Our queries will not change and our reports are still over 1 single day (one SnapKey).
We have one SnapKey per month. For now we have
20160131
20160229
20160330

...and so on. One SnapKey for each month-end.
By having 5 days more, SnapKey will look like:
20160126, 20160127, 20160128, 20160129, 20160130, 20160131
20160224, 20160225, 20160226, 20160227, 20160228, 20160229
20160325, 20160326, 20160327, 20160328, 20160329, 20160330 ...and so on

You'll see that we store 6 times more data, but still work on only one SnapKey in our queries. This means we always have:
WHERE SnapKey = xxxxxxxx

in all of our queries.


texts:
PostgreSQL 10.7

I keep getting the message:

HINT:  Consider increasing the configuration parameter "max_wal_size".

So, I updated the following parameters and reloaded the configuration.

checkpoint_timeout = 30min
max_wal_size = 2GB
min_wal_size = 80MB

However, the WAL size stays at 16MB.  What did I miss?

Btw, I use repmgr for replication wal_level = replica.


texts: remove postgres server from local machine

texts:
I know this is a real beginner question. But nevertheless I'm really confused:/
I wanted to have a clean installation of postgres on my ubuntu laptop. So I tried to delete everything related to postgres.
However, when I start running the PgAdmin, I see this under the servers:

I don't even remember when I created this robin_test-server. But now I fail to remove, connect or find any information about it. Anybody might have an idea on what to do in this situation? Any help is super appreciated:)


texts: What are the pros and cons of using my customer codes as a primary key?

texts:
I am designing a database for one of my sales teams. Right now, the goal is to get a master list of customers and their mailing addresses, but the DB will eventually expand to track some other customer data. So, right now I am just working on the customers table, but want to make sure I follow good normalization and efficiency guidelines in case this thing grows beyond current expectations.

Right now, we have ~3,000 customers. So, we have customer codes we use to ID our customers. Each one can be a max of 20 characters, is alphanumeric, and will always be unique per customer (Foo, Inc. might have a cust code of FOOINC and might have 25 sub accounts, but there will only be one FOOINC master entity. If we ever did biz with another company called Foo Technologies Inc, we would create a new code like FOOTECH or something).

I am not a DBA, but have designed several DB's in the past and traditionally use a SQL identity field as the PK. In this instance, I've been thinking of using the customer code. What are the pros/cons of doing this? On the one hand, it seems logical to use a unique identifier as the PK if I have one, which I do. On the other, I know string PKs are slower than int PKs - this database will definitely grow, but it will never be millions of rows. The 3,000 customers are over 7 years of business, so it will be quite awhile before we even get to tens of thousands of rows.

I've researched this question and the debate usually ends with a "it depends on the data" - so please, teach me...in this situation, what would you consider when planning the table? What would you use for the PK? Any advantage to sticking an autoincrementing INT in there anyway? Any issues with indexing and inserting records?

FYI, the table layout just needs the following:

-CustomerCode(nvarchar(20))
-CustomerName(nvarchar(50))
-Address1(nvarchar(50))
-Address2(nvarchar(50)) - nullable
-Address3(nvarchar(50)) - nullable
-ZipCode (nvarchar(9))

Bonus question - worth sticking with 3NF and making a separate table holding city, state, with ZipCode as a PK and making a relationship to the customers table? Or don't worry about 3NF and keep the city/state in the customer table to avoid some joins?

Thanks for the help! Let me know if you need any other details.


texts: Can anyone help me in fixing the issue (terminating the instance due to error 474) and then Instance terminated by PMON

texts:
I get these kinds of errors in my oracle logs many times leading to the abnormal shutdown of the oracle instance.

The alert log file contains the logs mentioned below:

Fri Feb 28 22:05:09 2020
Time drift detected. Please check VKTM trace file for more details.
Fri Feb 28 22:05:12 2020
System state dump requested by (instance=1, osid=26083 (PMON)), summary=[abnormal instance termination].
Fri Feb 28 22:05:12 2020
PMON (ospid: 26083): terminating the instance due to error 474
System State dumped to trace file /ora01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_diag_26097.trc
Fri Feb 28 22:05:14 2020
ORA-1092 : opitsk aborting process
Fri Feb 28 22:05:14 2020
ORA-1092 : opitsk aborting process
Dumping diagnostic data in directory=[cdmp_20200228220512], requested by (instance=1, osid=26083 (PMON)), summary=[abnormal instance termination].
Instance terminated by PMON, pid = 26083
Sat Feb 29 08:40:40 2020
Starting ORACLE instance (normal)


texts:
I can't explain this very well except by spelling it out.

I start a query on ID, and from there I can get TITLE, DATE, and REGNUMBER.

The problem is I also want to get 'where,' which is also stored in TITLE. So I need to get TITLE from REGNUMBER this time, instead of from ID.

In the end I want

1) ID->TITLE | 2)DATE | 3)REGNUMBER | 4)WHERE(REGNUMBER->TITLE)

to be more clear, TITLE is on the same table in both cases. In the first case, I need to get TITLE from a join on ID, and in the second, from a JOIN on REGNUMBER.

id   -   title    -   date   -   regnumber   -   title(same field as col2)
-------------------------------------------------------------------------------
1   -   event name   -   2/2/12   -   432   -   venue name


texts: How to create a custom view (using lookup) from aggregated data

texts:
I have data imported into a postgresql database:

groups (data import)
id | csv_user_ids
1  | 1,2,3
2  | 4,5,6
3  | 3,1,4

... and a lookup table for user names:

users (lookup)
id | name
1  | one
2  | two
3  | three
4  | four
5  | five
6  | six

I need to build a VIEW of groups with JSON column matching the user IDs as such

groups_view
id | json_array_users
1  | [{ 'id': 1, 'name': 'one'}, { 'id': 2, 'name': 'two'}, { 'id': 3, 'name': 'three'}]
2  | [{ 'id': 4, 'name': 'four'}, { 'id': 5, 'name': 'five'}, { 'id': 6, 'name': 'six'}]
3  | [{ 'id': 3, 'name': 'three'}, { 'id': 1, 'name': 'one'}, { 'id': 4, 'name': 'four'}]

How do I do that?

I guess I'd need to disaggregate rows in groups before aggregating into my new view again?

  JSON result is an array.

source:

groups (data import)
id | csv_user_ids
1  | 1,2,3
2  | 4,5,6
3  | 3,1,4

to:

group_user
group_id | user_id
1        | 1
1        | 2
1        | 3
2        | 4
2        | 5
2        | 6
3        | 3
3        | 1
3        | 6

How do I do that in SQL?

With string_to_array(text, text [, text]) from doc functions array, I was able to turn the CSV string into an array e.g. {1,2,3}

groups (data import)
id | csv_user_ids
1  | 1,2,3        => {1,2,3}
2  | 4,5,6        => {4,5,6}
3  | 3,1,4        => {3,1,4}

after what I tried array_to_json(anyarray [, pretty_bool]) doc functions-json ...


texts:
I've a very weird problem on mysql/Aurora 1.14 I have installed on a Linux EC2 instance on AWS.

I have an user able to connect remotely to the instance but not locally.

select user,host from mysql.user;
user | host
admin| %

I'm able to connect with that user from outside

mysql -u admin -pxxxx -h ip

but when I'm inside the machine

mysql -u admin -pxxx
ERROR 1045 (28000): Access denied for user 'admin'@'localhost' (using password: YES)

I've tried several other things (with or without sudo)

mysql -u admin -pxxx -h localhost
mysql -u admin -pxxx -h 127.0.0.1
mysql -u admin -pxxx --proto=TCP

I've also tried to create another user

create user 'localtest'@'localhost' identified by 'pwd18';
grant all on mydb.* to 'localtest'@'localhost';
flush privileges;

mysql -u localtest -ppwd18 -h localhost
ERROR 1045 (28000): Access denied for user 'localtest'@'localhost' (using password: YES)

Other weird things is I don't have an user "root" in mysql.user.

And if I try from remote, with user admin to give grant all on all dbs to localtest I get:

GRANT ALL PRIVILEGES ON *.* TO 'localtest'@'localhost'
Error Code: 1045. Access denied for user 'admin'@'%' (using password: YES)

No more ideas.
Logs is not very useful, just says "access denied"

As requested:

SELECT USER()
admin@xx.xx.xxx.x   admin@%

SHOW GRANTS FOR 'admin'@'%'
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, PROCESS, REFERENCES, INDEX, ALTER, SHOW DATABASES, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, LOAD FROM S3, SELECT INTO S3 ON *.* TO 'admin'@'%' IDENTIFIED BY PASSWORD '*xxxxxx' WITH GRANT OPTION

I also gave explicitly all above grants to user 'localtest'@'localhost', but nothing changed


texts: Multi-row insert vs multiple single row inserts

texts:
In my app I do multi-row inserts when I can just because it reduces the number of round trips between the db and the app.

However, I was curious, is there any other advantages? For example, if multiple rows are inserted at once like this:

insert into tbl (c1, c2) values
(v1, v2)
(v3, v4)

versus:

insert into tbl (c1, c2) values (v1, v2)
insert into tbl (c1, c2) values (v3, v4)

and the table has an index, is the index calculated once in the first case, and twice in the second case? Or is it always once per insert? Assume that both queries are in the same transaction.

I'm using PostgreSQL.


texts: How to tell which windows group login I used when logging in via windows authentication

texts:
I created two SQL Server Logins which correspond to two windows groups:

MachineName\MyAppAmdin
MachineName\MyAppUser

Then in the database, I created two users with the same name and mapped them to the logins.

In the windows server, I added my domain account MyDomain\MyAccount to the MachineName\MyAppAmdin group.

Now I can pass windows authentication via MyDomain\MyAccount.

The thing is, I would like to know exactly which windows group login I'm using, but I'm not able to find out how.

I tried:

ORIGINAL_LOGIN()
SYSTEM_USER
SUSER_SNAME
SELECT * FROM dm_exec_sessions

All the above return MyDomain\MyAccount, but what I need to know is if I was logging in via the group membership in MachineName\MyAppAmdin.

To sum up, my question is:

Is there a way to tell exactly which Windows Group Login (or user) the current connection is using?

Or is there any way I can check if MyDomain\MyAccount is associated with a particular user or login?

I know I can use C# or command to resolve if a domain account belongs to specific windows group, but we have some new IT policies, so I'm thinking of a way to achieve a similar result by using TSQL.


texts:
Maybe this question has been asked already, but I don't know if my approach is correct.

Once I was modelling a database for my university. It was meant to store information about the health of the students, and then analyze it to give the student a feedback.

For example: in one question it was asked who have or had diabetes, with multiple options as an answer, for example:

Mom
Dad
Student
Brothers

And the student could select one or many options. I needed to store that in the database.

First the values in the DB where stored in a string where values were separated by a comma. (I know, this isn't the best way).

So I thought about doing the next things:

[

I thought about creating a table for each field where the value was stored like a comma separated array.

Having the same fields, but just to identify which relative has/had the disease.

What I did was correct?


texts: Database layout to keep track of the "freshness" of a column value in a specific row

texts:
I have a Person with columns like name, address and bio.
How do I store data in order to know the "freshness" of these columns?
For instance, "for the Person Y, the name has changed 2 days ago, and the location 2 weeks ago".
Is this simply a versioning problem?
Person v1 - name: A1, address: B1, bio: C1, updated_at: 4.days.ago
Person v2 - name: A2, address: B1, bio: C1, updated_at: 3.days.ago
Person v3 - name: A2, address: B1, bio: C2, updated_at: 1.days.ago

And after I look trough different versions to find the last one with a different column?
Or is there better ways to handle that? Put freshness data in another table? Or even DB?
I will normally use MySQL, unless this can be solved by means of another database management system.
Regards.
Additional details
Thank you for you comments and answers, but I was forgetting something: Actually, I need to know when the column value was updated, but by who as well.
Something like "updated at XX by User Y".


texts: Replication problems after server upgrade

texts:
In transactional replication - first 8000 rows of snapshot copies to subscriber snapshot then acts like it completed successfully.

Publication table has 21 million rows. Snapshot says completed successfully then we get the error "cannot find row at the subscriber" when transaction replication begins.

This is on a new 64 bit server running SQL Server 2005 Standard 64 bit SP3 with Cumulative Update 4. We have a standalone distributor also.

We upgraded the server / OS to Windows 2008 R2 64 bit and SQL Server from 32 to 64bit with the Cumulative Update 4 to take care of the paging issue/bug.

Has anyone ever ran into this situation?


texts: with (tablock) does not seem to make code faster for insert select

texts:
I read about minimal logging and that one should use with (tablock) for insert selects. I had high expectations when I added this but it does not seem to make a difference. I use it in ETL and tables are empty (truncated) and the recovery model is "simple".
Am I missing something? I also see tablockx sometimes but when I read about minimal logging it mentioned specifically tablock.
E.g. here it does not mention tablockx https://docs.microsoft.com/en-us/sql/relational-databases/import-export/prerequisites-for-minimal-logging-in-bulk-import?view=sql-server-ver15


texts: How to create Unicode parameter and variable names

texts:
All of this works:

CREATE DATABASE [¯\_(ツ)_/¯];
GO
USE [¯\_(ツ)_/¯];
GO
CREATE SCHEMA [¯\_(ツ)_/¯];
GO
CREATE TABLE [¯\_(ツ)_/¯].[¯\_(ツ)_/¯]([¯\_(ツ)_/¯] NVARCHAR(20));
GO
CREATE UNIQUE CLUSTERED INDEX [¯\_(ツ)_/¯] ON [¯\_(ツ)_/¯].[¯\_(ツ)_/¯]([¯\_(ツ)_/¯]);
GO
INSERT INTO [¯\_(ツ)_/¯].[¯\_(ツ)_/¯]([¯\_(ツ)_/¯]) VALUES (N'[¯\_(ツ)_/¯]');
GO
CREATE VIEW [¯\_(ツ)_/¯].[vw_¯\_(ツ)_/¯] AS SELECT [¯\_(ツ)_/¯] FROM [¯\_(ツ)_/¯].[¯\_(ツ)_/¯];
GO
CREATE PROC [¯\_(ツ)_/¯].[sp_¯\_(ツ)_/¯] @Shrug NVARCHAR(20) AS SELECT [¯\_(ツ)_/¯] FROM [¯\_(ツ)_/¯].[vw_¯\_(ツ)_/¯] WHERE [¯\_(ツ)_/¯] = @Shrug;
GO
EXEC [¯\_(ツ)_/¯].[¯\_(ツ)_/¯].[sp_¯\_(ツ)_/¯] @Shrug = N'[¯\_(ツ)_/¯]';
GO

But you can probably see where I'm going with this: I don't want @Shrug, I want @¯\_(ツ)_/¯.

Neither of these work on any version from 2008-2017:

CREATE PROC [¯\_(ツ)_/¯].[sp_¯\_(ツ)_/¯] @[¯\_(ツ)_/¯] NVARCHAR(20) AS SELECT [¯\_(ツ)_/¯] FROM [¯\_(ツ)_/¯].[vw_¯\_(ツ)_/¯] WHERE [¯\_(ツ)_/¯] = @[¯\_(ツ)_/¯];
GO
CREATE PROC [¯\_(ツ)_/¯].[sp_¯\_(ツ)_/¯] [@¯\_(ツ)_/¯] NVARCHAR(20) AS SELECT [¯\_(ツ)_/¯] FROM [¯\_(ツ)_/¯].[vw_¯\_(ツ)_/¯] WHERE [¯\_(ツ)_/¯] = [@¯\_(ツ)_/¯];
GO

So, is there a way to use unicode stored procedure parameter names?


texts: How to recover from two queries stuck in kill state causing all the database to not respond?

texts:
During a INSERT INTO new_table (f1, f2, ..., fn) (SELECT f1, f2, ..., fn FROM table) I clicked the new_table in php-myadmin which ran a simple SELECT * FROM new_table LIMIT 0, 30 which ofcourse couldn't be completed because new_table was not ready.

I tried Killing the query, but it just kept itself in Kill state and therefore I assume it caused some sort of rollback in new_table.

The copy data query is for a 200GB table and was running for about 10hours till this interruption (I think it was almost finished). I tried to also kill that query thinking it will free things up, but again it got stuck.

I'm seeing this in SHOW ENGINE INNODB STATS

    ---TRANSACTION 5254EC173, ACTIVE 39375 sec rollback
mysql tables in use 2, locked 2
ROLLING BACK 4169557 lock struct(s), heap size 371276216, 34345786 row lock(s), undo log entries 26939759

Is there anything I can do? Any time estimation? Will it try to rollback the whole table and then try to return that whole data? Will it try to use diskspace for all these operations? Is there anything to avoid the lock? would it be a good idea to do mysql restart or will this just start the operation over again?


texts:
i have an unique case where a union table with a flag might be the answer, but my db admin experience is limited.

CREATE TABLE patient(
   patient_id serial PRIMARY KEY,
   name VARCHAR (50) UNIQUE NOT NULL,
   //...
);
CREATE TABLE diagnosis(
       diagnosis_id serial PRIMARY KEY,
       name VARCHAR (50) UNIQUE NOT NULL,
       //...
    );

//this is past medical history

CREATE TABLE patient_diagnosis(
      diagnosis_id integer
      patient_id integer
      FOREIGN KEY (diagnosis_id) REFERENCES diagnosis (diagnosis_id)
      FOREIGN KEY (patient_id) REFERENCES patient (patient_id)
)

Now, I wan't to add a patient's family medical history, which is also just referring to the diagnosis table, with similar ability to search later (show me all patients with family diagnosed with xyz). I was thinking of putting a family=boolean flag in the patient_diagnosis table, but not sure if thats bad design. I also don't know what terms to search for here for help. any thoughts appreciated


texts: Imported SSIS package fails if imported with SSMS on the SQL server, but works when imported from a remote PC

texts:
I am facing a strange issue on SQL Server 2017 Enterprise edition running in AWS on EC2.

When we are on the SQL server and we import an SSIS package using the locally installed SSMS connected to Integration Services it is successful. But, the package fails to run with an error:

The binary code for the script is not found.
Please open the script in the designer by clicking Edit Script button and make sure it builds successfully.

However, if we perform the exact same procedure from a remotely connected Windows 10 PC using the SSMS that is installed on the PC, the package will run properly.

Both the server and the Windows workstation are running SSMS 17.9.1. Comparing versions in Help -> About shows the exact same version numbers on everything. The same domain user is performing the procedure and that user is a domain admin and SQL Sysadmin.

This makes no sense. Is there another component being used I am unaware of? Any ideas?


texts: SQL Server: Is altering collation on existing databases safe?

texts:
I'm new to SQL Server, and wasn't aware of the possibility that T-SQL may be case insensitive.

I have been normalizing entities by splitting tables and constraints. I'm scared that if I change the collation now, that some of those constraints may be invalid if, for example, a 'case insensitive' foreign key value points to a PK value with different case.

If I were to run this query on the DB (tested on a basic database):

ALTER DATABASE <db_name>
COLLATE SQL_Latin1_General_Cp1_CS_AS ;
GO

What would happen if some constraints were violated?

Is there any other reason that I should be careful making a database-wide change such as changing the collation?


texts:
I have a string in which need to extract the date,

Example: S01_XXX001_000_min_01_20150526T123440583.arc ,
S01_XXX001_000_20150526T123440583_min_01.arc

based on the pattern like 'Numeric T Numeric',

I am trying to extract with the below sql , can any one help me.

SQL:

select  substring('S01_ZAR001_000_min_01_20150526T123440583.arc','^\d')

select  substring('SAPCC_XX_ZZZ_BitClass_XXX_20150623T45656566456_XXX_XX_XXXXXX.arc' like '_%T%_',1,8)

select  substring('SAPCC_XX_ZZZ_BitClass_XXX_201506235T335656_XXX_XX_XXXXXX.arc','[^_]+([^0-9]{1,8})$')

SELECT SUBSTRING('XY1234Z', 'Y*([0-9]{1,3})');


texts: What's the difference between "shrink" file vs simply reducing the initial size?

texts:
I'm totally new in DB Administration. So i got confused about the "file shrink" functionality offered on SQL Server Management Studio (SMSS) or via T-SQL. Why I would I need to use that "file shrink" functionality if I can simply lower the initial size of the db?

I tried to (edit) reduce the initial size of my DB and it succefully reduced my physical file of the db. Which would also work quite the same if I use that "shrink" feature.


texts: Using TOP and getting different result sets

texts:
I am trying to write an efficient query for deleting chunks of data. To this end I hoped to avoid an index scan by using the primary key to get the oldest records. However I'm seeing some unexpected results returned.

I hoped this

SELECT TOP 15 OrderID FROM [Order]

Would give me the oldest 15 records because I could rely on the primary key incrementing and therefore the order of storage would be low to high in the table.

However, this returns a different result set

SELECT TOP 15 OrderID FROM [Order] ORDER BY DateCreated ASC

Which seems to be a more accurate but more expensive way of getting the result I need.

Confusingly, this

SELECT TOP 15 * FROM [Order]

Gives a different set of OrderID s (PK) to this

SELECT TOP 15 OrderID FROM [Order]

I understand that http://msdn.microsoft.com/en-gb/library/ms189463.aspx explains that order cannot be guaranteed without an ORDER BY clause but expected the PK to order for me and can't explain the differences between the final two select clauses.


texts: Contradictory reports from MySQL that a binlog file exists

texts:
Our replication recently stopped working.  I submitted a question but was unsuccessful in fixing the problem (Error 1236 - "Could not find first log file name in binary log index file").  Since the occurrence of the events described in the question, I've executed a RESET MASTER on the master, a RESET SLAVE on the slave, and created a new full dump, but am still getting the same results.

I thought I'd take a step back and try taking a different approach, attacking the specifics of the error message itself.

When I execute SHOW SLAVE STATUS on the slave, it reports that the master cannot "find first log file name":

Got fatal error 1236 from master when reading data from binary log: 'Could not find first log file name in binary log index file'

The "first log file name" is /var/lib/mysql/mysql-bin.000001:

root@master [905 13:30:56 /var/lib/mysql]# cat mysql-bin.index
/var/lib/mysql/mysql-bin.000001
/var/lib/mysql/mysql-bin.000002
/var/lib/mysql/mysql-bin.000003
/var/lib/mysql/mysql-bin.000004
/var/lib/mysql/mysql-bin.000005
/var/lib/mysql/mysql-bin.000006

MySQL itself seems to be aware that mysql-bin.000001 is the first binary log:

MariaDB [(none)]> SHOW BINARY LOGS;
+------------------+------------+
| Log_name         | File_size  |
+------------------+------------+
| mysql-bin.000001 |      10421 |
| mysql-bin.000002 | 1073919628 |
| mysql-bin.000003 | 1074488806 |
| mysql-bin.000004 | 1073744707 |
| mysql-bin.000005 | 1074366770 |
| mysql-bin.000006 | 1069984818 |
+------------------+------------+
6 rows in set (0.00 sec)

mysqlbinlog shows that this file seems to be accessible and valid:

root@master [911 13:48:04 /var/lib/mysql]# mysqlbinlog /var/lib/mysql/mysql-bin.000001
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#160526 12:24:14 server id 5  end_log_pos 248   Start: binlog v 4, server v 10.0.21-MariaDB-log created 160526 12:24:14 at startup

MySQL itself also seems to have access to this first log file:

MariaDB [(none)]> SHOW BINLOG EVENTS in 'mysql-bin.000001' from 0 limit 4;
+------------------+-----+-------------------+-----------+-------------+------------------------------------------------+
| Log_name         | Pos | Event_type        | Server_id | End_log_pos | Info                                           |
+------------------+-----+-------------------+-----------+-------------+------------------------------------------------+
| mysql-bin.000001 |   4 | Format_desc       |         5 |         248 | Server ver: 10.0.21-MariaDB-log, Binlog ver: 4 |
| mysql-bin.000001 | 248 | Gtid_list         |         5 |         273 | []                                             |
| mysql-bin.000001 | 273 | Binlog_checkpoint |         5 |         312 | mysql-bin.000001                               |
| mysql-bin.000001 | 312 | Gtid              |         5 |         350 | BEGIN GTID 0-5-1                               |
+------------------+-----+-------------------+-----------+-------------+------------------------------------------------+
4 rows in set (0.00 sec)

Question

To recap, MySQL is complaining that it "Could not find first log file name in binary log index file".  However, as shown above, it appears that MySQL does know what the "first log file name" is, and it can, indeed, access it.

What else should I check to ensure that MySQL can actually "find [the] first log file name"?


texts: Providing access to the SQL server database for outside developers

texts:
I am beginning a project that will involve insert and updates to a sql server database from a website. I maintain the SQL server database and am providing access to the sql server db.

My plan is to create a user with read/write access and provide the outsourced developers with a connection string with access to views that I create. Please let me know if this is outside the development expectations or if anyone has recommendations.

Thank you


texts: PostGIS buffer in miles not lat/long degrees

texts:
I am trying to take two points, add a buffer around them (in meters), draw a line between them, draw a buffer around that, and then query whether items are inside the area of the circles expanded line joining them.

I have my columns in regular postgres point type, so I have to do some massaging with ST_MakePoint from the lat/long. I'm leaving out tables and columns because I think it's strictly a data-type/misunderstanding on my part.

I got the following working, but it feels like I'm doing way more than I need to:

SELECT ST_Intersects(
  ST_Collect(
    ARRAY[
      ST_Buffer(ST_Transform(ST_SetSRID(ST_MakePoint(-97.791803, 30.447039), 4326), 2163), 10 * 1609.3444),
      ST_Buffer(ST_Transform(ST_SetSRID(ST_MakePoint(-97.743119, 30.268503), 4326), 2163), 10 * 1609.3444),
      ST_Buffer(
        ST_Transform(
          ST_SetSRID(
            ST_MakeLine(
              ST_MakePoint(-97.791803, 30.447039),
              ST_MakePoint(-97.743119, 30.268503)
            ),
            4326
          ),
          2163
        ),
        10 * 1069.344, 'endcap=flat'
      )
    ]
  )::geometry,
  ST_Transform(ST_SetSRID(ST_MakePoint(-97.791803, 30.447039), 4326), 2163)
) AS intersects;

I'll be honest, I'm not even sure if this makes sense in PostGIS world, or if I'm trying to do some square peg in round hole type stuff. Hopefully that's enough to show that I'm trying to solve it on my own and what "solved" looks like, in this context.

Thanks!


texts: Is there any special configuration for SQL Server implementing Routing List in a heterogeneous enviroment (Client and Server in different domains)?

texts:
I had implemented successfully SQL Server Routing List Feature many times... but I have a very special scenario where our Reporting Services Node is located in a different Domain than SQL Server Nodes.

Brief environment description:

Reporting Services/SQL2012 (connection success with other server environments using Application Intent)
Always On Nodes/SQL2014.
There are several security levels between then. Only require port for SQL Server Listener is open.
When I built a Reporting Services DataSource without Application Intent Property, the connection test run with success but as soon I activate ApplicationIntent=ReadOnly;MultiSubnetFailover=True the connection fails throwing the following 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: TCP Provider, error: 0 - No such host is known.)

Datasource is using SQL Auth, and the user exist in all participant nodes of the availability group.


texts:
There are 3 shops.  In each of these is a mysql server.  I want all servers to synchronize their date if at least one server changes the date. I read a bit of literature and realized that I need master master replication. However, I must take into account the fact that one or more of the databases can work a couple of hours in offline mode (e.g. shut down Internet).  How will this work in this case? Will there be duplication of column with Primary key? if so then do I need to take this into account?

Advise me, please. I'm new to mysql.


texts: Good Natural Key For a Physical Mailing Address

texts:
I'm trying to figure out what a good natural key for a Physical Mailing (PO Box) address would be. I haven't designed the fields for the table yet, and initially I was going to go with a surrogate key and a text field for the address, allowing free input. But the other dev I'm working with is interested in having a natural key (just for this one table).
However, it's not quite making sense to me, because I'm not sure how to ensure, with an address, properly input data (when keyed in either on create or to do some kind of manual lookup). I know that you can often format the same address in more than one way.
For example:
Suite vs Ste.
Apt. 36 vs #36 vs Unit 36
1110 West 300 South vs 1110 W 300 South vs 1110 W. 300 S. vs....

All valid, but all different. Granted PO Boxes are more standardized, but I feel like it may still suffer from some of the same issues.
Any thoughts on the matter would be greatly appreciated!


texts:
Suppose a table data recording values of some foo and bar over time:

time | foo | bar
-----+-----+----
1    | a   | a
2    | a   | a
3    | a   | a
4    | B   | a
5    | B   | a
6    | a   | a
7    | a   | a
8    | a   | X
...

We would like to select the duplicate rows in order to get rid of them. Duplicate rows are, in this example, those with times 2, 3, 5 and 7, because the tuple (foo, bar) did not change from the previous time. Also, while the row 6 seems to be duplicate of 1, 2 and 3, there is a change in between, thus 6 is not a duplicate (but 7 is).

My simple solution is this:

SELECT
  *
FROM
  data AS candidate
WHERE
  EXISTS (
  SELECT
    *
  FROM
    data AS original
  WHERE
    ROW(candidate.foo, candidate.bar) IS NOT DISTINCT FROM ROW(original.foo, original.bar)
    AND original.time < candidate.time
    AND NOT EXISTS (
      SELECT
        *
      FROM
        data AS other
      WHERE
        ROW(candidate.foo, candidate.bar) IS DISTINCT FROM ROW(other.foo, other.bar)
        AND original.time < other.time
        AND other.time < candidate.time
    )
  )

In human language: looking for rows where we can find a row above it with the same (foo, bar), but where there is no other row with some other (foo, bar) in between.

Is there some feature of PostgreSQL that could be used for this purpose?


texts:
I have configured Distributed AG from DC1 to DC2 (SQL 2017 Ent edition with CU22 version)
DC1- DB1, DB2 and DC2-DB3, DB4

I have restarted DC1-DB1 SQL services, after restart DC1-DB2 should be primary. But still DC1-DB2 showing as  secondary.  Is this is the expected behaviour?

I have restarted DC1-DB2 SQL services, nothing happen - still DC1-DB1 is primary. Is this is the expected behaviour?

DC1 AG configurations:


texts: Any way of forcing MySQL to use smaller files?

texts:
This is actually Maria DB.  I'm using it on W10 and Linux Mint 18.3.

The issue here is that I recently strated to use Google Backup & Sync to sync between my various machines running W10 (I'm also trying to get rclone in Linux to do the same thing).

The problem is that the main files used by MariaDB/MySQL are rather big:  ibdata1 (currently 77MB), ib_logfile0 (currently 51 MB) and ib_logfile1 (currently 51 MB).

Each time I make even a minimal change to some data on one machine it takes a lot of time for this to propagate to the other machines synched with the same Gmail Drive account.  This is because each time (apparently) the new versions of all three files have to be laboriously uploaded to the Cloud and then laboriously downloaded to the other machines.

Are there any solutions to this to make the updating between machines quicker? I just spotted the tag (here) "Google-cloud-sql" ... could this be an answer???


texts: Tables not showing up when linking SQL Server to Access using ODBC connection

texts:
I am learning to link the back end of a database in SQL Server express 2016 to the front end in Access 2016. The tables were originally in Access and then I exported them to SQL Server. Now I am attempting to link them to the front end in Access but the problem is that they are not showing up in the possible tables that I can link. The answers I found when searching this up was that I simply did not have permission, but I am on my laptop and there are no other users and I am using Windows Authentication. I am doing this using the Northwind sample database. The following shows that I only see the system tables as options when trying to link not the desired tables.

This shows the settings of my data source.


texts: How To identify old and new value of a column in a same row while using GridDB

texts:
Is there any script to identify the OLD and NEW value of a column (last updated), output need in a single row with two different columns with OLD_Value and New_Value like this?
I am using GridDB on the Windows platform.


texts: How do I force the delete of users from my database?

texts:
I am making a clean backup of our development database for easy fresh spin-ups of the database. This database has about 200 test users that should be deleted to make it truly clean, but the problem is I am unable to delete any of them.

When I run a command like below:

DROP USER [MyGarbageUser]

It prompts me following error:

  Msg 15284, Level 16, State 1, Line 5 The database principal has
  granted or denied permissions to objects in the database and cannot be
  dropped.

These users neither own any objects in this database(as far as I know) nor they should. After a ton of searching on the net I have still not been able to find solution.

How can I force MyGarbageUser to be deleted from this database?


texts:
I get the below error from a SQL Server Agent Job. However, if I run the query in the job manually it works fine.
Why is this? Is it just SQL Agent that requires MSDTC?
I don't believe it is permissions issues as the job runs under a service account that is in sysadmin role.

Message

  Executed as user: SERVERS\quinsql_sa. The operation could not be
  performed because OLE DB provider "SQLNCLI10" for linked server
  "TestServer" was unable to begin a distributed transaction. [SQLSTATE
  42000] (Error 7391)  OLE DB provider "SQLNCLI10" for linked server
  "TestServer" returned message "The transaction manager has disabled
  its support for remote/network transactions.". [SQLSTATE 01000] (Error
  7412).  The step failed.

The involved versions are SQL Server 2008R2 and SQL Server 2012. The SQL Job is on the 2008R2


texts: Is it possible to prevent the audit plugin from being disabled?

texts:
We are experimenting with the MariaDB Audit Plugin and finding that users with the SUPER privilege can disable the auditing at any time and do whatever they want without any auditability.
mysql> SET GLOBAL server_audit_logging=OFF;
mysql> // perform nefarious tasks
mysql> SET GLOBAL server_audit_logging=ON;

The events of disabling and re-enabling the logging are logged (at least in the most recent versions of the plugin), but any "nefarious tasks" are not logged, as well as anything being done by other connections during that time period when the logging has been disabled.
Other than "don't give untrusted people SUPER privileges in your db, dummy", is there any way to ensure that the audit plugin is not disabled? The documentation shows how to prevent the plugin from being unloaded, but it does not say anything about preventing it from being disabled.


texts:
I have three tables: posts and tags and posts_tags which forms a many-to-many relationship between posts and tags.

Having a tag with milions of posts. How could I effectively retrieve the first 10 posts ordered by its name which contain the tag?

Schema:

CREATE TABLE posts (
    id          SERIAL PRIMARY KEY,
    post_time   TIMESTAMP
);

CREATE TABLE tags (
    id          SERIAL PRIMARY KEY,
    name        TEXT
);

CREATE TABLE posts_tags (
    post_id     INT REFERENCES posts(id),
    tag_id      INT REFERENCES tags(id)
);

I tried the query below but it's too slow.

WITH tag_id AS (SELECT id FROM tags WHERE name = 'tag_which_has_milions_of_posts'),
    post_ids AS (SELECT post_id FROM posts_tags WHERE tag_id = (SELECT id FROM tag_id))
SELECT * FROM posts WHERE id = ANY(SELECT post_id FROM post_ids)
ORDER BY post_time DESC LIMIT 10;

UPDATE:

I've change "post_time" to "name" to make it easier to generate test data.

Full schema (including indexes and test data):

CREATE TABLE tags (
    id          SERIAL PRIMARY KEY,
    name        TEXT
);

CREATE TABLE posts (
    id          SERIAL PRIMARY KEY,
    name        TEXT
);

CREATE TABLE posts_tags (
    tag_id    INT REFERENCES tags(id),
    post_id   INT REFERENCES posts(id)
);

CREATE INDEX ON posts(name);
CREATE INDEX ON tags(name);
CREATE INDEX ON posts_tags(post_id);
CREATE INDEX ON posts_tags(tag_id);

INSERT INTO posts (name) SELECT generate_series(1, 1000000)::TEXT;
INSERT INTO tags (name) SELECT generate_series(1, 1000000)::TEXT;
INSERT INTO posts_tags (tag_id, post_id) SELECT generate_series(1, 1000000), generate_series(1, 1000000);
INSERT INTO posts_tags (tag_id, post_id) SELECT 1, generate_series(2, 1000000);
INSERT INTO posts_tags (tag_id, post_id) SELECT 2, generate_series(400000, 600000);

Query (from McNets's answers):

select posts.* from posts_tags pt
    inner join tags on tags.id = pt.tag_id
    inner join posts on posts.id = pt.post_id
where tags.name = '2'
order by posts.name desc
limit 10;

Query plan:

Limit  (cost=288.72..288.73 rows=2 width=10) (actual time=893.348..893.349 rows=10 loops=1)
->  Sort  (cost=288.72..288.73 rows=2 width=10) (actual time=893.346..893.348 rows=10 loops=1)
        Sort Key: posts.name DESC
        Sort Method: top-N heapsort  Memory: 25kB
        ->  Nested Loop  (cost=1.28..288.71 rows=2 width=10) (actual time=0.061..694.762 rows=200002 loops=1)
            ->  Nested Loop  (cost=0.85..287.78 rows=2 width=4) (actual time=0.051..78.175 rows=200002 loops=1)
                    ->  Index Scan using tags_name_idx on tags  (cost=0.42..8.44 rows=1 width=4) (actual time=0.038..0.038 rows=1 loops=1)
                        Index Cond: (name = '2'::text)
                    ->  Index Scan using posts_tags_tag_id_idx on posts_tags pt  (cost=0.43..278.44 rows=89 width=8) (actual time=0.009..54.474 rows=200002 loops=1)
                        Index Cond: (tag_id = tags.id)
            ->  Index Scan using posts_pkey on posts  (cost=0.42..0.46 rows=1 width=10) (actual time=0.002..0.003 rows=1 loops=200002)
                    Index Cond: (id = pt.post_id)
Planning time: 0.562 ms
Execution time: 893.428 ms
(14 rows)


texts: ORA-00904 error while querying the Oracle database table

texts:
I am newbie with oracle DB and I need to List the Number of departments per region in the region table.

This is my SQL statement:

    select r.region_id, count(d.department_id) "Number of Department"
    from region r, department d
    where r.region_id = d.region_id
    group by r.region_id

I am getting this error:

  ORA-00904: "D"."region_id": invalid identifier

How can I correct this error?


texts: SQLite: What is the use of specifying data types?

texts:
As far as I am aware, SQLite does not enforce defined table data types — it has dynamic typing. For example, it allows you to insert strings into number fields, or to exceed the length of the string.

If that is the case, what is the point in defining data types in the CREATE TABLE statement?


texts: Is it safe to stop the shrinking of MSDB?

texts:
In a production server (Microsoft SQL Server 2014), the file MSDBData.mdf ballooned up to more than 450 GB, eating almost all the space in the server (only 38 GB remaining).

First thing, I created a backup of MSDB on a network share (via SSMS -> activities -> backup). Then I tried to reduce the size of the file (via SSMS -> activities-> Shrink -> file), but the shrinking is stalled and no disk space has been released. The shrinking dialog window is frozen since hours.

In the activities monitoring of SSMS, in processes monitoring the shrinking appears to be in SUSPENDED status.

In Event viewer there are several events

i.e.

(source MSSQLSERVER, eventid 847) beginning with

 1. Time-out occurred while waiting for latch: class 'FGCB_ADD_REMOVE'
 2. Time-out occurred while waiting for latch: class 'FCB'

The question: is it safe to close the dialog window of SSMS to stop the shrinking of MSDB, in order to carry out further investigation?


texts: How to correctly have binary logs stored.....Mysql/Maria

texts:
So I'm taking a full backup at 12AM each day.
At 2,4,6,8,10etc etc I'm taking incrementals.

Using Mariadb and maraibackup...

At each point I want to flush and purge the logs so I don't have to take the extra parts with me. at 11:59:59 I want to put all the incremental backups back into the main one and store it offsite with the approprriate binary logs... how can I do this?

-R.


texts:
I am having major trouble setting up up a database for an intranet web application. The users get the following errors:

  [SqlException (0x80131904): Login failed for user 'XXX\YYYY'.]

A few of the users that have admin rights to the server are able to get on.
The other users cannot.

We have IIS set up to use Windows Authentication because the application needs to pass the user's Windows login to the application.

I am basically doing what this user is doing in the following Stack Overflow question: How to get Windows user name when identity impersonate=“true” in asp.net?

I have added some images of the set up to better explain what it is I am trying to do:

The users are different users. We are not using Active Directory. The reason we went with this approach was so that we would not have to manage users accounts, as this tool is self-service.

When I set up IIS to use Anonymous Authentication and ASP.NET impersonation, the application works with the user we have in the Identity and the Application Pool settings. At that point all the users see that user id and the application works. This is not what we need to have happen. The tool needs to work for the user that access the site.

Basically any user on our network can pull up this site and use it (that's the goal at least). If they are logged into a PC on the network they browse to the site and the site picks up their id using the line of code:

var CurrentUser = System.Environment.UserName;

We pass that user's PC ID to a function that searches the database.

For example, I can access the tool on the web server through my IE browser. Other people with admin rights like mine can also get to the app on the web server. The General user can browse to the site and run anything that does not require access to the database, but as soon as they enter a page that uses the database to get data they get the SQL error.

The SQL Server database is on the same Windows Server as the web app run with IIS. They are both on virtual machines that are not here at this site, but I have admin rights to them.


texts: what is causing the reads in this configuration?

texts:
i have a setup here where we use a Solaris 10 x86, zpool for oracle12c-redo-log using mirrored disks and mirrored ZIL. logbias="latency", recordsize=128:

pool: xxxxxx01_redo00
 state: ONLINE
 scan: resilvered 25.1G in 0h1m with 0 errors on Thu Jan 22 15:24:41 2015
config:

    NAME                                       STATE     READ WRITE CKSUM
    xxxxxx01_redo00                            ONLINE       0     0     0
      mirror-0                                 ONLINE       0     0     0
        c5t60000970000295700789533031393742d0  ONLINE       0     0     0
        c5t60000970000295700860533033424537d0  ONLINE       0     0     0
    logs
      mirror-1                                 ONLINE       0     0     0
        c5t60000970000295700789533032304344d0  ONLINE       0     0     0
        c5t60000970000295700860533033423844d0  ONLINE       0     0     0

When i now check what the lgwr process does in terms of IO it is after all mostly writes to the redo-logs (when not switching logs).

Here is what i do not get:
The lgwr-process does writes in different sizes (which is okay) and i expect to see them actually happening on the ZIL-Devices until the txg_timeout is getting hit, then i expect to see the flush to the data-disks. Instead the picture is different.

Here is the iostat:

0.0  133.3    0.0 4067.1  0.0  0.1    0.0    0.5   0   7 c5t60000970000295700789533032304344d0
0.0  133.8    0.0 4077.1  0.0  0.3    0.0    2.2   0  29 c5t60000970000295700860533033423844d0
44.6    0.0 5708.7    0.0  0.0  0.1    0.0    2.0   0   9 c5t60000970000295700789533031393742d0
33.6    0.0 4297.6    0.0  0.0  0.1    0.0    3.3   0  11 c5t60000970000295700860533033424537d0

as you can see, the first two disks are the ZILs, the other two are the actual disks with the Data.

checking the lgwr from syscall-level with:

./rwsnoop -p <PID>
 UID    PID CMD          D   BYTES FILE
708  16559 oracle       W     512 /export/zones/xxxxxx01/root/opt/app/oracle/redo00/XXXX0/XXXX0_redo07.rdo
708  16559 oracle       W   58368 /export/zones/xxxxxx01/root/opt/app/oracle/redo00/XXXX0/XXXX0_redo07.rdo
708  16559 oracle       W    2560 /export/zones/xxxxxx01/root/opt/app/oracle/redo00/XXXX0/XXXX0_redo07.rdo
708  16559 oracle       W   26112 /export/zones/xxxxxx01/root/opt/app/oracle/redo00/XXXX0/XXXX0_redo07.rdo
708  16559 oracle       W   69632 /export/zones/xxxxxx01/root/opt/app/oracle/redo00/XXXX0/XXXX0_redo07.rdo

here we can see that the lgwr-process does its io with different blocksizes.

now from a devices-pov (of the Data-Devices not the ZIL)

./iosnoop -d <sd-dev>
UID   PID D    BLOCK   SIZE       COMM PATHNAME
708 16559 R 29073760 131072     oracle <none>
708 16559 R 29074016 131072     oracle <none>
708 16559 R 29073504 131072     oracle <none>
708 16559 R 29073760 131072     oracle <none>
708 16559 R 29073504 131072     oracle <none>
708 16559 R 29073760 131072     oracle <none>
708 16559 R 29074784 131072     oracle <none>
708 16559 R 29074784 131072     oracle <none>
708 16559 R 29074784 131072     oracle <none>
708 16559 R 29074272 131072     oracle <none>
708 16559 R 29074272 131072     oracle <none>

this seem to be clearly zfs 128k reads but why so they happen?
is it just because the recordsize=128 and zfs collecting the writes and because of the c-o-w it needs to read?

I would appreciate to dig deeper with you all or just be enlightened ;-)

Cheers
B.


texts: ms-access: issues with form and subform interaction

texts:
There is a main form bound to the main table, public_OUT_Detalle_PMC. And there is a form bound to a smaller table, public_ficha_negocio, which is a selection of fields in the bigger table. This second form is used as a subform, and I want to save the edits made to the subform onto the main table.

I have tried to use a simple SQL Update query, but for some unkown reason I can't make it work and it is driving me crazy.

    Dim db As DAO.Database
    Dim qdfFN As DAO.QueryDef
    Dim rsFN As DAO.Recordset
    Set db = CurrentDb
    Set qdfFN = db.CreateQueryDef("")
    qdfFN.sql = "select * from [public_ficha_negocio] where [Codigo_RQM_Necesidad]='" & Me.Codigo_filtro.value & "';"
    qdfFN.ReturnsRecords = True
    Set rsFN = qdfFN.OpenRecordset
    Dim qdf As DAO.QueryDef
    Dim sSQL As String
    sSQL = "UPDATE [public_OUT_Detalle_PMC] SET " & _
              {a lot of fields are updated, I cut them out of the paste for simplicity}
              "WHERE [public_OUT_Detalle_PMC].[Codigo_RQM_Necesidad] = '" & rsFN!Codigo_RQM_Necesidad & "');"
    Set qdf = db.CreateQueryDef("", sSQL)
    qdf.Execute dbFailOnError
    qdf.Close
    qdfFN.Close
    rsFN.Close
    Set qdfFN = Nothing
    Set rsFN = Nothing
    Set qdf = Nothing

The query qdfFN is used to retrieve the record of the smaller table which data I want to "passthrough" to the bigger table. The query qdf is the one that, either doesn't work or I am not understanding how to use these vba objects.

I have used Debug.print to check the values of rsFN and they are correct, I mean, the values I want to pass onto the bigger table are there! This is cracking my head


texts: Expressing the largest floating point value in PostgreSQL?

texts:
How to express the largest floating point value (for FLOAT8) in PostgreSQL?
I need it to replace 'Infinity'::FLOAT8 during conversion to jsonb field values, because json does not allow NaN and infinity values.
C++ has numeric_limits<double>::max(). Is there something equivalent in PostgreSQL?
(I'm using the latest PostgreSQL 11/12).


texts: Best way to linking different entities to a single table

texts:
I'm running for office and have created a web app for tracking my door knocks to voters at their homes. The database contains a table called voters that contains all the necessary information about voters in my community. I'm using mysql.

I'd like to add a new feature to track donors to my campaign. Not all of these donors live in the community and do not vote in the district where I'm running for office. I do not need to track the same kind of info for these individuals as I do for the voters so I'm going to place these individuals into a table called nonvoters.

Now, the individuals in my voters table can also make donations, and I want to track those as well.

Finally, I'd also like to track donations from organizations, so I also need an organizations table for storing information about the organizations.

To track the donations from both voters, nonvoters and organizations, I want to set up a new table called donations. This table would contain the appropriate details about each donation.

But I'm uncertain as to what the best structure is for linking the donations table to the voters, nonvoters, and organizations table should be. If I create a column called donor_id in the table to key it to the donors information, there's no way to know which table that ID refers to. So do I set up three columns, nonvoter_id, voter_id and org_id and insert the ID into the applicable column depending on whether the donor is a voter? This seems weird.

Or maybe I create a new column in each of the three tables called donor_id that I can use to link my data to the donations table. If I went this route, it seems like I'd have to do a some behind the scenes work to ensure the donor_id was unique and was keyed to the data inside the donations table.

Or maybe there are other approaches I'm not familiar with. Any guidance is appreciated.


texts: PostgreSQL - insert/update violates foreign key constraints

texts:
I am a new one in postgreSQL. I have 3 tables, one table references the other 2 table's primary keys. But I couldn't insert data into the Table3. See the code below :

DROP TABLE Table1 CASCADE;
CREATE TABLE Table1(
  "DataID" bigint NOT NULL DEFAULT '0',
  "AdData" integer DEFAULT NULL,
  PRIMARY KEY ("DataID")
);

DROP TABLE IF EXISTS Table2 CASCADE;
CREATE TABLE Table2 (
  "Address" numeric(20) NOT NULL DEFAULT '0',
  "Value" numeric(20) DEFAULT NULL,
  PRIMARY KEY ("Address")
);

DROP TABLE IF EXISTS Table3 CASCADE;
CREATE TABLE table3 (
  "ID" bigint NOT NULL DEFAULT '0',
  "DataID" bigint DEFAULT NULL,
  "Address" numeric(20) DEFAULT NULL,
  "Data" bigint DEFAULT NULL,
   PRIMARY KEY ("ID"),
   FOREIGN KEY ("DataID") REFERENCES Table1("DataID") on delete cascade on update cascade,
   FOREIGN KEY ("Address") REFERENCES Table2("Address") on delete cascade on update cascade
);

  ERROR:  insert or update on table "Table3" violates foreign key
  constraint "Table3_DataID_fkey" DETAIL:  Key (DataID)=(27856) is not
  present in table "Table1".

When I tried to insert data into the 3 tables, an error occured. I referred the postgreSQL documentation and changed my code as follows: (Unfortunately it showed another error)

DROP TABLE Table1 CASCADE;
CREATE TABLE Table1(
  "DataID" bigint NOT NULL DEFAULT '0',
  "AdData" integer DEFAULT NULL,
  PRIMARY KEY ("DataID")
);

DROP TABLE IF EXISTS Table2 CASCADE;
CREATE TABLE Table2 (
  "Address" numeric(20) NOT NULL DEFAULT '0',
  "Value" numeric(20) DEFAULT NULL,
  PRIMARY KEY ("Address")
);

DROP TABLE IF EXISTS Table3 CASCADE;
CREATE TABLE table3 (
  "ID" bigint NOT NULL DEFAULT '0',
  "DataID" bigint DEFAULT NULL REFERENCES Table1 ON DELETE RESTRICT,
  "Address" numeric(20) DEFAULT NULL REFERENCES Table2 ON DELETE CASCADE,
  "Data" bigint DEFAULT NULL,
   PRIMARY KEY ("ID"),
   PRIMARY KEY("DataID", "Address")
);

  ERROR:  multiple primary keys for table "Table3" are not allowed LINE
  65:   PRIMARY KEY("DataID", "Address")

Please help me... How I can create the reference?

I changed the ID as UNIQUE and removed the line PRIMARY KEY ("ID"). At that time it shows another error like :

  ERROR:  duplicate key value violates unique constraint "Table3_pkey"


texts:
I have a backup which in file explorer is roughly 160GB.

If I run

select sum((size*8)/1024) From sys.database_files where type=0

I get a result of 169493, fine.

When I try to restore this database to another server (the old DBA set up a job which restores the database automatically each morning from the latest backup, then runs DBCC CHECKDB on it) it fails. If i try RESTORE VERIFYONLY it turns out I need 288GB in order for this to work. Why? The backup is not compressed.

Thanks


texts: Updating data when data is from an external source

texts:
A government organization provides data in a series of files which can be re-engineered and imported into a database. The files are updated monthly. I have been able to reengineer the database in MySQL and import the data. I've also created additional tables on the database and added my own data. My issue is I'm unsure as to how to handle updates to the database.

How do I import the data every month and sync it with the database from the previous month, which would include the tables that I added?


texts:
I really don't understand promises about waits in case of concurrent data access.
All manuals operate by term lock. No one explains that lock probably causes SQL clients waiting for lock acquisition on the server side (and it can take seconds / minutes / infinity)?
Are there cases when locks don't cause waits? I can imagine:

probably READ UNCOMMITTED in some situations, like there are no DDL?
error is reported without waiting...
no one waits but first who does commit wins, other fail on their commit

I reviewed JDBC API: it mentions literally:

SQLTimeoutException - when the driver has determined that the timeout value that was specified by the setQueryTimeout method has been exceeded and has at least attempted to cancel the currently running Statement`

According to the docs timeout is detected on the client side. Widely used client API doesn't have influence on server side timeouts (including caused by locks). Probably you can set some proprietary connection properties to influence DB behavior.
I see some proprietary SQL extensions, like:

https://docs.microsoft.com/en-us/sql/t-sql/statements/set-lock-timeout-transact-sql SET LOCK_TIMEOUT timeout_period
https://www.postgresql.org/docs/13/runtime-config-client.html set lock_timeout ms & set statement_timeout ms

For me DB waits due to locks is such a grey area. Does a lock mean a wait?


texts: SQL3550W: ... target column has been defined as GENERATED ALWAYS

texts:
I am trying to export + import data from a table in a db2 database to another.
But while running the export command I am getting the following error.

SQL3550W  The field value in row "26" and column "17" is not NULL, but the
target column has been defined as GENERATED ALWAYS.

and

SQL3185W  The previous error occurred while processing data from row "26" of
the input file.

Why is this error coming and how can I fix it.

The Table

CREATE TABLE COMPANY_CLIENT (
        CLIENTGUID CHAR(36) NOT NULL,
        TYPECODE VARCHAR(2) NOT NULL,
        COMPANYNAME VARCHAR(150),
        LASTNAME VARCHAR(150),
        FIRSTNAME VARCHAR(150),
        MIDDLEINITIAL VARCHAR(150),
        PREFIX VARCHAR(10),
        SUFFIX VARCHAR(10),
        SEX VARCHAR(10),
        DATEOFBIRTH TIMESTAMP,
        DATEOFDEATH TIMESTAMP,
        TAXID VARCHAR(60),
        EMAIL VARCHAR(300),
        XMLDATA CLOB(1073741824) INLINE LENGTH 276,
        UPDATEDGMT TIMESTAMP,
        LEGALRESIDENCECOUNTRYCODE VARCHAR(3),
        LASTNAME_LOWER VARCHAR(150) GENERATED ALWAYS AS ((LOWER(LASTNAME))),
        FIRSTNAME_LOWER VARCHAR(150) GENERATED ALWAYS AS ((LOWER(FIRSTNAME))),
        COMPANYNAME_LOWER VARCHAR(150) GENERATED ALWAYS AS ((LOWER(COMPANYNAME))),
        TAXID_LOWER VARCHAR(60) GENERATED ALWAYS AS ((LOWER(TAXID))),
        ALTERNATENAME1 VARCHAR(150),
        ALTERNATENAME2 VARCHAR(150),
        ALTERNATENAME3 VARCHAR(150),
        ALTERNATENAME4 VARCHAR(150),
        ALTERNATENAME5 VARCHAR(150),
        ADDITIONALPREFIX VARCHAR(10),
        ADDITIONALSUFFIX VARCHAR(10),
        TAXIDTYPE VARCHAR(10),
        TITLE VARCHAR(10),
        MARITALSTATUS VARCHAR(10),
        BIRTHCOUNTRYCODE VARCHAR(3),
        CITIZENSHIPCOUNTRYCODE VARCHAR(3),
        BIRTHREGIONCODE VARCHAR(10),
        PRIMARYPHONE VARCHAR(60),
        TEXTFIELD1 VARCHAR(765),
        TEXTFIELD2 VARCHAR(765),
        CHECKBOX1 VARCHAR(9),
        CHECKBOX2 VARCHAR(9),
        RADIO1 VARCHAR(10),
        RADIO2 VARCHAR(10),
        COMBO1 VARCHAR(10),
        COMBO2 VARCHAR(10),
        DATE1 TIMESTAMP,
        DATE2 TIMESTAMP
    )


texts: RMAN backup set, nothing else, how can I duplicate?

texts:
I have what appears to be an RMAN backup set, it has been multiplexed in 32 files, each containing 2-3 dbf's inside. The backupset refers to an SPFILE file, but it was not included in the set, the backup piece containing just that file was not included.

I have no catalog, no infrastructure, and no way to connect to original database.  In addition the creators of the backupset have been evading my requests, I'm not sure why exactly but it appears to be business reasons.  In other words this is what I have, I cannot get anything better than what I've got right now.

I have an Oracle enterprise server, with the data in a backup directory.
What is the proper way to proceed?  Is it possible to duplicate from an auxiliary backup data TO a fresh blank database?  Meaning, is it possible to use an auxiliary as a primary?


texts: Do any of the PostgreSQL releases for Windows support automatic updates?

texts:
PostgreSQL has two distributions for Windows,

EnterpriseDB
BigSQL

Do either of these Windows distributions support automatic updates for the term of support? Something that looks for the updates online, downloads them, and installs them.


texts: How to optimize mysql table of 2 billions rows?

texts:
We have a table for logging actions. The volume added up pretty fast. After about 3-4 months, we got 600M rows, we need to store the data for 1 year at least, so it is expected to host 2-3 billions rows.

The structure is not very complicated:

    CREATE TABLE `site_statistic` (
     `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
     `entityType` varchar(50) NOT NULL,
     `entityId` int(10) unsigned NOT NULL,
     `entityCount` int(10) unsigned NOT NULL DEFAULT '1',
     `timeStamp` int(10) unsigned NOT NULL,
     PRIMARY KEY (`id`),
     KEY `entity` (`entityType`,`timeStamp`,`entityId`)
    ) ENGINE=MyISAM CHARSET=utf8

Sample data:

when a user logs in:
entityType = "user.login" , entityId = that user Id , entityCount= 1 most of the time
when a product is added:
entityType = "product.add" , entityId = that productId , entityCount= 1
when a product is clicked:
entityType = "product.clicked" , entityId = that productId , entityCount= 1
when an album is liked:
entityType = "album .liked" , entityId = that album id..
so on and so forth.

Currently, data insert is not a problem (yet). But reading the data is very slow. It took 90+ seconds to query product activities for a given period of time:

   SELECT
            `entityType`,
            DATE_FORMAT(FROM_UNIXTIME(`timeStamp`), "%e") AS `category`,
            SUM(`entityCount`) as `count`
        FROM
            `site_statistic`
        WHERE
            `entityType` IN ('product.displayed','product.clicked','product.add')
                AND
            `timeStamp` >= 1527267600
                AND
            `timeStamp` <= 1527872399 AND entityId = 12807
        GROUP BY
            `entityType`,
            `category`;

Mysql crashed (or rather not responding) from time to time when many statistic reports are required at the same time.

Deleting the data (related to a product for example) is also very slow. We need to delete to save space...

What can we  do to improve it, apart from disable real time report and send later by email?

P.s.: Our client can be considered small (few guys hosting a website with modest profit) not some crazy enterprise solution.


texts: PgBouncer logging details for `query_wait_timeout` error

texts:
Regarding this type of error in PgBouncer log:

2020-03-10 03:46:01.730 EDT [25305] LOG C-0xa5cf00: testdb/testdb_user@127.0.0.1:49214 closing because: query_wait_timeout (age=120s)
2020-03-10 03:46:01.730 EDT [25305] WARNING C-0xa5cf00: testdb/testdb_user@127.0.0.1:49214 pooler error: query_wait_timeout

Is there any possibility to see (or to log) the query that triggered the error? What are the most frequent causes for this error? a stopped server, a busy server?

UPDATE: the log file from PostgreSQL does not show any error at 2020-03-10 03:46:01


texts: Replication of database used by two programs

texts:
I have a peer to peer transactional replication set between two identical databases, each one of the databases is being used by a different program on  two different servers. Since we implemented the replication I keep getting a PK violation on a table both programs update although the insert statement doesn't enter a primary key and lets SQL server take care of it. In this case the table name is Statistics:
Violation of PRIMARY KEY constraint 'PK_Statistics'. Cannot insert duplicate key in object 'dbo.Statistic'.

My insert statement:

INSERT INTO [StackExchange].[dbo].[Statistic]
       ([UserId]
       ,[CreateDate]
       ,[IsError]
       ,[CustomData]
       ,[ModuleId]
       ,[StatisticTypeId])
 VALUES
       (19,GETDATE(),'true','test replication',1,1)

Also, the event viewer in the servers is filled with conflicts data:

This is the frequent error I get on server 2:

  A conflict of type 'Update-Delete' was detected at peer 2 between peer 1 (incoming), transaction id 0x000000000031dea3  and peer (null) (on disk), transaction id (null)

This the the same transaction error I get on server 1:

  A conflict of type 'Delete-Update' was detected at peer 1 between peer 2 (incoming), transaction id 0x0000000000245950  and peer 1 (on disk), transaction id 0x000000000031dea3

What would you advise me to do in that case?

Thank you!


texts: measure the traffic throughput on mysql table

texts:
In order to split mysql database into two different databases on different hosts. I would like to know few things:

what is the size of the tables.
what is the traffic throughput to those tables.

Size of the tables can be determined easily, but how to find traffic throughput to tables.

How to identify how many data was retrieved from specific table?


texts: SQL Server refuses to connect from time to time

texts:
I get this error like 5 times a day. It does not happen always and we cannot really reproduce it.
So is there any common knowledge as to why SQL Server sometimes refuses to connect and sometimes allows it?
Error #1070 is our own error code.

SQLState 42000
Error Code 4060

...and ...

SQLState 28000
Error Code 18456

...are pretty much standard error codes that do not help to solve this mystery.

I use PHP and AdoDB as a framework.
Here is a Link that adresses this issue, but i do not see that this will solve the "from time to time" aspect.
https://support.microsoft.com/de-de/help/2121736/error-message-when-you-try-to-authenticate-an-odbc-connection-to-your


texts: Should I rely on Database Engine Tuning Advisor for creating indexes?

texts:
How should I rely on SQL Server 2012 Database Engine Tuning Advisor for creating indexes and statistics? I find more often than not, it generates overly complex indexes that (I think) would be more costly in index maintenance than the benefit of having this index.

If I cannot solely rely on Database Engine Tuning Advisor, is there a straightforward method? A book an article perhaps?

The missing index DMVs are not particularly useful because I am trying to think of indexes for brand new tables.


texts: Lock Pages in Memory keep SQL database engine stuck at allocating 60MB ram?

texts:
I'm using Window Server 2003 Enterprise, Microsoft SQL Server 2005 Standard Edition.

The problem is the SQL Server memory gets stuck at allocating 1.7GB RAM max even though I have 11GB of RAM left.

Following the advice from a post in this thread, in which the thread starter had the same issue, I tried adding /PAE to my boot.ini, adding the network service account which is running sqlserv.exe to Lock Pages in Memory user right, and reconfiguring SQL to use AWE to allocate memory.

But I had no luck. After restarting the server, the SQL database engine cannot allocate more than 60MB RAM, which is a terrible failure as I expected.

So after that, I must restore the Lock Pages in Memory setting: remove the network service account from Lock Pages in Memory option, restart my server, and I'm back to the first problem. The SQL Server database engine is stuck at allocating 1.7GB RAM.

So the Lock Pages in Memory keeps the SQL database engine stuck at allocating 60MB RAM? And how can I resolve the first problem now?


texts:
Tables X and Y are given as below :

X (A, B, D, E, F, G, H, J) with A and B as primary key
Y (K, L, M, N, O, P, Q, R) with K and L as primary key

Now we need to decompose these tables into tables in the third normal form using the functional dependencies specified below:

A   ->  G, H
B   ->  D, E, F
E   ->  F
H   ->  G
L   ->  N, O, P, Q
N   ->  Q

I need to specify primary keys and foreign keys also. Please help to solve this problem.

I am newbie to normalization. So please help to solve this problem.If I normalize to 2NF I loose some relations which doesn't suit to 3NF. So I am confused. Any help will be appreciated. Because to convert to 3NF I need to go through 2NF too.

I had changed it to 3NF. Please help in finding if am right in doing it

R1 (E, F)
R2 (H, G)
R3 (A, B, L)
R4 (A, H)
R5 (B, D, E)

R6 (N, Q)
R7 (K, L, M, R)
R8 (L, N, O, P)


texts: PostgreSQL: Valid variable names for functions

texts:
I cannot find any definitive information on what constitutes a valid variable name in a PostgreSQL function.

I am assuming the function is written using plpgsql. I know I can use letters, numerals (apart from the first character) and underscores. Is there anything else?

Where is this documented?


texts: Data archival strategy that handles schema changes?

texts:
I am working with a legacy application that has about ten years of customer data. A majority of this data is not used in day-to-day operations but there is a business requirement to have the data available for the customer until his retirement from the system.

We are exploring archiving the data to a copy of the existing database and then purging the records from production past a certain point in time.

My concern is that the database undergoes a substantial schema change every quarter due to development efforts.

If I were to archive a mirror copy of the data, would I also need to apply every single change script that goes against production?

Are there any alternative strategies? It seems like no matter what form of storage you choose (i.e. database, flat files, xml), you will always need some way of mapping older schemas to newer ones.


texts: How to pass credentials to a linked server in another domain?

texts:
I am trying to link two SQL Servers (2008R2 and 2014) which are running in two separate (!) Active Directory domains.

The problem is that I can login into the remote server only with a Windows Account from the remote domain. So the challenge is to pass on the credentials to the remote server, but here I am stuck.

Local Domain: dom8, Server: dom8\sql2008, SQL (!) Login: localuser
Remote Domain: dom14, Server: dom14\sql20148, Windows (!) Login: dom14\import

Server dom8\sql2008 wants to connect to dom14\sql2014 in order to pull some data.

Here is what I have tried on the local server:  (logged into dom8\sql2008 as sa) :

-- Create a credential for the remote Windows login:
create credential cred_import WITH IDENTITY= 'dom14\user14',
       SECRET = 'password' ;

-- Alter the local SQL login and add the created credential:
alter login local_user with credential = cred_import ;

-- Create the Linked Server entry:
exec sp_addlinkedserver
    @server='dom14\sql2014',
    @srvproduct='SQL Server'

-- Add the credential to the linked server:
exec sp_addlinkedsrvlogin
    @rmtsrvname ='dom14\sql2014',
    @useself = 'FALSE',
    @locallogin=local_user,
    @rmtuser = [cred_import],  -- trying to pass on the credential
    @rmtpassword = NULL

However, it does not work. Whenever user localuser tries to connect to the remote server through the local server, then it gets an error login failed

The question is: How to properly pass on the credential to the remote server link? I am stuck.


texts:
I have 2 servers, one with SQL standard and the second one is express.
I wanted to know if there is any tool or any way to synchronized the databases and making it work like replication without the replication (because i don't have the agent in the express edition of corse).
Thanks.


texts:
I have a query that is taking a long time to execute minutes and more. The two tables orders and tickets have around 150k records. This is the query:

SELECT
  orders.id,
  count(distinct tickets.id) as attached_tickets
from
  orders
  LEFT JOIN tickets ON tickets.order_id = orders.id
GROUP BY
  orders.id
LIMIT 1;

I have found that by removing the ORDER BY statement it speeds it up to seconds. These are the results from the EXPLAIN

+----+-------------+---------+------------+-------+---------------+---------+---------+------+--------+----------+----------------------------------------------------+
| id | select_type | table   | partitions | type  | possible_keys | key     | key_len | ref  | rows   | filtered | Extra                                              |
+----+-------------+---------+------------+-------+---------------+---------+---------+------+--------+----------+----------------------------------------------------+
|  1 | SIMPLE      | orders  | NULL       | index | PRIMARY       | PRIMARY | 4       | NULL | 121291 |   100.00 | Using index; Using temporary; Using filesort       |
|  1 | SIMPLE      | tickets | NULL       | ALL   | NULL          | NULL    | NULL    | NULL |   7347 |   100.00 | Using where; Using join buffer (Block Nested Loop) |
+----+-------------+---------+------------+-------+---------------+---------+---------+------+--------+----------+----------------------------------------------------+

And yes i have indexes in both tables by id. Is there any way I can speed up my query?


texts: Error Code: 1215. Cannot add foreign key constraint

texts:
I'm creating a DBSM on Mysql Workbench, but when Itry creating the last table i get an error because it can't add the foreign key, but i can't understand why since the child key has the same data type and collation as the parent key and the name isn't repeated.
That's the code for the two tables:
CREATE TABLE PRESTITO(
MATRICOLA_U INT,
CODICE_LIB SMALLINT(4),
DATA_P DATE,
DATA_R DATE,
N_DIP SMALLINT(2),

PRIMARY KEY(CODICE_LIB),

FOREIGN KEY(MATRICOLA_U) REFERENCES UTENTI(MATRICOLA),
FOREIGN KEY(CODICE_LIB) REFERENCES LIBRI(CODICE_L)
);

CREATE TABLE DIPARTIMENTO(
NUMERO_DIP SMALLINT(2),
NOME_DIP VARCHAR(50) NOT NULL,
INDIRIZZO_DIP VARCHAR(50),

PRIMARY KEY(NUMERO_DIP),
FOREIGN KEY (NUMERO_DIP) REFERENCES PRESTITO(N_DIP)
);

Is there someone able to help?
Thank you very much!


texts: Database design help. many-to-many relationship

texts: I have four columns. All columns are varchar fields. Three of them are independent and distinct fields having many to many relation between them and the fourth one is dependent on the combination of first three. so for all the combinations of three columns there is different value for the fourth column. Now I want a design for data like this. Any help is appreciated.


texts: Covering Index Not Being Used Effectively

texts:
I have a unique composite index on three fields (hash, is_customer, user_id) in a table a.
And queries that go like this:
select `a`.`id`, `hash`, `is_customer`, `user_id`
from `a`
where `hash` = 34605915
limit 1;

When I do explain, it identifies properly as:
Using where; Using index |

However the above query showed up in a slow query log as Full_scan: Yes and query time of over 5.083821 seconds.
The dump of the explain is as follows:
| {
  "query_block": {
    "select_id": 1,
    "table": {
      "table_name": "a",
      "access_type": "index",
      "possible_keys": ["a_hash_unique"],
      "key": "a_id_hash_is_customer_user_id_unique",
      "key_length": "138",
      "used_key_parts": ["id", "hash", "is_customer", "user_id"],
      "rows": 2639299,
      "filtered": 100,
      "attached_condition": "db.a.hash = 34605915",
      "using_index": true
    }
  }
} |

The InnoDB pool buffer pool size is set to 21G (actual pool data size is about 18.1G) and pool instances as 21. What could be the explanation for the optimized missing this query index? In the process, I think the same issue causes the system to run into deadlocks during DB writes.
Below is the table structure dump:
 a | CREATE TABLE `a` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` text COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `anum` text COLLATE utf8mb4_unicode_ci NOT NULL,
  `currency` char(3) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'NGN',
  `hash` char(32) COLLATE utf8mb4_unicode_ci NOT NULL,
  `bid` int(10) unsigned DEFAULT NULL,
  `user_id` int(10) unsigned DEFAULT NULL,
  `a_type` int(10) unsigned DEFAULT NULL COMMENT '1=Current, 2=Savings, 3=Fixed, 4=Overdraft, 5=Loan, 6=Joint, 7=Other',
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  `deleted_at` timestamp NULL DEFAULT NULL,
  `is_customer` tinyint(1) NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`),
  UNIQUE KEY `a_hash_unique` (`hash`),
  UNIQUE KEY `a_id_hash_is_customer_user_id_unique` (`id`,`hash`,`is_customer`,`user_id`),
  KEY `a_bid_index` (`bid`),
  KEY `a_user_id_index` (`user_id`),
  CONSTRAINT `a_bid_foreign` FOREIGN KEY (`bid`) REFERENCES `b` (`id`),
  CONSTRAINT `a_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6004761 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci |


texts: Parameter triggers different execution plan, but why?

texts:
I have a table

CREATE TABLE [dbo].[Numbers]
    (
        [Date] [date] NULL,
        [Time] [time](3) NULL,
        [Value] [char](10) NULL
    )

and the table has > 10 Billion rows, therefore it is partitioned by month and has a clustered index on [Date], [Time] ASC

Now I use a table valued function that reads this data:

SELECT *
FROM [dbo].[QueryNumbers] ('2012-10-08','2012-10-08','07:00:00.000','08:00:00.000')

This returns me around 6000 rows in 1 second

However when I do the same like this:

declare

@StartDate date,
@EndDate date,
@StartTime time(3),
@EndTime time(3),

SET @StartDate = '2012-10-08';
SET @EndDate ='2012-10-08';
SET @StartTime ='07:00:00.000';
SET @EndTime = '08:00:00.000';

SELECT *
FROM [dbo].[QueryNumbers] (@StartDate,@EndDate,@StartTime,@EndTime)

The same query takes 3 minutes (which is a desaster), I played a bit with the parameters and it seems that the time parameter trigger the different behaviour. Anybody has a hint for me what is going wrong here ?


texts: Problem with Database for survey Questionnaires

texts:
Bellow I am showing a sample survey question for woman. I need your help to creating a Database.

Description:

Top part of the survey Questions are Project Code, Name... etc which
is understandable.

Questions have some groups with No and Name, Here I am showing 2 groups.
Each group have some questions.
Each question has 4 columns i.e No, Questions, Response and Skip.
Question 1.01 to 1.04: Data entry operator will input these data and Skip is Blank.
Question 1.05 Operator will input one number from 1 to 4.
If Question 1.05 answer is 1 then it will Skip question 1.06 to 1.08 and answer 2.01 no question. Skip column is showing Quesion no.
If Question 1.05 answer is 4 then Operator will type some text data.
Hard part of the Question, No 2.02, Inside this question there is another A to E sub question and their answer will be 1 or 2.
10.Question 2.02, sub part no D. Operator will input 1 or 2 and also input some text.

Hope I can make you understand for the survey. I need to create a database for this survey questions. Hope I can get your expart opinion.
Thanks in advance!

I did some homework and I am attaching the db diagram but it is not suitable for Qs. no 2.02. I need a better solution.


texts:
I need to calculate the total number of rows based on a set of criteria:

SELECT COUNT(*) FROM Users WHERE hasPhoto=1 AND userStatus='a';

The above query takes 0.10 sec to run on a MyISAM table with 500,000 rows despite having a composite index on columns userStatus and hasPhoto.

Is there any better way to improve counting performance short of writing the total count in a separate table?


texts: Postgres requires commit or rollback after exception

texts:
Just trying to understand the thinking here...

This bug explains that with other databases, you are allowed to run additional commands after a SQLException occurs. It was written in 2001 but has never been touched.  We're working with Postgres and SQL Server, and so now we're bumping into this issue.

Suppose you have:

start transaction;
create table test(id int primary key);
insert into test values (1);
commit;

-- Following statement throws a SQLException(duplicate key) in
-- PG, SS and ORacle
insert into test values (1);

-- Following statement behaves differently for different DBMS:
-- SS and OR: No error...statement runs fine
-- PG: Another SQLException thrown...must rollback or commit
insert into test values (99);

Can anyone help me understand why Postgres disallows this?  Or even better, is there a connection flag to change the behavior?  (I looked and could not find anything.)

I added the "start transaction" bit above to indicate that it was not an auto-commit tx.

This is a Java app, so we're using the PG 9.0 JDBC driver. I have tested the above through a query tool using the same driver to take our application out of the picture.  The second insert statement above yields:

  ERROR: current transaction is aborted, commands ignored until end of transaction block
  [SQL State: 25P02]


texts: Getting Transient connection Error while connecting applications to postgresql using pgbouncer

texts:
We are connecting PostgreSQL using pgbouncer from application.  However, it is not working.  it is giving transient error while connecting.
I can provide other parameter details
An exception has been raised that is likely due to a transient failure. Stack trace : at Npgsql.EntityFrameworkCore.PostgreSQL.Storage.Internal.NpgsqlExecutionStrategy.ExecuteAsync[TState,TResult]


texts: Insert iterated values from one table to another in OracleDB

texts:
I'm pretty new in Oracle universe and I need help. I have to iterate through Users Table and insert these 3 specific rows into Task_Group Table. The hard thing to do is to check if these rows even exist (tables are just samples, I need to iterate through a table with a lot of rows), and then insert them only in rows and set Company_Number to: 22.

Please help, guys!


texts:
I am currently building out an application that will contain many users, two roles and two groups. Logically we want users to be able to be administrators of other users within the two groups.

User Table

UserID         varchar    PK
DisplayName
Email
Role           int        FK `role`.`RoleID`

DATA [jschmo,Joe Schmo,joe@schmo.com,1]

Role Table

RoleID         int        PK     AI
RoleName       varchar

DATA [1=Master,2=Admin,3=User]

Group Table

GroupID        int        PK     AI
GroupName

DATA [1=Marketing,2=Infrastructure]

A user could potentially be an admin of both groups. I don't see the option to multi-select foreign keys but as I understand it, having a comma-delimited list in a column is a bad idea and I should use multiple records but I don't like the idea of having multiple records for each user.

Q: How would I keep track of which groups a user is an admin of?


texts: Is there a way to determine automatic storage table spaces?

texts:
First off, we are on both Windows and AIX and are using DB2 ESE 9.7 FP 4.

I am trying to determine dynamically whether or not table spaces are set up via automatic storage or not (so we can dynamically script how to lower the high water mark when necessary). I can see whether they are defined SMS or DMS "under the covers" via statements like db2 list tablespaces or db2 list tablespaces show detail.

I have also issued queries against the system catalog tables and views (ie, select * from syscat.tablespaces and select * from sysibm.systablespaces).

All I can tell is that the tablespace is defined as SMS or DMS. How can I tell whether or not the tablespace is configured for automatic storage? I know there must be a way I can do it dynamically, because if I use IBM Data Studio and visit the tablespaces "view" and click on properties, I can see whether or not the table space is automatic storage or not. I am assuming there must be a configuration or a query behind that.

Just wondering, how do we figure this out?


texts: Is there any way to keep rows with same id sequential even if they are not stored sequentially?

texts:
Edit: I think i used the wrong word here. By "sequential" i don't mean that everything aid 1 enters has to be in the order of the time at which they enter it.

What i mean is that they should be clubbed together in the table that's it.

Suppose there are tables A and B.

A is linked to table B that stores data which may be repeated for a user. Each user may have 0 or 1 or 2... or n(limit) number of data is table B.

But the data is not entered sequentially because it totally depends on if/when the user chooses to enter it, how many data they enter.

So if user with aid 1 enters "lol" today, and then user with aid 2 enters ":)" tomorrow,and then 1 enters xd day after tomorrow then the data will be stored in the same manner i.e.

| bid        | data3       |
|:-----------|------------:|
| 1          |        lol  |
| 2          |        :)   |
| 1          |        xd   |

Is there a better way to do this? Maybe the way i am storing data is wrong.

Or maybe the table can to optimized in some way like making the rows with same id to be sequential i.e. one after the other such that there is no row with a different id between 2 rows with the same id.