texts: Recreating a Postgres table with all cascading relations transferred

texts:
I'm trying to optimize the structure of a table. To do so, I've put the columns in the most optimal order possible and then ran this query (simplified):

INSERT INTO table_new ("id", "some_field", "some_other_field") SELECT "id", "some_field", "some_other_field" FROM table_current;

Then I simply rename the table and add all the same indexes and foreign keys.

ALTER TABLE "table_current" RENAME TO "table_old";
ALTER TABLE "table_new" RENAME TO "table_current";
ALTER TABLE "table_current" ADD PRIMARY KEY ("id") NOT DEFERRABLE INITIALLY IMMEDIATE;

This has worked great in the past, but in this particular case, there are a LOT of other tables with cascading foreign keys on this table.

So if I recreate and rename, then all the other tables still reference table_old in their foreign keys instead of the new table_current.

How do I do such a table migration like this without having to recreate the foreign keys on every other table as well?


texts: Postgres 9.4.5 small table grows into huge size - periodic VACUUM FULL needed

texts:
We are running a PostgreSQL database on Windows Server. The database version is 9.4.5 (it's been running stable for a long time now so we aren't updating it).

Some of our tables have millions of rows, and they are running fine.

What causes problems is a tiny little table that is only ~2000 rows.
This table is used to store current status reported from remote devices. These devices are sending their updated status every few seconds, so there are quite a few updates in that database each second. Nothing is being removed though, the rows are kept intact they are just being updated every time a device reports new status.

Some time ago I have noticed huge performance drop on our Apache server that uses this database. What I saw was that the status table was >3GB in size while having only 2000 rows. I read a lot on the internet, tried normal VACUUMs but they didn't do anything.

I finally went for VACUUM FULL as I saw that it is safe to use it on versions 9.0 and above. That really helped, the table size dropped just to a few KBs!

However, the problem is happening periodically every few days, and now when I try to run VACUUM FULL it takes a very long time, and our whole service is unusable during that time.

What could I do to prevent this from happening? Autovacuum is turned on, but it seems to do nothing in this particular case.

Output of VACUUM VERBOSE:

username=# vacuum verbose status;
INFO:  vacuuming "public.status"
INFO:  index "status_pkey" now contains 3295731 row versions in 11704 pages
DETAIL:  0 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.07s/0.01u sec elapsed 0.14 sec.
INFO:  "status": found 0 removable, 4472204 nonremovable row versions in 400557 out of 400558 pages
DETAIL:  4470176 dead row versions cannot be removed yet.
There were 23081 unused item pointers.
0 pages are entirely empty.
CPU 2.87s/0.70u sec elapsed 3.64 sec.
INFO:  vacuuming "pg_toast.pg_toast_16753"
INFO:  index "pg_toast_16753_index" now contains 0 row versions in 1 pages
DETAIL:  0 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO:  "pg_toast_16753": found 0 removable, 0 nonremovable row versions in 0 out of 0 pages
DETAIL:  0 dead row versions cannot be removed yet.
There were 0 unused item pointers.
0 pages are entirely empty.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
VACUUM

As  you can see, there are 0 removable row versions, despite the table has only 2039 physical rows.

SELECT n_live_tup, n_dead_tup
from pg_stat_user_tables
where relname = 'status';

 n_live_tup | n_dead_tup
------------+------------
       2039 |    4473416

At that moment,

SELECT pg_size_pretty(pg_total_relation_size('status'))

reported a size of 3221 MB, for only 2039 rows in the table. That is hilarious.


texts:
We have a database with flights, and in there, departureTime DATETIME field.

Extra info:

global.time_zone: Europe/Dublin
session.time_zone: Europe/Dublin
MySQL version: 5.7

Problem is, I need to save a flight from China at "2019-03-31 01:00", and server is saving "2019-03-31 02:00" because of the day saving time. In Dublin would be fine, but that flight is from China and should be saved properly.

So what would be the best here?,

Is there any way to ignore DST validations?.

Should I save all datetimes in UTC format and then show them in each timezone?

Should I use timestamp instead of datetime?

If I change server timezone to UTC, what would it happen with old records?


texts: Adding Timestamp to Exported Filename in SQL Server Management Studio

texts:
I am exporting to a CSV using a job with xp_cmdshell. I need the filename to include a timestamp. The code I currently have is...

EXEC xp_cmdshell 'bcp "SELECT * FROM dbo.tablename" queryout "C:\filename-timestamp.csv" -T -c -t, '

As you can see, I need the 'timestamp' to be replaced with the actual timestamp, and for the format to be .csv, how can I achieve this?


texts: Missing execution plans for stored procedures

texts:
What are the reasons for a plan to be missing from cache for stored procedures?

WITH RECOMPILE
Dynamic SQL
Encrypted code
Significant data changes
Update statistics
What else?

I've worked on 2 servers (SQL Server 2008 R2 and SQL Server 2012) recently that didn't have plans in cache for very resource-intensive stored procedures. Many, maybe all, of the statements inside the stored procedures also didn't have plans in cache. Some of the stored procedures execute pretty frequently like a few times per second.

No memory pressure whatsoever. One of the servers has far more hardware than is needed.

I thought the missing plans were due to temporary table creations in the middle of the stored procedures, but that appears to be old information from SQL Server 2000 or earlier. Starting with SQL Server 2005, the recompiles happen at the statement level for the statements after the DDL. Is that true in all cases or can it still happen on newer versions?

What else could be the culprit for the missing plans? I've skimmed a few articles on this topic, but nothing seems to fit.

Optimize for adhoc workloads is enabled on the server I am looking at this week. One of the stored procedures is only executed once a day. I do have the code for that one. I don't have the code for the one that's executing over 100 times per minute, but I can get it. I won't be able to post the code, but I can describe it in relation to my question.

I don't believe anyone is freeing the procedure cache or dropping clean buffers. This client is using Solarwinds DPA as one of their monitoring tools. DPA did capture one of the execution plans for the statement in the stored proc that gets called once a day. That statement has a massive amount of reads due to non-sargable WHERE clause. If DPA captured the statement, then it's an estimated plan and was in the plan cache at one time. Just isn't there when we are troubleshooting. I'll have them start logging sp_WhoIsActive to a table.

I'm using sp_BlitzCache. (I work for Brent Ozar Unlimited) This will show the plan for the entire stored procedure as well as the plans for the individual statements, if they exist. If they don't exist, it has this for a warning "We couldn't find a plan for this query. Possible reasons for this include dynamic SQL, RECOMPILE hints, and encrypted code." And that warning is on the statements too.

TF 2371 isn't in place. I'm looking at the wait stats. The server is pretty bored. PLE is over 130,000.

I now have the code for 2 more stored procedures. One of them is using dynamic SQL with exec (@sql) so that one we know why there isn't a plan for it. But the other one, and this is the one that's running over 100 times per minute, doesn't have anything out of the ordinary. The only thing that stands out in that one is that the temporary tables are being created in the middle of over 1000 lines of code. It does call a bunch of child stored procedures too.

Regarding Plan Caching in SQL Server 2008, I'm not seeing any literals >= 8k, but one of the stored procedures has a comment about a bulk insert right before it calls another stored procedure. But the bulk insert doesn't appear in the outer stored procedure that I am looking at. The "Recompilation Threshold" section of the article is interesting. What I'm seeing for the temporary tables is tons of INSERTs (that could result in millions of rows), some updates and deletes. So lots of data changes to the temporary tables. Millions.


texts:
I have a couple of tables that I need to join. I have an employees table (~ 400K rows), a companies table (~10 million rows) and a employee_companies table which stores where someone works.

Basically, I need to get all the employees that match some conditions (they work on a company that has a website, are located in a certain country, etc). I made a query to get this, but it's taking too long. I need to speed it up.

SELECT  DISTINCT "employees".*
FROM "employees"
INNER JOIN "employee_companies" ON "employee_companies"."employee_id" = "employees"."id"
INNER JOIN "companies" ON "companies"."id" = "employee_companies"."company_id"
WHERE (employee_companies.employee_id IS NOT NULL)
AND (companies.website IS NOT NULL)
AND (employees.country = 'Uruguay')
ORDER BY employees.connections DESC

This is the plan for that query:

Unique  (cost=877170.24..880752.72 rows=62304 width=1064) (actual time=24023.736..26001.876 rows=73318 loops=1)
  ->  Sort  (cost=877170.24..877326.00 rows=62304 width=1064) (actual time=24023.733..24305.989 rows=77579 loops=1)
        Sort Key: employees.connections DESC, employees.id, employees.name, employees.link, employees.role, employees.area, employees.profile_picture, employees.summary, employees.current_companies, employees.previous_companies, employees.skills, employees.education, employees.languages, employees.volunteer, employees.groups, employees.interests, employees.search_vector, employees.secondary_search_vector, employees.email_status, employees.languages_count, employees.role_hierarchy
        Sort Method: external merge  Disk: 85816kB
        ->  Nested Loop  (cost=2642.38..843246.15 rows=62304 width=1064) (actual time=139.870..23056.234 rows=77579 loops=1)
              ->  Hash Join  (cost=2641.95..221744.50 rows=77860 width=1068) (actual time=139.841..22617.587 rows=77579 loops=1)
                    Hash Cond: (employees.id = employee_companies.employee_id)
                    ->  Seq Scan on employees  (cost=0.00..212178.88 rows=409672 width=1064) (actual time=8.145..22369.166 rows=393725 loops=1)
                          Filter: ((country)::text = 'Uruguay'::text)
                          Rows Removed by Filter: 1075
                    ->  Hash  (cost=1666.42..1666.42 rows=78042 width=8) (actual time=44.675..44.675 rows=78042 loops=1)
                          Buckets: 131072  Batches: 1  Memory Usage: 4073kB
                          ->  Seq Scan on employee_companies  (cost=0.00..1666.42 rows=78042 width=8) (actual time=0.007..22.901 rows=78042 loops=1)
                                Filter: (employee_id IS NOT NULL)
              ->  Index Scan using companies_pkey on companies  (cost=0.43..7.97 rows=1 width=4) (actual time=0.004..0.004 rows=1 loops=77579)
                    Index Cond: (id = employee_companies.company_id)
                    Filter: (website IS NOT NULL)
Planning time: 1.957 ms
Execution time: 26025.045 ms

And these are the relevant indexes that I have on my table:

employees:

"employees_pkey" PRIMARY KEY, btree (id)
"ix_employees_country" btree (country)

companies:

"companies_pkey" PRIMARY KEY, btree (id)
"empty_websites" btree (website) WHERE website IS NULL
"index_companies_on_website" btree (website)
"not_empty_websites" btree (website) WHERE website IS NOT NULL

employee_companies:

"employee_companies_pkey" PRIMARY KEY, btree (id)
"index_employee_companies_on_company_id" btree (company_id)
"index_employee_companies_on_employee_id" btree (employee_id)
"index_employee_companies_on_employee_id_and_company_id" btree (employee_id, company_id)
"not_empty_employee_id" btree (employee_id) WHERE employee_id IS NOT NULL

Is there any other better way to do what I want that is more efficient/performant?

Thanks!


texts: Is Pseudo-Simple SQL Server Recovery a real thing?

texts:
"Pseudo-Simple SQL Server Recovery" is term and scenario, I was just made aware of in a (now deleted) comment for the new question SQL Server Truncates Transaction Logs with Copy Only Backups

I went to the post Pseudo-Simple SQL Server Recovery Model October 7, 2019 by Rajendra Gupta and using some of the code there and some of my own did some testing.

Create the database (Rajendra's code)

CREATE DATABASE RecoveryModel;

and Validate it is in Full (Rajendra's code)

SELECT name,
    recovery_model_desc
FROM sys.databases
WHERE name = 'RecoveryModel';

Do some work (Rajendra's code, modified slightly)

Use RecoveryModel
CREATE TABLE test(id INT);
GO
INSERT INTO test
VALUES(1);
GO 5000

See how much log space is used (my code)

select file_id
, type_desc
, name
, substring([physical_name],1,3) AS [Drive]
, physical_name
, state_desc
, size / 128 as 'AllocatedSizeMB'
, FILEPROPERTY([name],'SpaceUsed') /128 AS 'SpaceUsedMB'  --Addapted from https://sqlperformance.com/2014/12/io-subsystem/proactive-sql-server-health-checks-1
, (1- (FILEPROPERTY([name],'SpaceUsed') / CAST (size AS MONEY))) *100 AS 'PercentFree'
, growth / 128 as 'GrowthSettingMB'

 from sys.database_files
 order by type_desc Desc, name

We find that there log is filling.  Run the work again and check the size, the log grows, no surprise.

Try running t-log (My code)

BACKUP LOG [RecoveryModel] TO
DISK = N'E:\SQLBackups\RecoveryModel.trn' WITH NOFORMAT, NOINIT, SKIP, NOREWIND, NOUNLOAD
GO

It fails with message:

  Msg 4214, Level 16, State 1, Line 8

  BACKUP LOG cannot be performed because there is no current database backup.

  Msg 3013, Level 16, State 1, Line 8

  BACKUP LOG is terminating abnormally.

Nothing remotely simple about that, if you try to backup a database in Simple recovery. you get the message

  Msg 4208, Level 16, State 1, Line 19

  The statement BACKUP LOG is not allowed while the recovery model is SIMPLE. Use BACKUP DATABASE or change the recovery model using ALTER DATABASE.

Run a copy only backup (my code)

BACKUP DATABASE [RecoveryModel] TO
DISK = N'E:\SQLBackups\RecoveryModel.bak' WITH NOFORMAT, INIT, COPY_ONLY,
NAME = N'RecoveryModel-Full Database Backup', SKIP, NOREWIND, NOUNLOAD
GO

It runs fine, check the log space and it has not shrunk.  Run the work load a few more time, and the log space continues to grow. Run a t-log backup and it continues to fail.

Run a differential backup (My code)

BACKUP DATABASE [RecoveryModel] TO
DISK = N'E:\SQLBackups\RecoveryModel.dif' WITH  DIFFERENTIAL , NOFORMAT, NOINIT,
NAME = N'RecoveryModel-Diff Database Backup', SKIP, NOREWIND, NOUNLOAD
GO

It fails just like the t_log

  Msg 3035, Level 16, State 1, Line 13
  Cannot perform a differential backup for database "RecoveryModel", because a current database backup does not exist. Perform a full database backup by reissuing BACKUP DATABASE, omitting the WITH DIFFERENTIAL option.

So what is "Pseudo-Simple" about this?  The logs grow, t-log & differnatinal backups fail.  You have a database in Full recovery, with no full backup.

Edit it looks like there is something specific to servers in my build that is causing results different than what everyone else is seeing.  I have accepted Josh's answer.


texts:
I have a table with two columns, let's say FirstName and LastName.
I need to get another table, which for every pair of FirstName's from the first one contains a count of the common LastName's.

Is this even feasible to do in SQL?

There are much more unique LastName's than FirstName's, if this affects efficiency of the query.

A toy example, input:

FirstName, LastName
John, Smith
John, Doe
Jane, Doe

Output:

FirstName1, FirstName2, CommonLastNames
John, John, 2
John, Jane, 1
Jane, Jane, 1
Jane, John, 1

As this relation is reflexive and symmetrical, it's OK if the result is just one of the triangles (e.g, the one above the diagonal).


texts: This refactoring from cursor on a dblink'ed Oracle table OK?

texts:
The data is being compared from the local version of the table to one in the contracts linked server.  I'm not certain what the goal of this code is in the first place, but I'm trying to get a handle on simplifying it a little.

CURSOR c3 IS
    SELECT bs.billing_service_id, bs.oser_id
    FROM   (SELECT oser_id
            FROM   other_services
            MINUS
            SELECT oser_id
            FROM   other_services@contracts) a,
           billing_service bs
    WHERE  bs.oser_id = a.oser_id
           AND bs.sent = 0
           AND bs.billing_service_id NOT IN (
               SELECT bd.billing_service_id
               FROM   billing_details bd);

-- snip

FOR c_rec3 IN c3 LOOP
    DELETE FROM billing_service
          WHERE billing_service_id = c_rec3.billing_service_id;
END LOOP;

Seems to me that this could be naively refactored (without fooling with any other issues it has like the implicit joins and other garbage) simply as:

    DELETE FROM billing_service
          WHERE billing_service_id IN
(
    SELECT bs.billing_service_id
    FROM   (SELECT oser_id
            FROM   other_services
            MINUS
            SELECT oser_id
            FROM   other_services@contracts) a,
           billing_service bs
    WHERE  bs.oser_id = a.oser_id
           AND bs.sent = 0
           AND bs.billing_service_id NOT IN (
               SELECT bd.billing_service_id
               FROM   billing_details bd)
);

I don't think it's a significant amount of data in these tables - about 26000 rows on each side, and I don't think the set of differences is that big of a subset of the two.

But I was wondering if there are issues with DB Links which make cursors preferable to regular set-oriented methods?  This operation is part of a stored procedure which is run from an Oracle job on a daily basis.


texts: How can I determine which permissions needs to be assigned to the newly created login?

texts: We have 4 databases which contain different procedures and functions. Till now the actual application can access the database using only the sa account, now I would like to change the app to use something other than the sa account. For that I need to create a new SQL Login and assign to that login certain permissions. Is there any way like script to determine what permissions I need to assign to the newly created user?


texts: Change the order of a table according to preference in another table

texts:
In MS SQL server, I have a table A as below

Name   Food    PreferenceOrder
Jon    Burger  1
Jon    Coke    2
Jon    Pizza   3
Jon    Fries   4
Sam    Pizza   1
Sam    Coke    2

I have another table B that can override the preference order above

Name   Food    PreferredOverFood
Jon    Pizza   Fries
Jon    Coke    Burger
Jon    Fries   Coke
Sam    Coke    Pizza

Basically here, Food should come before PreferredOverFood (Pizza > Fries)

Now, I want to reorder table A according to Table B preferences, so the result should be like

Name   Food    PreferenceOrder
Jon    Burger  4
Jon    Pizza   1
Jon    Fries   3
Jon    Coke    2
Sam    Pizza   2
Sam    Coke    1

I tried by using cursors, so I created a dynamic cursor, with each fetch I am updating Table B with table A preference, but since we are updating things row by row its not considering rows that violate the previous preferences, so I am getting Fries before Pizza (since Fries > Coke is run and it forgot about first preference (Pizza > Fries)).

So dynamic cursor is not working, (its not refreshing the result set after update). Can I use CTE or something to do like above. (Can also have circular dependencies, but not too worried about it for now)

Thanks


texts: sp_addUser through sp_execute (SQL) in master db

texts:
The task is to automate restoring a group of databases, fixing orphaned users, and granting SELECT/INSERT/UPDATE/DELETE permissions.

use master
go

ALTER DATABASE [myDatabase] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
    RESTORE DATABASE [myDatabase] FROM  DISK = N'D:\MSSQL\Backups\myDatabase.bak' WITH  FILE = 1
    ,  MOVE N'myDatabase' TO N'D:\MSSQL\Data\myDatabase.mdf'
    ,  MOVE N'myDatabase_log' TO N'D:\MSSQL\Logs\myDatabase.ldf'
    ,  NOUNLOAD
    ,  REPLACE
    ,  STATS = 5
ALTER DATABASE [myDatabase] SET MULTI_USER
go

use myDatabase
go

sp_adduser 'userSelectOnly'
go

exec sp_change_users_login 'Auto_Fix', 'userSelectOnly'
go

grant select to userSelectOnly
go

Like this, in SSMS, it works great. Since there are several users and several databases, I'd like to put the database names and users into tables and loop through each, e.g.

    select @sql = 'ALTER DATABASE [' + @dbname + '] SET SINGLE_USER WITH ROLLBACK IMMEDIATE'

    + 'RESTORE DATABASE [' + @dbname + '] FROM  DISK = N''D:\MSSQL\Backups\' + @dbname + '.bak'' WITH  FILE = 1
        ,  MOVE N''' + @dbname + ''' TO N''D:\MSSQL\Data\' + @dbname + '.mdf''
        ,  MOVE N''' + @dbname + '_log'' TO N''D:\MSSQL\Logs\' + @dbname + '.ldf''
        ,  NOUNLOAD
        ,  REPLACE
        ,  STATS = 5'

    + 'ALTER DATABASE [' + @dbname + '] SET MULTI_USER'' -- extra tick to fix the SE code highlighter

    exec sp_executeSQL @sql

    @sql = 'use ' + @dbname
    exec sp_executeSQL @sql

    select @sql = 'grant select to ' + @current_loop_user
    exec sp_executeSQL @sql

Followed by all the login/user lines. I've tried it, and it neither do the job nor throw obvious errors, am I going to just have to type it all out longhand?


texts: PostgreSQL window functions very slow on big table

texts:
I have a PostgreSQL table constructed as

device_id | point     | dt_edit
----------+-----------+----------
100       | geometry  | timestamp
101       | geometry  | timestamp
100       | geometry  | timestamp
102       | geometry  | timestamp
102       | geometry  | timestamp
101       | geometry  | timestamp

I need to select the last 2 records ordered by dt_edit from unique device_id. This query works very slow, on 1 billion records - 500 seconds:

 SELECT dt.device_id,
    dt.point,
    dt.dt_edit
   FROM ( SELECT gps_data.device_id,
            gps_data.point,
            gps_data.dt_edit,
            rank() OVER (PARTITION BY gps_data.device_id
                         ORDER BY gps_data.dt_edit DESC) AS rank
           FROM gps_data) dt
  WHERE dt.rank <= 2

Any ideas?


texts: Is a UNION query equivalent with or without DISTINCT in subqueries?

texts:
Consider the following two queries.

SELECT Col1, Col2
FROM TblA

UNION

SELECT Col1, Col2
FROM TblB

and

SELECT DISTINCT Col1, Col2
FROM TblA

UNION

SELECT DISTINCT Col1, Col2
FROM TblB

These are logically equivalent. My question is if they are treated the same by the database engine. Does SQL Server recognize the redundancy and ignore the DISTINCT operator?


texts:
We had an oracle outage some days ago.
We have inbetween awr tools. I genarated  awrrpt report but dont see anything suspicious.
Which report should I generate to see sql, which caused very high cpu load?


texts: Alert message to remote SQL DB form local SQL DB if no data from software application

texts:
I am looking for a solution for my software application which has local SQL DB for data storage and from the local SQL DB replicate the data in real time to a remote SQL DB for visualizing the data in dashboard. This is possible with SQL configuration.

But I need to have another feature, which is kind of a job scheduler - if the local DB is not getting the data from the application for 15 minutes, I need to send a binary alert or add a field to the schema to notify the customer that there is no data coming in.

I cannot send an email because the database server is in a network isolated from the enterprise network.


texts: Limiting the input to a field as some pattern

texts:
i have a table student having field admission no. i want to restrict its input to the pattern '-/----/--'. If the input does not match this format then it should show an error. I tried doing this using LIKE constraint but the table was accepting all the values matching the pattern or not.....
please help


texts: How to unnest and display elements of a JSON array?

texts:
i have this table :

And i want display all the names in the table like this :

Also i want to list the number of gender each genre : Desired output:


texts: Should all queries where you expect a specific order, include an ORDER BY clause?

texts:
In order to better understand the SQL Server query processor, I've been thinking about how the ORDER BY clause works, and how SQL Server provides results.

It appears SQL Server will provide results in exactly the same order for any given set of data that remains the same and never, ever changes.  Once you introduce any kind of uncertainty such as developers changing something, the order of results can no longer be expected to be the same.

Simply seeing the results in the same order, each time you press F5 does not guarantee the results you expect.

Try this:

USE TempDB;
DROP TABLE TestOrderBy;
DROP TABLE TestOrderBy2;
CREATE TABLE TestOrderBy
(
    ID INT NOT NULL CONSTRAINT PK_TestOrderBy PRIMARY KEY CLUSTERED IDENTITY(1,1)
    , SomeData varchar(255)
);
INSERT INTO TestOrderBy (SomeData) VALUES ('E');
INSERT INTO TestOrderBy (SomeData) VALUES ('D');
INSERT INTO TestOrderBy (SomeData) VALUES ('C');
INSERT INTO TestOrderBy (SomeData) VALUES ('B');
INSERT INTO TestOrderBy (SomeData) VALUES ('A');

CREATE TABLE TestOrderBy2
(
    ID INT NOT NULL CONSTRAINT PK_TestOrderBy2 PRIMARY KEY CLUSTERED IDENTITY(1,1)
    , SomeData varchar(255)
);
INSERT INTO TestOrderBy2 (SomeData) VALUES ('E');
INSERT INTO TestOrderBy2 (SomeData) VALUES ('D');
INSERT INTO TestOrderBy2 (SomeData) VALUES ('C');
INSERT INTO TestOrderBy2 (SomeData) VALUES ('B');
INSERT INTO TestOrderBy2 (SomeData) VALUES ('A');

SELECT SomeData
FROM TestOrderBy
UNION ALL
SELECT SomeData
FROM TestOrderBy2;

CREATE INDEX IX_TestOrderBy_SomeData ON TestOrderBy (SomeData);
CREATE INDEX IX_TestOrderBy2_SomeData ON TestOrderBy2 (SomeData);

SELECT SomeData
FROM TestOrderBy
UNION ALL
SELECT SomeData
FROM TestOrderBy2;

The results:

As you see, adding a simple index on the fields selected in the query alters the order of the results.

From here on, I'm adding ORDER BY unless I truly don't care.


texts: SQL Server Express on Windows 8 isn't locking MDF database files

texts:
Historically, if I go to rename a database that is currently being used by SQL Server, I get an exception.

On this Windows 8 desktop I'm in.. my primary desktop.. I'm able to MOVE SQL Server database files to a different partition.. without getting any errors.. even if the files are currently 'in use'. I don't understand WHY I can do this, I want to get an error when I go to move a file and it should be listed as being attached in SQL Server master.sys.databases.

I am also able to rename a file that is currently in use. The punchline is that I was talking to my network admin today, and he actually attempts to rename a .MDF file, in order to determine if this file is in use.

I don't like this. What I really want to be able to do is to MOVE files (using a DOS command) from one disk partition to another, but if they're currently attached to the default SQL Server instance, then don't try to move the file.

I was counting on this to work as advertised. I shouldn't be able to rename or move a file that is currently being used as an attached database in SQL Server.

Also, I'm sorry.. but I do have an non-standard hard-drive config.

Disk 0
System Reserved 350mb
D: SSD

Disk 1
C: (Primary volume, Windows 8 Enterprise, etc)

I'm specifically using this SSD solely for SQL Server Express on this machine, I have a half dozen attached databases at any one time and I'm actually working towards creating a MILLION detached databases (and occassionally re-attaching some of them casually when I need them).


texts:
I'm designing an internal event store library based off SQL Server. One challenge I had to solve is to allow concurrent writes while still being able to generate a reliable Log Sequence Number (LSN) that could be used to consume the stream of events in order as they appear.

The way I'm solving this is by having a loose_position bigint IDENTITY(1, 1) column and a strict_position bigint NULL column which will get updated by a single writer and used as a consuming checkpoint. The sequencer does something like the following in a READ COMMITTED transaction:

DECLARE @maxPosition bigint;

SELECT @maxPosition = MAX(strict_position)
FROM RecordedEvent WITH(READPAST)
WHERE strict_position IS NOT NULL;

WITH seq AS (
    SELECT id, @maxPosition + ROW_NUMBER() OVER(ORDER BY loose_position) AS strict_position
    FROM RecordedEvent WITH (READPAST)
    WHERE strict_position IS NULL
)
UPDATE r WITH (READPAST)
SET strict_position = @maxPosition + seq.strict_position
FROM RecordedEvent r
INNER JOIN seq ON seq.id = r.id;

I wanted to prevent the sequencer from being blocked by newly inserted records and vice-versa, so I've used READPAST which seems to work, but I'm not sure that's the best approach.

Please note that I also have a UNIQUE CLUSTERED INDEX ON loose_position and a UNIQUE NONCLUSTERED INDEX ON strict_position WHERE strict_position IS NOT NULL, but I'm not entirely sure how these affects locking.

Could someone confirm that my current solution is appropriate or if there are better ways to achieve what I want?


texts: SQL Server tempdb on SSD or virtual RAM disc to increase temp-table performance

texts:
On a monthly bases we have a lot of temp calculations that run 4 days in total. We can't really change so many scripts and therefore i am searching for a config / hardware solution.

Background

Its not a ETL process because we are creating bits of information out of at least 10 applications (therefore i would call it calculations or lots of update-statements). This combination of information is only 1 stage of a bigger process that saves the calculated data finally with a normal ETL process (SSIS) in a DWH. If this calculation stage fails (server crash), we can easily restart it.

Problem

The MSSQL server where this calculation is running has only 2 HDD-raid1. One raid1 for data, indices and tempdb and one for the log. This calculation produce so much I/O that the whole process takes days. The server has 180GB of RAM and theoretically the whole data and result of these calculations could fit in 5GB memory.  This indicates we are using the database/resources in the wrong way.

Possible Solution

I think we have to reduce the I/O in the first place and also handle the I/O that is needed anyway in a second step. BTW the server is SQL Server 2014 Standard Edition.

Move the tempdb on a virtual ram disc (ca. 40GB). That means we move the main I/O to memory, correct?
Use more temp tables to reduce server-logging and recovery time and use the new ram disc (is there really less logging?)
Put the logs, indices and important tables on a new SSD raid5
Put all other data on the old HDD-drives

Is this a good solution or would you suggest the standard solution tempdb on the SSDs as well?


texts:
I can run one procedure in one database:

$ mysql --user=license --password=test123 --host=mariadb.devlocal --database=license -s '--execute=CALL config_sp_License_Update("missing","29b7ec4250df4fb30ce8634c98ae412a328652bb463749eec7a11b033b1e4eef","0","0","2019-06-19 09:04:26","2019-06-19 09:04:26", "0");'
status
missing

But not in the test database:

$ mysql --user=license --password=test123 --host=mariadb.devlocal --database=test_license -s '--execute=CALL config_sp_License_Update("missing","29b7ec4250df4fb30ce8634c98ae412a328652bb463749eec7a11b033b1e4eef","0","0","2019-06-19 09:04:26","2019-06-19 09:04:26", "0");'
ERROR 1370 (42000) at line 1: execute command denied to user 'license'@'%' for routine 'test_license.config_sp_License_Update'

The grants are correct:

Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 4653
Server version: 10.4.2-MariaDB-1:10.4.2+maria~bionic mariadb.org binary distribution

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> SHOW GRANTS FOR 'license'@'%';
+--------------------------------------------------------------------------------------------------------+
| Grants for license@%                                                                                |
+--------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'license'@'%' IDENTIFIED BY PASSWORD '*676243218923905CF94CB52A3C9D3EB30CE8E20D' |
| GRANT ALL PRIVILEGES ON `license`.* TO 'license'@'%'                                                   |
| GRANT ALL PRIVILEGES ON `test_license`.* TO 'license'@'%'                                              |
+--------------------------------------------------------------------------------------------------------+
3 rows in set (0.00 sec)

MariaDB [(none)]>

What is going on? What is special about the test_license database? Both test_license and license databases are initialized wit the same SQL commands


texts:
I have been working as a developer with SQL Server for years without asking the question, "what is dbo", a shame. Recently I have been studying the security model of SQL Server 2008. I have been overwhelmed by lots of concepts. Please clarify some concepts for me, and correct me where I am wrong. I don't need a thorough explanation on everything, just an idea what does what and the relationships involved:

LOGIN:  Server level (SQL Server login or Windows login).
USER:   Database level (a user is mapped to a login).
ROLE:   Users belong to a role. Can be at server level or at database level
        with permissions attached.
SCHEMA: Database objects belong to a schema (or dbo by default)
PERMISSION:
        For database objects or schema. A user or a role has permissions for
        an object or a schema.
OWNER:  Is it the user who created a object or schema? What does it do?
        How does it work? Where I can see the owner of a object in SSMS?
        I see only schema.object


texts: Executing a stored procedure over a linked server embedded as a synonym

texts:
If I use a domain account with explicit execute permissions, I can successfully execute a stored procedure over a linked server using a four part name eg [LINKEDSERVER].Databasename.schema.ProcedureName

However, if that four part name is defined within a database synonym - the explicit execute permission isn't enough. Indeed, I haven't been able to be able to execute it at all. I've tried it with a variety of versions of SQL Server up to 2017.

In the Microsoft docs, there's a note about four part names for functions not being supported, but I didn't think a synonym is a function - it's an object.
https://docs.microsoft.com/en-us/sql/relational-databases/synonyms/synonyms-database-engine?view=sql-server-2017

I suspect it is one step too far, but are there any ways to make it work?


texts: Azure Sql data warehouse - is there any way to see the true query plan?

texts: Azure SQL DWH / PDW have the EXPLAIN keyword to show the parallel query plan. This is really useful for seeing the data movement operations, however I was wondering if there was an equivalent of the traditional sql server query plan. I'm working on the assumption that just because there isn't any data movement, it doesn't necessarily mean that query is well optimised.


texts: Normalising Customers and Employers with a people table

texts:
At what normalisation stage is it recommend to split say Customers and Employees into a People table and then link to the unique attributes of Customers and Employees? I can't seem to find the Functional Dependency to justify the change. This makes sense in OOP where you use inheritance but I don’t understand why it is done in database schemas.

This is an example of what I mean, possibly with more unique attributes:

Customers: Cid, FName, LName, AddId, CustType
Employees: Eid, FName, LName, AddId, EmpType

vs

People   : FName, LName, AddId
Customers: PeopleId, CustType
Employers: PeopleId, EmpType

The current answers explain why it is recommended to move data to a People table; however, at what normalisation stage does it become a FD to justify creating the extra table?


texts: Determine the SQL Server version and edition from the available set up, before actually installing it?

texts:
We are going through a downgrade process from SQL Server 2008 R2 Enterprise to Standard edition.

To kick off the above activity, we were looking for available Standard Edition of SQL Server. We found one, but got confused on below points:

The setup files (saved by an earlier team member) are in a folder that says it is SQL Server 2008 R2, but just to cross check before we start installing, we went through the default.ini file, which says:

SQLSERVER2008 Configuration File
[SQLSERVER2008]

This created a doubt if this is the correct version of SQL Server required. So is there any other way, in case above mentioned method is incorrect to determine the version, before we actually go ahead and try installing?
Can we also check the edition of that available SQL Server, because on the set up, we can hardly figure out if it is really Standard Edition?

We cannot simply download new media, per our security policies. Also, we have no rights to download.


texts: SQL Server 2008R2 to SQL Server 2012 Install

texts:
I'm trying to upgrade one of my SQL servers from 2008R2 to 2012 and I'm getting an error popup within a couple of seconds from starting the setup

The Error is

  TITLE: SQL Server Setup failure.

  SQL Server Setup has encountered the following error:
  There was a failure to calculate the applicability of setting UIMODE.
  Error code 0x85640002.For help, click:http://go.microsoft.com/fwlink?LinkID=20476&ProdName=Microsoft%20SQL%20Server&EvtSrc=setup.rll&EvtID=50000&EvtType=0xC6B69F4C%25400xDC80C325

The Microsoft help link is http://www.microsoft.com/products/ee/transform.aspx?ProdName=Microsoft+SQL+Server&EvtSrc=setup.rll&EvtID=50000&EvtType=0xC6B69F4C%25400xDC80C325

I've spent a good amount of time searching for this error on google and stackoverflow to no avail. I've only found errors that are kind of like it that have to do with this

  There was a failure to calculate the applicability of setting ALLINSTANCES

What I've tried.

I ran a dotnet repair tool from Microsoft
Ran as Admin/From a different account
Installing from a cmd prompt using uimode=Normal action=Install
and uimode=autoadvance action=install.
Unzipping ISO to C: drive and ran install from there.
Re-downloaded the ISO to see if it was a corrupt download.
Downloaded SQL Server 2012 SP2 installer.

All of these have failed to get past this error. The below link is from the install log located at C:\Program Files\Microsoft SQL Server\110\Setup Bootstrap\Log

Install Log http://pastebin.com/Z71VSgg9

Any help is much appreciated


texts: How many row level locks can I have to same row?

texts:
I need some insights on row-level locking in Postgres.

How many shared locks (FOR SHARE) can be concurrently on a particular tuple in a relation .
Will have many shared locks on same tuple cause any performance/or any issue to database.
If such limitations exist, how to configure them and advisable limits.


texts: Defining external table on JSON with an @ sign in an element

texts:
I need to define a Hive external table onto a JSON file that has @ signs in its elements, e.g.

{ "data": { "@type": "person", "name": "Phil", "job": "Programmer" } }

This works:

create external table sandbox.test_table
( data STRUCT<name:STRING,job:STRING>
)
ROW FORMAT SERDE 'org.apache.hive.hcatalog.data.JsonSerDe'
STORED AS INPUTFORMAT 'org.apache.hadoop.mapred.TextInputFormat'
OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
LOCATION 's3a://bucket/DEV/data/raw/test/';

However this misses out the @type element, I've tried these:

data STRUCT<@type:STRING,name:STRING,job:STRING>
data STRUCT<`@type`:STRING,name:STRING,job:STRING>

Neither of them work. Any suggestions how I can do this, or do I need to preprocess the JSON to remove the @ from the element?


texts: Does SQL Table Column is assigned with column number

texts:
In Table, we can see there is number assigned automatically (line number) is the same thing is available for the columns (column number) and how can we get that number?
I need a SQL query to find the column number not the number of columns.


texts: moving a Partition of Partitioned Table to other File Group, SQL Server

texts:
I created partitioned table on DateTime column. for last 6 month.
I placed 3 oldest month on a file group named 'FG_OlderData', and 3 newest month on a file group named 'FG_NewerData'.
Partition Function Definition :

CREATE PARTITION FUNCTION pf_Positions_LastSixMonth_MonthlyRange (DateTime)
AS RANGE RIGHT FOR VALUES (
                        -- older_than_current_minus_5
        '20150121', -- current_minus_5
        '20150220', -- current_minus_4
        '20150321', -- current_minus_3
        '20150421', -- current_minus_2
        '20150522', -- current_minus_1
        '20150622',  -- current
        '20150723'  -- Future
)
GO

Partition Schema Definition :

CREATE PARTITION SCHEME ps_Positions_LastSixMonth_MontlyRange
AS PARTITION pf_Positions_LastSixMonth_MonthlyRange
TO (
      FG_OlderData,
      FG_OlderData,
      FG_OlderData,
      FG_OlderData,
      FG_NewerData, -- minus 2 month (to be moved to OlderData)
      FG_NewerData, -- minus 1 month
      FG_NewerData, -- current month
      FG_NewerData  -- future month+
      );
GO

The Result :

OnlineGeoPosition   1   FG_OlderData    0   0   less than   2015-01-21 00:00:00.000
OnlineGeoPosition   2   FG_OlderData    0   0   less than   2015-02-20 00:00:00.000
OnlineGeoPosition   3   FG_OlderData    0   0   less than   2015-03-21 00:00:00.000
OnlineGeoPosition   4   FG_OlderData    0   0   less than   2015-04-21 00:00:00.000
OnlineGeoPosition   5   FG_NewerData    0   0   less than   2015-05-22 00:00:00.000
OnlineGeoPosition   6   FG_NewerData    0   0   less than   2015-06-22 00:00:00.000
OnlineGeoPosition   7   FG_NewerData    0   0   less than   2015-07-23 00:00:00.000
OnlineGeoPosition   8   FG_NewerData    0   0   less than   NULL

First i switched  (20150522) partition's data to temp able to move to older file group :

ALTER TABLE dbo.OnlineGeoPosition
SWITCH PARTITION pf_Positions_LastSixMonth_MonthlyRange('20150522')
TO dbo.OnlineGeoPosition_NewerToOlderTemp;

Then Merge Switched partition :

ALTER PARTITION FUNCTION pf_Positions_LastSixMonth_MonthlyRange()
    MERGE RANGE ('20150522');

Result :

OnlineGeoPosition   1   FG_OlderData    0   0   less than   2015-01-21 00:00:00.000
OnlineGeoPosition   2   FG_OlderData    0   0   less than   2015-02-20 00:00:00.000
OnlineGeoPosition   3   FG_OlderData    0   0   less than   2015-03-21 00:00:00.000
OnlineGeoPosition   4   FG_OlderData    0   0   less than   2015-04-21 00:00:00.000
OnlineGeoPosition   5   FG_NewerData    0   0   less than   2015-06-22 00:00:00.000
OnlineGeoPosition   6   FG_NewerData    0   0   less than   2015-07-23 00:00:00.000
OnlineGeoPosition   7   FG_NewerData    0   0   less than   NULL

The Problem :
When i Split the '20150522' Range on OlderData, it remain in NewerData File group and next partion ('20150622') Goes to OlderData FileGroup.

ALTER PARTITION SCHEME ps_Positions_LastSixMonth_MontlyRange
          NEXT USED FG_OlderData;

ALTER PARTITION FUNCTION pf_Positions_LastSixMonth_MonthlyRange()
          SPLIT RANGE ('2015-05-22');

Result :

OnlineGeoPosition   1   FG_OlderData    0   0   less than   2015-01-21 00:00:00.000
OnlineGeoPosition   2   FG_OlderData    0   0   less than   2015-02-20 00:00:00.000
OnlineGeoPosition   3   FG_OlderData    0   0   less than   2015-03-21 00:00:00.000
OnlineGeoPosition   4   FG_OlderData    0   0   less than   2015-04-21 00:00:00.000
OnlineGeoPosition   5   **FG_NewerData**    0   0   less than   **2015-05-22** 00:00:00.000
OnlineGeoPosition   6   **FG_OlderData**    0   0   less than   **2015-06-22** 00:00:00.000
OnlineGeoPosition   7   FG_NewerData    0   0   less than   2015-07-23 00:00:00.000
OnlineGeoPosition   8   FG_NewerData    0   0   less than   NULL

After this command, i suppose to have  '2015-05-22' partition on OlderData File group but it remains in FG_NewerData File Group and instead , the next partition '20150622' goes to FG_OlderData FileGroup.

What is the problem with my code? as you saw i used Range Right.


texts: Need to group by column while ignoring some text format

texts:
I have the following data.

Sport
--------------
Baseball
Football
Baseball [a]
Football [a] [b]
Basketball

How can I group this to ignore the data in the brackets?


texts:
I want to automatically create user's, which all have a unique ID.

Now here's the problem. If two users are created at exactly the same nanosecond, it may not cause problems. Thus, what i do now is:

Get the next Autoincrement number
Add a new user with the name "U" + autoincrement number + random(0-9)

When more than 10 users are created at the exact same nanosecond, this will still cause problems (in theory!). Thus, I'm looking for a way to let MySQL handle the username.

Consider the following table:

CREATE TABLE IF NOT EXISTS `test` (
  `id` int(5) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

I want to use:

INSERT INTO test (id, name) VALUES (null, "U" + id)

where id is the autoincrement number. But, since id is not defined in MySQL, it will contain 0.

How to define id, so that it will contain the autoincrement number of that row.
This can easily be done by two queries, but I'm looking to do this in only one query :)

Thanks!


texts: Postgres not being able to create a tablespace

texts:
I just mounted an external HD for store my new tablespace.

postgres@vostro ~ $ df -k
Filesystem     1K-blocks     Used Available Use% Mounted on
/dev/sdb1      480589520    71580 456082280   1% /mnt/DB

postgres@vostro ~ $ ls -l /mnt/DB/
total 4
drwxrwxrwx 2 postgres postgres 4096 May 25 17:02 postgres

demisc@vostro /mnt $ mount | grep -i sdb1
/dev/sdb1 on /mnt/DB type ext4 (rw,nosuid,nodev,relatime,data=ordered)

I gave all the necessary permissions so that posgres could create the new ts in /mnt/DB/postgres. Here is the user groups:

postgres@vostro ~ $ id
uid=123(postgres) gid=131(postgres) groups=131(postgres),112(ssl-cert),1000(demisc)

Happens that when running the create tablespace command I get the following:

postgres=# create tablespace ts_project location '/mnt/DB/postgres';
ERROR:  could not set permissions on directory "/mnt/DB/postgres":
Permission denied

What I am missing?

ls -ln /mnt/DB/postgres

returns:

demisc@vostro /mnt/DB $ ls -ln /mnt/DB/postgres/
total 0

Everything looks good. There is no reason why postgres user can not create the tablespace. If i switch to postgres and try to create file or dir in the path it works fine.


texts: Error creating aggregate materialized view

texts:
Trying to create an aggregate materialized view:

CREATE MATERIALIZED VIEW MV_LLATTRDATA_MAX_VERSIONS
    NOLOGGING
    BUILD IMMEDIATE
    REFRESH FAST ON COMMIT
    ENABLE QUERY REWRITE
    AS
        SELECT AD.DEFID, AD.ATTRID, MAX(AD.VERNUM) AS MAX_VERNUM, MIN(AD.DEFVERN) AS MAX_DEFVERN
        FROM   CSTST.MV_LLATTRDATA_PARTITIONED_TEST AD, DTREECORE DT
        WHERE AD.ID = DT.DATAID
        GROUP BY AD.DEFID, AD.ATTRID;

I get this error however:

  ORA-23413: table "CSTST"."DTREECORE" does not have a materialized view
  log
  23413. 00000 -  "table \"%s\".\"%s\" does not have a materialized view log"
  *Cause:    The fast refresh can not be performed because the master table
             does not contain a materialized view log.
  *Action:   Use the CREATE MATERIALIZED VIEW LOG command to create a
             materialized view log on the master table.

I've created Materialized View Logs on (LLATTRDATA and DTREECORE) WITH ROWID INCLUDING NEW VALUES;

But I still get the error and I'm not sure why.


texts:
My large (+100 line) query contains a WHERE filter similar to:

select *
from (<complex 100+ line query>) d
WHERE (
   (EXISTS (select 1 FROM TABLE1 t1
            WHERE d.dummy = t1.x
              AND t1.col1 = 'FOOBAR' AND t1.col2 IN ('A', ))) OR
   (EXISTS (select 1 FROM TABLE2 t2
            WHERE d.dummy = t2.x
              AND t2.col1 = 'FOOBAR' AND t2.col2 IN ('A', 'B'))) OR
   (EXISTS (select 1 FROM TABLE3 t3
            WHERE d.dummy = t3.x
              AND t3.col1 = 'FOOBAR' AND t3.col2 IN ('A', 'B')))
)

Prior to this "type" of change, i.e. 3 EXISTS, the query ran in 1-3 seconds. However, with the above "type" of addition (as a WHERE filter, the query has ground to a halt - 60 seconds.

I've looked at the EXPLAIN PLAN, but I don't understand the output.

Basically, I only want to show the row from <query> d if its dummy column matches one of the x columns of TABLE1, TABLE2, or TABLE3.

From a performance and good SQL practice point-of-view, is the above WHERE inner query a reasonable way to determine if at least 1/3 conditions is true?


texts: MYSQL - 'Select distinct' - different results on two exact versions of Mysql, databases synced

texts:
Same versions of Mysql and the OS (Ubuntu), Databases synced fully.  Even the replica gives different results.

One mysql instance is showing 11 results (from a Select distinct statement) and all the other SYNCED db's are showing 12 results.

One the missing result server, if you change it to distinct by uid instead of *, then all 12 show up.  So the record is there it's just not showing up in the select query.

any idea's why this is happening?

mysql server version is 5.1.56

here is the select statement:

select distinct
bdtag3_.*
from
Company bdcompany0_
inner join
Company_Tag tags1_
on bdcompany0_.UID=tags1_.COMPANY_UID
inner join
Tag bdtag2_
on tags1_.TAG_UID=bdtag2_.UID
inner join
Tag bdtag3_
on bdtag2_.PARENT=bdtag3_.UID,
CompanyPostedStatus bdcompanyp4_
where
bdcompany0_.POSTED_STATUS_UID=bdcompanyp4_.UID
and bdcompanyp4_.IS_POSTED=1
and bdtag2_.TAG_TYPE_UID=14
order by
bdtag3_.DESCRIPTION


texts: SQL Server - redirect synonym without downtime

texts:
I have multiple application databases that use synonyms point to a shared database with immutable data that needs to be updated periodically by restoring an external backup. We now have a new requirement where we can no longer take the application down for the duration of the update, so the plan to update the database is to:

Restore the updated shared database as shared_temp
Redirect all synonyms in application databases to shared_temp
Restore the updated shared database onto the "main" shared database
Redirect synonyms back to shared
Drop shared_temp

Since there doesn't seem to be an alter synonym statement, how would I perform these synonym redirects without a race condition where queries performed between drop synonym and create synonym fail to reference the shared database?

Is there any way to lock the synonyms and allow them to be recreated during the lock?

Alternatively is there a way to lock the whole database (or schema) in a way that incoming queries will be queued rather than rejected like in single user mode?

If all else fails, is there a reliable way to update the contents of all tables in a database from another without slowing down queries? Note that there are internal references in the data. Existing references are guaranteed to remain valid with the new data. As long as we don't downgrade to a previous version it should be safe.

Modifying statements that reference these synonyms is not an option.

Minimum SQL Server version is 2008 R2, although if newer versions make this possible then upgrading is an option.

We don't really care how long the process takes, just that there is no downtime. Dropping and renaming is not atomic and likely not lockable, otherwise that would be an option.


texts:
I have implemented data denormalization strategy using postgresql RULEs. I picked rules instead of triggers for performance reasons.

Schema is structured like this:

Application has many clients
Client has many projects
Project has many users

One part of the system is storing hits for every user in stats table. Hit is an imaginary metric, it is not really relevant. System can collect many of these metrics. There are a lot of records in stats table (> 1,000,000 per day).

I want to know how many hits are per user, per project, per client and per application for given day.

To make it work fast, I've groupped stats by day and stored the output into user_hits table. During this process, also the application_id, client_id and project_id has been added (as columns), and appropriate indexes created.

I want to further optimise the process by grouping things by project_id, client_id and finally application_id. The data pipeline is like this:

stats -> user_hits -> project_hits -> client_hits -> application_hits

I want to make sure when I delete the data from user_hits for given day, that the data in project_hits for that same date is also deleted. This process should propagate to last table in chain.

I defined these simple rules:

CREATE RULE delete_children AS ON DELETE TO user_hits
  DO ALSO
  DELETE FROM project_hits WHERE day = OLD.day;

CREATE RULE delete_children AS ON DELETE TO project_hits
  DO ALSO
  DELETE FROM client_hits WHERE day = OLD.day;

CREATE RULE delete_children AS ON DELETE TO client_hits
  DO ALSO
  DELETE FROM application_hits WHERE day = OLD.day;

However, when I issue statement like this:

DELETE FROM user_hits WHERE day = current_date;

I expect it to run these 3 queries in return:

DELETE FROM project_hits WHERE day = current_date;
DELETE FROM client_hits WHERE day = current_date;
DELETE FROM application_hits WHERE day = current_date;

However, it doesn't.

It completes the operation, but it takes couple of minutes to do that (with test data). With real data it takes hours, while running those 3 queries by hand takes couple of milliseconds.
The time it takes seems proportional to number of combinations (users x projects x clients x applications).

What is the problem here? Am I missing something? Can this be implemented with triggers in an optimised way?

Included sample script which reproduces the problem:

https://gist.github.com/assembler/5151102

UPDATE: Transition from user_hits to project_hits (and so forth) is done by worker process in background (since it involves contacting 3rd party services for additional info). It is smart enough to recalculate everything for missing dates. So the only thing i need is a way to DELETE records cascadingly in optimised way.

UPDATE: stats table is filled on daily basis. The only possible scenario is to unconditionally delete the data for whole day and then replace it with new values.

UPDATE: I noticed that the number of affected rows (extracted from explain statement) is exactly equal to the product of affected rows in user_hits, project_hits, client_hits, and application_hits tables (hundreds of millions of rows).

It turns out that it works like this:

I run DELETE FROM user_hits WHERE day = current_date;
For each row in user_hits table, RULE is triggered which deletes EVERY row from project_hits
For each row of project_hits, RULE is triggered which deletes EVERY row from client_hits
For each row of client_hits, RULE is triggered which deletes EVERY row from application_hits

So, the number of operations is equal to the product of count of affected rows in these tables.


texts: Increased query_cache_size, Queries slowed drastically on increased traffic

texts: I increased our servers query_cache_size to 2gb, hoping for performance benefits. After sometime the query performance would drastically decrease. Once I dug in deeper, I found the queries in State "Waiting for query cache lock ". Lowering the query cache to 512M brought the performance back to normal. Why would this happen, and why would lowering it increase performance ?


texts:
I'm rather confused by this one and can't see any options in previous posts about similar things.

I've created a credential -> proxy account, that will run a PowerShell script.

When I run the agent job using the proxy account, I get an error saying username or password are incorrect.

  Unable to start execution of step 1
  (reason: Error authenticating proxy Domain\UserAccount,
  system error: The user name or password is incorrect.).
  The step failed.

I know the password is correct. I've logged into servers using the account and password, and have re-applied it 2-3 times trying copy and paste from password safes to text files, to manually putting it in. I'm 100% sure the password is correct.

I have restarted the Agent/SQL Server and even restarted the whole OS, i've never run into this problem before. I have completely deleted the proxy and credential, then re-created, and I still get the same error message

Just created another credential and proxy using the below code, so I know the password is 100% correct using my account so local admin, sysadmin on the box, and still get the same error.

USE [master]
GO
CREATE CREDENTIAL [test]
WITH IDENTITY = N'domain\myAccount',
SECRET = N'SuperSecretPassword'
GO

USE [msdb]
GO
EXEC msdb.dbo.sp_add_proxy
    @proxy_name=N'ProxyTest',
    @credential_name=N'test',
    @enabled=1
GO
EXEC msdb.dbo.sp_grant_proxy_to_subsystem
    @proxy_name=N'ProxyTest',
    @subsystem_id=12
GO

Any suggestions?


texts: Two named instances of SSAS on one server - can't connect

texts:
I have one Windows Server with two named instances of both SQL Server and SSAS. I am trying to connect to SSAS instances from Pyramid application (on another server) and none works. The port 2382 is open, SQL Browser runs on LocalSystem account. The user I am using is in administrators of SSAS.
Every connection try ends up with the message:

The following system error occurred:  A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond.

I wonder what can I do to make the connection works?


texts: What is the SQL Standard method of doing LIMIT OFFSET?

texts:
I know LIMIT and OFFSET are pretty common but unstandardized methods to page through a result set. I know there is a standardized method, but I can never find this when I'm going to look it up,

The PostgreSQL LIMIT/OFFSET docs don't link to it,
The PostgreSQL FETCH docs don't link to it either.

I know it's not either of them. What is the SQL Standard and PostgreSQL syntax for LIMIT and OFFSET?


texts: Transaction atomicity implies consistency

texts:
Transactions are said to need both atomicity and consistency. But, what is the point of consistency?

Once you ensure that you either update both accounts on money transfer, you are consistent. Wikipedia article on consistency says that consistency is a set of programmatic constraints, invariants. But you are free to code your transaction operations any way. So, I feel that programmer only needs Atomicity, Isolation and Durability. Consistency is an additional convenience DB tool, like trigger or stored procedure and is not absolute low level requirement. Given AID, the programmer can always ensure the consistency. It is a simple as check user data and reject the request if some rule is broken. Right?

It looks like Wikipedia identifies Atomicity with Consistency in the Data Consistency article, but not in the article on ACID transactions.

Listening on eventual consistency, I see that developers mean that DB is always in some correct snapshot state, i.e. data are stabilized and you can query/issue your transaction upon the DB, which you could not do if DB is available in transition. That is, consistent DB is one which satisfies the constraints at any moment of time. But, it satisifies the constraints if operations are atomic. Atomicity implies that we see only transaction results, which implies that we see only snapshots, which means consistence. Why do you need to single the consistence out as if it is independent property?


texts: Move large mysql table data from one server to another

texts:
I have a table to which millions of data are inserted every day. As the table size grows more than 10 GB, I have issues with the performance of server in fetching apis and generating reports, which are based on above table. What i am planning to do it is move the old, say 1 week, data to another server and keep the current table size always small. I am running a php application to fetch api and generate reports on the server.

My concerns are:

Is it the right approach or are there other solutions to my problem?
What is the best way to do it?


texts:
As I reported some days ago to MariaDB bug tracker the following bug: https://jira.mariadb.org/browse/MDEV-17420, slave replicas with version 10.2.x are leaking temporary tables on MyISAM files.

Our temp directory is filling with .MYD/.MYI files up to the point that the process file descriptor limit is reached, for example:

-rw-rw---- 1 mysql mysql    1024 Oct 24 13:25 #sql324a_1a28c_e4b18.MYI
-rw-rw---- 1 mysql mysql       0 Oct 24 13:25 #sql324a_1a28c_e4b18.MYD
-rw-rw---- 1 mysql mysql    1024 Oct 24 13:25 #sql324a_1a28c_e4b19.MYI
-rw-rw---- 1 mysql mysql       0 Oct 24 13:25 #sql324a_1a28c_e4b19.MYD
-rw-rw---- 1 mysql mysql  189376 Oct 24 13:25 #sql324a_1a28c_e4b0c.MYD
-rw-rw---- 1 mysql mysql    5120 Oct 24 13:26 #sql324a_1a28c_e4b58.MYI
-rw-rw---- 1 mysql mysql    3575 Oct 24 13:26 #sql324a_1a28c_e4b58.MYD
-rw-rw---- 1 mysql mysql    1024 Oct 24 13:26 #sql324a_1a28c_e4b59.MYI
-rw-rw---- 1 mysql mysql       0 Oct 24 13:26 #sql324a_1a28c_e4b59.MYD
-rw-rw---- 1 mysql mysql    1024 Oct 24 13:27 #sql324a_1a28c_e4b6e.MYI
-rw-rw---- 1 mysql mysql    1204 Oct 24 13:27 #sql324a_1a28c_e4b6e.MYD
-rw-rw---- 1 mysql mysql 1256372 Oct 24 13:28 #sql324a_1a28c_e4ae3.MYD

After that, the only solution is to stop the mysqld process and then start it again.

Of courdse, we have checked on the master and these same files are not leaked or stale.

The question: does anyone know how to find some information of these temporary tables (at least their names) to help in debugging this case with more detail?

Thanks


texts: How to export a MySQLtable with a longBLOB column to an Oracle database?

texts:
In MySQL I have a table:

Select * from Dummy;

Columns like below:

id name salary binary_col
1    a   100   A.pdf
2    b   200   b.pdf
3    c   300   c.pdf
4    d   400   d.pdf
5    e   500   e.pdf

I am using SQLyog 7.01. I have 200 records in the MySQL table.

I want export these 200 records and import them into an Oracle database table.

The binary_col  data type is longBLOB.


texts:
I have a Postgres/PostGIS database of geography points which I'm trying to cluster; meaning I want to group and count them by proximity. I have had first success with the kmeans Postgresql extension, but the algorithm is not optimal for what I require. I want to experiment with more algorithms, and R seems to be the best choice for this. I have set up PL/R and can call R functions from Postgres...

But what now? A simple R function which returns a scalar is no problem, but I'm not sure how to approach even the same kmeans solution in PL/R, yet alone implementing a whole clustering algorithm solution. The aforelinked kmeans extension is very approachable to me, with a query like:

    SELECT kmeans, count(*)
      FROM (
               SELECT kmeans(ARRAY[ST_X(geom), ST_Y(geom)], 5) OVER (), geom
                 FROM points
           ) AS ksub
  GROUP BY kmeans;

Since kmeans() only returns a single int this is very comprehensible to me. But I'm not sure where to start with some of the more complex R clustering solutions. Would it be better to post-process the entire result set in R, like SELECT r_cluster((SELECT ...))? Can I use a WINDOW function, in which case what would I return from R and how?

I'd like to see one actual code sample of a PL/R function declaration and invocation to get started on this.

-- please fill in the blanks

CREATE OR REPLACE FUNCTION r_cluster()
RETURNS ? AS $$ ? $$ LANGUAGE plr;

SELECT r_cluster(?) OVER (?) FROM points;


texts: Can sqlplus fetch more than one row at a time?

texts:
We have a task running every day dumping results of a query into a CSV-file -- as the stdout of sqlplus running a query.

The number of records is many millions (don't ask), and the job takes over two hours.

In trying to improve the timings, I wrote a C-program (using Oracle's ofetch() call), but the performance didn't change... Bumping up the Session Data Unit (SDU) size (to 1GB! on the client -- not sure, where the server would clamp it) didn't help.

I then did some more research and found, that ofen(), if used to capture multiple rows at a time, can improve performance dramatically -- and changed my C-program to use it instead.

Fetching 4K rows at a time, the timings went down to 21 minutes -- a factor of 10x reduction -- causing much rejoicing.

Before we go through the trouble of installing my new executable on production servers, maybe,  the stock sqlplus can also do the same multi-row fetching -- if supplied with the proper command-line option(s)?

Can it? We're using Oracle-11 although, if this feature is available in the client version 12, we could upgrade the client-side...


texts: Adding warning with sp_add_alert doesn't work with "Greater Than" and SQL Server 2014

texts:
We upgraded our SQL Server from 2008 R2 to 2014.

I want to add a custom warning.

USE [msdb]
GO

EXEC msdb.dbo.sp_add_alert @name = N'MAN more than 1000',
                           @enabled = 1,
                           @delay_between_responses = 0,
                           @include_event_description_in = 1,
                           @performance_condition = N'Replication Dist.|Dist:Delivered     Cmds/sec|MyFancyReplication|>|1000',
                           @job_id = N'00000000-0000-0000-0000-000000000000'
GO

This has worked with SQL Server 2008 R2.

When I want to add this Warning, but all I get is this error (it's translated, because my original is German)

  A performance condition must be formatted as:
  ‘object_name|counter_name|instance_name|comparator( or < or =)|numeric value'.

Did you recognize the missing > in the error message?

Why can't I add my custom warning?

Best Regards

Björn


texts: How can i import 150GB database after deleting ibdata1,logfile in MYSQL?

texts:
I have a 150GB database and my ibdata1 file size is 240GB so I want to shrink the ibdata1 file because I have to export the DB and then drop the tables and then stop mysql, delete the ibdata1 file and then start the =MYSQL again and import the DB again.

If the above solution is correct how can I export and import such huge DBs ?   Also, there is partition created in the DB table.

And I am also not sure that I will not lose any data in during this process.


texts:
We want to keep track of changes being done to MySQL procedures only, actually recently our procedures seem to have overwritten to some previous versions and we were unable to track it that who did this. We were able to track thread-id from mysqlbinlog but were not able to determine whose userid was using that thread-id.   Is there any way by which we can do following

• Track user-id from mysqlbinlog thread-id
•   Keep track of every create procedure/drop procedure

We are using MySQL 5.7.20 community edition on windows 10 & CentOS7 and we are using row level bin-logging

Any help is greatly appreciated.


texts: Mysql Replication backup and restoration failed multipal time

texts:
I tried restoring MySQL backup several time but unfortunately it gets failed by some or the new reason.

I have 2 host Master & Slave with below details.

Server1_Master
os-centos6.7
mysql- 5.6.28-2.el6

Server2_Slave
os- centos6.6
mysql-  5.5.41

Engine: innodb  (on both host)

Below are the respected step used by me.

Master Backup

  mysqldump -uroot -p --skip-lock-tables --single-transaction --flush-logs --hex-blob --master-data=2 -A  > ~/dump.sql

Restoring mysql on slave

  mysql -u root -p < /location/dump.sql

  CHANGE MASTER TO MASTER_HOST='<>',MASTER_USER='replicant',MASTER_PASSWORD='<>', MASTER_LOG_FILE='<>', MASTER_LOG_POS=<>;

START SLAVE;

Now the new error which we am getting after server hrs of restoration is below

  "ERROR 1449 (HY000) at line 150536: The user specified as a definer ('lipl_ga_app'@'%') does not exist "

Please let me know the best way to start new replication.


texts: What value should I choose as length for timestamp without timezone in PostgreSQL?

texts:
I'm currently setting up a database to populate it with big data. All the data I get comes from Valve's Steam Web API. Therefore, as all timestamps are returned in Unix timestamps, I cannot directly derive the correct timezone - which is also not of any interest as it's to fine-grained for what it is intended for. However, PostgreSQL enables the "length"-value field when setting the column up for timestamp without timezone but I don't know which value is meaningful to enter here and I also couldn't find any information about this value - neither in the official documentation nor on StackExchange so far.
As I'm someone who doesn't set up databases all the time I'm a bit confused and would love to get some assistance. Thanks in advance for your suggestions and input.


texts: is it possible to modify identify columns start with value in PostgreSQL 13

texts:
I have a table rss_sub_source, and the id as a identify column. Now I want to modify the start with value(I forget to define the start with value when I added identify column). I have tried this way:
ALTER TABLE rss_sub_source
    ALTER id SET NOT NULL,  -- optional
    ALTER id ADD GENERATED ALWAYS AS IDENTITY
        (START WITH 1233);

shows this column already as a identify column. Then I modify the SQL like that:
ALTER TABLE rss_sub_source
    ALTER id ADD GENERATED ALWAYS
        (START WITH 1233);

did not work shows SQL Error [42601]: ERROR: syntax error at or near "(" Position: 74. What should I do to make it work? This command still not work:
 ALTER TABLE rss_sub_source
    ALTER id START WITH 1233;

this is my table create command:
-- Drop table

-- DROP TABLE public.rss_sub_source;

CREATE TABLE public.rss_sub_source (
    id int8 NOT NULL GENERATED ALWAYS AS IDENTITY,
    sub_url varchar NOT NULL,
    created_time int8 NOT NULL DEFAULT date_part('epoch'::text, now()),
    updated_time bigserial NOT NULL,
    sub_status int2 NOT NULL DEFAULT 1,
    rss_type varchar(64) NOT NULL,
    standard_type varchar(32) NOT NULL DEFAULT 'RSS'::character varying,
    standard_version varchar(32) NOT NULL DEFAULT 'rss20'::character varying,
    cron varchar NOT NULL DEFAULT '0 */1 * * * *'::character varying,
    trigger_count int4 NOT NULL DEFAULT 0,
    next_trigger_time timestamp NULL,
    sub_name varchar(256) NOT NULL,
    last_trigger_time timestamptz NULL,
    tags _int4 NULL,
    source_url varchar(256) NULL,
    sub_type varchar NULL,
    intro varchar NULL,
    remark varchar NULL,
    title_hash varchar NULL,
    failed_count int4 NOT NULL DEFAULT 0,
    lang varchar NULL,
    frequency_month int4 NULL DEFAULT 0,
    reputation int4 NULL DEFAULT 0,
    rep_lastest_refresh_time int8 NULL DEFAULT 0,
    scrapy_take_time int4 NULL DEFAULT 0,
    follower int8 NULL,
    censor_status int4 NULL DEFAULT 1,
    etag varchar(64) NULL,
    last_modified varchar(32) NULL,
    editor_pick int4 NULL DEFAULT 0,
    fav_icon_url varchar(128) NULL,
    dynamic_interval int4 NOT NULL DEFAULT 0,
    local_icon_url varchar(256) NULL,
    CONSTRAINT rss_sub_source_pkey PRIMARY KEY (id),
    CONSTRAINT unique_sub_url UNIQUE (sub_url)
);

the table already have 1232 rows. I am using this command to modify the start with value:
ALTER TABLE rss_sub_source
ALTER id SET START WITH 1233;

it execute success but seems not work. How to check the table primary key current start with value?When I am using this command to check the current seed value of id:
select CURRVAL(pg_get_serial_sequence('public.rss_sub_source', 'id'))

shows greater than 1200. But when I insert a new sql in my app, still get a small seed id.


texts: Positional Operator Matching Nested Arrays Workaround?

texts:
As was just pointed out to me on stackoverflow, Positional Operator Matching Nested Arrays has yet to be made available..

I need to display read.marked:true replies differently than read.marked:false replies, dependent on each particular user's marking.

This findOneAndUpdate is contained by a loop that runs through each of the replies..is there a workaround for making updates to each reply for a particular user in the 'to' array?

I can capture each replies._id, or each replies.to._id

Right now, MongoDB produces this: [MongoError: cannot use the part (to of replies.0.to.read.marked) to traverse the element..] ..when it should really look something like this anyways: replies.$.to.$.read.marked

Models.Message.findOneAndUpdate(

  { "replies._id": ObjectId("53dd4b67f0f23cad267f9d8b"), "replies.to.username": "UserA" },
  {
    "$set": {
        "replies.$.to.read.marked": true,
        "replies.$.to.read.datetime": req.body.datetimeRead,
        "replies.$.to.updated": req.body.datetimeRead
    }
  },

To $set to.read.marked:true for userA of the specific reply:

{
    "_id" : ObjectId("53daf26af7b70fd82ffd4cff"),
    "updated" : ISODate("2014-08-02T20:36:03.000Z"),
    "message" : "here's a message",
    "replies" : [
        {
            "reply" : "here's a reply",
            "created" : 1407011687000,
            "_id" : ObjectId("53dd4b67f0f23cad267f9d8b"),
            "to" : [
                {
                    "username" : "userA",
                    "updated" : ISODate("2014-08-02T20:34:47.000Z"),
                    "_id" : ObjectId("53dd4b67f0f23cad267f9d8c"),
                    "read" : {
                        "datetime" : ISODate("2014-08-02T20:34:47.000Z"),
                        "marked" : false
                    }
                },
                {
                    "username" : "userB",
                    "updated" : ISODate("2014-08-02T20:34:47.000Z"),
                    "_id" : ObjectId("53dd4b67f0f23cad267f9d8d"),
                    "read" : {
                        "datetime" : ISODate("2014-08-02T20:34:47.000Z"),
                        "marked" : false
                    }
                }
            ]
        },
        {
            "reply" : "here's another reply",
            "created" : 1407011763000,
            "_id" : ObjectId("53dd4bb3a9f9497e270525cb"),
            "to" : [
                {
                    "username" : "userA",
                    "updated" : ISODate("2014-08-02T20:36:03.000Z"),
                    "_id" : ObjectId("53dd4bb3a9f9497e270525cc"),
                    "read" : {
                        "datetime" : ISODate("2014-08-02T20:36:03.000Z"),
                        "marked" : false
                    }
                },
                {
                    "username" : "userB",
                    "updated" : ISODate("2014-08-02T20:36:03.000Z"),
                    "_id" : ObjectId("53dd4bb3a9f9497e270525cd"),
                    "read" : {
                        "datetime" : ISODate("2014-08-02T20:36:03.000Z"),
                        "marked" : false
                    }
                }
            ]
        }
    ],
    ...
}


texts:
(Editing the question with additional information: )

We are in process of testing a Postgres 9.6 active-passive HA cluster using streaming replication on Rhel 7.5. The HA setup is done using pacemaker and  Failover is automated.

We are seeing below issue with the setup :

When a failover is triggered (while data is being added to the primary) by killing primary (killall -9 postgres), standby node takes role of primary and the failed node(old primary) is restarted. However it doesn't start streaming or take stand-by role. It starts in disconnect mode.

We are using Postgres 9.6 because of application specific requirement - other component's dependency on 9.6 version
One thing we checked and noticed that the wal receiver process is not started on the new standby. If we kill postgres processes again the wal receiver process is started and standby node now syncs with new primary.
Another observation is that in the disconnected node, there is some additional data, which is not present on the new stand-alone standby. Which means that the failed primary was ahead in terms of log.

Need urgent inputs in :

Is there any way of simulating abrupt shutdown of postgres?
How can we prevent the situation of data mismatch with "killall -9" on Primary that is prevent old primary having additional data when postgres is killed and failover happens. We are running streaming replication in Sync mode

Thanks !


texts:
I have a small database (<100 KB when dumped) that will hang in phpMyAdmin when I try to sort by various columns.
Sometimes it hangs when I try to edit columns by directly clicking on their content.
I see the following warnings and wonder if anyone can provide an experienced based explanation or any assistance in improving the interface.

The phpMyAdmin configuration storage is not completely configured,
some extended features have been deactivated. To find out why click
here.
Your PHP MySQL library version 5.0.77 differs from your MySQL server
version 5.5.10. This may cause unpredictable behavior.


texts:
Good Evening to all.

I was working with sql server 2008.

Thing i did:

Actually i was noticed that the free space available in C drive reaches below 1 GB .So i thought to Club the D drive with C drive.I had extended the C drive to 18 GB.

Error that Occurs:

Once i finish Extending the drive size i restarted my system.After logon i opened the Microsoft Sql server Management Studio which has multiple database.Every thing works fine except the Database which i have worked recently.When try to expand the table it shows the error as
"**

  Possible schema corruption. Run DBCC CHECKCATALOG. A severe error
  occurred on the current command.  The results, if any, should be
  discarded. (.Net SqlClient Data Provider)

**"
This is how i runned the DBCC runcheck catalog:

  DBCC CHECKDB (Demo) WITH NO_INFOMSGS, ALL_ERRORMSGS

and this is the error which i get while running the above:

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

Can any one please help me tocome out of this rid.

Thanks in advance


texts: SQL Server connectivity without specifying tcp/port

texts:
Having connection issues out of nowhere in my lab (Azure VMs):

SQL 2016, sp1, cu4 all the way around, Windows 2016 Datacenter

Windows Failover cluster for SQL AG testing.

SQLServer-0:

Default instance (port 1431)

Inst1 (port 1499)

SQLServer-1:

Default instance (port 1431)

Inst1 (port 1499)

From SQLServer-0, I can only connect to the remote instance(s) using tcp:SQLServer-1, 1431 (or tcp:SQlServer-1.kevinsdomain.com,1431).

Same thing for the named instance.

Same thing from SQLServer-1 to SQLServer-0.

Ping of just the NetBIOS name works just fine, ping -a resolves the FQDN.

None of these are running on port 1433, both servers are running SQL Browser under NT Authority\Local Service.

This is my test domain, along with a domain controller, DNS, etc.

Windows firewall on each has all of the ports open that are needed, including UDP 1434.

I'm sure there's something simple I've forgotten to check, flush, whatever.  Just can't think of it.


texts: Validate many SQL statements for correctness

texts:
I have about 150 reports generated from SQL statements. These reports are saved independently from the server. Some generated from sprocs, some from table-valued functions, but mostly just SQL statements.

Now the server goes through a big refactoring of tables and sprocs. And I need a way to validate that all the reports are still running, as it is possible the changes will break the reports.

One way to validate that all the reports are valid - run them and lookout for errors.

The problem - some of the reports are very heavy and include aggregation of of many tables with many joins with millions of records.

Is there a way to validate SQL statements without actually executing them?

I'm working with SQL Server 2008 and have ability to apply C# processing to the statements. Ideally I'd like a way to validate the reports often and without much effort, i.e. 10 times a day. So making a schema-only copy of DB is not an option.


texts: Force psqlODBC Postgres drivers to use Windows Trusted Root Certificate Store

texts: I need to configure my client software to use SSL when connecting to a postgres database (sslmode=verify-full). The configuration requires me to specify a root certificate file in the following directory: c:\users\Administrator\AppData\Roaming\postgresql\root.crt. The file path may change depending on what user is running the program, and in any case I can use sslrootcert setting to specify some common path. However, the software that I'm working on is already using Windows Certificate Store to check certificates and I'd like to force postgres drivers to use it, instead of a file. Is it possible somehow to configure psqlODBC drivers to use Windows Certificate Store to verify server cert?


texts: MCSE: Data Management and Analytics requirements overlap with MCSA: SQL Server 2016 Database Development

texts: I've been reviewing the requirements to get the MCSE: Data Management and Analytics certification, and was thinking of going the path of the MCSA: SQL 2016 Database Development pre-req. The thing is there's an overlap in the requirements. The MCSA requires passing exam 70-761 and 70-762. To get the MCSE I need to take 1 exam from a list of about 12, 70-762 being one of the options. I'm confused about the requirement overlap of exam 70-762. Once I have the MCSA do I meet the stated requirements for the MCSE. Or do I need to take a 3rd exam from the list to qualify?


texts: Connect by prior - multiple fields and query efficiency

texts:
I have 2 tables one of which defines the hierarchy for the other

equipment

id    desc
1     item1
2     item2
3     item3
4     item4
5     item5
6     item6
7     item7
8     item8
9     item9

hierarchy
equipId    parentEquipId    type    attrib_1    attrib_2    attrib3
1          3                D       blue        red         green
2          4                B       green       blue        red
3          2                C       orange      green       purple
4          null             A       pink        yellow      orange
5          3                D       yellow      red         purple
6          3                D       purple      green       red
7          2                C       grey        brown       tan
8          7                D       red         grey        yellow
9          7                D       blue        green       orange

My query is against the items of type D. These can be easily determined through a join on the second table to obtain the type.

select *
from equipment
left join on hierarchy.equipId = equipment.id
where hierarchy.type = 'D'

The tricky part is I also need to obtain the 3 attributes for the various parent types.
Each type D item has an A,B,C parent hierarchy based on the types of the parents. So for the piece of equipment with the id of 1 the ids of the parents are 3(C),2(B),4(A).

I've been using multiple CONNECT BY PRIOR statements to get the attributes of the parent hierarchy. I have only figured out how to get each attribute individually though and this adds up to a lot of connect by prior statements. Even for each of the parent types I still need to do 3 connect by priors, one for each attribute. It would be nice to be able to do a connect by prior once to each of the parent types and pull all 3 attributes at the same time.

Does anyone know if/how it can be done?

Here's my current SQL. It works but I'd like to make it more efficient and less verbose.

Any help is appreciated.

with
e as  (select equipment.*
       from equipment
       left join on hierarchy.equipId = equipment.id
       where hierarchy.type = 'D')

select e.*
  ,(select h.attrib_1
    from heirarchy h
    where h.type = 'A'
    start with h.equipId = e.equip_no
    connect by PRIOR h.parentEquipId = e.id
  ) as typeA_attrib_1
  ,(select h.attrib_2
    from heirarchy h
    where h.type = 'A'
    start with h.equipId = e.equip_no
    connect by PRIOR h.parentEquipId = e.id
  ) as typeA_attrib_2
  ,(select h.attrib_3
    from heirarchy h
    where h.type = 'A'
    start with h.equipId = e.equip_no
    connect by PRIOR h.parentEquipId = e.id
  ) as typeA_attrib_3
  ,(select h.attrib_1
    from heirarchy h
    where h.type = 'B'
    start with h.equipId = e.equip_no
    connect by PRIOR h.parentEquipId = e.id
  ) as typeB_attrib_1
  ,(select h.attrib_2
    from heirarchy h
    where h.type = 'B'
    start with h.equipId = e.equip_no
    connect by PRIOR h.parentEquipId = e.id
  ) as typeB_attrib_2
  ,(select h.attrib_3
    from heirarchy h
    where h.type = 'B'
    start with h.equipId = e.equip_no
    connect by PRIOR h.parentEquipId = e.id
  ) as typeB_attrib_3
  ,(select h.attrib_1
    from heirarchy h
    where h.type = 'C'
    start with h.equipId = e.equip_no
    connect by PRIOR h.parentEquipId = e.id
  ) as typeC_attrib_1
  ,(select h.attrib_2
    from heirarchy h
    where h.type = 'C'
    start with h.equipId = e.equip_no
    connect by PRIOR h.parentEquipId = e.id
  ) as typeC_attrib_2
  ,(select h.attrib_3
    from heirarchy h
    where h.type = 'C'
    start with h.equipId = e.equip_no
    connect by PRIOR h.parentEquipId = e.id
  ) as typeC_attrib_3
from e


texts: Tool to synchronize PostgreSQL DDL and data databases

texts:
We have several developers who each have a local database for testing. We often make DDL and/or data changes to the master database that we need to replicate to each development machine. The framework we use allows us to use the concept of migrations, which cover 95% of the use cases. However, in some cases we are in need of a simple refresh.

The procedure now is to dump the schema on the master, copy it to each developer's local machine, drop the schema from their database, and reload it from the dump. This is slow, and we would like to be able to compare the two databases more directly and only transfer/execute what has changed. Most tools that I've found (such as SLONY) don't handle DDL changes because they rely on triggers. I found this product: http://www.datanamic.com/schemadiff-for-postgresql/index.html, but it seems to be built for a Windows GUI only.

Does anyone have any suggestions?


texts: How to change a PostgreSQL parameter requiring server restart, using postgres -c

texts:
I need to temporarily change a parameter that requires server restart.
The command is as follows:

postgres -c checkpoint_segments=30 -D "C:\Program Files\PostgreSQL\9.2\data"

Where checkpoint_segments is the parameter, and -D specifies the directory containing the postgresql.conf file.

However, I get the following message upon running the command:

2015-10-12 11:44:55 EDT     |LOG:  could not bind IPv6 socket: No error
2015-10-12 11:44:55 EDT     |HINT:  Is another postmaster already running on port 5432? If not, wait a few seconds and retry.
2015-10-12 11:44:55 EDT     |LOG:  could not bind IPv4 socket: No error
2015-10-12 11:44:55 EDT     |HINT:  Is another postmaster already running on port 5432? If not, wait a few seconds and retry.
2015-10-12 11:44:55 EDT     |WARNING:  could not create listen socket for "*"
2015-10-12 11:44:55 EDT     |FATAL:  could not create any TCP/IP sockets

What is wrong with my command?

Note: Using PostgreSQL 9.2.2 for Windows 32 bits.


texts: Non-deterministic functions and row-based binary logging (MySQL)

texts:
Newbie questions. I'm working on a MySQL project that will use only row-level binary logging. There will be no replication. Since logging is row-level, does it even matter if a function is deterministic? From the manual, my understanding is that it does not.

Added 6/9/20:

I've added an example which I think would be non-deterministic; please tell me if I'm wrong. The result will always be the same for a given state of the data, returning the highest date value in a set of rows, but would not always return the same value for the same input of a given patient id, since that changes over time. Is that right?

CREATE FUNCTION `last_enc_date`(patient INT) RETURNS date
BEGIN
# Returns the date of the most recent encounter (of any kind) for patient
DECLARE lastvisit DATE;
SELECT MAX(DATE)
    INTO lastvisit
    FROM encounters
    WHERE personid = patient;
RETURN lastvisit;
END


texts: Extract return type of functions from metadata

texts:
I want to identify all my stored functions which return REFCURSOR.

Is there a way to do that?

I can't find this information anywhere.

I searched in dba_functions,dba_objects,dba_arguments...

What is the (best) way to achieve it?


texts: Indexing very similar character varying field in postgres

texts:
I have a table with a column "name", the table is describing a media asset and the name is a character varying field containing the file name. The table is generated and used by a CMS (Strapi) and I can't really tweak how the columns are used nor the SQL being executed. What I'm hoping to do is slap on an index (or two) and get a bit better performance.
The file names of our files are very similar, pretty much XYZ12345-Q2.png, where XYZ is the same for about 80% of the files. So what I'm wondering is what kind of index (if any) would help speeding up a query such as:
select count(*) as "count"
from "upload_file"
where ("upload_file"."name"::text ILIKE '%some_string%'
       or "upload_file"."id"::text ILIKE '%some_string%'

The id is the primary key, and it's an auto incrementing positive integer.
My concern regarding the actual string is that an index won't do much when the file names are so similar? Or would it actually make a difference? In that case what would be the best index type to use? My understanding of Gin is that it wouldn't really suit this case because there are no words (none of the file names contain space).


texts: Doing something immediately with data in array field

texts:
I have a table that looks like this:

stuff
    id
    content
    score
    children[]

I'd like to run a query that selects all the children for a given id, and then right away gets the full records for all these children sorted by score. The children field holds an array of ids.

I need to do something like this:

SELECT children as array
FROM stuff
where id=1

then

SELECT * FROM stuff
WHERE id=(any id from previous select)
SORT BY score DESC

Children has an array of ids that reference the same table, just like a person (id) can have children (an array of people). I want the full records for all the kids of a single parent, sorted by score.

Any suggestions on the best way to do this? I've looked into WITH RECURSIVE but I'm not sure that's workable.


texts: Multi-Subnet AlwaysOn: Is Routing and Remote Access always required?

texts:
Context

I'm setting up a 2-node AlwaysOn Availability Group (SQL Server 2017 Enterprise) with the following setup:

Existing DC+DNS (One NIC: 10.20.11.1/255.255.254.0)
New AG-Node1 (One NIC: 10.20.11.2/255.255.254.0)
New AG-Node2 (One NIC: 10.22.11.1/255.255.254.0)

I've read that this would be considered as an "AlwaysOn Multi-subnet" scenario (since nodes belong to different subnets).

Question:

Do I really have to treat this as an "AlwaysOn AG Multi subnet" even when all required routing is already implemented at networking device level and computers can all reach each other?

I've read that Routing and Remote Access is supposed to be deployed and configured but I do not get why this would be necessary since the routing is already implemented at networking device level.


texts: Can sp_MSforeachDB be used for DBCC CheckDB without errors being masked?

texts:
I have a test database with an index I intentionally corrupted to test DBCC CheckDB.

When I run:  DBCC CHECKDB (Test2) WITH ALL_ERRORMSGS, NO_INFOMSGS, DATA_PURITY

I get, as expected:

Msg 8928, Level 16, State 1, Line 1
Object ID 2105058535, index ID 2, partition ID 72057594038845440, alloc unit ID 72057594039762944 (type In-row data): Page (1:156) could not be processed.  See other errors for details.
Msg 8939, Level 16, State 98, Line 1
Table error: Object ID 2105058535, index ID 2, partition ID 72057594038845440, alloc unit ID 72057594039762944 (type In-row data), page (1:156). Test (IS_OFF (BUF_IOERR, pBUF->bstat)) failed. Values are 12716041 and -4.
Msg 8980, Level 16, State 1, Line 1
Table error: Object ID 2105058535, index ID 2, partition ID 72057594038845440, alloc unit ID 72057594039762944 (type In-row data). Index node page (0:0), slot 0 refers to child page (1:156) and previous child (0:0), but they were not encountered.
CHECKDB found 0 allocation errors and 3 consistency errors in table 'Names' (object ID 2105058535).
CHECKDB found 0 allocation errors and 3 consistency errors in database 'Test2'.
repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (Test2).

However, when I run:  exec sp_MSforeachDB 'DBCC CHECKDB (?) WITH ALL_ERRORMSGS, NO_INFOMSGS, DATA_PURITY'

I get no errors.

I understand that sp_MSforeachDB is not supported.  However, several blogs suggested using sp_MSforeachDB to check all the DB's on a SQL Server Instance.

Question:  Am I doing something wrong, and if so, what is the fix, or is this some idiosyncrasy with sp_MSforeachDB?


texts: Distributed database for worldwide LIDAR point cloud

texts:
I have an idea for a project that would build a free worldwide 3D scan of the real world. You may think of it as a high-definition version of OpenStreetMap (OSM) project. The point cloud data would originate in LIDAR or stereo camera 3D scans. I am thinking of possible database solution for the project.

The main problem would be storing trillions of data primitives (point cloud points). Each point would have few properties (coords - lon, lat, elevation; intensity, RGB values, some semantic tags). I think the scenario is mostly similar to OSM but with way more data (easily 1000 times the OSM data but most likely much more).

Real spatial features are not neededn it the database and only a queries by location (with the help of geohash or b-tree indexes?) are required.

OSM uses plain Postgres (not PostGIS) because they do not need any geometry operations on top of their nodes. Today (October 2016) the OSM project contains 3.5 billion of nodes in a plain Postgres db table that runs on a single server. The whole database (including ways, relations, editing history etc.) has a size of about 6 TB (see server hardware).

Since I need 1000x the scale of OSM it is clear that many servers will be needed. Can someone help to at least roughly outline how the system may look like? I think data fragmentation may be done geographically by dividing Earth's surface into squares and have one table_points (=one database?) per such patch - lets call these "slave servers". There would be a central "master server" (not a db) that would manage queries for slaves (in case data from multiple slaves would be needed) and handle data returns to clients. Would such model be viable?

I can think of some very sophisticated fully distributed database system but I think at the beginning something very simple like simple Postgres slaves (maybe with few read-only replicas for faster reads) could be sufficient for the basic functionality.

Note: I found the pointcloud extension for Postgres (explanatory video here). It seems to group points into "patches" to minimize table row count. Would this make DB faster?

EDIT: I am also exploring distributed NoSQL databases like Cassandra or CouchDB. Also thinking about a DIY solution with people having nodes at home rather than using a datacentre (mostly due to financial reasons - datacentre needs to be paid). But with a "flat worldwide NoSQL" (read: data not fragmented by geo coords) I am not sure how queries would work (speed etc.).


texts: Minimizing database size (Many small tables)

texts:
Our database is interesting in that we have a large amount of tables that take up a correspondingly large amount of space, but when we do a MySQL dump of said database, it is surprisingly small.

Specifically, the database is about 50GB, and contains about 50k tables.  When dumped, it takes up about 5GB.  (These numbers aren't spot on, but they're close enough for our discussion here).  It's particularly odd to me that the "binary" DB takes up so much more space than the SQL-dumped DB.

The vast majority of the tables (like 49990) are relatively small Wordpress Multisite tables.  These are seldom-used sites with very little content on them.

If it's relevant, we're using innodb_file_per_table.

What is the best way to minimize the size of our database?  Does minimizing the size of the database come at a cost of lower performance?  Ultimately, I'd like to reduce the size of the DB in order to increase performance (e.g. with regard to doing backup/restore operations).

Update: The structure of the tables is essentially just the default Wordpress Multisite layout: http://pastie.org/private/iufzw8z9zlyidqw8b7wggw  Note that I looked into some more accurate numbers, and it appears we have about 9k Multisite instances for a total of nearly 80k tables.  The larger numbers are partially due to our service continuing to grow and add new customers.


texts:
I've facing problem with my MySQL database. I know how to process when I want to do many conditions on one same column.

Example of a table :

No | Letter
------------
1 | a
5 | b
4 | b
4 | c
5 | b
6 | g
4 | a

Typically, if I want to show the 'No' that has Letter a AND Letter b AND Letter C, this query works well :

 SELECT No
 FROM table
 WHERE letter IN ('a', 'b', 'c')
 GROUP BY No
 HAVING COUNt(*) = 3

But what happens, if in the query, I want to subdivide each letter into many occurences and find the 'No' that could have one occurence of GROUP A(a,x,y,z) AND one occurence of GROUP B(b,w,l) AND one occurence of GROUP C(c,k,j) ?

 SELECT No
 FROM table
 WHERE letter IN ('a OR x OR y OR z' AND 'b OR w OR l' AND 'c OR k OR j')
 GROUP BY No
 HAVING COUNt(*) = 3

I don't know how to do that and I'm lost.


texts: Read from large table without causing disk queue length issues

texts:
I have a report that reads from many tables, one of which is 48GB.

To help with performance I planned to create a table which stored a subsection of values from this large table.

However, whenever I read from this table, disk queue length goes from 0.1-0.5 to 10-100.  I've also tried bcp and it has a similar effect on queue length.

How I can get the data out of this table without affecting performance of the database server?